【從零開始學習 MySql 資料庫】(2) 函式

DylanAndroid發表於2019-02-26
MySql資料庫函式
#函式
use test;

#檢視員工表資料
SELECT * FROM emp_test;

#檢視部門表資料
SELECT * FROM dept_test;

#round( 數字 , 小數點後的位數 )用於數字的四捨五入
#計算金額的四捨五入
#注意：Oracle中別名用雙引號"原樣輸出"，mysql可以用單引號
SELECT salary*0.1234567 "原樣輸出",ROUND(salary*0.1234567) `預設零位小數`, ROUND(salary*0.1234567,2) `保留兩位小數` FROM emp_test;

#trunc( 數字 , 小數點後的位數 )用於擷取如果沒有第二個引數 , 預設是 0

#計算金額 , 末尾不做四捨五入
#意：Oracle中擷取用關鍵字TRUNC，MySql用TRUNCATE
SELECT salary*0.1234567 `原樣輸出`,TRUNCATE(salary*0.1234567,2) `直接擷取留兩位小數`  FROM emp_test;



#計算員工入職多少天?
#mysql計算時間差用函式DATEDIFF(expr1,expr2),Oracle用(expr1,expr2)DAYS
SELECT name,hire_date, TO_DAYS(NOW())-TO_DAYS(hire_date) FROM emp_test;
SELECT name,hire_date, DATEDIFF(NOW(),hire_date) FROM emp_test;
SELECT name,hire_date, TIMESTAMPDIFF(DAY,hire_date,NOW()) FROM emp_test;

#計算員工入職多少個月？
#mysql用TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2),Oracle用MONTHS_BETWEEN(date1, date2)
SELECT name, TIMESTAMPDIFF(MONTH,hire_date,NOW()) FROM emp_test;

#計算 12 個月之前的時間點
#Oracle (date, int) *計算 12 個月之前的時間點,Myusql用DATE_ADD(date,INTERVAL expr unit)
SELECT DATE_ADD(SYSDATE(),INTERVAL -12 MONTH) FROM DUAL;

#計算本月的最後一天
SELECT LAST_DAY(NOW()) FROM DUAL;

#把時間資料按指定格式輸出
SELECT DATE_FORMAT(NOW(),`%Y-%c-%d %h:%i:%s`) FROM DUAL;

#插入一條資料 , 編號為 1012 , 姓名為 amy , 入職時間為當前系統時間
INSERT INTO emp_test(emp_id,name,hire_date) VALUES (1012,`amy`,SYSDATE()); 
SELECT * FROM emp_test;

#插入一條資料 , 編號為 1012 , 姓名為 amy , 入職時間為 2011 年 10 月 10 日
INSERT INTO emp_test(emp_id,name,hire_date) VALUES (1012,`amy`,DATE_FORMAT(`2011-10-10`,`%Y-%c-%d`)); 
SELECT * FROM emp_test;

#按指定格式顯示員工姓名和入職時間 , 顯示格式為： amy 2011-10-10
SELECT name,DATE_FORMAT(hire_date,`%Y-%c-%d`) FROM emp_test;

#計算員工的年終獎金
#要求：
#1)  如果 bonus 不是 null , 發年終獎金額為 bonus
#2)  如果 bonus 是 null , 發年終獎金額為 salary * 0.5
#3)  如果 bonus 和 salary 都是 null, 發 100 元安慰一下
#coalesce( 引數列表 )函式的作用：返回引數列表中第一個非空引數 , 引數列表中最後一個值通常為常量
SELECT COALESCE(bonus,salary*0.5,100) FROM emp_test;

#根據員工的職位 , 計算加薪後的薪水資料
#要求：
#1)  如果職位是 Analyst：加薪 10%
#2)  如果職位是 Programmer：加薪 5%
#3)  如果職位是 clerk：加薪 2%
#4)  其他職位：薪水丌變
SELECT name,job,salary ,
            CASE job WHEN `Analyst` THEN  salary*1.1
                              WHEN `Programmer` THEN  salary*1.05
                              WHEN `clerk` THEN  salary*1.02
                             ELSE salary
            END new_salary
            FROM emp_test;

