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

JavaScript to achieve Taobao product image switching effect

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

JavaScript to achieve Taobao product image switching effect

How to modify the default network segment of Docker0 bridge in Docker

A brief discussion on the execution order of JavaScript macrotasks and microtasks

mysql 8.0.15 winx64 decompression version graphic installation tutorial

Install tomcat and deploy the website under Linux (recommended)

Solution to the problem that the border style of the <td></td> tag cannot be displayed in the browser

Basic tutorial on using explain statement in MySQL

The process of JDK installation and configuration of environment variables under WIN10 (detailed version)

How to connect to MySQL visualization tool Navicat

How to solve the problem of ping being successful but the port being unavailable in Linux

Recommend

Add crontab scheduled tasks to debian docker container

Zen coding for editplus example code description

A brief introduction to web2.0 products and functions

How to install common components (mysql, redis) in Docker

Analysis and redesign of music player apps (application software) How to design a beautiful music player interface

Summary of common problems in downloading and installing MySQL 5.7 on Win7 64-bit

MySQL query sorting and paging related

How to use css overflow: hidden (overflow hiding and clearing floats)

Directory permissions when creating a container with Docker

MySQL tutorial on how to deploy multiple instances on a single machine using mysqld_multi

Mysql join table and id auto-increment example analysis

How to set up a shared folder on a vmware16 virtual machine

How to reduce the memory and CPU usage of web pages

Nginx prohibits direct access via IP and redirects to a custom 500 page

Linux nohup to run programs in the background and view them (nohup and &)