5.1SQL概述

特点

1.综合统一

DQL(Data Query Language )
DML(Data Manipulation Language)
DDL(Data Definition Language)
DCL(Data Control Language )

2.高度非过程化

用户只需要提出”做什么”,而不用指明”怎么做”。减轻了用户负担，提高了数据独立性。

3.面向集合的操作方式
支持关系型数据库，操作对象和结果都是元组的集合。

4.提供交互式和嵌入式

作为交互式语言独立使用，或作为子语言嵌入到高级语言程序中。

5.语言简洁

SQL功能 动词
数据查询 select
数据定义 create, drop, alter
数据操纵 insert, update, delete
数据控制 grant, revoke

T-SQL

  1. Transact-SQL (T-SQL)语言是微软公司对标准SQL语言的扩展
  2. T-SQL是SQL Server与应用程序之间的语言 ,是SQL Server对应用程序开发的应用程序开发接口。
  3. T-SQL增强了SQL语言的功能,同时兼容SQL标准。

SQL语言的基本概念

数据类型

1.系统数据类型

类别 数据类型定义符
精确数字 bigint、int、smallint、tinyint、bit、decimal、numeric、money、 smallmoney
近似数字 float、real
日期和时间 dateime、smalldatetime、date、datetime2、datetimeoffset、time
字符串 char、varchar、text
Unicode 字符串 nchar、nvarchar、ntex
二进制字符串 binary、varbinary、image
其他数据类型 cursor、sql_variant、table、timestamp、uniqueidentifier、xml、 hierarchyid
课本P86
W3School参考资料

2.用户自定义数据类型

  • 其定义基于SQL Server中提供的数据类型。
  • 当几个表中必须存储同一种数据类型并且为保证这些列具有相同的数据类型、长度和可空性时,可以使用用户自定义数据类型。
  • 例如,用于限定邮政编码的数据类型,可定义 postal_code,基于char数据类型。

表达式

  • 表达式是标识符、值和运算符的组合,在应用时可以对其求值以获取结果。
  • 表达式可以是常量、函数、列名、变量、子查询,或用运算符对这些实体进行组合以生成表达式。
  • eg. (price * 1.5) / (price + sales_tax)

运算符

运算符是一种符号,用来指定要在一个或多 个表达式中执行的操作。

运算符 含义
算术运算符 +(加)、-(减)、*(乘)、/(除)、%(取模)
比较运算符 =、>、<、>=、<=、<>、!=、!<、!>
赋值运算符 =
逻辑运算符 AND、OR、NOT

SQL语法规则与规定

约定 用于
下划线 指示当语句中省略了包含带下划线的值的子句时应用的默认值。
｜ 竖线 分隔括号或大括号中的语法项。只能使用其中一项。
[ ] 方括号 可选语法项。不要键入方括号。
{ } 大括号 必选语法项。不要键入大括号。
[,…n] 指示前面的项可以重复 n 次。各项之间以逗号分隔。
[…n] 指示前面的项可以重复 n 次。每一项由空格分隔。
; SQL语句终止符，可省略。
<label>::= 语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记。可使用语法块的每个位置由括在尖括号内的标签指示: <标签>

5.2 SQL数据定义

SQL的数据定义语句

操作对象 创建 修改 删除
数据库 create database alter database drop database
表 create table alter table drop table
视图 create view 转换为对基本表的更新 drop view
索引 create index null drop index

数据库的创建和删除

1
2
create database 数据库名
drop database 数据库名

  • 数据库名称必须遵循标识符命名规则。有两类标识符: 常规标识符和分隔标识符

    SELECT *
    FROM [TableX] –分隔符是可选的.
    SELECT *
    FROM [My Table] –分隔符必须使用。Identifier contains a space and uses a reserved keyword.

  • 创建数据库的用户将成为该数据库的所有者,拥有该数据库的所有权限。

  • 有三种文件类型可用于存储数据库: 主数据文件，次要数据文件，事务日志。

