MySQL数据库综合运用

您所在的位置：网站首页 › 顺丰快递信息查询系统电话 › MySQL数据库综合运用

MySQL数据库综合运用

2024-07-05 08:03| 来源: 网络整理| 查看: 265

MySQL综合运用（快递管理系统） Chapter1 概述 1.1 背景1.2 系统开发的可行性判断1.3 目标1.4 软件1.5 开发环境 Chapter2 需求分析 2.1 信息要求2.2 处理要求2.3 安全性与完整性要求 Chapter3 概念结构设计 3.1 局部E-R图3.2.全局E-R图 Chapter4 逻辑结构设计 4.1 关系模式4.2 设计数据表 Chapter5 快递管理系统的实现 5.1 数据表的创建与完整性约束5.1.1 数据表的创建5.1.2 完整性约束 5.2 向表中插入数据5.2.1 插入数据案例5.2.2 插入所有数据后的所有表数据 5.3 删除表中数据5.4 更改表中数据5.5 查询5.5.1 基础查询5.5.2 条件查询5.5.3 排序查询5.5.4 常见函数5.5.5 分组查询5.5.6 连接查询5.5.6 子查询 5.6 预处理SQL语句5.7 函数5.7.1 自定义函数5.7.2 条件判断函数 5.8 存储过程与触发器5.8.1 存储过程5.8.2 触发器附录1：附录2：

Chapter1 概述 1.1 背景

设计一个快递管理系统，要求将快递点作为沟通买家与卖家的桥梁。当买家看中卖家的某商品后，进行购买；支付成功后，卖家收集买家的位置信息，通过快递将商品运输到快递点；到达快递点后，快递点将把已到货的消息发送给买家，买家去往相应的快递点取自己所购买的商品，并在确认无误下确认收货；收货成功后，还可以进行评价、退货、换货等。

1.2 系统开发的可行性判断

经济可行性：所用的开发工具和软件是免费的，节省了经济成本；

技术可行性：MySQL数据库是一个开放源码的小型、跨平台数据库管理系统（DBMS），被广泛地应用在Internet上的中小型网站中，它是由MySQL AB公司开发、发布并支持；

法律可行性：使用的是免费的软件与开发工具，合法合规；

方案可行性：快递管理系统是通过快递点将买卖双方关联起来，两两相关，环环相扣，可行性较高。

1.3 目标

本系统主要完成了买家购买商品，卖家寄出商品，快递点接收商品，以及买家收货后评价退换货的增删改查，卖家对商品种类、价格、折扣的增删改查，快递点所接收的快递公司的增删改查等功能。

快递管理系统主要有五个表，分别为卖家信息表、卖家信息表、商品信息表、快递信息表、售后评价表。

本系统的使用者有卖家、买家、快递点，不同使用者有不同的职责和权限，卖家提供折扣、售后服务，买家不能自行修改商品价格，快递点不能改变商品的信息等。

总之，是通过快递点将买卖双方联系起来，推动线上经济更好的发展，使用合法且免费的软件开发。

1.4 软件

MySQL是一个开放源码的小型、跨平台数据库管理系统（DBMS），被广泛地应用在Internet上的中小型网站中，它是由MySQL AB公司开发、发布并支持。目前MySQL和Oracle数据库一样，都属于甲骨文公司。由于其具有体积小、运行速度快、总体拥有成本低、开放源码的优势，许多中小型网站都为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

1.5 开发环境

本实验用的是MySQL 5.6版本，Windows10。

Chapter2 需求分析 2.1 信息要求

快递管理系统方便了买卖双方且在一定程度上推动了线上经济的发展，所以快递管理系统不但要为卖家提供充足的信息和快捷的查询手段，也要为买家查询购买提供必要和快捷的查询手段，还要为快递点查询寄入寄出到货取货的信息提供快捷高效的查询手段。

数据字典的开发

1. 数据项

数据项的描述={数据项名称，别名，描述，数据类型及取值长度，取值范围，取值含义，存储处}

例如：“商品编号”的数据项

① 数据项名称：商品编号

