DQL中的函数
# 官方函数链接
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format
4.1 单行函数
函数都是数据库提前给我们准备好的，所以我们可以直接调用，使用函数可以让指定的列计算出我们需要的数据
单行函数 : 指的是操作一行数据返回一行数据，操作10行数据返回10行数据
字符串函数
-- 长度
- select ename,length(ename) from emp;
-- 截取
- select ename,SUBSTR(ename,1,3) from emp;
- select * from emp where substr(ename,5,1)='S';
-- 大小写
- select ename, upper(ename),lower(ename) from emp;
-- 拼接
- select CONCAT(empno,'=',ename) from emp;
-- 替换
- select ename,REPLACE(ename,'T','—') from emp
日期函数
-- 获取当前系统时间
- select hiredate,sysdate() from emp;
- select hiredate,CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP() from emp;
-- 日期转换
- select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s')
- select hiredate, date_format(now(),'%Y年%m月%d日 %H时%i分%s秒') from emp;
-- 分别获取 年月日 时分秒 星期
- select
- SECOND MINUTE HOUR DAY WEEK MONTH YEAR
-- 日期的加减操作
- select hiredate,ADDDATE(hiredate,9),ADDDATE(hiredate,-9) from emp;
- select DATE('2022-05-02');
数值函数
-- 向上取整 向下取整
- select ceil(12.1),floor(12.9)
-- mod abs pow PI rand round TRUNCATE(直接进行截取，不进行四舍五入)
-- 保留多少位有效数字
- select round(1.4999999,2),round(1.4999999),round(1.4999999,-1)
- select TRUNCATE(1.4999999,2)
转换函数
-- 日期--》字符串
- date_format(date,expr)
- select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s');
-- 字符串--》日期
- 要注意字符串和格式的匹配
- select STR_TO_DATE('2020-4-16 17:15:24','%Y-%c-%d %H:%i:%s');
- select STR_TO_DATE('5月2022年4日','%m月%Y年%d日');
'5月2022年4日'
-- 数字--》字符串
- 直接拼接一个字符串即可，可以自动转换 lpad,concat
-- 字符串--》数字
- 依靠函数提供的参数
其他函数
-- 空值的处理
if null(exp1,exp2) exp1!=null?exp1:exp2
select IFNULL(comm,888) from emp;
-- 加密算法
select MD5('123456');
select AES_ENCRYPT('123456','abcd'),AES_DECRYPT(AES_ENCRYPT('123456','abcd'),'abcd');
4.2 多行函数
不管函数处理多少条，只返回一条记录
如果你的数据可以分为多个组，那么返回的数据条数和组数相同
每个部门的平均薪资
10 20 30 --> 3
常用的多行函数有5个
max 最大值: 如果处理的值是字符串，将会把值按照字典序排序
min 最小值: 如果处理的值是字符串，将会把值按照字典序排序
avg 平均值: 只能用于数值型数据，求平均值
sum 求和: 如果求和过程中有null，那么不会计算在内
count 求总数: 如果统计的数据中有null,不会把null统计在内
经典的错误
--查询公司最低薪资的员工是谁？
select min(sal) ,ename from emp;
mysql语法可行 -- 5.7之前可以 ,即使问题解决了 结果也是不对的
oracle不可行
将来工作的时候不能把普通列和组函数写在一起，虽然mysql语法不会报错，但是给的结果是错误的
4.3 数据分组
按照某一个条件进行分组，每一组返回对应的结果
group by 可以对指定的列进行分组，列尽量有相同的
having可以对分组之后的数据进行过滤，所以能出现在having中的比较项一定是被分组的列或者是组函数
底层(注意！！！)
where称之为行级过滤，处理的是表中每一行数据的过滤
having称之为组级过滤，处理的是分组之后的每一组数据
能使用where的，尽量不要使用having
--查询每种工作的平均薪资
--查询每个部门的最高薪资和最低薪资
--查询每个部门的人数和每月工资总数
--查询每个部门，每种工作的平均薪资
--查询个人姓名的平均薪资--尽量对多数据进行分组
--查询平均薪资高于2500的部门
--查询20部门的平均薪资（比较这两种，最终推荐第二种）
--查询10 20部门中,并且在二月份入职员工中，每个部门中平均薪资高于1500的工作是什么，并按照部门，工作平均薪资进行排序
--美观写法
4.4 DQL单表关键字执行顺序
select: 我们要显示那些列的数据
from: 从那张表中获取数据
where: 从表中获取数据的时候进行行级的数据过滤
group by: 对数据进行分组处理，一组获取对应的结果
having: 组级过滤，组级过滤的数据必须是分组条件或者是组函数
order by: 排序 asc desc
执行的顺序(面试题)
from --> where -->group by -->select -->having-->order by-->limit
4.5 多表查询
a. 查询的两张表如果出现同名的列，我们需要将表名标注到列名前面
b. 如果是非同名的列，表名可加可不加，推荐加上
为了书写方便，可以给表添加别名
一般情况下取首字母，特殊情况下取它所代表的含义
表的别名只在本次查询中生效
c. 如果表与表进行关联查询的时候，如果不添加关联条件，查询的总记录数就是a*b = 笛卡尔积
select * from emp,dept;
a 15 b 10 c 10 -->1500条
d. 多表查询的时候必须要加条件
等值
非等值
--查询每个员工所在的部门名称
--等值关联查询
--添加别名
4.6 表与表关联的方式
因为表的关联条件和业务查询条件放在了一起，为了防止混淆于是提供了下面三种方式
自然连接 natural join
-- 会自动选择列名相同并且类型相同的列
--查询薪资大于2000的员工姓名和部门名称
--自然连接
using
-- 不需要mysql帮我们选择等值连接的列，现在我们指定等值连接的列
---- 查询薪资大于2000的员工姓名和部门名称 using
on
-- 我们可以指定两张表关联的条件，可以是非等值的操作
----查询薪资大于2000的员工姓名和部门名称 using
--查询每个员工所对应的薪资等级
查询名字中带有A字母的员工姓名，部门名称和薪资等级
-- 第一种写法
-----------------------------------------
-- 第二种写法
4.7 表与表的外连接
当我们对两张表进行关联查询的时候，基于数据的原因导致其中一张表中的数据没办法被完全查询出来
外连接可以让没查询出来的数据也显示出来
因为我们写SQL的时候表总有左右之分 ,外连接也分为
左外连接：显示左面表所有的数据
右外连接：显示右面表所有的数据
--统计每个部门的人数
-------------------------全外连接
-------------------------Oracle的全外连接使用 Full Join
4.8 表与表的自连接
我们要查询的两个字段同时处于一张表上，我们只能将一张表当做含有不同意义的两张表去处理
给相同的表取不同的简称（按照所代表的含义去取）
--查询每个员工与其直属领导的名字
4.9 表与表的子连接(常用！！)
-- 把一个SQL语句的查询结果当成另外一个SQL语句的查询条件
-- 查询公司中谁的薪资高于平均薪资
-- 谁的薪资高于20部门员工的薪资
4.10 表与表的伪表查询 （常用！！）
如果我们所需要的查询条件 需要别的SQL语句提供
如果只需要一个条件，那么可以使用子查询来完成
如果需要多个查询条件，这是就要将所有的查询结果当做伪表进行管理
我们需要把一些含有特殊符号的列名设置别名，然后给伪表设置一个别名（见名知意）
-- 查询高于自己部门平均薪资的员工信息