SQL Server高级查询与T-SQL编程

第1章数据库设计
数据库设计是建立数据库及其应用系统的技术，
信息系统开发和建设中的核心技术
数据库设计的主要工作
●需求分析明确系统功能和实体抽象
●E-R图进行概念结构设计，完成的E- R图
●转换数据模型相符合的逻辑结构
●使用PowerDesigner工具生成数据库模型
1.1数据需求分析
数据需求分析的方法
数据需求分析是整个数据库设计的基础，需收集数据库|
用户的信息内容和处理要求，并加以规范化和分析。

需求分析的任务是通过详细调查现实世界要处理的对象,
了解原系统(手工系统或计算机系统)的工作概况，明确用户
各种需求，然后在此基础上确定新系统的功能。

数据需求分析中常用的调查方法
数据需求分析的重点是调查收集和分析用户在数据管理
过程中的信息要求、处理要求、安全性和完整性要求等。

(1)跟班作业
(2 )开调查会
(3)请“专人”介绍
(4)询问
(5)设计调查表，请用户填写
(6)查阅记录
椭圆表示数据处理，动态连接线表示数据的流向，双杠线表示数据存储。

1.2概念结构设计和E-R模型
概念结构设计和E R模型
概念模型反映了信息系统所对应的现实事件中各部门、各
务的信息结构、信息流动情况、信息间的互相制约关系以及各门或各业务对信息存储、查询和加工的要求等。

核心内容是概念模型的表示方法
最常用:“实体关系”方法，简称E-R图
ER图中需要在实体联系的两端添加关联的个数
1.3逻辑结构设计
逻辑结构设计
数据库的逻辑结构设计就是将在概念结构设计阶段设计
完成的E-R图，转换为与所选用的DBMS产品所支持的数据
模型相符合的逻辑结构。

将E-R图转换为关系模型的实质是:
将实体、实体的属性和实体之间的联系转化为
关系模式。

其中实体和联系都可以表示成关系
E-R图中的属性可以转换为关系的属性。

逻辑结构设计
一对一联系还可以是与联系的任意一端实体所对应的关系模式合并，此时需要在该关系模式的属性中加入另一个实体的主键和联系本身的属性。

一般情况下，-对多联系不转换为一个独立的关系模式，而是与多端实体所对应的关系模式合并。

对于多对多则需要将该联系转换为一个独立的关系模式。

转换规则是:与该联系相连的各实体的主键以及联系本身的属性
均转换为关系的属性，该关系的主键为各实体主键的组合。

1.4使用PowerDesigner设计数据模型
使用PowerDesigner设计数据模型
PowerDesigner是Sybase公司开发的一款优秀的设计
工具软件，广泛应用于数据库建模。

使用它可以方便地创建概念数据模型(简称CDM)和
物理数据模型(简称PDM )。