② 别名：g_num

③ 描述：商品编号商品信息表的主码，不同店铺的商品都有唯一的商品编码。

④ 数据类型及取值长度：varchar(20)。

⑤ 取值范围：1~20个数字字符与字母字符。

⑥ 取值含义：店铺信息，商品信息，商品编码，如“swmbyg001”的含义是狮王官方旗舰店的美白牙膏，牙膏编号001。

⑦ 存储处：商品信息表commodity。

2. 数据结构

数据结构的描述={数据结构名称，描述，数据结构组成，其他说明}

例如，“快递站信息表”数据结构的定义如下：

① 数据结构名称：快递站信息表express_station。

② 描述：包括商品收发快递站的信息。

③ 数据结构组成：快递名称+快递员电话+快递员姓名+快递员工号。

④ 其他说明：在系统功能扩充时可能增加定义项。

3. 数据存储

数据存储的描述={数据库存储名称，描述，数据存储组成，主码，相关联的处理}

例如，“商品价格”数据存储定义如下：

① 数据存储名称：商品价格。

② 描述：存放某商品的价格。

③ 数据存储组成：某商品的价格，由商品编号+商品名称+商品价格+商品属性+折扣组成。

④ 主码：商品编号，商品名称。

2.2 处理要求

1.卖家信息sellers表的管理：能够储存一定数量的卖家信息，记录卖家所提供的商品信息，实现对卖家基本信息的增、删、改、查。

2.买家信息buyers表的管理：能够记录一定数量的买家信息，实现对买家购买商品以及是否确认购买，是否确认收货进行记录，让卖家知道买家已经收到货，对相关信息进行增、删、改、查。

3.商品信息commodity表的管理：能够储存一定数量不同卖家的商品信息，实现对商品信息的增、删、查、改。

4.快递站信息express_station表的管理：记录一定数量的快递收发情况，实现对快递收发信息的处理，当买家收货成功后，将收货成功的信息传递给卖家。实现快递收发信息的增、删、改、查。

5.售后评价after_market_evaluate表的管理：这是在买家确认收货后，对商品的满意度进行评估，评价，退换货情况的增、删、查、改。

2.3 安全性与完整性要求

安全性：买家、卖家、快递站信息都是安全的，不会泄露个人信息，设置一定的权限，防止数据被删除，被修改。

完整性：输入的数据要合法，完整，有效，否则会出错，系统予以相应的提示，可以通过触发器实现。

Chapter3 概念结构设计 3.1 局部E-R图

在这里插入图片描述

3.2.全局E-R图

在这里插入图片描述

Chapter4 逻辑结构设计 4.1 关系模式

卖家（店铺名称，店铺地址，商品名称）

买家（买家姓名，买家电话，买家地址，购买商品编号，确认收货）

快递站（快递名称，快递员电话，快递员姓名，快递员工号）

商品（商品编号，商品名称，商品价格，商品属性，折扣）

售后评价（买家电话，商品编号，商品名称，评价，退货，换货）

注：有下划线的是主键，加粗的是外键。

4.2 设计数据表

1.卖家信息表：sellers

列名数据类型默认值主键？非空？店铺名称varchar(20)无是是店铺地址varchar(20)无否是商品名称varchar(50)无否是

2.买家信息表：buyers

列名数据类型默认值主键？非空？买家姓名varchar(20)无否是买家电话varchar(11)无是是卖家地址varchar(20)无否是购买商品编号varchar(20)无是是确认收货varchar(20)无否是

3.快递点信息表：express_station

列名数据类型默认值主键？非空？快递名称varchar(20)无否是快递员电话varchar(11)无否是快递员姓名varchar(20)无否是快递员工号varchar(10)无是是

4.商品信息表：commodity

列名数据类型默认值主键？非空？商品编号varchar(20)无是是商品名称varchar(20)无是是商品价格float无否是商品属性varchar(20)无否是折扣float1否否

5.售后评价表：after_market_evaluate

