Sql server 列表定义 sql server怎么改表中列的属性

5.1  实体完整性

关系模型的实体完整性

CREATE  TABLE中用PRIMARY KEY定义

[例5.1] 将Student表中的Sno属性定义为码

--列级定义主码
create table Student
(Sno char(9) primary key,
 Sname char(20) not null,
 Ssex char(2),
 Sage smallint,
 Sdept char(20)
 );
 --表级定义主码
 create table Student2
 (Sno char (9),
  Sname char(20) not null,
  Ssex char(2),
  Sage smallint,
  Sdept char(20)
  primary key(Sno)
  );

 

[例5.2] 将SC表中的Sno，Cno属性组定义为码

create table SC
(Sno char(9) not null,
 Cno char(4) not null,
 Grade smallint,
 primary key (Sno,Cno)--只能在表级定义主码
 );

可以联想实体完整性

插入或更新操作时， DBMS 按照实体完整性规则自动进行检查。

检查主码值是否唯一 ，如果不唯一则拒绝插入或修改

检查主码的各个属性是否为空 ，只要有一个为空就拒绝插入或修改

 

5.2  参照完整性

关系模型的参照完整性定义

在 CREATE  TABLE 中用 FOREIGN KEY 短语定义哪些列为外码

用 REFERENCES 短语指明这些外码参照哪些表的主码

 

[例5.3]定义SC中的参照完整性

create table SC2
(Sno char(9) not null,
 Cno char(4) not null,
 grade smallint,
 primary key(Sno,Cno),
 foreign key(Sno) references Student(Sno),
 foreign key (Cno)references Course(Cno)
 );

[例5.4]  显式说明参照完整性的违约处理示例

create table SC3
(Sno char(9) not null,
Cno char(4) not null,
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Studen(Sno)
on delete cascade --级联删除SC3表中相应的元组
on update cascade --级联更新SC3表中相应的元组
foreign key (Cno) references Course(Cno)
on delete on action --当删除course 表中的元组造成了与SC表不一致时拒绝删除
on update cascade --当更新course表中的cno时，级联更新SC表中相应的元组
);

5.3  用户定义的完整性

• 针对某一具体应用的数据必须满足的语义要求
• CREATE TABLE时定义属性上的约束条件

    列值非空（NOT NULL）

    列值唯一（UNIQUE）

    检查列值是否满足一个条件表达式（CHECK）

 

（1）不允许取空值

create table SC4
(Sno char(9) not null,
Cno char (4) not null,
Grade smallint not null,
primary key(Sno,Cno)
--如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在  
--列级不允许取空值的定义 可以不写 
);

[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

create table Dept
(Deptno numeric(2)
 Dname char(9) unique not null,
 --Dname 列值唯一 并且不能取空
 location char(10),
 primary lkey(Deptno)
 );

（3）用CHECK短语指定列值应该满足的条件

  [例5.7]  Student表的Ssex只允许取“男”或“女”。

create char(9)primary key
Sname char(8) not null,
Ssex char(2) check(Ssex in('男','女')),
Sage smallint,
Sdept char(20)
);

[例5.8]  SC表的Grade的值应该在0和100之间。

create table SC
(Sno char(9),
Cno char(4),
grade smallint check (Grade>=0 and Grade<=100),
--Grade 取值范围是0-100
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key(Cno)references Course(Cno)
);

元组上约束条件：

在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制

[例5.9]当学生的性别是男时，其名字不能以Ms.打头。

create table Student4
( Sno char(9),
  Sname char(8) not null,
  Ssex char(2),
  Sage smallint,
  Sdept char(20),
  primary key(Sno),
  check (Ssex='女'or Sname not like 'Ms.%')
  --定义了元组中Sname 和 Ssex 两个属性值之间的而约束条件
  );

性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立;

当性别是男性时，要通过检查则名字一定不能以Ms.打头

5.4  完整性约束命名字句

1.完整性约束命名子句

CONSTRAINT <完整性约束条件名><完整性约束条件>

<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

[例5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。（常用 要会！)

create table Student
( Sno numeric(6)
constraint C1 check(Sno between 90000 and 99999),
Sname char(20)
constraint C2 not null,
Sage numeric C3 check (Sage<30),
Ssex char(2)
constraint C4 check(Ssex in('男','女')),
constraint StudentKey primary e=key(Sno)
);
--在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。

