Mysql数据库经典50问解答

最新推荐文章于 2023-02-07 09:59:49 发布

DeepGym

最新推荐文章于 2023-02-07 09:59:49 发布

阅读量457

点赞数

分类专栏： JAVA 文章标签：考研 KMP 数据库

本文链接：https://blog.csdn.net/qq_37377691/article/details/117428533

版权

JAVA 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

# 创建SQL_QUESTION数据库
CREATE DATABASE IF NOT EXISTS SQL_QUESTION DEFAULT CHARACTER SET utf8 COLLATE
USE SQL_QUESTION; # 使用SQL_QUESTION数据库
# 建表-学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
# 建表-课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
# 建表-教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
# 建表-成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
# 插入数据-插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
# 插入数据-教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
# 插入数据-成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩.

select s.*,e.s_score from student s inner join score e on e.s_id = s.s_id inner join
 (select * from score where c_id='02') k on k.s_id = s.s_id
where e.s_score > k.s_score and e.s_id = s.s_id and e.c_id ='01';

2. 查询在SC表存在成绩的学生信息.

select * from score k left join student s on s.s_id = k.s_id;

3. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null).

select s.s_id,s.s_name,count(*) from student s inner join score k on k.s_id = s.s_id group by k.s_id;

4. 查有成绩的学生信息.

select * from score k left join student s on s.s_id = k.s_id;

5. 查询「李」姓老师的数量.

select count(*) from teacher where t_name like '李_';

6. 查询学过「张三」老师授课的同学的信息.

select s.* from student s,teacher t,score k,course c where  k.c_id = c.c_id and c.t_id = t.t_id
and k.s_id = s.s_id and t.t_name = '张三';

7. 查询没有学全所有课程的同学的信息.

select * from student s inner join score k on s.s_id = k.s_id group by k.s_id
having count(*) < (select count(*) from course c);

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

select s.*  from student s inner join score k on k.s_id=s.s_id where k.c_id in (select k.c_id from score k where
k.s_id='01');

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

-- 这是一种正向查询的思想，先与01号学生选修的课程编号进行内连接，
-- 然后再根据01号学生选修课程的数量做最后的筛选。
select *
from student s
       inner join (select s.s_id, k.c_id
                   from score k
                          inner join student s on s.s_id = k.s_id
                          inner join (select k.c_id from score k where k.s_id = '01') x on x.c_id = k.c_id
                   group by s.s_id
                   having count(*) = (select count(*) from score k where k.s_id = '01' group by s.s_id)) x
                  on x.s_id = s.s_id
where s.s_id <> 01;

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

SELECT s.s_name FROM student s 
WHERE s.s_id NOT IN 
(SELECT DISTINCT s.s_id FROM student s,course c,score r,teacher t 
WHERE s.s_id = r.s_id AND r.c_id = c.c_id AND c.t_id =  t.t_id
AND t.t_name = "张三" )

11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩.

SELECT  s.s_id,s.s_name,AVG(r.s_score)  
FROM student s,score r 
WHERE s.s_id = r.s_id 
GROUP BY s.s_id 
HAVING COUNT(IF(r.s_score < 60,TRUE,NULL)) >=2

12. 检索"01"课程分数小于60,按分数降序排列的学生信息.

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

14. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率（及格为>=60,中等为:70-80, 优良为:80-90,优秀为:>=90.

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

16. 按各科成绩进行排序,并显示排名,Score重复时保留名次空缺.

17. 按各科成绩进行排序,并显示排名,Score重复时合并名次.

18. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺.

19. 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺.

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

21. 查询各科成绩前三名的记录.

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

23. 查询出只选修两门课程的学生学号和姓名.