#薪水由低到高排序( 升序排列 )
SELECT name,salary FROM emp_test ORDER BY salary;

#薪水由高到低排序( 降序排列 )
SELECT name,salary FROM emp_test ORDER BY salary DESC;

#按入職時間排序 , 入職時間越早排在前面
SELECT name,hire_date FROM emp_test ORDER BY hire_date ASC;

#按部門排序 , 同一部門按薪水由高到低排序
SELECT name,dept_test_id,salary FROM emp_test ORDER BY dept_test_id,salary;

#員工表中有多少條記錄？
SELECT COUNT(*) FROM emp_test;

#入職時間不是 null 的資料總數
SELECT COUNT(hire_date) FROM emp_test WHERE hire_date IS NOT NULL;

#計算員工的薪水總和是多少？
SELECT SUM(salary) FROM emp_test;

#計算員工的人數總和、薪水總和、平均薪水是多少？
SELECT COUNT(*), SUM(salary),AVG(salary) FROM emp_test;

#薪水平均值 = 薪水總和 / 人數總和 avg(salary) = sum(salary) / count(*)
#而 avg(salary)叧按有薪水的員工人數計算平均值。這樣得到的資料丌夠準確。
SELECT COUNT(*), SUM(salary),AVG(IFNULL(salary,0)) FROM emp_test;

#計算員工的最高薪水和最低薪水
SELECT max(salary),min(salary) FROM emp_test;
#組函式：
   #count / avg / sum / max / min 如果函式中寫列名 , 預設忽略空值
 # avg / sum 針對數字的操作
 # max / min 對所有資料型別都可以操作

#按部門計算每個部門的最高和最低薪水分別是多少？
SELECT dept_test_id,max(salary),min(salary) FROM emp_test GROUP BY dept_test_id;

#計算每個部門的 薪水總和 和 平均薪水？
SELECT dept_test_id,SUM(salary),AVG(IFNULL(salary,0)) FROM emp_test GROUP BY dept_test_id;

#每個部門的統計資訊：
#要求格式如下：
#deptno max_s min_s sum_s avg_s emp_num
#10 10000 5000 23000 6789 3
SELECT dept_test_id deptno,
                max(salary) max_s,
                min(salary) min_s,
                SUM(salary) sum_s,
                AVG(IFNULL(salary,0)) avg_s, 
                COUNT(*) emp_num
FROM emp_test GROUP BY dept_test_id;

#按職位分組 , 每個職位的最高、最低薪水和人數？
SELECT MAX(salary),MIN(salary),COUNT(*) emp_num FROM emp_test GROUP BY job order by emp_num;

#having 子句用於對分組後的資料進行過濾。
#注意區別 where 是對錶中資料的過濾 ;having 是對分組得到的結果資料進一步過濾
#平均薪水大於 5000 元的部門資料 , 沒有部門的不算在內？
SELECT  dept_test_id,AVG(IFNULL(salary,0)) avg_salary  FROM emp_test WHERE dept_test_id IS NOT NULL GROUP BY dept_test_id HAVING avg_salary>5000;

#薪水總和大於 20000 元的部門資料？
SELECT dept_test_id, SUM(salary) FROM emp_test WHERE dept_test_id IS NOT NULL GROUP BY dept_test_id HAVING SUM(salary)> 20000;

#哪些職位的人數超過 2 個人？
SELECT job,COUNT(*) FROM emp_test GROUP BY job HAVING COUNT(*)>2;

#查詢最高薪水的是誰？
SELECT * FROM emp_test WHERE salary=(SELECT MAX(salary) FROM emp_test);
SELECT *,MAX(salary) FROM emp_test複製程式碼

相關文章