sqljob连接mysql_sql练习(针对Mysql)

灰白光

于 2021-02-04 21:07:51 发布

阅读量108

点赞数

文章标签： sqljob连接mysql

本文链接：https://blog.csdn.net/weixin_36050671/article/details/113683336

版权

1 //多表与分组练习2

3 1、返回拥有员工的部门名、部门号。4 --不知道这个具体干什么的.通过看部门dept表和emp表可以看出来,员工并不是都是在dept表中存在的地方的.

5 --dept表中可能有四个部门号,但是员工emp表中可能只占其中的三个部门号码

6 --题干的需求就是干这个的.(即有的部门可能没有"员工")

7 select dname,deptno from dept where deptno in (select deptno fromemp);8 +------------+--------+

9 | dname | deptno |

10 +------------+--------+

11 | ACCOUNTING | 10 |

12 | RESEARCH | 20 |

13 | SALES | 30 |

14 +------------+--------+

17 2、工资水平多于smith的员工信息。18 select * from emp where sal>(select sal from emp where ename = 'smith');19

21 3、返回员工和所属经理的姓名。22 --这个感觉很简单就是没有想出来怎么办,看答案才理解了

23 --复用表emp,再加上使用外连接.

25 --select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm where ee.mgr=mm.empno;

26 --看完答案理解了之后也写错了,注意外连接是left/right join on

27 select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm on ee.mgr=mm.empno;28

29 --现在左外连接还有另外的一种写法,就是用加号"+";仔细查了一下,并且纳闷为什么＋的SQL语句外连接在navicate中没有效果.

30 --得知加号的外连接只有在oracle中才是用.

33 +--------+--------+

34 | eename | mename |

35 +--------+--------+

36 | SMITH | FORD |

37 | ALLEN | BLAKE |

38 | WARD | BLAKE |

39 | JONES | KING |

40 | MARTIN | BLAKE |

41 | BLAKE | KING |

42 | CLARK | KING |

43 | SCOTT | JONES |

44 | KING | NULL |

45 | TURNER | BLAKE |

46 | ADAMS | SCOTT |

47 | JAMES | BLAKE |

48 | FORD | JONES |

49 | MILLER | CLARK |

53 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。54 --感觉这个也是要复用emp表,确实复用emp表

55 --select ee.ename as eename,mm.ename as mename from emp ee,emp mm where ee.hiredate

56 --这句话没有加上是其经理的条件

57 --select ee.ename as eename,mm.ename as mename from emp ee left join emp mm on ee.mgr = mm.empno and ee.hiredate < mm.hiredate;

58 --上面这句仅仅是把第三句中加上了日期小于的条件.

59 select ee.ename as eename,mm.ename as mename from emp ee ,emp mm where ee.mgr = mm.empno and ee.hiredate

61 (select ee.ename as eename ,mm.ename as mename from emp ee left join emp mm where ee.mgr=mm.empno;)第三句的写法.62

64 5、返回员工姓名及其所在的部门名称。65 --这句有点像第三句,不过这个牵扯到了两个表

66 --select ee.ename as ename, ee.deptno as dptname from emp ee, dept dd where ee.deptno=dd.deptno;

67 --最开始写的,写错了.还是没有把最基本的需求高清,人家要部门名.

69 select ename as ename ,dname from emp ee,dept dd where ee.deptno=dd.deptno;70 +--------+------------+

71 | ename | dname |

72 +--------+------------+

73 | CLARK | ACCOUNTING |

74 | KING | ACCOUNTING |

75 | MILLER | ACCOUNTING |

76 | SMITH | RESEARCH |

77 | JONES | RESEARCH |

78 | SCOTT | RESEARCH |

79 | ADAMS | RESEARCH |

80 | FORD | RESEARCH |

81 | ALLEN | SALES |

82 | WARD | SALES |

83 | MARTIN | SALES |

84 | BLAKE | SALES |

85 | TURNER | SALES |

86 | JAMES | SALES |

87 +--------+------------+

90 6、返回从事clerk工作的员工姓名和所在部门名称。91 select ename ,dname from emp ee, dept dd where ee.job='clerk' and ee.deptno =dd.deptno;92 +--------+------------+

93 | ename | dname |

94 +--------+------------+

95 | MILLER | ACCOUNTING |

96 | SMITH | RESEARCH |

97 | ADAMS | RESEARCH |

98 | JAMES | SALES |

99 +--------+------------+

100

101

102 7、返回部门号及其本部门的最低工资。103 --这个语句不用where条件进行过滤.

104 --找出部门的最低工资 ----想不起来.提示一下用到了min()函数.

105 --select min(sal) from emp ;

106 --select deptno , min(sal) from deptno ,emp

107 --首先想需求中要用到哪几个表.然后再写.

108 --因为是按照部门为单位的所以用到分组

109 select deptno ,min(sal) from emp group bydeptno;110 +--------+----------+

111 | deptno | min(sal) |

112 +--------+----------+

113 | 10 | 1300 |

114 | 20 | 800 |

115 | 30 | 950 |

116 +--------+----------+

117

