SQL数据库设计和高级查询.docx
《SQL数据库设计和高级查询.docx》由会员分享，可在线阅读，更多相关《SQL数据库设计和高级查询.docx（18页珍藏版）》请在冰豆网上搜索。
SQL数据库设计和高级查询
SQL数据库设计和高级查询
第一章数据库的设计
1.1为什么需要规范的数据库设计
糟糕的数据库设计表现为以下几点：
效率低下
更新和检索数据时会出现许多问题
良好的数据库设计表现为一下几点：
效率高
便于进一步扩展
使得使用程序开发变得更容易
1.2设计数据库的步骤
设计过程如下：
→需求分析阶段
→概要设计阶段
→详细设计阶段
无论数据库的大小和复杂程度如何，在进行数据库的系统分析时，都可以参考下列基本步骤：
a、收集对象
b、标识对象（实体）
c、标识每个对象需要存储的详细信息（属性）
d、标识对象之间的关系
1.3绘制E-R（实体-关系）图
A.实体：
客观存在的事物。
B.属性：
实体的特征。
C.关系：
各实体之间的联系
D.映射基数：
通过关系与该实体关联的其他实体的个数（一对一、一对多、多对一、多对多）
E.实体关系图：
绘制E-R的步骤
1.3.2如何将E-R图转化为表
第一步：
将各实体转化为对应的表，将各属性转化为各表对应的列
第二步：
标识每个表的主键列
第三步：
在表之间体现实体之间的映射关系
1.4数据规范化
1.4.1设计问题
信息重复
更新异常
插入异常（无法表示某些信息）
删除异常（丢失有用的信息）
1.4.2规范设计
1.第一范式（1NF）：
确保每列的原子性。
即保证每列都是不可再拆分的。
2.第二范式（2NF）：
在1NF基础上，确保表中每列和主键相关。
即保证每张表描述一件事情。
3.第三范式（3NF）：
在2NF基础上，确保每列都和主键列直接相关，而非间接。
1.4.3规范化和性能的关系
为了满足三大范式，很有可能造成使得创建数据库中没有带来便捷，反而造成麻烦。
所以，为了减少表间连接，提高数据库的访问性能，允许适当的数据冗余列，可能是最何时的数据库设计方案。
第二章数据库的实现
2.1T-SQL语句回顾
1.添加数据INSERT
A：
单条插入：
insert[into]表名[列名]values（插入值）
B：
多条插入：
一共三种方法
a、通过insertselect将现有表中数据添加到新表：
insert[into]新表（新列名）select原列名from原表
b、通过selectinto将现有表中数据添加到新表：
select新列名into新表from原表
c、通过union关键字合并数据并插入：
insert表名（列名）
select插入值union...
select插入值
2.修改数据UPDATE
update表名set列名=更新值[where更新条件]
3.查询数据SELECT
select列名from表名[where查询条件][orderby列名]
4.删除数据DELETE
A：
使用delete删除数据：
deletefrom表名[where删除条件]
B：
使用truncatetable删除数据：
清空表，相当于没有删除条件的where，此方法比delete执行速度快，使用资源少。
此方法不能用于有外键约束引用的表，这种情况需使用delete。
truncatetable表名
2.2使用SQL语句创建和删除数据库
数据库文件由3部分组成：
主数据文件：
*.mdf可有多个
次要数据文件：
*.ndf可选
日志文件：
*.ldf可有多个
数据库文件其他属性：
文件存放位置
文件的增长设置
文件容量设置
2.2.1创建数据库
复制内容到剪贴板代码:
createdatebase数据库名称
on[primary]--是否是主数据库文件，一般在创建多数据库文件时使用
（
name=存放时数据库的名称,
filename=存放的路径,
[size=?
mb,]--初始大小
[maxsize=?
mb,]--最大值
[filegrowth=?
%]--增长率，可使用%,或者?
mb
）
logon（
name=存放时日志文件的名称,
filename=存放的路径,
[size=?
mb,]--初始大小
[maxsize=?
mb,]--最大值
[filegrowth=?
%]--增长率，可使用%,或者?
mb
）
go--GO表示一批T-SQL语句结束，GO之后的T-SQL语句属于另一个批处理的范围，在T-SQL所有语句的最后都默认有一个GO。
但是，请注意GO不是T-SQL语句，而只是一个能被SQLServer实用工具识别的命令。
2.2.2删除数据库
dropdatabase数据库名称
判断数据库是否存在：
ifexists（select*fromsysdatabaseswherename='数据库名称'）
2.3使用SQL语句创建和删除表
2.3.1创建表
createtable表名
（
列名数据类型列的特征（是否是主键列等）,
......
）
2.3.2删除表
droptable表名
判断表是否存在：
ifexists（select*fromsysobjectswherename='表名'）
2.4使用SQL语句创建和删除约束
常用约束如下：
主键约束（PrimaryKeyConstraint）
唯一约束（UniqueConstraint）
检查约束（CheckConstraint）
默认约束（DefaultConstraint）
外键约束（ForeignKeyConstraint）
2.4.1添加约束
altertable表名
addconstraint约束名称约束类型约束说明
2.4.2删除约束
altertable表名dropconstraint约束名称
2.5使用SQL语句创建登陆
2.5.1创建登陆账户
登陆有两种方式：
SQL身份验证：
适合非Window平台用户和Internet用户，需要提供账号密码。
execsp_addlogin'账户名','密码'
Windows身份验证：
适合Windows平台用户，不需要提供密码，和Windows集成验证。
execsp_addgrantlogin'windows域名\域账户'--本机可用计算机名替换windows域名
2.5.2创建数据库用户
execsp_grantbaccess'登陆账户'[,'数据库用户']
2.5.3给数据库用户授权
grant权限[on表名]to数据库用户
第三章T-SQL编程
3.1使用变量
3.1.1局部变量
局部变量以@作为前缀，声明语法：
declare@局部变量名变量类型
局部变量的赋值有两种方法：
使用set：
set@局部变量名=value。
一般用于赋给变量指定的数据常量。
使用select：
select@局部变量名=value。
一般用于表中查询数据然后赋值给变量。
如果select返回的记录多余一条，则只赋最后一条记录。
3.1.2全局变量
全局变量以@@作为前缀。
@@error最后一个T-SQL错误的错误号
@@identity最后一次插入的标识指，没有标识列返回null
3.2输出语句常用的输出语句有两种：
print局部变量或字符串
select局部变量[as自定义列名]
3.3逻辑控制语句
3.3.1if-else条件语句
if（条件）
begin//语句块开始{
....//执行的语句
end//语句块的结束}
else
begin
...
end
3.3.2while循环语句
while（条件）
begin
...
[可使用continue或break]
end
3.3.3case多分支语句
case
when条件then结果
....
[else其他结果]
End
3.4批处理语句
批处理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQLServer执行。
SQLServer将批处理语句编译成一个可执行单元，此单元称为执行计划。
执行计划中的语句每次执行一条。
GO是批处理的标志，表示SQLServer将这些T-SQL语句编译为一个执行单元，提高执行效率。
一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定。
SQLServer规定：
如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加GO批处理标志。
一个变量的作用范围就是一个批处理语句。
第四章高级查询
4.1简单子查询
简单查询即是用select语句作为筛选的条件。
一般来说，表连接都可以用子查询替换，但反过来说却不一定。
有的子查询不能用表连接来替换。
子查询比较灵活、方便，形式多样。
表连接更适合查看多表的数据。
4.2in和notin子查询
在SqlServer中，当子查询跟随在=、!
=、<、<=、>、>=之后，子查询只能返回单条记录。
如果返回多条查询记录，应使用关键字in。
4.3exists和notexists子查询
exists也可作为where语句的子查询，但一般用于if语句的存在检测。
1、查询所有数据行和列
select*from表名
2、查询部分行——条件查询
select列名from表名where列名=value
select列名from表名where列名<>value
3、在查询中使用列名
select列名as‘新列名'from表名
//合并列然后起新列名
select列名+列名as新列名
4、查询空行
select列名from表名where列名isnull
select列名from表名where列名isnotnull
5、在查询中使用常量列
select'常量值'=新列名from表名
6、查询返回限制的行数
//返回具体行数
selecttop行数列名from表名
//返回x%的行数
selecttop百分数percent列名from表名
7、查询排序
//升序，默认升序
select*from表名orderby排序列acs
//降序
select*from表名orderby排序列desc
8、使用like模糊查询
select*from表名where列名like'模糊条件'//这里的模糊条件和正则表达式很类似。
9、使用between在某个范围内进行查询
select*from表名where列名betweenvalues_1andvalues_2
10、使用in在列举值内进行查询
select*from表名where列名in（'values_1','values_2'……）//还可以使用notin
11、聚合函数sum
//sum只能用于数字类型的列，如有查询，只能返回一个值
selectsum（列名）from表名
12、聚合函数avg
//avg只能用于数字类型的列，如有查询，只能返回一个值
selectavg（列名）from表名
13、聚合函数max和min
//max最大值
selectmax（列名）from表名
//min最小值
selectmin（列名）from表名
14、聚合函数count
selectcount（列名）from表名
15、使用groupby进行分组查询
select分组列名from表名groupby分组列
16、使用having子句进行分组筛选
select分组列名from表名groupby分组列having条件
17、内联接
//在where子句中置顶联接条件
select*from表名1,表名2where表名1.列名=表名2.列名
//在from子句中使用[inner]join...on
select*from表名1innerjoin表名2on表名1.列名=表名2.列名
18、外联接
//左外联接
select*from表名1leftouterjoin表名2on表名1.列名=表名2.列名
//右外联接
select*from表名1rightouterjoin表名2on表名1.列名=表名2.列名
第五章事务、索引和视图
5.1事务
5.1.1为什么需要事务
不管是生活中，还是程序中，每当我们处理一个事件时，很有可能因为一个问题而导致这个事件的非正常运行。
如果我们知道这个问题应该停止事件的不正常运行。
5.1.2什么是事务
事务是一种机制、一个操作序列，它包含了一族数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤销操作请求。
事务是一个不可分割的逻辑单元，在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。
他特别使用于多用户同时操作的数据库系统。
一个逻辑工作单元必须有4个属性：
原子性（Atomicity）：
事务是一个完整的操作，它的各元素是不可分的，事务中任何元素失败，正式事务失败。
一损俱损。
一致性（Consistency）：
当事务完成时，数据必须处于一致状态。
隔离性（Isolation）：
对数据进行修改的所有并发事务是彼此隔离的，这表名事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
持久性（Durability）：
事务完成之后，它对于系统的影响是持久性的。
即修改即使出现系统故障，也将一直保持。
5.1.3如何创建事务
T-SQL中的事务：
开始事务：
begintransaction
提交事务：
committransaction
回滚（撤销）事务：
rollbacktracsaction
事务的分类：
显示事务：
用begintransaction明确指定事务的开始。
隐式事务：
通过设置setimplicit_transactionson语句，将隐式事务模式设置为打开。
当以隐式事务操作时，SqlServer将在提交或回滚事务后自动启动新事务。
无法描述事务的开始，只需提交或回滚（撤销）每个事务。
自动提交事务：
这是SqlServer默认模式，它将每条单独的T-SQL语句视为一个事务。
如果成功执行就自动提交，反之则自动回滚（撤销）。
5.2索引
5.2.1什么是索引
数据库中的索引是某个表中一列或者若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引：
它是SQLServer编排数据的内部方法，为SqlServer提供一种方法来编排查询数据的路径。
索引页：
数据库中存储索引的数据页。
索引页存放检索数据行关键字页以及该数据行地址指针。
通过使用索引可以大大提高数据库的检索速度，改善数据库性能。
索引可分为以下几类：
唯一索引：
唯一索引不允许两行具有相同的索引值。
创建唯一约束自动创建唯一索引。
尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束。
主键索引：
在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。
主键索引要求主键中每个值是唯一的。
当在查询中使用主键索引时，它还允许快速访问数据库。
聚集索引与非聚集索引：
在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。
表只能包含一个聚集索引。
聚集索引比非聚集索引有更快的数据访问速度。
一个表中可以有多个非聚集索引。
设置某列为主键，该列默认为聚集索引。
5.2.2如何创建索引
使用MicrosoftSqlServerManagementStudio创建索引
使用T-SQL语句创建索引：
create[unique][clustered|nonclustered]indexindex_nameontable_name（column_name[,column_name].....）[withfillfactor=x]
//unique指定唯一索引，可选
//clustered、nonclustered指定聚集索引还是非聚集索引
//fillfactor表示填充因子，置顶一个0~100的值，该值指示索引页填满的空间所占的百分比
创建索引的规则和标准（该列用于频繁搜索,该列用于对数据进行排列）
请不要对以下列创建索引（列中仅包含几个不同的值,表中仅包含几行.）
为小型表创建索引可能不太划算,因为在SQL索引中搜索数据所花费的时间比在表中搜索所花费的时间更长。
5.3视图
5.3.1什么是视图
视图是另种查看数据库中一个或多个表中的数据的方法。
视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。
当然，也可包含所有行和列。
视图并不是数据库中存储数据值的集合。
它的行和列来自查询中引用的表。
视图充当着查询中置顶的表的筛选器。
定义试图的查询一个或多个表，也可基于其他视图、当前数据库或其他数据库。
视图给用户和开发人员带来的好处：
A：
对最终用户的好处：
结果更容易理解；获得数据更容易。
B：
对开发人员的好处：
限制数据检索更容易；维护应用程序更方便。
5.3.2如何创建视图
使用T-SQL语句创建试图：
Createviewview_name
As
第六章存储过程
6.1什么是存储过程
存储过程就是一个方法体，里面放了些我们想要执行的东西。
存储过程可包含逻辑控制语句和数据操作语句，它可以接收参数、输出参数、返回单个或多个结果集以及返回值。
存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。
存储过程优点：
1.执行速度快。
存储过程创建时，就已经通过语法检查和性能优化，在执行时无须每次编译。
存储在数据库服务器，性能高。
2.允许模块化设计。
只需创建一次存储过程并将其存储在数据库中，以后即可在程序中调用该过程任意次。
3.提高系统安全性。
可将存储过程作为用户存取数据的管道。
可以限制用户对数据表的存取权限。
建立特定的存储过程供用户使用，完成对数据库的访问。
存储过程的定义文本可以被加密，使用户不能查看其内容。
4.减少网络流量。
一个需要数百行T-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。
6.2常用的系统存储过程
1.由系统定义，存放在master数据库中。
可以再任何数据库中运行系统存储过程，但执行结果会反映在当前数据库中。
系统存储过程的名称都以“sp_”开头。
常用的系统存储过程
系统存储过程
说明
2.系统存储过程调用：
EXEC存储过程名。
3.还有一个常用扩展存储过程：
xp_cmdshell：
可以执行DOS命令下的一些的操作
调用语法：
EXECxp_cmdshellDOS命令[NO_OUTPUT]
6.3用户定义的存储过程
创建存储过程时，需要注意事项：
1.只能在当前数据库中创建存储过程。
2.数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。
3.存储过程与其他的SQL语句分开，单独放在一个批处理语句当中。
创建存储过程语法：
CREATEPROC[EDURE]存储过程名
@参数1数据类型=默认值OUTPUT,
……,
@参数n数据类型=默认值OUTPUT
AS
SQL语句
GO
和C语言的函数一样，参数可选
参数分为输入参数、输出参数
输入参数允许有默认值
存储过程的参数分两种：
输入参数
输出参数
输入参数：
用于向存储过程传入值，类似C语言的按值传递;
输出参数：
用于在调用存储过程后，
返回结果，类似C语言的
按引用传递;
6.4处理错误信息
可以使用PRINT语句显示错误信息，但这些信息是临时的，只能显示给用户
RAISERROR显示用户定义的错误信息时：
可指定严重级别；
设置系统变量@@ERROR；
记录所发生的错误等。
RAISERROR语句的用法如下：
RAISERROR（msg_id|msg_str,severity,stateWITHoption[,...n]]）
msg_id：
在sysmessages系统表中指定用户定义错误信息
msg_str：
用户定义的特定信息，最长255个字符
severity：
定义严重性级别。
用户可使用的级别为0–18级
state：
表示错误的状态，1至127之间的值
option：
指示是否将错误记录到服务器错误日志中
升级会员 