Linux云计算 |【第四阶段】RDBMS1-DAY2

主要内容：

常用函数（函数分类1：单行、分组；函数分类2：字符、数学、日期、流程控制）、分组查询group by、连接查询

一、常用函数

1. 按使用方式分类

① 单行函数

单行函数（Scalar Functions）是对单个行进行操作的函数，每行返回一个结果。单行函数可以用于 SELECT、WHERE、ORDER BY 等子句中。

示例：

• 字符函数：UPPER()、LOWER()、CONCAT()
• 数学函数：ABS()、ROUND()、SQRT()
• 日期函数：NOW()、DATE_FORMAT()

SELECT UPPER(name) AS upper_name, ABS(salary) AS abs_salary
FROM employees
WHERE DATE_FORMAT(hire_date, '%Y-%m') = '2023-01';

② 分组函数

分组函数（Aggregate Functions）是对一组行进行操作的函数，返回一个汇总结果。分组函数通常与 GROUP BY 子句一起使用。

示例：

• 求和：SUM()
• 平均值：AVG()
• 最大值：MAX()
• 最小值：MIN()
• 计数：COUNT()

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

2. 按用途分类

① 字符函数

字符函数用于处理字符串数据，包括字符串的拼接、截取、转换等操作。

示例：

• 字符串拼接：CONCAT()
• 字符串长度：LENGTH()
• 字符串截取：SUBSTRING()
• 字符串转换：UPPER()、LOWER()

SELECT CONCAT(first_name, ' ', last_name) AS full_name, LENGTH(full_name) AS name_length
FROM employees;

② 数学函数

数学函数用于处理数值数据，包括数值的计算、取整、取绝对值等操作。

示例：

• 绝对值：ABS()
• 四舍五入：ROUND()
• 平方根：SQRT()
• 幂运算：POWER()

SELECT ABS(salary) AS abs_salary, ROUND(salary, 2) AS rounded_salary
FROM employees;

③ 日期函数

日期函数用于处理日期和时间数据，包括日期的格式化、日期差计算、当前日期获取等操作。

示例：

• 当前日期：NOW()
• 日期格式化：DATE_FORMAT()
• 日期差：DATEDIFF()
• 日期加减：DATE_ADD()、DATE_SUB()

SELECT NOW() AS current_date, DATE_FORMAT(hire_date, '%Y-%m-%d') AS formatted_hire_date
FROM employees;

④ 流程控制函数

流程控制函数用于在 SQL 语句中实现条件判断和流程控制，类似于编程语言中的条件语句。

示例：

• 条件判断：CASE
• 空值处理：IFNULL()、COALESCE()
• 条件选择：IF()

SELECT employee_id, salary,
       CASE
           WHEN salary > 10000 THEN 'High'
           WHEN salary > 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

二、函数应用

1、字符函数实例：

LENGTH(s)、CHAR_LENGTH(s)、CONCAT(s1,s2，...)、UPPER(s)、LOWER(s)、SUBSTR(s)、INSTR(s)、TRIM(s)

1）LENGTH(str)：返回字符串长度，以字节为单位；

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

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

补充：一个中文字一般占3-4个字节

例如：查询name字段是李平的记录，计算其email的字符串长度

mysql> select name,email,length(email) from employees where name='李平';
+--------+----------------+---------------+
| name   | email          | length(email) |
+--------+----------------+---------------+
| 李平   | liping@tedu.cn |            14 |
+--------+----------------+---------------+
1 row in set (0.00 sec)

2）CHAR_LENGTH(str)：返回字符串长度，以字符为单位；

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

mysql> select char_length('您好');
+-----------------------+
| char_length('您好')   |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

3）CONCAT(str1,str2,...)：返回连接参数产生的字符串（一个或多个待拼接的内容，任意一个为NULL，则返回值为NULL）；

mysql> select concat(dept_id,'-',dept_name) from departments;
+-------------------------------+
| concat(dept_id,'-',dept_name) |
+-------------------------------+
| 1-人事部                      |
| 2-财务部                      |
| 3-运维部                      |
| 4-开发部                      |
| 5-测试部                      |
| 6-市场部                      |
| 7-销售部                      |
| 8-法务部                      |
| NULL                          |
+-------------------------------+
9 rows in set (0.00 sec)

