mysql常用语句

use openshop_version1;
select NOW();
-- 编码查询
show variables like 'character%';
show variables like 'table_type';

select VERSION()
select DATABASE()
SELECT `DATABASE`();

-- 查看表信息
show create table abcd; -- 表创建信息
desc abcd;-- 表结构信息
show columns from abcd; -- 表结构信息
describe abcd;
show DATABASES;-- 查看所以数据库
show tables; -- 显示所有表

select * from columns where table_name='abcd';
select * from information_schema.COLUMNS where TABLE_SCHEMA='abcd' and TABLE_NAME='abcd';

/*
SET NAMES显示客户端发送的SQL语句中使用什么字符集。因此，SET NAMES 'cp1251'语句告诉服务器将来从这个客户端传来的信息采用字符集cp1251”。它还为服务器发送回客户端的结果指定了字符集。（例如，如果你使用一个SELECT语句，它表示列值使用了什么字符集。）
MySQL字符集设置
• 系统变量：
– character_set_server：默认的内部操作字符集
– character_set_client：客户端来源数据使用的字符集
– character_set_connection：连接层字符集
– character_set_results：查询结果字符集
– character_set_database：当前选中数据库的默认字符集
– character_set_system：系统元数据(字段名等)字符集
– 还有以collation_开头的同上面对应的变量，用来描述字符序。
• 用introducer指定文本字符串的字符集：
– 格式为：[_charset] 'string' [COLLATE collation]
– 例如：
• SELECT _latin1 'string';
• SELECT _utf8 '你好' COLLATE utf8_general_ci;
– 由introducer修饰的文本字符串在请求过程中不经过多余的转码，直接转换为内部字符集处理。
MySQL中的字符集转换过程
1. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
• 使用每个数据字段的CHARACTER SET设定值；
• 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
• 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
• 若上述值不存在，则使用character_set_server设定值。
3. 将操作结果从内部操作字符集转换为character_set_results。
*/
-- 创建utf8的数据库
create DATABASE if NOT EXISTS hechunhu DEFAULT CHARACTER SET utf8;
use openshop_version1

-- 删除数据库
drop DATABASE hechunhu;
create DATABASE if not EXISTS hechunhu DEFAULT CHARACTER SET utf8;

