项目２诚信管理论坛数据库设计与实现-人邮教育社区

职业能力目标和学习要求

在企业信息化系统中，数据库具有十分重要的地位，它是系统正常运行的基础，因此数据库设计是应用程序设计中最为关键的一项任务。MySQL是目前非常流行的、开源的、小型关系型数据库管理系统，在许多Web类型的项目开发中有着广泛的应用。通过本模块的学习，可以培养以下职业能力并完成相应的学习要求。

职业能力目标：

能进行MySQL数据库的设计与管理。
培养项目思路，提高动手操作能力。
促进学生形成工程化的思维习惯：自顶向下、逐步精化。

学习要求：

解数据建模与设计。
掌握MySQL数据库的管理。
了解MySQL数据库编程。

项目导入

2.1　诚信管理论坛数据库设计

数据库设计的主要目的是将现实世界中的事物及联系用数据模型描述，是应用程序设计中最为关键的一项任务。本节将介绍以下主要内容。

数据库建模技术。
数据库概念模型设计。
数据库物理模型设计。

任务

请根据诚信管理论坛的需求，完成以下任务。
（1）设计诚信管理论坛的概念模型。
（2）设计诚信管理论坛的物理模型。

2.1.1　数据库建模技术

2.1.2　数据库概念模型设计

练一练

1．熟悉PowerDesigner环境。
2．某图书馆借阅管理数据库要求提供以下服务。
（1）可随时查询书库中现有书籍的品种、数量与存放位置，所有书籍均可由书号唯一标识。
（2）可随时查询书籍借还情况，包括借书人单位、姓名、借书证号、借书日期和还书日期。规定：任何人可借多种书，任何一种书可为多个人所借，借书证号具有惟一性。
（3）当需要时，可通过数据库中保存的出版社的电报编号、电话、邮编及地址等信息向相应出版社增购有关书籍。注意：一个出版社可出版多种书籍，同一本书仅为一个出版社出版，出版社名具有唯一性。
请根据以上描述，建立图书管理系统的概念模型。

2.1.3　数据库物理模型设计

任务解决

示例2.1.2　完成诚信管理论坛系统的物理模型，通常可以分为以下3步：
（1）将实体转化为表；
（2）标识表中的主键；
（3）建立表的主外键。本例将演示如何使用PowerDesign建立系统的物理模型。
一般情况下，可以给E-R图中的每一个实体建立一张表，表名就是实体名，表的属性就是实体的属性。在PowerDesign中建立对应的物理模型，可以采用逐个实体转换的方式，其操作方式类似于创建概念模型，但在已经拥有概念模型的情况下，PowerDesign提供了一种自己生成物理模型的方式，其操作步骤如下。
① 选择“Tools”（工具）菜单下的“Generate Physical Data Model …”（生成物理模型），弹出“PDM Generation Options”（PDM生成选项）窗口，在“General”选项卡中，选择需要生成的物理模型所对应的DBMS，本例中采用MySQL 5.0做为数据库管理系统，其余采用默认选项即可，如图2.1.14所示，单击“确定”按钮，即可生成新的物理模型。

图像说明文字

② 修改列名和关系名。由于在自动转换时，PowerDesign会自动将实体转换成表，为转换成物理模型后便于编码，因此需要将采用中文名的实体名称和代码改为英文，其具体映射关系如表2.1.1~表2.1.5所示。

图像说明文字

完成后的物理模型如图2.1.15所示。

图像说明文字

③ 更新概念模型。使用Powerdesign工具可以非常方便地维护系统的概念模型和物理模型，在对物理模型进行修改后，可以选择“Tools”（工具）菜单下的“Generate Conceptual Data Model”（生成概念模型），弹出“CDM Generation Options”（CDM生成选项）窗口，在“General”选项卡中，会自动选择“update existing Conceptual Data Model”（更新已有概念模型)，单击“确定”按钮，即可更新对应的概念模型，如图2.1.16所示。

图像说明文字

④ 生成数据库。使用PowerDesign还可以非常方便地生成最终数据库的创建脚本，通常选择菜单“DataBase”下的“Generate Database”命令，即可完成相关任务。由于本书还将逐步介绍MySQL下的各类DDL和DML语句，这里就不再详述，读者可自行尝试。

练一练

使用PowerDesign完成图书馆借阅系统数据库的物理模型。

2.2　诚信管理论坛数据库实现

