大型数据库技术复习 —— T-SQL编程

简介

• T-SQL 即 Transact-SQL
• Transact-SQL是SQL-Server特有的在SQL基础上扩展后的超集。

标识符

• 常规标识符跟其他语言的变量命名完全一致
• 特殊标签符：

1. @：表示局部变量
2. @@：表示全局变量
3. #：表示局部临时对象
4. ##：表示全局临时对象

对象简写

• 例： server.database.owner.object
• 简写格式
  – server.database…object
  – server…owner.object
  – server…object
  – database.owner.object
  – database…object
  – owner.object
  – object

局部变量

• 声明

DECLARE @vLastName char(20), @vFirstName varchar(11)

• 赋值

SET @vLastName = 'Dodsworth'
SELECT @vFirstName = FirstName 
	FROM Employees
	WHERE LastName = @vLastName

• 使用

PRINT @vFirstName + ' ' + @vLastName

常用全局变量

运算符

• 算术： *, /, %, +、-
• 比较：=, >, <, >=, <=, <>
• 连接：+
• 逻辑：AND, OR, NOT

控制流语句

• BEGIN…END: 相当于C/C++的“{}”，用于将两条或两条以上的T-SQL语句组合成一个逻辑块
• IF…ELSE:

IF EXISTS (SELECT OrderID FROM Orders) WHERE CustomerID = 'sunyubob'
	PRINT 'sunyubob cannot be deleted'
ELSE
	BEGIN
		DELETE Customers WHERE CustomerID=@Username
		PRINT 'Customer deleted'
	END

• 简单 CASE

SELECT RELATIONSHIP,
CASE RELATIONSHIP	-- 这里指定要匹配的字段
	WHEN 'DAUGHTER' THEN '女儿'
	WHEN 'SON' THEN '儿子'
	WHEN 'SPOUSE' THEN '配偶'
	ELSE '其他'
END AS '关系'
FROM DEPENDENT

• 搜索 CASE

SELECT 'Product_Inventory_Status' = 
	CASE -- 这里不指定字段
		WHEN (UnitsInStock < UnisOnOrder And Discontinued = 0)	-- 匹配的条件
			THEN 'Negative Inventory - Order Now!'
		ELSE 'In Stock'
	END
FROM Northwind..Products

• WHILE

DECLARE @n tinyint
SET @n = 5

WHILE (@n > 0)
BEGIN
	IF @n % 2 = 1
		PRINT '奇数'
	ELSE
		PRINT '偶数'
	SET @n = @n - 1
END

函数

• 数学函数

-- COS()
DECLARE @angle FLOAT
SET @angle = 14.78
PRINT 'The COS of the value is: ' + CONVERT(VARCHAR, COS(@angle))

-- RAND()
DECLARE @counter SMALLINT
SET @counter = 1
WHILE @counter < 5 
BEGIN
      PRINT RAND(@counter)
      SET @counter = @counter + 1
END

• 字符串函数

-- LTRIM()
-- RTRIM()
-- LTRIM(RTRIM())
DECLARE @string CHAR(20)
SET @string = '      ABCDEFG       '
PRINT '[' + LTRIM(@string)+']'
PRINT '[' + RTRIM(@string)+']'
PRINT '[' + LTRIM(RTRIM(@string))+']'

-- SUBSTRING()
PRINT SUBSTRING('abcdefg', 3, 2)	-- 显示字符串“abcdefg”中从位置3开始的2个字符

-- REPLACE()
PRINT REPLACE('abcdefghicde', 'cde', 'xxx')	-- 用“xxx”替换“abcdefghicde”中的字符串“cde”

-- REVERSE()
PRINT REVERSE('ABC')	-- 将字符串“ABC”反转后返回

-- STUFF()
PRINT STUFF('ABCDEF', 2, 3, 'ijklmn')	-- 用“ijklmn”替换“ABCDEF”中第2个字符开始的3个字符

• 时间日期函数

-- GETDATE()
-- DATEADD()
DECLARE @old DATETIME, @new DATETIME
SELECT @old=GETDATE( ), @new=DATEADD(wk, 3, @old )	-- 当前日期时间加上3周后显示出来

-- DATEDIFF()
SELECT DATEDIFF(ms, @start, @end)	-- 时间 ms 差

• 转换函数

-- CAST()
SELECT CAST('ABC' AS CHAR(10) )+'CDE'

-- CONVERT()
SELECT CONVERT(VARCHAR(60), GETDATE(), 126)

注释

• 行内注释： --
• 块级注释：/**/

执行 T-SQL 的方式

• 动态构造执行

EXECUTE
('USE ' + @dbname + ' SELECT * FROM '+ @tblname)

• 批处理：使用GO定义一个批处理，作为批结束的标志

USE 教学管理
GO
CREATE VIEW sub_学生表
AS
SELECT 学号, 姓名 FROM 学生表
GO
SELECT * FROM sub_学生表
GO

• 脚本：编辑T-SQL语句并保存为 .sql 文件即可
• 事务：

USE 教学管理
GO
BEGIN TRANSACTION	-- 开始事务
DELETE  from 选课表 WHERE 学号='S060101'
DELETE FROM 学生表 WHERE 学号='S060101'
GO
SELECT *from 选课表 WHERE 学号='S060101'
SELECT * FROM 学生表 WHERE 学号='S060101'
GO
COMMIT	-- 提交事务

• 注意：事务执行时出现错误回退整个事务处理，批执行时出现错误只回退产生错误的语句

异常处理

BEGIN TRY
……
END TRY
BEGIN CATCH
  --TRY程序段出现任何错误则跳转至此执行异常处理程序
END CATCH