数据库系统个人记录

备注：本人不喜欢用大写，这是学习中的，如需实战使用请使用大写开头的表名以及列名哦！！！
DBMS：全称：(Database Manage System)数据库管理系统
OS：全称：(Operating System)操作系统
 
create schema   drop schema     ---创建模式
create table    drop table      alter table         --创建表
create view     drop view           ---创建视图
create index    drop index      alter index       create index 创建索引
 
 
1、创建模式
create schema test authorization zhang
create table tab1(
    col1 smallint,
    col2 int,
    col3 char(20),
    col4 numeric(10,3),
    col5 decimal(5,2)
);
 
decimal(size,d)
numeric(size,d)	存储带有小数的数字。数字的最大位数由 "size" 指定。小数点右边的最大位数由 "d" 指定。
 
2、删除模式
drop schema <模式名> <cascade|restrict>
cascade(级联)
删除模式的同时把该模式中所有的数据库对象全部删除
restrict(限制)
如果该模式中定义了下属的数据库对象（如表，视图等），则拒绝该删除语句的执行
 
drop schema zhang cascade;
删除模式zhang
同时该模式定义的表tab1也被删除
 
 
create table student(
    sno char(9) primary key,  --主码
    sname char(20) unique, --约束
    ssex    char(2),
    sage smallint,
    sdept   char(20)
);
 
create table course(
    cno char(4) primary key,
    cname char(40),
    cpno char(4),
    ccredit smallint,
    foreign key (cpno) references course(cno)
);
 
 
create table sc(
    scno char(9),   --备注后面使用了alter table sc rename column scno to sno ; 此处故意填错
    cno char(4),
    grage smallint,
    primary key (sno,cno),
    foreign key (sno) references student(sno), 
    foreign key (cno) references course(cno)
)
 
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
 
 
修改基本表
add 子句用于增加新列，新的列级完整性约束条件和新的表级完整性约束条件
drop column 子句用于删除表中的列
如果定义了cascade短语，则自动删除应用了该列的其他对象
如果指定了restrict短语，则如果该列被其他对象应用，关系数据库管理系统将拒绝删除该列
drop constraint 子句用于删除指定的完整性约束条件
alter column 子句用于修改原有的列定义，包括修改列名和数据类型
 
 
alter table student add s_entrance DATE; --插入列
alter table student alter column sage int;   --修改列的字段属性
alter table table_name modify column_name new_datatype; --备注：有时我们看到modify修改字段属性也是可以的，是alter的功能模块功能。 
alter table Course add unique(cname);  --添加约束
alter table old_table_name rename to new_table_name; --修改表名
alter table table_name rename column old_column_name to new_column_name; --修改列表名
 
 
删除基本表
DROP TABLE <表名>［RESTRICT| CASCADE］;
RESTRICT：删除表是有限制的。  --限制
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。  --级联
在删除基本表的同时，相关的依赖对象一起删除 
 
 
drop table student cascade;
基本表定义被删除，数据被删除
表上建立的索引，视图，触发器等一般也将被删除
 
 
create view is_student 
as                  ---备注 as 表示连接语句的操作符
select sno,sname,sage from student where sdept="IS";
 
DROP table student restrict;
--error :cannot drop table student because other object depend on it 
 
 
drop table student cascade;
--notice:drop cascade to view is_student
select * from is_student;
--error: relation "is_student" does not exist
 
创建索引
create unique cluster index 索引名
on 表名(列名[次序],列名[次序])
<表名>：要建索引的基本表的名字
索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔
<次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
UNIQUE：此索引的每一个索引值只对应唯一的数据记录
CLUSTER：表示要建立的索引是聚簇索引
 
 
[例3.13] 为学生-课程数据库中的Student，Course，SC三个表建立索引。
Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，
SC表按学号升序和课程号降序建唯一索引
create unique index stusno on student(sno);   --Student表按学号升序建唯一索引
create unique index coucno on course(cno);
create unique index  scno ON sc(sno ASC,cno DESC);   ----约束具体法用
 
 
alter index 旧索引名 rename to 新索引名
将sc表的scno索引改为scsno
alter  index scno rename to scsno;
 
删除索引
drop index <索引名>
 
drop index stusname;
 
select [all|distinct] <目标列表达式>[,<目标列表达式>]...
from <表名或视图名>[,<表名或视图名>]...|(select 语句)
    [as]<别名>
[where <条件表达式>]
[group by <列名1>[having <条件表达式>]]
[order by <列名2> [asc|DESC]];
 
SELECT子句：指定要显示的属性列
FROM子句：指定查询对象（基本表或视图）
WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语：只有满足指定条件的组才予以输出
ORDER BY子句：对查询结果表按指定列值的升序或降序排序 
 
 
查询满足条件的元组
查询条件            谓词
比较    =, >, <, >=, <=, !=, <>, !>, !<; NOT+上述比较运算符
确定范围    BETWEEN AND, NOT BETWEEN AND
确定集合    in，not index
字符匹配    like，not like
空值        is null ,is not null
多重条件(逻辑运算)   and ,or,not
 
1、比较
select sname from student where sdept='cs';
select sname,sage from student where sage<20;
 
2、确定范围
select sname,sdept,sage from student where sage between 20 and 23;
select sname,sdept,sage from student where sage not between 20 and 23;
 
3、确定集合
select sname,ssex from student where sdept in ('cs','ma','IS');
select sname,ssex from student where sdept not in ('cs','ma','IS');
 
4、字符匹配
SELECT * FROM Student WHERE  Sno LIKE '201215121';
SELECT * FROM Student WHERE  Sno= '201215121';
SELECT * FROM Student WHERE  sname like '刘%';
SELECT * FROM Student WHERE  sname  not like '刘%';
SELECT * FROM Student WHERE  sname like '欧阳恒_';   
SELECT * FROM Student WHERE  sname like '_阳恒%';  
 
	ESCAPE '＼' 表示“ ＼” 为换码字符
 
查询DB_Design课程的课程号和学分。    ---使用换码字符将通配符转义为普通字符  
select cno,ccredit from course where cname like 'DB\_Design' escape '\';
 
查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
select * from course where cname like 'DB\_%i_ _' ESCAPE '\' ;
 
5、涉及空值的查询
SELECT Sno,Cno FROM SC WHERE  Grade IS NULL;
select sno,cno from sc where grade is not null;
 
6、多重条件查询
SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20;
SELECT Sname, Ssex FROM Student WHERE  Sdept IN ('CS ','MA ','IS')
SELECT Sname, Ssex FROM Student WHERE  Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';   --与上面相同
 
    
----  order by 子句
可以按一个或多个属性列排序
升序：ASC;降序：DESC;缺省值为升序
对于空值，排序时显示的次序由具体系统实现来决定
 
SELECT Sno, Grade FROM SC WHERE  Cno= ' 3 ' ORDER BY Grade DESC; 
 
 
-----  聚合函数
统计元组个数
    COUNT(*)
统计一列中值的个数
    COUNT([DISTINCT|ALL] <列名>)
计算一列值的总和（此列必须为数值型）
    SUM([DISTINCT|ALL] <列名>)	
