MySQL Advanced Statement - Common Queries

Preface

(add, delete, change, check)
Queries to MySQL databases sometimes require processing of the result set of the query in addition to the basic query. Examples include taking only 10 pieces of data, sorting or grouping query results, and so on

1. Sort by keyword

PS: Analogue to windows Task Manager
You can use the SELECT statement to query the data you need from your MySQL database. If you sort the results of the query, you can use the ORDER BY statement to sort the statements and eventually return the sorted results to the user. This statement can be ordered not only for a single field, but also for multiple fields

#Syntax Format of ORDER BY
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... 

ASC|DESC;
ASC Is sorted in ascending order, is the default sort method, that is ASC It can be omitted. SELECT If no specific sort method is specified in the statement, the default is by ASC Sort by method.
DESC Is arranged in descending order. Of course ORDER BY You can also use it before WHERE Clauses further filter the query results.

##########Template##############################################
create database class;	//Create library class

#Create an info table that records the student's id, name, score, address, and hobbies
create table info (id int(10) primary key not null auto_increment,name varchar(20),score decimal(5,2),address varchar(40),hobby varchar(20));

#Insert new data
insert into info values(1,'liuyi',80,'beijing',2);
insert into info values(2,'wangwu',90,'shengzheng',2);
insert into info values(3,'lisi',60,'shanghai',4);
insert into info values(4,'tianqi',99,'hangzhou',5);
insert into info values(5,'jiaoshou',98,'laowo',3);
insert into info values(6,'hanmeimei',10,'nanjing',3);
insert into info values(7,'lilei',11,'nanjing',5);
insert into info values(8,'caicai',16,'nanjing',5);

select * from info;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)

1. Sort by single field

#Sort by score, asc is not specified by default
mysql> select id,name,score from info order by score;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  6 | hanmeimei | 10.00 |
|  7 | lilei     | 11.00 |
|  8 | caicai    | 16.00 |
|  3 | lisi      | 60.00 |
|  1 | liuyi     | 80.00 |
|  2 | wangwu    | 90.00 |
|  5 | jiaoshou  | 98.00 |
|  4 | tianqi    | 99.00 |
+----+-----------+-------+
8 rows in set (0.00 sec)

#Scores are sorted in descending order, using DESC
mysql> select id,name,score from info order by score desc;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  4 | tianqi    | 99.00 |
|  5 | jiaoshou  | 98.00 |
|  2 | wangwu    | 90.00 |
|  1 | liuyi     | 80.00 |
|  3 | lisi      | 60.00 |
|  8 | caicai    | 16.00 |
|  7 | lilei     | 11.00 |
|  6 | hanmeimei | 10.00 |
+----+-----------+-------+
8 rows in set (0.00 sec)

2. Conditional Query

#Order by also combines conditional filtering where students whose addresses are nanjing are sorted in descending order by score
mysql> select id,name,score from info where address='nanjing' order by score desc;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  8 | caicai    | 16.00 |
|  7 | lilei     | 11.00 |
|  6 | hanmeimei | 10.00 |
+----+-----------+-------+
3 rows in set (0.00 sec)

3. Multi-field Sorting

Principle:
After order by, use "," to split, with priority in order, for example:

mysql> select id,hobby,name from info order by hobby desc,id asc;
+----+-------+-----------+
| id | hobby | name      |
+----+-------+-----------+
|  4 | 5     | tianqi    |
|  7 | 5     | lilei     |
|  8 | 5     | caicai    |
|  3 | 4     | lisi      |
|  5 | 3     | jiaoshou  |
|  6 | 3     | hanmeimei |
|  1 | 2     | liuyi     |
|  2 | 2     | wangwu    |
+----+-------+-----------+
8 rows in set (0.00 sec)

The first parameter after order by makes sense only if the same value appears for the second field

4. Interval Judgment

AND And
or  or
#Find score s greater than 70 and less than or equal to 90 in the info table
select * from info where score >70 and score <=90;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
+----+--------+-------+------------+-------+
2 rows in set (0.00 sec)

#Find score s greater than 70 or less than or equal to 90 in the info table
select * from info where score>70 or score <=90;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 90.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)

Nested/Multiple Conditions

#Find score s greater than 70 or greater than 75 and less than 90
select * from info where score >70 or (score >75 and score <90);
+----+----------+-------+------------+-------+
| id | name     | score | address    | hobby |
+----+----------+-------+------------+-------+
|  1 | liuyi    | 80.00 | beijing    | 2     |
|  2 | wangwu   | 90.00 | shengzheng | 2     |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou | 98.00 | laowo      | 3     |
+----+----------+-------+------------+-------+
4 rows in set (0.00 sec)

