SQL Server 总结

实体：是指现实世界中具有区分其他事物的特征或属性并与其他实体有联系的实体。
属性：可以理解为实体的特征。
联系：是两个或多个实体之间的关联关系。
映射基数：标识通过联系与该实体关联的其他实体的个数。
一对一：X中的一个实体最多与Y中的一个实体关联，并且Y中的一个实体最多与X中的一个实体关联。
一对多：X中的一个实体可以与Y中的任意数量的实体关联，Y中的一个实体最多与X中的一个实体关联。
多对一：X中的一个实体最多与Y中的一个实体关联，Y中的一个实体可以与X中的任意数量的实体关联。
多对多：X中的一个实体可以与Y中的任意数量的实体关联，反之亦然。
实体关系图。
矩形标识实体集。
椭圆形标识属性。
菱形标识联系集。
直线用来链接属性和实体集，也可以用来链接实体集和联系集。

三：绘制数据库模型图

新建数据库模型图：打开Microsoft Visio依次点击“文件”、“新建”、“数据库”、“数据库模型图”选项。
添加实体：在绘图窗口左侧的实体关系中选择实体并将其拖动到页面的适当位置，在“数据库属性”中定义数据表的物理名称及概念名称。
添加数据列及相应的属性：在“数据库属性”中选择类别为“列”，添加列、数据类型和注释等。
添加实体之间的映射关系：单击左侧实体关系中的“连接线”工具，将“连接线”工具放在表的中信上，使表的四周出现方框，并拖动到另一个表的中心。

四：规范设计

第一范式：第一范式的目标是确保每列的原子性。如果每列或每个属性都是不可再分的最小数据单元，则满足第一范式。
第二范式：在第一范式的基础上更进一层，其目标是确保表中的每列和主键相关。如果满足第一范式，并且除了主键意外的其他列都依赖于该主键，则满足第二范式。
第三范式：在第二范式的基础上更进一层，其目标是确保每列都和主键列直接相关，而不是间接相关。如果满足第二范式，并且除了主键意外的其他列都智能依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。

二：数据库的实现

一：数据库文件的组成

每个数据库至少要包含两个文件：一个数据文件和一个日志文件。

数据文件：包含了数据库的数据和对象、表、视图和索引等。
日志文件：包含了用于恢复数据库所需的信息。

数据库的文件

主数据库文件：*.mdf。
次数据库文件：*.ndf。
日志文件：*.ldf。

二：创建和删除数据库语法

一：创建数据库语法

--创建数据库  
CREATE DATABASE 数据库名称  
    ON [PRIMARY](  
        <数据文件参数> [,......n] [<文件组参数>]  
    )  
    [LOG ON](  
        {<日志文件参数> [,......n]}  
    )  
    --数据文件的具体参数：  
    ([NAME=逻辑文件名],  
    FILENAME=物理文件路径名称,  
    [SIZE=大小],  
    [MAXSIZE={最大容量|UNLIMITED}],  
    [FILEGROWTH=增长量]  
    )[,......n]  
    --文件组的具体参数：  
    FILEGROUP 文件组名称 <文件参数> [,......n]

其中“[]”表示可选的部分，“{}”表示必须部分。

各参数含义如下：

数据库名：数据库的名称，最长为128个字符。

PRIMARY：该选项是一个关键字，指定主文件组中的文件。

LOG ON：指明事务日志文件的明确定义。

NAME：指定数据库的逻辑名称，这是在SQL Server中使用的名称，是数据库在SQL Server中的标识符。

FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。

SIZE：指定数据库的初始容量大小。

MAXSIZE：指定操作系统文件可以增长到的最大值。

FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。

二：删除数据库语法

--删除数据库
DROP DATABASE 数据库名称

--例如：
DROP DATABASE MySchoolDB

三：SQL Server中的数据类型

SQL Server中的数据类型
类型	数据类型	描述	数据范围	用途
整型	int	占用4个字节的整数	允许从 -2,147,483,648 到 2,147,483,647 的所有数字	存储到数据库的几乎所有数值型的数据都可以使用这种数据类型
	smallint	占用2个字节的整数	允许从 -32,768 到 32,767 的所有数字	对存储一些常限定在特定范围内的数值型非常有用
	tinyint	占用1个字节的整数	允许从 0 到 255 的所有数字	在存储有限数目的的数值时很有用
