MySQL数据库系统原理与应用章节练习题及答案

习题1

1.什么是数据？什么是信息？什么是数据处理？

数据是指存储在某一种媒体上的能够识别的物理符号,用来记录事物的情况，用类型和值

来表征。不同的数据类型，记录的事物性质不同。

信息是经过加工处理的有用的数据。数据只有经过提炼和抽象变成有用的数据才能成为

信息。信息仍以数据的形式表现。

数据处理是指将数据加工转换成信息的过程。数据处理也叫信息处理。通过处理数据可

以获得信息，通过分析和筛选信息可以产生决策。

2.什么是数据库管理系统？什么是数据库？什么是数据库应用系统？什么是数据库系

统？

数据库管理系统(DatabaseManagementsystem,简称DBMS)是用户和操作系统之间的数

据管理软件，用于管理和组织数据库的创建、访问、操作和维护。

数据库(Database,简称DB)是指长期存储在计算机内的，按一定数据模型组织、存储、

管理、可共享的数据集合，它包含了数据的结构、关系和约束等信息。

数据库应用系统是基于数据库的应用软件系统，通过对数据库的操作和管理来实现特定

的业务功能。

数据库系统(Databasesystem,简称DBS)是由数据库、数据库管理系统和数据库应用系统

组成的集成系统，它是由硬件、软件、数据和人员组合起来为用户提供信息服务的系统，是

存储介质、处理对象和管理系统的集合体。

3.什么是数据库系统的三级模式结构？并画图表示。

数据库系统中的数据是按三级结构、二级映象来组织的，以满足不同层次的用户对数据的

需求。这个结构称为数据库的体系结构,也称三级模式结构。这种结构是从数据库管理系统的

角度来看的，是数据库系统内部的体系结构。三级模式(外模式、模式、内模式)是对数据的三

个抽象级别，二级映象在DBMS内部实现这三个抽象层次的联系和转换。

4.信息有哪三种世界？分别具有什么特点？它们之间有什么联系？

信息分为现实世界、信息世界、U算机世界。

信息的现实世界是指人们要管理的客观存在的各种事物、事务之间的相互联系及事物的

发生、变化过程。它客观存在而不依赖于人们的思想。

信息世界是现实世界在人们头脑中的反映，人们用思维，以现实世界为基础作进一步的抽

象，对事物进行选择、命名、分类等抽象工作之后，并用文字符号表示出来,就形成了信息世界。

计算机世界是将信息世界中的信息经过人们的抽象和组织，按照特定的数据结构,即数据

模型，将数据存储在计算机中。

信息的三种世界之间是可以进行转换的。人们常常首先将现实世界中客观存在的事物或

对象抽象为某一种信息结构，这种结构并不依赖于计算机系统，是人们认识的概念模型。然后再

将概念模型转换为计算机上某一具体的DBMS支持的数据模型。

5.解释概念模型中常用的概念：实体、属性、码、域、实体型、实体集、联系。

实体：客观存在并且可以互相区别的事物称为实体。

属性：实体所具有的某一特征称为属性。

码：能在一个实体集中惟一标识一个实体的属性称为码。

域：某个（些）属性的取值范围称为该属性的域。

实体型：用实体名及其属性名集合来抽象和刻画的同类实体,称为实体型。

实体集：同类型的实体集合称为实体集。

联系：联系在信息世界中反映为实体（型）内部的联系和实体（型）之间的联系。

•91・

习题2

1.简述关系模型的完整性规则。

关系模型的完整性规则是对关系的某种约束条件。关系模型中可以有三类完整性约束：

实体完整性、参照完整性和用户定义的完整性。其中实体完整性和参照完整性是关系模型必

须满足的完整性约束条件，被称做是关系的两个不变性,应该由关系系统自动支持。

2.关系具有什么特点？

通常将关系模型成为关系或表，一个关系对应一张二维表。

3.什么是关系规范化?美系规范化的目的是什么？关系规范化的基本方法是什么？INF至

BCNF,它们之间的关系是什么？

一个低一级范式的关系模式，通过分解可以转换为若干个高一级范式的关系模式,这种过

程称为关系的规范化。

关系的规范化主要目的是解决数据库中数据冗余、插入异常、删除异常和更新异常等数

据存储问题。

关系规范化的基本方法是逐步消除关系模式中不合适的数据依赖，使关系模式达到某种