5. Query does not duplicate records

distinct Query does not duplicate records
 grammar:
select distinct field from Table Name﹔

#Example:
select distinct hobby from info;
+-------+
| hobby |
+-------+
| 2     |
| 4     |
| 5     |
| 3     |
+-------+
4 rows in set (0.00 sec)

1.distinct Must be at the beginning
2.distinct You can only use fields that need to be weighted.
----That is to say, I sidtinct Yes name , hobby Two fields, I want to base on later id Sorting is not possible because only name,age Two fields to operate on．
3.distinct When multiple fields are removed, the meaning is:Several fields are filtered when they are repeated at the same time.

Grouping results

Results from SQL queries can also be grouped
Implemented using GROUP BY statements, GROUP BY is usually used in conjunction with aggregate functions
Common aggregation functions include Count (COUNT), Sum (SUM), AVG, MAX, MIN.
When grouping GROUP BY, results can be grouped by one or more fields.

#grammar
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;
selet Field, aggregate function from Table name,(where Field name (match) value)group by Field name;

#Example:
#Grouping info s with a filter range/condition of'name'where score is greater than or equal to 45. The same score is grouped in one group by default
select count(name),score from info where score >=45 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 | 60.00 |
|           1 | 80.00 |
|           1 | 90.00 |
|           1 | 98.00 |
|           1 | 99.00 |
+-------------+-------+
5 rows in set (0.00 sec)

case

#Group sorting:
#Tabulate the same number of IDs of interest (hobby) in the info table and group them according to the same hobby
select count(id),hobby from info group by hobby;
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
|         2 | 2     |
|         2 | 3     |
|         1 | 4     |
|         3 | 5     |
+-----------+-------+
4 rows in set (0.00 sec)


#Based on the previous operation, use order by to sort the number of IDS in descending order
select count(id),hobby from info group by hobby order by count(id) desc;
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
|         3 | 5     |
|         2 | 3     |
|         2 | 2     |
|         1 | 4     |
+-----------+-------+
4 rows in set (0.00 sec)



#Grouping Conditions###########################################################
Combination where Sentence, filter groups with scores greater than or equal to 80, count the number of students in descending order
select count(name),score from info where score >=80 group by score order by count(name) desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 | 99.00 |
|           1 | 98.00 |
|           1 | 80.00 |
|           1 | 90.00 |
+-------------+-------+
4 rows in set (0.00 sec)

3. Restriction of Result Entries

imit limits the result record of the output
When querying using the MySQL SELECT statement, the result set returns all matching records (rows).
Sometimes you just need to return the first line or the first few lines, then you need to use the LIMIT clause

#grammar
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

#The first parameter of LIMIT is the position offset (optional parameter), which sets the line from which MySQL starts to display. 
#If you do not set the first parameter, it will start with the first record in the table.
#Note that the first record has a position offset of 0, the second is 1, and so on.
#The second parameter is to set the maximum number of rows returned to the record.

#Query all information to display the first 3 rows of records
select * from info limit 3;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
|  3 | lisi   | 60.00 | shanghai   | 4     |
+----+--------+-------+------------+-------+
3 rows in set (0.00 sec)


#Start at line 4 and display 3 lines back
select * from info limit 3,3;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  4 | tianqi    | 99.00 | hangzhou | 5     |
|  5 | jiaoshou  | 98.00 | laowo    | 3     |
|  6 | hanmeimei | 10.00 | nanjing  | 3     |
+----+-----------+-------+----------+-------+
3 rows in set (0.00 sec)


#Combined with the order by statement, the first three lines are displayed in ascending order by id size
select * from info order by id limit 3;
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  1 | liuyi  | 80.00 | beijing    | 2     |
|  2 | wangwu | 90.00 | shengzheng | 2     |
|  3 | lisi   | 60.00 | shanghai   | 4     |
+----+--------+-------+------------+-------+
3 rows in set (0.00 sec)

#Combined with the order by statement, the last three lines are displayed in descending order by id size
select * from info order by id desc limit 3;
+----+-----------+-------+---------+-------+
| id | name      | score | address | hobby |
+----+-----------+-------+---------+-------+
|  8 | caicai    | 16.00 | nanjing | 5     |
|  7 | lilei     | 11.00 | nanjing | 5     |
|  6 | hanmeimei | 10.00 | nanjing | 3     |
+----+-----------+-------+---------+-------+
3 rows in set (0.00 sec)