数据库是存储数据对象的容器，包括表、约束、视图、触发器、存储过程等。其中，表是最基本的数据对象，用于存放数据。约束是保持数据库完整性的一种方法，定义了可输入表或表的单个字段中的数据限制条件。本节将介绍以下主要内容。

创建项目数据库。
创建项目数据表。
创建数据约束。

任务

请根据诚信管理论坛的物理模型，完成以下任务。
（1）创建诚信管理论坛的数据库。
（2）创建诚信管理论坛的数据表。
（3）创建诚信管理论坛的数据表约束。

2.2.1　创建项目数据库

练一练

1．列出当前系统中的全部数据库。
2．创建诚信管理论坛系统数据库CXBBS。
3．使用诚信管理论坛系统数据库CXBBS。
4．修改诚信管理论坛系统数据库CXBBS的默认字符集和校对规则。

任务解决

示例2.2.1 显示当前系统中的全部数据库。
（1）打开MySQL控制台。在屏幕下方的任务栏区域，单击WAMPSERVER图标，在弹出的菜单中，选择“MySQL”菜单下的“MySQL控制台（m）”，打开MySQL控制台，如图2.2.1所示。
（2）输入MySQL用户密码。在输入密码提示行中输入MySQL用户密码，连接到MySQL数据库，如图2.2.2所示。
（3）查看数据库列表。在命令行提示符处，输入“SHOW DATABASES;”命令，查看系统中的数据库列表，如图2.2.3所示。

图像说明文字

注意：show databases命令中database后面要加“s”，每条命令需以“;”结束。

在MySQL中有3个系统默认数据库，分别是mysql、informationshcema和performance shema。（4）筛选数据。SHOW DATABASES命令可以后接LIKE或WHERE子句，从而实现对数据的过滤。例如，只显示含有“m”的数据库名称，可以使用以下命令。
SHOW DATABASES LIKE '%m%';
也可以使用以下命令。
SHOW DATABASES WHERE 'Database' like '%m%';
示例2.2.2 创建诚信管理论坛系统数据库CXBBS。
在MySQL控制台窗口中，输入“CREATE DATABASE CXBBS”，其结果如图2.2.4所示。

图像说明文字

示例2.2.3 使用诚信管理论坛系统数据库CXBBS。
（1）在MySQL控制台窗口中，输入“SELECT DATABASE()；”，查看默认数据库，其结果如图2.2.5所示。

图像说明文字

（2）在MySQL控制台窗口中，输入“USE CXBBS；”，将CXBBS设为默认数据库，其结果如图2.2.6所示。

图像说明文字

示例2.2.4 修改诚信管理论坛系统数据库CXBBS的默认字符集和校对规则。
在MySQL控制台窗口中，输入以下命令，将CXBBS数据库的默认字符集设为utf8，校对规则设为utf8_general_ci，其结果如图2.2.7所示。
ALTER DATABASE cxbbs
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

图像说明文字

2.2.2　创建项目数据表

练一练

1．创建诚信管理论坛系统数据库中的用户表。
2．将诚信管理论坛系统数据库中的用户表的用户编号字段设为自动增长列。
3．删除诚信管理论坛系统数据库中的用户表。

任务解决

示例2.2.5 创建诚信管理论坛系统数据库中的用户表。
用户表的结构见表2.1.1，在MySQL控制台窗口中，输入以下命令，创建用户表，其结果如图2.2.8所示。
CREATE TABLE tbl_user (
uId int(11),
uName varchar(20) ,
uPass varchar(20) ,
head varchar(50) ,
regTime timestamp ,
gender smallint(6) ) ;

图像说明文字

本例中创建的用户表仅定义了最基本的字段名和字段类型，而关于列的为空性和约束并没有给出，实际上可以通过“列定义”对列的为空性和约束进行描述。在MySQL中列定义的语法格式如下。

图像说明文字

说明：

col_name：表中列的名称。列名必须符合标识符的命名规则，长度不能超过64个字符，且在表中要唯一。如果有MySQL保留字必须用单引号括起来。
type：列的数据类型，有的数据类型需要指明长度n，并用括号括起，MySQL支持的数据类型见表2.2.1。
AUTO_INCREMENT：设置自增属性，只有整型列才能设置此属性。当插入NULL值或0到一个AUTO_INCREMENT列中时，列被设置为value+1，在这里，value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。
NOT NULL | NULL：指定该列是否允许为空。如果不指定，则默认为NULL。
DEFAULT default_value：为列指定默认值，默认值必须为一个常数。其中，BLOB和TEXT列不能被赋予默认值。如果没有为列指定默认值，MySQL自动分配一个。如果列可以取NULL值，默认值就是NULL。如果列被声明为NOT NULL，则默认值取决于列类型。

