Data Query Language (DQL)

Case Database Description

The database is named NSD2021 and has three tables
Departments table, with 8 departments

field	type	instructions
dept_id	int(4)	Unit no.
dept_name	varchar(20)	Department of

Table employees: A table of 133 employees in different departments

field	type	instructions
employee_id	int	Employee number
name	varchar()	The name
birth_date	date	birthday
hire_date	date	Date of entry
phone_number	char(11)	The phone number
email	varchar(30)	Email address
dept_id	int	Department No.

Salary scale: a salary scale that records salaries since 2015

field	type	instructions
id	int	The line Numbers
date	date	Date of pay
employee_id	int	Employee number
basic	int	Basic salary
bonus	int	bonus

The relationship between the three tables:
- The DEPARTMENTS table DEPARTMENTS has a foreign key constraint relationship with employees, and the DEPt_ID field of employees must appear in the DEPARTMENTS table
- There is a foreign key constraint between employees and salary table. The EMPLOYEe_ID of salary table must appear in employees table

SQL Statement Basics

MySQL > create MySQL

View all databases
mysql> SHOW DATABASES;
Switch the specified database
mysql> USE nsd2021;
View all tables in the current library
mysql> SHOW TABLES;
Check table structure
mysql> DESC departments;
Check the current database
mysql> SELECT DATABASE(a);# check the current login user
mysql> SELECT USER(a);# check version
mysql> SELECT VERSION(a); [root@localhost ~]# mysql --version
[root@localhost ~]# mysql -V
Copy the code

Grammar specification

It is not case sensitive, but it is recommended that the keyword be uppercase and the table name and column name be lowercase
Each commandThe bestEnd with a semicolon. Of course, you do\gYou can also end it
You can indent or wrap each command as required (it is better to use keywords on a single line), for example:

mysql> SELECT
    -> name, email
    -> FROM
    -> employees;
Copy the code

annotation

Single-line comments

mysql> # select * from departments
mysql> -- select * from departments
Copy the code

Multiline comment