列名数据类型默认值主键？非空？注释商品编号varchar(20)无是是无商品名称varchar(20)无否是无评价int(10)0否否1为已评价，0为未评价退货int(10)0否否1为已评价，0为未评价换货int(10)0否否1为已评价，0为未评价买家电话varchar(11)无是是无 Chapter5 快递管理系统的实现 5.1 数据表的创建与完整性约束 5.1.1 数据表的创建

1.卖家信息表sellers

CREATE TABLE sellers ( 店铺名称 varchar(20) NOT NULL, 店铺地址 varchar(20) NOT NULL, 商品名称 varchar(50) NOT NULL, #PRIMARY KEY (店铺名称) ) ENGINE=InnoDB;

2.买家信息表buyers

CREATE TABLE buyers ( 买家姓名 varchar(20) NOT NULL, 买家电话 varchar(11) NOT NULL, 买家地址 varchar(20) NOT NULL, 购买商品编号 varchar(20) NOT NULL, 确认收货 tinyint(10) NOT NULL, PRIMARY KEY (买家电话,购买商品编号) ) ENGINE=InnoDB;

3.快递站信息表express_station

Create Table CREATE TABLE express_station ( 快递名称 varchar(20) NOT NULL, 快递员电话 varchar(11) NOT NULL, 快递员姓名 varchar(20) DEFAULT NULL, 快递员工号 varchar(10) NOT NULL, PRIMARY KEY (快递员工号) ) ENGINE=InnoDB;

4.商品信息表commodity

CREATE TABLE commodity ( 商品编号 varchar(20) NOT NULL, 商品名称 varchar(20) NOT NULL, 商品价格 float NOT NULL, 商品属性 varchar(20) NOT NULL, 折扣 float DEFAULT '1' COMMENT '默认不打折', PRIMARY KEY (商品编号,商品名称) ) ENGINE=InnoDB;

5.售后评价表after_market_evaluate

CREATE TABLE after_market_evaluate ( 商品编号 varchar(20) CHARACTER SET latin1 NOT NULL, 商品名称 varchar(20) NOT NULL, 评价 int(10) DEFAULT 0 COMMENT '1为已评价，0为未评价', 退货 int(10) DEFAULT 0 COMMENT '1为退货，0为不退货', 换货 int(10) DEFAULT 0 COMMENT '1为换货，0为不换货', 买家电话 varchar(11) NOT NULL, PRIMARY KEY (商品编号,买家电话) ) ENGINE=InnoDB; 5.1.2 完整性约束

1.设置主键

# 案例：将sellers表中的'店铺名称'设置为主键 use expressage; alter table sellers add primary key(店铺名称); -- 插入数据看看效果 insert into sellers values('狮王官方旗舰店','上海虹口','美白牙膏,酵素牙膏'); insert into sellers values('狮王官方旗舰店','广东广州','酵素牙膏，美白牙膏');

实验结果：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存失败,源站可能有防盗链机制,建议将图片保存下来直接上传下上传(iVLywx8YCtOc-1593046446490)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614093857007.png)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614093857007.png)]$

由上结果知，此案例主键约束有效。

2.设置外键

注意：

① 对子表、增、删改时，结果中的对象必须是父表中已经有的。

② 当父表更新或删除数据时，字表中与之对应的信息也会做相应的改变。

案例：将售后评价表after_market_evaluate中的商品编号设置为外键（父表为商品信息表commodity）

-- 创建外键约束 use expressage; alter table after_market_evaluate add constraint fk_商品编号 foreign key(商品编号) references commodity(商品编号); -- 检验外键约束 select 商品编号 from commodity; # ① 在子表中插入父表commodity中有的商品编号对应的数据 insert into after_market_evaluate values('adfkyxfs402','控油洗发水',0,0,0,'19844557788'); # ② 在子表中插入父表commodity中没有的商品编号对应的数据 insert into after_market_evaluate values('adfkyxfs404','去屑洗发水',0,0,0,'19766554422'); # ③ 在父表中更新某商品编号，子表中对应行作相应的变化 update commodity set 商品编号='swjsyg003' where 商品编号='swmbyg002';

实验结果：