① 对于没有声明AUTO_INCREMENT属性的数字类型，默认值是0。对于一个AUTO_INCREMENT列，其默认值是在顺序中的下一个值。
② 对于除TIMESTAMP以外的日期和时间类型，默认值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，默认值是当前的日期和时间。
③ 对于字符串类型，默认值是空字符串。对于ENUM，默认值是第一个枚举值。

UNIQUE KEY | PRIMARY KEY：PRIMARY KEY和UNIQUE KEY都表示字段中的值是唯一的。PRIMARY KEY表示设置为主键，一个表只能定义一个主键，主键一定要为NOT NULL。
COMMENT 'string'：对列的描述，string是描述的内容。
reference_definition：指定外键所引用的表和列。

因此，带有完整列定义的创建用户表的SQL语句如下所示。

图像说明文字

通常情况下，设置好列定义，即可较好地完成数据表的创建工作，不过MySQL中还提供了表选项，以更好地完成表的创建，其语法格式如下。

图像说明文字

说明：
表中大多数的选项涉及的是表数据如何存储及存储在何处。多数情况下，不必指定表选项。ENGINE选项是定义表的存储引擎，存储引擎负责管理数据存储和MySQL的索引。目前使用最多的存储引擎是MyISAM和InnoDB。

MyISAM引擎是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。在MyISAM中，一个table实际保存为3个文件：.frm存储表定义、.MYD存储数据和.MYI存储索引。
InnoDB是一种支持事务的引擎。所有的数据存储在一个或者多个数据文件中，支持类似于Oracle的锁机制。一般在OLTP应用中使用较广泛。如果没有指定InnoDB配置选项，MySQL将在MySQL数据目录下创建一个名为ibdata1的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的日志文件。

因此，创建用户表的完整SQL语句如下所示。

图像说明文字

示例2.2.6 将示例2.2.5所建表的UID列设为自动增长列在MySQL控制台窗口中，输入以下命令，修改用户表中UID列的定义，其结果如图2.2.9所示。
ALTER TABLE tbl_user MODIFY COLUMN uid int(11) NOT NULL
AUTO_INCREMENT PRIMARY KEY;

图像说明文字

示例2.2.7 删除示例2.2.5所创建的tbl_user表
在MySQL控制台窗口中，输入以下命令，修改用户表中UID列的定义，其结果如图2.2.10所示。
DROP TABLE IF EXISTS 'tbl_user';

图像说明文字

2.2.3　创建数据约束

练一练

1．简述5种约束的实现方式。
2．完成电子商务网站数据库的创建。
3．创建下列某图书管理系统的数据库、表和约束。
模拟开发适合学校使用的小型图书管理系统的数据库BookManagement，需要建立5个关系表，分别为图书明细表、图书种类表、读者明细表、借阅记录表、罚款记录表。
图书种类表（Booktype）：记录所有的图书种类，如历史、政治，每条记录代表一种类型的图书，具体见表2.2.2。
图书明细表（BookInfo）：用途为记录所有图书的信息，每条记录代表一本图书，包括库存册数、借出数随借书、还书的行为而改变，具体见表2.2.3。
读者明细表(Readinfo)：记录所有读者的信息，每条记录代表一个读者，具体见表2.2.4。
借阅记录表（borrowinfo）：记录所有的借阅记录信息，每条记录代表一个读者借阅了一本书，借阅图书时如果这个读者借了同一本书而且还没归还，就不允许再借这本书，具体见表2.2.5。
罚款记录表（Fineinfo）：记录所有的罚款记录信息，每条记录代表一个读者借阅了一本书超出有效期后，将按照每天0.1元的金额进行罚款，具体见表2.2.6。

图像说明文字

任务解决

示例2.2.8 MySQL中的外键约束示例
（1）在MySQL控制台窗口中，输入以下命令，创建USER表。为简化操作，该表中仅定义一个uid列，表示用户编号，将其设为主键，同时注意使用Innodb存储引擎进行存储。

图像说明文字

