主要内容：
常用函数（函数分类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：毕竟两个人的智慧大于一个人的智慧，如果你不理解本章节的内容或需要相关笔记、视频，可私信小安，请不要害羞和回避，可以向他人请教，花点时间直到你真正的理解。
![[Redis][哨兵][下]详细讲解](https://i-blog.csdnimg.cn/direct/2d406f662b6c448e8ab37cd9c233808e.png)
