Mysql（二）

Mysql常见函数、分组查询、连接查询

01 常见函数

#概念：类似Java中的方法/函数，将一组逻辑语句封装在方法中，对外暴露方法名，直接调用函数实现功能。
#Mysql中函数可以分成两种类型：
#1. 单行函数：对一行数据进行处理得到一个结果。如: concat、ifnull等。
#2. 分组函数：对多行数据进行处理得到一个结果，又叫统计函数、聚合函数、组函数
​
#单行函数
#单行函数可以分为：字符函数、数学函数、日期函数、其他函数、流程控制函数
#字符函数
# char_length 获取参数值的长度。
#案例：查询员工姓名，姓名字数
SELECT emplyee_name,CHARACTER_LENGTH(emplyee_name) 
FROM emplyees;
​
#concat 拼接字符串。
#案例：将字符串'aaa','bbb','ccc'进行拼接
SELECT CONCAT('aaa','bbb','ccc');
​
#upper、lower 大小写转换。
#案例：查询员工邮箱，并转为大写显示
SELECT UPPER(email) FROM emplyees;
​
# substr、substring 截取字符串。
# substr、substring作用一样，没有区别。
# substr(要截取的字符串, 开始位置, 截取长度)。
# 案例：查询员工电话号码前面3位数字
SELECT SUBSTR(phone_number,1,3) FROM emplyees;
​
# replace 替换。
# replace(要处理的字符串, 旧字符串, 新字符串)。
# 案例：查询员工电话号码，要求去除中间的横线。
SELECT REPLACE(phone_number, '-', '') FROM emplyees;
​
#数学函数
# round 四舍五入。
#案例：查询员工工资，并且四舍五入取整
SELECT salary,ROUND(salary) FROM emplyees;
#ROUND(salary,2)的第二位是取整的
SELECT salary,ROUND(salary,2) FROM emplyees;
​
​
​
#ceil 向上取整，返回>=该参数的最小整数。
#案例：查询员工工资，并且向上取整
SELECT salary,CEIL(salary) FROM emplyees;
​
# floor 向下取整，返回<=该参数的最小整数。
#案例：查询员工工资，并且向下取整
SELECT salary,FLOOR(salary) FROM emplyees;
​
# truncate 截断
SELECT TRUNCATE(1.999999,2); -- 1.99
​
# mod 取余
-- 取余的两种方法
SELECT MOD(10, 3),10%3;
​
#日期函数
# now 返回当前系统日期+时间
-- 使用SYSDATE()也可以
SELECT NOW(),SYSDATE();
# curdate 返回当前系统日期，不包括时间。
SELECT CURDATE();
# curtime 返回当前系统时间，不包括日期。
SELECT CURTIME();
# 获取年、月、日、时、分钟、秒
-- 查询员工入职年份
SELECT YEAR(hiredate) FROM emplyees;
SELECT YEAR('2020-01-01 10:20:30');#2020
SELECT MONTH('2020-01-01 10:20:30');#01
SELECT DAY('2020-01-02 10:20:30');#02
SELECT HOUR('2020-01-02 10:20:30');#10
SELECT MINUTE('2020-01-02 10:20:30');#20
SELECT SECOND('2020-01-02 10:20:30');#30
​
#date_format 将日期转化成字符串。
#date_format(date,format)用于格式化日期，其中date是要格式化的数据，format是格式化的格式，格式设置如下表
/*
格式符 功能
%Y 4位年份
%y 2位年份
%m 月份（01，02,…,11,12）
%c 月份（1,2,…,11,12）
%d 日（01，02，…）
%H 小时（24小时制）
%h 小时（12小时制）
%i 分钟（00，01，…,58,59）
%s 秒（00，01，…,58,59）
*/
#案例：查询员工姓名、入职时间，入职时间按照xxxx年xx月xx日输出
SELECT DATE_FORMAT(hiredate,'%Y年%m月%d日') FROM emplyees;
​
#datediff日期相减获取天数。
#案例：查询你在地球生活了多少天，假设生日是2000-01-01
SELECT DATEDIFF(NOW(),'2000-01-01');
​
#其他函数
-- 查询数据库版本
SELECT VERSION();
-- 查询当前使用的数据库名
SELECT DATABASE();
-- 查询当前连接的用户名
SELECT USER();
​
# if()函数。
#if(表达式1，表达式2，表达式3)函数类似三元表达式，如果表达式1为true返回表达式2，否则返回表达式3。
SELECT IF(2 > 1, '大', '小');
​
# case 类似swith case效果，等值判断。
SELECT
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或者语句n
end
#案例：查询员工姓名以及工资，工资按照一定规则发放，部门2的员工工资*1.2，部门3的员工工资*1.3，部门4的员工工资*1.4，其他部门不变
SELECT
emplyee_name,department_id,salary 原工资,
CASE department_id WHEN 2 THEN salary * 1.2
WHEN 3 THEN salary * 1.3
WHEN 4 THEN salary * 1.4
ELSE salary
END 新工资
FROM emplyees;
​
#case 类似 多重if的效果，可以做区间判断
SELECT
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或者语句n
end
#案例：查询员工姓名以及工资，工资按照一定规则发放，入职时间在2015-01-01之前的员工工资*2，入职时间在2018-01-01之前的员工工资*1.5，其他不变
SELECT
emplyee_name,hiredate,salary 原工资,
CASE 
WHEN hiredate < '2015-01-01' THEN salary * 2
WHEN hiredate < '2018-01-01' THEN salary * 1.3
ELSE salary
END 新工资
FROM emplyees;
​
#分组函数
#处理多行数据得到一个结果。用作统计使用，又称为聚合函数或统计函数或组函数。
#分类：sum求和、avg平均值、max最大值、min最小值、count计算个数。
#案例：查询所有员工工资总和、平均值、最大值、最小值、员工个数
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(*)
FROM emplyees;
​

