技能二：数据库(MySQL)·【22江苏转本笔记】

（二）技能二：数据库（MySQL）

【考查目标】
1.掌握绘制 E-R 图的方法。
2.掌握创建配置数据库。
3.掌握创建表、表间联系等约束条件的方法。
4.掌握常用的 SQL 数据操纵语言。
【考查内容】

1 数据库概述

SQL结构化查询语言，是关系数据库通用的结构化查询语言。
数据库相关概念->

1.1 根据系统需求绘制 E-R 图。

ER图的组成元素是实体、属性和联系。
实体：把客观存在并且可以相互去别的事物称为实体。实体可以是实际事物也可以是抽象事件 。
属性：把描述实体的特性称为属性。
联系：联系是实体间有意义的相互作用，即实体间存在的关联。
在ER图中，用矩形表示实体，椭圆形表示属性，菱形框表示联系。
**设计原则：**首先根据需求分析，确定实体、属性共和联系三种ER图的基本要素。原则如下：
①相对原则：建模的过程实际上是对对象抽象的过程。实体、属性和联系是对同一个对象抽象过程的不同解释和理解。在同意情况下不同的人，或同一人在不同的情况下，对事物抽象的结果可能是不同的。在ER图的整个实际过程中，实体、属性和联系会被不断地调整和优化。
②一致原则：同一对象在同一个管理系统中的不同子系统抽象的结构要求保持一致。
③简单原则：现实世界中的事物能作属性对待就尽量作为属性处理。属性与实体和联系之间并无一定界限。当属性不再具有需要进一步描述的性质（因为属性在含义上不可再分的数据项）时，或者不能再与其它实体具有联系（ER图中的联系只能是实体与实体之间的联系）就不能作实体或关系对待。
我是这么绘制ER图的：
主要分为几个步骤：立实体，标联系，添属性，消冗余。
关于ER图的概念点击→ER图

E-R图题目1

设某教学管理系统，其查询模块需要提供如下功能：
Ⅰ.查询系信息，列出各系编号、系名、系办公电话；
Ⅱ.查询某系教师的信息，列出教师号、教师名、工资和聘用日期；
Ⅲ.查询某教师讲授的课程信息，列出课程号、课程名和学分；
Ⅳ.查询某门课程的教师信息，列出教师名和职称；
Ⅴ.查询某门课程的先修课程信息，列出先修课程号和先修课程名。
系统有如下业务规则：
Ⅰ.一个系可聘用多名教师，一名教师只能受聘于一个系；
Ⅱ.一名教师可讲授多门课程，一门课程可由多名教师讲授；
Ⅲ.一门课程可以有多门先修课程，也可以没有先修课程。
（1）请根据以上查询功能与业务规则，用ER图描述该系统的概念模型。
①由题目知要要查询系、教师、课程的信息，所以有三个实体为：系、教师、课程。实体用矩形，画出三个矩形实体。
②之间的联系为：系聘用教师、教师讲授课程，课程先修课程。
再画出三个联系，并把它们连线起来。

③添属性。各个实体的属性，系的属性有系编号、系名、系办公电话。教师的属性有：由Ⅱ所述，列出教师号、教师名、工资和聘用日期，和Ⅳ所述，还有一个职称。课程的属性由Ⅲ得：课程号、课程名和学分。属性用椭圆形。
接着直接添联系，由联系Ⅰ得一个系聘用多个教师，那么在[系]到<聘用>这条线标"1"，在<聘用>到[教师]这条线标"n"。由联系Ⅱ得一个教师讲授多门课，一门课被多名教师讲授，就是多对多，即m:n，就在教师讲授课程的两条线标注m和n。由联系Ⅲ得一门课程可以有多个先修，就是1:n了。

④消冗余。可以感觉到没有需要消除的地方。上图就是画好的ER图了。

E-R图题目2

已知某教务管理系统的设计人员在需求分析阶段收集到下列原始数据表格：
教师

