数据库复习01：基础篇

作者：非妃是公主
专栏：《数据库》
个性签：顺境不惰，逆境不馁，以心制境，万事可成。——曾国藩

专栏系列文章

数据库复习01：基础篇

数据库复习02：设计与应用篇

数据库复习03：系统篇

数据库复习04：课后习题

SQL Server Studio查看“估计的执行计划”和“实际的执行计划方法”

SQL Server中C/C++数据类型与ODBC c类型对照（将C/C++数据对象存到SQL Server数据库中）

复习大纲

一、关系代数

1. 基本概念

1、域：属性的取值范围。

2、主码（Key）：如果一个表中有多个候选码，可以选定其中一个作为主码。

3、分量：元组中的一个属性值。

4、关系模式：对关系的描述
关系名（属性1，属性2，…，属性n）
学生（学号，姓名，年龄，性别，系，年级）

5、

关系术语	一般表格的术语
关系名	表名
关系模式	表头（表格的描述）
关系	（一张）二维表
元组	记录或行
属性	列
属性名	列名
属性值	列值
分量	一条记录中的一个列值
非规范关系	表中有表（大表中嵌有小表）

6、笛卡尔积，域上面的一种集合运算。给定一组域D1，D2，…，Dn，这些域中可以有相同的。D1，D2，…，Dn的笛卡尔积为：
D1×D2×…×Dn ＝ ｛（d1，d2，…，dn）｜di$\in$Di，i＝1，2，…，n｝
所有域的所有取值的一个组合，不能重复

7、关系
定义2.3: D1×D2×…×Dn的子集叫作在域D1，D2，…，Dn上的关系，表示为：

R（D1，D2，…，Dn）

R：关系名

n：关系的目或度（Degree）

关系是笛卡尔积的有限子集。无限关系在数据库系统中是无意义的。

8、元组：关系中的每个元素是关系中的元组，通常用t表示。（tupple）

9、属性：关系中不同列可以对应相同的域
为了加以区分，必须对每列起一个名字，称为属性（Attribute）
n目关系必有n个属性

10、码
候选码（Candidate key）
若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码。
简单的情况：候选码只包含一个属性。
全码（All-key）
最极端的情况：关系模式的所有属性组是这个关系模式的候选码，称为全码（All-key）
只包含一个属性的情况。

主码
若一个关系有多个候选码，则选定其中一个为主码（Primary key）
主属性
候选码的诸属性称为主属性（Prime attribute）
不包含在任何侯选码中的属性称为非主属性（Non-Prime attribute）或非码属性（Non-key attribute）

11、关系模式可以形式化地表示为：
R（U，D，DOM，F）

	R         关系名
	U         组成该关系的属性名集合
	D         属性组U中属性所来自的域
	DOM  	  属性向域的映象集合
	F         属性间的数据依赖关系集合

2. 选择

1. 选择又称为限制（Restriction）

2. 选择运算符的含义
   在关系R中选择满足给定条件的诸元组
   $\sigma F(R)$ = {t|t$\in$R∧F(t)= ‘真’}
   F：选择条件，是一个逻辑表达式，基本形式为：
   X1θY1

3. 投影

[例3] 查询学生的姓名和所在系
即求Student关系上学生姓名和所在系两个属性上的投影
${\pi }_{Sname，Sdept}(Student)$或${\pi }_{2，5}(Student)$

4. 连接

Ⅰ. 等值连接

Ⅱ. 自然连接

把等值连接中相同的属性列去掉。

Ⅲ. 外连接

如果把舍弃的元组也保存在结果关系中，而在其他属性上填空值(Null)，这种连接就叫做外连接（OUTER JOIN）。

Ⅳ. 左外连接

如果只把左边关系R中要舍弃的元组保留就叫做左外连接(LEFT OUTER JOIN或LEFT JOIN)。

Ⅴ. 右外连接

如果只把右边关系S中要舍弃的元组保留就叫做右外连接(RIGHT OUTER JOIN或RIGHT JOIN)。

5. 除

二、SQL语句

1. 单表查询

select name
from instructor
where dept_name = 'Biology';

