Fuzzy queries (like, between, is null):

• Fuzzy Query-LIKE
• When querying, the content in the field does not necessarily match the query content exactly, as long as the field contains these contents.

Inquiry for student information surnamed Zhang

SELECT id,NAME FROM student WHERE NAME LIKE 'Zhang%';

Query the student information whose second name is Zhu

SELECT id,NAME FROM student WHERE NAME LIKE '_Zhu%';

Query the last word for Zhu's student information

SELECT id,NAME FROM student WHERE NAME LIKE '%Zhu';

Where% represents more than one arbitrary value, represents an arbitrary value.

• Fuzzy Query-BETWEEN

Query the records of a field in a specific range

SELECT NAME,age FROM student WHERE age BETWEEN 20 AND 30;
//Equivalent to
SELECT NAME,age FROM student WHERE age>=20 AND age<=30;

• Fuzzy Query-IS NULL

Query out records with empty contents in a field

SELECT id,NAME FROM student WHERE NAME is NULL;

Data Query-Sort:

SELECT < Column Name > FROM < Table Name > [WHERE < Query Conditional Expressions >] 
[ORDER BY < Sorted Column Name > [ASC or DESC]]
asc: asc (default) desc: descending

The default id is ascending sort

SELECT * FROM student ORDER BY id

Sort in ascending order

SELECT * FROM student ORDER BY id asc

Sort in descending order

SELECT * FROM student ORDER BY id desc

Sort by multiple columns (first by age, then by id when age is the same). (If a desc is added at the end, then only id is sorted by desc.)

SELECT *  FROM student ORDER BY age,id

Group Query - GROUP BY:

The common use of group by is to cooperate with aggregation function and use group information for statistics. The common use of group by is to cooperate with aggregation function such as max to screen data for analysis, and with having to filter.

CREATE TABLE `user_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` VARCHAR(50) NOT NULL,
    `grade` VARCHAR(50) NOT NULL
)

INSERT INTO `user_info` (`user_id`,`grade`) VALUES ('10230','C'),('10229','C'),('10227','B'),('10225', 'B'),('10222','A'),('10221','A');

The data is grouped according to the grad field, querying the largest user_id of each group and the current group content.

SELECT MAX(user_id),grade FROM user_info GROUP BY grade ;

Filter out grade s that are not satisfied with'> A'

SELECT MAX(user_id),grade FROM user_info GROUP BY grade  HAVING grade>'A'

Query the total price of more than 100 goods are those.

select product ,sum(price) from orders group by product having sum(price)>100 

Suggestion: When using grouping, only to write the column of grouping, as well as aggregation function, the content of other columns, not
To show.

Demand: In addition to detergent. The total cost of other goods is 100.
Inquiry: commodities, product s
Conditions: product <> laundry powder

sum(price) > 100 
select product from orders where product<> 'Washing powder' group by product having sum(price) > 100; 
select product from orders where product != 'Washing powder' group by product having sum(price) > 100; 

• The WHERE clause removes data from the data source that does not meet its search criteria
• The GROUP BY clause collects data into groups, and the statistical function calculates the statistical values for each group.
• The HAVING clause removes groups of rows that do not meet the search criteria for their groups.
• WHERE—>GROUP BY—>HAVING

EXISTS Subquery

• Point 1: DROP TABLE IF EXISTS temp;
• Point 2: SELECT... FROM table name WHERE EXISTS (sub-query);

Subqueries have returned results: EXISTS subquery results are TRUE
Subqueries do not return results: EXISTS subquery results are FALSE, the outer query does not execute

EXISTS was used to test whether anyone had scored more than 80 points in the exam. If so, SELECT statements were used to rank the top five students according to their grades from high to low.

SELECT gradeId,score FROM grade WHERE EXISTS (SELECT * FROM grade WHERE score>80) ORDER BY score LIMIT 5

Using NOT EXISTS subquery

SELECT gradeId,score FROM grade WHERE NOT EXISTS (SELECT * FROM grade WHERE score>80) ORDER BY score LIMIT 5