Write in front: This article is my study record. If there are mistakes, please point them out and correct them immediately
DDL, DQL, DML and DCL will be written in four parts, and DQL will be written in this article.

What is DQL?

DQL: data query language, used to query data

First, paste a table, and the following query contents will be used (for reference only)

Query all columns

-- SELECT * FROM Table name;
SELECT * FROM stu;

Result set

• Executing DQL statements in the database will not change the data, but let the database send the result set to the client.
• Result set: the data queried by the query statement is displayed in the form of a table. We call this table a virtual result set and store it in memory. The result set returned by the query is a virtual table.

Queries the data for the specified column

-- SELECT Column name 1,Column name 2 FROM Table name;
SELECT s_name,s_age FROM stu;

Condition query

Conditional query is to give a WHERE clause when querying. Some operators and keywords can be used in the WHERE clause;

Keywords are:=(be equal to),!=(Not equal to),<>(Not equal to),
<(less than),<=(Up to)>(Greater than)>((greater than or equal to)
BETWEEN...AND;What range is the value in
lN(set);
IS NULL;(Empty)IS NOT NULL(Not empty)
AND;And
OR;or
NOT;wrong

use

1. Query the records of students whose gender is male (1) and age is 20

SELECT * FROM stu WHERE s_gender =1 and s_age=20;

1. Query records with student number 1 or zs

SELECT * FROM stu WHERE id=1 OR s_name='zs';

1. Query records with student numbers 1, 2 and 3

SELECT * FROM stu WHERE id=1 OR id=2 OR id=3;

1. Query student records between the ages of 18 and 20

SELECT * FROM stu WHERE s_age BETWEEN 18 AND 20;

1. Query the records of non male students

SELECT * FROM stu WHERE s_gender !=1;

1. Query student records whose name is not nul

SELECT * FROM stu WHERE s_name IS NOT NULL;

Fuzzy query (a kind of conditional query)

Conduct direct inquiry according to the specified closing cone, and use the LIKE keyword followed by the general character

Wildcard:

_: Any letter
%: any 0~n letters

use
Xiangwen's name is a three letter student record

-- Three horizontal lines
SELECT * FROM stu WHERE s_name LIKE '___';

Query the student record whose name is composed of 4 letters and the fourth letter is "g"

-- Three horizontal lines+g
SELECT * from stu WHERE s_name like '___g';

Query student records with names beginning with "m"

SELECT * FROM stu WHERE s_name like 'm%';

Query the student record with the second letter "i" in the name

SELECT * FROM stu WHERE s_name like '_i%';

Ask directly for student records with the letter "l" in their name

SELECT * FROM stu WHERE s_name like '%l%';

Field control query

duplicate removal

-- use DISTINCT keyword
-- Query student names and remove duplicate records
SELECT DISTINCT s_name FROM students;

The results of query fields must be local numeric

-- Query age+Sum of scores
-- SELECT*.Ningduan 1+Field 2 FROM Table name;
SELECT s_age+s_score FROM stu;

If the list value is null, the result is null. You can convert the value to 0, so you can still calculate the result

SELECT *，IF NULL(s_age,0 )+IFNULL (s_score,O) FROM stu;

After the above query statement is executed, a new column appears. The column name is "s_age+s_+score", which is not good-looking, so we can alias the column

SELECT *，IFNULL(s_age,0 )+IFNULL(s_score,0)AS total FROM stu;

sort

Ascending (ASC) and descending (DESC)
Query the student table and sort by score

-- Ascending order
SELECT * FROM stu ORDER BY s_score ASC;
-- Descending order
SELECT * FROM stu ORDER BY s_score DESC;

Aggregate function

Perform statistical calculation on query results

Common aggregate functions

• COUNT(): counts the number of record rows whose specified column is not NULL;
• MAX(): calculates the maximum value of the specified column. If the specified column is of string type, string sorting is used;
• MIN(): calculate the minimum value of the specified column. If the specified column is of string type, use string sorting to calculate;
• SUM(): calculates the sum of the values of the specified column. If the specified column type is not a numeric type, the calculation result is 0;
• AVG(): calculates the average value of the specified column. If the specified column type is not numeric, the calculation result is 0;

use
COUNT:
Number of people with scores in the query table

SELECT COUNT(s_score) FROM stu;

Number of people with scores greater than 80 in the query table

SELECT COUNT(*) FROM stu WHERE s_score>80;

Score + number of people over 80 in the query table

SELECT COUNT(*) FROM stu WHERE s_age+s_score>80;

SELECT COUNT(*) FROM stu WHERE IFNULL(s_age,0)+IFNULL(s_score,0)>80;

Score and of all people in the query table

SELECT SUM(s_score) FROM stu;

Score and age of all people in the query table

SELECT SUM(s_age+s_score) FROM stu;

Average of all scores in the query table

SELECT AVG(s_score) FROM stu;

Grouping query

The query results are grouped according to one or more fields, and those with the same field values are a group

Group use

SELECT * FROM stu GROUP BY s_gender;

According to the gender field, there are only two values (1 and 2 ') in the gender field, so it is divided into two groups
When group by is used alone, only the first record of each group is displayed
Therefore, it is of little practical significance to use group by alone

Query the names and genders of all students and group them by gender

SELECT s_gender,GROUP_CONCAT(s_name) FROM stu GROUP BY s_gender;

matters needing attention
When using grouping, the fields directly following select generally appear after group by

group by+ group_concat()

• group_ Concat (field name) can be used as an output field
• Indicates that after grouping, group is used according to the grouping results_ Concat () to place a collection of values for a field in each group

group by + aggregate function
Query scores and sum of scores by gender

SELECT s_gender, GROUP_CONCAT(s_score),SUM(s_score) FROM stu GROUP BY s_gender;

Inspired by group concatO, since we can count the set of values of a field in each group, we can also do some operations on this "set of values" through the set function

Query two genders and students with scores greater than 70 in both genders

SELECT s_gender,GROUP_CONCAT(s_score) FROM stu WHERE s_score>70 GROUP BY s_gender;

group by + having

• Used to specify some conditions after grouping queries to output query results
• Having is the same as where, but having can only be used for group by
• The difference between having and where

having is to filter the data after grouping. where is to filter the data before grouping
Aggregate functions can be used after having, but not after where
WHERE is the condition for recording before grouping. If a row of records does not meet the conditions of the WHERE clause, the row of records will not participate in the grouping; HAVING is a constraint on the grouped data.

Query the gender whose total score is greater than 240 and the total score

SELECT s_gender,GROUP_CONCAT(s_score) , SUM(s_score)FROM stu  
GROUP BY s_gender HAVING SUM(s_score)>240;

Query the gender whose score is greater than 70 and the sum of scores is greater than 240, as well as the sum of scores

SELECT s_gender, GROUP_CONCAT(s_score),SUM(s_score) FROM stu 
WHERE s_score>70 
GROUP BY s_gender HAVING SUM(s_score)>240;

Writing order of SQL statements

Execution order of SQL statements

LIMIT paging query

• Which line to start with, how many lines in total
• Limit parameter 1 (which line to start from), parameter 2 (how many lines to check in total)
• The subscript starts at 0
• Pagination idea
  

SELECT * FROM STU LIMIT (curPage-1)*pageSize,pageSize;

The first three records in the query table

SELECT * FROM stu LIMIT 0,3;