浮点型	real	近似数值类型	从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据	一种近似数值类型，供浮点数使用
	float(n)	近似数值类型	从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53	一种近似数值类型，供浮点数使用
	decimal(p,s)	固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。范围时小数点左右所能存储的数字的总位数，精度时小数点右边存储的数字的位数	固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0	通常存储要求精度比较高的数据，如货币、金额等
	numeric	numeric数据类型与decimal类型相同
字符型	char(n)	固定长度非Unicode字符数据，最大8000个字节	字母、符号占用一个字节的存储空间。汉字、繁体字等占用两个字节的存储空间。n表示所有字符所占的存储空间，取值为1~8000。如不指定n的值，系统默认n的值为1。若输入数据的字符串长度小于n，则系统自动在其后添加空格来填满设定好的n；若输入的数据过长，则会截掉其超出部分	常用于存储固定长度的少量文本，如身份证号码、电话号码、手机号码等非中文字符。字母、符号占用一个字节的存储空间。汉字、繁体字等占用两个字节的存储空间
	varchar(n)	可变长度非Unicode字符数据，有两种形式：varchar(n)或varchar(max)	字母、符号、汉字、繁体等占用一个字节的存储空间。n为存储字符的最大长度，取值范围是1~8000，但可根据实际存储的字符数改变存储空间。输入数据的长度可以为0个字符。若输入数据的字符串长度小于n，则按实际数据存储；若输入的数据过长，则会截掉其超出部分	常用于存储长度不一的少量文本，如账号、密码、昵称、地址、邮箱等包含中文的字符。字母、符号、汉字、繁体等占用一个字节的存储空间
	varchar(max)	可变长度非Unicode字符数据，最多 1,073,741,824 个字符。	字母、符号、汉字、繁体等占用一个字节的存储空间。n为存储字符的最大长度，取值范围是1~1,073,741,824，但可根据实际存储的字符数改变存储空间。max表示最大存储大小是2的31次方-1个字节。输入的数据按实际数据存储	常用于存储长度较多的文本，如版权声明、合约、协议等包含中文的字符。字母、符号、汉字、繁体等占用一个字节的存储空间
	text	非Unicode字符数据	可变长度的非Unicode字符数据。最多 2GB 字符数据	常用于存储长度较多的文本，如版权声明、合约、协议等包含中文的字符
Unicode型	nchar	固定长度Unicode字符数据，最大4000个字节	固定长度的 Unicode 数据。最多 4,000 个字符。字母、符号、汉字、繁体等占用两个字节的存储空间。n表示所有字符所占的存储空间，取值为1~4000。如不指定n的值，系统默认n的值为1。若输入数据的字符串长度小于n，则系统自动在其后添加空格来填满设定好的n；若输入的数据过长，则会截掉其超出部分	常用于存储固定长度的少量文本，如身份证号码、电话号码、手机号码等非中文字符。字母、符号、汉字、繁体等占用两个字节的存储空间
	nvarchar(n)	可变长度非Unicode字符数据。有两种形式：varchar(n)或varchar(max)	可变长度的 Unicode 数据。最多 4,000 个字符。字母、符号、汉字、繁体等占用两个字节的存储空间。n为存储字符的最大长度，取值范围是1~4000，但可根据实际存储的字符数改变存储空间。输入数据的长度可以为0个字符。若输入数据的字符串长度小于n，则按实际数据存储；若输入的数据过长，则会截掉其超出部分	常用于存储长度不一的少量文本，如账号、密码、昵称、地址、邮箱等包含中文的字符。字母、符号、汉字、繁体等占用两个字节的存储空间
	nvarchar(max)	可变长度的 Unicode 数据	最多 536,870,912 个字符。字母、符号、汉字、繁体等占用一个字节的存储空间。n为存储字符的最大长度，取值范围是1~536,870,912，但可根据实际存储的字符数改变存储空间。max表示最大存储大小是2的31次方-1个字节。输入的数据按实际数据存储	常用于存储长度较多的文本，如版权声明、合约、协议等包含中文的字符。字母、符号、汉字、繁体等占用两个字节的存储空间
	ntext	非Unicode字符数据	可变长度的 Unicode 数据。最多 2GB 字符数据	常用于存储长度较多的文本，如版权声明、合约、协议等包含中文的字符
是/否型	bit	只能是0、1或空值		表示是/否值
二进制型	binary	定长的二进制数据	最多 8,000 字节	当输入表的内容接近相同的长度时，应该使用这种数据类型
	varbinary(n)	变长的二进制数据	最多 8,000 字节	当输入表的内容大小可变时，应该使用这种类型数据
	varbinary(max)	变长的二进制数据	最多 2GB 字节	当输入表的内容大小可变时，应该使用这种类型数据
	image	变长的二进制数据	最多 2GB	常用于存储图片、声音等。
