数据库完整性+T-SQL

数据库的完整性（integrity）是指数据的正确性（correctness）和相容性（compat-abiity）。 数据库的完整性是防止不合语义或不正确的数据进入数据库 完整性体现了是否真实地反映现实世界

意义

用合适的完整性约束来规范数据，方便查找和操作，也方便后期的维护和优化。

数据模型（关系）

结构 操作 完整性

DBMS的完整性机制

提供了定义完整性约束条件的机制

提供了完整性检查的方法

违约处理

实体完整性（区分记录）

关系模型的实体完整性

Primary key

定义实体完整性

单一属性可以在列级和表级定义主码

多属性只能在表级定义主码

DBMS实体完整性检查和违约处理

检查

检查记录中主码值是否唯一的一种方法是进行 全表扫描

该方法缺点：耗时

违约处理

拒绝操作

参照完整性（Foreign key）

定义参照完整性

create table sc(
    //列级定义参照完整性
    sno char(9) foreign key (sno) references student(sno),
    cno char(4),
    grade smallint,
    //表级定义实体完整性
    primary key(sno,cno),
    //表级定义参照完整性
    foreign key (cno) references student(cno)
)

复制代码

参照完整性检查和违约处理

如上代码：student表为被参照表，sc为参照表

被参照表（例如Student）		参照表（例如SC）	违约处理
可能破坏参照完整性	<——	插入元组	拒绝
可能破坏参照完整性	<——	修改外码值	拒绝
删除元组	——>	可能破坏参照完整性	拒绝/级联删除/设置为空值
修改主码值	——>	可能破坏参照完整性	拒绝/级联删除/设置为空值

违约处理

拒绝执行（默认处理）

级联操作

设置为空值

create table sc(
    //显式说明参照完整性的违约处理示例
    //列级定义参照完整性
    sno char(9),
    cno char(4),
    grade smallint,
    //表级定义实体完整性
    primary key(sno,cno),
    //表级定义参照完整性
    foreign key (sno) references student(sno)
    on delete cascade //级联删除sc表中相应的元组
    on update cascade,//级联更新sc表中相应的元组
    foreign key (cno) references course(cno)
    on delete no action//当删除course表中的元组造成了与sc表不一致时拒绝删除
    on update cascade//当更新course表中的cno时，级联更新sc表中相应的元组

    //如果在表级定义实体完整性，隐含了sno，cno不允许取空值，则在列级不允许取空值的定义就不必写了
)

复制代码

用户定义的完整性

定义

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求

RDBMS提供，而不必由应用程序承担

属性约束条件的定义

定义

列值为空not null

列值唯一unique

检查列值是否满足一个条件表达式check

create table sc(
    sno char(9) not null,//不允许取空值
    cno char(4) unique not null,//要求cno列值唯一，且不能取空值
    ssex char(2) check(ssex in('男','女')),//用check短语制定列值应满足的条件，in后面跟集合
    grade smallint check(grade>=0 and grade<=100>)//或者使用between 0 and 100
 
)
复制代码

ps：查询某个字段是否为空 ~ where sno is （not） null

属性上约束条件的检查和违约处理

拒绝执行

元组上的约束条件

在create table可以用check短语定义元组上的约束条件，即元组级的限制

定义

create table sc(
    sno char(9) ,
    ssex char(2),
    sname char(10),
    check(ssex='女' or sname not like 'Ms.%')//当性别为男时检查名字不能以Ms.开头
 
)
复制代码

元组上约束条件的检查和违约处理

完整性约束命名子句

constraint约束

创建

constraint <完整性约束条件名><完整性约束条件>
复制代码

create table student(
        sno numeric(6)
        constraint c1 check(sno between 9000 and 9999),
        sname char(20)
            constraint c2 not null,
        sage numeric(3)
            constraint c3 check(sage<30),
        ssex char(2)
            constraint c4 check (ssex in('男','女'))，
            constraint studentKey primary key(sno)

    )

    //在student表上建立了5个约束条件，包括主码约束（命名为studentKey以及C1，C2，C3，C4这四个列级约束）
