Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to carry out mysql multi-table query. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

First, prepare the database

The init.sql file is as follows:

/ * data import: Navicat Premium Data Transfer Source Server: localhost Source Server Type: MySQL Source Server Version: 50624 Source Host: localhost Source Database: sqlexam Target Server Type: MySQL Target Server Version: 50624 File Encoding: utf-8 Date: 10 localhost Source Server Type 21 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS 06:46:46 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0 -Table structure for `class`-DROP TABLE IF EXISTS `class`; CREATE TABLE `class` (`cid` int (11) NOT NULL AUTO_INCREMENT, `caption` varchar (32) NOT NULL, PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 -Records of `class`-BEGIN INSERT INTO `class` VALUES (Class 1, Class 2, Class 3), (Class 2, Class 3, Class 3), Class 3, Class 2, Class 4, Class 9); COMMIT -Table structure for `room`-DROP TABLE IF EXISTS `room` CREATE TABLE `room` (`cid` int (11) NOT NULL AUTO_INCREMENT, `cname` varchar (32) NOT NULL, `teacher_ id` int (11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_ teacher` (`teacher_ id`), CONSTRAINT `fk_course_ teacher` FOREIGN KEY (`teacher_ id`) REFERENCES `teacher` (`tid`) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 -Records of `room`-BEGIN INSERT INTO `biology 'VALUES (' 1', 'biology','1'), ('2', 'physics','2'), ('3', 'sports','3'), ('4', 'art','2'); COMMIT -Table structure for `score`-DROP TABLE IF EXISTS `score` CREATE TABLE `score` (`sid`int (11) NOT NULL AUTO_INCREMENT, `student_ id` int (11) NOT NULL, `course_ id` int (11) NOT NULL, `num`int (11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_ student` (`student_ id`), KEY `fk_score_ student` (`course_ id`), CONSTRAINT `fk_score_ student`FOREIGN KEY (`course_ id`) REFERENCES `room` (`cid`), CONSTRAINT `fk_score_ student` FOREIGN KEY (`student_ id`) REFERENCES `student` (`sid`) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 -Records of `score`-BEGIN INSERT INTO `score` VALUES (1, 1, 1, 1, 10), (2, 1, 2, 9, 9), (5, 1, 4, 66), (6, 2, 1, 1, 8), (8, 2, 3, 68), (9, 2, 4, 4') ), ('10, 3, 1, 77), (11, 3, 2, 66), (12, 3, 3, 3, 87), (13, 3, 4, 99), (14, 4, 1, 79), (15, 4, 2) ), ('16, 4, 3, 67), (17, 4, 4, 4, 100), (18, 5, 1, 79), (19, 5, 2, 11), (20, 5, 3, 67), (21, 5, 4, 4) ), ('22, 6, 1, 9), (23, 6, 2, 100), (24, 6, 3, 67), (25, 6, 4, 100), (26, 7, 1, 9), (27, 7, 2) ), ('28, 7, 3, 67), (29, 7, 4, 88), (30, 8, 1, 9), (31, 8, 2, 100), (32, 8, 3, 67), (33, 8, 4, 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) ), ('40, 10, 3, 43), (41, 10, 4, 87), (42, 11, 1, 90), (43, 11, 2, 77), (44, 11, 3, 43), (45, 11, 4, 4) ), ('46, 12, 1, 90), (47, 12, 2, 77), (48, 12, 3, 43), (49, 12, 4, 87), (52, 13, 3, 87) COMMIT;-- Table structure for `student`-DROP TABLE IF EXISTS `student` CREATE TABLE `student` (`sid` int (11) NOT NULL AUTO_INCREMENT, `gender` char (1) NOT NULL, `sname` int (11) NOT NULL, `sname` varchar (32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_ class` (`class_ id`), CONSTRAINT `fk_ class` FOREIGN KEY (`class_ id`) REFERENCES `class` (`cid`) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 -Records of `student`-BEGIN INSERT INTO `student` VALUES ('1Qing,' male', '1Qing,' understand'), ('2', 'female','1', 'steel egg'), ('3', 'male','1', 'Zhang San'), ('4Qing,' male', '1Qing,' Zhang Yi'), ('5Qing,' female', '1Qing,' Zhang er'), ('6Qing,' male') '1Qing,' Zhang Si'), ('7', 'female','2', 'hammer'), ('8', 'male','2','Li San'), ('9', 'male','2','Li Yi'), ('10', 'female','2','Li er'), ('11', 'male','2','Li Si') ('12, 'female','3', 'Ruhua'), ('13', 'male','3', 'Liu San'), ('14', 'male','3', 'Liu Yi'), ('15', 'female','3', 'Liu er'), ('16', 'male','3', 'Liu Si') COMMIT;---Table structure for `teacher`-DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` (`tid` int (11) NOT NULL AUTO_INCREMENT, `tname` varchar (32) NOT NULL, PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 -Records of `teacher`-BEGIN INSERT INTO `teacher` VALUES ('1Qing,' Mr. Zhang Lei'), ('2Qing,' Mr. Li Ping'), ('3Qing,' Mr. Liu Haiyan'), ('4Qing,' Mr. Zhu Yunhai'), ('5Qing,' Mr. Li Jie'); COMMIT;SET FOREIGN_KEY_CHECKS = 1

1. Create a database and import data

Mysql > create database db1

Mysql > create database db1

Mysql > use db1

Mysql > source / root/init.sql

2. View the table

Mysql > show tables;+-+ | Tables_in_db1 | +-+ | class | | course | | score | | student | | teacher | +-+ mysql > select * from class +-+-+ | cid | caption | +-+-+ | 1 | Class 2 in three years | 2 | Class 3 in three years | | 3 | Class 2 in one year | | 4 | Class 9 in two years | +-+-+ mysql > select * from course +-+ | cid | cname | teacher_id | +-+ | 1 | Biology | 1 | 2 | Physics | 2 | 3 | Sports | 3 | 4 | Art | 2 | +-- -- + mysql > select * from teacher +-+-+ | tid | tname | +-+-+ | 1 | Zhang Lei | | 2 | Li Ping | | 3 | Liu Haiyan | | 4 | Zhu Yunhai | | 5 | Mr. Li Jie | +-- -+-+ mysql > select * from student +-+ | sid | gender | class_id | sname | +-+ | 1 | male | 1 | understanding | | 2 | female | 1 | Steel Egg | | 3 | male | 1 | Zhang San | 4 | male | 1 | Zhang Yi | 5 | female | 1 | Zhang 2 | 6 | male | 1 | Zhang Si | 7 | female | 2 | Hammer | | 8 | male | 2 | Li San | 9 | male | 2 | Li Yi | 10 | female | 2 | Li 2 | 11 | male | 2 | Li Si | 12 | female | 3 | Ruhua | | 13 | male | 3 | Liu San | | 14 | male | 3 | Liu Yi | | 15 | female | 3 | Liu 2 | | 16 | male | 3 | Liu Si | +-+ mysql > select * from score +-+ | sid | student_id | course_id | num | +-+ | 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 | 77 | 11 | 3 | 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 | 5 | | 3 | 67 | | 21 | 5 | 4 | 100 | 22 | 6 | 1 | 9 | 23 | 6 | 2 | 100 | 24 | 6 | 3 | 67 | 25 | 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 | 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 | 43 | 49 | 12 | 4 | 87 | 52 | 13 | 3 | 87 | +-+

Second, operation exercises

1. Query the names of all courses and the names of the corresponding teachers.

SELECT course.cname,teacher.tname FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid;SELECT course.cname,teacher.tname FROM course JOIN teacher ON course.teacher_id = teacher.tid +-+-+ | cname | tname | +-+-+ | Biology | Mr. Zhang Lei | | Physics | Mr. Li Ping | | Art | Mr. Li Ping | | physical Education | Mr. Liu Haiyan | + -. -+-+

2. Inquire about the number of boys and girls in the student table

SELECT gender gender, count (1) number FROM student GROUP BY gender;+-+-+ | gender | number | +-+-+ | female | 6 | male | 10 | +-+-+

3. Inquire the names of students whose physics scores are equal to 100.

SELECT student.sname FROM student WHERE sid IN (SELECT student_id FROM score INNER JOIN course ON score.course_id = course.cid WHERE course.cname = 'physical' AND score.num = 100); +-+ | sname | +-+ | Zhang Si | | Hammer | | Li San | +-+

4. Inquire about the names and average scores of students whose average grade point is greater than 80

SELECT student.sname,t1.avg_num FROM student INNER JOIN (SELECT student_id,avg (num) AS avg_num FROM score GROUP BY student_id HAVING avg (num) > 80) AS T1 ON student.sid = t1.student_id +-+-+ | sname | avg_num | +-+-+ | Zhang San | 82.2500 | | Liu San | 87.0000 | +-+-+ SELECT student.sname, avg (num) AS avg_num FROM score JOIN student ON student.sid = score.student_id GROUP BY student_id HAVING avg_num > 80

5. Inquire about the student number, name, number of courses taken and total score of all students

SELECT student.sid,student.sname,t1.course_num,t1.total_num FROM student LEFT JOIN (SELECT student_id,COUNT (course_id) course_num,sum (num) total_num FROM score GROUP BY student_id) AS T1 ON student.sid = t1.student_id +-+ | sid | sname | course_num | total_num | +-+ | 1 | understanding | 3 | 85 | | 2 | Steel Egg | | 3 | 175 | | 3 | Zhang San | 4 | 329 | | 4 | Zhang Yi | 4 | 257 | 5 | Zhang er | 4 | 257 | | 6 | Zhang Si | 4 | 276 | | 7 | Hammer | 4 | 264 | 8 | Li San | 4 | 264 | | 9 | | Li Yi | 4 | 268 | | 10 | Li er | 4 | 297 | | 11 | Li Si | 4 | 297 | | 12 | Ruhua | 4 | 297 | | 13 | Liu San | 1 | 87 | 14 | Liu Yi | NULL | NULL | 15 | Liu 2 | NULL | NULL | 16 | Liu Si | NULL | NULL | +-+

6. Inquire about the number of teachers surnamed Li

SELECT count (tid) FROM teacher WHERE tname LIKE'Li%'; +-+ | count (tid) | +-+ | 2 | +-+

7. Inquire about the names of the students who did not report Mr. Li Ping's class.

SELECT student.sname FROM student WHERE sid NOT IN (SELECT DISTINCT student_id FROM score WHERE course_id IN (SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = 'Mr. Li Ping')); +-+ | sname | +-+ | Liu San | | Liu Yi | | Liu 2 | | Liu Si | +-+

8. Check the student numbers of students whose physics courses are higher than those of biology courses.

SELECT t1.student_id FROM (SELECT student_id,num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = 'physical')) AS t1INNER JOIN (SELECT student_id,num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = 'biological')) AS T2 ON t1.student_id = t2.student_id WHERE t1.num > t2.num +-+ | student_id | +-+ | 6 | | 7 | 8 | +-+

9. Inquire the names of students who do not take physics courses and physical education courses at the same time

SELECT student.sname FROM student WHERE sid IN (SELECT student_id FROM score WHERE course_id IN (SELECT cid FROM course WHERE cname = 'physical' OR cname = 'sports') GROUP BY student_id HAVING COUNT (course_id) = 1); +-+ | sname | +-+ | understanding | | Steel Egg | | Liu San | +-+

10. Inquire about the names and classes of students who fail more than two subjects (including two)

SELECT student.sname,class.caption FROM student INNER JOIN (SELECT student_id FROM score WHERE num

< 60 GROUP BY student_id HAVING count(course_id) >

= 2) AS t1INNER JOIN class ON student.sid = t1.student_id AND student.class_id = class.cid;+-+-+ | sname | caption | +-+-+ | understand | Class 2 in three years | +-+-+

11. Inquire the names of the students who have taken all the courses

SELECT student.sname FROM student WHERE sid IN (SELECT student_id FROM score GROUP BY student_id HAVING COUNT (course_id) = (SELECT count (cid) FROM course)); +-+ | sname | +-+ | Zhang San | | Zhang Yi | | Zhang er | | Zhang Si | | Hammer | | Li San | | Li Yi | | Li II | | Li Si | Ruhua | +-+ |

12. Check the records of all the courses taught by teacher Li Ping

SELECT * FROM score WHERE course_id IN (SELECT cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = 'Mr. Li Ping') +-+ | sid | student_id | course_id | num | +-+ | 2 | 1 | 2 | 9 | 11 | 3 | 2 | 66 | | 15 | 4 | 2 | 11 | 19 | 5 | 2 | 11 | 23 | 6 | 2 | 100 | 27 | 7 | 2 | 100 | 31 | 8 | 2 | 100 | 35 | 9 | 2 | 88 | 39 | 10 | 2 | 77 | 43 | 11 | | | 2 | 77 | | 47 | 12 | 2 | 77 | | 5 | 1 | 4 | 66 | 9 | 2 | 4 | 99 | 13 | 3 | 4 | 99 | 17 | 4 | 100 | 21 | 5 | 4 | 100 | 25 | 6 | 4 | | | 100 | | 29 | 7 | 4 | 88 | 33 | 8 | 4 | 88 | 37 | 9 | 4 | 22 | 41 | 10 | 4 | 87 | | 45 | 11 | 4 | 87 | 49 | 12 | 4 | 87 | +-| -+

13. Query the course number and name of all the students who have taken the course.

SELECT cid,cname FROM course WHERE cid IN (SELECT course_id FROM score GROUP BY course_id HAVING COUNT (student_id) = (SELECT COUNT (sid) FROM student))

14. Inquire about the number of electives for each course

SELECT course_id,COUNT (student_id) FROM score GROUP BY course_id +-+-+ | course_id | COUNT (student_id) | +-+-+ | 1 | 12 | 2 | 11 | 3 | 12 | | 4 | 12 | +-+-+

15. Inquire about the name and student number of the students who have taken a course.

SELECT sid,sname FROM student WHERE sid IN (SELECT student_id FROM score GROUP BY student_id HAVING COUNT (course_id) = 1); +-+-+ | sid | sname | +-+-+ | 13 | Liu San | +-+-+

16. Check the scores of all students and sort them from high to low (grades are removed)

SELECT DISTINCT num FROM score ORDER BY num DESC;+-+ | num | +-+ | 100 | 99 | 91 | 90 | 88 | 87 | 79 | 77 | 68 | 67 | 66 | 43 | 22 | 11 | 10 | 9 | 8 | +-+

17. Inquire about the names and average scores of students whose GPA is greater than 85

SELECT sname,t1.avg_num FROM student INNER JOIN (SELECT student_id,avg (num) avg_num FROM score GROUP BY student_id HAVING AVG (num) > 85) T1 ON student.sid = t1.students.Mushroom muster + | sname | avg_num | +-+-+ | Liu San | 87.0000 | +-+-+

18. Inquire the names and corresponding biological scores of students who fail in biology.

SELECT sname name, num biology score FROM scoreLEFT JOIN course ON score.course_id = course.cidLEFT JOIN student ON score.student_id = student.sidWHERE course.cname = 'biology' AND score.num

< 60;+--------+--------------+| 姓名 | 生物成绩 |+--------+--------------+| 理解 | 10 || 钢蛋 | 8 || 张四 | 9 || 铁锤 | 9 || 李三 | 9 |+--------+--------------+ 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名 SELECT sname FROM student WHERE sid = ( SELECT student_id FROM score WHERE course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平老师') GROUP BY student_id ORDER BY AVG(num) DESC LIMIT 1 );+--------+| sname |+--------+| 张四 |+--------+ 20、查询每门课程成绩最好的前两名学生姓名 #查看每门课程按照分数排序的信息，为下列查找正确与否提供依据SELECT * FROM score ORDER BY course_id,num DESC;+-----+------------+-----------+-----+| sid | student_id | course_id | num |+-----+------------+-----------+-----+| 34 | 9 | 1 | 91 || 46 | 12 | 1 | 90 || 42 | 11 | 1 | 90 || 38 | 10 | 1 | 90 || 14 | 4 | 1 | 79 || 18 | 5 | 1 | 79 || 10 | 3 | 1 | 77 || 1 | 1 | 1 | 10 || 30 | 8 | 1 | 9 || 26 | 7 | 1 | 9 || 22 | 6 | 1 | 9 || 6 | 2 | 1 | 8 || 31 | 8 | 2 | 100 || 23 | 6 | 2 | 100 || 27 | 7 | 2 | 100 || 35 | 9 | 2 | 88 || 47 | 12 | 2 | 77 || 43 | 11 | 2 | 77 || 39 | 10 | 2 | 77 || 11 | 3 | 2 | 66 || 19 | 5 | 2 | 11 || 15 | 4 | 2 | 11 || 2 | 1 | 2 | 9 || 52 | 13 | 3 | 87 || 12 | 3 | 3 | 87 || 8 | 2 | 3 | 68 || 20 | 5 | 3 | 67 || 36 | 9 | 3 | 67 || 16 | 4 | 3 | 67 || 24 | 6 | 3 | 67 || 32 | 8 | 3 | 67 || 28 | 7 | 3 | 67 || 48 | 12 | 3 | 43 || 44 | 11 | 3 | 43 || 40 | 10 | 3 | 43 || 21 | 5 | 4 | 100 || 17 | 4 | 4 | 100 || 25 | 6 | 4 | 100 || 9 | 2 | 4 | 99 || 13 | 3 | 4 | 99 || 29 | 7 | 4 | 88 || 33 | 8 | 4 | 88 || 41 | 10 | 4 | 87 || 49 | 12 | 4 | 87 || 45 | 11 | 4 | 87 || 5 | 1 | 4 | 66 || 37 | 9 | 4 | 22 |+-----+------------+-----------+-----+#表1：求出每门课程的课程course_id,与最高分数first_numSELECT course_id,max(num) first_num FROM score GROUP BY course_id;+-----------+-----------+| course_id | first_num |+-----------+-----------+| 1 | 91 || 2 | 100 || 3 | 87 || 4 | 100 |+-----------+-----------+#表2：去掉最高分，再按照课程分组，取得的最高分，就是第二高的分数second_numSELECT score.course_id,max(num) second_num FROM score INNER JOIN ( SELECT course_id,max(num) first_num FROM score GROUP BY course_id) AS t ON score.course_id = t.course_idWHERE score.num < t.first_num GROUP BY course_id;+-----------+------------+| course_id | second_num |+-----------+------------+| 1 | 90 || 2 | 88 || 3 | 68 || 4 | 99 |+-----------+------------+#将表1和表2联合到一起，得到一张表t3，包含课程course_id与该们课程的first_num与second_numSELECT t1.course_id,t1.first_num,t2.second_num FROM (SELECT course_id, max(num) first_num FROM score GROUP BY course_id) AS t1 INNER JOIN ( SELECT score.course_id,max(num) second_num FROM score INNER JOIN ( SELECT course_id,max(num) first_num FROM score GROUP BY course_id) AS t ON score.course_id = t.course_id WHERE score.num < t.first_num GROUP BY course_id ) AS t2 ON t1.course_id = t2.course_id;+-----------+-----------+------------+| course_id | first_num | second_num |+-----------+-----------+------------+| 1 | 91 | 90 || 2 | 100 | 88 || 3 | 87 | 68 || 4 | 100 | 99 |+-----------+-----------+------------+#查询前两名的学生（有可能出现并列第一或者并列第二的情况）SELECT score.student_id,t3.course_id,t3.first_num,t3.second_num FROM score INNER JOIN ( SELECT t1.course_id,t1.first_num,t2.second_num FROM ( SELECT course_id,max(num) first_num FROM score GROUP BY course_id) AS t1 INNER JOIN ( SELECT score.course_id,max(num) second_num FROM score INNER JOIN (SELECT course_id, max(num) first_num FROM score GROUP BY course_id) AS t ON score.course_id = t.course_id WHERE score.num < t.first_num GROUP BY course_id) AS t2 ON t1.course_id = t2.course_id) AS t3 ON score.course_id = t3.course_id WHERE score.num >

= t3.second_num AND score.num = t3.second_num AND score.num

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.