MySQL related knowledge

Create a table
#品牌表
CREATE TABLE Pinpai (
IDS int Auto_increment primary KEY, #主键自增长列
Name varchar (#品牌名称)
);
#商品表
CREATE TABLE Shangpin (
Code int primary KEY, #商品代号
Name varchar (not NULL), #商品名称
Price float, #商品价格
Pinpai int, #所属品牌
Foreign KEY (Pinpai) references Pinpai (IDS)
)

Primary key: Primary key
Non-empty: NOT NULL
Self-growth column: auto_increment
FOREIGN key: FOREIGN key

CRUD Operations
Add Data:
Insert into table name values (' p007 ', ' John Doe ', 0, ' n001 ', ' 1988-2-3 14:20:30 ');

INSERT into info values (' p007 ', ' John Doe ', 0, ' n001 ', ' 1988-2-3 14:20:30 ');

Insert into info (code,name,sex,nation) VALUES (' p008 ', ' Zhang San ', 0, ' n001 ');

Insert into work values (0, ' p001 ', ' 1989-2-3 ', ' 1990-3-4 ', ' AA ', ' AA ', 1);

1. If the column type is a string, the data outer layer is enclosed in single quotation marks
2. If the column type is bool type, add 0 or 1
3. If the column is a datetime type, add the format ' year-month-day time: minutes: Seconds '
4. If the column is an integer or decimal type, do not add anything to the outer layer of the data
5. There are several columns in the table, and there are several data to add.
6.SQL statements are case insensitive
7. Self-growth column to 0

To modify the data:
Update table name set Name= ' Harry ' Where condition
Update info set name= ' Harry ' where code= ' p005 '

Update info set name= ' Zhang San ', sex=1 where code= ' p005 '

Delete data:
Delete from table name where condition
Delete from info where code= ' p008 '

Query data:
Querying all data
SELECT * FROM table name
SELECT * FROM Info
Querying a specified column
Select Code,name from Info
Querying data for some rows (conditional query)
SELECT * FROM info where nation= ' n001 '

Three paradigms of database design
First paradigm: The atomicity of columns
Each column is no longer split.
Second paradigm: Each column in the table should have a relationship with the primary key
The third paradigm: each column in the table should have a direct relationship to the primary key
Inquire
Simple query:
1. Querying all data
SELECT * FROM Info
2. Querying a specified column
Select Code,name from Info
3. Specify a name for the column
Select Code as ' Code ', name as ' name ' from info
4. Conditional query
SELECT * FROM info where code= ' p001 '
SELECT * FROM info where code= ' p001 ' and nation= ' n001 '
5. Fuzzy query
SELECT * from car where name like '% Audi% '
6. Sort queries
SELECT * FROM Car ORDER by Price Asc,oil desc
7. Go to re-query
Select distinct brand from car
8. Paging Query
SELECT * FROM car limit 5,5
9. Statistical queries (aggregation functions)
Number of data bars
Select count (code) from car
Take maximum value
Select Max (price) from car
Take the minimum value
Select min (price) from car
Take average
Select AVG (price) from car
10. Group queries
Select Brand,count (*) from car GROUP by brand
Select brand from Car GROUP by Brand has count (*) >=3
11. Scope Query
SELECT * from car where price>=40 and price<=60
SELECT * from car where price between and 60
12. Discrete query
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)

Advanced Query
1. Joint queries

Select Code,name from Info
Union
Select Code,name from Nation

2. Connection Query
To form a Cartesian product
SELECT * FROM Info,nation where Info.nation=nation.code
Select Info.code,info.name,sex,nation.name,birthday from Info,nation where Info.nation=nation.code

Select Info.code,info.name,sex,nation.name,birthday from info join nation on Info.nation=nation.code

3. Sub-query =
The result of the subquery is used as a condition of the parent query

Unrelated subqueries
The subquery is not related to the parent query, and the subquery is taken out separately to perform
1. Find all the people of the nation as "Han" information
SELECT * FROM info where nation = (select code from Nation where Name= ' Han ')
2. Check all manufacturers are "FAW-VW" car information
SELECT * FROM car where brand in (select Brand_Code from Brand where Prod_code in (select Prod_code from Productor where pro D_name= ' FAW Volkswagen ')

Related sub-query
Inquire about vehicle fuel consumption below the average fuel consumption of the series
SELECT * FROM car where oil< (average fuel consumption for this series)
Select AVG (oil) from car where brand= ' the series '

SELECT * from Car a where oil< (select Avg. from car b where B.brand=a.brand)

Any as long as one of the
All
Now can be taken to the current year

MySQL related knowledge