sql练习

最新推荐文章于 2023-02-07 18:06:52 发布

Frank-Han

最新推荐文章于 2023-02-07 18:06:52 发布

阅读量1.3k

点赞数 5

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_42737442/article/details/83514888

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、创建数据

1、分析表关系

在这里插入图片描述

2、创建表

1.创建年级表

create table class_grade(
gid int not null auto_increment primary key,
gname char(20) not null 
)engine=INNODB DEFAULT CHARSET=utf8;

2.创建老师表

create table teacher(
tid int not null auto_increment primary key,
tname char(20) not null 
)engine=INNODB DEFAULT CHARSET=utf8;

3.创建班级表

create TABLE class(
cid int not null auto_increment primary key,
caption char(20) not null ,
grade_id int not null,
CONSTRAINT cls_cg foreign key (grade_id) references class_grade(gid)
)engine=INNODB DEFAULT charset=utf8;

4.创建学生表

create TABLE student(
sid int not null auto_increment primary key,
sname char(20) not null,
gender enum('男','女') not NULL,
class_id int NOT NULL,
constraint ci_cls foreign key (class_id) references class(cid)

)ENGINE=INNODB DEFAULT charset=utf8;

5.创建课程表

CREATE TABLE course(
cid int not null auto_increment PRIMARY KEY,
cname char(20) NOT NULL,
teacher_id INT NOT NULL,
CONSTRAINT ti_th FOREIGN KEY (teacher_id) REFERENCES teacher(tid)
)ENGINE=INNODB DEFAULT charset=utf8;

6.创建班级任职表

CREATE TABLE teacher2cls(
tcid int not NULL auto_increment PRIMARY KEY,
tid int not NULL,
cid int NOT NULL,
CONSTRAINT tid_th foreign KEY (tid) REFERENCES teacher(tid),
CONSTRAINT t2c_cls FOREIGN KEY (cid) REFERENCES class(cid)
)ENGINE=INNODB DEFAULT charset=utf8;

7.创建成绩表

CREATE TABLE score(
sid int not NULL auto_increment PRIMARY KEY,
student_id int not NULL,
course_id int NOT NULL,
score DECIMAL(6,2) not NULL,
CONSTRAINT sr_stu foreign KEY (student_id) REFERENCES student(sid),
CONSTRAINT sr_ci FOREIGN KEY (course_id) REFERENCES course(cid)
)ENGINE=INNODB DEFAULT charset=utf8;

3、写入数据

INSERT INTO class_grade
 (gname) 
VALUES 
('一年级'),
('二年级'),
('三年级');

INSERT INTO class
 (caption,
 grade_id) 
VALUES 
('一年一班',1),
('二年一班',2),
('三年二班',3);

INSERT INTO teacher
 (tname) 
VALUES 
('张三'),
('李四'),
('王五');

INSERT INTO course
 (
cname,
teacher_id) 
VALUES 
('生物',1),
('体育',1),
('物理',2);

INSERT INTO student
 (
sname,
gender,
class_id
) 
VALUES 
('乔丹','女',1),
('艾弗森','女',1),
('科比','男',2);

INSERT INTO score
 (
student_id,
course_id,
score
) 
VALUES 
(1,1,60),
(1,2,59),
(2,2,99);

INSERT INTO teacher2cls
 (
tid,
cid,
) 
VALUES 
(1,1),
(1,2),
(2,1),
(3,2);

二、数据库操作

自行创建测试数据
查询学生总人数

SELECT COUNT(*) from student;

在这里插入图片描述

查询“生物”课程和“物理”课程成绩都及格的学生id和姓名

SELECT sid,sname FROM student WHERE sid IN
(
SELECT student_id FROM 
(
SELECT * FROM score WHERE
course_id in 
(SELECT cid FROM course WHERE cname in ('生物','物理')
)
AND
score >= 60
) as f 
GROUP BY student_id HAVING count(course_id)=2)

在这里插入图片描述

查询每个年级的班级数，取出班级数最多的前三个年级

SELECT gid,gname FROM class_grade 
WHERE gid in (
SELECT grade_id FROM class 
GROUP BY grade_id 
ORDER BY COUNT(cid) 
DESC 
) limit  3

在这里插入图片描述

查询平均成绩最高和最低的学生的id和姓名以及平均成绩

(SELECT student_id,
(SELECT sname FROM student WHERE sid = student_id),avg(score) 
FROM score GROUP BY student_id ORDER BY AVG(score) DESC LIMIT 1)
UNION
(SELECT student_id,
(SELECT sname FROM student WHERE sid = student_id),AVG(score) 
FROM score GROUP BY student_id ORDER BY AVG(score) ASC LIMIT 1)

在这里插入图片描述

查询每个年级的学生人数