货币型	money	固定精度和范围的数值型数据	介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据	常用于存储金额
货币型	smallmoney	同money，但小于money的取值范围	介于 -214,748.3648 和 214,748.3647 之间的货币数据	常用于存储金额
日期时间型	datetime	表示日期和时间，可以精确到11300秒或3.33毫秒	从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为 3.33 毫秒
	datetime2	同datetime，精确到100纳秒	从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为 100 纳秒
	smalldatetime	同datetime，精确到60秒	从 1900 年 1 月 1 日到 2079 年 6 月 6 日，精度为 1 分钟
	date	仅存储日期	从 0001 年 1 月 1 日到 9999 年 12 月 31 日
	time	仅存储时间	精度为 100 纳秒
特殊类型	timestamp	创建一个数据库范围内的唯一时间戳	存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量	一个表中只能有一个timestamp列，在一个数据库中，timestamp值时唯一的
特殊类型	uniqueidentifier	一个全局的唯一标识符	存储全局标识符 (GUID)

四：创建和删除表语法

一：创建表语法

--创建表
CREATE TABLE 表名(
    列1 数据类型 列的特征,
    列2 数据类型 列的特征,
    ... ...     ...
)

其中“列的特征”包括该列是否为空、是否为标识列、是否有默认值及是否为主键等。

二：删除表语法

--删除表
DROP TABLE 表名

--例如：
DROP TABLE Student

五：创建和删除约束语法

数据完整性时指数据的正确性和相容性。

实体完整性：唯一确定表中一行记录。
域完整性：表中特定列或数据的有效性，以确保不会输入无效的数值。
引用完整性：插入或删除记录时，维护表之间定义的关系。
自定义完整性：定义不属于以上三种完整性的特定业务规则。

为了确保数据库的数据完整性，可以使用约束，常用的约束类型如下：

主键约束(Primary Key Constraint)：要求主键列数据唯一，并且不允许为空。
非空约束(Not Null)：要求该列不能存在空值。
唯一约束(Unique Constraint)：要求该列的值必须唯一，允许为空。
检查约束(Check Constraint)：要求该列的取值范围限制、格式限制等。
默认约束(Default Constraint)：要求该列的默认值。
外键约束(Foreign Key Constraint)：用于在两表之间建立关系，需要指定引用主表的哪一列。

一：创建约束语法

--添加约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名称 约束类型 具体的约束说明

例如：为学号列（StudentNo）添加主键约束，约束名推荐取名为“PK_StudentNo”。
例如：为身份证号码列（IdentityCard）添加唯一约束，约束名推荐取名为“UQ_IdentityCard”。
例如：为地址列（Address）添加默认约束，约束名推荐取名为“DF_Address”。
例如：为出生日期列（BornDate）添加检查约束，约束名推荐取名为“CK_BornDate”。
例如：为成绩表（Result）添加外键约束，约束名推荐取名为“FK_StudentNo”。

二：删除约束语法

--删除约束
ALTER TABLE 表名
DROP CONSTRAINT 约束名称

--例如
ALTER TABLE Student
DROP CONSTRAINT DF_Address

六：完整创建数据库、数据表并添加相关约束

------------------- 创建数据库 Start -------------------
--设置当前数据库为Master访问Sysdatabases表
USE Master
GO
--判断系统中是否存在该数据库
IF EXISTS(Select * From Sysdatabases Where Name='MySchoolDB')
	--如果存在该数据库就删除
	DROP DATABASE MySchoolDB
GO
--创建数据库 MySchoolDB
CREATE DATABASE MySchoolDB
	--默认属于PRIMARY主文件组
	ON PRIMARY(
		--主数据文件的逻辑名称
		NAME='MySchoolDB_Data',
		--主数据文件的物理路径
		FILENAME='D:\DevelopMent\Microsoft SQL Server\Backup DataBase\MySchoolDB_Data.mdf',
		--主数据文件的初始大小
		SIZE=10MB,
		--主数据文件的增长率
		FILEGROWTH=10MB
	)
	LOG ON(
		--日志文件的逻辑名称
		NAME='MySchoolDB_Log',
		--日志文件的物理路径
		FILENAME='D:\DevelopMent\Microsoft SQL Server\Backup DataBase\MySchoolDB_Log.ldf',
		--日志文件的初始大小
		SIZE=10MB,
		--日志文件的最大大小
		MAXSIZE=50MB,
		--日志文件的增长率
		FILEGROWTH=10MB
	)