计算一列值的平均值（此列必须为数值型）
    AVG([DISTINCT|ALL] <列名>)
求一列中的最大值和最小值
    MAX([DISTINCT|ALL] <列名>)
    MIN([DISTINCT|ALL] <列名>)
 
 
查询学生总人数。
    SELECT COUNT(*) FROM  Student; 
查询选修了课程的学生人数。
    SELECT COUNT(DISTINCT Sno) FROM SC;
计算1号课程的学生平均成绩。
    SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 ';
查询选修1号课程的学生最高分数。
   SELECT MAX(Grade) FROM SC WHERE Cno='1';
查询学生201215012选修课程的总学分数。
    SELECT SUM(Ccredit) FROM  SC,Course WHERE Sno='201215012' AND SC.Cno=Course.Cno;
 
-------------  group BY子句
GROUP BY子句分组：
    细化聚集函数的作用对象
        如果未对查询结果分组，聚集函数将作用于整个查询结果
        对查询结果分组后，聚集函数将分别作用于每个组 
        按指定的一列或多列值分组，值相等的为一组
 
求各个课程号及相应的选课人数。
    select cno,count(sno) from sc group by cno;
    
查询选修了3门以上课程的学生学号
    select sno from sc group by sno having count(*)>3;
 
查询平均成绩大于等于90分的学生学号和平均成绩
下面的语句是不对的：
    SELECT Sno, AVG(Grade) FROM  SC WHERE AVG(Grade)>=90 GROUP BY Sno;
因为WHERE子句中是不能用聚集函数作为条件表达式
正确的查询语句应该是：
    SELECT  Sno, AVG(Grade) FROM  SC GROUP BY Sno HAVING AVG(Grade)>=90;
 
HAVING短语与WHERE子句的区别：
    作用对象不同
    WHERE子句作用于基表或视图，从中选择满足条件的元组
    HAVING短语作用于组，从中选择满足条件的组。
 
 
1. 等值与非等值连接查询 
    等值连接：连接运算符为=
查询每个学生及其选修课程的情况
select student.*,sc.* from student ,sc where student.sno=sc.sno;
 
2.排序合并法（SORT-MERGE）
    常用于=连接
    首先按连接属性对表1和表2排序
对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，
找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续
找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，
找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续
重复上述操作，直到表1或表2中的全部元组都处理完毕为止
 
---------------------------------------------
查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
SELECT Student.Sno, Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;
执行过程：  
    先从SC中挑选出Cno='2'并且Grade>90的元组形成一个中间关系
    再和Student中满足连接条件的元组进行连接得到最终的结果关系
---------------------------------------------
 
 
2、自身连接
自身连接：一个表与自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀
 
查询每一门课程的间接先修课（即先修课的先修课）  ---备注：先修课：为上这门课程之前必须学习的的课程号。
SELECT  FIRST.Cno, SECOND.Cpno
     FROM  Course  FIRST, Course  SECOND
     WHERE FIRST.Cpno = SECOND.Cno;
 
 
 
sql select中加入常量列
string sql="select a,b,'常量' as c from table"
注：单引号' ' 很重要，否则编译时会把其看成查询参数，从而提示参数未指定错误
 
 
3、外连接
外连接与普通连接的区别
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
左外连接     ---left join  on 在某些数据库中，left join 称为left outer join 
    列出左边关系中的所有的元组
右外连接
    列出右边关系中所有的元组
 
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade 
FROM  Student  LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);     --left out join   ==》 left join
 
 
4、多表连接
多表连接：两个以上的表进行连接
 
查询每个学生的学号、姓名、选修的课程名及成绩
    SELECT Student.Sno, Sname, Cname, Grade
    FROM Student, SC, Course    /*多表连接*/
    WHERE Student.Sno = SC.Sno  AND SC.Cno = Course.Cno;
 
 
5、嵌套查询
嵌套查询概述
    一个select-from-where语句称为查询块
    将一个查询块嵌套在另外一个查询块的where子句或having短语的条件中的查询称为嵌套查询
 
    SELECT Sname	               /*外层查询/父查询*/
    FROM Student
    WHERE Sno IN
                ( SELECT Sno       /*内层查询/子查询*/
                    FROM SC
                    WHERE Cno= ' 2 ');
 
备注：
sql语句允许多层嵌套查询：即一个查询中还可以嵌套其他子查询
子查询的限制：不能使用order by 语句
 
嵌套查询求解方法：
不相关子查询：子查询的查询条件不依懒于父查询
由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
 
相关子查询：子查询的查询条件依赖于父查询
首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
然后再取外层表的下一个元组
重复这一过程，直至外层表全部检查完为止
 
 嵌套查询的几种方法
  1.带有IN谓词的子查询 
  2.带有比较运算符的子查询
  3.带有ANY（SOME）或ALL谓词的子查询
  4.带有EXISTS谓词的子查询
        exists 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False
 
 
[例 3.55]查询与“刘晨”在同一个系学习的学生。
         此查询要求可以分步来完成
    ① 确定“刘晨”所在系名             
         SELECT  Sdept  
         FROM     Student                            
         WHERE  Sname= ' 刘晨 ';
    ② 查找所有在CS系学习的学生。    
        SELECT   Sno, Sname, Sdept     
        FROM      Student                 
        WHERE   Sdept= ' CS '; 
 
将第一步查询嵌入到第二步查询的条件中
    SELECT Sno, Sname, Sdept
    	FROM Student
   	WHERE Sdept  IN
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= ' 刘晨 ');
----------------此查询为不相关子查询。--------------
 
 
[例 3.55]与上面相同的带有《---》自身连接完成查询要求
SELECT  S1.Sno, S1.Sname,S1.Sdept
      FROM     Student S1,Student S2
      WHERE  S1.Sdept = S2.Sdept  AND
                      S2.Sname = '刘晨';
 
[例 3.56]查询选修了课程名为“信息系统”的学生学号和姓名
 	SELECT Sno,Sname                 ③ 最后在Student关系中
  	FROM    Student                          取出Sno和Sname
 	WHERE Sno  IN
             (SELECT Sno                     ② 然后在SC关系中找出选
              FROM    SC                         修了3号课程的学生学号
              WHERE  Cno IN
                     (SELECT Cno             ① 首先在Course关系中找出
                       FROM Course           “信息系统”的课程号，为3号
                       WHERE Cname= '信息系统'                      
		          )
              );
 
用连接查询实现[例 3.56] :
     SELECT Sno,Sname
      FROM    Student,SC,Course
      WHERE Student.Sno = SC.Sno  AND
                     SC.Cno = Course.Cno AND
                     Course.Cname='信息系统';
 
---------------------------------------------------------------------
Student.Sno	Sname	Ssex	Sage	Sdept	Cno
201215121	李勇	男	20	CS	1
201215121	李勇	男	20	CS	2
201215121	李勇	男	20	CS	3
201215122	刘晨	女	19	CS	2
201215122	刘晨	女	19	CS	3
201215123	王敏	女	18	MA	NULL
201215125	张立	男	19	IS	NULL
---------------------------------------------------------------------
 
