MySQL 操作实战

获取&构建测数据

更多详细可参考：

1. 下载 Github 上的官方测试数据：

wget https://github.com/datacharmer/test_db.git

2. 安装

# 解压缩
unzip test_db-master.zip 
# 进入执行目录
cd test_db-master/
# 加载数据 (实际执行时可能需要添加 -uroot -p**** 来给予足够权限)
mysql -t < employees.sql
# 验证数据有效性
time mysql -t < test_employees_sha.sql

验证数据有效性输出

mysql  -t < test_employees_md5.sql
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name   | expected_records | expected_crc                     |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name   | found_records    | found_crc                        |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+

3. 数据结构

MySQL官方测试数据-数据架构

基础查询

1. 查询某表的数据

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)

2. 查询某表中的特定数据

例如：查询 departments 中包含字母 'o' 的行

mysql> SELECT * FROM departments WHERE dept_name like '%o%';
+---------+------------------+
| dept_no | dept_name        |
+---------+------------------+
| d009    | Customer Service |
| d005    | Development      |
| d003    | Human Resources  |
| d004    | Production       |
+---------+------------------+
4 rows in set (0.00 sec)

3. 查询指定列的数据

mysql> select dept_name FROM departments WHERE dept_name like '%o%';
+------------------+
| dept_name        |
+------------------+
| Customer Service |
| Development      |
| Human Resources  |
| Production       |
+------------------+
4 rows in set (0.00 sec)

mysql> select dept_no FROM departments WHERE dept_name like '%o%';
+---------+
| dept_no |
+---------+
| d009    |
| d005    |
| d003    |
| d004    |
+---------+
4 rows in set (0.00 sec)

4. 约束返回结果

mysql> select * from departments limit 5;
+---------+------------------+
| dept_no | dept_name        |
+---------+------------------+
| d009    | Customer Service |
| d005    | Development      |
| d002    | Finance          |
| d003    | Human Resources  |
| d001    | Marketing        |
+---------+------------------+
5 rows in set (0.00 sec)

-- 带有偏移的 LIMIT，偏移量从 0 开始 (LIMIT 开始行, 限制条数;)
mysql> select * from departments limit 5,5;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
4 rows in set (0.00 sec)

5. 带有别名的查询

mysql> select dept_no as department_number from departments limit 5,5;
+-------------------+
| department_number |
+-------------------+
| d004              |
| d006              |
| d008              |
| d007              |
+-------------------+
4 rows in set (0.00 sec)

6. 查询结果去重

-- 原表
mysql> select * from dept_manager ;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110022 | d001    | 1985-01-01 | 1991-10-01 |
| 110039 | d001    | 1991-10-01 | 9999-01-01 |
| 110085 | d002    | 1985-01-01 | 1989-12-17 |
| 110114 | d002    | 1989-12-17 | 9999-01-01 |
| 110183 | d003    | 1985-01-01 | 1992-03-21 |
| 110228 | d003    | 1992-03-21 | 9999-01-01 |
| 110303 | d004    | 1985-01-01 | 1988-09-09 |
| 110344 | d004    | 1988-09-09 | 1992-08-02 |
| 110386 | d004    | 1992-08-02 | 1996-08-30 |
| 110420 | d004    | 1996-08-30 | 9999-01-01 |
...
24 rows in set (0.01 sec)

-- 我们希望查询该表中去重的 dept_no 字段 (DISTINCT COL1, COL2...) 
mysql> select distinct dept_no from dept_manager;
+---------+
| dept_no |
+---------+
| d001    |
| d002    |
| d003    |
| d004    |
| d005    |
| d006    |
| d007    |
| d008    |
| d009    |
+---------+
9 rows in set (0.01 sec)

7. 对查询结果进行排序

-- ORDER BY 列名 ASC(升序)|DESC(降序)
mysql> select * from dept_manager order by from_date asc;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 111692 | d009    | 1985-01-01 | 1988-10-17 |
| 111035 | d007    | 1985-01-01 | 1991-03-07 |
| 110725 | d006    | 1985-01-01 | 1989-05-06 |
| 111400 | d008    | 1985-01-01 | 1991-04-08 |
| 110511 | d005    | 1985-01-01 | 1992-04-25 |
...

mysql> select * from dept_manager order by from_date desc;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110420 | d004    | 1996-08-30 | 9999-01-01 |
| 111939 | d009    | 1996-01-03 | 9999-01-01 |
| 110854 | d006    | 1994-06-28 | 9999-01-01 |
| 111877 | d009    | 1992-09-08 | 1996-01-03 |
| 110386 | d004    | 1992-08-02 | 1996-08-30 |
...

