12-2 MySQL Query

Teacher Outline

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

Own notes

Simple query
SELECT * from table name; Note: * represents all
);

Querying a specified column

Select column name, column name from table name

Modify the column name of the result set
Select column name as ', column name as ' from table name

Conditional query
SELECT * FROM table name where condition

Multi-Criteria Query
SELECT * FROM table name where condition or condition
SELECT * FROM table name where condition and condition

Scope Query
SELECT * FROM table name where price>=40 and price<=60;
SELECT * from table name where price betwen and 60

Discrete query
SELECT * from table name where price in (20,30,40,50);
SELECT * from table name where price isn't in (20,30,40,50)

Fuzzy query (keyword query)
SELECT * from table name where name like '% Audi% '% stands for any number of characters

SELECT * from table name where name like ' _ Horse% ' _ represents 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

Go to re-query
Select DISTINCT column from table name

Paging Query
A page showing 10, is currently the second page

Select *from Table name limit 10 (number of hops skipped), 10 (take third)

Aggregate functions (statistical functions)

Select COUNT (primary key) from table name query data total number of bars
Select SUM (column name) from table name sum
Select AVG (column name) from table name averaging
Select Max (column name) from table name to find the maximum value
Select min (column name) from table name to find minimum value

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 bought in the car table more than 3 series
Select brand from car GROUP by Brand have count*

12-2 MySQL Query