Records in MySQL's DQL query table
1. table creation
CREATE TABLE student ( id int, -- number name varchar(20), -- full name age int, -- Age sex varchar(5), -- Gender address varchar(100), -- address math int, -- mathematics english int -- English ); INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'Jack Ma',55,'male','State',66,78),(2,'pony ',45,'female','Shenzhen',98,87),(3,'Majingtao',55,'male','Hong Kong',56,77),(4,'Liuyan',20, ,20,'female','Hunan',76,65),(5,'Liuqing',20,'male','Hunan',86,NULL),(6,'Lau Andy',57,'male','Hong Kong',99,99),(7,'Mader',22,'female','Hong Kong',99,99),(8,'Demacia',18,'male','Nanjing',56,65);
2. grammar
Syntax:
select
Field list
from
Table name list
where
Condition list
group by
Group fields
having
Conditions after grouping
order by
sort
limit
Paging qualification
3.select basic query
Basic query
- Query of multiple fields
select field name 1, field name 2... from table name;
- be careful:
- If you query all fields, you can use * instead of the field list.
- Remove duplicates:
- distinct
- Calculation column
- Generally, four operations can be used to calculate the values of some columns. (generally, only numerical calculation will be performed)
- Ifnull (expression 1, expression 2): the calculation results of operations involving null are null
- Expression 1: which field needs to be judged to be null
- The replacement value if the field is null.
- Alias:
- as:
select * from Table name; select name,age from student; select address from student; select distinct address from student; #Remove duplicate select distinct address ,name from student; #You must have duplicate address and name to perform de duplication select name,math,english,math+english from student;#Calculate the sum of mathematics and English, but the original value will not be changed. If NULL is involved, the operation result will be null select name,math,english,math+ifnull(english,0) from student; #Treating null as 0 does not change the original value, but the column name is ugly select name,math,english,math+ifnull(english,0) as Total score from student; #Aliasing does not change the original value, select name ,math as mathematics,english as English ,math+ifnull(english,0) as Total score from student; #Alias
4.where condition query
Condition query
- where clause followed by condition
- operator
-
,< ,<= ,>= ,= ,<>,!=
-
and or&&
-
BETWEEN...AND
-
Or or||
-
In (set)
-
IS NULL , IS NOT NULL
-
LIKE: fuzzy query
-
Placeholder:
-
_: Single arbitrary character
-
%: multiple arbitrary characters
SELECT * FROM student WHERE age > 20;-- Query age > 20 SELECT * FROM student WHERE age >= 20;-- Query age ≥ 20 SELECT * FROM student WHERE age = 20;-- Query age equals 20 years old #Note not = =, yes= SELECT * FROM student WHERE age != 20;-- Query age is not equal to 20 years old SELECT * FROM student WHERE age <> 20;-- Like the above sentence functions, it is also an unequal sign; Query age is not equal to 20 years old SELECT * FROM student WHERE age >= 20 && age <=30;-- Query age ≥ 20 ≤ 30 SELECT * FROM student WHERE age >= 20 AND age <=30;-- The same function as the above statement SELECT * FROM student WHERE age BETWEEN 20 AND 30-- The same function as the above statement SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25-- Query the information of 22, 18 and 25 years old; Output if any one of them exists SELECT * FROM student WHERE age = 22 || age = 18 || age = 25;-- Query the information of 22, 18 and 25 years old; Output if any one of them exists SELECT * FROM student WHERE age IN (22,18,25);-- Query the information of 22, 18 and 25 years old; Output if any one of them exists SELECT * FROM student WHERE english = NULL; -- null Value cannot be used = (!=) judge SELECT * FROM student WHERE english IS NULL; ** SELECT * FROM student WHERE english = NULL; -- This statement is incorrect; null Value cannot be used = (!=) judge;The following statement is required SELECT * FROM student WHERE english IS NOT NULL; -- Query English score is not null SELECT * FROM student WHERE NAME LIKE 'horse%';-- What are the surnames of Ma? SELECT * FROM student WHERE NAME LIKE "_turn%";-- The second word of the query name is the person SELECT * FROM student WHERE NAME LIKE '___';-- Query the person whose name is 3 characters; These are three underscores SELECT * FROM student WHERE NAME LIKE '%virtue%';-- Query the person whose name contains de
5.order sort query
Sort query
- Syntax: order by clause
- order by sort field 1 sort by 1, sort field 2 sort by 2
- Sort by:
- ASC: ascending, default.
- DESC: descending.
- be careful:
- If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same.
select * from student order by math ;#In ascending order by default; Note that English is in descending order when math scores are the same select * from student order by math desc; #In descending order; Note that English is in descending order when math scores are the same select * from student order by math asc; #In ascending order; Note that English is in descending order when math scores are the same select * from student order by math asc,english asc ;#In ascending order; Note that English is in ascending order when math scores are the same select * from student order by math asc,english desc;#In ascending order; Note that English is in descending order when math scores are the same select * from student order by math asc,english; #In ascending order; Note that English is in ascending order when math scores are the same
6. aggregate query
Aggregate function: performs vertical calculation on a column of data as a whole.
- Count: count
- Generally, non empty columns are selected: primary key
- count(*)
- max: calculate the maximum value
- min: calculate the minimum value
- sum: Calculation and
- avg: calculate the average value
*Note: the calculation of aggregate function excludes null values.
Solution: - Select non empty columns for calculation
- IFNULL function
select count(name) from student; select count(english) from student; select count(ifnull(english,0)) from student; select count(*) from student; #As long as one is not null, it can be counted select max(math) from student; select min(math) from student; select sum(math) from student; select avg(math) from student;
7.group and having group query
Group query:
- Syntax: group by group field;
- be careful:
- Fields queried after grouping: grouping fields and aggregate functions (Note: only these two types can be used, and no others can be written)
- What is the difference between where and having?
- where is qualified before grouping. If the conditions are not met, it will not participate in grouping. having is qualified after grouping. If the result is not satisfied, it will not be queried
- where cannot be followed by an aggregate function. having can judge the aggregate function.
SELECT sex , AVG(math) FROM student GROUP BY sex;-- Grouped by sex. Query the average scores of male and female students respectively SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;-- Grouped by sex. Query the average scores of male and female students respectively,Number of persons SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;-- Grouped by sex. Query the average scores of male and female students respectively,Number of people required: those whose scores are lower than 70 will not participate in the grouping SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;-- Grouped by sex. Query the average scores of male and female students respectively,Number of people required: those whose scores are lower than 70 will not participate in the grouping,After grouping. The number of people should be more than 2 SELECT sex , AVG(math),COUNT(id) Number of persons FROM student WHERE math > 70 GROUP BY sex HAVING Number of persons > 2;
8.limit paging query
Paging query
1. syntax: the index starting from limit, and the number of queries per page;
2. formula: starting index = (current page number - 1) * number of entries displayed per page
– 3 records per page
3.limit is a MySQL "dialect"
4.LIMIT accepts one or two digital parameters;
For example: SELECT * FROM table LIMIT 5// Retrieve the first 5 record lines. In other words, LIMIT n is equivalent to LIMIT 0,n,
SELECT * FROM student LIMIT 0,3; -- Page 1 ;Check from 1 to 3 SELECT * FROM student LIMIT 3,3; -- Page 2 ;From 3 to 6 select *from student limit 2,5; -- From 3 to 7 SELECT * FROM student LIMIT 6,3; -- Page 3 ;Check from 7 to 9