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 1 or comm is null; -- 21、找出各月倒数第三天受雇的所有雇员 select ename 各月倒数三天受雇人姓名 from emp where dayofmonth(last_day(hiredate))-dayofmonth(hiredate)<="3;" last_day(hiredate) emp; dayofmonth(last_day(hiredate)) dayofmonth(hiredate) dayofmonth(now()) 22、获取当前日期所在月的最后一天 curdate(); 获取当前日期 date_add(curdate(),interval -day(curdate())+1 day) 获取本月第一天 last_day(curdate()); 获取当月最后一天 date_add(curdate()-day(curdate())+1,interval month ) 获取下个月的第一天 datediff(date_add(curdate()-day(curdate())+1,interval ),date_add(curdate(),interval day)) dual 获取当前月的天数 23、找出早于25年之前受雇的雇员 *from 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的员工姓名和工资 姓名,sal 工资 sal>1200;
-- 复杂查询
-- 1.  列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
SELECT ename 名字,dname 部门名称,(SELECT COUNT(*) FROM emp WHERE deptno=20) 人数 FROM emp,dept WHERE emp.job(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 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;" *from 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 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 'mgr', emp.ename -- 注意这里是查出所有领导的empno，作为mgr编号 from emp where in distinct(emp.mgr) 必须要distinct 不然查出来的mgr有重复，后面上层再查重复值接近笛卡尔积 emp mgr is not null t2 on t1.mgr="t2.mgr" join dept t1.deptno="dept.DEPTNO;" 22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 23. 列出薪金比"smith"或"allen"多的所有员工的编号、姓名、部门名称、其领导姓名。 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 '员工编号', '员工名称', (emp.sal*12) '年薪' order by 年薪 asc; 25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。 26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 dept.dname '部门名称', count(emp.empno) '员工数量', round(avg(emp.sal), 2) '平均工资', t2.avg_year '平均服务年限' dept.deptno="emp.DEPTNO" t1.deptno, round(avg(t1.year), 0) 'avg_year' 四舍五入，保留0位小数 from emp.deptno, round((to_days(now())-to_days(emp.hiredate)) 366, 'year' group t2 dept.deptno; 27. 列出所有"clerk"（办事员）的姓名及其部门名称，部门的人数，工资等级。 '姓名', tt.emp_count '部门人数' emp.deptno="dept.DEPTNO" inner dept.deptno, 'emp_count' dept.deptno tt emp.job="CLERK" ; 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 '工作名称', '雇员人数' in 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;</=></=></'12000';></'1981-05-01';></100></=2000>
答案二
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 22 23 24 25 26 27 28 29 30 31 32 33 34 35 -- select *from emp where hiredate="DATE_ADD(LAST_DAY(hiredate),INTERVAL" -2 day); last_day(now()); last_day(curdate()); last_day(current_date()); hiredate<date_add(current_date,interval -35 year); ename from char_length(ename)="6;" like '______'; regexp '^.{6}$'; enamenot '%r%'; instr(ename,'r')="0;" order by ename; ename,hiredate asc; ename,job,sal job desc,sal ,year(hiredate),month(hiredate) year(hiredate),month(hiredate); ename, sal,round(sal 30) emp; month(hiredate)="2;" between '1981-2-1'and last_day('1981-2-28'); timestampdiff(day,hiredate,now()) '%a%'; 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(year,hiredate,now())year),now()) month),now())日 ,sal,round(comm sal) comm is not null and>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 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 13 14 or year(hiredate)="1987;" select *from emp; -- distinct d.* from dept d left join emp e on d.deptno="e.deptno;" ename 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 18 19 ; -- select *from dept d left join emp e on d.deptno="e.deptno;" a.ename,a.job,b.dname,b.cc from a (select d.deptno,d.dname,count(*)cc group by d.deptno) b b.deptno="a.deptno" and a.job="CLERK" job,min(sal),count(*) 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 <= 22 23 salgrade.hisal ) as t1 left join ( select emp.empno 'mgr', emp.ename -- 注意这里是查出所有领导的empno，作为mgr编号 from emp where in distinct(emp.mgr) 必须要distinct 不然查出来的mgr有重复，后面上层再查重复值接近笛卡尔积 mgr is not null t2 on t1.mgr="t2.mgr" dept t1.deptno="dept.DEPTNO;" e.deptno,d.dname,avg(sal),max(e.sal),min(sal),count(*) d e e.deptno="d.deptno" group by e.deptno; a.empno,a.ename,d.dname,b.ename (select *from 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 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 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;</=></b.hiredate></40></100;>
Original: https://www.cnblogs.com/zcxxcvbn/p/15929018.html
Author: 一份人间烟火
Title: mysql练习题emp,dept
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/590771/
转载文章受原作者版权保护。转载请注明原作者出处！