-- 对多列进行组合排序
mysql> select * from dept_manager order by from_date,to_date asc;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110303 | d004    | 1985-01-01 | 1988-09-09 |
| 111692 | d009    | 1985-01-01 | 1988-10-17 |
| 110725 | d006    | 1985-01-01 | 1989-05-06 |
| 110085 | d002    | 1985-01-01 | 1989-12-17 |
| 111035 | d007    | 1985-01-01 | 1991-03-07 |
...

-- 多列组合混合排序（对不同列，应用不同的排列方式）
mysql> select * from dept_manager order by from_date asc, to_date desc;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110511 | d005    | 1985-01-01 | 1992-04-25 |
| 110183 | d003    | 1985-01-01 | 1992-03-21 |
| 110022 | d001    | 1985-01-01 | 1991-10-01 |
| 111400 | d008    | 1985-01-01 | 1991-04-08 |
| 111035 | d007    | 1985-01-01 | 1991-03-07 |
...

带条件的查询

1. 使用操作符进行查询

比较操作符：

操作符	示例	说明
=	a = b	a 等于 b
<> 或 !=	a <> b	a 不等于 b
<	a < b	a 小于 b
>	a > b	a 大于 b
BETWEEN	a BETWEEN b AND c	满足 b <= a <= c
...	...	...

操作符(Operators)官方文档说明

-- 查询 emp_no > 111000 的
mysql> select * from dept_manager where emp_no > 111000;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 111035 | d007    | 1985-01-01 | 1991-03-07 |
| 111133 | d007    | 1991-03-07 | 9999-01-01 |
| 111400 | d008    | 1985-01-01 | 1991-04-08 |
| 111534 | d008    | 1991-04-08 | 9999-01-01 |
| 111692 | d009    | 1985-01-01 | 1988-10-17 |
| 111784 | d009    | 1988-10-17 | 1992-09-08 |
| 111877 | d009    | 1992-09-08 | 1996-01-03 |
| 111939 | d009    | 1996-01-03 | 9999-01-01 |
+--------+---------+------------+------------+
8 rows in set (0.00 sec)

-- 如果想要查询 from_date < 1990-01-01 的呢？
mysql> select * from dept_manager where from_date < 1990-01-01;
Empty set, 1 warning (0.00 sec)
-- 结果为空，原因是什么？

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

2. 匹配列表中的元素 (IN | NOT IN)

-- 查询部门 d001 和 d002 的 manager 信息
-- 注意，这里的 d001 / d002 因为是字符串，所以添加了 ``
mysql> select * from dept_manager where dept_no in ('d001', 'd002');
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110022 | d001    | 1985-01-01 | 1991-10-01 |
| 110039 | d001    | 1991-10-01 | 9999-01-01 |
| 110085 | d002    | 1985-01-01 | 1989-12-17 |
| 110114 | d002    | 1989-12-17 | 9999-01-01 |
+--------+---------+------------+------------+
4 rows in set (0.00 sec)

-- 而对于数值型列则进行自动转换
mysql> select * from dept_manager where emp_no in (110022,110039);
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110022 | d001    | 1985-01-01 | 1991-10-01 |
| 110039 | d001    | 1991-10-01 | 9999-01-01 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)

mysql> select * from dept_manager where emp_no in ('110022','110039');
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110022 | d001    | 1985-01-01 | 1991-10-01 |
| 110039 | d001    | 1991-10-01 | 9999-01-01 |
+--------+---------+------------+------------+
2 rows in set (0.01 sec)

3. 匹配 NULL 值（IS NULL | IS NOT NULL）

略

4. 通配符 (LIKE | NOT LIKE)

-- employees 表数据示例
mysql> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

-- 查询 employees 表中名字中包含字符 `za` 的员工
mysql> select * from employees where first_name like '%za%' limit 5;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
|  10002 | 1964-06-02 | Bezalel    | Simmel      | F      | 1985-11-21 |
|  10302 | 1954-10-11 | Faiza      | Baer        | F      | 1986-07-22 |
|  10630 | 1954-08-10 | Bezalel    | Katzenelson | F      | 1993-06-26 |
|  10706 | 1959-10-11 | Gonzalo    | Eldridge    | M      | 1989-12-17 |
|  11251 | 1955-10-16 | Bezalel    | Chinal      | M      | 1985-11-26 |
+--------+------------+------------+-------------+--------+------------+
5 rows in set (0.00 sec)