概念数据模型( Conceptual Data Mode, 简
物理数据模型( Physical Data Mode,简
使用PowerDesigner设计数据模型
PowerDesigner模型图中多对多之间的联系用association (关联)表示。

设计PowerDesigner概念模型时的多对多的方法:分别选中方实体，并点击Association Link,即联系连接，绘制条指向多方，即联系的直线。

设计PowerDesigner物理模型时的对多的方法:选择Reference即参照，选中多方表，绘制一条连接到方表的直线。

设计PowerDesigner物理模型时的多对多的方法:将对多对多可以分解为两对对多。

在概念数据模型视图中，点击菜单工具”(t ools) →“生成物理数据模型”(Generate Physical Data
Model)，即可生成数据库物理数据模型。

sPewerDesigner设计数据模型方法
( 1)为了描述多对多关联关系，需要在概念数据模型中
绘制一个关联，在物理数据模型中绘制-一个中间表。

(2)对于概念数据模型，无论是-一对多还是多对多，都
是先选择一方，再绘制一条指向多方的联系线。

(3)对于物理数据模型，无论是一一对多还是
是先选择多方，再绘制一条指向一方的联系线
第二章数据查询和操作
数据查询和操作
DML语句的核心select查询基本结构、查询排序、
where条件查询和模糊查询等
聚合函数在数据最值查询
数据分析和统计汇总方面的应用
select语句中的group by子句的使用及对梦
select语句中的group by子句聚合函数
连接查询的用法与运用、连接查询的
2.1DDL和DML
DL悟言和DML语言的使用
DDL ( Data Definition Language )数据定义语言，
用于定义和管理SQL数据库中所有对象，它最基本的功能是
创建和维护数据库和数据表结构。

DML ( Data Manipulation Language,数据规
由DBMS提供，用于让用户或程序员使用，实现对
数据的操作。

DML
DDL
创建自增主键，"主键名数据类型identiry(1,1) primary key".
创建外键，"foreign key(外键) references 主表(主键)"。

新增记录，"insert [into]表名(字段列表values(值列表)"。

\
更新记录，"update 表名set字段名1 =值1,字段名2-值2 [where条件表达式"。

Concat为手机号加个027
删除记录，"delete from表名[where条件表达式"。

2.2select基本查询
会Selecl基本查询与使用
Select是任何SQL语言中使用频率最高的语句，是SQ
语言的灵魂。

select语句可以使数据库服务器根据客户的要
查询所需要的信息，并按规定的格式返回给客户。

Select语句具有强大的查询功能，允许从一
中选择满足给定条件的一个或多个行或列。

"order by排序字段ascldesc", asc升序(默认为升序，asc可省略)
, desc降序
模糊查询%匹配0到多个任意字符、模糊查询匹配任意一个字符。

"distinct col"用于过滤col字段重复的数据。

2.3聚合函数和分组查询
聚合函数和分组查询
聚合雨数用于对一组值进行计算并返回-一个汇总值，使
聚合函数可以统计记录行数、计算某个字段值的总和以及这
值的最大值、最小值和平均值等。

使用GROUP BY子句可以将数据划分到不同的组中，
实现对记录的分组查询。

“根据(by) - -定的规则进行分组( group )
作用:通过- -定的规则将-一个数据集划分成若
区域，然后针对这若干个小区域进行乡
min(col)返回col列最小值，max(coI)返回col列最大值，avg(col)返回col列平均值。

count(*)统计当前表所选取的行数，count(co1)用于统计当前表所选取的co列值不为null的行数。

sum(col)用于汇总.col列的数据之和。

"select col,聚合函数(col) group by col",根据字段col分组执行聚合函数。

聚合函数和分组查询
having子句对分组结果进行过滤where子句对分组之前的选取记录进行筛选。

2.4连接查询
连接查询
连接是将不同表的记录连到起的最普遍的方法，通i
连接查询可将多个表作为一个表进行处理。

连接查询分为内连接和外连接
“内连接也称为连接，它还可以被称为完全四声
是从结果表中删除与其他被连接表中没有匹配
所以内连接可能会丢失信息。

多张表的连接是通过两两相连的方式进行的。

第3章子查询
3.1单行子查询
子查询应用
数据库应用的核心在于检索数据、查询信息。

查询嵌套的
方式，可以将复杂的查询任务分解为一个个较为简单的查询，依
次反复分解，直至形成最简单的查询，实现复杂的查询逻辑。

单行子查询多行子查询
from子句和select子句中的子查询
exists子查询和DML语句中的子查询
子查询是-一个嵌套在select、insert、update 和del
语句或其他子查询中的查询，任何允许使用表达式的地方均
使用子查询，但子查询通常位于where子句中。

一个select语句的查询结果能够作为另一个语气
根据子查询所返回的结果行数可分:单行子查询7
单行子查询是指子查询的返回结果只有一-行数
一般情况下，连接查询可改为子查询实现;但子查询却不定可改为连接查询实现。

\当子查询执行结果的行数较大，而主查询执行结果的行数较小时，子查询执行效率较高;而情况相反时，则连接查询执行效率较高。

子查询的实质是一个selec语句的查询结果能够作为另一个语句的输入值。

子查询的执行过程遵循“由重及外原则，即先执行最内层的子查询语句，然后将执行结果与外层的语句进行合并，依次逐层向外扩展并最终形成完整的SQL语句。

3.2多行子查询
多行子查询应用
多行子查询是指子查询的返回结果是多行数据。

常见的多行比较符包括: in、all、any和some。

多行子查询不能用"="。

单行
>all,表示大于最大值; <all, 表示小于最小值。

>any,表示大于最小值; <any, 表示小于最大值
3.3子查询非典型应用
子查询非典型应用
子查询通常用于where子句中，但其也可在from子句和
Select子句中使用。

在这些场合下使用子查询，有时会实现
-一些特殊的查询应用。

另外，由exists 引出的查询也是子查询的妙用之一。

from子句中的子查询的结果为一个虚拟表，其地位与其他from子句中的
existsi定个子查询，用于检测行的存在exists子询实际上并不返回任何数据，而是返回值tueafalse.
3.4在DML中使用子查询
在DML中使用子查询
子查询不仅可在select语句中使用，用于实现需要嵌
查询功能，还可以维护数据，完成复杂的更新、删除和插
在DML中使用子查询:
update语句、delete 语句和insert语包
完成数据维护功能。

子查询不仅可在select语句中使用，用于实现需要嵌的查询功能，还可以维护数据。

删除数据时需要考虑表的主从关系，正确的做法是先删除从表数据，再删除主表数据。

第4章T-SQL编程
ST SQ1编程
在之前我们已经学习了使用SQL语句管理和操作数据库数瓶
但当面对一些较为复杂的数据应用时，仅使用这些知识，有时会
无法满足需求。

侧则某公司根据员工不同区间范围内的营业额或工龄
确定绩效工资或工龄工资。

单条的SQL语句则无法解
有一定程序逻辑的业务问题。

T- SQL是标准SQL程序设计语言的增强版，
用来让应用程序与SQL Server沟通的主要
4.1T-SQL局部变量定义和赋值
江SQ1局部变量定义和赋值
T-SQL可以使用两种变量:局部变量和全局变量
主要区别在于存储的数据作用范围不一样
局部变量是作用域局限在一-定范围内的T -SQL对象。

-般而
局部变量在一一个批处理、存储过程中被声明或定义，然后
通过T-SQL语句，设置此变量的值，或引用此变量7
当该批处理结束后，此局部变量的生命周期便随之
局部变量的名你必须以@开始
T-SQL局部变量定义和赋值
定义局部变量，"declare @局部变最名数据类型:"。

set语句仅能为一个变量赋值，"set @局部变量名=表达式”。

go的作用
1)等待go语句前的代码执行完毕，再执行go之后的语句。

2)将go之前已定义的局部变清除，被go分开的局部变量不能共享。

一般情况下，set赋给局部变量的值通常是给定的，而select赋给局部变量的值则由查询获得。

4.2流程控制语句
T SQ1编程流程控制语句
T-SQL语言的流程控制语句是对标准SQL的扩充，
可以用于控制一个批、存储过程或触发器中T-SQL语句
的执行顺序。

if..else
case条件语旬
while循环控制语句
循环
continue跳出本次循环。

沃参存储过程的编译与执行
由于批处理的T-SQL语句存储于数据库客户机，所以数据
在T-SQL语句和应用程序代码上将花费大量的时间。

在很多情
许多批处理代码被重复使用多次，每次都会输入相同的代码，
在客户机上的大量命令语句逐条向SQL Server发送，降低
SQL Server提供了-一种方法，将一些固定的操作集中起
SQL Server数据库服务器来完成，应用程序只需调用其名
即可实现某个特定的任务，这种方法就是存储过程。

存储过程的主要优点如下:
(1 )增强了SQL语言的功能和灵活性;
(2 )存储过程被刨建后，可以在程序中被多次调用，
不必重新编写该存储过程的SQL语句;
(3)如果某-操作包含大量的SQL代码或分别被多次扩
那么存储过程要比批处理的执行速度快很多。

(4)调用该存储过程时，网络中传送的只是该存储
而不是大量的SQL语句，从而大大减少了网乡
存储过程优点
(1)加快系统运行速度。

存储过程仅在创建时进行编译，以后每次执行存储过程均无须再重新编译。

(2)封装复杂操作。

当对数据库进行复杂操作时(如对多个表进行更新、删除时)，可用存储过程将此复杂操作进行封装
(3)实现代码重用。

执行存储过程，exec 存储过程名[参数1,参数2[output], .]
exec proc_product _info
存在删除后创建
有参存储过程的编译执行
存储过程的参数个数可以是多个，且与参数的顺序无
参数定义为: “参数名数据类型“
SQL Server存储过程中的参数分:
输入类型和输出类型，默认为输入类型。

定义在储过程时，输出类型变量需要添加output修饰。

执行
执行带参数的存储过程时，传入值的类型、个数和顺序都需要与存储过程中定义的参数逐对应。

第5章常见数据库对象
5.1数据库视图
常见数据库对象
数据表是数据库中最重要、最基础的常见对象。

除数据表人
数据库中还有-些比较重要和常见的对象。

数据库视图: 为提高数据库对应用程序的独立性、安全性，
保持数据的致性和简化查询的复杂度，为用户提供了一
源数据的可定制查询。

事务是一个由用户所定义的完整的工作单元，,
务内的所有语句作为一个整体来执行。

即或者全部执行，或全部不执行。

触发器是提供给程序员和数据分析员来
保证数据完整性的一种方法，是与表事件
的执行不是由程序调用，也不是手工启
视图是- 种数据库对象，是一个从- -张表、多张表或视图
导出的虚表。

视图的结构和数据是对数据表进行查询的结果。

视民仅存放视图的定义，不存放视图所对应的数据，如:
基表中的数据发生变化，则从视图中查询出的数据也随
1ssm工具创建视图
2命令行
数据库视图
创建视图语法: create view视图名as select字段列表from表[where条件表达]。

视图中的列不仅可以是基表的数据列，还可以是计算列或聚合函数列。

利用视图可以更新表中的某些列的值。

5.2数据库索引
常见数据库对象数据库索引
我们查阅书籍内容时，可以通过书籍的目录，即可快速
浏览书中特定内容，而无须阅读整本书。

书的目录是一个章节
内容列表，其标明了各个章节的页码。

疗据库中的索引就类似于书籍中的目录
在数据库中，索引使数据库系统无须对整个表进个
就可以在其中找到所需数据。

一般睛况下。

可根据下面标推创建索引
( 1 )在经常需要搜索的列上;
(2)在作为主键的列上;
(3)在经常用在连接的列上，这些列主要是-一些外键,
(4)在经常需要根据范围进行搜索的列上创建劣
(5 )在经常需要排序的列上创建索引;
(6)在使用where子句的列上创建索引;
一服况不，不应该创建索引的列的特点
(1)对于那些在查询中很少使用或参考的列;
(2)对于那些只有很少数据值的列;
(3)对于那些定义为text、image和bit数据类型的一
(4)当修改性能远大于检索性能时，不应该创处
Ssm创建索引（非聚集索引（多个）聚集索引（只能一个））
表名>索引展开创建>名+添加
删除索引: drop index索引名on表名。

唯一索引加unique
创建索引: create [unique]index索引名on表名(字段)。

创建复合索引create index索引名on表名(字段1:字段..
5.3数据库事务
常见数据库对象数据库事务
案例:某银行甲、乙客户的账号余额均为1000元，如甲客
通过ATM机，将500元从他的账户转到乙客户的账户，最终结果是:甲客户的账号余额为500元，而乙客户的账号余额为1500元。

交易过程中，当甲客户已经转出，恰好此时软件出珊华
系统还未将转出的500元转入到乙客户的账户
事务是一个由用户所定义的完整的工作单
事务内的所有语句作为一个整体来执行。

即或者全部执行，或者全部不执行。

数据库事务
启动数据库事务: begin transaction.
回滚事务: rollback transaction
提交事务: commit transaction
执行
5.4数据库触发器
常见数据库对象数据库触发器
触发器SQL Server提供给程序员和数据分析员米保
数据完整性的. 种方法，是与表事件相关的特殊的存储过程它的执行不是由程序调用，也不是手工启动，而是由事件来
触发器主要用于监视某个表的insert、update以及de
数据维护操作，这些维护操作可以分别激活该表的inse
或者delete类型的触发程序运行，从而实现数据的自
instead of触发器特点
4) instead of触发器的动作要早于表的约束处理
5)每个表上只能创建个instead of触发器
40成功60不成功
after触发器特点
1)如果对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用after触发器
2) after触发器只能在表上指定，且动作晚了F约束处理。