基本表的创建、复制、修改、删除

基本表的创建

基本表(Base Table)是实际独立存放在数据库中的表，是实表。

1
2
3
4
5
6
create table 表名称(
列名称1 数据类型 列级完整性约束条件,
列名称2 数据类型,
....
表级完整性约束条件,
)
完整性约束条件 含义
primary key 定义主键
not null 定义的属性不能取空值
unique 定义的属性值必须唯一
foreign key(属性名 1)references 表名[(属性名 2)] 定义外键
check (条件表达式) 定义的属性值必须满足check中的条件 
1
2
3
4
5
6
7
/*主键的两种定义方式*/
cid nvarchar(255) not null primary key,--列级完整性约束
primary key(cid), --表级完整性约束

/*外键的定义方式orders表*/
pid nvarchar(255) not null foreign key references products(pid),--表级完整性约束
foreign key(pid) references products(pid), --表级完整性约束

基本表的复制

1
2
3
4
5
6
/*复制表的结构为一个新表*/
select * into 新表 from 旧表 where 1=2
/*复制表的结构和数据到新表*/
select * into 新表 from 旧表
/*复制表的某一部分内容为一个新表*/
select *into test2 from reader where Rsex = '男'

基本表的修改

1
2
3
4
5
6
/*如果添加的列不允许空值,则只有在指定了默认值或表为空的情况下,才能用alter table语句添加该列。*/
alter table 表名称
add 新列名 数据类型 完整性约束 -- 添加列和新的完整性约束条件
drop 完整性约束名 --删除完整性约束
drop column 列名 	--删除列
alter column 列名 数据类型 -- 修改原有的列定义，包括修改列名和数据类型

基本表的删除

restrict: 确保只有不存在相关视图和完整性约束的表才能删除。
cascade: 任何相关视图和完整性约束一并被删。

1
2
drop table 表名 [restrict|cascade]
/* []表示可选语法项，使用的时候不要键入方括号*/

5.3 SQL数据查询

查询语句的基本结构

πA1,A2,…,An(σF(R1 X R2 X … X Rm))
对应的SQL语句为:
select A1,A2,…,An
from R1,R2,… ,Rm
where F