带有比较运算符的子查询
[例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。
    select sno,cno 
    from sc x
    where grade >=(select avg(grade)
                    from sc y
                    where y.sno=x.sno);
 
带有ANY（SOME）或ALL谓词的子查询 
使用any或all谓词时必须同时使用比较运算符
    语法为：
    > ANY	大于子查询结果中的某个值       
    > ALL	大于子查询结果中的所有值
    < ANY	小于子查询结果中的某个值    
    < ALL	小于子查询结果中的所有值
    >= ANY	大于等于子查询结果中的某个值    
    >= ALL	大于等于子查询结果中的所有值
    <= ANY	小于等于子查询结果中的某个值    
    <= ALL	小于等于子查询结果中的所有值
    = ANY	等于子查询结果中的某个值        
    =ALL	等于子查询结果中的所有值（通常没有实际意义）
    !=（或<>）ANY	不等于子查询结果中的某个值
    !=（或<>）ALL	不等于子查询结果中的任何一个值
 
[例 3.58]  查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
select sname,sage
from student 
where sage < any (select sage
                from student 
                where sdept='cs')
and sdept <> 'cs';      /*父查询块中的条件 */
 
结果：
    sname   sage
    王敏    18  
    张立    19
 
执行过程：
（1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
（2）处理父查询，找所有不是CS系且年龄小于20 或 19的学生
 
-----用聚集(聚合)函数实现[例 3.58] 
SELECT Sname,Sage
FROM   Student
WHERE Sage < 
            (SELECT MAX（Sage）
            FROM Student
            WHERE Sdept= 'CS ')
AND Sdept <> ' CS ';
 
[例 3.59]  查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一：用ALL谓词
select sname,sage
from student
where sage < all
            (select sage 
            from student
            where sdept='cs')
and sdept <> 'cs';
方法二：用聚集函数
select sname,sage
from student
where sage <
            (select MIN(sage)
            from student 
            where sdept='cs')
and sdept <> 'cs';
 
 
带有EXISTS谓词的子查询
 
[例 3.60]查询所有选修了1号课程的学生姓名。
思路分析：
    本查询涉及student和sc关系
    在student中依次取每个元组的sno值，用此值去检查sc表
    若sc中存在这样的元组，其sno值等于此student.sno值，并且其cno='1',则取此student.sname送入结果表
select sname 
from student 
where exists
        (select *
        from sc 
        where sno=student.sno and cno='1');
    
 
[例 3.61]  查询没有选修1号课程的学生姓名。
select sname 
from student 
where not exists
        (select *
        from sc 
        where sno=student.sno and cno='1');
 
 
[例 3.55]查询与“刘晨”在同一个系学习的学生。
    可以用带EXISTS谓词的子查询替换：
    
 SELECT Sno,Sname,Sdept
     FROM Student S1
      WHERE EXISTS
             　   (SELECT *
                    FROM Student S2
                    WHERE S2.Sdept = S1.Sdept AND
                          S2.Sname = '刘晨');
 
[例 3.62] 查询选修了全部课程的学生姓名。
    SELECT Sname
    FROM Student
    WHERE NOT EXISTS                         --负 
            (SELECT *
            FROM Course
            WHERE NOT EXISTS                  --负
                    (SELECT *
                    FROM SC
                    WHERE Sno= Student.Sno           --正
                            AND Cno= Course.Cno
                    )
            );
 
--负负得正
 
 
 
-------------------集合查询-----------------------
《 集合查询  》
集合操作的种类
并操作UNION
交操作INTERSECT
差操作EXCEPT
参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同
 
[例 3.64]  查询计算机科学系的学生及年龄不大于19岁的学生。  --及  union
select *
from student
where sdept='cs'
union
select *
from student
where sage<=19;
 
union:将多个查询结果合并起来时，系统自动去掉重复元组
union all：将多个查询结果合平起来时，保留重复元组
 
[例 3.65]  查询选修了课程1或者选修了课程2的学生。    --或 union
select sno
from sc
where cno=' 1 '
union
select sno
from sc
where cno= ' 2 ';
 
 
[例3.66]  查询计算机科学系的学生与年龄不大于19岁的学生的交集。 --交集  intersect
select *
from student
where sdept='cs'
intersect
select * 
from student
where sage<=19;
 
[例 3.66] 实际上就是查询计算机科学系中年龄不大于19岁的学生。
select *
from student
where sdept= 'cs' and  sage<=19;
 
[例 3.67]查询既选修了课程1又选修了课程2的学生。   --又  intersect
select sno
from sc
where cno='1'
intersect
select sno
from sc
where cno='2';
 
也可以表示为:
SELECT Sno
FROM    SC
WHERE Cno=' 1 ' AND Sno IN
                        (SELECT Sno
                            FROM SC
                            WHERE Cno=' 2 ');
 
[例 3.68]  查询计算机科学系的学生与年龄不大于19岁的学生的差集。   ---差集  except
select *
from student 
where sdept='cs'
except
select * 
from student 
where sage<=19;
[例3.68]实际上是查询计算机科学系中年龄大于19岁的学生        
SELECT *
FROM Student
WHERE Sdept= 'CS' AND  Sage>19;
 
--------------------- 基于派生表的查询  -------------------------
基于派生表的查询:
子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象。
 
[例3.57]找出每个学生超过他自己选修课程平均成绩的课程号。    ------难点
select sno,cno
from sc,(select sno,Avg(grade))
           from sc
           group by sno) as avg_sc(avg_sno,avg_grade)  
where sc.sno = avg_sc.avg_sno  and sc.grade >= avg_sc.avg_grade;
 
如果子查询中没有聚集函数，派生表可以不指定属性列，子查询select子句后面的列名为其缺省属性。
 
[例3.60]查询所有选修了1号课程的学生姓名，可以用如下查询完成：
select sname 
from student,(select sno from sc where cno='1') as sc1
where student.sno=sc1.sno;
 
 
-------------------SELECT语句的一般形式 ------------------------ 
 SELECT [ALL|DISTINCT]  
   <目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
 FROM     <表名或视图名> [别名] 
                [ ,<表名或视图名> [别名]] …
                |(<SELECT语句>)[AS]<别名>
 [WHERE <条件表达式>]
 [GROUP BY <列名1>[HAVING<条件表达式>]]
 [ORDER BY <列名2> [ASC|DESC]];
 
 
目标列表达式格式：
（1） *
（2） <表名>.*
（3） COUNT([DISTINCT|ALL]* )
（4） [<表名>.]<属性列名表达式>[,<表名>.]<属性列名表达式>]…
	
其中<属性列名表达式>可以是由属性列、作用于属性列
的聚集函数和常量的任意算术运算（+，-，*，/）组成的
运算公式 
 
聚集函数的一般格式：
    COUNT
    SUM
    AVG         （[DISTINCT|ALL] <列名>）
    MAX
    MIN
 
WHERE子句的条件表达式的可选格式：
（1）
                  <属性列名>
<属性列名> θ      <常量>
			      [ANY|ALL] （SELECT语句）
 
（2）                         
 				            <属性列名>                 <属性列名>  
<属性列名>  [NOT] BETWEEN   <常量>               AND   <常量>      
                           （SELECT语句）             （SELECT语句）
 