-- order by
[例24]	查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
SELECT Sno，Grade
FROM  SC
WHERE  Cno= ' 3 '
ORDER BY Grade DESC；

-- 聚集函数：
计数
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
计算总和
SUM（[DISTINCT|ALL] <列名>）	
 计算平均值
AVG（[DISTINCT|ALL] <列名>）
最大最小值
	  MAX（[DISTINCT|ALL] <列名>）
	  MIN（[DISTINCT|ALL] <列名>）
[例26]	查询学生总人数。
    SELECT COUNT(*)
    FROM  Student； 
[例27]	查询选修了课程的学生人数。
 	SELECT COUNT(DISTINCT Sno)
	FROM SC；
[例28]	计算1号课程的学生平均成绩。
	SELECT AVG(Grade)
	FROM SC
   	WHERE Cno= ' 1 '；

-- group by
[例31]  求各个课程号及相应的选课人数。
     SELECT Cno，COUNT(Sno)
     FROM    SC
     GROUP BY Cno； 

HAVING短语与WHERE子句的区别：
作用对象不同。
WHERE子句作用于基表或视图，从中选择满足条件的元组。
HAVING短语作用于组，从中选择满足条件的组。 

[例32]  查询选修了3门以上课程的学生学号。
      分析：先分组，再加选择条件。    
    SELECT Sno
     FROM  SC
     GROUP BY Sno
     HAVING  COUNT(*) >3；       

2. 连接查询

select ID,takes.course_id,title
from dbo.course,
     dbo.takes
where dbo.course.course_id = dbo.takes.course_id
  and dbo.takes.ID = '10527';
  
-- 8.As above, but display the names of the instructors also, not just the IDs. 
select distinct dbo.instructor.ID, dbo.instructor.name
from dbo.instructor
         left join dbo.teaches
                   on dbo.instructor.ID = dbo.teaches.ID
                       and dbo.teaches.ID = NULL;

-- 7.Display the IDs of all instructors who have never taught a couse (Notesad1); 
--   ( interpret "taught" as "taught or is scheduled to teach") 
select distinct dbo.instructor.ID
from dbo.instructor
         left join dbo.teaches
                   on dbo.instructor.ID = dbo.teaches.ID
                       where dbo.teaches.ID is NULL;

-- 8.As above, but display the names of the instructors also, not just the IDs. 
select distinct dbo.instructor.ID, dbo.instructor.name
from dbo.instructor
         left join dbo.teaches
                   on dbo.instructor.ID = dbo.teaches.ID
                       where dbo.teaches.ID is NULL;

3. 嵌套查询

-- 1.Find the maximum and minimum enrollment across all sections, 
--   considering only sections that had some enrollment, 
--   don't worry about those that had no students taking that section 
create view sec_enro as
(
select takes.course_id,
       takes.semester,
       takes.year,
       takes.sec_id,
       count(takes.semester) '选课人数'
from takes
group by takes.course_id,
         takes.semester,
         takes.year,
         takes.sec_id);

select max(选课人数) max, min(选课人数) min
from sec_enro;

-- 嵌套形式
select max(选课人数) max, min(选课人数) min
from (select takes.course_id,
             takes.semester,
             takes.year,
             takes.sec_id,
             count(takes.semester) '选课人数'
      from takes
      group by takes.course_id,
               takes.semester,
               takes.year,
               takes.sec_id) A;

-- 带有ANY（SOME）或ALL谓词的子查询 （续）
-- [例43]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
-- 方法一：用ANY谓词
SELECT Sname，Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
                  FROM Student
                  WHERE Sdept = ' CS ')
  AND Sdept <> 'CS';
  
-- 用聚集函数实现[例42]
SELECT Sname，Sage
FROM Student
WHERE Sage <
      (SELECT MAX(Sage)
       FROM Student
       WHERE Sdept = 'CS')
                    AND Sdept <> 'CS';

-- 带有ANY（SOME）或ALL谓词的子查询 （续）
-- [例43]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

-- 方法一：用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的嵌套形式
-- 6.Now delete all the newly added "students" above 
--   (note: already existing students who happened to
--   have tot_creds = 0 should not get deleted)

delete
from dbo.student
where exists(select *
             from dbo.instructor
             where student.ID = instructor.ID
               and student.name = instructor.name
               and student.dept_name = instructor.dept_name);

