SQL——查询

一、简单查询
1.查询student表中的学号、姓名和年龄并为列设置别名，结果按学号升序排。
order by默认按照升序排列，如果要按照降序排列要写成order by sno desc

select sno as '学号',
       sname as '姓名',
       sbirthday as '年龄'
from student
order by sno;  

2.查询姓“王”的学生信息。
通配符’%‘和’_’

select *
from student
where sname like '王%'

3.查询成绩在80-100之间的学号、课程号及成绩，结果先按课程号升序排，课程号一样的再按成绩降序排

select score.sno, cno, degree
from student, score
where student.sno = score.sno
		AND degree between 80 and 100
order by cno, degree desc

4.查询所有缺考的学生的学号及课程号。

select sno, cno
from score
where degree is NULL

判断是不是空值要用is null 不能用 = null
5.查询 ‘3-105’课的选课人数、最高分、最低分和平均分

select COUNT(sno) as '人数', MAX(degree) as '最高分', MIN(degree) as '最低分', AVG(degree) as '平均成绩'
from score
where cno = '3-105'

聚集函数只能用在select子句或者having子句中，不能放在where里面用
6.查询每位同学的平均成绩，包括学号和平均成绩两列，结果按学号升序排。

select sno, AVG(degree)
from score
group by sno
order by sno

7.查询各班各门课的平均成绩，包括班号、课程号和平均成绩三列，结果先按班升序排，班一样的再按课程号升序排

select sclass, cno, AVG(degree)
from student, score
where student.sno = score.sno
group by sclass,cno
order by sclass, cno

8.查询score表中至少有5名学生选修的课程号及平均分。

select cno, avg(degree)
from score
group by cno
Having COUNT(sno) >= 5

9.查询其平均分高于80的学生的学号，姓名和平均分。

select score.sno, sname, AVG(degree)as '平均成绩'
from student, score
where student.sno = score.sno
group by score.sno, student.sname
having AVG(degree) >= 80

10.查询“95031”班所选课程的平均分，包括课程名和平均分。

select cname as '课程名', AVG(degree) as '平均成绩'
from student, course, score
where student.sno = score.sno and course.cno = score.cno and student.sclass = '95031'
group by score.cno,course.cname

11.查询所有教师的任课情况，包括教师姓名和课程名两列，如果某位教师没有任课则课程名列显示NULL。

select tname as '教师名', cname as '课程名'
from teacher, course
where course.tno = teacher.tno

12.对表order_detail建立查询，把“订单号”的尾部字母相同且“器件号”相同的订单合并成一张订单，新的“订单号”取原来订单号的尾部字母，器件号不变，“单价”取最低价，
“数量”取合计，查询结果先按新的“订单号”升序排，再按“器件号”升序排。

select RIGHT(order_detail.订单号,1) as 订单号,器件号,MIN(单价) as 单价,SUM(数量) as 数量
from order_detail
group by order_detail.订单号,器件号
order by 订单号,器件号

二、嵌套查询（一步不能求出来的分两步，需要用到上一步的结果，就把选择出来的结果放在子查询里面）
IN语句：
1.查询其最低分大于70，最高分小于90的学生的学号、所选课程号及其分数。

select sno, cno, degree
from score
where sno in 
    (select sno
		from score 
		where degree is not null
		group by sno
		having MAX(degree) <= 90 and MIN(degree) >= 70
	) 

2.查询成绩高于所选课平均分的学生学号、姓名、课程号和成绩
先求出来所选课的平均成绩，然后找到成绩高于该分数的同学,

select score1.sno, sname, cno, score1.degree
from student student1, score score1
where student1.sno = score1.sno and degree >(
	select  avg(degree)
	from score as score2
	where score2.cno = score1.cno  ---!!!!
)

3.查询每门课最高分的课程名、学生姓名和成绩。
算出来每一门课的最高分，然后选择成绩等于这个分数的同学

select cname, sname, degree
from course, student, score score1
where course.cno = score1.cno and student.sno = score1.sno
	 and degree = (
		select MAX(degree)
		from score score2
		where score2.cno = score1.cno
)

4.查询选修其课程的学生人数多于5人的教师姓名。

select tname
from teacher t1
where tno in(
	select tno
	from course c1
	where c1.cno in(
		select cno
		from score 
		where score.cno = c1.cno
		group by cno
		having COUNT(*) >= 5
	)
)

5.查询没有任课的教师的姓名

select tname
from teacher t1 
where tname not in(
	select distinct tname 
	from teacher t2, score, course
	where score.cno = course.cno and course.tno = t2.tno
)

NOT EXIST语句：
1.查询没选“张旭”教师课的学生成绩，并按成绩递增排列。

select degree
from score s1
where NOT EXISTS(
	select *
	from score s2, course, teacher 
	where s1.sno = s2.sno and s2.cno = course.cno 
	    	and course.tno = teacher.tno and teacher.tname = '张旭'
) 
order by degree

2.查询没有选修"6-166"课程的学生的学号和姓名。(其实这个题也可以用not in语句做）

select distinct s1.sno, sname
from student, score s1
where s1.sno = student.sno and NOT EXISTS(
	select *
	from score s2, course 
	where s1.sno = s2.sno and s2.cno = course.cno 
	    	and s2.cno = '6-166'
)