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;

posted @ 2022-02-23 19:58 一份人间烟火阅读(1237) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· mysql数据类型与表操作

· mysqlCRUD

· MySQL练习题目及答案

· MySQL练习题

· mysql综合练习题

阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通

公告

昵称：一份人间烟火
园龄： 3年1个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

一份人间烟火

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, sal12 FROM emp ORDER BY sal12 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+sal0.1,IF(emp.`sal`=20,sal+sal0.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,sal12,d.dname FROM emp LEFT JOIN dept d ON d.`deptno`=emp.`deptno`ORDER BY sal12 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;