3

-- 1.单行函数
-- 每一行数据都会做为参数，得到自己对应的结果
-- 1.1 文本函数
-- 1.1.1 CHAR_LENGTH(s)返回文本s的字符（中文，英文，数字，符号都算一个字符）个数（长度）
-- MySQL: CHAR_LENGTH(s) Oracle: LENGTH(s)
SELECT ename, CHAR_LENGTH(ename)
FROM emp;

-- 练习：查询emp表中姓名字符长度超过4个的员工信息
SELECT *
FROM emp
WHERE CHAR_LENGTH(ename) > 4;

-- 1.1.2 CONCAT(s1,s2...sn) 将s1，s2...sn拼接在一起形成一个最终文本
-- MySQL: CONCAT(s1,s2...sn)
-- Oracle：CONCAT(s1,CONCAT(s2,s3)) 还可以 s1 || s2 || s3 ||...sn
-- 查询员工姓名和月薪，显示的格式为'XXX的月薪是XXXXX'
SELECT CONCAT(ename, '的月薪是' ,sal)
FROM emp;

SELECT CONCAT(ename, '的月薪是' ,IFNULL(comm,0))
FROM emp;

-- 练习：查询员工姓名和入职日期，显示的格式为'XXX在XXXXX入职'
SELECT CONCAT(ename, '在', hiredate, '入职')
FROM emp;

-- 1.1.3 LOWER(s)和UPPER(s) 将参数s全部转换为小写/大写字母
-- MySQL和Oracle的用法一致
-- Oracle中还有 INITCAP(s) 将s中所有单词的首字母变成大写，其余小写
SELECT adress, LOWER(adress), UPPER(adress)
FROM locs;

-- 1.1.4 SUBSTR(s, start, length) 截取文本s，从start位置起，截取length个字符
-- MySQL和Oracle的用法一致 start的位置都是从1开始
SELECT ename, SUBSTR(ename,1,2)
FROM emp;
-- 查询emp表中job项最后三个字符是'MAN'的数据
SELECT *
FROM emp
WHERE SUBSTR(job,CHAR_LENGTH(job)-2,3) = 'MAN';

-- 1.1.4 TRIM(s) 去掉文本s两侧多余的空格
-- MySQL和Oracle的用法一致

SELECT ' abc def ', CHAR_LENGTH(' abc def ')

SELECT ' abc def ', CHAR_LENGTH(TRIM(' abc def '))

-- 1.2 数值函数
-- 1.2.1 CEIL(n)和FLOOR(n)和ROUND(n,m)
-- 进一法，退一法，四舍五入
SELECT CEIL(3.14), FLOOR(3.99), ROUND(12.5), ROUND(-12.5), ROUND(-12.4)

SELECT ROUND(12.15,1),ROUND(12.15,-1),ROUND(15.5,-2),ROUND(55.5,-2)
-- 1.2.2 MOD(x,y)计算x和y相除后的余数
SELECT MOD(5,3),MOD(5,-3),MOD(-5,3),MOD(-5,-3)
-- 1.2.3 POW(x,y)和SQRT(x) 计算x的y次幂 对x进行开方
SELECT POW(2,3), SQRT(16)

-- 1.3 日期函数
-- 1.3.1 CURDATE(),CURTIME(),NOW() 分别获得当前的日期，时间，完整日期时间
-- MySQL：CURDATE(),CURTIME(),NOW()
-- Oracle：sysdate select sysdate form dual; 相当于MySQL的select now()
-- dual表是一张空表，oracle中书写select必须书写from,dual表补全SQL语句
SELECT CURDATE(),CURTIME(),NOW()

SELECT SYSDATE();
SELECT SYSDATE() FROM DUAL;

-- 1.3.2 DATEDIFF(d1,d2)：计算两个日期之间的天数 d1早于d2得到负数
-- MySQL DATEDIFF(d1,d2) 计算两个日期之间的天数
-- Oracle MONTHS_BETWEEN(d1,d2) 计算两个日期之间的月数
-- d1-d2 得到就是两个日期之间的天数差
SELECT DATEDIFF('2019-07-10','2019-07-20')

SELECT DATEDIFF(NOW(),'1997-07-19')/365

SELECT ename, hiredate, DATEDIFF(CURDATE(),hiredate)/365
FROM emp;

-- 1.3.3 DATE_ADD(d, INTERVAL n TYPE)：为时间d追加n个时间单位
-- TYPE: YEAR,MONTH,DAY,HOUR,MINUTE,SECOND
SELECT CURDATE(), DATE_ADD(CURDATE(),INTERVAL 1 YEAR), DATE_ADD(CURDATE(),INTERVAL 13 MONTH);

-- 练习：假设emp表中所有员工在入职6个月后转正
SELECT ename, hiredate, DATE_ADD(hiredate, INTERVAL 6 MONTH)
FROM emp;

-- 1.3.4 LAST_DAY(d) 计算d日期所在月份的最后一天
SELECT LAST_DAY(CURDATE())
SELECT LAST_DAY('2012-02-14')

-- 练习：计算当前月份的倒数第三天
SELECT DATE_ADD(LAST_DAY(CURDATE()),INTERVAL -2 DAY)

-- 1.4 转换函数
-- 1.4.1 STR_TO_DATE(s, fmt) 将文本s按照fmt格式转换成日期类型
-- MySQL: STR_TO_DATE(s, fmt)
-- Oracle: TO_DATE(s, fmt)

-- fmt是一种文本，用于描述日期的格式
-- MySQL:
-- year: %Y 四位数年
-- month: %m 数字表示月份
-- day: %d 数字表示天数
-- hour: %H 24进制小时的数字表示
-- minute: %i 数字表示分钟
-- second: %s 数字表示秒
-- week: %w 数字表示星期（0=星期日, 6=星期六）
-- %Y-%m-%d

