Experiment

用户666号

已于 2023-04-22 12:16:13 修改

阅读量627

点赞数

文章标签：数据库

于 2023-04-20 13:54:55 首次发布

本文链接：https://blog.csdn.net/qq_63789815/article/details/130264323

版权

实验一：项目名称：以进销存管理系统为例，创建各张表、索引、视图

一、实现功能：
超市购物业务处理：选取货品、收银台结账（收银员扫码，系统计算金额，顾客支付）
二、概念设计
1）分析上述业务中出现了哪些对象？（第一次抽象）
顾客
货品
收银（结账）（哪个收银员、在什么时间、收了哪个顾客，多少钱）？（卖给了这个顾客哪些货品）（要账实相符）
库存
2）分析每个对象有哪些特征？
顾客（姓名、性别、手机号、会员卡号、卡内余额、可用积分、上次消费时间）
货品（名称、规格、条码、计量单位、名称缩写（由触发器自行维护）、零售价、促销价）
收银（收银员，顾客，收银时间，应收金额、实收金额、支付方式）
收银明细（货物，销售数量，零售价、促销价）
库存（名称、库存数量、昨日库存、月初库存）
同学分思考:同一个对象，在不同业务场景下，我们关心的特征属性是不一样的，我们仅处理与业务有关的特征，思考上述对象的每个属性与业务的关系。

3）根据表述绘制系统ER图

4）利用ER图转换为关系模型的规则，设计关系模式
将实体型、实体的属性和实体型之间的联系转化为关系模式
转换为则：
一个实体型转换为一个关系模式
一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并（加入对应关系的码和联系本身的属性）。
一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并（合并后关系的属性：在n端关系中加入1端关系的码和联系本身的属性）。
一个m:n联系转换为一个关系模式（关系的属性：与该联系相连的各实体的码以及联系本身的属性，各实体码的组合）

注意：数据库中要求每个表都要有一个标识列（整型，自增），作为主码，表中的外码是为了表达实体之间的联系。

会员（会员id，姓名、性别、手机号、会员卡号、卡内余额、可用积分、上次消费时间）
货品（货品id，名称、规格、条码、计量单位、零售价、促销价）
供应商表（供应商id，供应商名称，联系人，联系电话，供应商状态）
收银（收银id，收银员id，顾客id，收银时间，应收金额、实收金额、支付方式，销售状态）
收银明细（收货明细id，收银id，货物id，销售数量，零售价、促销价）
库存（库存id、货品id，库存数量、昨日库存、月初库存）
扎帐(扎帐id,收银员id,扎帐时间，应收金额，实收金额，差错原因)

考虑到这次重点处理销售业务，供应商与货物之间的供应关系暂未考虑，收银员表未列入，同学们可以自己设计。
后面根据也可以加入支付方式表、销售状态、供应商状态表等。

三、逻辑结构设计
创建数据库、建表（每列的数据类型、列的附加属性：注释、默认值，能否为空，外键等）

1、会员信息表hyxxb（会员id，姓名、性别、手机号、会员卡号、卡内余额、可用积分、上次消费时间）

自增从10000开始，为什么？如何设置/

2、货品名称表hpmcb（货品id，货品名称，计量单位，货品条码，零售价，促销价，名称缩写(由触发器自行维护)，货品状态）

3、供应商信息表gysxxb（供应商id，供应商名称，联系人，联系电话，供应商状态）

4、收银记录表syjlb（收银id，收银员id，顾客id，收银时间，支付方式，应收金额，实收金额，优惠金额，销售状态（正常 ,已退货），扎帐id）

注意：CURRENT_TIMESTAMP的含义？
外键？

5、收银明细表symxb（收货明细id，收银id，货物id，销售数量，零售价、促销价）

外键：

6、货品库存表hpkcb（库存id，货品id，(当前)库存数量，昨日库存(用于日清)，月初库存(用于月结)）

外键：

7、扎帐记录表zzjlb（扎帐id，收银员id，扎帐时间，应收金额(由系统计算)，实收金额(由收银员输入)，差错原因）

8、辅助表汉字拼音表Hzpyb（汉字，简拼，拼音）