程度的分离，也就是说，不要将若干事物混在一起，而要彼此分开，用个关系表示一事或一

物，所以，规范化的过程也被认为是“单一化”的过程。

从第一范式至BCNF,它们之间的关系是BCNFq3NFG2NFqiNFo从第一范式到第二范式,

消除了非主属性对码的部分函数依赖；从第二范式到第三范式，消除了非主属性对码的传递

函数依赖；从第三范式到BCNF,消除了关系中冗余的码。

4.指出下列关系各属于第几范式。

1）学生（学号，姓名，课程号，成绩）；

2）学生（学号，姓名，性别）；

3）学生（学号，姓名，院系名,院系地址）；

4）员工（员工编号，基本工资，岗位级别，岗位工资，奖金，工资总额）；

5）供应商（供应商编号，零件号,零件名，单价，数量）。

答：

1）学生（学号，姓名，课程号，成绩）；

该关系属于第二范式。因为学生的成绩是依赖于学生和课程的关系，不是学生的属性,

所以需要将其拆分成两个关系：学生（学号，姓名）和成绩（学号，课程号，成绩）。

2）学生（学号，姓名，性别）；

该关系属于第一范式。每个属性都是原子的，没有重复的属性。

3）学生（学号，姓名，院系名,院系地址）;

习题3

1.通过系统服务管理器启动或停止MySQL服务。

1）打开命令提示符或终端窗口，以管理员身份运行。

2）输入以下命令启动MySQL服务：

在Windows上，输入netstartmysql

如果MySQL服务已经在运行，可以使用以下命令停止MySQL服务：

3）在Windows上，输入netstopmysql。

2.通过MySQL的命令行客户端程序先登录MySQL服务器，再退出MySQL。

要通过MySQL的命令行客户端程序登录MySQL服务器并退出，可以按照以下步骤进行

操作:

1）打开命令提示符或终端窗口。

2）输入以下命令以登录MySQL服务器：

mysql-u用户名-p

其中，“用户名”是你在MySQL服务器上的用户名。执行该命令后，系统会提示你输入

密码。

3）输入密码并按下回车键。如果密码正确，你将成功登录到MySQL服务器，并且命令

行提示符会变为mysql>o

4）在MySQL服务器上执行你需要的操作，例如执行SQL查询、创建数据库等。

5）当你完成操作后，可以使用以下命令退出MySQL客户端程序：

exit

或者按下Ctrl+D组合键。

3.在命令提示符窗口中，如何以管理员身份登录MySQL服务器，使用什么命令？

使用以下命令：

mysql-uroot-p

其中，root是MySQL服务器上的管理员账户名。执行该命令后，系统会提示你输入密

码,

习题4

1.使用MySQLCommandLineClient登录MySQL服务器，先用SQL语句创建book数据

库，再查看MySQL系统中还存在哪些数据库，查看支持的存储引擎类型。

I）打开命令提示符或终端窗口。

2）输入以下命令以登录MySQL服务器：

mysql-u用户名-p

其中，“用户名”是你在MySQL服务器上的用户名。执行该命令后，系统会提示你输入

密沼。

3）输入密码并按下回车键。如果密码正确，你将成功登录到MySQL服务器，并且命令

行提示符会变为“mysql>"。

4）使用以下SQL语句创建名为book的数据库：

CREATEDATABASEbook;

5）使用以下SQL语句查看MySQL系统中存在的数据库：

SHOWDATABASES;

6）使用以下SQL语句查看支持的存储引擎类型：

SHOWENGINES;

2.在NavicatforMySQL中,先使用SQL语句创建salary_managemen数据库，再使用

菜单方式删除salary_managemen数据库。

CREATEDATABASEsalary_managemen;

•6•

习题5

有一个员工工资管理数据库salary_managemen,包括三个表：部门表department＞员工

表employee和工资表salary,这三个表的结构见表5-8、表5-9和表5-10。

表5-8部门表department的结构

歹IJ名数据类型约束说明

部门编号定长字符串，长度为2主犍，非空值部门编号：2位数字

部门名变长字符串，长度为2。非空值部门名：2U个字符

负责人变长字符串，长度为I0空值部门负责人：I0个字符

人数整数类型空值部门人数INT

表5-9员工表employee的结构