4. 集合查询

-- 并操作UNION
-- [例48]  查询计算机科学系的学生及年龄不大于19岁的学生。
-- 方法一：
        SELECT *
        FROM Student
        WHERE Sdept= 'CS'
        UNION
        SELECT *
        FROM Student
        WHERE Sage<=19；
-- UNION：将多个查询结果合并起来时，系统自动去掉重复元组。
-- UNION ALL：将多个查询结果合并起来时，保留重复元组 

-- 方法二：
        SELECT  DISTINCT  *
        FROM Student
        WHERE Sdept= 'CS'  OR  Sage<=19；

-- 交操作INTERSECT
-- [例50] 查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19 

SELECT *
    FROM Student
    WHERE Sdept= 'CS' AND  Sage<=19；


-- 差操作EXCEPT
-- [例52]  查询计算机科学系的学生与年龄不大于19岁的学生的差集。

SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT  *
FROM Student
WHERE Sage <=19;

-- [例52]实际上是查询计算机科学系中年龄大于19岁的学生

SELECT *
FROM Student
WHERE Sdept= 'CS' AND  Sage>19;

-- Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.
select ID,grade
from takes
where grade='F'
group by ID,grade
having count(grade)>=2

5. SQL语句一般形式

 SELECT 	[ALL|DISTINCT]  