① 插入父表中有的数据：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3gR5jG1p-1593046446491)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614101718193.png)]$

成功将数据插入到子表。

② 插入父表中没有的数据：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YfuUpfnY-1593046446491)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614101837681.png)]$

报错提示！

③ 在父表中原来商品编号为'swmbyg002'的商品，商品编号变为'swjsyg003'，子表中对应行的商品编号也变成'swjsyg003'作相应的变化。

step1：先查询父表commodity、子表after_market_evaluat中是否都含有此商品

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-to27m5lF-1593046446492)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614103427508.png)]$

step2：由结果知都含有，现在更新父表commodity中的数据

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-INJ0lywM-1593046446493)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614103519338.png)]$

step3：更新成功后再次查询，子表after_market_evaluat的商品名称也已经被更新了。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O4ymOIz0-1593046446493)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614103622924.png)]$

综上外键约束成功。

综上所述，此案例外键约束设置成功且有效。

3.非空约束

案例：对商品信息表commodity中的’折扣’，设置非空约束

-- 设置非空约束 alter table commodity change 折扣折扣 float not null; -- 检验插入不合法数据 insert into commodity values('adfftxfs405','防脱洗发水',58,'洗护',null);

实验结果：

① 折扣为null 插入异常

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DAfj54z1-1593046446494)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614104236110.png)]$

② 折扣设为1 插入正常

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LIxLvIaa-1593046446495)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614104410615.png)]$

综上，此案例非空约束成功。

4.唯一性约束

案例：由于快递点信息表express_station中，一个快递员对应唯一电话号，对其设置唯一性约束。

-- 设置唯一性约束 alter table express_station add unique(快递员电话); -- 检验插入两条电话号相同的记录 insert into express_station values('顺丰','13267678989','张小二','sf005'); insert into express_station values('韵达','13267678989','张小五','yd003');

实验结果：

① 唯一性约束已设置

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TPIEjwGA-1593046446496)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614110106087.png)]$

② 插入两条电话号相同的记录检验

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TnTDhSwR-1593046446496)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614110152606.png)]$

综上，此案例的唯一性约束成功。

5.检查约束

案例1：由于手机号码是11位数字组成，对buyers表中的’买家电话’添加检查约束，步骤如下：

-- 设置检查约束 alter table buyers add constraint check_phone_num check(length(买家电话)=11); -- 插入不合法数据检验 INSERT INTO buyers VALUES('Jackson','1389880000','浙江温州','antapbx801',1);

实验结果：

检查约束建立已设置。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mdYaNBY5-1593046446497)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614092000577.png)]$

实验发现，用check()检查约束在插入'买家电话'错误时还是可以插入，这是由于插入字段范围比较大，所以只能用触发器来代替这一检查约束，具体做法如下：

创建触发器phone_check：

-- phone_check 触发器，当插入电话号码不合法时，给出提示。 delimiter // create trigger phone_check before insert on buyers for each row begin if length(new.买家电话) = 11 then set new.买家电话 = '电话号码错误，请检查！'; end if; end// delimiter; -- 检验触发器是否有用插入不合法数据 INSERT INTO buyers VALUES('Jackson','1389880000','浙江温州','antapbx801',1);

实验结果如下：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GX595OLR-1593046446498)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614092458564.png)]$

触发器已经创建，接下来进行检验：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ton5yLrf-1593046446499)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614092536631.png)]$

由上代码可看出，报错提醒！即触发器有效。

5.2 向表中插入数据 # 指定当前数据库 use expressage; 5.2.1 插入数据案例 #案例1.向sellers表中插入数据插入一条为例 insert into sellers values('狮王官方旗舰店', '上海虹口', '美白牙膏,酵素牙膏'); #案例2.向buyers表中插入数据插入多条数据 insert into buyers values ('Queenie', '19857187757', '浙江杭州', '防晒霜', 1), ('Lisa', '19957185577', '上海浦东', '运动鞋', 1), ('秦肖', '19838785424', '甘肃兰州', '香氛洗发水', 1); #案例3.向express_station表中插入数据 insert into express_station values('顺丰','13256564433','张三','sf001'); #案例4.向commodity表中插入数据 insert into commodity values('swmbyg002', '酵素牙膏', 35, '洗护', 0.9); #案例5.向after_market_evaluate表中插入数据 insert into after_market_evaluate values('顺丰', '13544553232', '孙六', 'sf003');

