《SQL Server 2008从入门到精通》--20180710

目录

1.使用Transact-SQL语言编程

尽管SQL Server 2008提供了图形化界面，但只有一种Transact-SQL语言能够直接与数据库引擎进行交互。根据执行功能特点可以将Transact-SQL语言分成3大类：数据定义语言DDL，数据操纵语言DML，数据控制语言DCL。

1.1.数据定义语言DDL

是最基础的Transact-SQL语言类型，用来创建数据库和创建，修改，删除数据库中的各种对象，为其他语言的操作提供对象。例如数据库，表，触发器，存储过程，视图，函数，索引，类型及用户等都是数据库中的对象。常见的DDL语句包括

CREATE TABLE--创建表
DROP TABLE--删除表
ALTER TABLE--修改表

1.2.数据操纵语言DML

是用于操纵表和视图中的数据的语句，例如查询数据（SELECT），插入数据（INSERT），更新数据（UPDATE）和删除数据（DELETE）等。

1.3.数据控制语言DCL

涉及到权限管理的语言称为数据控制语言，主要用于执行有关安全管理的操作。如授予权限（GRANT），收回权限（REVOKE），拒绝授予主体权限，并防止主体通过组或角色成员继承权限（DENY）

1.4.Transact-SQL语言基础

1.4.1.常量与变量

常量不多说。在SQL Server 2008中，存在两种变量。一种是系统定义和维护的全局变量，一种是用户定义用来保存中间结果的局部变量。

1.4.1.1.系统全局变量

系统全局变量分为两大类，一类是与当然SQL Server连接或与当前处理有关的全局变量，如@@Rowcount表示最近一个语句影响的行数。@@error表示保存最近执行操作的错误状态。一类是与整个SQL Server系统有关的全局变量，如@@Version表示当前SQL Server的版本信息。

SELECT @@VERSION AS 当前版本;--查看当前SQL Server的版本信息

结果如图所示

1.4.1.2.局部变量

局部变量能够拥有特定数据类型，有一定的作用域，一般用于充当计数器计算或控制循环执行次数，或者用于保存数据值。局部变量前只有1个@符，用DECLARE语句声明局部变量。

USE test
DECLARE @StudentId varchar(20)
SET @StudentId=(
SELECT Student.stu_no
FROM Student
WHERE stu_enter_score='603')
SELECT @StudentId AS 入学分数为603的学生学号
GO

结果如图所示

2.运算符

2.1.算数运算符

在SQL Server 2008中，算数运算包括加（+）减（-）乘（*）除（/）取模（%）。举一个简单的例子。
示例1：在Student表中添加一列，列名为stu_age，根据Student表的stu_birthday列计算stu_age列并插入数据。（演示插入整列数据的方法）
Student表数据如图所示

执行下面的语句

ALTER TABLE Student
ADD stu_age int;--在Student表中添加stu_age列
CREATE TABLE #agetemp(stu_no varchar(8),age int);--新建一个临时表
INSERT INTO #agetemp(stu_no,age)--在临时表中插入学号和计算出来的年龄
SELECT Student.stu_no,YEAR(GETDATE())-YEAR(stu_birthday)--利用函数和运算符计算年龄
FROM Student;
UPDATE Student
SET Student.stu_age=#agetemp.age--将临时表中的age列数据整个复制到Student表的stu_age列
FROM #agetemp
WHERE Student.stu_no=#agetemp.stu_no--条件是两个表的stu_no列值相等
GO
SELECT * FROM Student

结果如图所示

2.2.赋值运算符

即等号（=），将表达式的值赋予另一个变量。举一个简单的例子。
示例2：计算Student表中学生的平均入学成绩并打印。
Student表的数据如图所示，stu_enter_score列存放了学生的入学成绩

执行下面的语句

DECLARE @average int--声明@average变量
SET @average=(--将计算出的平均值赋值给@average
SELECT AVG(stu_enter_score)
FROM Student)
PRINT @average--打印@average的值

结果如图所示

2.3.位运算符

位运算符包括与运算（&），或运算（|）和异或运算（^），可以对两个表达式进行位操作，这两个表达式可以是整型数据或二进制数据。Transact-SQL首先把整型数据转换为二进制数据，然后按位运算。举个简单的例子。
示例3：声明2个int型变量@num1，@num2，对这两个赋值且做与或异或运算。
执行下面的语句

DECLARE @num1 int,@num2 int
SET @num1=5 
SET @num2=6
SELECT @num1&@num2 AS 与,
@num1|@num2 AS 或,
@num1^@num2 AS 异或

结果如图所示

扩展示例4：写一个十进制转换为二进制的函数

CREATE FUNCTION Bin_con_dec(@dec int)--定义十进制转换为二进制函数
RETURNS varchar(20)
AS
BEGIN
DECLARE @quo int,@remainder varchar(20),@quo1 int
SET @quo=@dec
SET @remainder=''
WHILE @quo<>0
BEGIN
SET @quo1=@quo/2
SET @remainder=CAST(@quo%2 AS varchar(20))+@remainder
SET @quo=@quo1
END
RETURN @remainder
END

