Detailed explanation of MySQL single table query operation examples [syntax, constraints, grouping, aggregation, filtering, sorting, etc.]

This article describes the MySQL single table query operation. Share with you for your reference, the details are as follows:

grammar

1. Single table query syntax

SELECT field1, field2... FROM tablename
WHERE Condition
GROUP BY field
HAVING Filter
ORDER BY field
LIMIT limit the number of entries

2. Keyword execution priority (key points)

The most important point: keyword execution priority

from
where
group by
having
select
distinct
order by
limit

1. Find the table: from

2. Take the constraints specified by where and retrieve the records one by one from the file/table

3. Group the retrieved records by group by. If there is no group by, the whole record is grouped as one.

4. Filter the grouped results by having

5. Execute select

6. Deduplication

7. Sort the results by condition: order by

8. Limit the number of results displayed

(1) where constraint

where operator

The where clause can be used
1. Comparison operators: >, <, >=, <=, <>, !=
2.between 80 and 100: the value is between 80 and 100
3.in(80,90,100) value is 10 or 20 or 30
4.like 'xiaomagepattern': pattern can be % or _. % represents any number of characters, _ represents one character
5. Logical operators: You can use logical operators and or not directly in multiple conditions

(2) Group by query

#1. First of all, it is clear that grouping occurs after where, that is, grouping is based on the records obtained after where.
#2. Grouping means: categorizing all records according to a common field, such as grouping by position in the employee information table, or grouping by gender, etc.
#3. Why do we need to group?
Take the highest salary in each department
Get the number of employees in each department
Take the number of men and the number of women
Tip: The field after the word 'each' is the basis for our grouping
#4. Major premise:
You can group by any field, but after grouping, for example, group by post, you can only view the post field. If you want to view the information within the group, you need to use the aggregate function

When executing the following SQL statement, no error is reported, but it is meaningless in itself

mysql> select * from employee group by post;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 14 | Zhang Ye | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪| female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | Ambassador of the Old Boy's Office in Shahe | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)

Set sql_mode to ONLY_FULL_GROUP_BY, exit, and then enter again for it to take effect.

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

Re-enter

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------

mysql> select * from emp group by post; // In the current case, it will report ERROR 1054 (42S22): Unknown column 'post' in 'group statement'
mysql> select * from employee group by post;
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select post from employee group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| Diplomatic Ambassador of Old Boys Office in Shahe |
+-----------------------------------------+
4 rows in set (0.00 sec)

Or use as follows

mysql> select name,post from employee group by post,name;
+------------+-----------------------------------------+
| name | post |
+------------+-----------------------------------------+
| Zhang Ye| operation |
| Cheng Yaojin | operation |
| Cheng Yaotie | operation |
| Cheng Yaotong | operation |
| Cheng Yaoyin | operation |
| Ding Ding | sale |
| Yaya | sale |
| Stars | sale |
| Gege | sale |
| Wai Wai | sale |
| alex | teacher |
| jingliyang | teacher |
| jinxin | teacher |
| liwenzhou | teacher |
| wupeiqi | teacher |
| xiaomage | teacher |
| yuanhao | teacher |
| egon | Diplomatic Ambassador of Old Boys Office in Shahe |
+------------+-----------------------------------------+
18 rows in set (0.00 sec)

mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post | count(id) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| Old Boys Diplomatic Ambassador to Shahe Office | 1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)

(3) Aggregation functions

max() finds the maximum value
min() finds the minimum value
avg() finds the average value
sum()
count() to find the total number

#Emphasis: The aggregation function aggregates the contents of the group. If there is no group, it defaults to one group. # How many employees are there in each department? select post, count(id) from employee group by post;
# The highest salary for each department select post,max(salary) from employee group by post;
# Minimum salary for each department select post,min(salary) from employee group by post;
# Average salary of each department select post,avg(salary) from employee group by post;
# All salaries for each department select post,sum(age) from employee group by post;

(4) HAVING filtering

The difference between HAVING and WHERE is

#! ! ! Execution priority from high to low: where > group by > having
#1. Where occurs before group by, so any field can be included in Where, but aggregate functions cannot be used.
#2. Having occurs after group by, so the grouped fields can be used in Having, and other fields cannot be directly obtained. Aggregate functions can be used

mysql> select * from employee where salary>1000000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

mysql> select * from employee having salary>1000000;
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause

