数据库实验示例带答案.doc

示例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-F0-90-90-90-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-90-90-9)0-90-90-9-0-90-90-90-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_nochar(5)Not nullprimary key员工编号emp_namechar(10)Not null员工姓名sexchar(1)Not null性别deptchar(4)Not null所属部门titlechar(6)Not null职称date_hireddatetimeNot null到职日birthdaydatetimeNull生日salaryintNot null薪水addrchar(50)null住址（2）/*客户表customer */cust_idchar(5)Not nullprimary key客户号cust_namechar(20)Not null,客户名称addrchar(40)Not null,客户住址tel_nochar(10)Not null,客户电话zipchar(6)null邮政编码（3）/*销售主表sales */order_nointNot nullprimary key订单编号cust_idchar(5)Not null,客户号sale_idchar(5)Not null,业务员编号tot_amtnumeric(9,2)Not null,订单金额order_datedatetimeNot null,订货日期ship_datedatetimeNot null,出货日期invoice_nochar(10)Not null发票号码（4）/*销货明细表sale_item */order_nointNot null,primary key订单编号prod_idchar(5)Not null,产品编号qtyintNot null销售数量unit_pricenumeric(7,2)Not null单价order_datedatetimenull订单日期（5）/*产品名称表product */pro_idchar(5)Not nullprimary key产品编号prod_namechar(20)Not null产品名称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,sexfrom employeewhere title=经理or title=职员and sex=F2、选取销售数量最多的前5条订单订单号、数量。select top 5 with ties order_no,sup_id,qtyfrom sale_itemorder by qty desc3、计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。select prod_id,qty,unit_price,(qty*unit_price)tot_amtfrom sale_itemorder 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 d_id,a.qty,a.unit_price,d_namefrom sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/on (d_id=_id) and a.qty2order by d_id2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addrfrom employee as a inner join employee as bon (a.emp_no!=b.emp_no) and (a.emp_nameb.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.addrfrom employee as a join employee as bon (a.emp_no!=b.emp_no) and (a.emp_nameb.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 d_id,d_name,b.qty,b.unit_price from product as a join sale_item as b on d_id=d_id order by prod_id ;3、 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。select d_id,d_name,b.qty,b.unit_price from product as a join sale_item as b on d_id=d_id and b.unit_price2400 ;4、 分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。 select d_id,d_name,b.qty,b.unit_price from product as a left join sale_item as b on d_id=d_id and b.unit_price2400 ; inner join left join right join full join 五、实验报告实验四实验名称：SELECT的嵌套使用，实现复杂查询（2课时）一、实验目的掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。二、实验环境三、实验示例1、由employee表中查找出薪水最高的员工信息。select *from employeewhere 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表中所有女职工的平均工资和住址在上海市的所有女职工的平均工资beginselect avg(salary)from employeewhere sex like funionselect avg(salary)from employeewhere sex like f and addr like 上海end四、实验内容与步骤1、 由sales表中查找出销售金额最高的订单。select * from saleswhere 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 saleswhere 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_idselect 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=f4、 找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。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_no5、 找出目前业绩未超过200000元的员工。 select * from employee where emp_no in (select sale_id from sales where tot_amt1 begin print 此删除操作可能回删除多条人事表数据! rollback transaction end*/delete from employeewhere sex=m /*结果：所影响的行数为*/3 创建AFTER-UPDATE触发器，在product表上创建触发器updproduct_trg,当对表product的记录进行修改时，触发器将给出修改时间信息。USE companyGOCREATE TRIGGER updproduct_trgON productFOR UPDATEAS /* 为触发器定义要执行的操作 */PRINT 修改的时间为:+CONVERT(char, getdate(),110) /* 显示修改的时间信息 */GOupdate product set prod_name=sql where prod_id=20（二） Instead of类型触发器1替代触发器能够使无法更新的视图支持更新功能。基于多表的视图必须使用Instead of触发器来支持基表数据的增、删、改操作。建立视图：Create View View_product_saleitem asSELECT d_id, d_name, dbo.sale_item.order_no, dbo.sale_item.qty, dbo.sale_item.unit_price, dbo.sale_item.order_dateFROM duct INNER JOIN dbo.sale_item ON d_id = dbo.sale_d_idUSE companyGOCREATE TRIGGER InsViewprod_saleitem_TrgON View_prod_saleitemINSTEAD OF INSERTASBEGIN /* 定义触发器的行为*/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_dateFROM 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)用于不更改或不更新数据的读取操作，如 SELECT 语句。更新 (U)用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。排他 (X)用于数据修改操作，例如 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 table1begin tranupdate table1 set a=abselect * from table1if not exists(select * from table1 where b=b5) begin print 找不到该记录，回滚事务 rollback tran endelse begin print 找到记录，提交事务 commit tran end select * from table1go三、验证脏读事务1select *from table1begin tranupdate table1 set a=aselect * from table1waitfor delay 00:00:10rollback transelect * from table1go事务二set transaction isolation level read uncommittedprint 脏读select * from table1if rowcount0beginwaitfor delay 00:00:10print 不重复读select * from table1endgo验证：将隔离级别设为set transaction isolation level read committed看看，脏数据不会读出四、不可重复读事务1set transaction isolation level read committedbegin tranprint 初始select *from table1waitfor delay00:00:10print 不可重复读select * from table1rollback tran事务2：set transaction isolation level read committedupdate 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 SERIALIZABLEBEGIN TRAN tUPDATE table1 SET a=aWAITFOR delay 00:00:20Select * from table1COMMIT TRAN t事务2. 在B连接中执行，并且要在A执行后的20秒内。BEGIN TRAN ttINSERT INTO table1 values(a4,b4,c4)Select * from table1COMMIT 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,loginamefrom master.dbo.sysprocesseswhere blocked0示例2数据库说明：这是一个大学入学注册管理