列名数据类型约束说明

部门编号定长字符串，长度为2非空值部门编号

员工编号定长字符串，长度为4主键，非空值员工编号，4位数字

姓名变长字符串，长度为10非空值姓名

性别定长字符串，长度为】非空值件别

续表

歹U名数据类型约束说明

生日DATE生日

入职时间DATE入职时间

离职时间DATE离职时间

在职状态定长字符串，长度为2在职状态：在职、离职、休假

表5-10工资表salary的结构

歹IJ名数据类型约束说明

部门编号定长字符串，长度为2非空值部门编号

员工编号定长字符串，长度为4非空值员工编号

姓名变长字符串，长度为10非空值姓名

年月标识DATE非空值年月标识

基本工资DECIMAL(10,2)岗位工资

绩效工资DECIMAL(10,2)绩效工资

补贴DECIMAL(10.2)补贴

扣款DECIMAU10.2)扣款

应发工资DECIMAL/10.2)应发工资

社保代扣DECIMALS0,2)社保代扣

实发工资DECIMAL(10,2)结算实发

1.用SQL语句创建数据库salary_managcmcno

CREATEDATABASEsalary_managemen;

2.用SQL语句创建3个表：部门表department＞员工表employee和工资表salary,存

储引擎和字符编码均采用默认值。

USEsalary_managemen;

DROPTABLEIFEXISTSdepartment;

CREATETABLEdepartment

(

部门编号CHAR(2)PRIMARYKEYNOTNULL,

部门名VARCHARQO)NOTNULL,

负责人VARCHAR(IO),

人数INT

DROPTABLEIFEXISTSemployee;

CREATETABLEemployee

(

部门编号CHAR(2)NOTNULL,

员工编号CHAR(4)PRIMARYKEYNOTNULL,

姓名VARCHAR(IO)NOTNULL,

才生别CHAR(l)NOTNULL,

生日DATE,

入职时间DATE,

离职时间DATE,

在职状态CHAR(2)

DROPTABLEIFEXISTSsalary;

CREATETABLEsalary

(

部门编号CHAR(2)NOTNULL,

员工编号CHAR(4)NOTNULL,

姓名VARCHAR(IO)NOTNULL,

年月标识DATENOTNULL,

基本工资DECIMAL。0,2),

绩效工资DECIMAL。0,2),

补贴DECIMAL。0,2),

扣款DECIMAL。0,2),

・8・

应发工资DECIMAL410,2),

社保代扣DECIMAL。0,2),

实发工资DECIMAL(10,2)

习题6

重新定义员工工资管理数据库salary_managemen中的表利联系，包括部门表department

员工表employee和工资表salary。这三个表的结构见表6-3、表6-4和表6-5。

表6-3部门表department的结构

歹IJ名数据类型约束说明

部门编号定长字符串，K度为2主键部门编号:2位数字

部门名变长字符串，长度为20非空值部门名：20个字符

负W人变长字符串，长度为10空值部门负责人：10个字符

人数整数类型空值部门人数INT

表6-4员工表employee的结构

歹U名数据类型约束说明

部门编号定长字符串，长度为2外键部门编号

员工编号定长字符串，长度为4主键员工编号：4位数字

姓名变长字符串，长度为10非空值姓名

性别定长字符串，长度为1非空值，默认男性别状态：男、女

生日DATE检查约束,生日生日

入职时间DATE入职时间

离职时间DATE检查约束，离职时间大于入职时间离职时间

在职状态定长字符串，长度为2默认值约束，默认在职在职状态：在职、离职、休假

表6-5工资表salary的结构

歹U名数据类型约束说明

部门编号定长字符串，长度为2外键部门编号

员工编号定长字符串，长度为4主键员工编号

姓名变长字符串，氏度为10非空值姓名

年月标识DATE主键年月标识

基本工资DECIMAL。0,2)岗位工资

绩效工资DECIMAL。0,2)绩效工资

补贴DECIMAL(IO,2)补贴

扣款DECIMAL(IO,2)扣款

应发工资DECIMAL(IO,2)应发工资

社保代扣DECIMAL(IO,2)社保代扣

实发工资DECIMAL(10.2)结算实发

1.用SQL语句创建数据库salary_managemen

•10・

DROPDATABASEIFEXISTSsalary_managemen;

CREATEDATABASEsalary_managemen;