4）UPPER(str)和UCASE(str): 将字符串中的字母全部转换成大写；

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

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

例如：查询name字段为“李某”，将email字段记录转换成大写

mysql> select name,upper(email) from employees where name like '李_';
+--------+-------------------+
| name   | upper(email)      |
+--------+-------------------+
| 李平   | LIPING@TEDU.CN    |
| 李莹   | LIYING@TEDU.CN    |
| 李柳   | LILIU@TARENA.COM  |
| 李慧   | LIHUI@TARENA.COM  |
| 李静   | LIJING@TARENA.COM |
| 李瑞   | LIRUI@TARENA.COM  |
+--------+-------------------+
6 rows in set (0.00 sec)

5）LOWER(str)和LCASE(str)：将str中的字母全部转换成小写；

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

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

6）SUBSTR(str, start, length)：从字符串str的start位置开始，取出length长度的子串，位置从1开始计算；

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

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

7）INSTR(str,str1)：返回str1参数，在str参数内的位置；

mysql> select instr('hello world','o');
+--------------------------+
| instr('hello world','o') |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select instr('hello world','or');
+---------------------------+
| instr('hello world','or') |
+---------------------------+
|                         8 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select instr('hello world','ol');      //不存在的参数，返回0
+---------------------------+
| instr('hello world','ol') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

例如：查询name字段为“李某”，返回email字段的记录中“@”的位置

mysql> select name,email,instr(email,'@') from employees where name='李平';
+--------+----------------+------------------+
| name   | email          | instr(email,'@') |
+--------+----------------+------------------+
| 李平   | liping@tedu.cn |                7 |
+--------+----------------+------------------+
1 row in set (0.00 sec)

8）TRIM(str)：返回并删除两边空格之后的字符串str（仅字符串两边的空格）；

mysql> select trim('  Hello World  ');
+-------------------------+
| trim('  Hello World  ') |
+-------------------------+
| Hello World             |
+-------------------------+
1 row in set (0.00 sec)

2、数学函数实例：

ABS(x)、PI()、MOD(x,y)、CEIL(x)、FLOOR(x)、ROUND(x)和ROUND(x,y)

1）ABS(x)：返回x的绝对值；

mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.01 sec)

2）PI(): 返回圆周率π，默认显示6位小数；

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

3）MOD(x,y): 返回x被y除后的余数；

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

4）CEIL(x)、CEILING(x): 返回不小于x的最小整数；

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

mysql> select ceil(-10.1);
+-------------+
| ceil(-10.1) |
+-------------+
|         -10 |
+-------------+
1 row in set (0.00 sec)

5）FLOOR(x): 返回不大于x的最大整数；

mysql> select floor(10.1);
+-------------+
| floor(10.1) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

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

6）ROUND(x)：返回最接近于x的整数（即对x进行四舍五入）；ROUND(x,y)：返回最接近x的数，其值保留到小数点后y位；若y为负值，则对x数进行位数四舍五入，例如y=-1，则为1的1次方(10位数)，y=-2，则为1的1的2次方（100位数）；

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

mysql> select round(10.66666,2);
+-------------------+
| round(10.66666,2) |
+-------------------+
|             10.67 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(13,-1);    //对13进行位数上的四舍五入
+--------------+
| round(13,-1) |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

mysql> select round(16,-1);
+--------------+
| round(16,-1) |
+--------------+
|           20 |
+--------------+
1 row in set (0.00 sec)

3、日期和时间函数实例：

1）CURDATE()、CURRENT_DATE(): 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定；

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-06-02 |
+------------+
1 row in set (0.00 sec)

mysql> select curdate() + 0;
+---------------+
| curdate() + 0 |
+---------------+
|      20210602 |
+---------------+
1 row in set (0.00 sec)

mysql> select curdate() + 30;     //字符串语境
+----------------+
| curdate() + 30 |
+----------------+
|       20210632 |
+----------------+
1 row in set (0.00 sec)

2）NOW()：返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定；

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-06-02 13:27:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now() + 0;
+----------------+
| now() + 0      |
+----------------+
| 20210602185848 |
+----------------+
1 row in set (0.00 sec)

