mysql基础练习(二)

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;
14. drop table if exists dept;
15. create table dept(
16. deptno int,
17. dname varchar(14),
18. loc varchar(13)
19. )engine=innodb default charset=utf8;
21. INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
22. INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
23. INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
24. INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
25. INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
26. INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
27. INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
28. INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
29. INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
30. INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
31. INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
32. INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
33. INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
34. INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
36. INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
37. INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
38. INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
39. 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;
35. -- 8、求最高薪水的员工信息
36. SELECT *
37. from emp
38. where sal = (
39. SELECT max(sal)
40. FROM emp
41. )
42. -- 9、求多于平均薪水的员工信息
43. SELECT *
44. FROM emp
45. where sal > (
46. SELECT AVG(sal)
47. FROM emp
48. )
50. -- 10、求各个部门薪水最高的员工信息 显示部门名称
51. SELECT dept.dname, t2.*
52. FROM dept,(
53. select emp.*
54. from emp, (
55. SELECT deptno, MAX(sal) maxSal
56. FROM emp
57. GROUP BY deptno) t1
58. where emp.deptno = t1.deptno AND emp.sal = t1.maxSal) t2
59. where dept.deptno = t2.deptno
62. -- 11. 查询和Scott相同部门的员工姓名和雇用日期
64. select ename, hiredate
65. FROM emp
66. WHERE ename != "SCOTT" and deptno = (
67. select deptno
68. from emp
69. where ename = "SCOTT");
70. -- 12.        查询工资比公司平均工资高的员工的员工号，姓名和工资。
71. SELECT empno, ename, sal
72. FROM emp
73. WHERE sal > (
74. select AVG(sal)
75. FROM emp);
76. -- 13.        查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
78. -- 13.1
79. SELECT empno, ename, sal
80. FROM emp,(
81. SELECT deptno, avg(sal) avgSal
82. from emp
83. GROUP BY deptno
84. ) t1
85. where emp.deptno = t1.deptno and emp.sal > t1.avgSal;
87. -- 13.2
88. select empno, ename, sal
89. FROM emp inner join (
90. SELECT deptno, avg(sal) avgSal
91. FROM emp
92. GROUP BY deptno
93. ) t1
94. on emp.deptno = t1.deptno and emp.sal > t1.avgSal
96. -- 14.        查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
97. SELECT empno, ename
98. FROM emp
99. WHERE deptno in (
100. SELECT deptno
101. FROM emp
102. WHERE ename LIKE "%u%"
103. )
105. -- 15.         查询在部门的loc为 DALLAS 的部门工作的员工的员工号
106. SELECT empno
107. from emp
108. WHERE deptno in (
109. SELECT deptno
110. FROM dept
111. WHERE loc = "DALLAS");
114. -- 16.        查询所有部门的名字，loc，员工数量 和 工资平均值 ()
115. -- 16.1
116. select dept.dname, dept.loc, t1.*
117. from dept, (
118. select avg(sal), count(empno), deptno
119. from emp
120. group by deptno
121. ) t1
122. where t1.deptno = dept.deptno;
124. -- 16.2
125. select d.*, count(e.empno), avg(sal)
126. from dept d, emp e
127. where d.deptno = e.deptno
128. GROUP BY d.deptno, d.dname, d.loc
131. -- 17.查询平均工资最低的部门信息
132. -- 17.1
133. select *
134. from dept
135. where deptno = (
136. select deptno
137. from emp
138. GROUP BY deptno
139. order by avg(sal)
140. limit 1)
142. -- 17.2
143. select *
144. from dept
145. where deptno = (
146. select deptno
147. from emp
148. group by deptno
149. having avg(sal) like (
150. select min(t1.avgSal)
151. from (
152. select deptno, avg(sal) avgSal
153. from emp
154. GROUP BY deptno
155. ) t1
156. ))
158. -- 18.查询平均工资最低的部门信息和该部门的平均工资
159. select dept.*, avgSal
160. from dept, (
161. select deptno, avg(sal) avgSal
162. from emp
163. group by deptno) t1
164. where dept.deptno = t1.deptno
167. -- 19.查询平均工资高于公司平均工资的部门有哪些?
168. select deptno
169. from emp
170. group by deptno
171. having avg(sal) > (
172. select avg(sal) avgSal
173. from emp
174. )
176. -- 20. 各个部门中 最高工资中最低的那个部门的 最低工资是多少       
177. select min(sal)
178. from emp
179. where deptno = (
180. select deptno
181. from emp
182. group by deptno
183. order by max(sal) asc
184. limit 1
185. )
186. -- 21.查询部门号大于20 或者 姓名中包含 a的 员工信息
187. -- 21.1
188. select *
189. from emp
190. where deptno > 20 or ename Like "%a%";
192. -- 21.2
193. select *
194. from emp
195. where deptno > 20
196. union
197. select *
198. from emp
199. where ename like "%a%";

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！