学习MySQL过程中的随笔二

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. 索引
  作用:
    - 约束
    - 加速查找
  索引:
    - 主键索引:加速查找 + 不能为空 + 不能重复
    - 普通索引:加速查找
    - 唯一索引:加速查找 + 不能重复
    - 联合索引(多列):
      - 联合主键索引
      - 联合唯一索引
      - 联合普通索引

待补充..........

其他

上一篇:C#获取年龄段 几零后


下一篇:Spring Boot 探索系列 - 自动化配置篇