SQL总结 - 博客文库 - 博客园

SQL总结

作者：dodo-yufan  来源：博客园  发布时间：2008-05-22 16:52  阅读：991 次  原文链接   [收藏]  

/*====================================第0节====================================*/
/*=======================进行1、2、3、4、5、小节前的准备工作=====================*/

---1\创建一个数据库。

-- 这一步可以不要，但是有了这一步，你做题时所产生的“垃圾”就不会留在系统自带的
-- 6个数据库中。当我们做完题目后，将自己建立的数据库删除，就解决了所有的垃圾问题。

create database mydata

---2\打开新创建的数据库。

use mydata

---3\创建3个表，这3个表被放在 mydata 数据库中。

-- 为了应对北大的上机考试，这里创建的三个表包含了各种常见的情况，
-- 所以比较复杂。请耐心搞懂每个列的定义以及各种约束的定义以及触发器的使用。

-- 学生(学号，姓名，性别，生日，年龄 )
-- 课程(课程号，课程名，学分，授课教师姓名 )
-- 选修(学号，课程号，成绩,选修日期 )

create table s -- 学生表定义。
(
s# char(4) primary key , -- 主键为学号。,
sname varchar(8) not null , -- 姓名不能为空值。
sex char(1) default 'm' ,   -- 性别默认为男性。
birthday datetime ,
age as year(getdate()) - year(birthday) , -- 年龄为自动计算列。
check(s# like 's%') , -- 学号必须以s开头。
check((sex = 'm') or (sex = 'f')) -- 性别必须为男性或女性。^-^
)

create table c -- 课程表定义。
(
c# char(3) primary key , -- 主键为课程号。,
cname varchar(20) not null , -- 课程名不能为空值。
credit tinyint not null ,   -- 学分不能为空值。
teacher varchar(8) ,
unique(cname , credit) ,
-- 课程名和学分是唯一的，即：(课程名 ，学分 )也可以唯一确定一个元组。
check(credit between 1 and 15) -- 学分必须在1到15之间。
)

create table sc -- 选修表定义。
(
s# char(4) ,
c# char(3) ,
grade tinyint ,
seleday datetime default getdate() , -- getdate()是取得当前系统的时间的函数。
-- 选课日期采用默认值，系统自动设置，即：什么时候往表中插入选课记录，这个时间就
-- 自动被系统记下来。例如我们网上报考时，网上报名系统会自动记下你的报名时间。
primary key(s# , c#) , -- 主键为学号、课程号的组合。
foreign key(s#) references s(s#) -- 学号引用表 S 。
on delete no action
-- 只有在 SC 表中没有某学生的选修元组时，才可以在 S 表中删除该学生信息。
on update cascade ,
-- 在 S 表中修改某学生学号时，系统会自动更新SC表中的学号，使SC与S表保持一致。
foreign key(c#) references c(c#)
on delete no action -- 参见上面有关外键的注释。
on update cascade , -- 参见上面有关外键的注释。
check(grade is null or grade between 0 and 100)
-- 成绩在0到100之间，或者为空，表示该学生缺考。
)
go  
 
-- 注意这个go命令是不可以省略的，否则下面的触发器定义语句将不可执行，
-- 除非单独选中执行。

/*
再来创建一个触发器：规定每个学生每年最多只能选修5们课程。
按照自考教材上的说法，只要在定义表 SC 时，加入一条CHECHK语句就可以了：

check( 5 >= all(select count(*) from sc group by s#) )

或者创建一个断言也可以实现这个约束：

create assertion asse1 check(约束条件)

遗憾的是 SQL SERVER 不支持 在 check 语句中含有 select 语句。
也不支持断言的定义。只好用触发器来实现这个约束了。
*/

create trigger sc_tr on sc
for insert , update
as
if @@rowcount = 0
  return
if exists(select count(*) from sc
      group by s# , year(seleday)
      having count(*) > 5)
  begin
    rollback transaction
    -- 如果有学生在一年内选修的课程超过了5门，则回滚事务。并给出下面的提示。
  print '此操作违反了约束<学生在一年内选修的课程不能超过了5门>！'
  print '系统已回滚违反约束的<插入操作或修改操作>。'
    end
go

---4\向 s、c、sc 表中插入数据。

insert into s values('s001' , 'jim', 'm' , '1985-03-29')
insert into s values('s002' , 'kate', 'f' , '1987-05-13 12:34:40')
insert into s values('s003' , 'tom', 'm' , '1983-08-16 9:36:54.123')
insert into s values('s004' , 'jacky','f' , '1984-11-25')
insert into s values('s005' , 'toky', 'f' , '9-26-1986')
insert into s values('s006' , 'wuli', 'f' , '6-3-1984')
insert into s values('s007' , 'john','m' , '1985-09-22')
-- 无论采用“月日年”还是“年月日”，系统都可正确识别出来。

insert into c values ('c01' , '英语' , 15 , '王喜')
insert into c values ('c02' , '语文' , 5 , '赵小明')
insert into c values ('c03' , '数学' , 12 , '张树')
insert into c values ('c04' , '数据库原理' , 6 , '孟君')
insert into c values ('c05' , '数据结构' , 8 , '孟君')
insert into c values ('c06' , '软件工程' , 10 , '王喜')

insert into sc(s#,c#,grade) values('s001' , 'c02' , 78)
insert into sc(s#,c#,grade) values('s001' , 'c03' , 70)
insert into sc(s#,c#,grade) values('s002' , 'c01' , 85)
insert into sc(s#,c#,grade) values('s002' , 'c03' , 83)
insert into sc(s#,c#,grade) values('s002' , 'c04' , 63)
insert into sc(s#,c#,grade) values('s003' , 'c01' , 85)
insert into sc(s#,c#,grade) values('s003' , 'c02' , 73)
insert into sc(s#,c#,grade) values('s003' , 'c04' , 56)
insert into sc(s#,c#,grade) values('s004' , 'c01' , 67)
insert into sc(s#,c#,grade) values('s004' , 'c02' , 96)
insert into sc(s#,c#,grade) values('s004' , 'c03' , 87)
insert into sc(s#,c#,grade) values('s004' , 'c04' , 65)
insert into sc(s#,c#,grade) values('s004' , 'c05' , 74)
insert into sc(s#,c#,grade) values('s006' , 'c01' , 75)
insert into sc(s#,c#,grade) values('s006' , 'c02' , 79)
insert into sc(s#,c#,grade) values('s006' , 'c04' , 98)
insert into sc(s#,c#,grade) values('s007' , 'c01' , 66)
insert into sc(s#,c#,grade) values('s007' , 'c02' , 70)
insert into sc(s#,c#,grade) values('s007' , 'c03' , 61)
insert into sc(s#,c#,grade) values('s007' , 'c04' , 58)

---5\书写、选中、并执行查询、修改、删除等操作。

select * from s
select * from c
select * from sc
go

/*
无论SQL语句出现在什么地方，只要是正确的，就可以将其选中后执行。
就算SQL语句被包含在注释里，仍然可以将其选中后执行。读者可以试一试。

delete from sc -- 如果在表定义时，没有书写 'on delete cascade'，那么
delete from s   -- 删除元组时，要注意删除顺序为：先删除从表，后删除主表。
delete from c   -- 否则，会发生错误，因为存在 foreign key 约束。
*/

---6\创建一个重修信息视图。每学分25元。

create view 重修信息表(学号 , 姓名 , 课程名 , 重修费)
as
select s.s# , sname , cname , 25 * credit
from s , sc , c
where s.s# = sc.s# and sc.c# = c.c# and grade < 60
go

-- 注意：在视图定义语句中不可以含有order by子句，而应该在使用的时候才加上。

select * from 重修信息表 order by 学号

---7\删除表、视图、临时表、触发器等等。

drop trigger sc_tr     -- 删除定义在表 SC 上的触发器sc_tr。
drop view 重修信息表   -- 删除视图。
drop table sc    
drop table s
drop table c

-- 注意删除表和删除元组的区别：删除表一定要先删除从表，后删除主表。
-- 否则，回发生错误，因为存在 foreign key 约束。
-- 而删除元组时，若在表定义中书写了'on delete cascade'，先删除哪个表的元组都行。        
   
---8\删除自己建立的数据库。

use master -- 因为当前正在使用的就是 mydata 数据库，要想删除它，必须切换
drop database mydata -- 到任意的其他数据库后，才能够执行删除操作。
go


/*====================================第1节=====================================*/
/*======================SQL SERVER 与自考教材不兼容的几点小结====================*/

---1\insert 用法。

-- 教材72页：可以在一个 insert 语句中同时插入多条元组。
-- SQL SERVER：不可以在一个 insert 语句中同时插入多条元组(除非插入查询结果)。
-- 解决方法：只好一条一条的插入啦！^-^

---2\check 用法。

-- 教材58页：check 子句中的条件可以很复杂，甚至可以嵌有 select 语句。
-- SQL SERVER ：check 子句不可以嵌有 select 语句。
-- 解决方法：用触发器来搞定。具体实施步骤，请参见第0节和第5节的例题。

---3\unique 用法。

-- 教材70页：可以用谓词 unique 来测试一个集合里是否重复元组存在。
-- 在基本表 c 中检索开设了一门课程的教师姓名。

select teacher from c as x
where unique (select teacher from c as y
        where y.teacher = x.teacher)

-- SQL SERVER ：不可以用谓词 unique 来测试一个集合里是否重复元组存在。
-- 在SQL SERVER中，unique 是一个关键字，在定义表的时候，用来限制列或组合列的唯一性。
-- 解决方法：大部分情况下，都可以用 having 子句来搞定。

select teacher from c
group by teacher
having count(c#) = 1

-- 用 having 子句来代替还有一个好处就是比较有灵活性，例如题目改为：
-- 检索开设了两门课程的教师姓名。那么，只要将1改为2就可以了：

select teacher from c
group by teacher
having count(c#) = 2

---4\intersect 以及 except 用法。

-- 教材68页：当两个子查询结构完全一致时，可以对这两个子查询执行并、交、差操作。
-- (select I) union/intersect/except (select II)
-- SQL SERVER ：只支持union(并)，不支持intersect(交)、except(差)
-- 解决方法：参见第2节集合操作。

---5\natural join 用法。

-- 教材71页：自然联结可以在from子句中以直接形式给出。

select s.s# , sname
from (s natural inner join sc)
where c# = 'c02'

-- SQL SERVER ：没有 natural join 这个关键字。
-- 解决方法：

select s.s# , sname
from s inner join sc on s.s# = sc.s#
where c# = 'c02'

---6\create assertion 用法。(断言)

-- 教材170页：create assertion <断言名> check(<条件>)
-- SQL SERVER ：没有 create assertion 这个关键字。
-- 解决方法：大部分情况下，都可以用触发器来搞定。

---7\元组---集合 算术比较 用法。

-- 教材69页： 元组 比较运算符 some(集合) / 元组 比较运算符 all(集合)
-- 这里要求元组和集合中的元素的结构一致。
-- 按照教材上的说法，那么下面的这个语句应该是可以执行的：

select s# , c# from sc
where (s# , c#) = some (('s001' , 'c01') ,('s002','c04'))

-- SQL SERVER ：上面的那个查询语句不可以执行。
-- 解决方法：将(s# , c#)分解。

select s# , c# from sc
where (s# = 's001' and c# = 'c01')
or (s# = 's002' and c# = 'c04')

---8\修改基本表的结构。

-- 教材59页：alter table table_name drop column_name 删除表的某些列。

-- SQL SERVER ：alter table table_name drop column column_name
-- 要在drop后加上cloumn关键字。


/*====================================第2节====================================*/
/*===============================SQL常用语法举例================================*/

---1\联结查询。

-- 查询不及格的学生 学号、姓名、年龄、课程、成绩：

select s.s# , sname , age , c# , grade
from s , sc
where sc.s# = s.s# and grade < 60

-- 已知学号为s001的学生只选修了2门课程，请仅以一条记录的形式显示相关信息。

select top 1 sname , m.cname , x.grade , n.cname , y.grade
from sc as x , sc as y , c as m , c as n , s
where x.s# = 's001'
and y.s# = 's001'
and x.c# <> y.c#
and s.s# = 's001'
and x.c# = m.c#
and y.c# = n.c#

/*
结果为：

sname   cname   grade     cname   grade
-------- ------   --------- ------ ------------
jim     语文   78       数学   70
*/

---2\嵌套查询。

-- 查询已经有学生选修，但没有任何男同学选修的课程名：

select cname from c
where c# in (select c# from sc)
and c# not in (select c# from sc
          where s# in (select s# from s
                  where sex = 'm'))


---3\相关子查询。

-- 查询选修c01课程的学生姓名：

select sname from s
where 'c05' in (select c# from sc
          where s# = s.s#)

---4\存在性判断。

-- 查询kate和tom都选修的课程的相关信息。

select * from c
where exists(select * from sc
        where c# = c.c#
        and s# = (select s# from s
              where sname = 'kate')
        and exists (select * from sc
                where c# = c.c#
                and s# = (select s# from s
                      where sname = 'tom')))

-- 也可以这么做：

select * from c
where c# in (select c# from sc
        where s# = (select s# from s
                where sname = 'kate'))
and c# in (select c# from sc
      where s# = (select s# from s
              where sname = 'tom'))

-- 只要某学生有一门课程成绩大于90,就显示该学生的姓名以及该学生所有
-- 选修课程的课程名、成绩。

select sname , cname , grade
from s , c , sc as x
where s.s# = x.s#
and c.c# = x.c#
and exists(select * from sc
      where s# = x.s#
      and grade > 90)

-- Exists只是测试是否存在符合条件的元组。而不管其后的select语句到底选择了
-- 哪些列或聚合函数。所以Exists语句后面的select语句在任意情况下，都可以
-- 简写为 select * from ... 例如：

select s#,c#,grade
from sc as x
where exists (select * from sc
        where s#=x.s#
        having max(grade) > 90)

-- 上面的那个语句包含了下面的这个语句，但是可以正确执行。
-- 但是下面这个语句单独拿出来却不可以执行(因为语法错误)。

select * from sc
having max(grade) > 90

---5\导出表。

-- SQL不仅支持在where和having子句中使用select子查询，在from子句中也可使用
-- select子查询。这就是导出表。使用时，要给导出表以及表中的各个列起个名字。
-- 导出表是可以嵌套使用的，例如：

-- 查询有多少个互不相同的平均分。

select count(*)
from (select distinct avg_grade
    from (select avg(grade)
        from sc group by s#)
    as t1(avg_grade)) -- 导出表t1。
as t2(avg_grade) -- 导出表t2。


---6\集合操作。

-- A1: 判断 集合X 是否等于 集合Y。(集合Y未知 )
-- 查询和tom选修的课程完全一样的同学姓名。

select sname from s as x
where sname <> 'tom'
and exists(select * from sc as y
      where s# = x.s#
      and not exists(select * from sc
                where s# = y.s#
                and c# not in (select c# from sc
                          where s# = (select s# from s
                                  where sname = 'tom')))
      and not exists(select * from sc
                where s# = (select s# from s
                        where sname = 'tom')
                and c# not in (select c# from sc
                          where s# = y.s#)))

-- A2: 判断 集合X 是否等于 集合Y。(集合Y已知 )
-- 查询只选修了c01、c02、c03、c04课程同学姓名。

select sname from s
where s# in (select s# from sc
        where c# = 'c01'
        and s# in (select s# from sc
                where c# = 'c02'
                and s# in (select s# from sc
                      where c# = 'c03'
                      and s# in (select s# from sc
                              where c# = 'c04'))))
and s# in (select s# from sc
      group by s#
      having count(c#) = 4)


-- B1: 判断 集合X 是否包含 集合Y。(集合Y未知 )
-- 查询选修的课程包含了tom选修的课程的同学姓名。

select sname from s as x
where sname <> 'tom'
and exists(select * from sc as y
      where s# = x.s#
      and not exists(select c# from sc
                where s# = (select s# from s
                        where sname = 'tom')
                and c# not in(select c# from sc
                          where s# = y.s#)))

-- B2: 判断 集合X 是否包含 集合Y。(集合Y已知 )
-- 查询选修了c01、c02、c03、c04课程同学姓名。

select sname from s
where s# in (select s# from sc
        where c# = 'c01'
        and s# in (select s# from sc
                where c# = 'c02'
                and s# in (select s# from sc
                      where c# = 'c03'
                      and s# in (select s# from sc
                              where c# = 'c04'))))


-- C1: 判断 集合X 相交 集合Y 是否为空。(集合Y未知 )
-- 查询选修的课程中，至少有两门课程和kate选修的课程一样的同学姓名。

select sname from s as x
where sname <> 'kate'
and exists(select count(c#) from sc
      where s# = x.s#
      and c# in (select c# from sc
              where c# in (select c# from sc
                      where s# = (select s# from s
                              where sname = 'kate')))
      having count(c#) >= 2)

-- C2: 判断 集合X 相交 集合Y 是否为空。(集合Y已知 )
-- 查询选修的课程中，至少有两门课程在c01、c03、c05中的同学姓名。

select sname from s as x
where exists(select s# from sc as y
        where s# = x.s#
        and exists(select count(c#) from sc
                where s# = y.s#
                and c# in ('c01' , 'c03' , 'c05')
                having count(c#) >= 2))

---7\两极操作。

-- 查询平均分最高的学生学号、姓名、平均分。

select sc.s# , sname , avg(grade)
from s , sc
where s.s# = sc.s#
group by sc.s# , sname
having avg(grade) >= all(select avg(grade)
                from sc
                group by s#)

-- 查询选修课程最少的学生学号、姓名、课程数目。

select sc.s# , sname , count(c#)
from s , sc
where s.s# = sc.s#
group by sc.s# , sname
having count(*) <= all(select count(*)
              from sc
              group by s#)

-- 查询取得c01课程最高分数的学生学号、姓名、成绩。

select s.s# , sname , grade
from s , sc
where s.s# = sc.s#
and c# = 'c01'
and grade = (select max(grade) from sc
        where c# = 'c01')

-- 查询取得总学分最高的学生学号、姓名、总学分。

select s.s# , sname , sum(credit) sum_credit
from s , c , sc
where s.s# = sc.s#
and c.c# = sc.c#
group by s.s# , sname
having sum(credit) >= all(select sum(credit) from c , sc
                where c.c# = sc.c#
                group by s#)
             
---8\名次操作。

-- 一般情况下，名次操作都用游标来做，也可以用其它特殊方法来做，(参见第4节变量赋值)
-- 还可以直接用SQL的select语句来完成查询，不过有点麻烦。如下例：

-- 查询平均成绩第4的学生学号及其平均成绩,若存在并列第4，则按学号的升序显示。
-- 解题思路：若有且仅有3个互不相同的平均分比某个学生的平均分的高，则该学生就是第4名。

select s# , avg_grade
from (select s# ,avg(grade)
    from sc group by s#)
as t1(s# , avg_grade)
where exists(select count(*)
        from (select distinct avg(grade)
            from sc group by s#)
        as t2(avg_grade)
        where avg_grade > t1.avg_grade
        having count(*) = 3)
order by s# asc              

-- 看懂这道题目的关键是要看懂被 exists() 所包含的那个SQL语句的含义。
-- 这个语句包含了3点语法：导出表、相关子查询、having的特殊用法。
-- 有关having的特殊用法请参见第4节(在没有 group by 的情况下使用having)。

-- 若题目改为：
-- 查询平均成绩前4名的学生学号及其平均成绩,则按名次的升序(成绩的降序 )显示。
-- 若有并列名次，按学号的升序显示。
-- 解题方法：只要将having count(*) = 3 改为 <=3就可以了。

select s# , avg_grade
from (select s# ,avg(grade)
    from sc group by s#)
as t1(s# , avg_grade)
where exists(select count(*)
        from (select distinct avg(grade)
            from sc group by s#)
        as t2(avg_grade)
        where avg_grade > t1.avg_grade
        having count(*) <= 3)
order by avg_grade desc , s# asc

-- 也可以这么做：

select s# , avg(grade) from sc
group by s#       -- 注意：distinct 要放在top之前。
having avg(grade) in (select distinct top 4 avg(grade)
              from sc group by s#
              order by avg(grade) desc)
order by avg(grade) desc , s# asc

---9\表达式应用。

-- a)select 语句可以派生出多个列，尽管这些列不在基本表中。

select count(*),count(*)+1 ,avg(grade) , 'abcdefg' from sc

-- b)select 语句支持在表达式中喊有select 语句。例如：
-- 查询每个学生的平均分和最高平均分相差的分数。

select sc.s# as 学号 , sname 姓名 ,
cast((select avg(grade) from sc group by s#
    having avg(grade)
    >= all(select avg(grade)
    from sc group by s#)) - avg(grade) as varchar) + '分'
    as 比最高平均分低
from sc , s
where sc.s# = s.s#
group by sc.s# , sname
order by 比最高平均分低 desc

/*
结果如下：

学号   姓名     比最高平均分相低                
---- -------- -----------------------
s002   kate       7分
s004   jacky     7分
s007   john       21分
s003   tom       13分
s001   jim       10分
s006   wuli       0分
*/

---10\update 用法。

-- 先看两个例子：

update sc
set grade = (select avg(grade) from sc where s# = 's006')
where s# = 's006'

-- 这个语句的执行结果是：学号为s006的学生的每们课程的成绩都变成了84。
-- <84 = (75 + 79 + 68) / 3>,并不是每更新一个元组，就从新执行一次查询，
-- 而是在更新之前，一次性查询完毕。

update sc
set grade = (select avg(grade) from sc as x
        where sc.s# = x.s#)
/*
这个语句的执行结果是：每个学生的任何一门课程的成绩都变成了他的平均成绩。
但是注意下面的这个语句是不能执行的：

update sc as x
set grade = (select avg(grade) from sc
        where s# = x.s#)

也就是：在update 后不能给表起别名。
*/


/*================================*===第3节===================================*/
/*========================SQL SERVER 2000 的常用语法小结=======================*/

---1\各种联结。

-- a)内联结。

select * from s inner join sc on s.s# = sc.s#
select * from s , sc where s.s# = sc.s#

-- b)外联结。

select * from s left outer join sc on s.s# = sc.s#
select * from sc right outer join c on c.c# = sc.c#
select * from s full outer join sc on s.s# = sc.s#

select s.s# , count(c#)
from s left outer join sc on s.s# = sc.s#
group by s.s#

-- c)交叉联结。

select * from s cross join sc
select * from s , sc

---2\临时表和视图。
/*
有本地和全局两种类型的临时表，本地临时表的名称以符号 (#) 开头；
它们仅对当前的用户连接是可见的；当用户从 SQL Server 2000 实例断开连接时，
临时表就被删除。全局临时表的名称以符号 (##) 开头，创建后对任何用户都是
可见的，当所有引用该表的用户从 SQL Server 断开连接时，临时表被就删除。
当然，也可以在没有断开连接时，执行 drop table #name/##name将其删除。
临时表没有主键，可以有重复元组。
*/

/*
临时表和视图在分步查询中的应用：

若数据查询的逻辑条件很复杂，那么用一个SQL语句来查询时，往往比较困难，而利用
系统提供的临时表、视图等机制，我们可以分多步来完成这个查询。分步书写查询，
可以使得查询逻辑变得条理清晰。但是上机实验有时会规定不准用临时表和视图，这时，
我们可以先分步书写查询，再利用导出表机制将临时表、视图变换为导出表，从而将
分步查询联合为一个查询语句。具体例子，参加第5节。
*/

-- a) 先建立一个空表，然后再插入数据。

select s# , avg(grade) as avg_grade
into #excellence1
from sc
where 0<>0 -- 创建一个空的临时表。
group by s#

insert into #excellence
select s# , avg(grade)
from sc group by s#
having avg(grade) >= 75
order by avg(grade) desc , s# asc

select * from #excellence1
drop table #excellence1

-- b)创建表的同时插入数据。

select s# , avg(grade) as avg_grade
into #excellence2
from sc
group by s#
having avg(grade) >= 75
order by avg(grade) desc , s# asc

select * from #excellence2
drop table #excellence2

---3\触发器。

-- a)使用 inserted 和 deleted 表
/*
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。
SQL Server 自动创建和管理这些表。
可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；
然而，不能直接对表中的数据进行更改。

Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。
在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，
并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。
在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。
Inserted 表中的行是触发器表中新行的副本。

更新事务类似于在删除之后执行插入；首先旧行被复制到 deleted 表中，
然后新行被复制到触发器表和 inserted 表中。

在设置触发器条件时，应当为引发触发器的操作恰当使用 inserted 和 deleted 表。
虽然在测试 INSERT 时引用 deleted 表或在测试 DELETE 时引用 inserted 表
不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。

如果触发器操作取决于一个数据修改所影响的行数，应该为多行数据修改
(基于 SELECT 语句的 INSERT、DELETE 或 UPDATE )使用测试(如检查 @@ROWCOUNT )，
然后采取相应的对策。
*/

create table test_trigger
(
a int primary key ,
b char(2) ,
d char(3)
)
go

create trigger modify_restrict_tr1 on test_trigger
for insert , update as
if exists(select count(*)
      from test_trigger
      group by b
      having count(*) > 3)
  begin
  rollback transaction
  print 'error: exists count(*) > 3 group by b'
  end
go

create trigger list_inserted_tr on test_trigger
for insert as
select * from inserted
go

create trigger list_deleted_tr on test_trigger
for update , delete as
select * from deleted
go

insert into test_trigger values(1 , 'sx' , 'fgg')
insert into test_trigger values(2 , 'we' , 'bvh')
insert into test_trigger values(3 , 'er' , 'dgh')
insert into test_trigger values(8 , 'se' , 'dgh')
go

insert into test_trigger values(4 , 'er' , 'grh')
insert into test_trigger values(5 , 'er' , 'vbn')
insert into test_trigger values(6 , 'we' , 'frg')
insert into test_trigger values(7 , 'er' , 'ert')
insert into test_trigger values(9 , 'sx' , 'ert')
go

update test_trigger
set b = 'sx'
where b = 'we' or b = 'se'
go

delete from test_trigger where b = 'sx'
go

drop table test_trigger

select * from test_trigger


---4\游标。

-- 注意：T-SQL 标准比较复杂，就不讨论了，这里列举的是SQL-92标准，
-- 并非SQL SERVER的T-SQL 标准，不过SQL SERVER支持SQL-92标准，可以放心使用！

-- a)只进游标(默认) 和 卷游标(scroll)
-- b)只读游标(read only) 和 可更新游标(update)
-- c)动态游标(默认) 和 静态游标(insensitive)

-- 具体例子，请参见第5节。下面给出SQL-92标准游标定义语法：

declare cursor_name [ insensitive ] [ scrool ]
for select_statement
[ for { read only | update [of 列名序列] } ]

-- 1)静态游标是只读游,因此标游标选项FOR UPDATE和INSENSITIVE冲突。
-- 2)动态游标可动态反映出对数据库所做的更新。
-- 3)动态游标支持READ ONLY选项，表示不可以通过游标更新数据库，但是仍可
--   动态反映其他操作对数据库所做的更新。
-- 4)卷游标支持first last prior next relative absolute ,只进游标只支持next。
-- 5)如果游标是可更新的，若想更新游标当前行，则采用如下形式：

update table_name
set column_name = {...}
where current of cursor_name -- 表示游标当前行。


---5\系统变量(不区分大小写)。

@@rowcount -- 对select、delete、update、insert有效。
-- 记下最近一次操作所影响的行(元组)数，值为0表示没有行受影响或者
-- 最近一次select、delete、update、insert操作执行失败。

@@fetch_status -- 记下游标的推进状态，值为0表示游标推进成功，
-- 否则表示游标推进失败。

@@cursor_rows -- 记下最近一次打开的游标所返回的行数。
-- 当最近一次打开的游标被关闭后，其值变为0。

@@error -- 记下最近一次Transact-SQL语句的执行情况，值为0表示执行成功，
-- 否则表示执行语句失败。例如：数据库中已经存在 sc 表，若再次执行

create table sc(a int primary key) -- 结果如下：

-- 服务器: 消息 2714
-- 数据库中已存在名为 'sc' 的对象。

print @@error -- 值为2714，因为create table sc执行失败。
print @@error -- 值为0，因为print @@error执行成功。

-- 每执行一个SQL语句，@@error的值就被从新设置，甚至用来读@@error的语句
-- 都会从新设置它的值。

---6\常用函数(不区分大小写)。

print getdate()
print year(getdate())
print month(getdate())
print day(getdate())

-- 注意：两个日期型数据可以直接进行比较大小操作，一个日期型数据可以与实型/整型
-- 数据相加减得到另一个日期型数据，但是不可以直接拿两个日期型数据相减。
-- 必须用函数来实现两个日期型数据相减。

datediff(day , date1 , date2)   -- date2与date1相差的天数
datediff(month , date1 , date2) -- date2与date1相差的月数
datediff(year , date1 , date2)   -- date2与date1相差的年数

dateadd(day , 天数 , date1)     -- date1加上天数所得到的日期，天数可以是负数。
dateadd(month , 月数 , date1)   -- date1加上月数所得到的日期，月数可以是负数。
dateadd(year , 年数 , date1)   -- date1加上年数所得到的日期，年数可以是负数。

print getdate() + 30   -- 今天的日期加上30天所得的日期。
print getdate() - 30   -- 今天的日期减去30天所得的日期。
print getdate() + 1.5 -- 今天的日期加上1.5天所得的日期。

print abs(-6.2)
print floor(3.3)
print ceiling(5.1)
print power(5,3) -- 5的3次方。
print sqrt(81)

-- cast(表达式 as 数据类型) 将表达式的数据类型转换为所指定的新数据类型。

print cast(120.56 as char(6)) + '是小数' -- 数字型转字符型。
print cast('1999' as int) + 1 -- 字符型转数字型。

-- ltrim()去除字符串左边的空格，rtrim()去除字符串右边的空格。

print ltrim('   abcd   efg')
print rtrim('abcd   ') + 'efg'

---7\缺省值列、自动计算列。


---8\unique约束。



---9\控制流。请参见北大出版的上机蓝皮书。 :-)


/*====================================第4节=====================================*/
/*================================其它一些小总结=================================*/

---1\go 的使用(注：比较重要！)

-- GO 不是Transact-SQL语句，而是可被SQL Server查询分析器识别的命令。可以用它来
-- 将一SQL文件分割为多个批处理单元。

-- GO 命令十分重要，即使你对SQL很有把握，若不知道 GO 命令，那么上机器实验若规定
-- 只能将所有的语句保存为一个文件，执行SQL文件时，一定会出现问题。即使你做对了，
-- 若北大的老师不看源代码的话，你的上机很可能会挂掉。因为北大的上机实验一般都要求
-- 先定义几个表，然后再定义触发器，视图等等。若没有GO，所有的触发器、视图的定义
-- 都不可执行。原因如下：

-- CREATE PROCEDURE、CREATE TRIGGER 、CREATE VIEW 等等语句必须是批处理的第一条语句。
-- 因此，若没有 GO 命令对整篇SQL语句的分割，那么，在一个SQL文本中就不可能一次性
-- 执行多条含有CREATE PROCEDURE、CREATE TRIGGER 、CREATE VIEW 的语句。必须一条条的
-- 选中后执行。

-- 要是一条一条的选中执行，那么有没有GO是无所谓的。但是谁知道老师是怎么查阅试题呢！
-- 加上GO命令，保险一些。呵呵！

-- 读者可以将第0小节里的所有的 GO 命令都去掉，再次执行一次，系统就会发出错误指示。

---2\对 group by 与 order by 的正确理解。

-- group by与order by都可以实现分组功能，order by是按造大小排序，从而将关键字
-- 相同的记录分在一个小组。因此，用order by实现的分组是一种自然分组。
-- group by是与聚合函数相关联的分组操作，是一种强制性聚合分组。
-- 有一个初学者经常出错的地方，例如若要求：
-- 按照课程号C#来分组显示表SC的信息，每组都按从大到小的顺序排列。
-- 那么下面的这个查询就是错误的：

select * from sc group by c# order by grade

-- 结果如下：
-- 列 'sc.s#'、 'sc.seleday'、 'sc.grade'、 在选择列表中无效，因为该列
-- 既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
-- 正确的答案因该是：

select * from sc order by c# asc , grade desc

-- 也就是说，有些看起来好像是要用GROUP BY语句来完成的查询，其实是用ORDER BY
-- 语句来做。用 ORDER BY 语句排序，就自然会将关键字相同的记录放在一起，也就
-- 实现了分组功能。

---3\在没有 group by 的情况下使用having。

-- group by 是可以省略的，尽管语句中含有having子句。条件是：在select子句中，
-- 只含有聚合函数时才可以这么用。这时，整个结果集被当成一个小组进行聚合操作。
-- 例如：返回表SC的行数。若行数大于10，才返回行数值。

select count(*) as 表SC的行数 from sc having count(*) > 10

-- 而下面的这个语句就是错误的：

select s# from sc having count(s#) > 10

---4\distinct在游标中的使用举例。

-- 上机实验经常会遇到一些名次查询，例如：查询平均成绩为第2名的学生学号及其平均成绩
-- 显然，这个查询要涉及到游标机制。不少同学在做该题时直接将指针推进到该游标所对应的
-- 结果集(也就是排过序的平均成绩表)的第二个元组上，而忽略了以下两点：

--(1)第2个元组未必是第2名，因为有可能第2个元组和第1个元组为并列第一名。
--(2)正是因为并列性的是比较普遍的现象，存在并列第2名的可能性也是非常大的。

-- 要想去除并列性，用关键字 distinct 是最有效、最简单的办法。如下例：

declare @var int -- 定义变量 @var。
declare mycur scroll cursor for -- 定义游标 myc。
select distinct avg(grade) as avg_grade
from sc   -- 用distinct 的目的是防止有并列的名次。
group by s#
order by avg_grade desc
open mycur   -- 打开游标。print @@cursor_rows
fetch absolute 2 from mycur into @var -- 推进游标，并将结果存入变量 @var。
if @@fetch_status <> 0 -- 检测推进游标是否成功。
  begin
  print '没有第2名！'
  end
else -- 若推进游标成功，则执行下面的SQL语句。
  begin
  select s# as 平均成绩为第2名的学生学号 , avg(grade) as 平均成绩
  from sc
  group by s#
  having avg(grade) = @var
  end
close mycur   -- 关闭游标。print @@cursor_rows
deallocate mycur -- 释放游标。
go

---5\日期型数据、货币型数据、实数型数据、逻辑型数据的使用。

-- char varchar int tinyint 比较简单。
-- numeric/decimal bit 注意在插入 bit 型数据时，不为0的任何数字都被当作1看待。
-- numeric(x,y)指实数总共有x位，y为小数点后的数字位数。如123.45就是numeric(5,2)
-- datetime money 这两个数据类型的使用要注意格式。例如：

create table test
(
a tinyint , b char(3) , c varchar(5) , d datetime ,
e money , f numeric(4,1) , g bit , h bit ,
primary key(a)
)
insert into test values
(12,'dfg','hjkl','04-23-1993 09:36:54.123',$200.12,500.2,1,1)
insert into test values
(53,'mnb','fghk','2003-11-18 11:20:39.458',$300.30,432.6,0,1)
insert into test values
(26,null,'fghk','2003-11-18 11:20:39.458',$300.30,432.6,0,1)
select * from test

/*
日期型数据，无论是采用“月日年”还是“年月日”，系统都可正确识别出来。
但是“日月年”就不能识别出来了。下面这个语句就是错误的：

insert into test values
(45,'dfg','hjkl','23-04-1993 09:36:54.123',$200.12,500.2,1,1)
*/

-- 顺便说一下对空值的判断不可以用"="/"!="，而因该用"is"/"is not"。例如：

select * from test where b = null -- 错误用法。
select * from test where b is null -- 正确用法。
drop table test
go

---6\通配符 % 和 _ 的使用。

select * from s where s# like 's%2'

---7\select 语句返回行数的限制。

-- 使用TOP n [PERCENT]选项限制返回的数据行数，TOP n说明返回前n行，若有PERCENT时，
-- 说明n表示一百分数，指定返回的行数等于总行数的百分之几。例如：

select top 13 with ties *
from sc -- 关于with ties的用法，请参见SQL SERVER的联机帮助。
order by c#

select top 13 with ties *
from sc
order by c# , s#

select * from sc order by c#

select top 30 percent *
from sc

---8\char(n)的使用。

-- char(十进制数表示的ASCII码)代表了ASCII码所指的字符。
-- char(0x十六进制表示的ASCII码)也代表ASCII码所指的字符。
-- 例如： char(0x20)表示空格符，char(13)表示换行符。

declare @message varchar(50)
select @message = '你好' + '高兴哦' + '！'
print char(0x20) + @message
print char(13) + @message + char(65)
print @message

---9\局部变量的赋值与输出。

-- 第一种赋值方法： set 局部变量 = 和局部变量类型相符合的表达式
-- 第二种赋值方法： select 局部变量 = 和局部变量类型相符合的表达式
-- 用set一次只能给一个变量用赋值，并且可以将查询结果赋给变量。
-- 用select可以同时给多个变量赋值，也可以将查询结果赋给变量。

declare @var1 int , @var2 char(4)
set @var1 = (select count(*) from sc) -- 注意括号不可以省略。
print @var1
select @var1 = count(distinct s#) , @var2 = count(*) from sc
print @var1
print @var2

-- 用select给变量赋值时，若查询结果不止一行，则将最后一行的值赋给变量。
-- 这一点我们可以利用一下。例如：
-- 查询平均成绩第2的学生学号及其平均成绩,若出现并列第2名，则按学号的升序显示。

declare @var1 int , @var2 int
select @var1 = count(*)
from (select distinct avg(grade)
    from sc group by s#)
as t1(avg_grade)
if @var1 >=2   -- 判断是否存在第2名。
  begin     -- top 2 的作用就是返回最前面的那两个元组。
  select distinct top 2 @var2 = avg(grade)
  from sc group by s#   -- 在order by语句的作用下，distinct top 2语句返回
  order by avg(grade) desc   -- 最前面的那两个元组必然是前2名。
        -- 因查询结果不止一行，所以将最后一行(第2个行)的值赋给变量@var2。
  select s# , avg(grade)
  from sc
  group by s#
  having avg(grade) = @var2
  order by s#
  end
else
  print '没有第2名！'
go

-- 可以明显看出，这样做比用游标做题方便多了，但是不建议这么做。

-- 第一种输出方法： print 局部变量
-- 第二种输出方法： select 局部变量
-- 用print一次只能输出一个变量，或多个变量所组成的一个表达式。
-- 用select可以同时输出多个变量/表达式，并且可以给变量/表达式从新起名。

declare @message varchar(50)
select @message = '你好' + '高兴哦' + '！'
print @message
print char(13)
select char(0x20)+ @message as 高兴

---10\group by all 的使用。

---11\computer by 的使用。



/*====================================第5节=====================================*/
/*=============================数据库原理上机题举例=============================*/

/*====================================例题1=====================================*/

/*
要求：不允许使用临时表，第4题必须用游标实现，其它各题均不得使用游标。
有如下员工数据库，存放了四个表：

Employees(EmployeeID, EmployeeName, Street, City)
员工表(员工号，员工姓名，员工居住街道名，员工居住城市名)
要求：员工号为主码，所有字段都不能为空；员工号为整型，其它字段类型自己定义。

Works(EmployeeID, CompanyID, Salary)工作表(员工号，公司号，薪水)
要求：员工号为主码，所有字段都不能为空，薪水值为大于500的一整数；
员工号和公司号分别为参照员工表和公司表主码的外码。

Companies(CompanyID, CompanyName, City)公司表(公司号，公司名字，公司所在城市名)
要求：公司号为主码，所有字段都不能为空；公司号为整型，其它字段类型自己定义。

Managers(EmployeeID, ManagerID)管理表(员工号，员工经理的员工号)
要求：员工号为主码，所有字段都不能为空，员工号、员工经理的员工号都是参照员工表主码的外码。

试用SQL语言完成下列功能(用上述英文表定义，不要用中文)：
*/

-- 1：在数据库中创建如上四个表

create table Employees
(
EmployeeID char(3) primary key ,
EmployeeName varchar(10) not null ,
Street varchar(20) not null ,
City varchar(20) not null ,
)

create table Companies
(
CompanyID char(3) primary key ,
CompanyName varchar(20) not null ,
City varchar(20) not null ,
)

create table Works
(
EmployeeID char(3) ,
CompanyID char(3) ,
Salary int not null ,
primary key(EmployeeID , CompanyID) ,
foreign key(EmployeeID) references Employees(EmployeeID) ,
foreign key(CompanyID) references Companies(CompanyID) ,
check(Salary > 500)
)

create table Managers
(
EmployeeID char(3) ,
ManagerID char(3) not null ,
primary key(EmployeeID) ,
foreign key(EmployeeID) references Employees(EmployeeID) ,
foreign key(ManagerID) references Employees(EmployeeID)
)

-- 2：向表中插入数据

insert into Employees values('e01' , 'Tom' , 'ChangAnJie' , 'BeiJing')
insert into Employees values('e02' , 'Kate' , 'ChangAnJie' , 'BeiJing')
insert into Employees values('e03' , 'Zhao' , 'XuJiaHui' , 'ShangHai')
insert into Employees values('e04' , 'Zhong' , 'ChaoXi' , 'ShangHai')
select * from Employees

insert into Companies values('c01' , 'Soft' , 'BeiJing')
insert into Companies values('c02' , 'Hard' , 'HangZou')
select * from Companies

insert into Works values('e01' , 'c01' , 3000)
insert into Works values('e02' , 'c01' , 4000)
insert into Works values('e03' , 'c02' , 2000)
insert into Works values('e04' , 'c02' , 3500)
select * from Works

insert into Managers values('e01' , 'e02')
insert into Managers values('e02' , 'e04') -- e02 和 e04 不在一个公司，
insert into Managers values('e03' , 'e04') -- 这个表的数据设计的不好！
select * from Managers

-- 3：实现如下查询

-- 给出所有居住城市与工作公司所在城市不相同的员工姓名

select EmployeeName from Employees as x
where City not in (select City from Companies
            where CompanyID in (select CompanyID from Works as y
                          where y.EmployeeID = x.EmployeeID))

-- 给出薪水大于其所居住城市员工平均薪水的员工姓名。

select EmployeeName from Employees as x
where EmployeeID in (select EmployeeID from Works
              where Salary > (select avg(Salary)
                        from Works join Employees
                        on Works.EmployeeID = Employees.EmployeeID
                        where City = x.City))

-- 给出位于“HangZou”的每个公司的公司名和员工工资总额，并按照工资总额降序排列

select CompanyName , sum(salary) as sum_salary
from Companies join Works
on Companies.CompanyID = Works.CompanyID
where City = 'HangZou'
group by Companies.CompanyID , CompanyName
order by sum_salary desc

-- 4：更新数据库：对于居住在“BeiJing”的“soft”公司的所有员工，
-- 1)若 工资 <= 3000 ， 则工资增长20%
-- 2)若 3000 < 工资 <=4000 ， 则工资增长10%

update Works
set Salary = Salary * 1.1
where CompanyID in (select CompanyID from Companies
            where CompanyName = 'soft')
and (Salary > 3000 and Salary <= 4000)
and EmployeeID in (select EmployeeID from Employees
            where City = 'BeiJing')

update Works
set Salary = Salary * 1.2
where CompanyID in (select CompanyID from Companies
            where CompanyName = 'soft')
and salary <= 3000
and EmployeeID in (select EmployeeID from Employees
            where City = 'BeiJing')

select * from Works
go

-- 5：用游标实现如下功能

-- 对于每一经理员工(也就是员工号在管理表的“员工经理的员工号”中出现过的员工)
-- 为其增加薪水，每有一个受其直接管理的员工就为其增加20元薪水。

-- 这个题目有点歧义：
--(1 )若为某经理加薪时，就同时也为该经理手下的每个员工加薪20元？
--(2 )为所有经理加薪，若该经理手下有 X 个员工，就为该经理加薪 20 * X 元？

-- 若按第1个意思，不太好做，可能会涉及到触发器！

-- 若按第2个意思，做法如下：

declare @var1 char(3) , @var2 int
declare Manager_cursor scroll cursor for
select ManagerID , count(*)
from Managers
group by ManagerID
open Manager_cursor
fetch next from Manager_cursor into @var1 , @var2
while(@@FETCH_STATUS = 0)
begin
  update Works
  set Salary = Salary + 20 * @var2
  where EmployeeID = @var1
  fetch next from Manager_cursor into @var1 , @var2
end
close Manager_cursor
deallocate Manager_cursor
go

-- 6：给出“soft”公司所有居住街道和城市与其经理居住街道和城市相同的员工姓名。

select EmployeeName from Employees as a
where EmployeeID in (select EmployeeID from Works
              where CompanyID in (select CompanyID from Companies
                          where CompanyName = 'Soft'))
and EmployeeID in (select EmployeeID from Managers as x
            where exists(select * from Employees as b
                    where EmployeeID = x.EmployeeID
                    and exists (select * from Employees as c
                            where EmployeeID = x.ManagerID
                            and City = b.City)))

/*
insert into Employees values('e06' , 'wang' , 'ChangAnJie' , 'BeiJing')
insert into Works values('e06' , 'c01' , 3200)
insert into Managers values('e06' , 'e02')
delete from Works where EmployeeID = 'e06'
*/
     
drop table Works
drop table Managers
drop table Employees
drop table Companies
go

/*====================================例题2=====================================*/

/*
要求：不允许使用临时表，第5题务必用游标，其他题不得用游标。

学校图书馆有如下的数据库，其中存放了如下表：
u Student(SID, SName, SDept) 学生(学号，学生姓名，所属系号)
u Book(BID, BName, BType, Author, BCount)
图书(图书编号，图书名称，图书类别，作者姓名，总本数)
u Borrow(SID, BID, BTime, RTime) 借阅(学号，图书编号，借书日期，还书日期)。

试用SQL语言完成下列功能(用上述英文表定义，不要用中文)
*/

-- 1. 建表，要求在定义中做如下声明：

-- a) 必要的主码外码
-- b) 学生的姓名和图书名称不能为空
-- c) 还书日期必须迟于借书日期或者为空，数据类型均为datetime。
-- d) 对同一编号的图书，一个学生只能借1本。不同编号的图书，最多只能借3本。

create table Student
(
SID char(5) primary key ,
SName varchar(10) not null ,
SDept char(4) ,
)

create table Book
(
BID char(5) primary key ,
BName varchar(20) not null ,
BType varchar(20) ,
Author varchar(10) ,
BCount int ,
)

create table Borrow
(
SID char(5) ,
BID char(5) ,
BTime datetime ,
RTime datetime ,
primary key(SID , BID , BTime) ,
foreign key(SID) references Student(SID) ,
foreign key(BID) references Book(BID) ,
check((RTime > BTime) or (RTime is null))
)     -- 日期型数据可以比较大小。
go

create trigger borrow_tr on Borrow
for update , insert
as
if exists(select SID , BID from Borrow
      where RTime is null
      group by SID , BID
      having count(*) >= 2)
  begin
  rollback transaction
  select '该学生尚未归还所借阅的图书，不能再次借阅同一编号的图书！'
  end
if exists(select SID from Borrow
      where RTime is null
      group by SID
      having count(SID) > 3)
  begin
  rollback transaction
  select '该学生尚有3本图书未归还，不可以再借阅任何图书！'
  end
go


-- 2. 往表中插入数据(注意：给定数据中的日期插入时必须转换为合适的格式)

insert into Student values('S0001' , '张三' , 'D001')
insert into Student values('S0002' , '李平' , 'D001')
insert into Student values('S0003' , '王玲' , 'D002')
insert into Student values('S0004' , '李四' , 'D002')
select * from Student

insert into Book values('B0001','概率统计' , '计算机','张立昂',2)
insert into Book values('B0002','随机过程导论' , '数学','Edward',2)
insert into Book values('B0003','数据库系统概论' , '计算机','杨冬青',2)
insert into Book values('B0004','大学语文','语文' , '教学组',2)
select * from Book

insert into Borrow values('S0001','B0001','2005-11-28','2006-01-30')
insert into Borrow values('S0001','B0002','2006-02-02','2006-02-18')
insert into Borrow values('S0001','B0004','2005-12-07',null)
insert into Borrow values('S0002','B0001','2005-12-23','2006-02-01')
insert into Borrow values('S0002','B0002','2005-11-01','2006-01-04')
insert into Borrow values('S0003','B0003','2005-12-21',null)
insert into Borrow values('S0004','B0004','2006-01-16',null)
select * from Borrow
go

-- 试试触发器的功能：
/*
insert into Borrow values('S0004','B0004', '2004-11-26' ,null)
go
insert into Borrow values('S0004','B0002','2004-11-26',null)
insert into Borrow values('S0004','B0001','2004-11-26',null)
insert into Borrow values('S0004','B0003','2004-11-26',null)
go
-- delete from Borrow
*/


-- 3. 用SQL语句完成如下查询

-- a) 找出借书日期超过30天的所有学生的姓名。

select SName from Student
where SID in (select SID from Borrow
        where datediff(day , BTime ,RTime) > 30
        or (datediff(day ,BTime ,getdate()) > 30 and RTime is null))

-- b1)找出至少借阅过一本张三所借阅过的图书的学生姓名和所属系(不包括张三自己)。

select SName , SDept from Student as x
where SName <> '张三'
and exists(select count(distinct BID) from Borrow
      where SID = x.SID
      and BID in (select BID from Borrow
              where SID = (select SID from Student
                        where SName = '张三'))
      having count(distinct BID) >= 1)

-- b2)找出至少借阅过两本张三所借阅过的图书的学生姓名和所属系(不包括张三自己)。

select SName , SDept from Student as x
where SName <> '张三'
and exists(select count(distinct BID) from Borrow
      where SID = x.SID
      and BID in (select BID from Borrow
              where SID = (select SID from Student
                        where SName = '张三'))
      having count(distinct BID) >= 2)

-- c) 找出借书最多的学生及其所借书的本数。

select SID , count(*) from Borrow
group by SID
having count(*) >= all(select count(*) from Borrow
              group by SID)

-- 4. 今天是2006年2月26日，王玲归还了所借的全部图书，请在数据库中更新相应记录。

update Book
set BCount = BCount + 1
where BID in (select BID from Borrow
        where RTime is null
        and SID = (select SID from Student
                where SName = '王玲'))

update Borrow
set RTime = '2006-02-26'
where RTime is null
and SID = (select SID from Student
      where SName = '王玲')
go

-- 5. 使用游标，定位王玲同学的第二次借阅记录，列出借阅的图书。

-- insert into Borrow values('S0003','B0002','2005-12-18',null)
declare @bid char(5) , @btime datetime
declare Borrow_cur scroll cursor for
select BID , BTime from Borrow
where SID = (select SID from Student
        where SName = '王玲')
order by BTime asc

open Borrow_cur
fetch absolute 2 from Borrow_cur into @bid , @btime

if @@fetch_status = 0
  begin
  select Borrow.* , BName from Borrow , Book
  where Borrow.BID = Book.BID
  and Borrow.BID = @bid
  and Borrow.BTime = @btime
  and SID = (select SID from Student
        where SName = '王玲')
  end
else
  begin
  select '王玲同学在目前还没有第二次借阅记录！'
  end
close Borrow_cur
deallocate Borrow_cur
go


-- 6. 给出被借阅次数排名第一的书名。

select BID from Borrow
group by BID
having count(*) >= all(select count(*) from Borrow
              group by BID)
go

-- 7.创建一个视图，给出学号，姓名，罚款(超过60天没有还书，每天罚款0.1元)。

create view Forfeit (SID , SName ,BName , Monies)
as
select Student.SID , SName ,BName , 0.1 * datediff(day , BTime , getdate())
from Book ,Borrow , Student
where Book.BID = Borrow.BID
and Borrow.SID = Student.SID
and RTime is null
-- drop view Forfeit
go

select * from Forfeit

-- 8.删除所定义的3个表。

drop table Borrow
drop table Student
drop table Book
go

/*====================================例题3=====================================*/

/*
商品交易系统：
Member (MID, Gender, Location) 成员(成员标识，性别，所在地)
Product (PID, Brand, Type, Price, SID) 商品(商品编号，品牌，型号，价格，卖家)
Record (RID, PID, Amount, BID, SumPrice)
交易记录(记录编号，商品编号，数量，买家，总金额)

User表中保存在该系统中注册的所有成员，Product表中保存当前可供交易的商品。每个成
员可以购买Product表中已有的商品，也可以把自己想要卖出的商品登记到Product表中。注意
可能有多于一个成员卖同一品牌同一型号的商品。Record表中保存所有已经完成的交易的记录。

试用SQL语句完成下列功能：(使用英文表名和属性名)
*/

-- 1. 在数据库中建立上述三个表，写明主码和外码。

create table Member
(
MID varchar(10) primary key ,
Gender char(1) ,
Location varchar(20) ,
check ((Gender = 'M') or (Gender = 'F'))
)

create table Product
(
PID char(4) primary key ,
Brand varchar(10) ,
Type varchar(10) ,
Price int ,
SID varchar(10) foreign key references Member(MID)
)

create table Record
(
RID char(4) primary key ,
PID char(4) foreign key references Product(PID) ,
Amount int ,
BID varchar(10) foreign key references Member(MID) ,
SumPrice int ,
check(RID like 'A%')
)

-- 2. 在三个表中插入如下数据。

insert into Member values( '家明', 'M', '北京')
insert into Member values('Sam', 'M', '北京')
insert into Member values('天蓝', 'F', '深圳')
insert into Member values('玫瑰', 'F', '上海')
insert into Member values('Julie', 'F', '上海')
select * from Member

insert into Product values('1000', 'Nokia', '7610', 2890, '家明')
insert into Product values('1001', 'Siemens', 'M65', 1400, '家明')
insert into Product values('1002', 'Nokia', '6230', 2180, '天蓝')
insert into Product values('1003', 'SE', 'K700', 1900, '天蓝')
insert into Product values('1004', 'Nokia', '7610', 3180, '天蓝')
select * from Product

insert into Record values('A001', '1001', 1, 'Sam', 0)
insert into Record values('A002', '1002', 1, '家明',0)
insert into Record values('A003', '1003', 3, 'Julie',0)
insert into Record values('A004', '1000', 2, '玫瑰',0)
insert into Record values('A005', '1002', 1, 'Sam', 0)
select * from Record

-- 3.完成如下查询。

-- 1)列出所有女性成员的购买记录，需给出记录编号、成员标识、
-- 商品的品牌、型号和购买数量。

select RID , BID , Brand , Type , Amount
from Record , Product
where Record.PID = Product.PID
and BID in (select MID from Member
            where Gender = 'F')


-- 2)列出所有异地购买的记录，即买家所在地和卖家所在地不同的交易记录，需给出
-- 记录编号、买家的成员标识、买家所在地、卖家的成员标识和卖家所在地。

/*
select RID , PID , BID , Location
into #t1
from Record , Member
where BID = MID
-- trop table #t1
select PID , SID , Location
into #t2
from Product , Member
where SID = MID
-- trop table #t2
select RID , BID , #t1.Location , SID , #t2.Location
from #t1 , #t2
where #t1.PID = #t2.PID and #t1.Location <> #t2.Location

-- 利用导出表将这3个查询合并为一个查询：
*/

select RID , BID , x.Location , SID , y.Location
from (select RID , PID , BID , Location
    from Record , Member
    where BID = MID) -- 买家信息导出表：x(RID , PID , BID , Location)
as x(RID , PID , BID , Location) ,
  (select PID , SID , Location
    from Product , Member
    where SID = MID) -- 卖家信息导出表：y(PID , SID , Location)
as y(PID , SID , Location)
where x.PID = y.PID and x.Location <> y.Location

-- 也可以不用导出表做：

select RID , BID , x.Location , SID , y.Location
from Member as x , Member as y , Record , Product
where BID = x.MID
and SID = y.MID
and x.Location <> y.Location
and Record.PID = Product.PID

-- 3)列出所有有卖出记录的商品，按卖出的总数量从多到少排序，需给出
-- 商品编号和卖出的总数量。

select PID , sum(Amount) as SumAcount
from Record
group by PID
order by sum(Amount) desc

-- 4.计算每笔交易记录的总金额，更新Record表的SumPrice属性。
-- 注：总金额 = 所购商品的数量 * 价格。

update Record
set SumPrice = Amount * (select Price from Product
                where PID = Record.PID)
select * from Record
go

-- 5.用游标完成如下的价格调整：如果某一品牌某种型号的商品平均价格高于2000，
-- 则将该品牌该型号的商品价格都调整为原价的90%，结果保留整数。
-- 例：Nokia 7610家明的售价为2890，天蓝的售价为3180，平均价格为3035。
-- 则家明和天蓝的售价都应调整为原价的90%。

declare @barnd varchar(10) , @type varchar(10)
declare prod_cur insensitive cursor for
select Brand , Type -- 在这里，insensitive是不可以缺少的，因为在游标打开后，
from Product     -- 基本表Product的Price列将被更新。如果不指定insensitive，
group by Brand , Type -- 那么游标的结果集可能会随着基本表的改变而改变。
having avg(Price) > 2000
open prod_cur
fetch next from prod_cur into @barnd , @type
while @@fetch_status = 0
begin
  update Product
  set Price = Price * 0.9
  where Brand = @barnd and Type = @type
  fetch next from prod_cur into @barnd , @type
end
close prod_cur
deallocate prod_cur
select * from Product
go

-- 6.找出在该电子商务社区中销售总量最高的品牌，需给出品牌和销售总量。
-- 注：品牌的销售总量为该品牌所有交易的商品数量之和。

select Brand , sum(Amount) as SumAmount
from Product , Record
where Product.PID = Record.PID
group by Brand
having sum(Amount) >= all(select sum(Amount)
                from Product , Record
                where Product.PID = Record.PID
                group by Brand)

-- 7.删除所定义的3个表。

drop table Record
drop table Product
drop table Member
go

/*====================================例题4=====================================*/

/*
赛马数据库,要建立的表：

会员（会员编号，会员姓名，所在地，会费余额)
赛马（赛马编号，赛马颜色，赛马产地)
比赛记录（场次，获胜赛马编号)
投注记录（场次，会员编号，所选赛马编号)

每场比赛会员可投注一匹马，投注花2元，获胜可得到10元。
*/

-- 1.建表，要定义以下声名：
-- a)必要的主，外码
-- b)会员姓名，所在地，赛马颜色，产地不能为空。
-- c)会费余额不能小于0。

create table memb
(
mid char(4) primary key ,
mname varchar(10) not null ,
maddr varchar(10) not null ,
monies int ,
check(monies >= 0)
)

create table horse
(
hid char(4) primary key ,
colar char(2) not null ,
haddr varchar(10) not null
)

create table race
(
rid varchar(2) primary key ,
winhid char(4) foreign key references horse(hid)
)

create table wager
(
rid varchar(2) ,
mid char(4) ,
waghid char(4) ,
primary key(rid , mid) ,
foreign key(rid) references race(rid) ,
foreign key(mid) references memb(mid) ,
foreign key(waghid) references horse(hid) ,
)

-- 2.插入数据：

insert into memb values( 'U001','李三','北京',20)
insert into memb values('U002','李斯','北京',1)
insert into memb values('U003','韩家','西安',15)
insert into memb values('U004','王五','上海',8)
select * from memb

insert into horse values('H001','红','法国')
insert into horse values('H002','白','中国')
insert into horse values('H003','黑','印度')
insert into horse values('H004','灰','法国')
select * from horse

insert into race values('1','H001')
insert into race values('2','H001')
insert into race values('3','H002')
insert into race values ('4','H004')
select * from race

insert into wager values('1','U001','H002')
insert into wager values('1','U002','H004')
insert into wager values('1','U003','H001')
insert into wager values('2','U002','H001')
insert into wager values('2','U003','H001')
insert into wager values('3','U001','H004')
insert into wager values('3','U002','H001')
insert into wager values('3','U004','H002')
insert into wager values('4','U001','H001')
select * from wager

-- 3.完成如下查询。

-- a)找出所在地在北京的，会员余额大于10的会员。

select * from memb
where maddr = '北京' and monies > 10

-- b)找出赛马中获胜次数最多的产地及其获胜次数。

select haddr , count(*) as 获胜次数
from horse , race
where horse.hid = race.winhid
group by haddr
having count(*) >= all(select count(*) from horse , race
              where horse.hid = race.winhid
              group by haddr)

-- c)找出投注次数最多的会员的编号，姓名，投注次数。

select * from memb
where mid in (select mid from wager
        group by mid
        having count(*) >= all(select count(*)
                        from wager group by mid))

select memb.mid , mname , count(*) as 投注次数
from memb , wager
where memb.mid = wager.mid
group by memb.mid , mname
having count(*) >= all(select count(*)
              from wager group by mid)
go

-- 4.会员韩家，在第四场中给h004的马投注了，请更新数据库。

declare @A char(4)
select @A = mid from memb
where mname = '韩家'
insert into wager values('4' , @A , 'H004')
update memb
set monies = monies - 2
where mid = @A
select * from wager
select * from memb
go

-- 5.使用游标确定赚钱最多的用户(只考虑获得的奖励)的会员编号、姓名、奖励金额。

declare @mid char(4) , @sum_monies int ,
@temp int , @mname varchar(10)

declare mycur cursor for
select mid , count(*) * 10 as sum_monies
from race , wager
where race.rid = wager.rid
and winhid = waghid
group by mid
order by sum_monies desc

set @temp = 0
open mycur
fetch next from mycur into @mid , @sum_monies
while @@fetch_status = 0
begin
  if @sum_monies < @temp
    break
  else
    begin
    select @mname = mname
    from memb
    where mid = @mid
    select @mid as 会员编号 , @mname as 姓名 , @sum_monies as 奖励金额
    set @temp = @sum_monies
    fetch next from mycur into @mid , @sum_monies
    end
end
close mycur
deallocate mycur
go

-- 6.算出马场主在所有的比赛中赚了多少钱。

select count(*) * 2 - (select count(*) * 10
              from race , wager
              where race.rid = wager.rid
              and winhid = waghid) as 马场主所赚金额
from race , wager
where race.rid = wager.rid
go

drop table wager
drop table race
drop table horse
drop table memb
go[/sell]