数据库入门知识点

MySQL 索引有哪两种存储类型？其中系统默认索引存储类型是什么？
- 两种存储引擎：INNODB、MyISAM
- 默认存储引擎：INNODB
使用存储过程有哪些优点？
- 存储过程增强了 SQL 语言的功能和灵活性。
- 存储过程允许标准组件式编程，可以一次编译多次运行。
- 存储过程能实现较快的执行速度。
- 存储过程能够减少网络流量。
- 存储过程可被作为一种安全机制来充分利用。
使用触发器有什么作用？
安全性，审计，实现复杂的数据完整性规则，实现复杂的非标准的数据库相关完整性规则。
使用视图有哪些优势？视图的工作机制是怎样的？
- 视图的优势：
  - 增强数据的安全性；
  - 提高灵活性，操作变得简单；
  - 提高数据的逻辑独立性。
- 视图的工作机制：
  在数据库中，视图只有结构没有数据，视图的数据来自于基本表；当调用视图时，才会执行视图中的 SQL 语句，显示基本表中的数据。
为保证数据库的安全，MySQL提供了三种不同类型的安全检查，分别是什么？
登陆验证，授权，访问控制
由 ANSI/ISO 定义的 SQL 标准中，定义了哪四种隔离级别？
序列化，可重复读，提交读，未提交读
如果没有并发控制就会出现数据不一致问题，这四个问题分别是什么？
丢失更新、脏读、不可重复读、幻读
MySQL 常见的日志有哪四种？它们的作用分别是什么？
- 通用查询日志：记录用户登录和记录查询的信息。
- 慢查询日志：记录执行时间超过指定时间的操作。
- 错误日志：记录MySQL服务器的启动、关闭、运行错误等信息。
- 二进制日志：以二进制文件形式记录了数据库中的操作，但不记录查询语句。
数据库设计的 6 个阶段。
需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。
简述三级模式结构与两级映像的优点。

保证数据的独立性。
方便用户使用，简化用户接口。
保证数据库安全性的一个有力措施。
有利于数据的共享性。
有利于从宏观上通俗的理解数据库系统的内部结构。

数据库系统概述

1.数据库基本概念

数据库是长期存储在计算机内、按照一定的组织方式存储在一起、可共享的大量相关数据的集合。

2.数据库系统的特点

(1) 数据结构化

(2) 数据的共享性高，冗余度低且易扩充

(3) 数据独立性高

(4) 数据由 DBMS 统一管理和控制

3.DBMS 的主要功能?

(1) 数据库的定义功能
(2) 数据库的操纵功能
(3) 数据库的运行管理
(4) 数据库的建立和维护功能

4.数据库系统组成

组成 : 一般由硬件系统、应用系统、数据库和人员组成。

5.数据库的三级模式定义

(1) 概念模式: 模式是数据库种全体数据的逻辑结构和特征的描述，是所有用户的公用数据库结构是型的描述。
(2) 外模式: 外模式(也称子模式或用户模式)是数据库用户所见和使用的局部数据的逻辑结构和特征的描述，是用户所用的数据库结构。

(3) 内模式: 内模式(也称存储模式)是数据物理结构和存储方法的描述。它是整个数据库的最底层结构的表示。

6.数据库两级映像的作用?

(1) 外模式/概念模式映像

定义外模式与模式之间的对应关系,实现了数据的全局逻辑结构和局部逻辑结构的转换。

(2) 概念模式/内模式映像
定义数据全局逻辑结构与存储结构之问的对应关系, 实现了数据的全局逻辑结构和物理存储结构的转换。

总结:在数据库三级模式间引入二级映象的主要作用是提高数据与程序的独立性。

信息与数据模型

1.信息的三种世界是什么?彼此之间的联系是什么?

信息的三种世界是指现实世界、信息世界和计算机世界(也称机器世界)

2.数据模型的三个要素:

数据结构、数据操作、完整性约束条件

3.数据库系统按照不同层次可采用不同的数据模型，一般可分为三层:

概念层、逻辑层和物理层。

4.什么是概念模型?

概念模型也称为信息模型，是现实世界到信息世界的第一层抽象，是数据库设计人员进行数据库设计的有力工具,也是数据库设计人员和用户之间进行交流的语言，是对事物和事物之间联系的静态描述。

5.E-R 模型的要素

E-R 模型的要素分别是:实体、联系和属性
(1) 实体: 用于表示现实世界具有相同属性描述的事物的集合.

(2) 属性: 用于表示实体的某种特征或者表示实体间关系的特征。