02 分组查询

#使用group by 子句将表中的数据分成若干组
#语法
SELECT 查询列表，分组函数（字段）
FROM 表
[where 筛选条件]
GROUP BY 分组
[ORDER BY 排序]
​
 #按单个字段分组
 #案例：查询每个部门的最高工资
 SELECT MAX(salary) FROM emplyees GROUP BY department_id;
 
# 分组前筛选数据
#案例：查询每个部门入职时间在2010-01-01之后，并且工资最高的员工信息
SELECT department_id,MAX(salary) FROM emplyees
WHERE hiredate > '2010-01-01'
GROUP BY department_id;
​
#分组后筛选数据
#在分组之后的基础上进行筛选数据，使用HAVING关键字。
#案例：查询员工人数大于120的部门
SELECT department_id,COUNT(*) FROM emplyees
GROUP BY department_id
HAVING COUNT(*) > 120;
 
#按多个字段分组
#案例：查询每个部门，男女员工的平均工资
SELECT department_id,sex,AVG(salary) FROM emplyees
GROUP BY department_id,sex
ORDER BY department_id;
​

03 连接查询

#又称多表查询，当查询的字段来自于多个表时，就要用到连接查询
#   语法
-- sql92版本写法
SELECT 查询列表 FROM 表1,表2
-- sql99版本写法
SELECT 查询列表 FROM 表1 JOIN 表2
​
#笛卡尔乘积现象
#假设表1有n行数据，表2有m行数据，使用连接查询时，最后的结果会有 n * m 行
#解决方法：加上有效的连接条件
​
#案例：查询员工姓名以及所在的部门名称
-- sql92版本写法
SELECT emplyee_name,department_name
FROM emplyees,departments
WHERE emplyees.department_id = departments.department_id;
-- sql99版本写法
SELECT emplyee_name,department_name
FROM emplyees
INNER JOIN departments -- INNER可以省略
ON emplyees.department_id = departments.department_id;
-- 表格也能起别名
SELECT emplyee_name,department_name
FROM emplyees t1
JOIN departments t2
ON t1.department_id = t2.department_id;
​
#内连接
#内连接分成三种类型：等值连接、非等值连接、自连接
​
#等值连接
#案例：查询员工姓名以及所在的部门名称
-- 根据员工表和部门表的关系作等值连接
SELECT emplyee_name,department_name
FROM emplyees t1
JOIN departments t2
ON t1.department_id = t2.department_id;
​
#非等值连接
#案例：查询员工工资及工资等级
SELECT t1.salary,t2.grade_level
FROM emplyees t1
JOIN job_grades t2
ON t1.salary BETWEEN t2.lowest_sal AND t2.higest_sal;
​
#自连接
#案例：查询员工姓名以及对应的直系领导
SELECT t1.emplyee_name 员工,t2.emplyee_name 领导
FROM emplyees t1
JOIN emplyees t2
ON t1.manager_id = t2.emplyee_id;
​
/*
外连接
外连接分为左外连接、右外连接、全外连接。
​
外连接分成主表和从表，主表的信息会全部显示，再去连接从表，能连接上的就连接对应的信息，没有对应信息可以连接的，那就是null。
结果相当于是内连接的结果 + 主表有从表没有的记录。
​
怎么区分主表和从表？
左外连接：left join左边的是主表。
右外连接：right join右边的是主表
*/
​
#左(右)外连接
#左外和右外交换两个表的顺序，可以实现同样的效果
select 查询列表
from 表1 别名 [inner|left|right] -- inner内连接，默认值；left左连接；right右连接
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
​
#案例：查询员工姓名以及所在的部门名称，没有部门信息的员工也要查询出来
SELECT emplyee_name,department_name
FROM emplyees t1
LEFT JOIN departments t2
ON t1.department_id = t2.department_id;
​
#全外连接
#内连接的结果(交集部分) + 表1有表2没有的记录 + 表2有表1没有的记录。
#Mysql不支持全外连接
​
#交叉连接（一般没用）
#一般用来返回连接表的笛卡尔积
SELECT emplyee_name,department_name
FROM emplyees t1
CROSS JOIN departments t2;