【SQL server速成之路】T-SQL语言（一）

🎉个人主页：这个昵称我想了20分钟
✨往期专栏：【速成之路】jQuery

---

🎖️专栏：【速成之路】SQL server
🔓往期回顾：
【SQL server速成之路】数据库基础
【SQL server速成之路】数据库和表（一）
【SQL server速成之路】数据库和表（二）
【SQL server】速成之路 数据库的查询
【SQL server速成之路】数据库的视图和游标

🎆常量、变量与数据类型

常量

  1．字符串常量
分为ASCII字符串常量和Unicode 字符串常量。
  （1）ASCII字符串常量。ASCII字符串常量是用单引号括起来，由ASCII字符构成的符号串。
  ASCII字符串常量举例：

'China'
'How do you!'
'O''Bbaar'
/*如果单引号中的字符串包含引号，可以使用两个单引号表示嵌入的单引号。*/

  （2）Unicode 字符串常量。Unicode 字符串常量与ASCII字符串常量相似，但它前面有一个 N 标识符（N 代表 SQL-92 标准中的国际语言)（National Language）。N 前缀必须大写。
  Unicode 字符串常量举例：

N'China '
N'How do you!

  Unicode 数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。
  2．整型常量
  按照整型常量的不同表示方式，又分为二进制整型常量、十六进制整型常量=和十进制整型常量。
  （1）十六进制整型常量的表示
前辍 0x 后跟十六进制数字串表示。例如：0xEBF、0x12Ff、0x69048AEFDD010E、0x 。/0x空十六进制常量/
  （2）二进制整型常量的表示
即数字0 或1，并且不使用引号。如果使用一个大于 1 的数字，它将被转换为1。
  （3）十进制整型常量
即不带小数点的十进制数，例如：1 894、2、+145 345 234、-2 147 483 648
  3．实型常量
  实型常量有定点表示和浮点表示两种方式。举例如下：
定点表示：

1894.1204
2.0
+145345234.2234
-2147483648.10

浮点表示：

101.5E5
0.5E-2
+123E-3
-12E5

  4．日期时间常量
  日期时间常量：用单引号将表示日期时间的字符串括起来构成。SQL Server可以识别如下格式的日期和时间：

字母日期格式，例如：‘April 20, 2000’
数字日期格式，例如：‘4/15/1998’、‘1998-04-15’
未分隔的字符串格式，例如：‘20001207’

  如下是时间常量的例子：

'14:30:24'
'04:24:PM'

  如下是日期时间常量的例子：

'April 20, 2000 14:30:24'

  5．money常量
  money常量是以==“$”==作为前缀的一个整型或实型常量数据。下面是money常量的例子：

$12
$542023
-$45.56
+$423456.99

  6．uniqueidentifier常量
  uniqueidentifier 常量是用于表示全局唯一标识符（GUID）值的字符串。可以使用字符串或十六进制字符串格式指定。例如：

'6F9619FF-8A86-D011-B42D-00004FC964FF'
0xff19966f868b11d0b42d00c04fc964ff

数据类型

  1．系统数据类型
  系统数据类型又称为基本数据类型。

  2．用户自定义数据类型
  用户自定义数据类型可看做是系统数据类型的别名。

  例如：在图书借阅系统中xsbook数据库，创建了xs、book、jy三个表，从三个表的表结构可看出：表xs中的借书证号字段值与表jy中的借书证号字段值应有相同的类型，均为字符型值、长度可定义为8，并且不允许为空值，为了使用方便，并使含义明确，可以先定义一数据类型，命名为：library_card_num，用于描述借书证号的类型属性，然后将表xs中的借书证号字段和表jy中的借书证号字段定义为library_card_num数据类型。
自定义数据类型library_card_num后，重新设计xsbook数据库中xs、jy表结构中的借书证号字段，如表5.4~表5.6所示。



  创建用户自定义数据类型时应考虑如下三个属性：

