字段类型

int(5) 和 int(10) 的区别

可点击博客 程序猿杂货铺
int(5) 与int(11)后的括号中的字符表示显示宽度，整数列的显示宽度与 MySQL 需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，
int类型的字段能存储的数据上限依旧是2147483647(有符号型)和4294967295(无符号型)。

char 和 varchar 的区别

可点击博客 A_aliane
1.两者存储大小不同：char 258 varchar65535
2.char为定长，varchar变长,char效率更高，时间换空间
3.char保留两边空格，varchar会去掉空格

例子
人的年龄适合使用tinyint类型 乌龟的年龄使用smallint类型
数据表主键id值在没有超过1600万的时候，就可以使用mediumint类型
手机号码存储：char(11)
存储邮箱：速度快char(40) 、 空间节省 varchar(40)
集合:set(‘篮球’,’排球’,’足球’,’棒球’)
枚举:enum(‘男’,’女’,’保密’)

存储引擎

概念：数据表存储数据的一种格式。
常见存储引擎的区别 可点击博客 m2ez

MyISAM 与 InnoDB

属性/存储	MyISAM	InnoDB
存储结构	每张表被存放在三个文件：frm-格定义 MYD(MYData)-数据文件 MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
存储方式	不排顺序 ，按写入存储	数据的写入顺序 与 存储的顺序不一致，按照主键的顺序把记录摆放到对应的位置上去，速度比Myisam的要稍慢。
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
事务安全	不支持 每次查询具有原子性	支持 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表
AUTO_INCREMEN	MyISAM表可以和其他字段一起建立联合索引	InnoDB中必须包含只有该字段的索引
SELECT	MyISAM查询更优
INSERT		InnoDB更优
UPDATE		InnoDB更优
DELETE		InnoDB更优 它不会重新建立表，而是一行一行的删除
COUNT without WHERE	MyISAM更优。因为MyISAM保存了表的具体行数	InnoDB没有保存表的具体行数，需要逐行扫描统计，就很慢了
COUNT with WHERE	一样	一样，InnoDB也会锁表
锁	只支持表锁/并发性低	支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的
外键	不支持	支持
FULLTEXT全文索引	支持	不支持（5.6.4以上支持英文全文索引） 可以通过使用Sphinx从InnoDB中获得全文索引，会慢一点

锁

悲观锁 与乐观锁

可点击博客 jopen

	悲观锁	乐观锁
概念	正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）	乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重（H.T.Kung）教授提出
MySQL InnoDB中使用	四步 0. 开始事务begin;/begin work;/start transaction; (三者选一就可以) 1.查询出商品信息select status from t_goods where id=1 for update; 2.根据商品信息生成订单insert into t_orders (id,goods_id) values(null,1); 3.修改商品status为2update t_goods set status=2; 4.提交事务commit;/commit work;	三步 1.查询出商品信息select (status,status,version) from t_goods where id=#{id} 2.根据商品信息生成订单 3.修改商品status为2update t_goods set status=2,version=version+1where id=#{id} and version=#{version};
优点与不足	悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数	乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

触发器

触发程序是与表有关的命名数据库对象，当该表出现特定事件时，将激活该对象。\
监听：记录的增加、修改、删除。

创建触发器

• CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 参数： trigger_time是触发程序的动作时间。它可以是 before 或 after，以指明触发程序是在激活它的语句之前或之后触发。 trigger_event指明了激活触发程序的语句的类型
• INSERT：将新行插入表时激活触发程序
• UPDATE：更改某一行时激活触发程序
• DELETE：从表中删除某一行时激活触发程序
• tbl_name：监听的表，必须是永久性的表，不能将触发程序与TEMPORARY表或视图关联起来。
• trigger_stmt：当触发程序激活时执行的语句。执行多个语句，可使用BEGIN...END复合语句结构

删除

• DROP TRIGGER [schema_name.]trigger_name
• 可以使用old和new代替旧的和新的数据
• 更新操作，更新前是old，更新后是new.
• 删除操作，只有old.
• 增加操作，只有new.

注意

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。 字符连接函数 concat(str1,str2,...]) concat_ws(separator,str1,str2,...)

语法

 分支语句
if 条件 then
执行语句
elseif 条件 then
执行语句
else
执行语句
end if;

 修改最外层语句结束符
delimiter 自定义结束符号
SQL语句
自定义结束符号
delimiter ;      修改回原来的分号

 语句块包裹
begin
语句块
end

特殊的执行

1. 只要添加记录，就会触发程序。
2. Insert into on duplicate key update 语法会触发：
   如果没有重复记录，会触发 before insert, after insert;
   如果有重复记录并更新，会触发 before insert, before update, after update;
   如果有重复记录但是没有发生更新，则触发 before insert, before update
3. Replace 语法 如果有记录，则执行 before insert, before delete, after delete, after insert

SQL编程

