第一部分
一、实验内容和结果
1、顾客user(顾客id,姓名、性别、会员卡号、手机号、卡内余额、可用积分、上次消费时间)自增从10000开始,为什么?如何设置
CREATE TABLE USER(
uid INT AUTO_INCREMENT PRIMARY KEY,
uname VARCHAR(20)NOT NULL,
sex VARCHAR(20),
card_number VARCHAR(20),
phone VARCHAR(20),
card_balance DECIMAL(10,2),
points INT,
last_purchase_date DATE
)AUTO_INCREMENT=10000;
- 货品名称表goods(货品id,货品名称,计量单位,货品条码,零售价,促销价,名称缩写(由触发器自行维护),货品状态)
CREATE TABLE goods(
gid INT PRIMARY KEY,
gname VARCHAR(20)NOT NULL,
unit VARCHAR(20)NOT NULL,
barcode VARCHAR(20)NOT NULL,
retail_Price DECIMAL(10,2),
promotional_Price DECIMAL(10,2),
abbreviations VARCHAR(20)NOT NULL,
STATUS TINYINT);
- 供应商信息表supplier(供应商id,供应商名称,联系人,联系电话,供应商状态)
CREATE TABLE supplier(
supplierID INT PRIMARY KEY,
supplier_Name VARCHAR(50)NOT NULL,
contact_Person VARCHAR(20)NOT NULL,
contact_Number VARCHAR(20)NOT NULL,
supplier_Status TINYINT);
4、收银员(收银员id、收银员姓名、收银员手机号)
CREATE TABLE cashier(
cashierid INT PRIMARY KEY,
cashier_Name VARCHAR(20)NOT NULL,
phone VARCHAR(20)NOT NULL);
5、收银记录表cashaccount(收银id,收银员id,顾客id,扎帐id,收银时间,支付方式,应收金额,实收金额,优惠金额,销售状态(正常 ,已退货))
CREATE TABLE cashaccount(
cashaccountid INT PRIMARY KEY AUTO_INCREMENT,
cashierid INT,
uid INT,
settle_accountstid INT,
cashier_time DATETIME,
payment TINYINT,
amount_money DECIMAL(18,3),
actual_money DECIMAL(18,3),
discount_money DECIMAL(18,3),
sales_status TINYINT
);
- 收银明细表cashaccount_detil(收货明细id,收银id,货物id,销售数量,零售价、促销价)
CREATE TABLE cashaccount_detil(
cashaccountdetailid INT PRIMARY KEY,
cashaccountid INT,
gid INT,
salesquantity DECIMAL(18,3),
retailprice DECIMAL(18,3),
promotionalprice DECIMAL(18,3));
7、货品库存表stock(库存id,货品id,(当前)库存数量,昨日库存(用于日清),月初库存(用于月结))
CREATE TABLE stock(
stockid INT PRIMARY KEY,
gid INT,
quantity DECIMAL(18,3),
yesterday_quantity DECIMAL(18,3),
month_quantity DECIMAL(18,3));
8、扎帐记录表Settle_accounts(扎帐id,收银员id,扎帐时间,应收金额(由系统计算),实收金额(由收银员输入),差错原因)
CREATE TABLE Settle_accounts(
settle_accountsid INT PRIMARY KEY,
cashierid INT,
settle_accounts_time DATETIME,
amount_money DECIMAL(18,3),
actual_money DECIMAL(18,3),
errorReason VARCHAR(100));
9、辅助表 汉字拼音表Hzpyb(汉字,简拼,拼音)
CREATE TABLE Hzpyb(
hz VARCHAR(6),
jp VARCHAR(3),
py VARCHAR(30));
- 货品名称表goods
INSERT INTO goods VALUES(1,"可口可乐","瓶","101",3.00,2.50,"kkkl",0),
(2,"百事可乐","瓶","102",3.00,2.50,"bskl",0),
(3,"伊利牛奶","盒","103",5.00,4.50,"ylnn",0),
(4,"农夫山泉","瓶","105",2.00,1.50,"nfsq",0),
(5,"涪陵榨菜","袋","106",1.00,0.80,"flzc",0),
(6,"青岛啤酒","听","107",5.0,4,"qdpj",0)
- 收银员表cashier
INSERT INTO cashier VALUES(1,"王小小","13683868928")
- 收银记录表cashaccount
INSERT INTO cashaccount VALUES(1,1,0,1,"2024-03-01",0,6.000,5.000,1.000,0),
(2,1,0,1,"2024-03-11",0,12.000,10.600,1.400,0)
3、收银明细表symxb
INSERT INTO cashaccount_detil VALUES(1,1,1,1.000,3.000,2.500),
(2,1,2,1.000,3.000,2.500),
(3,2,5,2.000,1.000,0.800),
(4,2,3,2.000,5.000,4.500)
4、扎帐记录表zzjlb
INSERT INTO Settle_accounts VALUES(1,1,"2024-03-11 10",15.600,15.600,"无差错")
- 汉字拼音表Hzpyb
根据发的脚本文件导入数据
其他表数据自行插入。
- 建立视图
1、货品库存数量v_goods_stock
包括货品id,货品名称、计量单位、库存数量
CREATE VIEW v_goods_stock
AS SELECT goods.`gid`,goods.gname,unit,stock.quantity
FROM goods,stock
WITH CHECK OPTION
2、收银明细视图
包括:明细id,收银id,货品名称、计量单位、销售数量、单价、零售价等
CREATE VIEW symx
AS SELECT cashaccount_detil.`cashaccountdetailid`,cashaccount.cashaccountid,gname,unit,salesquantity,promotionalprice,retailprice
FROM cashaccount_detil,goods,cashaccount
WITH CHECK OPTION
第二部分
实验二:函数、存储过程、触发器练习
一、实验要求:
- 理解函数、存储过程、触发器的概念
- 掌握函数、存储过程、触发器的语法格式、使用方法
- 掌握函数、存储过程、触发器的创建、执行
- 能够使用函数、存储过程、触发器处理实际系统中的业务逻辑
二、环境准备:
进销存管理系统数据库salesmanagement中的货品表、货品库存表结构如下。
表2-1 货品名称表(goods)
Field | Type | Comment |
gid | int | 货品id |
gname | varchar(20) | 货品名称 |
unit | varchar(20) | 计量单位 |
barcode | varchar(20) | 货品条码 |
retail_Price | decimal(10, 2) | 零售价 |
promotional_Price | decimal(10, 2) | 促销价 |
abbreviations | varchar(20) | 名称缩写(由触发器自行维护) |
Status | tinyint | 货品状态 (0正常,1暂停进货(不能购进,但可以销售),2不再进货(不能购进,也不能销售)) |
表2-2货品库存表stock
Field | Type | Comment |
stockid | int | 库存id |
gid | Int | 货品id,外键 |
quantity | decimal(18, 3) | (当前)库存数量 |
yesterday_quantity | decimal(18, 3) | 昨日库存(用于日清),系统自动维护 |
month_quantity | decimal(18, 3) | 月初库存(用于月结),系统自动维护 |
表中的数据如下表所示:
表2-3货品名称表(goods)数据
gid | gname | unit | barcode | retail_Price | promotional_Price | abbreviations | STATUS |
1 | 可口可乐 | 瓶 | 101 | 3 | 2.5 | kkkl | 0 |
2 | 百事可乐 | 瓶 | 102 | 3 | 2.5 | bskl | 0 |
3 | 伊利牛奶 | 盒 | 103 | 5 | 4.5 | ylnn | 0 |
4 | 农夫山泉 | 瓶 | 105 | 2 | 1.5 | nfsq | 0 |
5 | 涪陵榨菜 | 袋 | 106 | 1 | 0.8 | flzc | 0 |
6 | 青岛啤酒 | 听 | 107 | 5 | 4 | qdpj | 0 |
7 | 牙刷 | 支 | 1009 | 3 | 2.5 | yss | 0 |
9 | 牙膏 | 支 | 1010 | 30 | 25 | yggg | 0 |
10 | 毛巾 | 条 | 1011 | 20 | 15 | mjj | 0 |
表2-4货品库存表stock数据
stockid | gid | quantity | yesterday_quantity | month_quantity |
2 | 10 | 20 | 0 | 0 |
3 | 1 | 10 | 0 | 0 |
4 | 2 | 11 | 0 | 0 |
5 | 3 | 30 | 0 | 0 |
6 | 4 | 40 | 0 | 0 |
7 | 5 | 30 | 0 | 0 |
8 | 6 | 50 | 0 | 0 |
9 | 7 | 60 | 0 | 0 |
10 | 9 | 70 | 0 | 0 |
一、事件
(1)创建一个事件,10秒后,用库存更新昨日库存。
DELIMITER $$
CREATE EVENT `salesmanagement`.`direct_happen`
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
DO
BEGIN
UPDATE `stock` SET `yesterday_quantity`=`quantity`;
END$$
DELIMITER ;
在做上面语句前,先查看一下系统变量:SELECT @@event_scheduler;
如果值为on,表示,已经开启事件,否则,需要更改变量的值。
Set @@event_scheduler=on
打卡stock表,发现昨日库存已经更新。
(思考与练习 ]
- 创建一个2024年3月15日上午12:40(这个时间大家灵活掌握)点执行的事件,跟新stock的月初库存为现在的库存信息。
- 创建一个从下个月最后一天24点开始到2025年12月30日结束、每个月执行一次的事件,每次用当前库存更新月初库存。
二、常量及系统函数使用
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)创建一个名为@quantity的用户变量,查询货品编号为1的库存到@quantity变量中。
use salesmanagement;
SELECT quantity INTO @quantity
FROM goods,stock
WHERE goods.gid=stock.gid AND goods.gid=1;
SELECT @quantity;
或者用下面命令。
SET @quantity=(SELECT quantity
FROM goods,stock
WHERE goods.gid=stock.gid AND goods.gid=1);
SELECT @quantity;
用户变量不需要定义,可以直接使用。执行完该语句后可使用SELECT语句查询变量quantity的值。
(2)定义二个变量,分别用于获取编号为1的商品的零售价,促销价。
SELECT retail_Price ,
promotional_Price
FROM goods WHERE goods.gid=1 INTO @retail_Price,@promotional_Price;
SELECT @retail_Price,@promotional_Price;
4.运算符的使用
(1)使用算术运算符“-”查询1号货品的优惠额。
SELECT retail_Price - promotional_Price
FROM goods WHERE goods.gid=1;
(2)使用比较运算符“>”查询昨日库存大于库存的商品名称。
5.系统内置函数的使用
(1)获区字符串的长度。
SELECT CHAR_LENGTH('可口可乐');
(2)使用CONCAT()函数连接两个字符串。
SELECT CONCAT(‘I love', MySQL’);
(3)取字串
SELECT SUBSTR('可口可乐',1,1);
(4)获得当前的日期和时间。
select NOW();
三、存储函数
(1)创建PysxCx函数,能够根据输入的汉字,返回其拼音首字母缩写
四、触发器
(1)在goods表上创建触发器,实现货品名称缩写的自动维护
(2)添加货品时,自动在库存中添加一条对应数据
(3)删除货品时,自动在库存中删除对应库存数据
五、使用存储过程处理进销存系统中的简单业务逻辑
(1)创建货品信息维护goodsWh存储过程,能够实现对货品信息的增删改操作
- 创建货品信息查询goodsCx存储过程,能够实现对货品信息的查询操作
(3)参照goodsWh、goodsCx存储过程分别创建2个存储过程,实现对user表信息的增删改及查询操作。
表3-1 顾客表(user)
Field | Type | Comment |
uid | int | 顾客id,从10000开始 |
uname | varchar(20) | 姓名 |
sex | varchar(20) | 性别 |
card_number | varchar(20) | 会员卡号 |
phone | varchar(20) | 手机号 |
card_balance | decimal(10, 2) | 卡内余额 |
points | int | 积分 |
last_purchase_date | date | 上次消费时间 |
第二部分续写
一、实验内容和结果
1、事件操作截图
DELIMITER $$
CREATE EVENT `jxcglxt`.`monthly_inventory_update`
ON SCHEDULE
EVERY 1 MONTH
STARTS TIMESTAMP(CONCAT(DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%m-'), '01 00:00:00'))
ENDS '2025-12-30 23:59:59'
DO
BEGIN
UPDATE `stock`
SET `month_quantity` = `quantity`;
END$$
DELIMITER ;
- 常量及系统函数使用操作截图
1.常量的使用
(1)计算194和142的乘积,可使用如下SQL语句:
(2)获取以下这串字母的值: 'i\nlove\nMySQL'。
2.系统变量的使用
(1)获得现在使用的MySQL版本。
(2)获得系统当前的时间。
3.用户变量的使用
(1)创建一个名为@quantity的用户变量,查询货品编号为1的库存到@quantity变量中。
- 定义二个变量,分别用于获取编号为1的商品的零售价,促销价。
4.运算符的使用
(1)使用算术运算符“-”查询1号货品的优惠额。
(2)使用比较运算符“>”查询昨日库存大于库存的商品名称。
5.系统内置函数的使用
(1)获区字符串的长度。
(2)使用CONCAT()函数连接两个字符串。
(3)取字串
(4)获得当前的日期和时间。
3、创建PysxCx函数,能够根据输入的汉字,返回其拼音首字母缩写,操作截图
DELIMITER $$
CREATE
FUNCTION `jxcglxt`.`pysxcx`(zw CHAR(50))
RETURNS CHAR(50)
READS SQL DATA
BEGIN
SET @l=CHAR_LENGTH(zw);
SET @pysx='';
SET @i=1;
WHILE @i<=@l DO
SET @hz=SUBSTR(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 ;
4、触发器操作截图
(1)在goods表上创建触发器,实现货品名称缩写的自动维护
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `jxcglxt`.`Update_mcsx_before_insert_goods`
BEFORE INSERT
ON `jxcglxt`.`goods`
FOR EACH ROW BEGIN
SET new.`abbreviations`=`pysxcx`(new.gname);
END$$
DELIMITER ;
(2)添加货品时,自动在库存中添加一条对应数据
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `jxcglxt`.`Insert_into_stock_after_insert_name` AFTER INSERT
ON `jxcglxt`.`goods`
FOR EACH ROW BEGIN
INSERT INTO stock(gid) VALUES(new.gid);
END$$
DELIMITER ;
(3)删除货品时,自动在库存中删除对应库存数据
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `jxcglxt`.`delete_from_stock_after_delete_goods` AFTER DELETE
ON `jxcglxt`.`goods`
FOR EACH ROW BEGIN
DELETE FROM stock WHERE gid=old.gid;
END$$
DELIMITER ;
5、存储过程操作截图
(1)创建货品信息维护goodsWh存储过程,能够实现对货品信息的增删改操作
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`goodsWh`(
gid INT,gname VARCHAR(20),
unit VARCHAR(20),barcode VARCHAR(20),
retail_Price DECIMAL(10,2),
promotional_Price DECIMAL(10,2),
STATUS TINYINT
)
BEGIN
IF gid=0 THEN
INSERT INTO goods(`gname`,`unit``barcode`,
`retail_Price`,`promotional_Price`,
`Status`)VALUES
(gname,unit,barcode,retai_Price,STATUS);
ELSEIF gid>0 THEN
UPDATE goods SET `goods`.`gname`=gname,
`goods`.`unit`=unit,`goods`.`barcode`=barcode,
`goods`.`retail_Price`=retail_Price,
`goods`.`promotional_Price`=promotional_Price,
`goods`.`Status`=`Status`
WHERE goods.`gid`=gid;
ELSE
DELETE FROM goods WHERE goods.gid=-gid;
END IF;
END$$
DELIMITER ;
- 创建货品信息查询goodsCx存储过程,能够实现对货品信息的查询操作
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`goodsCx`(gid INT)
BEGIN
IF gid=0 THEN
SELECT * FROM goods;
ELSE
SELECT * FROM goods WHERE goods.`gid`=gid;
END IF;
END$$
DELIMITER ;
6、使用存储过程处理进销存系统中的简单业务逻辑操作截图
3)参照goodsWh、goodsCx存储过程分别创建2个存储过程,实现对user表信息的增删改及查询操作。
1.userwh存储过程:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`userwh`(uid INT,uname VARCHAR(20),
sex VARCHAR(20),card_number VARCHAR(20),phone VARCHAR(20),
card_balance DECIMAL(10,2),points INT,last_purchase_date DATE)
BEGIN
IF uid=0 THEN
INSERT INTO `user`(`uname`,`sex`,`card_number`,
`phone`,`card_balance`,`points`,`last_purchase_date`)
VALUES(uname,sex,card_number,phone,
card_balance,points,last_purchase_date);
ELSEIF uid>0 THEN
UPDATE USER SET `user`.`uname`=uname,`user`.`sex`=sex,
`user`.`card_number`=card_number,`user`.`phone`=phone,
`user`.`card_balance`=card_balance,`user`.`points`=points,
`user`.`last_purchase_date`=last_purchase_date
WHERE `user`.`uid`=uid;
ELSE
DELETE FROM USER WHERE `user`.`uid`=-uid;
END IF;
END$$
DELIMITER ;
- usercx存储过程
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`usercx`(uid INT)
BEGIN
IF uid=0 THEN
SELECT * FROM `user`;
ELSE
SELECT * FROM `user` WHERE `user`.`uid`=uid;
END IF;
END$$
DELIMITER ;
第三部分
- 实验内容和结果
1.索引操作截图
创建索引
查看索引
删除索引
2.视图操作截图
创建视图
查询视图
查看视图
删除视图
3.存储过程和函数操作截图
创建存储过程
调用存储过程
查看存储过程
删除存储过程
定义变量
If条件判断
传递参数
Case结构
While循环
Repeat结构
Loop语句和leave语句
游标光标
存储函数
4.触发器操作截图
首先创建一张日志表 :
创建 insert 型触发器,完成插入数据时的日志记录 :
创建 update 型触发器,完成更新数据时的日志记录 :
创建delete 行的触发器 , 完成删除数据时的日志记录 :
测试:
删除触发器:
查看触发器:
第四部分
一、实验内容和结果
1、把字符串'1,1,2,2,3,3,4,1,'还原为表,写出存储过程。代码及运行截图。
delimiter $$
CREATE DEFINER = CURRENT_USER PROCEDURE `xsjlcp`(xsmx varchar(100))
BEGIN
drop table if exists lsb;
CREATE TEMPORARY table lsb(gid int,xssl NUMERIC(18,3));
WHILE (xsmx<>'') DO
set @k=POSITION(',' in xsmx);
set @gid=LEFT(xsmx,@k-1);
set xsmx=SUBSTR(xsmx,@k+1);
set @k=POSITION(',' in xsmx);
set @xxsl=LEFT(xsmx,@k-1);
insert into lsb values(@gid,@xxsl);
set xsmx=SUBSTR(xsmx,@k+1);
END WHILE;
select * from lsb;
select barcode,gname,unit,retail_Price,promotional_Price,xssl,xssl*promotional_Price as 金额 from
goods,lsb where goods.gid=lsb.gid;
END$$
delimiter ;
2、实现销售过程的存储过程,代码及运行截图
delimiter $$
create PROCEDURE xsjlcp_new(cashierid int,uid int,payment TINYINT,xsmx varchar(8000))
begin
drop table if exists lsb;
create TEMPORARY table lsb(gid int,xssl NUMERIC(18,3));
while (xsmx>'') do
set @k=POSITION(',' in xsmx);
set @gid=LEFT(xsmx,@k-1);
set xsmx=SUBSTR(xsmx,@k+1);
set @k=POSITION(',' in xsmx);
set @xssl=LEFT(xsmx,@k-1);
end while;
select SUM(xssl*retail_Price) into @amount_money from lsb,goods where lsb.gid=goods.gid;
select SUM(xssl*promotional_Price) into @actual_money from lsb,goods where lsb.gid=goods.gid;
set @discount_money=@amount_money-@actual_money;
INSERT INTO cashaccount(cashierid,uid,payment,amount_money,discount_money) values
(cashierid,uid,payment,@amount_money,@discount_money);
INSERT INTO cashaccount_detail(cashaccountid,gid,salesquantity,retailprice,promotionalprice)
select @@identity,lsb.gid,xssl,retail_Price,promotional_Price from lsb,goods where lsb.gid=goods.gid;
drop table if exists lsb1;
create TEMPORARY table lsb1(gid int,xssl NUMERIC(18,3));
INSERT into lsb1(gid,xssl) select gid,SUM(xssl) from lsb GROUP BY gid;
UPDATE stock b1,lsb1 b2 set b1.quantity=b1.quantity-b2.xssl where b1.gid=b2.gid;
if uid>=10000 THEN
UPDATE `user` SET card_banlance=card_banlance-@actual_money,
points=points+FLOOR(@actual_money)
where `user`.uid=@uid;
END IF;
END$$
delimiter;
CALL `xsjlcp_new`(1,10000,0,'1,1,2,2,3,2,')
第五部分
一、实验内容和结果
1、销售记录、销售明细查询的存储过程代码及运行截图。
DELIMITER $$
DROP PROCEDURE IF EXISTS `SyjlCx`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SyjlCx`(cxlb INT,cxcs INT)
BEGIN
IF cxlb=0 THEN
SELECT `cashaccountid`,`cashierid`, `cashier_time`,`uid`,`amount_money`,`actual_money`,`discount_money`,CASE `payment`
WHEN 0 THEN '现金'
WHEN 1 THEN '储值卡'
WHEN 2 THEN '支付宝'
WHEN 3 THEN '微信支付'
END AS 支付方式
FROM `cashaccount` WHERE `cashierid`= cxcs AND `cashier_time`>=CURDATE() ORDER BY `cashaccountid` DESC;
ELSE
SELECT * FROM v_xsjlmcb WHERE v_xsjlmcb.cashaccountid=cxcs;
END IF;
END$$
DELIMITER ;
成功截图;
- 当天退货处理的存储过程代码及运行截图(可以用存储过程,或者触发器,写一个即可)。
代码成功截图:
代码:
DELIMITER $$
DROP PROCEDURE IF EXISTS `ThjlCp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ThjlCp`(cashaccountid INT)
BEGIN
SELECT `cashaccount`.`uid` INTO @uid FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=cashaccountid;
SELECT `cashaccount`.`actual_money` INTO @ssje FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=cashaccountid;
/*变更 余额与积分*/
IF @uid>=10000 THEN
UPDATE `user` SET `card_balance`=`card_balance`+@ssje,`points`=`points`-FLOOR(@ssje)
WHERE `user`.`uid`=@uid;
END IF;
-- 还原库存(update `stock`)
DROP TABLE IF EXISTS lsb;
CREATE TEMPORARY TABLE lsb (gid INT,xssl NUMERIC(18,3));
INSERT INTO lsb (gid,xssl) SELECT gid,SUM(`salesquantity`) FROM `cashaccount_detail`
WHERE `cashaccount_detail`.`cashaccountid`=cashaccountid GROUP BY gid;
UPDATE stock b1,lsb b2 SET b1.`quantity`=b1.quantity+b2.xssl WHERE b1.gid=b2.gid;
-- 删除销售明细 (delete from `cashaccount_detail`)
DELETE FROM `cashaccount_detail` WHERE `cashaccount_detail`.`cashaccountid`=cashaccountid;
-- 删除收银记录 (delete from `cashaccount`)
DELETE FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=cashaccountid;
END$$
DELIMITER ;
验证截图:
销售一笔商品:
验证退货处理:
- 扎帐过程的存储过程代码及运行截图。
运行成功的截图代码
代码:
DELIMITER $$
DROP PROCEDURE IF EXISTS `ZzjlCp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ZzjlCp`(syyid INT,ssje DECIMAL(18,3))
BEGIN
-- 计算应收金额
SELECT SUM(`cashaccount`.`actual_money`)
INTO @yjje FROM `cashaccount`
WHERE `cashaccount`.`cashierid`=syyid
AND `payment`=0 AND `settle_accountsid` IS NULL;
-- 插入扎帐记录
IF ABS(@yjje-ssje)>0.01 THEN
INSERT INTO `settle_accounts`
(cashierid,amount_money,actual_money,errorReason
) VALUE (syyid,@yjje,ssje,'出差错');
ELSE
INSERT INTO `settle_accounts`
(cashierid,amount_money,actual_money) VALUE (syyid,@yjje,ssje);
END IF;
SET @zzid=@@identity;
-- 更新 收银记录表中的zzid;
UPDATE `cashaccount` SET `settle_accountsid`=@zzid
WHERE `cashaccount`.`cashierid`=syyid
AND `settle_accountsid` IS NULL;
END$$
DELIMITER ;
验证这个存储过程;
运行成功
查看一下扎帐记录表:
- 事后退货处理的存储过程代码及运行截图。
代码以及运行成功截图:
完整代码:
DELIMITER $$
CREATE
PROCEDURE `jxcglxt`.`thjlcp_new2`(syid INT)
BEGIN
IF syid>0 THEN /*当天退货*/
DELETE FROM cashaccount WHERE cashaccount.cashaccountid=syid;
ELSE /*事后退货*/ /*增加一条负销售*/
INSERT INTO cashaccount(cashierid,uid,payment,amount_money,actual_money,discount_money,sales_status)
SELECT cashierid,uid,payment,-amount_money,-actual_money, -discount_money,-1
FROM cashaccount WHERE `cashaccount`.`cashaccountid`=-syid;
SET @syid_new=@@identity;
/*修改销售状态,以避免多次退货*/
UPDATE cashaccount SET sales_status=@syid_new
WHERE cashaccount.cashaccountid=-syid;
/*在销售明细表中插入负销售*/
INSERT INTO cashaccount_detil
(cashaccountid,gid,salesquantity,retailprice,promotionalprice)
SELECT @syid_new,gid,-salesquantity,retailprice,promotionalprice
FROM `cashaccount_detil`
WHERE `cashaccount_detil`.`cashaccountid`=-syid;
/*变更库存*/
DROP TABLE IF EXISTS lsb;
CREATE TEMPORARY TABLE lsb (gid INT,xssl NUMERIC(18,3));
INSERT INTO lsb (gid,xssl) SELECT gid,SUM(salesquantity) FROM cashaccount_detil
WHERE cashaccount_detil.cashaccountid=-syid
GROUP BY gid ;
UPDATE stock b1,lsb b2
SET b1.`quantity`=b1.`quantity`+b2.xssl
WHERE b1.gid=b2.gid;
/*如果是会员,还原积分*/
SELECT `uid` INTO @uid FROM `cashaccount`
WHERE `cashaccount`.`cashaccountid`=-syid;
SELECT `actual_money` INTO @ssje
FROM `cashaccount`
WHERE `cashaccount`.`cashaccountid`=-syid;
IF @uid>=10000 THEN
UPDATE `user`
SET `card_balance`=`card_balance`+@ssje,
`points`=`points`-FLOOR(@ssje)
WHERE `user`.`uid`=@uid;
END IF;
END IF;
END$$
DELIMITER ;
验证:
第六部分
- 回答下面问题
- InnoDB 、 MyISAM 、 MEMORY、 MERGE有什么不同?
InnoDB:是MySQL默认的存储引擎,支持事务处理和行级锁定,提供高并发性和数据完整性。它适用于大部分应用场景,特别是需要事务支持的应用,如电子商务、金融系统等。
MyISAM:是MySQL的另一种存储引擎,不支持事务处理,但在读操作上有很好的性能表现。它适用于读密集型的应用,如博客、新闻网站等。
MEMORY:也称为HEAP,将数据存储在内存中,提供了非常快速的读写操作,但是数据会在MySQL重启或崩溃时丢失。它适用于需要快速读写的临时数据或缓存数据。
MERGE:允许将一系列相同结构的MyISAM表以逻辑方式合并成一个逻辑表,提供了对这些表进行查询的统一视图。它适用于对多个MyISAM表进行查询操作的情况,可以简化查询语句。
- explain分析执行计划中的
id
select_type
table
partitions
type
possible_keys
key
key_len
rows
filtered
Extra
分别是什么意思?
id: 这是一个表示查询中每个操作步骤的唯一标识符。如果查询包含了多个操作步骤(例如联接或子查询),则每个操作步骤都会有一个独特的ID。ID号一般按照操作执行的顺序递增。
select_type: 这个字段表示了查询的类型,它描述了查询的种类,比如简单查询、联接查询、子查询等等。常见的取值包括:
SIMPLE:简单的SELECT查询,不包含子查询或UNION。
PRIMARY:最外层的查询,如果查询包含了子查询,则最外层查询的select_type为PRIMARY。
SUBQUERY:子查询,作为其他查询的一部分执行。
UNION:UNION操作的结果。
DERIVED:派生表,这个表是从其他表中派生出来的临时表。
UNION RESULT:UNION操作的结果。
table: 这个字段表示了查询涉及到的表的名字。如果查询涉及到多个表,那么会有多行记录,每行记录对应一个表。
partitions: 如果表被分区了,这个字段会显示在哪个分区中执行查询。
type: 这个字段表示了访问表的方式,也称为“访问类型”。它表示MySQL在执行查询时选择了何种策略来访问表中的数据。常见的取值包括:
ALL:全表扫描,检查表中的每一行。
index:仅通过索引进行扫描,而不是整个表。
range:通过索引进行范围扫描。
ref:通过非唯一索引或唯一索引的前缀来访问表中的单个行。
eq_ref:类似于ref,但使用的是唯一索引。
const/system:查询中有一个表最多只有一行匹配,通常是主键或唯一索引的查询。
NULL:无表被访问。
possible_keys: 这个字段显示了MySQL查询优化器可能使用的索引列表。MySQL在执行查询时,会根据查询条件和表的索引情况来选择合适的索引进行优化查询,这个字段列出了可能被用到的索引名称。
key: 这个字段显示了MySQL实际选择使用的索引。如果MySQL选择了一个索引来优化查询,那么这个字段会显示实际使用的索引名称。
key_len: 这个字段显示了MySQL在索引中使用的字节数。它表示MySQL在执行查询时使用的索引长度。
rows: 这个字段显示了MySQL在执行查询时估计会检索的行数。这个值是MySQL优化器根据表的统计信息和查询条件估计的。
filtered: 这个字段显示了从表中检索的行数在返回给用户之前经过过滤的百分比。它是在查询过程中,基于WHERE条件和索引条件进行过滤后的结果。
Extra: 这个字段包含了额外的信息,通常用于提供关于查询执行过程中的一些附加信息。例如,如果使用了临时表、使用了文件排序、使用了索引等信息都可能在这个字段中显示。
- 避免索引失效的方法有哪些?
正确设计索引: 确保对经常用于查询条件的列创建索引,避免创建过多或不必要的索引。
使用覆盖索引: 确保查询所需的列都包含在索引中,这样可以避免访问表数据,提高查询性能。
避免在索引列上进行函数操作: 如果在索引列上使用函数,将导致索引失效,应尽量避免在索引列上进行函数操作,可以通过重构查询语句或创建函数索引来解决。
避免对索引列进行类型转换: 如果查询条件的数据类型与索引列的数据类型不匹配,将导致索引失效,应尽量保持查询条件与索引列的数据类型一致。
避免使用NOT、<>或!=操作符: 在查询条件中使用这些操作符会导致索引失效,应尽量避免使用,可以改用其他方式替代。
避免使用全表扫描: 尽量避免在大表上执行全表扫描的查询,可以通过合适的索引和优化查询语句来提高性能。
定期优化索引: 对于频繁更新的表,需要定期进行索引优化,包括删除不需要的索引、重建索引以及分析表的使用情况来调整索引策略。
第七部分
实验七:项目名称:网络课程平台(1)
http://www.e100soft.com/sjbz/
- 实现的功能:
(一)学生用户
- 课程作业
- 课程实验
- 课程考试
(二)教师用户:
- 课程的章节管理
- 课程的题库管理
- 教师管理
- 班级管理
- 学生管理
- 作业管理
- 实验管理
- 考试管理
- ER图
- 需要建立的表:
课程章节表:
chapter(chapterId,chapterName,questionCount)
课程题库表:
questionBank(questionId,questionContent,questionAnswer,type,difficulty,chapterId)
教师表:
teacher(teacherId,teacherNUM,teacherName,teacherPW)
班级表:
class(classId,className,classSize,teacherId)
学生表:
student(studentId,studentNUM,studentName,studentPW,classId)
作业规则表:
work_rule(work_ruleId,chapterId,type,difficulty,count)
实验规则表:
experiment_rule(experiment_ruleId,chapterId,type,difficulty,count)
试卷规则表:
exam_rule(experiment_ruleId,fromchapterId,tochapterId,type,difficulty,count)
课程作业总表:
work(workId,chapterId,studentId,fromTime,toTime,score)
课程作业明细表:
work_detail(work_detailId,workId,questionId,answer,grade)
课程实验总表:
experiment(experimentId,chapterId,studentId,fromTime,toTime,score)
课程实验明细表:
experiment_detail(experiment_detailId,experimentId,questionId,answer,grade)
课程试卷总表:
exam(examId,chapterId,studentId,fromTime,toTime,state,score)
课程试卷明细表:
exam_detail(exam_detailId,examId,questionId,answer,grade)
- 建立索引:
教师表和学生表的姓名索引
题库表的试题内容、试题答案、题型、难度索引
班级表的班级名称索引
作业明细表的答案索引
课程试卷总表的答案索引
- 建立视图:
班级学生视图(班级表和学生表)
教师学生视图(教师表和班级学生视图)
作业视图(作业总表和作业明细表)
实验视图(实验总表和实验明细表)
试卷视图(试卷总表和试卷明细表)
学生作业视图(学生表、章节表和作业表)
学生实验视图(学生表、章节表和实验表)
学生试卷视图(学生表和实验表)
教师作业视图(教师学生视图、章节表和作业表)
教师实验视图(教师学生视图、章节表和实验表)
教师试卷视图(教师学生视图和试卷表)
- 插入记录
(一)插入章节表
- MySQL概述
- 数据类型
- MySQL常用操作
- MySQL查询
- MySQL函数和存储过程
- MySQL高级特性
- 索引
- 综合案例
(二)插入题库表
【单选题】______表示一个新的事务处理块的开始
A、 START TRANSACTION
B、 BEGIN TRANSACTION
C、 BEGIN COMMIT
D、 START COMMIT
答案: A
所属章节:第一章
难度:容易
【单选题】______函数通常用来计算累计排名、移动平均数和报表聚合等。
A、 汇总
B、 分析
C、 分组
D、 单行
答案: A
所属章节:第一章
难度:容易
【单选题】______是实体属性。
A、 形状
B、 汽车
C、 盘子
D、 高铁
答案: A
所属章节:第一章
难度:容易
【单选题】______是一个单一的逻辑工作单元。
A、 记录
B、 数据库
C、 事务
D、 字段
答案: C
所属章节:第一章
难度:容易
【单选题】______子句用于查询列的唯一值。
A、 unique
B、 distinct
C、 order by
D、 group by
答案: B
所属章节:第一章
难度:容易
【单选题】MySql数据库中,下面______可以作为有效的列名。
A、 Column
B、 123_NUM
C、 NUM_#123
D、 #NUM123
答案: C
所属章节:第一章
难度:容易
【单选题】MySql数据库中,以下______命令可以删除整个表中的数据,并且无法回滚。
A、 drop
B、 delete
C、 truncate
D、 cascade
答案: C
所属章节:第一章
难度:容易
【单选题】MySQL中,预设的、拥有最高权限超级用户的用户名为______
A、 test
B、 Administrator
C、 DA
D、 root
答案: D
所属章节:第一章
难度:容易
【单选题】MySQL组织数据采用______
A、 层次模型
B、 网状模型
C、 关系模型
D、 数据模型
答案: C
所属章节:第一章
难度:容易
【单选题】SELECT语句的完整语法较复杂,但至少包括的部分是______
A、 仅SELECT
B、 SELECT,FROM
C、 SELECT,GROUP
D、 SELECT,INTO
答案: B
所属章节:第一章
难度:容易
【单选题】SQL 查询中去除重复数据的是______
A、 ORDER BY
B、 GROUP BY
C、 DISTINCT
D、 DESC
答案: C
所属章节:第一章
难度:容易
【单选题】SQL是一种______语言。
A、 函数型
B、 高级算法
C、 关系数据库
D、 人工智能
答案: C
所属章节:第一章
难度:容易
【单选题】SQL语句中______命令可以授予用户对象权限。
A、 REVOKE
B、 GRANT
C、 DENY
D、 CREATE
答案: B
所属章节:第一章
难度:容易
【单选题】SQL语句中的条件用以下哪一项来表达______
A、 THEN
B、 WHILE
C、 WHERE
D、 IF
答案: C
所属章节:第一章
难度:容易
【单选题】SQL语言的数据操纵语句包括SELECT、INSERT、UPDATE、DELETE等。其中最重要的,也是使用最频繁的语句是______。
A、 UPDATE
B、 SELECT
C、 DELETE
D、 INSERT
答案: B
所属章节:第一章
难度:容易
【填空题】SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体,其中,CREATE、DROP、ALTER语句是实现哪种功能______
答案:数据定义
B、 数据控制
C、 数据定义
D、 数据查询
答案: C
所属章节:第一章
难度:容易
【填空题】数据操纵语言中典型的四种语句是Insert、select 、update和____。
答案: delete;
所属章节:第一章
难度:容易
【填空题】事务处理控制语言中的Commit命令表示提交事务,而回退事务则用____命令表示。
答案: ROLLBACK;
所属章节:第一章
难度:容易
【填空题】PL/SQL基本语句块中的声明部分使用____关键词。
答案: DECLARE;
所属章节:第一章
难度:容易
【填空题】查询数据表的内容,需要用到的sql命令为:____.(字母小写)
答案: select
所属章节:第一章
难度:容易
【填空题】在MySQL中,可以使用____(要求小写)语句来修改、更新一个表或多个表中的数据。
答案: update
所属章节:第一章
难度:容易
【填空题】SQL语言是______的语言,轻易学习 。
答案:非过程化
所属章节:第一章
难度:容易
【填空题】SQL语言中,删除一个视图的命令是______
答案:DROP
所属章节:第一章
难度:容易
【填空题】UNIQUE惟一索引的作用是______
答案:保证各行在该索引上的值都不得重复
【填空题】ORDER BY NAME DESC是指按照姓名______
答案:降序
所属章节:第一章
难度:容易
(三)插入教师表
帐号:1001
密码:1001
姓名:刘老师
(四)插入班级表
名称:软工2001-2003班
教师:刘老师
(五)插入学生表
学号 姓名 班级
181451080217 李永贤 软工2001-2003
181451081139 邹海洋 软工2001-2003
181451081301 安炳元 软工2001-2003
191451080133 张志敏 软工2001-2003
191451080437 张逸尘 软工2001-2003
191451081323 王朝辉 软工2001-2003
191451081601 边留洋 软工2001-2003
201451080101 白龙瑞 软工2001-2003
201451080102 陈一博 软工2001-2003
201451080103 陈永 软工2001-2003
201451080104 陈志凯 软工2001-2003
201451080105 杜彦霖 软工2001-2003
201451080106 樊宇涛 软工2001-2003
201451080107 甘雨浓 软工2001-2003
201451080108 谷尧 软工2001-2003
201451080109 顾展博 软工2001-2003
201451080111 何承赛 软工2001-2003
201451080112 胡知临 软工2001-2003
201451080113 黄海城 软工2001-2003
201451080114 李林蔚 软工2001-2003
201451080115 李元枫 软工2001-2003
201451080116 梁雨辰 软工2001-2003
201451080117 刘文帅 软工2001-2003
201451080118 刘鑫军 软工2001-2003
201451080119 马思恒 软工2001-2003
201451080120 孟棒棒 软工2001-2003
201451080121 莫童童 软工2001-2003
201451080122 穆鸣昊 软工2001-2003
201451080123 秦梦碟 软工2001-2003
201451080124 王梦蕊 软工2001-2003
201451080125 肖志杰 软工2001-2003
201451080126 徐嘉乐 软工2001-2003
201451080127 徐思雨 软工2001-2003
201451080128 姚超凡 软工2001-2003
201451080129 叶京城 软工2001-2003
201451080130 张江涛 软工2001-2003
201451080131 张炯炯 软工2001-2003
201451080132 张昊晨 软工2001-2003
201451080133 赵京鹏 软工2001-2003
201451080134 赵依格 软工2001-2003
201451080135 周宁 软工2001-2003
201451080136 宗自钦 软工2001-2003
201451080137 郅梦源 软工2001-2003
201451080201 白小龙 软工2001-2003
201451080202 常恒 软工2001-2003
201451080203 陈智鹏 软工2001-2003
201451080204 单其峰 软工2001-2003
201451080205 段金萍 软工2001-2003
201451080206 韩梦蕊 软工2001-2003
201451080207 胡士魁 软工2001-2003
201451080208 黄逸博 软工2001-2003
201451080209 敬丰源 软工2001-2003
201451080210 李超飞 软工2001-2003
201451080211 李厚兵 软工2001-2003
201451080212 李娇娇 软工2001-2003
201451080213 李强 软工2001-2003
201451080214 刘博 软工2001-2003
201451080215 刘东洋 软工2001-2003
201451080216 马嘉辰 软工2001-2003
201451080217 马睿明 软工2001-2003
201451080218 申玉祺 软工2001-2003
201451080219 史明耀 软工2001-2003
201451080220 双仕达 软工2001-2003
201451080221 宋峥卓 软工2001-2003
201451080222 孙龙飞 软工2001-2003
201451080223 王欢 软工2001-2003
201451080224 王会雅 软工2001-2003
201451080225 王凯 软工2001-2003
201451080226 王艺冰 软工2001-2003
201451080227 王淼 软工2001-2003
201451080228 辛玉琳 软工2001-2003
201451080229 薛帅帅 软工2001-2003
201451080230 杨博轩 软工2001-2003
201451080231 殷同坤 软工2001-2003
201451080232 于欣可 软工2001-2003
201451080233 喻道豹 软工2001-2003
201451080234 张博睿 软工2001-2003
201451080235 张豪杰 软工2001-2003
201451080236 祝启骏 软工2001-2003
201451080237 邰澜晰 软工2001-2003
201451080301 崔奇胜 软工2001-2003
201451080302 崔腾飞 软工2001-2003
201451080303 段龙辉 软工2001-2003
201451080304 段中飞 软工2001-2003
201451080305 耿浩文 软工2001-2003
201451080306 侯世轩 软工2001-2003
201451080307 贾昊林 软工2001-2003
201451080308 姜明辉 软工2001-2003
201451080309 姜琰 软工2001-2003
201451080310 景一帆 软工2001-2003
201451080311 李诚 软工2001-2003
201451080312 李嘉豪 软工2001-2003
201451080313 李金孖 软工2001-2003
201451080314 李明洋 软工2001-2003
201451080315 李鹏涛 软工2001-2003
201451080316 李小飞 软工2001-2003
201451080317 李星宇 软工2001-2003
201451080318 李轶飞 软工2001-2003
201451080319 刘校晨 软工2001-2003
201451080320 刘潇 软工2001-2003
201451080321 孟文 软工2001-2003
201451080322 乔艺雯 软工2001-2003
201451080323 申辰晨 软工2001-2003
201451080324 宋富康 软工2001-2003
201451080325 苏永琪 软工2001-2003
201451080326 孙攀科 软工2001-2003
201451080327 王昌豪 软工2001-2003
201451080328 王旭 软工2001-2003
201451080329 王子涵 软工2001-2003
201451080330 王皓 软工2001-2003
201451080331 魏卓翰 软工2001-2003
201451080332 夏天行 软工2001-2003
201451080333 徐堃元 软工2001-2003
201451080334 张俊豪 软工2001-2003
201451080335 张威 软工2001-2003
201451080336 赵文静 软工2001-2003
201451080337 朱国庆 软工2001-2003
(六)插入试卷规则表:
第一章 至 第一章 单选题 容易 6
第一章 至 第一章 填空题 容易 4
(七)插入作业规则表:
- 单选题 容易 10
- 编写触发器
(一)章节试题数量触发器
当插入、删除试题时,修改章节表的试题数量
(二)班级学生人数触发器
当插入、删除学生时,修改班级表的学生人数
- 编写存储过程或函数
- 写出插入一条学生数据的存储过程
- 写出一次插入多条学生数据的存储过程,学生数据的格式为:
“学号1,姓名1,密码1,班级号1;学号2,姓名2,密码2,班级号2;”
例如:
"201451080102,张三,NULL,1;201451080103,李四,NULL,1;"
CAST( 字符串 AS SIGNED); 将字符串类型的数字转化为数字类型的数字
(三)生成学生试卷
输入参数:学生帐号,按照规则生成学生试卷总表以及试卷明细表
(四)生成班级试卷
输入参数:班级ID,按照规则生成班级所有学生的试卷总表以及试卷明细表
第七部分续写
根据七内容,按顺序把代码和截屏写在下面
建表代码:省略
插入数据:
insert into `chapter`(`chapterId`,`chapterName`,`questionCount`) values (1,'第一章MySQL概述',0000000000),(2,'第二章数据类型',0000000000),(3,'第三章MySQL常用操作',0000000000),(4,'第四章MySQL查询',0000000000),(5,'第五章MySQL函数和存储过程',0000000000),(6,'第六章MySQL高级特性',0000000000),(7,'第七章索引',0000000000),(8,'第八章综合案例',0000000000);
insert into `class`(`classid`,`classname`,`classsize`,`teacherid`) values (1,'软工2001-2003班',121,1);
insert into `exam_rule`(`exam_ruleid`,`fromchapterid`,`tochapterid`,`type`,`difficulty`,`count`) values (1,1,1,1,1,6),(2,1,1,2,1,4);
insert into `questionbank`(`questionId`,`questionContent`,`questionAnswer`,`type`,`difficulty`,`chapterId`) values (1,'______表示一个新的事务处理块的开始 \nA、 START TRANSACTION \nB、 BEGIN TRANSACTION \nC、 BEGIN COMMIT \nD、 START COMMIT ','A',001,001,1),(2,'______表示一个新的事务处理块的开始 \nA、 START TRANSACTION \nB、 BEGIN TRANSACTION \nC、 BEGIN COMMIT \nD、 START COMMIT ','A',001,001,1),(3,'______表示一个新的事务处理块的开始 \r\nA、 START TRANSACTION \r\nB、 BEGIN TRANSACTION \r\nC、 BEGIN COMMIT \r\nD、 START COMMIT ','A',001,001,1),(4,'______表示一个新的事务处理块的开始 \nA、 START TRANSACTION \nB、 BEGIN TRANSACTION \nC、 BEGIN COMMIT \nD、 START COMMIT ','A',001,001,1),(5,'______表示一个新的事务处理块的开始 A、 START TRANSACTION B、 BEGIN TRANSACTION C、 BEGIN COMMIT D、 START COMMIT ','A',001,001,1),(6,'______表示一个新的事务处理块的开始 \r\nA、 START TRANSACTION \r\nB、 BEGIN TRANSACTION \r\nC、 BEGIN COMMIT \r\nD、 START COMMIT \r\n','A',001,001,1),(7,'______函数通常用来计算累计排名、移动平均数和报表聚合等。 \r\nA、 汇总 \r\nB、 分析 \r\nC、 分组 \r\nD、 单行','A',001,001,1),(8,'查询数据表的内容,需要用到的sql命令为:____。(字母小写)','select',002,001,1),(9,'在MySQL中,可以使用____(要求小写)语句来修改、更新一个表或多个表中的数据。','update',002,001,1),(10,'SQL语言是______的语言,轻易学习。','非过程',002,001,1),(11,'SQL语言中,删除一个视图的命令是_____。','drop',002,001,1),(12,'ORDER BY NAME DESC是指按照姓名______。','降序',002,001,1),(13,'UNIQUE惟一索引的作用是______。','不重复',002,001,1);
insert into `teacher`(`teacherId`,`teacherNUM`,`teacherName`,`teacherPW`) values (1,'1001','刘老师','1001'),(2,'1002','王老师','1002'),(3,'1003','李老师','25654');
CREATE INDEX teacher_index ON `teacher`(`teacherName`);
CREATE INDEX student_index ON `student`(`studentname`);
题库表的试题内容、试题答案、题型、难度索引
CREATE INDEX question_indexA ON `questionbank`(`questionContent`(100));
CREATE INDEX question_indexB ON `questionbank`(`questionAnswer`);
CREATE INDEX question_indexC ON `questionbank`(`type`);
CREATE INDEX question_indexD ON `questionbank`(`difficulty`);
班级表的班级名称索引
CREATE INDEX class_index ON `class`(`classname`)
作业明细表的答案索引
CREATE INDEX work_index ON `work_detail`(`answer`)
课程试卷总表的答案索引
CREATE INDEX exam_index ON `exam_detail`(`answer`)
- 建立视图:
班级学生视图(班级表和学生表)
教师学生视图(教师表和班级学生视图)
作业视图(作业总表和作业明细表)
学生作业视图(学生表、章节表和作业表)
学生实验视图(学生表、章节表和实验表)
学生试卷视图(学生表和实验表)
教师作业视图(教师学生视图、章节表和作业表)
教师实验视图(教师学生视图、章节表和实验表)
视图目录如下:
(一)章节试题数量触发器
当插入、删除试题时,修改章节表的试题数量
插入试题的触发器:
删除试题的触发器:
(二)班级学生人数触发器
当插入、删除学生时,修改班级表的学生人数
DELIMITER $$
CREATE TRIGGER `update_class_size`
AFTER INSERT ON `student`
FOR EACH ROW
BEGIN
UPDATE `class`
SET `classsize` = `classsize` + 1
WHERE `classid` = NEW.classid;
END$$
DELIMITER ;
存储过程
使用存储过程向student表中存入一组数据,
生成一个学生试卷:
DELIMITER $$
CREATE
PROCEDURE `network_course`.`insert_exam0`(
IN classid0 INT
)
BEGIN
DECLARE examid0 INT;
DECLARE stuid0 INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
SELECT studentid FROM student
WHERE classid=classid0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE;
OPEN cur;
FETCH cur INTO stuid0;
WHILE(NOT done)DO
CALL insert_exam (stuid0);
FETCH cur INTO stuid0;
END WHILE;
CLOSE cur;
END$$
DELIMITER ;
生成一个班的试卷:
DELIMITER $$
CREATE
PROCEDURE `network_course`.`insert_exam`(
IN studentid0 INT
)
BEGIN
DECLARE examid0 INT;
DECLARE fromc0 INT;
DECLARE toc0 INT;
DECLARE type0 INT;
DECLARE diff0 INT;
DECLARE c0 INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT `fromchapterid`,`tochapterid`,`type', difficulty`, `count`
FROM exam_rule ORDER BY exam_ruleid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE;
SELECT COUNT(*)INTO c0 FROM exam WHERE studentid=studentid0;
IF c0=0 THEN
INSERT INTO exam(studentid,state,score)VALUES(studentid0,0,0);
SELECT LAST_INSERT_ID()INTO examid0;
OPEN cur;
FETCH cur INTO fromc0,toc0,type0,diff0,c0;
WHILE(NOT done) DO
INSERT INTO exam_detail(examid,questionid,answer ,grade)
SELECT examid0 AS examid,questionid,'' AS answer,0 AS grade
FROM questionbank WHERE chapterid>=fromc0 AND chapterid<=toc0
AND `type`=type0 AND difficulty=diff0 ORDER BY RAND() LIMIT c0;
FETCH cur INTO fromc0,toc0,type0,diff0,c0;
END WHILE;
CLOSE cur;
END IF;
END$$
DELIMITER ;