MySQL数据库多表进阶查询练习⼀、数据库建表语句
#创建表及插⼊记录
CREATE TABLE class (
cid int(11) NOT NULL AUTO_INCREMENT,
caption varchar(32) NOT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;
#插⼊班级数据
INSERT INTO class VALUES
(1, '三年⼆班'),
(2, '三年三班'),
(3, '⼀年⼆班'),
(4, '⼆年九班');
CREATE TABLE teacher(
tid int(11) NOT NULL AUTO_INCREMENT,
tname varchar(32) NOT NULL,
PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO teacher VALUES
(1, '爱因斯坦⽼师'),
(2, '⽜顿⽼师'),
(3, '⽼王⽼师'),
(4, '华罗庚⽼师'),
(5, '郎朗⽼师');
CREATE TABLE student(
json如何解析sid int(11) NOT NULL AUTO_INCREMENT,
gender char(1) NOT NULL,
class_id 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 DEFAULT CHARSET=utf8;
INSERT INTO student VALUES
(1, '男', 1, '理解'),
(2, '⼥', 1, '钢蛋'),
(3, '男', 1, '张三'),
(4, '男', 1, '张⼀'),
(5, '⼥', 1, '张⼆'),
(6, '男', 1, '张四'),
(7, '⼥', 2, '铁锤'),
(8, '男', 2, '李三'),
(9, '男', 2, '李⼀'),
(10, '⼥', 2, '李⼆'),mysql语句多表查询
(11, '男', 2, '李四'),
(12, '⼥', 3, '如花'),
(13, '男', 3, '刘三'),
(14, '男', 3, '刘⼀'),
(15, '⼥', 3, '刘⼆'),wordpress地址和站点地址
(16, '男', 3, '刘四');
添加数据
CREATE TABLE course(
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 DEFAULT CHARSET=utf8;
INSERT INTO course VALUES
(1, '⽣物', 1),
(2, '物理', 2),
(3, '体育', 3),
(4, '美术', 2);
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_course (course_id),
CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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),
apache的配置文件在哪
(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),
(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);
三、解答题⽬
1、查询所有的课程的名称以及对应的任课⽼师姓名
SELECT c.cid cid,came cname ,t.tname tname ,t.`tid` tid
FROM course c
LEFT JOIN teacher t
ON c.`teacher_id`=t.`tid`;
2、查询学⽣表中男⼥⽣各有多少⼈
SELECT  COUNT(CASE WHEN s.`gender`='男' THEN s.`gender` END)AS 男,
COUNT(CASE WHEN s.`gender`='⼥' THEN s.`gender` END )AS ⼥
FROM student s;
3、查询物理成绩等于100的学⽣的姓名
SELECT stu.`sid` sid,stu.`sname` sname ,stu.`class_id` cid,s.`num`
FROM score s
LEFT JOIN student stu ON s.`student_id`=stu.`sid`
WHERE s.`course_id`=(SELECT cid FROM course WHERE cname='物理') AND s.`num`=100;
4、查询平均成绩⼤于⼋⼗分的同学的姓名和平均成绩
SELECT stu.`sid` stu_id ,stu.`sname` sname ,s.avg_num
FROM student stu, (SELECT student_id , AVG(num) AS avg_num
foreach循环遍历数组参数FROM score
GROUP BY student_id
HAVING AVG(num)>=80) AS s
WHERE s.student_id=stu.`sid`;
5、查询所有学⽣的学号，姓名，选课数，总成绩
SELECT stu.`sid` 学⽣学号 ,stu.`sname` 学⽣姓名 ,s.`学⽣选课总数` 学⽣选课总数,s.`学⽣总成绩` 学⽣总成绩
FROM student stu,
(SELECT s.`student_id` sid ,COUNT(s.`course_id`) 学⽣选课总数                ,SUM(s.`num`) 学⽣总成绩
FROM score s
GROUP BY s.`student_id`) AS s
WHERE stu.`sid`=s.sid ;
7、查询没有报⽜顿⽼师课的学⽣姓名(⾸先在查'⽜顿⽼师'所教课程的id，然后在成绩表中查有对应课程成绩的学号，最后判断哪个学⽣不该表中)    SELECT *
FROM student stu
WHERE stu.`sid` NOT IN (
SELECT DISTINCT s.`student_id`
FROM score s
WHERE s.`course_id` IN (
SELECT c.`cid` cid
FROM course c
WHERE c.`teacher_id`=(
SELECT tid
FROM teacher
WHERE tname = '⽜顿⽼师')));
8、查询物理课程⽐⽣物课程⾼的学⽣的学号(⾸先查，⽣物成绩表和物理成绩表，然后左连接通过学⽣id，然后使⽤where判断条件对数据进⾏筛选)
SELECT t1.sid sid,t1.s_num s_num,t2.w_num w_num
FROM (SELECT s.`student_id` sid ,s.`course_id`,s.`num` s_num
FROM score s
WHERE s.`course_id`=(SELECT c.`cid`
FROM course c
WHERE c.`cname`='⽣物')) AS t1
LEFT JOIN (SELECT s.`student_id`  sid ,s.`course_id`,s.`num` w_num
FROM score s
WHERE s.`course_id`=(SELECT c.`cid`
FROM course c
WHERE c.`cname`='物理')) AS t2
ON t1.sid=t2.sid
WHERE t1.s_num<t2.w_num;
9、查询没有同时选修物理课程和体育课程的学⽣姓名(⾸先查询物理课程和⽣物课程的cid，然后从成绩表中查选修过这两门课的同学，然后使⽤sid对其进⾏分组，    SELECT stu.`sname` sname
FROM student stu
WHERE stu.`sid` IN(SELECT s.`student_id`
FROM score s
WHERE s.`course_id` IN (SELECT cid
FROM course
WHERE cname = '物理' OR cname = '体育')
GROUP BY s.`student_id`
HAVING COUNT(s.`course_id`)=1);
10、查询挂科超过两门(包括两门)的学⽣姓名和班级(⾸先查询成绩低于60超过两门的学⽣的id，然后通过学⽣id和学⽣表关联，查询学⽣信息，最后通过学⽣的班级id    SELECT stu.`sid`,stu.`sname`,class.`caption`
FROM student stu
LEFT JOIN class ON stu.`class_id`=class.`cid`
WHERE stu.`sid` IN(SELECT s.`student_id`
FROM score s
WHERE s.`num`<60
GROUP BY s.`student_id`
HAVING COUNT(s.`course_id`)>=2);
11、查询选修了所有课程的学⽣姓名(⾸先查询课程的总数，然后查看学⽣的成绩表，成绩等于总科⽬
数的表⽰选修了所有课程，最后根据学⽣id查学⽣信息)
SELECT DISTINCT stu.`sid` sid ,stu.`sname` sname
FROM student stu
WHERE stu.`sid` IN(SELECT s.`student_id`
FROM score s
GROUP BY s.`student_id`
HAVING COUNT(s.`course_id`)=(SELECT COUNT(cid)
FROM course ));
12、查询⽜顿⽼师教的课程的所有成绩记录(⾸先查询‘⽜顿⽼师‘所教课程的课程id，然后将学⽣表和成绩表拼接，查看成绩表中存在包含⽜顿⽼师课程的id，进⾏筛选)    SELECT DISTINCT s.`student_id` sid , stu.`sname` NAME,s.`course_id` cid ,s.`num` num
FROM score s
LEFT JOIN student stu
ON s.`student_id`=stu.`sid`风景网页模板图片
WHERE s.`course_id` IN (SELECT c.`cid` cid
FROM course c
WHERE c.`teacher_id`=(SELECT tid
FROM teacher
WHERE tname = '⽜顿⽼师')) ;
13、查询每门课程被选修的次数
SELECT c.`cid` cid,c.`cname` 课程,COUNT(s.`student_id`) 选修次数
FROM score s
LEFT JOIN course c
ON s.`course_id`=c.`cid`
GROUP BY s.`course_id`;
14、查询全部学⽣都选修了的课程号和课程名
SELECT c.`cid` 课程号,c.`cname` 课程名
FROM course c
WHERE c.`cid`IN (SELECT course_id
FROM score
GROUP BY course_id
HAVING COUNT(sid) = (SELECT COUNT(sid)
FROM student)-12);
15、查询之选修了⼀门课程的学⽣姓名和学号
SELECT stu.`sid` 学号,stu.`sname` 学⽣姓名,same 课程名称
FROM student stu
LEFT JOIN (SELECT * FROM score
LEFT JOIN course
ON score.`course_id`=course.`cid`) s
ON s.`student_id`=stu.`sid`
GROUP BY stu.`sid`
HAVING COUNT(s.`course_id`)=1;
16、查询所有学⽣考出的成绩并按从⾼到低排序（成绩去重）
SELECT DISTINCT num
FROM score
ORDER BY num DESC;
17、查询平均成绩⼤于85的学⽣姓名和平均成绩
SELECT stu.`sname` 学⽣姓名,AVG(s.`num`) 平均成绩
FROM score s
LEFT JOIN student stu
ON s.`student_id`=stu.`sid`
GROUP BY s.`student_id`
HAVING AVG(s.`num`)>=85;
18、查询⽣物成绩不及格的学⽣姓名和对应⽣物分数
SELECT stu.`sname`,s.`num`
FROM score s
LEFT JOIN student stu
ON s.`sid`=stu.`sid`
LEFT JOIN course c
ON s.`course_id`=c.`cid`
WHERE c.`cname`='⽣物' AND s.`num`<60 AND stu.`sname` IS NOT NULL;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。