(3) 联系: 现实世界中事物内部以及事物之间的联系在信息世界的概念模型中反映为实体内部的联系和实体之间的联系。

6.两个实体之间的联系有几种

一对一（1:1）

一对多（1:n）

多对多（n:n）

7.常用的逻辑模型有几种?

最常用的逻辑数据模型有:层次模型、网状模型、关系模型

8.关系模型的数据结构形式

关系模型的数据结构是关系，从用户的角度米说，关系模型的而逻辑结构就是二维表。

9.e-r 模型向关系模型进行转换的原则

(1)具有 1:1 联系的 e-r
方法一: 将两个实体类型转换成两个独立的关系模式。在任意一端的关系模式中加入另一个关系模式的码和联系本身的属性。
方法二: 将两个实体类型转换成两个独立的关系模式。将与联系相连的两个实体的码以及联系本身的属性均转换为一个新的关系模式的属性,每个实体的码均是该关系模式的码。

例题

某医院数据库的部分关系模式为:
        科室(科密号，科家名，负责人，电话)
        病患(病历号，姓名，住址，联系电话)
        职工(职工号，职工姓名，科号，住址，联系电话)
        假设每个科农有一位负责人和一部电话: 每个科常有若于名职工，一名职T只属于一个科室。一个医生可以为多个病患看病。一个病患可以由多个医生诊治。
科室与职工的关系为( 1：n)，病患与医生的的就诊联系类型为(n:m)。

对于就诊联系最合理的设计是：就诊（病历号，职工号，就诊时间，就诊情况）

就诊关系的主键是：（病历号，职工号）

关系模型与关系代数复习题

1.关系模型的三要素

关系数据结构、关系数据操作、关系完整性约束。

(1)关系数据结构描述各种数据的基本结构形式(Table/Relation)

(2)关系数据操作描述关系与关系之间所有可能发生的各种操作(关系运算)

(3)关系完整性约束描述这些操作所应遵循的约束条件

2.定义为主码的属性列的特点

属性列的值唯一，属性列的值不为空。

3.定义外码/外键的属性列的特点

(1)属性列的值可以为空。
(2)属性列的值可以是外码所在的另一个关系中相应属性列的值。

4、关系模式的各要素

关系的描述称为关系模式，它可以形式化地表示为R(U，D，Dom，F)。

R：是关系的名称

U：是关系的元组集合（即关系中元素的集合）

D：是关系的属性集合

Dom：是属性的定义域

F：是属性之间的函数依赖关系。

5.关系操作的三大功能

数据查询、数据维护、数据控制

8.完整性约束有哪几类?

实体完整性、参照完整性、用户自定义的完整性。

其中:关系模型中，实体完整性是指实体的主键值不允许是空值。

9.并运算

对关系 R、S 进行集合运算，结果中既包含R中元组也包含S中元组，这种集合运算为并运算。

10.交运算

对关系R、S进行集合运算，结果中包含的所有元组既是R中元组也是S中元组，这种集合运算为交运算.

11.差运算

对关系 R、S进行集合运算，运算结果中由属于R而不属于S的所有元组构成，这种集合运算为差运算。

12. 关系R和S的属性个数分别为 r 和 s ,则(RXS)操作结果的属性个数为 r+s

13.关系中的5种基本操作分别是选择（ $\sigma$ ）、投影（π）、并（U）、差（-）、笛卡尔积（X）。

14.专门（特殊的）的关系运算

包括连接运算（⨝）、选择运算（σ）、投影运算（π）和除法运算（÷）

有如下关系模式，根据题意写出关系代数表达式

        学生关系 student(sno,sname,ssex，sage，sdept)，其中sno 表示学号,sname 表示姓名，ssex表示性别，sage 表示年龄，sdept 表示所在系。
        课程关系 course(cpo,cname,ccredit)，其中 cno 表示课程号，cname 表示课程名，ccredit表示学分。
        选修关系 score(sno,cno,grade),其中sno 表示学号,cno 表示课程号,grade表示成绩

(1)査询course 关系中课程名称为“数据库原理”的学分

$\pi _{ccredit}(\sigma _{cname='bookname'}(course))$

(2)查询学号为“s112”的学生姓名、所属院系、选修课程号和成绩

$\pi _{sname,sdept,cno,grade}(\sigma _{sno='s112'}(student\bowtie score))$

关系数据库理论复习题

1.INF

设R是个关系模式，如果R中的每个属性A的值域中的每个值都是不可分解的，则称R属于 INF。

2. BCNF

在关系模式 R(A,B,C)中,有函数依赖集 F={(A,B)→C,B,C)→A},则R最高达到 BCNF。