GO
------------------- 创建数据库 End -------------------
------------------- 创建数据表 Start -------------------
--设置当前数据库为MySchoolDB
USE MySchoolDB
GO
--判断数据库中是否存在该数据表
IF EXISTS(Select * From Sysobjects Where Name='School_User')
	--如果存在该数据表就删除
	DROP TABLE School_User
GO
--创建用户表 School_User
CREATE TABLE School_User(
	--用户编号，非空，主键，每次自增1
	User_UserId int NOT NULL IDENTITY(1,1),
	--用户昵称，非空
	User_UserName Varchar(12) NOT NULL,
	--用户账号，非空
	User_LoginId Varchar(16) NOT NULL,
	--用户密码，非空
	User_LoginPwd Varchar(32) NOT NULL,
	--用户邮箱，非空
	User_UserEmail Varchar(30) NOT NULL,
	--登录时间，非空
	User_LoginTime DateTime NOT NULL,
	--登录IP地址，非空
	User_LoginIP Varchar(15) NOT NULL,
	--用户性别，非空  0=False=女  1=True=男 默认值为1
	User_Sex Bit NOT NULL
)
GO
--判断数据库中是否存在该数据表
IF EXISTS(Select * From Sysobjects Where Name='School_Teacher')
	--如果存在该数据表就删除
	DROP TABLE School_Teacher
GO
--创建教师表 School_Teacher
CREATE TABLE School_Teacher(
	--教师编号，非空，主键，每次自增1
	Teacher_TeacherId int NOT NULL IDENTITY(1,1),
	--教师昵称，非空
	Teacher_TeacherName Varchar(12) NOT NULL,
	--教师账号，非空
	Teacher_LoginId Varchar(16) NOT NULL,
	--教师密码，非空
	Teacher_LoginPwd Varchar(32) NOT NULL,
	--教师邮箱，非空
	Teacher_TeacherEmail Varchar(30) NOT NULL,
	--登录时间，非空
	Teacher_LoginTime DateTime NOT NULL,
	--登录IP地址，非空
	Teacher_LoginIP Varchar(15) NOT NULL,
	--教师性别，非空  0=False=女  1=True=男 默认值为1
	Teacher_Sex Bit NOT NULL
)
GO
--判断数据库中是否存在该数据表
IF EXISTS(Select * From Sysobjects Where Name='School_ClassRoom')
	--如果存在该数据表就删除
	DROP TABLE School_ClassRoom
GO
--创建教室表 School_ClassRoom
CREATE TABLE School_ClassRoom(
	--教室编号，非空，主键，每次自增1
	ClassRoom_ClassRoomId int NOT NULL IDENTITY(1,1),
	--学生编号，非空
	User_UserId int NOT NULL,
	--教师编号，非空
	Teacher_TeacherId int NOT NULL
)
GO
------------------- 创建表约束 Start -------------------
	--为用户表 School_User 添加约束
	--为用户编号添加主键约束
	ALTER TABLE School_User ADD CONSTRAINT PK_User_UserId PRIMARY KEY(User_UserId)
	--为用户昵称、账号、邮箱添加唯一约束
	ALTER TABLE School_User ADD CONSTRAINT UQ_User_UserName UNIQUE(User_UserName)
	ALTER TABLE School_User ADD CONSTRAINT UQ_User_LoginId UNIQUE(User_LoginId)
	ALTER TABLE School_User ADD CONSTRAINT UQ_User_UserEmail UNIQUE(User_UserEmail)
    --为用户邮箱添加检查约束
	ALTER TABLE School_User ADD CONSTRAINT CK_User_UserEmail CHECK(User_UserEmail LIKE '%@%')
	--为用户性别添加默认状态
	ALTER TABLE School_User ADD CONSTRAINT DF_User_Sex DEFAULT(1) FOR User_Sex 
    --为教师表 School_Teacher 添加约束
	--为教师编号添加主键约束
	ALTER TABLE School_Teacher ADD CONSTRAINT PK_Teacher_TeacherId PRIMARY KEY(Teacher_TeacherId)
	--为教师昵称、账号、邮箱添加唯一约束
	ALTER TABLE School_Teacher ADD CONSTRAINT UQ_Teacher_TeacherName UNIQUE(Teacher_TeacherName)
	ALTER TABLE School_Teacher ADD CONSTRAINT UQ_Teacher_LoginId UNIQUE(Teacher_LoginId)
	ALTER TABLE School_Teacher ADD CONSTRAINT UQ_UTeacher_TeacherEmail UNIQUE(Teacher_TeacherEmail)
    --为教师邮箱添加检查约束
	ALTER TABLE School_Teacher ADD CONSTRAINT CK_Teacher_TeacherEmail CHECK(Teacher_TeacherEmail LIKE '%@%')
	--为教师性别添加默认状态
	ALTER TABLE School_Teacher ADD CONSTRAINT DF_Teacher_Sex DEFAULT(0) FOR Teacher_Sex 
	--为用户编号添加外键约束
	ALTER TABLE School_ClassRoom ADD CONSTRAINT FK_ClassRoom_UserId FOREIGN KEY(User_UserId) REFERENCES School_User(User_UserId)
	--为教师编号添加外键约束
	ALTER TABLE School_ClassRoom ADD CONSTRAINT FK_ClassRoom_TeacherId FOREIGN KEY(Teacher_TeacherId) REFERENCES School_Teacher(Teacher_TeacherId)