变量

变量声明
declare var_name[,...] type [default value]
这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个default子句。值可以被指定为一个表达式，不需要为一个常数。如果没有default子句，初始值为null。
 赋值
使用 set 和 select into 语句为变量赋值。
- 注意：在函数内是可以使用全局变量（用户自定义的变量）

// 全局变量 
 定义、赋值
set 语句可以定义并为变量赋值。
set @var = value;
也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行，但是可以是多个字段，就意味着同时为多个变量进行赋值，变量的数量需要与查询的列数一致。
还可以把赋值语句看作一个表达式，通过select执行完成。此时为了避免=被当作关系运算符看待，使用:=代替。（set语句可以使用= 和 :=）。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into 可以将表中查询获得的数据赋给变量。
-| select max(height) into @max_height from tb;

 自定义变量名
为了避免select语句中，用户自定义的变量与系统标识符（通常是字段名）冲突，用户自定义变量在变量名前使用@作为开始符号。
@var=10;
- 变量被定义后，在整个会话周期都有效（登录到退出）

控制结构

if语句
if search_condition then
statement_list
[elseif search_condition then
statement_list]
...
[else
statement_list]
end if;
 case语句
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
 while循环
[begin_label:] while search_condition do
statement_list
end while [end_label];
- 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。
 退出循环
退出整个循环 leave
退出当前循环 iterate
通过退出的标签决定退出哪个循环

函数

 数值函数
abs(x)           绝对值 abs(-10.9) = 10
format(x, d)     格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x)          向上取整 ceil(10.1) = 11
floor(x)         向下取整 floor (10.1) = 10
round(x)         四舍五入去整
mod(m, n)        m%n m mod n 求余 10%3=1
pi()             获得圆周率
pow(m, n)        m^n
sqrt(x)          算术平方根
rand()           随机数
truncate(x, d)   截取d位小数

 时间日期函数
now(), current_timestamp();      当前日期时间
current_date();                  当前日期
current_time();                  当前时间
date('yyyy-mm-dd hh:ii:ss');     获取日期部分
time('yyyy-mm-dd hh:ii:ss');     获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');  格式化时间
unix_timestamp();                获得unix时间戳
from_unixtime();                 从时间戳获得时间

 字符串函数
length(string)           string长度，字节
char_length(string)      string的字符个数
substring(str, position [,length])       从str的position开始,取length个字符
replace(str ,search_str ,replace_str)    在str中用replace_str替换search_str
instr(string ,substring)     返回substring首次在string中出现的位置
concat(string [,...])    连接字串
charset(str)             返回字串字符集
lcase(string)            转换成小写
left(string, length)     从string2中的左边起取length个字符
load_file(file_name)     从文件读取内容
locate(substring, string [,start_position])  同instr,但可指定开始位置
lpad(string, length, pad)    重复用pad加在string开头,直到字串长度为length
ltrim(string)            去除前端空格
repeat(string, count)    重复count次
rpad(string, length, pad)   在str后用pad补充,直到长度为length
rtrim(string)            去除后端空格
strcmp(string1 ,string2)     逐字符比较两字串大小

 流程函数
case when [condition] then result [when [condition] then result ...] [else result] end   多分支
if(expr1,expr2,expr3)  双分支。

 聚合函数
count()
sum();
max();
min();
avg();
group_concat()；

其他常用函数
md5();
default();

存储函数，自定义函数

新建
CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型
函数体
- 函数名，应该合法的标识符，并且不应该与已有的关键字冲突。
- 一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。
- 参数部分，由"参数名"和"参数类型"组成。多个参数用逗号隔开。
- 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。
- 多条语句应该使用 begin...end 语句块包含。
- 一定要有 return 返回值语句。

 删除
DROP FUNCTION [IF EXISTS] function_name;

 查看
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name;

 修改
ALTER FUNCTION function_name 函数选项

存储过程

存储存储过程 是一段代码（过程），存储在数据库中的sql组成。 一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。 而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。
实例 可点击博客 Crazygolf

创建
CREATE PROCEDURE sp_name (参数列表)
过程体
参数列表：不同于函数的参数列表，需要指明参数类型
IN，表示输入型
OUT，表示输出型
INOUT，表示混合型
注意，没有返回值。

/* 存储过程 */ 
存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。
调用：CALL 过程名
 注意
- 没有返回值。
- 只能单独调用，不可夹杂在其他语句中

 参数
IN|OUT|INOUT 参数名 数据类型
IN      输入：在调用过程中，将数据输入到过程体内部的参数
OUT     输出：在调用过程中，将过程体处理完的结果返回到客户端
INOUT   输入输出：既可输入，也可输出

 语法
CREATE PROCEDURE 过程名 (参数列表)
BEGIN
过程体
END

各位帅气的博主

• 程序猿杂货铺
• A_aliane
• m2ez
• Crazygolf