4. Setting Alias (alias-)as)

When querying in MySQL, aliases can be set for field columns or tables to facilitate writing or to use the same table multiple times when the name of the table is longer or some fields in the table are longer. Use aliases directly when using them for simplicity and readability

#Grammar:
For column aliases: SELECT column_name AS alias_name FROM table_name;
For table aliases: SELECT column_name(s) FROM table_name AS alias_name;

in use AS After that, you can use the alias_name replace table_name，among AS Statement is optional. AS Later aliases, which provide temporary names for columns or tables within a table, are used during queries to keep the actual table or field names in the library intact

######################################################################
#Example column alias settings:
select name as Full name, score as achievement from info;
+-----------+--------+
| Full name      | achievement   |
+-----------+--------+
| liuyi     |  80.00 |
| wangwu    |  90.00 |
| lisi      |  60.00 |
| tianqi    |  99.00 |
| jiaoshou  |  98.00 |
| hanmeimei |  10.00 |
| lilei     |  11.00 |
| caicai    |  16.00 |
+-----------+--------+
8 rows in set (0.00 sec)


#If the table is long, you can use AS to alias it and use aliases directly during queries
#Temporarily set info's alias to i
select a.name as Full name, a.score as achievement from info a;
+-----------+--------+
| Full name      | achievement   |
+-----------+--------+
| liuyi     |  80.00 |
| wangwu    |  90.00 |
| lisi      |  60.00 |
| tianqi    |  99.00 |
| jiaoshou  |  98.00 |
| hanmeimei |  10.00 |
| lilei     |  11.00 |
| caicai    |  16.00 |
+-----------+--------+
8 rows in set (0.00 sec)i ;

#Query the number of fields in the info table to display as numbers
select count(*) as number from info;
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)


#You can display it without as
select count(*) number from info;
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)

#Count the number of info fields over time
select count(*) number from info where date '2021-01-01' and date '2021-10-28';
+----------+
|  number  |
+----------+
|        8 |
+----------+
1 row in set (0.01 sec)


#Use scenarios:
1,Aliases shorten the length of query statements when querying complex tables
2,When multiple tables are joined to query (easy to understand, shorten) sql Statement)

In addition, AS can be used as an operator for connection statements.

#Create a t1 table and insert all the query records of the info table into the t1 table
create table t1 as select * from info;

#Here AS plays a role:
1,A new table was created t1 And define the table structure to insert the table data (and info Same table)
2,However, the constraint is not fully replicated, but if the original table has a primary key set, the Schedule: default The field will default to a 0
#Similar: Clone, copy table structure
create table t1 (select * from info);

#You can also add where statements to judge
create table test1 as select * from info where score >=60;
 
When aliasing a table, make sure that the alias does not conflict with the names of other tables in the database.
Column aliases are displayed in the results, while table aliases are not displayed in the results and are used only when executing queries.

5. Wildcards

Wildcards are used to replace part of a character in a string, and the results are queried by matching part of the character.
Generally, wildcards are used with LIKE (Fuzzy Query) and in conjunction with the WHERE clause to complete the query task.
There are two common wildcards:
%: A percent sign means zero, one or more characters*
_: Underlines represent a single character

#Query name is a record beginning with c
select id,name from info where name like 'c%';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)


#A record with a character between c and i in the query name
select id,name from info where name like 'c_ic_i';
+----+--------+
| id | name   |
+----+--------+
|  8 | caicai |
+----+--------+
1 row in set (0.00 sec)


#Query for records with g in the middle of their names
select id,name from info where name like '%g%';
+----+--------+
| id | name   |
+----+--------+
|  2 | wangwu |
+----+--------+
1 row in set (0.00 sec)

#Query name records for the last 3 characters of hanmei
select id,name from info where name like 'hanmei___';
+----+-----------+
| id | name      |
+----+-----------+
|  6 | hanmeimei |
+----+-----------+
1 row in set (0.00 sec)

#Wildcards'%'and'' can be used not only individually but also in combination
 The name of the query is l Beginning Record
select id,name from info where name like 'l%';
+----+-------+
| id | name  |
+----+-------+
|  1 | liuyi |
|  3 | lisi  |
|  7 | lilei |
+----+-------+

6. Subqueries