------------------- 创建表约束 End -------------------

三：SQL编程

一：使用变量

变量时可以存储数据值的对象，可以使用局部变量向SQL语句传递数据。

变量分为局部变量和全局变量，使用方法如下：

局部变量：先声明、再赋值。
全局变量：由系统定义和维护，可以直接使用，但一般不会自定义全局变量。

一：局部变量

--声明局部变量
DECLARE @variable_name DateType
--其中，variable_name为局部变量的名称。DataType为数据类型。

--例如：
--声明存放姓名变量username
DECLARE @username varchar(8)
--声明一个存放学号的变量number
DECLARE @number int

--使用方法赋值
--声明一个变量并赋值
SET @variable_name = value
--或者
SELECT @variable_name = value

SET语句与SELECT语句的区别
	SET	SELECT
同时对多个变量赋值	不支持	支持
表达式返回多个值时	出错	将返回的最后一个值赋给变量
表达式未返回值时	变量被赋值为Null	变量保持原值

二：全局变量

全局变量
变量	含义
@@ERROR	最后一个T-SQL错误的错误号
@@IDENTITY	最后一次插入的标识值
@@LANGUAGE	当前使用的语言的名称
@@MAN_CONNECTIONS	可以创建的、同时链接的最大数目
@@ROWCOUNT	受上一个SQL语句影响的行数
@@SERVERNAME	本地服务器的名称
@@SERVICENAME	该计算机上的SQL服务的名称
@@TIMETICKS	当前计算机上每刻度的微秒数
@@TRANSCOUNT	当前连接打开的事务数
@@VERSION	SQL Server的版本信息

三：输出语句

--输出语句
PRINT 局部变量或字符串
--或者：
SELECT 局部变量 AS 自定义列名

二：类型数据转换

--显式类型转换
CAST(表达式 AS 数据类型)
CONVERT(数据类型[(长度)],表达式[,样式])

CAST()函数和CONVERT()函数用于将某种数据类型的表达式转换为另一种数据类型的表达式。与CAST()函数不同之处是，再将日期时间类型/浮点类型的数据转换为字符串数据时，CONVERT()函数可以通过第三个参数指定转换后的字符样式，不同的样式使转换后字符数据的显示格式不同。VONCERT()函数的第三个参数可以省略。

三：逻辑控制语句

常用的逻辑控制语句由以下几种：

顺序结构控制语句：BEGIN-END语句
分支结构控制语句：IF-ELSE语句和CASE-END语句
循环结构控制语句：WHILE语句

一：顺序结构控制语句：BEGIN-END语句

经常在分支结构语句和循环结构语句中出现，表示语句块的开始和结束。在一个语句块种可以包含另一个语句块。在BEGIN-END语句种，BEGIN和END应放置在新的一行。当BEGIN和END之间有多行SQL语句时，可以在END语句后添加注释。以便它们很好的匹配起来，提高代码可读性。

--顺序结构控制语句：BEGIN-END语句
BEGIN
    语句或语句块
END

二：分支结构控制语句：IF-ELSE语句和CASE-END语句

IF-ELSE语句属于分支结构语句，根据条件是否成立来确定程序执行方向。

CASE-END多分支语句计算一组条件表达式，并返回其中一个符合条件的结果。

--分支结构控制语句：IF-ELSE语句
IF(条件)
    语句或语句块1
ELSE
    语句或语句块2

--分支结构控制语句：IF-ELSE语句结合BEGIN-END语句使用
IF(条件)
    BEGIN
        语句或语句块1
        语句或语句块2
    END
ELSE
    BEGIN
        语句或语句块3
        语句或语句块4
    END


