MySQL拓展操作

MySQL除了基本的增删该查功能，还有以下拓展功能：

create table t1(
            id int ....,
            num int,
            xx int,
            unique 唯一索引名称 (列名,列名),
            constraint ....
        )

索引不能重复
功能：
1.加速检索
2.约束数据不重复

unique 如果只写一列，关联另一张表的id，实际上就是建立一张一对一关系表。

如果写了两列，分别关联两张表的id字段，实际上就是给两张表建立了多对多关系。

视图：为某个查询语句设置别名，方便以后使用

创建：CREATE VIEW 视图名称 AS  SQL语句
eg：CREATE VIEW tb1 AS SELECT * FROM tb WHERE ID > 10;
这是一个虚拟表，会从数据库动态的添加

修改：ALTER VIEW 视图名称 as SQL语句

删除：DROP VIEW 视图名称 

触发器：当对某个表进行增删改时，希望触发某个操作。相当于一个中间件，可以在增删改时执行触发器设定好的操作

增：
CREATE TRIGGER T1 BEFORE INSERT(或为AFTER) ON 表名 FOR EACH ROW(每增加一条数据都会执行)
DELIMITER // (mysql默认分号结尾改为//)
BEGIN
    SQL语句，eg：INSERT INTO teacher (name,gender)  VALUES('张','男')；(触发器的每次都会在teacher表插入新数据)
END //
DELIMITER ;(设定为分号结尾，防止对其他操作影响)

删：
CREATE TRIGGER T2 BEFORE(或为AFTER) DELETE ON 表名 FOR EACH ROW
BEGIN
...
END

改：
CREATE TRIGGER T3 BEFORE(或为AFTER) UPDATE ON 表名 FOR EACH ROW
BEGIN
...
END


NEW&OLD数据：
在BEGIN 和 END之间的SQL语句，可以用NEW,OLD变量，NEW表示新加入的数据，OLD可以表示删除或修改后的数据：
eg：INSERT INTO teacher(name） VALUES（NEW.name）可以将插入数据的字段加到teacher表新数据的name字段中

删除触发器：
DROP TRIGGER 触发器名称

调用触发器：
不能有用户调用，是基于某张表的操作时自动调用

函数：MySQL有很多内置函数，可以参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/functions.html
比如字符串拼接：CONCAT('hello'，‘world’)，时间格式化:SELECT DATE_FORMAT(ctime,"%Y-%m"),COUNT(1) FROM tb GROUP BY DATE_FORMAT(ctime,"%Y-%m")

设置自定义函数：（注意：与触发器不同，函数中不能写sql语句）
delimiter \
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \
delimiter ;

函数调用：
SELECT f1(nid,id),name FROM tb1

删除函数：
DROP function f1

存储过程：是一个SQL语句集合，当程序调用存储过程的时候，会执行存储过程中的SQL语句
delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;

调用：call p1()

存储过程可以传递参数，参数有三种类型：
in 仅可以作为传入参数
out 尽可以作为存储过程的传出值
inout 可以作为传入或传出的参数

===========创建一个带参数的存储过程：===========
delimiter \
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;   
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2;
    set i3 = i3 + 100;

end\
delimiter ;

==========执行存储过程=============
set @t1 =4;   注意out类型的值传入要用变量传入
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;



==========out值的意义========
当我们执行存储过程的时候会返回给我们 SQL语句的结果集，同时也会返回给我们out的数值，这有什么意义呢？ 其实在存储过程中如果有很多SQL语句，我们无法确定是否全部执行成功，如果在SQL语句的最后加上一个对out变量的操作，就可以检查存储过程是否执行成功了。

事物：让SQL存储过程支持事物操作
delimiter \
create PROCEDURE p5(OUT p_return_code tinyint) 
BEGIN 
DECLARE exit handler for sqlexception （如果发生异常会执行DECLARE部分下的代码）
BEGIN 
-- ERROR 
set p_return_code = 1; 
rollback; 
END; 
             
START TRANSACTION; （正常执行代码）
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT; 
             
-- SUCCESS 
set p_return_code = 2; 
             
END\
delimiter ;

存储过程中用set设置变量，用call来调用存储过程，select查看结果

游标：是SQL中的for循环语法，如果要对数据每一行进行计算处理可以使用，相当于自建一个方法对每一行的字段进行处理。

delimiter //
create procedure p3()
begin 
declare ssid int; -- 自定义变量1  
declare ssname varchar(50); -- 自定义变量2  
DECLARE done INT DEFAULT FALSE;


DECLARE my_cursor CURSOR FOR select sid,sname from student;（游标处理哪些数据）
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;（游标停止条件）
                        
open my_cursor;
xxoo: LOOP
 fetch my_cursor into ssid,ssname;
 if done then 
 leave xxoo;
END IF;（下面写游标完成后执行的操作）
insert into teacher(tname) values(ssname);
end loop xxoo;
close my_cursor;
end  //
delimter ;

动态执行SQL（防SQL注入）

delimiter \
DROP PROCEDURE IF EXISTS proc_sql \
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?'; （准备阶段预检测SQL语句的合法性）
    EXECUTE prod USING @p1;（格式化SQL语句，如拼接）
    DEALLOCATE prepare prod; （执行）

END\
delimiter ;