mysql基础知识

存储过程和函数的相关操作.

在对储存过程和函数操作时，需要首先确认用户是否具有相应的权限。例如，创建存储过程或者函数需要CREATE ROUTINE权限，修改或者删除存储过程或者函数需要ALTER ROUT

INE权限，执行过程或者函数需要EXECUTE权限

创建一个新的过程 film_in_stock,该过程用来检查 film_id和store_id对应的inventory是否满足要求，并且返回满足的inventory_id 以及满足要求的记录数
CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int)
READS sql data
begin
  select inventory_id
  from inventory
  where film_id =p_film_id
  and store_id=p_store_id
  and inventory_in_stock(inventory_id);

  SELECT found_rows() into p_film_count;
end $$

通常在创建过程和函数之前，都会通过DELIMITE $$命令将语句的结束符从'；'修改成其他符号，这里使用‘$$’，这样在过程和函数中的
';'就不会被MySql，解释成语句的结束而错误。在存储过程或者函数创建完成 通过‘DELIMITER;'命令在将结束符改回成';'
调用过程
CALL film_in_stock(2,2,@a);
存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，一旦逻辑改变，只需要修改存储过程，对调用者的程序没有影响

删除存储过程或者函数
一次只能删除一个存储过程或者函数，删除需要ALTER ROUTINE权限

drop procedure film_in_stock;

查看存储过程或者函数状态

show procedure status like 'film_in_stock';

查看存储过程的函数定义
show create procedure film_in_stock

变量使用
存储过程和函数中可以使用变量，在MySql 5.1版本中，变量不区分大小写

变量的定义
通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN...END中，可以用在嵌套块中
定义一个DATE类型的变量
DECLARE last_month_start date;
变量赋值 可以直接赋值，或者通过查询赋值。直接赋值使用set，可以赋常量或者赋表达式
set var_name=expr [,var_name=expr]...
set last_month_start=date_sub(current_date(),interval month);
select col_name[,...] into var_name[,...] table_expr;

定义条件和处理

delimiter $$create procedure actor_insert()begin
 declare continue handler for sqlstate '23000' set @x2=1;
 set @x=1;
 insert into actor(actor_id,first_name,last_name) values(201,'test','201');
 set @x=2;
 insert into actor(actor_id,first_name,last_name) values(1,'test','1');
 set @x=3;end ;$$

调用处理函数时遇到主键重的错误会按照定义的处理方式去处理，由于定义的是CONTINUE 会继续执行下面的语句
还支持EXIT表示终止

光标使用

声明光标
declare cursor_name cursor for select_statement
open光标
open cursor_name
fetch光标
fetch cursor_name into var_name[,var_name]...
close光标
close cursor_name

delimiter $$
create procedure payment_stat()
begin
 declare i_staff_id int;
 declare d_amount decimal(5,2);
 declare cur_payment cursor for select staff_id,amount from payment;
 declare exit handler for not found close cur_payment;
  set @x1=0;
  set @x2=0;
  open cur_payment;
 REPEAT
   FETCH cur_payment into i_staff_id,d_amount;
    if i_staff_id =2 then
    set @x1=@x1+d_amount;
    else
    set @x2=@x2+d_amount;
    end if;
 until 0 end repeat;
 close cur_payment;
 end;
 $$
变量，条件，处理程序，光标都是通过DECLARE定义的，她们之间是有先后顺序要求的。
变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明

控制语句

case 
 when i_staff_id =2 then
 set @x1=@x1+d_amount; else
 set @x2=@x2+d_amount;loop 和leave结合create procedure actor_insert()begin
 set @x=0;
 ins:loop
  set @x=@x+1;  if @x=100 then
  leave ins;  end if;
  insert into actor(first_name,last_name) values('Test','201');  end loop ins;end;
$$

inerate 语句作用是跳过当前循环的剩下语句，直接进入下一轮循环create procedure actor_insert()begin
 set @x=0;
 ins:loop
 set @x=@x+1; if @x=10 then
 leave ins;
 elseif mod(@x,2)=0 then
 iterate ins; end if;
 insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x); end loop ins;end;
$$repeat 语句 有条件的循环控制语句，当满足条件的时候退出循环repeat
  fetch cur_payment into i_staff_id,d_amount;  if i_staff_id =2 then
   set @x1=@x1+d_amount;  else
   set @x2=@x2+d_amount;  end if; until 0 end repeat;whiledelimiter $$create procedure loop_demo()begin
 set @x=1,@x1=1;
 repeat
   set @x=@x+1；  until @x>0 end repeat;  while @x<1 do
   set @x=@x+1;  end while; end;
 $$//创建事件调度器CREATE EVEN test_event_1 ON SCHEDULE
EVERY 5 SECONDDOINSERT INTO dept(deptno,deptname)
VALUES(3,'3');//查看本地调度器状态
 show variables like '%scheduler%'; //打开调度器
 set global event_scheduler=1; //查看后台进程
 show processlist; //创建一个新的定时器 定时清空表，防止表变大，这类触发器非常适合去定期清空临时表或者日志表
 create event trunc_test on schedule every 1 minute do truncate table test;

 禁用调度器或者删除
 alter event test_event_1 disable;
 drop event test_event_1;

SQL Mode

在MySql中，SQLMode常用来解决下面几类问题

• 通过设置SQL Mode，可以完成不同严格程度的数据校验，有效的保障数据准确性。

• 通过设置SQL Mode，为ANSI模式，来保证大多数SQL符合标准的Sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务SQL进行较大的修改

• 在不同数据库之间进行数据迁移之前，通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中

查看 SQL Mode命令select @@sql_mode

插入一个出国实际定义值的大小varchar(10)insert into value('123400000000000000000000000000000');//查看warning内容show warningsselect * from t 这里对插入的数据
进行截取前10位

设置SQL Mode为 严格模式set session sql_mode='STRICT_TRANS_TABLES'再次插入insert into value('123400000000000000000000000000000'); 直接给出ERROR，而不是
warning

SQL Mode常见功能
校验日期是合法性set seesion sql_mode='ANSI'insert into t values('2007-04-31')
结果是 插入值变成'0000-00-00 00:00:00' 并且系统给出warning 而在TRADITIONAL模式下，直接提示日期非法，拒绝插入，同时Mode（x,0）也会报错

qidon NO_BACKSLASH_ESCAPES模式，使反斜杠成为普通字符，在导入数据时，如果数据含有反斜杠字符,你们启动NO_BACKSLASH_ESCAPES模式，保证数据的正确性

启动PIPES_AS_CONCAT。将(北联网教程,专业提供视频软件下载)

 
第1页  第2页  第3页  第4页  第5页  第6页  第7页  第8页 
 
……
标签:mysql基础知识