四、插入记录
1、货品名称表hpmcb

2、收银记录表syjlb

3、收银明细表symxb

4、扎帐记录表zzjlb

5、汉字拼音表Hzpyb
根据发的脚本文件导入数据
其他表数据自行插入。

五、建立索引
哪些表上哪些列需要建索引？为什么？

六、建立视图
1、货品库存数量

2、收银明细视图

最终得到如下数据库结构：

备份数据库的结构和数据，导出SQL文件名为：shop.sql，将shop.sql上传至实验报告一。

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for gysxxb

-- ----------------------------

DROP TABLE IF EXISTS `gysxxb`;

CREATE TABLE `gysxxb` (

`gysid` int NOT NULL COMMENT '供应商id',

`gysmc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '供应商名称',

`lxren` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '联系人',

`lxdh` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '联系电话',

`gyszt` tinyint NULL DEFAULT NULL COMMENT '供应商状态(0 正常往来， 1 暂停往来， 2不再往来)',

PRIMARY KEY (`gysid`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of gysxxb

-- ----------------------------

-- Table structure for hpkcb

-- ----------------------------

DROP TABLE IF EXISTS `hpkcb`;

CREATE TABLE `hpkcb` (

`kcid` int NOT NULL COMMENT '库存id',

`hpid` int NULL DEFAULT NULL COMMENT '货品id',

`kcsl` decimal(18, 3) NULL DEFAULT NULL COMMENT '（当前）库存数量',

`zrkc` decimal(18, 3) NULL DEFAULT NULL COMMENT '昨日库存(用于日清),系统自动维护',

`yckc` decimal(18, 3) NULL DEFAULT NULL COMMENT '月初库存(用于月结)，系统自动维护'

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of hpkcb

-- ----------------------------

-- Table structure for hpmcb

-- ----------------------------

DROP TABLE IF EXISTS `hpmcb`;

CREATE TABLE `hpmcb` (

`Hpid` int NOT NULL COMMENT '货品id',

`hpmc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '货品名称',

`jldw` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '计量单位',

`hptm` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '货品条码',

`lsj` decimal(9, 2) NULL DEFAULT NULL COMMENT '零售价',

`cxj` decimal(9, 2) NULL DEFAULT NULL COMMENT '促销价',

`mcsx` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '名称缩写(由触发器自行维护)',

`hpzt` tinyint NULL DEFAULT NULL COMMENT '货品状态(0正常，1暂停进货(不能购进但可以销售)，2不再进货(不能购进也不能销售))',

PRIMARY KEY (`Hpid`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of hpmcb

-- ----------------------------

INSERT INTO `hpmcb` VALUES (9, '可口可乐', '瓶', '101', 3.00, 2.50, 'kkkl', 0);

INSERT INTO `hpmcb` VALUES (10, '百事可乐', '瓶', '102', 3.00, 2.50, 'bskl', 0);

INSERT INTO `hpmcb` VALUES (11, '伊利牛奶', '盒', '103', 5.00, 4.50, 'ylnn', 0);

INSERT INTO `hpmcb` VALUES (12, '农夫山泉', '瓶', '105', 2.00, 1.50, 'nfsq', 0);

INSERT INTO `hpmcb` VALUES (13, '涪陵榨菜', '袋', '106', 1.00, 0.80, 'flzc', 0);

INSERT INTO `hpmcb` VALUES (14, '青岛啤酒', '听', '107', 5.00, 4.00, 'qdpj', 0);

-- ----------------------------

-- Table structure for hyxxb

-- ----------------------------

DROP TABLE IF EXISTS `hyxxb`;

CREATE TABLE `hyxxb` (

`hyid` int NOT NULL COMMENT '会员信息表id，从10000开始',

`hyxm` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '会员姓名',

`hyxb` char(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '会员性别',

`hykh` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '会员卡号',

`sjhm` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '手机号码',

`knye` decimal(9, 2) NULL DEFAULT NULL COMMENT '卡内余额',

`kyjf` int NULL DEFAULT NULL COMMENT '可用积分',

`scxfsj` datetime NULL DEFAULT NULL COMMENT '上次消费时间',

PRIMARY KEY (`hyid`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of hyxxb

-- ----------------------------

-- Table structure for hzpyb

-- ----------------------------

DROP TABLE IF EXISTS `hzpyb`;

CREATE TABLE `hzpyb` (

`hz` varchar(6) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '汉字',

`jp` varchar(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '简拼',

`py` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '拼音'

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Table structure for syjlb

-- ----------------------------

DROP TABLE IF EXISTS `syjlb`;

CREATE TABLE `syjlb` (

`syid` int NOT NULL,

`syyid` int NULL DEFAULT NULL COMMENT '收银员id',

`gkid` int NULL DEFAULT NULL COMMENT '顾客id 0 普通顾客，1-9999大客户，>=10000会员',

`sysj` datetime NULL DEFAULT NULL COMMENT '收银时间，默认为系统当前时间',

`zffs` tinyint NULL DEFAULT NULL COMMENT '支付方式 0现金，1储值卡，2支付宝，3微信支付',

`ysje` decimal(18, 2) NULL DEFAULT NULL COMMENT '应收金额',

`ssje` decimal(18, 2) NULL DEFAULT NULL COMMENT '实收金额',

`yhje` decimal(18, 2) NULL DEFAULT NULL COMMENT '优惠金额',

`xszt` tinyint NULL DEFAULT NULL COMMENT '销售状态 0正常，1已退货',

`zzid` int NULL DEFAULT NULL COMMENT '扎帐',

PRIMARY KEY (`syid`) USING BTREE,

INDEX `fk_zzjlb_syjlb`(`zzid` ASC) USING BTREE,

CONSTRAINT `fk_zzjlb_syjlb` FOREIGN KEY (`zzid`) REFERENCES `zzjlb` (`zzid`) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of syjlb

-- ----------------------------

-- Table structure for symxb

-- ----------------------------

DROP TABLE IF EXISTS `symxb`;

CREATE TABLE `symxb` (

`mxid` int NOT NULL COMMENT '收银明细id',

`syid` int NULL DEFAULT NULL COMMENT '收银id',

`hpid` int NULL DEFAULT NULL COMMENT '货品id',

`xssl` decimal(18, 3) NULL DEFAULT NULL COMMENT '销售数量',

`dj` decimal(9, 2) NULL DEFAULT NULL COMMENT '定价',

`lsj` decimal(9, 2) NULL DEFAULT NULL COMMENT '零售价(用来解决货品调价问题)',

PRIMARY KEY (`mxid`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of symxb

-- ----------------------------

INSERT INTO `symxb` VALUES (16, 8, 9, 1.000, 2.50, 3.00);

INSERT INTO `symxb` VALUES (17, 8, 10, 1.000, 2.50, 3.00);

INSERT INTO `symxb` VALUES (19, 9, 14, 1.000, 4.00, 5.00);

INSERT INTO `symxb` VALUES (20, 9, 11, 1.000, 4.50, 5.00);

INSERT INTO `symxb` VALUES (21, 9, 12, 1.000, 1.50, 2.00);

-- ----------------------------

-- Table structure for zzjlb

-- ----------------------------

DROP TABLE IF EXISTS `zzjlb`;

CREATE TABLE `zzjlb` (

`zzid` int NOT NULL COMMENT '扎帐记录表',

`syyid` int NULL DEFAULT NULL COMMENT '收银员id',

`zzsj` datetime NULL DEFAULT NULL COMMENT '扎帐时间',

`ysje` decimal(18, 2) NULL DEFAULT NULL COMMENT '应收金额',

`ssje` decimal(18, 2) NULL DEFAULT NULL COMMENT '实收金额',

`ccyy` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '差错原因',

PRIMARY KEY (`zzid`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of zzjlb

-- ----------------------------

INSERT INTO `zzjlb` VALUES (5, 66, '2021-04-20 16:33:33', 15.00, 15.00, '本次结账无差错');

-- ----------------------------

-- View structure for v_hpkcb

-- ----------------------------

DROP VIEW IF EXISTS `v_hpkcb`;

CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_hpkcb` AS select `hpkcb`.`hpid` AS `hpid`,`hpkcb`.`kcsl` AS `kcsl`,`hpmcb`.`jldw` AS `jldw`,`hpmcb`.`hpmc` AS `hpmc` from (`hpmcb` join `hpkcb`);

-- ----------------------------

-- View structure for v_symxb

-- ----------------------------

DROP VIEW IF EXISTS `v_symxb`;

CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_symxb` AS select `symxb`.`mxid` AS `mxid`,`symxb`.`syid` AS `syid`,`symxb`.`xssl` AS `xssl`,`symxb`.`dj` AS `dj`,`symxb`.`lsj` AS `lsj`,`hpmcb`.`hpmc` AS `hpmc`,`hpmcb`.`jldw` AS `jldw` from (`symxb` join `hpmcb`);

SET FOREIGN_KEY_CHECKS = 1;

实验二：函数、存储过程、触发器练习

一、实验要求：

理解函数、存储过程、触发器的概念
掌握函数、存储过程、触发器的语法格式、使用方法
掌握函数、存储过程、触发器的创建、执行
能够使用函数、存储过程、触发器处理实际系统中的业务逻辑

二、环境准备：

1、创建用于企业管理的员工管理数据库，数据库名为YGGL，包含员工的信息、部门信息及员工的薪水信息。数据库YGGL包含下列3个表:

(1) Employees: 员工信息表;

(2) Departments: 部门信息表;

(3) Salary:员工薪水情况表。

各表的结构如表T2.1、表T2.2、表T2.3所示。

表T2.1 Employees 表结构

列名数据类型长度是否允许为空值说明

EmployeelD char 5 x 员工编号,主键

Name char 10 x 姓名

Education char 4 x 学历

Birthday date 16 x 出生日期

Sex char 2 x 性别

WorkYear tinyint 1 √ 工作时间

Address varchar 20 √ 地址

PhoneNumber char 12 √ 电话号码

DepartmentID char 3 x 部门编号,外键

表T2.2 Departments 表结构

列名数据类型长度是否允许为空值说明

DepartmenTD char 3 x 部门编号,主键

DepartmentName char 20 x 部门名

Note text 16 √ 备注

表T2.3 Salary 表结构

列名数据类型长度是否允许为空值说明

EmployeelD char 6 x 员工编号,主键

InCome float 8 x 收入

Outcome float 8 x 支出

三、常量及系统函数使用

1.常量的使用

(1)计算194和142的乘积，可使用如下SQL语句:

select 194* 142;

(2)获取以下这串字母的值: 'i\nlove\nMySQL'。

select 'i\nlove\nMySQL';

[思考与练习 ]

熟悉其他类型的常量，掌握不同类型的常量的用法。

2.系统变量的使用

(1)获得现在使用的MySQL版本。

select @@VERSION;

执行结果如右图所示。

(2)获得系统当前的时间。

select CURRENT_TIME;

(思考与练习 ]

了解各种常用系统变量的功能及用法。

3.用户变量的使用

(1)创建一个名为female的用户变量，并在SELECT 语句中，用该局部变量查找表中所有女员工的编号、姓名。

use YGGL

set @female=0;

变量赋值完毕，可使用以下语句进行查询:

select EmployeeID, Name

from Employees

where sex=@female;

(2)定义一个变量，用于获取号码为102201的员工的电话号码。

set @phone=(select PhoneNumber

from Employees

where EmployeeID='102201);

执行完该语句后可使用SELECT语句查询变量phone的值，执行结果如右图示。

[思考与练习 ]

定义一个变量，用于描述YGGL数据库的Salary表中员工00001的实际收入，然后查询该变量。

4.运算符的使用

(1)使用算术运算符“-”查询员工的实际收入。

select InCome-OutCome

from Salary;

(2)使用比较运算符“>”查询Employees表中工作时间大于5年的员工信息。

select *

from Employees

where WorkYear > 5;

(3)使用逻辑运算符“AND"查看以下语句的结果。

select (7>6) AND(A'=' B);

[思考与练习]

熟悉各种常用运算符的功能和用法，如LIKE、BETWEEN等。

5.系统内置函数的使用

(1)获得一组数值的最大值和最小值。

select GREATEST(5, 76, 25.9), LEAST(5, 76, 25.9);

[思考与练习]

a.使用ROUND()函数获得一个数的四舍五入的整数值。

b.使用ABS()函数获得-个数的绝对值。

c.使用SQRT()函数返回一个数的平方根。

(2)求广告部员工的总人数。

select COUNT( EmployeeID)as广告部人数

from Employees

where DepartmentID =

( select DepartmentID

from Departments

where DepartmentName='广告部);

[思考与练习 ]

a.求广告部收入最高的员工的姓名。

b.查询员工收入的平均数。

C．聚合函数如何与GROUP BY函数一起使用?

(3)使用CONCAT（）函数连接两个字符串。

select CONCAT(‘I love', MySQL’);

执行结果如右图所示。

(4)使用ASCIIO函数返回字符表达式最左端字符的ASCII值。

select ASCII(‘abc’);

[思考与练习 ]

a.使用CHAR（）函数将ASCII码代表的字符组成字符串。

b.使用LEFT（）函数返回从字符串‘abcdef’左侧开始的3个字符。

(5)获得当前的日期和时间。

select NOW（）;

(6)查询YGGL数据库中员工号为00001的员工的出生年份:

select YEAR(Birthday)

from Employees

where EmployeeID=‘000001’;

[思考与练习]

a.使用DAYNAME（）函数返回当前时间的星期名。

b.列举出其他的时间日期函数。

(7)使用其他类型的系统内置函数，如格式化函数、控制流函数、系统信息函数等。

四、存储过程

(1)创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。

USE YGGL

DELIMITER $$

CREATE PROCEDURE TEST(OUT NUMBER1 INTEGER)

BEGIN

DECLARE NUMBER2 INTEGER;

SET NUMBER2=(SELECT COUNT(*) FROM Employees);

SET NUMBER1=NUMBER2;

END$$

DELIMITER;

调用该存储过程:

CALL TEST(@NUMBER);

查看结果:

selct @NUMBER;

(2)创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，

否则输出1。

DELIMITER $$

CREATE PROCEDURE

COMPA(in ID1 CHAR(6), IN ID2 CHAR(6), oUT BJ INTEGER)

BEGIN

DECLARE SR1,SR2 FLOAT(8);

SELECT InCome-OutCome INTO SR1 FROM Salary WHERE EmployeelD=ID1;

SELECT InCome-OutCome INTO SR2 FROM Salary WHERE EmployeeID=ID2;

IF ID1>ID2 THEN

SET BJ=0;

ELSE

SET BJ=1;

END IF;

END$$

DELIMITER;

调用该存储过程:

CALL COMPA(‘000001', '108991',@BJ);

查看结果:

select @BJ;

执行结果如右图所示。

(3)创建存储过程，使用游标确定一个员工的实际收入是否排在前3名。

结果为TRUE表示是，结果为FALSE表示否。

DELIMITER $$

CREATE PROCEDURE TOP_THREE @EM_ID char(6),@OK bit OUTPUT

BEGIN

DECLARE @X_EM_ID char(6)

DECLARE @ACT_IN int,@SEQ int

DECLARE SALARY_DIS cursor FOR

SELECT EmployeeID,InCome_OutCome

FROM Salary

ORDER BY InCome_OutCome DESC

SET @SEQ=0

SET @OK=0

OPEN SALARY_DIS

FETCH SALARY_DIS INTO @X_EM_ID,@ACT_IN

WHILE @SEQ<3 AND @OK=0

BEGIN

SET @SEQ=@SEQ+1

IF @X_EM_ID=@EM_ID

SET @OK=1

FETCH SALARY_DIS INTO @X_EM_ID,@ACT_IN

END

CLOSE SALARY_DIS

DEALLOCATE SALARY_DIS

END$$

DELIMITER;

执行该存储过程，并查看结果

[思考与练习]

a.创建存储过程，要求当一个员工的工作时间大于6年时将其转到经理办公室工作。

五、存储函数

(1)创建一个存储函数，返回员工的总人数。

CREATE FUNCTION EM NUM()

RETURNS INTEGER

RETURN(SELECT COUNT(*) FROM Employees);

调用该存储函数:

select EM_NUM();

(2)创建一个存储函数，删除在Salary表中有但在Employees表中不存在的员工号。若在Employees表中存在则返回FALSE,若不存在则删除该员工号并返回TRUE。

DELIMITER $$

CREATE FUNCTION DELETE_EM(EM_ID CHAR(6))

RETURNS BOOLEAN

BEGIN

DECLARE EM_NAME CHAR(10);

SELECT Name INTO EM_NAME FROM Employees WHERE EmployeelD=EM_ID;

IF EM_NAME IS NULL THEN

DELETE FROM Salary WHERE EmployeeID=EM_ID;

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

END$$

DELIMITER ;

调用该存储函数:

select DELETE_eM(‘000001’);

执行结果如右图所示。

[思考与练习 ]

a.创建存储函数，判断员工是否在研发部工作，若是则返回其学历，若不是则返回字符串“NO"。

b.创建一个存储函数，将工作时间满4年的员工收入增加500元。

六、触发器

(1)创建触发器，在Employees表中删除员工信息的同时将Salary 表中该员工的信息删除，以确

保数据完整性。

CREATE TRIGGER DELETE_EM AFTER DELETE

ON Employees FOR EACH ROW

DELETE FROM Salary

WHERE EmployeeID=OLD,EmployeeID;

创建完后删除Employees表中的一行数据，然后查看Salary表中的变化情况。

(2)假设Departments2表和Departments表的结构和内容都相同，在Departments上创建一个触发

器，如果添加一个新的部门，该部门也会添加到Departments2表中。

DELIMITER $$

CREATE TRIGGER Departments Ins

AFTER INSERT ON Departments FOR EACH ROW

BEGIN

INSERT INTO Departments2 VALUES(NEW.DepartmentID, NEW.Department Name,NEW.Note);

END$$

DELIMITER;

(3)当修改Employees表时，若将Employes表中员工的工作时间增加1年，则将收入增加500

元，若工作时间增加2年则收入增加1000元，依次增加。若工作时间减少则无变化。

DELIMITER $$

CREATE TRIGGER ADD SALARY

AFTER UPDATE ON Employees FOR EACH ROW

BEGIN

DECLARE YEARS INTEGER;

SET YEARS= NEW.WorkYear-OLD.WorkYear;

IF YEARS>0 THEN

UPDATE Salary SET InCome=InCome+500*YEARS

WHERE EmployeeID =NEW.EmployeeID;

END IF;

END$$

DELIMITER;

[思考与练习]

a.创建UPDATE触发器，当Departments表中部门号发生变化时，Employees表中员工所属的部门号也将改变。

创建UPDATE触发器，当Salary 表中的InCome值增加500时，OutCome值也增加500

七、事件

(1)创建一个立即执行的事件，查询Employees表的信息。

CREATE EVENT direct_happen

ON SCHEDULE AT NOW()

SELECT * FROM Employees;

在做下面语句前，先查看一下系统变量：SELECT @@event_scheduler;

如果值为on，表示，已经开启事件，否则，需要更改变量的值。

Set @@event_scheduler=on

因为事件控制查询语句，看不出执行效果，大家把上面代码改一下：

下面代码的意思是，立刻根据库存数量更新昨日库存。一开始置zrkc列为0，创建事件后，可以发现zrkc列自动更新。

(2)创建一个时间，每天执行一次，它从第二天开始直到2019年12月31日结束。

DELIMITER $$

CREATE EVENT every day

ON SCHEDULE EVERY 1 DAY

STARTS CURDATE()+INTERVAL 1 DAY

ENDS ‘2019-12-31'

BEGIN

SELECT * FROM Employees;

END$$

DELIMITER ;

上面代码更新为如下代码：意思是设置一个事件，每分钟根据kcsl，更新zrkc。创建事件后，大家更新kcsl，过一分钟后，会发现zrkc自动更新。

1分钟后

(思考与练习 ]

创建一个2020年11月25日上午11点执行的事件。
创建一个从下个月20日开始到2020年5月20日结束、每个月执行一次的事件.

八、使用存储过程处理进销存系统中的简单业务逻辑

创建货品信息维护HpxxWh存储过程,能够实现对货品信息的增删改操作
创建货品信息查询HpxxCx存储过程,能够实现对货品信息的查询操作

九、创建PysxCx函数，能够根据输入的汉字，返回其拼音首字母缩写

十、对于货品信息表创建触发器，自动维护名称缩写列

Update_mcsx_before_insert_hpmcb
Update_mcsx_before_update_hpmcb_hpmc

完成八、九、十后备份数据库的结构和数据，导出SQL文件名为：shop2.sql，将shop2.sql上传至实验报告二。

附件：MySQL的内置函数

1、数学函数

abs(x):绝对值

ceil(x)或者ceiling(x)：向上取整

Floor(x):向下取整

pow(x,y)：求指数，x的y次方

rand(),rand(N):返回0-1间的浮点数，使用不同的seed N可以获得不同的随机数

round(x, D)：四舍五入保留D位小数，D默认为0，可以为负数，如round(19, -1)返回20

2 字符串函数

char_length(str):返回str所包含的字符数，一个多字节字符算一个字符

length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节

concat(s1, s1, ...): 返回连接参数产生的字符串

lower（str)或者lcase(str):全部小写

upper(str)或者ucase(str):全部大写

left(s,n):返回字符串s最左边n个字符

right(s,n): 返回字符串最右边n个字符

ltrim(s):删除s左侧空格字符

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置，特定长度的子字符串

INSTR(str,substr): 判断子字符串在目标字符串中是否存在，存在返回其位置，不存在，返回0。

3 日期和时间函数

CURDATE(), CURRENT_DATE, CURRENT_DATE():用于获取当前日期，格式为'YYYY-MM-DD'; 若+0则返回YYYYMMDD

CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于获取当前时间，格式为'HH:MM:SS' 若+0则返回 HHMMSS

DATEDIFF(expr1,expr2):返回相差的天数,参数是必须使用字符串格式（用引号）。

TIMEDIFF(expr1,expr2)：返回相隔的时间

ADDDATE(date,INTERVAL expr unit):进行时间的增加。

Unit：day/hour/minute/second

MONTH(date)

MONTHNAME(date)

DAYNAME(date)

DAY(date)，DAYOFMONTH(date)：1-31或者0

DAYOFWEEK(date)：1-7==>星期天-星期六

DAYOFYEAR(date)： 1-365

4 系统信息函数

VERSION():返回mysql服务器的版本，是utf8编码的字符串

DATABASE()，SCHEMA()：显示当前使用的数据库

SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回当前的用户名@主机，utf8编码字符串

CHARSET(str)

COLLATION(str)

LAST_INSERT_ID()：自动返回最后一个insert或者update查询，为auto_increment列设置的第一个发生的值

5 其他函数

MD5(str):对数据进行MD5加密。

UUID：生成一个唯一标识符（自增长），是整数，数据唯一，空间唯一

6条件判断函数

IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值

IFNULL(expr1,expr2)：如果expr1不为NULL,返回expr1,否则返回expr2

NULLIF(expr1,expr2): 如果expr1=expr2则返回NULL, 否则返回expr2

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

当compare_value=value时返回result

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

当condition为TRUE时返回result

SELECT CASE 1 WHEN 1 THEN 'one'

WHEN 2 THEN 'two' ELSE 'more' END;##'one'

SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;##'true'

SELECT CASE BINARY 'B'

WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;##NULL

7 格式或类型转化函数

FORMAT(X,D[,locale])：将数字X转化成'#,###,###.##'格式，D为保留的小数位数

CONV(N,from_base,to_base)：改变数字N的进制，返回值为该进制下的数字构成的字符串

INET_ATON(expr)：ip字符串转数字

INET_NTOA(expr)：数字转ip字符串

CAST(expr AS type)：转换数据类型

CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以为BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等

-- 创建PysxCx函数，能够根据输入的汉字，返回其拼音首字母缩写

DROP FUNCTION IF EXISTS pysxcx;

DELIMITER $$

CREATE FUNCTION `pysxcx`(zw VARCHAR(50))

RETURNS VARCHAR(10) DETERMINISTIC

BEGIN

SET @l=CHAR_LENGTH(zw);

SET @i=1;

SET @pysx='';

SET @jp='';

WHILE @i<=@l DO

SET @hz=SUBSTRING(zw,@i,1);

SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;

SET @pysx=CONCAT(@pysx,@jp);

SET @i=@i+1;

END WHILE;

RETURN @pysx;

END

DELIMITER;

SELECT pysxcx('可口可乐');

-- 对于货品信息表创建触发器，自动维护名称缩写列

DROP TRIGGER IF EXISTS Update_mcsx_before_insert_hpmcb;

DELIMITER $$

CREATE TRIGGER Update_mcsx_before_insert_hpmcb

BEFORE INSERT ON hpmcb

FOR EACH ROW

BEGIN

SET new.mcsx=pysxcx(new.hpmc);

END$$

DELIMITER ;

DROP TRIGGER IF EXISTS Update_mcsx_before_update_hpmcb_hpmc;

DELIMITER $$

CREATE TRIGGER Update_mcsx_before_update_hpmcb_hpmc

BEFORE UPDATE ON hpmcb

FOR EACH ROW

BEGIN

IF(new.hpmc<>old.hpmc)

THEN

SET new.mcsx=pysxcx(new.hpmc);

END IF;

END$$

DELIMITER;

-- 创建货品信息维护HpxxWh存储过程,能够实现对货品信息的增删改操作

DROP PROCEDURE IF EXISTS hpxxwh;

DELIMITER $$

CREATE PROCEDURE hpxxwh(

hpid INT,

hpmc VARCHAR(50),

jldw VARCHAR(10),

hptm VARCHAR(20),

lsj DECIMAL(9,2),

cxj DECIMAL(9,2),

hpzt TINYINT)

BEGIN

-- 输入0，增加货品

IF hpid=0

THEN

INSERT INTO hpmcb(hpmc,hptm,jldw,lsj,cxj,mcsx,hpzt)

VALUES(hpmc,hptm,jldw,lsj,cxj,pysxcx(hpmc),hpzt);

SET @hpid=@@identity;

INSERT INTO hpkcb(hpid,kcsl,zrkc,yckc)

VALUES(@hpid,0,0,0);

-- 输入hpid，修改货品信息

ELSEIF hpid>0

THEN

UPDATE hpmcb SET

hpmcb.hpmc=hpmc,

hpmcb.hptm=hptm,

hpmcb.jldw=jldw,

hpmcb.lsj=lsj,

hpmcb.cxj=cxj,

hpmcb.mcsx=pysxcx(hpmc),

hpmcb.hptm=hptm,

hpmcb.hpzt=hpzt

WHERE hpmcb.hpid=hpid;

-- 输入-hpid，删除指定商品信息

ELSE

DELETE FROM hpkcb WHERE hpkcb.hpid=-hpid;

DELETE FROM hpmcb WHERE hpmcb.hpid=-hpid;

END IF;

END$$

DELIMITER;

-- 调用增删改存储过程

CALL hpxxwh(-22,'香蕉','个','001',4.00,2.00,0);

CALL hpxxwh(0,'苹果','个','001',4.00,2.00,0);

CALL hpxxwh(1,'梨','个','001',4.00,2.00,0);

-- 创建货品信息查询HpxxCx存储过程,能够实现对货品信息的查询操作

DROP PROCEDURE IF EXISTS hpxxcx;

DELIMITER $$

CREATE PROCEDURE hpxxcx(hpid INT)

BEGIN

-- 输入0，查询全部商品

IF hpid=0

THEN

SELECT * FROM hpmcb;

ELSE

-- 输入hpid，查询指定商品

SELECT * FROM hpmcb WHERE hpmcb.hpid=hpid;

END IF;

END $$

DELIMITER;

-- 调用查存储过程

CALL hpxxcx(0);

CALL hpxxcx(20);

用户666号

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Experiment

CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以为BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等。
复制链接

扫一扫