MySQL数据库（4）
目录
日期函数
在我们的数据库中，难免要存放日期，比如我们要存储，开始日期和结束日期，在设计数据库的时候，一遍都会有这么两个字段，一个是开始时间，还有一个是修改时间，还有比如我们在收集一个人的信息的时候，在收集到出生年月的时候，也会使用到日期类型
细节
DATE ADD()中的 interval 后面可以是 year minute second day 等
DATE SUB()中的 interval 后面可以是 year minute second hour day等
DATEDIFF(date1,date2) 得到的是天数，而且是date1-date2 的天数，因此可以取负数
这四个函数的日期类型可以是 date,datetime 或者 timestamp
代码演示：
-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
#DUAL表示亚元表 是系统表
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME ( ) 当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 创建测试表 信息表
CREATE TABLE mes(
id INT ,
content VARCHAR(30),
send_time DATETIME);
-- 添加一条记录
INSERT INTO mes
VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL;
-- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id, content, DATE(send_time)
FROM mes;
-- 请查询在10分钟内发布的新闻, 思路一定要梳理一下.
#表示从发送的时间(send_time)基础上 加了10分钟 如果大于当前时间 就表示在10分钟内
SELECT *
FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
#这句话表示 当前的时间（NOW()） 减去10分钟之后 如果小于发送的时间（send_time）也可以表示在10分钟内发送
SELECT *FROM mes WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE)<=send_time
-- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL
-- 请用mysql 的sql语句求出你活了多少天? [练习] 2003-03-03 出生
SELECT DATEDIFF(NOW(), '2003-03-03')/365 FROM DUAL;
-- 如果你能活80岁，求出你还能活多少天.[练习] 2003-03-03 出生
-- 先求出活80岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 2003-03-03->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '2003-03-03' 可以date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('2003-03-03', INTERVAL 80 YEAR),NOW()) FROM DUAL
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
--
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;
SELECT * FROM mysql.user G 加密系统函数
我们知道，在记录用户的登录信息和密码的时候，比如我们注册的账号密码，都是存放在一个数据库中的，但是我们真的就把数据进来的密码，直接存放在数据库中吗，和明显这不显示，如果直接存放在数据库中，万一数据库被入侵，导致数据泄露，所以我们一般存放在数据库中的 密码都是经过加密的，常见的加密格式有md5 password加密
代码演示：
-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
-- DATABASE() 查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('yjw') FROM DUAL;
SELECT LENGTH(MD5('yjw')) FROM DUAL;
-- 演示用户表，存放密码时，是md5
CREATE TABLE hsp_user
(id INT ,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
VALUES(100, 'jack', MD5('yjw'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL注入问题
WHERE `name`='jack' AND pwd = MD5('yjw')
-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密
SELECT PASSWORD('yjw') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user G 从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user
流程控制函数
所谓流程控制函数，也就是和Java中的if语句差不多
如果是if()后面有三个表达式
IF(expr1,expr2,expr3) 如果expr1为True ,则返回 expr2 否则返回 expr3
这就有点类似于Java中的三元运算符了，只不过语法形式不同
# IFNULL(expr1,expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2
ifnull函数是判断是否为空，如果为空返回第二个表达式的值，如果不为空就返回第一个表达式的值
代码演示：
# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果expr1为True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL( NULL, '明天你好') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果expr1 为TRUE,则返回expr2,如果expr2 为t, 返回 expr4, 否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom'
ELSE 'mary' END
-- 1. 查询emp 表, 如果 comm 是null , 则显示0.0
-- 老师说明，判断是否为null 要使用 is null, 判断不为空 使用 is not
#表示 comm 如果为空 那么返回 第一个表达式的值 否则返回第二个表达式的值
SELECT ename, IF(comm IS NULL , 0.0, comm)
FROM emp;
#表示 comm如果为空就继续显示原来的值 如果为空 就显示0.0
SELECT ename, IFNULL(comm, 0.0)
FROM emp;
-- 2. 如果emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job'
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
单表查询加强
where子句加强 orderby子句加强
代码演示：
-- 查询加强
-- ■ 使用where子句
-- ?如何查找1992.1.1后入职的员工
-- 老师说明： 在mysql中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
WHERE hiredate > '1992-01-01'
-- ■ 如何使用like操作符(模糊)
-- %: 表示0到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%'
-- ?如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'
-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- ■ 查询表结构
DESC emp
-- 使用order by子句
-- ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
ORDER BY sal
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;
分页查询
代码演示：
-- 分页查询
-- 按雇员的id号升序取出， 每页显示3条记录，请分别显示 第1页，第2页，第3页
-- 第1页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3;
-- 第2页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3;
-- 第3页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
-- 推导一个公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job;
-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;
groupby子句加强
代码演示：
-- 增强group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)，如果该列的值为null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)
FROM emp
-- 扩展要求：统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
FROM emp;
SELECT * FROM e
mp;
SELECT * FROM dept;
-- 应用案例：请统计各个部门group by 的平均工资 avg，
-- 并且是大于1000的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno #先按部门进行分组
HAVING avg_sal > 1000#在使用having过滤出 平均工资大于1000的
ORDER BY avg_sal DESC
LIMIT 0,2
数据分组总结
代码演示：
-- 增强group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)，如果该列的值为null, 是
-- 不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)
FROM emp
-- 扩展
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
FROM emp;
SELECT * FROM e
mp;
SELECT * FROM dept;
-- 应用案例：请统计各个部门group by 的平均工资 avg，
-- 并且是大于1000的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno #先按部门进行分组
HAVING avg_sal > 1000#在使用having过滤出 平均工资大于1000的
ORDER BY avg_sal DESC
LIMIT 0,2