--分支结构控制语句：CASE语句
CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    [ELSE 其他结果]
END

三：循环结构控制语句：WHILE语句

WHILE循环语句可以根据某些条件重复执行一条SQL语句或一个语句块。通过使用WHILE关键字，可以确保只要指定的条件为True，就会重复执行语句或语句块，直至指定条件为False为止。在WHILE循环语句种可以使用CONTINUE和BREAK语句来控制语句的执行。

--循环结构控制语句：WHILE语句
WHILE(条件)
    BEGIN
        语句或语句块
        [BREAK | CONTINUE]
    END

四：批处理的GO指令

批处理指令时一条或多条SQL语句的集合，SQL Server将批处理编译成一个可执行单元，此单元称为执行计划。每个批处理可以编译成单个执行计划，从而提高执行效率。GO关键字标志着批处理的结束。

四：高级查询

一：子查询

--子查询语法
SELECT ... FROM 表1 WHERE 列1 > (子查询)

其中，子查询必须放在圆括号内，在列1后面除了“>”运算符外，还可以使用其他运算符号。外面的查询称为父查询，圆括号种嵌入的查询称为子查询。SQL Server执行时，先执行子查询部分，再求出子查询部分的值，再执行整个父查询，返回最后的结果。因为子查询作为WHHERE条件的一部分，所以还可以和UPDATE、INSERT、DELESTE一起使用，语法类似于SELECT语句。

二：IN和NOT IN子查询

--IN子查询语法
SELECT ... FROM 表1 WHERE 列1 IN (子查询)

--NOT IN子查询语法
SELECT ... FROM 表1 WHERE 列1 NOT IN (子查询)

使用=、>等比较运算符时，要求子查询只能返回一条或空的记录。在SQL Server种，当子查询跟随在=、!=、<、<=、>和>=之后，不允许子查询返回多条记录。

三：EXISTS和NOT EXISTS子查询

--EXISTS子查询语法
IF EXISTS(子查询)
    语句
ELSE
    语句


--NOT EXISTS子查询语法
IF NOT EXISTS(子查询)
    语句
ELSE
    语句

EXISTS关键字能够检测子查询语句。如果子查询的结果非空，则EXISTS子查询将返回True，否则返回False。NOT EXISTS子查询实现取反操作。

四：子查询的注意事项

--嵌套在SELECT语句的SELECT子句中的子查询
SELECT (子查询) FROM 表名
--子查询结果为单行单列，不必指定列别名

--嵌套在SELECT语句的FROM子句中的子查询
SELECT * FROM (子查询) AS 表别名
--必须为表指定别名

在完成较为复杂的数据查询时，经常会使用到子查询。编写子查询语句时，注意以下事项：

子查询语句可以嵌套在SQL语句种任何表达式出现的位置。在SELECT语句种，子查询可以被嵌套在SELECT语句的列、表和查询条件中，即SELECT子句、FROM子句、WHERE子句、GROUP BY子句和HAVING子句。
在子查询的SELECT子句中不能出现TEXT、NTEXT或IMAGE类型的列。目前大部分数据库管理系统的编译器不支持在子查询语句中查询二进制类型的数据。
只出现在子查询中而没有出现在父查询中的表不能包含在输出列中。多层嵌套子查询的最终数据集只包含父查询的SELECT子句中出现的列，而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配。

五：事务、视图和索引

一：事务

--开始事务
BEGIN TRANSACTION
--这个语句显式的标记一个事务的起始点

--提交事务
COMMIT TRANSACTION
--这个语句标志一个事务成功结束。
--自事务开始至提交语句之间执行的所有数据更新将永久的保存在数据库文件中
并释放连接时占用的资源

--回滚事务
ROLLBACK TRANSACTION
--清楚自事务起始点至该语句所作的所有数据更新操作
--将数据状态回滚到事务开始前
--并释放由事务控制的资源

事务时一种机制，它包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性，即原子性、一致性、隔离性及持久性。

原子性：事务是一个完整的操作，事务内的各元素是不可分的。事务中所有的元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。
一致性：当事务完成时，数据必须处于一致状态。在事务开始之前，数据库中存储的数据处于一致状态，在正在进行的事务时，数据可能处于不一致的状态。当事务完成时，数据必须再次回到一致状态。通过事务对数据所做的修改不能破坏数据。
隔离性：对数据进行修改的所有并发事务时彼此隔离的，这辨明事务必须时独立的，它不应以任何方式影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或在另一个使用相同数据的事务结束之后访问这些数据。
持久性：事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。一个事务成功完成之后，它对数据库的改变时永久性的，即使系统出现故障也是如此，就是说，一旦事务被提交，事务的效果会永久的保留在数据库中。

