mysql查询练习题

程序员文章站 2024-03-05 18:18:07

...

原地址：http://www.cnblogs.com/wupeiqi/articles/5729934.html

mysql查询练习题
我没有做完，只做了一部分，附上我的数据库

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=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '三年二班');
INSERT INTO `class` VALUES ('2', '一年三班');
INSERT INTO `class` VALUES ('3', '三年一班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(16) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '生物', '1');
INSERT INTO `course` VALUES ('2', '体育', '1');
INSERT INTO `course` VALUES ('3', '物理', '2');
INSERT INTO `course` VALUES ('4', '日语', '3');
INSERT INTO `course` VALUES ('5', '心理', '2');

-- ----------------------------
-- 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,
  `corse_id` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '60');
INSERT INTO `score` VALUES ('2', '1', '2', '59');
INSERT INTO `score` VALUES ('3', '2', '2', '80');
INSERT INTO `score` VALUES ('4', '2', '1', '65');
INSERT INTO `score` VALUES ('5', '1', '3', '34');
INSERT INTO `score` VALUES ('6', '2', '3', '88');
INSERT INTO `score` VALUES ('7', '3', '1', '78');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) NOT NULL,
  `gender` varchar(8) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '钢蛋', '女', '1');
INSERT INTO `student` VALUES ('2', '铁锤', '女', '1');
INSERT INTO `student` VALUES ('3', '山炮', '南', '2');

-- ----------------------------
-- 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=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '波多');
INSERT INTO `teacher` VALUES ('2', '苍空');
INSERT INTO `teacher` VALUES ('3', '饭岛');

题目

二、操作表

1、自行创建测试数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

3、查询平均成绩大于60分的同学的学号和平均成绩； 

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

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

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

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

10、查询有课程成绩小于60分的同学的学号、姓名；

11、查询没有学全所有课的同学的学号、姓名；

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

15、删除学习“叶平”老师课的SC表记录；

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 

17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

20、课程平均分从高到低显示（现实任课老师）；

21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 

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

23、查询出只选修了一门课程的全部学生的学号和姓名；

24、查询男生、女生的人数；

25、查询姓“张”的学生名单；

26、查询同名同姓学生名单，并统计同名人数；

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

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

29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

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

31、求选了课程的学生人数

32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

33、查询各个课程及相应的选修人数；

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

35、查询每门课程成绩最好的前两名；

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

37、查询全部学生都选修的课程的课程号和课程名；

38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；

39、查询两门以上不及格课程的同学的学号及其平均成绩；

40、检索“004”课程分数小于60，按分数降序排列的同学学号；

41、删除“002”同学的“001”课程的成绩；

附上我自己做的部分

查询“生物”课程比“物理”课程成绩高的所有学生的学号
SELECT
c.sid from 
(SELECT a.sid,b.corse_id,b.number 
FROM student a
LEFT JOIN  score  b
on a.sid=b.student_id )c
 join 
(SELECT a.sid,b.corse_id,b.number 
FROM student a
LEFT JOIN  score  b
on a.sid=b.student_id )d
ON c.sid=d.sid
where c.corse_id= (SELECT cid FROM course where cname='生物') and d.corse_id =(SELECT cid FROM course where cname='体育') and c.number>d.number

查询平均成绩大于60分的同学的学号和平均成绩；


SELECT a.sid,AVG(b.number)
FROM student a
LEFT JOIN  score  b
on a.sid=b.student_id
GROUP BY a.sid
HAVING AVG(b.number)>60


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

SELECT b.sid 学号,b.sname 姓名,COUNT(1) 选课数,SUM(number) 总成绩
FROM  score a
LEFT JOIN  student  b
on b.sid=a.student_id
group BY b.sid

查询姓“李”的老师的个数；
select count(1) from teacher where tname like '波%'

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

查出他教授的课程ID
SELECT cid FROM course b
where b.teacher_id = (SELECT tid from teacher where tname='波多') 

只要学生里面有此课程ID，输出来
  
SELECT DISTINCT student_id
from score  a 
 where a.corse_id = any(SELECT cid FROM course b
where b.teacher_id = (SELECT tid from teacher where tname='波多') ) 

去掉这些学生
select sid,sname
FROM student
where student.sid not in(  
SELECT DISTINCT student_id
from score  a 
 where a.corse_id = any(SELECT cid FROM course b
where b.teacher_id = (SELECT tid from teacher where tname='波多') ) 
)

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

 查出学过001的，查出学过002的
select * from score a WHERE a.corse_id=1
select * from score a WHERE a.corse_id=2
求交集
SELECT b.student_id
FROM
(select student_id from score a WHERE a.corse_id=1)b
inner JOIN
(select student_id from score a WHERE a.corse_id=2)c
on b.student_id=c.student_id