mysql> /* /*> SELECT /*> * /*> FROM /*> departments; / * * / >basic | bonus
Copy the code

SQL Statement classification

Data Query Language (DQL)

A statement responsible for querying data without modifying the data itself. This is the most basic SQL statement.
Data Definition Language (DDL)

The language responsible for data structure definition and database object definition, consisting of CREATE, ALTER, and DROP syntax
Data Manipulation Language (DML)

The instruction set responsible for the operation of data access to database objects, with INSERT, UPDATE, DELETE three instructions as the core, respectively on behalf of INSERT, UPDATE and DELETE.
Data Control Language

It can control the control of a particular user account over database objects such as data tables, view tables, stored programs, user-defined functions, and so on. Consists of the GRANT and REVOKE directives.

Data query language DQL

Based on the query

SELECTList of fields to be queriedFROMTable;Copy the code

- The list of queried fields can be fields, constants, expressions, and functionsCopy the code

# search for a single field
mysql> select dept_name from departments;
Select * from * where *
mysql> select name, email from employees;
Select * from all fields
mysql> select * from departments;
# use expressions
mysql> select date, employee_id, basic+bonus from salary;
# query constant
mysql> select 100;
# query expression
mysql> select 10+5;
# query function
mysql> select version(a);Mysql > select * from rows where salary is generated
mysql> select count(*) from salary;
Copy the code

Alias names can be separated by Spaces or the keyword AS

mysql> selectDept_id Specifies the department ID. Dept_nameASDepartment offrom departments;
+--------------+-----------+| department number | | + department name--------------+-----------+| 1 | human resources | | 2 | department | | 3 | operations department | | | development | 4 5 test department | | | | | | Marketing Department 6 7 | sales | | | | | legal + 8--------------+-----------+
8 rows in set (0.00 sec)
Copy the code

duplicate removal

mysql> select dept_id from employees;
mysql> select distinct dept_id from employees;
Copy the code

Concat functions are used for string concatenation

mysql> select concat(name.The '-', phone_number) from employees;
Copy the code

Conditions of the query

SELECTList of fields to be queriedFROM 表 WHEREConditions;Copy the code

Conditional operators, similar to Python, are used
- >: more than
- <: less than
- =Is equal to:
- > =: Greater than or equal to
- < =: Less than or equal to
- ! =: is not equal to

mysql> select * from departments where dept_id>3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+4 | development | | | | 5 test department | | | | Marketing Department 6 7 | sales | | | | | legal + 8---------+-----------+
5 rows in set (0.00 sec)


mysql> select * from departments where dept_id<3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| | 1 hr | | 2 | | + the finance department---------+-----------+
2 rows in set (0.00 sec)


mysql> select * from departments where dept_id=3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| 3 | | + operations department---------+-----------+
1 row in set (0.01 sec)


mysql> select * from departments wheredept_id! =3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| 1 hr | | | | | finance department 2 4 | development | | | | 5 test department | | | | Marketing Department 6 7 | sales | | | | | legal + 8---------+-----------+
7 rows in set (0.00 sec)


mysql> select * from departments where dept_id>=3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| 3 | operations department | | | development | 4 5 test department | | | | | | Marketing Department 6 7 | sales | | | | | legal + 8---------+-----------+
6 rows in set (0.00 sec)


mysql> select * from departments where dept_id<=3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| | 1 hr | | | | finance department 2 | 3 | | + operations department---------+-----------+
3 rows in set (0.00 sec)
Copy the code

Logical operators, and (&), or (| |), not (!)

mysql> select * from departments where dept_id>1 and dept_id<5;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| | financial department | 2 | 3 | operations department | | | development | + 4---------+-----------+
3 rows in set (0.00 sec)


mysql> select * from departments where dept_id<3 or dept_id>6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| | 1 hr | | 2 | | finance department 7 | sales | | | | | legal + 8---------+-----------+
4 rows in set (0.00 sec)


mysql> select * from departments where not dept_id<=6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+7 | sales | | | | | legal + 8---------+-----------+
2 rows in set (0.00 sec)
Copy the code

Fuzzy query
- Like: contains
- Between XXX and YYy: indicates that the value is between XXX and YYy
- In: in the list
- Is null: null, equivalent to Python’s None
- Is not null: indicates that the value is not null

# % matches 0 to more than one arbitrary character
mysql> select name, email from employees where name like 'a %';
+-----------+--------------------------+
| name      | email                    |
+-----------+--------------------------+| xiu-yun zhang | [email protected] | | yu-ying zhang | [email protected] | | zhang | [email protected] | | Bryan | [email protected] | | gui-xiang zhang | [email protected] | | get | [email protected] | | altieri dc | [email protected] | | xiu-lan zhang | [email protected] | +-----------+--------------------------+
8 rows in set (0.00 sec)


# _ matches a character
mysql> select name, email from employees where name like 'a _';
+--------+----------------------+
| name   | email                |
+--------+----------------------+| zhang | [email protected] | | Bryan | [email protected] | | get | [email protected] | +--------+----------------------+
3 rows in set (0.00 sec)


mysql> select name, email from employees where name like 'a __';
+-----------+--------------------------+
| name      | email                    |
+-----------+--------------------------+| xiu-yun zhang | [email protected] | | yu-ying zhang | [email protected] | | gui-xiang zhang | [email protected] | | altieri dc | [email protected] | | xiu-lan zhang | [email protected] | +-----------+--------------------------+
5 rows in set (0.00 sec)


mysql> select * from departments where dept_id between 3 and 5;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| | 3 | operations department 4 | development | | | | | testing department + 5---------+-----------+
3 rows in set (0.00 sec)


mysql> select * from departments where dept_id in (1.3.5.8);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| 1 | human resources | | 3 | | operations department 5 test department | | | | | | legal + 8---------+-----------+
4 rows in set (0.00 sec)


# match records with an empty department name
mysql> select * from departments where dept_name is null;
Empty set (0.00 sec)


Select * from department where department name is not empty
mysql> select * from departments where dept_name is not null;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| 1 | human resources | | 2 | department | | 3 | operations department | | | development | 4 5 test department | | | | | | Marketing Department 6 7 | sales | | | | | legal + 8---------+-----------+
8 rows in set (0.00 sec)
Copy the code

The sorting

SELECTList of fields to be queriedFROM 表 ORDER BYSorted list [asc|desc];
Copy the code

Sort: default ascending order

mysql> select name, birth_date from employees where birth_date>'19980101';
+-----------+------------+
| name      | birth_date |
+-----------+------------+| Yao Lin | 1998-05-20 | | Wu Xue | 1998-06-13 | | BoGang | 2000-05-17 | | yu-ying zhang | | 1998-06-22 | | 1998-10-27 | liu qian | ShenFeng | | 1999-01-13 | Chen yong | 1998-02-04 | | xiu-yun li | 1999-09-08 | | altieri dc | 1999-05-31 | | zhao | 1998-03-06 | | MengMei | 2000-09-01 | | Chen huan | 1998-07-01 | | Ma Lei | 2000-08-07 | | xiu-mei zhao | | + 1998-09-25-----------+------------+
14 rows in set (0.00 sec)

# default ascending order
mysql> select name, birth_date from employees where birth_date>'19980101' order by birth_date;
+-----------+------------+
| name      | birth_date |
+-----------+------------+| Chen yong | 1998-02-04 | | zhao | 1998-03-06 | | Yao Lin | 1998-05-20 | | Wu Xue | 1998-06-13 | | yu-ying zhang | 1998-06-22 | | Chen huan | | 1998-07-01 | xiu-mei zhao | | 1998-09-25 | | 1998-10-27 | liu qian | ShenFeng | 1999-01-13 | | altieri dc | 1999-05-31 | | xiu-yun li | 1999-09-08 | | BoGang | The 2000-05-17 | | Ma Lei | 2000-08-07 | | MengMei | | + 2000-09-01-----------+------------+
14 rows in set (0.00 sec)

# descending order
mysql> select name, birth_date from employees where birth_date>'19980101' order by birth_date desc;
+-----------+------------+
| name      | birth_date |
+-----------+------------+| MengMei | 2000-09-01 | | Ma Lei | 2000-08-07 | | BoGang | 2000-05-17 | | xiu-yun li | 1999-09-08 | | altieri dc | 1999-05-31 | | ShenFeng | 1999-01-13 | | | | 1998-10-27 liu qian | xiu-mei zhao | 1998-09-25 | | Chen huan | 1998-07-01 | | yu-ying zhang | 1998-06-22 | | Wu Xue | 1998-06-13 | | Yao Lin | The 1998-05-20 | | zhao | 1998-03-06 | | Chen yong | | + 1998-02-04-----------+------------+
14 rows in set (0.00 sec)


# Query employee's salary on January 10, 2015
mysql> select date, employee_id, basic, bonus from salary where date='20150110';

# query employee salary on January 10, 2015 in descending order by basic salary; If the base salary is the same, they are listed in ascending order of bonus
mysql> select date, employee_id, basic, bonus from salary where date='20150110' order by basic desc, bonus;

Select * from employee where date: January 10, 2015 select * from employee where date: January 10, 2015
mysql> select date, employee_id, basic, bonus, basic+bonus as total from salary where date='20150110' order by total;
Copy the code

Commonly used functions

classification

According to the mode of use:
- A single function
- The grouping function
Divided into:
- Character function
- Mathematical function
- Date function
- Flow control function
Usage:

SELECTFunction (parameter)FROMTable;Copy the code

Function application

Character function examples:

LENGTH(STR) : indicates the LENGTH of the returned string, in bytes

mysql> select length('abc');
+---------------+
| length('abc') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)


mysql> select length('hello');
+------------------+| | length (' hello ') +------------------+| | + 6------------------+
1 row in set (0.00 sec)


mysql> select name, email, length(email) from employees where name='li ping';
+--------+----------------+---------------+
| name   | email          | length(email) |
+--------+----------------+---------------+| | li ping [email protected] | | + 14--------+----------------+---------------+
1 row in set (0.00 sec)
Copy the code

CHAR_LENGTH(STR): Returns the length of a string, in characters

mysql> select char_length('abc');
+--------------------+
| char_length('abc') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)


mysql> select char_length('hello');
+-----------------------+| | char_length (' hello ') +-----------------------+| | + 2-----------------------+
1 row in set (0.00 sec)
Copy the code

CONCAT (s1, s2,…). : Returns the string produced by the concatenation argument, one or more of the contents to be concatenated, NULL if any of them is NULL

# concatenate string
mysql> select concat(dept_id, The '-', dept_name) from departments;
+---------------------------------+
| concat(dept_id, '-', dept_name) |
+---------------------------------+1 - human resources | | | - finance department | 2 | 3 | - operations department | 4 - department | | 5 - test department | | 6 - marketing | | sales | 7-8 - justice | | +---------------------------------+
8 rows in set (0.00 sec)
Copy the code

UPPER(STR) and UCASE(STR): Converts all letters in the string to uppercase

mysql> select name.upper(email) from employees where name like 'l %';
+-----------+----------------------+
| name      | upper(email)         |
+-----------+----------------------+Exactly the | | [email protected] | | li ping | [email protected] | | colton | [email protected] | | unpaid | [email protected] | | Li Liu | [email protected] | | li hui | [email protected] | | li jing | [email protected] | | fiere | [email protected] | +-----------+----------------------+
8 rows in set (0.00 sec)
Copy the code

LOWER(STR) and LCASE(STR): Convert all letters in STR to lowercase

# to lowercase
mysql> select lower('HelloWorld');
+---------------------+
| lower('HelloWorld') |
+---------------------+
| helloworld          |
+---------------------+
1 row in set (0.00 sec)
Copy the code

SUBSTR(s, start, length): Extract SUBSTR(s, start, length) from the start position of the substring s, counting from 1

mysql> select substr('hello world'.7);
+--------------------------+
| substr('hello world', 7) |
+--------------------------+
| world                    |
+--------------------------+
1 row in set (0.00 sec)


Select 3 substrings starting at 7
mysql> select substr('hello world'.7.3);
+-----------------------------+
| substr('hello world', 7, 3) |
+-----------------------------+
| wor                         |
+-----------------------------+
1 row in set (0.00 sec)
Copy the code

INSTR(STR,str1) : Returns the position of the str1 argument within the STR argument

# Position of a substring in a string
mysql> select instr('hello world'.'or');
+----------------------------+
| instr('hello world', 'or') |
+----------------------------+| | + 8----------------------------+
1 row in set (0.00 sec)


mysql> select instr('hello world'.'ol');
+----------------------------+
| instr('hello world', 'ol') |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)
Copy the code

TRIM(s): Returns string s with whitespace removed

mysql> select trim(' hello world. ');
+--------------------------+
| trim('  hello world.  ') |
+--------------------------+
| hello world.             |
+--------------------------+
1 row in set (0.00 sec)
Copy the code

Example of mathematical function

ABS(x) : Returns the absolute value of x

mysql> select abs(- 10);
+----------+
| abs(-10) |
+----------+| | + 10----------+
1 row in set (0.00 sec)
Copy the code

PI(): Returns the value of PI, which displays six decimal digits by default

mysql> select pi(a); +----------+
| pi()     |
+----------+| | + 3.141593----------+
1 row in set (0.00 sec)
Copy the code

MOD(x,y): Returns the remainder of x divided by y

mysql> select mod(10.3);
+------------+
| mod(10, 3) |
+------------+| | + 1------------+
1 row in set (0.00 sec)
Copy the code

CEIL(x), CEILING(x): returns the smallest integer not less than x

mysql> select ceil(10.1);
+------------+| | ceil (10.1) +------------+| | + 11------------+
1 row in set (0.00 sec)
Copy the code

FLOOR(x): Returns the largest integer not greater than x

mysql> select floor(10.9);
+-------------+| | floor (10.9) +-------------+| | + 10-------------+
1 row in set (0.00 sec)
Copy the code

ROUND(x) and ROUND(x,y): the former returns the integer closest to x, that is, x is rounded. The latter returns the number closest to x, reserved to the y place behind the decimal point, or to the y place to the left of the decimal point if y is negative

mysql> select round(10.6666);
+----------------+| | round (10.6666) +----------------+| | + 11----------------+
1 row in set (0.00 sec)


mysql> select round(10.6666.2);
+-------------------+| | round (10.6666, 2) +-------------------+| | + 10.67-------------------+
1 row in set (0.00 sec)
Copy the code

Date and time function instances

CURDATE(), CURRENT_DATE(): Returns the current date in either “YYYY-MM-DD” or “YYYYMMDD” format, depending on the string or number context in which the function is used

mysql> select curdate(a); +------------+
| curdate()  |
+------------+| | + 2021-03-09------------+
1 row in set (0.00 sec)


mysql> select curdate() + 0;
+---------------+
| curdate() + 0 |
+---------------+| | + 20210309---------------+
1 row in set (0.00 sec)
Copy the code

NOW(): Returns the current date and time in the format of “YYYY_MM-DD HH:MM:SS” or “YYYYMMDDHHMMSS”, depending on the string or number context in which the function is used

mysql> select now(a); +---------------------+
| now()               |
+---------------------+02:28:26 | 2021-03-09 | +---------------------+
1 row in set (0.00 sec)


mysql> select now() + 0;
+----------------+
| now() + 0      |
+----------------+| | + 20210309022848----------------+
1 row in set (0.00 sec)
Copy the code

UNIX_TIMESTAMP() and UNIX_TIMESTAMP(date): the former returns the number of seconds from 1970-01-01 00:00:00 to the present, while the latter returns the number of seconds from 1970-01-01 00:00:00 to the specified time

mysql> select unix_timestamp(a); +------------------+
| unix_timestamp() |
+------------------+| | + 1615275274------------------+
1 row in set (0.00 sec)
Copy the code

FROM_UNIXTIME(date): The inverse function of UNIX_TIMESTAMP converts the UNIX timestamp to a normal format

mysql> select from_unixtime(0);
+---------------------+
| from_unixtime(0)    |
+---------------------+19:00:00 | 1969-12-31 | +---------------------+
1 row in set (0.00 sec)
Copy the code

MONTH(date) and MONTHNAME(date): The former returns the MONTH of a specified date, and the latter returns the name of a MONTH of a specified date

mysql> select month('20211001120000');
+-------------------------+
| month('20211001120000') |
+-------------------------+| | + 10-------------------------+
1 row in set (0.00 sec)


mysql> select monthname('20211001120000');
+-----------------------------+
| monthname('20211001120000') |
+-----------------------------+
| October                     |
+-----------------------------+
1 row in set (0.00 sec)
Copy the code

DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d): DAYNAME(d) Returns the English name of the working day corresponding to D, such as Sunday and Monday. DAYOFWEEK(d) Indicates the index of a week. 1 indicates Sunday and 2 indicates Monday. WEEKDAY(d) indicates the index of working days corresponding to D. 0 indicates Monday and 1 indicates Tuesday

mysql> select dayname('20211001120000');
+---------------------------+
| dayname('20211001120000') |
+---------------------------+
| Friday                    |
+---------------------------+
1 row in set (0.00 sec)


mysql> select dayname('20211001');
+---------------------+
| dayname('20211001') |
+---------------------+
| Friday              |
+---------------------+
1 row in set (0.00 sec)
Copy the code

WEEK(d): calculates the number of weeks in a year

mysql> select week('20211001');
+------------------+
| week('20211001') |
+------------------+| | + 39------------------+
1 row in set (0.00 sec)
Copy the code

DAYOFYEAR(d) and DAYOFMONTH(d) : the former returns D as the day of the year, while the latter returns D as the day of the month

mysql> select dayofyear('20211001');
+-----------------------+
| dayofyear('20211001') |
+-----------------------+| | + 274-----------------------+
1 row in set (0.00 sec)
Copy the code

YEAR(date), QUARTER(date), MINUTE(time), SECOND(time): YEAR(date) Returns the YEAR corresponding to the specified date. The value ranges from 1970 to 2069. QUARTER(date) Returns date corresponding to the QUARTER of a year, ranging from 1 to 4. MINUTE(time) Returns the number of minutes corresponding to time. The value ranges from 0 to 59. SECOND(time) Returns the SECOND value of the specified time

mysql> select year('20211001');
+------------------+
| year('20211001') |
+------------------+| | + 2021------------------+
1 row in set (0.00 sec)


mysql> select quarter('20211001');
+---------------------+
| quarter('20211001') |
+---------------------+| | + 4---------------------+
1 row in set (0.00 sec)
Copy the code

Example of a process control function

IF(expr,v1,v2): return v1 IF expr is TRUE, otherwise return v2

mysql> select if(3>0.'yes'.'no');
+----------------------+
| if(3>0, 'yes', 'no') |
+----------------------+
| yes                  |
+----------------------+
1 row in set (0.00 sec)



mysql> select name, dept_id, if(dept_id=1.'Personnel Department'.'Not Personnel')  from employees where name='zhang';
+--------+---------+--------------------------------------------+| name | dept_id | the if (dept_id = 1, 'human resources',' the ministry of personnel) | +--------+---------+--------------------------------------------+7 | | liang zhang | human resources | +--------+---------+--------------------------------------------+
1 row in set (0.00 sec)
Copy the code

IFNULL(v1,v2): if v1 is not NULL, return v1, otherwise return v2

mysql> select dept_id, dept_name, ifnull(dept_name, 'Not set') from departments;
+---------+-----------+--------------------------------+| dept_id | dept_name | ifnull (dept_name, 'not set') | +---------+-----------+--------------------------------+| | 1 hr hr | | | | 2 | finance department financial department | | 3 | | operations department operations department | | 4 | development | development | | | | | 5 testing department test department marketing | | | | 6 Marketing Department | | sales | 7 The sales department | | | | | legal legal + 8---------+-----------+--------------------------------+
8 rows in set (0.00 sec)


mysql> insert into departments(dept_id) values(9);
mysql> select dept_id, dept_name, ifnull(dept_name, 'Not set') from departments; 
+---------+-----------+--------------------------------+| dept_id | dept_name | ifnull (dept_name, 'not set') | +---------+-----------+--------------------------------+| | 1 hr hr | | | | 2 | finance department financial department | | 3 | | operations department operations department | | 4 | development | development | | | | | 5 testing department test department marketing | | | | 6 Marketing Department | | sales | 7 Sales department | | | | 8 legal justice | | | NULL | 9 not set | +---------+-----------+--------------------------------+
9 rows in set (0.00 sec)
Copy the code

CASE expr WHEN v1 THEN R1 [WHEN v2 THEN v2] [ELSE RN] END: if vn = v1 THEN r1 [WHEN v2 THEN v2] [ELSE RN] END: if vn = v1 THEN R1 [WHEN v2 THEN V2

mysql> select dept_id, dept_name,
    -> case dept_name
    -> when 'Operations' then 'Technical Department'
    -> when 'Development Department' then 'Technical Department'
    -> when 'Test Department' then 'Technical Department'
    -> when null then 'Not set'
    -> else 'Non-technical sector'
    -> end as 'Type of Department'
    -> from departments;
+---------+-----------+-----------------+| dept_id | dept_name | | + department type---------+-----------+-----------------+| 1 | | non-technical personnel department department | | 2 | | finance department technical department | | 3 | | operations department technical department | | | development | 4 technical department | | | | 5 test department technical department | | | 6 marketing | | non-technical department | 7 | | sales | non-technical department | | | | 8 legal non-technical department | | NULL | | non-technical department + 9---------+-----------+-----------------+
9 rows in set (0.00 sec)


mysql> select dept_id, dept_name,
    -> case 
    -> when dept_name='Operations' then 'Technical Department'
    -> when dept_name='Development Department' then 'Technical Department'
    -> when dept_name='Test Department' then 'Technical Department'
    -> when dept_name is null then 'Not set'
    -> else 'Non-technical sector'
    -> end as 'Type of Department'
    -> from departments;
+---------+-----------+-----------------+| dept_id | dept_name | | + department type---------+-----------+-----------------+| 1 | | non-technical personnel department department | | 2 | | finance department technical department | | 3 | | operations department technical department | | | development | 4 technical department | | | | 5 test department technical department | | | 6 marketing | | non-technical department | 7 | | sales | non-technical department | | | | 8 legal non-technical department | | NULL set | | not + 9---------+-----------+-----------------+
9 rows in set (0.00 sec)
Copy the code

The grouping function

Used for statistics, also known as aggregate functions or statistical functions

The sum () : the sum

mysql> select employee_id, sum(basic+bonus) from salary where employee_id=10 and year(date) =2018;
+-------------+------------------+
| employee_id | sum(basic+bonus) |
+-------------+------------------+| | 116389 | + 10-------------+------------------+
1 row in set (0.00 sec)
Copy the code

Avg () : calculate the average value

mysql> select employee_id, avg(basic+bonus) from salary where employee_id=10 and year(date) =2018;
+-------------+------------------+
| employee_id | avg(basic+bonus) |
+-------------+------------------+| | | 29097.2500 + 10-------------+------------------+
1 row in set (0.00 sec)
Copy the code

Max () : Finds the maximum value

mysql> select employee_id, max(basic+bonus) from salary where employee_id=10 and year(date) =2018;
+-------------+------------------+
| employee_id | max(basic+bonus) |
+-------------+------------------+| | 31837 | + 10-------------+------------------+
1 row in set (0.00 sec)
Copy the code

Min () : find the minimum value

mysql> select employee_id, min(basic+bonus) from salary where employee_id=10 and year(date) =2018;
+-------------+------------------+
| employee_id | min(basic+bonus) |
+-------------+------------------+| | 24837 | + 10-------------+------------------+
1 row in set (0.00 sec)
Copy the code

Count () : Counts the number

mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+| | + 9----------+
1 row in set (0.00 sec)
Copy the code

Grouping query

When making statistics on data in a data table, you may need to make statistics separately according to certain categories. For example, query the number of employees in each department.
Use GROUP BY to GROUP the values of a field or multiple fields

Syntax format

The list of queries must be grouping functions and fields that appear after GROUP BY
Typically, pre-group data filtering is placed in the WHERE clause, and post-group data filtering is placed in the HAVING clause

SELECTThe field name1Request to appear atgroup byAfter), grouping function (),......FROMThe name of the tableWHEREconditionsGROUP BYThe field name1The field name2
HAVINGFilter conditionsORDER BYField;Copy the code

Examples of application

Query the number of people in each department

mysql> select dept_id, count(*) from employees group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+8 | | 1 | | 2 | 5 | | 3 | | 6 4 55 | | | | 5 12 | | | | | 9 6 7 35 | | | | | 3 | + 8---------+----------+
8 rows in set (0.00 sec)
Copy the code

Query the oldest employee in each department

mysql> select dept_id, min(birth_date) from employees group by dept_id;
+---------+-----------------+
| dept_id | min(birth_date) |
+---------+-----------------+| 1 | | 1971-08-19 | | 1971-11-02 | 2 | 3 | | 1971-09-09 | | 1972-01-31 | 4 5 | | 1971-08-14 | | 1973-04-14 | | 6 7 | | The 1971-12-10 | | | | 1989-05-19 + 8---------+-----------------+
8 rows in set (0.00 sec)
Copy the code

Query the entry time of the latest employee in each department

mysql> select dept_id, max(hire_date) from employees group by dept_id;
+---------+----------------+
| dept_id | max(hire_date) |
+---------+----------------+| 1 | | 2018-11-21 | | 2018-09-03 | 2 | 3 | | 2019-07-04 | | 2021-02-04 | 4 5 | | 2019-06-08 | | 2017-10-07 | | 6 7 | | The 2020-08-21 | | | | 2019-11-14 + 8---------+----------------+
8 rows in set (0.00 sec)
Copy the code

Count the number of employees using tedu.cn email in each department

mysql> select dept_id, count(*) from employees where email like '%@tedu.cn' group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+5 | | 1 | | 2 | 2 | 3 | | | 4 4 32 | | | | 5 7 | | | | | 5 6 7 15 | | | | | | 1 + 8---------+----------+
8 rows in set (0.00 sec)
Copy the code

View the total salary of employees in 2018, in descending order of total income

mysql> select employee_id, sum(basic+bonus) as total from salary where year(date) =2018 group by employee_id order by total desc;
Copy the code

The number of inquiry department is less than 10

mysql> select dept_id, count(*) from employees where count(*) <10 group by dept_id;
ERROR 1111 (HY000): Invalid use of group function


mysql> select dept_id, count(*) from employees group by dept_id having count(*) <10;
+---------+----------+
| dept_id | count(*) |
+---------+----------+8 | | 1 | | 2 | 5 | | 3 | 6 | | | 6 to 9 | | | 3 | + 8---------+----------+
5 rows in set (0.00 sec)
Copy the code

Join queries

Also called multi-table query. Often used to query fields from multiple tables
If you query the two tables directly, you get the Cartesian product

mysql> select name, dept_name from employees, departments;
Copy the code

You can qualify the query results by adding valid conditions

mysql> select name, dept_name from employees, departments where employees.dept_id=departments.dept_id;
Copy the code

Connect the classification

Classification by function

The connection (important)
- Contour connection
- Unequal connection
- Since the connection
Outer join
- Left external connection (critical)
- Right outer connection (critical)
- Full external connection (mysql does not support this, you can use UNION to achieve the same effect)
Cross connection

Chronologically

SQL92 standard: only internal connection is supported
SQL99 standard: support the connection of the function

SQL99 standard multi-table query

Syntax format

SELECTField...FROM 表1 [AS] alias [connection type]JOIN 表2 [AS] the aliasONJoin conditionWHEREPre-grouping screening criteriaGROUP BYgroupingHAVINGFilter conditions after groupingORDER BYSort fieldCopy the code

In the connection

Syntax format

selectQuery listfrom 表1The aliasinner join 表2The aliasonJoin conditioninner join 表3The aliasonConnection conditions [whereScreening criteria] [group byGrouping] [havingGroup filtering] [order bySorted list]Copy the code

Contour connection

Example Query the department name of each employee

mysql> select name, dept_name
    -> from employees
    -> inner join departments
    -> on employees.dept_id=departments.dept_id;
Copy the code

Query the department name of each employee using an alias

mysql> select name, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;
Copy the code

Query the department name of each employee using an alias. Fields in two tables with the same name must be named

mysql> select name, d.dept_id, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;
Copy the code

Query employee no. 11’s name and monthly salary in 2018

mysql> select name.date, basic+bonus as total
    -> from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018 and e.employee_id=11;
Copy the code

Query the total salary per employee in 2018

mysql> select name.sum(basic+bonus) from employees
    -> inner join salary
    -> on employees.employee_id=salary.employee_id
    -> where year(salary.date)=2018
    -> group by name;
Copy the code

Query the total salary of each employee in 2018, in ascending salary order

mysql> select name.sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> order by total;
Copy the code

Query employees whose total salary is greater than 300,000 in 2018 in descending order of salary

 mysql> select name.sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> having total>300000
    -> order by total desc;
Copy the code

Unequal connection

Side note: Create a salary scale

Create table expressions:
CREATE TABLETable name (column name1Data type, column name2Data type, column name3Data type,....)Copy the code
Create salary scale table:

Id: primary key. Used only as the row number of the table

Grade: salary level, ABCDE 5

Low: indicates the lowest wage of this level

“High” : the highest salary of this level
mysql> use nsd2021;
mysql> create table wage_grade
    -> (
    -> id int,
    -> grade char(1), - >low int- >high int,
    -> primary key (id));
Copy the code
Insert data into table;

Grammar:
INSERT INTOThe name of the tableVALUES(value1And the value2,...). ;Copy the code
Insert five rows into WAGe_grade table:
mysql> insert into wage_grade values- > (1.'A'.5000.8000),
    -> (2.'B'.8001.10000),
    -> (3.'C'.10001.15000),
    -> (4.'D'.15001.20000),
    -> (5.'E'.20001.1000000);
Copy the code

Query the basic salary range of employees in December 2018

mysql> select employee_id, date, basic, grade
    -> from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date) =2018 and month(date) =12;
Copy the code

Query the number of employees in each basic salary band in December 2018

mysql> select grade, count(*) - >from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date) =2018 and month(date) =12
    -> group by grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| A     |       13 |
| B     |       12 |
| C     |       30 |
| D     |       32 |
| E     |       33 |
+-------+----------+
5 rows in set (0.00 sec)
Copy the code

To query the basic salary range of the employee in December 2018, the employee needs to show his/her name

mysql> select name.date, basic, grade
    -> from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date) =2018 and month(date) =12;
Copy the code

Since the connection

Key points:
- Use one table as two
- Give each table an alias
See which employees have the same birthday month as their starting month

mysql> select e.name, e.hire_date, em.birth_date
    -> from employees as e
    -> inner join employees as em
    -> on month(e.hire_date)=month(em.birth_date)
    -> and e.employee_id=em.employee_id;
+-----------+------------+------------+
| name      | hire_date  | birth_date |
+-----------+------------+------------+Exactly the | | 2012-01-19 | 2012-01-19 | | Zheng Jing | 2018-02-03 | 2018-02-03 | | Lin Gang | 2007-09-19 | 2007-09-19 | | gui-lan liu | | 2003-10-14 The 1982-10-11 | | liang zhang | 2015-08-10 | 2015-08-10 | | Xu Xin | 2011-09-09 | 2011-09-09 | | Mr Wang | 2019-11-14 | 2019-11-14 | +-----------+------------+------------+
7 rows in set (0.00 sec)
Copy the code

Outer join

Often used to query for records that are present in one table but not in another
If there is a match from the table, the matched value is displayed
If there is no match from the table, NULL is displayed
External join query result = internal join query result + records in the primary table but not in the secondary table
In the left outer join, the left join is the main table
In the right outer join, the main table is to the right of the right join
Left outer join and right outer join are interchangeable to achieve the same goal

The left outer join

grammar

SELECTTb1. The field... , tb2. FieldsFROM table1 AS tb1
LEFT OUTER JOIN table2 AS tb2 
ONTb1. Field = TB2. FieldCopy the code

Query the personnel of all departments and departments without employees

mysql> select d.*, e.name
    -> from departments as d
    -> left outer join employees as e
    -> on d.dept_id=e.dept_id;
Copy the code

Right connection

grammar

SELECTTb1. The field... , tb2. FieldsFROM table1 AS tb1
RIGHT OUTER JOIN table2 AS tb2 
ONTb1. Field = TB2. FieldCopy the code

Query the personnel of all departments and departments without employees

mysql> select d.*, e.name
    -> from employees as e
    -> right outer join departments as d
    -> on d.dept_id=e.dept_id;
Copy the code

Cross connection

Return cartesian product
Grammar:

SELECT< field name >FROM< table1> CROSS JOIN< table2> [WHEREClause]Copy the code

Query the Cartesian product of the employee table and department table

mysql> select name, dept_name
    -> from employees
    -> cross join departments;
Copy the code

Add: grant administrator root access to the database at any address with password [email protected]. By default, root is only accessible on the host
mysql> grant all on*. *to root@The '%' identified by '[email protected]';
Copy the code
Insert data into department table:
mysql> insert into departments(dept_name) values('Purchasing Department');

Copy the code

The subquery

Subquery refers to a complete query statement, nested a number of small queries with different functions, so as to complete a complex query writing form

Classification of data returned by subqueries

Single row single column: Returns the contents of a specific column, which can be understood as a single value of data
Single row multiple columns: Returns the contents of multiple columns in a single row of data
Multi-row single-column: Returns the contents of the same column in a multi-row record, giving a range of operations
Multi-row, multi-column: The query returns a temporary table

The location where subqueries occur frequently

After select: Supports only one row and one column
After from: Supports multiple rows and columns
Where or after HAVING: Supports single row single column, single row multiple columns, and multiple rows single column

Subquery instance

A single line

Example Query information about all employees in the OPERATION and Maintenance department

Analysis:

First get the OPERATIONS and maintenance number from the departments table

mysql> select dept_id from departments where dept_name='Operations';
+---------+
| dept_id |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
Copy the code

Then look for the employees whose numbers are the same as those of the OPERATION and Maintenance department in the employees table

mysql> select* - >from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='Operations'
    -> );
Copy the code

Query all salary information higher than employee no. 100’s base salary in December 2018

Analysis:

First check the basic salary of the employee on December 100, 2018

mysql> select basic from salary
    -> where year(date) =2018 and month(date) =12 and employee_id=100;
+-------+
| basic |
+-------+| | + 14585-------+
1 row in set (0.00 sec)
Copy the code

Query all salary information higher than employee no. 100’s base salary in December 2018

mysql> select * from salary
    -> where year(date) =2018 and month(date) =12 and basic>(
    ->   select basic from salary
    ->   where year(date) =2018 and month(date) =12 and employee_id=100
    -> );
Copy the code

Query departments with fewer employees than development

Analysis:

Query the department number of the development department

mysql> select dept_id from departments where dept_name='Development Department';
+---------+
| dept_id |
+---------+| | + 4---------+
1 row in set (0.00 sec)
Copy the code

Query the number of people in development department

mysql> select count(*) from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='Development Department'
    -> );
+----------+
| count(*) |
+----------+| | + 55----------+
1 row in set (0.00 sec)
Copy the code

Query the number of people in each department by group

mysql> select count(*), dept_id from employees group by dept_id;
+----------+---------+
| count(*) | dept_id |
+----------+---------+8 | | 1 | | 5 | 2 | | | 3 | 6 55 4 | | | | | | 5 12 9 6 | | | | | 35 7 | | 3 | | + 8----------+---------+
8 rows in set (0.01 sec)
Copy the code

Query departments with fewer employees than development

mysql> select count(*), dept_id from employees group by dept_id
    -> having count(*) < (- >select count(*) from employees
    ->   where dept_id=(
    ->     select dept_id from departments where dept_name='Development Department'
    ->   )
    -> );
+----------+---------+
| count(*) | dept_id |
+----------+---------+8 | | 1 | | 5 | | 2 | 3 | | 6 12 5 | | | | 9 6 | | | | 35 7 | | 3 | | + 8----------+---------+
7 rows in set (0.00 sec)
Copy the code

Query the number of people in each department

Analysis:

Query information about all departments

mysql> select d.* from departments as d;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+| 1 | human resources | | 2 | department | | 3 | operations department | | | development | 4 5 test department | | | | | | Marketing Department 6 7 | sales | | | | | 8 legal | | NULL | + 9---------+-----------+
9 rows in set (0.00 sec)
Copy the code

Query the number of people in each department

mysql> select d.*, (
    ->  select count(*) from employees as e
    ->   where d.dept_id=e.dept_id
    -> ) as amount
    -> from departments as d;
+---------+-----------+--------+
| dept_id | dept_name | amount |
+---------+-----------+--------+| 1 hr 8 | | | | 2 | | finance department 5 | | 3 | | 6 operations department | | | development | 4 55 | | | 5 test department 12 | | | | | Marketing Department 6 to 9 | | 7 35 | sales | | | | 8 Ministry of law | 3 | | | | NULL | 0 + 9---------+-----------+--------+
9 rows in set (0.00 sec)
Copy the code

Multi-line single-row

Query personnel and finance department employee information

Analysis:

Check personnel and finance department numbers

mysql> select dept_id from departments
    -> where dept_name in ('Personnel Department'.'Finance Department');
+---------+
| dept_id |
+---------+| 1 | | | + 2---------+
2 rows in set (0.00 sec)
Copy the code

Query the employee information of two department numbers

mysql> select * from employees
    -> where dept_id in (
    ->   select dept_id from departments
    ->   where dept_name in ('Personnel Department'.'Finance Department')
    -> );
Copy the code

Query the salaries of all employees in Personnel Department in December 2018

Analysis:

Query the department number of personnel department

mysql> select dept_id from departments where dept_name='Personnel Department';
+---------+
| dept_id |
+---------+| | + 1---------+
1 row in set (0.00 sec)
Copy the code

Query the number of personnel personnel

mysql> select employee_id from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='Personnel Department'
    -> );
+-------------+
| employee_id |
+-------------+| 1 | | 2 | 3 | | | | 4 5 | | | | 6 7 | | | | + 8-------------+
8 rows in set (0.00 sec)
Copy the code

Query the salaries of all employees in personnel department in December 2018

mysql> select * from salary
    -> where year(date) =2018 and month(date) =12 and employee_id in (
    ->   select employee_id from employees
    ->   where dept_id=(
    ->     select dept_id from departments where dept_name='Personnel Department'
    ->   )
    -> );
+------+------------+-------------+-------+-------+
| id   | date       | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+| 6252 | 2018-12-10 | 1 | 17016 | 7000 | | 6253 | 2018-12-10 | | 20662 | 9000 | | 6254 | | 3 | | 9724 8000 2018-12-10 | | 6255 | 2018-12-10 | | 17016 | 2000 | | 6256 | 2018-12-10 | | 17016 | 3000 | | 6257 | 2018-12-10 | | 17016 | 6 1000 | | 6258 | 2018-12-10 | | 23093 | 4000 | | 6259 | 2018-12-10 | | 23093 | | 2000 + 8------+------------+-------------+-------+-------+
8 rows in set (0.00 sec)
Copy the code

Separate multiple columns

Find the highest salary information for December 2018 for both base salary and bonus

Analysis:

Query the highest base salary as of December 2018

mysql> select max(basic) from salary
    -> where year(date) =2018 and month(date) =12;
+------------+
| max(basic) |
+------------+| | + 25524------------+
1 row in set (0.00 sec)
Copy the code

Query the highest bonus for December 2018

mysql> select max(bonus) from salary
    -> where year(date) =2018 and month(date) =12;
+------------+
| max(bonus) |
+------------+| | + 11000------------+
1 row in set (0.00 sec)
Copy the code

The query

mysql> select * from salary
    -> where year(date) =2018 and month(date) =12 and basic=(
    ->   select max(basic) from salary
    ->   where year(date) =2018 and month(date) =12
    -> ) and bonus=(
    ->   select max(bonus) from salary
    ->   where year(date) =2018 and month(date) =12
    -> );
+------+------------+-------------+-------+-------+
| id   | date       | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+| 6368 | | 117 | | 11000 | 25524 + 2018-12-10------+------------+-------------+-------+-------+
1 row in set (0.01 sec)
Copy the code

Multi-line column

Query the numbers, names, and email addresses of department 3 and its employees

Analysis of the

Query all information about department 3 and employees

mysql> select d.dept_name, e.*
    -> from departments as d
    -> inner join employees as e
    -> on d.dept_id=e.dept_id;
Copy the code

Treat the above result as a temporary table that must be aliased. Then query from the temporary table

mysql> select dept_id, dept_name, employee_id, name, email
    -> from (
    ->   select d.dept_name, e.*
    ->   from departments as d
    ->   inner join employees as e
    ->   on d.dept_id=e.dept_id
    -> ) as tmp_table
    -> where dept_id=3;
+---------+-----------+-------------+-----------+--------------------+
| dept_id | dept_name | employee_id | name      | email              |
+---------+-----------+-------------+-----------+--------------------+14 | | 3 | | operations department Liao Na | [email protected] | | 3 | operations department | | 15 DouHongMei | [email protected] | | 3 | | operations department 16 | | nie think [email protected] | | 3 | | | 17 operations department Chen | [email protected] | | 3 | | | 18 operations department Dai Lu | [email protected] | | 3 | operations department | | 19 Chen | [email protected] | +---------+-----------+-------------+-----------+--------------------+
6 rows in set (0.00 sec)
Copy the code

Paging query

When using SELECT query, if the result set has a large amount of data, such as tens of thousands of rows, it is too large to display on one page. It is better to display 100 rows at a time
To implement paging, you essentially display records 1 to 100 as page 1 from the result set, records 101 to 200 as page 2, and so on
Paging is essentially “intercepting” the m-nth records from the result set. This query can be implemented with the LIMIT

,

clause
The initial index starts at 0
LIMIT (page-1)*SIZE, SIZE
Example:

Select top 5 employees by employee_id
mysql> select employee_id, name from employees
    -> order by employee_id
    -> limit 0.5;
+-------------+-----------+
| employee_id | name      |
+-------------+-----------+| | 1 wei | | 2 | yan guo | exactly the | 3 | | | | 4 zhang jian | | | 5 Zheng Jing | +-------------+-----------+
5 rows in set (0.00 sec)


Select the first 15 to 20 employee names by employee_id
mysql> select employee_id, name from employees
    -> order by employee_id
    -> limit 15.5;
+-------------+--------+
| employee_id | name   |
+-------------+--------+16 | | nie to | | | 17 Chen | | | 18 Dai Lu | | | 19 Chen | | | 20 Jiang Hong | +-------------+--------+
5 rows in set (0.00 sec)
Copy the code

Join query UNION

Function: Combine the results of multiple SELECT statements.
Grammar:( ) UNION ( )
When a query is required, the number of fields retrieved by multiple SELECT statements must be consistent
The field types and order of each record should be consistent
The UNION keyword is de-duplicated by default. You can use UNION ALL to include duplicates

mysql> (select 'yes') union (select 'yes');
+-----+
| yes |
+-----+
| yes |
+-----+
1 row in set (0.00 sec)


mysql> (select 'yes') union all (select 'yes');
+-----+
| yes |
+-----+
| yes |
| yes |
+-----+
2 rows in set (0.00 sec)
Copy the code

For example, a manufacturer has a list of raw materials and a list of commodities and needs to export the prices of raw materials and commodities together
Query employees born before 1972 or after 2000

# Common method
mysql> select name, birth_date from employees
    -> where year(birth_date)<1972 or year(birth_date)>2000;
+-----------+------------+
| name      | birth_date |
+-----------+------------+| wei | 1971-08-19 | | jianping | 1971-11-02 | | DouHongMei | 1971-09-09 | | WenLanYing | 1971-08-14 | | base | | 1971-08-15 | | 1971-12-10 Lin  | +-----------+------------+
6 rows in set (0.01 sec)


# federated query method
mysql> (
    -> select name, birth_date from employees
    ->   where year(birth_date)<1972- >) - >union- > (- >select name, birth_date from employees
    ->   where year(birth_date)>2000
    -> );
+-----------+------------+
| name      | birth_date |
+-----------+------------+| wei | 1971-08-19 | | jianping | 1971-11-02 | | DouHongMei | 1971-09-09 | | WenLanYing | 1971-08-14 | | base | | 1971-08-15 | | 1971-12-10 Lin  | +-----------+------------+
6 rows in set (0.00 sec)
Copy the code