翼度科技»论坛 编程开发 mysql 查看内容

mysql基础练习(二)

2

主题

2

帖子

6

积分

新手上路

Rank: 1

积分
6
  1. -- 创建表
  2. drop table if exists emp;
  3. create table emp(
  4. empno int,
  5. ename varchar(50),
  6. job varchar(50),
  7. mgr int,
  8. hiredate date,
  9. sal decimal(7,2),
  10. comm decimal(7,2),
  11. deptno int
  12. )engine=innodb default charset=utf8;
  13. drop table if exists dept;
  14. create table dept(
  15. deptno int,
  16. dname varchar(14),
  17. loc varchar(13)
  18. )engine=innodb default charset=utf8;
  19. INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
  20. INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
  21. INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
  22. INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
  23. INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
  24. INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
  25. INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
  26. INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
  27. INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
  28. INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
  29. INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
  30. INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
  31. INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
  32. INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
  33. INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
  34. INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
  35. INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
  36. INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');
复制代码
  1. -- 1、按员工编号升序排列不在10号部门工作的员工信息
  2. SELECT *
  3. FROM emp
  4. WHERE deptno != 10
  5. ORDER BY empno;
  6. -- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息,按薪水降序排列
  7. SELECT *
  8. FROM emp
  9. where ename not LIKE "_A%" and CEIL(sal) > 800;
  10. -- 3、求每个部门的平均薪水
  11. SELECT deptno, avg(sal)
  12. FROM emp
  13. GROUP BY deptno;
  14. -- 4、求各个部门的最高薪水
  15. SELECT deptno, max(sal)
  16. FROM emp
  17. GROUP BY deptno;
  18. -- 5、求每个部门每个职位的最高薪水
  19. SELECT deptno, job, MAX(sal)
  20. FROM emp
  21. group by deptno, job
  22. -- 6、求平均薪水大于2000的部门编号
  23. SELECT deptno, avg(sal) avgSal
  24. from emp
  25. group by deptno
  26. HAVING avgSal > 2000;
  27. -- 7、将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来,按部门平均薪水降序排列
  28. select deptno, avg(sal) avgSal
  29. from emp
  30. where sal > 1200
  31. GROUP BY deptno
  32. HAVING avgSal > 1500
  33. ORDER BY avgSal desc;
  34. -- 8、求最高薪水的员工信息
  35. SELECT *
  36. from emp
  37. where sal = (
  38. SELECT max(sal)
  39. FROM emp
  40. )
  41. -- 9、求多于平均薪水的员工信息
  42. SELECT *
  43. FROM emp
  44. where sal > (
  45. SELECT AVG(sal)
  46. FROM emp
  47. )
  48. -- 10、求各个部门薪水最高的员工信息 显示部门名称
  49. SELECT dept.dname, t2.*
  50. FROM dept,(
  51. select emp.*
  52. from emp, (
  53. SELECT deptno, MAX(sal) maxSal
  54. FROM emp
  55. GROUP BY deptno) t1
  56. where emp.deptno = t1.deptno AND emp.sal = t1.maxSal) t2
  57. where dept.deptno = t2.deptno
  58. -- 11. 查询和Scott相同部门的员工姓名和雇用日期
  59. select ename, hiredate
  60. FROM emp
  61. WHERE ename != "SCOTT" and deptno = (
  62. select deptno
  63. from emp
  64. where ename = "SCOTT");
  65. -- 12.        查询工资比公司平均工资高的员工的员工号,姓名和工资。
  66. SELECT empno, ename, sal
  67. FROM emp
  68. WHERE sal > (
  69. select AVG(sal)
  70. FROM emp);
  71. -- 13.        查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
  72. -- 13.1
  73. SELECT empno, ename, sal
  74. FROM emp,(
  75. SELECT deptno, avg(sal) avgSal
  76. from emp
  77. GROUP BY deptno
  78. ) t1
  79. where emp.deptno = t1.deptno and emp.sal > t1.avgSal;
  80. -- 13.2
  81. select empno, ename, sal
  82. FROM emp inner join (
  83. SELECT deptno, avg(sal) avgSal
  84. FROM emp
  85. GROUP BY deptno
  86. ) t1
  87. on emp.deptno = t1.deptno and emp.sal > t1.avgSal
  88. -- 14.        查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
  89. SELECT empno, ename
  90. FROM emp
  91. WHERE deptno in (
  92. SELECT deptno
  93. FROM emp
  94. WHERE ename LIKE "%u%"
  95. )
  96. -- 15.         查询在部门的loc为 DALLAS 的部门工作的员工的员工号
  97. SELECT empno
  98. from emp
  99. WHERE deptno in (
  100. SELECT deptno
  101. FROM dept
  102. WHERE loc = "DALLAS");
  103. -- 16.        查询所有部门的名字,loc,员工数量 和 工资平均值 ()
  104. -- 16.1
  105. select dept.dname, dept.loc, t1.*
  106. from dept, (
  107. select avg(sal), count(empno), deptno
  108. from emp
  109. group by deptno
  110. ) t1
  111. where t1.deptno = dept.deptno;
  112. -- 16.2
  113. select d.*, count(e.empno), avg(sal)
  114. from dept d, emp e
  115. where d.deptno = e.deptno
  116. GROUP BY d.deptno, d.dname, d.loc
  117. -- 17.查询平均工资最低的部门信息
  118. -- 17.1
  119. select *
  120. from dept
  121. where deptno = (
  122. select deptno
  123. from emp
  124. GROUP BY deptno
  125. order by avg(sal)
  126. limit 1)
  127. -- 17.2
  128. select *
  129. from dept
  130. where deptno = (
  131. select deptno
  132. from emp
  133. group by deptno
  134. having avg(sal) like (
  135. select min(t1.avgSal)
  136. from (
  137. select deptno, avg(sal) avgSal
  138. from emp
  139. GROUP BY deptno
  140. ) t1
  141. ))
  142. -- 18.查询平均工资最低的部门信息和该部门的平均工资
  143. select dept.*, avgSal
  144. from dept, (
  145. select deptno, avg(sal) avgSal
  146. from emp
  147. group by deptno) t1
  148. where dept.deptno = t1.deptno
  149. -- 19.查询平均工资高于公司平均工资的部门有哪些?
  150. select deptno
  151. from emp
  152. group by deptno
  153. having avg(sal) > (
  154. select avg(sal) avgSal
  155. from emp
  156. )
  157. -- 20. 各个部门中 最高工资中最低的那个部门的 最低工资是多少       
  158. select min(sal)
  159. from emp
  160. where deptno = (
  161. select deptno
  162. from emp
  163. group by deptno
  164. order by max(sal) asc
  165. limit 1
  166. )
  167. -- 21.查询部门号大于20 或者 姓名中包含 a的 员工信息
  168. -- 21.1
  169. select *
  170. from emp
  171. where deptno > 20 or ename Like "%a%";
  172. -- 21.2
  173. select *
  174. from emp
  175. where deptno > 20
  176. union
  177. select *
  178. from emp
  179. where ename like "%a%";
复制代码
来源:https://www.cnblogs.com/paopaoT/p/17357181.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

举报 回复 使用道具