-- 1.單行函數(shù)
-- 每一行數(shù)據(jù)都會做為參數(shù)楼誓，得到自己對應的結(jié)果
-- 1.1 文本函數(shù)
-- 1.1.1 CHAR_LENGTH(s)返回文本s的字符（中文，英文名挥，數(shù)字疟羹，符號都算一個字符）個數(shù)（長度）
-- 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) 將參數(shù)s全部轉(zhuǎn)換為小寫/大寫字母
-- 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'的數(shù)據(jù)
SELECT *
FROM emp
WHERE SUBSTR(job,CHAR_LENGTH(job)-2,3) = 'MAN';

-- 1.1.4 TRIM(s) 去掉文本s兩側(cè)多余的空格
-- MySQL和Oracle的用法一致

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

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

-- 1.2 數(shù)值函數(shù)
-- 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相除后的余數(shù)
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 日期函數(shù)
-- 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)：計算兩個日期之間的天數(shù) d1早于d2得到負數(shù)
-- MySQL DATEDIFF(d1,d2) 計算兩個日期之間的天數(shù)
-- Oracle MONTHS_BETWEEN(d1,d2) 計算兩個日期之間的月數(shù)
-- d1-d2 得到就是兩個日期之間的天數(shù)差
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);

-- 練習：假設(shè)emp表中所有員工在入職6個月后轉(zhuǎn)正
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')

-- 練習：計算當前月份的倒數(shù)第三天
SELECT DATE_ADD(LAST_DAY(CURDATE()),INTERVAL -2 DAY)

-- 1.4 轉(zhuǎn)換函數(shù)
-- 1.4.1 STR_TO_DATE(s, fmt) 將文本s按照fmt格式轉(zhuǎn)換成日期類型
-- MySQL: STR_TO_DATE(s, fmt)
-- Oracle: TO_DATE(s, fmt)

-- fmt是一種文本畏邢，用于描述日期的格式
-- MySQL:
-- year: %Y 四位數(shù)年
-- month: %m 數(shù)字表示月份
-- day: %d 數(shù)字表示天數(shù)
-- hour: %H 24進制小時的數(shù)字表示
-- minute: %i 數(shù)字表示分鐘
-- second: %s 數(shù)字表示秒
-- week: %w 數(shù)字表示星期（0=星期日, 6=星期六）
-- %Y-%m-%d

-- Oracle:
-- year: yyyy 四位數(shù)年
-- month: mm 數(shù)字表示月份
-- day: dd 數(shù)字表示天數(shù)
-- hour: HH24 24進制小時的數(shù)字表示
-- minute: mi 數(shù)字表示分鐘
-- second: ss 數(shù)字表示秒
-- 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格式轉(zhuǎn)換成文本類型
-- 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 其他函數(shù)
-- ifnull(expr1,expr2): 當expr1表達式的結(jié)果為null時程储，使用expr2的結(jié)果
-- MySQL：ifnull(expr1,expr2)
-- Oracle：nvl(expr1,expr2)
-- nvl2(expr1,expr2,expr3): 當expr1表達式的結(jié)果為null時，使用expr2的結(jié)果臂寝，否則使用expr3的結(jié)果

-- 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.分組函數(shù)
-- 多行數(shù)據(jù)（一組數(shù)據(jù)）作為參數(shù)败徊，得到一個結(jié)果
-- 2.1 求和 sum(x) 對x列的數(shù)據(jù)進行求和計算，得到求和結(jié)果,x應該是表示數(shù)值列
SELECT SUM(sal)
FROM emp;
-- 2.2 平均 avg(x) 對x列的數(shù)據(jù)進行平均計算掏缎，得到平均值,x應該是表示數(shù)值列
SELECT AVG(sal)
FROM emp;

SELECT SUM(sal),AVG(sal)
FROM emp;
-- 2.3 計數(shù) count(x): 統(tǒng)計記錄數(shù)量,重復的數(shù)據(jù)會重復計算
-- 查詢月薪大于3000的員工數(shù)量
SELECT COUNT(*)
FROM emp
WHERE sal > 3000;

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

-- 查詢10號部門所有員工的平均月薪
SELECT AVG(sal)
FROM emp
WHERE deptno = 10;

-- 分組概念： 新的句式： group by x 以x列的數(shù)據(jù)分組統(tǒng)計數(shù)據(jù)
-- 書寫順序： select...from...where...group by...having...order by...
-- 執(zhí)行順序: from...where...group by...having...select...order by...
-- 查詢各個部門員工的平均月薪
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;
-- 只有在group by中出現(xiàn)的列皱蹦，才可以書寫在select中，否則在MySQL沒有意義
-- 在Oracle中直接報錯
-- 錯誤演示:ename在一個部門中沒有代表性
SELECT deptno,ename,AVG(sal)
FROM emp
GROUP BY deptno;

-- 查詢各部門中各職位的平均月薪
-- 在部門分組的前提下眷蜈，繼續(xù)對職位進行分組
SELECT deptno, job, AVG(sal)
FROM emp
GROUP BY deptno, job

-- 查詢平均月薪高于3000的部門編號和平均月薪
-- having: 專門用于對分組函數(shù)進行條件篩選

-- 錯誤演示：where在group by之前執(zhí)行沪哺，無法執(zhí)行分組函數(shù)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;

-- 相關(guān)子查詢：外部查詢一條數(shù)據(jù)，內(nèi)部查詢執(zhí)行一次
SELECT *
FROM emp e
WHERE e.sal > (SELECT AVG(sal) FROM emp e1 WHERE e1.deptno = e.deptno)

-- 創(chuàng)建視圖
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;