mysql数据库关联练习_MySQL 数据库SQL练习

title: MySQL 数据库SQL练习

tags: MySQL,练习

grammar_cjkRuby: true

网上很多类似的SQL运行总是出现这样的那样的问题，或者是写的异常复杂，本着研究练习的心态写下此教程。注：本教程sql全部在MySQL数据库上经过测试，如发现问题，请下方留言，转载请注明出处，谢谢！

数据库参数导入

/*

Navicat MySQL Data Transfer

Source Server : mysql

Source Server Version : 50528

Source Host : localhost:3306

Source Database : student_course_test

Target Server Type : MYSQL

Target Server Version : 50528

File Encoding : 65001

Date: 2016-06-19 16:34:13

CREATE BY MARVIS.ZHAO

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for `course`

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`C` varchar(10) NOT NULL DEFAULT '',

`Cname` varchar(10) DEFAULT NULL,

`T` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('01', '语文', '02');

INSERT INTO `course` VALUES ('02', '数学', '01');

INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------

-- Table structure for `sc`

-- ----------------------------

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (

`S` varchar(10) DEFAULT NULL,

`C` varchar(10) DEFAULT NULL,

`score` decimal(18,1) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of sc

-- ----------------------------

INSERT INTO `sc` VALUES ('01', '01', '80.0');

INSERT INTO `sc` VALUES ('01', '02', '90.0');

INSERT INTO `sc` VALUES ('01', '03', '99.0');

INSERT INTO `sc` VALUES ('02', '01', '70.0');

INSERT INTO `sc` VALUES ('02', '02', '60.0');

INSERT INTO `sc` VALUES ('02', '03', '80.0');

INSERT INTO `sc` VALUES ('03', '01', '80.0');

INSERT INTO `sc` VALUES ('03', '02', '80.0');

INSERT INTO `sc` VALUES ('03', '03', '80.0');

INSERT INTO `sc` VALUES ('04', '01', '50.0');

INSERT INTO `sc` VALUES ('04', '02', '30.0');

INSERT INTO `sc` VALUES ('04', '03', '20.0');

INSERT INTO `sc` VALUES ('05', '01', '76.0');

INSERT INTO `sc` VALUES ('05', '02', '87.0');

INSERT INTO `sc` VALUES ('06', '01', '31.0');

INSERT INTO `sc` VALUES ('06', '03', '34.0');

INSERT INTO `sc` VALUES ('07', '02', '89.0');

INSERT INTO `sc` VALUES ('07', '03', '98.0');

INSERT INTO `sc` VALUES ('09', '01', '30.0');

-- ----------------------------

-- Table structure for `student`

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`S` varchar(10) DEFAULT NULL,

`Sname` varchar(10) DEFAULT NULL,

`Sage` datetime DEFAULT NULL,

`Ssex` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('01', '赵小六', '1990-09-01 03:18:03', '男');

INSERT INTO `student` VALUES ('02', '钱钟书', '1916-12-21 00:00:00', '男');

INSERT INTO `student` VALUES ('03', '孙菲菲', '1990-05-20 00:00:00', '男');

INSERT INTO `student` VALUES ('04', '李东升', '1990-08-06 00:00:00', '男');

INSERT INTO `student` VALUES ('05', '周佛海', '1900-12-01 00:00:00', '女');

INSERT INTO `student` VALUES ('06', '吴天', '1992-03-01 00:00:00', '女');

INSERT INTO `student` VALUES ('07', '郑板桥', '1989-07-01 00:00:00', '女');

INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');

INSERT INTO `student` VALUES ('09', '张凯', '1983-08-01 20:10:21', '男');

INSERT INTO `student` VALUES ('10', '李云', '1989-05-19 11:07:30', '男');

INSERT INTO `student` VALUES ('11', '张凯', '1997-06-12 11:47:23', '男');

INSERT INTO `student` VALUES ('12', '张凯', '1997-06-16 11:47:23', '女');

INSERT INTO `student` VALUES ('13', '张海洋', '1989-05-14 11:07:30', '男');

-- ----------------------------

-- Table structure for `teacher`

-- ----------------------------

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (

`T` varchar(10) DEFAULT NULL,

`Tname` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of teacher

-- ----------------------------

INSERT INTO `teacher` VALUES ('01', '张三');

INSERT INTO `teacher` VALUES ('02', '李魁');

INSERT INTO `teacher` VALUES ('03', '王一凡');

INSERT INTO `teacher` VALUES ('04', '赵廓');

数据库练习

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT * FROM student s

WHERE EXISTS

(SELECT 1 from sc a

JOIN sc b

ON a.S = b.S

WHERE a.C = '01' AND b.C='02' AND a.score > b.score AND s.S=a.S)

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT s.*

,c.score

FROM student s

,

(SELECT a.S as s_id

,a.Score as score

from sc a

JOIN sc b

ON a.S = b.S

WHERE a.C = '01' AND b.C='02' AND a.score < b.score) c

WHERE s.S = c.s_id

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.S

,s.Sname

,c.avg

FROM student as s

JOIN

(SELECT sc.S as s_id

,AVG(score) as avg

FROM sc

GROUP BY sc.S

HAVING AVG(score) > 60) as c

ON c.s_id = s.S

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.S

,s.Sname

,c.avg

FROM student as s

JOIN

(SELECT sc.S as s_id

,AVG(score) as avg

FROM sc

GROUP BY sc.S

HAVING AVG(score) < 60) as c

ON c.s_id = s.S

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.S

,s.Sname

,c.cnt

,c.total

FROM student s

LEFT OUTER JOIN

(SELECT sc.S as s_id

,COUNT(sc.C) as cnt

,SUM(sc.score) as total

FROM sc

GROUP BY sc.S

)c

ON s.S = c.s_id

6、查询"李"姓老师的数量

SELECT COUNT(1)

FROM teacher

WHERE Tname LIKE '李%'

7、查询学过"张三"老师授课的同学的信息

SELECT *

FROM student s

WHERE EXISTS (

SELECT DISTINCT sc.S

FROM sc

WHERE EXISTS

(

SELECT c.C

FROM teacher t

JOIN course c

ON c.T = t.T

WHERE t.Tname='张三' AND sc.C = c.C)

AND s.S=sc.S

)

8、查询没学过"张三"老师授课的同学的信息

SELECT *

FROM student s

WHERE NOT EXISTS (

SELECT DISTINCT sc.S

FROM sc

WHERE EXISTS

(

SELECT c.C

FROM teacher t

JOIN course c

ON c.T = t.T

WHERE t.Tname='张三' AND sc.C = c.C)

AND s.S=sc.S

)

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT * FROM student s

WHERE EXISTS(

SELECT 1

FROM sc s1

JOIN sc s2

ON s1.S=s2.S

WHERE s1.C='01' AND s2.C='02' AND s.S=s1.S

)

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 有问题

1 查出学了01课程的学生

2 查出没有学过02课程的学生

3 组合关联一下查出1,2的综合

第一种写法

select *

from student c

join sc a

on c.S=a.S and a.C='01'

where not exists

(select 1 from sc b where a.S=b.S and b.C='02')

第二种写法

select *

from(

SELECT *

from student s

where exists(select 1 from sc sc where sc.c='01' and sc.s=s.s)

) a

inner join(

SELECT *

from student s

where not exists(select 1 from sc sc where sc.c='02' and sc.s=s.s)

)b

on a.s=b.s

11、查询没有学全所有课程的同学的信息

1 查出所有的课程数

2 按学生分组查找C个数小于上一步的个数的学生

3 匹配学生表找出学生信息

SELECT *

FROM student s

JOIN (SELECT S

FROM sc

JOIN (SELECT COUNT(1) as cnt

FROM course)c

GROUP BY S

HAVING COUNT(C) < max(c.cnt))b

ON s.S = b.S

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

1找出01学生所学的课程

2 查找学过这些课程的学生

3 排除01

SELECT *

FROM student s

JOIN sc

ON s.S=sc.S AND sc.C IN

(SELECT DISTINCT C

FROM sc

WHERE sc.S='01')

WHERE s.S!='01'

GROUP BY s.S

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

1 选出01号同学所学课程

2 选出其他同学的课程

该方法只是查出了与01学生所学课程数目完全相同的学生信心

select * from student where S in (

SELECT DISTINCT S

FROM(

select S

from sc

where C in (select C from sc where S = '01')

and S != '01' -- 这个，过滤掉自己

group by S

having count(*) = (select count(*) from sc where S = '01')

)a

);

该方法貌似有问题，是上面的改版

SELECT * FROM (

select S,

c

from sc

where C in (select C from sc where S = '01')

and S != '01'

GROUP BY s HAVING COUNT(*) = (select count(*) from sc where S = '01')

)t1

WHERE t1.c NOT IN(

SELECT distinct c FROM sc WHERE c

not IN (select distinct C from sc where S = '01')

)

该方法比较简单便于使用

SELECT *

FROM student s

JOIN

(

SELECT s1.s AS s1

,s2.s AS s2

,s1.c AS c1

,s2.c AS c2

FROM

(

SELECT s

,c

FROM sc

WHERE s='01'

) s1

JOIN sc s2

ON s1.s!=s2.s

WHERE s1.c=s2.c

GROUP BY s2.s

HAVING COUNT(*) = (select count(*) from sc where S = '01')

)b

ON s.S = b.s2

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT *

FROM student

WHERE S NOT IN (

select S

from sc

where C in (SELECT C

FROM course c

JOIN teacher t

ON c.T = t.T

WHERE t.Tname='张三')

group by S

)

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

1 选出所有不及格的记录

2 根据S group by 查找不及格的同学，及其平均分

3 根据筛选出的S查出学生信息展示

SELECT *

FROM student

WHERE S IN(

SELECT S

FROM sc

WHERE score < 60

GROUP BY S

HAVING COUNT(*)>=2

)

16、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT s.*

,sc.score

FROM student s

JOIN sc

ON sc.S = s.S

WHERE sc.score <60 AND sc.C = '01'

ORDER BY sc.score DESC

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s.Sname

,b.C

,b.score

,b.avg

FROM student s

LEFT JOIN

(

SELECT s1.S

,s1.C

,s1.score

,s2.avg

FROM sc s1

JOIN (

SELECT S

,AVG(sc.score) AS avg

FROM sc

GROUP BY S)s2

ON s1.S = s2.S

)b

ON s.S = b.S

ORDER BY b.avg DESC

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT c.C

,c.Cname

,a.top

,a.bott

,a.avg

,ROUND((a.lev1/a.cnt),2) AS good

,ROUND((a.lev2/a.cnt),2) AS great

,ROUND((a.lev3/a.cnt),2) AS excellent

,ROUND((a.lev4/a.cnt),2) AS best

FROM course c

JOIN

(SELECT C

,SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) lev1

,SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) lev2

,SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) lev3

,SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) lev4

,MAX(score) AS top

,MIN(score) AS bott

,AVG(score) AS avg

,COUNT(*) AS cnt

FROM sc

GROUP BY C

)a

ON c.C=a.C

19、按各科成绩进行排序，并显示排名 有问题

-- 第一种全排序

set @x=0;

SELECT sc.C

,sc.S

,sc.score

,@x:=ifnull(@x,0)+1 as rownum

FROM sc

ORDER BY C, sc.score DESC;

第二种按照科目成绩进行排序

set @px=0;

SELECT a.c

,a.s

,a.score

,@px:=(SELECT COUNT(*) FROM sc WHERE c=a.c AND score>a.score)+1 AS ran

FROM sc a

ORDER BY a.C,a.score DESC

20、查询学生的总成绩并进行排名

set @x=0;

SELECT st.*

,a.total

,a.rownum

FROM student st

JOIN

(SELECT s

,SUM(score) as total

,@x:=ifnull(@x,0)+1 as rownum

from sc

GROUP BY s)a

ON st.s=a.s

21、查询不同老师所教不同课程平均分从高到低显示

SELECT t.Tname

,c.Cname

,a.avg

FROM teacher t

JOIN course c

ON t.T=c.T

JOIN (

SELECT c

,AVG(score) as avg

FROM sc

GROUP BY c)a

ON a.c=c.C

ORDER BY a.avg DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

set @px=0;

SELECT s.Sname

,c.Cname

,b.score

,b.ran

FROM student s

JOIN(

SELECT a.c

,a.s

,a.score

,@px:=(SELECT COUNT(*) FROM sc WHERE c=a.c AND score>a.score)+1 AS ran

FROM sc a

-- ORDER BY a.C,a.score DESC -- 这句没啥用了

)b

ON s.s = b.s

JOIN course c

ON c.c=b.c

WHERE b.ran BETWEEN 2 AND 3

ORDER BY c.Cname

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT c.c

,c.Cname

,ROUND(lev1/cnt,2) best

,ROUND(lev2/cnt,2) better

,ROUND(lev3/cnt,2) good

,ROUND(lev4/cnt,2) not_well

FROM course c

JOIN (

SELECT c

,SUM(CASE WHEN score>=85 AND score <= 100 THEN 1 ELSE 0 END) AS lev1

,SUM(CASE WHEN score>=70 AND score < 85 THEN 1 ELSE 0 END) AS lev2

,SUM(CASE WHEN score>=60 AND score < 70 THEN 1 ELSE 0 END) AS lev3

,SUM(CASE WHEN score>=0 AND score < 60 THEN 1 ELSE 0 END) AS lev4

,COUNT(*) AS cnt

FROM sc

GROUP BY c

) a

ON a.c= c.c

24、查询学生平均成绩及其名次

SET @px=0;

SELECT a.s

,s.Sname

,a.avg

,a.row_num

FROM

(

-- 因为order by 在select之后而select 中的@px必须是在排好序之后再进行排名，

-- 因此只能多嵌套一个子查询

SELECT *

,@px:=IFNULL(@px,0)+1 AS row_num

FROM

(

SELECT s

,AVG(score) as avg

FROM sc

GROUP BY s

ORDER BY AVG(score) DESC

)b

) a

JOIN student s

ON s.s=a.s

ORDER BY a.row_num

25、查询各科成绩前三名的记录

SET @px=0;

SELECT s.Sname

,b.C

,b.S

,b.score

,b.row_num

FROM

(

SELECT a.C

,a.S

,a.score

,@px:=(SELECT COUNT(*) FROM sc WHERE a.c=c AND score > a.score) + 1 AS row_num

FROM sc a

)b

JOIN student s

ON b.S=s.S

WHERE b.row_num BETWEEN 1 AND 2

ORDER BY b.C,b.score DESC

26、查询每门课程被选修的学生数

SELECT c.C

,c.Cname

,a.cnt

FROM course c

JOIN

(

SELECT C

,COUNT(*) AS cnt

FROM sc

GROUP BY C

)a

ON a.C=c.C

27、查询出只有两门课程的全部学生的学号和姓名

SELECT s.S

,s.Sname

FROM student s

JOIN

(

SELECT S

FROM sc

GROUP BY S

HAVING COUNT(*)=2

)a

ON s.S=a.S

28、查询男生、女生人数

SELECT SUM(CASE WHEN Ssex='男' THEN 1 ELSE 0 END) boy

,SUM(CASE WHEN Ssex='女' THEN 1 ELSE 0 END) girl

,COUNT(*) total

FROM student s

29、查询名字中含有"风"字的学生信息

SELECT *

FROM student s

WHERE Sname LIKE '%风%'

30、查询同名同性学生名单，并统计同名人数

COUNT(expr) 属于聚合函数在select的时候会将所有数据合并到一起

因此必须多建立一个子查询

SET @px=0;

SELECT DISTINCT s1.S

,s1.Sname

,@px:=(SELECT COUNT(*) FROM student s3 WHERE s3.Sname=s1.Sname AND s3.S!=s1.S)+1 AS cnt

FROM student s1

JOIN student s2

ON s1.Sname=s2.Sname AND s1.S != s2.S

ORDER BY s1.S

31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT *

FROM student s

WHERE YEAR(s.Sage)='1990'

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号

SELECT b.C

,c.Cname

,b.score

,b.avg

FROM

(

SELECT C

,score

,AVG(score) avg

FROM sc

GROUP BY C

)b

JOIN course c

ON b.C = c.C

ORDER BY b.avg DESC,b.C ASC

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT s.S

,s.Sname

,b.avg

FROM student s

JOIN

(

SELECT S

,AVG(score) avg

FROM sc

GROUP BY S

HAVING AVG(score) >= 85

)b

ON s.S=b.S

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT s.Sname

,sc.score

FROM sc

JOIN student s

ON sc.S = s.S

WHERE EXISTS

(

SELECT C

FROM course c

WHERE c.Cname ='数学' AND sc.C=c.C AND sc.score < 60

)

35、查询所有学生的课程及分数情况；

SELECT s.Sname

,c.Cname

,sc.score

FROM student s

LEFT JOIN sc

ON s.S = sc.S

LEFT JOIN course c

ON sc.C=c.C

ORDER BY s.S

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT s.Sname

,c.Cname

,sc.score

FROM student s

JOIN sc

ON sc.S=s.S

JOIN course c

ON c.C=sc.C

WHERE sc.score > 70

37、查询不及格的课程

SELECT s.Sname

,c.Cname

,sc.score

FROM student s

JOIN sc

ON s.S=sc.S

JOIN course c

ON sc.C=c.C

WHERE sc.score<60

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

SELECT m.S

,m.Sname

FROM student AS m

JOIN sc

ON sc.S=m.S

WHERE sc.C='01' AND sc.score>=80

39、求每门课程的学生人数

SELECT a.C

,co.Cname

,a.cnt

FROM

(

SELECT C

,COUNT(1) AS cnt

FROM sc

GROUP BY C

) a

JOIN course co

ON a.C = co.C

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT s.*

,MAX(score) AS top

FROM sc

JOIN student s

ON s.S=sc.S

WHERE EXISTS

(

SELECT c.C

FROM teacher t

JOIN course c

ON c.T=t.T

WHERE t.Tname='张三' AND sc.C=c.C

)

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT t1.s

,t1.c

,t1.score

,t2.c

,t2.score

FROM sc t1

JOIN sc t2

ON t1.s=t2.s

where t1.c>t2.c AND t1.score=t2.score

42、查询每门功课成绩最好的前两名

SET @px=0;

SELECT s.Sname

,d.Cname

,c.row_num

FROM

(

SELECT C

,S

,@px:=(SELECT COUNT(*) FROM sc b WHERE b.C=a.C AND b.score>a.score)+1 AS row_num

FROM sc a

)c

JOIN course d

ON c.C=d.C

JOIN student s

ON s.S=c.S

WHERE c.row_num BETWEEN 1 AND 2;

ORDER BY d.C

43、统计每门课程的学生选修人数(超过5人的课程才统计)。

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT C

,COUNT(*) AS cnt

FROM sc

GROUP BY C

HAVING COUNT(*)>5

ORDER BY cnt DESC,C ASC

44、检索至少选修两门课程的学生学号

SELECT S

,COUNT(*)

FROM sc

GROUP BY S

HAVING COUNT(*) >=2

45、查询选修了全部课程的学生信息

46、查询各学生的年龄

SELECT *

,YEAR(CURDATE()) - YEAR(s.sage) AS age

FROM student s;

47、查询本周过生日的学生

SELECT *

FROM student s

WHERE YEARWEEK(date_format(s.Sage,CONCAT(YEAR(NOW()),'-%m-%d'))) = YEARWEEK(now());

48、查询下周过生日的学生

SELECT *

FROM student s

WHERE YEARWEEK(date_format(s.Sage,CONCAT(YEAR(NOW()),'-%m-%d'))) = YEARWEEK(DATE_ADD(NOW(),INTERVAL 1 WEEK));

49、查询本月过生日的学生

SELECT *

FROM student s

WHERE MONTH(s.Sage) = MONTH(now());

50、查询下月过生日的学生

SELECT *

FROM student s

WHERE MONTH(s.Sage) = MONTH(DATE_ADD(NOW(),INTERVAL 1 MONTH));

SHOW WARNINGS;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！