（2）在MySQL控制台窗口中，输入以下命令，创建TOPIC表。为简化操作，该表中仅定义一个tid列，表示帖子编号，将其设为主键，同时使用uid列记录发帖人，将该列设为外键，引用USER表中的uid列。注意使用Innodb存储引擎进行存储。

图像说明文字

（3）在MySQL控制台窗口中，输入以下命令，在USER表中插入1条记录。

图像说明文字

（4）在MySQL控制台窗口中，输入以下命令，在TOPIC表中插入1条记录，表示有1个用户发帖。

图像说明文字

请注意，由于已将TOPIC表与USER表建立了外键关联，因此要求帖子表中的用户必须是用户表中已有的用户，如不是，则报错。所有操作运行结果如图2.2.11所示。

图像说明文字

示例2.2.9 完成诚信管理论坛中用户表、版块表和帖子表的创建，并建立外键关联。
（1）创建用户表并将UID设为主键，其SQL语句如下所示。

图像说明文字

（2）创建版块表，并将版块编号设为主键，其SQL语句如下。

图像说明文字

（3）创建帖子表，并将帖子编号设为主键，同时建立与用户表和版块表的关联关系，其SQL语句如下。

图像说明文字

2.3　诚信管理论坛数据库编程与管理

数据库创建完成后，最重要的工作就是在数据库中查询需要的数据，并对数据进行增加、修改和删除操作，为了便于高效率的工作，有时还需要利用存储过程和触发器来实现业务操作。本节将介绍以下主要内容。

数据管理语句。
数据查询语句。
存储过程和触发器。

2.3.1　数据管理语句

练一练

1．请在用户表中插入以下数据。

图像说明文字

2．删除上述用户表中的数据。

任务解决

示例2.3.1 向用户表中插入一条数据，其值如下。

图像说明文字

在MySQL中还提供了INSERT语句另一种形式，可以使用SET子句来逐列指明所插入的值，其语法格式如下：

图像说明文字

因此示例2.3.1的插入语句也可以写成如下形式。

图像说明文字

示例2.3.2 将用户表中uId为1的用户性别修改为男性。

图像说明文字

示例2.3.3 将用户表中uId为3的记录删除。

图像说明文字

2.3.2　数据查询语句

任务解决

示例2.3.4 查询发帖用户的用户名、性别和注册时间。
SELECT uName, gender, regtime FROM tbl_user;
其结果如图2.3.2所示。
示例2.3.5 查询用户表中的全体用户的详细信息。
SELECT * FROM tbl_user;
其结果如图2.3.3所示。

图像说明文字

示例2.3.6 使用别名显示用户的用户名、性别和注册时间。
SELECT uName '用户名', gender AS '性别', regtime AS '注册时间' FROM tbl_user;
其结果如图2.3.4所示。

图像说明文字

示例2.3.7 给论坛用户统一增加“cx”前缀。
SELECT CONCAT('cx',uName) username FROM tbl_user ;

说明：
CONCAT函数用于连接字符串，MySQL不支持字符串的“+”运算符，其结果如图2.3.5所示。

图像说明文字

示例2.3.8 显示用户表中的用户名和性别，要求将性别的查询结果替换为“男”或“女”。

图像说明文字

其结果如图2.3.6所示。
示例2.3.9 显示用户表中用户名不同的用户。

图像说明文字

示例2.3.10 显示用户表的用户数。
SELECT COUNT(*) FROM tbl_user;
其结果如图2.3.8所示。

示例2.3.11 显示帖子的标题和发帖人的名称。
SELECT uName,title
FROM tbl_user AS u,tbl_topic AS t
WHERE u.uId=t.uId;
其结果如图2.3.9所示。

示例2.3.12 使用内连接显示某个帖子的发帖人和标题。
SELECT uName,title
FROM tbl_topic t INNER JOIN tbl_user u ON t.uId = u.uId;
其结果如图2.3.10所示。
示例2.3.13 显示所有版块的父版块的名称。
SELECT a.boardName AS '版块名',b.boardName AS '父版块名'
FROM tbl_board AS a JOIN tbl_board AS b ON a.parentId = b.boardId;
其结果如图2.3.11所示。

图像说明文字

如果要连接的表中有同名的列，并且连接条件就是列名相同，那么ON条件也可以换成USING子句，如示例2.3.9也可以写成以下形式。
SELECT uName,title
FROM tbl_topic JOIN tbl_user
USING (uId);