-- Oracle:
-- year: yyyy 四位数年
-- month: mm 数字表示月份
-- day: dd 数字表示天数
-- hour: HH24 24进制小时的数字表示
-- minute: mi 数字表示分钟
-- second: ss 数字表示秒
-- yyyy-mm-dd

SELECT LAST_DAY(STR_TO_DATE('02-14/2012','%m-%d/%Y'))
SELECT LAST_DAY(STR_TO_DATE('2012年02月14日','%Y年%m月%d日'))

-- 1.4.2 DATE_FORMAT(d, fmt) 将日期d按照fmt格式转换成文本类型
-- MySQL: DATE_FORMAT(d, fmt)
-- Oracle: TO_CHAR(d, fmt)
-- 查询员工姓名和入职日期，按照'xxx的入职日期是xxxx年xx月xx日'显示

SELECT CONCAT(ename,'的入职日期是', DATE_FORMAT(hiredate,'%Y年%m月%d日'))
FROM emp;

-- 练习：查询员工姓名，入职日期，入职日期的格式为"xx月xx日xxxx年"
SELECT ename, DATE_FORMAT(hiredate,'%m月%d日%Y年')
FROM emp;

-- 1.5 其他函数
-- ifnull(expr1,expr2): 当expr1表达式的结果为null时，使用expr2的结果
-- MySQL：ifnull(expr1,expr2)
-- Oracle：nvl(expr1,expr2)
-- nvl2(expr1,expr2,expr3): 当expr1表达式的结果为null时，使用expr2的结果，否则使用expr3的结果

-- 1.查询名字第二个字符是O的员工姓名，入职日期，显示格式为'XXXX的入职日期是XXXXXX'
SELECT CONCAT(ename,'的入职日期是', hiredate)
FROM emp
WHERE SUBSTR(ename,2,1) = 'O' ;
-- 2.计算员工姓名和他的年收入，将年收入四舍五入精确至万位
SELECT ename, ROUND((sal+IFNULL(comm,0))*12,-4)
FROM emp;
-- 3.查询在‘1994年5月1日’至‘1994年9月15日’期间入职的员工信息
SELECT *
FROM emp
WHERE hiredate BETWEEN STR_TO_DATE('1994年5月1日','%Y年%m月%d日' ) AND STR_TO_DATE('1994年9月15日','%Y年%m月%d日' );

-- 2.分组函数
-- 多行数据（一组数据）作为参数，得到一个结果
-- 2.1 求和 sum(x) 对x列的数据进行求和计算，得到求和结果,x应该是表示数值列
SELECT SUM(sal)
FROM emp;
-- 2.2 平均 avg(x) 对x列的数据进行平均计算，得到平均值,x应该是表示数值列
SELECT AVG(sal)
FROM emp;

SELECT SUM(sal),AVG(sal)
FROM emp;
-- 2.3 计数 count(x): 统计记录数量,重复的数据会重复计算
-- 查询月薪大于3000的员工数量
SELECT COUNT(*)
FROM emp
WHERE sal > 3000;

-- 2.4 最大 Max(x) 求得x列中的最大值
-- 2.5 最小 min(x) 求得x列中的最小值
-- 查询月薪最高数和月薪最低数
SELECT MAX(sal), MIN(sal)
FROM emp

-- 查询10号部门所有员工的平均月薪
SELECT AVG(sal)
FROM emp
WHERE deptno = 10;

-- 分组概念： 新的句式： group by x 以x列的数据分组统计数据
-- 书写顺序： select...from...where...group by...having...order by...
-- 执行顺序: from...where...group by...having...select...order by...
-- 查询各个部门员工的平均月薪
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;
-- 只有在group by中出现的列，才可以书写在select中，否则在MySQL没有意义
-- 在Oracle中直接报错
-- 错误演示:ename在一个部门中没有代表性
SELECT deptno,ename,AVG(sal)
FROM emp
GROUP BY deptno;

-- 查询各部门中各职位的平均月薪
-- 在部门分组的前提下，继续对职位进行分组
SELECT deptno, job, AVG(sal)
FROM emp
GROUP BY deptno, job

-- 查询平均月薪高于3000的部门编号和平均月薪
-- having: 专门用于对分组函数进行条件筛选

-- 错误演示：where在group by之前执行，无法执行分组函数AVG
SELECT deptno, AVG(sal)
FROM emp
WHERE AVG(sal) > 3000
GROUP BY deptno;

-- 正确演示
SELECT deptno, AVG(sal)
FROM emp
WHERE hiredate < CURDATE()
GROUP BY deptno
HAVING AVG(sal) BETWEEN 3000 AND 4000;

-- 相关子查询：外部查询一条数据，内部查询执行一次
SELECT *
FROM emp e
WHERE e.sal > (SELECT AVG(sal) FROM emp e1 WHERE e1.deptno = e.deptno)

-- 创建视图
CREATE VIEW emp_view
AS
SELECT e.*,d.dname,d.loc
FROM emp e LEFT JOIN dept d ON(e.deptno = d.deptno)

-- 使用视图
SELECT * FROM emp_view;

-- 修改视图
CREATE OR REPLACE VIEW emp_view
AS
SELECT e.*,d.dname,l.loc,l.adress,l.country
FROM emp e
LEFT JOIN dept d ON(e.deptno = d.deptno)
LEFT JOIN locs l ON(d.loc = l.loc);

-- 删除视图
DROP VIEW emp_view;

SELECT t.ename, t.dname, t.adress
FROM emp_view t;

CREATE OR REPLACE VIEW emp10
AS
SELECT empno,ename,job,mgr,hiredate,deptno FROM emp WHERE deptno = 10;