SELECT 
(SELECT gname FROM class_grade WHERE gid = f.grade_id),count(sid) 
FROM
(
SELECT student.sid,class.grade_id
FROM student INNER JOIN  class
 ON student.class_id = class.cid
) as f
GROUP BY grade_id

在这里插入图片描述

查询每位学生的学号，姓名，选课数，平均成绩

SELECT 
(SELECT sid FROM student WHERE student.sid=score.student_id),
(SELECT sname FROM student WHERE student.sid=score.student_id),
count(course_id),
avg(score)
FROM
score GROUP BY student_id

在这里插入图片描述

查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数

SELECT sname,
(SELECT cname FROM course WHERE cid=
(SELECT course_id FROM score WHERE student_id=2 ORDER BY score DESC LIMIT 1)),
(SELECT score FROM score WHERE student_id=2 ORDER BY score DESC LIMIT 1),
(SELECT cname FROM course WHERE cid=
(SELECT course_id FROM score WHERE student_id=2 ORDER BY score ASC LIMIT 1)),
(SELECT score FROM score WHERE student_id=2 ORDER BY score ASC LIMIT 1)
FROM student WHERE sid =2

在这里插入图片描述

查询姓“李”的老师的个数和所带班级数

SELECT f.tid,count(f.tid),count(f.cid),GROUP_CONCAT(f.cid)
FROM
(SELECT teacher.tname,teacher.tid,teacher2cls.cid FROM teacher left JOIN teacher2cls ON teacher.tid = teacher2cls.tid ) as f
WHERE f.tname LIKE '李%'
GROUP BY f.tid

在这里插入图片描述

查询班级数小于5的年级id和年级名


SELECT class.grade_id ,(SELECT gname FROM class_grade WHERE gid = grade_id)  FROM 
class 
GROUP BY grade_id
HAVING count(cid) < 5

在这里插入图片描述

查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下

班级id	班级名称	年级	年级级别
1	一年一班	一年级	低

SELECT class.cid,class.caption,'低年级' as lev FROM class WHERE class.grade_id <=2 
UNION
SELECT class.cid,class.caption,'中年级' as lev FROM class WHERE class.grade_id > 2 AND class.grade_id <=4
UNION
SELECT class.cid,class.caption,'高年级' as lev FROM class WHERE class.grade_id > 4 AND class.grade_id <=6

在这里插入图片描述

查询学过“张三”老师2门课以上的同学的学号、姓名

SELECT student_id,(SELECT student.sname FROM student WHERE student.sid=student_id) FROM
(
SELECT * FROM teacher RIGHT JOIN course ON teacher.tid=course.teacher_id 
RIGHT JOIN
score ON cid = score.course_id
) as f WHERE f.tname = '张三'
GROUP BY student_id
HAVING count(course_id) >=2

在这里插入图片描述

查询教授课程超过2门的老师的id和姓名

SELECT teacher.tid,teacher.tname 
FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid 
GROUP BY teacher.tid
HAVING count(course.cid) >=2

在这里插入图片描述

查询学过编号“1”课程和编号“2”课程的同学的学号、姓名

SELECT score.student_id,(SELECT student.sname FROM student WHERE sid=score.student_id)
FROM score
WHERE score.course_id =1 OR score.course_id =2

在这里插入图片描述

查询没有带过高年级的老师id和姓名

SELECT teacher2cls.tid,(SELECT teacher.tname FROM teacher WHERE teacher.tid = teacher2cls.tid) 
FROM teacher2cls LEFT JOIN class ON class.cid = teacher2cls.cid
WHERE 
class.grade_id<5
GROUP BY
teacher2cls.tid

在这里插入图片描述
16. 查询学过“张三”老师所教的所有课的同学的学号、姓名

SELECT student_id,(SELECT student.sname FROM student WHERE student.sid=student_id) FROM
(
SELECT * FROM teacher RIGHT JOIN course ON teacher.tid=course.teacher_id 
RIGHT JOIN
score ON cid = score.course_id
) as f WHERE f.tname = '张三'
GROUP BY f.student_id
HAVING COUNT(f.cid) =
(SELECT count(course.cid) FROM course WHERE course.teacher_id = 
(SELECT teacher.tid FROM teacher WHERE teacher.tname ='张三')
)

在这里插入图片描述
17. 查询带过超过2个班级的老师的id和姓名

SELECT tid,(SELECT teacher.tname FROM teacher WHERE teacher.tid=teacher2cls.tid) 
FROM
teacher2cls
GROUP BY teacher2cls.tid
HAVING COUNT(teacher2cls.cid)>=2

在这里插入图片描述

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

select student.sid,student.sname from student  where student.sid in 
(select t1.student_id from 
(select student_id,score from score  where course_id = 1 )as t1,
(select student_id,score from score  where course_id = 2  ) as t2
where t1.student_id = t2.student_id and t2.score<t1.score);

在这里插入图片描述