示例2.3.14 显示版块名中含有“Java”字符的版块。
SELECT * FROM tbl_board
WHERE boardName LIKE '%java%';

其结果如图2.3.12所示。

示例2.3.15 显示版块名中第2个字符为“a”的版块。
SELECT * FROM tbl_board
WHERE boardName LIKE '_a%';
其结果如图2.3.13所示。

图像说明文字

示例2.3.16 显示诚信管理论坛中2011年3月注册的全部用户。
SELECT * FROM tbl_user
WHERE regTime BETWEEN '2011-03-01' AND '2011-04-01'; 其结果如图2.3.14所示。

示例2.3.17 显示诚信管理论坛中Java技术和.NET技术版块下的所有子版块。

说明：
.NET技术版块的编号为1，java技术的版块编号为2。

SELECT * FROM tbl_board
WHERE parentId IN (1,2);
其结果如图2.3.15所示。

图像说明文字

示例2.3.18 列出诚信管理论坛中用户名为“qq”的用户发过帖子的全部版块名称。

图像说明文字

此例中，首先执行内部查询
SELECT boardId FROM tbl_topic t,tbl_user u
WHERE t.uId=u.uId AND u.uName='qq'
子查询从帖子表中首先查出qq用户曾发过帖子的全部版块编号；然后再执行外部查询SELECT boardName FROM tbl_board WHERE boardId IN（内查询的值）语句查询出最后结果。其结果如图2.3.16所示。

示例2.3.19 统计帖子表中各版块的发帖数量。
SELECT boardId,COUNT(topicId)
FROM tbl_topic
GROUP BY boardId;
其结果如图2.3.17所示。

示例2.3.20 显示发帖数量超过1条的全部版块。
SELECT boardId,COUNT(topicId) as '发帖量'
FROM tbl_topic
GROUP BY boardId
HAVING COUNT(topicId)>1;
其结果如图2.3.18所示。

图像说明文字

示例2.3.21 按注册时间降序显示用户表的数据。 SELECT * FROM tbl_user ORDER BY regTime DESC;

当对空值排序时，ORDER BY子句将空值作为最小值对待，按升序排列时将空值放在最上方，降序放在最下方。其结果如图2.3.19所示。

示例2.3.22 显示最新注册的3个用户。
SELECT * FROM tbl_user
ORDER BY regTime DESC
LIMIT 3;
其结果如图2.3.20所示。

图像说明文字

示例2.3.23 显示C#语言版块和JAVA基础版块下的全部帖子（使用UNION语句实现）。

说明：
C#语言版块的版块编号为5，JAVA基础版块的版块编号为8。

SELECT topicId,boardId,publishTime,title FROM tbl_topic
WHERE boardId=5
UNION SELECT topicId,boardId,publishTime,title FROM tbl_topic
WHERE boardId=8;
其结果如图2.3.21所示。

图像说明文字

2.4　实践习题

1．INSERT语句和REPLACE语句有何不同，请设计相关试验。
2．简述DELETE语句和TRUNCATE语句的区别。
3．说明SELECT各子句的作用。
4．简述视图的作用与特点。
5．可以使用（　　）语句创建数据库。
A．NEW　DATABASE
B．BUILD　DATABASE
C．INSERT　DATABASE
D．CREATE　DATABASE
6．使用SQL语句进行分组检索时，为了去掉不满足条件的分组，应当（　　）。
A．使用WHERE子句
B．在GROUP BY后面使用HAVING子句
C．先使用WHERE子句，再使用HAVING子句
D．先使用HAVING子句，再使用WHERE子句
7．假定有以下企业管理的员工管理数据库，数据库名为YGGL，其中主要有如表2.3.2～表2.3.7所示的表及其样本数据。

图像说明文字