• 数据类型名称；
• 新数据类型所依据的系统数据类型（又称为基类型）；
• 为空性。

创建用户自定义数据类型的方法如下：
  （1）使用“对象资源管理器”定义。步骤如下：

  ① 启动“SQL Server Management Studio”→在“对象资源管理器”中展开“数据库”→“xsbook”→“可编程性”→右击“类型”，选择“新建”选项，再选择“新建用户定义数据类型”，弹出“新建用户定义数据类型”窗口。
  ② 在“名称”文本框中输入自定义的数据类型名称，如Library_card_num。在“数据类型”下拉框中选择自定义数据类型所基于的系统数据类型char。在“长度”栏中填写要定义的数据类型的长度8。其他选项使用默认值，如图5.1所示，单击“确定”按钮即可完成创建。

  （2）使用CREATE TYPE命令定义。在SQL Server 2012中，使用CRETAE TYPE语句来实现用户数据类型的定义。语法格式：

CREATE TYPE [<数据库架构名>. ] <自定义类型名>
    FROM <系统数据类型名> [ ( <精度> [ , <小数位数> ] ) ]
    [ NULL | NOT NULL ] 
[ ; ]

  根据上述语法，定义描述借书证号字段的数据类型的语句如下：

CREATE TYPE library_card_num
	FROM char(8) NOT NULL

  （3）删除用户自定义数据类型。界面方式删除用户自定义数据类型的主要步骤如下：
在“对象资源管理器”中展开数据库“xsbook”→“可编程性”→“类型”→在“用户定义数据类型”中选择类型“dbo.library_card_num”，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”窗口后单击“确定”按钮即可（实际不做操作）。

  （4）使用命令删除用户自定义数据类型。使用命令方式删除自定义数据类型可以使用DROP TYPE语句。语法格式：

DROP TYPE [ <数据库架构名>. ] <自定义类型名>[ ; ]

  例如，删除前面定义的library_card_num类型的语句为：

DROP TYPE library_card_num

  （5）利用用户自定义数据类型定义字段。

  在定义类型后，接着应考虑定义这种类型的字段，同样可以利用“对象资源管理器”和“T-SQL命令”两种方式实现。
利用命令方式定义xs表结构如下：

CREATE TABLE xs
( 	
	借书证号 library_card_num  NOT NULL PRIMARY KEY,	/*定义为library_card_num类型*/
  	姓名 	char(8) 	NOT NULL,
  	性别 	bit 		NOT NULL DEFAULT 1,
  	出生时间 	date 		NOT NULL,
  	专业 	char(12) 	NOT NULL,
  	借书量 	int 		NOT NULL,
  	照片 	varbinary(MAX)	 NULL
)
GO

  3．用户自定义表数据类型

  SQL Server 2012还提供了一种新的用户自定义数据类型，称为用户自定义表数据类型（User-defined Table Types）。这种数据类型也是由用户自行定义，可以作为参数提供给语句、存储过程或者函数。创建自定义表数据类型也使用CREATE TYPE语句，语法格式如下：

CREATE TYPE [ <数据库架构名>. ] <自定义类型名>
  AS TABLE ( <column_definition>
        [ <table_constraint> ] [ ,...n ] )  
[ ; ]

【例】 创建用户自定义表数据类型，包含jy表中的所有列。

CREATE TYPE jy_tabletype
	AS TABLE
	(
		借书证号char(8)	NOT NULL,
		ISBN 	char(18)	NOT NULL,
		索书号	char(10)	NOT NULL PRIMARY KEY,
		借书时间date
	)

变量

  1．变量
  变量名必须是一个合法的标识符。

（1）标识符
在SQL Server中标识符分为两类：

① 常规标识符。
② 分隔标识符。

（2）变量的分类
SQL Server中变量可分为两类：

① 全局变量。
② 局部变量。

2．局部变量的使用

  （1）局部变量的定义与赋值

  ① 局部变量的定义
  在批处理或过程中用 DECLARE 语句声明局部变量，所有局部变量在声明后均初始化为NULL。
