Single table query 、 add to 、 Update and delete data

Single table query

Simple query

Query all fields

SELECT * FROM  Table name  ;

Query the specified field

SELECT  Field name 1, Field name 2,... FROM  Table name  ;

Query by criteria

Queries with relational operators

SELECT *|( Field name 1, Field name 2,... )
FROM  Table name 
WHERE  Relationship expression  ;

“ be equal to =” " It's not equal to != or <>“ ” Less than <“ ” Greater than >“ ” Less than or equal to <=” ” Greater than or equal to >=“
belt IN Keyword query

SELECT *|( Field name 1, Field name 2,... )
FROM  Table name  
WHERE  Field name  [NOT] IN ( Elements 1, Elements 2,...) ;

NOT Is an optional parameter , Indicates that the query is not in IN Keyword specifies the records in the collection scope .
For example, query student In the table id The value is 1,2,3 The record of ：select * from student where id in(1,2,3);
belt BETWEEN AND Keyword query

SELECT *|( Field name 1, Field name 2,... )
FROM  Table name 
WHERE  Field name  [NOT] BETWEEN  value 1 AND  value 2 ;

NOT Is an optional parameter , Indicates that the query is not in the specified range .
For example, query student In the table id The scope is 【2,5】 Of name：select name from student where id between 2 and 5;
Null value query

SELECT *|( Field name 1, Field name 2,... )
FROM  Table name 
WHERE  Field name  IS [NOT] NULL ;

NOT Is an optional parameter , Indicates that the query is not a null record .
For example, query student In the table grade Blank student name ：select name from student where grade is NULL;
belt DISTINCT Keyword query （ Used to filter duplicate records ）

SELECT DISTINCT  Field name  FROM  Table name  ;

When DISTINCT When there are multiple field names , Only when the values of multiple fields are repeated together is it considered to be a duplicate record .
belt LIKE Keyword query

SELECT *|( Field name 1, Field name 2,... )
FROM  Table name 
WHERE  Field name  [NOT] LIKE ' Match string ' ;

NOT Is an optional parameter , Use NOT Indicates that the query does not match the specified string .
among ‘ Match string ’ It refers to the inclusion of a percent sign （%） Or underline （ _ ） Wildcard string for ：
Percent sign （%） wildcard , Match any length of string , Include empty string . for example "c%“ To refer to c Starting string ,”%c" To refer to c a null-terminated string ,"%c%" It means containing c String （ Including the beginning and the end ）.select id,name from student where name like '%c%' ;
Underline ( _ ) wildcard , An underline represents a character , for example "c_t" It begins with c It ends with t The length is 3 String ; And pay attention to the space , With a space “M_ _QL” Only match to MY SQL, Can't match to MYSQL.
special ： If you want to match % or _ Need to use translation characters “\”, for example “%\%%” Means to contain % String .
belt AND Multi criteria query of keywords

SELECT *|( Field name 1, Field name 2,... )
FROM  Table name 
WHERE  expression 1 AND  expression 2 [... AND  expression n]

For example, query student In the table id by 1 and 2、name With c At the beginning and grade>50 The record of ：select * from student where id in(1,2) and name like 'c%' and grade>50;
belt OR Multi criteria query of keywords

SELECT *|( Field name 1, Field name 2,... )
FROM  Table name 
WHERE  expression 1 OR  expression 2 [... OR  expression n]

Satisfy one of the expressions , For example, query student In the table id<3 perhaps sex The name of the female student ：select name from student where id<1 or sex=' Woman ';
expand ： OR and AND When keywords are used together ,AND Has a higher priority than OR, When used together , Calculate first AND Conditional expressions on both sides , Count again OR Conditional expressions on both sides of the condition .

Advanced query

Aggregate functions

• COUNT( ) function ： Count the number of records

SELECT COUNT( * ) FROM  Table name  ;

• SUM( ) function ： Sum a field

SELECT SUM( Field name ) FROM  Table name  ;

• AVG( ) function ： Average a field

SELECT AVG( Field name ) FROM  Table name  ;

• MAX( ) function ： Find the maximum value of a field

SELECT MAX( Field name ) FROM  Table name  ;

• MIN( ) function ： Find the minimum value of a field

SELECT MIN( Field name ) FROM  Table name  ;

Sort query results

SELECT *| Field name 1, Field name 2,...
FROM  Table name 
ORDER BY  Field name 1 [ASC|DESC], Field name 2 [ASC|DESC]... ;

ASC Ascending ,DESC Descending ,rand() For random sorting , The default is ascending .
For example, find out student Table all records and follow grade Sort ：select * from student order by grade;
When sorting by multiple fields is specified , First sort the first field , If you encounter the same field value, then sort these according to the next .

Group query

Use GROUP BY Group by values in a field or fields , Those with the same value in the field are a group .

SELECT *| Field name 1, Field name 2,...
FROM  Table name 
GROUP BY  Field name 1, Field name 2,...[HAVING  Conditional expression ] ;

Use alone GROUP BY grouping ：
The query is a record in each group .
GROUP BY Used with aggregate functions ：
For example, query student Tabular count( * ) and sex, according to sex Group query , Then calculate the number of students in each group select count(*),sex from student group by sex; The result is （ among 1、5、3 Indicates the number of corresponding sex ）

+----------+------+
| count(*) | sex  |
+----------+------+
|        1 | NULL |
|        5 |  Woman    |
|        3 |  male    |
+----------+------+
3 rows in set (0.00 sec)

GROUP BY and HAVING Use keywords together
HAVING Key words and WHERE Keywords work the same , Are used to set conditional expressions , Filter the query results , Difference between them ：HAVING Keywords can be followed by aggregate functions , and WHERE Keywords cannot .
For example student According to the table sex Field for grouping query , Find out grade The sum of field values is greater than 200 The grouping ：select sum(grade),sex from student group by sex having sum(grade)>200;
Use LIMIT Limit the number of query results
This keyword can specify which record the query result starts from and how many pieces of information to query .

SELECT  Field name 1, Field name 2,...
FROM  Table name 
LIMIT [OFFSET,]  Record number  ;

OFFSET Is an optional parameter , Represents the offset , If the offset is 0 Start with the first record of the query , The offset for the 1 Start with the two inside , And so on . When not specified, the default value is 0.‘ Record number ’ Indicates the number of returned query records .
For example, query student No 2 To the first article 5 A record of ：select * from student limit 1,4;

Add update and delete data

Add data

INSERT Add
（ When adding to all fields , You can not write the field name ）

INSERT INTO  Table name ( Field name 1, Field name 2, ...)
VALUES( value 1, value 2, ...) ;

There is another way to add ：

INSERT INTO  Table name 
SET  Field name 1= value 1[(, Field name 2= value 2,...)] ;

INSERT Statement to add multiple records at the same time

INSERT INTO  Table name  [( Field name 1, Field name 2, ...)]
VALUES( value 1, value 2, ...) ,
( value 1, value 2, ...) ,
...
( value 1, value 2, ...) ;

Update data

UPDATE  Table name 
SET  Field name 1= value 1[, Field name 2= value 2,...]
[WHERE  Conditional expression ] ;

Don't write WHERE When, it means to change the data of all specified field names .

Delete data

DELETE FROM  Table name 
[WHERE  Conditional expression ] ;

Don't write WHERE When, it means to delete all the data of the table .
You can also use keywords TRUNCATE Delete all data in the table

TRUNCATE [TABLE]  Table name  ;

notes ： DELETE and TURNCATE The difference between