精华内容
下载资源
问答
  • 虚拟列:定义视图时根据应用的需要设置一些派生属性列,这些派生属性由于在基本表中并不实际存在,成为虚拟列。 【例3.88】定义一个反应学生出生年份的视图。 create view BT_S(Sno,Sname,Sbirth) as select Sno,...

    3.4.4 集合查询

    三种集合操作:
    并-union
    交-intersect
    差-except
    【注】参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同。
    【例3.64】查询计算机科学系的学生及年龄不大于19岁的学生。(并)

    select *
    from Student
    where Sdept='CS'--选择CS系的学生
    union
    select *
    from Student
    where Sage<=19;--选择年龄不大于19的学生
    

    在这里插入图片描述
    重复元组自动去重,要保留则用union all操作符。
    【例3.65】查询选修了课程1或者选修了课程2的学生集合的并集

    select Sno
    from SC
    where Cno=1--选课程1的学生
    union
    select Sno
    from SC
    where Cno=2;--选课程2的学生
    

    在这里插入图片描述
    【例3.66】查询计算机科学系的学生与年龄不大于19岁的学生的交集。
    实际上是查询CS系中年龄小于19的学生。

    select *
    from Student
    where Sdept='CS'
    intersect
    select *
    from Student
    where Sage<=19;
    
    select *
    from Student
    where Sdept='CS' and Sage<=19;
    

    在这里插入图片描述
    【例3.67】查询既选修了课程1又选修了课程2 的学生。(交集)

    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岁的学生的差集。

    select *  --计算机科学系的学生与年龄不大于19岁的学生的差集
    from Student
    where Sdept='CS'
    except
    select *
    from Student
    where Sage<=19;
    select *    --查询计算机科学系年龄大于19岁的学生
    from Student
    where Sdept='CS'and Sage>19;
    

    在这里插入图片描述

    3.4.5 基于派生表的查询

    子查询出现在from子句中,这时子查询生成的临时派生表称为主查询的查询对象。
    例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;
    

    在这里插入图片描述
    如果子查询中没有聚集函数,派生表可以不指定属性列,列名为其默认属性。
    例3.60查询所有选修了1号课程的学生姓名。

    select Sname
    from Student,(select Sno from SC where Cno=1)as SC1
    where Student.Sno=SC1.Sno;
    

    在这里插入图片描述
    as关键字可以省略,但必须为派生表指定一个别名.

    3.5 数据更新

    三种数据更新操作:添加,修改和删除

    3.5.1 插入数据

    插入元组的操作在前面的文章中已经写过了,在此直接进行下一个插入操作。

    2.插入子查询结果

    语句格式:

    INSERT
    INTO <表名> [(<属性列1><属性列2>...)]
    子查询
    

    【例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;--先按系别分组
    select * from Dept_age;--查询表内容
    

    在这里插入图片描述

    3.5.2修改数据

    语句的一般格式为:

    UPDATE <表名>
    SET <列名>=<表达式> [,<列名>=<表达式>]...
    [WHERE <条件>];
    

    若省略where子句,表示要修改表中的所有元组。

    1. 修改某一元组的值

    【例3.73】将学生201215121的年龄改为22岁。

    update Student
    set Sage=22
    where Sno='201215121';
    select Sno,Sname,Sage 
    from Student
    where Sno='201215121';
    

    在这里插入图片描述

    2. 修改多个元组的值

    【例3.74】将所有学生的年龄增加1岁。

    select Sno,Sname,Sage 
    from Student;
    update Student
    set Sage=Sage+1;
    select Sno,Sname,Sage 
    from Student;
    

    在这里插入图片描述

    3.带子查询的修改语句

    子查询也可以嵌套在update语句中,用以构造修改的条件。
    【例3.75】将计算机科学系的全体学生的成绩置为0。

    select * from SC;
    update SC
    set Grade=0
    where Sno in(
    select Sno
    from Student 
    where Sdept='CS');
    select * from SC;
    

    在这里插入图片描述

    3.5.3 删除数据

    删除语句的一般格式为:

    DELETE
    FROM <表名>
    [WHERE <条件>];
    

    如果省略where子句则删除所有的元组。
    DELETE语句删除的是表中的数据,而不是关于表的定义。

    1. 删除某一个元组的值

    【例3.76】删除学号为201215128的学生的记录。

    select Sno ,Sname
    from Student 
    where Sno='201215128';
    delete 
    from Student
    where Sno='201215128';
    select Sno Sname
    from Student 
    where Sno='201215128';
    

    在这里插入图片描述
    因为学号为201215128的学生已经选修了课程,所以在学生表中删除该学生会不满足参照完整性,所以我在SC表中删除了该学生的选课记录。delete from SC where Sno='201215128';
    结果如下:
    在这里插入图片描述

    2. 删除多个元组的值

    【例3.77】删除所有的学生选课记录。

    select * from SC;
    delete 
    from SC;
    select * from SC;
    

    在这里插入图片描述

    3. 带子查询的删除语句

    【例3.78】删除计算机科学系所有学生的选课记录。

    delete 
    from SC
    where Sno in
    (select Sno
    from Studnt
    where Sdept='CS');
    

    因为刚才手快,所有的选课记录都被删除了,所以就没有截图了。

    3.6 空值的处理

    1. 空值的产生

    【例3.79】向SC表中插入一个元组,学生号为201215126,课程号是1,成绩为空。

    insert 
    into SC(Sno,Cno,Grade)
    values('201215126','1',NULL);
    等同于
    --select * from SC;
    insert 
    into SC(Sno,Cno)
    values('201215126','1');
    

    在这里插入图片描述
    因为是新建的表,还没有201215126的信息,违背了参照完整性。
    所以向学生表中添加学生即可。
    在这里插入图片描述
    【例3.80】将Student表中学生号为201215200的学生所属的系改为空值。

    insert
    into Student
    values('201215200','刘阳','男',18,'CS');
    select *
    from Student
    where Sno='201215200';
    update Student
    set Sdept=NULL
    where Sno='201215200';
    select *
    from Student
    where Sno='201215200';
    

    在这里插入图片描述

    2.空值的判断

    【例3.81】从Student表中找出漏填了数据的学生信息。

    select*
    from Student
    where Sname is null or Ssex is null or Sage is null or Sdept is null;
    

    在这里插入图片描述

    3.空值的约束条件

    not null 不能取空值,又unique限制的属性不能为空,码属性不能取空值。

    4. 空值的算数运算、比较运算和逻辑运算

    【例3.82】找出选修1号课程的不及格的学生。

    select * from SC;
    select Sno
    from SC
    where Grade<60 and Cno='1';
    

    在这里插入图片描述
    2001215126的成绩为空,该查询不包含成绩为空的元组。既它查询的是参加了考试的但不及格的学生,所以缺考的学生不在查询的范围内。
    【例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);
    

    在这里插入图片描述

    3.7 视图

    视图是一个虚表,数据库中只存放视图的定义,而不存放视图对应的数据。
    视图一经定义,就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图,但对视图的更新操作有一定的限制。

    3.7.1 定义视图

    1. 建立视图

    建立视图的一般语句:

    CREATE VIEW <视图名>[(<列名> [,<列名>]...)]
    AS <子查询>
    [WITH CHECK OPTION];
    

    WITH CHECK OPTION会在对视图进行操作时的行满足视图定义中的谓词条件。
    组成视图的属性列名或者全部省略或者全部指定。
    以下三种情况必须明确指定组成视图的所有列名:
    (1)某个目标列不是单纯的属性名,而是聚集函数或列表达式;
    (2)多表连接时选出了几个同名列作为视图的字段;
    (3)需要在视图中为某个列启用新的更合适的名字。
    【例3.84】建立信息系学生的视图

    create view IS_Student
    as 
    select Sno,Sname,Sage
    from Student
    where Sdept='IS';
    select * from IS_Student
    

    在这里插入图片描述
    【例3.85】建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

    drop view IS_Student;--先删除之前建立的视图
    create view IS_Student
    as select Sno,Sname,Sage
    from Student
    where Sdept='IS'
    with check option;
    select * from 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';
    

    因为Student和SC表中都有Sno,所以需要指定视图的各个属性的列名。

    视图也可以建立在一个或多个已定义好的视图上,或建立在基本表与视图上。
    【例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;
    select * from BT_S;
    

    在这里插入图片描述

    分组视图:用带有group by子句的查询来定义视图
    【例3.89】将学生的学号及平均成绩定义为一个视图。

    create view S_G(Sno,Gavg)
    as
    select Sno,avg(Grade)
    from SC
    group by Sno;
    select * from S_G;
    

    在这里插入图片描述
    【例3.90】将Student表中所有女生记录定义为一个视图。

    create view F_Student(F_sno,name,sex,age,dept)
    as
    select *
    from Student
    where Ssex='女';
    select * from F_Student;
    

    在这里插入图片描述

    2.删除视图

    删除语句的格式为:

    DROP VIEW <视图名>[CASCADE]
    

    CASCADE会把该视图和由它导出的视图一并删除。
    【例3.91】删除视图BT_S和视图IS_S1

    drop view BT_S;
    drop view IS_S1;
    

    删除IS_S1时因为存在由它导出的IS_S2视图,所以该语句被拒绝执行。
    可以使用级联删除语句:

    drop view IS_S1 cascade;
    

    也可以在删除该语句之前将由它导出的视图删除。

    3.7.2 查询视图

    【例3.92】在信息系学生得视图中找出年龄小于20岁得学生。

    select Sno,Sage
    from IS_Student
    where Sage<20;
    select Sno,Sage from IS_Student;
    

    在这里插入图片描述
    【例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
    

    会出现错误。因为将其转换后的语句为:

    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;
    

    除此之外,也可以使用派生表进行查询:

    select *
    from 
    (select Sno,avg(Grade)
    from SC
    group by Sno)as S_G(Sno,Gavg)
    where Gavg>=90;
    

    但生成的派生表是临时存储的,它并不像视图一样被永久存储,执行完语句后派生表就被删除了。

    3.7.3 更新视图

    更新视图的操作:插入,删除,修改
    因为视图是虚表,所以对视图的更新最终要转换为对基本表的更新。
    [WITH CHECK OPTION]——在更新视图时会检查是否满足视图中定义的条件,不满足拒绝该操作。
    【例3.95】将信息系学生视图IS_Student中学号为201215128的学生姓名改为“陈东”。

    select * from IS_Student;
    update Student
    set Sname='陈东'
    where Sno='201215128';
    select * from IS_Student;
    

    在这里插入图片描述
    转换后的更新语句会加上Sdept=‘IS的条件。
    【例3.96】向信息系学生视图中插入一个新的学生记录。

    insert
    into IS_Student
    values('201215129','赵新',20);
    

    在这里插入图片描述
    因为在定义IS_Student视图时使用了with check option ,所以在T-SQL上插入数据时是不被允许的,若未使用则可以插入。
    【例3.97】删除信息系学生视图中学号为“201215129”的记录。

    delete
    from IS_Student
    where Sno='201215129';
    

    最后,有些视图是可更新的,但有些视图是不可更新的。
    总结:内容不难,但是有点多,花费了挺长的时间,不过还好,有了新的收获。

    展开全文
  • 数据库系统概论》复习

    千次阅读 多人点赞 2019-05-27 12:13:27
    数据库系统概论》复习 第一章 绪论 1.1 数据库系统概述 1、数据库系统的四个基本概念 数据(Data):描述事物的符号记录称为数据,数据是数据库存储的基本对象。 数据库(DB):长期存储在计算机内、有组织的...

    《数据库系统概论》复习

    除标题外加粗的是长安大学软件工程系数据库期末考试的考点,没有加粗的是课堂涉及的内容,省略和没有提及的内容是没有在课堂上讨论的内容。
    仅用于个人复习,本文著作权归原书作者所有,转载必须注明原书作者和出版社。

    第一章 绪论

    1.1 数据库系统概述

    1、数据库系统的四个基本概念
    数据(Data):描述事物的符号记录称为数据,数据是数据库存储的基本对象。
    数据库(DB):长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按照一定的数据模型组织、描述和存储,具有较小的冗余度、较高的数据独立性和易扩展性,并可为各种用户共享。概括地讲,数据库数据具有永久储存、有组织和可共享三个基本特点。
    数据库管理系统(DBMS):位于用户和操作系统之间的一层数据管理软件。主要功能包括提供数据定义语言(DDL)、数据的组织存储和管理、提供数据操纵语言(DML)、事务管理和运行管理、创建和维护等。
    数据库系统(DBS):数据库系统是由数据库、数据库管理系统、应用程序和数据库管理员(DBA)组成的存储、管理和维护数据的系统。DBS=DB+DBMS+APP+DBA
    2、数据管理技术的产生和发展
    数据管理的三个阶段:人工管理、文件管理、数据库管理系统
    文件系统与数据库管理系统的区别

    人工管理阶段文件系统阶段数据库系统阶段
    数据的管理者用户(程序员)文件系统数据库管理系统
    数据面向的对象某一应用程序某一应用现实世界(法人、社团)
    数据的共享程度无共享,冗余性极大共享性差,冗余度大共享性高,冗余度小
    数据的独立性不独立,完全依赖于程序独立性差具有高度的物理独立性和一定的逻辑独立性
    数据的结构性无结构记录内有结构、整体无结构整体结构化,用数据模型描述
    数据控制能力应用程序自己控制应用程序自己控制由数据库管理系统提供数据安全性、完整性、并发控制和恢复能力

    3、数据库系统的特点
    数据结构化;数据的共享性高、冗余性低且易扩充;数据独立性高;数据由数据库管理系统统一管理和控制。

    1.2 数据模型

    数据模型:对现实世界数据特征的抽象,用来描述数据、组织数据和操作数据,是数据库系统的核心和基础。
    1、两类数据模型:①概念模型②逻辑模型和物理模型
    概念模型:按照用户的观点对数据和信息建模,主要用于数据库设计。
    逻辑模型:按照计算机系统的观点对数据建模,主要用于数据库管理系统的实现。
    物理模型:对数据最底层的抽象,描述数据在系统内部或存储介质上的表示方式和存取方法。
    构建数据模型的方法:将现实世界抽象为信息世界,得到概念模型;将信息世界转换为机器世界,得到DBMS支持的数据模型。
    2、概念模型:
    信息世界中的基本概念:
    (1)实体:客观存在并可相互区别的事物。
    (2)属性:实体的特性称为属性。
    (3)码:唯一标识实体的属性集称为码。
    (4)实体型:用实体名及其属性名集合来抽象和刻画同类实体,称为实体型。
    (5)实体集:同一类型的实体的集合称为实体集。
    (6)联系:实体内部的联系指组成实体的属性之间的联系,实体之间的联系指不同实体集之间的联系。实体之间的联系有一对一、一对多和多对多等多种类型。
    概念模型的表示方法:实体-联系(E-R)方法,该方法使用E-R图描述概念模型。
    3、数据模型的组成要素
    数据模型通常由数据结构、数据操作和完整性约束条件三部分组成。数据结构描述数据库组成对象和对象之间的联系;数据操作指对数据库中各种对象(型)的实例(值)允许执行操作的集合;数据的完整性约束条件是一组完整性规则,包括实体完整性、参照完整性和用户定义的完整性。
    4、常用的数据模型
    层次模型、网状模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型等。
    5、层次模型(略)
    6、网状模型(略)
    7、关系模型
    关系模型的数据结构:
    (1)关系:一个关系对应通常所说的一张表。
    (2)元组:表中的一行即为一个元组。
    (3)属性:表中的一列即为一个属性。
    (4)码/键:唯一确定一个元组的属性集。
    (5):一组具有相同数据类型的值的集合。属性的取值范围来自某个域。
    (6)分量:元组中的一个属性值。
    (7)关系模式:对关系的描述,一般表示为
    关系名(属性1,属性2,…,属性n)
    关系模型的数据操作:增删改查
    【必考】关系的完整性约束:实体完整性、参照完整性、用户定义的完整性
    关系模型的评价:严格数学定义、概念单一、存储路径对用户透明、查询效率稍稍逊于格式化数据模型。

    1.3 数据库系统的结构

    1、数据库系统模式的概念
    “型”和“值”:型是指对某一类数据的结构和属性的说明,值是型的一个具体赋值。
    模式:数据库中全体数据的逻辑结构和特征的描述,仅仅涉及型的描述,不涉及具体的值。模式的一个具体值称为模式的一个实例。模式是相对稳定的,实例是相对变动的。
    2、数据库系统的三层模式结构
    在这里插入图片描述
    模式:也称逻辑模式,是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共视图。在数据库系统模式结构中处于中间层,与硬件平台和应用程序无关。一个数据库只有一个模式。
    外模式:也称子模式和用户模式,它是数据库用户(应用程序员与最终用户)能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图,是与某一应用有关的数据的逻辑表示。外模式通常是模式的子集,一个数据库可以有很多外模式。
    内模式:也称存储模式,一个数据库只有一个内模式。它是数据物理结构和存储方式的描述,是数据在数据库内部的组织方式。例如,记录的存储方式是堆存储、升序/降序存储还是聚簇存储;B+树索引还是哈希索引;是否压缩存储,是否加密;数据存储记录结构是定长结构还是边长结构;等等。
    3、数据库的而二级映像功能与数据独立性
    数据库的二级映像功能:外模式/模式映像、模式/内模式映像。
    外模式/模式映像针对于每一个外模式,定义了外模式与模式的对应关系;模式/内模式映像是唯一的,定义了数据全局逻辑结构与存储结构之间的对应关系。
    数据的逻辑独立性:当模式改变时,由DBA对各个外模式/模式的映像作相应改变,可以使外模式保持不变。应用程序是依据数据的外模式编写的,从而应用程序不必修改,保证了数据与程序的逻辑独立性,简称数据逻辑独立性。
    数据的物理独立性:当数据库的存储结构改变时,由DBA对模式/内模式映像作出相应改变,可以使模式保持不变,从而应用程序也不必改变,保证了数据与程序的物理独立性,简称数据物理独立性。

    1.4 数据库系统的组成

    数据库系统=数据库+数据库管理系统+应用程序+数据库管理员
    数据库管理员(DBA)的职责
    (1)决定数据库中的信息内容和结构;
    (2)决定数据库的存储结构和存取策略;
    (3)定义数据的安全性要求和完整性约束条件;
    (4)监控数据库的使用与运行;
    (5)数据库的改进、重组和重构

    第二章 关系数据库

    2.1 关系数据结构及形式化定义

    关系模型三要素:关系数据结构、关系操作集合、关系完整性约束
    1、关系
    关系:描述现实世界的实体以及实体之间的各种联系的单一结构类型就是关系,站在用户的视角,关系就是一张二维表。
    (1):域是一组具有相同数据类型的集合。
    (2)笛卡尔积:给定的一组域D1,D2,…,Dn,允许其中某些域是相同的,它们的笛卡尔积定义为
    D1×D2×…×Dn={(d1,d2,…,dn)|di∈Di,i=1,2,…,n}
    其中每一个元素(d1,d2,…,dn)叫做一个n元组,或简称元组,元组中的每一个分量di叫做一个分量。一个域允许的不同取值个数称为这个域的基数。
    (3)关系:D1×D2×…×Dn的子集叫做在域D1,D2,…,Dn上的关系,表示为R(D1,D2,…,Dn),R表示关系的名字,n是关系的目或度。关系中的每个元素是关系中的元组,用t表示。

    • 关系是笛卡尔积的有限子集,所以关系也是一张二维表,表的每一行表示一个元组,表的每一列对应一个域,区分域的列名就是属性名。
    • 候选码:某一属性组的的值能够唯一地标识一个元组,但是它的任何一个真子集不能,则称该属性组为候选码
    • 主码:若一个关系有多个候选码,则选定其中一个为主码。
    • 主属性:候选码的诸属性为主属性,不包含在任何候选码中的属性称为非主属性。
    • 全码:关系模式的所有属性构成这个关系模式的候选码,称为全码。
    • 关系类型:基本关系(基本表或基表)、查询表和视图表。基本表是实表;查询表是查询结果对应的表;视图表是导出表,是虚表。
    • 关系的补充限定:禁止无限关系,附加属性名来消除关系属性的有序性
    • 基本关系的6条性质:列同质、异列可同域、行列无序性、候选码唯一性、分量原子性

    2、关系模式
    关系模式:关系的描述称为模式,它的形式化表达为R(U,D,DOM,F),其中R为关系名,U为属性名集合,D为属性域,DOM为属性向域的映像集合,F为属性间数据的依赖关系集合。
    3、关系数据库(略)
    4、关系模型的存储结构(略)

    2.2 关系操作

    1、基本的关系操作:增删改查
    查询操作:选择、投影、连接、除、并、差、交、笛卡尔积
    2、关系数据语言的分类:关系代数和关系演算
    结构化查询语言SQL:DQL、DDL、DML、DCL

    2.3 关系的完整性

    【必考】关系模型三类完整性约束:实体完整性、参照完整性、用户定义完整性
    1、实体完整性:主码不可重复且不为空
    2、参照完整性:若属性/属性组F是基本关系R的外码,它与基本关系S的主码Ks相对应(R与S可以是同一关系),则对于R中每个元组在F上的值必须取空值或S中某个元组的主码值。
    外码:设F是基本关系R的属性/属性集,但不是关系R的码,Ks是基本关系S的主码,如果F与Ks相对应,则称F是R的外码,并称R为参照关系,S为被参照关系或目标关系,R与S可以是同一关系。
    3、用户定义的完整性

    2.4 关系代数

    1、传统的集合运算:并、差、交、笛卡尔积
    2、专门的关系运算:选择、投影、连接、除
    (1)选择:σF(R),F是选择条件
    (2)投影:ΠA(R),A是属性列
    (3)连接:从两个关系的笛卡尔积中选取属性间满足条件AθB的元组。
    等值连接:θ为“=”的连接运算。
    自然连接:特殊的等值连接,要求比较的分量必须是同名的属性组,并在结果中去掉重复的列。
    悬浮元组:自然连接中,关系R中某些元组有可能在S中不存在公共属性上值相等的元组,因此被舍弃,这些元组称为悬浮元组。
    外连接:进行自然连接时,保留悬浮元组,而在其他属性列上填空值。分为左外连接和右外连接,分别表示保留哪一边关系的悬浮元组。
    (4)除运算:给定关系R(X,Y)和S(Y,Z),X,Y,Z为属性组。R中的Y和S中的Y可以有不同的属性名但必须有相同的域集。R与S的除运算得到一个新的关系P(X),P是R中满足以下条件的元组在X属性列上的投影:元组在X上的分量x的象集Yx包含S在Y上的投影的集合。记作:

    R÷S={t r[X]|t r∈R∧Π Y(S)⊆Y x}
    其中Y x为x在R中的象集,x=t r[X]。

    象集Yx:给定一个关系R(X,Y),X和Y为属性组。当t[X]=x时,x在R中的象集定义为:

    Y x={t[Y]|t∈R,t[X]=x}

    如何理解除运算:R÷S,将R中属性分为公共属性和非公共属性,对于同一个非公共属性值,如果公共属性值在S中的元组中都出现过,那么把非公共属性值加入结果中。除运算一般用于解决“至少”问题。

    2.5 关系演算(略)

    第3章 关系数据库标准语言SQL

    3.1 SQL概述

    1、SQL的产生与发展
    2、SQL语言的特点
    (1)综合统一,集DDL、DML、DCL、DQL于一身。
    (2)高度非过程化。
    (3)面向集合的操作方式。
    (4)以同一种语法结构提供多种使用方式。
    (5)语言简介,易学易用。
    3、SQL的基本概念
    外模式——视图,模式——基本表,内模式——存储文件

    3.2 学生-课程数据库(略)

    之后几节全部是重点,不考察exists谓词

    3.3 数据定义

    1、模式的定义与删除
    定义模式:

    CREATE SCHEMA 模式名 AUTHORIZATION 用户名;
    CREATE SCHEMA 模式名 AUTHORIZATION 用户名 [表定义子句|视图定义子句|授权定义子句]
    

    删除模式:

    DROP SCHEMA 模式名 [CASCADE|RESTRICT]
    

    2、基本表的定义、删除与修改
    定义基本表:

    CREATE TABLE 表名 (
    	列名 数据类型 [列级完整性约束条件], 
    	[列名 数据类型 [列级完整性约束条件]] 
    	... 
    	[, 表级完整性约束条件]
    );
    

    参照完整性约束条件:

    FOREIGN KEY(Sno) REFERENCES Student(Sno)
    

    数据类型:定长与变长字符串,大对象,整型,定点数,浮点数,布尔型,时间类型
    修改基本表:

    ALTER TABLE 表名 
    [ADD [COLUMN] 新列名 数据类型 [完整性约束]] 
    [ADD 表级完整性约束]
    [DROP [COLUMN] 列名 [CASCADE|RESTRICT]]
    [DROP [CONSTRAINT] 完整性约束名 [RESTRICT|CASCADE]]
    [ALTER COLUMN 列名 数据类型];
    

    删除基本表:

    DROP TABLE 表名 [RESTRICT|CASCADE];
    

    3、索引的建立与删除
    索引类型:顺序文件上的索引、B+树索引、散列索引、位图索引
    建立索引:

    CREATE [UNIQUE|CLUSTER] INDEX 索引名 ON 表名(列名 [ASC|DESC] [, 列名 [ASC|DESC]] ... );
    

    修改索引:

    ALTER INDEX 旧索引名 RENAME TO 新索引名;
    

    删除索引:

    DROP INDEX 索引名;
    

    4、数据字典:记录数据库中所有定义信息,包括关系模式定义、视图定义、索引定义、完整性约束定义、权限定义、统计信息,的一组系统表。

    3.4 数据查询

    SELECT [ALL|DISTINCT] 目标列表达式[, 目标列表达式] ... 
    FROM 表名|视图名[, 表名|视图名] | (SELECT语句) [AS] 别名
    [WHERE 条件表达式]
    [GROUP BY 列名1 [HAVING 条件表达式]]
    [ORDER BY 列名2 [ASC|DESC]];
    

    1、单表查询
    常用的查询条件

    查询条件谓词
    比较关系运算符,NOT
    确定范围BETWEEN AND,NOT BETWEEN AND
    确定集合IN,NOT IN
    字符匹配LIKE,NOT LIKE
    空值IS NULL,IS NOT NULL
    多重条件AND,OR,NOT

    通配符:%,_
    聚集函数

    聚集函数功能
    COUNT(*)统计元组个数
    COUNT([DISTINCT|ALL] 列名)统计一列中值的个数
    SUM([DISTINCT|ALL] 列名)计算一列值的总和
    AVG([DISTINCT|ALL] 列名)计算一列值的平均值
    MAX([DISTINCT|ALL] 列名)求一列值中的最大值
    MIN([DISTINCT|ALL] 列名)求一列值中的最小值

    GROUP BY子句:将查询结果按照某一列或多列的值分组,值相等的为一组。分组后聚集函数将作用于每一组,即每一组都有一个函数值。HAVING子句与WHERE子句的区别在于HAVING子句作用于组,可以使用聚集函数,但WHERE子句中不可以。
    具体使用:

    SELECT Sno,Sname FROM Student;
    SELECT * FROM Student;
    SELECT Sname,'Year of Birth',2014-Sage BIRTHDAY,LOWER(Sdept);/*目标列表达式可以是属性列,常量,函数,也可以是表达式,通过指定别名可以改变列标题*/
    SELECT DISTINCT Sno FROM SC;/*去重*/
    SELECT Sname FROM Student WHERE Sdept='cs';/*比较大小*/
    SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;/*确定范围*/
    SELECT Sname,Ssex FROM Student WHERE Sdept IN ('cs','ma','is');/*确定集合*/
    SELECT * FROM Student WHERE Sname LIKE '刘%';/*字符匹配*/
    SELECT * FROM SC WHERE GRADE IS NULL;/*空值查询*/
    SELECT * FROM Student WHERE Sdept='cs' AND Sage<20;/*多重条件查询*/
    SELECT COUNT(*) FROM Student;/*使用聚集函数*/
    SELECT Sno,AVG(Grade)
    FROM SC
    GROUP BY Sno
    HAVING AVG(Grade)>=90;/*使用GROUP BY子句*/
    

    2、连接查询

    -- 等值连接
    SELECT Student.*,SC.*
    FROM Student,SC
    WHERE Student.Sno=SC.Sno;
    -- 自身连接
    SELECT FIRST.Cno,SECOND.Cpno
    FROM Course FIRST,Course SECOND
    WHERE FIRST.Cpno=SECOND.Cno;
    -- 外连接
    SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
    FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
    -- 多表连接
    SELECT Student.Sno,Sname,Cname,Grade
    FROM Student,SC,Course
    WHERE Student.Sno=SC.Sno AND SC.Sno=Course.Cno;
    

    嵌套循环连接算法:R与S等值连接,先在R找到第一个元组,扫描S表查找符合等值条件的S表元组,合并后形成结果表中第一个元组,然后再在R找第二个元组,重复以上步骤,直到遍历完整个R表。使用索引可以提高查找的效率。
    3、嵌套查询
    嵌套查询:一个SELECT-FROM-WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。需要特别指出的是子查询的SELECT语句中不能使用ORDER BY子句,ORDER BY子句只能对最终查询结果排序。
    不相关子查询:子查询的查询条件不依赖于父查询。
    相关子查询:子查询的查询条件依赖于父查询。相关子查询的一种执行过程为,从外层查询中取出一个元组,将它的属性值传给内层查询,执行内层查询得到一个值,用该值代替内层查询,得到外层查询,执行外层查询。
    (1)带有IN谓词的子查询

    SELECT Sname 
    FROM Student
    WHERE Sno IN(
    	SELECT Sno 
    	FROM SC
    	WHERE Cno='2'
    );
    

    (2)带有比较运算符的子查询

    SELECT Sno,Sname,Sdept
    FROM SC x
    WHERE Grade>=(
    	SELECT AVG(Grade)
    	FROM SC y
    	WHERE y.Sno=x.Sno
    );
    

    (3)带有ANY(SOME)或ALL谓词的子查询
    op ANY表示对于子查询中某一个值条件成立则为真,op是关系运算符
    op ALL表示对于子查询中所有值条件都成立才为真,op是关系运算符

    -- 查询年龄至少小于一个计科学生的非计科的学生的姓名和年龄
    SELECT Sname,Sage
    FROM Student
    WHERE Sage<ANY(
    	SELECT Sage
    	FROM Student
    	WHERE Sdept='cs'
    ) AND Sdept <> 'cs';
    -- 等价于
    SELECT Sname,Sage
    FROM Student
    WHERE Sage<(
    	SELECT MAX(Sage)
    	FROM Student
    	WHERE Sdept='cs'
    ) AND Sdept <> 'cs';
    

    (4)带有EXISTS谓词的子查询
    带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真或假。内层查询不为空则为真,内层查询为空则为假。可以使用NOT运算符对逻辑值结果取反。

    -- 查询选修了1号课程的学生
    SELECT Sname
    FROM Student
    WHERE EXISTS(
    	SELECT *
    	FROM SC
    	WHERE Sno=Student.Sno AND Cno='1'
    );
    /*使用了相关子查询,对于每一个Student,将Sno属性值传入子查询中,执行子查询,
    根据结果是否为空得到一个布尔值,为真时就将这个学生的姓名放在结果中。*/
    
    -- 变态版子查询
    -- 查询至少选修了学生201215122选修的全部课程的学生号码
    /*
    p:“学生201215122选修了课程y”
    q:“学生x也选修了课程y”
    查询:(∀y)p->q
    转换:(∀y)p->q ≡ ┐(∃y(┐(p->q))) ≡ ┐(∃y(┐(┐p∨q))) ≡ ┐∃y(p∧┐q)
    */
    SELECT DISTINCT Sno
    FROM SC SCX -- 表示学生x
    WHERE NOT EXISTS( -- 再次取反,表示x没有选修课程y的这种情况不出现
    	SELECT *
    	FROM SC SCY -- 表示学生201215122
    	WHERE SCY.Sno='201215122' AND NOT EXISTS( -- 取反,也就是x没有选修课程y
    		SELECT *
    		FROM SC SCZ
    		WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno -- 表示x也选修了课程y
    	)
    );
    

    4、集合查询
    集合操作主要包括并操作(UNION)、交操作(INTERSECT)、差操作(EXCEPT),将两个SELECT语句通过集合操作运算符连接即可。
    5、基于派生表的查询
    子查询出现在FROM子句中。

    3.5 数据更新

    1、插入数据

    -- 插入元组
    INSERT INTO 表名 [(属性列1, 属性列2 ...)]
    VALUES(常量1, 常量2 ...),[(常量1, 常量2 ...) ...];
    -- 插入子查询结果
    INSERT INTO 表名 [(属性列1, 属性列2 ...)]
    子查询;
    

    2、修改数据

    UPDATE 表名
    SET 列名=表达式[, 列名=表达式 ...]
    [WHERE 条件];
    -- 子查询也可以出现在UPDATE语句的where子句中
    

    3、删除数据

    DELETE
    FROM 表名
    [WHERE 条件];
    -- 子查询也可以出现在DELETE语句的where子句中
    

    3.6 空值的处理

    空值的产生:插入仅给部分属性赋值的元组、外连接、空值的关系运算
    空值的判断:IS NULL、IS NOT NULL
    空值的约束条件:NOT NULL、UNIQUE、码属性不能为空
    空值的算术运算、比较运算和逻辑运算:空值的算术运算结果为空值,空值的比较运算结果为UNKNOWN,UNKNOWN的逻辑运算结果如下:
    NOT U = U, U AND U = U U AND T = U, U AND F = F,U OR U = U, U OR T = T, U OR F = U

    3.7 视图

    1、定义视图
    (1)建立视图

    CREATE VIEW 视图名 [(列名[, 列名]...)]
    AS 子查询
    [WITH CHECK OPTION];
    /*WITH CHECK OPTION表示对视图进行UPDATE、INSERT、DELETE操作时
    要保证执行操作的行满足视图定义中的谓词条件(即子查询中的条件表达式)*/
    

    组成视图的所有列名全部省略或全部指定,必须指定列名的情况:

    • 某个目标列不是单纯的属性名,而是聚集函数或列表达式;
    • 多表连接时选出了几个同名列作为视图的字段
    • 需要在视图中为某个列启用新的更合适的名字
      CREATE VIEW语句并不执行子查询,而是在查询视图时才执行子查询。
      行列子集视图:从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则称这类视图为行列子集视图。
      视图可以建立在基本表、视图、基本表和视图的组合之上。
      虚拟列:由基本表经过计算导出的属性列称为虚拟列,带虚拟列的视图也称为带表达式的视图。
      分组视图:用带有聚集函数和GROUP BY子句的查询定义的视图称为分组视图。
      (2)删除视图
    DROP VIEW 视图名 [CASCADE];
    

    2、查询视图
    查询视图的方法与查询表的方法一样。
    视图消解:从数据字典中取出数据的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询。这一转换过程称为视图消解。
    目前多数关系型数据库能够对行列子集视图的查询均能进行正确转换,但对非行列子集视图的查询就不一定能够转换,这类查询应当直接对基本表进行。
    视图查询与派生表查询的区别:视图定义将永久保存在数据字典中,派生表是临时定义,执行后即被删除。
    3、更新视图
    视图是虚表,对视图的更新将转换为对基本表的更新。
    使用WITH CHECK OPTION定义视图可以防止对非视图基本表数据进行操作。
    视图并不总是可以更新的,一般地,行列子集视图是可更新的。
    4、视图的作用
    简化操作、多角度看待数据、为重构数据库提供一定程度逻辑独立性、安全性控制、更清晰的表达

    第4章 数据库安全性

    4.1 数据库安全性概述

    数据库安全性:保护数据库以防止不合法使用所造成的数据泄露、更改或破坏。
    1、数据库的不安全因素:非法入侵、数据泄露、安全环境的脆弱性
    2、安全标准简介:TCSEC、CC

    4.2 数据库安全性控制

    数据库安全性控制:身份鉴别、多层存取控制、审计、视图和数据加密
    1、用户身份鉴别(略)
    2、存取控制
    存取控制机制:

    • 定义用户权限,并将用户权限登记到数据字典中
    • 合法权限检查

    存取控制方法:

    • 自主存取控制:采用授权方式实现存取控制,比较灵活。
    • 强制存取控制:采用密级标定数据库对象,比较严格。

    3、自主存取控制方法
    用户权限二要素:数据库对象、操作类型
    授权:定义存取权限。
    在关系型数据库系统中,存取控制的对象不仅有数据本身,还有数据库模式(数据库、基本表、视图和索引的创建)。
    4、授权:授予与收回
    (1)GRANT

    GRANT 权限[, 权限]...
    ON 对象类型 对象名[, 对象类型 对象名]...
    TO 用户[, 用户]...
    [WITH GRANT OPTION];-- 允许权限传播
    

    eg:

    GRANT SELECT ON TABLE Student TO U1;
    GRANT ALL PRIVILEGES ON TABLE Student,Course TO U2,U3; -- mysql中不允许写多个用户和多个表
    GRANT SELECT ON TABLE SC TO PUBLIC; -- mysql中没有PUBLIC
    GRANT UPDATE(Sno),SELECT ON TABLE Student TO U4;
    GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;
    

    (2)REVOKE

    REVOKE 权限[, 权限]...
    ON 对象类型 对象名[, 对象类型 对象名]...
    FROM 用户[, 用户]...
    [CASCADE|RESTRICT]
    

    eg:

    REVOKE UPDATE(Sno) ON TABLE Student FROM U4;
    REVOKE SELECT ON TABLE SC FROM PUBLIC;
    REVOKE INSERT ON TABLE SC FROM U5 CASCADE;
    REVOKE ALL PRIVILEGES,GRANT OPTION ON TABLE Student FROM U4;-- 收回一切权限
    

    自主控制控制:用户可以“自主”地决定将数据的存取权限授予何人、决定是否也将“授权”的权限授予别人。因此称这样的存取控制是自主存取控制。
    3、创建数据库模式的权限
    创建用户:

    CREATE USER 用户名 [WITH DBA|RESOURCE|CONNECT];
    
    • 只有系统的超级用户才有权创建一个新的数据库用户
    • 新创建的数据库用户有三种权限:CONNECT、RESOURCE和DBA
    • 新用户默认的权限为CONNECT,拥有CONNECT权限的用户只能登录数据库
    • 拥有RESOURCE权限的用户能创建基本表和视图,成为所创建对象的属主,但不能创建模式,也不可以新建用户。数据库对象的属主可以把该对象上的权限授权给其他用户。
    • 拥有DBA权限的用户具有最高权限。

    【注意】CREATE USER语句不是SQL标准

    5、数据库角色
    数据库角色:被命名的一组与数据库操作相关的权限,角色是权限的集合。使用角色管理数据库权限可以简化授权的过程。
    角色的创建:

    CREATE ROLE 角色名
    

    给角色授权:

    GRANT 权限[, 权限]...
    ON 对象类型 对象名
    TO 角色[, 角色]...
    

    将一个角色授予其他用户或角色:

    GRANT 角色[, 角色]...
    TO 角色|用户[,角色|用户]...
    [WITH ADMIN OPTION];-- mysql不支持WITH ADMIN OPTION
    

    角色权限的收回:

    REVOKE 权限[, 权限]...
    ON 对象类型 对象名
    FROM 角色[, 角色]...
    

    6、强制存取控制方法(MAC)
    基本思想:对系统控制下的所有主客体实施强制存取控制策略。在强制存取控制中,数据库管理系统所管理的全部实体被分为主体和客体两部分,主体包括用户和外部进程,客体包括数据库对象如文件、表、索引、视图。数据库系统为每个主体和客体指派敏感度标记,主体敏感度标记称为许可证级别,客体敏感度标记称为密级。同等或高许可证级别用户可以读取相应客体(规则Ⅰ),同等或低许可证级别用户可以写相应客体(规则Ⅱ),规则Ⅱ防止密级从高流向低从而泄密。MAC对数据进行密级标记,无论数据如何复制,标记与数据都是不可分割的整体,只有符合密级要求的用户才能访问,从而提供更高级别的安全性。

    4.3 视图机制(略)

    4.4 审计

    审计:建立审计日志记录数据库操作

    4.5 数据加密(略)

    4.6 其他安全性保护(略)

    第5章 数据库完整性

    数据库的完整性:正确性+相容性
    数据库完整性要求:定义完整性约束性机制+完整性检查+违约处理

    5.1 实体完整性

    1、实体完整性定义
    两种约束条件说明方法:列级约束条件、标记约束条件

    CREATE TABLE Student(
    	Sno CHAR(9) PRIMARY KEY, -- 列级约束条件
    	Sname CHAR(20) NOT NULL,
    	Ssex CHAR(2),
    	Sage SMALLINT,
    	Sdept CHAR(20)
    );
    -- 等价于
    CREATE TABLE Student(
    	Sno CHAR(9),
    	Sname CHAR(20) NOT NULL,
    	Ssex CHAR(2),
    	Sage SMALLINT,
    	Sdept CHAR(20),
    	PRIMARY KEY(Sno) -- 表级约束条件
    );
    

    2、实体完整性检查和违约处理
    唯一性检查+空值检查

    5.2 参照完整性

    1、定义参照性完整性

    CREATE TABLE SC(
    	Sno CHAR(9),
    	Cno CHAR(4),
    	Grade SMALLINT,
    	PRIMARY KEY(Sno,Cno), -- 只能用表级实体完整性约束
    	FOREIGN KEY(Sno) REFERENCES Student(Sno), -- 表级完整性约束
    	FOREIGN KEY(Cno) REFERENCES Course(Cno) -- 表级完整性约束
    );
    

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

    可能破坏参照完整性的情况及违约处理
    被参照表(如Student)参照表(如SC)违约处理
    可能破坏参照完整性插入元组NO ACTION
    可能破坏参照完整性修改外码值NO ACTION
    删除元组可能破坏参照完整性NO ACTION/CASCADE DELETE/NULL
    修改主码值可能破坏参照完整性NO ACTION/CASCADE UPDATE/NULL

    显式说明参照完整性约束:

    CREATE TABLE SC(
    	Sno CHAR(9),
    	Cno CHAR(4),
    	Grade SMALLINT,
    	PRIMARY KEY(Sno,Cno), -- 只能用表级实体完整性约束
    	FOREIGN KEY(Sno) REFERENCES Student(Sno)
    		ON DELETE CASCADE
    		ON UPDATE CASCADE, -- 表级完整性约束
    	FOREIGN KEY(Cno) REFERENCES Course(Cno)
    		ON DELETE NO ACTION
    		ON UPDATE CASCADE-- 表级完整性约束
    );
    

    5.3 用户定义的完整性

    1、属性上的约束条件

    -- 列值非空
    CREATE TABLE SC(
    	Sno CHAR(9) NOT NULL, /* 列值非空 */
    	Cno CHAR(4) NOT NULL, /* 列值非空 */
    	Grade SMALLINT NOT NULL, /* 列值非空 */
    	PRIMARY KEY(Sno,Cno),
    	...
    );
    -- 列值唯一
    CREATE TABLE DEPT(
    	Dno NUMERIC(2),
    	Dname CHAR(9) UNIQUE NOT NULL, /* 列值唯一,非空 */
    	Location CHAR(9),
    	PRIMARY KEY(Dno)
    );
    -- CHECK短语
    CREATE TABLE Student(
    	Sno CHAR(9) PRIMARY KEY,
    	Sname CHAR(8) NOT NULL,
    	Ssex CHAR(2) CHECK(Ssex IN ('男','女')),
    	Sage SMALLINT,
    	Sdept CHAR(20)
    );
    -- 注意在MySQL8.0.16之前的版本不支持CHECK短语
    

    2、元组上的约束条件

    CREATE TABLE Student(
    	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.%')
    );
    

    5.4 完整性约束命名子句

    1、完整性约束命名子句

    CONSTRAINT 完整性约束条件名 完整性约束条件
    -- 完整性约束条件包括NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK短语
    -- 完整性约束条件可以作为列级约束条件或表级约束条件出现
    

    2、修改表中的完整性约束

    ALTER TABLE 表名 ADD 完整性约束条件子句
    ALTER TABLE 表名 DROP CONSTRAINT 完整性约束条件名
    

    5.5 域中的完整性限制(略)

    5.6 断言(略)

    5.7 触发器

    不同数据库触发器实现方式差异较大,其中MYSQL不支持语句级触发器。
    1、定义触发器

    -- SQL标准触发器定义
    CREATE TRIGGER 触发器名 -- 只有表的拥有者才有权创建触发器
    BEFORE|AFTER 触发事件 ON 表名 -- 触发器只能定义在基本表上,触发事件可以是INSERT、DELETE、UPDATE
    REFERENCING NEW|OLD ROW AS 变量
    FOR EACH ROW|STATEMENT -- 触发器类型可以是行级触发器和语句级触发器
    [WHEN 触发条件] 触发动作体
    -- MySQL触发器定义
    CREATE [DEFINER = 用户名] TRIGGER 触发器名
    BEFORE|AFTER 触发事件
    ON tbl_name FOR EACH ROW
    [FOLLOWS|PRECEDES 其他触发器名]
    触发动作体
    /*触发动作体既可以是一个匿名PL/SQL过程块,也可以是对过程的调用。
    如果是行级触发器,用户可以在过程体中使用NEW/OLD引用触发事件发生
    之前的旧值和之后的新值*/
    

    书上例题的MySQL写法

    -- 习题5.21
    DROP TRIGGER IF EXISTS `lifang`.`sc_AFTER_UPDATE`;
    DELIMITER $$
    USE `lifang`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `lifang`.`sc_AFTER_UPDATE` AFTER UPDATE ON `sc` FOR EACH ROW
    BEGIN
    	if new.grade >= 1.1 * old.grade
        then
    		insert into sc_u values(new.sno,new.cno,old.grade,new.grade);
        end if;
    END$$
    DELIMITER ;
    -- 习题5.23
    CREATE TABLE `lifang`.`insert_log` (
      `numbers` INT UNSIGNED NOT NULL);
    DROP TRIGGER IF EXISTS `lifang`.`worker_BEFORE_UPDATE`;
    DELIMITER $$
    USE `lifang`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `lifang`.`worker_BEFORE_UPDATE` BEFORE INSERT ON `worker` FOR EACH ROW
    BEGIN
    	if(new.wpos="经理" and new.wwage<4000) then
    		set new.wwage=4000;
        end if;
    END$$
    DELIMITER ;
    

    2、激活触发器的顺序:BEFORE触发器->触发事件->AFTER触发器
    3、删除触发器

    DROP TRIGGER 触发器名 ON 表名;
    

    第6章 关系数据理论

    6.1 问题的提出

    数据依赖:一个关系内部属性和属性之间的一种约束关系,这种约束关系是通过属性间值相等与否体现出来的数据间相关联系。其中函数依赖和多值依赖是最重要的数据依赖。
    函数依赖:关系R的属性x,y如果满足y=f(x)的关系,则称y函数依赖于x。

    一个好的模式应当不会发生插入异常、删除异常和更新异常,数据冗余应尽可能少。

    6.2 规范化

    1、函数依赖
    定义:X、Y是关系R上的属性集,对于R中任意一个关系r,如果不存在元组t1,t2使得t1[X]=t2[X]且t1[Y]≠t2[Y],那么称X函数确定Y或Y函数依赖于X,记作X->Y。
    非平凡的函数依赖:X→Y且Y⊈X,则称X→Y是非平凡的函数依赖。
    平凡的函数依赖:X→Y且Y⊆X,则称X→Y是平凡的函数依赖。
    决定因素:若X→Y,则X称为这个函数依赖的决定属性组,也称为决定因素。
    X→Y且Y→X,记作X←→Y。
    完全函数依赖:在R(U)中,如果X->Y且对于X任何一个真子集X’,都有X’-/->Y,则称Y对X完全函数依赖,记作X-F->Y。
    部分函数依赖:不是完全函数依赖的函数依赖是部分函数依赖,记作X-P->Y。
    传递函数依赖:在R(U)中,如果X->Y(Y⊈X),Y-/->X,Y->Z,Z⊈Y则称Z对X传递函数依赖,记作X-传递->Z。
    2、
    候选码:关系R的全体属性U完全函数依赖于R中的属性(集)K,则K是R的候选码。
    超码:关系R的全体属性U部分函数依赖于R中的属性(集)K,则K是R的超码。
    主码、主属性、非主属性、全码的概念见第2章。
    3、范式
    范式:关系数据库中关系需要满足的要求称为范式,根据要求的程度可以将范式分为1NF、2NF、3NF、BCNF、4NF、5NF(范围依次缩小)。
    规范化:低级范式转换成高级范式的过程称为规范化,方法为模式分解。
    1NF:关系的每一个分量具有原子性,不能再分割。
    4、2NF
    2NF:满足1NF且每一个非主属性完全函数依赖于任何一个候选码。
    在这里插入图片描述
    这个关系模式不是2NF,就会产生以下问题:
    (1)插入异常。如果插入一个学生(Sdept,Sdept,Sloc),但该学生并未选课没有课程号Cno,这样的元组就无法插入S-L-C中。
    (2)删除异常。某个学生只选了一门课,如果现在要删除这门课,就必须连带整个元组删除。
    (3)修改复杂。某个学生转专业,本来只需修改Sdept,但Sloc函数依赖于Sdept,所以要连带修改住处Sloc。如果学生选了多门课,Sdept和Sloc冗余度大,修改次数成倍增长。
    模式分解方法:用投影分解把关系模式转换成两个关系模式。
    在这里插入图片描述
    5、3NF
    3NF:1NF,不存在码X,属性组Y和非主属性Z(Z不包含Y),使得Z函数依赖于Y,Y函数依赖于X。
    3NF的必要条件:没有传递函数依赖,没有部分函数依赖,是2NF。注意,定义里没有说Y不包含X,如果有部分函数依赖,就一定存在定义里面的X,Y,Z,如上面的Sno->(Sno,Cno)->Sdept。
    模式分解方法:分解传递依赖。
    在这里插入图片描述
    6、BCNF
    BCNF:1NF,Y非平凡函数依赖于X时X必有码。
    BCNF的必要条件:3NF、主属性不函数依赖于非主属性。
    显然,全码一定是BCNF。
    7、多值依赖
    多值依赖:关系模式R(U),X、Y、Z是U的子集,且Z=U-X-Y。多值依赖X->->Y成立当且仅当对R(U)的任一关系r,给定(x, z),有一组Y的值,这组值仅决定于x值而与z值无关。这里的“无关”应该理解为,将R按X属性值分组,在每一组中对于任意z,z的象集都相等。
    平凡的多值依赖:Y多值依赖于X且Z为空集。
    多值依赖的性质:
    (1)对称性:若X->->Y则X->->Z,Z=U-X-Y。
    (2)传递性:若X->->Y,Y->->Z,则X->->Z-Y。
    (3)函数依赖可以看成特殊的多值依赖。
    (4)若X->->Y,X->->Z,则X->->YZ。
    (5)若X->->Y,X->->Z,则X->->Y∩Z。
    (6)若X->->Y,X->->Z,则X->->Y-Z,X->->Z-Y。
    多值依赖与函数依赖的区别:
    (1)多值依赖的有效性与属性集的范围有关。
    (2)如果Y函数依赖于X,则Y的非空真子集也函数依赖于X。但是如果Y多值依赖于X,却不能断言Y的非空真子集也多值依赖于X。
    8、4NF
    4NF:1NF,如果对于R的每个非平凡多值依赖X->->Y(Y⊈X),X都含有码,则称关系模式满足4NF。
    4NF必要条件:限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。
    9、规范化小结
    关系模式的规范化过程是通过对关系模式的分解来实现的,即把低一级的关系模式为若干个高一级的关系模式。

    6.3 数据依赖的公理系统(难度太大)

    6.4 模式的分解

    模式分解的三个定义:

    • 分解具有无损连接性
    • 分解要保持函数依赖
    • 分解既要保持函数依赖,又要具有无损连接性

    第7章 数据库设计

    7.1 数据库设计概述

    数据库设计的一般定义:指对于一个给定的应用环境,构造(设计)优化的数据库逻辑模式和物理结构,并据此建立数据库及其应用系统,使之能够有效地存储和管理数据,满足各种用户的需求,包括信息管理需求和数据操作需求。
    1、数据库设计的特点:
    三分技术,七分管理,十二分基础数据
    结构设计和处理设计相结合
    2、数据库设计方法(略)
    3、数据库设计基本步骤
    需求分析阶段->概念结构设计阶段->逻辑结构设计阶段->物理结构设计阶段->数据库实施阶段->数据库运维
    需求分析阶段:数字字典、全系统中数据项、数据结构、数据流、数据存储的描述
    概念结构设计阶段:概念模型(E-R图)、数据字典
    逻辑结构设计阶段:关系模式转换
    物理结构设计:存储结构、存取方式、存取路径建立
    数据库实施:创建数据库、装入数据、数据库试运行
    数据库运维:性能监测、转储/恢复、数据库重组和重构

    7.2 需求分析

    数据字典:关于数据库中数据的描述,即元数据。需求分析阶段建立,设计过程中完善。数据字典通常包括数据项、数据结构、数据流、数据存储、处理过程。

    7.3 概念结构设计

    1、概念模型(略)
    2、E-R模型
    实体之间的联系:一对一、一对多、多对多
    E-R图:实体、属性、联系
    3、扩展E-R图(略)
    4、uml(略)
    5、概念结构设计
    实体与属性划分原则:能作为属性就作为属性,属性不能再有属性,属性不能与其他实体有联系
    E-R图的集成:合并->修改与重构
    合并时的冲突:属性冲突、命名冲突、结构冲突

    • 属性冲突:域冲突和单位冲突,协商解决
    • 命名冲突:同名异义、一义多名,协商解决
    • 结构冲突:同一对象一个是实体一个是属性,统一抽象;属性个数和排列不同,统一属性;联系类型不同,综合调整。

    消除冗余:数据字典+数据流图或采用规范化理论

    7.4 逻辑结构设计

    1、关系模型转换
    转换方法:

    • 一对一联系:任意一端合并或转换为独立的关系模式
    • 一对多联系:n端合并或转换为独立的关系模式
    • 多对多联系:转换为独立的关系模式
    • 三元联系:转换为独立的关系模式
    • 具有相同码的关系模式可合并

    2、数据模型的优化
    数据模型优化的方法:
    (1)确定数据依赖;
    (2)对于各个关系模式之间的数据依赖进行极小化处理,消除冗余的联系;
    (3)按照数据依赖的理论对关系模式逐一进行分析,考察是否存在部分函数依赖、传递函数依赖、多值依赖等,确定各关系模式分别属于第几范式;
    (4)根据需求分析阶段得到的处理要求分析对于这样的应用环境这些模式是否合适,确定是否要对某些模式进行合并或分解;
    (5)对关系模式进行必要分解,提高数据操作效率和存储空间利用率。常用的两种分解方法是水平分解和垂直分解。
    水平分解是把(基本关系)的元组分为若干子集合,定义每个子集合为一个子关系,以提高系统的效率。根据“二八原则”把经常使用的数据分解出来,形成一个子关系。
    垂直分解是把关系模式R的属性分解为若干子集合,形成若干子关系模式。垂直分解的原则是将经常在一起使用的属性从R中分解出来形成一个子关系模式。垂直分解需要确保无损连接性和保持函数依赖
    3、设计用户子模式(略)

    7.5 物理结构的设计

    数据库的物理设计:为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程,就是数据库的物理结构。
    数据库的物理结构设计通常分两步走:
    (1)确定数据库的物理结构;
    (2)对物理结构进行评价。
    1‘、数据库物理设计的内容和方法
    目标:事务响应时间小、存储空间利用率高、事务吞吐量大。
    关系数据库物理设计的内容主要包括:为关系模式选择存取方法,以及设计关系、索引等数据库文件的物理存储结构。
    2、关系模式存取方法选择
    常用的存取方法:索引方法、聚簇方法
    常用的索引方法:B+树索引、hash索引
    (1)B+树索引存取方法的选择
    建立索引的原则:属性经常出现在出查询条件中、属性经常作为聚簇函数的参数、属性经常在连接操作的连接条件中出现、更新频率较高的关系不能建立太多索引。
    (2)hash索引存取方法的选择
    选择hash存取方法的原则:关系属性主要出现在等值连接条件或等值比较选择条件中,而且满足以下两个条件,则此关系可以选择hash存取方法。
    ①一个关系的大小可预知,而且不变;
    ②关系的大小动态改变,但DBMS提供了动态hash存取方法。
    (3)聚簇存取方法的选择
    聚簇:把具有相同属性值的元组放在连续的物理块中称为聚簇,该属性(组)称为聚簇码。
    特点:提高查询效率、适用于单个或多个关系、一个关系只能加入一个聚簇。
    候选聚簇的原则:经常一起进行连接操作的关系、关系属性经常出现在相等比较条件中、关系在某个属性上冗余度高
    聚簇方法排除原则:经常全表扫描的关系、更新操作多于连接、加入其他聚簇的关系
    聚簇的局限性:只能提高某些应用的性能、开销大、所有索引失效、改变聚簇码需要维护开销
    3、确定数据库的存储结构
    内容:存放位置和存储结构
    指标:存取时间、存储空间利用率、维护代价

    7.6 数据库的实施与维护(略)

    第8章 数据库编程

    8.1 嵌入式SQL

    1、处理过程
    含嵌入式SQL主语言程序->预处理,将嵌入式SQL翻译为主语言函数调用->转换后的主语言程序->编译链接
    2、嵌入式SQL语句与主语言之间的通信
    数据库工作单元与源程序工作单元之间的通信包括:
    (1)向主语言传递SQL语句执行状态信息,主要使用SQL通信区;
    (2)主语言向SQL语句提供参数,主要用主变量实现;
    (3)将SQL语句的查询结果提交给主语言,主变量和游标实现。
    SQL通信区:SQL语句执行后的执行状态信息通过SQL通信区传递给主语言,主语言根据这些信息决定接下来执行的语句。
    主变量:SQL语句中使用的主语言程序变量简称为主变量,分为输入主变量和输出主变量。指示变量是主变量附带的一个整型变量,指示输入主变量是否为空,检测输出主变量是否为空。
    游标:游标是系统为用户开设的一个数据缓冲区,存放SQL语句执行的结果,每个游标区都有一个名字,用户通过游标逐一获取记录并赋给主变量。
    程序实例

    EXEC SQL BEGIN DECLARE SECTION;/*主变量说明开始*/
    	char deptname[20];
    	char hsno[9];
    	char hsname[20];
    	char hssex[2];
    	int HSage;
    	int NEWAGE;
    EXEC SQL END DECLARE SECTION;/*主变量说明结束*/
    long SQLCODE;
    EXEC SQL INCLUDE SQLCODE;/*定义SQL通信区*/
    int main(void){/*C语言主程序开始*/
    	int count = 0;
    	char yn;
    	printf("Please choose the department name(cs/ma/is):");
    	scanf("%s",&deptname);
    	EXEC SQL CONNECT TO TEST@localhost:54321 USER "SYSTEM"/"MANAGER";/*连接数据库TEST*/
    	EXEC SQL DECLARE SX CURSOR FOR/*定义游标SX*/
    		SELECT Sno,Sname,Ssex,Sage
    		FROM Student
    		WHERE Sdept=:deptname;/*SX对应的语句*/
    	EXEC SQL OPEN SX;/*开启游标,指向查询结果的第一行*/
    	while(1){/*逐行处理查询的结果*/
    		EXEC SQL FETCH SX INTO:HSno,:HSname,:HSsex,:HSage;/*推进游标,将当前结果放入主变量*/
    		if(SQLCA.SQLCODE!=0) break;/*SQLCODE记录操作状态,成功或失败*/
    		if(count++==0)
    			printf("\n%-10s %-20s %-10s %-10s\n","Sno","Sname","Ssex","Sage");/*打印表头*/
    		printf("%-10s %-20s %-10s %-10d\n",HSno,HSname,HSsex,HSage);/*输出结果*/
    		printf("UPDATE AGE(y/n)?");
    		do{
    			scanf("%c",&yn);
    		}while(yn!='N'&&yn!='n'&&yn!='Y'&&yn!='y');
    		if(yn=='Y'||yn=='y'){
    			printf("INPUT NEW AGE:");
    			scanf("%d",&NEWAGE);/*输入新年龄到主变量中*/
    			EXEC SQL UPDATE Student/*SQL更新语句*/
    				SET Sage=:NEWAGE
    				WHERE CURRENT OF SX;/*对当前游标指向的学生进行更新*/
    		}
    	}
    	EXEC SQL CLOSE SX;/*关闭游标,不再与查询结果对应*/
    	EXEC SQL COMMIT WORK;/*提交更新*/
    	EXEC SQL DISCONNECT TEST;/*断开数据库连接*/
    	return 0;
    }
    

    3、不用游标的SQL语句(略)
    4、使用游标的SQL语句(略)
    5、动态SQL|(略)

    8.2 过程化SQL

    1、过程化SQL的块结构

    DECLARE /*定义的变量、常量只能块中使用*/
    变量、常量、游标、异常等
    BEGIN
    	SQL语句、PL/SQL语句
    EXCEPTION
    	异常处理部分
    END;
    

    2、变量和常量的定义

    变量名 数据类型 [[NOT NULL]:=初值表达式]/*定义变量*/
    变量名 数据类型 [[NOT NULL] 初值表达式]/*定义变量*/
    常量名 数据类型 CONSTANT:=常量表达式/*定义常量*/
    变量名:=表达式/*赋值*/
    

    3、流程控制

    IF condition THEN
    	statements;
    END IF
    
    IF condition THEN
    	statements1;
    ELSE
    	statements2;
    END IF;
    
    LOOP
    	statements;/*使用EXIT、BREAK或LEAVE退出循环*/
    END LOOP;
    
    WHILE condition LOOP
    	statements;
    END LOOP;
    
    FOR count IN [REVERSE] lower_bound .. upper_bound LOOP
    	statements
    END LOOP;
    

    8.3 存储过程和函数

    1、存储过程
    存储过程的优点:编译运行效率高、降低C/S之间通信量、集中控制方便维护
    存储过程的用户接口:
    (1)创建存储过程

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

    注解:ROLLBACK表示回滚事务
    (2)执行存储过程

    CALL/PERFORM PROCEDURE 过程名([参数1, 参数2, ...]);
    

    (3)修改存储过程

    ALTER PROCEDURE 过程名1 RENAME TO 过程名2;/*重命名*/
    ALTER PROCEDURE 过程名 COMPILE;
    

    (4)删除存储过程

    DROP PROCEDURE 过程名();
    

    2、函数
    (1)函数的定义语句格式

    CREATE OR REPLACE FUNCTION 函数名([参数1, 参数2, ...]) RETURNS 类型
    AS 过程化SQL

    (2)函数的执行语句格式

    CALL/SELECT 函数名([参数1, 参数2, ...]);
    

    (3)修改函数

    ALTER FUNCTION 函数名1 RENAME 函数名2;
    ALTER FUNCTION 函数名 COMPILE;
    

    3、过程化SQL中的游标
    当查询返回多条记录时,使用游标对结果集进行处理。一个游标与一个SQL语句相关联。在存储过程中可以定义普通游标、REFCURSOR类型游标、带参数的游标等。

    CREATE OR REPLACE PROCEDURE proc_cursor() AS
    DECLARE
    	cno CHAR(3);
    	cname CHAR(8);
    	CURSOR mycursor(leaderno CHAR(3)) FOR
    		SELECT lno,lname FROM leader WHERE Lno=leaderno;
    BEGIN
    	OPEN mycursor('L01');
    	FETCH mycursor INTO cno,cname;
    	INSERT INTO temp(lno,lname) VALUES(cno,cname);
    	CLOSE mycursor;
    	OPEN mycursor('L02');
    	FETCH mycursor INTO cno,cname;
    	INSERT INTO cno,cname;
    	INSERT INTO temp(lno,lname) VALUES(cno,cname);
    	CLOSE mycursor;
    END
    

    8.4 ODBC编程(略)

    8.5 OLE DB(略)

    8.6 JDBC编程(略)

    第9章 关系查询处理和查询优化

    查询优化一般可分为代数优化(逻辑优化)和物理优化(非代数优化)

    9.1 关系数据库系统的查询处理

    1、查询处理步骤
    查询处理步骤:查询分析、查询检查、查询优化、查询执行
    查询分析:语句扫描、词法分析、语法分析
    查询检查:语义检查、视图消解、存取权限检查、转换成等价的关系代数并构建语法分析树
    查询优化:代数优化和物理优化
    查询执行:生成查询执行计划、由代码生成器执行查询计划并返回查询结果
    2、实现查询优化的算法举例
    (1)选择操作的实现
    简单全表扫描算法
    索引扫描算法
    (2)连接操作实现
    嵌套循环算法:类似于二重循环
    排序合并算法:先排序再查找。常用于等值连接。
    索引连接算法:通过索引查找元组然后连接
    hash join算法:第一步,划分阶段,对包含元组较少的表进行散列;第二步,试探阶段,对另一个表用相同的散列函数进行处理,把hash值相同的元组进行匹配。处理等值连接。

    9.2 关系数据库系统的查询优化

    1、查询优化概述
    查询优化的优点是用户不再需要考虑如何表达查询可以达到最高的效率。
    总代价=I/O代价+CPU代价+内存代价+通信代价
    2、一个实例
    题目:求选取了2号课程的学生姓名
    SQL表达:

    SELECT Student.Sname
    FROM Student,SC
    WHERE Student.Sno=SC.Sno AND SC.Cno='2';
    

    关系代数表达:
    Q1SnameStudent.Sno=SC.Sno∧SC.Cno=‘2’(Student×SC))
    Q2SnameSC.Cno=‘2’(Student⋈SC))
    Q3Sname(Student⋈σSC.Cno=‘2’(SC))
    查询效率分析:
    假设学生-课程表数据库中有1000个学生记录,10000个选课记录,其中选修2号课程的选课记录为50个。一个块能够装10个Student元组或100个SC元组,在内存中存放5块Student元组和1块SC元组,每块能装连接后的元组10块。
    (1)第一种情况:
    计算广义笛卡尔积:读取1000/10+1000/(10*5)*10000/100=2100块,连接后元组107个写出106块。
    选择操作:读入106块。
    投影操作:把上一步结果投影输出即可。
    总读写数据块=2100+106+106
    (2)第二种情况:
    计算自然连接:读取1000/10+1000/(10*5)*10000/100=2100块,连接后元组104个写出103块。
    选择操作:读入103块。
    投影操作:把上一步结果投影输出即可。
    总读写数据块=2100+103+103
    (3)第三种情况:
    选择操作:读取一遍SC表,共计100块;满足条件的块只有50个不必写入中间文件。
    连接操作:读取Student表,共计100块,把读入的元组和内存中的SC元组作连接。
    投影操作:把上一步结果投影输出即可。
    总读写数据块=100+100=200

    9.3 代数优化

    1、关系代数表达式等价变换规则
    连接、笛卡尔积交换律与结合律、投影与选择串接定律、选择与投影可以交换次序、选择对笛卡尔积的分配律、选择对并、差、自然连接的分配律、投影对笛卡尔积和并的分配律
    2、查询树的启发式优化
    先选择;投影与选择同步;逆用分配律减少投影次数;选择与连接的合并;找出公共子表达式。
    重在例题。

    9.4 物理优化

    物理优化的方法:基于规则的启发式优化、基于代价估算的优化、两者结合的优化
    1、基于启发式规则的存取路径选择优化
    (1)选择操作的启发式优化:
    对于小关系全表扫描
    对于大关系,启发式规则有:

    选择条件启发式规则
    主码=值主码索引
    非主属性=值索引扫描/全表扫描(估算结果>10%)
    非等值条件/范围查询索引扫描/全表扫描(估算结果>10%)
    AND连接的合取条件优先组合索引,索引扫描次之,如果选择率>10%全表扫描
    OR连接的析取条件全表扫描

    (2)连接操作的启发式规则
    已排序->排序合并算法;
    有索引->索引连接算法;
    其中一个表较小->hash join算法;
    嵌套循环算法垫底。
    2、基于代价估算的优化(略)

    9.5 查询计划的执行(略)

    第10章 数据库恢复技术

    10.1 事务的基本概念

    1、事务
    事务:用户定义的一个数据库操作序列,这些操作要么全做,要么全都不做,是一个不可分割的工作单位。
    定义事务的语句:

    BEGIN TRANSACTION;
    COMMIT;/*提交事务的全部操作*/
    ROLLBACK;/*回滚,事务中对数据库的所有已完成操作全部撤销*/
    

    2、事务的ACID特性
    (1)原子性:事务中的操作要么都做要么都不做。
    (2)一致性:事务执行是一致性状态的切换,事务中断会造成不一致。
    (3)隔离性:事务与事务相互独立。
    (4)持续性:一经提交永久改变。

    10.2 数据库恢复概述

    数据库的恢复:把数据库从错误状态恢复到一致性状态的过程

    10.3 故障的种类

    1、故障的种类
    (1)事务内部故障:非预期不可控,撤销(UNDO)处理。
    (2)系统故障(软故障):造成系统停摆需要重启的事件,不破坏数据库但会丢失内存中数据,重做(REDO)处理。
    (3)介质故障(硬故障):存储介质故障,破坏数据库,仅能通过备份恢复。
    (4)计算机病毒:人为破坏,需要信息安全技术支持。
    2、恢复的基本原理:冗余

    10.4 恢复的实现技术

    建立冗余的技术:数据转储、登记日志文件
    1、数据转储:定期将数据库拷贝到后备副本,数据库被破坏后可将数据库恢复到转储时的状态。静态转储在无事务时进行,只拷贝不修改;动态转储期间允许对数据库的修改,需要日志文件。海量转储指每次都全部转储数据库,增量转储指只转储更新的部分。
    2、登记日志文件
    日志文件的格式与内容:登记内容包括事务的开始、结束和所有更新操作;记录内容包括事务标识、操作类型、操作对象、旧值和新值。
    日志文件的作用:事务和系统故障恢复的依据,动态转储必须建立日志文件以便结合后备副本恢复数据库,静态转储中可创建日志文件以提高恢复效率。
    登记日志文件二原则:严格时间顺序、先写日志后写数据库

    10.5 恢复策略

    1、事务故障的恢复:反向扫描+逆操作
    2、系统故障的恢复:正向扫描,已写的重做,未写的撤销
    3、介质故障的恢复:重装数据库、重做完成的事务,DBA介入

    10.6 具有检查点的恢复技术

    周期性设置检查点保存数据库状态,恢复时扫描日志到检查点为止。

    10.7数据库镜像(略)

    第11章 并发控制

    事务执行方式:串行执行、交叉并发、同时并发

    11.1 并发控制概述

    并发操作的不一致性:丢失修改、不可重复读、读脏数据。
    在这里插入图片描述
    并发控制的主要技术:封锁、时间戳、乐观控制法、多版本并发控制

    11.2 封锁

    封锁类型:排他锁(写锁,X锁)、共享锁(读锁、S锁)。

    XS-
    XNNY
    SNYY
    -YYY
    Y=Yes,表示相容,N=No,表示不相容

    11.3 封锁协议

    一级封锁协议:加X锁——修改——事务结束——释放X锁
    二级封锁协议:加S锁——读取——读取结束——释放S锁
    三级封锁协议:加S锁——读取——事务结束——释放S锁

    在这里插入图片描述

    11.4 活锁和死锁

    1、活锁:事务总是处于等待状态,采用先来先服务策略避免活锁
    2、死锁:互斥、请求与保持、不可剥夺、循环等待
    (1)死锁的预防:一次封锁、顺序封锁
    (2)死锁的诊断与解除:超时法、等待图法

    11.5 并发调度的可串行性

    1、可串行化调度
    可串行化调度:多个事务的并发执行是正确的,当且仅当其结果与按某一次序串行地执行这些事务的结果相同,称这种调度策略为可串行化调度。可串行性是并发事务正调度的准则。
    2、冲突可串行化调度
    冲突操作:不同的事务对同一个数据的读-写操作和写-写操作。
    冲突可串行化调度:一个调度Sc在保证冲突操作的次序不变的情况下,通过交换两个事务不冲突操作的次序得到另一个调度Sc’,如果Sc’是串行的,称Sc为冲突可串行化的调度。
    判断可串行化调度的充分条件:若一个调度是冲突可串行化,则一定是可串行化的调度。

    11.6 两段锁协议

    两段锁协议:读写操作先加锁、释放后不再加锁
    “两段锁”的含义:扩展阶段获得封锁,收缩阶段释放封锁
    若并发事务都遵守2PL协议,则对这些事务的任何并发调度策略都是可串行化的。

    11.7 封锁的粒度

    1、多粒度封锁
    封锁的粒度就是封锁对象的大小,封锁粒度与系统并发度和并发控制的开销密切相关。
    多粒度封锁:一个系统同时支持多种粒度的封锁。
    多粒度树:
    在这里插入图片描述
    多粒度封锁协议:允许每个节点独立加锁,对节点加锁意味着该节点子树节点加同类锁。显示封锁,直接加到数据对象上的锁,隐式封锁,因为父节点加锁而被加锁。检查封锁冲突时既要向上检查,又要向下检查。有了意向锁就无需向下检查。
    2、意向锁
    意向锁:节点加意向锁,说明下层节点正在被加锁;任一节点加锁必须给它的上层节点加锁。
    三种常用意向锁:意向共享锁(IS锁)、意向排他锁(IX锁)、共享意向排他锁(SIX)。
    节点加IS锁表示后裔节点拟加S锁,节点加IX锁表示后裔节点拟加X锁,节点加SIX锁表示对它加S锁再加IX锁。
    在这里插入图片描述

    11.8 其他并发控制机制(略)

    展开全文
  • 当产品的状态发生变化时,只需使用当前的DateTime将一行插入到ProductStatus中.无需触摸前一行(这是真的,并保持为真).没有虚拟值报告工具(除了您的应用程序)必须解释.> DateTime是产品放置在该状态中的实际...

    这是一个达到您所述要求的模型.

    >归一化到5NF;没有重复的列;没有更新异常,没有空.

    >当产品的状态发生变化时,只需使用当前的DateTime将一行插入到ProductStatus中.无需触摸前一行(这是真的,并保持为真).没有虚拟值报告工具(除了您的应用程序)必须解释.

    > DateTime是产品放置在该状态中的实际DateTime;如果你愿意的话,“从”. “To”很容易导出:它是Product的下一个(DateTime>“From”)行的DateTime;在它不存在的地方,该值是当前的DateTime(使用ISNULL).

    第一个模型完成; (ProductId,DateTime)足以为主键提供唯一性.但是,由于您要求某些查询条件的速度,我们可以在物理级别增强模型,并提供:

    >一个索引(我们已经有了PK索引,所以我们将在添加第二个索引之前首先增强它)以支持覆盖的查询(基于{ProductId | DateTime | Status}的任何排列的索引可以由索引提供,没有不得不去数据行).这将Status :: ProductStatus关系从Non-Identifying(折线)更改为Identifying类型(实线).

    >根据Product⇢DateTime⇢Status,大多数查询都是时间序列,选择PK安排.

    >提供第二个索引以提高基于状态的查询速度.

    >在替代安排中,这是相反的;即,我们主要想要所有产品的当前状态.

    >在ProductStatus的所有版本中,辅助索引(而不是PK)中的DateTime列是DESCending;最近的是第一次.

    我已经提供了您要求的讨论.当然,您需要尝试合理大小的数据集,并做出自己的决定.如果您有任何不明白的地方,请询问,我会扩展.

    回应评论

    报告当前状态为2的所有产品

    SELECT ProductId,Description

    FROM Product p,ProductStatus ps

    WHERE p.ProductId = ps.ProductId -- Join

    AND StatusCode = 2 -- Request

    AND DateTime = ( -- Current Status on the left ...

    SELECT MAX(DateTime) -- Current Status row for outer Product

    FROM ProductStatus ps_inner

    WHERE p.ProductId = ps_inner.ProductId

    )

    > ProductId是索引,领先col,双方

    >涵盖查询选项中的索引,第二列中的日期时间

    > StatusCode是索引的,涵盖查询选项中的第3列

    >由于索引中的StatusCode是DESCending,因此只需要一次提取即可满足内部查询

    >对于一个查询,行是同时需要的;它们很接近(由于Clstered Index);由于行的大小,几乎总是在同一页面上.

    这是普通的SQL,一个子查询,使用SQL引擎的强大功能,即Relational set处理.这是一种正确的方法,没有什么比这更快,任何其他方法都会更慢.任何报告工具只需点击几下即可生成此代码,无需输入.

    ProductStatus中的两个日期

    DateTimeFrom和DateTimeTo等列是严重错误.让我们按重要性排序.

    >这是一个严重的标准化错误. “DateTimeTo”很容易从下一行的单个DateTime派生出来;因此它是多余的,一个重复的列.

    >精度没有进入:它可以通过DataType(DATE,DATETIME,SMALLDATETIME)轻松解决.无论是显示少于秒,微秒还是纳秒,都是商业决策;它与存储的数据无关.

    >实现DateTo列是100%重复(下一行的DateTime).这需要两倍的磁盘空间.对于大桌子来说,这将是非常不必要的浪费.

    >鉴于它是一个短行,每次访问时,您需要两倍的逻辑和物理I / O来读取表.

    >两倍的缓存空间(换句话说,只有一半的行适合任何给定的缓存空间).

    >通过引入重复列,您已经引入了错误的可能性(现在可以通过两种方式导出值:从重复的DateTimeTo列或下一行的DateTimeFrom).

    >这也是一个更新异常.当您更新任何DateTimeFrom已更新时,必须获取前一行的DateTimeTo(因为它已关闭而没什么大不了的)和更新(大不了,因为它是一个可以避免的附加动词).

    >“Shorter”和“编码快捷方式”无关,SQL是一种繁琐的数据操作语言,但SQL就是我们所拥有的(Just Deal It It).任何无法对子查询进行编码的人都不应该编码.任何复制列以减轻次要编码“难度”的人实际上都不应该建模数据库.

    请注意,如果维持最高阶规则(标准化),则会消除整组低阶问题.

    从集合的角度思考

    >在编写简单的SQL时遇到“困难”或遇到“痛苦”的任何人在执行其工作职能时都会瘫痪.通常,开发人员不考虑集合,而关系数据库是面向集合的模型.

    >对于上面的查询,我们需要Current DateTime;由于ProductStatus是按时间顺序排列的一组产品状态,因此我们只需要属于产品的最新或MAX(DateTime)集合.

    >现在让我们看一下据称“困难”的东西.对于每个产品在特定状态下的持续时间的报告:DateTimeFrom是可用列,并定义水平截止,子集(我们可以排除较早的行); DateTimeTo是产品状态子集中最早的.

    SELECT ProductId,Description,[DateFrom] = DateTime,[DateTo] = (

    SELECT MIN(DateTime) -- earliest in subset

    FROM ProductStatus ps_inner

    WHERE p.ProductId = ps_inner.ProductId -- our Product

    AND ps_inner.DateTime > ps.DateTime -- defines subset,cutoff

    )

    FROM Product p,ProductStatus ps

    WHERE p.ProductId = ps.ProductId

    AND StatusCode = 2 -- Request

    >在获取下一行方面的思考是面向行的,而不是面向集合的处理.使用面向集合的数据库时会造成严重影响.让Optimiser为您做所有想法.检查你的SHOWPLAN,这可以很好地优化.

    >无法集中思考,因此仅限于编写单级查询,这不是一个合理的理由:在数据库中实现大量复制和更新异常;浪费在线资源和磁盘空间;保证一半的性能.学习如何编写简单的SQL子查询以获取易于派生的数据要便宜得多.

    展开全文
  • mysql面试题

    千次阅读 2019-09-23 12:28:36
    最全MySQL面试题和答案 ...2.innodb是支持事务的存储引擎,合于插入和更新操作比较多的应用,设计合理的话是行锁(最大区别就在锁的级别上),适合大数据,大并发。 数据表类型有哪些     ...

    最全MySQL面试题和答案

    Mysql 的存储引擎,myisam和innodb的区别。

    答:

    1.MyISAM 是非事务的存储引擎,适合用于频繁查询的应用。表锁,不会出现死锁,适合小数据,小并发。

    2.innodb是支持事务的存储引擎,合于插入和更新操作比较多的应用,设计合理的话是行锁(最大区别就在锁的级别上),适合大数据,大并发。

    数据表类型有哪些

           答:MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。
           MyISAM:成熟、稳定、易于管理,快速读取。一些功能不支持(事务等),表级锁。
           InnoDB:支持事务、外键等特性、数据行锁定。空间占用大,不支持全文索引等。

    MySQL数据库作发布系统的存储,一天五万条以上的增量,预计运维三年,怎么优化?

    a. 设计良好的数据库结构,允许部分数据冗余,尽量避免join查询,提高效率。
    b. 选择合适的表字段数据类型和存储引擎,适当的添加索引。
    c. mysql库主从读写分离。
    d. 找规律分表,减少单表中的数据量提高查询速度。
    e。添加缓存机制,比如memcached,apc等。
    f. 不经常改动的页面,生成静态页面。
    g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

    对于大流量的网站,您采用什么样的方法来解决各页面访问量统计问题?

    答:a. 确认服务器是否能支撑当前访问量。
    b. 优化数据库访问。
    c. 禁止外部访问链接(盗链), 比如图片盗链。
    d. 控制文件下载。
    e. 使用不同主机分流。
    f. 使用浏览统计软件,了解访问量,有针对性的进行优化。

    如何进行SQL优化?

    答:
    (1)选择正确的存储引擎
    以 MySQL为例,包括有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。
    MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

    InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。但是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。

     

    (2)优化字段的数据类型

    记住一个原则,越小的列会越快。如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。当然,你也需要留够足够的扩展空间。

     

    (3)为搜索字段添加索引

    索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么最好是为其建立索引,除非你要搜索的字段是大的文本字段,那应该建立全文索引。

     

    (4)避免使用Select *从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。即使你要查询数据表的所有字段,也尽量不要用*通配符,善用内置提供的字段排除定义也许能给带来更多的便利。

     

    (5)使用 ENUM 而不是 VARCHAR

    ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。例如,性别、民族、部门和状态之类的这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。

     

    (6)尽可能的使用 NOT NULL

    除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。 当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。

     

    (7)固定长度的表会更快

    如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。

     

    固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。

     

    并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。

     

    如何设计一个高并发的系统

    ① 数据库的优化,包括合理的事务隔离级别、SQL语句优化、索引的优化

    ② 使用缓存,尽量减少数据库 IO

    ③ 分布式数据库、分布式缓存

    ④ 服务器的负载均衡

    锁的优化策略

    ① 读写分离

    ② 分段加锁

    ③ 减少锁持有的时间

    ④ 多个线程尽量以相同的顺序去获取资源

    等等,这些都不是绝对原则,都要根据情况,比如不能将锁的粒度过于细化,不然可能会出现线程的加锁和释放次数过多,反而效率不如一次加一把大锁。这部分跟面试官谈了很久

    索引的底层实现原理和优化

    B+树,经过优化的B+树

    主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此InnoDB建议为大部分表使用默认自增的主键作为主索引。

     什么情况下设置了索引但无法使用 

    ① 以“%”开头的LIKE语句,模糊匹配

    ② OR语句前后没有同时使用索引

    ③ 数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)

    SQL语句的优化 

    order by要怎么处理

    alter尽量将多次合并为一次

    insert和delete也需要合并

    等等

    实践中如何优化MySQL

    我当时是按以下四条依次回答的,他们四条从效果上第一条影响最大,后面越来越小。

    ① SQL语句及索引的优化

    ② 数据库表结构的优化

    ③ 系统配置的优化

    ④ 硬件的优化

    sql注入的主要特点

    变种极多,攻击简单,危害极大

    sql注入的主要危害

    未经授权操作数据库的数据

    恶意纂改网页

    私自添加系统账号或者是数据库使用者账号

    网页挂木马

    优化数据库的方法

    1.  选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL,例如’省份’、’性别’最好适用ENUM
    2.  使用连接(JOIN)来代替子查询
    3.  适用联合(UNION)来代替手动创建的临时表
    4.  事务处理
    5.  锁定表、优化事务处理
    6.  适用外键,优化锁定表
    7.  建立索引
    8.  优化查询语句

     

    简单描述mysql中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。

    普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

     

    普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说,唯一索引可以保证数据记录的唯一性。

     

    主键,是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字 PRIMARY KEY 来创建。

    索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引,这就是联合索引。

     

    索引可以极大的提高数据的查询速度,但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件。

    数据库中的事务是什么?

    事务(transaction)是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功,则认为事务成功,即使只有一个操作失败,事务也不成功。如果所有操作完成,事务则提交,其修改将作用于所有其他数据库进程。如果一个操作失败,则事务将回滚,该事务所有操作的影响都将取消。ACID 四大特性,原子性、隔离性、一致性、持久性。

    了解XSS攻击吗?如何防止?

    XSS是跨站脚本攻击,首先是利用跨站脚本漏洞以一个特权模式去执行攻击者构造的脚本,然后利用不安全的Activex控件执行恶意的行为。
    使用htmlspecialchars()函数对提交的内容进行过滤,使字符串里面的特殊符号实体化。

    SQL注入漏洞产生的原因?如何防止?

    SQL注入产生的原因:程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤,导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。

    防止SQL注入的方式:
    开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置

    执行sql语句时使用addslashes进行sql语句转换

    Sql语句书写尽量不要省略双引号和单引号。

    过滤掉sql语句中的一些关键词:update、insert、delete、select、 * 。

    提高数据库表和字段的命名技巧,对一些重要的字段根据程序的特点命名,取不易被猜到的。

    Php配置文件中设置register_globals为off,关闭全局变量注册

    控制错误信息,不要在浏览器上输出错误信息,将错误信息写到日志文件中。

     

     

    为表中得字段选择合适得数据类型(物理设计)

     字段类型优先级: 整形>date,time>enum,char>varchar>blob,text
     优先考虑数字类型,其次是日期或者二进制类型,最后是字符串类型,同级别得数据类型,应该优先选择占用空间小的数据类型

    存储时期

    Datatime:以 YYYY-MM-DD HH:MM:SS 格式存储时期时间,精确到秒,占用8个字节得存储空间,datatime类型与时区无关
    Timestamp:以时间戳格式存储,占用4个字节,范围小1970-1-1到2038-1-19,显示依赖于所指定得时区,默认在第一个列行的数据修改时可以自动得修改timestamp列得值
    Date:(生日)占用得字节数比使用字符串.datatime.int储存要少,使用date只需要3个字节,存储日期月份,还可以利用日期时间函数进行日期间得计算
    Time:存储时间部分得数据
    注意:不要使用字符串类型来存储日期时间数据(通常比字符串占用得储存空间小,在进行查找过滤可以利用日期得函数)
    使用int存储日期时间不如使用timestamp类型

     

    对于关系型数据库而言,索引是相当重要的概念,请回答有关索引的几个问题:

    a)、索引的目的是什么?
    快速访问数据表中的特定信息,提高检索速度

    创建唯一性索引,保证数据库表中每一行数据的唯一性。

    加速表和表之间的连接

    使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间

    b)、索引对数据库系统的负面影响是什么?
    负面影响:
    创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加;索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间;当对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。

    c)、为数据表建立索引的原则有哪些?
    在最频繁使用的、用以缩小查询范围的字段上建立索引。

    在频繁使用的、需要排序的字段上建立索引

    d)、 什么情况下不宜建立索引?
    对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引。

    对于一些特殊的数据类型,不宜建立索引,比如文本字段(text)等

     简述在MySQL数据库中MyISAM和InnoDB的区别

    区别于其他数据库的最重要的特点就是其插件式的表存储引擎。切记:存储引擎是基于表的,而不是数据库。

    InnoDB与MyISAM的区别:

    InnoDB存储引擎: 主要面向OLTP(Online Transaction Processing,在线事务处理)方面的应用,是第一个完整支持ACID事务的存储引擎(BDB第一个支持事务的存储引擎,已经停止开发)。

    特点:

    · 行锁设计、支持外键,支持事务,支持并发,锁粒度是支持mvcc得行级锁;

     MyISAM存储引擎: 是MySQL官方提供的存储引擎,主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。
    特点:

    不支持事务,锁粒度是支持并发插入得表级锁,支持表所和全文索引。操作速度快,不能读写操作太频繁;

     解释MySQL外连接、内连接与自连接的区别

    先说什么是交叉连接: 交叉连接又叫笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配。

    内连接 则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中,即内连接只连接匹配的行。
    外连接 其结果集中不仅包含符合连接条件的行,而且还会包括左表、右表或两个表中
    的所有数据行,这三种情况依次称之为左外连接,右外连接,和全外连接。

    左外连接,也称左连接,左表为主表,左表中的所有记录都会出现在结果集中,对于那些在右表中并没有匹配的记录,仍然要显示,右边对应的那些字段值以NULL来填充。右外连接,也称右连接,右表为主表,右表中的所有记录都会出现在结果集中。左连接和右连接可以互换,MySQL目前还不支持全外连接。

    写出三种以上MySQL数据库存储引擎的名称(提示:不区分大小写)

    MyISAM、InnoDB、BDB(BerkeleyDB)、Merge、Memory(Heap)、Example、Federated、
    Archive、CSV、Blackhole、MaxDB 等等十几个引擎

    Myql中的事务回滚机制概述

    事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,是一个不可分割的工作单位,事务回滚是指将该事务已经完成的对数据库的更新操作撤销。

    要同时修改数据库中两个不同表时,如果它们不是一个事务的话,当第一个表修改完,可能第二个表修改过程中出现了异常而没能修改,此时就只有第二个表依旧是未修改之前的状态,而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候,当第一个表修改完,第二表修改出现异常而没能修改,第一个表和第二个表都要回到未修改的状态,这就是所谓的事务回滚

     

    SQL语言包括哪几部分?每部分都有哪些操作关键字?

    答:SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询(DQL)四个部分。

    数据定义:Create Table,Alter Table,Drop Table, Craete/Drop Index等

    数据操纵:Select ,insert,update,delete,

    数据控制:grant,revoke

    数据查询:select

     

    完整性约束包括哪些?


    答:数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

    分为以下四类:

    1) 实体完整性:规定表的每一行在表中是惟一的实体。

    2) 域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括取值范围、精度等规定。

    3) 参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。

    4) 用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。

    与表有关的约束:包括列约束(NOT NULL(非空约束))和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。

     

    什么是事务?及其特性?


    答:事务:是一系列的数据库操作,是数据库应用的基本逻辑单位。

    事务特性:

    (1)原子性:即不可分割性,事务要么全部被执行,要么就全部不被执行。

    (2)一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态

    (3)隔离性。在事务正确提交之前,不允许把该事务对数据的任何改变提供给任何其他事务,

    (4) 持久性。事务正确提交后,其结果将永久保存在数据库中,即使在事务提交后有了其他故障,事务的处理结果也会得到保存。

    或者这样理解:

    事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组,如果任何一个语句操作失败那么整个操作就被失败,以后操作就会回滚到操作前状态,或者是上有个节点。为了确保要么执行,要么不执行,就可以使用事务。要将有组语句作为事务考虑,就需要通过ACID测试,即原子性,一致性,隔离性和持久性。

     

    什么是锁?


      答:数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

    基本锁类型:锁包括行级锁和表级锁

     

    什么叫视图?游标是什么?


    答:视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。

      游标:是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

     

    什么是存储过程?用什么来调用?


    答:存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

     

    索引的作用?和它的优点缺点是什么?


    答:索引就一种特殊的查询表,数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录,不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的,创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度,同时也增加了数据库的尺寸大小。

     

    如何通俗地理解三个范式?  


    答:第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;

    第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;  

    第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。。

    范式化设计优缺点:

    优点:

    可以尽量得减少数据冗余,使得更新快,体积小

    缺点:对于查询需要多个表进行关联,减少写得效率增加读得效率,更难进行索引优化

    反范式化:

    优点:可以减少表得关联,可以更好得进行索引优化

    缺点:数据冗余以及数据异常,数据得修改需要更多的成本

     

    什么是基本表?什么是视图?


    答:基本表是本身独立存在的表,在 SQL 中一个关系就对应一个表。  视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中,是一个虚表  

     

    试述视图的优点?


    答:(1) 视图能够简化用户的操作  (2) 视图使用户能以多种角度看待同一数据; (3) 视图为数据库提供了一定程度的逻辑独立性; (4) 视图能够对机密数据提供安全保护。

     

     NULL是什么意思


    答:NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 NULL值进行比较,并在逻辑上希望获得一个答案。

    使用IS  NULL来进行NULL判断

     

    主键、外键和索引的区别?


    主键、外键和索引的区别

    定义:

     主键–唯一标识一条记录,不能有重复的,不允许为空

     外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值

     索引–该字段没有重复值,但可以有一个空值

    作用:

     主键–用来保证数据完整性

     外键–用来和其他表建立联系用的

     索引–是提高查询排序的速度

    个数:

     主键–主键只能有一个

     外键–一个表可以有多个外键

     索引–一个表可以有多个唯一索引

     

    你可以用什么来确保表格里的字段只接受特定范围里的值?


    答:Check限制,它在数据库表格里被定义,用来限制输入该列的值。

    触发器也可以被用来限制数据库表格里的字段能够接受的值,但是这种办法要求触发器在表格里被定义,这可能会在某些情况下影响到性能。

     

    说说对SQL语句优化有哪些方法?(选择几条)


    (1)Where子句中:where表之间的连接必须写在其他Where条件之前,那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

    (2)用EXISTS替代IN、用NOT EXISTS替代NOT IN。

    (3) 避免在索引列上使用计算

    (4)避免在索引列上使用IS NULL和IS NOT NULL

    (5)对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

    (6)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描

    (7)应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描

     

    SQL语句中‘相关子查询’与‘非相关子查询’有什么区别?


    答:子查询:嵌套在其他查询中的查询称之。

    子查询又称内部,而包含子查询的语句称之外部查询(又称主查询)。

    所有的子查询可以分为两类,即相关子查询和非相关子查询

    (1)非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。

    (2)相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。

    故非相关子查询比相关子查询效率高

     

    char和varchar的区别?


    答:是一种固定长度的类型,varchar则是一种可变长度的类型,它们的区别是:  

    char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足.(在检索操作中那些填补出来的空格字符将被去掉)在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节).  

    varchar得适用场景:

    字符串列得最大长度比平均长度大很多 2.字符串很少被更新,容易产生存储碎片 3.使用多字节字符集存储字符串

    Char得场景:

        存储具有近似得长度(md5值,身份证,手机号),长度比较短小得字符串(因为varchar需要额外空间记录字符串长度),更适合经常更新得字符串,更新时不会出现页分裂得情况,避免出现存储碎片,获得更好的io性能。

    展开全文
  • 前端面试题

    万次阅读 多人点赞 2019-08-08 11:49:01
    添加 删除 替换 插入到某个接点的方法 50 javascript的本地对象,内置对象和宿主对象 50 window.onload 和document ready的区别 50 ” ”和“ =”的不同 51 javascript的同源策略 51 JavaScript是一门什么...
  • JPA

    千次阅读 多人点赞 2019-01-05 09:43:11
    今天做东西的时候使用到了JPA,在这里把JPA需要注意的知道的体系结构,给大家罗列一遍。如果能帮到大家一点,希望大家,点个... 需要指定跟哪个数据库进行交互; 需要指定 JPA 使用哪个持久化的框架以及配置该框架的...
  • C#基础教程-c#实例教程,适合初学者

    万次阅读 多人点赞 2016-08-22 11:13:24
     交叉语言处理:由于任何遵守通用语言规范的语言源程序,都可编译为相同的中间语言代码,不同语言设计的组件,可以互相通用,可以从其它语言定义的类派生出本语言的新类。由于中间语言代码由CLR负责执行,因此异常...
  • ERP

    千次阅读 2018-11-14 22:16:03
    尽管UML和其它开发工具还会设计出许多派生的视图,但上述这些图和其它辅助性的文档是软件开发人员所见的最基本的构造。 1.2.6 设计工具PowerDesigner(PD) PowerDesigner(PD)最初由Xiao-Yun Wang(王晓昀)在SDP ...
  • 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1=value1 where 范围 查找:select * from table1 where field1 like ’%value...
  • 史上最全面Java面试汇总(面试题+答案)

    万次阅读 多人点赞 2018-07-06 14:09:25
    跨域不同 抽象类所体现的是一种继承关系,要想使得继承关系合理,父类和派生类之间必须存在"is-a" 关系,即父类和派生类在概念本质上应该是相同的。对于接口则不然,并不要求接口的实现者和接口定义在概念本质上是...
  • WPF开发教程

    万次阅读 多人点赞 2019-07-02 23:13:20
    ------WPF开发教程 目录 WPF基础入门....... 1. WPF基础之体系结构......4. WPF基础之属性系统... 26 5. WPF基础之路由事件... 33 6. WPF基础之布局系统... 46 7. WPF基础之样式设置和模板化... ...
  • 数据库设计就是根据各种应用处理的要求、硬件环境及操作系统特性等,将现实世界中的数据进行合理组织,并利用已有的数据库管理系统(DBMS)来建立数据库系统的过程。 2. 设计流程 (1). 需求分析(设计的基础、最困难...
  • formula:设置一个 SQL 表达式, Hibernate 将根据它来计算出派生属性的值. 派生属性: 并不是持久化类的所有属性都直接和表的字段匹配, 持久化类的有些属性的值必须在运行时通过计算才能得出来, ...
  •  Alias(别名):某属性的另一个名字。在SQL中,可以用别名替换表名。 Alternate keys(备用键,ER/关系模型):在实体/表中没有被选为主健的候选键。 Anomalies(异常)参见更新异常(update anomalies) ...
  • 低 没有为派生表所生成的列正确派生 IDENTITY 属性。 为派生表所生成的列正确派生了 IDENTITY 属性。 低 对浮点数据类型执行运算的算术运算符的可为 Null 性属性始终可以为 Null。 在输入不可为 Null 并且 ANSI_...
  • 数据库——数据库结构设计

    千次阅读 2020-03-08 22:21:25
    目标 1 数据库概念设计 2 数据库逻辑设计 3 数据库物理设计 数据库概念设计 ...3 描述数据的属性特征 4 描述数据之间的关系 5 定义和描述数据的约束 6 说明数据的安全性要求 7 支持用户的各种数据处理需求...
  • 数据库知识 (1)数据库模型(概念模式、外模式、内模式) (2)数据模型,ER图,规范化 (3)数据操作 (4)数据库语言 (5)数据库管理系统的功能和特征 (6)数据库的控制功能 (7)数据仓库和分布式数据库基础...
  • 2、在插入记录之时,必须将数据实例的属性值逐一赋给该表的各字段; 3、在查询记录之时,必须遍历结果集游标,把各字段值逐一赋给数据实例; 4、每次读写操作之前,都要先开启数据库连接;读写操作之后,又要关闭...
  • SQL Server数据库应用技术

    千次阅读 2017-09-09 01:14:50
    SQL Server数据库应用技术 SQL是Structured Query Language的缩写。SQL是为数据库而建立的操作命令集,是一种功能齐全的数据库语言。SQL功能强大、简单、易学、使用方便,已经成为了数据库操作的基础,并且现在几乎...
  • 数据库期末复习

    2021-06-17 20:28:00
    数据库系统阶段 1.2 数据库管理的组成和特点 1.2.1组成 计算机硬件系统 数据库集合 数据库管理系统(DBMS) 相关的软件系统 数据库管理员以及其他人员 1.2.2特点 数据冗余度小,数据共享性高 具有较高的数据独立性 ...
  • 计算机三级(数据库)复习重点欢迎阅读我的计算机三级总结第一章 数据库应用系统开发方法第二章 需求分析第三章 数据库结构设计(自底向上)第四章 数据库应用系统功能设计与实现第五章 UML与数据库应用系统第六章 ...
  • 本文正在参与炫“库”行动-人大金仓有奖征文:快来点击活动链接参与投稿吧https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb ...2、改变数据库应用系统体系结构、升级DBMS版本、修改程序以适.
  • 经典SQL语句大全

    热门讨论 2014-12-20 12:00:30
    插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1=value1 where 范围 查找:select * from table1 where field1 like ’%value...
  • 经典数据库代码收集

    万次阅读 2018-09-13 16:37:25
    1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\...
  • 数据库复习题二

    千次阅读 2019-12-30 11:21:12
    数据库复习题二数据库系统与应用的知识点详细内容:一、概念设计:画ER图 重点二、逻辑设计:关系模式(利用函数依赖、范式规范关系) 重点1、关系模式(Relational Schema):由一个关系名以及它所有的属性构成。...
  • 如果子査询中没有聚集函数,派生表可以不指定属性列,子査询SELECT子句后面的列名为其默认属性。例如: [例3.60] 査询所有选修了1号课程的学生姓名 --原来的方法 select Sname from Student where exists ( ...
  • ** 数据库分析与设计 ** 事务规范包括了事务名称、事务描述、事务所访问的数据项、事务用户。 ** UML与数据库应用系统 **
  • 数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中——所以,一旦基本表中的数据发生变化,从视图中查询出的数据也就随之改变了。从这种意义上看,视图只是一个窗口,透过它可以...
  • 数据库原理复习笔记(实用)

    万次阅读 多人点赞 2018-12-20 16:36:59
    这是大三上学期复习数据库时自己整理的,有些地方可能有误,大家可以参考参考,欢迎讨论哦 最后有一些习题
  • 数据库新技术

    2021-05-03 08:27:49
    文章目录集中式数据库1. 数据库组成2 数据管理技术的3个发展阶段3. 数据库体系结构3.1 建模3.2 分类3.2.1 概念模型3.2.2 逻辑模型3.2.2.1 层次模型3.2.2.2 网络模型3.2.2.3 关系模型3.2.3 物理模型4 数据库系统结构...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,489
精华内容 9,795
关键字:

数据库派生属性如何插入