事务的三种类型：

显式事务：用BEGIN TRANSACTION明确指定事务的开始。
隐式事务：通过设置SETIMPLICIT_TRANSACTIONS ON语句，将隐式事务模式设置为打开。当以隐式事务操作时，SQL Server将在提交或回滚事务后自动启动新的事务，不需要藐视每个事务的开始，只要提交或回滚每个事务即可。
自动提交事务：这是SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务，如果成功执行，则自动提交。如果错误，则自动回滚。

二：视图

使用Microsoft SQL Server Management Studio创建视图：

张开数据库，选择“视图”选项并右击，在弹出的快捷菜单中选择“新建视图”选项。
在弹出的对话框中单击“添加”按钮添加表，窗口下方自动生成响应的T-SQL语句。
选择希望查看的列，并在“列名”一览填写对应的别名，然后单击按钮运行。

--使用T-SQL创建视图
CREATE VIEW view_name
AS
<SELECT 语句>

--使用T-SQL删除视图
DROP VIEW view_name

--通过EXISTS关键字检测是否存在该视图并删除视图
IF EXISTS(SELECT * FROM sysobjects WHERE NAME='view_name')
    DROP VIEW view_name

--使用T-SQL语句查看视图数据
SELECT col_name1,Col_name2,...FROM view_name

视图时另一种查看数据库中一个或多个表中数据的方法。视图时一种虚拟表，通常时作为来自一个或多个表的行或列的子集创建的。它也可以包含全部的行和列。直接显式来自于表中的数据。视图充当着查询中指定表筛选器。视图通常用来进行以下三种操作：

筛选表中的行。
放置未经许可的用户访问敏感数据。
将多喝物理数据表抽象为一盒逻辑数据表。

使用视图的注意事项：

每个视图中可以使用多个表。
与查询相似，一个视图可以嵌套另一个视图，但最好不要超过三层。
视图定义中的SELECT语句不能包括下列内容：
ORDER BY子句，除非在SELECT语句的选择列表中也有一个TOP子句。
INTO关键字。
引用临时表或变量。

三：索引

使用Microsoft SQL Server Management Studio创建索引：

在表的设计视图中右击，在弹出的快捷菜单中选择“索引/键”选项，弹出“索引/键”对话框。
然后单击“添加”按钮创建索引，可以选择索引列、指定索引的类型：UNIQUE唯一索引、CLUSTERED聚合索引及填充因子。

--使用T-SQL语句创建索引
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name(column_name[,column_name]...)
[WITH FILLFACTOR = x]
--UNIQUE指定唯一索引，可选。
--CLUSTERED、NONCLUSTERED指定时聚集索引还是非聚集索引，可选。
FILFACTOR表示填充因子，指定一个0~100的值，该值指示索引页填满的空间所占的百分比


--使用T-SQL语句删除索引
DROP INDEX table_name.index_name
--删除表时，该表的所有索引将同时被删除。
--如果要删除表的所有索引，则先删除非聚集索引，再删除聚集索引。


--使用T-SQL查看索引
--使用系统存储过程sp_helpIndex查看索引
EXEC sp_helpIndex table_name

--使用视图sys.indexes查看索引
SELECT * FROM sys.indexes

在SQL Server中，常用的索引有以下六类：

唯一索引：不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则一般情况下大多数数据库都不允许创建唯一索引。若已创建了唯一索引，则当新数据使表中的键重复时，数据库也拒绝接受此数据。
主键索引：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引时唯一索引的特殊类型。主键索引要求主键中的每个值都是非空、唯一的。当在查询中使用主键索引时，它还允许快速访问数据。
聚集索引：在聚集索引中，表中各行的物理顺序与键值的逻辑顺序相同。一个表只能包含一个聚集索引。
非聚集索引：非聚集索引简历在索引页上，当查询数据时可以从索引中找到记录存放的位置。非聚集索引使表中各行数据存放的物理顺序与键值的逻辑顺序不匹配、
复合索引：在创建索引时，并不是只能对其中一列创建索引，与创建主键一样，可以将多个列组合作为索引，这种索引称为复合索引。需要注意只有用到复合索引的第一列或整个复合索引作为条件完成数据查询时才会用到该索引。
全文索引：全文索引时一种特殊类型的基于标记的功能性索引，由SQL Server中全文引擎服务创建和维护。全文索引主要用于在大量文本文字中搜索字符串，此时使用全文索引的效率将大大高于使用T-SQL的Like关键字的效率。因为全文索引的创建过程与创建其他类型的索引由很大的区别。

