-- 显示所有部门名称
SELECT dname FROM dept;
SELECT * FROM emp
-- 显示所有雇员及其全年收入,13月(工资加补助),并指定名'年收入'
SELECT ename,(sal*12 + IFNULL (comm,0))AS '年收入'
FROM emp;
-- 显示工资超过2850的雇员姓名和工资
SELECT ename,sal FROM emp
WHERE sal > 2850;
-- 显示工资不再1500到2850之间的雇员名字和工资
SELECT ename,sal FROM emp
WHERE sal < 1500 OR sal > 2850;
-- 显示编号为7566的雇员姓名及部门编号
SELECT ename ,deptno FROM emp
WHERE empno = 7566;
-- 显示部门10和30中工资超过1500的雇员名及工资
SELECT ename,sal,deptno FROM emp
WHERE sal > 1500 AND (deptno = 10 OR deptno = 30);
-- 显示无管理者的雇员名及岗位
SELECT ename,job FROM emp
WHERE mgr IS NULL;
-- 显示在1991年2月1日 到1991年5月1日之间的雇员名,岗位,雇佣日期,并以雇佣日期排序
SELECT ename,job,hiredate FROM emp
WHERE hiredate BETWEEN '1991-2-1' AND '1991-5-1'
ORDER BY hiredate;
-- 显示获得补助的所有雇佣员名,工资及补助,并以工资降序
SELECT ename,sal,comm FROM emp
WHERE comm IS NOT NULL
ORDER BY sal DESC;
-- 选择部门中30的所有员工
SELECT * FROM emp
WHERE deptno = 30;
-- 列出所有办事员clerk的姓名,编号,和部门编号
SELECT ename, empno,deptno,job FROM emp
WHERE job = 'CLERK';
-- 找出佣金高于薪金的员工
SELECT ename,sal,comm FROM emp
WHERE IFNULL(comm,0) > sal;
-- 找出佣金高于薪金60%的员工
SELECT ename,sal,comm FROM emp
WHERE IFNULL(comm,0) > (sal * 0.6);
-- 找出部门10中所有经理MANAGER,部门20中所有办事员CLERK的详细资料
SELECT * FROM emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (DEPTNO = 20 AND job = 'CLERK');
-- 找出部门10中所有经理MANAGER,部门20所有办事员CLERK
-- 还有既不是经理又不是办事员,但其薪金>=2000的所有员工详细资料
SELECT * FROM emp
WHERE ((deptno = 10 AND job = 'MANGER'))
OR (deptno = 20 AND job = 'CLERK')
OR (job != 'MANGER' AND job != 'CLERK' AND sal >= 2000);
-- 找出收取佣金的员工的不同工作
SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL;
-- 找出不收佣金或收取的佣金低于100的员工
SELECT * FROM emp
WHERE comm IS NULL OR IFNULL(comm,0) < 100;
-- 找出各月倒数第三天受雇的所有员工
-- 提示 LAST_DAY() 返回该日期月的最后一天
SELECT * FROM emp
WHERE hiredate = LAST_DAY(hiredate) - 2; # 最后一天的前两天是这个月倒数第三天
-- 找出早于十二年前受雇的员工,入职时间超过12年的员工
SELECT * FROM emp
WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW();
SELECT * FROM emp
WHERE DATE_SUB(NOW(),INTERVAL 12 YEAR) > hiredate;
-- 以首字母小写方式显示所有员工姓名
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2))
AS '首字母小写name'
FROM emp;
-- 显示正好为5个字符的员工姓名
SELECT ename FROM emp
WHERE LENGTH(ename) = 5;
-- 显示不带有R的员工姓名
SELECT ename FROM emp
WHERE ename NOT LIKE '%R%';
-- 显示所有员工姓名前三个字符
SELECT LEFT(ename,3) FROM emp;
-- 显示所有员工的姓名,用a替代所有A
SELECT REPLACE(ename,'A','a')FROM emp;
-- 显示满十年工作的员工姓名和受雇日期
SELECT ename,hiredate FROM emp
WHERE DATE_SUB(NOW(),INTERVAL 10 YEAR) >= hiredate;
-- 显示员工的详细资料,按姓名排序
SELECT * FROM emp
ORDER BY ename;
-- 显示员工姓名和受雇日期,根据工作年限,最老排最前
SELECT ename,hiredate FROM emp
ORDER BY hiredate ;
-- 显示所有员工姓名,工作和薪资,按照工作降序排序,若工作相同按薪资排序
SELECT ename,job, sal FROM emp
ORDER BY job DESC,sal;
-- 显示所有员工姓名,加入公司的年份和月份,受雇日期所在月排序,
-- 如月份相同则按年份早到晚排序
SELECT ename,CONCAT(YEAR(hiredate),'年',MONTH(hiredate),'月')
AS '受雇年月' FROM emp
ORDER BY MONTH(hiredate),YEAR(hiredate);
-- 显示在一个月为30天的情况所有员工的日薪资,忽略余数
SELECT ename,FLOOR(sal/30),sal/30 FROM emp;
-- 找出在(任何年份的)2月受聘的所有员工
SELECT * FROM emp
WHERE MONTH(hiredate) = 2;
-- 对于每个员工,显示加入公司的天数
SELECT ename,DATEDIFF(NOW(),hiredate)
AS '加入公司时间' FROM emp;
-- 显示姓名字段任何位置包含A的所有成员
SELECT ename FROM emp
WHERE ename LIKE '%A%' ;
-- 以年月日的方式显示所有员工的服务年限(大概)
SELECT ename ,'工作年月日',FLOOR(DATEDIFF(NOW(),hiredate)/365) AS '年',
FLOOR(FLOOR(DATEDIFF(NOW(),hiredate)%365)/30) AS '月',
FLOOR(DATEDIFF(NOW(),hiredate)%365)%30 AS '天'
FROM emp ;
-- 工资 = sal + comm
-- 列出至少有一个员工的所有部门
SELECT COUNT(*)AS c,deptno FROM emp
GROUP BY deptno
HAVING c > 1
-- 列出薪金比SMITH多的所有员工
SELECT sal FROM emp
WHERE ename = 'SMITH';
SELECT * FROM emp
WHERE sal >= (SELECT sal FROM emp
WHERE ename = 'SMITH')
HAVING ename != 'SMITH';
-- 列出受雇日期晚于其直接上级的所有员工
-- emp 当作两张表,一张为worker,一张为leader (自连接)
-- 通过worker.mgr与leader.empno 进行日期比较
SELECT leader.ename,leader.hiredate ,worker.ename AS '其员工',
worker.hiredate AS '员工入职日期'
FROM emp worker,emp leader
WHERE worker.hiredate > leader.hiredate
AND worker.mgr = leader.empno
-- 列出所有部门名称和这些部门员工的员工信息,同时列出那些员工没有部门
SELECT *FROM emp
SELECT * FROM dept
SELECT * FROM salgrade
SELECT dname,emp.*
FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno
-- 列出所有CLERK的姓名及其部门名称
SELECT emp.ename,job,dept.dname
FROM emp LEFT JOIN dept ON dept.deptno = emp.deptno
AND emp.job = 'CLERK';
-- 列出最低薪金大于1500的各种工作
SELECT job,MIN(sal)AS min_sal FROM emp
GROUP BY job
HAVING min_sal > 1500;
-- 列出在部门SALES 工作的员工的姓名
SELECT dname,ename FROM emp,dept
WHERE emp.deptno = dept.deptno
AND dept.dname = 'sales';
-- 列出薪金高于公司平均薪金的所有员工
SELECT ename,sal FROM emp
WHERE sal > (SELECT AVG(sal)
FROM emp
)
-- 列出与SCOTT从事相同工作的所有员工
SELECT * FROM emp
WHERE job = (SELECT job FROM emp
WHERE ename = 'SCOTT')
HAVING ename != 'SCOTT';
-- 列出薪金高于所在部门30工作的所有员工的薪金的员工姓名和薪金
SELECT * FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp
WHERE deptno = 30);
-- 列出每个部门工作的员工数量,平均工资和平均服务期限
SELECT COUNT(*)AS '部门人数',
AVG(sal)AS '部门平均工资',
AVG(DATEDIFF(NOW(),hiredate))/ 365 AS '部门平均服务年数'
FROM emp GROUP BY deptno
-- 列出所有员工姓名,部门名称和工资
SELECT ename,dname,sal
FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno
-- 列出所有部门的详细信息和部门人数
SELECT dept.*,dept_nums.num
FROM dept LEFT JOIN (SELECT COUNT(*) AS num ,deptno
FROM emp
GROUP BY deptno) dept_nums ON dept_nums.deptno = dept.deptno
;
-- 列出各种各工作的最低工资
SELECT job,MIN(sal) FROM emp
GROUP BY job
-- 列出MANAGER的最低薪金
SELECT MIN(sal) FROM emp
WHERE job = 'MANAGER';
-- 列出所有员工的年工资,按年薪从低到高
SELECT ename ,(sal+IFNULL(comm,0))*12
year_sal FROM emp
ORDER BY year_sal