1202 database queries

1. Create DATABASE Test
2. Drop DATABASE Test
3. CREATE TABLE Info
(
Code int PRIMARY KEY,
Name varchar (NOT NULL)
)
Auto_increment Self-growing column
Foreign key (column name) references primary table name (column name) foreign key relationship

4. DROP TABLE Info

Crud:
1.insert into table name (column name) values (value)

2.delete from table name where condition

3.update table Name set column name = value WHERE condition

Simple query

1. Most simple query (check all data)
SELECT * from table name; Note: * Represents all Columns
SELECT * FROM Info

2. Querying a specified column
Select Code,name from Info

3. Modify the column name of the result set
Select Code as ' Code ', name as ' name ' from info

4. Conditional query
SELECT * FROM info where code= ' p003 '

5. Multi-Criteria Query
Query all data in the Info table for code p003 or nation to n001
SELECT * FROM info where code= ' p003 ' or nation= ' n001 '
Querying data in the Info table for code p004 and nation to n001
SELECT * FROM info where code= ' p004 ' and nation= ' n001 '

6. Scope Query
SELECT * from car where price>=40 and price<=60
SELECT * from car where price 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 car where price 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 '% audi% '% any n characters
Query car Name The second character is ' horse ' in cars
SELECT * from car where name like ' _ Horse% ' _ any one character

9. Sort queries
SELECT * FROM Car ORDER BY price ASC ASC Ascending (omitted)
SELECT * FROM car ORDER BY oil desc desc Descending

Sort by brand in ascending order, then
SELECT * FROM car ORDER BY brand,price Desc

10. Go to re-query
Select distinct brand from car

11. Paging Query
Page 10 of the current is the 3rd page
SELECT * FROM Chinastates limit 20,10

One page shows M bar is currently page n
Limit (n-1) *m,m

12. Aggregate functions (statistical functions)
Select COUNT (AreaCode) from Chinastates #查询数据总条数
Select SUM (price) from car #求和
Select AVG (price) from car #求平均
Select Max from Car #求最大值
Select min (price) from car #求最小值

13. Group queries
Find out how many cars are under each series in a car table
Select Brand,count (*) from car GROUP by brand

Check the number of cars sold in the car table more than 3 series
Select brand from Car GROUP by Brand has count (*) >3

1202 database queries