注意，可以用select检验是否插入，例如：

select * from commodity where 商品编号='swmbyg002';

结果如下：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wl8Fb5B4-1593046446499)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614003604025.png)]$

也就是说数据成功插入表commodity中（其他的也检验类似）。

5.2.2 插入所有数据后的所有表数据 select * from sellers; select * from buyers; select * from express_station; select * from commodity; select * from after_market_evaluat;

注意：由于数据比较多，具体见附录.[^1]

5.3 删除表中数据 #案例1：删除buyers表中未确认收货的买家 delete from buyers where 确认收货=0; #案例2：由于某商品（假设是蜜之番旗舰店的开心果，‘mzjkxg904’）已下架，在commodity表中将其删除 delete from commodity where 商品编号='mzjkxg904'; #案例3：删除express_station表中快递名称为“韵达”的记录 delete from express_station where 快递名称='韵达'; -- 注意：可通过以下查询检查是否已删除数据 select * from buyers; select * from commodity; select * from express_station;

例如，验证案例2

select * from commdity where 商品编号='mzjkxg904';

结果如下：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6YADJG7E-1593046446500)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614005618727.png)]$

上述结果说明删除成功（其他删除操作类似）。

5.4 更改表中数据

案例1：某个买家（电话号为’19852008899’，商品名称为’葡萄干’）在收货成功后，没有立刻评价，过了几天给出评价，因此要跟新after_market_evaluate表的相关行数据。

-- 更新数据 update after_market_evaluate set 评价=1 where 买家电话='19852008899' and 商品名称='葡萄干'; -- 查看数据是否被更新 select *from after_market_evaluate where 买家电话='19852008899' and 商品名称='葡萄干';

结果如下：

1. 更新 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k3o0swZl-1593046446502)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614010211073.png)]$

2. 检验更新 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JYi3WZNc-1593046446503)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614010235717.png)]$

案例2：买家’苏叶’填错了地址，现在需要更改为’北京东城区’。

-- 更新数据 update buyers set 买家地址='北京东城区' where 买家姓名='苏叶'; -- 查看数据是否被更新 select *from buyers where 买家姓名='苏叶';

结果如下：

1. 更新 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N5RfXDCj-1593046446504)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614010652300.png)]$

2. 检验更新 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kcTuIpoH-1593046446504)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614010712709.png)]$

5.5 查询 5.5.1 基础查询

1.查询表中的字段

select 买家姓名 from buyers; -- 查询单个字段 select 买家姓名,买家电话 from buyers; -- 查询多个字段 select * from buyers; -- 查询所有字段

2.给字段起别名

select 买家姓名 as b_name from buyers; -- 使用as select 买家姓名 b_name,买家电话 b_phone from buyers; -- 使用空格

3.去重

# 案例：查询buyers表中所有买家 select distinct 买家姓名 from buyers;

4.拼接字段，concat和+

案例：查询buyers表，将买家姓名、电话、地址连接成一个字段并命名为‘买家信息’

-- 拼接+ select 买家姓名+','+买家电话+','+买家地址 as 买家信息 from buyers; -- 拼接concat select concat(买家姓名,',',买家电话,',',买家地址) as 买家信息 from buyers;

实验结果：

① 拼接+ ：不报错但不是我们想要的结果

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z7A2lgRd-1593046446505)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614113539619.png)]$

② 拼接concat：成功 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DZk7US4C-1593046446506)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614113513005.png)]$

5.5.2 条件查询

1.按条件表达式查询

条件运算符：> < = != >= 100的商品信息。

select * from commodity where 商品价格>100;

实验结果： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DslMJKLg-1593046446507)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614124853015.png)]$

2.按逻辑表达式查询

逻辑运算符：&& || ！

