SQL SERVER设计与优化实验指导

1、SQLServer设计与优化实验指导邢台学院信息科学与技术系2012.9实验报告姓 名学 号系 别班级主讲教师指导教师实验日期专业课程名称一、 实验名称：二、实验目的：三、实验内容及要求四、实验材料、工具、或软件五、实验内容及实验步骤（或记录）六、实验存在问题和解决办法七、意见和建议八、教师评语（或成绩） 教师签字： 年 月 日实验项目周次章节及教学内容累计学时1实验一 数据库编程基础122实验一 数据库编程基础223实验二 使用ADO.NET访问数据库124实验二 使用ADO.NET访问数据库225实验三 SQL Server事务处理机制126实验三 SQL Server事务处理机制227实

2、验四 监控SQL Server的性能与活动128实验四 监控SQL Server的性能与活动229实验五 数据库系统开发1210实验五 数据库系统开发2211实验五 数据库系统开发3212实验五 数据库系统开发4213实验五 数据库系统开发5214实验五 数据库系统开发6215实验五 数据库系统开发7216实验五 数据库系统开发82实验一 数据库编程基础实验时数： 4学时授课类型：实验课教学方法与手段：指导目的要求：了解ADO.NET的工作原理。掌握常用OLE DB对象属性和方法，设计简单的数据库应用程序。实验内容：1、 自己编写代码实现数据库连接和数据显示using System;using

3、 System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient ;namespace 自己编写数据库连接 public partial class Form1 : Form private SqlConnection myCon; private SqlDataAdapter myAda; private SqlCommand

4、 myCmd; private SqlCommandBuilder myCbd; private DataSet mySet; public Form1() InitializeComponent(); myCon = new SqlConnection(Data Source=.;Initial Catalog=student;Integrated Security=True); myAda = new SqlDataAdapter(); myCmd = new SqlCommand(select * from c,myCon); myAda.SelectCommand = myCmd; m

5、yCbd = new SqlCommandBuilder(myAda);/ SqlCommandBuilder有什么妙用，我们从字面意思翻译一下，Sql就是Sql，众人名其曰结构化查询语言；Command，名令；Builder，构造，建造，不太好，人家带了个er，学过E文的都知道，这是个名词，那就译之为构造器，建造者能说得过去。Sql命令构造器，这下恍然明白了，就是用来构造Sql命令的，比如你更新数据库需要一个Update，通过这句它就给咱构造出来了。那么Sql命令构造器这个东西又是怎么生成咱需要的Sql语句命令的？它怎么知道我们要什么样滴？其实它不知道，是我们给了它提示信息，不过它实在是太聪

6、明了，就这么点提示信息人家就举一反三，根据这点提示推断出我们的需求。那我们在哪里给提示了，看这句myAda.SelectCommand = myCmd;我们把查询的Sql命令给了出来，给了DataAdaper数据适配器的SelectCommand，而我们又把DataAdaper数据适配器（在这里为myAda）作为参数丢给了SqlCommandBuilder，这下好了，被SqlCommandBuilder抓到了，它就这样轻松加愉快的，根据抓到的DataAdaper的SelectCommand信息，一骨碌的把其它的Sql操作命令都给生成出来了。可能有朋友问了，我不用这个命令构造器生成Sql操作命令

7、行不？当然是可以了，要是总觉得它自动给搞出来的不符合我们的要求，我们完全可以自己写的灵活点，像下面这样就可以了：/myAda.DeleteCommand = New SqlCommand(delete * from UserInfo where .) mySet = new DataSet(); /通过DataAdaper给我们提供了TableMappings属性，利用它就可以把数据库里的千奇百怪的字段名毫不费力的给转成我们需要的名字，人家这里称作是映射。 myAda.TableMappings.Add(UserInfo, UserInfo); myAda.TableMappings0.Col

