USE ex01;
#演示流程控制语句,if(1,2,3)如果1正确,返回2,反之返回3
SELECT IF(TRUE,'北京','上海') FROM DUAL
#ifnull(1,2)如果1不为null,那么返回1,反之返回2
SELECT IFNULL(NULL,2)
#类似多重分支,但只能返回一个值,如果a对返回b,a错看c对不对,对返回d,反之返回e
#select CASE WHEN 'a' THEN 'b' WHEN 'c' THEN 'd' ELSE 'e' END
SELECT CASE WHEN FALSE THEN 'jack' WHEN FALSE THEN 'tom' ELSE 'mary' END
#判断是否为null,要用is null,不为空用is not
SELECT ename,IF(comm IS NULL,'职员','长官') AS '级别' FROM emp
#
SELECT ename,(SELECT CASE WHEN mgr=7698 THEN '帅哥' WHEN mgr=7839 THEN '猪脚' WHEN mgr=7566 THEN '美女' ELSE '老六' END)
AS '评价' FROM emp;
SELECT *FROM emp
SELECT *FROM salgrade
SELECT *FROM dept
#查询加强
SELECT ename,hiredate FROM emp WHERE hiredate>'1992-01-01'
SELECT ename,hiredate FROM emp WHERE DATEDIFF(hiredate,'1992-01-01')>0
#like模糊查询
#--%表示0到多个任意字符 _表示单个任意字符
#如何表示姓小的员工
SELECT*FROM emp WHERE ename LIKE '小%'
#
SELECT *FROM emp WHERE ename LIKE '__帝'
#查看没有上级的雇员
SELECT *FROM emp WHERE mgr IS NULL
#查看表的结构
DESC emp
#order by emp表中把部门编号升序,部门中工资降序
SELECT ename,sal,depton FROM emp ORDER BY depton ASC,sal DESC
#分页查询
#LIMIT(a,b)从第a+1个记录开始取,取b个
#第一页
SELECT*FROM emp ORDER BY empno LIMIT 0,5
#第二页
SELECT*FROM emp ORDER BY empno LIMIT 5,5
#第三页
SELECT *FROM emp ORDER BY empno LIMIT 10,5
#显示各个部门的人数
SELECT COUNT(*),depton FROM emp GROUP BY depton
#显示雇员的总数,以及获得奖金的雇员人数
SELECT COUNT(*),COUNT(comm) FROM emp
#统计没有奖金的雇员
SELECT COUNT(IF(comm IS NULL,1,NULL)) FROM emp
SELECT COUNT(*)-COUNT(comm) FROM emp
#显示管理者的人数
SELECT COUNT(DISTINCT mgr) FROM emp
#显示薪水差
SELECT MAX(sal)-MIN(sal) FROM emp
#先后顺序 group by,having,order by,limit
#统计各部门平均工资,并且大于2000,按平均工资到高到低排序,
#取出前两行记录
SELECT depton,AVG(sal) FROM emp
GROUP BY depton
HAVING AVG(sal)>1000
ORDER BY AVG(sal) DESC
LIMIT 0,2