示例1
实验一
实验名称：SQL语句的应用（6课时） 一、实验目的
通过使用SQL SERVER企业管理器建立表。模式为人事表、客户表、销售表、销售明细表、产品表。理解数据库模式的概念，理解主键约束、外键约束、UNIQUE约束和CHECK约束。通过SQL SERVER企业管理器建立表间的约束。将得到的表生成脚本，保存。 二、实验环境
是MS SQL SERVER 2000的中文客户端。 三、实验示例 create table employee1(
emp_no char(5) not null,
constraint emp_nochk check(emp_no like '[E-F][0-9][0-9][0-9][0-9]'), emp_name char(10) not null, emp_sex char(1) not null,
constraint emp_sexchk check(emp_sex in ('m','f')), emp_phone char (13) not null,
constraint emp_phonechk check(emp_phone like '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), emp_add varchar(60) not null, emp_salary char(5) not null,
constraint emp_salarychk check (emp_salary between 60000 and 80000) )
go
四、实验内容与步骤
1、建立五张表，每张表至少需要20条记录。
（1）/*员工人事表employee */ emp_no emp_name sex dept title date_hired birthday salary addr
（2）/*客户表customer */ cust_id cust_name addr
char(5) char(10) char(1) char(4) char(6) datetime datetime int char(50) Not null Not null Not null Not null Not null Not null Null Not null null primary key 员工编号 员工姓名 性别 所属部门 职称 到职日 生日 薪水 住址 char(5) char(20) char(40) Not null Not null, Not null, primary key 客户号 客户名称 客户住址
tel_no zip
char(10) char(6) Not null, null 客户电话 邮政编码 （3）/*销售主表sales */ order_no cust_id sale_id tot_amt order_date ship_date invoice_no
（4）/*销货明细表sale_item */ order_no prod_id qty unit_price order_date
（5）/*产品名称表product */ pro_id prod_name char(5) char(20) Not null Not null primary key 产品编号 产品名称 int char(5) int numeric(7,2) datetime Not null, Not null, Not null Not null null primary key 订单编号 产品编号 销售数量 单价 订单日期 int char(5) char(5) numeric(9,2) datetime datetime char(10) Not null Not null, Not null, Not null, Not null, Not null, Not null primary key 订单编号 客户号 业务员编号 订单金额 订货日期 出货日期 发票号码 2、建立表的同时创建表的约束。 （1） 为每张表建立主键约束。 （2） 通过拖放操作加入外键。
（3） 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为
M/F。
（4） 为销售主表sales中的发票编号字段建立UNIQUE约束。
3、通过快捷菜单得到脚本。 五、实验报告
实验二
实验名称：使用SELECT、UPDATE/INSERT/DELETE语句。（2课时） 一、实验目的
理解SELECT语句的操作和基本使用方法，熟练使用UPDATE/INSERT/DELETE语句进行表操作。
二、实验环境
三、实验示例
1、查找出职称为经理和职称为职员的女员工的姓名、职称、性别。 select emp_name,title,sex from employee
where title='经理'or title='职员'and sex='F'
2、选取销售数量最多的前5条订单订单号、数量。
select top 5 with ties order_no,sup_id,qty from sale_item order by qty desc
3、计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。 select prod_id,qty,unit_price,(qty*unit_price)tot_amt from sale_item order by tot_amt desc 四、实验内容与步骤
1、 查找所有经理的姓名、职称、薪水。
select emp_name,title,salary from employee where title='经理'; 2、 查找出姓“王”并且姓名的最后一个字为“功”的员工。
select * from employee where emp_name like '王_功' 3、 添加一条记录至employee表（用INSERT…..INTO）。
insert into employee values('E1014','a14','m','办公','经理','2012-01-20','1999-12-23',60000,'北京'); 4、 将每个员工的薪水上调3%。
update employee set salary =salary*(1+0.03);
5、 查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。
select emp_name,dept,title,addr from employee where sex='f' and
(addr='上海' or addr='北京');
6、 在表sales中挑出销售金额大于等于10000元订单。 select * from sales where tot_amt>=10000;
7、 选取订单金额最高的前10%的订单数据。
select top 10 percent * from sales order by tot_amt desc;
8、 查找出职称为经理或职称为职员的女员工的信息。
select * from employee where title='经理' or title='职员' and sex='f'; 9、 删除sales表中作废的订单（其发票号码为I000000004），其订货明细数据也一并删除。 delete from sales where invoice_no='I000000004'; sales 表和sale_item 要建立关联，删除原则设为“层叠” 10、计算出一共销售了几种产品。
select count(distinct prod_id ) from sale_item ;
11、显示sale_item表中每种个别产品的订购销售金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。 select prod_id,sum(qty*unit_price) as 销售金额from sale_item group by
prod_id order by sum(qty*unit_price) desc ;
12、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。 select month(order_date) as 月份,prod_id,sum(qty*unit_price) as 销
售金额from sale_item group by prod_id ,month(order_date);
五、实验报告
实验三
实验名称：表连接JOIN（2课时）
一、实验目的
理解JOIN语句的操作和基本使用方法，掌握内连接、外连接、自身连接的概念和使用。 二、实验环境
三、实验示例
1、 检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、
单价。
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/ on (a.prod_id=b.pro_id) and a.qty>2 order by a.prod_id
2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。 select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addr from employee as a inner join employee as b
on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr) 四、实验内容与步骤
1、 查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。 select
a.emp_name,a.sex,a.title,a.dept,a.salary,a.addr,b.emp_name,b.sex,b.title,b.dept,b.salary,b.addr
from employee as a join employee as b
on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.dept=b.dept) and (a.addr=b.addr) and a.sex = 'm' and b.sex = 'm';
2、 检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。 select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a join
sale_item as b on a.prod_id=b.prod_id order by prod_id ;
3、 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、
数量、单价。
select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a join sale_item as b on a.prod_id=b.prod_id and b.unit_price>2400 ;
4、 分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价
高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。
select a.prod_id,a.prod_name,b.qty,b.unit_price from product as a left join sale_item as b on a.prod_id=b.prod_id and b.unit_price>2400 ;
inner join left join right join full join 五、实验报告
实验四
实验名称：SELECT的嵌套使用，实现复杂查询（2课时）
一、实验目的
掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。
二、实验环境
三、实验示例
1、由employee表中查找出薪水最高的员工信息。 select *
from employee
where salary=
(select max(salary )
from employee ) 2、由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接每一张订单的金额”的所有订单。
select * from sales where tot_amt >all (select tot_amt from sales
where sale_id='E0107' and order_date='2012-01-02') order by tot_amt
3、利用begin...end语句实现数据查询，查询出employee表中所有女职工的平均工资和 住址在＂上海市＂的所有女职工的平均工资
begin
select avg(salary) from employee where sex like 'f' union
select avg(salary) from employee
where sex like 'f' and addr like '上海' end
四、实验内容与步骤
1、 由sales表中查找出销售金额最高的订单。
select * from sales where tot_amt=
(select max(tot_amt ) from sales )
2、 由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接任一张订单的金
额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。 select sale_id,tot_amt from sales where tot_amt >all
(select tot_amt from sales
where sale_id='E0107' and order_date='2012-01-02') order by tot_amt
3、 找出公司女业务员所接的订单。 select *
from sales where sale_id in (select emp_no
from employee where sex='f')
select * from sales a join employee b on a.sale_id=b.emp_no and b.sex='f' order by sale_id
select a.order_no,a.cust_id,a.sale_id,a.tot_amt from sales a,employee b where a.sale_id=b.emp_no and b.sex='f'
4、 找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。
select * from employee where emp_name in
(select emp_name from employee group by emp_name having count(emp_name)>1
)order by emp_name,emp_no;
select * from employee as a where exists
(select * from employee b where a.emp_no!=b.emp_no and a.emp_name=b.emp_name ) order by emp_name,emp_no
5、 找出目前业绩未超过200000元的员工。
select * from employee where emp_no in
(select sale_id from sales where tot_amt<50000)
6、 计算公司内各个部门的工资支出总和。
select dept,sum(salary) from employee group by dept
7、 计算每一产品销售数量总和与平均销售单价。
select prod_id,sum(qty),avg(unit_price)from sale_item group by
prod_id
五、实验报告
实验五
实验名称：视图的建立与使用（2课时） 一、实验目的
掌握视图的定义与工作原理。 二、实验环境
三、实验示例 1、
create view emp_view as
select emp_no,emp_name,salary from employee where sex='f' 2、
create view sale_item_view as
select sales.order_no,sales.order_date,prod_id,qty from sales,sale_item
where sales.order_no=sale_item.order_no
/*注意：插入数据时要考虑数据列是否有默认值或允许为空，否则INSERT失败。*/ insert into sale_item_view(order_no,order_date) values(10010,'1996/12/20')
insert into sale_item_view(prod_id,qty) values('p0010',8)
四、实验内容与步骤
1、 创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。 2、 对视图添加一条记录数据。（注意：分别查看customer表和该视图的结果。）
3、 删除视图中所有姓“王”的客户数据。 4、 通过视图修改表内某一客户的姓名。
5、 有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、
订单号、销售总金额。 6、 将上述视图中订单号为10001的记录的销售总金额改为60000。 7、 给上述视图添加一条记录数据。 8、 删除上述视图。 五、实验报告
实验六
实验名称：存储过程建立与调用（含带参存储过程的建立与调用）（2课时） 一、实验目的
理解存储过程的概念、建立和调用方法。 二、实验环境
三、实验示例 1、模糊查询
create procedure sp_empname @E_name varchar(10) as select a.emp_name,a.dept,b.tot_amt from employee a inner join sales b on a.emp_no=b.sale_id
where a.emp_name like @E_name go
exec sp_empname '陈%'
2、 利用存储过程计算出’E0014’业务员的销售总金额。 create procedure sp_saletot @E_no char(5),@p_tot int output as select @p_tot=sum(tot_amt) from sales
where sale_id=@E_no go
declare @tot_amt int
exec sp_saletot E0014, @tot_amt output
select @tot_amt
四、实验内容与步骤
1、 利用存储过程，给employee表添加一条业务部门员工的信息。
2、 利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓
名、销售金额。 3、 利用存储过程查找“刘刚”的员工编号、订单编号、销售金额。
4、 利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
5、 利用存储过程计算出订单编号为10003的订单的销售金额。 五、实验报告
实验七
实验名称：触发器的建立与使用（2课时）
一、实验目的
理解触发器的概念、定义方法和触发条件。 二、实验环境
采用Client/Server模式，学生为客户端，是MS SQL SERVER 2000的中文客户端。登录用户名是：学号；密码为：****** 。用户名和密码以任课老师给出为准。 三、实验示例
(一)After类型触发器
1.创建AFTER-INSERT触发器：在sale_item表上创建dml触发器inssale_item_trg,
当向sale_item插入一条记录时，--检查prod_id 是否在product中，order是否在sales表中，只要一项不存在，则给出错误提示，并撤销插入操作。
USE company GO
CREATE TRIGGER inssale_item_trg ON sale_item FOR INSERT
AS /* 这触发器定义要执行的操作*/ IF EXISTS(SELECT * FROM sale_item
WHERE (sale_item.prod_id NOT IN(SELECT prod_id FROM product) OR sale_item.order_no NOT IN(SELECT order_no FROM sales))) BEGIN
RAISERROR('插入的数据违背了数据的一致性',16,1) /* 错误提示*/ ROLLBACK TRANSACTION /* 事务回滚*/ END; GO */
insert into sale_item(order_no,prod_id) values('24','21')
2.创建AFTER-DELETE触发器：在表sales上创建触发器delsales_trg 当从sales表中删除一条记录时，检查sale_item中是否存在被删记录的order_no,如果存在，则从sale_item表中删除该记录。
/*
USE company GO
CREATE TRIGGER delsales_trg ON sales FOR DELETE
AS /* 为触发器定义要执行的操作*/
DELETE sale_item WHERE sale_item.order_no IN (SELECT order_no FROM deleted) GO */
delete from sales where order_no='24'
写一个允许用户一次只删除一条记录的触发器。
/*
create trigger tr_emp on employee for delete as declare @row_cnt int
select @row_cnt=count(*) from deleted if @row_cnt>1 begin
print '此删除操作可能回删除多条人事表数据!!!' rollback transaction
end */
delete from employee
where sex='m' /*结果：所影响的行数为*/
3 创建AFTER-UPDATE触发器，在product表上创建触发器updproduct_trg,当对表product的记录进行修改时，触发器将给出修改时间信息。 USE company GO
CREATE TRIGGER updproduct_trg
ON product FOR UPDATE
AS /* 为触发器定义要执行的操作 */
PRINT '修改的时间为:'+CONVERT(char, getdate(),110) /* 显示修改的时间信息 */ GO
update product set prod_name='sql' where prod_id='20'
（二） Instead of类型触发器
1替代触发器能够使无法更新的视图支持更新功能。基于多表的视图必须使用Instead of触发器来支持基表数据的增、删、改操作。 建立视图：
Create View View_product_saleitem as
SELECT dbo.product.prod_id, dbo.product.prod_name, dbo.sale_item.order_no, dbo.sale_item.qty, dbo.sale_item.unit_price, dbo.sale_item.order_date FROM dbo.product INNER JOIN
dbo.sale_item ON dbo.product.prod_id = dbo.sale_item.prod_id
USE company GO
CREATE TRIGGER InsViewprod_saleitem_Trg ON View_prod_saleitem INSTEAD OF INSERT AS
BEGIN /* 定义触发器的行为*/
DECLARE @prod_id char(5),@prod_name char(20) /* 定义局部变量*/
DECLARE @order_no int,@qty int,@unit_price numeric(7,2) ,@order_date datetime
/* 从逻辑表inserted中选择相应的数据存放到变量中*/ SELECT
@prod_id=prod_id,@prod_name=prod_name,@order_no=order_no,@qty=qty,@unit_price=unit_price,@order_date=order_date FROM inserted
/* 将相应变量值插入到两个基表中*/ INSERT INTO product (prod_id,prod_name)
VALUES(@prod_id,@prod_name) INSERT INTO sale_item
(order_no,qty,unit_price,order_date)
VALUES (@order_no,@qty,@unit_price,@order_date) END; GO */
insert
into
View_prod_saleitem
values('26','javaee',50,100,23.5,'2012-12-25')
实验八 事务及加锁
脏读，丢失更新，不可重复读，幻像度
1．脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据
是脏数据，依据脏数据所做的操作可能是不正确的。 2．丢失更新:当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将
重写由其它事务所做的更新，这将导致数据丢失。 3．不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重
复读。 4．幻象读：指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻
觉一样。
1.3 锁的分类
按数据库引擎使用的资源锁模式。
锁模式 共享 (S) 更新 (U) 排他 (X) 意向 说明 用于不更改或不更新数据的读取操作，如 SELECT 语句。 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。 用于建立锁的层次结构。意向锁的类型有：意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。 在执行依赖于表架构的操作时使用。架构锁的类型有：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。 架构 大容量更新 (BU) 键范围 在向表进行大容量数据复制且指定了 TABLOCK 提示时使用 当使用可序列化事务隔离级别时保护查询读取的行的范围。确保再次运行查询时其它事务无法插入符合可序列化事务的查询的行
从程序员的角度，分为悲观锁和乐观锁 乐观锁：完全依靠数据库来管理锁的工作。 悲观锁：程序员自己管理数据或对象上的锁处理。
从粒度来分： 表类型：锁定整个表 行类型：锁定某个行
文件类型：锁定某个数据库文件 数据库类型：锁定整个数据库 页类型：锁定8K为单位的数据库页
事务4大特性性：
1 原子性(Atomicity):事务是一个完整的操作。
2 一致性（Consistency）：当事务完成时，数据必须处于一致状态。 3 隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。 4 持久性(Durability):事务完成后，它对于系统的影响是永久性的。
一、程序中的事务的类型 1）显式事务
显示事务是手工配置的事务。用保留字标识显式事务的开始和结束。保留字包括 BEGIN TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION和SAVE TRANSACTION，其中TRANSACTION可以简写为TRAN。
要开始显式事务，首先输入关键词BEGIN TRAN。 要结束显示事务，使用COMMIT TRAN。
如果要取消事务，使用ROLL BACK TRAN命令，ROLLBACK TRAN将完全取消事务，或将事务恢复到初始状态。
2）隐式事务
有时候看起来没有使用事务，但它们可能隐藏在幕后，这种事务叫做隐式事务。 要使用这种模式，必须使用Set Implicit_Transactions On语句来设置连接。SQLServer的任何数据修改语句，都是隐式事务。下列的任何一条语句都可以作为一个隐式事务的开始：ALTER TABLE、CREATE、 DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REVOKE、SELECT、TRUNCATE TABLE、UPDATE。
要结束隐式事务，必须使用COMMIT TRANSACTION或ROLLBACK TRANSACTION语句。之后，任何指令的命令都将会开始另一个事务。 二、实验准备
设计一个用于实验的简单表： 设table1(A,B,C)
A B C a1 b1 c1 a2 b2 c2 a3 b3 c3
事务实验
select * from table1
begin tran
update table1 set a='ab' select * from table1
if not exists(select * from table1 where b='b5') begin
print '找不到该记录，回滚事务' rollback tran end else
begin
print '找到记录，提交事务' commit tran end
select * from table1 go
三、验证脏读 事务1
select *from table1 begin tran
update table1 set a='a' select * from table1 waitfor delay '00:00:10' rollback tran
select * from table1 go 事务二
set transaction isolation level read uncommitted print '脏读'
select * from table1 if @@rowcount>0 begin
waitfor delay '00:00:10' print '不重复读' select * from table1
end go
验证：将隔离级别设为set transaction isolation level read committed看看，脏数据不会读出 四、不可重复读 事务1
set transaction isolation level read committed begin tran
print '初始'
select *from table1 waitfor delay'00:00:10' print '不可重复读'
select * from table1 rollback tran 事务2：
set transaction isolation level read committed update table1 set c='c'
验证：将隔离级别设为 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ看
看，这个时候，此连接将锁住不能执行，一直等到A连接结束为止。而且A连接中两次读取到的数据相同，不受B连接干扰。
注，对于Read Committed和Read UnCommitted情况下，B连接不会锁住，等到A连接执行完以后，两条查询语句结果不同。 五、序列化读：SERIALIZABLE 事务1、在A连接中执行。
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN t UPDATE table1 SET a='a' WAITFOR delay '00:00:20'
Select * from table1 COMMIT TRAN t
事务2. 在B连接中执行，并且要在A执行后的20秒内。
BEGIN TRAN tt
INSERT INTO table1 values('a4','b4','c4') Select * from table1 COMMIT TRAN tt
在A连接的事务提交之前，B连接无法插入数据到表中，这就避免了幻觉读。 注：幻觉读是指当事务不是独立执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。 五、设置隔离级别
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ
| SERIALIZABLE }
若要确定当前设置的事务隔离级别，请使用 DBCC USEROPTIONS 语句
提交读（SQL Server 默认级别）。
SQL Server 2005 还支持使用行版本控制的两个事务隔离级别。一个是已提交读隔离的新实现，另一个是新事务隔离级别（快照）。
将 READ_COMMITED_SNAPSHOT 数据库选项设置为 ON 时，已提交读隔离使用行版本控制提供语句级别的读取一致性。读取操作只需要 SCH-S 表级别的锁，不需要页锁或行锁。将 READ_COMMITED_SNAPSHOT 数据库选项设置为 OFF（默认设置）时，已提交读隔离的行为与在 SQL Server 的早期版本中相同。两个实现都满足已提交读隔离的 ANSI 定义。
快照隔离级别使用行版本控制来提供事务级别的读取一致性。读取操作不获取页锁或行锁，只获取 SCH-S 表锁。读取其它事务修改的行时，读取操作将检索启动事务时存在的行的版本。将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，将启用快照隔离。默认情况下，用户数据库的此选项设置为 OFF。 隔离级别 脏读 不可重复读取 未提交读
幻像 是 是 是
提交 可重复读 快照 可串行读 否 否 否 否 是 否 否 否 是 是 否 否 六、加锁实验 锁定提示对SQL语句进行特别指定，这个指定将覆盖事务的隔离级别。事务隔离级别是并发控制的整体解决方案，其实际上是综合利用各种类型的锁和行版本控制，来解决并发问题。
对用户来说，只有当事务隔离级别无法解决一些并发问题和需求时，才有必要在语句中手动设置锁。不适当的设置锁，可能会导致严重的阻塞和死锁。建议，只有在完全了解锁机制的情况下，才可以在语句中手动设置锁，否则应该使用事务隔离级别。 1）排它锁
新建两个连接
在第一个连接中执行以下语句 begin tran
update table1 set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒 commit tran
在第二个连接中执行以下语句 begin tran
select * from table1
where B='b2' commit tran
若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒
2）共享锁
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁 where B='b2'
waitfor delay '00:00:30' --等待30秒 commit tran
在第二个连接中执行以下语句 begin tran
select A,C from table1 where B='b2' update table1 set A='aa'
where B='b2'
commit tran
若同时执行上述两个语句，则第二个连接中的select查询可以执行 而update必须等待第一个连接中的共享锁结束后才能执行 即要等待30秒
3）死锁 增设table2(D,E)
D E d1 e1 d2 e2
在第一个连接中执行以下语句
begin tran
update table1 set A='aa' where B='b2'
waitfor delay '00:00:30' update table2 set D='d5'
where E='e1' commit tran
在第二个连接中执行以下语句 begin tran
update table2 set D='d5' where E='e1'
waitfor delay '00:00:10' update table1 set A='aa'
where B='b2' commit tran
同时执行，系统会检测出死锁，并中止进程 检测数据库中是否存在锁
select spid,blocked,status,waittime,waittype,waitresource,db_name(dbid),cmd,hostname,loginame from master.dbo.sysprocesses where blocked<>0
示例2
数据库说明：
这是一个大学入学注册管理系统，包含五张表 classes开课 Department Course Description max_students current_students Num_credits room_id
开课系别 开课课程 课程名称 课程容量 现有学生数量 学分 教室
Major_stats专业统计 Major Total_credits Total_students
Registered_students学生选修 Student_id Department Course Grade
Rooms教室 Room_id Building Room_number Number_seats Description
Students学生 Id First_name Last_name Major Current_credits students表描述学生信息 学号 名 姓 专业 当前已得学分 教室号 大楼 房间号 座位数 说明 学生号 开课系别 开课课程 分数 专业 学分数 人数 id唯一标识学生，major是专业，current_credits是该学生已修的学分数 classes表描述课程信息
department是开课系，course是课程号，department和course唯一决定该课程 max_students是该课程能容纳的学生数，current_students是当前选修的学生数
num_credits是该课程的学分数，room_id是所在的教室位置（和rooms表相关联） rooms教室信息
room_id唯一标识教室，building是所在的教学楼，room_number是房间号 number_seats是教室的座位数 registered_students 学生课程的成绩
student_id是学生号（和students表关联），department和course描述具体课程 grade是该门课的成绩
1． 查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore） 2． 查询所有学生所有课程的成绩
3． 查询特定专业的所有学生名字（比如history专业） 4． 列出所有专业的学生名字
5． 查询cs系开的课所在教室的位置信息 6． 查询所有系开的课所在教室的位置信息
7． 统计music专业的学生总数和学分数
8． 统计所有专业的学生总数和学分数 9． 在students表中新加一个学生
10.该学生选了一门新课（比如his系开的301课），请改变相应的表 11.该学生修完新课，成绩为B，请改变相应表 12 .一个学生毕业，请删除该学生所有相应信息 13找出所有姓中以S.开头的学生。
14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。 15找出所有教室的座位数，最大教室的座位数、最小座位数。 16找出各大楼的最大教室，最小教室，平均座位数、座位总数。 17找出各课程尚可选修的人数。 18找出开课最多的系。 19找出开课最少的系。 20找出选课最多的学生。 21找出选课最少的学生。 22找出不及格的学生。
23找出各课程平均分以下的学生。 24找出各系所占教室的座位数。
25分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。 26找出选修了HIS系开的所有课程的学生。
参考答案：
1查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore）
select first_name last_name Grade from Registered_students and Students where first_name=david and students.ID=Regjested_students.Student_id. 2查询所有学生所有课程的成绩
last_name=dinsmore
and
select ID course Grade from Registered_students students.ID=Regjested_students.Student_id.
3查询特定专业的所有学生名字（比如history专业）
select Firstname Lastname from students where major=”history” 4列出所有专业的学生名字
and Students where
select major Firstname lastname from students group by major (order by major asc?) 5查询cs系开的课所在教室的位置信息
select course roomid building roomnumber from classes and rooms where classes.roomid =rooms.roomid and department=”cs”加引号 6查询所有系开的课所在教室的位置信息
select department course building roomnumber from classes and roomes where classes.roomid=rooms.roomid group by department 7统计music专业的学生总数和学分数
select totalstudent tatalcredits from major_stats where major=”music”
8统计所有专业的学生总数和学分数 select* from major_stats group by major 9在students表中新加一个学生
insert into students (id, firstname, lastname, major, values(“4445”,”m”,”ml”,”cs”,”133”)
10该学生选了一门新课（比如his系开的301课），请改变相应的表
currentcredits
)
insert into registered_students(studentid, department,course) values(“4445”,”his”,”301”) update classes set currentstudents+=1 where department=”his” and course=”301” 11该学生修完新课，成绩为B，请改变相应表
Update registeredstudents set grade=”B” where studentid=”4445” Update students set currentcredits+=2 where studentid=”4445” update classes set current_students=current_students-1 where department=\"HIS\" and course=\"301\" 12
一个学生毕业，请删除该学生所有相应信息
delete from students where id=1205
delete from regidtered_students where student_id=1205 update major_stats
set total_students=total_students-1,total_credits=total_credits-12 where major_stats.major=\"Computer Science\"
13找出所有姓中以S.开头的学生。
Select *
From students
Where Last_name like “S%”
14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。
Select major,count(distinct id),max(current_credits),min(current_credits),avg(current_credits),sum(current_credits) from students group by major
15找出所有教室的座位数，最大教室的座位数、最小座位数。 Select (Number_seats_Seats,Max(Number_Seats),Min(Number_Seats) From Rooms
16找出各大楼的最大教室，最小教室，平均座位数、座位总数。 找出各大楼的最大教室，
select building,room_number as biggest_room from rooms as R
where number_seats>=all(select number_seats from rooms as T
where R.building=T.building) 最小教室，
select building,room_number as biggest_room
from rooms as R
where number_seats<=all(select number_seats from rooms as T
where R.building=T.building) 平均座位数、座位总数。
select avg(number_seats),sum(number_seats) from rooms
group by building
17找出各课程尚可选修的人数。
select course,max_students-current_students as remain from classes
18找出开课最多的系。 select department from classes
group by department
having count(course)>=all(select count(course) from classes
group by department) 19找出开课最少的系。 select department
from classes
group by department
having count(course)<=all(select count(course) from classes
group by department)
20找出选课最多的学生。 select student_id,count(course) from registered_students
group by student_id
having count(course)>=all(select count(course) from registered_students group by student_id)
21找出选课最少的学生。 select student_id,count(course) from registered_students group by student_id
having count(course)<=all(select count(course) from registered_students group by student_id)
22找出不及格的学生。
select distinct id,first_name,last_name from registered_students,students where id=student_id and grade=\"E\"
23找出各课程平均分以下的学生。 select student_id,course,grade from registered_students as R where grade<=(select avg(grade) from registered_students as G where R.course=G.course)
24找出各系所占教室的座位数。 select department,sum(number_seats) from classes,rooms
where classes.room_id=rooms.room_id
25分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。 creat table history_table (student_id integer, first_name char(20), last_name char(20),
department char(3), course integer, register_time date,
primary key (student_id,course),
check (student_id in (select id from students)))
将当前学生、选修信息全部存储到该历史表。 select id,first_name,last_name,department,course,2002-5-1 from students,registered_students as R where students.id=R.studnet_id T.first_name=students.first_name and
and
T.student_id=students.id
T.last_naem=students.last_name
and and
T.department=R.department and T.course=R.course 当前学生毕业，将当前学生删除。
delete from history_table
where register_time=2002-5-1
26找出选修了HIS系开的所有课程的学生。 select id,first_name,last_name from students as S
where not exists ((select course from classes
where department=\"HIS\") except
(select R.course
from registered_students as R
where R.student_id=S.id)) 或者
Select student_id
From registered_students
Where department=”HIS” Group by student_id
Having count(student_id)=
（select count(distinct course) from registered_students where department=”HIS” )
因篇幅问题不能全部显示，请点此查看更多更全内容