教师号	教师名	职称	工资	上级领导教师号
9868	王文华	教授	8000	null
9983	李一斌	副教授	6000	9868
9985	丁一	讲师	4000	9868
0783	王润泽	讲师	4000	9868
0899	欧阳丹尼	讲师	4000	9868

课程

课程号	课程名	学分	教材号	教材名	出版社名	任课教师号
C2006	计算机原理	3	11	计算机原理	清华大学出版社	9868
C2006	计算机原理	3	12	计算机原理与应用	高等教育出版社	9868
C2004	数据结构	3	13	数据结构	清华大学出版社	9868
C2010	数据库原理	3	14	数据库原理	清华大学出版社	9868
C2010	数据库原理	3	15	数据库原理与技术	高等教育出版社	9868
S3001	音乐欣赏	2	16	音乐欣赏	清华大学出版社	9983

已知该业务系统存在如下规则：
I.每个教师有唯一的教师号，每个教师号对应唯一的一名教师；
II.每门课程有唯一的课程号，每个课程号对应唯一的一门课程；
III.每本教材有唯一的教材号，每个教材号对应唯一的一本教材；
IV.每个教师最多只有一个上级领导，也可以没有上级领导；
V.一门课程仅由一名教师讲授；
VI.一本教材仅用于一门课程。

画出该系统的ER图，ER图中需给出每个实体集的属性，主码属性用下划线标识。
第一步，画出实体部分，教师、课程、教材。
第二部，画联系，教师讲授课程，课程使用教材。由课程表看出一个老师讲授多门课，一门课有多本教材。结合规则Ⅴ、Ⅵ得老师和课程为1:n，课程和教材为1:n。
第三步，添属性，将表格的属性都赋给实体。

第四步，消冗余。通过表格可以知道，职称和工资挂钩，如果不能通过职称直接查到工资，说明这个ER图没写完整。一个老师有一个职称，可以有多名老师是同一个职称，因此老师和职称是1:n的关系。由于职称还有工资属性。因此将职称变为实体。
由规则Ⅳ知道一个领导带领多个教师，领导和老师之间存在一对多的关系。存在关系就都要写出来。

关系模式转ER图

已知有如下关系模式：
R1（a，b，c），R2（c，d，a），R3（e，f），R4（a，e，g）
其中下划线的属性是主码。将上述关系模式用适当的ER图表示出来，并用下划线标注出作为实体主码的属性。
第一步，由题意可以看到，R4有两个主键为a和e，而这两个主键分别是R1和R3的主键，所以判断R4是R1和R3的关系，g就是R4的属性，从而也能得出，R1和R3之间是多对多的关系。

将属性标好。

R4和R3就画好了，接着看R1和R2。
因为R1(a,b,c)表示a推出b和c，即a可以推出c。
R2（c,d,a)表示c推出d和a，即c也可以推出a。
【主键可以推出别的属性数据，例如学号可以推出学生姓名等】
而a可以推出一个唯一的c，c也可以推出唯一的a，由此得出R1和R2是一对一关系。
R1和R2之间就相差b和d两个属性，而通过a和c两个主键就可以分别推出R1里的b属性和R2里的d属性，那么就将这两个属性分开为两个实体，为不混淆实体和属性，将实体命名为大写的B和D。
即

（ER图圆圈里的文字下划线格式怎么添加？[markdown，mermaid相关问题]）
R1的a和c都是主键，R3的e是实体主码，B的b和D的d是实体主码。(在图里的这几个字母下画出下划线)

ER图题目4

现有论文和作者两个实体，论文实体的属性包括题目、期刊名称、年份、期刊号；作者实体的属性包括姓名、单位、地址；一篇论文可以有多个作者，且每一位作者写过多篇论文，在每一篇论文中有作者的顺序号。
画出ER图。
由题意知作者和论文之间的关系为多对多(m:n)

添加属性。

因为作者发表论文的时候产生的顺序号，所以顺序号为联系的属性。
作者的主键是姓名。
论文的主键是期刊号和题目。因为确定期刊号和题目才能确定是那篇论文。
ER图转换成关系模式，指出每个关系模式的主键和外键。（主键加下划线）
论文(题目，期刊号，期刊名称，年份)
作者(姓名，单位，地址)
发表(姓名，题目，期刊号，顺序号)
其中发表中的姓名、题目、期刊号为发表的外键。