3）UNIX_TIMESTAMP()：返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数（时间戳），UNIX_TIMESTAMP(date): 返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数；

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1622603480 |
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(20211001);
+--------------------------+
| unix_timestamp(20211001) |
+--------------------------+
|               1633017600 |
+--------------------------+
1 row in set (0.00 sec)

4）FROM_UNIXTIME(date)：和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间；

mysql> select from_unixtime(0);
+---------------------+
| from_unixtime(0)    |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
1 row in set (0.00 sec)

5）MONTH(date)：返回指定日期中的月份，MONTHNAME(date): 返回指定日期中的月份的名称

mysql> select month(20210602111400);
+-----------------------+
| month(20210602111400) |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

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

mysql> select monthname(20210602111400);
+---------------------------+
| monthname(20210602111400) |
+---------------------------+
| June                      |
+---------------------------+
1 row in set (0.00 sec)

6）DAY(date)返回日期，DAYNAME(date)返回d对应日期的英文名称，如Sunday、Monday等；

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

mysql> select dayname(20211001);    //20211001为星期五
+-------------------+
| dayname(20211001) |
+-------------------+
| Friday            |
+-------------------+
1 row in set (0.00 sec)

7）WEEK(date)：返回计算日期是一年中的第几周；DAYOFWEEK(date)：返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(date)：表示d对应的工作日索引，0表示周一，1表示周二；

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

mysql> select dayofweek(20211001);
+---------------------+
| dayofweek(20211001) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)

mysql> select weekday(20211001);
+-------------------+
| weekday(20211001) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

8）DAYOFYEAR(date)：返回date是一年中的第几天，DAYOFMONTH(date): 返回d是一月中的第几天

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

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

9）YEAR(date)：返回指定日期对应的年份，范围是1970到2069；QUARTER(date)返回date对应一年中的季度，范围是1到4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(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)

mysql> select minute('20211001141158');
+--------------------------+
| minute('20211001141158') |
+--------------------------+
|                       11 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select second('20211001141158');
+--------------------------+
| second('20211001141158') |
+--------------------------+
|                       58 |
+--------------------------+
1 row in set (0.00 sec)

例如：查询birth_date字段的记录，以2000后出生的人，并进行升序排序

mysql> select name,birth_date 
    -> from employees where year(birth_date) >= 2000 order by birth_date;
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 陈斌      | 2000-01-22 |
| 王璐      | 2000-02-01 |
| 游静      | 2000-02-14 |
| 陶红      | 2000-02-21 |
| 张倩      | 2000-04-27 |
| 蒋秀芳    | 2000-04-27 |
| 胡秀云    | 2000-05-14 |
| 张宇      | 2000-07-16 |
+-----------+------------+
8 rows in set (0.00 sec)

4、流程控制函数实例：

1）IF(expr,v1,v2)：如果expr表达式是TRUE则返回v1，否则返回v2

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

例如：查询name字段为张亮的记录，判断该记录的dept_id是否为1

mysql> select name,dept_id,if(dept_id=1,'人事部','非人事部') 
    -> from employees where name='张亮';
+--------+---------+------------------------------------------+
| name   | dept_id | if(dept_id=1,'人事部','非人事部')        |
+--------+---------+------------------------------------------+
| 张亮   |       7 | 非人事部                                 |
+--------+---------+------------------------------------------+
1 row in set (0.00 sec)

2）IFNULL(v1,v2)：如果v1不为NULL，则返回v1，否则返回v2

mysql> select dept_id,dept_name,ifnull(dept_name,'not set') 
    -> from departments;
+---------+-----------+-----------------------------+
| dept_id | dept_name | ifnull(dept_name,'not set') |
+---------+-----------+-----------------------------+
|       1 | 人事部    | 人事部                      |
|       2 | 财务部    | 财务部                      |
|       3 | 运维部    | 运维部                      |
|       4 | 开发部    | 开发部                      |
|       5 | 测试部    | 测试部                      |
|       6 | 市场部    | 市场部                      |
|       7 | 销售部    | 销售部                      |
|       8 | 法务部    | 法务部                      |
|       9 | NULL      | not set                     |
+---------+-----------+-----------------------------+
9 rows in set (0.00 sec)