[例5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。

create table Teacher
( Eno numeric(4) primary key
 Ename char(10)
 Job char(8)
 Sal numeric(7,2),
 Deduct numeric(7,2),
 Deptno numeric(2),
 constraint Teacherfkey foreign key(Deptno)
 references Dept(Deptno),
 constraint C1 check(Sal+Deduct>=3000)
 );

2. 修改表中的完整性限制

使用ALTER TABLE语句修改表中的完整性限制

 

[例5.12]去掉例5.10 Student表中对性别的限制。

alter table Student
drop constraint C4;

   [例5.13]  修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

alter table Student
drop constraint C1;
alter table Student
add constraint C1 check (Sno between 900000 and 99999);
 alter table Student
 drop constraint C3;
 alter table Student
 add constraint C3 check (Sage<40);

5.6  断言

1. 创建断言的语句格式

CREATE ASSERTION<断言名><CHECK 子句>

每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。

[例5.18] 限制数据库课程最多60名学生选修

 标准Sql

CREATE ASSERTION ASSE_SC_DB_NUM

  CHECK (60 >= (select count(*)

                    From Course,SC

            Where SC.Cno=Course.Cno and   Course.Cname ='数据库')

       );

 

[例5.19]限制每一门课程最多60名学生选修

CREATE ASSERTION ASSE_SC_CNUM1

  CHECK(60 >= ALL (SELECT count(*)                  FROM  SC

            GROUP BY cno)

            );

   /*此断言的谓词，涉及聚集操作count 和分组函数group by  的SQL语句*/

2. 删除断言的语句格式为

DROP ASSERTION <断言名>;

注意：T-SQL 中没有 ASSERTION 功能。

类似的有RULE，但使用方法不同：

上下文中不允许使用子查询，只允许使用标量表达式。

例如：

CREATE RULE sex_rule

AS @sex in ('男','女')

使用Constraint基本能完成功能，不建议使用RULE。

以后再研究研究吧 目前没看懂。。。

触发器

1、定义触发器

CREATE TRIGGER语法格式
	   CREATE TRIGGER <触发器名>  
       {BEFORE | AFTER} <触发事件> ON <表名>
       REFERENCING NEW|OLD ROW AS<变量>
       FOR EACH  {ROW | STATEMENT}
       [WHEN <触发条件>]<触发动作体>

当特定的系统事件发生时，对规则的条件进行检查。

如果条件成立则执行规则中的动作，否则不执行该动作。

规则中的动作体可以很复杂，通常是一段SQL存储过程

2.触发事件

• 触发事件可以是INSERT、DELETE或UPDATE

      也可以是这几个事件的组合

• 还可以UPDATE OF<触发列，...>，即进一步指明修改哪些列时激活触发器
• AFTER/BEFORE是触发的时机

       AFTER表示在触发事件的操作执行之后激活触发器

       BEFORE表示在触发事件的操作执行之前激活触发器

 

3.触发器类型

• 行级触发器（FOR EACH ROW）
• 语句级触发器（FOR EACH STATEMENT）

例如,在例5.11的TEACHER表上创建一个AFTER UPDATE触发器，触发事件是UPDATE语句：

UPDATE TEACHER SET Deptno=5;

           假设表TEACHER有1000行

• 如果是语句级触发器，那么执行完该语句后，触发动作只发生1次
• 如果是行级触发器，触发动作将执行1000次

           注意：不同的RDBMS产品触发器语法各不相同

 

[例5.21]当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：

create trigger Sc_t
after update of Grade on SC
referencing 
	old row as OldTuple,
	new row as NewTuple
for each row
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)
values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);

触发条件是update并且由for each row子句，那么可以引用的变量有oldrow 和 newrow，分别表示修改前后的元组。

T-Sql

create trigger Sc_t
on SC after update 
as
begin
  declare @OldTuple int;
  declare @NewTuple int;
select @OldTuple= Grade from inserted
select @NewTuple=Grade from inserted;
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)
values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

create trigger Student_Count
after insert on Student
 new table as Delta
 for each statement
 insert into Studentinsertlog(Numbers)
 select count(*) from delta;

 

T-Sql

create table StudentInsertLog(
Numbers int
);
--记得分开写不然会像下面一样报错

create trigger Student_Count
on Student
after insert
as insert into StudentInsertLog(Numbers)
select count(*) from Student;

执行

insert into Student
values('20151167','My','男',12,'CS');

select * from  StudentInsertLog;

 

[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

create trigger insert_or_Update_Sal
before insert or update on Teacher
for each row
begin 
if(new.Job='教授')and(new.sal<4000)
then new.Sal :=4000;
end if;
end;

注意标准sql 的“=”

4.激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行！

一个数据表上可能定义了多个触发器，遵循如下的执行顺序:

（1） 执行该表上的BEFORE触发器;

（2） 激活触发器的SQL语句;

（3） 执行该表上的AFTER触发器。

5.删除触发器

DROP TRIGGER <触发器名> ON <表名>;

 

存储过程和函数

1.存储过程

存储过程：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。

优点：

（1）运行效率高

（2）降低了客户机和服务器之间的通信量 

（3）方便实施企业规则

 

 

（1）创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

(这里不知哪里错误提示列名accountnum无效 Account 表中是有的。。。。。）

create procedure Proc_TRANSFER (
--定义存储过程为 transfer
@inAccount int,
@outAccount int,
@amount float)
--形参
as
begin transaction trans
declare
@totalDepositOut float,--定义变量
@totalDepositin float,
@inAccountum int;

select @totalDepositOut=total from Account where accountnum = @outAccount;
if @totalDepositOut is null --1.如果转出账户不存在或账户中没有存款
begin
    print '转出账户不存在或者账户余额为0'
rollback transaction trans; --回滚事务
return
end ;
if @totalDepositOut < @amount --2.账户余额不足
begin
 print '账户余额不足'
rollback transaction trans;
return;
end 
select @inAccount =accountnum from Account
where accountnum = @inAccount;
if @inAccount is null
begin
print '转入账户不存在'
rollback transaction trans;
return ;
end ;
begin
update Account set total =total-@amount
where accoutnum =@outAccount;
update account set total = total+@amount
where accountnum =@inAccount;
print '转账成功'
commit transaction trans;
return ;
end;

（2）执行存储过程

[例8.9] 从账户01003815868转10000元到01003813828账户中。

sql

call procedure
transfer (01003813828,01003815868,10000);

T-sql

exec	Proc_TRANSFER
		@inAccount = 01003813828,	--转入账户
		@outAccount = 01003815868,	--转出账户
		@amount = 10000     --转出金额

select * from Account;

（3）修改存储过程

ALTER PROCEDURE 过程名1  RENAME TO 过程名2;

（4）删除存储过程 

DROP  PROCEDURE 过程名()；