知识内容：
1.SqlServer数据库概述
2.SqlServer数据库基本操作
3.Transact-SQL程序设计
一、SqlServer数据库概述
1.SqlServer系统概述
SQLServer是一款面向高端的数据库管理系统，SQLServer 是Microsoft公司推出的数据库管理系统，是新一代大型电子商务、数据仓库和数据库解决方案
2.SqlServer的数据库结构
(1)数据库逻辑体系结构
(2)数据库物理体系结构
- 存储页和扩展盘区
- 物理数据库文件和文件组
- 聚簇索引和非聚簇索引
- 文本和图像的存储
- 事务日志等
3.SqlServer客户端/服务器体系结构及数据库类型
sqlserver采用的是客户端/服务器的体系结构，这种体系结构又叫C/S架构
客户/服务器体系结构有利于数据库系统把数据集中保存在*服务器上，从而实现了多用户共享数据的目的。根据物理结构的不同客户/服务器体系结构可以分成两层的客户/服务器体系结构和三层（多层）的客户/服务器体系结构
SQL Server2014是典型的关系型数据库，它由表、视图、索引、用户、存储过程和触发器等对象组成，另外SQL Server2014数据库分为四类：
- 系统数据库：存储SQL Server2014的系统信息，SQL Server2014的系统数据库包括 master、model、msdb、resource 和 tempdb，用以管理系统
- Report Server数据库：存储SSRS配置,报告定义、报告元数据、报告历史、缓存政策、快照、资源、安全设置、加密的数据、调度和提交数据及扩展信息等
- Report Server临时数据库：Report Server临时数据库主要负责存储中间处理产品，例如缓冲的报告、会话和执行数据等
- 用户数据库：由数据库用户自行创建的数据库
二、SqlServer数据库基本操作 -> 只介绍用SQL语言操作的方式，不介绍图形化界面操作
1.用户数据库的操作
(1)SQL语句创建：在窗口上部的工具栏的左侧找到“新建查询”按钮，单击“新建查询”，在SQL Server Management Studio的窗口右侧会建立一个新的查询页面，在这个页面中输入要执行的SQL语句，SQL语句如下所示：
CREATE DATABASE STUDENT2
ON PRIMARY
(NAME = ‘STUDENT2_DATA’,
FILENAME = ‘F:\STUDENT2_DATA.MDF’ ,
SIZE = 5MB,
MAXSIZE = 20MB,
FILEGROWTH = 20%)
LOG ON
(NAME ='STUDENT2_LOG',
FILENAME = ‘F:\STUDENT2_LOG. LDF’,
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 2MB)
(2)缩减已有数据库文件的大小
- 数据库名上右击，选择快捷菜单中的“任务”→“收缩”→“数据库”，在出现的“收缩数据库”对话框中，保持默认设置，单击“确定”按钮，数据库收缩完毕
- 如果要收缩特定的数据文件或日志文件，选择快捷菜单中的“任务”→“收缩”→“文件”
- 数据库的自动收缩可以在数据库的属性中“选项”选项页面中设置，只要将选项中的“自动收缩”设为“True”即可
注：为了避免存储空间的浪费，可以进行数据库的手动收缩或设置自动收缩。但是，无论怎么收缩，数据库的大小也不会小于其初始大小，所以创建数据库时初始大小的选择应尽可能合理
(3)用SQL语句修改：可以使用ALTER DATABASE语句修改数据库名称，详情如下：
使用ALTER DATABASE语句修改数据库名称的语法形式如下：
ALTER DATABASE old_databasename MODIFY NAME=new_databasename 也可以使用SQL语句增加已有数据库文件的大小，ALTER DATABASE 数据库名
MODIFY FILE
(NAME=逻辑文件名,
SIZE=文件大小,
MAXSIZE=增长限制) 也可以使用T-SQL语句增加数据库文件的数目：
ALTER DATABASE 数据库名
ADD FILE|ADD LOG FILE
(NAME=逻辑文件名,
FILE=物理文件名,
SIZE=文件大小,
MAXSIZE=增长限制)
(4)删除用户数据库：
使用DROP DATABASE命令删除数据库：DROP DATABASE database_name[,……n]
注：用户只能根据自己的权限删除用户数据库；不能删除当前正在使用（正打开供用户读写）的数据库；无法删除系统数据库（msdb、model、master、tempdb）
补充：ALTER DATABASE的REMOVE FILE子句，可以删除指定的文件。语法格式如下：
ALTER DATABASE 数据库名
REMOVE FILE 逻辑文件名
2.数据库中的表的操作
数据库是由各种数据库表组成的，数据库表是SQL Server数据库中最重要的操作对象，是用于存储和操作数据的逻辑结构
在数据库中，表是由数据按一定的顺序和格式构成的数据集合，是数据库的主要对象。表的数据组织形式是行、列结构。表中每一行代表一条记录，每一列代表记录的一个字段。没有记录的表称为空表
(1)设计表
对于具体的某一个表，在创建之前，需要确定表的下列特征：
- 表要包含的数据的类型；
- 表中的列数，每一列中数据的类型和长度（如果必要），哪些列允许空值；
- 是否要使用以及何处使用约束、默认设置和规则；
- 所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。
(2)常用数据类型
字符型：
- char(n)/nchar(n)按固定长度存储字符串，字符数不满n个时，自动补空格
- varchar[(n)]/nvarchar(n)按变长存储字符串，存储大小为输入数据的字节的实际长度，若输入的数据超过n个字节，则截断后存储
- text、ntext数据类型可以存储最大长度为231-1、 230-1个字节的字符数据。超过8KB的ASCII数据可以使用Text数据类型存储
整型：
- bigint（大整数）：从 -263 到 263-1的整型数据即所有数字。存储大小为 8 个字节
- int（整型）：从 -231 到 231 - 1 的整型数据所有数字。存储大小为 4 个字节
日期时间型：
- datetime：1753年1月1日到9999年12月31日，每个数据需8个存储字节，精确度为千分之三秒，时间范围为 00:00:00 到 23:59:59.999
- smalldatetime：1900年1月1日到2079年6月6日，每个数据需4个存储字节，精确度为分，时间范围为 00:00到 23:59
- Date：公元元年 1 月 1 日到 9999 年 12 月 31 日，每个日期型数据都需要 3 个存储字节且精度为 10 位，仅存储日期，不存储时间
- Time[(n)] 数据类型仅存储一天中的时间，不存储日期。它使用的是 24 小时时钟，因此支持的范围是 00:00:00.0000000 到 23:59:59.9999999
(3)创建表
使用SQL语句创建表：
CREATE TABLE
[ database_name.[ owner ] .| owner.] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint >} [,…n] )
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
参数说明：
①NULL 和NOT NULL：如果表的某一列被指定具有NULL属性，那么就允许在插入数据时省略该列的值。反之如果表的某一列被指定具有NOT NULL属性，那么就不允许在没有指定列缺省值的情况下插入省略该列值的数据行。在SQL Server中列的缺省属性是NULL。
②PRIMARY KEY：设置字段为主键。
③UNIQUE：设置字段具有唯一性。
④FOREIGN KEY REFERENCES ref_table [(ref_column)]：与其它表建立关联，其中ref_table（表名），ref_column（列名）
列定义与约束：
创建表实例：
CREATE TABLE S
(SNo CHAR(6),
SN VARCHAR(8) CONSTRAINT S_Cons1 NOT NULL,
Sex CHAR(2) CONSTRAINT S_Cons2 DEFAULT '男',
Age INT,
Dept VARCHAR(20))
(4)修改表
SQL语句修改表：
ALTER TABLE table_name
[ ALTER COLUMN column_name /*修改已有列的属性*/
{ new_data_type [ ( precision [ , scale ] ) ] [ NULL | NOT NULL ]}
| ADD [column_name data_type ] /*增加新列*/
[ PRIMARY KEY | constrain ]
[FOREIGN KEY [(column )]
REFERENCES ref_table [(ref_column)]
| DROP /*删除列或约束*/
[ CONSTRAINT ] constraint_name | COLUMN column_name
修改表实例：
在学生表中修改“姓名”字段的属性，使该字段的数据类型为varchar(50)，允许为空：
ALTER TABLE 学生 ALTER COLUMN 姓名 varchar(50) NULL 在课程表中添加“任课教师”字段，数据类型为varchar(10)：
ALTER TABLE 课程表
ADD 任课教师 varchar(10) 删除学生表中的“年龄”字段：
ALTER TABLE 学生表
DROP COLUMN 年龄
3.约束相关操作
(1)创建和删除PRIMARY KEY约束
PRIMARY KEY（主键）约束在表中定义一个主键值，这是唯一确定表中每一行数据的标识符。在所有的约束类型中，主键约束是最重要的一种约束类型，也是使用最广泛的约束类型。该约束强制实体完整性。一个表中最多只能有一个主键，主键列不允许取空值
主键经常定义在一个列上，但是也可以定义在多个列的组合上。当主键定义在多个列上时，虽然某一个列中的数据可能重复，但是这些列的组合值不能重复
创建PRIMARY KEY约束：
使用ALTER TABLE的ADD CONSTRAINT子句添加约束的一般格式为：
ALTER TABLE table_name
ADD [ CONSTRAINT constraint_name ]
PRIMARY KEY
CLUSTERED | NONCLUSTERED /*由系统自动创建聚集或非聚集索引*/
( column [ ,...n ] )
实例：
修改学生表，对学号字段创建PRIMARY KEY约束：
ALTER TABLE 学生
ADD CONSTRAINT pk_st PRIMARY KEY (学号)）
PRIMARY KEY与UNIQUE 的区别：
- 一个基本表中只能有一个PRIMARY KEY，但可多个UNIQUE
- 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL
- 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL
删除PRIMARY KEY约束：
可以使用ALTER TABLE的DROP CONSTRAINT子句删除PRIMARY KEY约束，其一般格式为：
ALTER TABLE table_name
DROP CONSTRAINT constraint_name [,…n]
实例：
删除“学生教学管理”数据库中 “学生”表的PRIMARY KEY约束pk_st：
ALTER TABLE 学生
DROP CONSTRAINT pk_st
(2)创建和删除UNIQUE约束
UNIQUE（唯一性）约束指定表中某一个列或多个列不能有相同的两行或两行以上的数据存在。这种约束通过实现唯一性索引来强制实体完整性。当表中已经有了一个主键约束时，如果需要在其他列上实现实体完整性，又因为表中不能有两个或两个以上的主键约束，所以只能通过创建UNIQUE约束来实现。一般地，把UNIQUE约束称为候选键约束。
例如，在“学生”表中，主键创建在“学号”列上，如果还需要保证该表中的存储“身份证号”列的数据是唯一的，那么可以使用UNIQUE约束
创建表时设置UNIQUE约束：
创建“学生”表，主键约束创建在“学号”列上，要求“身份证号”列的数据是唯一的：
CREATE TABLE 学生
( 学号 char(6) PRIMARY KEY,
姓名 char(8) NOT NULL,
身份证号 char(20) CONSTRAINT uk _st1 UNIQUE,
性别 bit NOT NULL)
修改表时设置UNIQUE约束：
可以使用ALTER TABLE的ADD CONSTRAINT子句设置UNIQUE约束，其一般格式为：
ALTER TABLE table_name
ADD [ CONSTRAINT constraint_name ] UNIQUE
CLUSTERED | NONCLUSTERED /*由系统自动创建聚集或非聚集索引*/
( column [ ,...n ] )
实例：
设置“学生”表“身份证号”字段值唯一：
ALTER TABLE 学生
ADD CONSTRAINT uk_st UNIQUE (身份证号)
删除UNIQUE约束： 方法与删除PRIMARY KEY约束相同
删除“学生”表中创建的UNIQUE约束：
ALTER TABLE 学生
DROP CONSTRAINT uk_st
使用UNIQUE约束时应考虑的问题：
UNIQUE约束所在的列允许空值，但是主键约束所在的列不允许空值；一个表中可以有多个UNIQUE约束；可以把UNIQUE约束放在一个或者多个列上，这些列或列的组合必须有唯一的值，但是，UNIQUE约束所在的列并不是表的主键列；UNIQUE约束强制在指定的列上创建一个唯一性索引。在默认情况下，是创建唯一性的非聚集索引。但是，在定义UNIQUE约束时也可以指定所创建的索引是聚集索引
(3)创建和删除FOREIGN KEY约束
表和表之间的引用关系可以通过FOREIGN KEY（外键）约束来实现。创建外键约束既可以由FOREIGN KEY子句完成，也可以在表设计器中完成
外键约束定义一个或多个列，这些列可以引用同一个表或另外一个表中的主键约束列或UNIQUE约束列。实际上，通过创建外键约束可以实现表和表之间的依赖关系
一般情况下，在Microsoft SQL Server关系型数据库管理系统中，表和表之间经常存在着大量的关系，这些关系都是通过定义主键约束和外键约束实现的
创建表时定义外键约束：
在数据库“学生教学管理”中创建一个“成绩”表，包括：学号(主键)、课程号、成绩，并为成绩表创建外键约束，该约束把“成绩”表中的“学号”字段和“学生”表中的“学号”字段关联起来： CREATE TABLE 成绩
( 学号 char(6) CONSTRAINT st_xh
FOREIGN KEY REFERENCES 学生(学号),
课程号 char(4),
成绩 int )
修改表时添加外键约束：
ALTER TABLE table_name
ADD [ CONSTRAINT constraint_name]
FOREIGN KEY ( column [ ,...n ] )
REFERENCES ref_table ( ref_column [ ,...n ] )
实例：
将“学生教学管理”数据库中学生表、课程表和选课表进行关联，学生表和课程表为主表，其中的学号和课程号字段为主键，选课表为从表，将选课表的学号和课程号字段定义为外键。
①ALTER TABLE 选课表
ADD CONSTRAINT st_foreign
FOREIGN KEY (学号) REFERENCES 学生表(学号 ) ②ALTER TABLE 选课表
ADD CONSTRAINT kc_foreign
FOREIGN KEY (课程号) REFERENCES 课程表(课程号 )
删除外键约束：
SQL语句的ALTER TABLE命令可以删除外键约束。
删除创建的外键约束：
ALTER TABLE 成绩
DROP CONSTRAINT st_xh
(4)创建和删除CHECK约束
CHECK约束用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。CHECK约束的作用非常类似于外键约束，两者都是限制某个列的取值范围，但是外键是通过其他表来限制列的取值范围，CHECK约束是通过指定的逻辑表达式来限制列的取值范围
例如，在描述学生“性别”列中可以创建一个CHECK约束，指定其取值范围是“男”或者“女”。这样，当向该列输入数据时，要么输入数据“男”，要么输入数据“女”，而不能输入其他不相关的数据
SQL语句在创建表时创建CHECK约束：
CREATE TABLE table_name /*指定表名*/
(column_name datatype
[[check_name ] CHECK ( logical_expression )]
/*CHECK约束表达式*/
[,…n])
实例：
在 STUDENT数据库中创建表books，其中包含CHECK约束定义： CREATE TABLE books
(book_id smallint PRIMARY KEY, /*书号*/
book_name varchar(50) NOT NULL, /*书名*/
max_price tinyint NOT NULL CHECK (max_price <= 250)
/*书允许的最高价CHECK约束*/
）
修改表时创建CHECK约束 ：
语法格式：
ALTER TABLE table_name
ADD CONSTRAINT check_name CHECK (logical_expression) 通过修改STUDENT数据库的“成绩”表，增加成绩字段的CHECK约束：
ALTER TABLE 成绩
ADD CONSTRAINT cj_constraint CHECK (成绩>=0 and 成绩<=100)
删除CHECK约束：
SQL语句的ALTER TABLE命令可以删除CHECK约束。
删除创建的CHECK约束：
ALTER TABLE 成绩
DROP CONSTRAINT cj_constraint
注：一个列上可以定义多个CHECK约束；当执行INSERT语句或者UPDATE语句时，该约束验证相应的数据是否满足CHECK约束的条件。但是，执行DELETE语句时不检查CHECK约束
(5)创建和删除DEFAULT约束
当使用INSERT语句插入数据时，如果没有为某一个列指定数据，那么DEFAULT约束就在该列中输入一个默认值
例如在学生表的性别列中定义了一个DEFAULT约束为“男”。当向该表中输入数据时，如果没有为性别列提供数据，那么DEFAULT约束把默认值“男”自动插入到该列中。因此，DEFAULT约束可以实现保证域完整性
在创建表时定义默认值约束:
CREATE TABLE table_name /*指定表名*/
(column_name datatype NOT NULL | NULL
[DEFAULT constraint_expression] /*默认值约束表达式*/ [,…n])
/*定义列名、数据类型、标识列、是否空值及定义默认值约束*/
实例：
先在“STUDENT”数据库中创建表ST，定义一个“入学日期”字段的默认值为系统当前日期：
CREATE TABLE ST
( 学号 char(6) NOT NULL,
姓名 char(8) NOT NULL,
专业名 char(10) NULL,
性别 bit NOT NULL,
出生时间 smalldatetime NOT NULL,
总学分 tinyint NULL,
备注 text NULL,
入学日期 datetime DEFAULT getdate() ) /*定义默认值约束*/
在修改表时定义默认值约束：
修改ST表，添加一字段AddDate，并为其设置默认值约束，默认值为当前日期:
USE STUDENT
GO
ALTER TABLE ST
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDf /*默认值约束名*/
DEFAULT getdate()
删除默认值约束:
ALTER TABLE ST
DROP CONSTRAINT AddDateDf
定义DEFAULT约束时应考虑的问题:
①定义的常量值必须与该列的数据类型和精度是一致的
②DEFAULT约束只能应用于INSERT语句
③每一个列只能定义一个DEFAULT约束。DEFAULT约束不能放在有IDENTITY属性的列上或者数据类型为timestamp的列上，因为这两种列都会由系统自动提供数据
④DEFAULT约束允许指定一些由系统函数提供的值，这些系统函数包括SYSTEM_USER、GETDATE、CURRENT_USER等
4.数据表的创建与增删改查
T-SQL语句类似于SQL语句，因此在此不详细介绍T-SQL语句的语法，关于SQL语句详细内容见：http://www.cnblogs.com/wyb666/p/9017402.html
(1)创建、删除数据表及插入、删除、更改数据表
创建数据表 CREATE TABLE语句
删除数据表 DROP TABLE语句
向数据表中插入数据 INSERT语句
从数据表中删除记录 DELETE语句
修改和更新记录 UPDATE语句
实例1：
在“教学库”数据库的“学生表”中插入一行数据（学号、姓名、性别、年级）为（‘0100215’、‘刘玲玲’、‘女’、‘10级’）：
INSERT into 学生表(学号，姓名，性别，年级) VALUES('','刘玲玲','女','10级')
【例】在“STUDENT1”数据库的“学生”表中插入一行数据（‘120101’、‘刘玲’、‘130212199407190926’、‘女’）：
INSERT into 学生 VALUES('','刘玲','','女')
实例2：
删除“学生”表中“120101”号学生的记录：
USE STUDENT1
DELETE 学生 WHERE 学号 = '' ***使用TRUNCATE TABLE清空表格***
语法格式： TRUNCATE TABLE table_name 注：TRUNCATE TABLE与不含有WHERE子句的DELETE语句在功能上相同。但是，TRUNCATE TABLE速度更快，并且使用更少的系统资源和事务日志资源。 清空“学生”表中的数据：
TRUNCATE TABLE 学生
实例3：
将STUDENT1数据库的“学生”表中的“性别”字段的值设为“男”：
UPDATE 学生 SET 性别='男'
在STUDENT1数据库的“学生”表中添加一字段“备注”varchar(20)，“备注”字段信息为“已毕业”：
ALTER TABLE 学生 Add 备注 varchar(20)
UPDATE 学生 SET 备注='已毕业'0
实例3：
在“学生”表中，将学号为“120101”的学生姓名改为“王武”：
UPDATE 学生 SET 姓名='王武' WHERE 学号=''
(2)简单查询
SELECT语句的语法格式如下：
SELECT select_list [INTO new_table_name ]
FROM table_list
[ WHERE search_conditions ]
[ GROUP BY group_by_list ]
[ HAVING search_conditions ]
[ ORDER BY order_list [ ASC | DESC ] ]
实例：
查询所有记录：SELECT * FROM 课程
选择一个表中指定的列：SELECT 姓名,性别,专业号 FROM 学生 用DISTINCT关键字可以过滤掉查询结果中的重复行：SELECT DISTINCT 专业号 FROM 学生
前三行信息：SELECT top 3 * FROM 课程
查询前50%：SELECT top 50 percent * FROM 课程 计算列值:
查询“成绩单”，按150分制计算成绩：SELECT 学号, 课程号, 成绩150=成绩*1.50 FROM 成绩单 选择查询：
SELECT * FROM 成绩单 WHERE 成绩>=60
SELECT * FROM 学生 WHERE 专业号=‘0501' and 性别='男'
SELECT * FROM 成绩单 WHERE 成绩 BETWEEN 80 AND 90
SELECT 姓名,学号,专业号 FROM 学生 WHERE 专业 IN(‘0501' ,‘0403')
SELECT * FROM 成绩单 WHERE 成绩 IS NULL
SELECT * FROM 学生表 WHERE 性别=’女’ AND （专业=’0501’OR 专业=’0403’） 使用通配符匹配查询:
SELECT * FROM 学生 WHERE 姓名 like '张%'
(3)聚合函数查询
SQL Server提供了一系列聚合函数。这些函数把存储在数据库中的数据描述为一个整体而不是一行行孤立的记录，通过使用这些函数可以实现数据集合的汇总或是求平均值等各种运算
实例：
SELECT avg(成绩) AS 平均成绩 FROM 成绩单
SELECT count (DISTINCT 专业号) AS 专业种类数 FROM 学生
SELECT avg(成绩) AS 平均成绩 FROM 成绩单 WHERE 学号=’ 01053113’ Group by 学号
(4)分组和汇总
使用聚合函数返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就要使用GROUP BY子句了。分组技术是指使用GROUP BY子句完成分组操作的技术
GROUP BY子句的语法结构如下：
[ GROUP BY [ ALL ] group_by_expression [,…n]
[ WITH { CUBE | ROLLUP } ] ]
实例：
查询“学生教学管理”中男生和女生的人数：
SELECT 性别,count(性别) 人数 FROM 学生 GROUP BY 性别
注：指定 GROUP BY 子句时，选择列表中任意非聚合表达式内的所有列都应包含在 GROUP BY 列表中（不能使用别名列），或者 GROUP BY 表达式必须与选择列表表达式完全匹配 在“成绩单”中查询选修了两门及以上课程的学生学号和选课数：
SELECT 学号,COUNT(课程号) 选修课程数 FROM 成绩单 GROUP BY 学号 HAVING COUNT(课程号)>=2
HAVING与 WHERE 子句的区别是：WHERE 子句式对整表中数据筛选满足条件的行；而HAVING子句是对GROUP BY分组查询后产生的组加条件，筛选出满足条件的组。另外，WHERE中条件不能使用聚合函数，HAVING中条件一般使用聚合函数
(5)连接查询
内连接：
SELECT select_list
FROM 表1 INNER JOIN 表2 ON 连接条件
查询学生管理数据库中有成绩的学生的姓名、性别、籍贯、课程编号以及成绩：
Select 学生.学号,姓名,籍贯,课程号,成绩 From 成绩单 inner join 学生 On 学生.学号=成绩单.学号
自连接：
连接操作不仅可以在不同的表上进行，而且在同一张表内可以进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表
从“学生教学管理”中查询同名学生的信息：
SELECT * FROM 学生 a INNER JOIN 学生 b ON a.姓名=b.姓名 AND a.学号<>b.学号
外连接：
在外连接中，不仅包括那些满足条件的数据，而且某些表不满足条件的数据也会显示在结果集中。也就是说，外连接只限制其中一个表的数据行，而不限制另外一个表中的数据。这种连接形式在许多情况下是非常有用的，例如在连锁超市统计报表时，不仅要统计那些有销售量的超市和商品，而且还要统计那些没有销售量的超市和商品
外连接的分类：
- LEFT OUTER JOIN：左外连接是对连接条件中左边的表不加限制
- RIGHT OUTER JOIN：右外连接是对右边的表不加限制
- FULL OUTER JOIN：全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中
左外连接：SELECT select_list FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.列1=表2.列2
右外连接：SELECT select_list FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.列1=表2.列2
全外连接：SELECT select_list FROM 表1 FULL [OUTER] JOIN 表2 ON 表1.列1=表2.列2
实例：
显示所有学生信息,同时显示选课学生的学习成绩：
Select 学生.*,成绩单.* From 学生 left join 成绩单 on 学生.学号=成绩单.学号 显示所有成绩单信息,同时显示所有相关学生的信息：
Select 学生.*,成绩单.* From 学生 right join 成绩单 on 成绩单.学号=学生.学号 显示所有学生和成绩单信息：
Select 学生.*,成绩单.* From 学生 full join 成绩单 on 成绩单.学号=学生.学号
(6)对查询结果进行排序
在使用SELECT语句时，排序是一种常见的操作。排序是指按照指定的列或其他表达式对结果集进行排列顺序的方式。SELECT语句中的ORDER BY子句负责完成排序操作。另外ASC表示升序，DESC表示降序，默认情况下是升序
实例：
查询“学生教学管理”中“女”学生的姓名和专业号，并按姓名升序排列：
SELECT 姓名,专业号 FROM 学生 WHERE 性别=’女’ ORDER BY 姓名 ASC
查询“成绩单”中学生的成绩和学号，并按成绩降序排列：
SELECT 学号,成绩 FROM 成绩单 ORDER BY 成绩 DESC
5.存储查询结果
通过在SELECT语句中使用INTO子句，可以创建一个新表并将查询结果中的行添加到该表中。用户在执行一个带有INTO子句的SELECT语句时，必须拥有在目标数据库上创建表的权限。SELECT...INTO不能与COMPUTE子句一起使用
SELECT...INTO语句的语法格式如下：
SELECT select_list
INTO new_table
FROM table_source
[WHERE search_condition]
实例：
查询学生姓名，学号，课程名，成绩的相关数据存放在表“成绩信息”中。并对新表进行查询：
SELECT 姓名,学生.学号,课程名, 成绩 INTO 成绩信息
FROM 学生 inner join 成绩单 on 学生.学号=成绩单.学号
inner join 课程 on 成绩单.课程号=课程.课程号 SELECT * FROM 成绩信息
6.数据库索引
索引类似书籍的目录，索引通过记录表中的关键值指向表中的记录，这样数据库就不用扫描整个表定位到相关的记录。相反，如果没有索引，则会导致SQL Server搜索表中的所有记录以获取匹配结果
创建索引的语法格式如下：
CREATE [ UNIQUE ][ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table_name | view_name } ( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ] [ ON filegroup ]
< index_option > ::=
{ PAD_INDEX | FILLFACTOR = fillfactor
| IGNORE_DUP_KEY | DROP_EXISTING
| STATISTICS_NORECOMPUTE }
实例：
根据学生教学管理中学生表的姓名列的升序创建一个名为“index_学生姓名”的普通索引：
CREATE INDEX index_学生姓名 ON 学生(姓名) 根据“仓库库存”数据库中“商品表”的商品名称、生产商创建一个名为“商品_生产商”的唯一性复合索引，其中商品名称为升序，生产商为降序：
CREATE UNIQUE INDEX 商品_生产商 ON 商品(商品名称 ASC, 生产商 DESC)
注：在定义表结构或修改表结构时，如果定义了主键约束（PRAMARY KEY）或者唯一性约束（UNIQUE），可以间接创建索引
7.数据库视图
视图是一种数据库对象，为用户提供一个可以检索数据表中数据的方式。视图是一个虚表，可以视为另一种形式的表，是从一个或多个表中使用SELECT语句导出的虚拟表，那些用来导出视图的表称为基本表
用户通过视图来浏览数据表中的数据，另外数据库中只存储视图的定义而不存储视图对应的数据，这些数据仍存储在导出视图的基本表中。当基本表中的数据变化时，从视图中查询出来的数据也随之改变
创建视图：
CREATE VIEW [schema_name.]view_name [ (column_name[ ,...n ] ) ]
[with <view_attribute>[,…n]]
AS select_statement
[WITH CHECK OPTION]
实例：
创建“学生_课程_成绩”视图，包括“0501”专业的学生的学号、姓名，和他们选修的课程号、课程名和成绩：
CREATE VIEW 学生_课程_成绩 AS SELECT 学生.学号,姓名,课程.课程号,课程名,成绩
FROM 学生,成绩单,课程 WHERE 学生.学号=成绩单.学号 AND 课程.课程号=成绩单.课程号 AND 专业号=‘0501'
修改视图：
ALTER VIEW [schema_name.]view_name
[ (column_name[ ,...n ] ) ] [with <view_attribute>[…n]]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
注：语句中的参数与CREATE VIEW语句中的参数相同
实例：
修改“库存统计视图”，求每种商品的总库存数量和所在仓库个数，要求包括商品编号和商品名称：
ALTER VIEW 库存统计视图 AS SELECT 商品.商品编号,商品名称,SUM(数量) AS 总库存数量,
COUNT(仓库编号) AS 所在仓库个数 FROM 商品 INNER JOIN 库存情况 ON 商品.商品编号=库存情况.商品编号 GROUP BY 商品.商品编号,商品名称
删除索引：
DROP INDEX table_name.index_name 或 DROP INDEX 索引名 ON 基表名；
三、Transact-SQL程序设计
1.变量与流程控制
Transact-SQL附加语言元素不是SQL的标准内容，而是Transact-SQL语言为了编程方便而增加的语言元素。这些语言元素包括变量、运算符、函数、流程控制语言和注释等内容
(1)变量
- Transact-SQL语言允许使用两种变量： 一种是用户自己定一的局部变量，另一种是系统提供的全局变量
- 声明并创建局部变量：DECLARE @变量名 变量类型[，@变量名 变量类型…]，给局部变量赋值：SET @局部变量 = 变量值 或 SELECT @局部变量 = 变量值
- 全局变量由系统提供且预先声明，在名称前加两个“@”区别于局部变量。只能使用全局变量而不能修改它们。全局变量的作用范围是整个SQL Server系统，任何程序都可以随时调用它们
实例：
1 声明一个长度为10的字符型变量“name”，并赋值。
2 DECLARE @name char(10)
3 SET @name=‘张三’
4 PRINT @name
5
6 执行结果：张三
(2)set语句
声明一个局部变量后，该变量将被初始化为 NULL。使用 SET 语句将一个不是 NULL 的值赋给声明的变量，给变量赋值的 SET 语句返回单值。在初始化多个变量时，为每个局部变量使用单独的 SET 语句。其语法格式为：
SET @local_variable=expression
实例：
1 声明变量，并用SET给变量赋值：
2 DECLARE @myvar char(20)
3 SET @myvar = 'This is a test'
4 SELECT @myvar
5 GO
(3)BEGIN…END语句
作用：将多个T-SQL语句组合成一个语句块，并将它们视为一个单元处理
语法格式如下：
BEGIN
{ sql_statement | statement_block }
END
(4)IF… ELSE语句
语法格式如下：
IF <条件表达式>
<命令行或程序块>
[ELSE [条件表达式]
<命令行或程序块>]
实例：
1 学号为“01053113”的同学平均成绩高于80分，则显示“平均成绩还不错”，否则显示“平均成绩一般”。
2 USE 学生教学管理
3 GO
4 IF ( SELECT AVG(成绩) FROM 成绩单 WHERE 学号= ' 01053113' ) >80
5 PRINT ' 学号01053113的同学平均成绩还不错'
6 ELSE
7 PRINT '学号01053113的同学平均成绩一般'
(5)CASE语句
使用CASE语句可以进行多个分支的选择，CASE具有以下两种格式。
- 简单CASE格式：将某个表达式与一组简单表达式进行比较，以确定结果。
- 搜索CASE格式：计算一组布尔表达式，以确定结果
简单CASE的语法格式：
CASE<运算式>
WHEN<运算式>THEN<运算式>
…
WHEN<运算式>THEN<运算式>
[ELSE<运算式>]
END
实例：
1 以简单CASE格式查询所有学生的专业情况，包括学号，姓名和专业名：
2 USE 学生教学管理
3 SELECT 学号,姓名,
4 CASE 专业号
5 WHEN ‘0403’ THEN ‘国际法'
6 WHEN ‘0501’ THEN ‘工商管理'
7 WHEN ‘0904’ THEN ‘计算机科学'
8 END AS 专业
9 FROM 学生
搜索CASE的语法格式：
CASE
WHEN<条件表达式>THEN<运算式>
…
WHEN<条件表达式>THEN<运算式>
[ELSE<运算式>]
END
实例：
1 以搜索CASE格式查询所有学生的考试等级，包括学号，课程号和成绩级别（a、b、c、d、e）：
2 USE 学生教学管理
3 SELECT 学号, 课程号,
4 CASE
5 WHEN 成绩>=90 then 'a'
6 WHEN 成绩>=80 then 'b'
7 WHEN 成绩>=70 then 'c'
8 WHEN 成绩>=60 then 'd'
9 WHEN 成绩<60 then 'e'
10 END AS 成绩级别
11 FROM 成绩单
(6)WHILE语句
如果需要重复执行程序中的一部分语句，可使用WHILE循环语句实现。WHILE语句通过布尔表达式来设置一个条件，当这个条件成立时，重复执行一个语句或语句块，重复执行的部分称为循环体。
可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行
语法格式：
WHILE<条件表达式>
BEGIN
<命令行或程序块>
[BREAK]
[CONTINUE]
[命令行或程序块]
END
实例：
1 显示字符串“China”中每个字符和其ASCII值：
2 DECLARE @position int, @string char(5)
3 SET @position = 1
4 SET @string = 'China'
5 WHILE @position <= DATALENGTH(@string)
6 BEGIN
7 SELECT SUBSTRING(@string, @position, 1) ,
8 ASCII(SUBSTRING(@string, @position, 1))
9 SET @position = @position + 1
10 END
1 求1到100的累加和，当和超过1000时停止累加，显示累加和以及累加到的位置：
2 DECLARE @i int,@a int
3 SET @i=1
4 SET @a=0
5 WHILE @i <= 100
6 BEGIN
7 SET @a=@a+@i
8 IF @a>=1000 BREAK
9 SET @i=@i+1
10 END
11 SELECT @a AS 'a', @i AS 'i'
(7)RETURN语句
使用RETURN语句，可以从查询或过程中无条件退出。可在任何时候用于从过程、批处理或语句块中退出，而不执行位于RETURN之后的语句。
语法格式为：RETURN [integer_expression] /*整形表达式*/
其中，整型表达式为一个整数值，是RETURN语句要返回的值。
注意：当用于存储过程时，不能返回空值。如果试图返回空值，将生成警告信息。并返回0值
(8)注释
- -- 单行注释
- /**/ 多行注释
2.存储过程
(1)什么是存储过程
- 存储过程是一种封装重复任务操作的方法，支持用户提供的参数变量，具有强大的编程能力。存储过程中可以把任务的许多SQL语句写在一起，组成命令集合的形式
- 存储过程是一种数据库对象，独立存储在数据库内。存储过程可以接受输入参数、输出参数，返回单个或多个结果集以及返回值，由应用程序通过调用执行。
- 存储过程是独立存在于表之外的数据对象。可以由客户调用，也可以从另一个过程或触发器调用，参数可以被传递和返回，出错代码也可以被检验。
(2)存储过程的特色及优点
特色：当写完一个存储过程后即被翻译成可执行码存储在系统表内，当作是数据库的对象之一，一般用户只要执行存储过程，并且提供存储过程所需的参数就可以得到所要的结果而不必再去编辑T-SQL命令
优点：
- 允许模块化程序设计
- 允许更快速地执行
- 减少网络流量
- 可作为安全机制使用
(3)创建存储过程
在窗口中创建存储过程：
(1)打开SQLServer Management Studio，展开要创建存储过程的数据库，展开“可编程性”选项，再右键单击“存储过程”选项，选择“新建存储过程”命令
(2)出现创建存储过程的T-SQL命令，编辑相关的命令
(3)命令编辑成功后，进行语法检查，然后单击“！”按钮，至此一个新的存储过程建立成功 注：用户只能在当前数据库中创建存储过程，数据库的拥有者有默认的创建权限，权限也可以转让给其他用户
使用T-SQL语句创建存储过程：
CREATE { PROC | PROCEDURE } procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } [ ,...n ] ]
[FOR REPLICATION]
AS sql_statement [ ...n ]
注：
- 存储过程最大不能超过128MB
- 用户定义的存储过程只能在当前数据库中创建
- 在一条T-SQL语句中CREATE PROCEDURE不能与其他T-SQL 语句一起使用
- SQLServer允许在存储过程创建时引用不存在的对象，创建时系统只检查语法。如果存储过程执行时缓存中没有一个有效的计划，则会编译生成一个可执行计划。在编译时才会检查存储过程所引用的对象是否都存在。
实例：
在“学生教学管理”创建无参存储过程，查询每个同学各门功课的平均成绩：
CREATE PROCEDURE student_avg
AS
SELECT 学号, avg(成绩) as 平均分 FROM 成绩单
GROUP BY 学号
GO 在学生教学管理创建带参数的存储过程，查询某个同学的基本信息：
CREATE PROCEDURE GetStudent @number char(8)
AS
SELECT * FROM 学生 WHERE 学号= @number
GO 在“学生教学管理”创建带参数的存储过程，修改某个同学某门课的成绩：
CREATE PROCEDURE Update_score @number char(8),@cn char(8),@score int
AS UPDATE 成绩单 SET 成绩=@score
WHERE 学号= @number and 课程号=@cn
GO
(4)执行存储过程
在窗口中执行存储过程：
(1)打开SQL Server Management Studio，展开存储过程所在的数据库，展开“可编程性”选项，右键单击存储过程名，如“学生教学管理”中的“GetStudent”，在弹出的快捷菜单中选择“执行存储过程”命令
(2)进入“执行过程”对话框，输入要查询的学生的学号，如“05013113”
使用T-SQL语句执行存储过程：
[ [EXEC[UTE] ] [@return_status=]procedure_name[;number]
{[[@parameter=]value | [@ parameter=] @variable [OUTPUT]]}
[WITH RECOMPILE ]
实例：
执行存储过程student_avg： EXECUTE student_avg
执行带参数的存储过程GetStudent，查询学号为‘01053113’的学生的基本信息： EXECUTE GetStudent ‘01053113’
3 执行修改成绩的存储过程Update_score ： EXECUTE Update_score ‘01053113',‘01054010‘,100
(5)查看存储过程
查看存储过程可以使用SQL Server Management Studio界面，也可以使用T-SQL 语句。
(1)打开SQL Server Management Studio查看.
(2)使用T-SQL 语句查看存储过程.
sp_helptext 存储过程名称
sp_help 存储过程名称
(6)修改和删除存储过程
修改存储过程:
可在SQL Server Management Studio中；也可以通过T-SQL中的ALTER语句来完成:
ALTER { PROC | PROCEDURE } procedure_name [ ; number ] [ { @parameter data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } [ ,...n ] ]
[FOR REPLICATION]
AS sql_statement [ ...n ] 删除存储过程:
对于不需要的存储过程可以在SQL Server Management Studio中鼠标右击要删除的存储过程，选择“删除”命令将其删除，也可以使用T-SQL语句中的DROP PROCEDURE命令将其删除
DROP PROCEDURE {procedure_name} [,…n]
procedure_name指要删除的存储过程或存储过程组的名称。
3.触发器
(1)触发器的本质：触发器也是一种存储过程，它在特定语言事件发生时自动执行
本质而言，触发器也是一种存储过程，它是一种在基本表被修改时自动执行的内嵌过程，主要通过事件进行触发而被执行，而存储过程可以通过存储过程名字而被直接调用。
(2)触发器的作用
当对某一张表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server 2012就会自动执行触发器所定义的SQL语句。从而确保对数据的处理符合由这些SQL语句所定义的规则，
触发器的主要作用是其能实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。
(3)触发器功能
- 强制比CHECK约束更复杂的数据的完整性
- 使用自定义的错误提示信息
- 实现数据库中多张表的级联修改
- 比较数据库修改前后数据的状态
- 调用更多的存储过程
- 维护非规范化数据
(4)创建触发器
利用T-SQL语句创建触发器：SQL Server 2012提供了CREATE TRIGGER创建触发器。
语法格式如下：
CREATE TRIGGER trigger_name
ON { table_name | view }
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ DELETE ] [ UPDATE ] }
[NOT FOR REPLICATION]
AS sql_statement [...n ]
实例：
# 创建触发器禁止向成绩单表插入数据:
CREATE TRIGGER FORBIDDEN_INSERT ON 成绩单 FOR INSERT
AS
BEGIN
RAISERROR('未授权',10,1)
ROLLBACK TRAN
END INSERT INTO 成绩单(学号,课程号,成绩) VALUES ('','',95) # 检查要向学生插入的这条的学号是否成绩单表中的学号字段中有，如果不存在则不允许插入:
CREATE TRIGGER TRIGINSERTEDMESSAGES ON 学生 AFTER INSERT
AS
IF EXISTS (SELECT * FROM 学生 A WHERE A.学号 NOT IN (SELECT 学号 FROM 成绩单))
BEGIN
RAISERROR('数据一致性验证',16,1)
ROLLBACK TRANSACTION
END # 为学生表创建一个简单触发器，在插入和修改数据时，都会自动显示提示信息:
USE 学生教学管理
GO
CREATE TRIGGER reminder ON 学生
FOR INSERT,UPDATE
AS print '你在插入或修改学生表的数据‘ UPDATE 学生表 SET 姓名=‘王艳艳’ WHERE 姓名=‘王艳‘
在创建触发器前，需要注意以下问题：
- CREATE TRIGGER语句必须是批处理中第一条语句，只能用于一个表或视图
- 创建触发器的权限默认为表的所有者，不能将该权限转给其他用户
- 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建
- 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表
- 在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF触发器
- 虽然TRUNCATE TABLE语句类似于没有WHERE子句的DELETE语句，但不会激发DELETE触发器，因为TRUNCATE TABLE语句没有记录日志
(5)查看触发器信息
一般有两种：在SQL Server Management Studio中查看触发器和使用系统存储过程查看触发器。
在SQL Server Management Studio中查看触发器:
(1)在SQL Server Management Studio的“对象资源管理器”面板中，展开“学生教学管理”选项，再展开“表”选项，选中“学生”选项并展开，最后再展开“触发器”选项，选中要查看的触发器名如“reminder”，鼠标右击，在弹出的快捷菜单中选择“编写触发器脚本为”命令，再选择“CREATE到”，再选择“新查询编辑器窗口”。
(2)在弹出的T-SQL命令窗口中显示了该触发器的语句内容 使用系统存储过程查看触发器:
系统存储过程SP_HELP和SP_HELPTEXT分别提供有关触发器的不同信息。
(1)通过SP_HELP系统存储过程，可以了解触发器的一般信息，包括名字、拥有者名称、类型、创建时间。
【例】通过SP_HELP查看学生上的触发器reminder。
SP_HELP reminder
(2)通过SP_HELPTEXT能够查看触发器的定义信息。
【例】通过SP_HELPTEXT查看学生表上的触发器reminder 。
SP_HELPTEXT reminder
还可以通过使用系统存储过程SP_HELPTRIGGER来查看某张特定表上存在的触发器的某些相关信息。
【例】通过SP_HELPTRIGGER查看学生表上的触发器信息。
SP_HELPTRIGGER 学生
(6)修改触发器
# 使用ALTER TRIGGER语句来修改触发器:
ALTER RIGGER trigger_name
ON { table_name | view }
[WITH ENCRYPTION ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ DELETE ] [ UPDATE ] }
[NOT FOR REPLICATION ]
AS sql_statement [...n ]
实例：
修改“学生教学管理”中的“学生”上的触发器reminder，使得在用户执行添加或修改操作时，自动给出错误提示信息，撤销此次操作:
ALTER TRIGGER reminder
ON 学生
INSTEAD OF INSERT , UPDATE
AS print ’你执行的添加或修改操作无效！’
(7)禁止、启用和删除触发器
禁用触发器与删除触发器不同，禁用触发器时，仍会为数据表定义该触发器，只是在执行Insert、Update或Delete语句时，除非重新启用触发器，否则不会执行触发器中的操作
删除触发器是将该触发器在数据表上的定义完全删除，如果想使用此触发器，需重新创建
禁用、启用触发器：
ALTER TABLE table_name
[ENABLE | DISABLE] TRIGGER
[ ALL | trigger_name [ ,...n ] ] 【例】禁止学生表上创建的所有触发器。
ALTER TABLE 学生
DISABLE TRIGGER ALL
删除触发器：
删除已创建的触发器一般有以下两种方法：
(1)在SQL Server Management Studio的“对象资源管理器”中找到相应的触发器，鼠标右击，在弹出的快捷菜单中，选择“删除”命令即可。
(2)使用T-SQL命令DROP TRIGGER删除指定的触发器，删除触发器的具体语法格式如下：
DROP TRIGGER trigger_ name
实例：
使用DROP TRIGGER命令删除学生上的reminder触发器：
USE 学生教学管理
GO
DROP TRIGGER reminder
注：删除触发器所在的表时，SQL Server 将自动删除与该表相关的触发器
4.事务和锁
(1)数据并发性和并发控制的概念
数据库并发性的含义：为了充分利用数据库资源，很多时候数据库用户都是对数据库系统并行存取数据，这样就会发生多个用户并发存取同一数据块的情况，如果对并发操作不加控制可能会产生不正确的数据，破坏数据的完整性
并发控制就是解决这类问题，以保持数据库中数据的一致性，即在任何一个时刻数据库都将以相同的形式给用户提供数据。
(2)事务的定义
数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源
(3)事务的特征
(4)SqlServer中的事务分类
显式事务：显式事务也称为用户自定义事务。有明显的开始和结束标致。
隐式事务：隐式事务指在当前事务提交或回滚后，由SQL Server自动启动的新事务。
自动事务：自动事务是SQL Server的默认事务管理模式
(5)并发操作与数据的不一致性
(6)锁