查询所带班级数最多的老师id和姓名

SELECT teacher2cls.tid,(SELECT teacher.tname FROM teacher WHERE teacher.tid = teacher2cls.tid)
FROM teacher2cls
GROUP BY teacher2cls.tid
ORDER BY count(cid) DESC
LIMIT 1

在这里插入图片描述
20. 查询有课程成绩小于60分的同学的学号、姓名

SELECT score.student_id,(SELECT student.sname FROM student WHERE score.student_id= student.sid)
FROM score
WHERE score.score<60

在这里插入图片描述

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

SELECT score.student_id,(SELECT student.sname FROM student WHERE student.sid=score.student_id)
FROM score 
GROUP BY score.student_id
HAVING COUNT(score.course_id) >=
(SELECT count(course.cid) FROM course)

在这里插入图片描述
22. 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

SELECT score.student_id,(SELECT student.sname FROM student WHERE student.sid=score.student_id)
FROM score WHERE score.course_id IN
(
SELECT score.course_id FROM score WHERE score.student_id=1)

在这里插入图片描述

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

SELECT score.student_id,(SELECT student.sname FROM student WHERE student.sid=score.student_id)
FROM score WHERE score.course_id IN
(
SELECT score.course_id FROM score WHERE score.student_id=1) 
AND score.student_id != 1

在这里插入图片描述

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

SELECT score.student_id,(SELECT student.sname FROM student WHERE student.sid=score.student_id)
FROM score WHERE 
score.course_id IN
(SELECT score.course_id FROM score WHERE score.student_id=2) 
GROUP BY score.student_id
HAVING count(course_id)=
(SELECT count(course_id) FROM score WHERE score.student_id = 2)
AND score.student_id != 2

在这里插入图片描述
25. 删除学习“张三”老师课的score表记录；

DELETE FROM score WHERE score.course_id IN
(SELECT course.cid FROM course WHERE course.teacher_id in
(SELECT teacher.tid FROM teacher WHERE teacher.tname='张三')
)

在这里插入图片描述
26. 向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课
程的平均成绩；

INSERT INTO course
(cname,teacher_id)
VALUES
('语文',2),
('数学',3),
('英语',3)

INSERT INTO score
(student_id,course_id,score)
VALUES
(1,3,80),
(2,4,45),
(3,5,65),
(2,4,90),
(3,1,70)

INSERT INTO score
(avg_socre)
SELECT avg(score.score)
FROM score
GROUP BY score.course_id

在这里插入图片描述

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

select *,
(case when 数学 is null then 0 else 1 end)+ (case when 英语 is null then 0 else 1 end)+ (case when 语文 is null then 0 else 1 end)  as 有效成绩数, 
(语文+数学+英语)/((case when 数学 is null then 0 else 1 end)+  (case when 英语 is null then 0 else 1 end)+  (case when 语文 is null then 0 else 1 end))  as 有效平均分  
from  
(

SELECT student_id AS 学生ID,
(SELECT score FROM score LEFT JOIN course on sore.course_id=course.cid  WHERE course.cname="语文" AND student_id=s1.student_id) AS 语文,
(SELECT score FROM score LEFT JOIN course on score.course_id=course.cid  WHERE course.cname="数学" AND student_id=s1.student_id) AS 数学,
(SELECT score FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="英语" AND student_id=s1.student_id) AS 英语 

FROM score as s1 GROUP BY student_id) as t2

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

select course_id,
max(score) as "最高分",
min(score) as "最低分" 
from score 
group by course_id;

在这里插入图片描述

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

SELECT course_id,
avg(score) as "平均分",
sum(score>60)/count(score.student_id)*100 as "及格率" 
from score 
group by  course_id 
order by avg(score) asc,"及格率" desc;

在这里插入图片描述

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

SELECT score.course_id,avg(score.score),(SELECT teacher.tname FROM teacher WHERE teacher.tid=(SELECT course.teacher_id FROM course WHERE course.cid=score.course_id))
FROM score 
GROUP BY score.course_id
ORDER BY avg(score.score) DESC

在这里插入图片描述

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

select  
t1.student_id,
t1.course_id,
t1.score  
from score as t1 
where 
	(select count(course_id) 
	from score  
	where 
	t1.course_id= course_id and t1.score<score)<3 
order by t1.course_id,score desc;

在这里插入图片描述

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

SELECT score.course_id,
(SELECT course.cname FROM course WHERE course.cid=score.course_id),
COUNT(score.student_id)
FROM score
GROUP BY course_id

在这里插入图片描述

查询选修了2门以上课程的全部学生的学号和姓名；

SELECT score.student_id,(SELECT student.sname FROM student WHERE student.sid=score.student_id)
FROM score
GROUP BY score.student_id
HAVING COUNT(score.course_id)>=2

在这里插入图片描述