-- 设置链接方式为utf8
set NAMES 'utf8';
-- 它相当于下面的三句指令： 6 {% }1 _& [, e& B4 l& c
SET character_set_client = utf8;
SET character_set_results = utf8; # h( e% x8 A k7 ]2 _0 q
SET character_set_connection = utf8; #O2 x& A0 p+ [# - X% g% ]% l6 E
-- 创建数据库并设为utf8
create database if not exists test default character set utf8;

ALTER TABLE newname RENAME as abc
create table bcd select * from abc LIMIT 100
describe abc;
SELECT NOW()
SELECT COLUMNS from abc
select * from abc
-- 查询表

SELECT * from abc WHERE user_id='yuzhonghe@126.com';
select * from abc where sign_id in(60,61);
show TABLES
SELECT * from abcde a,abcdef b
SELECT * from abcde a,abcdef b WHERE a.id=b.id
SELECT * from abcde full JOIN abcdef -- 将两个记录集进行合并，并且去掉重复的记录
SELECT name FROM abcde UNION SELECT name FROM abcdef -- 去掉重复的记录
SELECT name FROM abcde UNION ALL SELECT name FROM abcdef -- 不去掉重复的记录
SELECT * from abcde inner JOIN abcdef ON abcdef.id=abcde.id
SELECT * from abcde LEFT JOIN abcdef ON abcdef.id=abcde.id
SELECT * from abcde right JOIN abcdef ON abcdef.id=abcde.id
SELECT * from abcde cross JOIN abcdef ON abcdef.id=abcde.id
SELECT * from abcde JOIN abcdef
SELECT * from abcde JOIN abcdef ON abcdef.id=abcde.id

SELECT * FROM abcdef
SELECT * FROM abcde
SELECT * from abcd WHERE UNIX_TIMESTAMP(datetime1) BETWEEN UNIX_TIMESTAMP("2012-09-26 09:00:01") and UNIX_TIMESTAMP("2012-09-26 23:00:00");
SELECT * from abcd WHERE datetime1 BETWEEN '2012-09-26 09:00:01' and '2012-09-26 23:00:00';
-- 搜索时间段数据
SELECT * from abcd WHERE datetime1 > '2012-09-26 09:00:01' and datetime1 < '2012-09-26 23:00:00';
-- 搜索某天数据
select * from abcd WHERE date_format(datetime1,'%Y-%m-%d') = '2012-09-26';
select * from abcd WHERE date(datetime1) = '2012-09-26';
-- 搜索今天数据
select * from abcd WHERE to_days(datetime1) = TO_DAYS(NOW());
-- 搜索几天钱的数据数据
SELECT * from abcd WHERE to_days(NOW()) -to_days(datetime1)=15;
-- 搜索近7天数据
select * FROM abcd WHERE date(datetime1) >= DATE_ADD(CURDATE(),INTERVAL 15 day);

-- 搜索昨天数据
-- 搜索昨天数据
-- 搜索昨天数据
SELECT TO_DAYS(NOW());
SELECT UNIX_TIMESTAMP("2012-09-26 09:00:01")
select TO_DAYS('1997-10-07');
SELECT CURDATE();
SELECT date(NOW());
SELECT CURRENT_TIMESTAMP();

SELECT CONCAT(user_id,sign_amount) as fullname from abc WHERE user_id='yuzhonghe@126.com'
update abc SET sign_amount =sign_amount +1 where user_id='yuzhonghe@126.com'
INSERT into abc(`user_id`,`sign_time`,`sign_amount`,`is_change`,`vouchers_sn`,`sign_validity`) VALUES('yuzhonghe@126.com',NOW(),'1','0','',NOW())
-- 清空表
DELETE FROM abc
TRUNCATE TABLE abcdef
SELECT * FROM abcdef
SELECT * FROM abcd
-- 复制表结构及数据到新表
CREATE TABLE abcdef SELECT * FROM abcd
-- 只复制表结构到新表
CREATE TABLE abcdef SELECT * FROM abcd where 1=2
create table abcdef LIKE abcd

-- 复制旧表的数据到新表(假设两个表结构一样)
INSERT into abcdef SELECT *from abcd
INSERT into abcdef(NAME,sex) SELECT name,sex from abcd

-- sql复制旧表的数据到新表(假设两个表结构不一样)
-- INSERT into abc(user_id,sign_time,sign_amount) SELECT name,datetime1,sex from abcd

-- sql 可以将表1结构复制到表2
-- SELECT * INTO abcdef FROM abcd WHERE 1=2

-- myisan 和innodb的区别
/*
MyIASM是IASM表的新版本，有如下扩展：
二进制层次的可移植性。
NULL列索引。
对变长行比ISAM表有更少的碎片。
支持大文件。
更好的索引压缩。
更好的键吗统计分布。
更好和更快的auto_increment处理。

1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
*/

-- 删除表
drop TABLE abcdef
CREATE table abc select * from bcd
UPDATE abc SET `user_id`='123@126.com' where user_id='yuzhonghe@126'
mysqldump -r root -p abc>'c:abc.sql'
net STOP mysql
mysqldump -h localhost -u root -p openshop > openshop.sql
select ord('DEL')
select CONCAT('a','v','v')

select LPAD('hechunhua',3,'3')
SELECT substring('hechunhua',3,5)

use openshop_version1
-- 创建表并设为utf8编码
CREATE TABLE IF NOT EXISTs abcdef(
id int(10) not null auto_increment PRIMARY key COMMENT '主键',
name varchar(10) COMMENT '姓名',
class int(1) comment '性别'
)ENGINE=INNODB DEFAULT CHARSET=utf8; -- 表引擎支持存储过程和外部键等高级数据库功能。MyISAM类型不支持事务处理等高级处理，MyISAM类型的表强调的是性能其执行数度比InnoDB类型更快
select * from abcdef
-- 建表并设置默认时间
create table abcdef(
id int not null auto_increment PRIMARY KEY COMMENT '主键',
name1 char(20) COMMENT '姓名',
sex int COMMENT '性别',
date timestamp not null DEFAULT current_timestamp,-- 或者将current_timestamp改为now()： timestamp not null DEFAULT NOW(),
abcd_id INT(10),
foreign key(abcd_id) references abcd(id) on delete cascade on update cascade
)ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT * from abcdef
-- 设置字段为utf8编码
CREATE TABLE IF NOT EXISTs abcdef(
id int(10) not null auto_increment PRIMARY key COMMENT '主键',
name varchar(10) CHARACTER SET utf8 COMMENT '姓名',
class int(1) comment '性别'
)ENGINE=INNODB -- 表引擎支持存储过程
-- 修改表
SELECT * from abcd
alter table abcd add datetime1 timestamp not null default current_timestamp -- 或当前时间设为默认值
/*
添加字段：
alter table `user_movement_log` Add column GatewayId int not null default 0 AFTER `Regionid` (在哪个字段后面添加)
删除字段：
alter table `user_movement_log` drop column Gatewayid
调整字段顺序：
ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID
//主键
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);
//增加一个新列
alter table t2 add newfield timestamp; (简单语法，只指定新添字段newfield 类型为timestamp)
alter table infos add newfield tinyint not null default '0'; （同时指定字段null属性、默认值）
//删除列
alter table t2 drop column c; （删除列c）
//重命名列
alter table t1 change a b integer; （把列a改名为b，注意要指定列类型）
//改变列的类型
alter table t1 change b b bigint not null; （把列a改名为b，指定列类型及null属性）
alter table infos change list list tinyint not null default '0';
//重命名表
alter table t1 rename t2;
加索引
mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[，字段名2 …]);
mysql> alter table tablename add index emp_name (name);
加主关键字的索引
mysql> alter table tablename add primary key(id);
加唯一限制条件的索引
mysql> alter table tablename add unique emp_name2(cardnumber);
删除某个索引
mysql>alter table tablename drop index emp_name;
修改表：
增加字段：
mysql> ALTER TABLE table_name ADD field_name field_type;
修改原字段名称及类型：
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
删除字段：
mysql> ALTER TABLE table_name DROP field_name;*/
-- 修改表字段名或类型
ALTER TABLE abcd change name name varchar(20) DEFAULT NULL COMMENT '姓名';
-- 修改表
-- 1.修改表类型
alter table emp modify ename varchar(20); #修改emp的ename字段定义,将varchar(10)改为varchar(20)
-- 2.增加表字段
alter table emp add column age int(3); #表emp上新增加字段age,类型为int(3);
-- 3.删除表字段
alter table emp drop column age; #将字段age删除
-- 4.字段改名
alter table emp change age age1 int(4); #将age改名为age1,同时修改字段类型为int(4);
-- 注意：change和modify均可修改表的定义,不同的是change后面需要写两次列名,但是change的优点是可以修改列名,而modify不可。
-- 5.修改字段排列顺序
-- 之前所介绍的字段增加和修改语法(add/change/modify)中,都有一个可选项first|after column_name,这个选项可以用来修改字段在表中的位置,默认add增加的新字段是加在表的最后位置,而change/modify默认都不会改变字段的位置。
alter table emp add birth date after ename; #将新增的字段birth加在ename之后
alter table emp modify age int(3) first; #修改字段age,将它放在最前面
-- 注意：fist|after column_name这些关键字都属于MySQL在标准SQL上的扩展,在其他数据库上不一定适用。
-- 6.更改表名
alter table emp rename emp1; #将emp改名为emp1
desc emp; #ERROR 1146 (42S02):Table 'sakila.emp' doesn't existdesc emp1;
CREATE TABLE IF NOT EXISTs abcdef(
id int(10) not null auto_increment PRIMARY key COMMENT '主键',
name varchar(10) CHARACTER SET utf8 COMMENT '姓名',
class int(1) comment '班级'
)ENGINE=INNODB -- 表引擎支持存储过程

-- 显示数据库和表引擎
show create DATABASE openshop_version1;
show create table abcdef
-- 修改表引擎
alter table abcdef type=INNODB;
-- 临时改变默认表类型可以用：
set table_type=InnoDB;

select * from abcdef
-- 插入数据
INSERT into abcd(name,sex) VALUES('hec',1)
INSERT into abcdef VALUES(null,'和纯化',1)
DROP table abcdef
SELECT * INTO abcd FROM abcde
-- 复制表
create table dust select * from student;
set NAMES utf8
INSERT INTO abcd(name,sex) SELECT name,sex from abcde
-- MYSQL不支持:
Select * Into new_table_name from old_table_name;

-- 变量定义 MYSQL 变量定义应该只能在存储过程, 函数里面定义.
DECLARE cnt int;

-- case
SELECT name,
case when sex=0 THEN '女'
else '男' END '性别'
from abcd
-- if
BEGIN
-- DECLARE cnt int;
select count(*) into cnt from abcd where sex = 1 -- 这个SELECT语法把选定的列直接存储到变量。
if cnt>5 THEN SELECT 3;
else SELECT 4;
END IF;
END
-- 表联合查询注意：表结构要相同，名字不同没事
SELECT * from abc UNION all SELECT * FROM abcd
SELECT * from abcdef UNION SELECT * FROM abcd

-- 创建临时表关掉链接就删除临时表了
create TEMPORARY TABLE temp_test (SELECT * from abcd)
SELECT * from temp_test

-- --------------------------------------------------------------------------变量问题--------------------------------------------------------------------------------------------------
-- 变量问题
/*
使用select定义用户变量的实践
将如下语句改成select的形式：
set @VAR=(select sum(amount) from penalties);
我的修改：select @VAR:=(select sum(amount) from penalties);我这样改，虽然是可以的。但是，对比的书中的答案，发现这样的方式太蹩脚了。说明没有很好地理解select定义变量的本质。
在select中，发现有一个用户变量，如果没有定义，那么就会初始化。select子句原来的方式根本不受到影响。只是增加了用户变量。所以，还是按照原来的方式使用select子句。那么像：select sum(amount) from penalties。增加变量就成
了:select @VAR:=sum(amount) from penalties。
将sum(amount)的结果赋给变量@VAR:。变量前面有select，那用户么就是显示该变量了。
笔记部分：mysql变量的术语分类：
1.用户变量：以"@"开始，形式为"@变量名"
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名或者 set @@global.变量名
对所有客户端生效。只有具有super权限才可以设置全局变量
3.会话变量：只对连接的客户端有效。
4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量
通俗理解术语之间的区别：
用户定义的变量就叫用户变量。这样理解的话，会话变量和全局变量都可以是用户定义的变量。只是他们是对当前客户端生效还是对所有客户端生效的区别了。所以，用户变量包括了会话变量和全局变量
局部变量与用户变量的区分在于两点:1.用户变量是以"@"开头的。局部变量没有这个符号。2.定义变量不同。用户变量使用set语句，局部变量使用declare语句定义 3.作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。
所以，最后它们之间的层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。
使用备忘，set @var 若没有指定GLOBAL 或SESSION ，那么默认将会定义用户变量

两种方式定义用户变量：
1."=",如 set @a =3,@a:=5
2.":="。select常常这样使用
总结：使用select 和set设置变量的区别，set可以使用以上两种形式设置变量。而select只能使用":="的形式设置变量也可以用语句代替也可以用语句代替也可以用语句代替也可以用语句代替SET 来为用户变量分配一个值来为用户变量分配一个值来为用户变量分配一个值来为用户变量分配一个值。。。。在这种情况下在这种情况下在这种情况下在这种情况下，，，，分配符必须为分配符必须为分配符必须为分配符必须为:= 而不能而不能而不能而不能用用用用= ，，，，因为在非因为在非因为在非因为在非SET 语句中语句中语句中语句中= 被视为一个比较被视为一个比较被视为一个比较被视为一个比较操作符操作符操作符操作符
局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量
1.用户变量：以"@"开始，形式为"@变量名"
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名或者 set @@global.变量名或者 set @@变量名
对所有客户端生效。只有具有super权限才可以设置全局变量
局部变量与用户变量的区分在于两点:1.用户变量是以"@"开头的。局部变量没有这个符号。2.定义变量不同。用户变量使用set语句，局部变量使用declare语句定义 3.作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。
所以，最后它们之间的层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。
设置常量对group_concat()的配置影响：
SET @@GROUP_CONCAT_MAX_LEN=4
手册中提到设置的语法是这样的：
SET [SESSION | GLOBAL] group_concat_max_len = val;
以下两种形式都能达到达到同样的效果，但是有什么区别？

SET @@global.GROUP_CONCAT_MAX_LEN=4;
global可以省略，那么就变成了：SET @@GROUP_CONCAT_MAX_LEN=4;

之前的理解不怎么准确。现在对加深理解后的地方进行总结。
mysql中变量的层次关系是:大体包括用户变量和系统变量。系统变量包括系统会话变量和系统全局变量。
我是这样理解相互之间的区别：
因为用户变量就是用户定义的变量，系统变量就是mysql定义和维护的变量。所以，用户变量与系统变量的区别在于，是谁在管理这些变量。mysql一启动的时候就会读取系统变量(这样做目的是可以确定mysql的以何种机制或模式运行)。系统会话变量与用户变量都是在当前客户端退出后消失。他们之间的区别可以这样理解，虽然常常看到"set @@varible"的形式去改变系统变量的值，但是并不涉及到定义系统变量。用户变量是可以自己定义(初始化)。系统变量按照只是在改变值。
局部变量只在begin-end语句块中定义并有效。执行到该语句块之后就消失了。定义的方式有明显的特点，使用declare语句。
为什么看到使用系统变量以"@@变量名"和"变量名"的形式出现，怎么理解两者形式的区别？
使用系统变量理论上是可以使用两种形式：1.前面带有符号"@@" 2.符号省略。比如我会看的如下形式：CURRENT_USER。但是，约定系统变量要使用"@@变量名"的形式，就是在前面加上符号"@@"。为什么会出现CURRENT_USER这样没有符号的情况？看书籍《SQL For MySQL Developers A Comprehensive Tutorial and Reference》大致说明的原因，这样做是为了与其他的SQL产品保持一致。
hch的理解：变量分为；1、局部变量，会话变量（用户变量），系统变量（全局变量）
*/
SELECT * FROM abcd
set @var=123;
set @var=(select COUNT(*) from abcd);
SELECT @var;
select @var:=(select COUNT(*) from abcd) as count;
select @var:=count(*) as count from abcd
SELECT count(*) INTO @var FROM abcd;
SELECT @var123
-- 如果想要更改会话变量的值，利用语句：
　　set session varname = value;
　-- 　或者set @@session.varname = value;
-- 如果想要更改全局变量的值，将session改成global：
　　set global sort_buffer_size = 40000;
　　set @@global.sort_buffer_size = 40000;
　 -- 　不过要想更改全局变量的值，需要拥有SUPER权限。
　　-- 注意，ROOT只是一个内置的账号，而不是一种权限，
　-- 　这个账号拥有了MYSQL数据库里的所有权限。任何账号只要它拥有了名为SUPER的这个权限，
　-- 　就可以更改全局变量的值，正如任何用户只要拥有FILE权限就可以调用load_file或者
-- 凡是上面提到的session，都可以用local这个关键字来代替。
select @@local.sort_buffer_size
-- 注意，用户变量名在MySQL 5.1中是对大小写不敏感的。请参阅9.3节，“用户变量”。

-- ------------------------------------------------------------------------存储过程----------------------------------------------------------------------------------------------------
-- 删除存储过程 if语句
drop PROCEDURE IF EXISTS P_test;

-- 判断语句
SELECT * from abcd
UPDATE abcd SET sex=sex+2 where sex<1
-- 创建存储过程 if语句
create PROCEDURE P_test()
BEGIN
DECLARE he,name1 VARCHAR(500) DEFAULT ''; -- DEFAULT '' 很关键否则下面 he=CONCAT(he,name1); 就没办法赋值
DECLARE no_more_record,sex1 Integer DEFAULT '' ;
DECLARE cur1 CURSOR for SELECT name,sex FROM abcd;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
-- declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
SET no_more_record=0;

OPEN cur1;
fetch cur1 INTO name1,sex1;
-- set he=""; -- 这是必须的，否则he为null 或 DECLARE he,name1 VARCHAR(500) DEFAULT '';
WHILE (no_more_record !=1 ) DO
if (sex1=2) THEN SET sex1='女';
else SET sex1='男';
END IF;
set name1 =CONCAT(name1 ,";123");
set he=CONCAT(he,name1);-- CONCAT(he,name1);
-- set he=CONCAT(he ,sex1) ;
fetch cur1 INTO name1,sex1;
-- SELECT name1,sex1;
END WHILE;
CLOSE cur1;
select he;
END
-- 例2
SELECT * FROM abcd;

drop PROCEDURE IF EXISTS P_test;

create PROCEDURE P_test()
BEGIN
DECLARE he,name1 VARCHAR(500) DEFAULT ''; -- DEFAULT '' 很关键否则下面 he=CONCAT(he,name1); 就没办法赋值
DECLARE no_more_record,sex1 Integer DEFAULT '' ;
DECLARE cur1 CURSOR for SELECT name,sex FROM abcd;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
-- declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
SET no_more_record=0;
SET @chun="";

OPEN cur1;
WHILE (no_more_record !=1 ) DO
fetch cur1 INTO name1,sex1;
if (no_more_record !=1 ) THEN
set he=@chun;
SET he=CONCAT(he,name1 , ";" , sex1); -- concat 变量中不能用@变量名
set @chun=he;
END IF;
END WHILE;
CLOSE cur1;
select @chun;
END

-- 例3
SELECT * FROM abcd;

drop PROCEDURE IF EXISTS P_test;

create PROCEDURE P_test()
BEGIN
-- 显示变量值是乱码，则定义变量的时候设置变量的编码，如：DECLARE he,name1,sex2 VARCHAR(500) character set utf8 DEFAULT '';
DECLARE he,name1,sex2 VARCHAR(500) character set utf8 DEFAULT ''; -- DEFAULT '' 很关键否则下面 he=CONCAT(he,name1); 就没办法赋值，default要放在最后面
DECLARE no_more_record,sex1 Integer DEFAULT '' ;
DECLARE cur1 CURSOR for SELECT name,sex FROM abcd;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
-- declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
SET no_more_record=0;

OPEN cur1;
WHILE (no_more_record !=1 ) DO
fetch cur1 INTO name1,sex1;
if (no_more_record !=1 ) THEN
if (sex1=2) THEN SET sex2='女';
else SET sex2='男';
END IF;
SET he=CONCAT(he,name1 , ";" , sex2); -- concat 变量中不能用@变量名
END IF;
END WHILE;
CLOSE cur1;
select he;
END

call P_test1()
drop PROCEDURE IF EXISTS P_updageSex;
-- 例4
CREATE PROCEDURE P_updageSex()
BEGIN
DECLARE name1 VARCHAR(10) CHARACTER SET utf8 DEFAULT '';
DECLARE sex1,no_more_record int(1);
DECLARE cur1 CURSOR FOR SELECT name,sex from abcd;
DECLARE CONTINUE HANDLER for NOT found set no_more_record=1;
set no_more_record=0;
OPEN cur1;
WHILE (no_more_record !=1) DO
FETCH cur1 INTO name1,sex1;
if(no_more_record !=1) THEN
IF (sex1=2) THEN
UPDATE abcd set sex=0 where sex=sex1;
END if;
END if;
end WHILE;
END
select * from abcd
-- 调用存储过程
CALL P_updageSex()
-- -----------------------------------------------------------------------创建视图------------------------------------------------------------------------------------------------
-- 创建视图的理由：1.安全性。2.查询性能提高。3.有灵活性的功能需求。4.复杂的查询需求。

-- 视图(ALGORITHM 是是图标的操作方式，MERGE是合并，select * from（select name,sex FROM abcd），TEMPTABLE临时表是将as后面的select语句查询出来并生成一个临时表，其他在这临时表中操作，UNDEFINED（默认）是MERGE)
-- 语法CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
-- REPLACE VIEW V_test(name) as SELECT name from abcd 替换现有的视图
create VIEW V_test(name) as SELECT name from abcd
select * from V_test
select * from abcd where name='???'
update V_test SET name='何春华' where name='???'
-- 注意
/*（1）运行创建视图的语句需要用户具有创建视图（crate view）的权限，若加了[or replace]时，还需要用户具有删除视图（drop view）的权限；
（2） select语句不能包含from子句中的子查询；
（3） select语句不能引用系统或用户变量；
（4） select语句不能引用预处理语句参数；
（5）在存储子程序内，定义不能引用子程序参数或局部变量；
（6）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用check table语句；
（7）在定义中不能引用temporary表，不能创建temporary视图；
（8）在视图定义中命名的表必须已存在；
（9）不能将触发程序与视图关联在一起；
（10）在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。
因为视图也是一种表，是虚拟表。不能与已有的表(视图)出现重名
*/
-- 修改表
-- ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。
ALTER VIEW V_test as select name,sex from abcd

-- 删除视图
DROP VIEW IF EXISTS V_test
/*
Eg1. 删除在前面的小节中创建的视图purchase_detail：DROP VIEW purchase_detail;
Eg2. 删除一个未知的视图：DROP VIEW IF EXISTS test_view;
Eg3. 删除多个视图：DROP VIEW IF EXISTS test_view1, test_view2;
必须对要删除的一个或多个视图拥有DROP VIEW的权限。
*/

-- -----------------------------------------------------------------------定时器------------------------------------------------------------------------------------------------
#要使定时起作用 MySQL的常量GLOBAL event_scheduler必须为on或者是1
-- 查看是否开启定时器
SHOW VARIABLES LIKE '%sche%';
-- 设置全局变量
SET @@global.event_scheduler = 1;
SET global event_scheduler = 0;
SELECT now();
SELECT * from abcd;
alter table abcd add datetime1 timestamp not null default current_timestamp
create PROCEDURE P_event()
BEGIN
INSERT INTO abcd(name,sex) VALUES ('hechunhua',1);
END

call P_event()
-- 创建定时器
/*CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ''comment'']
DO sql_statement;
schedule:
AT timestamp [+ INTERVAL interval]
| EVERY interval
[STARTS timestamp [+ INTERVAL interval]]
[ENDS timestamp [+ INTERVAL interval]]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}*/
DELIMITER $$
CREATE EVENT IF NOT EXISTS E_event
on SCHEDULE EVERY 10 SECOND STARTS now() -- SCHEDULE 日程
ON COMPLETION PRESERVE DISABLE COMMENT '测试定时器注释' -- disabled 设置不开启定时器，默认值是开启，on 直到完成， PRESERVE 是否保护定时器，DISABLE、enable 是否开启，COMMENT 定时器注释说明
DO
BEGIN
call P_event();
END $$ -- 不加 DELIMITER 无法运行
/*注：
假如在定义event的时候有指定ends,那么是否当到期的时候,
当为on completion preserve 的时候,当event到期了,event会被disable,但是该event还是会存在
当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉.*/
DELIMITER ;
-- 修改定时器
/*ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT ''comment'']
[ENABLE | DISABLE]
[DO sql_statement]*/
DELIMITER $$ -- 定义结束符，默认的是;视图中就不需要这样定义
alter EVENT E_event -- 不可以alter EVENT IF EXISTS E_event这样定义
on SCHEDULE EVERY 10 SECOND STARTS now()
ON COMPLETION NOT PRESERVE DISABLE COMMENT '测试定时器注释' -- disabled 设置不开启定时器，默认值是开启
DO
BEGIN
call P_event();
END $$ -- 不加 DELIMITER 无法运行
DELIMITER ; -- 还原结束符；

CREATE EVENT `user_gift_operation`
ON SCHEDULE EVERY 1 DAY STARTS '2012-06-05 12:50:00'
ON COMPLETION PRESERVE ENABLE DO CALL USER_GIFT_OPERATION(30)
-- 删除定时器
drop EVENT IF EXISTS E_event

-- 开启定时器
ALTER EVENT E_event ON
COMPLETION PRESERVE ENABLE;

ALTER EVENT E_event ENABLE;

delete from abcd
-- 关闭定时器
alter EVENT E_event
ON COMPLETION PRESERVE DISABLE;

ALTER EVENT E_event DISABLE;
-- -----------------------------------------------------------------------自定义函数------------------------------------------------------------------------------------------------
-- 创建函数
-- DELIMITER $$
CREATE FUNCTION F_test(s int)
RETURNS INT
BEGIN
RETURN s+s;
END -- $$
-- DELIMITER ;

-- 使用函数
SELECT F_test(5) as sum

-- 删除函数
drop FUNCTION IF EXISTS F_test

SELECT * from abcdef UNION ALL SELECT * FROM abcd

CREATE TABLE abcdef SELECT * FROM abcd

-- -----------------------------------------------------------------------事务------------------------------------------------------------------------------------------------
/*
ACID:Atomic（原子性）、Consistent（一致性）、Isolated（独立性）、Durable （持久性）

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：
sql代码
1. MyISAM：不支持事务，用于只读程序提高性能
2. InnoDB：支持ACID事务、行级锁、并发
3. Berkeley DB：支持事务

事务隔离级别标准：
ANSI（美国国家标准学会）标准定义了4个隔离级别，MySQL的InnoDB都支持：
sql代码
1. READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirty read可能不是我们想要的
2. READ COMMITTED：在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见
3. REPEATABLE READ：在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select的结果一样，除非本事务中update数据库。
4. SERIALIZABLE：*别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。 Mysql的默认隔离级别是：REPEATABLE READREAD UNCOMMITTED级别会导致数据完整性的严重问题，需要自己控制如何保持数据完整性SERIALIZABLE会导致性能问题并增加死锁的机率
Mysql事务操作语句：
1. START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
2. COMMIT：提交事务，保存更改，释放锁
3. ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁
4. SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
5. ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
6. SET TRANSACTION：允许设置事务的隔离级别
7. LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。我们一般所以一般在事务代码里不会使用LOCK TABLES
定义事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。
在复杂的应用场景下这种方式就不能满足需求了。
为了打开事务，允许在COMMIT和ROLLBACK之前多条语句被执行，我们需要做以下两步：
1, 设置MySQL的autocommit属性为0，默认为1
2，使用START TRANSACTION语句显式的打开一个事务上面已经说了，当使用START TRANSACTION开始一个事物的时候，则SET autocommit=0不会起作用，因为START TRANSACTION会隐式的提交session中所有当前的更改，结束已有的事务，并打开一个新的事务。
通常COMMIT或ROLLBACK语句执行时才完成一个事务，但是有些DDL语句等会隐式触发COMMIT，所以应该在事务中尽可能少用或注意一下：

SAVEPOINT语句用于设置一个事务保存点，带一个标识符名称。如果当前事务有一个同样名称的保存点，则旧的保存点被删除，新的保存点被设置。

注：加入事务中运行了commit的话，事务就无效了，就不能回滚了，回滚是在commit之前才执行的
DML(Data Manipulation Language)数据操纵语言命令使用户能够查询数据库以及操作已有数据库中的数据。
如insert,delete,update,select等都是DML.
DDL语句用语定义和管理数据库中的对象，如Create,Alter和Drop.
DDL操作是隐性提交的！不能rollback

*/
set autocommit=0; -- 这和commit 与rollback是互斥事件，要么执行commit要么就执行回滚
START TRANSACTION;ROLLBACK;-- 这和ROLLBACK有关；
SAVEPOINT savepoint_1; ROLLBACK TO SAVEPOINT savepoint_1-- 这和ROLLBACK有关；
/*
默认情况下，MySQL采用autocommit模式运行。这意味着，当您执行一个用于更新（修改）表的语句之后，MySQL立刻把更新存储到磁盘中。
如果您正在使用一个事务安全型的存储引擎（如InnoDB, BDB或NDB簇），则您可以使用以下语句禁用autocommit模式：
SET AUTOCOMMIT=0;
通过把AUTOCOMMIT变量设置为零，禁用autocommit模式之后，您必须使用COMMIT把变更存储到磁盘中，或着如果您想要忽略从事务开始进行以来做出的变更，使用ROLLBACK。
如果您想要对于一个单一系列的语句禁用autocommit模式，则您可以使用START TRANSACTION语句
使用START TRANSACTION，autocommit仍然被禁用，直到您使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态。
BEGIN和BEGIN WORK被作为START TRANSACTION的别名受到支持，用于对事务进行初始化。START TRANSACTION是标准的SQL语法，并且是启动一个ad-hoc事务的推荐方法。BEGIN语句与BEGIN关键词的使用不同。BEGIN关键词可以启动一个BEGIN...END复合语句。后者不会开始一项事务。
开始一项事务会造成一个隐含的UNLOCK TABLES被执行。
START TRANSACTION WITH CONSISTENT SNAPSHOT;
WITH CONSISTENT SNAPSHOT子句用于启动一个一致的读取，用于具有此类功能的存储引擎。目前，该子句只适用于InnoDB。该子句的效果与发布一个START TRANSACTION，后面跟一个来自任何InnoDB表的SELECT的效果一样。请参见15.2.10.4节，“一致的非锁定读”。
开始一项事务会造成一个隐含的UNLOCK TABLES被执行。
为了获得最好的结果，事务应只使用由单一事务存储引擎管理的表执行。否则，会出现以下问题：
如果您使用的表来自多个事务安全型存储引擎（例如InnoDB和BDB），并且事务隔离等级不是SERIALIZABLE，则有可能当一个事务提交时，其它正在进行中的、使用同样的表的事务将只会发生由第一个事务产生的变更。也就是，用混合引擎不能保证事务的原子性，并会造成不一致。（如果混合引擎事务不经常有，则您可以根据需要使用SET TRANSACTION ISOLATION LEVEL把隔离等级设置到SERIALIZABLE。）
如果您在事务中使用非事务安全型表，则对这些表的任何变更被立刻存储，不论autocommit模式的状态如何。
如果您在更新了事务中一个事务表之后，发布一个ROLLBACK语句，则会出现一个ER_WARNING_NOT_COMPLETE_ROLLBACK警告。对事务安全型表的变更被回滚，但是对非事务安全型表没有变更。
每个事务被存储在一个组块中的二进制日志中，在COMMIT之上。被回滚的事务不被计入日志。（例外情况：对非事务表的更改不会被回滚。如果一个被回滚的事务包括对非事务表的更改，则整个事务使用一个在末端的ROLLBACK语句计入日志，以确保对这些表的更改进行复制。）
您可以使用SET TRANSACTION ISOLATION LEVEL更改事务的隔离等级。
回滚可以慢速运行。在用户没有明确要求时，也可以进行回滚（例如，当错误发生时）。因此，在明确地和隐含的（ROLLBACK SQL命令）回滚时，SHOW PROCESSLIST会在Stage列中显示Rolling back，用于连接。

*/
COMMIT; -- autocommit=0;并未执行commit的的语句都可以回滚的。一个事物一个commit

show variables like '%autocommit%'; -- 每条语句后是否自动提交；因为每条语句后都自动执行commit();
-- 关掉自动提交
set autocommit=1;
INSERT INTO abcd VALUES (null,'hebenhua111',1,null);
select * from abcd

SAVEPOINT pointposition;
ROLLBACK TO SAVEPOINT pointposition;

SELECT * FROM abcd

DELETE FROM abcd where id>63;
-- 事务实例1 SAVEPOINT保留点的学习
DELIMITER $$
DROP PROCEDURE IF EXISTS `openshop_version1`.`P_test1`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_test1`()
BEGIN
DECLARE X2 INT;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET X2= 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET X2 = 2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET X2= 3;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET X2 = 4;
START TRANSACTION;-- START TRANSACTION或BEGIN语句可以开始一项新的事务。
-- mysql_query('SET autocommit=0');
SAVEPOINT savepoint_1;
BEGIN
INSERT INTO abcd VALUES (null,'her',0,null);
INSERT INTO abcd VALUES (null,'heronghua',1,null);
SELECT X2;
end;
ROLLBACK TO SAVEPOINT savepoint_1;
INSERT INTO abcd VALUES (null,'heronghua112',2,null);
END$$
DELIMITER ;

-- 事务实例2
DELIMITER $$
DROP PROCEDURE IF EXISTS `openshop_version1`.`P_test2` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_test2`()
BEGIN
DECLARE X2 INT;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET X2= 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET X2 = 2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET X2= 3;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET X2 = 4;
-- START TRANSACTION;
set autocommit=0; -- 这和commit 有关，相当于START TRANSACTION;autocommit这是用于当前连接的设置而START TRANSACTION;是临时设置。
INSERT INTO abcd VALUES (null,'her',0,null);
INSERT INTO abcd VALUES (null,'heronghua',1,null);
SELECT X2;
ROLLBACK;
INSERT INTO abcd VALUES (null,'heronghua112',2,null);
COMMIT; -- autocommit=0;并未执行commit的的语句都可以回滚的。
END $$
DELIMITER ;

call `P_test2`();
DELETE FROM abcd where id>63;
SELECT * FROM abcd
show create TABLE abcd

select * from abcd
DELIMITER $$
DROP PROCEDURE IF EXISTS `openshop_version1`.`P_test4` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_test4`()
BEGIN
DECLARE num INT;
set num=(select count(0) from abcd where name like '%112' AND datetime1='2012-09-26 09:29:01');
SELECT num;
END $$
DELIMITER ;
call P_test4();
-- -----------------------------------------------------------------------事务和锁------------------------------------------------------------------------------------------------
/*
事务的ACID属性只能通过限制数据库的同步更改来实现，通过对数据加锁来实现。
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
这样做的缺点是后面的事务必须等前面的事务完成才能开始执行，吞吐量随着等待锁释放的时间增长而递减。
Mysql的innodb通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制，而且读数据可以始终没有等待。
可以在SELECT语句里使用FOR UPDATE或LOCK IN SHARE MODE语句来加上行级锁
1. SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE]
FOR UPDATE会锁住该SELECT语句返回的行，其他SELECT和DML语句必须等待该SELECT语句所在的事务完成
LOCK IN SHARE MODE同FOR UPDATE，但是允许其他session的SELECT语句执行并允许获取SHARE MODE锁
下面了解一下死锁，悲观锁，乐观锁，但是不深入掌握，当前只掌握概念

死锁发生于两个事务相互等待彼此释放锁的情景
悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新
事务设计指南
1，保持事务短小
2，尽量避免事务中rollback
3，尽量避免savepoint
4，默认情况下，依赖于悲观锁
5，为吞吐量要求苛刻的事务考虑乐观锁
6，显示声明打开事务
7，锁的行越少越好，锁的时间越短越好

2. MySQL的InnoDB引擎中事物与锁

2.1 SELECT …… LOCK IN SHARE MODE

会话事务中查找的数据，加上一个共享锁。若会话事务中查找的数据已经被其他会话事务加上独占锁的话，共享锁会等待其结束再加，若等待时间过长就会显示事务需要的锁等待超时。

2.2 SELECT ….. FOR UPDATE

会话事务中查找的数据，加上一个读更新琐，其他会话事务将无法再加其他锁，必须等待其结束。

2.3 INSERT、UPDATE、DELETE

会话事务会对DML语句操作的数据加上一个独占锁，其他会话的事务都将会等待其释放独占锁。

2.4 gap and next key lock（间隙锁）

InnoDB引擎会自动给会话事务中的共享锁、更新琐以及独占锁，需要加到一个区间值域的时候，再加上个间隙锁（或称范围锁），对不存在的数据也锁住，防止出现幻写。

3.mysql行锁和表锁
在调用存储过程中，就会涉及到表锁，行锁这一概念：所谓区别：有索引的时候就是行锁，没有索引的时候就是表索。
innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.
3.1mysql中使用select for update的必须针对InnoDb，并且是在一个事务中，才能起作用。
3.2select的条件不一样，采用的是行级锁还是表级锁也不一样。
3.3由於 InnoDB 預設是 Row-Level Lock，所以只有「明確」的指定主鍵，MySQL 才會執行 Row lock (只鎖住被選取的資料例) ，否則 MySQL 將會執行 Table Lock (將整個資料表單給鎖住)
3.4FOR UPDATE 僅適用於 InnoDB，且必須在交易區塊(BEGIN/COMMIT)中才能生效
3.5要測試鎖定的狀況，可以利用 MySQL 的 Command Mode ，開二個視窗來做測試。

*/
-- 例1: (明確指定主鍵，並且有此筆資料，row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
-- 例2: (明確指定主鍵，若查無此筆資料，無 lock)
SELECT * FROM products WHERE id='-1' FOR UPDATE;
-- 例2: (無主鍵，table lock)
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
-- 例3: (主鍵不明確，table lock)
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
-- 例4: (主鍵不明確，table lock)
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
-- -----------------------------------------------------------------------异常处理------------------------------------------------------------------------------------------------
/*
DECLARE {EXIT|CONTINUE} HANDLER FOR {error_number|{SQLSTATE error-string}|condition} SQL Statement
1．定义条件
DECLARE condition_name CONDITION FOR condition_value
condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code
其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。
【示例14-6】下面定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find。可以用两种不同的方法来定义，代码如下：
//方法一：使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ;
//方法二：使用mysql_error_code
DECLARE can_not_find CONDITION FOR 1146 ;
【示例14-6】下面定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find。可以用两种不同的方法来定义，代码如下：
2．定义处理程序
MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：
DECLARE handler_type HANDLER FOR
condition_value[,...] sp_statement 其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。
注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。
handler_type: CONTINUE | EXIT | UNDO
condition_value: SQLSTATE [VALUE] sqlstate_value |condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code
condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。
【示例14-7】下面是定义处理程序的几种方式。代码如下：
//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND';
//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';
//方法三：先定义条件，然后调用
DECLARE can_not_find CONDITION FOR 1146 ;
DECLARE CONTINUE HANDLER FOR can_not_find SET
@info='CAN NOT FIND';
//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';
//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
上述代码是6种定义处理程序的方法。第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息。第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。

*/
DECLARE CONTINUE HANDLER FOR SQLWARNING SET X2= 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET X2 = 2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET X2= 3;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET X2 = 4;

-- -----------------------------------------------------------------------外键------------------------------------------------------------------------------------------------
/*
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中的列的值!
a表中存有客户号，客户名称
　　b表中存有每个客户的订单
　　有了外键后
　　你只能在确信b 表中没有客户x的订单后，才可以在a表中删除客户x
　　建立外键的前提：本表的列必须与外键类型相同(外键必须是外表主键)。
事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值),set Default(设默认值),[默认]no action
*/

drop TABLE IF EXISTS abcdef;
create table abcdef(
id int not null auto_increment PRIMARY KEY COMMENT '主键',
name1 char(20) COMMENT '姓名',
sex int COMMENT '性别',
date timestamp DEFAULT CURRENT_TIMESTAMP,-- 或者将current_timestamp改为now()： timestamp not null DEFAULT NOW(),-- TIMESTAMP和now()值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符(包括空格)。如果想要获得数字值，应在TIMESTAMP 列添加+0。
abcd_id INT(10),
foreign key(abcd_id) references abcd(id) on delete cascade on update cascade
)ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT * from abcdef

INSERT INTO abcdef VALUES(null,'hechunhua',1, null,60)
INSERT INTO abcdef(name1,sex,abcd_id) VALUES('hechunhua',1,60)
UPDATE abcdef SET sex=0 WHERE sex=1;
show create table abcd
show create table abcdef
-- -----------------------------------------------------------------------时间------------------------------------------------------------------------------------------------
-- TIMESTAMP值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符(包括空格)。如果想要获得数字值，应在TIMESTAMP 列添加+0。
#timestamp跟系统时区有关，但要重启mysql服务器.看来mysql在启动的时候就已经把系统时区读过去了，改变系统时区后，需要重启数据库才能更新mysql时区（这可是个麻烦事，一旦你的业务扩展到多个国家和地区，时区就会成为一个问题，经过测试datetime也是如此）。
SELECT current_timestamp+0;
select NOW()+0;
SELECT date+0 from abcdef;
-- 我们直接用mysql命令更改一下mysql的时区((UTC+04:00)巴库)：
set time_zone= '+8:00';
/*
1，TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
在创建新记录和修改现有记录的时候都对这个数据列刷新

2，TIMESTAMP DEFAULT CURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它

3，TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为0，以后修改时刷新它

4，TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss’ ON UPDATE CURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它

注：默认设置（date TIMESTAMP）；TIMESTAMP 列类型提供了一种类型，通过它你可以以当前操作的日期和时间自动地标记 Insert或Update 操作。如果一张表中有多个 TIMESTAMP 列，只有第一个被自动更新。
drop TABLE IF EXISTS abcdef;

*/
-- 实例1
create table abcdef(
id int not null auto_increment PRIMARY KEY COMMENT '主键',
name1 char(20) COMMENT '姓名',
sex int not null DEFAULT '1' COMMENT '性别',
date timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,-- 或者将current_timestamp改为now()： timestamp not null DEFAULT NOW(),-- TIMESTAMP和now()值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符(包括空格)。如果想要获得数字值，应在TIMESTAMP 列添加+0。
abcd_id INT(10),
foreign key(abcd_id) references abcd(id) on delete cascade on update cascade
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 实例2
create table abcdef(
id int not null auto_increment PRIMARY KEY COMMENT '主键',
name1 char(20) COMMENT '姓名',
sex int COMMENT '性别',
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,-- 或者将current_timestamp改为now()： timestamp not null DEFAULT NOW(),-- TIMESTAMP和now()值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符(包括空格)。如果想要获得数字值，应在TIMESTAMP 列添加+0。
abcd_id INT(10),
foreign key(abcd_id) references abcd(id) on delete cascade on update cascade
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 实例3
create table abcdef(
id int not null auto_increment PRIMARY KEY COMMENT '主键',
name1 char(20) COMMENT '姓名',
sex int COMMENT '性别',
date TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,-- 或者将current_timestamp改为now()： timestamp not null DEFAULT NOW(),-- TIMESTAMP和now()值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符(包括空格)。如果想要获得数字值，应在TIMESTAMP 列添加+0。
abcd_id INT(10),
foreign key(abcd_id) references abcd(id) on delete cascade on update cascade
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 实例4
create table abcdef(
id int not null auto_increment PRIMARY KEY COMMENT '主键',
name1 char(20) COMMENT '姓名',
sex int COMMENT '性别',
date TIMESTAMP ,-- 或者将current_timestamp改为now()： timestamp not null DEFAULT NOW(),-- TIMESTAMP和now()值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符(包括空格)。如果想要获得数字值，应在TIMESTAMP 列添加+0。
abcd_id INT(10),
foreign key(abcd_id) references abcd(id) on delete cascade on update cascade
)ENGINE=INNODB DEFAULT CHARSET=utf8;

drop TABLE IF EXISTS abcdef;
INSERT INTO abcdef VALUES(null,'hechunhua','', null,60);
INSERT INTO abcdef(name1,sex,abcd_id) VALUES('hechunhua',1,60);
INSERT INTO abcdef(name1,abcd_id) VALUES('hechunhua',60);
UPDATE abcdef SET sex=0 WHERE sex=1;
select * from abcdef;

EXIT;

相关推荐