SQLServer（⼀）对表的增删改查、事务、锁1、数据库命名规则（⽼版本，2008之前版本）
名称长度不能超过128个字符，本地临时表名称不能超过116个字符。
名称的第⼀个字符尽量使⽤英⽂字母、中⽂（或其他语⾔的字母）、下划线、“@”或“#”符号。
除第⼀个字符外的其他字符，还可以包括数字和“$”符号。
名称中间不允许有空格或其他特殊字符。
名称不能是保留字。
2、基本操作
/*创建表*/
CREATE TABLE Class(
cId INT PRIMARY KEY,
cNum INT NOT NULL,
cMteacher VARCHAR(20)
)
CREATE TABLE Student(
sId INT  PRIMARY KEY,
sName VARCHAR(20),
sAge INT,
sTel INT,
cId INT NOT NULL ,FOREIGN KEY(cId) REFERENCES class(cId)
)
CREATE TABLE Teacher(
tId int NOT NULL,
tName VARCHAR(20) NOT NULL,
tAge int,
cId int
)
/*删除表*/
DROP TABLE Teacher;
/*删除表数据*/
TRUNCATE TABLE Class;
/*更改表名*/
SP_RENAME Student,Studentinfo
/*更改表结构 -添加字段*/
ALTER TABLE Student ADD sAddress VARCHAR(50);
/*更改表结构 -删除字段*/
ALTER TABLE Student DROP COLUMN sAddress ;
/*添加组合主键*/
ALTER TABLE [dbo].[Teacher]
ADD
CONSTRAINT PK_test PRIMARY KEY NONCLUSTERED (tId,tName )
/*添加外键约束*/
ALTER TABLE dbo.Teacher
WITH CHECK
ADD CONSTRAINT FK_Teacher FOREIGN KEY(cId)REFERENCES dbo.Class(cId)
/
*添加check约束 */
ALTER TABLE dbo.Teacher
WITH NOCHECK
WITH NOCHECK
ADD CONSTRAINT CK_Teacher CHECK(tAge > 0 AND tAge <70)
/* 插⼊数据*/
INSERT INTO Class VALUES(1,30,'王⼩红'),
(2,30,'张⼩明')
/*利⽤显⽰事务控制DML操作 */
BEGIN TRANSACTION tr_Teacher
INSERT INTO test.dbo.Teacher VALUES(01,'张⼩明',28,2)
GO
UPDATE test.dbo.Teacher
SET tAge = 29
WHERE tId = 01
GO
COMMIT TRANSACTION tr_Teacher
/*打开隐式事务后，每次执⾏操作，都会开始⼀个新事务，需要⽤COMMIT或ROLLBACK结束*/
SET IMPLICIT_TRANSACTIONS ON
INSERT INTO Teacher VALUES(02,'王⼩红',35,1)
GO
UPDATE Teacher
SET tAge = 34
WHERE tId = 02
GO
COMMIT
SET IMPLICIT_TRANSACTIONS OFF
SELECT * FROM Teacher
/*设置事务保护点和事务回滚⾄保护点的语法*/
SAVE TRANSACTION savepoint_name
ROLLBACK TRANSACTION savepoint_name
--并发控制
当多个⽤户同时访问同⼀数据时，为了保证数据的准确性，将对事务进⾏并发控制。
（1）并发访问的问题：
丢失更新数据：
解决：在A完成数据修改并提交事务之前，B客户端不允许访问相同的数据⽂件
脏读：
解决：事务A提交事务之前，不允许其他事务读取正在更改的数据基本的增删改查语句
⾮重复读：
解决：在事务A完成最后⼀次读取数据前，不允许其他事务读取正在更改的数据
幻象读：
解决：采⽤并发技术，利⽤锁⽅法等来确保数据的准确性
--SQL server中的锁
锁定是数据库引擎为了避免数据出现异常，⽽限制多个⽤户在同⼀时间访问相同数据块的⼀种机制                                                                  有关锁资源1
锁模式应⽤
/* 得到数据库中活跃的事务和锁的信息*/
BEGIN TRANSACTION  /*开始事务*/
USE test
DELETE FROM dbo.Teacher/*删除表中数据并使⽤锁*/
WITH (TABLOCKX)
WHERE tId=1
DBCC OPENTRAN('test')/*查询活动事务信息 */
COMMIT/*关闭事务*/
查询结果：
在SQL Server中提供了⼀个动态视图（sys.dm_tran_locks），它管理着有关当前活动的锁管理器资源的信息。有关他的常⽤返回列及其含义：
* 查询当前的锁管理器资源的信息*/
SELECT request_session_id,
resource_type AS type,
resource_database_id,
request_status
FROM sys.dm_tran_locks
查询结果：
--事务的阻塞
如果⼀个事务在数据操作过程中锁住了某个数据库资源，⽽此时，另⼀个事务想访问该资源，则必须等待该资源解锁，这样就会发⽣阻塞。
【例5.11】演⽰事务的阻塞。
要求事务A对表AtriTest中的数据进⾏修改，但要求事务不提交，然后利⽤事务B对表AtriTest中相同的数据进⾏更新，查看效果，操作步骤如下：
1 利⽤事务A对表AtriTest中的数据进⾏更新，脚本如下：
--READ COMMITTED隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
USE test
GO
BEGIN TRANSACTION A                                --事务A开始
UPDATE Teacher SET tAge = 39 WHERE
tId = 1
事务A对表Teacher中id是1的记录进⾏修改操作，此时事务已经开始，但始终没有提交。
2 利⽤事务B对表Teacher中id为1的数据进⾏修改，脚本如下：
-READ COMMITTED隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
USE test
GO
BEGIN TRANSACTION B                                --事务B开始
UPDATE Teacher SET tAge = 47 WHERE
tId = 1
事务B同样修改id为1的tAge列数据，此时，由于事务A没有提交事务，所以事务B将等待事务A完成，如果A⼀直没有提交，那么B将⼀直等待下去，这就形成了事务的阻塞。
为了快速地了解当前是否有事务进⼊了阻塞，可以使⽤动态管理视图sys.dm_os_waiting_tasks，它可以返回正在等待某些资源的任务的等待队列的有关信息。在下表给出了它常⽤的列及对应的含义。
该实例中⼀旦出现B事务⽆法执⾏下去的情况，可以利⽤以下的SQL语句查看当前的事务阻塞情况：
SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
这段脚本执⾏结果如下：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。