mysql练习题emp,dept
DROP DATABASE IF EXISTS `emp`;
CREATE DATABASE `emp`;
USE emp;
CREATE TABLE `dept`(
`deptno` INT(2) NOT NULL,
`dname` VARCHAR(14),
`loc` VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT *FROM dept;
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE `emp` (
`empno` INT(4) NOT NULL PRIMARY KEY,
`ename` VARCHAR(10),
`job` VARCHAR(9),
`mgr` INT(4),
`hiredate` DATE,
`sal` FLOAT(7,2),
`comm` FLOAT(7,2),
`deptno` INT(2),
CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT *FROM emp;
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
SELECT *FROM emp;
CREATE TABLE `salgrade` (
`grade` INT,
`losal` INT,
`hisal` INT
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
SELECT *FROM salgrade;
-- SQL练习训练一sal工资，comm奖金，mgr 经理编号
-- 1、 选择部门30中的雇员
SELECT *FROM emp WHERE deptno='30';
-- 2、 检索emp表中的员工姓名、月收入及部门编号
SELECT ename 姓名,sal 月收入,empno 部门编号 FROM emp;
-- 3、 检索emp表中员工姓名、及雇佣时间（雇佣时间按照yyyy-mm-dd显示）
SELECT ename 姓名,hiredate 雇佣时间 FROM emp;
-- 4、 检索emp表中的部门编号及工种，并去掉重复行
SELECT DISTINCT empno 部门编号,job 工种 FROM emp;
-- 5、 检索emp表中的员工姓名及全年的月收入
SELECT ename 姓名,sal*12 年收入 FROM emp;
-- 6、 用姓名显示员工姓名，用年收入显示全年月收入。
SELECT ename 姓名,sal*12 年收入 FROM emp;
-- 7、 检索月收入大于2000的员工姓名及月收入
SELECT ename 姓名,sal 月收入 FROM emp WHERE sal>2000;
-- 8、 检索月收入在1000元到2000元的员工姓名、月收入及雇佣时间
SELECT ename 姓名,sal 月收入 FROM emp WHERE sal<=2000 AND sal>=1000;
-- 9、 检索以S开头的员工姓名及月收入
SELECT ename 姓名,sal 月收入 FROM emp WHERE ename LIKE 's%';
-- 10、检索emp表中月收入是800的或是1250的员工姓名及部门编号
SELECT ename 姓名, empno 部门编号 FROM emp WHERE sal='800'OR sal='1250';
-- 11、显示在部门20中岗位是CLERK的所有雇员信息
SELECT *FROM (SELECT *FROM emp WHERE job='CLERK') s WHERE s.deptno='20';
-- 12、显示工资高于2500或岗位为MANAGER的所有雇员信息
SELECT *FROM emp WHERE sal>2500 OR job='MANAGER';
-- 13、检索emp表中有奖金的员工姓名、月收入及奖金
SELECT ename 姓名,sal 月收入,comm 奖金 FROM emp WHERE comm>0;
-- 14、检索emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示
SELECT s.ename 姓名,s.sal 月收入,s.mgr 提成 FROM(SELECT *FROM emp WHERE deptno='30' ORDER BY sal ASC) s ORDER BY s.mgr DESC;
-- 15、列出所有办事员的姓名、编号和部门(姓名对不上)
SELECT a.ename 姓名,a.empno ,b.deptno FROM dept a LEFT JOIN emp b WHERE a.ename=b.dname;
-- 17、找出部门10中所有经理和部门20中的所有办事员的详细资料
SELECT *FROM emp WHERE (deptno='10' AND job='MANAGER') OR (deptno ='20' AND job='CLERK');
-- 18、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
SELECT *FROM emp WHERE (deptno='10' AND job='MANAGER') OR (deptno ='20' AND job='CLERK')OR (job NOT IN('MANAGER','CLERK') AND sal>=2000);
-- 19、找出收取奖金的雇员的不同工作
SELECT DISTINCT job 工作 FROM emp WHERE comm>0;
-- 20、找出不收取奖金或收取的奖金低于100的雇员
SELECT ename 姓名 FROM emp WHERE comm<100 OR comm IS NULL;
-- 21、找出各月倒数第三天受雇的所有雇员
SELECT ename 各月倒数三天受雇人姓名 FROM emp WHERE DAYOFMONTH(LAST_DAY(hiredate))-DAYOFMONTH(hiredate)<=3;
SELECT LAST_DAY(hiredate) FROM emp;
SELECT DAYOFMONTH(LAST_DAY(hiredate)) FROM emp;
SELECT DAYOFMONTH(hiredate) FROM emp;
-- select DAYOFMONTH(now())
-- 22、获取当前日期所在月的最后一天
SELECT CURDATE(); -- 获取当前日期
SELECT DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY) -- 获取本月第一天
SELECT LAST_DAY(CURDATE()); -- 获取当月最后一天
SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH ) -- 获取下个月的第一天
SELECT DATEDIFF(DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH ),DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY)) FROM DUAL -- 获取当前月的天数
-- 23、找出早于25年之前受雇的雇员
SELECT *FROM emp WHERE DATE_ADD(NOW(),INTERVAL -25 YEAR)>hiredate;
-- 24、显示正好为6个字符的雇员姓名
SELECT *FROM emp WHERE ename LIKE '%______';
SELECT *FROM emp WHERE LENGTH(ename)='6';
-- 25、显示不带有'R'的雇员姓名
SELECT *FROM emp WHERE ename NOT LIKE '%R%';
-- 26、显示雇员的详细资料，按姓名排序
SELECT *FROM emp ORDER BY ename;
-- 27、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
SELECT ename 姓名,hiredate FROM emp ORDER BY hiredate;
-- 28、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
SELECT t.ename 姓名,t.job 工作,t.sal 薪金 FROM(SELECT * FROM emp ORDER BY job DESC) t ORDER BY t.sal ASC;
-- 29、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
SELECT t.ename 姓名, YEAR(t.hiredate) 年份, MONTH(t.hiredate)月份 FROM(SELECT *FROM emp ORDER BY DAY(hiredate))t ORDER BY YEAR(t.hiredate);
-- 30、显示在一个月为30天的情况下所有雇员的日薪金
SELECT sal/30 日薪金 FROM emp WHERE DAY(LAST_DAY(hiredate));
SELECT LAST_DAY(hiredate) FROM emp;
-- 31、找出在（任何年份的）2月受聘的所有雇员
SELECT ename 姓名,MONTH(hiredate) 月份 FROM emp WHERE MONTH(hiredate)=2;
-- 32、对于每个雇员，显示其加入公司的天数
SELECT ename 姓名,TIMESTAMPDIFF(DAY,hiredate,NOW()) 加入公司天数 FROM emp;
-- 33、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名
SELECT SUBSTRING(ename,1,1)第一个字母,SUBSTRING(ename,2,1)第二个字母,SUBSTRING(ename,3,1)第三个字母,SUBSTRING(ename,4,1)第四个字母,SUBSTRING(ename,5,1)第六个字母,SUBSTRING(ename,6,1)第五个字母,ename 姓名 FROM emp WHERE ename LIKE '%A%';
-- 34、以年、月和日显示所有雇员的服务年限
SELECT ename 姓名, YEAR(hiredate) 年,MONTH(hiredate)月,DAY(hiredate) 日,TIMESTAMPDIFF(YEAR,hiredate,NOW()) 服务年数 FROM emp;
-- 35、选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序.
SELECT t.ename 有奖金员工姓名,t.sal 工资,t.comm 奖金 FROM ( SELECT *FROM emp WHERE comm>0 ORDER BY sal DESC) t ORDER BY t.comm DESC;
-- 36、选择公司中没有管理者的员工姓名及job
SELECT ename 姓名,job 工作 FROM emp WHERE job NOT IN('MANAGER','PRESIDENT');
-- 37、选择在1987年雇用的员工的姓名和雇用时间
SELECT ename 姓名,hiredate 雇用时间 FROM emp WHERE YEAR(hiredate)=1987;
-- 38、选择在20或10号部门工作的员工姓名和部门号
SELECT ename 姓名,deptno 部门号 FROM emp WHERE deptno=10 OR deptno=20;
-- 39、选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序.
SELECT ename 姓名,job 职位,hiredate 雇佣时间 FROM emp WHERE hiredate>'1981-02-01' AND hiredate<'1981-05-01';
-- 40、选择工资不在5000到12000的员工的姓名和工资
SELECT ename 姓名,sal 工资 FROM emp WHERE NOT sal>'5000' AND sal<'12000';
-- 41、查询员工号为7934的员工的姓名和部门号
SELECT ename 姓名,deptno 部门号 FROM emp WHERE empno IN('7934');
-- 42、查询工资大于1200的员工姓名和工资
SELECT ename 姓名,sal 工资 FROM emp WHERE sal>1200;
-- 复杂查询
-- 1. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
SELECT ename 名字,dname 部门名称,(SELECT COUNT(*) FROM emp WHERE deptno=20) 人数 FROM emp,dept WHERE emp.`job`IN(SELECT emp.`JOB` FROM emp WHERE emp.`ENAME` = 'SCOTT')AND emp.`deptno`=dept.`deptno`;
SELECT COUNT(*) FROM emp WHERE deptno=20;
-- 2. 列出公司各个工资等级雇员的数量、平均工资。
SELECT salgrade.`GRADE` AS '薪资等级', COUNT(tt.`EMPNO`) AS '员工数' ,AVG(tt.sal)平均工资
FROM salgrade,
(SELECT emp.`EMPNO`, emp.`SAL`FROM emp) AS tt WHERE tt.`SAL` >= salgrade.`LOSAL` AND tt.`SAL` <= salgrade.`HISAL`
GROUP BY salgrade.`GRADE`;
-- 3. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
SELECT MAX(sal) FROM emp WHERE deptno=30;
SELECT *FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
SELECT c.ename 姓名,c.sal 薪金,d.`dname` 部门名称 FROM dept AS d JOIN (SELECT *FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30)) AS c WHERE d.`deptno`=c.deptno;
-- 4. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT AVG(sal),(SELECT COUNT(*)FROM emp GROUP BY deptno) FROM emp ;
SELECT COUNT(*)FROM emp GROUP BY deptno;
SELECT AVG(sal)FROM emp GROUP BY deptno;
SELECT COUNT(*),AVG(sal),AVG(YEAR(NOW())-YEAR(hiredate))FROM emp GROUP BY deptno;
-- 5. 列出所有员工的姓名、部门名称和工资。
SELECT e.`ename` 姓名,d.`dname` 部门名称,e.sal 工资 FROM emp e JOIN dept d ON e.`deptno`=d.`deptno`;
-- 6. 列出所有部门的详细信息和部门人数。
SELECT d.*,COUNT(*) 部门人数 FROM dept d JOIN emp e ON e.`deptno`=d.`deptno` GROUP BY deptno;
-- 7. 列出各种工作的最低工资及从事此工作的雇员姓名。
SELECT job, MIN(sal) FROM emp GROUP BY job;
SELECT job, MIN(sal) FROM emp GROUP BY job;
SELECT ename FROM emp e JOIN (SELECT job, MIN(sal) FROM emp GROUP BY job) t WHERE e.`job`=t.job AND e.`sal`=MIN(sal);
-- 8. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
SELECT MIN(e.sal),e.`ename` FROM emp e JOIN dept t ON e.`deptno`=t.`deptno` WHERE job IN('MANAGER') GROUP BY t.`deptno`;
SELECT e.`sal`,e.`ename`, d.`dname`,COUNT=(SELECT COUNT(*) FROM emp WHERE job IN('MANAGER')GROUP BY deptno ) FROM emp e JOIN dept d ON e.`deptno`=d.`deptno` WHERE job IN('MANAGER')
-- 9. 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
SELECT ename, sal*12 FROM emp ORDER BY sal*12 ASC;
-- 10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
SELECT mgr FROM emp WHERE ename='SMITH'
SELECT e.ename ,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE empno=(SELECT mgr FROM emp WHERE ename='SMITH') AND e.sal>3000;
-- 11. 求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数。
SELECT deptno FROM dept WHERE dname LIKE '%S%';-- 查询部门
SELECT SUM(sal),COUNT(*)FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE '%S%') GROUP BY deptno;
-- 12. 给任职日期超过40年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
SELECT *FROM emp WHERE YEAR(NOW())-YEAR(hiredate)>40 OR YEAR(hiredate)=1987;
SELECT YEAR(NOW())-YEAR(hiredate)FROM emp;
SELECT IF(emp.deptno=10,sal+sal*0.1,IF(emp.`sal`=20,sal+sal*0.2,IF(emp.`deptno`=30,sal+sal*0.3,'无'))) FROM emp WHERE YEAR(NOW())-YEAR(hiredate)>40 OR YEAR(hiredate)=1987;
-- 13. 列出至少有一个员工的所有部门的信息：
SELECT COUNT(*)FROM emp GROUP BY deptno;
SELECT deptno FROM emp ;
SELECT *FROM emp LEFT JOIN dept ON dept.`deptno`=emp.`deptno` GROUP BY dept.`dname` HAVING COUNT(emp.`empno`)>=5;
SELECT dept.`DNAME` AS '部门名', COUNT(emp.`EMPNO`) AS '员工个数'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
GROUP BY dept.`DNAME` HAVING COUNT(emp.`EMPNO`) >= 5;
-- 14. 列出薪水比“SMITH”多的所有员工信息
SELECT *FROM emp WHERE emp.`SAL` > (SELECT emp.`SAL`FROM emp WHERE emp.`ENAME` = 'SMITH');
-- 15. 列出所有员工的姓名以及其直接上级的姓名：
SELECT DISTINCT e.ename 员工姓名,d.ename FROM emp e JOIN (SELECT ename FROM emp WHERE empno IN(SELECT mgr FROM emp WHERE ename=ename)) d;
SELECT mgr FROM emp WHERE ename=ename;
SELECT ename FROM emp WHERE empno IN(SELECT mgr FROM emp WHERE ename=ename);
SELECT emp.`ENAME` AS '员工名称', tt.lname AS '领导名称'
FROM emp LEFT JOIN (
SELECT emp.`EMPNO`, emp.`ENAME` AS 'lname'
FROM emp
WHERE empno IN (SELECT mgr FROM emp)) AS tt ON emp.`MGR` = tt.empno;
-- 16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
SELECT t2.eno AS '编号', t2.ename AS '姓名', dept.`DNAME` AS '部门名称', t2.ehiredate AS '员工受雇日期', t2.lhiredate AS '领导受雇日期'
FROM dept
INNER JOIN
(
SELECT emp.`EMPNO` AS 'eno', emp.`ENAME`, emp.`HIREDATE` AS 'ehiredate', t1.lhiredate, emp.`DEPTNO`
FROM
emp LEFT JOIN
(
SELECT emp.`EMPNO` AS 'lno', emp.`HIREDATE` AS 'lhiredate'
FROM emp
WHERE empno IN
(
SELECT mgr FROM emp
) -- 找出所有是领导的员工no
) AS t1
ON emp.`MGR` = t1.lno
) AS t2
ON t2.deptno = dept.`DEPTNO`
WHERE t2.ehiredate < t2.lhiredate OR t2.lhiredate IS NULL;
-- 17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dept.`DNAME` AS '部门名称', emp.`ENAME` AS '员工名称', emp.`JOB` AS '职位', emp.`HIREDATE` AS '入职时间', emp.`SAL` AS '薪水'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`;
-- 18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数
SELECT emp.`ENAME` AS '姓名', dept.`DNAME` AS '部门名称', tt.emp_count AS '部门人数'
FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO`
INNER JOIN
(
SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'emp_count'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
GROUP BY dept.`DEPTNO`
) AS tt
ON emp.`DEPTNO` = tt.deptno
WHERE emp.`JOB` = 'CLERK';
-- 19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
SELECT emp.`JOB` AS '工作名称', COUNT(emp.`EMPNO`) AS '雇员人数'
FROM emp
WHERE emp.`JOB` IN (
SELECT emp.`JOB`
FROM emp
GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500
)
GROUP BY emp.`JOB`;
-- 20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
SELECT emp.`ENAME` AS '销售部人员'
FROM emp
WHERE emp.`DEPTNO` = (
SELECT dept.`DEPTNO`
FROM dept
WHERE dept.`DNAME` = 'sales'
);
-- 21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
SELECT t1.ename AS '员工名称', dept.`DNAME` AS '部门名称', t2.ename AS '上级名称', t1.grade AS '工资等级', t1.sal AS '工资'
FROM
(
SELECT emp.`EMPNO`, emp.`DEPTNO`, emp.`ENAME`, emp.`SAL`, emp.`MGR`,salgrade.`GRADE`
FROM emp, salgrade
WHERE emp.`SAL` > (
SELECT AVG(sal) AS 'avg_sal'
FROM emp
) AND emp.`SAL` >= salgrade.`LOSAL` AND emp.`SAL` <= salgrade.`HISAL`
) AS t1
LEFT JOIN
(
SELECT emp.`EMPNO` AS 'mgr', emp.`ENAME` -- 注意这里是查出所有领导的empno，作为mgr编号
FROM emp
WHERE emp.`EMPNO` IN
(
SELECT DISTINCT(emp.`MGR`) -- 必须要distinct 不然查出来的mgr有重复，后面上层再查重复值接近笛卡尔积
FROM emp
WHERE mgr IS NOT NULL
)
) AS t2
ON t1.mgr = t2.mgr
LEFT JOIN dept ON t1.deptno = dept.`DEPTNO`;
-- 22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
-- 23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
-- 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', (emp.`SAL`*12) AS '年薪'
FROM emp
ORDER BY 年薪 ASC;
-- 25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
-- 26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。
SELECT dept.`DNAME` AS '部门名称', COUNT(emp.`EMPNO`) AS '员工数量', ROUND(AVG(emp.`SAL`), 2) AS '平均工资', t2.avg_year AS '平均服务年限'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
LEFT JOIN
(
SELECT t1.deptno, ROUND(AVG(t1.year), 0) AS 'avg_year' -- 四舍五入，保留0位小数
FROM
(
SELECT emp.`DEPTNO`, ROUND((TO_DAYS(NOW())-TO_DAYS(emp.`HIREDATE`))/366, 0) AS 'year'
FROM emp
) AS t1
GROUP BY t1.deptno
) AS t2
ON dept.`DEPTNO` = t2.deptno
GROUP BY dept.`DEPTNO`;
-- 27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。
SELECT emp.`ENAME` AS '姓名', dept.`DNAME` AS '部门名称', tt.emp_count AS '部门人数'
FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO`
INNER JOIN
(
SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'emp_count'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
GROUP BY dept.`DEPTNO`
) AS tt
ON emp.`DEPTNO` = tt.deptno
WHERE emp.`JOB` = 'CLERK';
-- 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。
SELECT emp.`JOB` AS '工作名称', COUNT(emp.`EMPNO`) AS '雇员人数'
FROM emp
WHERE emp.`JOB` IN (
SELECT emp.`JOB`
FROM emp
GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500
)
GROUP BY emp.`JOB`;
29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。
30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
-- 31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
SELECT emp.`EMPNO` AS '员工编号', emp.`ENAME` AS '员工名称', dept.`DNAME` AS '部门名称'-- , emp.`DEPTNO`
FROM emp, dept
WHERE emp.`JOB` = (
SELECT emp.`JOB` -- 查询出SCOTT从事的职业
FROM emp
WHERE emp.`ENAME` = 'SCOTT'
) AND emp.`ENAME` <> 'SCOTT'
AND emp.`DEPTNO` = dept.`DEPTNO`;
-- 32. 查询dept表的结构
33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段
34. 检索emp表中有提成的员工姓名、月收入及提成。
SELECT *FROM emp WHERE deptno='20'
SELECT *FROM emp;
SELECT *FROM dept;
SELECT *FROM salgrade;
答案二
USE emp;
-- 1.
SELECT *FROM emp WHERE emp.`deptno`=30;
-- 2.
SELECT ename 姓名,sal 收入, deptno 部门编号 FROM emp;
-- 3.
SELECT ename 姓名, DATE_FORMAT(hiredate,'%Y年%m月%d日') 时间 FROM emp;
-- select date_format(now(),'%Y-%m-%d %H:%i:%s');
-- 4
SELECT DISTINCT deptno 部门编号 ,job 工作 FROM emp;
-- 5
SELECT ename 姓名 ,sal*12 年工资 FROM emp;
-- 6
SELECT ename 姓名 ,CONCAT('￥',ROUND(sal*12)) 年收入 FROM emp;
-- 7
SELECT ename ,sal FROM emp WHERE sal>2000;
-- 8
SELECT ename, sal,hiredate FROM emp wher sal BETWEEN 1000 AND 200;
-- 9
SELECT ename, sal FROM emp WHERE ename LIKE 'S%';
-- 10
SELECT ename,deptno FROM emp WHERE sal IN(800,1250);
-- 11
SELECT *FROM emp WHERE deptno=20 AND job='CLERK';
-- 12
slect *FROM emp WHERE sal>2500 OR job='MANAGER';
-- 13
SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL;
-- 14
SELECT ename , sal,comm FROM emp WHERE deptno=30 ORDER BY sal ASC,comm DESC;
-- 15
SELECT ename ,empno,deptno FROM emp WHERE job='CLERK'
-- 16
SELECT *FROM emp WHERE comm>sal;
-- 17
SELECT *FROM emp WHERE deptno=10 AND job='MANAGER' OR deptno=20 AND job='CLERK';
-- 18
SELECT *FROM emp WHERE deptno=10 AND job='MANAGER' OR deptno=20 AND job='CLERK' OR sal>=2000 AND (job!='MANAGER' AND job!='CLERK');
-- 19
SELECT job FROM emp WHERE comm IS NOT NULL;
-- 20
SELECT *FROM emp WHERE comm IS NULL OR comm<100;
-- 21
SELECT *FROM emp WHERE hiredate=DATE_ADD(LAST_DAY(hiredate),INTERVAL -2 DAY);
-- 22
SELECT LAST_DAY(NOW());
SELECT LAST_DAY(CURDATE());
SELECT LAST_DAY(CURRENT_DATE());
-- 23
SELECT *FROM emp WHERE hiredate<DATE_ADD(CURRENT_DATE,INTERVAL -35 YEAR);
-- 24
SELECT ename FROM emp WHERE CHAR_LENGTH(ename)=6;
SELECT ename FROM emp WHERE ename LIKE '______';
SELECT ename FROM emp WHERE ename REGEXP '^.{6}$';
-- 25
SELECT *FROM emp WHERE enamenot LIKE '%R%';
SELECT *FROM emp WHERE INSTR(ename,'R')=0;
-- 26
SELECT *FROM emp ORDER BY ename;
-- 27
SELECT ename,hiredate FROM emp ORDER BY hiredate ASC;
-- 28
SELECT ename,job,sal FROM emp ORDER BY job DESC,sal ASC;
-- 29
SELECT ename ,YEAR(hiredate),MONTH(hiredate) FROM emp ORDER BY YEAR(hiredate),MONTH(hiredate);
-- 30
SELECT ename, sal,ROUND(sal/30) FROM emp;
-- 31
SELECT *FROM emp MONTH(hiredate)=2;
SELECT *FROM emp WHERE hiredate BETWEEN '1981-2-1'AND LAST_DAY('1981-2-28');
-- 32
SELECT TIMESTAMPDIFF(DAY,hiredate,NOW()) FROM emp;
-- 33
SELECT ename FROM emp WHERE ename LIKE '%A%';
-- 34
SELECT ename,TIMESTAMPDIFF(YEAR,hiredate,NOW()) 年,
TIMESTAMPDIFF(MONTH,DATE_ADD(hiredate,INTERVAL TIMESTAMPDIFF(YEAR,hiredate,NOW())YEAR),NOW())月,
TIMESTAMPDIFF(DAY,DATE_ADD(DATE_ADD(hiredate,INTERVAL TIMESTAMPDIFF(YEAR,hiredate,NOW()) YEAR),INTERVAL TIMESTAMPDIFF(MONTH,DATE_ADD(hiredate,INTERVAL TIMESTAMPDIFF(YEAR,hiredate,NOW())YEAR),NOW()) MONTH),NOW())日 FROM emp;
-- 35
SELECT ename ,sal,ROUND(comm/sal) FROM emp WHERE comm IS NOT NULL AND comm>0 ORDER BY DESC,ROUND(comm/sal,2) DESC;
-- 36
SELECT ename , job FROM emp WHERE mgr IS NULL;
-- 37
SELECT ename,hiredate FROM emp WHERE YEAR(hiredate)=1987;
SELECT ename,hiredate FROM emp WHERE hiredate BETWEEN '1987-1-1' AND '1987-12-31';
-- 38
SELECT ename ,deptno FROM emp WHERE deptno IN(10,20);
SELECT ename ,deptno FROM emp WHERE deptno =10 OR deptno=20;
-- 39
SELECT ename,job,hiredate FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-05-01' ORDER BY hiredate ASC;
-- 40
SELECT ename ,sal FROM emp WHERE sal NOT BETWEEN 5000 AND 12000;
-- 41
SELECT ename deptno FROM emp WHERE empno=7934;
-- 42
SELECT ename,sal FROM emp sal >1200;
-- 复杂查询
-- 1
-- 2
SELECT s.`grade`,COUNT(*),AVG(e.`sal`) FROM emp e LEFT JOIN salgrade s ON e.sal BETWEEN s.`losal` AND s.`hisal` GROUP BY s.`grade`;
-- 3
SELECT e.ename ,e.sal,d.dname,d.`deptno` FROM emp e LEFT JOIN dept d ON e.`deptno`=d.`deptno` WHERE e.sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
-- 4
SELECT COUNT(*),AVG(sal),AVG(YEAR(NOW())-YEAR(hiredate)) FROM emp GROUP BY deptno;
-- 5
SELECT e.ename,d.dname,e.sal FROM emp e LEFT JOIN dept d ON e.`deptno`=d.`deptno`;
-- 6
SELECT d.*,COUNT(*) FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno GROUP BY d.`deptno`;
-- 7
SELECT a.ename,t.* FROM emp a LEFT JOIN -(SELECT job,MIN(sal) FROM emp GROUP BY job) t ON a.`job`=t.job;
-- 8 binary 区分大小写
SELECT a.mm,c.ename,b.dname,b.cc,c.job FROM
(SELECT d.`dname`, e.deptno ,MIN(sal) mm FROM emp e LEFT JOIN dept d ON e.`deptno`=d.`deptno` WHERE job='MANAGER' GROUP BY deptno) a
LEFT JOIN
(SELECT d.`deptno`,d.`dname`, COUNT(*) cc FROM dept d LEFT JOIN emp e ON e.`deptno`=d.`deptno` GROUP BY d.`deptno`) b ON a.deptno=b.deptno
LEFT JOIN emp c ON c.`sal`=a.mm AND b.deptno =c.`deptno`;
-- 9
SELECT empno,ename,sal*12,d.dname FROM emp LEFT JOIN dept d ON d.`deptno`=emp.`deptno`ORDER BY sal*12 ASC;
-- 10
SELECT a.`empno`,a.`ename`,b.`ename` 领导,b.`sal` 领导工资 FROM emp a LEFT JOIN emp b ON a.mgr=b.`empno` WHERE b.sal>3000;
-- 11
SELECT d.`dname`, d.`deptno`,COUNT(*),SUM(e.sal) FROM emp e LEFT JOIN dept d ON e.`deptno`=d.`deptno` WHERE d.`dname` LIKE '%S%' GROUP BY d.`deptno` ;
-- 12
UPDATE emp SET sal=sal+sal*(deptno/100) WHERE (YEAR(NOW())-YEAR(hiredate))<40 OR YEAR(hiredate)=1987;
SELECT *FROM emp;
-- 13
SELECT DISTINCT d.* FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno;
-- 14
SELECT ename FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='smith');
-- 15
SELECT a.empno,a.`ename`,b.`ename` FROM emp a LEFT JOIN emp b ON a.`mgr`=b.`empno`;
-- 16
SELECT a.empno,a.`ename`,b.`ename`,d.deptno FROM emp a LEFT JOIN emp b ON a.`mgr`=b.`empno` LEFT JOIN dept d ON d.deptno=a.`deptno` WHERE a.`hiredate`<b.`hiredate` ;
-- 17
SELECT *FROM dept d LEFT JOIN emp e ON d.`deptno`=e.`deptno`;
-- 18
SELECT a.`ename`,a.`job`,b.dname,b.cc FROM emp a LEFT JOIN
(SELECT d.`deptno`,d.dname,COUNT(*)cc FROM dept d LEFT JOIN emp e ON d.`deptno`=e.`deptno` GROUP BY d.`deptno`) b
ON b.deptno=a.deptno AND a.job='CLERK';
-- 19
SELECT job,MIN(sal),COUNT(*) FROM emp WHERE sal>1500 GROUP BY job;
-- 20
SELECT ename FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname='sales');
SELECT e.`ename` FROM emp e JOIN dept d ON e.`deptno`=d.`deptno` AND d.`dname`='sales';
-- 21列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
SELECT t1.ename AS '员工名称', dept.`DNAME` AS '部门名称', t2.ename AS '上级名称', t1.grade AS '工资等级', t1.sal AS '工资'
FROM
(
SELECT emp.`EMPNO`, emp.`DEPTNO`, emp.`ENAME`, emp.`SAL`, emp.`MGR`,salgrade.`GRADE`
FROM emp, salgrade
WHERE emp.`SAL` > (
SELECT AVG(sal) AS 'avg_sal'
FROM emp
) AND emp.`SAL` >= salgrade.`LOSAL` AND emp.`SAL` <= salgrade.`HISAL`
) AS t1
LEFT JOIN
(
SELECT emp.`EMPNO` AS 'mgr', emp.`ENAME` -- 注意这里是查出所有领导的empno，作为mgr编号
FROM emp
WHERE emp.`EMPNO` IN
(
SELECT DISTINCT(emp.`MGR`) -- 必须要distinct 不然查出来的mgr有重复，后面上层再查重复值接近笛卡尔积
FROM emp
WHERE mgr IS NOT NULL
)
) AS t2
ON t1.mgr = t2.mgr
LEFT JOIN dept ON t1.deptno = dept.`DEPTNO`;
-- 22
SELECT e.`deptno`,d.`dname`,AVG(sal),MAX(e.`sal`),MIN(sal),COUNT(*) FROM dept d JOIN emp e ON e.`deptno`=d.`deptno` GROUP BY e.`deptno`;
-- 23
SELECT a.empno,a.ename,d.`dname`,b.ename FROM
(SELECT *FROM emp WHERE sal>(SELECT MIN(sal) FROM emp WHERE ename IN('smith','allen'))) a LEFT JOIN emp b ON a.mgr=b.`empno` LEFT JOIN dept d ON b.`deptno`=d.`deptno` ;
-- 24
SELECT a.`empno`,a.`ename`,b.`empno`,b.`ename` FROM emp a LEFT JOIN emp b ON a.mgr=b.`empno` ORDER BY b.`sal`*12;
-- 25
select ename from emp where ename not like '%R%';
select ename from emp where not ename like '%R%';
select ename from emp where instr(ename,'R') = 0;
select instr('helloR','r');
-- 26
SELECT d.`deptno`,d.`dname`,COUNT(e.ename),AVG(sal)FROM dept d LEFT JOIN emp e ON d.`deptno`=e.`deptno`GROUP BY d.`deptno`;
-- 27
SELECT a.deptno,a.ename,d.`dname`,a.sal,s.`grade`,(SELECT COUNT(ename) FROM emp WHERE deptno=a.deptno)人数 FROM (SELECT *FROM emp WHERE job='clerk') a LEFT JOIN dept d ON a.deptno=d.`deptno` LEFT JOIN salgrade s
ON a.sal BETWEEN s.`losal`AND s.`hisal`;
-- 28
select ename,job,sal from emp order by job desc,sal asc;
-- 29
select ename,year(hiredate),month(hiredate) from emp order by
year(hiredate),month(hiredate);
-- 30
select ename,sal,round(sal/30) from emp;
-- 31
SELECT a.ename , d.`dname` ,(SELECT COUNT(ename) FROM emp WHERE deptno=a.deptno) FROM
(SELECT *FROM emp WHERE job=(SELECT job FROM emp WHERE ename='scott')) a LEFT JOIN dept d ON a.deptno =d.`deptno` ;
-- 32
DESC emp;
DESCRIBE emp;
SHOW CREATE TABLE emp;
SHOW COLUMNS FROM emp;
-- 33
SELECT CONCAT(empno,ename,job) FROM emp;
SELECT CONCAT_WS('-',.empno,ename , job)FROM emp;
SELECT GROUP_CONCAT(DISTINCT job)FROM emp;
SELECT GROUP_CONCAT(DISTINCT ename) FROM emp;
SELECT GROUP_CONCAT(DISTINCT job ORDER BY sal SEPARATOR '=')FROM emp;
-- 34
SELECT ename ,sal
-- 每个部门所有工作的最低工资，工作名称，员工姓名
SELECT MIN(sal),job FROM emp GROUP BY job;
SELECT e.ename 姓名,b.job 工作,b.s 工资,e.`deptno` 部门编号 FROM emp e LEFT JOIN (SELECT MIN(sal) s,job FROM emp GROUP BY job) b ON e.`sal`=b.s WHERE e.sal=b.s;
SELECT*FROM emp;
-- 所有部门的工作的最低工资，工作名称，员工姓名 ,部门编号
SELECT deptno FROM emp;
SELECT MIN(sal) FROM emp e LEFT JOIN (
SELECT job ,deptno FROM emp WHERE deptno IN(SELECT deptno FROM emp)) b e.`deptno`=b.deptno GROUP BY b.job;
SELECT sal FROM emp;