ER图题目5

设有高校选课系统，需要对学校的系信息、教师信息、课程信息、学生信息、学生选课信息进行管理。已知系（DEPT）信息包括系编号（DeptNO）、系名称（DeptName）；教师（Teacher）信息包括教师号(TNO)、教师名(Tname)、课程（Course）信息包括课程号（CNO）、课程名（CName）、课程学分(Credit)；学生(Student)信息包括学号(SNO)、学生姓名(Sname)、学生性别（Sex）。
选课系统的管理规则如下：
Ⅰ一个系可聘用多名教师，一个教师只受聘于一个系。
Ⅱ一个系可有多名学生，一个学生只属于一个系。
Ⅲ一名教师可讲授多门课程，一门课程可由多名教师讲授。
Ⅳ一名学生可选修多门课程，一名课程可被多名学生选修。
Ⅴ学生选修完课程后，可获得相应课程的成绩。
（1）构建选修课系统的ER图。（要求图中的实体集名用试卷中给出的英文名）
（2）根据所构建的ER图，设计满足3NF的关系模式，并标出每个关系模式的主码和外码。

Dept(DeptNo,DeptName)主码：DeptNo,无外码。
Teacher(TNO,TName，DeptNO)主码：TNO，外码：DeptNO。
Student（SNO,SName，Sex，DeptNO)，主码SNO,外码DeptNO。
Courese(CNO,CName,Credit)主码CNO，无外码。
T-C(TNO,CNO)主码（TNO,CNO)，外码：TNO,CNO。
S-C（SNO,CNO，成绩)主码：SNO,CNO，外码：SNO,CNO。

2 创建数据库和表

2.1 创建配置数据库。

创建数据库语法->创建数据库

-- 用CERATE DATABASE 数据库名;和CRETA SCHEMA 数据库名;是一样的。
CREATE DATABASE sjk1;
CREATE SCHEMA sjk2;
-- 创建一个数据库sjk3默认字符集为UTF8
CREATE DATABASE sjk3 CHARACTER SET=UTF8;
-- 创建一个数据库SJK4，查看是否有同名的存在，如果没有创建就创建，如果有就不创建
CREATE DATABASE IF NOT EXISTS SJK4;
-- 查看数据库
SHOW DATABASES;-- 也可以用SHOW SCHEMAS；

2.2 使用合适的数据类型创建表。

创建表的笔记->创建表的习题

数据类型

MySQL支持多种类型，大致分为三类：数值、日期/时间和字符串（字符）类型。常用的数据类型有整型、浮点型、精确小数类、二进制、日期/时间、字符串数据类型。
1.数值类型：
TINTINT、SMALLINT、DECIMAL、NUMERIC,近似数值数据类型(FLOAT,REAL,DOUBLE PRECISION).

类型	大小	范围	用途
TINYINT	1byte	-128~127	小整数值
SMALLINT	2bytes	-32768~32767	大整数值
MEDIUMINT	3bytes	-8388608,8388607	大整数值
INT或INTEGER	4个字节	若是无符号，范围是0~232;若是有符号，范围-231 ~231-1	大整数值
BIGINT	8bytes	范围是0~264,因为占8个字节，也就是2的8次方，范围就是0 ~264	极大整数值
FLOAT	4bytes
DOUBLE	8bytes
DECIMAL	对DECIMAL（M,D），M>D？M+2:D+2。	依赖于M和D的值	小数值
…	…	…	…

创建表

创建表的SQL语句是CREATE TABLE

CREATE TABLE 表名(
字段名1 数据类型 [NULL|NOT NULL],
字段名2 数据类型，
……
字段名n 数据类型
);

字段与字段之间用逗号分隔，最后的字段之后没有逗号。
NULL或NOT NULL指定是否可容纳控制，如果未指定，默认为NULL。