查询学过“叶平”老师所教的所有课的同学的学号、姓名；
找出全部符合的
select cid 
from course a
LEFT JOIN teacher b
ON a.teacher_id=b.tid
where b.tname='波多'
 
右外连接，以老师教授过得课程，出考试成绩，按照学生分组，统计个数，
select student_id,corse_id 
from score c
LEFT JOIN (select cid 
from course a
right JOIN teacher b
ON a.teacher_id=b.tid
where b.tname='波多') d
on c.corse_id=d.cid

select student_id,corse_id 
from score c
LEFT JOIN (select cid 
from course a
right JOIN teacher b
ON a.teacher_id=b.tid
where b.tname='波多') d
on c.corse_id=d.cid

SELECT student_id from  (select student_id,corse_id 
from score c
LEFT JOIN (select cid 
from course a
right JOIN teacher b
ON a.teacher_id=b.tid
where b.tname='波多') d
on c.corse_id=d.cid) a
GROUP BY student_id
HAVING count(student_id)>(select COUNT(1) 
from course a
LEFT JOIN teacher b
ON a.teacher_id=b.tid
where b.tname='波多'
 )

SELECT cc.student_id,dd.sname
from (SELECT student_id from  (select student_id,corse_id 
from score c
LEFT JOIN (select cid 
from course a
right JOIN teacher b
ON a.teacher_id=b.tid
where b.tname='波多') d
on c.corse_id=d.cid) a
GROUP BY student_id
HAVING count(student_id)>(select COUNT(1) 
from course a
LEFT JOIN teacher b
ON a.teacher_id=b.tid
where b.tname='波多'
 )) cc
LEFT  JOIN
student  dd
on dd.sid=cc.student_id

查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT A.student_id
FROM (SELECT   a.*,b.sname  FROM score a LEFT JOIN student b ON b.sid=a.student_id) A
JOIN (SELECT   a.*,b.sname  FROM score a LEFT JOIN student b ON b.sid=a.student_id) B
ON A.student_id=B.student_id
where  A.corse_id=002 and B.corse_id=001 and A.number<B.number

查询有课程成绩小于60分的同学的学号、姓名；
 
select DISTINCT b.sid,b.sname from score a
LEFT JOIN student b
on a.student_id=b.sid
where number<60

查询没有学全所有课的同学的学号、姓名
总共课程数量
SELECT COUNT(1) from course 
课程ID
SELECT course.cid from course 

SELECT * 
from score a
RIGHT JOIN (SELECT course.cid from course ) b
on a.corse_id=b.cid

找出学号
SELECT c.student_id
from (SELECT * 
from score a
LEFT JOIN (SELECT course.cid from course ) b
on a.corse_id=b.cid) c
GROUP BY c.student_id
HAVING COUNT(c.student_id)<(SELECT COUNT(1) from course )

得到全部信息
SELECT aa.sid,aa.sname
FROM student  aa
RIGHT JOIN (
SELECT c.student_id
from (SELECT * 
from score a
LEFT JOIN (SELECT course.cid from course ) b
on a.corse_id=b.cid) c
GROUP BY c.student_id
HAVING COUNT(c.student_id)<(SELECT COUNT(1) from course )) bb
on aa.sid=bb.student_id

查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

select corse_id from score where student_id=1

SELECT bb.sid,bb.sname
from (
select DISTINCT student_id from score where corse_id in (select corse_id from score where student_id=1) and student_id!=1
) aa
LEFT JOIN student bb
on aa.student_id=bb.sid


查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；--类似于某老师教过所有课的同学

上一篇： ASP.NET对路径"xxxxx"的访问被拒绝的解决方法小结

下一篇： Python seaborn画热力图(heatmap)删除相关性高的的特征

mysql查询练习题

新手练习题目和答案，当然答案不是唯一的

适合Java新手入门的编码练习题，附有答案

mysql查询练习题

MySQL练习题

详解JDBC对Mysql utf8mb4字符集的处理

java实现变更文件查询的方法

PHP中SQL查询语句的id=%d解释(推荐)

mybatis collection 多条件查询结合mysql行列转换

Mybatis多表关联查询的实现(DEMO)

mybatis-plus使用注解多条件查询，整合bootstrap-table

mysql查询练习题

新手练习题目和答案，当然答案不是唯一的

适合Java新手入门的编码练习题，附有答案

mysql查询练习题

MySQL练习题

详解JDBC对Mysql utf8mb4字符集的处理

java实现变更文件查询的方法

PHP中SQL查询语句的id=%d解释(推荐)

mybatis collection 多条件查询 结合mysql行列转换

Mybatis多表关联查询的实现(DEMO)

mybatis-plus使用注解多条件查询，整合bootstrap-table

mybatis collection 多条件查询结合mysql行列转换