2.用SQL语句创建上述三个表，即部门表departments员工表employee和工资表salary,

存储引擎和字符编码都采用默认值。

USEsalary_managemen;

DROPTABLEIFEXISTSdepartment;

CREATETABLEdepartment

(

部门编号CHAR(2)PRIMARYKEY,

部门名VARCHAR(20)NOTNULL,

负责人VARCHAR(IO),

人数INT

DROPTABLEIFEXISTSemployee;

CREATETABLEemployee

(

部门编号CHAR(2)REFERENCESdepartment(部门编号)ONUPDATERESTRICT

ONDELETERESTRICT,

员工编号CHAR(4)PRIMARYKEY,

姓名VARCHAR(IO)NOTNULL,

性别ENUMC男；女)NOTNULLDEFAULT'男二

生日DATECHECK(生日＞,1970-1-I1),#列级只能引用本列的字段

入职时间DATE,

离职时间DATE,

在职状态ENUM('在职'离职'休假')NOTNULLDEFAULT'在职'，

CONSTRAINTCK_employeeCHECK(离职时间〉入职时间)

DROPTABLEIFEXISTSsalary;

CREATETABLEsalary(

部门编号CHAR(2),

员工编号CHAR⑷，

姓名VARCHAR(IO)NOTNULL,

年月标识DATE,

基本工资DECIMAL。0,2),

绩效工资DECIMAL/10,2),

补贴DECIMAL。0,2),

扣款DECIMALS0,2),

应发工资DECIMAL。0,2),

社保代扣DECIMAL。0,2),

实发工资DECIMALS0,2),

CONSTRAINTPK^salaryPRIMARYKEY(员工编号，年月标识)，

CONSTRAINTFK_salaryFOREIGNKEY(音B门编号)REFERENCESdepartment(部门

编号)ONUPDATECASCADEONDELETECASCADE

)；

3.先显示salary表的外键约束，然后删除salary表的外键约束；再显示salary表表的创

建语句，最后再添加该表的外键约束。

1）在MySQL中，可以使用以下SQL语句来显示salary表的外键约束：

SHOWCREATETABLEsalary;

执行以上语句后，将会显示salary表的创建语句，其中包含了外键约束的定义。

2）删除salary表的外键约束：

ALTERTABLEsalaryDROPFOREIGNKEYFK.salary;

3）再显示salary表表的创建语句：

SHOWCREATETABLEsalary;

4）添加该表的外键约束：

ALTERTABLEsalary

ADDCONSTRAINTFK_salaryFOREIGNKEY（部门编号）REFERENCES

departmenl（部门编号）ONUPDATECASCADEONDELETECASCADE;

•12・

习题7

1.员工工资管理数据库salary_managemen中3个表的记录分别见表7-5、表7-6和表7-7o

表7-5department表中的记录

部门编号部门名负责人人数

II人力部刘一5

21研发部李四60

31市场都周八40

41财务部吴九3

表7-6employee表中的记录

部门编号员工编号姓名性别生日入职时间离职时间在职状态

111132刘一女1998-10-232021-04-10在职

111152除一女1995-09-122020-05-15在职

II1163张三男1992-12-252021-08-012022-04-30离职

212151李匹男1990-07-082020-11-01在职

212175王五男1992-05-072021-03-01在职

212192赵六女1998-06-152021-05-012022-03-31离职

313121孙七女1996-10-142020-08-10在职

313122周八女1998-09-202021-04-01在职

414125吴九女1997-12-282022-06-19在职

414128郑十女1991-11-192022-08-01在职

表7-7salary表中的记录

部门编号员工编号姓名年月标识基本工资绩效工资补贴扣款应发工资社保代扣实发工资

II1132刘一2022-05-01300040002000-500

II1152陈二2022-05-013(X)()4(XM)100()-50()

111163张三2022-05-01300040001000-500

212151李四2022-05-01400050003000-500

212175王五2022-05-01400050002000-500

212192赵六2022-05-01400050001000-500

313121孙七2022-05-015(X)06(XX)200()-500

313122周八2022-05-015(X)()6(X)()20()()-50()

414125吴九2022-05-01600070003000-500

414128郑十2022-05-01600070002000-500

II1132刘一2022-06-01300040003000-500

111152陈二2022-06-01300040001000-500