案例：查询商品价格在不在20~150之间的商品信息

select * from commodity where 商品价格>150 or 商品价格80) as C where C.商品价格*C.折扣 > 60;

实验结果： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cOAM1OIf-1593046446519)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614160951673.png)]$

3.where子句中的子查询

案例：查询折后价低于原价的商品，即查询打折商品的商品编号，商品名称，折后价。

select 商品编号,商品名称,round(商品价格*折扣,2) 折后价 from commodity as a where round(商品价格*折扣,2) 100);

实验结果： commodity02表的结构和commodity一样，起始表commodity02是空表，插入后结果如下：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-laranvQ7-1593046446521)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614174536507.png)]$

（2）利用子查询更新数据

案例：将折扣=1的折扣变为0.95

update commodity02 set 折扣 = 折扣*0.95 where 商品编号 in (select 商品编号 from commodity where 折扣=1);

实验结果： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q5Txb1W1-1593046446522)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614174756150.png)]$

（3）利用子查询删除数据

案例：将折扣为1的商品删除

delete from commodity02 where 商品编号 in (select 商品编号 from commodity where 折扣=1);

实验结果： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M6GpoFxS-1593046446523)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614174832626.png)]$

5.6 预处理SQL语句

案例：利用预处理SQL输出buyers表中的前两行记录。

SET @a=2; PREPARE tr FROM 'select *from buyers limit ?'; EXECUTE tr USING @a; DEALLOCATE PREPARE tr;

实验结果： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kjAJMdcK-1593046446524)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614180958074.png)]$

5.7 函数 5.7.1 自定义函数

案例1：创建一个名为func_goods的函数返回指定商品编号对应的商品价格。

USE expressage; DELIMITER && CREATE FUNCTION func_goods(c_no VARCHAR(20)) RETURNS float BEGIN RETURN (SELECT 商品价格 FROM commodity WHERE 商品编号 = c_no); END&& DELIMITER ; SELECT func_goods('mzjkxg904');

案例2：创建函数func_price_evaluate，若价格>300输出1；若300>价格>100输出2，否则输出3.

USE expressage; DELIMITER // CREATE FUNCTION func_price_evaluate(a int) RETURNS int NO SQL BEGIN IF a>300 THEN SET a=1; ELSEIF a200 显示’expensive!’，否则显示’cheap!’

USE expressage; SELECT 商品编号,商品名称,商品价格,IF(商品价格>100,'Expensive!','Cheap!') FROM commodity LIMIT 5;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wmu2GsPe-1593046446525)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20200614184300395.png)]$

5.8 存储过程与触发器 5.8.1 存储过程

案例1：创建存储过程num_goods,统计指定商品属性的商品数。

DELIMITER // CREATE PROCEDURE num_goods(IN gname VARCHAR(20), OUT num_of_g INT) READS SQL DATA BEGIN SELECT COUNT(*) INTO num_of_g FROM commodity WHERE 商品属性 = gname; END// DELIMITER ; CALL num_goods('洗护',@c_num); SELECT @c_num;

案例2：创建存储过程proc_goods从数据库expressage中的commodity表中检索出所有商品属性为“洗护”的商品名称商品编号。

DELIMITER // CREATE PROCEDURE proc_goods() READS SQL DATA BEGIN SELECT 商品编号,商品名称 FROM commodity WHERE 商品属性='洗护'; END// DELIMITER ; CALL proc_goods();

案例3：创建存储过程 price_evaluate,输入商品属性，查看该类商品价格>100的商品数超过2 输出 Expensive! 并输出商品信息，否则输出 Cheap!

DELIMITER // CREATE PROCEDURE price_evaluate(IN cno char(20),OUT evaluate CHAR(20)) BEGIN DECLARE A TINYINT DEFAULT 0; SELECT COUNT(*) INTO A FROM commodity WHERE 商品属性=cno AND 商品价格>100; IF A>=2 THEN BEGIN SET evaluate='Expensive!'; SELECT * FROM commodity WHERE 商品属性=cno ; END; ELSEIF A

【本文地址】

公司简介

联系我们