执行上面的函数后，运行下列语句验证函数正确性

PRINT dbo.Bin_con_dec(42)

结果为101010，函数定义正确。

2.4.比较运算符

也称关系运算符，用于比较两个值的关系，常见的有等于（=），大于（>），小于（<），大于等于（>=），小于等于（<=），不等于（<>或！=）
示例5：从Student表中查询入学成绩在平均分以上的学生信息
Student表的数据如图所示

执行下列语句

DECLARE @ave int
SET @ave=(SELECT AVG(stu_enter_score) FROM Student)
SELECT *FROM Student
WHERE stu_enter_score>=@ave;

结果如下图所示

注：不能直接把代码写成下面的形式

SELECT * FROM Student
WHERE stu_enter_score>=AVG(stu_enter_score)

消息147，级别15，状态1，第2 行
聚合不应出现在WHERE 子句中，除非该聚合位于HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

因为AVG是聚合函数。

2.5.逻辑运算符

逻辑运算符的作用是对条件进行测试。ALL,AND,ANY,BETWEEN,EXISTS,IN,LIKE,NOT,ALL,SOME。下面用SOME来举例。SOME的作用是如果在一组比较中，有些为true那就为true。
示例6：查询Student表中是否存在入学成绩高于平均分的学生，如果存在，输出true，不存在输出false。
Student表的stu_enter_score列（入学成绩）数据如图所示

执行下面的语句

USE test
IF (SELECT AVG(stu_enter_score) FROM Student)<=SOME(SELECT stu_enter_score FROM Student)
PRINT 'true'
ELSE
PRINT 'false'
GO

结果如图所示

2.6.连接运算符

加号（+）是字符串连接运算符，可以用它把字符串串连起来，在示例4的十进制转二进制函数中，就用上了加号。
示例7：将Student表的stu_name列和stu_enter_score列放在同一列显示，列名为score
Student表的数据如图所示

执行下列语句

SELECT stu_name+CAST(stu_enter_score AS VARCHAR(3)) AS score FROM Student

执行结果如图所示

注：stu_enter_score列数据类型为int，加号只对字符串类型数据有效，因此要用CAST函数将stu_enter_score的数据类型转换为varchar(3)，这样才能实现字符串拼接。

2.7.一元运算符

一元运算符只对一个表达式执行操作，该表达式可以是数字数据类型中的任何一种数据类型。SQL Server 2008提供的一元运算符包含正（+），负（-），位反（~）。
示例8：声明一个int数据类型变量@num并赋值，对该变量做正负位反操作。
执行下列语句

DECLARE @num INT
SET @num=45
SELECT +@num AS 正,-@num AS 负,~@num AS 位反
GO

结果如图所示

注：位反操作符用于取一个数的补数，只能用于整数。

2.8.运算符的优先级

优先级	运算符
1	~（位反）
2	*（乘），/（除），%（取模）
3	+（正），-（负），+（加），+（连接），-（减），&（位与）
4	=，>，<，>=，<=，<>，!=，!>，!<（比较运算符）
5	^（位异或），位或(符号打不出来，前面有，自己翻)
6	NOT
7	AND
8	ALL，ANY，BETWEEN，IN，LIKE，ALL，SOME
9	=（赋值）

当表达式中的运算符有相同的优先级时，按照它们在表达式中的位置，一元运算符按从右往左运算，二元运算符（对两个表达式作用的运算符）按从左往右运算。
示例9：验证运算符优先级
执行下列语句

DECLARE @result INT,@num INT
SET @num=45
SET @result=@num+(~@num)*4-@num/(~@num)
SELECT @result AS result
GO

结果如图所示

计算代码中的表达式
@result=@num+(~@num)4-@num/(~@num)
=@num+(-46)4-@num/(-46)
=45+(-46)4-45/(-46)
=45+(-46)4
=-139

3.控制语句

3.1.BEGIN END语句块

BEGIN END可以定义SQL Server语句块，使这些语句作为一组语句执行，允许语句嵌套。举例请见示例4

3.2.IF ELSE语句块

用于指定T-SQL语句的执行条件，若条件为真，则执行条件表达式后面的语句，条件为假时，可以试用ELSE关键字指定要执行的T-SQL语句。举例请见示例4。

3.3.CASE分支语句

示例10：将Student表的学生，性别和籍贯打印出来，要求籍贯只能显示省内，省外或自治区。
Student表的数据如图所示

执行下列语句

SELECT stu_name AS 姓名,stu_sex AS 性别,
(CASE stu_native_place
WHEN '浙江' THEN '省内'
WHEN '内蒙古' THEN '自治区'
WHEN '*' THEN '自治区'
WHEN '宁夏' THEN '自治区'
WHEN '*' THEN '自治区'
WHEN '广西' THEN '自治区'
ELSE '省外'
END) AS 籍贯 
FROM Student