212151李四2022-06-014(X)05(X)()20()()-50()

212175王五2022-()6-014(X)05(X)()20()()-50()

313121孙七2022-06-01500060003000-600

313122周八2022-06-01500060002000-600

414125吴九2022-06-01600070003000-800

414128郑十2022-06-01600070002000-800

用SQL语句插入表中所有记录。

USEsalary_managemen;

INSERTINTOd叩anmem(部门编号，部门名，负责人，人数)

VALUES

人力部刘一5),

C21」研发部/李四:60),

(3市场部:周八140),

('417财务部7吴九；3);

INSERTINTOemployee(部门编号,员工编号,姓名，性别,生日，入职时间，离职时间,

在职状态)

VALUES

('1171132','刘一'，'女'，'1998-10-23','2021-04-10',NULLJ在职)

('1「，’1152','陈二'，'女'，'1995-09-12','2020-05-15',NULL,,在职),

('11','1163','张三'，'男丁1992-12-25；'2021-08-01','2022-04-30','离职')，

('2'2151「李四「男’1990-07-08','2020-11-0NULL,'在职')，

('21',‘2175’，‘王五'，'男'，’1992-05-07丁2021-03-0「，NULL」在职)

('21丁2192」赵六丁女71998-06-1572021-05-01','2022-03-31丁离职')，

('31,‘3121’,'孙七丁女丁1996-10-14丁2020-0800',NULL,'在职')，

('31丁3122丁周八「女’1998-09-20','2021-04-01',NULLJ在职)

('41,‘4125’,'吴九丁女丁1997・12・28'，'2022・06・19',NULL,'在职')，

('41',‘4128‘，‘郑十‘，‘女'，’1991-11-19','2022-08-01',NULL,'在职')；

INSERTINTOsalary(部门编号，员工编号，姓名，年月标识，基本工资，绩效工资，补贴,

扣款)

VALUES

('1「，’1132','刘一'，'2022-05-01','3000','4000','2000','-500'),

('1「，’1152','陈二'，'2022-05-01','3000',4000','1000','-500'),

('1「，’1163','张三'，'2022-05-01','3000','4000','1000','-500'),

C2r,'215T,*2022-05-01;'40001,'5000','3000','-500'),

C2T,,2175；'3E5L;,2022-05-01;'4000;'5000',,2000','-500'),

•14・

('21\'2192',A','2022-05-01;'4000','5000','1000','-500'),

OV,3121','2022-05-01;'5000','6000;'2000;'-5001),

⑶,,'3122「周八「2022-05-01丁5000「6000丁2000「-500)

('4「，'4125/吴九'2022-05-01','6000丁7000」3000「500'),

('41',4128','郑十'2022-05-01','6000丁7000','2000','-500'),

(IT,'1132',^lj—\'2022-06-0r,3000','4000',3000,,,-500,),

('1一,'1152','陈二'2022-06-01','3000','4000','1000','-500'),

(2r,1215r,'2022-06-0r,'4000','5000;2000','-500'),

('21J2175'，'王五'，'2022・06・01','4000','5000','2000','-500')，

(3T,13121','2022-06-01;'5000','6000\'SOOO1,'-600'),

(311,,3122','2022-06-01;'50001,'6000;7000','-6001),

('41','4125丁吴九丁2022-06-01'6000丁7000「3000丁-800'),

(14r,4128','2022-06-Or,'6000','7000\^OOO1,'-800');

2.在salary表中,计算年月标识为2022-05-01的所有记录，计算方法如下:

应发工资=基本工资+绩效工资+补贴•扣款

假设：社保代扣=（基本工资+绩效工资+补贴）*30%

实发工资=应发工资•社保代扣

写出实现以上功能的SQL语句。

UPDATEsalary

SET应发工资=基本工资+绩效工资+补贴-扣款,

社保代扣=（基本工资+绩效工资+补贴）*0.30,

实发工资=应发工资-社保代扣

WHERE年月标识=,2022-05-01';

习题8

1.在salary_managemen数据库中，查询employee表中的所有记录。

USEsalary_managcmcn;

SELECT*FROMemployee;

2.查询employee表中的“员工编号”、“姓名”、“性别”和“生口”列的所有记录。

SELECT员工编号，姓名，性别，生日FROMemployee;

3.在employee表中查询“入职时间”在2021年以前的员工记录。

SELECT*FROMemployeeWHEREYEAR（入职时间）＜2021;

4.在salary表中，查询“基本工资”在4000〜5000元范围内的员工记录，并显示“部

门名”。

SELECTsalary.部门编号，department.部门名，员工编号，姓名，年月标识，基本工资，绩

效工资，补贴，扣款，应发工资，社保代扣，实发工资

FROMsalaryINNERJOINdepartment

ONsalary.部门编号二deparlmenl.部门编号

WHERE基本工资BETWEEN4000AND5000;

•16•

习题9

1.在salary_managemen数据库的department表中，定义“部门编号”为唯一索引，以

升序排列。然后查看在department表上的索引。

USEsalary_managemen;

CREATEUNIQUEINDEXdep_noONdeparlment（部门编号ASC）;

SHOWINDEXFROMdeparlment;

2.在employee表中，创建“部门编号”和“员工编号”的多歹U索弓I。然后查看在employee

表上的索引。

CREATEINDEXdep_empONemployee（部门编号，员工编号）；

SHOWINDEXFROMemployee;

习题10

1.在salary_managemen数据库的employee表中，创建名为view_employee的视图，要

求该视图包含employee表上所有列和在职员工的记录，且要求今后对该视图数据的修改都必

须符合这个条件。

USEsalary_managemen;

DROPVIEWIFEXISTSview_employee;

CREATEVIEWview_employee

ASSELECT*FROMemployeeWHERE在职状态二'在职，

WITHCHECKOPTION;

2.使用view_employee观图统计每个部门的员工人数。

SELECT部门编号,COUNT(*)人数FROMview_employee

GROUPBY部门编号；

•18・

习题11

1.在salary_managemen数据库中，创建自定义函数，给定姓名，并返回该员工编号。

USEsalary_managcmcn;

CREATEFUNCTIONfu_gel_id(nameCHAR(IO))

RETURNSCHAR(4)#返回的员工编号的字符串宽度

DETERMINISTIC

BEGIN

DECLAREemp_idCHAR(4);#返回的员工编号

SELECT员工编号INTOemp.idFROMemployeeWHERE姓名二name;

IFemp_idISNULLTHEN

RETURN'没有该员工'；

ELSE

RETURNemp_id;#返|§1字符串

ENDIF;

END

SELECTfu_get」d('张三

2.创建自定义函数，给定部门编号，并返回该部门的部门名、负责人和人数。

CREATEFUNCTIONfu_get_dep(dep_idCHAR(2))

RETURNSCHAR(50)#返回的部门信息字符串的宽度

DETERMINISTIC

BEGIN

DECLAREdep.messageCHAR(50);#返回的部门信息

DECLAREdep_name,dep_headCHAR(20);

DECLAREdep_noINT;

SELECT部门名，负责人，人数INTOdep_name,dep_head,dep_noFROMdepartment

WHERE部门编号:dep_id;

IFdep_nameISNULLTHEN

RETURN'没有该部门'；

ELSE

SETdep_message=CONCAT(dep_id,dep_name,'负责人:',dep_head,,人

数:\dep_no);#连接字符串并赋值

RETURNdep_message;#返回的部门信息字符串

ENDIF;

END;

SELECTfu_get_dep('31');

习题12

1.在salary_managemen数据库中，创建存储过程,给定员工编号，查询其全部工资记

录，

CREATEPROCEDUREpr_id(INemp_idCHAR(4))

READSSQLDATA

COMMEN「给定员工编号，查询其全部工资记录，

BEGIN

SELECT*FROMsalaryWHERE员工编号一emp_id;

END;

CALLpr_id('1152');

2.创建存储过程，给定部门编号，在employee表中统计该部门在职人数并显示。

CREATEPROCEDUREpr_emp(INdep_idCHAR(2),OUTdep_noINT)

DETERMINISTIC

BEGIN

SELECTCOUNTS)INTOdep_noFROMemployee

WHERE部门编号二dep_idAND在职状态二'在职'；

END;

SET@d_id='21';

SET@d_no=();

CALLpr_emp(@d_id,@d_no);

SELECT@d_no;

3.创建存储过程，在employee表中统计所有部门的在职人数，并保存到department

表中。

UPDATEdepartment

SET人数=(

SELECTCOUNT。

FROMemployee

WHEREemployee.部门编号=department.部门编号ANDemployee.在职状态='在

职，

)；

CREATEPROCEDUREpr_calculatcEmployccCount()

BEGIN

UPDATEdepartment

•20・

SET人数二(

SELECTCOUNT(*)

FROMemployee

WHEREemployee.部门编号=d叩ailment.部门编号ANDemployee.在职状态

二'在职，

)；

END;

这个存储过程使用了子查询来统计每个部门的在职人数，并将结果更新到department表

中的“人数”字段中。子查询中的条件是employee表中的“部门编号”与外部查询中的

department表中的“部门编号”相匹配。

CALLpr_calculateEmployeeCount();

SELECT*FROMdepartment;

4.创建存储过程，计算所有员工的应发工资、实发工资，并保存到salary表中。

CREATEPROCEDUREpr_salary_all()

READSSQLDATA

BEGIN

UPDATEsalarySET应发工资=基本工资+绩效工资+补贴-扣款，

社保代扣=(基本工资+绩效工资+补贴)*0.30,

实发工资=应发工资-社保代扣；

END;

CALLpr_salary_all();

5.创建存储过程，在这个存储过程中创建一个游标，在employee表中统计所有部门的

在职人数，并保存到department表中。

CREATEPROCEDUREpr_calculateEmployeeCount_cur()

BEGIN

DECLAREdepartment_idINT;

DECLAREcmploycc_count1NT;

DECLAREdoneBOOLEANDEFAULTFALSE;

DECLAREcurCURSORFORSELECT部门编号FROMdepartment;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;

OPENcur;

read_loop:LOOP

FETCHcurINTOdepartment_id;

IFdoneTHEN

LEAVEread_loop;

ENDIF;

SELECTCOUNT(*)INTOemployee_countFROMemployee

WHERE部门编号=department_idAND在职状态='在职';

UPDATEdepartmentSET人数=employee_count

WHERE部门编号=department_id;

ENDLOOP;

CLOSEcur;

END;

这个存储过程会创建一个游标，遍历department表中的每个部门,然后在employee表中

统计该部门的在职人数，并将结果更新到department表中的“人数”字段中。

CALLpr_calculateEmpIoyeeCount_cur();

SELECT*FROMdepartment;

•22•

习题13

1.在salary_managemen数据库中的employee表上定义一个触发器tr_employee_delete,

当该员工离职时，把该员工的信息添加到被删除员工记录的表employee_delete中，然后删除

employee表中该员工的记录。

USEsalary_managemen;

-创建被删除员工记录的表employee_delete

CREATETABLEIFNOTEXISTSemployee_delete(

department_idCHAR(2),#部门编号

employee_idCHAR(4),#员工编号

employee_nameVARCHAR(10),#姓名

employee_sexCHAR(1),#性另1」

employee_birthdayDATE,#生日

entry_dateDATE,#入职时间

resignation_dateDATE#离职时间

)；

SHOWTRIGGERS;

DROPTRIGGERIFEXISTStr_eniployee_delete;

-创建触发器lr_employee_deleie

CREATETRIGGERtr_employee_delete

AFTERDELETEONemployee

FOREACHROW

BEGIN

INSERTINTOemployce_dclete(dcpartmcnt_id,cmployce_id,cmployce_name,

employee_sex,

employee_birthday,enlry_date,resignation_date)

VALUES(OLD.部门编号，OLD.员工编号，OLD.姓名，OLD.性别，OLD.生日，OLD.A

职时间，CURDATE。)；

END;

以上代码中，首先创建了一个名为empk)yee_delete的表，用于存储被删除员工的信息。

然后创建了一个名为tr_employee_delete的触发器，当employee表中的记录被删除时，触发

器会将被删除员工的信息插入到employee_delete表中。

最后，通过执行DELETE语句来删除employee表中指定员工的记录。指定要删除的员

工的实际编号。

--删除employee表中的员工记录，删除1163号员工，触发执行tr_employee_delete

DELETEFROMemployeeWHERE员工编号=,1163,;

—查看employee表中的记录

SELECT*FROMemployee;

—查看cmploycc_dclctc表中的记录

SELECT*FROMemplo)ee_delele;

2.创建事件ev_count,每月1日自动计算每个部门的员工人数，并更新到department

表中。

—创建事件ev_count