前言
近来感觉SQL语句有些生疏，于是，便有了这次的回忆
采用的是SQLServer 2008
最常见的三张表
Student(sno, name, sex, age, dept )
Course(cno, name, pno, teacher, credit)
SC(sno, cno, grade)
常见SQL语句
1.插入一名学生的信息'200215126', '张辉', '男', 20, 'IS'
insert into student(sno, name, sex, age, dept )
values( '200215126', '张辉', '男', 20, 'IS' )
2.更新学号为200215121的学生的年龄为20，学院为计算机院
update student
set age=20,dept='CS'
where sno='200215121'
3.检索不学C2课的学生姓名与年龄
(思路：学生表里总体的姓名和年龄 “减去” 学C2的学生姓名和年龄 等于不学的)
集合的操作，见SQL语句对结果集操作
(select name ,age from student)
Except
(select name ,age from student,sc where student.sno=sc.sno and sc.cno='2')
4.检索学习全部课程的学生学号
(思路：选课表中全部的学号和课程号 “除以” 课表中全部的课程号)
除法的操作，见SQL语句实现关系代数中的“除法”
select distinct sno from sc sc1
where not exists
(
select cno from course
where not exists
(
select * from sc sc2
where sc2.sno=sc1.sno and sc2.cno=course.cno
)
)
5.检索所学课程包含学生95002所学课程的学生学号
(思路：选课表中全部的学号和课程号“除以”选课表中95002学生所学的课程号)
select distinct sno from sc sc1
where not exists
(
select cno from sc sc2
where sno='95002'
and not exists
(
select * from sc sc3
where sc3.sno=sc1.sno and sc3.cno=sc2.cno
)
)
6.统计信息（总人数，男生人数，女生人数，平均年龄，每个课程的平均分，最高分，95001的总学分）
详情见 Sql Server 2008单个存储过程统计多个信息
select
(select count(sno)from student) sumPersno,
(select count(sno)from student where sex='男') boynumber,
(select count(sno)from student where sex='女') girlnumber,
(select avg(age)from student) avgOfAge,
(select avg(grade)from sc where cno='1' ) avgOfCourse1,
(select avg(grade)from sc where cno='2' ) avgOfCourse2,
(select avg(grade)from sc where cno='3' ) avgOfCourse3,
(select max(grade)from sc where cno='1' ) maxOfCourse1,
(select max(grade)from sc where cno='2' ) maxOfCourse2,
(select max(grade)from sc where cno='3' ) maxOfCourse3,
(select sum(credit)from course, sc where course.cno=sc.cno and sc.sno='95001' ) sumCreditOf95001
7.找出不姓‘王’的学生记录。
select sno, name, sex, age, dept
from student
where name not like'王%'
8.统计每个学生选修课程的个数
select student.sno, count(cno)SCnumber
from student left join sc on sc.sno=student.sno
group by student.sno
9.统计有学生选修的课程门数。
select count(distinct SC.cno)SCCourseNumber from SC;
10.求选修课程号为2的课程名称，学生平均年龄,平均成绩，均保留两位小数
select course.name ,round(avg(age),2) avgOfAge ,round(avg(grade),2) avgGrade
from student ,sc,course
where student.sno=sc.sno
and course.cno=sc.cno
and sc.cno='2'
group by course.name;
还可以用第6个的统计信息的方式来求(其中平均年龄可以换个方式)
select
(select course.name from course where course.cno='2') courseName,
(select round(avg(age),2)avgOfAge from student
where student.sno in(select sno from sc where cno='2'))avgOfAge,
(select round(avg(grade),2) from sc where sc.cno='2')avgGrade
11.检索学号比刘晨同学大，而年龄比他小的学生姓名
法1：暴力法
select student.name from student
where sno>(select sno from student where student.name='刘晨')
and age<(select age from student where student.name='刘晨')
法2：嵌套法
select name from student x
where sno> some(select sno from student y where y.name='刘晨' and x.age<y.age)
12.求年龄大于女同学平均年龄的男学生姓名和年龄。
select name ,age from student
where sex='男'
and age>(select round(avg(age),2) from student where sex='女')
13.求年龄大于所有女同学年龄的男学生姓名和年龄
select name ,age
from student
where sex='男' and age>(select max(age) from student where sex='女')
14.检索每一门课程成绩都大于等于90分的学生学号、姓名和性别
(思路：
1.在SC表中查出最小成绩大于90的学生学号
按照学号进行分组，然后求小组的最小成绩大于90的学号
2.在Student表中根据sno查详细信息
select sno,name,sex from student
where sno in (select sno from sc group by sno having min(grade)>=90)
收获：聚合函数只能用在Select后面和Having后面
group by 对数据进行分组，然后 having对小组内部的数据进行操作
注意：操作的过程是：
先查，后对查询结果分组，分组后，组内调用函数，最后根据Having后面的条件对其进行筛选
注意：
Group By 后面出现的属性，在Select后面一定要出现
15.把低于总平均成绩的女同学成绩提高5%
(思路：
1.首先求总平均成绩
2.修改低于平均成绩的女生的成绩(此时的修改没有改变已求出的平均成绩)
update sc
set grade=grade*1.05
where sc.grade<(select avg(grade) from sc )
and sc.sno in(select sno from student where sex='女')
16.检索至少选修两门课程的学生的学号
思路：
1.按照学号将学生分组
2.统计小组内部选修课程的数量
3.根据条件检索
select sc.sno
from sc
group by sc.sno
having count(sc.cno)>=2
17.检索所修课程平均成绩大于等于90分的学生姓名及其平均成绩
select sc.sno, student.name,avg(grade)
from sc,student
where student.sno=sc.sno
group by sc.sno,student.name
having avg(grade)>90
收获：
当select里有聚合函数时，select的其他元素也必须是聚合函数或出现在group by后面
18.查询最高分的学生学号和课程号
select sno,cno from sc
where grade=(select max(grade)from sc)
19.查询存在有85分以上成绩的课程Cno
不能用：select distinct cno from sc where grade>85，虽然能够实现
要用select distinct cno from sc where grade in(select grade from sc where grade>85)
或者
select cno from sc where grade>85 group by cno
再或者
select cno from sc group by cno having max(grade)>85
20.查询所有选修“数学”课程的同学的学号和成绩
方法一嵌套
select sno,grade from sc
where cno=(select cno from course where course.name='数学' )
方法二链接
select sno,grade from course, sc
where cno=(select cno from course where course.name='数学' )