3）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END: 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不相等，则返回ELSE后面的rn

mysql> select dept_id,dept_name,
    -> case
    -> when dept_name='运维部' then '技术部门'
    -> when dept_name='开发部' then '技术部门'
    -> when dept_name='测试部' then '技术部门'
    -> when dept_name is null then '未设置'
    -> else '非技术部门'
    -> end as '部门类型'
    -> from departments;
+---------+-----------+-----------------+
| dept_id | dept_name | 部门类型        |
+---------+-----------+-----------------+
|       1 | 人事部    | 非技术部门      |
|       2 | 财务部    | 非技术部门      |
|       3 | 运维部    | 技术部门        |
|       4 | 开发部    | 技术部门        |
|       5 | 测试部    | 技术部门        |
|       6 | 市场部    | 非技术部门      |
|       7 | 销售部    | 非技术部门      |
|       8 | 法务部    | 非技术部门      |
|       9 | NULL      | 未设置          |
+---------+-----------+-----------------+
9 rows in set (0.00 sec)

5、分组函数

分组函数用于统计，又称为聚合函数或统计函数

1）sum(): 求和

例如：查询员工id=10且在2018年的工资+奖金的记录（每一行记录单独处理）

mysql> select employee_id,basic+bonus 
    -> from salary where employee_id=10 and year(date)=2018;
+-------------+-------------+
| employee_id | basic+bonus |
+-------------+-------------+
|          10 |       24837 |
|          10 |       31837 |
|          10 |       29837 |
|          10 |       29878 |
+-------------+-------------+
4 rows in set (0.00 sec)

例如：查询员工id=10且在2018年的工资+奖金求和的记录（所有记录进行求和）

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

2）avg()：求平均值

例如：查询员工id=10且在2018年的工资+奖金的平均值的记录

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

3）max()：求最大值

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

4）min()：求最小值

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

5）count()：统计记录的总个数

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

三、分组查询

在对数据表中数据进行统计时，可能需要按照一定的类别分别进行统计。比如查询每个部门的员工数；使用GROUP BY按某个字段，或者多个字段中的值，进行分组，字段中值相同的为一组；

语法格式：

SELECT 字段名1(要求出现在group by后面)，分组函数(),……

FROM 表名

WHERE 条件

GROUP BY 字段名1，字段名2

HAVING 过滤条件;

ORDER BY 字段

注意事项：

• ① 查询列表必须是分组函数和出现在GROUP BY后面的字段；
• ② 通常而言，分组前的数据筛选放在where子句中，分组后的数据筛选放在having子句中；

例如：查询每个部门的人数

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

例如：查询每个部门的每个员工在2018年的工资+奖金

mysql> select employee_id,sum(basic+bonus) 
    -> from salary where year(date)=2018 
    -> group by employee_id;
+-------------+------------------+
| employee_id | sum(basic+bonus) |
+-------------+------------------+
|           1 |           151046 |
|           2 |           328131 |
|           3 |           177595 |
…
|         133 |           146733 |
+-------------+------------------+
120 rows in set (0.01 sec)

例如：查询每个部门中年龄最大的员工（出生年月最小，即年龄最大）

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

例如：查询每个部门入职最晚员工的入职时间

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

例如：统计各部门使用了tedu.cn邮箱的人数

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

例如：查看员工2018年工资总收入，按总收入进行降序排列

mysql> select employee_id,sum(basic+bonus) as total 
    -> from salary where year(date)=2018 
    -> group by employee_id 
    -> order by total desc;
+-------------+--------+
| employee_id | total  |
+-------------+--------+
|          31 | 374923 |
|         117 | 374923 |
|          37 | 362981 |
|          68 | 360923 |
…
|           8 |  25093 |
+-------------+--------+
120 rows in set (0.00 sec)

例如：查询部门人数少于10人

mysql> select dept_id,count(*) 
    -> from employees group by dept_id having count(*) <10;    //分组后过滤
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        8 |
|       2 |        5 |
|       3 |        6 |
|       6 |        9 |
|       8 |        3 |
+---------+----------+
5 rows in set (0.00 sec)

例如：查询2018年的员工总工资大于300000的员工

