Database MySQL Statement

Lamp:linux system a Apache server MySQL database php language

MySQL: Common code

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:

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 '

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 Max from Car #查询price列的最大值
Select min (Price) from Car #查询price列的最小值

5. Paging Query
SELECT * from Car limit n,m #跳过n条数据取m条数据

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 ')

Database MySQL Statement