<目标列表达式> [别名] [ ，<目标列表达式> [名]] …
 FROM     	<表名或视图名> [别名] 
          	[ ，<表名或视图名> [别名]] …
 [WHERE 	<条件表达式>]
 [GROUP BY  <列名1>
 [HAVING 	<条件表达式>]]
 [ORDER BY 	<列名2> [ASC|DESC] 

三、安全性

为防止因用户非法使用而造成的数据泄密、更改或破坏。
数据库安全的两个层面：①防止非法用户的数据访问，②防止合法用户的非法数据访问
数据库的产生主要是出于数据共享的目的，但共享与安全是一对矛盾（平衡点）

D级
最低级别。不符合更高标准的统统归于D组。DOS：具有操作系统的基本功能，如文件系统、进程调度等，但安全方面几乎没有保障。
C1级
只提供了非常初级的自主安全保护。能够实现对用户和数据的分离，进行自主存取控制（DAC），保护或限制用户权限的传播。现有的商业系统往往稍作改进即可满足要求。（用户数据分离，存取权限的分配）
C2级
安全产品的最低档次，提供受控的存取保护，即将C1级的DAC进一步细化，以个人身份注册负责，并实施审计和资源隔离。
Oracle公司 Oracle7 ，Sybase公司 SQL Server11.0.6
B1级
标记安全保护。对系统的数据加以标记，并对标记的主体和客体实施强制存取控制（MAC）以及审计等安全机制。
B1级能够较好的满足大型企业或一般政府部门对于数据的安全需求，这一级别的产品才认为是真正意义上的安全产品。满足此级别的产品前多冠以“安全”“可信”字样。
Trusted Oracle 7,　Secure SQL Server 11.0.6
B2
结构化保护。建立形式化的安全策略模型并对系统内的所有主体和客体实施DAC和MAC。
经过认证的，B2级以上的安全系统非常稀少。
Trusted Information Systems公司的Trusted XENIX
数据库产品没有。
B3
安全域。满足访问控制器的要求，审计跟踪能力更强，提供系统恢复过程。
A1
验证设计，即提供B3保护的同时给出系统的形式化设计说明和验证，以确信各安全保护真正实现。

总结：

D	基本功能，在安全性方面几乎没有保障机制
C1	实施自主存取控制（DAC），基本实现用户和数据分离
C2	以个人身份注册负责，并增加审计功能
B1	实施强制存取控制（MAC），真正意义上的安全产品

1. 用户标识与鉴别

2. 存取控制

Ⅰ. 自主存取控制（Discretionary Access Control ，简称DAC）

用户通过grant，revoke来定义用户可以在哪些数据库的哪些数据库对象上进行哪些操作。

C2级
灵活

-- 创建登陆帐户（create login）
create
login U5 with password='123456', default_database=test_db;
-- 为登陆账户创建数据库用户（create user）,
-- 在mydb数据库中的security中的user下可以找到新创建的U1
create user U5 for login U5 with default_schema=dbo;
-- [例5]把对表SC的INSERT权限授予U5用户，
-- 并允许他再将此权限授予其他用户
GRANT INSERT
    ON takes
    TO U5
    WITH GRANT OPTION;

Ⅱ. 数据库角色

数据库角色：被命名的一组与数据库操作相关的权限
角色是权限的集合
可以为一组具有相同权限的用户创建一个角色
简化授权的过程

-- 角色创建
-- 1. 首先创建一个角色 R1
CREATE ROLE R1;
drop role R1;
-- 2. 然后使用GRANT语句，
-- 使角色R1拥有Student表的SELECT、UPDATE、INSERT权限
GRANT SELECT, UPDATE, INSERT
    ON student
    TO R1;
-- 3. 将这个角色授予王平，张明，赵玲。使他们具有角色R1所包含的全部权限
ALTER
ROLE R1 ADD MEMBER U1;
-- 4. 可以一次性通过R1来回收王平的这3个权限
ALTER
ROLE R1 drop
MEMBER U1;

drop
R1;

Ⅲ. 强制存取控制（Mandatory Access Control，简称 MAC）

强制存取控制规则：
① 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体（高安全级别的人才能访问相关的数据）
② 仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体（高的话，只能看不能写）
修正规则（２）：
① 用户可以为写入的数据对象赋予>=自己的许可证级别的密级。
② 如果违反，就有可能将数据密级从高流向低，造成数据泄露。

B1级
严格

3. 视图机制

建立仅可以满足用户工作需要的视图，用户只有查阅这个视图的权限

[例14]建立计算机系学生的视图，把对该视图的SELECT权限授于王平，把该视图上的所有操作权限授于张明
先建立计算机系学生的视图CS_Student

 CREATE VIEW CS_Student
 AS 
 SELECT  *
 FROM   Student
 WHERE  Sdept='CS';

-- 在视图上进一步定义存取权限
     GRANT  SELECT
     ON  CS_Student  
     TO 王平；
     
     GRANT ALL PRIVILIGES
     ON  CS_Student  
     TO  张明；

4. 审计

其实就是日志，每执行一项操作后，要把时间、用户、操作等写进日志文件进行记录，方便后续查阅

5. 数据加密

数据加密：防止数据库中数据在存储和传输中失密的有效手段

加密的基本思想：根据一定的算法将原始数据变换为不可识别的格式。

加密方法：

① 替换方法：密钥将明文中每一个字符转换为密文中的一个字符。
② 置换方法：将明文中的字符重新排列。
③ 混合方法：更高的安全性。ＤＥＳ

6. 统计数据库安全性

允许用户查询聚集类型的信息（如合计、平均值等）

• 程序员的平均工资是多少？
  不允许查询单个记录信息

规则1：任何查询至少要涉及N(N足够大)个以上的记录
规则2：任意两个查询的相交数据项不能超过M个
规则3：任一用户的查询次数不能超过1+(N-2)/M

数据库安全机制的设计目标：试图破坏安全的人所花费的代价 >> 得到的利益

四、完整性

1. 实体完整性

若属性A是基本关系R的主属性，则属性A不能取空值
实体完整性规则规定基本关系的所有主属性都不能取空值。
例：
选修（学号，课程号，成绩）
问题：学号、课程号如何取值？

“学号、课程号”为主码，则两个属性都不能取空值。

2. 参照完整性

关系间的引用：

学生（学号，姓名，性别，专业号，年龄） 参照关系

专业（专业号，专业名） 被参照关系

学生关系中每个元组的“专业号”属性只取两类值：
（1）空值，表示尚未给该学生分配专业
（2）非空值，这时该值必须是专业关系中某个元组的“专业号”值，表示该学生不可能分配一个不存在的专业

3. 用户自定义完整性

针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求
关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理它们，而不要由应用程序承担这一功能

[例：]课程(课程号，课程名，学分)

“课程号”属性必须取唯一值
非主属性“课程名”也不能取空值
“学分”属性只能取值{1，2，3，4}