mysql> select employee_id,sum(basic+bonus) as total 
    -> from salary where year(date)=2018 
    -> group by employee_id having sum(basic+bonus)>300000 
    -> order by total desc;
+-------------+--------+
| employee_id | total  |
+-------------+--------+
|          31 | 374923 |
|         117 | 374923 |
|          37 | 362981 |
…
|          40 | 304131 |
+-------------+--------+
31 rows in set (0.01 sec)

四、连接查询

连接查询也叫多表查询，常用于查询字段来自于多张表；

例如：如果直接查询两张表，将会得到笛卡尔积（两张表相乘）

mysql> select name, dept_name from employees, departments;
+-----------+-----------+
| name      | dept_name |
+-----------+-----------+
| 梁伟      | 人事部    |
| 梁伟      | 财务部    |
| 梁伟      | 运维部    |
| 梁伟      | 开发部    |
| 梁伟      | 测试部    |
| 梁伟      | 市场部    |
| 梁伟      | 销售部    |
| 梁伟      | 法务部    |
| 梁伟      | NULL      |
+-----------+-----------+
1197 rows in set (0.00 sec)

例如：通过添加有效的条件可以进行查询结果的限定

mysql> select name,dept_name 
    -> from employees,departments 
    -> where employees.dept_id=departments.dept_id;
+-----------+-----------+
| name      | dept_name |
+-----------+-----------+
| 梁伟      | 人事部    |
| 郭岩      | 人事部    |
| 李玉英    | 人事部    |
| 张健      | 人事部    |
| 郑静      | 人事部    |
| 杨金凤    | 法务部    |
+-----------+-----------+
133 rows in set (0.00 sec)

1. 按功能分类

1）内连接（Inner Join）

内连接返回两个表中满足连接条件的行。

① 等值连接（Equi Join）

等值连接使用等号（=）作为连接条件，返回两个表中连接字段值相等的行。

示例：

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

② 非等值连接（Non-Equi Join）

非等值连接使用除等号以外的比较运算符（如 <、>、<=、>=、BETWEEN 等）作为连接条件，返回两个表中连接字段值满足非等值条件的行。

示例：

SELECT employees.employee_id, employees.first_name, salaries.salary
FROM employees
INNER JOIN salaries ON employees.salary_id BETWEEN salaries.min_salary AND salaries.max_salary;

③ 自连接（Self Join）

自连接是指一个表与自身进行连接，通常用于查询表中具有层次结构或父子关系的数据。

示例：

SELECT e1.employee_id, e1.first_name, e2.first_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

2）外连接（Outer Join）

外连接返回两个表中满足连接条件的行，同时还会返回不满足连接条件的行。

① 左外连接（Left Outer Join）

左外连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则返回 NULL。

示例：

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

② 右外连接（Right Outer Join）

右外连接返回右表中的所有行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则返回 NULL。

示例：

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

③ 全外连接（Full Outer Join）

全外连接返回两个表中的所有行，无论是否满足连接条件。如果一个表中没有匹配的行，则返回 NULL。MySQL 不直接支持全外连接，但可以使用 UNION 实现相同的效果。

示例：

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

3）交叉连接（Cross Join）

交叉连接返回两个表的笛卡尔积，即第一个表中的每一行与第二个表中的每一行进行组合。

示例：

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
CROSS JOIN departments;

2. 按年代分类

① SQL92 标准

SQL92 标准仅支持内连接，使用 FROM 子句和 WHERE 子句进行连接操作。

示例：

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

② SQL99 标准

SQL99 标准支持所有功能的连接，包括内连接、外连接和交叉连接，使用 JOIN 关键字和 ON 子句进行连接操作。

示例：

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

补充：SQL99 标准多表查询

SQL99 标准引入了更清晰的语法来处理多表查询，使得查询语句更易于理解和维护。

示例：

SELECT e.employee_id, e.first_name, d.department_name, l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id;

【SQL99标准多表查询】

• 语法格式：

SELECT 字段...

FROM 表1 [AS] 别名 [连接类型]

JOIN 表2 [AS] 别名

ON 连接条件

WHERE 分组前筛选条件

GROUP BY 分组

HAVING 分组后筛选条件

ORDER BY 排序字段

【内连接】

• 语法格式：

SELECT 查询列表

FROM 表1 别名