3)每一个表上可以创建多个after触发器
修改内容为
after或者for
第6章数据库安全管理
数据库安全管理
数据库管理系纺须具有严谨的安全机制和丰富的安全措施才能保证“三合法”，即合法的用户合法地使用合法的数据
也就是说，系统必须提供对用户合法性的校验才能保证
只有合法的用户才能进入系统。

6.1SQL Server安全机制和账号管理
KsSQL Server安全机制和账号管理
(1)发永账号:
账号标识符，用来控制对任何SQL Server系统的访问权限(2 )放据库用户:
用户是在特定的数据库内创建，并关联一个登录账户。

(3)用户与账户:
数据库用户仅仅是-一个数据库对象，而账户则是
由用户管理器所创建的Windows账户。

更新登录名密码: alter login登录名with password=新密码。

更新登录名: alter login旧登录名with name=新登录名。

禁用登录名: alter login登录名disable.
启用登录名: alter login登录名enable.
6.2数据库用户管理
SQ1 Server数据库用户管理
创建登录账户后，用户只能连接SQL Server服务器而已，
还没有访问某个具体数据库的权限，还不能操纵数据库中的数据
用户要拥有访问数据库的权限，须将登录账户映射到数据库用户
数据库用户是数据库级的主体，是登录名在数据库中的映射，
是在数据库中执行操作和活动的行动者。