118 8、返回销售部(sales)所有员工的姓名。119 --根据需求用到了emp和dept两个表

120 select ename from emp ee,dept dd where dd.dname='sales' and ee.deptno = (select deptno from dept where dname = 'sales');121 +--------+

122 | ename |

123 +--------+

124 | ALLEN |

125 | WARD |

126 | MARTIN |

127 | BLAKE |

128 | TURNER |

129 | JAMES |

130 +--------+

131

132

133 9、返回工资水平多于平均工资的员工。134 --这个用到了avg()函数. 用到表emp

135 --select * from emp where sal > avg(sal);

136 --一开始这样写的,但是报错,提示信息为:ERROR 1111 : Invalid use of group function

137 --在这种情况下要把聚合函数放在select语句中

138 select * from emp where sal > (select avg(sal) fromemp);139 +-------+-------+-----------+------+------------+------+------+--------+

140 | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |

141 +-------+-------+-----------+------+------------+------+------+--------+

142 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |

143 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |

144 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |

145 | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 |

146 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |

147 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |

148 +-------+-------+-----------+------+------------+------+------+--------+

149

150 10、返回与SCOTT从事相同工作的员工。151 --只用到表emp

152 --select * from emp where job = '(select job from emp where ename = 'scott')';

153 --上一句在语法上是没有问题的,但是有个小问题就是job是varchar字符串类型的,但是在其后是select语句

154 --的时候其后是不能加上单引号,或者是单引号的,否则都会报错的.

155 --还要把搜索到的结果去掉scott他本身这条记录.

156

157 select * from emp where job = (select job from emp where ename = 'scott') and ename<> 'scott';158

159

160

161 11、返回与30部门员工工资水平相同的员工姓名与工资。162 --这个也只用到了emp表

163

164 select ename ,sal from emp where sal in( select sal from emp where deptno = 30);165 +--------+------+

166 | ename | sal |

167 +--------+------+

168 | ALLEN | 1600 |

169 | WARD | 1250 |

170 | MARTIN | 1250 |

171 | BLAKE | 2850 |

172 | TURNER | 1500 |

173 | JAMES | 950 |

174 +--------+------+

175

176

177

178 12、返回工资高于30部门所有员工工资水平的员工信息。179 --从题意中就可以看出要使用到关键字all 也只用到了emp表

180 --all关键字怎么使用.

181 select * from emp where sal>all(select sal from emp where deptno = 30);182 +-------+-------+-----------+------+------------+------+------+--------+

183 | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |

184 +-------+-------+-----------+------+------------+------+------+--------+

185 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |

186 | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 |

187 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |

188 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |

189 +-------+-------+-----------+------+------------+------+------+--------+

190

191

192 13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。193 --需要用到emp和dept两个表

194 select deptno ,dname ,loc ,count(*) from emp ee,dept dd where ee.deptno =dd.deptno195

196 14、返回员工的姓名、所在部门名及其工资。197 --需要用到emp表和dept表.

198 select ee.ename , dd.dname ,ee.sal from emp ee,dept dd where ee.deptno =dd.deptno;199 +--------+------------+------+

200 | ename | dname | sal |

201 +--------+------------+------+

202 | CLARK | ACCOUNTING | 2450 |

203 | KING | ACCOUNTING | 5000 |

204 | MILLER | ACCOUNTING | 1300 |

205 | SMITH | RESEARCH | 800 |

206 | JONES | RESEARCH | 2975 |

207 | SCOTT | RESEARCH | 3000 |

208 | ADAMS | RESEARCH | 1100 |

209 | FORD | RESEARCH | 3000 |

210 | ALLEN | SALES | 1600 |

211 | WARD | SALES | 1250 |

212 | MARTIN | SALES | 1250 |

213 | BLAKE | SALES | 2850 |

214 | TURNER | SALES | 1500 |

215 | JAMES | SALES | 950 |

216 +--------+------------+------+

217

218 15、返回员工的详细信息。(包括部门名)219 --需要用到两个表emp和dept

220 --在用select * 的基础上还要加上部门名这一列,这咋办????

221 --看了答案明白了既然是还要加上部门名并且是详细信息,那么就对这三个表都select* 操作

222 --select * from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno;

223 --一开始没有加上工资的限制.

224 select * from emp ee,dept dd,salgrade ss where ee.deptno = dd.deptno and sal between losal andhisal;225

226 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+

228 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+

229 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS | 1 | 700 | 1200 |

230 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICAGO | 3 | 1401 | 2000 |

231 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICAGO | 2 | 1201 | 1400 |

232 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS | 4 | 2001 | 3000 |

233 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO | 2 | 1201 | 1400 |

234 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 30 | SALES | CHICAGO | 4 | 2001 | 3000 |

235 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | 4 | 2001 | 3000 |

236 | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS | 4 | 2001 | 3000 |

237 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | 5 | 3001 | 9999 |

238 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICAGO | 3 | 1401 | 2000 |

239 | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS | 1 | 700 | 1200 |

240 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | SALES | CHICAGO | 1 | 700 | 1200 |

241 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS | 4 | 2001 | 3000 |

