MySQL深入学习:
视图
使用视图的目的:多次使用同一张临时表(或者已经存在的表)
视图只是一个虚拟表,其本质为【根据SQL语句获取动态的数据集,并为其命名】,用户只需使用别名即可获得实时的数据集,并可以将其作为表使用。
1、创建视图:create view 视图名称 as SQL语句; # 例:create view v1 as select * from userinfo;
2、修改视图:alter view 视图名称 as SQL语句; # 例:alter view v1 as select name from userinfo;
3、删除视图:drop view 视图名称; # 例:drop view v1;
4、注意事项:由于视图是虚拟表,故无法用其对真实表进行增、删、改,仅仅作为查询使用
触发器
对于某个表进行【增、删、改】操作的前后关联某些特定的行为时,可以使用触发器。
1、创建触发器:
通用格式为:
delimiter // # 修改操作的结束符为//
create trigger 触发器名称 【before / after】 【insert / delete / update】 on student for each row
begin
关联SQL语句......
end //
delimiter ; # 还原结束符为;
# 插入前关联语句:insert into teacher (name) values ("new.name"); # 关于插入时,可以在创建触发器时将关联语句里的参数置为new,得到的为即将插入的数据行。
# 删除前关联语句:delete from teacher where id > 10; # 关于删除时,可以在创建触发器时将关联语句里的参数置为old,得到的为即将删除的数据行。
# 更新前关联语句:update teacher set name = "wahaha" where id = 10;
事例代码:
-- 触发器
delimiter //
create trigger cfq before insert on userinfo for each row
begin
-- insert into t1 (name) values ('wahha') ;
insert into t1 (name) values (new.name) ;
-- insert into t2 (name,age,color) values (new.name,20,"yellow") ;
-- delete from t1 where id = 7;
update t1 set name =" 哇哈" where id = 6 ;
end //
delimiter ;
drop trigger cfq;
insert into userinfo (name,email) values ("娃哈哈1","10000000@qq.com"),("娃哈哈2","10000000@qq.com"),("娃哈哈3","10000000@qq.com") ;
-- select * from userinfo ;
-- delete from userinfo where id > 65 ;
-- update userinfo set name = 'ABC' where id = 12;
2、删除触发器:drop trigger cfq;
3、使用触发器:触发器无法被用户直接调用,而是基于对表的【增、删、改】的操作后引发的。
4、报错的情况:
MySQL This function has none of DETERMINISTIC, NO SQL...错误1418 的原因分析及解决方法 : 解决办法也有两种, 第一种是在创建子程序(存储过程、函数、触发器)时,声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个, 例如: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`() DETERMINISTIC BEGIN #Routine body goes here... END;; 第二种是信任子程序的创建者,禁止创建、修改子程序时对SUPER权限的要求,设置log_bin_trust_routine_creators全局系统变量为1。 设置方法有三种: 1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1; 2.MySQL启动时,加上--log-bin-trust-function-creators选贤,参数设置为1 3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1
函数
MySQL中提供的内置函数:<--官方链接-->
1、自定义函数:
delimiter \\
create function f1(
i1 int,
i2 int
)
returns int
begin
declare num int default 0; # 声明默认值为0的变量num
set num=i1+i2;
return (num);
end \\
delimiter ;
2、调用函数:select f1 (1,100) ;
3、删除函数:drop function f1 ;
存储过程
存储过程实际上是SQL语句集合,比函数更为高级,二者都可以传参数,但在函数当中不能执行SQL语句,而在存储过程中却可以,当调用存储过程时,其中内部的SQL语句会按照逻辑执行。
对于存储过程,其中的参数有三类:
- in 仅用于传入参数
- out 仅用于返回
- inout 既可以传入,又可以当作返回值使用
-- 无参数的存储过程
delimiter //
create procedure p1()
begin
select * from userinfo where id > 60;
end //
delimiter ;
call p1();
drop procedure p1;
无参数的存储过程
-- 带in参数的存储函数
delimiter //
create procedure p1(
in n1 int,
in n2 int
)
begin
select * from userinfo where id > n1;
end //
delimiter ;
call p1(60,12);
drop procedure p1; -- 带out参数的存储函数
delimiter //
create procedure p1(
in n1 int,
out n2 int,
out n3 int
)
begin
set n2=123123;
set n3=456456;
select * from userinfo where id > n1;
end //
delimiter ;
set @v2=100;
set @v3=200;
call p1(60,@v2,@v3);
select @v2,@v3;
drop procedure p1;
有参数的存储过程
-- 事务
-- 类似于转账时,若转的过程中遇到错误,则金额会通过rollback的方式返回原账户
-- 第一步,创建存储过程
delimiter //
create procedure p1(
out p_return_code tinyint#定义状态量,根据返回值判断程序进行的状态
)
begin
declare exit handler for sqlexception#出错时执行的语句
begin
-- error
set p_return_code=1;
rollback;#回滚
end; declare exit handler for sqlwarning#有警告时执行的语句
begin
-- warning
set p_return_code=2;
rollback;#回滚
end; start transaction;#若无错误,则正常执行的语句
delete from b;
select * from userinfo;
commit;#提交数据 -- success
set p_return_code=0;#语句执行成功时,状态量设置为0
end //
delimiter ;
#第二步,执行存储过程,并在最后删除
set @v1=0;
call p1(@v1);
select @v1;
drop procedure p1;
事务(类似于转账出错)
#第一步,创建表格
create table A(
id int not null auto_increment primary key,
num int
)engine=innodb default charset=utf8; create table B(
id int not nint
)engine=innodb default charset=utf8; drop table A,B;#删除表格
#第二步,创建游标
delimiter //
create procedure p1()
begin
declare row_id int;#定义变量
declare row_num int;#定义变量
declare temp int;#定义变量
declare done int default false;#定义状态量,如果为true,则表示操作完成
declare my_cursor cursor for select id,num from a;#定义游标,得到一个表
declare continue handler for not found set done=true;#如果没有数据,则自动将状态设置为true open my_cursor;#创建游标,类似于打开文件
func:loop#创建循环,其关键字为loop
fetch my_cursor into row_id,row_num;#从游标中取数值
if done then leave func;#如果数据取完了为NULL,则退出
end if;
set temp=row_id+row_num;
insert into b(number)values(temp);#插入数据
end loop func;#退出循环
close my_cursor;#关闭游标
end //
demiliter ;
#第三步,执行存储过程,并在最后删除
call p1();
drop procedure p1;
游标(循环操作表格每一行)
-- 动态执行MySQL(防SQL注入)
#第一步,创建存储过程
delimiter //
create procedure p1(
in nid int
)
begin
set @nid=nid;
prepare prod from "select * from userinfo where is > ?";#执行的语句的名称为prod
execute prod using @nid;#将占位符替换成相对应的值
deallocate prepare prod;#执行语句
end //
delimiter ;
#第二步,执行并删除存储过程。
call p1(60);
drop procedure p1;
动态执行SQL(防SQL注入)
事务
索引
1. 索引
作用:
- 约束
- 加速查找
索引:
- 主键索引:加速查找 + 不能为空 + 不能重复
- 普通索引:加速查找
- 唯一索引:加速查找 + 不能重复
- 联合索引(多列):
- 联合主键索引
- 联合唯一索引
- 联合普通索引
待补充..........
其他