例如：
创建数据库dbtest1，然后创建表tbla，列cola，int型，不可为空。

CREATE DATABASE dbtest1;
use dbtest1;
CREATE TABLE tbla (
	cola int NOT NULL
);

2.3 修改表结构。

ALTER语句。
表的操作

2.4 删除表和数据库。

用DROP语句删除。

3 SQL 数据操纵语言

3.1 数据的插入、删除和修改处理。

使用INSERT语句向数据库表中插入新的数据记录。
使用基本的INSERT语句插入数据要求制定表名称和插入到心记录中的1值。基本语法格式为：

INSERT INTO table_name(column_list) VALUES(value_list);

table_name指定要插入数据的表名，coumn_list指定要插入数据的哪些列，value_list指定每个列对应插入的数据。
对了，在执行插入操作之前使用SELECT语句查看表中的数据。USE这张表。

3.2 简单查询、连接查询。

查询所有字段：

在MySQL中可以使用星号*代表所有的列，即可以查出所有的字段。

SELECT *FROM 表名;

查询指定字段：

SELECT 字段名 FROM 表名;

查询的筛选条件：

设定查询条件应用的是WHERE子句通过它可以实现很多复杂的条件查询。
在使用WHERE子句时，需要使用一些比较运算符来确定查询的条件。
①带IN关键字查询

WHERE 条件 [NOT] IN (元素1,元素2,···,元素n);

②带BETWEEN AND关键字查询
若字段的值在指定范围内，满足查询条件，记录将被查询出来。

WHERE 条件 [NOT]BETWEEN ··· AND ···;

③带LIKE的字符匹配查询
它可以实现模糊查询，有两种通配符：%和_
④带IS NULL关键字查询空值

IS [NOT]NULL

⑤带AND的多条件查询
使用AND关键字时只有同时满足所有查询条件的记录会被查询出来，如果不满足这些查询条件的其中一个，这样的记录将被排除掉。

WHERE 条件1 AND 条件2 [··· AND 条件表达式n]

⑥带OR的多条件查询

WHERE 条件1 OR 条件2 [··· OR 条件表达式n]

使用正则表达式的查询：
用某种模式去匹配一类字符串的一个方式，正则表达式的查询能力比通配字符的查询能力更强大吗而且更加的灵活。
使用REGEXP关键字来匹配查询正则表达式。

WHERE 字段名 REGEXP '匹配方式';

使用[]方括号可以将需要查询字符组成一个字符集。(只要记录中包含方括号中的任意字符，该字符将会被查询出来。)
使用*和+来匹配多个字符，+至少表示一个字符。而*可以表示0个字符。
"^“表示字符串开始的位置，”$"表示字符串的结束位置。

3.3 嵌套子查询。

一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此再写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容再一层层往外测试，增加子查询正确率，否则多层的嵌套使语句可读性很低。
子查询指一个查询语句嵌套在另一个查询语句内部的查询。子查询中常用的操作符有ANY(SOME)、ALL、IN、EXISTS。子查询可以添加到SELECT,UPDATE,DELETE语句中，而且可以进行多层嵌套。

ANY,SOME关键字子查询

ANY,SOME是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE,则返回TRUE。

EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行。如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是FALSE ，此时外层语句将不再进行查询。

IN关键字的子查询

IN关键字进行子查询是，内层查询语句仅仅返回一个数据列，这个数据列的值将提供给外层查询语句比较操作。

子查询的功能也可以通过连接查询完成，但是子查询使得MySQL代码更容易阅读和编写。

3.4 对查询结果进行排序、计算及分组。

使用DISTINCT可以去除查询结果中的重复记录。

SELECT DISTINCT 字段名 FROM 表名;

DESC降序

使用ORDER BY关键字对查询的结果进行排列。
默认情况下ORDER BY按升序输出结果，用DESC来实现降序排列。

ORDER BY 字段名[ASC|DESC];

LIMIT

限制查询结果的数量，控制它输出的行数。

3.5 求和、统计、求平均等常用函数在查询操作中的应用。

