Database Part---query-simple query;

1. Simplest query (all data query)

SELECT * from table name; * Represents all Columns

2. Querying data for a specified column

Select Column Name 1, column name 2 from table name

3. Modify the column name of the result set

Select Column Name 1 as ' Code 1 ', column name 2 as ' Code 2 ' from table name

4. Conditional query

SELECT * FROM table name where column name = ';

5. Multi-Criteria Query

SELECT * FROM table name where column name 1= ' or column name 2 = ';

SELECT * FROM table name where column name 1= ' and column name 2 = ';

6. Scope Query

Take the car watch price as an example and choose between 40 and 60 of the price:

SELECT * FROM table name where column name >=40 and column name <=60;

SELECT * FROM table name where column name between and 60;

7. Discrete query

Check car prices in (10,20,30,40,50,60) for car information

SELECT * from car where price=10 or price=20 or price=30 or price=40 or price=50 or price=60;

SELECT * from car where price in (10,20,30,40,50,60);

SELECT * from the car where price is not in (10,20,30,40,50,60);

8. Fuzzy query (keyword query)

Query car table inside name contains Audi's

SELECT * from car where name like '% Audi% '; % represents any number of characters

SELECT * from car where name like '% Audi '; End With ' Audi '

SELECT * from car where name like ' Audi% '; Start with ' Audi '

Query car table name second character for ' horse ' cars

SELECT * from car where name like ' _ Ma% '; _ represents any one character;

9. Sort queries

Car Watch

SELECT * FROM Car ORDER by price ASC;     Sort in ascending order of price. ASC stands for Ascending (because the default ascending order can be omitted)

SELECT * FROM car order by oil desc;   Sorted in descending order of fuel consumption. DESC represents descending order.

Sort by brand in ascending order, then

SELECT * FROM Car ORDER by brand Asc,price desc; When the data is duplicated in brand, it is then sorted by price

10. To repeat the query

Select DISTINCT column name from table name;

Select distinct brand from car;

11. Paging Query

Page 10 of the current is the 2nd page

SELECT * FROM car limit 10, 10; The first 10 represents skipping 10, and the second 10 means showing 10 bars.

One page shows M bar is currently page n

Limit (n-1) *m,m;

12. Aggregate functions (statistical functions)

Select COUNT (*) from chinastates Query the total number of data in the Chinastates table

Select COUNT (primary key column name) from chinastates query the total number of data in the Chinastates table

Select SUM (price) from car; Sum represents sum, the total price of all cars in the car table

Select AVG (price) from car; AVG representative averages. Find the average of all car prices in the car table

Select Max from car, the maximum value of all car prices in the car table

Select Max from car, the minimum value of all car prices in the car table

13. Group queries

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

Select Brand,count (*) from car GROUP by brand; From car group by brand-grouped by brand. Select Brand,count (*)--and query the number of brand and each group

Check the number of cars sold in the car table more than 3 series

Select brand from car GROUP by Brand have count (*) >1;       From car group by brand-grouped by brand. having+ Conditions--having COUNT (*) >1; quantity greater than 1

Database Part---query-simple query;