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。将  视为字符串连接符，在Oracle等数据库中，  被视为字符串的连接操作符，所以在其他数据库中含有  操作符的sql在MySql将无法执行，为了解决这个
问题mysql提供了PIPES_AS_CONCAT模式、

MySql分区

MySql从5.1版本开始支持分区，分区是指按照一定的规则，数据库把一个表分解成多个更小的，更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上

这个表可能由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用而言是完全透明的，不影响应用的业务逻辑优点

• 和单个磁盘或者文件系统分区相比，可以存储更多数据

• 优化查询。在where子句中包含分区条件，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及SUM（）和COUNT（）这类聚合函数的查询时，可以容易的在每个分区上并行

  处理，最终只需要汇总所有分区的结果

• 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据

• 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

分区有利于管理非常大的表，它采用 分而治之的逻辑，分区引入分区键的概念，分区键用于根据某个区间键，特定值列表或者HASH函数执行数据的聚集，让数据根据规则分布在不同的分区

中，让一个大对象变成一些小对象

show VARIABLES like '%partition%' 查看是否支持分区

Mysql支持大部分存储引起如MyISAM，INNODb,Memory等存储引擎，创建分区，在5.1版本中，同一个分区表的所以分区必须使用同一个存储引擎；
在同要给表上，不能对一个分区使用MyISAM引擎和Innodb引擎，但是在同一个MySQL服务器服务器上，甚至同一个数据库中，对于不同的分区表使用不同的存储引擎

分区类型

• range分区：基于一个给定连续区间范围，把数据分配到不同的分区。

• LIST分区：类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区

• HASH分区：基于给定的分区个数，把数据分配到不同的分区

• KEY分区：类似HASH分区

range分区

CREATE TABLE emp(
    id int not null,
    ename varchar(30),
    hired date not null DEFAULT '1970-01-01',
    separated date NOT null DEFAULT '9999-12-21',
    job varchar(30) not null,
    store_id int not null
)
partition by range(store_id)(
    PARTITION p0 VALUES less than (10),
    PARTITION p1 VALUES less than (20),
    PARTITION p2 VALUES less than (30)
);
//上述的分区方案将storid,1-9分到p0区，10-19分到p1区，等如果插入大于30，会出现错误，因为没有规则保护大于30的

INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',19);//可以

//Table has no partition for value 40
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',40);

添加分区
alter  table emp add partition(partition p3 values less than maxvalue);
maxvalue表示最大的可能的整数值

mysql 支持在values less than 语句中加入表达式
比如以日期作为分区
CREATE TABLE emp(
    id int not null,
    ename varchar(30),
    hired date not null DEFAULT '1970-01-01',
    separated date NOT null DEFAULT '9999-12-21',
    job varchar(30) not null,
    store_id int not null
)
partition by range(year(separated ))(
    PARTITION p0 VALUES less than (1995),
    PARTITION p1 VALUES less than (2000),
    PARTITION p2 VALUES less than (2005)
);
MySQl 5.5改进了range分区给你，通过支持非整数分区，创建日期分区就不需要通过函数进行转换
partition by range(separated )(
    PARTITION p0 VALUES less than ('1996-01-01'),
    PARTITION p1 VALUES less than ('2001-01-01'),
    PARTITION p2 VALUES less than ('2006-01-01')
);

List分区

create table expenses（
  expense_date date not null,
  category int,
  amount decimal(10,3)
）partition by list (category)(
   partition p0 values in(3,5),
   partition p1 values in(1,10),
   partition p2 values in(4,9),
   partition p3 values in(2),
   partition p4 values in(6)
)

LIST分区不存在类似于VALUES LESS THAN MAXVALUE这样的值 在MYSQL5.5支持非整数分区

Columns分区

create table rc3(a int,b int)
parition by range columns(a,b)(
 parition p01 values less than(0,10),
 parition p01 values less than(10,10),
 parition p01 values less than(10,20),
 parition p01 values less than(maxvalue,maxvalue)
)

Hash分区

create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,
store_id int not null) partition by hash(store_id)partitions 4;

这里创建了一个常规的hash 使用 partition by hash（expr）其中expr是某列值或一个整数值的表达式返回值。 partition num 对分区类型，分区键，分区个数进行定义，上述基于
store_id列hash分区，表被分为4个分区

我们可以计算出它被保存在哪个分区中假设，假设记录的分区编号为N，那么N=MOD（expr，num），例如emp表中有4个分区，插入一个store_id为234的 mod（234,4）=2,倍保存在第二个
分区

相关推荐：

关于mysql 基础知识的总结

PHP 和 MySQL 基础教程（一）

PHP 和 MySQL 基础教程（二）

PHP 和 MySQL 基础教程（三）

PHP 和 MySQL 基础教程（四）