六：存储过程

一：存储过程的概念

存储过程Procedure时SQL语句和控制语句的预编译集合，保存再数据库里，可由应用程序调用执行，而且允许用户声明变量、逻辑控制语句及其它强大的编程功能。存储过程可包含逻辑控制语句和数据操作语句，它可以接受参数、输出参数、返回单个或多个结果集及返回值。

模块化程序设计
执行速度快、效率高
减少网络流量
具有良好的安全性

二：系统存储过程

常用的系统存储过程
系统存储过程	说明
sp_databases	列出服务器上的所有数据库信息，包括数据库名称和数据库大小
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的表或视图的信息
sp_columns	返回某个表或视图的列信息，包括列的数据类型和长度等
sp_help	查看某个数据库对象的信息，如列名、主键、约束、外键、索引等
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stired_procedures	显式存储过程的列表
sp_password	添加或修改登录账户的密码
sp_helptext	显式默认值、未加密的存储过程、用户定义的存储过程、、触发器或视图的实际文本

--调用执行系统存储过程
EXEC[UTE] 系统存储过程名 [参数值]
--其中EXEC时EXECUTE的简写
--如果执行系统存储过程的语句时批处理中的第一个居于
--则可以不指定EXECUTE关键字
--例如：
sp_database MySchool
Go

三：用户自定义存储过程

一：创建不带参数的存储过程

--创建不带参数的存储过程
CREATE PROC[EDURE] 存储过程名称
[
    {@参数1 数据类型} [= 默认值] [OUTPUT],
    ... ...,
    {@参数n 数据类型} [= 默认值] [OUTPUT]
]
AS
    SQL语句

--删除存储过程
DROP PROC[EDURE] 存储过程名称


--建村存储过程是否已经创建
IF EXISTS(SELECT * FROM sysobjects WHERE NAME = 存储过程名称)
    DROP PROCEDURE 存储过程名称

二：创建带参数的存储过程

存储过程中的参数分为以下两种：

输入参数：可以再调用时向存储过程传递参数，此类参数可用来向存储过程中传入值。

输出参数：如果需要返回值，则可以使用输出参数，输出参数后由”OUTPUT“标记，执行存储过程后，将返回值存放再输出参数中，供T-SQL语句读取访问。

--创建带输入参数的存储过程
EXEC[UTE] [返回变量 =] 存储过程名称 [@参数 1=] 参数1 [OUTPUT] | [DEFAULT]，
    ... ...,
    [@参数 1=] 参数值n [OUTPUT] | [DEFAULT]
--其中，OUTPUT表明参数时输出参数，没有OUTPUT关键字则视为输入参数
--其中，DEFAULT表示参数的默认值

四：处理错误信息

在存储过程中，可以使用PRINT语句显示用户定义的错误信息。但是，，这些信息时临时的，且只能显示给用户。RAISERROR返回用户定义的信息时，可指定严重级别，设置系统变量记录所发生的错误。

--RAISERROR语句的语法
RAISERROR ({msg_id | msg_str}{,serverity,state}{WITH OPTION{,......n}})

其中，各参数含义如下：

msg_id：在sysmessages系统表中指定的用户定义错误信息。
msg_str：用户定义的特定西南西，最长为255个字符。
severity：与特定信息相关联，表示用户定义的验证性级别。用户可使用的级别为0~18级、19~25级时为sysadmin固定角色成员预留的，并且需要指定WITH LOG选项、20~25级被认为时致命错误。
state：表示错误的状态，时1~255中的值。
Option：错误的自定义选项，可以是以下任一值：
LOG：在Microsoft SQL Server数据库引擎实例的错误日志和应用程序日志中记录错误。
NOWAIT：将消息立即发送给客户端。
SETERROR：将@@ERROR值和ERROR_NUMBER值设置为msg_id或50000，不考虑严重级别。

Vin Cente

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
1
评论
SQL Server 总结

一：数据库的设计一：设计数据库的步骤二：实体关系模型三：绘制数据库模型图四：规范设计二：数据库的实现一：数据库文件的组成二：创建和删除数据库语法一：创建数据库语法二：删除数据库语法三：SQL Server中的数据类型四：创建和删除表语法一：创建表语法二：删除表语法五：创建和删除约束语法一：创建约束语法二：删除约束语法六：完整创建数据库、数据表并添加相关约束三：SQL编程一：使用变量一：局部变量二：全局变量三：输出语句
复制链接

扫一扫