文章目录
- 一. 多表查询
- 1. 等值连接
- 2. 自然连接
- 3. 自身连接
- 4. 复合条件连接
- 5. 内连接
- 二. 联合查询
- 1. UNION 运算符
- 2. INTERSECT 运算符
- 3. EXCEPT 运算符
- 三. 子查询
- 1. 一般子查询
- 2. 带有 IN 或 NOT IN 的子查询
- 3. 带有比较运算符的子查询
- 4. 带有 EXISTS 运算符的子查询
- 5. 在查询的基础上创建新表
- 四. 索引
- 1. 创建索引
- 2. 禁用索引
- 3. 其他操作
- 五. 视图
- 1. 单表视图
- 2. 多表视图
- 3. 基于视图的视图
- 4. 基于视图的操作
- 5. 修改视图
- 6. 删除视图
- 六. 存储过程应用
- 1. 创建并执行存储过程
- 2. 带参数的存储过程
- 3. 创建并执行带有通配符参数的存储过程
- 4. 创建并执行带有输出参数的存储过程
- 5. 存储过程的重编译处理
- 6. 查看存储过程
- 7. 修改存储过程
- 8. 删除存储过程
一. 多表查询
1. 等值连接
采用 等值连接 的方法，查询教学计划及开设课程的详细情况。教学计划（ 课程号，专业代码，课程类型，开课学期，学时 ），课程（ 课程号，课程名，课程性质，学分 ）
代码如下:
SELECT 教学计划.*, 课程.*
FROM 教学计划,课程
WHERE 教学计划.课程号 = 课程.课程号2. 自然连接
采用自然连接的方法，查询教学计划及开设课程的详细情况。
代码如下:
SELECT 教学计划.课程号, 专业代码, 课程类型, 开课学期, 学时, 课程名, 课程性质, 学分
FROM 教学计划,课程
WHERE 教学计划.课程号 = 课程.课程号3. 自身连接
采用自身连接的方法，查找 不同课程成绩相同 的学生的学号、课程号和成绩。选课（ 学号，课程号，成绩，补考成绩，学分 )
分析: 若要在一个表中查找具有相同字段值的行，则可以使用自身连接。使用自身连接时需为表指定两个别名，且对所有列的引用均要用别名限定。
代码如下:
SELECT 表1.学号, 表1.课程号, 表1.成绩
FROM 表1, 表2
WHERE 表1.成绩 = 表2.成绩 AND 表1.学号 = 表2.学号 AND 表1.课程号 <> 表2.课程号4. 复合条件连接
采用复合条件连接方法，查找选修了 “ 电子商务 ” 课程且成绩在 60 分及以上的学生学号、姓名、课程名及成绩。课程（ 课程号，课程名，课程性质，学分 ），选课（ 学号，课程号，成绩，补考成绩，学分 ）。
分析:本例中涉及到数据库中的学生基本信息表、选课表、课程表等3个表。要建立多个连接条件，在多表操作中，复合条件连接（ 多个连接条件 ）的使用最为广泛。
代码如下:
SELECT 学生基本信息.学号, 姓名, 课程.课程名, 选课.成绩
FROM 学生基本信息, 课程, 选课
WHERE 学生基本信息.学号 = 选课.学号
AND 课程.课程号 = 选课.课程号
AND 课程.课程名 = '电子商务'
AND 选课.成绩 >= 60在使用 WHERE 子句中的条件表达式时，一般先写自然连接的条件表达式，然后通过逻辑运算符再写出其他的附加限定条件。
5. 内连接
查找数据库中每个学生的基本信息及所在班级情况。班级 ( 班号，班级名称，学生数，专业代码，系部代码，班主任，班长，教室 )
代码如下:
SELECT *
FROM 学生基本信息
INNER JOIN 班级 ON 学生基本信息.班号 = 班级.班号- 内连接是系统默认的，可以省略 INNER 关键字，使用内连接后，仍可以使用 WHERE 子句指定条件。
二. 联合查询
联合查询也称集合查询。是一种将两个或更多查询的结果，通过 并、交、差 等集合运算合并为单个结果集的一种查询方法。在 SQL Server 2008 中，用于联合查询的运算符有 UNION（求并）、INTERSECT（求交）和 EXCEPT(求差）。
1. UNION 运算符
UNION 运算符是求并操作，能够将两个或更多查询结果顺序连接，合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。
语法格式:
SELECT 语句1
{ UNION <SELECT语句2> } […n]
语法说明如下:
参加 UNION 操作的所有查询中的列数和列的顺序必须相同，对应的数据类型也必须相同。
系统将自动去掉并集的重复记录。
最后结果集的字段名来自第一个 SELECT 语句。利用 UNION 查询 “ 学生基本信息 ” 表中班号为 "520402 " 和 " 620203 " 的学生学号、姓名和班号。
代码如下:
SELECT 学号, 姓名, 班号
FROM 学生基本信息
WHERE 班号 = '520402'
UNION
SELECT 学号, 姓名, 班号
FROM 学生基本信息
WHERE 班号 = '620203'2. INTERSECT 运算符
INTERSECT 运算符是求交操作，能够将两个或更多查询结果的交集作为联合查询的结果集，该结果集包含两个或更多查询均返回的所有非重复值。
语法格式:
SELECT 语句1 { INTERSECT <SELECT语句2> } […n]
其语法说明与 UNION 运算相同。利用 INTERSECT 查询 “ 选课 ” 表中成绩大于等于 70 与选修了 “ 0310 ” 课程的学生学号、课程号及成绩交集数据。
代码如下:
SELECT 学号, 课程号, 成绩
FROM 选课
WHERE 成绩 >= 70
INTERSECT
SELECT 学号, 课程号, 成绩
FROM 选课
WHERE 课程号 = '0310'3. EXCEPT 运算符
EXCEPT 运算符是求差操作，从左查询中返回右查询没有找到的所有非重复值，作为联合查询的结果集，该结果集包含两个或更多查询均返回的所有非重复值。
语法格式:
SELECT 语句1
{EXCEPT <SELECT语句2> } [,…n]
其语法说明与 UNION 运算相同。利用 EXCEPT 查询 “ 选课 ” 表中成绩大于 70 与选修了 " 0310 " 课程的学生学号、课程号及成绩差集数据。
代码如下:
SELECT 学号, 课程号, 成绩
FROM 选课
WHERE 成绩 >= 70
EXCEPT
SELECT 学号, 课程号, 成绩
FROM 选课
WHERE 课程号 = '0310'
GO三. 子查询
子查询是一个 SELECT 查询，它返回单个值且嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其他子查询中。
任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。
子查询能够将比较复杂的查询分解为几个简单的查询，而且子查询可以嵌套，嵌套查询的执行过程是：首先执行内部查询，查询出的数据并不被显示，而是传递给外层语句，并作为外层语句的查询条件来使用。
1. 一般子查询
使用 子查询 查询学生 “ 王一枚 ” 所在的班级名称。
代码如下:
SELECT 班级名称
FROM 班级
WHERE 班号 =
(SELECT 班号
FROM 学生基本信息
WHERE 姓名 = '王一枚')2. 带有 IN 或 NOT IN 的子查询
在嵌套查询中，子查询的结果通常是一个集合。运算符 IN 或 NOT IN 是嵌套查询中使用最频繁的运算符，用于进行一个给定值是否在子查询结果集中的判断。其语法格式为:
表达式 [NOT] IN（子查询)
说明 : 当表达式与子查询的结果表中的某个值相等时，IN 谓词返回 TRUE，否则返回 FALSE，若使用了 NOT，则返回的值刚好相反。在 “ 学生基本信息 ” 表、“ 选课 ” 表与 “ 课程 ” 表中查询选修了 “ 西方经济学 ” 课程的学生情况。
代码如下:
SELECT *
FROM 学生基本信息
WHERE 学号 IN
(SELECT 学号
FROM 选课
WHERE 课程号 = (
SELECT 课程号
FROM 课程
WHERE 课程名 = '西方经济学'))3. 带有比较运算符的子查询
带有 IN 运算符的子查询返回的结果是集合，而带有比较运算符( =、<>、>、>=、<、<= ) 的子查询可以返回单值结果，可以看作是 N 子查询的扩展。其语法格式为:
表达式 { < | <= | = | > | >= | != | !< | !> } { ALL | ANY } (子查询)
其中，ALL 和 ANY 说明对比较运算符的限制。
ALL指定表达式要与子查询的结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才回返 TRUE，否则返回 FALSE。ANY 表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回 TRUE，否则返回 FALSE。在 “ 教师 ” 表与 “ 系部 ” 表中查询其他系中比 “ 航海系 ” 任一教师年龄小的教师基本信息。
代码如下:
SELECT * FROM 教师
WHERE 出生日期 > ANY(SELECT 出生日期
FROM 教师
WHERE 系部代码 = (SELECT 系部代码
FROM 系部
WHERE 系部名称 = '航海系'))
AND
系部代码 <> (SELECT 系部代码
FROM 系部
WHERE 系部名称 = '航海'
ORDER BY 出生日期 ASC在 “ 教师 ” 表中查询与 “ 赵安 ” 同在一个系的教师基本信息。
代码如下:
SELECT * FROM 教师
WHERE 系部代码 =
(SELECT 系部代码
FROM 教师
WHERE 姓名 = '赵安')4. 带有 EXISTS 运算符的子查询
使用 EXISTS 运算符后，子查询不返回任何数据，主要用于测试子查询的结果是否为空表。若子查询的结果集不为空，则 EXISTS 返回 TRUE，否则返回 FALSE。EXISTS 还可与 NOT 结合使用，即 NOT EXISTS，其返回值与 EXISTS 则刚好相反，其语法格式为：
[NOT] EXISTS (子查询)用 EXISTS 运算符查询在 “ 教师 ” 表中与 “ 赵安 ” 同在一个系的教师基本信息。
代码如下:
SELECT *
FROM 教师
AS
T1 WHERE EXISTS
(SELECT* FROM 教师
AS T2
WHERE T2.系部代码 = T1.系部代码 AND T2.姓名 = '赵安')从 STUMS 数据库中，查询没有选修过任何课程的学生的学号和姓名。
代码如下:
SELECT 学号, 姓名
FROM 学生基本信息
WHERE NOT EXISTS (SELECT *
FROM 选课
WHERE 学号 = 学生基本信息.学号)
GO5. 在查询的基础上创建新表
使用 SELECT 的 INTO 子句可以创建新表来保存查询结果。新表可以是一个永久表或一个临时表。
在 “ 学生基本信息 ” 表与 “ 班级 ” 表中查询学生姓名和班级名，将结果行插入到新表 “ 学生_班级 ” 中。
代码如下:
SELECT 姓名, 班级名称
INTO 学生_班级
FROM 学生基本信息,班级
WHERE 学生基本信息.班号 = 班级.班号创建一个空的 “ 教师 ” 表的副本
代码如下:
SELECT *
INTO 教师副本
FROM 教师
WHERE 1>3注:此例中只需创建一个空表，而不需要原表任何记录，可以采用在 WHERE 子句的条件设为“假”的方法。
四. 索引
1. 创建索引
- 在 STUMS 数据库的班级表上按班号创建一个名为 bj_bh_index 唯一聚集索引。
USE STUMS
GO
CREATE UNIQUE CLUSTERED INDEX bj_bh_index ON 班级(班号)
GO- 在 STUMS 数据库的班级表上按班级名称创建一个名为 bj_bjmc_index 的唯一非聚集索引。
USE STUMS
GO
CREATE UNIQUE NONCLUSTERED INDEX bj_bjmc_index ON 班级(班级名称)
GO在 STUMS 数据库的 “ 学生基本信息 ” 表上按学号建立聚集索引 xs_xh_index。
若本例曾用 SSMS 创建过，若再用命令创建，将会导致索引创建失败。为避免在同一表中，重复创建聚集索引或同名创建索引，可使用短语 WITH DROP_EXISTING 删除己存在的索引。
代码如下:
USE STUMS
GO
CREATE CLUSTERED INDEX xs_xh_index ON 学生基本信息(学号)
WITH DROP_EXISTING
GO在 STUMS 数据库的班级表中，按 bj_bjmc_index 索引指定的顺序，查询班级信息。
USE STUMS
GO
SELECT *
FROM 班级
WITH (INDEX(bj_bjmc_index))
GO在 STUMS 数据库的选课表上按 学号 + 课程号 建立唯一非聚集索引 xk_xhkch，其填充因子为 60。
USE STUMS
GO
CREATE UNIQUE NONCLUSTERED INDEX xk_xhkch ON 选课(学号,课程号)
WITH FILLFACTOR = 60
GO2. 禁用索引
禁用 “ 学生基本信息 ” 表的非聚集索引 xs_xm_index。
代码如下:
USE STUMS
GO
ALTER INDEX xs_xm_index ON 学生基本信息 DISABLE
GO当使用 xs_xm _index 索引查询学生基本信息数据时，系统会提示此索引已被禁用。
3. 其他操作
重新组织 “ 学生基本信息 ” 表上的聚集索引 xs_xh_index 。
代码如下:
USE STUMS
ALTER INDEX xs_xh_index ON 学生基本信息 REORGANIZE重新命名索引 xk_xhkch 为 xk_xhkch_index。
EXEC sp_rename '学生基本信息. xk_xhkch','xk_xhkch_index'使用填充因子值 70，重建班级表上所有索引。
USE STUMS
GO
ALTER INDEX ALL ON 班级
REBUILD
WITH (FILLFACTOR = 70)
GO查看班级表上的索引信息。
EXEC sp_helpindex '班级'
EXEC sp_help '班级'删除班级表上的所有索引。
DROP INDEX 班级使用DROP INDEX语句删除学生基本信息表上的 xs_new_index 索引和表上的 js_bhkchbh_index 索引。
代码如下:
DROP INDEX 学生基本信息.xs_new_index,教师任课.js_bhkchbh_index说明:
删除表或视图时，自动删除在表或视图上创建的索引。删除聚集索引时，表上的所有非聚集索引都将被重建。
只有表的所有者可以删除其索引，所有者无法将该权限转让给其他用户。
五. 视图
1. 单表视图
创建一个名为 CJ_BK_VIEW 的视图，该视图中包含不及格学生的学号、姓名、课程名，成绩和所在的班级名信息。
CREATE VIEW CJ_BK_VIEW
AS
SELECT 学号, 姓名, 课程名, 成绩, 班级名
FROM 学生基本信息
WHERE 成绩 < 60
GO创建一个名为 JS_RK_VIEW 的视图，该视图中包含教师编号、姓名、课程名、学时、授课班级和学生数。
CREATE VIEW JS_RK_VIEW
AS
SELECT 教师编号, 姓名, 课程名, 学时, 授课班级, 学生数
FROM 教师信息, 学生基本信息
GO创建一个名为 xs_07_view 的视图，该视图中只含有 “ 07 ” 系的学生基本信息。
CREATE VIEW xs_07_view
AS
SELECT *
FROM 学生基本信息
WHERE 系 = '07'
GO2. 多表视图
创建描述学生成绩的视图 xs_cj_view，该视图中包含学号、姓名、性别、课程名和成绩等数据内容。
代码如下:
CREATE VIEW xs_cj_view
AS
SELECT 学生基本信息.学号, 姓名, 性别, 课程名, 成绩
FROM 学生基本信息, 选课, 课程
WHERE 学生基本信息.学号 = 选课.学号 AND 选课.课程号 = 课程.课程号
GO本例创建的是基于学生基本信息表、选课表和课程表等三张表的视图，即多表视图。
3. 基于视图的视图
定义一个反映女生学习成绩的视图 Nxs_cj_view。
代码如下：
CREATE VIEW Nxs_cj_view
AS
SELECT *
FROM xs_cj_view
WHERE 性别 = '女'
GO本例创建的是基于视图的视图。
假设我们已创建了描述所有学生学习成绩情况的视图，本例要的是女生的学习成绩情况。因此，可以通过 xs_ cj _view 视图来创建 Nxs_cj_view 视图，简化其操作。
4. 基于视图的操作
通过 CJ_BK_VIEW 视图查询补考的学生信息。
SELECT *
FROM CJ_BK_VIEW
WHERE 成绩 < 60 AND 成绩 > 35
GO通过 07_NJ_VIEW 视图进行插入、修改和删除，数据由自己拟定。
插入
INSERT INTO 07_NJ_VIEW
VALUES('0201005', '张荣', '男', '1976-4-23', '副教授')
GO修改
UPDATE 07_NJ_VIEW
SET 姓名 = '王一枚'
WHERE 姓名 = '王敏'
GO删除
DELETE 07_NJ_VIEW
WHERE 性别 = '男'
GO使用系统存储过程 sp_depends 查看 07_NJ_VIEW 视图的相关性。
Exec sp_depends 07_NJ_VIEW5. 修改视图
使用 ALTER VIEW 语句修改视图，使 CJ_BK_VIEW 为加密视图。
ALTER VIEW CJ_BK_VIEW
WITH ENCRYPTION将 07_NJ_VIEW 视图重命名为 NEW_VIEW。
EXEC sp_rename '07_NJ_VIEW ', 'NEW_VIEW'6. 删除视图
删除 CJ_BK_VIEW 视图。
DROP VIEW CJ_BK_VIEW六. 存储过程应用
1. 创建并执行存储过程
在 STUMS 数据库中，创建查询学生成绩的存储过程 xs_cj_proc。
代码如下:
USE STUMS
GO
CREATE PROC xs_cj_proc
AS
SELECT 学生基本信息.学号, 姓名, 课程名, 成绩
FROM 学生基本信息 JOIN 选课 ON 学生基本信息.学号 = 选课.学号
JOIN 课程 ON 选课.课程号 = 课程.课程号在查询分析器中执行 xs_cj_proc 存储过程。
代码如下:
EXEC xs_cj_proc
GO2. 带参数的存储过程
在 STUMS 数据库中，创建一个名为 XIBU_INFO_PROC 的存储过程，它带有一个输入参数，用于接受系部代码，显示该系的系部名称、系主任和联系电话。
代码如下:
USE STUMS
GO
CREATE PROCEDURE XIBU_INFO_PROC
@XBDM CHAR(2)
AS
SELECT 系部名称，系主任，联系电话
FROM 系部
WHERE 系部代码 = @XBDM执行 XIBU_INFO_PROC 存储过程，查询系部代码为 03 的系部信息。
代码如下:
EXEC XIBU_INFO_PROC '03'
GO在 STUMS 数据库中，创建一个名为 KC_INS_PROC 的存储过程，执行该存储过程将完成向课程表插入一条记录，新记录的值由参数提供。
代码如下:
USE STUMS
GO
CREATE PROCEDURE KC_INS_PROC
@KCH char(4),
@KCM varchar(20),
@KCXZ char(1),
@XF tinyint
AS
INSERT 课程 VALUES(@KCH,@KCM,@KCXZ,@XF)执行 KC_INS_PROC 存储过程，完成向课程表中插入一数据行（ ’ 0303 ’ , ’ VB程序设计 ’ , ’ A ’ , 5 )
代码如下:
EXEC KC_INS_PROC '0303','VB程序设计','A',5
GO3. 创建并执行带有通配符参数的存储过程
在 STUMS 数据库中，创建一个名为 JS_CX_PROC 的存储过程，执行该存储过程，查询教师表中同姓的老师信息。
代码如下:
USE STUMS
GO
CREATE PROCEDURE JS_CX_PROC
@XM VARCHAR(8) = '_%'
AS
SELECT *
FROM 教师
WHERE 姓名 LIKE @XM
GO执行 JS_CX_PROC 存储过程，查询所有姓王的教师信息。
代码如下:
EXEC JS_CX_PROC '王%'
GO4. 创建并执行带有输出参数的存储过程
在 STUMS 数据库中，创建一个存储过程 tj_nopass_num 统计未通过考试的学生人数。
代码如下:
USE STUMS
GO
CREATE PROCEDURE tj_nopass_num
@count int OUTPUT
AS
SELECT @count = COUNT(*)
FROM 选课
WHERE 成绩 < 60
GO执行 tj_nopass_num 存储过程,统计考试不及格的人数。
代码如下:
DECLARE @tj int /*定义变量*/
EXEC tj_nopass_num @tj OUTPUT
PRINT @tj /*在屏幕上显示统计结果*/
GO☆ 需要强调的是，执行带有输出参数的存储过程，需定义一个变量接受输出参数这回的值，而且在该变量后面也需要跟随 OUTPUT 关键字。
在 STUMS 数据库中，创建一个存储过程 js_xl_tj_proc，其功能是从 “ 教师 ” 表中根据输入的学历名称，统计出相应的人数。
这是要创建带有一个输入参数和输出参数的存储过程。
代码如下:
USE STUMS
GO
CREATE PROCEDURE js_xl_tj_proc
@xl char(8), /*定义输入参数*/
@rs int OUTPUT /*定义输出参数*/
AS
BEGIN
select @rs = count(*)
from 教师
where 学历 = @xl
GROUP BY 学历
END
GO执行 js_xl_tj _proc 存储过程,统计教师表中 “ 大学本科 ” 的人数。
代码如下:
DECLARE @xl char(8), @rs int
SET @x = '大学本科'
EXEC js_xl_tj_proc @xl, @rs OUTPUT
PRINT @xl + STR(@rs) + '人'
GO5. 存储过程的重编译处理
1 在建立存储过程时设定重编译其语法格式如下:
CREAT PROCEDURE procedure_name
WITH RECOMPILE
AS sql_statement2 在执行存储过程时设定重编译其语法格式如下:
EXECTUE procedure_name WITH RECOMPILE3 通过使用系统存储过程设定重新编译其语法格式如下:
EXEC sp_recompile procedure_name利用 sp_recompile 命令为存储过程 xs_cj_proc 设定重编译标记。
EXEC sp_recompile xs_cj_proc运行后提示：已成功地将对象 xs_cj _proc 标记为重新编译。
6. 查看存储过程
使用系统存储过程 sp_helptext 查看存储过程的源代码
语法如下:
EXEC sp_helptext procedure_name其中，
procedure_name 为用户需要查看的存储过程名称。
利用 sp_ helptext 存储过程查看 KC_INS_PROC 的源代码。
代码:
EXEC sp_ helptext KC_INS_PROC使用 sp_ help 、sp_depends 和sp_stored_procedures 查看 xs_cj_proc 存储过程的相关信息。
代码如下：
EXEC sp_ help xs_cj_proc --查看存储过程的参数、类型等一般信息
EXEC sp_depends xs_cj_proc --查看存储过程的依赖关系及列引用等相关性信息
EXEC sp_stored_procedures xs_cj_proc --查看当前数据库中的存储过程列表7. 修改存储过程
使用 ALTER PROCEDURE 语句修改存储过程
修改存储过程 xs_cj_proc，查询课程成绩不及格的学生的学号、姓名、课程名和成绩。
代码如下：
USE STUMS
GO
ALTER PROC xs_cj_proc
AS
SELECT 学生基本信息.学号, 姓名, 课程名,成绩
FROM 学生基本信息 JOIN 选课 ON 学生基本信息.学号=选课.学号
JOIN 课程 ON 选课.课程号 = 课程.课程号
WHERE 成绩 < 60
GO8. 删除存储过程
DROP PROCEDURE 语句可以一次从当前数据库中将一个或多个存储过程或过程组删除，其语法格式如下：
DROP PROCEDURE 存储过程名[，…n]
删除存储过程 xs_cj_proc
代码如下：
DROP PROCEDURE xs_cj_proc
GO