请完成以下操作。
（1）创建数据库YGGL。
（2）创建员工信息表、部门表和薪水表。
（3）插入各表的样本数据。
（4）在员工信息表和薪水表中，删除员工编号为“000001”的数据。
（5）将员工编号为“020018”的记录的部门号改为4。
（6）根据员工信息样本数据表和薪水样本数据表，重新插入员工编号为“000001”的数据。
（7）使用REPLACE语句向部门插入一条数据，其部门号为“1”，名称为“广告部”，备注为“负责广告业务”。
（8）将员工号为011112的职工收入改为2890。
（9）将所有职工收入增加100。
（10）删除所有收入大于2500的员工信息。
（11）查询员工表中的全部记录。
（12）查询每个员工的地址和电话。
（13）查询员工编号为“000001”的员工地址和电话。
（14）查询月收入高于2000的员工号码。
（15）查询1970年以后出生的员工姓名和地址。
（16）查询财务部所有的员工编号和姓名。
（17）查询所有女员工的地址和电话，需指定显示的列标题为姓名、地址、电话。
（18）查询所有男员工的姓名和出生日期，要求各标题用中文表示。
（19）获得员工总数。
（20）计算所有员工收入的平均数。
（21）获得员工信息表中最大的员工编号。
（22）计算所有员工的总支出。
（23）找出所有姓王的员工的部门编号。
（24）找出员工编号中倒数第2个数字为0的员工的姓名、地址和学历。
（25）找出所有收入在2000～3000的员工编号。
（26）找出所有在部门编号为“1”或“2”中工作的员工编号。
（27）查找在财务部工作的员工信息。
（28）查找所有收入在2500以下的员工信息。
（29）查找财务部中比所有研发部员工年龄都大的员工信息。
（30）查找研发部中比所有财务部员工收入都高的员工信息
（31）显示所有员工的基本信息和薪水情况。
（32）显示每个员工的基本信息及其工作的部门信息。
（33）显示“王林”所在的部门名称。
（34）统计员工信息表中男性和女性的人数。
（35）按部门列出部门的员工人数。
（36）按员工学历分组，列出本科、大专和硕士的人数。
（37）显示员工数超过2人的部门名称和部门人数。
（38）按员工收入从多到少显示员工的基本信息和收入。
（39）按员工的出生日期从小到大显示员工信息。
（40）显示收入最高的5位员工的基本信息和收入。
（41）创建存储过程，要求当一个员工的工作年份大于6年时，将其转到经理办公室工作。
（42）创建触发器，要求当员工信息表中删除一个员工信息时，同时删除员工薪水表中对应的薪水信息。

2.5　项目总结

本项目介绍数据库的设计与实现，重点介绍了数据库的建模技术和使用MySQL进行数据库管理的相关知识，主要内容如下。
1．数据库建模技术
数据库设计的主要目的是将现实世界中的事物及联系用数据模型（Data Model，DM）描述，即信息数据化。数据库建模主要包括建立数据库的概念模型和物理模型。概念模型设计的主要工作是设计数据库的实体-关系模型，物理模型设计的主要任务是将实体-关系模型转换为关系数据库模型。
2．数据库的实现
主要介绍了数据库管理的基本操作，包括查看、创建、使用、修改、删除操作，同时介绍了对数据表管理的基本操作，包括查看、创建、修改、重命名、删除操作，最后介绍了“NOT NULL”、缺省值、Unique约束、主键约束和外键约束等5种数据约束的创建。
3．数据库管理与编程
主要介绍了INSERT、REPLACE、UPDATE、DELETE、TRUNCATE等用于数据插入、修改与删除的基本命令，详细介绍了SELECT查询语句，最后对存储过程和触发器的创建和使用进行了介绍。

2.6　专业术语

Data Model（DM）：数据模型，就是现实世界的模拟，通常数据模型分成两大类：一类是按用户的观点来对数据和信息建模，称为概念模型或信息模型；另一类是按计算机系统的观点对数据建模，主要包括网状模型、层次模型、关系模型等。
实体（Entity）：客观存在并可以区分的事物。
属性（Attribute）：实体所具有的某一特性称。
码（Key）：能唯一标识实体的属性的集合。
关系（Relation）：实体集合间存在的相互联系。
E-R图：实体-关系模型图， E-R模型是由Peter Chen在1976年引入的，它使用实体（Entity）和关系（Relation）来模拟现实世界，E-R图可以将数据库的全局逻辑结构图形化表示，它是从计算机角度出发来对数据进行建模。
主键（Primary Key）：是实体中的一个或多个属性，它的值用于唯一标识一个实体对象。

2.7　拓展提升

