前言

近来感觉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='数学' )

---