Mysql Basics 3

Enquiry column Select .... From ...

a . Simple query (check all data)

Select*from Table name Note: * Check all the columns

--------------------------------------------------------------------------------------

Two   querying data for a specified column (the query result is virtual)

Select column name, column name from table name

Example:select Code,name from info;

Three Modify the column name of the result set

Select Code as ' Code ', name as ' name ' from info

---------------------------- query row -----------------------------------------------

Four Out of the news. Specify the row's data

Select *form Info where code= ' p003 ';

five . Multi-Criteria Query

Query the info table for code p003 or nation='n001 ' of

Select *form Info where code= ' p003 ' or nation= ' n001 ';

Query the info table for code p003 and nation=' n001' 's

Select *form Info where code= ' p003 ' and nation= ' n001 ';

Six Scope Query

SELECT * from info where price>=40 and price<=70;

SELECT * from info where price between and 70;

------------------------------------------------------------------------in

Seven . Discrete Query

Find car Prices (20,32,423,54,657,787) all the cars inside

Select *from Info where price in (20,32,423,54,657,787);

Check car prices are not (20,32,423,54,657,787) all the cars inside

Select *from Info where price isn't in (20,32,3,54,657,787);

-----------------------------------------------------------------------like

Eight Fuzzy query

the name of the query table also has Audi's

Select*from car where name like '% Audi %'% denotes any n characters

Query car table name the second character for a horse

SELECT * from car where name like ' _ horse ' _ represents a character

Nine . Sort Queries

Price in ascending order

Select*from car Order by price ASC ASC Ascending (can be omitted)

Price Descending row

Select*from car order by price desc ASC Ascending (can be omitted)

Order by brand and then Price

Select*from car order by Brand,price,desc;

Ten Go to re-query

Select Distance brand from car;

11 ;

one page shows The current is the third page

Select*from Car Limit 20,10

--------------------------------------------------------------------------------------------------------------- ------

12. Aggregation Functions (statistical function)

Select COUNT (*) from chinastates # query data total number of bars

Select COUNT (AreaCode) from chinastates # query data Total number of parentheses turn into primary key column to improve operational efficiency

Select COUNT (AreaCode) from chinastates # query data Total number of parentheses turn into primary key column to improve operational efficiency

Select SUM (price) from car sum

Select Ave (Price) from car average

Select Max (price) from car Max

Select min (price) from car min

---------------------------------GROUP by ..... Having-------------------------------------------------------------

13. Group queries

Find out how many cars are under each series in a car table

Select Brand,count (*) from car GROUP by brand;

Find a car shop the car sold the number is greater than 4

Select brand from car GROUP by Brand have count (*) >3;

Mysql Basics 3