存储过程和触发器
自MySQL5.0开始，MySQL开始支持存储过程、存储函数、触发器和事件。也就是说，MySQL的版本要在5.0以上才能创建上述对象。本节将讨论其中的存储过程和触发器。
1．存储过程
存储过程最初是为了提高数据库在网络中的处理性能而提出的，但今天，存储过程已经是现代关系数据库的重要特性之一，绝大多数企业级DBMS产品都提供了存储过程性能。存储过程具有高速、可重用、减少网络拥挤、安全等优点。
存储过程也是数据库对象之一，其实质上是一段事先定义好的代码，由声明式的SQL语句(如CREATE、UPDATE和SELECT等语句)和过程式SQL语句（如IF THEN ELSE语句）组成，可以由程序、触发器或者另一个存储过程来调用，执行代码段中的SQL语句。
（1）创建存储过程。
创建存储过程可以使用CREATE PROCEDURE语句，要在MySQL 5.1中创建存储过程，必须具有相应权限，其语法格式如下。

图像说明文字

说明：

sp_name：存储过程的名称，默认在当前数据库中创建。需要在特定数据库中创建存储过程时，要在名称前面加上数据库的名称，格式为db_name.sp_name。值得注意的是，这个名称应当尽量避免与MySQL的内置函数同名，否则会发生错误。
proc_parameter：存储过程的参数，param_narne为参数名，type为参数的类型，当有多个参数时中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持3种类型的参数：输入参数、输出参数和输入/输出参数，关键字分别是IN、OUT和INOUT。如果需要向存储过程传递数据可使用输入参数，如果需要从存储过程中返回数据则可使用输出参数，而输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数，但名称后面的括号不可省略。要注意的是：参数的名称不能与列的名称相等，否则虽然不会返回出错消息，但存储过程中的SQL语句会将参数名看做列名，从而引发不可预知的结果。
routing-body：表示存储过程的主体部分，也叫做存储过程体，里面包含了在过程调用时必须执行的语句，这个部分总是由BEGIN开始，以END结束，当存储过程中只有一条SQL语句时，可省略BEGIN-END。
Characteristic：表示存储过程的特征，感兴趣的读者可自行参考相关资料。

在开始创建存储过程之前，先介绍一个很实用的命令，即DELIMITER。在MySQL中，服务器处理语句时是以分号为结束标志的。但在创建存储过程时，由于存储过程中可能包含多条SQL语句，而每个SQL语句都是以分号为结尾的，这时服务器处理程序时遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。DELIMITER的格式如下。
DELIMITER <结束标志>
结束标志是用户自定义的特殊字符，如“##”，但要注意要避免使用反斜杠(“\”)字符，因为它是MySQL的转义字符。

示例2.7.1 存储过程示例。

图像说明文字

说明：
该存储过程首先在用户表中插入一条用户编号为10的用户，随即将其显示出来，然后删除该记录。其结果如图2.7.1所示。

图像说明文字

要想查看数据库中的存储过程列表，可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息，可使用SHOW CREATE PROCEDURE sp_name，其中sp_name是存储过程的名称。
（2）执行存储过程。
存储过程创建完成后，可以在程序、触发器或者存储过程中被调用，但是都必须使用CALL语句，其语法格式如下。

CALL sp_name ([parameter[,...]])

说明：
sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数，其个数和类型取决于要调用的存储过程。

示例2.7.2 执行示例2.7.1所创建的存储过程。

图像说明文字

说明：
首先显示用户表中的数据，其中不存在用户编号为10的用户。然后执行存储过程，显示了uId为10的用户，再查看用户表，可以发现该用户已经被删除了，证明该存储过程已成功执行。其结果如图2.7.2所示。

（3）修改存储过程。
使用ALTER PROCEDURE语句可以修改存储过程，其语法格式如下。
ALTER PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
（4）删除存储过程。
存储过程创建后需要将其删除时使用DROP PROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行，其语法格式如下。

图像说明文字

DROP PROCEDURE [IF EXISTS] sp_name

说明：
sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL中特有的功能，如果存储过程存在则删除，否则不执行该语句，使用该子句，可防止错误出现。

示例2.7.3 删除示例2.7.3创建的存储过程。

图像说明文字

其结果如图2.7.3所示。

图像说明文字

（5）带参数的存储过程。
带参数的存储过程在实际开发中有着非常广泛的应用，下面给出一个带参数的存储过程示例。

示例2.6.4 带参数的存储过程。

图像说明文字

