SQL语句练习【自用】

由于有一门考试是DB，这里记录一下写过的SQL语句，慢慢更新
这里也学习了其他大佬的解法！感谢！

#数据查询语句
#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数	
SELECT a.*,b.s_score as 01_score,c.s_score as 02_score
from Student a,Score b,Score c
where b.c_id='01' and c.c_id='02' and b.s_score>c.s_score and a.s_id=b.s_id and a.s_id=c.s_id;

#2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*,b.s_score as 01_score,c.s_score as 02_score
from Student a,Score b,Score c
where a.s_id=b.s_id and a.s_id=c.s_id and b.c_id='01' and c.c_id='02' and b.s_score<c.s_score;

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.s_id,a.s_name,avg(s_score)
from Student a,Score b
where a.s_id=b.s_id 
GROUP BY a.s_id
having avg(b.s_score)>=60
order by avg(b.s_score) desc

#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
SELECT a.s_id,a.s_name,avg(b.s_score) 
from Student a,Score b
where a.s_id=b.s_id
group by a.s_id
HAVING avg(b.s_score)<60 or avg(b.s_score) is null

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id),sum(b.s_score)
from Student a,Score b
where a.s_id=b.s_id
group by a.s_id

#6、查询"李"姓老师的数量 
select count(*) 李老师
from Teacher
where t_name like '李%'

#7、查询学过"张三"老师授课的同学的信息 
select a.*
from Student a,Score b,course c,teacher d
where t_name='张三' and d.t_id=c.t_id and c.c_id=b.c_id and a.s_id=b.s_id;


# 8、查询没学过"张三"老师授课的同学的信息 
select a.*
from student a
where a.s_id not in(
	select a.s_id
	from Student a,Score b,course c,teacher d
	where t_name='张三' and d.t_id=c.t_id and c.c_id=b.c_id and a.s_id=b.s_id
)

#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT a.*
from student a,score b,score c
where a.s_id=b.s_id and a.s_id=c.s_id and b.c_id='01' and c.c_id='02'

#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.*
from student a
where a.s_id in (
	SELECT s_id
	from score
	where c_id='01'
) and a.s_id not in(
	SELECT s_id
	from score
	where c_id='02'
)

#11、查询没有学全所有课程的同学的信息  we can use 'in' this key word
SELECT student.*
from student
where s_id not in(
	SELECT s_id
	from score a
	group by s_id
	HAVING count(*) = (SELECT count(DISTINCT c_id) from course)
)
 
 
#11.5、查询选修了全部课程的同学信息
SELECT student.*
 from student
 where not exists(
		SELECT *
		from course
		where not exists(
				SELECT *
				from score
				where score.c_id=course.c_id and score.s_id=student.s_id
		)
)

#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 ⭐
select student.*
from student
where s_id in(
		SELECT DISTINCT score.s_id from score where score.c_id in (SELECT score.c_id from score where s_id='01')
)

#13、查询与学号为"01"的同学所学相同的同学的信息
SELECT *
from student
where s_id in (select s_id from score group by s_id HAVING count(s_id) = (
		SELECT count(c_id) from score where s_id='01'
	)
	#选修的课程数量是相同的
)and s_id not in(
	SELECT s_id from score where c_id in(
		SELECT DISTINCT c_id from score
		where c_id not in(
			SELECT c_id from score where s_id='01'
		)
	)
	group by s_id
	#没选修的课程数量及课程也是相同的
)
and s_id not in('01');#排除自己

#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
#逐层寻找，首先看学生id，然后找课程id，再找教师id，再找张三老师 
select a.s_name
from student a
where a.s_id not in(
		SELECT s_id
		from score where c_id=
		(
				SELECT c_id from course where t_id=(
						SELECT t_id from teacher where t_name='张三'
				)
		)
)

# ❗ 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
select a.s_id,a.s_name,avg(b.s_score)
from student a,score b
where a.s_id=b.s_id
group by a.s_id
HAVING count(b.s_score)>=2 

SELECT a.s_id,a.s_name,round(avg(b.s_score))
from student a
left join score b on a.s_id=b.s_id
where a.s_id in(
	SELECT s_id from score where s_score<60 GROUP BY s_id HAVING count(*)>=2
)
GROUP BY a.s_id,a.s_name

#16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.s_name,b.s_score
from student a,score b
where b.s_id=a.s_id and b.s_score<60 and b.c_id='01'
order by s_score desc

#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,(SELECT s_score from score where s_id=a.s_id and c_id='01') as 语文,
							(SELECT s_score from score where s_id=a.s_id and c_id='02') as 数学,
							(SELECT s_score from score where s_id=a.s_id and c_id='03') as 英语,
							avg(s_score) as 平均分
from score a 
group by a.s_id
order by 平均分 desc

#数据控制语句
#在学生表中增加一列学生爱好
alter table student add hobby varchar(20)#注意加数据类型

#在课程表中插入物理这门课程nihao
insert into course values('04','物理','04')
insert into course values('05','化学','04')

#更新教师表中“老6”的名字为“刘六”
update teacher set t_name='刘六' where t_name='老六'

#删除课程表中化学那一栏的信息
delete from course where c_name='化学'

#创建学生课程视图
create view stu_course_info
as
SELECT a.s_id,a.s_name,c.c_name,b.s_score
from student a,score b,course c
where a.s_id=b.s_id and b.c_id=c.c_id

#删除视图	
drop view stu_course_info