Subqueries, also known as inline queries or nested queries, refer to queries in which another query statement is nested.
The subquery statement is executed before the main query statement, and the result is returned as an outer condition to the main query for next query filtering.
Substatements can be the same or different tables as the table queried by the main statement

#Same representation example:
select name,score from info where name in(select name from info where score >80);
+----------+-------+
| name     | score |
+----------+-------+
| wangwu   | 90.00 |
| tianqi   | 99.00 |
| jiaoshou | 98.00 |
+----------+-------+
3 rows in set (0.00 sec)
#Above
 Main statement: select name,score from info where id
 Substatement(aggregate):  select id from info where score >80
PS:In a clause sql Statement is to filter out a result set for the main statement's criteria
in: Associate the main table with the subtable/Syntax of Connection

#Different table/multiple table examples:
#Create table info2 and insert two days of new data
create table info2 (id int(4));
insert into infa2 values(1),(2),(3);

#multi-table query
select id,name,score from info where id in (select * from info2);
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | liuyi  | 80.00 |
|  2 | wangwu | 90.00 |
|  3 | lisi   | 60.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

Subqueries can be used not only in SELECT statements, but also in INSERT, UPDATE, DELETE. When nesting, new subqueries can also be nested inside subqueries, that is, they can be nested at multiple levels.
Subqueries can also be used in INSERT statements. The result set of a subquery can be inserted into other tables through an INSERT statement

#Delete all records in t1 and reinsert records in info table
insert into t1 select * from info where id in (select id from info);
select * from t1;                                                  


#UPDATE Statements can also use subqueries.####################################
#Subqueries within UPDATE can be a single column or multiple columns when set updates content. Note: Create
#Change the score with id=2 to 50
select * from info;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 50.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.01 sec)

update info set score=50 where id in (select * from info2 where id=2);
select * from info;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 50.00 | shengzheng | 2     |	//Change to 50
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  4 | tianqi    | 99.00 | hangzhou   | 5     |
|  5 | jiaoshou  | 98.00 | laowo      | 3     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
8 rows in set (0.01 sec)


#DELETE also applies to subqueries
#Delete records with scores greater than 80
delete from info where id in (select id where score>80);
select * from info;
+----+-----------+-------+------------+-------+
| id | name      | score | address    | hobby |
+----+-----------+-------+------------+-------+
|  1 | liuyi     | 80.00 | beijing    | 2     |
|  2 | wangwu    | 50.00 | shengzheng | 2     |
|  3 | lisi      | 60.00 | shanghai   | 4     |
|  6 | hanmeimei | 10.00 | nanjing    | 3     |
|  7 | lilei     | 11.00 | nanjing    | 5     |
|  8 | caicai    | 16.00 | nanjing    | 5     |
+----+-----------+-------+------------+-------+
6 rows in set (0.00 sec)
#More than 80 have been deleted

select id,name,score from t1;

#NOT can also be added before IN, which, in contrast to IN, denies (i.e., is not in the result set of a subquery)
#Delete records with scores not greater than or equal to 80
delete from t1 where id not in (select id where score>=80);
select id,name,score from t1;

1. Subquery-exists

Adding exists is only to determine whether the conditions after exists are valid. If they are valid, the matching of the main statement will be executed normally. If not, the query of the main statement will not be executed
PS: count is count, sum is sum, use sum to combine exists, output is null if subquery result set is unsuccessful

#Column Query Calculates the number of fields in info if there are records with a score equal to 80
select count(*) from info where exists(select id from info where score=80);


#Query calculates the number of fields in info if there are records with a score less than 50. Info tables are not less than 50, so 0 is returned
 select count(*) from info where exists(select id from info where score<50);

2. Subquery, alias as

#Query info table id,name field
select id,name from info;
#The above command can see the contents of the info table

#When querying a result set as a table, we also need to use aliases, for example:
#Requirement: From the content of the id and name fields in the info table as part of the output id of the Content
mysql> select id from (select id,name from info);
ERROR 1248 (42000): Every derived table must have its own alias

#An error will occur because:
select * from The table name is in standard format, and the above query statement,"Table Name"Location is actually a result set. mysql Instead of directly identifying it, set an alias to the result set to " select a.id from a"The method query will set this result set as one"surface"，You can query the data normally.
#The following:
select a.id from (select id,name from info) a;

#Amount to
select info.id,name from info;
select surface.Field, field from Table;

Keywords: Database MySQL SQL

Added by Drumminxx on Wed, 27 Oct 2021 20:37:51 +0300

Programming VIP