INNER JOIN 表2 别名 ON 连接条件

INNER JOIN 表3 别名 ON 连接条件

[WHERE 筛选条件]

[GROUP BY 分组]

[HAVING 分组后筛选]

[ORDER BY 排序列表]

内连接查询案例

1）内连接 - 等值连接案例

例如：查询每个员工所在的部门名

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

例如：查询每个员工所在的部门名，使用别名

mysql> select e.name,d.dept_name from employees as e 
    -> inner join departments as d 
    -> on e.dept_id=d.dept_id;
+-----------+-----------+
| name      | dept_name |
+-----------+-----------+
| 梁伟      | 人事部    |
| 郭岩      | 人事部    |
| 李玉英    | 人事部    |
| 张健      | 人事部    |
| 郑静      | 人事部    |
| 杨金凤    | 法务部    |
+-----------+-----------+
133 rows in set (0.00 sec)

例如：查询11号员工的名字及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;
+-----------+------------+-------+
| name      | date       | total |
+-----------+------------+-------+
| 郭兰英    | 2018-01-10 | 18206 |
| 郭兰英    | 2018-02-10 | 19206 |
| 郭兰英    | 2018-03-10 | 18206 |
| 郭兰英    | 2018-04-10 | 19206 |
| 郭兰英    | 2018-05-10 | 18206 |
| 郭兰英    | 2018-06-10 | 19206 |
| 郭兰英    | 2018-07-10 | 27206 |
| 郭兰英    | 2018-08-10 | 27206 |
| 郭兰英    | 2018-09-10 | 19206 |
| 郭兰英    | 2018-10-10 | 21206 |
| 郭兰英    | 2018-11-10 | 22206 |
| 郭兰英    | 2018-12-10 | 25016 |
+-----------+------------+-------+
12 rows in set (0.00 sec)

例如：查询2018年每个员工的总工资，按工资升序排列

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;

例如：查询2018年总工资大于30万的员工，按工资降序排列

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;

2）内连接 - 非等值连接案例

① 创建数据表

• 语法格式：

CREATE TABLE 表名称

(

列名称1 数据类型,

列名称2 数据类型,

列名称3 数据类型,

....

);

例如：创建工资级别表

• - id：设为主键，仅作为表的行号
• - grade：工资级别，共ABCDE五类
• - low：该级别最低工资
• - high：该级别最高工资

mysql> use nsd2021;
mysql> create table wage_grade
    -> (
    -> id int,
    -> grade char(1),
    -> low int,
    -> high int,
    -> primary key (id)
    );
Query OK, 0 rows affected (0.00 sec)

② 向表中插入数据

• 语法格式：INSERT INTO 表名称 VALUES (值1, 值2,....);

例如：向wage_grade表中插入五行数据：

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);

例如：查询2018年12月员工基本工资级别

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;
+-------------+------------+-------+-------+
| employee_id | date       | basic | grade |
+-------------+------------+-------+-------+
|           1 | 2018-12-10 | 17016 | D     |
|           2 | 2018-12-10 | 20662 | E     |
|           3 | 2018-12-10 |  9724 | B     |
+-------------+------------+-------+-------+
120 rows in set (0.00 sec)

例如：查询2018年12月员工各基本工资级别的人数

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)

例如：查询2018年12月员工基本工资级别，员工需要显示姓名

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;
+-----------+------------+-------+-------+
| name      | date       | basic | grade |
+-----------+------------+-------+-------+
| 梁伟      | 2018-12-10 | 17016 | D     |
| 郭岩      | 2018-12-10 | 20662 | E     |
| 李玉英    | 2018-12-10 |  9724 | B     |
| 杨金凤    | 2018-12-10 |  6076 | A     |
+-----------+------------+-------+-------+
120 rows in set (0.00 sec)

3）内连接 - 自连接案例

将一张表作为两张表使用，每张表各起一个别名