SQL Server架构是形成单个命名空间的数据库实体广
是数据库对象的容器，也是分离数据库用户和安全对象
修改用户的默认架构: alter user用户名with default schema=架构名。

6.3数据库用户权限操作
SqL Server数据库用户权限操作
哪些人”“那些货源”和“哪些操作”分别对应三个对象，
即主体( Principals )安全对象( Securables )和权限( Permissions )
权力和限制则对应了SQL Server中的Grant和IDeny。

权限:在软件领域通俗的解释就是哪些人
可以对哪些资源做哪些操作。

权限是针对用户而言的，若用户需要进行某种
就必须具备使用该操作的权限。

权限是用来指定授权用户可以使用的数据库对
些授权用户可以对这些数据库对象执行的操作。

授权用户权限
形式1: grant 权限1,权限2e on架构表to用户名
形式2: grant权限on架构表(字段列表) to用户名
6.4数据库角色权限操作
SqL Server数据库角色权限操作
在SQL Server中，角色是为了方便权限管理而设置的管理
它将数据库中的不同用户集中到不同单元(角色)中，并以
为单位进行权限管理，该单元的所有用户都具有该权限。

角色的成员继承了角色的权限，
大大减少了管理员的工作量。

固定角色类型:
固定服务器角色
固定数据库角色
固定服务器角色是服务器级别的主体，独立于各个数据库,
其作用范围是整个服务器。

固定服务器角色具有固定的场
不能被修改，但可在这些角色中添加登录名以获得
固定数据库角色是数据库级别的主体，其作用范
数据库。

固定数据库角色具有固定的权限，不能
但可在这些角色中添加数据库用户以获得相关的
授予用户权限:形式3: grant 权限1,权限2(字段列表)，.. on架构表to用户名角色名。

回收权限: revoke权限on架构表from用户名角色名。

角色是一类具有相同职能的用户集合。

创建角色: create role角色名。

删除是drop
添动加用户到角色: exec sp addrolemember角色名，用户名
从角色中删除用户: exec sp droprolemember角色名，用户名
更新架构的所有者: alter authorization on schema:架构名to角色名|用户名。