mysql多表查询选择题,MySQL中多表查询练习题

-- 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 student (

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, '李二' ),

( 11, '男', 2, '李四' ),

( 12, '女', 3, '如花' ),

( 13, '男', 3, '刘三' ),

( 14, '男', 3, '刘一' ),

( 15, '女', 3, '刘二' ),

( 16, '男', 3, '刘四' );

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 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 ),

( 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 );

-- 1、查询所有的课程的名称以及对应的任课老师姓名

SELECT

c.cname,    -- 课程的名称

t.tname         -- 任课老师姓名

FROM

course c,

teacher t

WHERE

c.teacher_id = t.tid;

-- 2、查询学生表中男女生各有多少人

SELECT

s.gender,

COUNT( s.gender )

FROM

student s

GROUP BY

s.gender;

-- 3、查询物理成绩等于100的学生的姓名

SELECT

s.sname,                -- 学生的姓名

c.cname,                -- 学科的姓名

sc.num                     -- 学科的成绩

FROM

student s

LEFT JOIN score sc ON s.sid = sc.student_id

LEFT JOIN course c ON sc.course_id = c.cid

WHERE

-- 先获取物理学科所对应的编号 (编号为2)

-- 通过编号(course_id) 和 成绩(100) 判断对应符合条件的学生

sc.course_id = ( SELECT c.cid FROM course c WHERE c.cname = '物理' )

AND sc.num = 100;

-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT

s.sname,

AVG( sc.num )

FROM

student s

LEFT JOIN score sc ON s.sid = sc.student_id

GROUP BY

-- 按照学生id 进行分组

sc.student_id

HAVING

AVG( sc.num ) > 80;

-- 5、查询所有学生的学号，姓名，选课数，总成绩

SELECT

s.sid,

s.sname,

COUNT( sc.student_id ) 选课数,

SUM(

IFNULL( sc.num, 0 )) 总成绩

FROM

student s

LEFT JOIN score sc ON s.sid = sc.student_id

GROUP BY

sc.student_id

-- 6、 查询姓李老师的个数

SELECT

COUNT( t.tname )

FROM

teacher t

WHERE

t.tname LIKE '李%';

-- 7、 查询没有报李平老师课的学生姓名

-- 分析: 1,先查李平老师课程号

--                 2,在通过课程号获取  报了李平老师课程的学生id(去重).  得到一个报了李老师课程的,学生id多行单列的表

--                     3,将学生表表于学生id表通过左外连接在一起,把学生id列为null的提取出来就是没有报李平老师课的学生信息

SELECT

s.sname

FROM

student s

LEFT JOIN (

SELECT DISTINCT

student_id

FROM

score

WHERE

course_id IN ( SELECT c.cid FROM course c LEFT JOIN teacher t ON t.tid = c.teacher_id WHERE t.tname = '李平老师' )) t1 ON s.sid = t1.student_id

WHERE

student_id IS NULL;

-- 8、 查询物理课程比生物课程高的学生的学号

SELECT DISTINCT

t1.student_id,

t1.物理成绩,

t2.生物成绩

FROM

(

SELECT

sc.student_id,

sc.num 物理成绩

FROM

score sc

LEFT JOIN course c ON sc.course_id = c.cid

WHERE

sc.course_id = ( SELECT cid FROM course WHERE cname = '物理' )) t1

LEFT JOIN (

SELECT

sc.student_id,

sc.num 生物成绩

FROM

score sc

LEFT JOIN course c ON sc.course_id = c.cid

WHERE

sc.course_id = ( SELECT cid FROM course WHERE cname = '生物' )) t2

ON t1.student_id = t2.student_id

WHERE

t1.物理成绩 > t2.生物成绩

-- 9、 查询没有同时选修物理课程和体育课程的学生姓名

SELECT

s.sname

FROM

student s

LEFT JOIN (

SELECT

t1.student_id

FROM

(

SELECT

sc.student_id

FROM

score sc

LEFT JOIN course c ON sc.course_id = c.cid

WHERE

sc.course_id = ( SELECT cid FROM course WHERE cname = '物理' )) t1

INNER JOIN (

SELECT

sc.student_id

FROM

score sc

LEFT JOIN course c ON sc.course_id = c.cid

WHERE

sc.course_id = ( SELECT cid FROM course WHERE cname = '体育' )) t2 ON t1.student_id = t2.student_id

) t3 ON s.sid = t3.student_id

WHERE

student_id IS NULL;

-- 10、查询挂科超过两门(包括两门)的学生姓名和班级

SELECT

s.sname,cl.caption

FROM

student s

LEFT JOIN score sc ON sc.student_id = s.sid

LEFT JOIN class cl ON s.class_id = cl.cid

WHERE

IFNULL( sc.num, 0 ) < 60 GROUP BY s.sname HAVING COUNT( s.sname ) >= 2

-- 11、查询选修了所有课程的学生姓名

SELECT

s.sname

FROM

student s

LEFT JOIN score sc ON s.sid = sc.student_id

GROUP BY

sc.student_id

HAVING

COUNT( sc.student_id ) = 4;

-- 12、查询李平老师教的课程的所有成绩记录

SELECT

*

FROM

score sc

WHERE

sc.course_id IN ( SELECT c.cid FROM course c LEFT JOIN teacher t ON t.tid = c.teacher_id WHERE t.tname = '李平老师' )

-- 13、查询全部学生都选修了的课程号和课程名

SELECT

t2.课程号,

c.cname

FROM

( SELECT COUNT(*) 学生人数 FROM student ) t1,

course c

LEFT JOIN ( SELECT sc.course_id 课程号, COUNT( sc.course_id ) 课程被选的次数 FROM score sc GROUP BY sc.course_id ) t2 ON c.cid = t2.课程号

WHERE

t1.学生人数 = t2.课程被选的次数

-- 14、查询每门课程被选修的次数

SELECT

sc.course_id 课程号,

COUNT( sc.course_id ) 课程被选的次数

FROM

score sc

GROUP BY

sc.course_id;

-- 15、查询之选修了一门课程的学生姓名和学号

SELECT

s.sname,

s.sid

FROM

student s

LEFT JOIN score sc ON s.sid = sc.student_id

GROUP BY

sc.student_id

HAVING

COUNT( sc.student_id ) = 1;

-- 16、查询所有学生考出的成绩并按从高到低排序(成绩去重)

SELECT

s.sname,

SUM( sc.num )

FROM

student s

LEFT JOIN score sc ON s.sid = sc.student_id

GROUP BY

sc.student_id

ORDER BY

SUM( sc.num ) DESC;

-- 17、查询平均成绩大于85的学生姓名和平均成绩

SELECT

s.sname,

AVG( sc.num )

FROM

student s

LEFT JOIN score sc ON s.sid = sc.student_id

GROUP BY

sc.student_id

HAVING

AVG( sc.num ) > 85;

-- 18、查询生物成绩不及格的学生姓名和对应生物分数

SELECT

s.sname,

sc.num 生物分数

FROM

score sc

LEFT JOIN student s ON sc.student_id = s.sid

WHERE

sc.num < 60

AND sc.course_id = ( SELECT c.cid FROM course c WHERE c.cname = '生物' );