例如：查看哪些员的生日月份与入职月份相同（同一张表中的两个字段，作为两张表使用）

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 |
+-----------+------------+------------+
| 李玉英    | 2012-01-19 | 1974-01-25 |
| 郑静      | 2018-02-03 | 1997-02-14 |
| 林刚      | 2007-09-19 | 1990-09-23 |
| 刘桂兰    | 2003-10-14 | 1982-10-11 |
| 张亮      | 2015-08-10 | 1996-08-25 |
| 许欣      | 2011-09-09 | 1982-09-25 |
| 王荣      | 2019-11-14 | 1999-11-22 |
+-----------+------------+------------+
7 rows in set (0.01 sec)

外连接查询案例

常用于查询一个表中有，另一个表中没有的记录

• 如果从表中有和它匹配的，则显示匹配的值
• 如果从表中没有和它匹配的，则显示NULL
• 外连接查询结果 = 内连接查询结果 + 主表中有而从表中没有的记录
• 左外连接中，left join左边的是主表
• 右外连接中，right join右边的是主表
• 左外连接和右外连接可互换，实现相同的目标

【左外连接】

• 语法格式：

SELECT tb1.字段..., tb2.字段

FROM table1 AS tb1

LEFT [OUTER] JOIN table2 AS tb2

ON tb1.字段=tb2.字段

例如：查询所有部门的人员，以及没有员工的部门

mysql> SELECT d.*,e.name
    -> from departments d
    -> left outer join employees e
    -> on d.dept_id = e.dept_id;
+---------+-----------+-----------+
| dept_id | dept_name | name      |
+---------+-----------+-----------+
|       1 | 人事部    | 梁伟      |
|       1 | 人事部    | 郭岩      |
|       1 | 人事部    | 李玉英    |
…
|       8 | 法务部    | 王荣      |
|       8 | 法务部    | 刘倩      |
|       8 | 法务部    | 杨金凤    |
|       9 | NULL      | NULL      |
+---------+-----------+-----------+
134 rows in set (0.00 sec)

【右外连接】

• 语法格式：

SELECT tb1.字段..., tb2.字段

FROM table1 AS tb1

RIGHT [OUTER] JOIN table2 AS tb2

ON tb1.字段=tb2.字段

例如：查询所有部门的人员，以及没有员工的部门（结果左外连接与相同）

mysql> SELECT d.*,e.name
    -> from employees e
    -> right outer join departments d
    -> on d.dept_id = e.dept_id;

【交叉连接】

- 返回笛卡尔积

• 格式：SELECT 字段名 FROM 表1 CROSS JOIN 表2 [WHERE子句]

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

扩展知识：

授予管理员root可以通过任意地址访问数据库，密码是NSD2021@tedu.cn。默认情况下，root只允许在本机访问；

mysql> grant all on *.* to root@'%' identified by 'NSD2021@tedu.cn';
# 解释：@后面是主机名，to后面是用户名，一般是root用户，by后面是mysql数据库的密码

报错信息：java.sql.SQLException: null, message from server: "Host '192.168.2.5' is not allowed to connect...

• 分析原因：表示该host不是远程对象，不能通过该对象远程访问数据库；
• 解决办法：授予远程访问数据库对象权限；如果尚明命令不生效，尝试以下办法：

a) 如果使用root@'主机名'的授权方式，在没有设置本机IP与主机名的映射，即使数据库知道IP，或者hostname单独的一个是不能远程连接Mysql数据库的，需要修改hosts文件添加主机名和IP的映射；

b) 不建议的方法，对任何远程的IP或者Hostname都允许远程连接，root@'%'的授权方式

c) 通过在配置好Hosts文件后，在JDBC的连接中输入IP或者Hostname都是可以访问的，单独设置主机和IP的映射；

例如：

mysql> grant all on *.* to root@'localhost' identified by 'NSD2021@tedu.cn';
mysql> grant all on *.* to root@'192.168.2.5' identified by 'NSD2021@tedu.cn';

Visualstudio数据库工具下载地址：Visual Studio Code - Code Editing. Redefined

思维导图：

小结：

本篇章节为【第四阶段】RDBMS1-DAY2 的学习笔记，这篇笔记可以初步了解到 常用函数（函数分类1：单行、分组；函数分类2：字符、数学、日期、流程控制）、分组查询group by、连接查询。

Tip：毕竟两个人的智慧大于一个人的智慧，如果你不理解本章节的内容或需要相关笔记、视频，可私信小安，请不要害羞和回避，可以向他人请教，花点时间直到你真正的理解。