22 存储过程的定义
-- 22 存储过程的定义
-- 1 传入和传出参数 要求: p1 atk>x有n条 标记: 参数为in natk int和out n int
DROP PROCEDURE IF EXISTS p1;
DELIMITER //
CREATE PROCEDURE p1(IN natk INT,OUT n INT)
BEGIN
SELECT COUNT(*) INTO n FROM deck WHERE atk>natk;
END //
DELIMITER ;
-- 调用p1 要求: 然后改值为2700 标记: natk是第一个参数 @n是传出的参数
CALL p1(2300,@n);
-- 查询变量@n的值 要求: 结果为6 标记: 个人习惯, n代表查询的结果值
SELECT @n
-- 2 只有传出参数 要求: 名为p1 3个参数分别查询最大小,平均值
DROP PROCEDURE IF EXISTS p2;
DELIMITER //
CREATE PROCEDURE p2(
OUT pa1 INT(50),
OUT pa2 INT(50),
OUT pa3 INT(50),
OUT pa4 INT(50),
OUT pa5 INT(50)
)
BEGIN
-- 要求: 条数 给pa1 标记: into
SELECT COUNT(atk) INTO pa1 FROM deck;
SELECT SUM(atk) INTO pa2 FROM deck;
SELECT AVG(atk) INTO pa3 FROM deck;
SELECT MAX(atk) INTO pa4 FROM deck;
SELECT MIN(atk) INTO pa5 FROM deck;
END //
DELIMITER;
-- 调用 指明5个参数 名称为聚合函数
CALL p2(@count,@sum,@avg,@max,@min);
-- 查变量值 要求: 结果为10条 22900 2290 3000 1300
SELECT @count;
SELECT @sum;
SELECT @avg;
SELECT @max;
SELECT @min;
-- 3 只有传入参数 要求: p5 查询传入的atk数值,这一条记录行
DROP PROCEDURE IF EXISTS p3;
DELIMITER //
CREATE PROCEDURE p3(IN number INT)
BEGIN
SELECT * FROM deck WHERE atk=number;
END //
DELIMITER;
-- 如果只有传入参数, 那么call调用时, 就会显示结果, 并不需要select变量
CALL p3(2600)
-- 5 存储函数的 inout
-- 如果p1存在就删除
DROP PROCEDURE IF EXISTS p4;
-- 结束符分号; 改变为双斜杠//
DELIMITER //
-- 创建存储过程p1 参数为inout模式 同时作为输入和输出参数
CREATE PROCEDURE p4(INOUT var INT)
-- 开始多条语句 可正常使用分号结束符,因为真正的结束符已经改变成//
BEGIN
-- 定义新的变量temp 作为被赋值使用 个人习惯使用temp命名这种临时类型的变量
DECLARE temp INT(11);
-- if then 大于3时 设temp设置成10
IF var > 3 THEN
SET temp = 10;
-- 否则 将值设置成3
ELSE
SET temp = 3;
-- 结束if-then-else控制语句
END IF ;
-- 将临时变量temp的值赋值给var
SET var = temp;
END //
DELIMITER ;
-- 在调用存储过程p1前 先给用户变量@var赋值
SET @var = 2;
-- 调用存储过程 如果赋的值大于3就会变成10
CALL p4(@var);
-- 查询用户变量@var 在调用p1前 赋的值为2 调用后,因为不大于3,所以变成了3
SELECT @var;
-- 查建存储过程的语句 标记: 网格视图有编码 文字视图有begin和end之内的语句
SHOW CREATE PROCEDURE p4
-- 删除存储过程
DROP PROCEDURE IF EXISTS p4
-- 5 用户变量
-- 用户变量的运算 结果为和50
SET @i4=30+20;
SELECT @i4;
-- 给用户变量赋值一个字符串 语法: 查询 字面量值 into 用户变量
SELECT '今天继续学习' INTO @i5;
SELECT @i5;
-- 给用户变量赋值一个字符串2 语法: 设置 用户变量 赋值运算符 字面量值
SET @i6 = '静心学习';
SELECT @i6
-- 在普通sql语句中使用用户变量 整型类型
SET @i7=1;
SELECT * FROM deck WHERE id=@i7;
-- 在普通sql语句中使用用户变量 字符串类型
SET @i8='自然木鳞龙';
SELECT * FROM deck WHERE c_name=@i8;
22 定义
23 存储过程的控制语句
-- 23 存储过程的控制语句
-- ① if-elseif-else
-- 6 用来判断deck和deck3哪张表的记录数多, 并返回多的那个表名
DROP PROCEDURE IF EXISTS p6;
DELIMITER //
CREATE PROCEDURE p6 (OUT t CHAR(10))
BEGIN
IF (SELECT COUNT(*) FROM deck)>(SELECT COUNT(*) FROM deck3)
THEN
SET t='deck';
ELSEIF (SELECT COUNT(*) FROM deck)=(SELECT COUNT(*) FROM deck3)
THEN
SET t='equal';
ELSE
SET t='deck3';
END IF;
END //
DELIMITER ;
CALL p6(@records);
-- 查询deck和deck3哪张表的记录行多, 返回表名
SELECT @records;
-- 7 控制语句中只有if和else 没有else if deck表atk列 desc代表降序,asc升序
DROP PROCEDURE IF EXISTS p7;
DELIMITER //
CREATE PROCEDURE p7(IN in_n INT)
BEGIN
IF in_n=1
THEN
SELECT * FROM deck ORDER BY atk ASC;
ELSE
SELECT * FROM deck ORDER BY atk DESC;
END IF;
END //
DELIMITER ;
-- 1以外的数字都代表降序, 个人习惯0代表降序 1代表升序
CALL p7(0);
-- ② case语句
-- 8 使用case语句分别根据输入的数值, 查询deck这4张表中的所有数据
DROP PROCEDURE IF EXISTS p8;
DELIMITER //
CREATE PROCEDURE p8(IN param INT)
BEGIN
CASE param
WHEN 2 THEN
SELECT * FROM deck2;
WHEN 3 THEN
SELECT * FROM deck3;
WHEN 4 THEN
SELECT * FROM deck4;
ELSE
SELECT * FROM deck;
END CASE;
END //
DELIMITER ;
-- 个人习惯 用0来调用控制语句的默认情况 标记: 查询4表中的全部数据
CALL p8(0)
CREATE TABLE IF NOT EXISTS deck8(
id INT(50)
)ENGINE=INNODB;
-- ③ while do
-- 9 当小于传入参数时,就往表中插入一条记录,然后参数自增1
DROP PROCEDURE IF EXISTS p9;
DELIMITER //
CREATE PROCEDURE p9(IN param INT)
BEGIN
WHILE param < 10 DO
INSERT INTO deck8 (id) VALUES(1);
SET param = param+1;
END WHILE;
END //
DELIMITER ;
-- 向deck8中插入数据 要求: 第一次插入10条, 第二次插入5条 标记: 调用两次即可
CALL p9(5)
-- ④ repeat...end repeat语句
-- 10 是至少执行 这个存储过程中设置的是大于5时停止 标记: 传入3时,插入3次
DROP PROCEDURE IF EXISTS p10;
DELIMITER //
CREATE PROCEDURE p10(IN param INT)
BEGIN
REPEAT
INSERT INTO deck8(id) VALUES(1);
SET param = param+1;
UNTIL param>5
END REPEAT;
END //
DELIMITER ;
-- repeat...end until中设置的是>5停止插入 想要有18条 需要调用p10传入几?
CALL p10(3)
-- ⑤ loop...end loop语句
-- 11 leave是离开循环loop
DROP PROCEDURE IF EXISTS p11;
DELIMITER //
CREATE PROCEDURE p11(IN param INT)
BEGIN
loop_lable:LOOP
INSERT INTO deck8(id) VALUES(1);
SET param=param+1;
IF param>10 THEN
LEAVE loop_lable;
END IF;
END LOOP;
END //
DELIMITER;
-- loop 条件>10 传入参数10 也会执行一次 因为先插入,后判断
CALL p11(10)
-- ⑥ iterate语句
-- 12 p12是向deck8中插入id值为1到10
DROP PROCEDURE IF EXISTS p12;
DELIMITER //
CREATE PROCEDURE p12()
BEGIN
DECLARE v INT;
SET v=0;
loop_lable:LOOP
IF v=0 THEN
SET v=v+1;
ITERATE loop_lable;
END IF;
INSERT INTO deck8 (id) VALUES(v);
SET v=v+1;
IF v>10 THEN
LEAVE LOOP_LABLE;
END IF;
END LOOP;
END //
DELIMITER ;
-- 原本有19个1 第一次调用后20到29 是1到10 要求: 调用2次p12 最终为39条
CALL p12;
-- deck8新增一列 列名atk 类型int(50) 位置在id列后
ALTER TABLE deck8 ADD COLUMN atk INT(50) AFTER id;
-- ⑥.2iterate语句
-- 13 使用iterate 将deck8表中的atk列的值 设置成从1到5
DROP PROCEDURE IF EXISTS p13;
DELIMITER //
CREATE PROCEDURE p13()
BEGIN
DECLARE v INT;
SET v=0;
loop_lable:LOOP
IF v=0 THEN
SET v=v+1;
ITERATE loop_lable;
END IF;
UPDATE deck8 SET atk=v WHERE id=v;
SET v=v+1;
IF v>5 THEN -- 让loop_lable循环执行5次
LEAVE loop_lable;
END IF;
END LOOP;
END //
DELIMITER ;
-- 需要deck8中id是从1开始到5结果 为了作为条件
CALL p13;
23 控制语句
24 存储过程的事务
-- 24 事务 在存储过程中使用事务
-- 14 建表deck9 主键id 名称名c_name engine=innodb
CREATE TABLE IF NOT EXISTS deck9 (
id INT(50),
c_name VARCHAR(50),
PRIMARY KEY (id)
)ENGINE=INNODB;
-- 存储过程中的语句报错时, 要求能回滚
DROP PROCEDURE IF EXISTS p14;
DELIMITER //
CREATE PROCEDURE p14(IN in_name VARCHAR(50),OUT back VARCHAR(50))
BEGIN
DECLARE error INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = 1;
START TRANSACTION;
INSERT INTO deck9 (id,c_name) VALUES (in_name);
INSERT INTO deck9 (id,c_name) VALUES (2,in_name);
IF (error = 1) THEN
ROLLBACK;
SET back = '有异常,事务回滚了';
ELSE
COMMIT;
SET back = '无异常,事务提交了';
END IF;
END //
DELIMITER ;
-- 调用p14
CALL p14('自然木龙兽',@back14)
-- 有异常事务回滚了 回滚原因是第一次插入数据时, 插入的数目不正确
SELECT @back14
-- 存储过程不是事务的, 想要事务,就需要开启
-- 15 查看存储过程本身是否是开启事务
DROP PROCEDURE IF EXISTS p15;
DELIMITER //
CREATE PROCEDURE p15(IN in_name VARCHAR(50))
BEGIN
INSERT INTO deck9 (id,c_name) VALUES (1,in_name);
INSERT INTO deck9 (id,c_name) VALUES (1,in_name);
END //
DELIMITER ;
-- 第一条语句提交,第二条报错 虽然报错但第一条语句能正常插入,这就是非事务
CALL p15('自然木龙兽');
-- 16 让存储过程变成事务
DROP PROCEDURE IF EXISTS p16;
DELIMITER //
CREATE PROCEDURE p16(IN in_name VARCHAR(50))
BEGIN
-- 给变量error设置默认值0
DECLARE error INTEGER DEFAULT 0;
-- 当有错误时, 设置成值为1
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=1;
START TRANSACTION ;
INSERT INTO deck9 (id,c_name) VALUES (1,in_name);
INSERT INTO deck9 (id,c_name) VALUES (1,in_name);
IF error = 1
THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END //
DELIMITER ;
-- 第一条提交,第二条报错 因为这个存储过程中有开启事务 所以都不会提交,回滚了
CALL p16('自然木龙兽');
24 事务
25 存储过程的使用
-- 存储过程的使用 有控制语句, 有事务
-- 14 根据inner,left,right 输出内连接 左连接,右连接 标记: 查询无需用事务
DROP PROCEDURE IF EXISTS p14;
DELIMITER //
CREATE PROCEDURE p14(IN in_n CHAR(50))
BEGIN
DECLARE error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=1;
START TRANSACTION ;
IF in_n='left'
THEN
SELECT * FROM deck a LEFT JOIN deck5 b ON a.attribute=b.attribute;
ELSEIF in_n='right'
THEN
SELECT * FROM deck a RIGHT JOIN deck5 b ON a.attribute=b.attribute;
ELSE
SELECT * FROM deck a INNER JOIN deck5 b ON a.attribute=b.attribute;
END IF;
IF error = 1
THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END //
DELIMITER ;
-- 默认是内连接,只要是''都是内连接 值为left时是左连接 right是右
CALL p14('')
CALL p14('inner')
CALL p14('left')
CALL p14('right')
-- 15 根据传入的多个值判断 查询哪张表的最大值 标记: 查询无需用事务
DROP PROCEDURE IF EXISTS p15;
DELIMITER //
CREATE PROCEDURE p15(IN in_num INT(11) ,OUT out_atk VARCHAR(32))
BEGIN
DECLARE error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=1;
START TRANSACTION ;
CASE in_num
WHEN 0 THEN
SELECT MAX(atk) INTO out_atk FROM deck;
WHEN 2 THEN
SELECT MAX(atk) INTO out_atk FROM deck2;
WHEN 3 THEN
SELECT MAX(atk) INTO out_atk FROM deck3;
WHEN 4 THEN
SELECT MAX(atk) INTO out_atk FROM deck4;
ELSE
SET out_atk = '请输入指定数值0,2,3,4';
END CASE;
IF error = 1
THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END //
DELIMITER ;
-- 当传入的值不是0,2,3,4时 默认输出0
CALL p15(0,@back5);
-- 查询调用p15的结果
SELECT @back5;
-- 16 批量创建数据表 deck,deck2,deck3,deck4,deck5的表结构 不能插入数据
DROP PROCEDURE IF EXISTS p16;
DELIMITER $$
CREATE PROCEDURE p16()
BEGIN
DECLARE error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = 1;
START TRANSACTION ;
CREATE TABLE IF NOT EXISTS deck(
id INT(50) AUTO_INCREMENT COMMENT '自增' ,
c_name VARCHAR(50) ,
attribute CHAR(50) ,
race CHAR(50) ,
atk INT(50) ,
def INT(50) ,
PRIMARY KEY (id)
)ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
CREATE TABLE deck2 LIKE deck;
CREATE TABLE deck3 LIKE deck;
CREATE TABLE deck4 LIKE deck;
CREATE TABLE IF NOT EXISTS deck5 (
id INT(50),
attribute CHAR(50),
deckRace CHAR(50),
effect CHAR(50)
)ENGINE=INNODB;
IF error=1
THEN ROLLBACK;
ELSE COMMIT;
END IF;
END $$
DELIMITER ;
CALL p16;
-- 17 查deck中全部数据 名为d 优点: 效率提高61%, 提高正确率
DROP PROCEDURE IF EXISTS d;
DELIMITER //
CREATE PROCEDURE d()
BEGIN
SELECT * FROM deck;
END //
DELIMITER ;
-- 调用存储过程d 标记: 调用存储过程d 结果无法刷新,无法限制行
CALL d;
-- 18 查deck索引列表 名为i 优点: 效率提高66%, 提高正确率
DROP PROCEDURE IF EXISTS i;
DELIMITER //
CREATE PROCEDURE i()
BEGIN
SHOW INDEX FROM deck;
END //
DELIMITER ;
-- 调用存储过程i
CALL i
-- 19最简单的存储过程
-- 不用: 改变; 无参数 无复合语句 无定义变量 无事务 只一条sql 调用时不用变量
-- 就像创建视图一样 只是view换成了procedure 名称后有() 调用时用call
CREATE PROCEDURE p4()
SELECT * FROM deck;
CALL p4;
-- 我之前以为最简单的是 足足多了4行
DELIMITER //
CREATE PROCEDURE p5()
BEGIN
SHOW INDEX FROM deck;
END //
DELIMITER ;
CALL p5;
25 存储过程的使用
26 存储函数
-- 26 存储函数
-- 1 要求: f1 定义变量out_def 标记: 参数n int returns int begin return括号;
DROP FUNCTION IF EXISTS f1;
DELIMITER //
CREATE FUNCTION f1(n2 INT)
RETURNS VARCHAR(50)
BEGIN
DECLARE out_def VARCHAR(50);
SELECT def INTO out_def FROM deck WHERE id = n2;
RETURN out_def;
END //
DELIMITER ;
-- 查询f2 要求: 传入数值5 查到防御力2500 卡通混沌战士的
SELECT f1(5)
-- 查存储函数的定义 网络视图有编码 文字视图有begin和end中的语句
SHOW CREATE FUNCTION f1
-- 删存储函数
DROP FUNCTION f1
-- 2 要求: 名为f2 不定义变量out_def 标记: 是78的第二种方法
DROP FUNCTION IF EXISTS f2;
DELIMITER //
CREATE FUNCTION f2(n3 INT)
RETURNS INT
BEGIN
RETURN(SELECT def FROM deck WHERE id = n3);
END //
DELIMITER ;
-- 查询存储函数f3 需要传入参数5
SELECT f2(5)
-- 3 名f3 查atk>x有n条
DROP FUNCTION IF EXISTS f3;
DELIMITER //
CREATE FUNCTION f3(n INT)
RETURNS INT
BEGIN
RETURN(SELECT COUNT(*) FROM deck WHERE atk>n);
END //
DELIMITER ;
-- 传入参数2300 结果为6 第二次传入2700为2条 标记: 像查函数
SELECT f3(2300)
-- 4 查询4表最大值
DROP FUNCTION IF EXISTS f4;
DELIMITER //
CREATE FUNCTION f4()
RETURNS VARCHAR(50)
BEGIN
DECLARE out_atk VARCHAR(50);
-- 四表查询最大值 赋值给out_atk
SELECT f.atk INTO out_atk FROM (
SELECT * FROM deck
UNION ALL
SELECT * FROM deck2
UNION ALL
SELECT * FROM deck3
UNION ALL
SELECT * FROM deck4
) AS f
ORDER BY f.atk DESC LIMIT 0,1;
RETURN out_atk;
END //
DELIMITER ;
SELECT f4()
-- 5 根据传入的参数值0,2,3,4 查询对应的表中的最大值
DROP FUNCTION IF EXISTS f5;
DELIMITER //
CREATE FUNCTION f5(param INT(11))
RETURNS INT(11)
BEGIN
DECLARE out_atk INT(50);
CASE param
WHEN 2 THEN
SELECT MAX(atk) INTO out_atk FROM deck2;
WHEN 3 THEN
SELECT MAX(atk) INTO out_atk FROM deck3;
WHEN 4 THEN
SELECT MAX(atk) INTO out_atk FROM deck4;
ELSE
SELECT MAX(atk) INTO out_atk FROM deck;
END CASE;
RETURN out_atk;
END //
DELIMITER ;
SELECT f5(2);
-- 3000大于2800 返回1
SELECT f5(0) > f5(2);
26 存储函数
27 触发器
-- 27 触发器
-- 根据练习时的情况 对dec表设置触发器 增删改时, 会触发 3个不同类型
-- 1 触发器 当向deck表插入数据之后, 要在myinsert表中留下记录
CREATE TABLE myinsert(
id INT(50) AUTO_INCREMENT COMMENT '记录插入的次数',
record VARCHAR(50),
PRIMARY KEY (id)
);
-- 创建触发器1 向deck表插入数据后, 会触发 标记: trigger
CREATE TRIGGER t1 AFTER INSERT ON deck
FOR EACH ROW INSERT INTO myinsert (record) VALUES ('after insert');
-- 2 当向deck表修改数据之后, 要在myupdate表中留下记录
CREATE TABLE myupdate(
id INT(50) AUTO_INCREMENT COMMENT '记录修改的次数',
record VARCHAR(50),
PRIMARY KEY (id)
);
-- 创建触发器2 当向deck表修改数据之后, 会触发 标记: 在update后要有on
CREATE TRIGGER t2 AFTER UPDATE ON deck
FOR EACH ROW INSERT INTO myupdate (record) VALUES ('after update');
-- 3 当向deck表删除数据之前, 要在mydelete表中留下记录
CREATE TABLE mydelete(
id INT(50) AUTO_INCREMENT COMMENT '记录删除的次数',
record VARCHAR(50),
PRIMARY KEY (id)
);
-- 创建触发器3 向deck表删除数据之前, 会触发
CREATE TRIGGER t3 BEFORE DELETE ON deck
FOR EACH ROW INSERT INTO mydelete (record) VALUES ('before delete');
-- 删deck中所有数据
DELETE FROM deck;
-- 记录数据 重新插入数据
INSERT INTO deck (id,c_name,attribute,race,atk,def) VALUES
(1, '自然木鳞龙', '地', '龙族', 2500, 1800) ,
(2, '卡通黑魔术师', '暗', '魔法师族', 2500, 2100) ,
(3, '卡通黑魔导少女', '暗', '魔法师族', 2000, 1700) ,
(4, '卡通机械巨人', '地', '机械族', 3000, 3000) ,
(5, '卡通混沌战士', '地', '战士族', 3000, 2500) ,
(6, '卡通电子龙', '光', '机械族', 2100, 1600) ,
(7, '卡通恶魔', '暗', '恶魔族', 2500, 1200) ,
(8, '卡通鹰身女郎', '风', '鸟兽族', 1300, 1400) ,
(9, '卡通破坏剑士', '地', '战士族', 2600, 2300) ,
(10, '卡通人鱼', '水', '水', 1400, 1500) ;
-- 删触发器 标记: 后面d5也是对表插入时触发, 同一表同一事件 就会报错
DROP TRIGGER t3;
-- 4 向deck插入时触发 可通过@sumF查询插入的atk总值
-- 标记 trigger before insert on for each row set
CREATE TRIGGER t4 BEFORE INSERT ON deck
FOR EACH ROW SET @sumT = @sumT + new.atk;
-- 设置结果 给已存在结果一个初始值
SET @sumT = 0 ;
-- 插入数据, 来达到触发条件
INSERT INTO deck (c_name,attribute,race,atk,def) VALUES
('自然木鳞龙','地','龙族',2500,1800);
-- 查询变量sumT 在插入一条数据后调用,可以查询到插入的atk值, 是合计值
SELECT @sumT;
-- 删触发器 标记: 后面d5也是对表插入时触发, 同一表同一事件 就会报错
DROP TRIGGER t4;
-- 5 触发器 向deck表插入,修改,删除时, 都在另外三个表中记录 标记: 多条语句
-- 在deck11中, 插入与deck同样的结构, 不要数据 标记: like
CREATE TABLE deck11 LIKE deck;
-- 在deck12中, 要拥有与deck同样的数据, 准备被触发器删除
CREATE TABLE deck12 SELECT * FROM deck;
-- 在deck13中, 要拥有与deck同样的数据, 准备被触发器设置成3倍+10
CREATE TABLE deck13 SELECT * FROM deck;
-- 创建触发器 要求: 有多条执行语句 需考虑改变结束符
DROP TRIGGER IF EXISTS t5;
DELIMITER //
CREATE TRIGGER t5 BEFORE INSERT ON deck
FOR EACH ROW
BEGIN
INSERT INTO deck11 SET
id=new.id, c_name=new.c_name, attribute=new.attribute,
race=new.race, atk=new.atk, def=new.def;
DELETE FROM deck12 WHERE id = new.id;
UPDATE deck13 SET atk = (new.atk)*3+10 WHERE id = new.id;
END //
DELIMITER ;
DELETE FROM deck WHERE id=1;
-- 在dek11设同样值 deck12中同样数据,删除记录行 deck13这个id值的atk变成3倍+10
INSERT INTO deck (id,c_name,attribute,race,atk,def) VALUES
(1,'自然木鳞龙','地','龙族',2500,1800);
-- 28 触发器的应用 回收站功能
-- 删除时,会新增一个代表书架种类的列 列值代表是哪张表 4代表sql表
DROP TRIGGER IF EXISTS delete31;
DELIMITER //
CREATE TRIGGER delete31 BEFORE DELETE ON rack_sql
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 4;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END //
DELIMITER ;
-- basic表删除数据时,触发 有代表是哪张表的字样2 2代表是basic书架
DROP TRIGGER IF EXISTS delete32;
DELIMITER //
CREATE TRIGGER delete32 BEFORE DELETE ON rack_basic
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 2;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END //
DELIMITER ;
-- java表删除数据时,触发 有代表是哪张表的字样3 3代表是java书架
DROP TRIGGER IF EXISTS delete33;
DELIMITER //
CREATE TRIGGER delete33 BEFORE DELETE ON rack_java
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 3;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END //
DELIMITER ;
-- 当re表被修改时, 触发一个还原功能 根据rackType的值判断还原在哪张表中
DROP TRIGGER IF EXISTS restore47;
DELIMITER //
CREATE TRIGGER restore47 AFTER UPDATE ON recyclebin_table
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = old.rackType;
IF @rackType = 2
THEN
INSERT INTO rack_basic
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
ELSEIF @rackType = 3
THEN
INSERT INTO rack_java
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
ELSE
INSERT INTO rack_sql
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
END IF;
END //
DELIMITER ;
27 触发器
28 触发器的使用
-- 28 触发器的使用 回收站功能
-- 删除时,会新增一个代表书架种类的列 列值代表是哪张表 4代表sql表
DROP TRIGGER IF EXISTS delete31;
DELIMITER //
CREATE TRIGGER delete31 BEFORE DELETE ON rack_sql
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 4;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END //
DELIMITER ;
-- basic表删除数据时,触发 有代表是哪张表的字样2 2代表是basic书架
DROP TRIGGER IF EXISTS delete32;
DELIMITER //
CREATE TRIGGER delete32 BEFORE DELETE ON rack_basic
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 2;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END //
DELIMITER ;
-- java表删除数据时,触发 有代表是哪张表的字样3 3代表是java书架
DROP TRIGGER IF EXISTS delete33;
DELIMITER //
CREATE TRIGGER delete33 BEFORE DELETE ON rack_java
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 3;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END //
DELIMITER ;
-- 当re表被修改时, 触发一个还原功能 根据rackType的值判断还原在哪张表中
DROP TRIGGER IF EXISTS restore47;
DELIMITER //
CREATE TRIGGER restore47 AFTER UPDATE ON recyclebin_table
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = old.rackType;
IF @rackType = 2
THEN
INSERT INTO rack_basic
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
ELSEIF @rackType = 3
THEN
INSERT INTO rack_java
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
ELSE
INSERT INTO rack_sql
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
END IF;
END //
DELIMITER ;
28 触发器的使用
备份
DELIMITER $$
USE `db`$$
DROP TRIGGER /*!50032 IF EXISTS */ `delete31`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `delete31` BEFORE DELETE ON `rack_sql`
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 4;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END;
$$
DELIMITER ;
DELIMITER $$
USE `db`$$
DROP TRIGGER /*!50032 IF EXISTS */ `delete32`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `delete32` BEFORE DELETE ON `rack_basic`
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 2;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END;
$$
DELIMITER ;
DELIMITER $$
USE `db`$$
DROP TRIGGER /*!50032 IF EXISTS */ `delete33`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `delete33` BEFORE DELETE ON `rack_java`
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = 3;
INSERT INTO recycleBin_table
(id,bookTitle,bookType,record,startDate,endDate,rackType) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate,
@rackType);
END;
$$
DELIMITER ;
DELIMITER $$
USE `db`$$
DROP TRIGGER /*!50032 IF EXISTS */ `restore47`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `restore47` AFTER UPDATE ON `recyclebin_table`
FOR EACH ROW
BEGIN
SET @oldId = old.id;
SET @oldbookTitle = old.bookTitle;
SET @oldbookType = old.bookType;
SET @oldrecord = old.record;
SET @oldstartDate = old.startDate;
SET @oldendDate = old.endDate;
SET @rackType = old.rackType;
IF @rackType = 2
THEN
INSERT INTO rack_basic
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
ELSEIF @rackType = 3
THEN
INSERT INTO rack_java
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
ELSE
INSERT INTO rack_sql
(id,bookTitle,bookType,record,startDate,endDate) VALUES
(@oldId,@oldbookTitle,@oldbookType,@oldrecord,@oldstartDate,@oldendDate);
END IF;
END;
$$
DELIMITER ;
备份
30 索引的创建
-- 30 索引的创建 5.5版本 myisam存储引擎
-- 1 建表语句
SHOW CREATE TABLE deck
-- 2 改表引擎 标记: 直接修改即可 在表名后
ALTER TABLE deck ENGINE=MYISAM
-- 3 建索引 要求: 唯一 在id
ALTER TABLE deck ADD UNIQUE INDEX idIdx(id)
-- 4 建索引 要求: 全文 在race
ALTER TABLE deck ADD FULLTEXT INDEX raceIdx(race)
-- 5 删索引1 要求: 删除fulltext
DROP INDEX raceIdx ON deck
-- 删索引2 要求: 删除unique
ALTER TABLE deck DROP INDEX idIdx
-- 7 索引列表 要求: 使用上面的存储过程来查看效果 不能使用正常的方式
SHOW INDEX FROM deck;
-- 8 查MySQL的默认存储引擎 标记: 5.5默认InnoDB 每个版本都不一样
SHOW VARIABLES LIKE '%storage_engine%';
-- 9 自动提交 标记: mysql默认自动开启 InnoDB支持事务,所以是
SHOW VARIABLES LIKE '%autocommit%';
-- 10 存储引擎列表 标记: 默认InnoDB
SHOW ENGINES;
30 索引的创建
31 普通索引
-- 31 普通索引的使用
-- 1 解释未索引且* 要求: type为all 均为普通or未索引 标记: 均为组合索引则index
EXPLAIN SELECT * FROM deck
-- 2 建索引 要求: 普通索引 在atk列
ALTER TABLE deck ADD INDEX atkIdx(atk)
-- 3 解释单列 要求: type为index atk列是索引 检索atk
EXPLAIN SELECT atk FROM deck
-- 4 具体值 要求: type为ref 索引列atk 指定具体值
EXPLAIN SELECT atk FROM deck WHERE atk=2000
-- 5 范围 要求: type为range 索引列atk 指定范围atk>2100
EXPLAIN SELECT atk FROM deck WHERE atk>2100
-- 6 显式运算 要求: type为index 标记: where的atk-500=2000
EXPLAIN SELECT atk FROM deck WHERE atk-500=2000
-- 7 != 要求: type为range where!=2500
EXPLAIN SELECT atk FROM deck WHERE atk!=2500
-- 要求: 设值null id=5的记录行
UPDATE deck SET atk=NULL WHERE id=5
-- 8 where列 要求: type为ref is null值判断 标记: is not null则为range
EXPLAIN SELECT atk FROM deck WHERE atk IS NULL
-- 建索引 要求: 普通 在def or连接 和 检索多列 要用到
ALTER TABLE deck ADD INDEX defIdx(def)
-- 解释 or连接 标记: 普通索引会失效, 组合索引可解决这个问题
-- 9 要求: type为range 普通索引 or连接 左右同列 检索列为atk
EXPLAIN SELECT atk FROM deck WHERE atk>2100 OR atk<2600
-- 10 要求: type为all 普通索引 or连接 左右不同列
EXPLAIN SELECT atk FROM deck WHERE atk>2100 OR def>2100
-- 11 解释检索多列 要求: type为all 多列均是索引列
EXPLAIN SELECT atk,def FROM deck;
-- 12 检查多列时,索引失效 解决方法: 只检索单列 要求: type为index
EXPLAIN SELECT atk FROM deck;
EXPLAIN SELECT def FROM deck;
-- 13 为避免索引多过后,发生冲突, 删所有索引
CALL i;
DROP INDEX atkIdx ON deck;
DROP INDEX defIdx ON deck;
-- 索引与like查询
-- 建索引 要求: 普通索引 c_name列
ALTER TABLE deck ADD INDEX c_nameIdx(c_name)
-- 14 检索like,前后% 要求: type为index
EXPLAIN SELECT c_name FROM deck WHERE c_name LIKE '%卡通%'
-- 15 检索like,前% 要求: type为index
EXPLAIN SELECT c_name FROM deck WHERE c_name LIKE '%卡通';
-- 16 检索like,后% 要求: type为range 标记: range比index快, 建议后%
EXPLAIN SELECT c_name FROM deck WHERE c_name LIKE '卡通%'
-- 删c_name索引
DROP INDEX c_nameIdx ON deck
31 普通索引
32 组合索引
-- 32 组合索引的使用
-- 1 建索引 要求: 组合索引 列为race,atk,def 标记: multi
ALTER TABLE deck ADD INDEX multiIdx(race,atk,def)
-- 2 组合索引, 检索多列 要求: type为index
EXPLAIN SELECT atk,def FROM deck
-- 3 当任一列不是组合索引时, 则all
EXPLAIN SELECT * FROM deck
-- 4 组合索引, or连接,同一列 要求: type为index
EXPLAIN SELECT atk FROM deck WHERE atk>2100 OR atk<2600
-- 5 组合索引, or连接,不同列 要求: type为index 解决: 普通索引检索多列, all
EXPLAIN SELECT atk FROM deck WHERE atk>2100 OR def >2100
32 组合索引
33 主键 34唯一索引
-- 33 主键的使用 自带索引
-- 1 只检索主键列 要求: type为index 标记: 主键自带索引
EXPLAIN SELECT id FROM deck
-- 2 检索主键列值 要求: type为const 标记: const比range快
EXPLAIN SELECT id FROM deck WHERE id=1
-- 34 唯一索引的使用
-- 1 建唯一索引 要求: 保证deck中名称唯一 在c_name上
ALTER TABLE deck ADD UNIQUE INDEX c_nameIdx(c_name)
-- 2 使用唯一索引 要求: type为const where指定一个值 c_name列值为卡通混沌战士
EXPLAIN SELECT * FROM deck WHERE c_name='卡通混沌战士'
主键和唯一索引
35 全文索引
-- 35 全文索引的使用
-- 1 创建全文索引 需要engine=myiasm
CREATE TABLE IF NOT EXISTS deck10 (
s1 TINYINT(50) ,
s2 TEXT ,
FULLTEXT INDEX (s2)
)ENGINE=MYISAM;
-- 插入数据
INSERT INTO deck10 (s1,s2) VALUES
(1,'abcdefg'),(2,'hij'),(3,'opq');
-- 2 全文索引需要存储索引是myisam
SHOW CREATE TABLE deck10;
-- 3 修改表的存储引擎 标记: 需要deck10的engine是myisam
ALTER TABLE deck10 ENGINE=MYISAM;
-- 查看索引列表 正常的方式
SHOW INDEX FROM deck10
-- 4 要求: 检索单列时 type为all 标记: 后又是system 第一次遇到
EXPLAIN SELECT s2 FROM deck10;
-- 5 不用match和against type为all
EXPLAIN SELECT s2 FROM deck10 WHERE s2='abcdefg'
-- 6 要求: type为fulltext 标记: match指定列, against指定值
EXPLAIN SELECT s2 FROM deck10 WHERE MATCH(s2) AGAINST('abcdefg')
-- 7 要求: deck7表 全文索引列s2 查询值为hij
EXPLAIN SELECT s2 FROM deck10 WHERE MATCH(s2) AGAINST('hij')
35 全文索引
36 多表查询的不同方式
-- 使用不同方式 统计4表中atk列和def列的合计值 结果为84450+61900的值146350
-- 这4张表中分别有10条不同记录 表结构相同
SELECT * FROM deck
UNION
SELECT * FROM deck2
UNION
SELECT * FROM deck3
UNION
SELECT * FROM deck4;
-- 1 union查询 84450和61900=146350
SELECT SUM(a.atk)+SUM(a.def) AS atkdefSum FROM (
SELECT * FROM deck
UNION
SELECT * FROM deck2
UNION
SELECT * FROM deck3
UNION
SELECT * FROM deck4
) AS a;
-- 2 union 各自计算两字段相加的全 84450和61900=146350
SELECT SUM(a.sumAtk) AS atkdefSum FROM (
SELECT SUM(atk)+SUM(def) AS sumAtk FROM deck
UNION
SELECT SUM(atk)+SUM(def) AS sumAtk FROM deck2
UNION
SELECT SUM(atk)+SUM(def) AS sumAtk FROM deck3
UNION
SELECT SUM(atk)+SUM(def) AS sumAtk FROM deck4
) AS a
-- 3 列子查询 84450和61900=146350
SELECT
(SELECT SUM(atk)+SUM(def) FROM deck )+
(SELECT SUM(atk)+SUM(def) FROM deck2)+
(SELECT SUM(atk)+SUM(def) FROM deck3)+
(SELECT SUM(atk)+SUM(def) FROM deck4) AS atkdefSum
FROM deck WHERE id=1;
-- 4 视图 84450和61900=146350
CREATE VIEW v34 (atkdefSum) AS
SELECT
(SELECT SUM(atk)+SUM(def) FROM deck )+
(SELECT SUM(atk)+SUM(def) FROM deck2)+
(SELECT SUM(atk)+SUM(def) FROM deck3)+
(SELECT SUM(atk)+SUM(def) FROM deck4) AS atkdefsum
FROM deck WHERE id=1;
SELECT * FROM v34;
-- 5 触发器 需要分别为4表创建4个触发器
CREATE TRIGGER t6 BEFORE INSERT ON deck
FOR EACH ROW SET @sumT6 = @sumT6 + new.atk;
-- 设置结果 给已存在结果一个初始值
SET @sumT6 = 0 ;
-- 插入数据, 来达到t6触发条件
INSERT INTO deck (c_name,attribute,race,atk,def) VALUES
('自然木鳞龙','地','龙族',2500,1800);
-- 查询变量
SELECT @sumT6;
-- 6 暴力破解 维护好原数据 如果有更改,在达到效果后,立即改回
-- 7 内连接+视图 84450和61900=146350
CREATE VIEW v34_2 (v_sum1, v_sum2, v_sum3, v_sum4) AS
SELECT
a.atk+a.def,b.atk+b.def,c.atk+c.def,d.atk+d.def
FROM (
(deck a INNER JOIN deck2 b ON a.id=b.id)
INNER JOIN deck3 c ON a.id=c.id
)
INNER JOIN deck4 d ON a.id=d.id;
SELECT SUM(v_sum1)+SUM(v_sum2)+SUM(v_sum3)+SUM(v_sum4) AS atkdefSum
FROM v34_2;
-- 8 笛卡尔积 84450和61900=146350
SELECT (
SUM(a.atk)+SUM(b.atk)+SUM(c.atk)+SUM(d.atk) +
SUM(a.def)+SUM(b.def)+SUM(c.def)+SUM(d.def)
)
/1000 AS atkdefSum
FROM deck a, deck2 b, deck3 c, deck4 d;
-- 9 存储过程 84450和61900=146350 结果列为aekdefSum
DELIMITER //
CREATE PROCEDURE p34(
OUT p1 INT(50),
OUT p2 INT(50),
OUT p3 INT(50),
OUT p4 INT(50)
)
BEGIN
SELECT SUM(atk)+SUM(def)
INTO p1
FROM deck;
SELECT SUM(atk)+SUM(def)
INTO p2
FROM deck2;
SELECT SUM(atk)+SUM(def)
INTO p3
FROM deck3;
SELECT SUM(atk)+SUM(def)
INTO p4
FROM deck4;
END //
DELIMITER ;
-- 调用p341
CALL p34(@p1,@p2,@p3,@p4);
-- 查询变量值的和
SELECT @p1+@p2+@p3+@p4 AS atkdefSum;
36 不同方式
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
