MYSQL练习题54道(包含尽可能多的解决方法及思路)

本文链接：https://blog.youkuaiyun.com/weixin_44936331/article/details/92778180

MYSQL练习题（包含尽可能多的解决方法)

下面的表和题目来自于网上，解题方法和思路及遇到的问题是本人在练习过程中遇到的，在这里分享给大家。为了更好的熟悉sql语句，希望大家在自己做练习的时候，可以用尽量多的方法来解决问题，在这篇MYAQL练习题当中，暂时不考虑查询的速度等问题，在下面的语句中还有不足之处，请大家与我一起讨论如何更正，以及有更好的思路也请与我一起学习进步。(下面非红色字体的大都是有错误的）

–创建teacher表

DROP TABLE IF EXISTS teacher; 
CREATE TABLE teacher(
tid INT NOT NULL PRIMARY KEY,
tname VARCHAR(20) NOT NULL
)DEFAULT CHARSET = 'utf8mb4';

INSERT INTO teacher(tid,tname)VALUES(1,'张老师');
INSERT INTO teacher(tid,tname)VALUES(2,'王老师');
INSERT INTO teacher(tid,tname)VALUES(3,'李老师');
INSERT INTO teacher(tid,tname)VALUES(4,'赵老师');
INSERT INTO teacher(tid,tname)VALUES(5,'刘老师');
INSERT INTO teacher(tid,tname)VALUES(6,'向老师');
INSERT INTO teacher(tid,tname)VALUES(7,'李文静');
INSERT INTO teacher(tid,tname)VALUES(8,'叶平');

–创建student表

 DROP TABLE IF EXISTS student;
    CREATE TABLE student(
    sid INT NOT NULL PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sage DATETIME NOT NULL,
    ssex CHAR(2) NOT NULL
    )DEFAULT CHARSET = 'utf8mb4';

INSERT INTO student(sid,sname,sage,ssex) VALUES(1,'张三','1980-1-23','男');
INSERT INTO student(sid,sname,sage,ssex) VALUES(2,'李四','1982-12-12','男');
INSERT INTO student(sid,sname,sage,ssex) VALUES(3,'张飒','1981-9-9','男');
INSERT INTO student(sid,sname,sage,ssex) VALUES(4,'莉莉','1983-3-23','女');
INSERT INTO student(sid,sname,sage,ssex) VALUES(5,'王弼','1982-6-21','男');
INSERT INTO student(sid,sname,sage,ssex) VALUES(6,'王丽','1984-10-10','女');
INSERT INTO student(sid,sname,sage,ssex) VALUES(7,'刘香','1980-12-22','女');

–创建course表

DROP TABLE IF EXISTS course;
CREATE TABLE course(
cid INT NOT NULL PRIMARY KEY,
cname VARCHAR(20) NOT NULL,
tid INT NOT NULL
)DEFAULT CHARSET = 'utf8mb4';
--增加外键
ALTER TABLE course ADD CONSTRAINT fk_course_teacher
 FOREIGN KEY(tid) REFERENCES teacher(tid);
--表的结构
SHOW INDEX FROM course;

insert into course(cid,cname,tid) values(1,'企业管理',3);
insert into course(cid,cname,tid) values(2,'马克思',1);
insert into course(cid,cname,tid) values(3,'UML',2);
insert into course(cid,cname,tid) values(4,'数据库',5);
insert into course(cid,cname,tid) values(5,'物理',8);

–创建sc表

DROP TABLE IF EXISTS sc;
CREATE TABLE sc(
sid int NOT NULL,
cid int NOT NULL,
score int NOT NULL
)DEFAULT CHARSET = 'utf8mb4';

ALTER TABLE sc ADD CONSTRAINT fk_sc_course
 FOREIGN KEY(cid) REFERENCES course(cid);
ALTER TABLE sc ADD CONSTRAINT fk_sc_student 
FOREIGN KEY(sid) REFERENCES student(sid);

INSERT INTO sc(sid,cid,score)VALUES(1,1,80);
INSERT INTO sc(sid,cid,score)VALUES(1,2,86);
INSERT INTO sc(sid,cid,score)VALUES(1,3,83);
INSERT INTO sc(sid,cid,score)VALUES(1,4,89);
INSERT INTO sc(sid,cid,score)VALUES(2,1,50);
INSERT INTO sc(sid,cid,score)VALUES(2,2,36);
INSERT INTO sc(sid,cid,score)VALUES(2,3,43);
INSERT INTO sc(sid,cid,score)VALUES(2,4,59);
INSERT INTO sc(sid,cid,score)VALUES(3,1,50);
INSERT INTO sc(sid,cid,score)VALUES(3,2,96);
INSERT INTO sc(sid,cid,score)VALUES(3,3,73);
INSERT INTO sc(sid,cid,score)VALUES(3,4,69);
INSERT INTO sc(sid,cid,score)VALUES(4,1,90);
INSERT INTO sc(sid,cid,score)VALUES(4,2,36);
INSERT INTO sc(sid,cid,score)VALUES(4,3,88);
INSERT INTO sc(sid,cid,score)VALUES(4,4,99);
INSERT INTO sc(sid,cid,score)VALUES(5,1,90);
INSERT INTO sc(sid,cid,score)VALUES(5,2,96);
INSERT INTO sc(sid,cid,score)VALUES(5,3,98);
INSERT INTO sc(sid,cid,score)VALUES(5,4,99);
INSERT INTO sc(sid,cid,score)VALUES(6,1,70);
INSERT INTO sc(sid,cid,score)VALUES(6,2,66);
INSERT INTO sc(sid,cid,score)VALUES(6,3,58);
INSERT INTO sc(sid,cid,score)VALUES(6,4,79);
INSERT INTO sc(sid,cid,score)VALUES(7,1,80);
INSERT INTO sc(sid,cid,score)VALUES(7,2,76);
INSERT INTO sc(sid,cid,score)VALUES(7,3,68);
INSERT INTO sc(sid,cid,score)VALUES(7,4,59);
INSERT INTO sc(sid,cid,score)VALUES(7,5,89);

1、查询课程1的成绩比课程2的成绩高的所有学生的学号；

SELECT a.sid
FROM
(SELECT sid,score FROM sc WHERE cid = 01) a ,
(SELECT sid,score FROM sc WHERE cid = 02) b
WHERE a.score>b.score AND a.sid = b.sid;

--这个方法很重要
SELECT * 
FROM (SELECT * FROM sc WHERE cid = 01) a
JOIN (SELECT * FROM sc WHERE cid = 02) b ON a.sid = b.sid
WHERE a.score>b.score;

--用子查询
SELECT a.sid
FROM (SELECT * FROM sc WHERE cid = 01) a,
(SELECT * FROM sc WHERE cid = 02) b
WHERE a.score > b.score AND a.sid = b.sid;

SELECT a.sid
FROM sc a,(SELECT * FROM sc WHERE cid = 01) b,
(SELECT * FROM sc WHERE cid = 02) c
WHERE b.sid = c.sid AND b.score > c.score 
AND a.sid = b.sid AND a.sid = c.sid AND a.cid = b.cid;

–SELECT a.*
–FROM sc a
–INNER JOIN sc b ON a.cid = b.cid AND a.sid = b.sid
–WHERE (SELECT score FROM sc WHERE cid =01) > ALL(SELECT score FROM sc WHERE cid --=02)
–GROUP BY a.sid;(这里有没有好的方法实现，这个方法不对）

2、查询平均成绩大于60分的同学的学号和平均成绩；还有其他方法吗

SELECT a.sid,ROUND(AVG(a.score),2) AS `平均成绩`
FROM sc a 
GROUP BY a.sid 
HAVING `平均成绩` > 60;

3、查询所有同学的学号、姓名、选课数、总成绩；

SELECT b.sid,b.sname,COUNT(a.cid) AS `选课数`,SUM(a.score) AS `总成绩`
FROM sc a
LEFT JOIN student b ON a.sid = b.sid
GROUP BY b.sid,b.sname;


SELECT b.sid,b.sname,COUNT(a.cid) AS `选课数`,SUM(a.score) AS `总成绩`
FROM sc a, student b
WHERE a.sid = b.sid
GROUP BY b.sid,b.sname;

4、查询姓“李”的老师的个数；

SELECT COUNT(DISTINCT tid) AS `数量` FROM teacher WHERE tname LIKE '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名；

SELECT sid,sname
FROM student 
WHERE sid NOT IN 
(SELECT sid FROM sc WHERE cid IN
(SELECT cid FROM course WHERE tid IN
(SELECT tid FROM teacher WHERE tname = '叶平')));

– SELECT a.*
– FROM student a
– LEFT JOIN sc b ON a.sid = b.sid
– LEFT JOIN course c ON b.cid = c.cid
– LEFT JOIN teacher d ON c.tid = d.tid
– GROUP BY a.sid
– HAVING d.tname !=‘叶平’ AND 为什么 Unkidwn column ‘d.tname’ in ‘having clause’

SELECT a.*,d.tname
FROM student a
LEFT JOIN sc b ON a.sid = b.sid
LEFT JOIN course c ON b.cid = c.cid
LEFT JOIN teacher d ON c.tid = d.tid 
GROUP BY a.sid
HAVING a.sid NOT IN
 (SELECT sid FROM sc WHERE cid IN 
 (SELECT cid FROM course WHERE tid IN
 (SELECT tid FROM teacher WHERE tname = '叶平')));

SELECT e.*
FROM (SELECT * FROM student a
NATURAL JOIN sc b 
NATURAL JOIN course c 
NATURAL JOIN teacher d ) e 
WHERE e.tname != '叶平' AND e.sid NOT IN (SELECT f.sid 
FROM (SELECT * FROM student a
NATURAL JOIN sc b 
NATURAL JOIN course c 
NATURAL JOIN teacher d ) f WHERE f.tname = '叶平')
GROUP BY e.sid;

– SELECT e.*
– FROM (SELECT * FROM student a
– NATURAL JOIN sc b
– NATURAL JOIN course c
– NATURAL JOIN teacher d ) e
– WHERE e.tname != ‘叶平’ AND e.sid NOT IN (SELECT e.sid FROM e WHERE e.tname = ‘叶平’)
– GROUP BY e.sid;这个方法也不行

SELECT a.*
FROM student a
WHERE a.sid NOT IN 
(SELECT sid FROM sc b,course c,teacher d WHERE b.cid = c.cid
AND c.tid = d.tid AND d.tname = '叶平') ;

SELECT a.*
FROM student a
WHERE a.sid NOT IN (SELECT sid FROM sc b
LEFT JOIN course c ON b.cid = c.cid
LEFT JOIN teacher d ON c.tid = d.tid
WHERE d.tname = '叶平') ;

6、查询同时学过课程1和课程2的同学的学号、姓名；

SELECT a.sid,a.sname
FROM student a
LEFT JOIN sc b ON a.sid = b.sid 
WHERE b.cid IN (01,02)
GROUP BY a.sid,a.sname;

SELECT c.sid,c.sname
FROM student c
WHERE c.sid IN 
(SELECT a.sid FROM sc a
LEFT JOIN sc b ON a.sid = b.sid
WHERE a.cid = 01 AND b.cid = 02);