MySQL进阶

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;

 

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;

 

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('自然木龙兽');

 

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;

 

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);

 

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 ;

 

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 ;

 

备份

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; 

 

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

 

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

 

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')

 

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;