结果如图所示

3.4.WHILE语句

用于设置重复执行T-SQL语句或语句块的条件。
示例11：用“*”在屏幕上输出一个宽度为9的菱形。
执行下列语句

DECLARE @width int,@j int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
WHILE @j<=@width
BEGIN
PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)--SPACE函数打印n个空字符，REPLICATE打印n个特定字符串
SET @j=@j+2
END
SET @j=@width-2
WHILE @j>0
BEGIN
PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)
SET @j=@j-2
END

结果如图所示

3.5.WAITFOR延迟语句

WAITFOR延迟语句可以让在它之后的语句在一个指定的时刻或是时间间隔后执行，可以悬挂起批处理，存储过程或事务的执行。
示例12：在某个时间点查询Student表学号为20180101的学生信息

BEGIN
WAITFOR TIME '15:03'--在15点03分查询
SELECT * FROM Student
WHERE stu_no='20180101'
END

示例13：在3分钟后查询Student表学号为20180102的学生信息

BEGIN
WAITFOR DELAY '00:03'--在3分钟后查询
SELECT * FROM Student
WHERE stu_no='20180102'
END

3.6.RETURN无条件退出语句

该语句表示无条件终止查询，批处理或存储过程的执行。存储过程和批处理RETURN语句后面的语句都不再执行。当在存储过程中使用该语句时，可以指定返回给调用应用程序、批处理或过程的整数值。如果RETURN未指定返回值，则存储过程的返回值是0

3.7.GOTO跳转语句

该语句使T-SQL批处理的执行跳转至指定标签。由于该语句破坏结构化语句的结构，尽量少用
示例13：将GOTO作为分支机制
执行下面语句

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 10  
BEGIN   
    SELECT @Counter  
    SET @Counter = @Counter + 1  
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.  
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.  
END  
Branch_One:  
    SELECT 'Jumping To Branch One.'  
    GOTO Branch_Three; --This will prevent Branch_Two from executing.  
Branch_Two:  
    SELECT 'Jumping To Branch Two.'  
Branch_Three:  
SELECT 'Jumping To Branch Three.';

结果如图所示

当Counter=4时，执行GOTO语句输出Branch One，执行完这个语句之后就打破了WHILE循环，接着执行Branch_One语句中的GOTO，输出Branch Three，结束。

注：在WHILE循环中使用GOTO会打破循环。

示例14：用GOTO语句实现示例11中打印菱形的功能
执行下列语句

DECLARE @width int,@j int,@i int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
SET @i=1--@i表示下一行打印第i行
Set3:PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)--SPACE函数打印n个空字符，REPLICATE打印n个特定字符串
SET @i=@i+1
IF @i<=(@width+1)/2
GOTO Set1
ELSE
GOTO Set2
Set1:
SET @j=@j+2
GOTO Set3
Set2:
SET @j=@j-2
IF @j>=1
GOTO Set3

结果如图所示

3.8.TRY CATCH错误处理语句

如果TRY块内部发生错误，会将控制传递给CATCH块内的语句组。TRY CATCH构造捕捉所有严重级别大于10但不会终止数据库连接的错误。
示例15：TRY CATCH的示例
执行下列语句

BEGIN TRY
SELECT * FROM Student
SELECT 120/0 FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

执行结果如图所示

语句中3个select语句全部都执行了。如果把报错的select语句放到正常的select语句前面，正常的select语句还能不能执行呢？执行下列语句

BEGIN TRY
SELECT 120/0 FROM Student
SELECT * FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

结果如图所示

正常select语句无法执行。TRY CATCH语句的逻辑是，一旦TRY语句块中出现问题语句，立刻跳转到CATCH语句块，TRY语句块接下去的语句不再执行。

4.常用函数

4.1.数据类型转换函数

默认情况下SQL Server会对一些数据类型进行自动转换，这种转换称为隐式转换。遇到无法自动转换，则需要用CAST()函数和CONVERT()函数转换，这种转换称为显式转换。CAST()函数和CONVERT()函数的功能是相同的，CAST函数更容易使用，CONVERT函数的优点是可以指定日期和数值格式。
示例16：将Student表中的学号转换为日期格式
下面两句语句的功能是一样的，执行下列语句

SELECT stu_name,CAST(stu_no AS DATE) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student
SELECT stu_name,CONVERT(DATE,stu_no) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student

结果如图所示

示例17：用CONVERT()函数将stu_birthday转化成指定格式的日期
执行下列语句

SELECT stu_name,CONVERT(VARCHAR(20),stu_birthday,101) FROM Student
--CONVERT函数将DATE类型的stu_birthday字段转化为字符串，并限定了样式，代码101

结果如图所示

注：在上述代码中，CONVERT(DATE,stu_birthday,101)这么写是没用的。101格式码只对日期格式转化为字符串有效，其他格式转化为日期格式是无效的。

其他常用函数太简单了这边不写了，略。