语法格式：

DECLARE { @<局部变量名> <数据类型> [= <变量值>]}  [ ,...n]

  ② 局部变量的赋值
  定义局部变量后，可用SET或SELECT语句给其赋值。
  用SET语句赋值，语法格式：

SET  @<局部变量名>＝<值>

【例】 创建局部变量@var1、@var2，并赋值，然后输出变量的值。

DECLARE @var1 char(10), @var2 char(30)
SET  @var1='中国'                   /*一个SET语句只能给一个变量赋值*/
SET  @var2=@var1+ '是一个伟大的国家'
SELECT @var1, @var2

语句的执行结果如图所示。

【例】 创建一个名为sex的局部变量，并在 SELECT 语句中使用该局部变量查找表xs中所有男同学的借书证号、姓名。

DECLARE @sex bit
SET @sex=1
SELECT 借书证号,姓名
	FROM xs
	WHERE 性别=@sex

【例】 将查询结果赋给变量。

DECLARE @student char(8)
SET @student=(SELECT 姓名 FROM xs WHERE 借书证号= '131101')
SELECT @student

用SELECT语句赋值，语法格式：

SELECT {@<局部变量名>=<值>} [,…n]

使用SELECT语句赋值时局部变量可以是出cursor、text、ntext、image外的任何类型变量。值可以是任何有效的SQL表达式，包含标量子查询。

【例】 使用SELECT给局部变量赋值。

DECLARE @var1 nvarchar(30)
SELECT @var1 ='刘丰'
SELECT  @var1 AS 'NAME'

【例】 给局部变量赋空值。

DECLARE @var1 nvarchar(30)
SELECT @var1 = '刘丰'
SELECT @var1 =
(
	SELECT 姓名
		FROM xs
		WHERE 借书证号= '131101'	
)
SELECT @var1 AS  'NAME'

  （2）局部游标变量的定义与赋值
  ① 局部游标变量的定义。语法格式：

DECLARE { @<游标变量名> CURSOR } [ ,...n]

  CURSOR表示表示该变量是游标变量。

  ② 局部游标变量的赋值。利用SET语句给一个游标变量赋值，有三种情况：

• 将一个已存在的并且赋值的游标变量的值赋给另一局部游标变量；
• 将一个已申明的游标名赋给指定的局部游标变量；
• 申明一个游标，同时将其赋给指定的局部游标变量。

  ③ 游标变量的使用步骤
  定义游标变量→给游标变量赋值→打开游标→利用游标读取行（记录）→使用结束后关闭游标→删除游标的引用。

【例】 使用游标变量

DECLARE @CursorVar CURSOR                 	/*定义游标变量*/
SET @CursorVar = CURSOR SCROLL DYNAMIC  	/*给游标变量赋值*/
FOR
	SELECT 借书证号, 姓名
		FROM xs
		WHERE 借书证号 LIKE '20%'
OPEN @CursorVar                           	 		/*打开游标*/
FETCH NEXT FROM @CursorVar
WHILE @@FETCH_STATUS = 0
BEGIN
    	FETCH NEXT FROM @CursorVar          	/*通过游标读行记录*/
END
CLOSE @CursorVar
DEALLOCATE @CursorVar                  		/*删除对游标的引用*/

  （3）表数据类型变量的定义与赋值
  语法格式：

DECLARE 
   { @table_variable_name [AS] TABLE ( { <column_definition> | <table_constraint> } [ ,... ] ) }

  【例】 声明一个表数据类型变量并向变量中插入数据。

DECLARE @var_table 
	AS TABLE
		(
			num char(8) NOT NULL PRIMARY KEY,
			name char(8) NOT NULL,
			sex bit NULL
		)				/*声明变量*/
INSERT INTO @var_table 
	SELECT 借书证号,姓名,性别 FROM xs	/*插入数据*/
SELECT * FROM @var_table			/*查看内容*/