3.2NF

设有关系 R(X，Y，Z)，其函数依赖集 F={X→Y，Y→Z}，则关系 R 至多满足 2NF。

4.判断关系模式是第1范式的关键是什么?

关系模式中的所有属性都是不可分的基本数据项，则关系R满足1NF。如果有重复的组或者字段是可分的就不是第一范式了。

5.关系模式满足第2范式的要求是什么?

如果关系模式满足 1NF，且该关系模式中的每个非主属性完全函数依赖于R的某个候选码（主关键字），则该关系满足 2NF。若存在部分函数依赖则不是2NF。

完全函数依赖解释：在关系模式R中，如果存在一个依赖关系A → B，其中A是一组属性，B是非主属性，我们说B对A完全函数依赖，如果仅当A的整体被考虑时，B的值才能被唯一确定。如果去除A中的任何属性都能影响到B的确定性，那么我们称这种依赖为“完全函数依赖”。

例：设有关系模式学生（学号，所在系，系主任姓名，课程号，成绩）。。主关键字=（学号，课程名）。存在函数依赖：{（学号，课程号）→所在系，（学号，课程号）→系主任姓名，（学号，课程号）→成绩}。这个满足2NF吗？

答：

所在系和系主任姓名显然与课程号没有直接关系，这两个属性很可能只依赖于学号，而不是依赖于整个复合主键（学号，课程号）。这就意味着存在部分函数依赖：

学号 → 所在系
学号 → 系主任姓名

这两个属性不涉及到课程号，因此它们不满足完全函数依赖的要求，从而不符合2NF。

6.关系模式满足第3范式的要求是什么?

如果关系模式满足 2NF，且每个非主属性都不传递函数依赖于任何候选码，则该关系模式满足 3NF。

传递函数依赖解释：传递函数依赖是指当一个非主属性A依赖于另一个非主属性B，而这个非主属性B又依赖于候选码C，形成了A → B → C的依赖链，这种依赖关系就叫做传递函数依赖。

7.关系模式例题

有关系模式stusc (sno,sname, sage,sdept,tel,manger,cno,cname,grade)，其语义文库环境如下:

每个学生只属于一个院系，每个学生都有自己的姓名和年龄，学号是学生在校期间的唯一标识。

每个院系只有一个电话号和一名负责人。每门课程的课程号不同。每个学生可以跨学院选择多门课程，但每名学生选择的课程不允许重复，且该门课程的成绩有一个。

要求:分析其主属性和非主属性，判断是否满足2NF，如不满足，则进行优化，使其达到第2范式的要求。

答：

（1）主属性：sno，cno。非主属性：sname, sage,sdept,manger,cname,grade

（2）分析每个非主属性对主属性的函数依赖类型

函数依赖如下：

F={（sno，cno）→grade，（sno，cno）→sdept，（sno，cno）→sname

（sno，cno）→sage，（sno，cno）→cname，

sdept→tel，sdept→manger }

结论：存在部分函数依赖，非2NF关系模式，需要分解

义库
(3)解决方法

将关系模式 stusc,使用投影分解成多个关系模式。首先将存在部分函数依赖关系的部分投影分解出来，则 stusc关系模式中为的函数依赖为完全函数依赖;

course(cno，cname);

stu(sno，sname，sage，sdept，tel，manger);

stusc(sno，cno，grade)

(4)分析每个关系，是否仍然存在部分函数依赖，若存在继续分解。若不存在，则所有关系模式都符合 2NF

数据库设计复习题

1.需求分析阶段

在数据库设计中，表示用户业务流程的常用方法是 DFD（数据流图），该过程属于需求分析阶段。

2.概念结构设计阶段

在数据库的概念结构设计阶段中，最常用的模型是实体联系 E-R模型

3.逻辑结构设计阶段

在数据库设计中，将 ER 图转换成关系数据模型的过程属于逻辑结构设计阶段。

4.物理结构设计阶段

设计 DB 的存储结构属于数据库设计的物理结构设计阶段。

5.数据库设计的6个阶段。

需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行、维护。

7.概念结构设计步骤

(1)进行局部数据抽象，设计局部概念模式

(2)将局部概念模式综合成为全局概念模式

(3)评审

8.局部 E-R 图合并全局 E-R 图产生的冲突有哪几类?

属性冲突、命名冲突、结构冲突

9.将E-R 转换为关系模式，主要涉及到哪几种联系的 ER模型转换?

将 E-R图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化

存储过程示例代码

