sql查询复习笔记-小技巧，大智慧

最近看sql的书，这里自己举个例子来温故知新一遍常用常见的sql查询。好了，code is cheap，代码来了。

-- 新建一个数据库TestDb
CREATE DATABASE [TestDb]
-- 新建 学生表 Student
CREATE TABLE [dbo].[Student](
    [Tid] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Age] [int] NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [Tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- 新建 课程表 Course
CREATE TABLE [dbo].[Course](
    [Tid] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Credit] [int] NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED 
(
    [Tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- 新建 教授表 Professor
CREATE TABLE [dbo].[Professor](
    [Tid] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Dept] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Salary] [int] NULL,
    [Age] [int] NULL,
 CONSTRAINT [PK_Professor] PRIMARY KEY CLUSTERED 
(
    [Tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- 新建 学生选择的课程表 StudentTakeCourses 
CREATE TABLE [dbo].[StudentTakeCourses](
    [Tid] [int] IDENTITY(1,1) NOT NULL,
    [Sid] [int] NULL,
    [Cid] [int] NULL,
 CONSTRAINT [PK_StudentTakeCourses] PRIMARY KEY CLUSTERED 
(
    [Tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- 新建 教授所教的课程表 ProfessorTeachCourses 
CREATE TABLE [dbo].[ProfessorTeachCourses](
    [Tid] [int] IDENTITY(1,1) NOT NULL,
    [Cid] [int] NULL,
    [Pid] [int] NULL,
 CONSTRAINT [PK_ProfessorTeachCourses] PRIMARY KEY CLUSTERED 
(
    [Tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

下面对上面新建表的字段稍微介绍一下：

1.Student
Tid  关键字 自增字段
Name 学生名
Age  学生年龄

2.Course
Tid  关键字 自增字段
Name 课程名
Credit  学分

3.Professor
Tid  关键字 自增字段
Name 教授名
Dept 所在系
Salary 月薪
Age  教授年龄

4.StudentTakeCourses
Tid  关键字 自增字段
Sid 学生Tid
Cid  课程Tid

5.Student
Tid  关键字 自增字段
Pid  教授Tid
Cid  课程Tid

接着在相应表里插入一些记录：

use TestDb
insert into Student values('Jeff',25)
insert into Student values('Angela',22)
insert into Student values('Bob',23)
insert into Student values('Dwight',22)
insert into Student values('Ella',21)
insert into Student values('Fred',19)
insert into Student values('Nicolas',23)
insert into Student values('John',26)
insert into Student values('Tom',21)
insert into Student values('Zack',20)

insert into Course values('Sql Server',4)
insert into Course values('Oracle',4)
insert into Course values('My Sql',4)

insert into Professor values('Fogson','Science',6000,35)
insert into Professor values('David','Computer',8000,45)
insert into Professor values('Owen','Math',10000,54)
insert into Professor values('Gerrard','Computer',7500,39)
insert into Professor values('Lampard','Math',9000,42)

insert into StudentTakeCourses values(1,1)
insert into StudentTakeCourses values(1,2)
insert into StudentTakeCourses values(1,3)
insert into StudentTakeCourses values(2,1)
insert into StudentTakeCourses values(3,1)
insert into StudentTakeCourses values(3,3)
insert into StudentTakeCourses values(4,1)
insert into StudentTakeCourses values(4,2)
insert into StudentTakeCourses values(5,2)
insert into StudentTakeCourses values(6,2)
insert into StudentTakeCourses values(6,3)

insert into ProfessorTeachCourses values(1,1)
insert into ProfessorTeachCourses values(1,2)
insert into ProfessorTeachCourses values(1,3)
insert into ProfessorTeachCourses values(4,2)
insert into ProfessorTeachCourses values(3,1)
insert into ProfessorTeachCourses values(3,3)

最后就是要按条件查询结果了：
1 回答关于”否定“的问题
问题（1）： 找到没有选择sql server课程的学生

-- 错误的查询语句
select * from Student where tid in 
(select sid from StudentTakeCourses 
 where cid<>(select tid from Course where Name='Sql Server'))

-- 正确的查询语句
select * from Student where tid not in 
(select sid from StudentTakeCourses 
 where cid=(select tid from Course where Name='Sql Server'))

分析：上述问题的结果集应该是包含没有选任何课程的学生和选择了除sql server之外的任何其他课程的学生，但是第一种查询返回的结果集是“选取了不是sql server课程的学生“而不是”没有选sql server的学生“。
结论：真正的否定需要两步操作，即要找出”谁不在“，先找出”谁在“，然后排除它们。
问题（2）：找到选取sql server或者my sql课程（但不是两者都选）的学生。

-- 错误的查询语句
select * from Student where tid in 
(select sid from StudentTakeCourses 
 where cid<>(select tid from Course where Name='Sql Server')
and  cid<>(select tid from Course where Name='My Sql'))

-- 正确的查询语句
select sd.* from Student as sd,StudentTakeCourses as stc 
where sd.tid=stc.sid and stc.cid in (select tid from Course where Name='Sql Server' or Name='My Sql')
and sd.tid not in
(select stca.sid from StudentTakeCourses as stca,StudentTakeCourses as stcb
where stca.sid=stcb.sid and stca.cid =(select tid from Course where Name='Sql Server')
and stcb.cid=(select tid from Course where Name='My Sql'))
 

分析：从正确的查询语句我们可以看到这里也采取了问题一里的解决方案，并通过使用自联接查找选取sql server和my sql课程的学生，然后使用子查询，选出选取sqlserver或者mysql的学生。
问题（3）：选取了sqlserver而且未选取其他课程的学生

-- 错误的查询语句
select sd.* from Student as sd,StudentTakeCourses as stc 
where sd.tid=stc.sid
and stc.cid = (select tid from Course where Name='Sql Server')

-- 正确的查询语句
select sd.* from Student as sd,StudentTakeCourses as stc 
where sd.tid=stc.sid
and sd.tid not in 
(select sid from StudentTakeCourses where cid <>(
select tid from Course where Name='Sql Server'))

  

分析：正确的查询通过子查询找到所有没有选sqlserver的学生，外层查询通过not in就找到了选择sqlserver或者没有选择任何一门课程的学生，然后表Student和StudentTakeCourses之间的联结会筛选掉未选择任何课程的学生。
2 回答有关”至多“的问题
问题（4）：找到选取了课程并且至多选取两门课程的学生

select sd.Tid,sd.Name,sd.Age from Student as sd,StudentTakeCourses as stc 
where sd.tid=stc.sid group by sd.Tid,sd.Name,sd.Age having count(*)<=2

 分析：通过聚集函数count可以查找到选取课程不多于两门的学生。下面给出一种使用多次自联接的解决方案：

select distinct sd.* from Student as sd,StudentTakeCourses as stc 
where sd.tid=stc.sid
and sd.tid not in 
(select stca.sid from StudentTakeCourses  stca,StudentTakeCourses stcb,StudentTakeCourses stcc
where stca.sid=stcb.sid
and stcb.sid=stcc.sid
and stca.cid<stcb.cid
and stcb.cid<stcc.cid
)

总结：上述子查询的where判断可以这样理解，对于某个特定学生，返回满足下述条件的结果集：他的第一个课程id小于第二个课程id，而且第二个课程id小于第三个课程id。如果学生选择的课程小于三门，则该表达式永远不会为真，因为并不存在第三个课程id。子查询查到了选取三门以上课程的学生，外层查询not in加上学生表和学生选择的课程表联接查询一下就找到了至少选取一门课程且不多于两门的学生。
问题（5）：找到至多比另外两名学生大的学生。
分析：问题换一种说法就是“找到比0个学生、1个学生、2个学生大的那些学生。”

select Tid,Name,Age from 
(select Tid,Name,Age ,dense_rank() over (order by age) as num from Student ) sd
where num<=3

通过窗口函数DENSE_RANK，我们可以非常方便地找到所要选取的结果集。其实，不使用dbms的函数，同样可以取到结果，思路如下： 先找到不比三个以上学生大的学生，然后排除他们。

select * from Student where Tid not in
(select sd1.Tid from Student sd1,Student sd2,Student sd3,Student sd4
where sd1.age>sd2.age and sd2.age>sd3.age and sd3.age>sd4.age)

未完待续。