数据库课堂笔记

安全性

---

权限的授予和回收：

        权限授予一般格式：
                grant  <权限>  on  <对象类型>  to  <用户>  [with  grant  option];

        with  grant  option：允许授予权限的用户再进行权限的授予，即权限的传播；
        All privileges： 将一个表格的所有的权限（增删改查引用）给用户；    

        权限回收一般格式：    
                revoke  <权限>  on  <对象类型>  from  <用户>  [cascade][restrict];

        cascade：回收授权给用户的权限，同时此用户授权给的其他用户权限同样失效；

         管理员创建新用户：
                create  User  <用户名>;

        角色的理解：角色相当于管理员统一授权和回收权限的群聊。角色的权限的授予和回收的格式和权限授予一般格式无差别；

        管理员对角色的创建：
                create  role  <角色名>;

审计：

       理解： 相当于审计员的日志。

        审计语言：
                audit       noaudit
        注：审计语言在SQL server中不可以使用。

        例： audit alter,update on SC;
                noaudit alter,update on SC;

课堂实例： 

---

/*把查询Student表的权限授权给U1*/
grant select on Student To U1;

/*把Student表和Course表的全部权限给U2和U3*/
grant All privileges on Student to U2,U3;
grant All privileges on Course to U2,U3;

/*把对表SC表的查询授权给所有用户*/
grant Select on SC to Public;

/*把查询Student表和修改学生学号的权限授予给用户U4*/
grant update(Sno),select on Student to U4;

/*把对表SC的insert权限授权给U5用户，并允许他再将此权限授权给其他用户*/
grant Insert on SC to U5 with grant Option;

/*把用户U4修改学生学号的权限回收*/
revoke update(Sno) on Student from U4;

/*回收所有用户对表SC的查询权限*/
revoke select on SC from public;

/*把用户U5对SC表的insert权限收回*/
revoke insert on SC from U5 cascade;

/*建立计算机系学生的视图，把对视图的select权限授给王平，把该视图上的所有操作权限授予张明*/
create view CS_Student
as
Select Sno,Sname,Ssex,sage,Sdept from Student
where Sdept='计算机系';

 数据库完整性

---

实体完整性：

设置主码等的操作即为实体而完整性。

通过primary  key来定义主码，在实体完整性中分表级约束、列级约束
        表级约束：在表中单独定义主码primary  key；
        列级约束：在列后定义主码primary  key；

违反实体完整性的课堂实例：

/*向学生表中添加一条记录
9512101 张三 男 21 数学系*/
insert into Student values(9512101,'张三','男',21,'数学系');

/*将王敏的学号改为9512101*/
update Student set Sno='9512101' where Sname='王敏';

参照完整性：

设置外码等的操作即为参照完整性。

通过foreign  key(列名)  references<表名>(列名)来定义外码。

增删改参照表的码使参照完整性遭到破坏。        

违反参照完整性的课堂实例：

/*在选课表SC表中增加一条选课记录
9999999 c01 99*/
/*INSERT 语句与 FOREIGN KEY 约束"FK__SC__Sno__0EA330E9"冲突*/
insert into SC values(9999999,'c01',99);

/*演示操作第一种违反参照完整性的示例
向SC表中添加一条记录：7777777，c01*/
insert into SC(Sno,Cno) values('7777777','c01');

/*演示操作第二种违反参照完整性的示例
向修改SC表中9512101的学号为：7777777*/
update SC set Sno='7777777' where Sno='9512101';

/*演示操作第三种违反参照完整性的示例
从Student表中删除李勇的记录*/
delete from Student where Sname='李勇';

/*演示操作第四种违反参照完整性的示例
修改Student表中李勇的学号为：7777777*/
update Student set Sno='7777777' where Sname='李勇';

/*向学生表中添加一条记录，语句如下：
违反Check约束1:man;2:50*/
insert into Student(Sno,Sname,Ssex,Sage)
	values('9512101','张三','man',50);

/*创建学生等级表Student，要求学号在90000~99999之间，
姓名不能取空值，年龄小于30，性别只能是“男”或“女”*/
create table Student1
(Sno numeric(6)
 constraint c1 check(sno between 90000 and 99999),
 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)
 );

//查看创建的表的约束条件
exec SP_helpconstraint Student1;

/*查看全局约束条件*/
exec SP_help Student1;

约束不可以修改，只可以增加和删除。

/*删除c4约束*/
alter table student1 drop constraint c4;

触发器：

---

create trigger语法格式

create  trigger <触发器名>
{before/after} <触发事件>  on  <表名>    指明触发器激活的时间是在执行触发事件前或后

删除触发器：
        drop Trigger <触发器名>;

表的拥有者才可以在表上创建触发器。

/*创建一个触发器（当student表修改时，显示一个已经修改学生信息*/
create Trigger T1 on Student for update as
print '已修改Student表的数据' go

/*查看代码*/
exec Sp_helptext T1;

/*将学生表中的所有学生的年龄加1岁*/
update student set Sage=sage+1;

课堂SQL数据库环境资源：

---

表格创建：

CREATE TABLE Student
(
Sno char(7) PRIMARY KEY,
Sname char(10) NOT NULL,
Ssex char(2) CHECK(Ssex='男' OR Ssex='女'),
Sage tinyint CHECK(Sage>=15 AND Sage<=45),
Sdept char(20) DEFAULT '计算机系' 
);

CREATE TABLE Course
(
Cno char(10) NOT NULL,
Cname char(20) NOT NULL,
Ccredit tinyint CHECK(Ccredit>0),
Semester tinyint CHECK(Semester>0),
PRIMARY KEY(Cno)
);

CREATE TABLE SC
(
Sno char(7) NOT NULL,
Cno char(10) NOT NULL,
Grade tinyint,
CHECK(Grade>=0 AND Grade<=100),
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
);

Student表数据的插入： 

insert into student values('9512101','李勇','男',19,'计算机系');
insert into student values('9512102','刘晨','男',20,'计算机系');
insert into student values('9512103','王敏','女',20,'计算机系');
insert into student values('9521101','张立','男',22,'信息系');
insert into student values('9521102','吴宾','女',21,'信息系');
insert into student values('9521103','张海','男',20,'信息系');
insert into student values('9531101','钱小平','女',18,'数学系');
insert into student values('9531102','王大力','男',19,'数学系');

Course表数据的插入：

insert into course values('c01','计算机文化课',3,1);
insert into course values('c02','VB',2,3);
insert into course values('c03','计算机网络',4,7);
insert into course values('c04','数据库基础',6,6);
insert into course values('c05','高等数学',8,2);
insert into course values('c06','数据结构',5,4);

SC表数据的插入：

insert into sc values('9512101','c01',90);
insert into sc values('9512101','c02',86);
insert into sc values('9512101','c06',null);
insert into sc values('9512102','c02',78);
insert into sc values('9512102','c04',66);
insert into sc values('9521102','c01',82);
insert into sc values('9521102','c02',75);
insert into sc values('9521102','c04',92);
insert into sc values('9521102','c05',50);
insert into sc values('9521103','c02',68);
insert into sc values('9521103','c06',null);
insert into sc values('9531101','c01',80);
insert into sc values('9531101','c05',95);
insert into sc values('9531102','c05',85);