数据库视频——T-SQL

Transact-SQL 

• Transact-SQL语言是结构化查询语言（SQL）的增强版本，Transact-SQL 代码已成为SQL Server 的核心。
• Transact-SQL在关系数据库管理系统中实现数据的检索、操纵和添加功能。
• 在Microsoft SQL Server 2008系统中，Transact-SQL 可以创建，维保护数据库对象，并且可以操作对象中的数据，所以T-SQL 是一种完整的语言
• 根据T-SQL 语言的执行功能特点，可以将T-SQL语言分为三种基本类型：

 

• 三种基本类型
  • 数据定义语言（DDL）
  • 数据操纵语言（DCL）
  • 数据控制语言（DML）
• 其他类型
  • 事务管理语言
  • 流程控制语言
  • 附加的语言元素

 

 

 变量

声明：一种方式；赋值：两种方式

declare @name nvarchar(50)    '声明语句

'要给声明的局部变量赋值，可以使用SET或SELECT语句
set @name = '李明'                   '赋值语句
select @name = '王文'              '也可进行赋值

select @name                           '进行查询

IF…ELSE 语句

DECLARE @NUM INT  '声明变量

SELECT @NUM = AVG(分数)
FROM 成绩信息
where 课程编号 = '2' AND 考试编号 = '0802'

IF @NUM >= 60                         
BEGIN
  IF @NUM<70
  PRINT '成绩刚刚及格，还要继续努力'
  ELSE IF @NUM<80
  PRINT '成绩中等'
  ELSE IF @NUM<90
  PRINT '成绩良好'
  ELSE PRINT '成绩优秀，希望把经验与大家分享分享！'
END 
ELSE 
PRINT '这个老师教学方法需要改进'

 case 语句

SELECT 员工姓名,所任职位,员工职称=
 case 所任职位 
 when '经理' then '高级职称'
 when '主管' then '中级职称'
 when '职员' then '初级职称'
 else '其他职称'
 end
 FROM 员工信息

 While语句

Declare @i int, @num int
set @i=1
set @num=1
while @i<10
begin
       set @num+@num+@i
       set @i=@i+1
end 
print @num

 

 

函数

数学函数

举例：

先声明变量，并赋值

declare @i float, @j int, @a decimal(10,2)
set @i=-12.2324
set @j=100

 如果：

print abs(@i)

则输出结果为：12.2324

如果：

print round(@i,2)

 则输出结果为：12.23

......

 

字符串函数

举例：

declare @str nvarchar(50)
set @str= 'hello world!'
print ascii(@str)

输出结果为：104 

declare @str nvarchar(50)
set @str= 'hello world!'
print char(104)

输出结果为：h

 

declare @str nvarchar(50)
set @str= 'hello world!'
print substring(@str,7,11)

 输出结果为：world！

......

 

聚合函数

• AVG
• COUNT
• MAX
• MIN
• SUM

SELECT AVG(分数) as 平均成绩
FROM 成绩信息
where 考试编号='0801' and 课程编号='2'

 

SELECT AVG(分数) as 平均成绩, COUNT(分数) as 考试人数,SUM(分数) as 总体分数
FROM 成绩信息
where 考试编号='0801' and 课程编号='2'

 

SELECT MAX(分数) as 最高分, MIN(分数) as 最低分
FROM 成绩信息
where 考试编号='0801' and 课程编号='2'

 

 

时间和日期函数

print getdate()

输出结果为： 11  1 2018  5:58PM

print year(getdate())

输出结果为：2018 

......

 

游标

是一种数据访问机制，它允许用户访问单独的数据行，而不是对整个行集进行操作。用户可以通过单独处理每一行逐条收集信息并对数据逐行进行操作，这样可以降低系统开销和潜在的阻隔情况。用户也可以使用这些数据生成SQL代码并立即执行或输出。

游标主要包括：

• 游标结果集，由定义游标的select语句返回的行的集合。
• 游标位置，纸箱这个结果集中的某一行的指针

游标的特点：

• 游标返回一个完整的结果集，但允许程序设计语言只调用集合中的一行
• 允许定位在结果集的特定行
• 从结果集的当前位置检索一行或多行
• 支持对结果集中当前位置的行进行数据修改
• 可以为其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持
• 提供脚本、存储过程和触发器中使用的访问结果集中数据的T-SQL语句

游标的声明

declare STUDENT_CORSOR SCROLL CURSOR
FOR
SELECT * FROM Student
for update 

操作游标

• 打开游标：open
• 检索游标：fetch ….into
• 关闭游标：close
• 释放游标：deallocate

取值：

0 FETCH 语句成功

-1FETCH 语句失败或行不在结果集中

-2 提取的行不存在

 

事务

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

使用下列4条语句管理事务：

• BEGIN TRANSACTION
• COMMIT TRANSACTION
• ROLLBACK TRANSACTION（回滚，避免数据出错）
• SAVE TRANSACTION

同时对数据进行修改时，可能会由于时间问题，可能会导致修改语句出错，这时就可以通过创建事务

begin transaction   ‘事务的开始
declare @nation varchar(20)   '声明变量
set @nation='满族'               '给变量赋值
insert into student_info  values('20150023','梨花','男','1979-9-28','1','15910784658','2018-9-26')   '插入记录
if @nation='汉族'    '对值进行判断
begin                     '执行操作
	commit transaction       '提交当前事务
end 
else
  rollback transaction              '回滚事务

'当前查询无法查询到结果，但如若将赋值语句处改成“汉族”，则可以查询到一条记录

 

锁

封锁：就是一个事务可向系统提出请求，对被操作的数据枷锁（Lock）,其他事务必须等到此事务解锁（Unlock）之后才能访问该数据。

从而，在多个用户并发访问数据库时，确保不互相干扰。

可锁定的单位是：行、页、表、盘区、和数据库

 

锁的类型：

（1）共享（S）锁，用于读操作

多个事务科封锁一个共享单位的数据

任何事务都不能修改加S锁的数据

通常是加S锁的数据被读取完毕，S锁立即被释放

（2）独占/排他（X）锁：用于写操作

仅允许一个事务封锁此共享数据

其他任何事务必须等到X锁被释放才能对该数据进行访问

X锁一直到事务结束才能被释放

（3）更新（U）锁

用来预定要对此页施加X锁，她允许其他事务读，但不允许再施加U锁或X锁

当被读取数据页将要被更新时，则升级为X锁

U锁一直到事务结束时才能被释放

 

死锁

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，他们都将无法推进下去，此时成系统处于死锁状态或系统产生了死锁。

 

SQL Server 2008 事务模式

• 自动提交事务：每条单独的语句都是一个事务
• 显示事务：每个事务均以  BEGIN TRANSACTION 语句显式开始，以COMMIT 或 ROLLBACK语句显式结束
• 隐式事务：在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT 或 ROLLBACK语句显式完成
• 批处理级事务：只能应用于多个活动结果集（MARS）， 在MARS会话中启动的Transact-SQL 显式或隐式事务变为批处理级事务，当屁处理完成时没有提交或回滚的批处理级事务自动由SQL Server进行回滚