1
2
3
4
5
6
select [*|all|distinct]
	[<目标表达式[[as]列别名]>[,<目标列表达式[[as]列别名]>[,...n]］
from <表名或视图名[[as]表别名]>[,<表名或视图名[[as]表别名]>] [,...n]
[where <条件表达式>]
[group by<列名1>[having<条件表达式>]]
[order by<列名2>[asc|dese]];

执行顺序

对from子句中所有表作关系乘法
删除where子句中条件不为真的元组
根据group by子句中指定的列对剩余元组分组
删除having子句中条件不为真的元组
计算select子句选择列表中目标列表大事的值–投影
如果存在distinct关键字，则删除重复的元组
如果有order by子句，则对所有选出的元组按照其后列值进行排序

  • select不是一个单独的语句，from是必选子句，where, group by, having, order by子句是可选字句。
  • *表示输出结果包含在表的所有字段all表示显示所有行，包括重复的行distinct禁止在输出结果中包含重复的行
  • from 后面指定数据的来源。一张表为单表查询或简单查询；多张表之间用逗号分隔，为连接查询

简单查询

选择列表中的列/消除取值重复行

1
2
3
select * from customers --选择列表中的列
select pid from orders
select distinct pid from orders -- 消除取值重复的行

查询经过计算的值

1
select pname, quantity* price totalqty --查询经过计算的值

quantity* price不是列名，而是一个计算表达式。totalqty是别名，作为查询结果中总金额的列标题

条件查询

查询条件 谓词
比较 =(等于),< (小于),> (大于),>= (大于等于),<= (小于等于),!=或<> (不等于)
确定范围 between and(介于两者之间), not between and(不介于两者之间)
确定集合 in(在其中), not in(不在其中)
存在 exists, not exists
量化比较 any, all
字符匹配 like(匹配), not like(不匹配)
空值 is null(是空值), is not null(不是空值)
多重条件 and(与), or(或), not(非)

如果处理字段为NULL值，则条件表达式返回为unknown

原值 ture false unknown
not非运算结果 false true unknown
and true false unknown
unknown unknown false unknown
or ture false unknown
unknown true unknown unknown
  • 单个/多重条件查询
1
2
3
4
5
6
7
8
9
10
11
select aid,aname from agents 
where city='new york';

select aid,aname from agents 
where not (city='new york');

/*和第一个查询结果相同*/
select aid,aname from agents
where not(not (city='new york'));

/*多重条件查询*/
select * from products
where price>5 and price<8

city列值为字符串常量，应包含在单引号内
如果单引号中的字符串包含有一个嵌入的引号，可以使用两个单引号表示嵌入的单引号

  • 确定范围查询

表达式[not]between 最小值 and 最大值

最小值 ≤ 表达式 and 表达式 ≤ 最大值
not: 最小值 > 表达式 or 表达式 > 最大值

1
select pname,price
from products
where price between 5 and 10
  • 模糊查询
    精确查询: 对查询字段有准确的描述。在实际应用中，只知道字符串的一部分，希望可以找到与之匹配的整个字符串，采用模糊查询。
1
/*查询姓名(cname)以字母“A”开始的顾客 */
select * from customers where cname like ‘A%';
  • 空值的处理
    is null 或is not null不能用 = ／ != 来代替
1
2
select * from customers 
where city is null
  • 聚合函数
函数 格式 对象
count count({ [ [all | distinct] expression ] | * } ) 统计行数，与列的数据类型无关
sum sum([all | distinct] expression) 返回值的和，只能用于数字列。
avg avg([all | distinct] expression) 返回平均值，只能用于数字列
max max([all | distinct] expression) 求列值的最大值
min min([all | distinct] expression) 求列值的最大值

除了count以外，聚合函数都会忽略空值
聚合函数只能在以下位置作为表达式使用:
select语句的选择列表(子查询或外部查询)
having字句;compute/compute by子句

1
2
3
4
5
select count(distinct city) from customers --查询所有顾客居住城市数目
select sum(dollars) as totaldollars from orders --查询所有订货交易的总金额
select avg(price) as avgprice from products --查询所有产品的平均价格
select max(dollars) from orders --查询订购表中最高的订购总价
select min([percent]) from agents --查询代理商表中最低的代理佣金

  • 查询结果排序
    升序排列asc表示，降序排列desc表示。默认为升序排列。

  • 查询结果分组
    group by 子句将表中的元组按某一列或多列值分组。值相等的为一组。

    对分组的结果再进行筛选，可以在group by子句后面使用having语句。where子句用于在分组之前筛选数据，having子句用于在分组后筛选数据。

1
2
3
4
5
6
7
8
/*查询所有的借阅记录，按照读者编号(Rno)升序排列，读者编号相同的，按照借阅时间(BorrowDate)降序排列*/
select * from Borrow
order by Rno asc, BorrowDate desc

/*查询至少被两个顾客订购的所欲产品的pid值*/
select pid from orders
group by pid
having count(distinct cid)>=2

连接查询

1
2
3
4
5
6
select 列名1, 列名2, ...
from 表名1, 表名2, ...
where 连接条件1 and 连接条件2

/*连接条件的格式*/
[<表名1>] <列名1> <比较运算符> [<表名2>] <列名2>
  • 等值连接与非等值连接查询
    当连接运算符为=时，成为等值连接，使用其他连接运算符则成为称为非等值连接。

    比较运算符主要用=, >, <, >=, <=, !=

1
2
3
4
5
6
7
/*查询既订购了产品p01又订购了产品p07的顾客的cid值*/
select distinct x.cid from orders x ,orders y
where x.pid='p01' and x.cid=y.cid and y.pid='p07'

/*查询至少订购了一件价格低于$0.60商品的所有顾客名字*/
select distinct cname
from((orders o join(select pid from products where price<0.60)p on pid = p.pid) join customers c on o.cid = c.cid)

  • 内连接
    可以通过(inner) join来建立表之间的连接。只有满足连接条件的元组才能作为结果输出。

  • 外连接

  • 左外连接: left (outer) join 以join左边的表为主表进行连接

  • 右外连接: right (outer) join以join右边的表为主表进行连接

左/右外连接的结果集包括LEFT OUTER子句中指定的左/右表的所有行，而不仅仅是连接列所匹配的行。

如果左/右表的某行在右/左表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select 列名1, 列名2...
from 表名1 join 表名2
on 连接条件1
join 表名3 
on 连接条件2
...

/*查询所有满足条件"顾客通过代理商订了货"的顾客-代理商姓名组合*/
select distinct customers.cname,agents.aname
from customers join orders
on customers.cid=orders.cid
join agents on orders.aid=agents.aid

/*为了书写方便，可以用别名代替表名*/
select distinct c.cname,a.aname
from customers c join orders o
on c.cid=o.cid join agents a on o.aid=a.aid

/*查询所欲顾客的顾客号，姓名，订购的产品号以及数量。没有订购信息的顾客显示为NULL*/
select c.cid,c.cname,o.pid,o.qty 
from customers c left outer join orders o 
on c.cid=o.cid

嵌套查询

  • 在SQL语言中，一个select-from-where语句称为一个查询块。
  • 将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。
  • 嵌套在where子句或having短语条件中的下层查询块又称为内层查询块或子查询块。
  • 它的上层select-from-where查询块又称为外层查询或父查询或主查询。
  • SQL语言允许多层嵌套查询。

带有in谓词的子查询

1
2
3
4
表达式 [not] in  (子查询)| 表达式 [not] in  (常量1 {,常量2……})

/*查询通过居住在Duluth或Dallas的代理商订了货的所有顾客的cid值*/
select distinct cid from orders 
where aid in 
	(select aid from agents 
	where city = 'duluth'or city = 'dallas')

用于判断某个属性列值是否在子查询结果中或者由多个常量组成的集合中。

  • in谓词表示如果计算之后表达式的值至少与子查询结果中的一个值相同时，或者与常量构成的集合中的一个值相同时，返回true，否则返回false。
  • not in谓词表示如果计算之后表达式的值与子查询结果中的所有值都不相同时，或者与常量构成的集合中的所有值都不相同时，返回true，否则返回false。
  • 不相关子查询: 子查询的查询条件不依赖于外层父查询，独立于外部查询。

带有比较运算符的子查询

当确切知道内层查询返回的是单值时，可以用比较运算符。

1
2
/*查询至少被两个顾客订购的产品*/
--相关子查询
select pid from products p 
where 2 <=
	 (select count(distinct cid) from orders 
	where pid = p.pid)

带有量化比较谓词的子查询

量化比较谓词 含义 量化比较谓词 含义
>(=)any/some 大于(等于)子查询结果中的某一个值 >(=)all 大于(等于)子查询结果中的所有值
<(=)any/some 小于(等于)子查询结果中的某一个值 <(=)all 小于(等于)子查询结果中的所有值
=any/some 等于子查询结果中的某一个值 =all 等于子查询结果中的所有值
!=/<> any/some 不等于子查询结果中的某一个值 !=/<> all 不等于子查询结果中的任何一个值
某些情况下，子查询返回多个值，可以使用量化比较谓词将表达式的值和子查询的结果进行比较。 
1
2
3
4
5
表达式θ{some|any|all}(子查询)

/*查询佣金百分率最小的代理商的aid值*/
select aid from agents 
where  [percent]<=all
	 (select [percent] from agents)

/*查询与居住在Dallas 或Boston 的顾客拥有相同折扣的所有顾客的cid，cname*/
select cid,cname from customers 
where discnt =some
	(select discnt from customers
	where city='dallas' or city='boston')

带有exists谓词的子查询

带有exists谓词的子查询不返回任何实数据，它只产生逻辑值true/false。
exists后面的子查询，目标列表达式通常都用*，因为只返回逻辑值，给出列名无意义。

1
2
3
4
5
6
[not] exists(子查询)

/*查询既订购了产品p01又订购了产品p07的顾客的cid*/
select distinct cid from orders x 
where pid='p01' and exists 
	(select * from orders
	where cid=x.cid and pid='p07')

/*查询没有通过代理商a05订货的所有顾客的名字*/
select distinct c.cname from customers c 
where not exists 
	(select * from orders x
	where  c.cid=x.cid and x.aid='a05')

相关子查询:子查询的查询条件依赖于外层父查询的表属性。
所有带有in谓词、比较运算符、any/all谓词的子查询都能用带exists谓词的子查询等价替换。但是一些带有[not] exists谓词的子查询却不能被其他形式的子查询等价替换。

集合查询

相容的表: 列和列的顺序必须相同，对应项的数据类型也相同。
进行并(union)、交(intersect)、差(except)操作的子查询结果表必须是相容的表。

  • union: 并操作，将两个或多个查询结果合并为单过结果集。
  • intersect: 交操作，返回两个或多个查询结果中都具有的非重复行。
  • except: 差操作，从左查询中返回有右查询没有找到的所有非重复值。
1
2
3
4
5
6
7
8
9
10
子查询1 union [all] 子查询2
/*未指定all，则删除重复行指定all,则将全部行并入结果，包括重复行*/
子查询1 intersect 子查询2
子查询1 except 子查询2

/*查询顾客所居住的城市、代理商所在城市或者两者皆在的城市*/
select city  from customers 
union 
select city from agents

/*查询既订购了产品p01又订购了产品p07的顾客的cid值*/
select cid from orders where pid='p01' 
intersect 
select cid from orders where pid='p07'

/*查询没有通过代理商a05定货的所有顾客的名字*/
select c.cname  from customers c 
except 
select c.cname from customers c,orders x 
where (c.cid=x.cid and x.aid='a05')

复杂查询

因为SQL中没有与除法运算等价的运算符，所以必须利用复杂查询将除法运算符转换为等价的形式。转换成SQL语句的一般形式为:

```sql
select ……
where not exists
(select…… where not exists
(select……
where……))；

1
2
3
4
5
6

对于“订购了所有产品的顾客”，可以等价于“没有一个产品该顾客没有订购”。  
(&forall;x)P &equiv; &not; (&exist;x(&not;P))

```sql
select c.cid from customers c 
where not exists
	(select pid from orders x where not exists  
		(select * from orders y 
		where x.pid = y.pid and y.cid = c.cid))

5.4 SQL数据操纵

插入数据

向一个表中插入数据，使用insert into，指定列名的顺序可以和表定义顺序不同，values子句值的列顺序必须和指定列名顺序相同。

插入单个记录

1
2
3
4
5
insert into <表名>[ (<属性列1>[，<属性列2>] [ ,...n ]) ]
values(<属性值1>[，<属性值2>] [ ,...n ])

insert into agents 
values ('a13', 'sam', null,5)
/*null才是空值，''是空字符串*/

插入子查询的结果

1
2
3
4
5
6
insert into<表名>[(<属性列1>[，<属性列2>] [ ,...n ]) ] 子查询语句

/*设数据库中已有一个关系cust1，其关系模式与customers完全一样，
将关系customers中的所有元组插入到关系cust1中去*/
insert into cust1
select * from customers

更新数据

修改记录中的一个或多个属性的值，使用update,如果省略where子句，则修改表中所有的记录。

1
2
3
4
5
6
7
8
9
10
11
update<表名>
set<列名1>=<表达式1>[，<列名2>=<表达式2>][ ,...n ]
[where<条件>]

/* 将所有订货总金额超过2000的顾客的折扣率增加10% */
update customers 
set discnt=discnt*1.1 
where cid in 
	(select cid from orders 
	group by cid having sum(dollars)>2000)
```
### 删除数据
从一个表中删除一条记录，使用`delete`，如果省略where子句，则删除全部表中的数据，但是表的定义仍在。

```sql
delete from<表名>
[where<表名>]

/*删除所有没有人订购的产品*/
delete from products
where pid not in	(select pid from orders)

5.5 视图

视图的概念及特点

视图(view):是从一个或几个基本表(或视图)导出的一个虚拟表，数据库中只存放视图的定义，视图对应的数据仍然存储在原来的基本表中，基本表中的数据发生了改变，视图中查询出的数据也会发生改变。视图对增删改操作有一定限制。
使用视图的优点:

  • 提高安全性
  • 简化操作
  • 增强数据逻辑独立性

视图的创建和使用

1
2
3
4
5
6
7
8
9
create view <视图名>[(<列名>[,<列名>][ ,...n ])
as
<子查询语句>
[with check option]

/* 创建一个视图custp01，列出订购了产品p01的顾客编号、姓名、产品编号、产品数量和总金额 */
create view custp01 
as
select c.cid,cname,pid,qty,dollars
from customers c, orders o
where c.cid = o.cid and o.pid ='p01'

/*视图定义后，用户可以像查询基本表一样使用视图*/
select *from custp01

/*视图嵌套*/
create view custp01_1 
as
select cid,cname
from custp01
where dollars>2000
  • 子查询语句可以任意复杂，但是通常不允许含有order by子句和distinct子句。
  • with check option表示对视图进行更新操作时要保证更新的行满足视图查询语句中的条件表达式。
  • 如果指定了视图名，省略了组成视图的属性列名，则默认该视图由子查询中select子句目标列中的字段组成。
  • 视图可以嵌套，视图可以在基本表/视图的基础上创建。

视图的更新

更新视图包括更新update，插入insert，删除delete
格式: 

1
2
3
4
5
6
7
8
9
update<视图名>
set <列名>=*<表达式>*
[where 条件]；

insert into <视图名>
values(<列名>[,<列名>][ ,...n ])

delete from <表名>
[where <条件>]

举例: 

1
2
3
4
5
6
7
8
9
10
11
12
/*建立一个视图，查询折扣率小于15的顾客信息*/
create view cust
as 
select * from customers 
where discnt<=15 [with check option]

/*修改增加折扣率，在原来的基础上+4*/
/*with check option只有在+4之后都满足discnt<=15才能执行成功*/
update cust set discnt=discnt+4


/*插入信息*/
insert into cust
values('c009','mary','dallas',12)

/*创建agentsales视图，包含所有下过订单的代理商的aid值以及他们的销售总额*/
create view agentsales(aid,totsales) 
as
select aid,sum(dollars)
from orders group by aid;

/*使用了聚合函数，无法进行更新*/
update agentsales set totsales=totsales+1000
  • with check option: 所有在该视图上执行的数据修改语句都必须符合定义视图的select语句。
  • 以下几种情况不允许更新视图:
  • 若视图是由两个以上基本表导出的，则视图不允许更新。
  • 若视图的字段来自表达式或常数，则不允许对此视图执行插入和更新操作，但允许删除。
  • 若视图定义中含有group by子句，则此视图不允许更新。
  • 若视图的属性列来自聚集函数，则此视图不允许更新。

视图的删除

1
drop view <视图名>

5.6 索引

索引的概念及作用

索引是对数据库表中一列或者多列的值进行排序的一种结构。通常只有当经常查询索引列中的数据时，才在基本表的基础上创建索引。

优点 缺点
加快数据的检索速度 占据物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间
创建唯一性索引，保证数据库表中每一行数据的唯一性 创建索引和维护索引要耗费时间
加速表和表之间的连接 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

索引的分类

  • 聚集索引
  • 基于聚集索引键对存储表或视图的数据行按照顺序排序
  • 每个表只能有一个聚集索引
  • 能提高多行检索的速度
  • 非聚集索引
  • 包含非聚集索引键值和行定位符。
  • 定位符指向聚集索引或堆中包含该键值的数据行
  • 对单行检索很快
  • 唯一性索引
  • 保证数据库表中每一行数据的唯一性

聚集索引和非聚集索引都可是唯一性索引

索引的创建及删除

索引的创建

1
2
3
4
create [unique][clustered | nonclustered]index <索引名>
on <表名> (<列名>[<asc | des>][,n...]）

/*为表agents创建一个索引，保证每一行都有唯一的aid*/
create unique clustered index aidx on agents(aid)

unique表示唯一性索引，clustered表示聚集索引，nonclustered表示非聚集索引。默认非聚集索引。默认asc升序。

索引的删除

1
2
3
4
5
drop index<索引名>
drop index <表名>.<索引名>

/*删除索引aidx*/
drop index agents.aidx
索引的使用情况 不应该创建索引的情况
经常需要搜索的列 在查询中很少使用或参考的列
作为主键的列 只有很少数据值的列
经常用于连接的列 定义为text,image和bit数据类型的列
经常需要根据范围搜索的列 修改性能远远大于检索性能
经常需要排序的列
经常使用在where子句中的列

5.7 SQL数据控制语言(DCL)

DBMS提供的数据库安全保护常用措施有:

  • 用户标识和鉴定
  • 存取权限控制
  • 视图
  • 跟踪审查
  • 数据加密存储

授予权限

存取权限控制: DBMS提供的内部安全性措施保护措施。

  • 确保只授权给有资格的用户访问DB的权限。
  • 存取控制机制的组成
  • 定义存取权限
  • 检查存取权限(确保用户只能执行合法操作)
  • 存取权限控制机制主要包括用户权限定义和合法权检查机制两部分。

SQL语言用grant语句将对指定操作对象的指定操作权限授予指定的用户

1
2
3
4
5
6
grant<权限>[,<权限>][,...n]
[on<对象类型><对象名>]
to<用户>[,<用户>],[,...n]
[with grant option]

/*授予用户wintest对数据库sales的agents表的插入、更新权限*/
grant insert,update on agents 
to wintest

收回权限

授予的权限可以由DBA或其他授权着用revoke语句收回

1
2
3
4
5
6
7
8
9
10
11
12
13
revoke<权限>[,<权限>][,...n]
[on<对象类型><对象名>]
from<用户>[,<用户>],[,...n]

/*把用户sqltest对agents表的插入、更新权限收回*/
revoke insert, update
on table agents
from sqltest

/*收回所有用户对视图cust1的查询权限*/
revoke s elect
on view  cust1
from public

public 角色是一个特殊的数据库角色，每个数据库用户都属于它。

视图机制保证安全性

  • 视图把需要保密的数据对无权存取这些数据的用户隐藏起来
  • 实际应用中，通常把视图机制与授权机制配合使用: 首先使用视图机制屏蔽掉一些保密数据，然后在视图上再进一步定义其存取权限

5.8 存储过程

存储过程简介

  • 存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。
  • 存储过程可包含程序流、逻辑以及对数据库的查询。可以接受参数、输出参数、返回单个或多个结果集。
  • 存储过程可以嵌套，即一个存储过程可以调用另一个存储过程。

优点:

  • 重复使用。存储过程可以重复使用。从而可以减少数据库开发人员的工作量。
  • 提高性能。存储过程在创建的时候就进行了变异，将来使用的时候不用再重新编译。
  • 减少网络流量。
  • 安全性。可以将grant、deny以及revoke权限应用于存储过程。

###存储过程的创建与执行

1
2
3
4
5
6
7
8
9
10
create procedure[拥有者.]存储过程名[;程序编号]
[(参数#1,...，参数#n)]
[with
{recompile|encryption|recomplie,encryption}
]
[for replication]
as 程序行

/*执行存储过程*/
exec<存储过程名>[(参数表)]

###存储过程的修改

1
2
alter procedure<存储过程名>［(参数表)］
as 程序行

###重新编译存储过程
###存储过程的删除

1
drop procedure<存储过程名>

###使用存储功能的注意事项
使用存储过程的目的是为了提高应用系统的运行效率，增强系统的可维护性，保证数据的完整性和一致性。在使用存储过程时要注意以下几个方面:

  • 避免潜逃的、递归的存储过程的使用

  • 在删除存储过程之前，需确定存储过程是否分组

    使用存储功能的注意事项

    使用存储过程的目的是为了提高应用系统的运行效率，增强系统的可维护性，保证数据的完整性和一致性。在使用存储过程时要注意以下几个方面:

  • 避免潜逃的、递归的存储过程的使用

  • 在删除存储过程之前，需确定存储过程是否分组

5.9 函数

函数的概念及优点

函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。
优点:

  • 允许模块化程序设计
  • 执行速度更快。使用用户定义函数时无需重新解析和重新优化，从而缩短了执行时间
  • 减少网络流量
  • 使用灵活，能实现复杂约束

存储过程和函数的区别

  • 存储过程可以返回参数，而函数只能返回值或者表对象。
  • 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
  • 函数在系统启动时就进行编译并加载，存储过程在调用时才加载。
  • 一般来说，存储过程实现的功能要复杂一点，而函数实现的功能针对性更强。

函数的创建与使用

  • 内置函数
  • 聚合函数(count, avg, sum, max, min…)
  • 配置函数
  • 日期和时间函数
  • 数学函数
  • 元数据函数
  • 用户自定义函数
  • 标量函数
  • 内嵌表函数
  • 多语句表值函数

函数的修改

1
2
alter function <函数名>[(参数表)]
as 程序行

函数的删除

1
drop function <函数名>

5.10 T-SQL的流程控制语句

begin…end语句

begin…end语句作为一对表示符包括多条T-SQL语句，将其组合为一个具有逻辑性的语句块整体。

1
2
3
4
5
begin
{
sql_statement | statement_block
}
end

if…else语句

if…else语句是单条件判断语句

case语句

case语句是多条件判断语句

while语句

while语句是一个循环语句

goto语句

goto语句将执行流更改到标签处

waitfor语句

waitfor语句主要用于以下两个方面:

  • 暂停执行程序一段时间后再继续执行
  • 暂停执行程序到指定时间后再继续执行

return语句

return语句能够无条件地终止一个查询

try/catch语句

5.12 嵌入式SQL

嵌入式SQL的定义及实现

嵌入式SQL就是将SQL语言直接嵌入到高级程序设计语言的源代码中，利用高级语言的过程性结构来弥补SQL语言实现复杂应用方面的不足。

嵌入式SQL的实现，有两种方法：

  • 修改和扩充宿主语言的编译程序，使之能处理SQL语句；
  • 预处理方式（常用）:
  • 将具有前缀的语句，用SQL预处理器转换成宿主语言的函数调用语句
  • 由宿主语言的编译器生成目标程序

SQL和宿主语言的接口

SQL语言是面向集合的，一条SQL语句可以产生或处理一条或多条记录，主语言是面向记录的，一组主变量一次只能存法一条记录。
如果SQL语句产生的是单行结果，可以使用select into语句；如果SQL语句产生的是多行结果，则必须使用游标来解决SQL语句和主语言处理方式的矛盾。

  • 游标：是计算机系统为用户开设的一个数据缓存区，用于存放SQL语句的结果数据集，每个游标区都有一个名字。
  • 用户可以通过游标逐一读取数据记录，然后赋值给主变量，再交给主语言程序作进一步处理。