242 | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | 2 | 1201 | 1400 |

243 +-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+-------+-------+-------+

244

245 16、返回员工工作及其从事此工作的最低工资。246 --需要用到表emp,很明显需要用到分组group by.

247 select job ,min(sal) from emp group byjob;248 --原来可以在from前面直接用聚合函数.

249 +-----------+----------+

250 | job | min(sal) |

251 +-----------+----------+

252 | ANALYST | 3000 |

253 | CLERK | 800 |

254 | MANAGER | 2450 |

255 | PRESIDENT | 5000 |

256 | SALESMAN | 1250 |

257 +-----------+----------+

258 17、返回不同部门经理的最低工资。259 --需要用到一个表emp,需要用到group by

260 select deptno , min(sal) from emp where job = 'manager' group bydeptno;261 +--------+----------+

262 | deptno | min(sal) |

263 +--------+----------+

264 | 10 | 2450 |

265 | 20 | 2975 |

266 | 30 | 2850 |

267 +--------+----------+

268

269

270

271 18、计算出员工的年薪，并且以年薪排序。272 --需要用到一个表emp

273 select sal*12 as ysal from emp order by ysal asc;274 --上面的语句没有问题,关键是年薪的计算方法,这里还牵扯到奖金的问题.

275

276

277

278 19、返回工资处于第四级别的员工的姓名。279 --这里需要两个表,一个是emp一个是salgrade

280 --select ename form emp ee,salgrade where grade =4;

281 --这句话仔细一想就可以看出不会有任何结果,where过滤不掉任何东西

282

283 select ename from emp ,salgrade where grade =4 and sal>=losal and sal <=hisal;284 select ename from emp ,salgrade where grade =4 and sal between losal andhisal;285 +-------+

286 | ename |

287 +-------+

288 | JONES |

289 | BLAKE |

290 | CLARK |

291 | SCOTT |

292 | FORD |

293 +-------+

294

295 20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资296 --从题意中可知需要用到三个表 emp dept salgrade

297 --二等级的最低工资和最高工资.并不是二等级员工的工资.这个是和第21问的区别.

298 --select ename ,loc ,min(sal) ,max(sal) from emp ee,dept dd, salgrade ss where grade = 2 and sal between losal and hisal;

299 --二等级的最低工资和最高工资并不是二等级员工的最低工资和最高工资.

300

301 select ename ,loc ,losal,hisal from emp ee,dept dd,salgrade ss where grade = 2 and sal between losal andhisal;302

303

304 21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资305 --需要用到salgrade , dept表和emp表

306 mysql> select ename , loc ,min(sal) ,max(sal) from emp ee, dept dd, salgrade ss where grade = 2 and sal between losal andhisal;307 +-------+----------+----------+----------+

309 +-------+----------+----------+----------+

310 | WARD | NEW YORK | 1250 | 1300 |

311 +-------+----------+----------+----------+

312

313 --这样写是错误的.到底在select后加上聚合函数的应用?????

314

315 --select e.ename,loc,max(m.sal) ,min(m.sal),grade from emp e,emp m ,dept salgrade where e.deptno=dept.deptno and e.sal beteeen losal and hissal

316 --m.sal between losal and hisal and grade = 2 group by e.ename , loc, grade;

317

318

319 select ename , loc ,(select min(sal) from emp,salgrade where sal between losal and hisal and grade = 2) minsal,(select max(sal) fromemp,salgrade320 where sal between losal and hisal and grade = 2) maxsal from emp,salgrade ,dept where emp.deptno = dept.deptno and sal between

321 losal and hisal and grade = 2;322 +--------+----------+--------+--------+

324 +--------+----------+--------+--------+

325 | WARD | CHICAGO | 1250 | 1300 |

326 | MARTIN | CHICAGO | 1250 | 1300 |

327 | MILLER | NEW YORK | 1250 | 1300 |

328 +--------+----------+--------+--------+

329

330 22.工资等级多于smith的员工信息。331 --获得员工信息,三个表中有的信息都获取出来,就用到三个表.

332 --需要用到两个表 emp表和salgrade 表

333 --select * from emp,salgrade where grade >(select sal from emp,salgrade where sal between losal and hisal);

334 --select * from emp ee,salgrade ss,dept dd where grade > (select grade from salgrade, emp where ename = 'smith' and sal between losal and ss.hisal )

335 --and sal > (select sal from em );

336 select * from emp,salgrade where sal between losal and hisal and grade >(select grade from emp,salgrade where sal betweenlosal337 and hisal and ename = 'smith');338 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+

340 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+

341 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 3 | 1401 | 2000 |

342 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 2 | 1201 | 1400 |

343 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 4 | 2001 | 3000 |

344 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 2 | 1201 | 1400 |

345 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 4 | 2001 | 3000 |

346 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 4 | 2001 | 3000 |

347 | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | 4 | 2001 | 3000 |

348 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 5 | 3001 | 9999 |

349 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 3 | 1401 | 2000 |

350 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 4 | 2001 | 3000 |

351 | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | 2 | 1201 | 1400 |

352 +-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+

灰白光

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