MySQL操作/上
一、视图
视图表是一个虚拟表（非真实存在），其本质是【根据sql语句获取动态的数据集，并为其命名】，用户使用表只需使用（名称）即可获取结果集，并可以将其当做表来使用。
1.创建视图：
格式： create view 视图名称 as SQL语句
CREATE VIEW v2 AS SELECT *FROM student WHERE sid > 10
2.删除视图：
格式：drop view 视图名称
DROP VIEW v1
3.修改视图：
格式：alter view 视图名称 as SQL
4.使用视图：
使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。
select *from v1
二、触发器
当对某张表做：增删改操作时，可以使用触发器自定义关联行为，可以使用触发器，触发器用于定制用户对表的行进行【增删改】前后的行为。
1.创建基本语法:
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END # 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END # 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END # 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END # 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END # 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
创建基本语法
--
-- delimiter// -- 声明以//作为结束
--
-- create trigger t1 BEFORE INSERT on student for EACH ROW -- 创建触发器
--
-- BEGIN
-- INSERT into teacher(tname) VALUES("karina");
--
-- END//
-- delimiter; insert into student(gender,class_id,sname) VALUES("女",4,"as"),("男",4,"djls");
- delimiter// -- 声明以//作为结束
--
-- create trigger t1 BEFORE delete on student for EACH ROW -- 创建触发器
--
-- BEGIN
-- INSERT into teacher(tname) VALUES("karina");
--
-- END//
-- delimiter;
insert into student(gender,class_id,sname) VALUES("女",4,"as"),("男",4,"djls");
--new 代表新数据 insert时
--old 代表旧数据 delete时
三、函数
内置函数：
CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 CONV(N,from_base,to_base)
进制转换
例如：
SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
内置函数
更多函数请点击：http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
自定义函数(有返回值)：
delimiter \\
create FUNCTION f2(
i1 int,
i2 int)
returns INT
BEGIN
declare num int default 0;
set num =i1+i2;
return(num);
end \\
delimiter ; select f2(1,100); select 函数名调用()
四、存储过程
存储过程是一个sql语句集合，当主动去调用存储过程时，其中内部的sql语句会按照逻辑执行
创建存储过程：
delimiter \\
create procedure p1() -- 创建存储过程
BEGIN
select *from student ; --sql语
end \\
delimiter; call p1() -- call 调用执行存储过程
in 能传值
out 从外面拿值，伪造一个返回值
in out 既能传值也能往回拿值
1.简单存储过程
create procedure p1()
BEGIN
select * from student;
INSERT into teacher(tname) values("ct");
END call p1()
cursor.callproc('p1')
2.传参数（in，out，inout）
delimiter \\
create PROCEDURE p3(
in n1 int,
in n2 INT
)
BEGIN
select *from student where sid>n1;
END \\
delimiter;
call p3(12,2)
3.参数 inout
delimiter //
create procedure p3(
in n1 int,
inout n2 int
)
BEGIN
set n2 = 123123;
select * from student where sid > n1;
END //
delimiter ; set @v1 = 10;
call p2(12,@v1)
select @v1; set @_p3_0 = 12
ser @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1
4. 事务
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 声明如果出现异常则执行{
set status = 1;
rollback;
} 开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束 set status = 2; END //
delimiter ;
delimiter \\
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
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 ;
5. 游标
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE;
declare temp int; declare my_cursor CURSOR FOR select id,num from A;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor; end //
delimter ;
6. 动态执行SQL（防SQL注入）
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1. 预检测某个东西 SQL语句合法性
2. SQL =格式化 tpl + arg
3. 执行SQL语句 set @xo = arg;
PREPARE xxx FROM 'select * from student where sid > ?';
EXECUTE xxx USING @xo;
DEALLOCATE prepare prod;
end //
delimter ; call p7("select * from tb where id > ?",9)
===> delimiter \\
CREATE PROCEDURE p8 (
in nid int
)
BEGIN
set @nid = nid;
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;