-- 例3:创建存储过程,可以实现对sdept表数据的录入
-- (1)创建存储过程:
DELIMITER//
drop procedure if exists p_insdept//
create procedure p_insdept(in_dno char(2),in_name varchar(30),
in_addr varchar(50),in_tele char(15))

BEGIN
	insert into sdept values(in_dno,in_name,in_addr,in_tele);
END;

-- (2）执行存储过程:
call p_insdept('12','电子信息学院','沿滨路6号','84233312')



-- 例4:使用存储过程录入数据时,违反主键、唯一约束,需要给出提示,代码正常结束。
-- (2)判断错误码:1062的两种情况
-- 电话和已有数据重复，违反唯一约束
insert into sdept values('O7','电子信息学院','天津路4号','045188010102')
-- 院系编号和现有数据重复，违反主码约束
insert into sdept values('05','电子信息学院','天津路4号','045188221234')
-- (2)创建存储过程，增加异常处理，并给出错误信息提示:
drop procedure if exists p_insdept;
create procedure p_insdept(in_dno char(2),in_name varchar(30),in_addr varchar(50),in_tele char(15))
	
begin
	declare exit handler for 1062 set @status='违反约束';
	insert into sdept values(in_dno,in_name,in_addr,in_tele);
end;

-- (3）调用存储过程
call p_insdept('05','外语学院','南京路10号','88881230');
call p_insdept('11','设计学院','南京路10号','88885555');
-- (4)查看存储过程中的异常值
select @status



-- 例5:创建存储过程,可以实现根据学院的名称对sdept表数据的册除操作
-- (1)创建存储过程
DELIMITER//
drop procedure if exists p_delsdept;

create procedure p_delsdept(in_name varchar(30))

BEGIN
	delete from sdept where dname=in_name;
end;

(2)执行存储过程
call p_delsdept('电子信息学院');



-- 例6:使用存储过程删除sdept表中数据,输入参数为院系名称,
-- 当该院系被其他表引用,则提示出现外键约束,代码正常结束
-- (1）判断错误码
-- 错误码: 1451，触发外键关联
delete from sdept where dname='计算机与信息工程学院';
-- (2)创建存储过程
drop procedure if exists p_delsdept;

create procedure p_delsdept(in_name varchar(30))

BEGIN
	-- declare continue handler for SQLSTATE23000 set @sta='违反外键约束';
	declare continue handler for 1451 set @sta='违反外键约束';
	delete from sdept where dname=in_name;
end;

-- (3)调用存储过程
call p_delsdept('计算机与信息工程学院');
-- (4)查看存储过程中的异常值
select @sta

索引

1.索引定义

索引是数据库管理系统中一个排序的数据结构，可以快速查询表中的记录，优化数据库查询速度，提高数据库性能。

索引指向数据库中具体数据所在位置，当用户通过索引查询数据时，不需要遍历数据库的所有数据。

2.MySQL索引类型

（1）普通索引

（2）唯一性索引

（3）全文索引

（4）单列索引

（5）多列索引

导出

例题1

将teacher 表导出至指定目录下，文件类型xls

select * from teacher into outfile 'e:/backup/teacher.xls' character set utf8

例题3

将teacher 表导出至指定目录下

select * from teacher into outfile 'e:/backup/teacher.txt'
fields
    terminated by'\t'
    enclosed by'"'
lines
    terminated by '\n'

用户管理

例题1

在MySQL服务器中添加新的用户，其用户名分别为 sysman、system，主机为 % 和 localhost，口令设置为明文：123


CREATE USER 'sysman' IDENTIFIED BY '123';
CREATE USER 'system'@'localhost' IDENTIFIED BY '123';

说明

用户名和主机名：
- 'sys'@'localhost'：这里指定了用户名为sys，并且指定localhost作为其主机名。这表示，只有从本机（运行MySQL服务的机器）连接时，该用户才能登录。localhost是一种特殊的主机名，指的是当前操作的本地机器。
设置口令：
- IDENTIFIED BY '123'：这部分用于设置用户的密码。在这个例子中，密码设置为123。
关于主机名的说明：
- %：当作为主机名时，代表任何客户端IP地址都可以尝试连接到MySQL服务器。例如，'sys'@'%'意味着任何网络位置的客户端都可以尝试以用户名sys登录。
- localhost：特指本地机器，即MySQL服务器所在的机器。只有通过该机器上的客户端程序尝试登录时，才会被允许。
关于权限校验：
- 当用户尝试登录MySQL服务器时，服务器会根据授权表中的条目（包括用户名、密码和主机名）来验证尝试登录的用户。如果提供的凭证与授权表中的相匹不符，登录尝试将被拒绝。