复制代码

修改

可以先删除原来的约束条件，再增加新的约束条件

//去掉上表对性别的限制
    alter table student 
        drop constraint C4;
复制代码

//修改表student中的约束条件，要求学号改为在9000~9999之间的年龄由小于30改为小于40
//先删除原来的约束条件，再添加新的约束条件
alter table student
    drop constraint c1;
alter table student
    add constraint c1 check (sno between 9000 and 9999);
alter table student
    drop constraint c3;
alter table student
    add constraint c3 check(sage<40>
    )
复制代码

域中的完整性限制(略)

断言

概念

在 SQL 中可以使用数据定义语言中的create assertion语句，通过声明性断言来指定更具一般性的约束

创建断言的语句格式

create assertion <断言名> <check子句> 每个断言都被赋予一个名字，<check 子句>中的约束条件与where子句的条件表打式类似。

//限制数据库课程最多60名学生选修
create assertion asse_sc_db_num
    check(60>=(select count(*)//此断言的谓词涉及聚集操作count的sql语句
    from course,sc
    where sc.cno = course.cno adn course.cname='数据库'
    );

//每当学生选修课程时，将在sc表中插入一条元组（sno,cno,null）,asse_sc_db_num断言被触发检查，如果选修数据库课程的人数已经超过60人，check返回值就为“假”，对sc表的插入操作被拒绝。
复制代码

删除断言的语句格式

drop assertion <断言名>

触发器

Transact（事务）——SQL语言简介

快速入门： www.jianshu.com/p/a7bb06705… 系统学习： www.w3cschool.cn/t_sql/t_sql…

概念

T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增强版，它是用来让应用程式与 SQL Server 沟通的主要语言。T-SQL 提供标准 SQL 的 DDL 和 DML 功能，加上延伸的函数、系统预存程序以及程式设计结构(例如 IF 和 WHILE)让程式设计更有弹性。

常用语句

1. "--"
   ep:use st //选择数据库
   go//批处理结束
2. " --- "
   用法同其他语言

批处理GO

批处理时传给服务器的一组完整的数据和指令 一个批处理中的所有语句作为一个整体被成组的分析、编译、和执行

print命令

格式：print ‘需要输出的字符串’

功能：发送用户指定的信息

Use mydata;
    GO
    If Exists(select cname from course where cname='操作系统')
    print '你选择的课程是操作系统'
    GO
复制代码

变量

全局变量

由系统定义和维护，用户不能建立和修改。作用范围时所有应用过程序。

全局变量的表示：@@名称

ep: @@ SERVERNAME SELECT @@SERVERNAME AS 本地服务器

(1)局部变量

  局部变量由用户定义，一般出现在批处理、存储过程和触发器中，其作用范围仅在程序内部。

  局部变量必须先声明，后使用。T-SQL还为局部变量提供了赋值语句。

  ①declare变量声明语句，其语法格式为：

declare @变量1 [as] datatype,@变量2 [as] datatype...
复制代码

局部变量名称必须以@开始开头

as可以省略

赋初值NULL

局部变量的赋值有三种方式：

  ①在变量定义的时候对其赋值：

declare @变量1 [as] datatype = value,@变量2 [as] datatype = value...
复制代码

②select赋值语句，其语法格式为：

select @变量1 = 表达式1,@变量2 = 表达式2...
复制代码

用select命令可以一次给多个变量赋值

表达式可以为普通的value，也可以为查询结果

当表达式为表的列名时，形式与普通查询中使用列别名的用法类似。可以使用子查询从表中一次返回多个值。

如果查询的结果为多行，则只会把最后一行的相应列值赋给变量，这与PL/SQL的处理方式不同，在PL/SQL中，不允许把多行查询结果赋值给变量

③set赋值语句，其语法格式为：

set @变量 = 表达式
复制代码

基本用法和select一样，区别在于一条set赋值语句只能给一个变量赋值，而一条select语句可以给多个变量赋值

【示例】
declare @sumsal as numeric(10,2),@dno as tinyint
select @dno = deptno,@sumsal = sum(sal) 
from emp 
where deptno = 10
group by deptno
print cast(@dno as varchar)+'：'+cast(@sumsal as varchar)
复制代码

运算符

算术运算符：+、-、*、/、%(求余)

字符串运算符：+(连接)

比较运算符：=、>、>=、<、<=、<>(不等于)、!>(不大于)、!<(不小于)

逻辑运算符：NOT、AND、OR、ALL(所有)、ANY(或SOME，任意一个)、BETWEEN...AND、EXISTS(存在)、IN(在范围内)、LIKE(匹配)

按位运算符：&(位与)、|(位或)、^(按位异或)

一元运算符：+(正)、-(负)、~(按位取反)

赋值运算符：=(等于)

函数

函数是用来完成某种特定功能，并返回处理结果的一组T-SQL语句，处理结果成为“返回值”，处理过程成为“函数体”。

  函数又分为系统内置函数和用户自动以函数。SQL Server提供了大量系统内置函数，主要可以分为以下几类：数学函数、字符串函数、日期函数、convert函数、聚合函数。

  (1)数学函数

  T-SQL中提供的常用的数学函数如下：

abs()：返回绝对值
round(数值表达式,长度,[,类型])：舍入到指定长度或精度。类型为0，表示舍入，类型为非0，表示截断
power(m,n)：返回m的n次幂
trunc()：将数字截断到指定的位数
%：求余数，SQL Server没有mod(m,n)，而用m%n代替
复制代码

(2)字符串函数

ltrim(str,substr)/rtrim(str,substr)：str表示要操作的字符串，substr表示要裁剪的子串，若裁剪空格，则可以省略
substring(str,position,length)：求子字符串
replace(str,search_str,rep_str)：替换一个字符串中的子串。search_str表示要搜索的子字符串，rep_str表示要替换的目标字符串
left(str,n)：返回字符串从左边开始的指定个数的字符
len()：求字符串长度
复制代码

(3)日期和时间函数

T-SQL中提供下列日期函数：

getdate()：返回当前的日期和时间

year(日期)：返回指定日期的“年”部分的整数

month(日期)：返回指定日期的“月”部分的整数

day(日期)：返回指定日期的“日”部分的整数

datepart(日期元素，日期)：返回日期元素指定的日期部分的整数

datename(日期元素，日期)：以字符串的形式返回日期元素指定时间的日期名称

datediff(日期元素，日期1，日期2)：返回两个日期间的差值并将其转换为指定日期元素的形式

dateadd(日期元素，数值，日期) ：按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期

涉及日期时间常量时，SQL Server建议使用与dateformat及语言环境设置无关的字符串格式，通常这样的字符串常量符合下面两种形式：

日期之间不使用分隔符，格式为yyyymmdd[ hh:mi:[:ss][,mmm]]，如'20070703'，'20070703 17:53:00.997'。

ISO 8601标准形式，格式为yyyy-mm-ddThh:mi:ss[.mmm]，日期各个部分之间使用“-”分隔符，日期和时间部分用T分隔，并且时间部分不能省略，如'2007-07-03T17:53:10'。

(4)数据类型转换函数

  转换的方式有隐式转换和显式转换两种。

  隐式转换是SQL Server自动地将数据从一种数据类型转换为另一种数据类型，用户不可见。

  显式转换使用convert函数，该函数可以将一种数据类型的表达式强制转换为另一种数据类型的表达式。两种数据类型必须能够进行转换，例如，char值可以转换为binary，但不能转换为image。该函数的主要作用是把数值型或日期型数据转换为字符串，而只包含数字的字符串转换为数值型数据一般隐式转换。

  格式：convert(数据类型(长度),表达式[,n])

  函数的第4个参数n是可选的，用于日期时间型数据类型和字符数据类型转换。

程序控制语句

if else

begin end

begin <多条SQL语句> end

while

示例：

Use mydata 
Go
while(select avg(grade) from sc)<90
begin
    update sc set grade=grade+5
    if(select max(grade) from sc)>80
        break
    else
        continue
end
print '成绩已调整'
复制代码