Mysql_多表查询练习
下面是数据初始化，在本地创建sql文件，拷贝代码内容，创建数据库，然后在数据库里source执行sql文件。
1 /* 2 数据导入： 3 Navicat Premium Data Transfer 4 5 Source Server : localhost 6 Source Server Type : MySQL 7 Source Server Version : 50624 8 Source Host : localhost 9 Source Database : sqlexam 10 11 Target Server Type : MySQL 12 Target Server Version : 50624 13 File Encoding : utf-8 14 15 Date: 10/21/2016 06:46:46 AM 16 */ 17 18 SET NAMES utf8; 19 SET FOREIGN_KEY_CHECKS = 0; 20 21 -- ---------------------------- 22 -- Table structure for `class` 23 -- ---------------------------- 24 DROP TABLE IF EXISTS `class`; 25 CREATE TABLE `class` ( 26 `cid` int(11) NOT NULL AUTO_INCREMENT, 27 `caption` varchar(32) NOT NULL, 28 PRIMARY KEY (`cid`) 29 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 30 31 -- ---------------------------- 32 -- Records of `class` 33 -- ---------------------------- 34 BEGIN; 35 INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); 36 COMMIT; 37 38 -- ---------------------------- 39 -- Table structure for `course` 40 -- ---------------------------- 41 DROP TABLE IF EXISTS `course`; 42 CREATE TABLE `course` ( 43 `cid` int(11) NOT NULL AUTO_INCREMENT, 44 `cname` varchar(32) NOT NULL, 45 `teacher_id` int(11) NOT NULL, 46 PRIMARY KEY (`cid`), 47 KEY `fk_course_teacher` (`teacher_id`), 48 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) 49 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 50 51 -- ---------------------------- 52 -- Records of `course` 53 -- ---------------------------- 54 BEGIN; 55 INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); 56 COMMIT; 57 58 -- ---------------------------- 59 -- Table structure for `score` 60 -- ---------------------------- 61 DROP TABLE IF EXISTS `score`; 62 CREATE TABLE `score` ( 63 `sid` int(11) NOT NULL AUTO_INCREMENT, 64 `student_id` int(11) NOT NULL, 65 `course_id` int(11) NOT NULL, 66 `num` int(11) NOT NULL, 67 PRIMARY KEY (`sid`), 68 KEY `fk_score_student` (`student_id`), 69 KEY `fk_score_course` (`course_id`), 70 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), 71 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) 72 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; 73 74 -- ---------------------------- 75 -- Records of `score` 76 -- ---------------------------- 77 BEGIN; 78 INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); 79 COMMIT; 80 81 -- ---------------------------- 82 -- Table structure for `student` 83 -- ---------------------------- 84 DROP TABLE IF EXISTS `student`; 85 CREATE TABLE `student` ( 86 `sid` int(11) NOT NULL AUTO_INCREMENT, 87 `gender` char(1) NOT NULL, 88 `class_id` int(11) NOT NULL, 89 `sname` varchar(32) NOT NULL, 90 PRIMARY KEY (`sid`), 91 KEY `fk_class` (`class_id`), 92 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) 93 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; 94 95 -- ---------------------------- 96 -- Records of `student` 97 -- ---------------------------- 98 BEGIN; 99 INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四'); 100 COMMIT; 101 102 -- ---------------------------- 103 -- Table structure for `teacher` 104 -- ---------------------------- 105 DROP TABLE IF EXISTS `teacher`; 106 CREATE TABLE `teacher` ( 107 `tid` int(11) NOT NULL AUTO_INCREMENT, 108 `tname` varchar(32) NOT NULL, 109 PRIMARY KEY (`tid`) 110 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 111 112 -- ---------------------------- 113 -- Records of `teacher` 114 -- ---------------------------- 115 BEGIN; 116 INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师'); 117 COMMIT; 118 119 SET FOREIGN_KEY_CHECKS = 1;
题目如下：
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询学生表中男女生各有多少人
3、查询物理成绩等于100的学生的姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
5、查询所有学生的学号，姓名，选课数，总成绩
6、 查询姓李老师的个数
7、 查询没有报李平老师课的学生姓名
8、 查询物理课程比生物课程高的学生的学号
9、 查询没有同时选修物理课程和体育课程的学生姓名
10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名
12、查询李平老师教的课程的所有成绩记录
13、查询全部学生都选修了的课程号和课程名
14、查询每门课程被选修的次数
15、查询之选修了一门课程的学生姓名和学号
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
17、查询平均成绩大于85的学生姓名和平均成绩
18、查询生物成绩不及格的学生姓名和对应生物分数
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
20、查询每门课程成绩最好的前两名学生姓名
21、查询不同课程但成绩相同的学号，课程号，成绩
22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
24、任课最多的老师中学生单科成绩最高的学生姓名
答案：
1 -- 1、查询所有的课程的名称以及对应的任课老师姓名 2 SELECT 3 c.cname, 4 t.tname 5 FROM 6 course c 7 LEFT JOIN teacher t ON c.teacher_id = t.tid; 8 9 -- 2、查询学生表中男女生各有多少人 10 SELECT 11 gender, 12 count(sid)count 13 FROM 14 student 15 GROUP BY 16 gender; 17 18 -- 3、查询物理成绩等于100的学生的姓名 19 SELECT 20 a.sid, 21 b.sname 22 FROM 23 score a 24 LEFT JOIN student b ON a.student_id = b.sid 25 WHERE 26 a.num = 100 27 AND a.course_id =( 28 SELECT 29 c.cid 30 FROM 31 course c 32 WHERE 33 c.cname = "物理" 34 ); 35 36 -- 4、查询平均成绩大于八十分的同学的姓名和平均成绩 37 SELECT 38 a.sname, 39 c.avg_score 40 FROM 41 student a 42 INNER JOIN( 43 SELECT 44 b.*, AVG(b.num)avg_score 45 FROM 46 score b 47 GROUP BY 48 b.student_id 49 HAVING 50 avg_score > 80 51 )c ON a.sid = c.student_id; 52 53 -- 5、查询所有学生的学号，姓名，选课数，总成绩 54 SELECT 55 a.sid, 56 a.sname, 57 c.count_course, 58 c.sum_score 59 FROM 60 student a 61 LEFT JOIN( 62 SELECT 63 b.student_id, 64 count(b.course_id)count_course, 65 SUM(b.num)sum_score 66 FROM 67 score b 68 GROUP BY 69 b.student_id 70 )c ON a.sid = c.student_id; 71 72 -- 6、 查询姓李老师的个数 73 SELECT 74 count(tid) 75 FROM 76 teacher t 77 WHERE 78 t.tname LIKE "李%"; 79 80 -- 7、 查询没有报李平老师课的学生姓名 81 SELECT 82 a.sname 83 FROM 84 student a 85 WHERE 86 a.sid NOT IN( 87 SELECT 88 b.student_id 89 FROM 90 score b 91 WHERE 92 course_id IN( 93 SELECT 94 c.cid 95 FROM 96 course c 97 WHERE 98 c.teacher_id IN( 99 SELECT 100 d.tid 101 FROM 102 teacher d 103 WHERE 104 d.tname = "李平老师" 105 ) 106 ) 107 ); 108 109 -- 8、 查询物理课程比生物课程高的学生的学号 110 SELECT 111 c.student_id 112 FROM 113 ( 114 SELECT 115 * 116 FROM 117 score a 118 WHERE 119 a.course_id =( 120 SELECT 121 b.cid 122 FROM 123 course b 124 WHERE 125 b.cname = "物理" 126 ) 127 )c 128 INNER JOIN( 129 SELECT 130 * 131 FROM 132 score d 133 WHERE 134 d.course_id =( 135 SELECT 136 f.cid 137 FROM 138 course f 139 WHERE 140 f.cname = "生物" 141 ) 142 )g ON c.student_id = g.student_id 143 AND c.num > g.num; 144 145 -- 9、 查询没有同时选修物理课程和体育课程的学生姓名 146 SELECT 147 a.sname 148 FROM 149 student a 150 WHERE 151 a.sid NOT IN( 152 SELECT 153 d.student_id 154 FROM 155 ( 156 SELECT 157 * 158 FROM 159 score b 160 WHERE 161 b.course_id =( 162 SELECT 163 c.cid 164 FROM 165 course c 166 WHERE 167 c.cname = "物理" 168 ) 169 )d 170 INNER JOIN( 171 SELECT 172 * 173 FROM 174 score f 175 WHERE 176 f.course_id =( 177 SELECT 178 g.cid 179 FROM 180 course g 181 WHERE 182 g.cname = "体育" 183 ) 184 )h ON d.student_id = h.student_id 185 ); 186 187 -- 10、查询挂科超过两门(包括两门)的学生姓名和班级 188 SELECT 189 c.sname, 190 c.caption 191 FROM 192 ( 193 SELECT 194 * 195 FROM 196 student a 197 LEFT JOIN class b ON a.class_id = b.cid 198 )c 199 INNER JOIN( 200 SELECT 201 d.student_id, 202 count(d.student_id)count 203 FROM 204 score d 205 WHERE 206 d.num < 60 207 GROUP BY 208 d.student_id 209 HAVING 210 count >= 2 211 )e ON c.sid = e.student_id; 212 213 -- 11、查询选修了所有课程的学生姓名 214 SELECT 215 s.sname 216 FROM 217 student s 218 INNER JOIN( 219 SELECT 220 a.student_id, 221 count(a.course_id)count_course 222 FROM 223 score a 224 GROUP BY 225 a.student_id 226 HAVING 227 count_course =(SELECT count(*) FROM course) 228 )b ON s.sid = b.student_id; 229 230 -- 12、查询李平老师教的课程的所有成绩记录 231 SELECT 232 a.* 233 FROM 234 score a 235 WHERE 236 a.course_id IN( 237 SELECT 238 b.cid 239 FROM 240 course b 241 WHERE 242 b.teacher_id =( 243 SELECT 244 c.tid 245 FROM 246 teacher c 247 WHERE 248 c.tname = "李平老师" 249 ) 250 ) 251 252 -- 13、查询全部学生都选修了的课程号和课程名 253 SELECT 254 c.cid, 255 c.cname 256 FROM 257 course c 258 INNER JOIN( 259 SELECT 260 a.course_id, 261 count(a.student_id)count_student 262 FROM 263 score a 264 GROUP BY 265 a.course_id 266 HAVING 267 count_student =(SELECT count(*) FROM student) 268 )b ON c.cid = b.course_id; 269 270 -- 14、查询每门课程被选修的次数 271 SELECT 272 c.cid, 273 c.cname, 274 b.count_student 275 FROM 276 course c 277 LEFT JOIN( 278 SELECT 279 a.course_id, 280 count(a.student_id)count_student 281 FROM 282 score a 283 GROUP BY 284 a.course_id 285 )b ON c.cid = b.course_id; 286 287 -- 15、查询只选修了一门课程的学生姓名和学号 288 SELECT 289 s.sname 290 FROM 291 student s 292 INNER JOIN( 293 SELECT 294 a.student_id, 295 count(a.course_id)count_course 296 FROM 297 score a 298 GROUP BY 299 a.student_id 300 HAVING 301 count_course = 1 302 )b ON s.sid = b.student_id; 303 304 -- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重） 305 SELECT DISTINCT 306 (num) 307 FROM 308 score a 309 ORDER BY 310 a.num DESC 311 312 -- 17、查询平均成绩大于85的学生姓名和平均成绩 313 SELECT 314 a.sname 315 FROM 316 student a 317 INNER JOIN( 318 SELECT 319 b.student_id, 320 avg(b.num)avg_num 321 FROM 322 score b 323 GROUP BY 324 b.student_id 325 HAVING 326 avg_num > 85 327 )c ON a.sid = c.student_id; 328 329 -- 18、查询生物成绩不及格的学生姓名和对应生物分数 330 SELECT 331 a.sname, 332 c.num 333 FROM 334 student a 335 INNER JOIN( 336 SELECT 337 b.student_id, 338 b.num 339 FROM 340 score b 341 WHERE 342 b.course_id =( 343 SELECT 344 cid 345 FROM 346 course 347 WHERE 348 cname = "生物" 349 ) 350 AND b.num < 60 351 )c ON a.sid = c.student_id; 352 353 -- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名 354 SELECT 355 sname 356 FROM 357 student 358 WHERE 359 sid =( 360 SELECT 361 a.student_id 362 FROM 363 score a 364 WHERE 365 a.course_id IN( 366 SELECT 367 b.cid 368 FROM 369 course b 370 WHERE 371 b.teacher_id =( 372 SELECT 373 c.tid 374 FROM 375 teacher c 376 WHERE 377 c.tname = "李平老师" 378 ) 379 ) 380 GROUP BY 381 a.student_id 382 ORDER BY 383 avg(num)DESC 384 LIMIT 1 385 ) 386 387 -- 20、查询每门课程成绩最好的前两名学生姓名 388 SELECT 389 e.sname, 390 f.course_id, 391 f.num 392 FROM 393 student e 394 INNER JOIN( 395 SELECT 396 c.* 397 FROM 398 score c 399 INNER JOIN( 400 SELECT 401 * 402 FROM 403 ( 404 SELECT 405 course_id, 406 MAX(num)num 407 FROM 408 score 409 GROUP BY 410 course_id 411 ORDER BY 412 course_id, 413 num DESC 414 )a 415 UNION ALL 416 ( 417 SELECT 418 a.course_id, 419 MAX(a.num)num 420 FROM 421 score a 422 INNER JOIN( 423 SELECT 424 course_id, 425 MAX(num)num 426 FROM 427 score 428 GROUP BY 429 course_id 430 ORDER BY 431 course_id, 432 num DESC 433 )b ON a.course_id = b.course_id 434 WHERE 435 a.num < b.num 436 GROUP BY 437 a.course_id 438 ) 439 )d ON c.course_id = d.course_id 440 WHERE 441 c.num = d.num 442 ORDER BY 443 c.course_id, 444 c.num DESC 445 )f ON e.sid = f.student_id 446 ORDER BY 447 f.course_id, 448 f.num DESC 449 450 -- 21、查询不同课程但成绩相同的学号，课程号，成绩 451 SELECT 452 student_id, 453 course_id, 454 num 455 FROM 456 score 457 WHERE 458 num IN( 459 SELECT 460 a.num 461 FROM 462 ( 463 SELECT 464 * 465 FROM 466 score 467 GROUP BY 468 course_id, 469 num 470 )a 471 GROUP BY 472 a.num 473 HAVING 474 count(a.num)> 1 475 ) 476 ORDER BY 477 num; 478 479 -- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称； 480 SELECT 481 c.sname, 482 GROUP_CONCAT(d.cname) 483 FROM 484 ( 485 SELECT 486 a.*, b.course_id 487 FROM 488 ( 489 SELECT 490 * 491 FROM 492 student 493 WHERE 494 sid NOT IN( 495 SELECT DISTINCT 496 (student_id) 497 FROM 498 score 499 WHERE 500 course_id IN( 501 SELECT 502 cid 503 FROM 504 course 505 WHERE 506 teacher_id =( 507 SELECT 508 tid 509 FROM 510 teacher 511 WHERE 512 tname = "李平老师" 513 ) 514 ) 515 ) 516 )a 517 INNER JOIN score b ON a.sid = b.student_id 518 )c 519 INNER JOIN course d ON c.course_id = d.cid 520 GROUP BY 521 c.sname 522 523 -- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名； 524 SELECT 525 a.sid, 526 a.sname 527 FROM 528 student a 529 INNER JOIN( 530 SELECT 531 student_id 532 FROM 533 score 534 WHERE 535 student_id != 1 536 AND course_id IN( 537 SELECT 538 course_id 539 FROM 540 score 541 WHERE 542 student_id = 1 543 ) 544 GROUP BY 545 student_id 546 )b ON a.sid = b.student_id 547 548 -- 24、任课最多的老师中学生单科成绩最高的学生姓名 549 SELECT 550 sname 551 FROM 552 student 553 WHERE 554 sid =( 555 SELECT 556 student_id 557 FROM 558 score 559 WHERE 560 course_id IN( 561 SELECT 562 cid 563 FROM 564 course 565 WHERE 566 teacher_id =( 567 SELECT 568 teacher_id 569 FROM 570 course 571 GROUP BY 572 teacher_id 573 ORDER BY 574 count(teacher_id)DESC 575 LIMIT 1 576 ) 577 ) 578 ORDER BY 579 num DESC 580 LIMIT 1 581 )
夕闻道不如朝闻道
