SQL语句之函数
时间：2021-07-01 10:21:17
帮助过：21人阅读
# 打印入职年份
2 SELECT ename,DATE_FORMAT(hiredate,"
%Y")
AS year FROM t_emp;
3 # 查询1981年上半年入职的员工
4 SELECT empno,ename,hiredate
FROM t_emp
WHERE DATE_FORMAT(hiredate,"
%Y")
=1981 AND DATE_FORMAT(hiredate,"
%m")
<=6;
5 # 条件函数－＞中秋节发放礼品，SALES部门发放A，其余部门发放B
6 SELECT e.ename,d.dname,
IF(d.dname
="SALES","A","B")
AS gift
FROM t_emp e
JOIN t_dept d
ON e.deptno
=d.deptno
ORDER BY d.deptno;
7 # SALES部门去北京旅游，RESEARCH部门去上海旅游，ACCOUNTING部门去桂林旅游
8 SELECT e.ename,d.dname,(
CASE WHEN d.dname
="SALES"
THEN "BEIJING"
WHEN d.dname
="RESEARCH"
THEN "SHANGHAI"
ELSE "GUILIN"
END)
AS loc
FROM t_emp e
JOIN t_dept d
ON e.deptno
=d.deptno
ORDER BY d.dname;
9 # 贼多贼多条件的数据更新
10 UPDATE t_emp e
LEFT JOIN t_dept d
ON e.deptno
=d.deptno
JOIN (
SELECT deptno,
AVG(sal)
avg FROM t_emp
GROUP BY deptno) t
ON e.deptno
=d.deptno
SET e.sal
=
11 (
CASE
12 WHEN d.dname
="SALES"
AND DATEDIFF(NOW(),e.hiredate)
/365>=20 THEN e.sal
*(
1+0.1)
13 WHEN d.dname
="SALES"
AND DATEDIFF(NOW(),e.hiredate)
/365<20 THEN e.sal
*(
1+0.05)
14 WHEN d.dname
="ACCOUNTING"
THEN e.sal
+300
15 WHEN d.dname
="RESEARCH"
AND e.sal
<t.
avg THEN e.sal
+200
16 WHEN e.deptno
IS NULL THEN e.sal
+100
17 ELSE NULL
18 END)
数字函数，日期函数，字符函数，条件函数
SQL语句之函数
标签：sha shanghai hang 中秋 account select 条件 search 语句