-- 查询 employees 表中名字中 不包含 字符 `za` 的员工
mysql> select * from employees where first_name not like '%za%' limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

% 代表任意一个字符

_ 代表任意一个字符

-- 这里 ___ 相当于三个任意字符
mysql> select distinct first_name from employees where first_name like 'Be___' limit 5;
+------------+
| first_name |
+------------+
| Berni      |
| Bedir      |
| Bernt      |
| Bernd      |
| Berna      |
+------------+
5 rows in set (0.00 sec)

5. 复杂&组合查询 (AND | OR ...)

-- 查询 last_name 为 Simmel 且 gender 为 M ,且 first_name 中包含 de 或 on 的
mysql> select * from employees where last_name = 'Simmel' and gender = 'M' and (first_name like '%de%' or first_name like '%on%')limit 20;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  21020 | 1955-02-14 | Adel       | Simmel    | M      | 1991-01-28 |
|  29753 | 1959-11-07 | Moni       | Simmel    | M      | 1990-05-02 |
|  41544 | 1953-11-16 | Rayond     | Simmel    | M      | 1987-12-05 |
| 211086 | 1962-09-18 | Wonhee     | Simmel    | M      | 1998-04-30 |
| 239318 | 1958-07-16 | Zhonghui   | Simmel    | M      | 1995-05-02 |
| 252312 | 1955-07-07 | Gonzalo    | Simmel    | M      | 1985-09-23 |
| 287561 | 1961-02-25 | Honesty    | Simmel    | M      | 1985-11-24 |
| 293263 | 1957-08-19 | Theron     | Simmel    | M      | 1985-03-23 |
| 406928 | 1956-07-26 | Clyde      | Simmel    | M      | 1986-03-27 |
| 478738 | 1955-03-07 | Cordelia   | Simmel    | M      | 1985-10-18 |
| 492745 | 1958-06-16 | Almudena   | Simmel    | M      | 1986-03-29 |
| 496531 | 1952-12-06 | Saddek     | Simmel    | M      | 1988-11-17 |
+--------+------------+------------+-----------+--------+------------+
12 rows in set (0.08 sec)

-- 问题：为何这里使用了 () 如何不使用，会发生什么情况？

表达式 & 函数

1. 简单表达式

mysql> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

-- emp_no 都是从 10001 开始的，想要从 1 开始；
mysql> select emp_no-10000 as new_emp_no, birth_date from employees limit 5;
+------------+------------+
| new_emp_no | birth_date |
+------------+------------+
|          1 | 1953-09-02 |
|          2 | 1964-06-02 |
|          3 | 1959-12-03 |
|          4 | 1954-05-01 |
|          5 | 1955-01-21 |
+------------+------------+
5 rows in set (0.00 sec)
-- 这里的 emp_no-10000 就是一个表达式

2. 函数

2.1 文本处理函数

String-Functions 大全

