第1章 数据库 的 设计 Netstopmssqlserver ( 开启 SQL) Netstartmssqlserver( 关闭 SQL) 1.1 设计 数据库 的步骤： 需求分析阶段 1)收集信息 2)标识实体 3)标识每个实体需要存储的详细信息 4)标识实体之间的关系 概要 设计 阶段 5)绘制 E-R 图 6)将 E-R 图转
第1章 数据库 的 设计
Net stop mssqlserver ( 开启 SQL)
Net start mssqlserver ( 关闭 SQL)
1.1 设计 数据库 的步骤：
需求分析阶段
1) 收集信息
2) 标识实体
3) 标识每个实体需要存储的详细信息
4) 标识实体之间的关系
概要 设计 阶段
5) 绘制 E-R 图
6) 将 E-R 图转换为 数据库 模型图
7) 遵循三大范式
详细 设计 阶段
三个范式的含义
1) 确保每列的原子性
2) 确保表中的每列都和主键相关（只描述一件事）
3) 确保每列都和主键列直接相关，而不是间接相关（两列不能描述同一件事）
三个范式的优点
4) 有助于规范化 数据库 的 设计
5) 有助于减少数据沉（冗）余
1.2 基数都有哪几种基数
1. 一对一： 1: 1 eg: 夫妻
2. 一对多 : 1: N eg: 一个老师可以有多个学生
3. 多对一 : N: 1 eg: 多个学生可以有一个老师
4. 多对多： M : N eg: 群殴
1.3 画 E-R 图 要用哪几个图形
l 矩形——实体集
l 椭圆——属性
l 菱形——联系集
l 直线——连接属性和实体集，也用来联系实体集和联系集
1.4 实体规范化和性能的关系
规范化——从关系型 数据库 表中除去沉（冗）余数据的过程
用于获得高效的关系型 数据库 表的逻辑结构的最好和最容易的方法
设计 数据库 时要遵守三大范式，满足的范式级别越高，系统性能就越低，因此允许适当的数据沉余列
一 . SQL Server 数据库 的基础
1 . 数据库
含义：由表，关系，以及操作对象组成。
作用：存储数据、检索数据、生成新的数据
要求：统一、安全、性能等
按用途可分为：系统 数据库 用户 数据库
行：实际对应一个实体 （一个实体一条记录）（实体）
列：（字段）（属性）
表：实体的集合，用来存储具体的数据的。
2 数据库 系统和 数据库 管理系统
数据库 管理系统：是一种系统软件，由一个相互关联的数据集合和一组访问数据的程序构成。
这个数据集合称为 数据库
作用：维护 数据库
数据库 系统：是一个实际可运行的软件系统，可以对系统提供数据进行存储、维护、应用。
通常有 ：软件、 数据库 、 数据库 管理员组成。
3.Miscrosoft SQL Server 提供了 4 个系统 数据库
（ 1 ）： Master 数据库 ： 所有的登录账号和系统配置设置
所有其他的 数据库 及 数据库 文件的位置
Sql server 的初始化信息
（ 2 ）： Tempdb 数据库 ：保存所有的临时表和临时存储过程，以及临时生成的工作表 （启动时都重新创建）
(3): Model 数据库 ：创建的所有 数据库 的模板。
（ 4 ）： Msdb 数据库 ：代理调度警报、作业、以及记录操作时使用。
4.创建 数据库
一个 数据库 至少包含一个 数据库 文件和一个事物日志文件。
mdf是 primary data file 的缩写
ndf是 Secondary data files 的缩写
（ 1 ）： 数据库 文件：主 数据库 文件的扩展名为 .mdf , 用来存储 数据库 的启动信息数据。 一个 数据库 只能那个由一个主 数据库 ，其他 数据库 文件被称为次 数据库 文件。
（ 2 ）：事物日志文件：扩展名为： .ldf, 事物日志文件名后需要加一个[ _Log ]。
一个数据可以有一个或多个事物日志文件。
（ 3 ）：次文件：扩展名为： ndf ; 次文件可有可无， 由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果 数据库 超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样 数据库 就能继续增长。
第2章 数据库 的实现
2.1 建库删库
【解决方案：】
use master -- 设置当前 数据库 为 master 便于访问 sysdatabases
--exists( 查询 语句 ) 检测某个 查询 是否存在
if exists(select * from sysdatabases where name='MySchool')
Drop dataBase MySchool -- 删除后再创建
2.2 创建文件夹
//****// 在 D 盘创建名为 project的文件夹
exec sp_configure 'show advanced options',1 // 显示 高级 配置信息
reconfigure
exec sp_configure 'xp_cmdshell',1 // 需要执行 sp_configure启用xp_cmdshell
reconfigure
exec xp_cmdshell 'mkdir D:\project',NO_output //DOS 命令 创建文件夹
扩展存储过程（ Extened Stored Procedures ）允许使用其他编程语言创建外部存储过程，为用户提供 SQL Sever 实例到外部程序的接口，便于维护。以[ xp_]为前缀，以 DLL 形式单独存在
2.3 建表删表
use MySchool // 指明在哪个 数据库 中建表
// 判断若存在先删除
if Exists(select * from sysobjects where name ='Student')
drop table Student
// 创建表
create table Student
(
//identity ( 标识增量，标识种子 )
// 标识列不能为 varchar 类型，只能是（ bigint(8),int(4), smallint(2),tinyint(1(0-255 之间 )),decimal ）
StudentNo int not null identity (1,1), // 非空，是标识列
LoginPwd varchar(20) not null,
StudentName varchar(20) not null,
Sex bit not null,
GradeId int not null,
Phone nvarchar(50) null,
Address nvarchar(255) null,
Borndate datetime not null,
Email nvarchar(50) null,
IndentityCard varchar(18) not null
)
2.3 创建和删除约束
-- 主键
alter table Student
add constraint PK_StudentNo primary key (StudentNo)
-- 唯一 ( 可以为 NULL 值，但不能重复 )
alter table Student
add constraint UQ_IdentityCard unique (IdentityCard)
-- 默认
alter table Student
add constraint DF_Address default(' 安徽 ' ) for Address
-- 检查
alter table Student
add constraint CK_BornDate Check(BornDate>=1990-01-01)
-- 外键 ( 主表 Student和从表Result通过关联列StudentNo建立关系 )
alter table Result
add constraint FK_Student_Result foreign key (StudentNo) references( 引用 Student(StudentNo)
-- 删除约束
alter table Student
drop constraint DF_Address
二． 数据库 表的管理
1 数据完整性：
有四种类型的约束
（ 1 ）：实体完整性约束 ：要求表中的每一行数据反映不同的实体，不能存在相同的数据行
通过： 索引、唯一约束 ( Unique) 、主键约束 ( primary key) 或标识列 ( identity) 属性。
（ 2 ）：域完整性约束： 给定 列的输入有效值
通过：限制数据类型、检查约束 ( check) 、输入格式、外键约束（ Foreign Key References）、
默认值 ( default) 、空约束 ( null) 、非空约束 ( not null)
（ 3 ）：引用完整性约束 : 输入或删除数据行时，此约束用来保持表之间已定义的关系。
通过：主键和外键之间的引用关系实现。
(4): 自定义完整约束
2 ：主键和外键
主键：一个表只能有一个主键
原则：唯一、最少性、非空性、稳定性
外键：一个表可以有多个外键
3 ．标识列：
identity ( 标识种子，标识增量 )
标识列常被定义为主键，在插入数据时，不许为标识列指定值。
4 ．主表和子表的关系
（ 1 ）：子表的相关项目的数据，在主表中必须存在
（ 2 ）：主表中相关项的数据更改了，则子表对性的数据项也应当随之更改。
（ 3 ）：在删除子表之前，不能够删除主表。
5 ．数据类型
分类
备注和说明
数据类型
说明
二进制数据类型
用来存储非字符和文本的数据
binary
固定长度的二进制数据
varbinary
可变长度的二进制数据
image
可用来存储图像
文本数据类型
字符数据包括任意字母、符号、或数字字符的组合
char
固定长度的非 Unicode 字符数据，最大长度为 8000 个字符
varchar
可变长度的 Unicode
( 可存放新词 )
nvarchar
可变长度的 Unicode 数据 ( 全球统一标识符 )
ncahr
固定长度的 Unicode
text
存储长文本信息
ntext
存储可变长度的长文本
日期和时间
日期和时间在单引号内分别输入
DateTime
日期和时间
数字数据
数字 ( 正数、负数、分数 )
Int
占用 4 个字节的整数
smallint
占用 2 个字节的整数
tinyint
占用 1 个字节的整数
货币数据类型
十进制货币值，且精确到小数点 4 位数字
Money
smallMoney
固定四位小数
Bit 数据类型
1 和 0 或空值
Bit
布尔类型（表示是 / 否）
浮点型
近似数值类型
real
供浮点数使用
float
存储精度比较高的（如货币金额）
Decimal(18,2)
numeric
固定精度和范围的数值型数据
第3章 SQL 编程
3.1 使用变量
//***// 根据学号查找 ’ 李斯文’的信息，及与’ 李斯文’相邻的学生信息
use MySchool // 使用 MySchool 数据库
// 声明变量
declare @name varchar(8)
declare @no int
// 局部变量的赋值有两种方法：使用 set 和 select
set @name=' 李斯文 '
select * from Student where StudentName=@name
select @no =StudentNo from Student where StudentName=@name
// 两种方式实现 查询 和 李斯文相邻学号的信息
select * from Student where (StudentNo=@no -1) or (StudentNo=@no +1)
select * from Student where (StudentNo in (@no+1,@no -1))
编号
区分方面
set
select
同时对多个变量赋值
不支持
支持
表达式返回多个值时
出错
将返回的最后一个值赋给变量
表达式未返回值时
变量被赋空值
变量保持原值
eg:
1. 【 set 不能同时为两个变量赋值】
2. 【 A ==set 只能赋一个值 B==select 查询 有多个值时，赋值 查询 的最后一个结果】
3 ．【 查询 无结果时 set == @addr 被赋 NULL 值 select ==@addr 保持原值 】
【警告：】select @addr=(select Address from Student where 1
3.2 全局变量
【@@ERROR >0 表示上一条执行语句有误 】
【注意】 常用的全局变量有：@@ERROR ——错误号
@@IDENTITY ——标识列
@@RowCount ——受影响的行数
3.3 输出语句
print 局部变量或字符串 // 在消息框中显示
select 局部变量 as 自定义列名 // 在结果框中显示
eg:
print '服务器的名称：'+@@ServerName
select @@ServerName as '服务器的名称'
3.4 数据类型转换
Cast （表达式 as 数据类型） Cast（date as varchar(10)）
Convert(varchar(10),@date,111)
Convernt （数据类型（长度） ， 表达式 ， 日期型（ 1-131 ）、浮点型指定转换的格式）
print ' 成绩： ' +convert(varchar(20) ,@result)
print ' 成绩： ' +cast(@result as varchar(20)))
获取年龄
@age=floor(DATEDIFF(DY,Borndate,GETDATE())/365)
Floor 小于某个数的最大整数 eg: Floor(27.7) 27( 周岁 )
Ceiling 大于某个数的最小整数 eg: Ceiling (27.7) 28( 虚岁 )
3.5 逻辑控制语句
顺序 begin ~ end 相当于 大括号 {}
分支 if ~ else 和 case ~ end
循环 while
set nocount on -- 不显示 [n 行受影响 ] ，写在执行 SQL 语句的上面
3.6 批处理
GO ——把 SQL 语句批量处理（以一条命令的方式来处理一组命令的过程称为 批处理。）
它可以提高语句执行的效率。
【★☆】[不显示 n 行受影响]—— set nocount on
三．数据管理
1 ． T-SQL 的组成
DML( 数据操作语言 ) ： 用来插入、删除、修改、 查询 数据库 中的数据 ( insert delete update select)
DCL ( 控制 ) ： 用来控制 数据库 组件的存取许可、存取权限等。（ Grant Revoke ）
DDL ( 定义 ): 用来建立 数据库 、 数据库 对象和定义其列 。
大部分 Greate 开头的命令 ：（ Greate Table Great view drop Tabel ）
2. 比较运算符：
> = ( 不等于 ) ! ( 非 )
3. 通配符
通配符
解释
示例
‘_’
一个字符
A like ‘C_’
%
任意长度的字符串
B like ‘CO_%’
[]
括号里所有指定范围内的一个字符
C like ‘9wo[1-2]’
[^]
不再括号中指定范围内的任意一个字符
D like like’9wo[^1-2]’
通配符经常与 like 关键字使用。
4. 逻辑表达式
T-sql 支持的逻辑运算符有 not 、 And 、 or 。
eg: 采购订单表中—付款方式：信用卡
—约束要求：牡丹卡、金穗卡、龙卡、阳光卡
Not( 付款方式 = ’信用卡’) or ( 信用卡 in ( ‘牡丹卡’ , ’ 金穗卡’ , ’ 龙卡’ , ’ 阳光卡’) )
在一个语句中使用了多个逻辑运算符时，首先求not 值，然后求and值，最后且or值
注：int 关键字用来限制范围。
列名 Between 低值 And 高值
Where 条件种类 And Or Not( 与，或，非 )
In( 值列表 ) eg ： Select * from stuInfo Where stuAge IN (21,25)
Like( 模糊 查询 )
5.SQL 语句
增
1. 一次插入一行
Insert [into] 表名 > [ 列名列表 ] values 值列表 >
列名列表顺序可自己指定，但值列表的顺序应该和列名列表相同
2. 一次插入多行
l Insert [into] 新表名 >[ 此表必须预先创建 ]
select 列名 > from 查询 的表 >
l Select 列名 > into 新表名 >[ 必须预先没有 ] from 查询 的表 >
l Insert 查询 的表 >[ 列名 1, 列名 2, 列名 3] ——得到的是虚拟表
Select ' ', ' ', ' ' union
Select ' ', ' ', ' ' union
Select ' ', ' ', ' '
删
Delete from 表名 > [where 删除条件 >]
只要删除就是删除整条记录，不会只删除单个字段，所以在 delete 后不出现字段名。
注：delete...from...只要删除就是删除整条记录，不会删除单个字段，所有在 delete 后不能出现字段名，例如：
[delete SAddress from Students] 此语句将会报错
Truncate table 表名 >
注意： 1 执行删除不能删除主外建关系的表数据
2 删除表中所有数据
3 执行效率高（快）
改
Update 表名 > set 列名 = 更新值 > [where 更新条件 > ]
查
Select 列名 >
From 表名 >
Where [ 查询 条件表达式 ]
Group by [ 分组 ]
Having [ 过滤 ] ——不单独使用，只与 group by 一起使用 ( 相当于 Where)
[order by 排序的列名 > [ ASC 或DESC（降序） ]
1. 选择指定数量的记录 , 通常配合 order By 使用
Eg; Select Top 2 * From stuInfo Order By stuAge Desc
第4章 高级 查询
4.1 子 查询
含义：嵌入的 查询 称为子 查询 或嵌套 查询
实现方式：可通过关系运算符、 in 、 exists 实现的 查询
注意：子 查询 语句必须放置在一对圆括号内。
与关系运算符使用时，必须保证子 查询 返回的值不能多于一个
举例：
-- 查询 最近一次 C# OOP 考试成绩最高分和最低分
select 科目='C# OOP',MAX(StudentResult) as 最高分 , Min(StudentResult)as 最低分 from dbo.Result
where SubjectNo=(select SubjectNo from dbo.Subject where SubjectName='C# OOP')
and ExamDate=(select Max(ExamDate)from dbo.Result where SubjectNo=(select SubjectNo from dbo.Subject where SubjectName='C# OOP'))
go
4.2 表联接
内联接——返回两表的交集
select 列名，列名，列名，列名
from 表1 inner join 表2 on (表1. 列名=表2. 列名)
inner join 表3 on 主外键关系
第一个 on 主外键关系是表 1 和表 2 的
第二个 on 主外键关系是表 2 和表 3 的或表 1 和表 3
(在内连接中，参与连接的表的地位是平等的，没有主从之分)
外联接—— A C B
1. 左外联接（ left join 或 left outer join ） A+C
2. 右外连接（ Right join 或 Right outer join ） B+C
3. 完整外联接 （ Full join 或 Full outer join ） A+B+C
select 列名，列名，列名，列名
from表1 left join 表2 on (表1. 列名=表2. 列名)
left join 表3 on 主外键关系
第一个 on 主外键关系是表 1 和表 2 的
第二个 on 主外键关系是表 2 和表 3 的
(在外部的连接中参与连接的表有主从之分，left join 左边的表是主表，右边是从表 )
交叉联接——返回两表之积
4.3 in 和 not in
和where做 查询 使用
-- 查询 S1 学期开设得课程 （ in ）（ not in 查询 的是非 S1 开始的课程）
select SubjectName from dbo.Subject
where GradeID in (select GradeID from dbo.Grade where GradeName='S1')
go
4.4 Exists 和 not Exists
和if 做判断使用
-- 如果有 S1 的学生，就将他的在读年级更新为 S2
if exists( SELECT * FROM Student INNER JOIN Grade ON
Student.GradeId=Grade.GradeId WHERE GradeName='S1')
begin
update dbo.Student set GradeId=(select GradeID from dbo.Grade where GradeName='S2')
from dbo.Student inner join dbo.Grade on Student.GradeId=Grade.GradeID
where GradeName='S2'
end
go
4.5 表联接和子 查询 的区别
表联接都可以用子 查询 替换，反之不可以。
子 查询 ——适合于作为 查询 的筛选条件
表联接——更适合于查看多表的数据
4.6 子 查询 的注意事项
1. 父 查询 中的子 查询 可包括
select 、 from 、 where 、 group by 、 having
但排序语句（ order by ）不能写在子 查询 中
1. 子 查询 的 select 子句中不能出现 text 、 ntext 、 image 数据类型的列
2. 子 查询 中包含而父 查询 中没有的列不能包含在 输出列中
select * from (select * from Result) Info
第5章 ??☆☆ SQL 函数
5.1 字符串函数
/* 字符串函数 */
--1. 查找一个字符串在另一个字符串中位置
-- 从第一个位置开始查找青鸟在合肥科海北大青鸟中出现的位置
select CharIndex(' 青鸟 ' ,' 合肥科海北大青鸟 ' ,1)
--2. 返回给定字符串的长度
select len(' 合肥科海北大青鸟 ' )
--3. 将字符串转换成大写
select upper('hefei-kehai')
--4. 将字符串转换成小写
select lower('HEFEI-kehai')
--5. 去除字符串左边空格
select ltrim(' hefei ')
--6.去除字符串右边空格
select rtrim(' hefei ')
--7.从左边返回指定长度的字符串
-- 返回结果(合肥科海)
select left('合肥科海北大青鸟',4)
--8.从右边返回指定长度的字符串
-- 返回结果(北大青鸟)
select right('合肥科海北大青鸟',4)
--9. 替换字符串中的字符
-- 合肥替换成安徽
select replace(' 合肥科海北大青鸟 ' ,' 合肥 ' ,' 安徽 ' )
--10. 在指定位置删除指定的长度的字符串，并在此插入行的字符串
-- 删除科海插入圣大
select stuff(' 合肥科海北大青鸟 ' ,3,2,' 圣大 ' )
--11. 从指定位置返回指定长度的字符串
-- 返回结果北大青鸟
select substring(' 合肥科海北大青鸟 ' ,5,4)
5.2 日期函数
/* 数据库 日期函数 */
--1. 获取系统当前日期
select getdate()
--2. 将指定的数增加到指定的日期上
-- 返回对月增加结果 -11-06 0:000
select dateAdd(mm,2,'2009-09-06')
--3. 返回两个日期指定部分之差
-- 返回年差 结果为
select datediff(yy,'2000-07-07','2009-02-09')
--4. 返回日期中指定部分的字符串
-- 返回日期中的日为
select dateName(dd,'2009-9-18')
--5. 返回日期中指定部分的整数
-- 返回日期为
select datePart(day,'2009-9-15')
/* 截取各种时间格式 */
--1. 截取时间格式为 -09-15( 年 - 月 - 日 )
select convert(varchar(10),getdate(),20)
--2. 截取时间格式为 :28:34( 时 - 分 - 秒 )
select convert(varchar(10),getdate(),8)
--3. 截取时间格式为 -09-15 21:31:03( 年 - 月 - 日 - 时 - 分 - 秒 )
select convert(varchar(20),getdate(),120)
--4. 截取时间格式为 -15-2009( 月 - 日 - 年 )
select convert(varchar(100),getdate(),110)
--5. 截取时间格式为 /09/15( 年 / 月 / 日 )
select convert(varchar(100),getdate(),11)
5.3 数学函数
--1. 取绝对值
select ABS(-43)
--2. 取大于或等于指定数值、表达式的最小整数
select ceiling(43.5)
--3. 取小于或等于指定数值、表达式的最大整数
select floor(43.5)
--4. 取幂值
select power(5,2)
--5. 四舍五入到指定精度
select round(43.543,1)
--6. 正数返回 +1 、负数返回 -1 、返回
select sign(-43)
--7. 取浮点表达式的平方根
select sqrt(9)
5.4 聚合函数
1. sum 放回数值的总和 （只能用于数字类型的列 ，不能汇总字符、日期等其他数据类型）
2. avg 返回数值的平均值 （只能用于数字类型的列）
3. max（返回最大值） 和min（返回最小值）
（可以用于数字型、字符型、以及时间 / 日期类型）
4 ． count （返回提供的表达式非空值的计数 ）（可用数字和字符串类型）
第6章 事务、视图和索引
6.1 事务
含义：
是单个逻辑工作单元执行的一系列操作。
如果某一事务成功，则该事物中进行的所有数据更改均会提交，成为 数据库 的永久组成部分。
如果事务遇到错误且必须取消或回滚，则所有数据更改均被清楚
特性：
一个逻辑单元必须有四个属性（ACID）。
原子性（Atomicity ） 一致性（ Consistency ） 隔离性（ Isolation ） 持久性（ Durability ）
语法：
begin transaction -- 开始事务
commit transaction --提交事务
rollback transaction --回滚事务
分类：
显式事务——用begin transaction明确指定事务的开始
隐式事务——通过设置set implicit_transactions on 将隐式事务模式设置为打开。
无需描述每个事务的开始，只需要提交或回滚每个事务
自动提交事务——SQL Server 的默认模式，它将每条 T-SQL 语句视为一个事务，自动提交或回滚
编写事务的原则：
l 事务尽可能简短
l 事务中访问的数据量尽量最少
l 查询 数据时尽量不要使用事务
l 在事务处理过程中尽量不要出现等待用户输入的操作
6.2 视图
含义：
保存在 数据库 中的 select 查询 。是一种虚拟表，由一个或多个表中的列和行组合而成。
使用原因：
1. 数据的安全性。用户不必查看整个 数据库 结构，有所侧重
2. 符合用户日常业务逻辑，使他们对数据更容易理解
如何操作视图：
l 筛选表中的行
l 防止未经许可的用户访问敏感数据
l 将多个物理数据表抽象为一个逻辑数据表
特点：
视图并不是 数据库 中存储的数据值的集合，它的行和列来自 查询 中引用的表。在执行时，直接显示表中的数据。
使用方法：
一：使用 Microsoft SQL Server Management Studio
1. 选中 数据库 下的[视图]，右击选择[新建视图]
2. 在打开的对话框中选择需要连接的表后，单击[添加]按钮
3. 选择希望查看的列，可以取别名，然后单击 即可
二：使用 T-SQL 语句
use MySchool
go
-- 检测视图是否存在，视图记录存放在系统表 sysobjects中
if exists(select * from sysobjects where name='vw_Stu_Result_Subject')
drop view vw_Stu_Result_Subject
go
-- 创建视图
create view vw_Stu_ReSub
as
SELECT Student.StudentName AS 学生姓名, Student.Phone AS 联系电话,
Result.StudentResult AS 成绩, Result.ExamDate AS 考试日期,
Subject.SubjectName AS 课程名称
FROM Student
INNER JOIN Result ON Student.StudentNo = Result.StudentNo
INNER JOIN Subject ON Result.SubjectNo = Subject.SubjectNo
go
-- 查看视图结果
select * from vw_Stu_Result_Subject
注意事项：
l 每个视图中可使用多张表
l 一个视图可以嵌套另一个视图
l 视图定义中的 select 语句不能包括以下内容
Ø Order by 子句，除非 查询 语句的选择列表中也有一个 TOP 子句
Ø Into 关键字
Ø 引用临时表或表变量
6.3 索引
作用：
提升 数据库 的检索速度，改善 数据库 性能
分类：
Ø 唯一索引——不允许两行具有相同的索引值（不建议使用）
Ø 主键索引——是唯一索引的特殊类型，它要求主键中的每个值都是唯一的
Ø 聚集索引——按字母（字母是逻辑顺序，页码是物理顺序）一个表只能由一个聚集索引
Ø 非聚集索引——按笔画排序
Ø 复合索引——多列组合而成的索引
Ø 全文索引——基于标记的功能性索引
使用方法：
一：使用 Microsoft SQL Server Management Studio
在 设计 表中右击[索引 / 键]，点击[添加]——索引列的排序、是唯一的……
二：使用 T-SQL 语句
use MySchool
go
-- 检测是否存在该索引 ( 索引存放在系统表 sysindexes 中 )
if exists(select name from sysindexes where name ='IX_Student_StudentName')
drop index Student.IX_Student_StudentName -- 删除索引
-- 创建非聚集索引，填充因子 30%
--unique(唯一索引) clustered (聚集索引) nonclustered (非聚集索引)
create nonclustered index IX_Student_StudentName
on Student(StudentName)
go
/*-- 指定按索引： IX_Student_StudentName 查询 --*/
select * from Student
with(index=IX_Student_StudentName)
where StudentName like ' 李 %'
建立索引的标准：
Ø 频繁搜索的列
Ø 经常用作 查询 选择的列
Ø 经常排序分组的列
Ø 经常用作连接的列（主键 / 外键）
第7章 存储过程
7.1 存储过程的概念
含义：
是 SQL 语句和控制语句的预编译集合。包括 声明变量、逻辑控制语句、编程
优点：
Ø 模块化程序 设计 ——创建一次，调用多次
Ø 执行速度快，效率高——已创建的存储过程，可直接执行，无需编译优化
Ø 减少网络流量——使用一行执行语句即可
Ø 具有良好的安全性——不同权限用户使用不同存储过程，通过提交的存储过程名称执行
一个完整的存储过程包括以下三个部分：
Ø 输入参数和 输出参数
Ø 在存储过程中执行的 T-SQL 语句
Ø 存储过程的返回值
分类：
一：系统存储过程
二：用户自定义的存储过程
7.2 系统存储过程
exec sp_databases -- 列出当前系统中的 数据库
exec sp_renamedb 'MyBank','Bank' -- 改变 数据库 名称
use MySchool
go
exec sp_tables -- 当前 数据库 中可 查询 对象的列表
exec sp_columns Student -- 查询 Student 表中列的信息
exec sp_help Student -- 查询 Student 表的所有信息
exec sp_helpconstraint Student -- 查看 Student 表的约束
exec sp_helptext 'vw_Stu_Result_Subject' -- 查看视图的语句文本
exec sp_stored_procedures -- 返回当前 数据库 中的存储过程列表
exec sp_helpindex Student -- 查询 Student 表的索引
exec sp_stored_procedures -- 显示存储过程的列表
7.3 用户自定义的存储过程
set @errorSum=@errorSum+@@ERROR -- 显示错误数
set @subjectNo=@@IDENTITY -- 获得课程编号 （获取主键标识列的编号）
l 创建不带参数的存储过程
use MySchool
go
if exists(select * from sysobjects where name='usp_StudentResult')
drop procedure usp_StudentResult -- 删除 存储过程-
go
/*--创建不带参数的存储过程--*/
create procedure usp_StudentResult
as
SELECT Student.StudentName AS 学生姓名, Student.Phone AS 联系电话,
Result.StudentResult AS 成绩, Result.ExamDate AS 考试日期,
Subject.SubjectName AS 课程名称
FROM Student
INNER JOIN Result ON Student.StudentNo = Result.StudentNo
INNER JOIN Subject ON Result.SubjectNo = Subject.SubjectNo
Go --go 不能省，要省略需写 begin end
exec usp_StudentResult -- 调用执行存储过程
l 创建带输入参数的存储过程
create procedure usp_unpass
@subName varchar(50),-- 考试课程
As
-- 输入参数
exec usp_unpass 50, 'SQL Base'
exec usp_unpass @score=50, @subName='SQL Base'
-- 输入参数采用默认值
exec usp_unpass default, 'SQL Base'
exec usp_unpass @subName='SQL Base'
l 创建带 输出参数的存储过程
create procedure usp_query_num
@UnPassNum int output, -- 未通过人数 **** 输出参数
@TotalNum int output, -- 参加考试人数 **** 输出参数
@subName varchar(50),-- 考试课程 **** 输入参数
@score int=60 -- 平均成绩 **** 输入参数
As
-- 声明变量用于接收 输出的值
declare @UnPassNum int
declare @TotalNum int
例如： 1. 输入参数 2.Exec 执行
7.4 处理错误信息
作用：向用户报告错误
语法：
-- 创建存储过程
create procedure usp_存储过程名
as
if(@GradeName is null or @SubjectName is null)
begin
raiserror(' 学期名称和课程名称是否为空 ' ,16,1)
return -- 立即返回，退出存储过程
end
--SQL 语句
7.5 注意
存在于sysobjects中的有—— 表 、 视图 、存储过程
string account=string.Format("{0:000000}",int.Parse(userItem[1]));
///C3 货币格式 eg ： 2000 ————￥ 2 ， 000.000
///D3 十进制格式 eg ： 2000 ———— 2000
///F3 小数点后的位数固定 eg ： 2000 ———— 2000 ， 000
///N 用逗号隔开的数字 eg ： 250000 ———— 250 ， 000.00
///P3 百分比计算法 eg ： 0.3256 ———— 32.56
///X000 十六进制格式 eg ： 12 ———— C
string Money = string.Format("{0:C2}",int.Parse(userItem[2])); // 货币格式 小数点后两位小数
其他：
CREATE PROCEDURE P_GetPagedOrders2005
@startIndex INT,
@pageSize INT
AS
begin
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.orderid,O.orderdate,
O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID)
SELECT orderid,orderdate,customerid,companyName,employeeName
FROM orderlist
WHERE Row between @startIndex and @startIndex+@pageSize-1
end
查看更多关于Sqlserver数据库高级查询和设计的详细内容...