查询男生、女生的人数，按倒序排列；

SELECT student.gender, COUNT(sid)
FROM student
GROUP BY student.gender
ORDER BY COUNT(sid) deSC

在这里插入图片描述

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

SELECT student.sid,student.sname
FROM student
WHERE
student.sname LIKE '张%'

在这里插入图片描述

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

SELECT student.sname,COUNT(student.sname)
FROM student
GROUP BY student.sname
HAVING COUNT(student.sname) >1

在这里插入图片描述

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

SELECT score.course_id,avg(score.score)
FROM score
GROUP BY score.course_id
ORDER BY avg(score.score) DESC,score.course_id ASC

在这里插入图片描述

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

SELECT score.student_id,
(SELECT student.sname FROM student WHERE student.sid = score.student_id),
score.score
FROM score
WHERE score.course_id=(SELECT course.cid FROM course WHERE course.cname='数学')
AND score.score < 60

在这里插入图片描述

查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；

SELECT score.student_id,
(SELECT student.sname FROM student WHERE student.sid = score.student_id),
score.score
FROM score
WHERE score.course_id=3
AND score.score > 80

在这里插入图片描述

求选修了课程的学生人数

SELECT COUNT(sid) FROM student WHERE sid IN
(
SELECT score.student_id
FROM score
GROUP BY score.student_id)

在这里插入图片描述

查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

(SELECT student.sname,score.score FROM score LEFT JOIN student ON student.sid= score.student_id WHERE score.course_id IN(
SELECT course.cid FROM course WHERE course.cname='王五') ORDER BY score.score DESC LIMIT 1)
UNION
(SELECT student.sname,score.score FROM score LEFT JOIN student ON student.sid= score.student_id WHERE score.course_id IN(
SELECT course.cid FROM course WHERE course.cname='王五') ORDER BY score.score ASC LIMIT 1)

在这里插入图片描述

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

SELECT score.course_id,
(SELECT course.cname FROM course WHERE course.cid=score.course_id),
COUNT(score.student_id)
FROM score
GROUP BY score.course_id

在这里插入图片描述

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

select  
score.student_id,
score.course_id,
score.score 
from 
score,
	(select score,student_id 
	from score  
	group by score,student_id  
	having count(course_id)>1)as t1  
where 
score.score =t1.score 
and score.student_id =t1.student_id;

在这里插入图片描述

查询每门课程成绩最好的前两名学生id和姓名；

select  student.sid,student.sname, t2.course_id, t2.score, t2.first_score, t2.second_score  
from  student  
inner join 
( select  score.student_id, score.course_id, score.score, t1.first_score, t1.second_score  
	from  score  
		inner join 
			(select  s1.sid, 
				(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score, 
				(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score  
			from  score as s1) as t1 on score.sid = t1.sid  
		where  score.score in (t1.first_score, t1.second_score )) as t2 on student.sid = t2.student_id;

在这里插入图片描述

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

SELECT score.student_id FROM score
GROUP BY score.student_id
HAVING COUNT(score.course_id)>=2

在这里插入图片描述

查询没有学生选修的课程的课程号和课程名；

SELECT course.cid,course.cname FROM course
WHERE course.cid NOT IN
(SELECT score.course_id FROM score)

在这里插入图片描述

查询没带过任何班级的老师id和姓名；

SELECT teacher.tid,teacher.tname FROM teacher
WHERE teacher.tid NOT IN
(SELECT teacher2cls.tid FROM teacher2cls)

在这里插入图片描述

查询有两门以上课程超过80分的学生id及其平均成绩；

SELECT score.student_id,avg(score.score)
FROM score
WHERE score.score >80
GROUP BY score.student_id
HAVING COUNT(score.course_id)>=2

在这里插入图片描述

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

SELECT score.student_id
FROM score
WHERE score.score < 60 AND score.course_id=3
ORDER BY score.score DESC

在这里插入图片描述

删除编号为“2”的同学的“1”课程的成绩；

DELETE FROM score 
WHERE score.student_id=2 
AND score.course_id=1

在这里插入图片描述

查询同时选修了物理课和生物课的学生id和姓名；

(select t1.student_id,(SELECT student.sname FROM student WHERE student.sid=t1.student_id) from 
(select student_id,score from score  where score.course_id =(SELECT course.cid FROM course WHERE course.cname='生物') )as t1,
(select student_id,score from score  where score.course_id =(SELECT course.cid FROM course WHERE course.cname='物理')  ) as t2
where t1.student_id = t2.student_id )

在这里插入图片描述

Frank-Han

关注

5
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
sql练习

一、创建数据1、分析表关系2、创建表1.创建年级表create table class_grade(gid int not null auto_increment primary key,gname char(20) not null )engine=INNODB DEFAULT CHARSET=utf8;2.创建老师表create table teacher(tid in...
复制链接

扫一扫