名称	示例	示例结果	说明
CONCAT	CONCAT('Z', 'W', 'S')	'ZWS'	合并多个字符串（列）
CONCAT_WS	CONCATWS('\', 'Z', 'W', 'S')	'Z_W_S'	基于指定分隔符合并字符串（列）
LENGTH	LENGTH('zws')	3	计算给定字符串长度
LOWER	LOWER('ZWS')	zws	给定字符串的小写格式
SUBSTRING	SUBSTRING('ZWS1994', 4, 4)	1994	获取指定字符串从指定位置开始，指定长度的字符串
REPEAT	REPEAT('ZWS',3)	ZWSZWSZWS	重复字符串指定次数
REPLACE	REPLACE('ZWS','WS','ZZ')	ZZZ	替换字符串中指定字符（WS）为相应字符（ZZ）
REVERSE	REVERSE('ZWS')	SWZ	字符串反转

mysql> select concat(first_name, ' ', last_name )as name from employees limit 5;
+-------------------+
| name              |
+-------------------+
| Georgi Facello    |
| Bezalel Simmel    |
| Parto Bamford     |
| Chirstian Koblick |
| Kyoichi Maliniak  |
+-------------------+
5 rows in set (0.00 sec)

2.2 正则表达式

-- 查询 first_name 中首字母为 B, 尾字母为 l 的行。
mysql> select * from employees where first_name regexp '^B.*l$' limit 5;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
|  10002 | 1964-06-02 | Bezalel    | Simmel      | F      | 1985-11-21 |
|  10049 | 1961-04-24 | Basil      | Tramer      | F      | 1992-05-04 |
|  10258 | 1955-12-12 | Basil      | Ishibashi   | F      | 1985-05-17 |
|  10630 | 1954-08-10 | Bezalel    | Katzenelson | F      | 1993-06-26 |
|  11251 | 1955-10-16 | Bezalel    | Chinal      | M      | 1985-11-26 |
+--------+------------+------------+-------------+--------+------------+
5 rows in set (0.00 sec)

-- 将字符串中的 to 换成 o_o 
mysql> select emp_no, regexp_replace(first_name, 'to', 'o_o') as new_first_name from employees limit 5;
+--------+----------------+
| emp_no | new_first_name |
+--------+----------------+
|  10001 | Georgi         |
|  10002 | Bezalel        |
|  10003 | Paro_o         |
|  10004 | Chirstian      |
|  10005 | Kyoichi        |
+--------+----------------+
5 rows in set (0.01 sec)

-- 将字符串中符合条件的字符串提取出来
mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc                                    |
+----------------------------------------+
mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi                                          |
+----------------------------------------------+

2.3 时间/日期处理函数

Date&Time-Functions 大全

名称	示例	示例结果	说明
NOW	NOW()	2020-08-31 09:15:10	返回当前时间（注意时区）
DATEDIFF	DATEDIFF('2020-08-01', '2020-08-31');	-30	返回两个日期之间的天数，前者-后者
DATE_FORMAT	DATE_FORMAT(NOW(),'%m-%d-%Y')	08-31-2020	用给定的格式显示日期时间
DATE_ADD	DATE_ADD(NOW(), INTERVAL 1 Year)	2021-08-31 09:19:16	时间+给定时间间隔（可自主选择日期格式）
DATE	DATE(NOW())	2020-08-31	提取时间中的日期部分
...	...	...	...

mysql> select emp_no,dept_no, datediff(now(), from_date)as days, from_date, to_date from dept_emp limit 5;
+--------+---------+-------+------------+------------+
| emp_no | dept_no | days  | from_date  | to_date    |
+--------+---------+-------+------------+------------+
|  10001 | d005    | 12485 | 1986-06-26 | 9999-01-01 |
|  10002 | d007    |  8794 | 1996-08-03 | 9999-01-01 |
|  10003 | d004    |  9038 | 1995-12-03 | 9999-01-01 |
|  10004 | d004    | 12327 | 1986-12-01 | 9999-01-01 |
|  10005 | d003    | 11311 | 1989-09-12 | 9999-01-01 |
+--------+---------+-------+------------+------------+
5 rows in set (0.00 sec)

2.4 数值处理函数

Numeric-Functions 大全

名称	示例	示例结果	说明
ABS	ABS(-11)	11	返回绝对值
COS	COS(PI())	-1	返回余弦值
EXP	EXP(2)	7.3890560989307	取e(自然对数的底数)的x次方的值
LOG	LOG(10,100)	2	取对数
POW	POW(2,4)	16	取指数
RAND	RAND()	0.3439174130036658	返回 0-1 范围内的浮点数
TRUNCATE	TRUNCATE(0.343917413, 2)	0.34	保留几位小数
...	...	...	...

2.5 聚集函数

Aggregate-Functions 大全

名称	说明
AVG()	返回均值
COUNT()	返回某列的行数
COUNT(DISTINCT)	返回某列唯一值数
GROUP_CONCAT()	返回串联后的字符串
MAX()	返回最大值
MIN()	返回最小值
STD()	返回标准差
SUM()	返回和
...	...

mysql> select max(emp_no),min(emp_no), count(dept_no), count(distinct dept_no) from dept_emp limit 20;
+-------------+-------------+----------------+-------------------------+
| max(emp_no) | min(emp_no) | count(dept_no) | count(distinct dept_no) |
+-------------+-------------+----------------+-------------------------+
|      499999 |       10001 |         331603 |                       9 |
+-------------+-------------+----------------+-------------------------+
1 row in set (0.18 sec)

分组查询

Group-by 官方文档说明

-- 依然以 employees 数据为例
mysql> select * from employees limit 20;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello     | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel      | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford     | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick     | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak    | M      | 1989-09-12 |
...

-- 分别统计不同性别的总人数
mysql> SELECT gender, COUNT(gender) from employees group by gender;
+--------+---------------+
| gender | COUNT(gender) |
+--------+---------------+
| M      |        179973 |
| F      |        120051 |
+--------+---------------+
2 rows in set (0.11 sec)

-- 统计不同性别下，不同姓氏对应的人数
mysql> SELECT gender, last_name, COUNT(*)as num from employees group by gender, last_name order by num desc limit 5;
+--------+-----------+-----+
| gender | last_name | num |
+--------+-----------+-----+
| M      | Sudbeck   | 143 |
| M      | Taubman   | 142 |
| M      | Mandell   | 141 |
| M      | Osgood    | 141 |
| M      | Engberts  | 141 |
+--------+-----------+-----+
5 rows in set (0.26 sec)

-- 统计不同姓氏下，年龄最大的人
mysql> SELECT  last_name, max(birth_date) from employees group by last_name limit 5;
+-----------+-----------------+
| last_name | max(birth_date) |
+-----------+-----------------+
| Facello   | 1964-12-17      |
| Simmel    | 1965-01-30      |
| Bamford   | 1965-01-22      |
| Koblick   | 1965-01-27      |
| Maliniak  | 1965-01-21      |
+-----------+-----------------+
5 rows in set (0.20 sec)

Group-by 通常是和聚集函数结合起来使用的。

窗口函数

Window-Function 官方文档说明

更多关于窗口函数的介绍，可参考：MySQL 8.0 窗口函数(Window Functions)

-- 将同姓的人，按照 hire_date 进行排序，并给定序号
mysql> select birth_date, first_name, last_name, gender, hire_date, row_number() over(PARTITION BY last_name ORDER BY hire_date asc) AS 'row_number' From employees LIMIT 20;
+------------+-------------+-----------+--------+------------+------------+
| birth_date | first_name  | last_name | gender | hire_date  | row_number |
+------------+-------------+-----------+--------+------------+------------+
| 1960-04-16 | Gian        | Aamodt    | M      | 1985-02-08 |          1 |
| 1957-07-14 | Fuqing      | Aamodt    | M      | 1985-02-11 |          2 |
| 1960-10-15 | Luisa       | Aamodt    | F      | 1985-02-13 |          3 |
| 1962-09-08 | Yolla       | Aamodt    | F      | 1985-02-18 |          4 |
| 1953-05-19 | Sigeru      | Aamodt    | M      | 1985-03-02 |          5 |
| 1959-03-06 | Aluzio      | Aamodt    | M      | 1985-03-03 |          6 |
| 1958-10-17 | Sreekrishna | Aamodt    | M      | 1985-03-17 |          7 |
| 1955-09-27 | Magy        | Aamodt    | M      | 1985-03-31 |          8 |
| 1957-08-29 | Hidefumi    | Aamodt    | M      | 1985-05-02 |          9 |
| 1964-12-01 | Conal       | Aamodt    | F      | 1985-05-22 |         10 |
| 1964-11-26 | Peternela   | Aamodt    | M      | 1985-06-03 |         11 |
| 1956-11-16 | Rimli       | Aamodt    | M      | 1985-06-12 |         12 |
| 1958-04-20 | Yakkov      | Aamodt    | M      | 1985-06-22 |         13 |
| 1957-08-14 | Weiyi       | Aamodt    | M      | 1985-06-24 |         14 |
| 1961-03-07 | Supot       | Aamodt    | M      | 1985-06-25 |         15 |
| 1958-06-21 | Weiru       | Aamodt    | M      | 1985-06-28 |         16 |
| 1954-01-14 | Hiroyasu    | Aamodt    | M      | 1985-07-26 |         17 |
| 1964-03-14 | Tamiya      | Aamodt    | M      | 1985-08-03 |         18 |
| 1964-04-27 | Garnet      | Aamodt    | F      | 1985-08-06 |         19 |
| 1955-03-23 | Kwan        | Aamodt    | F      | 1985-08-09 |         20 |
+------------+-------------+-----------+--------+------------+------------+
20 rows in set (0.54 sec)

连接查询

连接的本质就是把各个表中的记录都取出来依次匹配的组合加入结果集并返回给用户

mysql> select * from dept_manager as a left join departments as b on a.dept_no = b.dept_no limit 5;
+--------+---------+------------+------------+---------+-----------------+
| emp_no | dept_no | from_date  | to_date    | dept_no | dept_name       |
+--------+---------+------------+------------+---------+-----------------+
| 110022 | d001    | 1985-01-01 | 1991-10-01 | d001    | Marketing       |
| 110039 | d001    | 1991-10-01 | 9999-01-01 | d001    | Marketing       |
| 110085 | d002    | 1985-01-01 | 1989-12-17 | d002    | Finance         |
| 110114 | d002    | 1989-12-17 | 9999-01-01 | d002    | Finance         |
| 110183 | d003    | 1985-01-01 | 1992-03-21 | d003    | Human Resources |
+--------+---------+------------+------------+---------+-----------------+
5 rows in set (0.00 sec)

除了 LEFT JOIN 外还存在 INNER JOIN / RIGHT JOIN / OUTER ... 更多可参考上方官方文档链接。

User-Defined Functions

有时，我们需要自己建立一个函数，用于处理。

CREATE PROCEDURE and CREATE FUNCTION Statements

语法：

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

示例：

CREATE FUNCTION full_name (first_name CHAR(20), last_name CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT(first_name, ' ', last_name);

调用 UDF

mysql> select full_name(first_name, last_name) from employees limit 5;
+----------------------------------+
| full_name(first_name, last_name) |
+----------------------------------+
| Georgi Facello                   |
| Bezalel Simmel                   |
| Parto Bamford                    |
| Chirstian Koblick                |
| Kyoichi Maliniak                 |
+----------------------------------+
5 rows in set (0.00 sec)

视图

视图是一个虚拟表，可以理解为一个查询语句的别名，创建视图语法如下：

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

简单来说： CREATE VIEW 视图名 AS 查询语句

举例：

-- 创建视图，该视图中 first_name 中都包含 to
mysql> create view first_name_contain_to as select * from employees where first_name like '%to%';
Query OK, 0 rows affected (0.00 sec)

-- 查询视图
mysql> select * from first_name_contain_to limit 5;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
|  10003 | 1959-12-03 | Parto      | Bamford     | M      | 1986-08-28 |
|  10016 | 1961-05-02 | Kazuhito   | Cappelletti | M      | 1995-01-27 |
|  10065 | 1963-04-14 | Satosi     | Awdeh       | M      | 1988-05-18 |
|  10117 | 1961-10-24 | Kiyotoshi  | Blokdijk    | F      | 1990-05-28 |
|  10147 | 1964-10-13 | Kazuhito   | Encarnacion | M      | 1986-08-21 |
+--------+------------+------------+-------------+--------+------------+
5 rows in set (0.01 sec)

创建的视图，也可以通过 show tables 进行查看。

其他延申内容

索引
游标
触发器
查询优化
主从复制
全文索引
存储引擎 ...

FurtherGazer

FurtherGazer's blog

MySQL 操作实战

获取&构建测数据

1. 下载 Github 上的官方测试数据：

2. 安装

3. 数据结构

基础查询

1. 查询某表的数据

2. 查询某表中的特定数据

3. 查询指定列的数据

4. 约束返回结果

5. 带有别名的查询

6. 查询结果去重

7. 对查询结果进行排序

带条件的查询

1. 使用操作符进行查询

2. 匹配列表中的元素 (IN | NOT IN)

3. 匹配 NULL 值（IS NULL | IS NOT NULL）

4. 通配符 (LIKE | NOT LIKE)

5. 复杂&组合查询 (AND | OR ...)

表达式 & 函数

1. 简单表达式

2. 函数

2.1 文本处理函数

2.2 正则表达式

2.3 时间/日期处理函数

2.4 数值处理函数

2.5 聚集函数

分组查询

窗口函数

连接查询

User-Defined Functions

视图

其他延申内容

您还没有登录，请您登录后发表评论。

获取&构建测数据

1. 下载 Github 上的官方测试数据：

2. 安装

3. 数据结构

基础查询

1. 查询某表的数据

2. 查询某表中的特定数据

3. 查询指定列的数据

4. 约束返回结果

5. 带有别名的查询

6. 查询结果去重

7. 对查询结果进行排序

带条件的查询

1. 使用操作符进行查询

2. 匹配列表中的元素 (IN | NOT IN)

3. 匹配 NULL 值 （IS NULL | IS NOT NULL）

4. 通配符 (LIKE | NOT LIKE)

5. 复杂&组合查询 (AND | OR ...)

表达式 & 函数

1. 简单表达式

2. 函数

2.1 文本处理函数

2.2 正则表达式

2.3 时间/日期处理函数

2.4 数值处理函数

2.5 聚集函数

分组查询

窗口函数

连接查询

User-Defined Functions

视图

其他延申内容

您还没有登录，请您登录后发表评论。

3. 匹配 NULL 值（IS NULL | IS NOT NULL）