说明：
本例定义了一个用于判断是否是休眠用户的存储过程，休眠用户的定义是：如果一个用户没有发过任何帖子，则为休眠用户，否则为活跃用户。
第01行改变命令结束标记为“##”。
第02行创建存储过程，请注意这里定义了两个参数_uId与RET，其中_uId为输入参数，RET为输出参数。
第03行为存储过程的开始标记。
第04行定义了局部变量count，用于保存用户的发帖数量。
第05行统计指定用户的发帖量，并保存到count中。
第06～10行根据得到的用户发帖量，判断是否是休眠用户。
第11行为存储过程的结束标记。
第12行将命令结束标记修改为“;”。

要运行上述存储过程，可执行以下命令。
CALL procIsSleepUser(5,@RET);
SELECT @RET;
@RET表示用户变量，用于获取输出参数，整个示例的运行结果如图2.7.4所示。

图像说明文字

2．触发器
触发器是一种特殊的存储过程，主要用于确保复杂的业务规则和实施数据完整性。触发器不像一般的存储过程，不可以使用存储过程的名称来调用或执行。当用户对指定的表进行修改(包括插入、删除或更新)时，设置于该表的触发器被自动触发。目前MySQL触发器的功能还不够全面，在以后的版本中将得到改进。
（1）创建触发器。
创建触发器可以使用CREATE TRIGGER语句，要在MySQL 5.1中创建存储过程，必须具有相应权限，其语法格式如下。

图像说明文字

说明：

trigger_name：触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。
trigger_timer：触发器触发的时刻，有两个选项：AFTER和BEFORE，以表示触发器是在激活它的语句之前还是之后触发。如果要在激活触发器的语句执行之后触发，通常使用AFTER选项；如果想要验证新数据是否满足使用的限制，则使用BEFORE选项。
trigger_event：触发事件，指明激活触发程序的语句类型。其值可以是以下几种：
INSERT：插入数据时触发，如INSERT、LOAD DATA和REPLACE语句。
UPDATE：修改数据时触发，如UPDATE语句。
DELETE：删除数据时触发，如DELETE和REPLACE语句。
tbl_name：与触发器相关的表名，只有该表的相应事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于某表，不能有两个BEFORE UPDATE触发器，但可以有1个BEFORE UPDATEE触发器和1个BEFORE INSERT触发器，或1个BEFORE UPDATE触发器和1个AFTER UPDATE触发器。
FOR EACH ROW：这个声明用来指定，对于受触发事件影响的每一条记录，都要激活触发器的动作。例如，使用一条语句向一个表中添加一组记录，触发器会对每一条记录都执行相应的触发器动作。
trigger_stmt：触发器语句，包含触发器激活时将要执行的语句。如果要执行多个语句可使用BEGIN END复合语句结构。

在触发器中不可避免地要引用关联表中的列，MySQL触发器中的SQL语句可以使用关联表中的任意列，但不能直接使用列的名称引用，因为激活触发器的语句可能已修改、删除或添加了新的列名，而列的旧名同时存在，这样就会使系统混淆，无法确定使用的列究竟是操作之前的列，还是操作之后的列。所以，必须通过“NEW.column_name”或者“OLD.column_name”引用。NEW.column_name用来引用操作之后，也就是新记录的列，而“OLD.column_name”用来引用更新或删除前的已有记录的列。
对于INSERT语句，只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以同时使用NEW或OLD。

示例2.7.5 使用触发器实现在新增用户时自动在灌水乐园版块中发布用户报道帖。

图像说明文字

说明：
本例在用户表（tbl_user）中定义了一个AFTER INSERT触发器，以实现在新用户注册时，自动在灌水乐园版块中发布用户报道帖。
第01行首先改变命令结束标记为“##”。
第02～03行创建触发器，请注意这里定义的AFTER INSERT触发器所关联的表是tbl_user。
第04行为触发器的开始标记。
第05～09行在帖子表中插入报道帖，请注意这里使用的是INSERT语句的SET写法。第07行插入报道帖的内容，其中使用NEW.uName引用新增用户的用户名，CONCAT函数用于连接字符串。第08行设置帖子表的发帖人，使用NEW.uId引用新增用户的用户编号。
第10行为触发器的结束标记。
第12行将命令结束标记修改为“;”。

要观察上述触发器的执行结果，可执行以下命令。
INSERT INTO tbl_user VALUES (DEFAULT,'tom','12345', '2.gif',DEFAULT,'1');
SELECT * FROM tbl_topic ORDER BY publishTime DESC LIMIT 1;
整个示例的运行结果如图2.7.5所示。

图像说明文字