聚合函数常见的有sum(求和)、count(*)记录数、max最大值、min最小值。

求和sum

求该表中的该列中的数据的和。

SELECT SUM(列名) FROM [表名];

统计count(*)

COUNT()：对表中记录进行统计,不管表字段中包含的是NULL值还是非NULL值。
COUNT(字段名):对指定字段的记录进行统计,在具体统计时忽略NULL值。

统计该表中的该字段的数据个数

SELECT COUNT(列名) FROM [表名];

求平均AVG

对指定字段的平均值进行计算,在具体统计时忽略NULL值。

SELECT AVG(列名) FROM [表名];

等常用函数

最大值MAX，最小值MIN。

4 视图和索引

视图是一个虚拟表，是从数据库中一个活多个表中导出来的表。
通过视图看到的数据只是存放在基本表中的数据，对视图的操作与对标的操作一样，可以对其进行查询修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表达数据发生变化，则这种变化也可以自动反映到视图中。
视图的作用：简单、安全、逻辑数据独立性。

4.1 创建和查看视图。

视图中包含了SELECT查询的结果，因此视图的创建基于SELECT语句和已存在的数据表。视图可以建立在一张表上，也可以建立在多张表上。
使用SHOW CREATE VIEW view-name;来查看创建视图的语句。

查看创建视图的权限。

创建视图需要有CREATE VIEW的权限。同时应该具有查询设计的列的SELECT的权限。使用SELECT语句来查询这些权限信息。
查询语法如下：

SELECT Selete_priv.Create_view_priv FROM mysql.user WHERE user='用户名';

菜单：其他-视图-创建视图。
创建视图使用CREATE VIEW语句。
CRETE VIEW 视图名;

创建视图注意事项

①不能包括COMPUTER、COMPUTER BY语句
②不能有ORDER BY语句（除非使用TOP关键字）
③不能有OPTION关键字
④不能有INTO关键字
⑤不能使用临时表、表变量

查看视图

①DESCRIBE语句查看视图基本信息。

DESC 视图名;

②SHOW TABLE STATUS LIKE语句查看视图的基本信息

SHOW TABLE STATUS LIKE '视图名';

③SHOW CREATE VIEW 语句查看视图。

SHOW CREATE VIEW 视图名;

④在views表中查看视图详细信息。

USE Information_schema;
SELECT *FROM views;

4.2 用视图检索和修改基本表中数据。

和操作表一样，将table换成view。

4.3 创建索引。

索引是一种将数据库中单列或者多列的值进行排序的结构。用户创建的索引指向数据库中具体数据所在位置。
索引特点：当用户通过索引查询数据库中的个数据时，不需要遍历所有数据库中的所有数据。用户通过索引查询数据，不但可以提高查询速度，也可以降低服务器的负载。整体来说，索引可以提高查询的速度但是会影响用户操作数据库的插入操作。
索引分类：
①普通索引：最基本的索引。
②唯一索引：索引列值不能重复
③主键索引：建立主键时自动创建，索引列值不能重复或为空。
④聚簇索引：索引的顺序就是数据存储的物理存储顺序，一个表中只能有一个聚簇索引。
⑤全文索引：只能创建在数据类型为varchar或text的列上，可以帮助进行全文查找。
⑥空间索引：只能建立在空间数据类型上。
可以通过几种不同的方式创建索引，包括在创建表时创建索引，在已经创建的表中创建索引和修改表结构创建索引。
创建表时创建索引：

CREATE TABLE table_name(
属性名 数据类型 约束条件,
···
[PRIMARY KEY|UNIQUE|FULLTEXT|SPATIAL] INDEX KEY
[别名](属性名 [(长度)])[ASC|DESC]
);

修改表添加索引：
ALTER TABLE
ADD
查看索引：

SHOW INDEX FROM 表名;

删除索引：
已经被创建且不常用的索引，一方面可能会占用系统资源，另一方面也可能导致更新速度下降，这极大地影响了数据表地性能。

DROP INDEX index_name ON table_name;