MySQL Common code

Create DATABASE name

CREATE TABLE CeShi1
(
Uid varchar (primary) key,
PWD varchar (50),
Name varchar (50),
Nation varchar (50),
Foreign KEY (Nation) references Nation (Code)
)

Writing query statements requires attention:
1. When creating a table, do not write a comma after the last column
2. If more than one statement is executed together, be aware that semicolons are separated between statements
3. Write code all symbols are half-width

Relational database: There is a relationship between tables and tables

Create several keywords for the table:
1. Primary key: Primary key
2. Non-empty: NOT NULL
3. Self-growth column: auto_increment
4. Foreign key Relationship: FOREIGN key (column name) references table name (column name)

CRUD operations: Adding and deleting changes

1. Add Data:
Insert into Info values (', ', ', ', ', ', ') require that the value inside the parentheses be the same as the number of columns in the table
Insert into Info (code,name) VALUES (",") adds the value of the specified column

2. Modify the data
Update Info Set Name = ' Zhang San ' where Code = ' p001 '

3. Delete data
Delete from Info where Code = ' p001 '

ALTER TABLE name to add, delete, and modify columns in an existing table

PRIMARY KEY constraint primary key #一个表中只能有一个主键

ALTER TABLE name add primary key (column name)

Uniquely constrained unique #一个表中可以有多个unique

ALTER TABLE name add unique (column name)

FOREIGN KEY constraint foreign key References #一个表中可以有多个外键

Altre table foreign Key name add foreign key (column name) references primary key table name (column name)

Query Data :

1. General Enquiry, check all
SELECT * FROM Info #查所有数据
Select Code,name from Info #查指定列

2. Conditional query
SELECT * from Info where Code = ' p001 ' #一个条件
SELECT * from Info where Name = ' Zhang San ' and Nation = ' n001 ' #两个条件并的关系
SELECT * from Info where Name = ' Zhang San ' or Nation = ' n001 ' #两个条件或的关系

3. Sort queries
SELECT * FROM Info order by Birthday #默认升序排列asc If you want to sort desc in descending order
SELECT * from Car ORDER BY brand,oil Desc #多列排序

4. Aggregation functions
Select Count(*) from Info #取个数
Select sum(price) from Car #查询price列的和
Select avg(price) from Car #查询price列的平均值
Select Maxfrom Car #查询price列的最大值
Select min(price) from Car #查询price列的最小值

5. Paging Query
SELECT * from Car limit n,m #跳过n条数据取m条数据
SELECT * from Car limit (n-1) *m,m #第二页跳过 (n-1) *m bar data fetch m data

6. Group queries
Select brand from Car group by Brand #简单分组查询
Select brand from Car group by Brand has count (*)>2 #查询系列里面车的数量大于2的系列

7. Go to re-query
Select distinct Brand from Car

8. Modify column names
Select Brand as ' series ' from Car

9. Fuzzy Query
SELECT * from Car where Name like ' _ Dee% '% stands for any number of characters _ represents one character

10. Discrete query
SELECT * from Car where Code in (' c001 ', ' c002 ', ' c003 ', ' c004 ')
SELECT * from Car where Code not in (' c001 ', ' c002 ', ' c003 ', ' c004 ')

Advanced Query :

1. Connection Query

SELECT * FROM Info,nation #得出的结果称为笛卡尔积
SELECT * from info,nation where info.nation = Nation.code

Join on connection

SELECT * from Info join Nation #join连接
SELECT * from Info join Nation on info.nation = Nation.code

2. Joint queries

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

3. Sub-query

1) Unrelated subqueries

Select Code from Nation where Name = ' Han ' #去Nation表中查询汉族的民族代号

SELECT * from Info where Nation = (ethnic code) #在Info表中查询民族代号为上一个查询结果的所有信息

SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

The results of a subquery query are used by the parent query, which can be performed independently by a subquery called unrelated subqueries

2) Related sub-query

SELECT * from Car where oil< (average fuel consumption for this series) #查询油耗小于该系列平均油耗的

Select AVG (oil) from Car where Brand = "value" #查询某系列的平均油耗

SELECT * from Car a where oil< (select AVG (oil) from car b where B.brand = A.brand) #a, B is alias

# inner query when the outer query is fixed: query in order one by one query

Select AVG (oil) from Car b where B.brond = ' b001 ' #结果为8.7

#外层查询就变为:

SELECT * from Car where oil < 8.7

MySQL Common code