8、umnMappings.Add(cno, 课程号); myAda.TableMappings0.ColumnMappings.Add(cname, 课程名); myAda.TableMappings0.ColumnMappings.Add(credit, 学分); private void Form1_Load(object sender, EventArgs e) try myAda.Fill(mySet, C); catch (SqlException ex) MessageBox.Show(ex.ToString(); finally myCon.Close(); dataGridVie

9、w1.DataSource = mySet.Tablesc; /查询完成了，更新，删除当然也是不在话下了，我们在dataGridView控件中编辑完数据后，会首先将变化反映到关联的DataSet的DataTable中,接下来还是数据适配器上场,直接调用它的Update万能方法就可以了,就会把我们的数据的更改写到源数据表中了.这里万能的背后仍然是DataAdaper的SqlCommand命令,我们此前已经为它们配置好了,这里的一切就交给Update方法吧.这里要罗索一下的是mySet.Tables0.AcceptChanges(),这句很有用,要不你更新一条后,接着再去更新第二条,第三条的话,就

10、会出现异常,因为一次更新发生后,DataSet会对本次更新挂起,到下一次更新的时候又会重复进行上一次挂起的更改,所以在每次更新后必须调用AcceptChanges来清除挂起的更改. private void button1_Click(object sender, EventArgs e) try /将更改的数据更新到数据表里 myAda.Update(mySet.Tables0.GetChanges(); MessageBox.Show(数据更新成功！); /DataTable接受更改,以便为下一次更改作准备 mySet.Tables0.AcceptChanges(); catch (Sql

11、Exception ex) ex.ToString(); private void button2_Click(object sender, EventArgs e) /从DataTable中删除当前选中的行mySet.Tables0.RowsdataGridView1.CurrentRow.Index.Delete(); if (MessageBox.Show(确定要删除当前行数据？, , MessageBoxButtons.OKCancel) = DialogResult.OK) try /将更改的数据更新到数据表里 myAda.Update(mySet.Tables0.GetChange

12、s(); MessageBox.Show(数据删除成功！); /DataTable接受更改,以便为下一次更改作准备 mySet.Tables0.AcceptChanges(); catch (SqlException ex) MessageBox.Show(ex.ToString(); else /取消对DataTable的更改 mySet.Tables0.RejectChanges(); 2、 完善数据库SQL Server2005的数据库应用，创建实例完成C表的查看，增、删、改和查询等。1) 建立应用，并设计窗体2) 设置各控件属性注意DataGridView，BindingDataSou

13、rce，以及显示数据的3个文本框的数据源一致，才能产生联动效果。组合框采用单独的数据源（单独的数据集）用于获取所有的课程名。3) 添加代码和响应事件实验二 使用ADO.NET访问数据库实验时数： 4学时授课类型：实验课教学方法与手段：指导1、 目的要求：了解Windows窗体的数据体系结构，理解数据集的属性和方法，掌握DataTable和DataRow的属性和方法，掌握数据视图的主要属性。熟练使用ADO.NET编程。实验内容： 1、 查看数据集的XML格式2、 DataTable的相关设置与操作1）创建DataTable和列、约束以及主键2）创建计算列3、 DataRow的属性和方法1） Ro

14、wState属性private void DemonstrateRowState() /Run a function to create a DataTable with one column. DataTable myTable = MakeTable(); DataRow myRow; / Create a new DataRow. myRow = myTable.NewRow(); / Detached row. Console.WriteLine(New Row + myRow.RowState); myTable.Rows.Add(myRow); / New row. Console

15、.WriteLine(AddRow + myRow.RowState); myTable.AcceptChanges(); / Unchanged row. Console.WriteLine(AcceptChanges + myRow.RowState); myRowFirstName = Scott; / Modified row. Console.WriteLine(Modified + myRow.RowState); myRow.Delete(); / Deleted row. Console.WriteLine(Deleted + myRow.RowState);2） DataRo

16、w方法A：AcceptChanges：private void DemonstrateDeleteRow() / Create a simple DataTable with two columns and ten rows. DataTable table = new DataTable(table); DataColumn idColumn = new DataColumn(id, Type.GetType(System.Int32); idColumn.AutoIncrement=true; DataColumn itemColumn = new DataColumn(item, Typ

17、e.GetType(System.String); table.Columns.Add(idColumn); table.Columns.Add(itemColumn); / Add ten rows. DataRow newRow; for(int i = 0; i 10; i+) newRow = table.NewRow(); newRowitem = Item + i; table.Rows.Add(newRow); table.AcceptChanges(); DataRowCollection itemColumns = table.Rows; itemColumns0.Delet

18、e(); itemColumns2.Delete(); itemColumns3.Delete(); itemColumns5.Delete(); Console.WriteLine(itemColumns3.RowState.ToString(); / Reject changes on one deletion. itemColumns3.RejectChanges(); / Change the value of the column so it stands out. itemColumns3item = Deleted, Undeleted, Edited; / Accept cha

19、nges on others. table.AcceptChanges(); / Print the remaining row values. foreach(DataRow row in table.Rows) Console.WriteLine(row0 + table + row1); B: BeginEdit 方法DataTable table = new DataTable(table1); DataColumn column = new DataColumn(col1,Type.GetType(System.Int32); table.RowChanged+=new DataRo

20、wChangeEventHandler(Row_Changed); table.Columns.Add(column); / Add a UniqueConstraint to the table. table.Constraints.Add(new UniqueConstraint(column); / Add five rows. DataRow newRow; for(int i = 0;i5; i+) / RowChanged event will occur for every addition. newRow= table.NewRow(); newRow0= i; table.R

21、ows.Add(newRow); / AcceptChanges. table.AcceptChanges(); / Invoke BeginEdit on each. Console.WriteLine( n Begin Edit and print original and proposed values n); foreach(DataRow row in table.Rows) row.BeginEdit(); row0=(int) row0+10; Console.Write(table Original table + row0, DataRowVersion.Original);

22、 Console.Write(table Proposed table + row0,DataRowVersion.Proposed + n); Console.WriteLine(n); / Accept changes table.AcceptChanges(); / Change two rows to identical values after invoking BeginEdit. table.Rows0.BeginEdit(); table.Rows1.BeginEdit(); table.Rows00= 100; table.Rows10=100; try /* Now inv

23、oke EndEdit. This will cause the UniqueConstraint to be enforced.*/ table.Rows0.EndEdit(); table.Rows1.EndEdit(); catch(Exception e) / Process exception and return. Console.WriteLine(Exception of type 0 occurred., e.GetType(); 其中Row_Changed为：private void (object sender, System.Data.DataRowChangeEven

24、tArgs e) DataTable table = (DataTable) sender; Console.WriteLine(RowChanged + e.Action.ToString() + table + e.Row.ItemArray0);4、 视图操作5、 例题12.8，详细代码见教材1） 检索数据2） 显示雇员信息实验三 SQL Server事务处理机制实验时数： 4学时授课类型：实验课教学方法与手段：指导目的要求：掌握事务处理和锁的使用方法，设计事务，体验事务的原子性。设计事务，实现死锁，处理死锁。实验内容： 1、 初识事务-创建库IF DB_ID(TransactionTe

25、stDb) IS NULL CREATE DATABASE TransactionTestDb;GOSELECT * FROM SYS.DATABASESGO-创建表USE TransactionTestDbGOIF OBJECT_ID(dbo.Orders) IS NOT NULL DROP TABLE dbo.OrdersIF OBJECT_ID(dbo.Parts) IS NOT NULL DROP TABLE dbo.PartsGOCREATE TABLE dbo.Orders( OrderNoINT NOT NULL PRIMARY KEY, PartNoINT NOT NULL,

26、QutantityINTNOT NULL, PriceFLOAT)GOCREATE TABLE dbo.Parts( PartNoINT NOT NULL PRIMARY KEY, PartNameVARCHAR(50) NOT NULL, UnitPriceFLOATNOT NULL)GO不使用事务插入数据INSERT INTO dbo.Parts(PartNo,PartName,UnitPrice) VALUES(1, 1号镙丝, 0.5);INSERT INTO dbo.Parts(PartNo,PartName,UnitPrice) VALUES(2, 2号钢钉, 0.1);INSER

27、T INTO dbo.Parts(PartNo,PartName,UnitPrice) VALUES(2, 1号铅钉, 1.0);SELECT * FROM PartsGO使用事务插入数据（同一个表）SET XACT_ABORT ON USE TRANSACTIONTESTDBGOBEGIN TRANINSERT INTO dbo.Parts(PartNo,PartName,UnitPrice) VALUES(3, 2号镙母, 0.5);INSERT INTO dbo.Parts(PartNo,PartName,UnitPrice) VALUES(4, 2号齿轮, 2);INSERT INTO

28、 dbo.Parts(PartNo,PartName,UnitPrice) VALUES(4, 大号垫片,1)COMMIT TRANGOSELECT * FROM Parts使用事务插入数据（不同的表）SET XACT_ABORT ONUSE TransactionTestDbGOBEGIN TRANINSERT INTO dbo.Parts(PartNo,PartName,UnitPrice) VALUES(3, 2号镙母, 0.5);INSERT INTO dbo.Orders(OrderNo,PartNo,Price) VALUES(1, 3,10.0);COMMIT TRANGOSEL

29、ECT * FROM PartsSELECT * FROM OrdersGO2、 显示事务USE TransactionTestDbGODECLARE outQuantities AS INTDECLARE leftQuantities AS INTDECLARE OrderQuantities AS INTSET outQuantities = 2000BEGIN TRANSACTION-首先调整指定零件的库存信息;UPDATE Parts SET Parts.Quantities = Parts.Quantities - outQuantitiesFROM Parts WHERE Part

30、No = 1SELECT leftQuantities = QuantitiesFROM PartsWHERE PartNo =1 IF leftQuantities 0BEGINPRINT 库存不足，无发生成订单ROLLBACK; -撤销刚才的修改，恢复事务END ELSE-否则生成订单BEGIN INSERT INTO Orders(OrderNo,PartNo,Qutantity,Price)VALUES(1,1,outQuantities,outQuantities * 0.5);COMMIT;END GOSELECT * FROM PartsGOSELECT * FROM Order

31、sGO3、 隐式事务SET IMPLICIT_TRANSACTIONS ONGOUSE TransactionTestDbGOPRINT CURRENT TRANSACTION IS +CONVERT(VARCHAR(10),TRANCOUNT)SELECT * FROM PartsPRINT CURRENT TRANSACTION IS +CONVERT(VARCHAR(10),TRANCOUNT)INSERT INTO PARTS(PartNo,PartName,UnitPrice,Quantities)VALUES(3,1号镙母,0.8,2000)PRINT CURRENT TRANSA

32、CTION IS +CONVERT(VARCHAR(10),TRANCOUNT)SELECT * FROM PartsGO4、 嵌套事务-内部事务提交，外部回滚，则内部事务也自动撤销BEGIN TRANSACTION PartsTranUPDATE PartsSET Quantities = Quantities - 500WHERE PartNo = 3 BEGIN TRANSACTION OrdersTranINSERT INTO Orders (OrderNo,PartNo,Qutantity,Price)VALUES (2,3,500,500 * 0.6) COMMIT TRANSAC

33、TION OrdersTranROLLBACK TRANSACTION PartsTranGOSELECT * FROM PartsGOSELECT * FROM OrdersGO-内部事务回滚，外部事务提交，报错BEGIN TRANSACTION PartsTranUPDATE PartsSET Quantities = Quantities - 500WHERE PartNo = 3 BEGIN TRANSACTION OrdersTranINSERT INTO Orders (OrderNo,PartNo,Qutantity,Price)VALUES (3,3,500,500 * 0.6

34、) ROLLBACK TRANSACTION OrdersTranCOMMIT TRANSACTION PartsTranGOSELECT * FROM PartsGOSELECT * FROM OrdersGO5、 保存点单个事务中保存点USE TransactionTestDbGOSELECT * FROM PartsGOBEGIN TRANSACTION INSERT INTO dbo.Parts(PartNo,PartName,UnitPrice,Quantities) VALUES(4, 1号钢钉, 0.5,1000); SAVE TRANSACTION SAVEPOINT1 INS

35、ERT INTO dbo.Parts(PartNo,PartName,UnitPrice,Quantities) VALUES(5, 3号钢钉, 0.5,1000); ROLLBACK TRANSACTION SAVEPOINT1COMMITGOSELECT * FROM PartsGO嵌套事务中使用保存点USE transactiontestdbGOBEGIN TRANSACTION PartsTranUPDATE PartsSET Quantities = Quantities - 500WHERE PartNo = 3 SAVE TRANSACTION innnerSavePointIN

36、SERT INTO Orders (OrderNo,PartNo,Qutantity,Price)VALUES (3,3,500,500 * 0.6) ROLLBACK TRANSACTION innnerSavePointCOMMIT TRANSACTION PartsTranGOSELECT * FROM PartsGOSELECT * FROM OrdersGO 6、 写锁-连接USE TransactionTestDbGO-事物1BEGIN TRANSELECT * FROM Orders WITH(XLOCK)WAITFOR DELAY 00:00:10COMMIT TRAN-连接U

37、SE TransactionTestDbGO-事物2BEGIN TRANSELECT * FROM Orders WITH(XLOCK)COMMIT TRAN7、 死锁-连接1USE TransactionTestDbGO-事物1BEGIN TRANUPDATE Orders SET Quantity = Quantity - 100WHERE OrderNo = 1 SELECT * FROM Parts WHERE PartNo = 1; COMMIT TRAN-连接USE TransactionTestDbGO-事物2BEGIN TRAN UPDATE Parts SET Quantit

38、ies = Quantities + 100WHERE PartNo = 1SELECT * FROM Orders WHERE OrderNo = 1; COMMIT TRAN8、 更新锁事务1USE TransactionTestDbGOBEGIN TRANSACTION -开始一个事务SELECT * FROM Orders WITH(UPDLOCK) WHERE OrderNo in (1,2) WAITFOR DELAY 00:00:10 UPDATE Orders SET Qutantity = Qutantity - 100 FROM Orders WHERE OrderNo=1

39、 -COMMIT TRANSACTION -提交事务COMMIT TRAN事务2USE TransactionTestDbGOBEGIN TRANSACTIONSELECT * FROM Orders WHERE OrderNo IN (1,2)-UPDATE Orders SET Qutantity = Qutantity + 100-WHERE OrderNo = 1SELECT * FROM OrdersCOMMIT TRAN9、 自定义锁自定义锁可以作用于两种操作级别之上，一种是针对于指向数据库的连接操作，一种则以作用于事务操作的级别之上。例9-1，9-2-会话A:更改数据表Order

40、s中指定的记录USE TransactionTestDbGOSELECT * FROM OrdersGOEXEC sp_getapplock testLock, Exclusive, Session, -1UPDATE Orders SET Quantity = Quantity + 100FROM Orders WHERE OrderNo=1GO-会话B:查询数据表Orders中的详细记录USE TransactionTestDbGOEXEC sp_getapplock testLock, Exclusive, Session, -1SELECT * FROM OrdersGO如果事务A、B

41、同时执行则会发生阻塞，因为事务A中的自定义锁没有释放，但是如果不采用此机制则可能出现读脏数据的现象。将事务A更改为9-3-会话A:更改数据表Orders中指定的记录USE TransactionTestDbGOSELECT * FROM OrdersGOEXEC sp_getapplock testLock, Exclusive, Session, -1UPDATE Orders SET Quantity = Quantity + 100FROM Orders WHERE OrderNo=1WAITFOR DELAY 00:00:05EXEC sp_releaseapplock testLoc

42、k, Session在修改结束后释放锁，事务B将出现一定的滞后。10、 锁提示输入数据：ABC/lenovo/Jone/Sales Agent/ZhongGuanCun/BeiJing/aaa/China/DEF/lenovo/Jone/Sales Agent/ZhongGuanCun/BeiJing/aaa/China/GHI/lenovo/Jone/Sales Agent/ZhongGuanCun/BeiJing/aaa/China/JKL/lenovo/Jone/Sales Agent/ZhongGuanCun/BeiJing/aaa/China/-会话A，事务AUSE Northwin

43、dGOBEGIN TRANSELECT * FROM Customers WITH (TABLOCKX )WAITFOR DELAY 00:00:10-等待秒COMMIT TRAN-会话B，事务BUSE NorthwindGOBEGIN TRANBULK INSERT Customers FROM D:SQLDEMOCustomers.txtWITH (FIELDTERMINATOR = /)COMMIT TRAN11、 查看动态管理视图USE TransactionTestDb;GO- Lock infoSELECT request_session_id AS 进程标识, resource_

44、type AS 资源类型, resource_database_id AS 数据库标识, resource_description AS 资源文本说明, resource_associated_entity_id AS 资源实体标识, request_mode AS 请求模式, request_status AS 当前请求状态FROM sys.dm_tran_locks;SELECT name,database_id FROMSYS.DATABASESWHERE database_id =10-加锁，再查看动态管理视图-会话AUSE TransactionTestDbGOBEGIN TRANSELECT * FROM Orders WITH (HOLDLOCK )WHERE OrderNo = 1WAITFOR DELAY 00:00:10-等待秒GOCOMMIT TRAN12、 控制事务隔离级别-事务1USE TransactionTestDb;GOSET TRANSACTION I