# You must use group by to use the group_concat() function to concatenate all name valuesmysql> select post,group_concat(name) from emp group by post having salary > 10000; ##Error, the salary field cannot be directly retrieved after groupingERROR 1054 (42S22): Unknown column 'post' in 'field list'

practise

1. Query the position names where the number of employees in each position is less than 2, and the names and number of employees in each position
2. Query the job names and average salaries of all positions with an average salary greater than 10,000
3. Query the job names and average salaries of all positions whose average salary is greater than 10,000 and less than 20,000

Answer

mysql> select post,group_concat(name),count(id) from employee group by post;
+-----------------------------------------+----------------------------------------------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+----------------------------------------------------------+-----------+
| operation | Cheng Yaotie, Cheng Yaotong, Cheng Yaoyin, Cheng Yaojin, Zhang Ye | 5 |
| sale | Gege, Xingxing, Dingding, Yaya, Waiwai | 5 |
| teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | 7 |
| Old Boys Diplomatic Ambassador to Shahe Office | egon | 1 |
+-----------------------------------------+----------------------------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> select post,group_concat(name),count(id) from employee group by post having count(id)<2;
+-----------------------------------------+--------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+--------------------+-----------+
| Old Boys Diplomatic Ambassador to Shahe Office | egon | 1 |
+-----------------------------------------+--------------------+-----------+
1 row in set (0.00 sec)

#Question 2:
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)

#Question 3:
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
+-----------+--------------+
| post | avg(salary) |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
1 row in set (0.00 sec)

(5) order by query sort

Sort by single column

  SELECT * FROM employee ORDER BY age;
  SELECT * FROM employee ORDER BY age ASC;
  SELECT * FROM employee ORDER BY age DESC;

Sort by multiple columns: Sort by age in ascending order first, if the ages are the same, sort by id in descending order

  SELECT * from employee
    ORDER BY age ASC,
    id DESC;

(5) Limit limits the number of records queried:

Example:

  SELECT * FROM employee ORDER BY salary DESC
   LIMIT 3; #The default initial position is 0

  SELECT * FROM employee ORDER BY salary DESC
    LIMIT 0,5; #Start from number 0, that is, query the first item first, and then query the next 5 items including this one SELECT * FROM employee ORDER BY salary DESC
    LIMIT 5,5; #Start from the 5th item, that is, first find the 6th item, and then find the next 5 items including this one

Exercise: Display 5 items at a time

# Page 1 datamysql> select * from employee limit 0,5;
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | Ambassador of the Old Boy's Office in Shahe | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)
# Page 2 datamysql> select * from employee limit 5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪| female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | Yaya | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
# Page 3 datamysql> select * from employee limit 10,5;
+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| 11 | Ding Ding | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | Star | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格| female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | Zhang Ye | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | Cheng Yaojin | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
5 rows in set (0.00 sec)

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

SQL Aggregation, Grouping, and Sorting

<<: Vue implements a search box with a magnifying glass

>>: Detailed explanation of how to configure Nginx web server sample code

Vue3 Vue CLI multi-environment configuration

Detailed explanation of MySQL single table query operation examples [syntax, constraints, grouping, aggregation, filtering, sorting, etc.]

Vue3 Vue CLI multi-environment configuration

How to install Docker on Windows Server 2016

MySQL learning database operation DML detailed explanation for beginners

Summary of MySQL database like statement wildcard fuzzy query

Mysql queries the transactions being executed and how to wait for locks

Solve the problem of changing the password when logging in for the first time after installing MySQL 8.0

Nginx high concurrency optimization practice

6 solutions to IDEA's inability to connect to the MySQL database

Summary of how to use the MySQL authorization command grant

Vue template compilation details

Recommend

Comparison of the efficiency of different methods of deleting files in Linux

The image element img has extra blank space in IE6

Detailed description of the use of advanced configuration of Firewalld in Linux

Does MySql need to commit?

Rainbow button style made with CSS3

How to change mysql password under Centos

MySQL 5.7.21 winx64 installation and configuration method graphic tutorial under Windows 10

How to design the homepage of Tudou.com

Various ways to achieve the hollowing effect of CSS3 mask layer

Sequence implementation method based on MySQL

MySql 8.0.11 installation and configuration tutorial

What hidden attributes in the form can be submitted with the form

How to configure Nginx virtual host in CentOS 7.3

40 fonts recommended for famous website logos

Tutorial on installing mysql5.7.18 on windows10