（3）
                        （<值1>[，<值2> ] …）
 <属性列名> [NOT] IN                    
                        （SELECT语句）
 
（4）  <属性列名> [NOT] LIKE <匹配串>
（5）  <属性列名> IS [NOT] NULL
（6）  [NOT] EXISTS （SELECT语句）
（7）
                  AND                        AND
 <条件表达式>                <条件表达式>                   <条件表达>    …
                  OR                          OR
 
 
---------------------------  插入元组 -------------------------
插入元组:
语句格式
    INSERT
    INTO <表名> [(<属性列1>[,<属性列2 >…)]
    VALUES (<常量1> [,<常量2>]… );
功能：将新元组插入指定表中
 
INTO子句：
    指定要插入数据的表名及属性列
    属性列的顺序可与表定义中的顺序不一致
    没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
    指定部分属性列：插入的元组在其余属性列上取空值
 
VALUES子句：
提供的值必须与INTO子句匹配
    值的个数
    值的类型
 
[例3.69]将一个新学生元组（学号：201215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中。
insert into  Student (Sno,Sname,Ssex,Sdept,Sage) values ('201215128','陈冬','男','IS',18);
 
[例3.72]  对每一个系，求学生的平均年龄，并把结果存入数据库
第一步：建表
CREATE  TABLE  Dept_age(
    Sdept    CHAR(15)   /*系名*/
    Avg_age  SMALLINT   /*学生平均年龄*/
);          	
第二步：插入数据
INSERT INTO  Dept_age(Sdept,Avg_age)
SELECT  Sdept，AVG(Sage)
FROM     Student
GROUP BY Sdept;
 
------------------------  修改数据  --------------------------------
语句格式
UPDATE  <表名>
SET  <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
 
三种修改方式:
    修改某一个元组的值
    修改多个元组的值
    带子查询的修改语句
 
功能:
    修改指定表中满足WHERE子句条件的元组
    SET子句给出<表达式>的值用于取代相应的属性列
    如果省略WHERE子句，表示要修改表中的所有元组
 
[例3.73]  将学生201215121的年龄改为22岁
UPDATE  Student SET Sage=22 WHERE  Sno=' 201215121 '; 
 
[例3.74]  将所有学生的年龄增加1岁。
UPDATE Student SET Sage= Sage+1;
 
[例3.75]  将计算机科学系全体学生的成绩置零。
update sc 
set grade=0
where sno in
(   select sno
    from student
    where sdept='cs');
 
关系数据库管理系统在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则
    实体完整性
    主码不允许修改
    用户定义的完整性
        not null约束
        unique约束
        值域约束
 
------------------------  删除数据 -----------------------
语句格式：
    DELETE
    FROM     <表名>
    [WHERE <条件>];
功能：
    删除指定表中满足WHERE子句条件的元组
WHERE子句：
    指定要删除的元组
    缺省表示要删除表中的全部元组，表的定义仍在字典中
 
三种删除方式：
    删除某一个元组的值
    删除多个元组的值
    带子查询的删除语句
 
[例3.76]  删除学号为201215128的学生记录。
delete from student where sno='201215128';
[例3.77]  删除所有的学生选课记录。
delete from sc;
 
[例3.78]  删除计算机科学系所有学生的选课记录。
delete 
from sc 
where sno in(
    select sno 
    from student 
    where sdept='cs'
);
 
 
 
---------------------  空值的处理  --------------------------
空值就是“不知道”或“不存在”或“无意义”的值。
一般有以下几种情况：
    该属性应该有一个值，但目前不知道它的具体值
    该属性不应该有值
    由于某种原因不便于填写
 
空值的产生:
    空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要特殊的处理。
 
[例 3.79]向SC表中插入一个元组，学生号是”201215126”，课程号是”1”，成绩为空。
insert into SC(Sno,Cno,Grade)  values('201215126 ','1',NULL);   /*该学生还没有考试成绩，取空值*/
或者
insert into SC(Sno,Cno) values(' 201215126 ','1');             /*没有赋值的属性，其值为空值*/
 
[例3.80]  将Student表中学生号为”201215200”的学生所属的系改为空值。
update student set sdept=null where sno='201215200';
 
空值的判断：
    判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示。
 
[例 3.81]  从Student表中找出漏填了数据的学生信息
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
 
空值的约束条件
    属性定义（或者域定义）中
        有NOT NULL约束条件的不能取空值
        加了UNIQUE限制的属性不能取空值
        码属性不能取空值
 
[例3.82]  找出选修1号课程的不及格的学生。
    SELECT Sno
    FROM SC
    WHERE Grade < 60 AND Cno='1';
备注：查询结果不包括缺考的学生，因为他们的Grade值为 null。
 
[例 3.83]  选出选修1号课程的不及格的学生以及缺考的学生。
select sno
from sc
where grade < 60 and cno='1'
union 
select sno
from sc
where grade is null and cno='1'
或者
select sno 
from sc
where cno='1' and (grade < 60 or grade is null);
 
 
------------------------------  视图 -------------------------------
视图的特点：
    虚表，是从一个或几个基本表（或视图）导出的表
    只存放视图的定义，不存放视图对应的数据
    基表中的数据发生变化，从视图中查询出的数据也随之改变
 
语句格式
CREATE  VIEW 
    <视图名>  [(<列名>  [,<列名>]…)]
AS  <子查询>
[WITH  CHECK  OPTION];
 
WITH CHECK OPTION
    对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现。
 
组成视图的属性列名：全部省略或全部指定
全部省略: 
    由子查询中SELECT目标列中的诸字段组成
明确指定视图的所有列名:
    某个目标列是聚集函数或列表达式
    多表连接时选出了几个同名列作为视图的字段
    需要在视图中为某个列启用新的更合适的名字
 
 
 [例3.84]  建立信息系学生的视图。
create view IS_STUDENT
as
select sno,sname,sage
from student
where sdept='IS';
 
[例3.85]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
create view IS_STUDENT
as 
select sno,sname,sage
from  student
where  sdept= 'IS'
with check option;   --可以进行修改和插入等操作
 
定义IS_Student视图时加上了WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS'的条件。
若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。
IS_Student视图就是一个行列子集视图。
 
基于多个基表的视图
[例3.86]  建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。
create view IS_S1(sno,sname,grade)
as
select student.sno,sname,grade
from student,sc
where sdept='IS' and student.sno=sc.sno and sc.cno='1';
 
基于视图的视图
[例3.87]  建立信息系选修了1号课程且成绩在90分以上的学生的视图。
create view IS_S2
as
select sno,sname,grade
from IS_S1
where grade>=90;
 
带表达的视图
[例3.88]  定义一个反映学生出生年份的视图。
create view BT_S(sno,sname,sbirth)
as
select sno,sname,2014-sage
from student;
 
分组视图
[例3.89]  将学生的学号及平均成绩定义为一个视图
create view  S_G(sno,gavg)
as
select sno,AVG(grade)
from sc
group by sno;
 
[例3.90]将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT  *                        /*没有不指定属性列*/
FROM  Student
WHERE Ssex=‘女’;
 
缺点：
    修改基表Student的结构后，Student表与 F_Student 视图的映象关系被破坏，导致该视图不能正确工作。
 
 
删除视图
语句的格式：
		DROP  VIEW  <视图名>[CASCADE];
    该语句从数据字典中删除指定的视图定义
    如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 
    删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除 
 
[例3.91 ] 删除视图BT_S和IS_S1
    DROP VIEW BT_S;	/*成功执行*/
    DROP VIEW IS_S1;	/*拒绝执行*/
 
    要删除IS_S1，需使用级联删除：
    DROP VIEW IS_S1 CASCADE;   ----使用cascade
 
 
--------------------------    查询视图   ------------------------
用户角度：查询视图与查询基本表相同
关系数据库管理系统实现视图查询的方法
    视图消解法（View Resolution）
        进行有效性检查
        转换成等价的对基本表的查询
        执行修正后的查询
 
[例3.92]  在信息系学生的视图中找出年龄小于20岁的学生。
    SELECT  Sno,Sage
    FROM    IS_Student
    WHERE   Sage<20;
 
视图消解转换后的查询语句为：
    SELECT  Sno,Sage       
    FROM  Student
    WHERE  Sdept= 'IS'  AND  Sage<20;
 
[例3.93]  查询选修了1号课程的信息系学生
SELECT IS_Student.Sno,Sname
FROM   IS_Student,SC
WHERE  IS_Student.Sno =SC.Sno AND SC.Cno= '1';
 
视图消解法的局限
    有些情况下，视图消解法不能生成正确的查询。
 
[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩
    SELECT *
    FROM   S_G
    WHERE  Gavg>=90;
 
S_G视图的子查询定义： 
    CREATE VIEW S_G (Sno,Gavg)
    AS 
    SELECT  Sno,AVG(Grade)
    FROM  SC
    GROUP BY Sno;
 
错误：
    SELECT Sno，AVG(Grade)
    FROM     SC
    WHERE  AVG(Grade)>=90   --聚合函数不能在where之后
    GROUP BY Sno;
 
正确：
    SELECT  Sno,AVG(Grade)
    FROM  SC
    GROUP BY Sno
    HAVING AVG(Grade)>=90;
 
[例3.94]也可以用如下SQL语句完成          ----此方法需了解
	SELECT *
    FROM  (SELECT Sno,AVG(Grade)
		       FROM  SC 
 		       GROUP BY Sno) AS S_G(Sno,Gavg)           
    WHERE Gavg>=90;
 
[例3.95]  将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
    UPDATE  IS_Student
    SET  Sname= '刘辰'
    WHERE  Sno= ' 201215122 ';
转换后的语句：
    UPDATE  Student
    SET Sname= '刘辰'
    WHERE Sno= ' 201215122 ' AND Sdept= 'IS';
 
[例3.96]  向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁
    INSERT INTO IS_Student
    VALUES(‘201215129’,’赵新’,20);
转换为对基本表的更新：
    INSERT INTO Student(Sno,Sname,Sage,Sdept)
    VALUES('200215129','赵新',20,'IS' );
 
[例3.97]  删除信息系学生视图IS_Student中学号为”201215129”的记录 
    DELETE
    FROM IS_Student
    WHERE Sno= ' 201215129 ';
转换为对基本表的更新：
    DELETE
    FROM Student
    WHERE Sno= ' 201215129 ' AND Sdept= 'IS';
 
 
更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新    ---了解
 
[例3.89]  定义的视图S_G为不可更新视图。
UPDATE  S_G
SET Gavg=90
WHERE  Sno= '201215121';
备注：这个对视图的更新无法转换成对基本表SC的更新
 
允许对行列子集视图进行更新
对其他类型视图的更新不同系统有不同限制
 
DB2对视图更新的限制：
    若视图是由两个以上基本表导出的，则此视图不允许更新。
    若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
    若视图的字段来自集函数，则此视图不允许更新。
    若视图定义中含有GROUP BY子句，则此视图不允许更新。
    若视图定义中含有DISTINCT短语，则此视图不允许更新。
    若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
 
例：将SC中成绩在平均成绩之上的元组定义成一个视图
CREATE VIEW GOOD_SC
AS 
SELECT  Sno,Cno,Grade
FROM     SC
WHERE Grade > (SELECT AVG(Grade) FROM SC);
一个不允许更新的视图上定义的视图也不允许更新
 
视图的作用：
    视图能够简化用户的操作
    视图使用户能以多种角度看待同一数据 
    视图对重构数据库提供了一定程度的逻辑独立性 
    视图能够对机密数据提供安全保护  
    适当的利用视图可以更清晰的表达查询
 
视图能够简化用户的操作
    当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作
    基于多张表连接形成的视图
    基于复杂嵌套查询的视图
    含导出属性的视图
 
视图使用户能以多种角度看待同一数据
    视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要
 
视图对重构数据库提供了一定程度的逻辑独立性
    数据库重构 ：
    例：学生关系Student(Sno,Sname,Ssex,Sage,Sdept) 
        “垂直”地分成两个基本表：
            SX(Sno,Sname,Sage)        
            SY(Sno,Ssex,Sdept)
 
通过建立一个视图Student：
CREATE VIEW  Student(Sno,Sname,Ssex,Sage,Sdept)
AS  
SELECT SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept
FROM   SX,SY
WHERE  SX.Sno=SY.Sno;
使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据
 
视图对重构数据库提供了一定程度的逻辑独立性(续)
    视图只能在一定程度上提供数据的逻辑独立性
        由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。
 
视图能够对机密数据提供安全保护
        对不同用户定义不同视图，使每个用户只能看到他有权看到的数据
 
适当的利用视图可以更清晰的表达查询
    经常需要执行这样的查询“对每个同学找出他获得最高成绩的课程号”。可以先定义一个视图，求出每个同学获得的最高成绩 
 
CREATE VIEW VMGRADE
AS
SELECT Sno, MAX(Grade)  Mgrade
FROM  SC
GROUP BY Sno;
 
然后用如下的查询语句完成查询：
    SELECT SC.Sno,Cno
    FROM SC,VMGRADE 
    WHERE SC.Sno=VMGRADE.Sno AND       
    SC.Grade=VMGRADE .Mgrade; 
 
 
-------------------   数据库安全性    ----------------------
数据库安全性
问题的提出？
数据库的一大特点是数据可以共享
数据共享必然带来数据库的安全性问题
数据库系统中的数据共享不能是无条件的共享
例： 军事秘密、国家机密、新产品实验数据、
        市场需求分析、市场营销策略、销售计划、
        客户档案、医疗档案、银行储蓄数据
 
 
数据库的安全性是指保护数据库以防止不合法使用造成的数据泄露，更改或者破坏。
系统安全保护措施是否有效是数据库系统主要的性能指标之一。
 
1、非授权用户队数据库的恶意存取和破坏
一些黑客和犯罪分子在用户存取数据库时猎取用户名和用户口令，然后假冒合法用户偷取，修改甚至破坏用户数据。
数据库管理系统提供的安全措施主要包括用户身份鉴别，存取控制和视图等技术。
 
 
2、数据库中重要或敏感的数据被泄露
黑客和敌对分子千方百计盗窃数据库中的重要数据，一些机密信息被暴露。
数据库管理系统提供的主要技术有强制存取控制、数据加密存储和加密传输等。
审计日志分析
 
3.安全环境的脆弱性
数据库的安全性与计算机系统的安全性紧密联系
计算机硬件、操作系统、网络系统等的安全性
建立一套可信（Trusted）计算机系统的概念和标准
 
 
数据库安全性控制的常用方法
    用户标识和鉴定
    存取控制
    视图
    审计
    数据加密
 
 
用户身份鉴别
  （Identification &  Authentication）
系统提供的最外层安全保护措施
用户标识：由用户名和用户标识号组成
  （用户标识号在系统整个生命周期内唯一）
 
 
用户身份鉴别的方法
1.静态口令鉴别
    静态口令一般由用户自己设定，这些口令是静态不变的
2.动态口令鉴别
    口令是动态变化的，每次鉴别时均需使用动态产生的新口令登录数据库管理系统，即采用一次一密的方法
3.生物特征鉴别
    通过生物特征进行认证的技术，生物特征如指纹、虹膜和掌纹等
4.智能卡鉴别
    智能卡是一种不可复制的硬件，内置集成电路的芯片，具有硬件加密功能
 
 
 
关系数据库系统中存取控制对象
 
对象类型	    对象	        操 作 类 型
 
数据库模式	    模式	        CREATE SCHEMA
                基本表	        CREATE TABLE，ALTER TABLE
                视图	        CREATE VIEW
                索引	        CREATE INDEX
数据	        基本表和视图	SELECT，INSERT，UPDATE，DELETE，REFERENCES，ALL PRIVILEGES
                属性列	        SELECT，INSERT，UPDATE， REFERENCES，all privileges
 
 
----------------- 授权与回收 -----------------
授权：授权与回收
1、grant
grant 语句的一般格式：
    grant <权限>[,<权限>]... 
    on <对象类型> <对象名>[,<对象类型> <对象名>]…
    to <用户>[,<用户>]...
    [with grant option]
 
语义：将对指定操作对象的指定操作权限授予指定的用户
 
 
发出GRANT：
    数据库管理员
    数据库对象创建者（即属主Owner）
    拥有该权限的用户
按受权限的用户 
    一个或多个具体用户
    PUBLIC（即全体用户）  
 
WITH GRANT OPTION子句:
    指定：可以再授予
    没有指定：不能传播
 
不允许循环授权: U1 -> U2 -> U3 -> U4     U4 -> U1 U4不允许在授权给u1  
 
[例4.1] 把查询Student表权限授给用户U1
grant select 
on table Student
to U1;
 
[例4.2]把对Student表和Course表的全部权限授予用户U2和U3
grant all privileges
on table Student,Course
to U2,U3;
 
[例4.3] 把对表SC的查询权限授予所有用户
grant select 
on table sc 
to public;
 
[例4.4]把查询Student表和修改学生学号的权限授权给用户U4
grant select, update(Sno)
on table Student
to U4;
备注： 
    对属性列的授权时必须明确指出相应属性列名
 
[例4.5] 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户
grant  insert
on table SC
to U5
with grant option;
 
执行例4.5后，U5不仅拥有了对表SC的INSERT权限，
 还可以传播此权限：
 
[例4.6] U5将权限授予U6
grant insert
on table SC 
to U6
with grant option;
同样，u6还可以将次权限授予u7;
[例4.7] U6将权限授予U7
grant insert 
on table SC
to U7;
但U7不能再传播此权限。
 
执行了例4.1~例4.7语句后学生-课程数据库中的用户权限定义表:
 
授权用户名	被授权用户名	数据库对象名	    允许的操作类型	能否转授权
DBA	        U1	            关系Student	            SELECT	        不能
DBA	        U2	            关系Student	            ALL	            不能
DBA	        U2	            关系Course	            ALL	            不能
DBA	        U3	            关系Student	            ALL	            不能
DBA	        U3	            关系Course	            ALL	            不能
DBA	        PUBLIC	        关系SC	                SELECT	        不能
DBA	        U4	            关系Student	            SELECT	        不能
DBA	        U4	            属性列Student.Sno       UPDATE	        不能
DBA	        U5	            关系SC	                INSERT	        能
U5	        U6	            关系SC	                INSERT	        能
U6	        U7	            关系SC	                INSERT	        不能
 
 
 
2、revoke
授予的权限可以有数据库管理员或其他授权者用revoke语句收回
revoke语句的一般格式为：
REVOKE <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型><对象名>]…
FROM <用户>[,<用户>]...[CASCADE | RESTRICT];
 
[例4.8] 把用户U4修改学生学号的权限收回
revoke update(Sno)
on table Student
from U4;
 
[例4.9] 收回所有用户对表SC的查询权限
revoke select
on table sc
from public;
 
[例4.10] 把用户U5对SC表的INSERT权限收回
revoke insert
on table sc
from U5 cascade;
 
将用户U5的insert权限收回的时候应该使用cascade，否则拒绝执行该语句
如果U6或U7还从其他用户处获得对SC表的insert权限，则他们仍具有此权限，系统只收回直接或间接从U5处获得的权限。
 
执行例4.8~4.10语句后学生-课程数据库中的用户权限定义表
 
授权用户名	被授权用户名	数据库对象名	    允许的操作类型	能否转授权
DBA	        U1	            关系Student	            SELECT	        不能
DBA	        U2	            关系Student	            ALL	            不能
DBA	        U2	            关系Course	            ALL	            不能
DBA	        U3	            关系Student	            ALL	            不能
DBA	        U3	            关系Course	            ALL	            不能
DBA	        U4	            关系Student	            SELECT	        不能
 
数据库管理员：
    拥有所有对象的所有权限
    根据实际情况不同的权限授予不同的用户
 
用户：
    拥有自己建立的对象的全部的操作权限
    可以使用grant，把权限授予其他用户
 
被授权的用户
    如果具有“继续授权”的许可，可以把获得的权限再授予其他用户
 
所有授予出去的权力在必要时又都可用REVOKE语句收回
 
3、创建数据库模式的权限
数据库管理员在创建用户时实现
create user 语句格式:
    CREATE USER <USERNAME>
    [WITH][DBA|RESOURCE|CONNECT];
备注：
    CREATE USER不是SQL标准，各个系统的实现相差甚远
 
CREATE USER语句格式说明
    只有系统的超级用户才有权创建一个新的数据库用户
    新创建的数据库用户有三种权限：CONNECT、RESOURCE和DBA
    如没有指定创建的新用户的权限，默认该用户拥有CONNECT权限。拥有CONNECT权限的用户不能创建新用户，不能创建模式，也不能创建基本表，只能登录数据库
 
    拥有RESOURCE权限的用户能创建基本表和视图，成为所创建对象的属主。但不能创建模式，不能创建新的用户
    拥有DBA权限的用户是系统中的超级用户，可以创建新的用户、创建模式、创建基本表和视图等；DBA拥有对所有数据库对象的存取权限，还可以把这些权限授予一般用户
 
 
拥有的权限	      可否执行的操作
	        CREATE USER	    CREATE SCHEMA    CREATE TABLE	登录数据库 ，执行数据查询和操纵
DBA	        可以	        可以	            可以	        可以
RESOURCE	不可以	        不可以	            不可以	        不可以
CONNECT	    不可以	        不可以	            不可以	        可以，但必须拥有相应权限
 
4.2.5数据库角色
数据库角色：被命名的一组与数据库操作相关的权限
    角色是权限的集合
    可以为一组具有相同权限的用户创建一个角色
    简化授权的过程
 
1、角色的创建
    create role <角色名>
 
2、给角色授权
grant  <权限>[,<权限>]… 
on <对象类型>对象名  
to <角色>[,<角色>]…
 
3、将一个角色授予其他的角色或用户
grant <角色1>[,<角色2>]...
to <角色3>[,<用户1>]...
[with admin option]
 
该语句把角色授予某用户，或授予另一个角色
授予者是角色的创建者或拥有在这个角色上的ADMIN OPTION
指定了WITH ADMIN OPTION则获得某种权限的角色或用户还可以把这种权限授予其他角色
一个角色的权限：直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限
 
4.角色权限的收回 
revoke <权限>[,<权限>]…
on <对象类型> <对象名>
from <角色>[,<角色>]…
----或者 revoke <权限> from <角色>
用户可以回收角色的权限，从而修改角色拥有的权限
REVOKE执行者是角色的创建者
拥有在这个（些）角色上的ADMIN OPTION
 
 
[例4.11] 通过角色来实现将一组权限授予一个用户。
步骤如下：
(1) 首先创建一个角色R1
create role R1;
(2) 然后使用grant语句，使角色R1拥有Student表的select、update、insert权限
grant select,update,insert
on table Student
to R1;
 
(3) 将这个角色授予王平，张明，赵玲。使他们具有角色R1所包含的全部权限
grant R1
to 王平，张明，赵玲;
 
(4) 可以一次性通过R1来回收王平的这3个权限
revoke R1
from 王平;
 
[例4.12] 角色的权限修改
grant delete
on table student
to R1;
使用角色R1在原来的基础上增加了student表的delete权限
 
[例4.13]
revoke SELECT
on table Student 
from R1;
使R1减少了select权限
 
自主存取空值缺点：
    可能存在数据的“无意泄露”
原因：这种机制仅仅通过对数据的存取权限来进行安全控制，而数据本身并无安全性标记
    解决：对系统控制下的所有主客体实施强制存取控制策略
 
强制存取控制（MAC）
    保证更高程度的安全性
    用户不能直接感知或进行控制
    适用于对数据有严格而固定密级分类的部门
        军事部门
        政府部门
 
 
在强制存取控制中，数据库管理系统所管理的全部实体被分为主体和客体两大类
主体是系统中的活动实体
    数据库管理系统所管理的实际用户
    代表用户的各进程
 
客体是系统中的被动实体，受主体操纵
    文件、基本表、索引、视图
 
敏感度标记（Label）
 对于主体和客体，DBMS为它们每个实例（值）指派一个敏感度标记（Label）
 敏感度标记分成若干级别
    绝密（Top Secret，TS）
    机密（Secret，S）
    可信（Confidential，C）
    公开（Public，P）
    TS>=S>=C>=P
主体的敏感度标记称为许可证级别（Clearance Level）
客体的敏感度标记称为密级（Classification Level）
 
 强制存取控制规则
 （1）仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体
 （2）仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体
 
强制存取控制（MAC）是对数据本身进行密级标记，无论数据如何复制，标记与数据是一个不可分的整体，只有符合密级标记要求的用户才可以操纵数据。
实现强制存取控制时要首先实现自主存取控制
    原因：较高安全性级别提供的安全保护要包含较低级别的所有保护
自主存取控制与强制存取控制共同构成数据库管理系统的安全机制
 
 
 
[例4.14] 建立计算机系学生的视图，把对该视图的SELECT权限授于王平，把该视图上的所有操作权限授于张明 
先建立计算机系学生的视图CS_Student
create view CS_Student
AS
select *
from student
where sdept='cs';
再视图上进一步定义存取权限
grant select 
on CS_Student
to 王平;
 
grant all privileges
on CS_Student
to 张明;
 
 
--------------------------   审计  -------------------------
什么是审计：
启用一个专用的审计日志（Audit Log）
   将用户对数据库的所有操作记录在上面
审计员利用审计日志
    监控数据库中的各种行为，找出非法存取数据的人、时间和内容
 
C2以上安全级别的DBMS必须具有审计功能
 
审计功能的可选性
    审计很费时间和空间
    DBA可以根据应用对安全性的要求，灵活地打开或关闭审计功能
    审计功能主要用于安全性要求较高的部门
 
1.审计事件
服务器事件
    审计数据库服务器发生的事件  
系统权限
    对系统拥有的结构或模式对象进行操作的审计
    要求该操作的权限是通过系统权限获得的
语句事件
    对SQL语句，如DDL、DML、DQL及DCL语句的审计
模式对象事件
    对特定模式对象上进行的SELECT或DML操作的审计 
 
2.审计功能
基本功能
    提供多种审计查阅方式提供多种审计查阅方式
多套审计规则：一般在初始化设定
提供审计分析和报表功能
审计日志管理功能
    防止审计员误删审计记录，审计日志必须先转储后删除
    对转储的审计记录文件提供完整性和保密性保护
    只允许审计员查阅和转储审计记录，不允许任何用户新增和修改审计记录等
提供查询审计设置及审计记录信息的专门视图
 
 
3. AUDIT语句和NOAUDIT语句
    AUDIT语句：设置审计功能 
    NOAUDIT语句：取消审计功能 
 
用户级审计
    任何用户可设置的审计
    主要是用户针对自己创建的数据库表和视图进行审计
 
系统级审计
    只能由数据库管理员设置
    监测成功或失败的登录要求、监测授权和收回操作以及其他数据库级权限下的操作
 
 
[例4.15] 对修改SC表结构或修改SC表数据的操作进行审计
audit alter,update 
on SC;
 
[例4.16] 取消对SC表的一切审计
noaudit alter,update
on SC;
 
 
-------------------------------  数据加密  --------------------------------
数据加密
    房子数据库中数据在存储和传输中失密的有效手段
 
加密的基本思想
    根据一定的算法将原始数据——明文（plain text）变换为不可直接识别的格式——密文（cipher text）
    
加密方法
    存储加密
    传输加密
 
---存储加密
透明存储加密：
    内核级加密保护方式，对用户完全透明
    将数据在写到磁盘时对数据进行加密，授权用户读取数据时再对其进行解密
    数据库的应用程序不需要做任何修改，只需在创建表语句中说明需加密的字段即可
内核级加密方法: 性能较好，安全完备性较高
非透明存储加密：
    通过多个加密函数实现
 
 
--传输加密
链路加密
    在链路层进行加密
    传输信息由报头和报文两部分组成
    报文和报头均加密
端到端加密
    在发送端加密，接收端解密
    只加密报文不加密报头
    所需密码设备数量相对较少，容易被非法监听者发现并从中获取敏感信息
 
                第一步：创建可信连接
                第二步：确认通信双方端点的可靠性
 用户  《—————— 第三步：协商加密算法和密钥   ——————》     数据库服务
                第四步：可信传输数据
                第五步：关闭可信连接
 
 
基于安全套接层协议SSL传输方案的实现思路：
（1）确认通信双方端点的可靠性
    采用基于数字证书的服务器和客户端认证方式
    通信时均首先向对方提供己方证书，然后使用本地的CA 信任列表和证书撤销列表对接收到的对方证书进行验证
（2）协商加密算法和密钥
    确认双方端点的可靠性后，通信双方协商本次会话的加密算法与密钥
（3）可信数据传输
    业务数据在被发送之前将被用某一组特定的密钥进行加密和消息摘要计算，以密文形式在网络上传输
    当业务数据被接收的时候，需用相同一组特定的密钥进行解密和摘要计算
 
 
实现数据库系统安全性的技术和方法：
    用户身份鉴别
    存取控制技术：自主存取控制和强制存取控制
    视图技术
    审计技术
    数据加密存储和加密传输
 
 
 
--------------------------  过程化SQL的块结构  -----------------------
 
过程化SQL块的基本结构
1. 定义部分
       	DECLARE 变量、常量、游标、异常等   
定义的变量、常量等只能在该基本块中使用
当基本块执行结束时，定义就不再存在
 
过程化SQL块的基本结构（续）
2. 执行部分
BEGIN
    SQL语句、过程化SQL的流程控制语句
    EXCEPTION
        异常处理部分        		
END；
 
 
变量和常量的定义
1. 变量定义
    变量名 数据类型 [[NOT NULL]:=初值表达式]或
    变量名 数据类型 [[NOT NULL] 初值表达式]
2. 常量定义
    常量名 数据类型 CONSTANT :=常量表达式
    常量必须要给一个值，并且该值在存在期间或常量的作用域内不能改变。如果试图修改它，过程化SQL将返回一个异常
3. 赋值语句
    变量名称 :=表达式
 
 
--------------------------流程控制---------------------
1. 条件控制语句
    IF-THEN，IF-THEN-ELSE和嵌套的IF语句 
（1）IF condition THEN
	       Sequence_of_statements;        
	  END IF;   
（2）IF condition THEN
	       Sequence_of_statements1;  
	  ELSE
	       Sequence_of_statements2;  
     END IF;
（3）在THEN和ELSE子句中还可以再包含IF语句，即IF语句可以嵌套 
 
2. 循环控制语句
    LOOP，WHILE-LOOP和FOR-LOOP 
（1）简单的循环语句LOOP
    LOOP
        Sequence_of_statements;        
    END LOOP; 
     多数数据库服务器的过程化SQL都提供EXIT、BREAK或
     LEAVE等循环结束语句，保证LOOP语句块能够结束
（2）WHILE-LOOP
    WHILE condition LOOP
        Sequence_of_statements;
    END LOOP;
    每次执行循环体语句之前，首先对条件进行求值
    如果条件为真，则执行循环体内的语句序列
    如果条件为假，则跳过循环并把控制传递给下一个语句 
（3）FOR-LOOP
    FOR count IN [REVERSE] bound1 … bound2 LOOP
        Sequence_of_statements;
    END LOOP;
 
3. 错误处理
如果过程化SQL在执行时出现异常，则应该让程序在产生异常的语句处停下来，根据异常的类型去执行异常处理语句 
SQL标准对数据库服务器提供什么样的异常处理做出了建议，要求过程化SQL管理器提供完善的异常处理机制 
 
------------------------------  存储过程  -----------------------------------
 
存储过程：
    由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。
存储过程的优点
（1）运行效率高
（2）降低了客户机和服务器之间的通信量	
（3）方便实施企业规则
 
存储过程的用户接口
（1）创建存储过程 
（2）执行存储过程 
（3）修改存储过程
（4）删除存储过程  
 
（1）创建存储过程
	CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；
过程名：数据库服务器合法的对象标识
参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。
参数也可以定义输入参数、输出参数或输入/输出参数，默认为输入参数
过程体：是一个<过程化SQL块>，包括声明部分和可执行语句部分 
 
 
[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。
 
CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount  INT,amount FLOAT) 
       /*定义存储过程TRANSFER，其参数为转入账户、转出账户、转账额度*/
AS DECLARE		/*定义变量*/
    totalDepositOut Float;
    totalDepositIn Float;
    inAccountnum INT;
BEGIN                         	    /*检查转出账户的余额 */	                       
    SELECT Total INTO totalDepositOut FROM Accout 
    WHERE accountnum=outAccount; 
    IF totalDepositOut IS NULL THEN   
                                    /*如果转出账户不存在或账户中没有存款*/
            ROLLBACK; 	   /*回滚事务*/
            RETURN;
    END IF; 
    IF totalDeposit Out< amount THEN    	/*如果账户存款不足*/
        ROLLBACK; 				/*回滚事务*/
        RETURN;
    END IF; 
    SELECT Accountnum INTO inAccountnum FROM Account
    WHERE accountnum=inAccount;
    IF inAccount IS NULL THEN  		/*如果转入账户不存在*/                        
        ROLLBACK; 	         	 		/*回滚事务*/
        RETURN;
    END IF;
 
    UPDATE Account SET total=total-amount 
    WHERE accountnum=outAccount;
                    /* 修改转出账户余额，减去转出额 */
    UPDATE Account SET total=total + amount 
    WHERE   accountnum=inAccount; 
                    /* 修改转入账户余额，增加转入额 */
    COMMIT;                       	/* 提交转账事务 */
END;
 
 
（2）执行存储过程
    CALL/PERFORM  PROCEDURE 过程名([参数1,参数2,...])；
    使用CALL或者PERFORM等方式激活存储过程的执行
    在过程化SQL中，数据库服务器支持在过程体中调用其他存储过程
 
[例8.9] 从账户01003815868转10000元到01003813828账户中。
    CALL PROCEDURE TRANSFER(01003813828,01003815868,10000);
 
 
（3） 修改存储过程
alter procedure 过程名1 rename to 过程名2;
 
（4）删除存储过程
drop procedure 过程名();
 
 
----------------------------   函数   -----------------------
函数和存储过程的异同
    同：都是持久性存储模块
    异：函数必须指定返回的类型
 
1. 函数的定义语句格式
    CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型>  AS <过程化SQL块>;
2. 函数的执行语句格式
    CALL/SELECT 函数名 ([参数1,参数2,…]);
3. 修改函数
    重命名
        ALTER FUNCTION 过程名1 RENAME TO 过程名2;
    重新编译
        ALTER FUNCTION 过程名 COMPILE;
 
 
ODBC优点
    移植性好
    能同时访问不同的数据库
    共享多个数据资源  
    
ODBC产生的原因
由于不同的数据库管理系统的存在，在某个关系数据库管理系统下编写的应用程序就不能在另一个关系数据库管理系统下运行  
许多应用程序需要共享多个部门的数据资源，访问不同的关系数据库管理系统