Ø function 函数
函数的作用比较大，一般多用在select查询语句和where条件语句之后。按照函数返回的结果，
可以分为：多行函数和单行函数；所谓的单行函数就是将每条数据进行独立的计算，然后每条数据得到一条结果。
如：字符串函数；而多行函数，就是多条记录同时计算，得到最终只有一条结果记录。如：
sum
、avg等
多行函数也称为聚集函数、分组函数，主要用于完成一些统计功能。MySQL的单行函数有如下特征：
单行函数的参数可以是变量、常量或数据列。单行函数可以接受多个参数，但返回一个值。
单行函数就是它会对每一行单独起作用，每一行（可能包含多个参数）返回一个结果。
单行函数可以改变参数的数据类型。单行函数支持嵌套使用：内层函数的返回值是外层函数的参数。
单行函数可以分为：
类型转换函数；
位函数；
流程控制语句；
加密解密函数；
信息函数
单行函数
1、 char_length字符长度
select
char_length
(tel)
from
user
;
2、 sin函数
select
sin(age)
from
user
;
select
sin(1.57);
3、 添加日期函数
select
date_add(
'2010-06-21'
,
interval
2
month
);
interval是一个关键字，2 month是2个月的意思，2是数值，month是单位
select
addDate(
'2011-05-28'
, 2);
在前面的日期上加上后面的天数
4、 获取当前系统时间、日期
select
curdate();
select
curtime();
5、 加密函数
select
md5(
'zhangsan'
);
6、
Null
处理函数
select
ifnull(birthday,
'is null birthday'
)
from
user
;
如果birthday为null，就返回后面的字符串
select
nullif
(age, 245)
from
user
;
如果age等于245就返回null，不等就返回age
select
isnull(birthday)
from
user
;
判断birthday是否为null
select
if
(isnull(birthday),
'birthday is null'
,
'birthday not is null'
)
from
user
;
如果birthday为null或是0就返回birthday
is
null
，否则就返回birthday
not
is
null
；类似于三目运算符
7、
case
流程函数
case函数是一个流程控制函数，可以接受多个参数，但最终只会返回一个结果。
select
name,
age,
(
case
sex
when
1
then
'男'
when
0
then
'女'
else
'火星人'
end
) sex
from
user
;
组函数
组函数就是多行函数，组函数是完成一行或多行结果集的运算，最后返回一个结果，而不是每条记录返回一个结果。
1、 avg平均值运算
select
avg
(age)
from
user
;
select
avg
(
distinct
age)
from
user
;
2、
count
记录条数统计
select
count
(*),
count
(age),
count
(
distinct
age)
from
user
;
3、
max
最大值
select
max
(age),
max
(
distinct
age)
from
user
;
4、
min
最小值
select
min
(age),
min
(
distinct
age)
from
user
;
5、
sum
求和、聚和
select
sum
(age),
sum
(
distinct
age)
from
user
;
select
sum
(ifnull(age, 0))
from
user
;
6、
group
by
分组
select
count
(*), sex
from
user
group
by
sex;
select
count
(*)
from
user
group
by
age;
select
*
from
user
group
by
sex, age;
7、 having进行条件过滤
不能在where子句中过滤组，where子句仅用于过滤行。过滤group by需要having
不能在where子句中用组函数，having中才能用组函数
select
count
(*)
from
user
group
by
sex
having
sex <> 2;
Ø 多表查询和子查询
数据库的查询功能最为丰富，很多时候需要用到查询完成一些事物，而且不是单纯的对一个表进行操作。而是对多个表进行联合查询，
MySQL中多表连接查询有两种规范，较早的SQL92规范支持，如下几种表连接查询：
等值连接
非等值连接
外连接
广义笛卡尔积
SQL99规则提供了可读性更好的多表连接语法，并提供了更多类型的连接查询，SQL99支持如下几种多表连接查询：
交叉连接
自然连接
使用using子句的连接
使用on子句连接
全部连接或者左右外连接
SQL92的连接查询
SQL92的连接查询语法比较简单，多将多个table放置在from关键字之后，多个table用“，”隔开；
连接的条件放在where条件之后，与查询条件直接用and逻辑运算符进行连接。如果条件中使用的是相等，
则称为等值连接，相反则称为非等值，如果没有任何条件则称为广义笛卡尔积。
广义笛卡尔积：
select
s.*, c.*
from
student s, classes c;
等值：
select
s.*, c.*
from
student s, classes c
where
s.cid = c.id;
非等值：
select
s.*, c.*
from
student s, classes c
where
s.cid <> c.id;
select
s.*, c.name classes
from
classes c, student s
where
c.id = s.classes_id
and
s.name
is
not
null
;
SQL99连接查询
1、交叉连接cross
join
，类似于SQL92的笛卡尔积查询，无需条件。如：
select
s.*, c.name
from
student s
cross
join
classes c;
2、自然连接
natural
join查询，无需条件，默认条件是将2个table中的相同字段作为连接条件，如果没有相同字段，查询的结果就是空。
select
s.*, c.name
from
student s
natural
join
classes c;
3、using子句连接查询：using的子句可以是一列或多列，显示的指定两个表中同名列作为连接条件。
如果用natural join的连接查询，会把所有的相同字段作为连接查询。而using可以指定相同列及个数。
select
s.*, c.name
from
student s
join
classes c
using
(id);
4、
join
… on连接查询，查询条件在on中完成，每个on语句只能指定一个条件。
select
s.*, c.name
from
student s
join
classes c
on
s.classes_id = c.id;
5、 左右外连接：3种外连接，
left
[
outer
]
join
、
right
[
outer
]
join
，连接条件都是通过用on子句来指定，条件可以等值、非等值。
select
s.*, c.name
from
student s
left
join
classes c
on
s.classes_id = c.id;
select
s.*, c.name
from
student s
right
join
classes c
on
s.classes_id = c.id;
子查询
子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。子查询可以出现在2个位置：
from关键字之后，被当做一个表来进行查询，这种用法被称为行内视图，因为该子查询的实质就是一个临时视图
出现在where条件之后作为过滤条件的值
子查询注意点：
子查询用括号括起来，特别情况下需要起一个临时名称
子查询当做临时表时（在from之后的子查询），可以为该子查询起别名，尤其是要作为前缀来限定数据列名时
子查询用作过滤条件时，将子查询放在比较运算符的右边，提供可读性
子查询作为过滤条件时，单行子查询使用单行运算符，多行子查询用多行运算符
将from后面的子查询当做一个table来用：
select
*
from
(
select
id, name
from
classes) s
where
s.id
in
(1, 2);
当做条件来用：
select
*
from
student s
where
s.classes_id
in
(
select
id
from
classes);
select
*
from
student s
where
s.classes_id =
any
(
select
id
from
classes);
select
*
from
student s
where
s.classes_id >
any
(
select
id
from
classes);
Ø 操作符和函数
1、 boolean只判断
select
1
is
true
, 0
is
false
,
null
is
unknown
;
select
1
is
not
unknown
, 0
is
not
unknown
,
null
is
not
unknown
;
2、 coalesce函数，返回第一个非null的值
select
coalesce
(
null
, 1);
select
coalesce
(1, 1);
select
coalesce
(
null
, 1);
select
coalesce
(
null
,
null
);
3、 当有2个或多个参数时，返回最大的那个参数值
select
greatest(2, 3);
select
greatest(2, 3, 1, 9, 55, 23);
select
greatest(
'D'
,
'A'
,
'B'
);
4、 Least函数，返回最小值，如果有null就返回null值
select
least(2, 0);
select
least(2, 0,
null
);
select
least(2, 10, 22.2, 35.1, 1.1);
5、 控制流函数
select
case
1
when
1
then
'is 1'
when
2
then
'is 2'
else
'none'
end
;
select
case
when
1 > 2
then
'yes'
else
'no'
end
;
6、 ascii字符串函数
select
ascii(
'A'
);
select
ascii(
'1'
);
7、 二进制函数
select
bin(22);
8、 返回二进制字符串长度
select
bit_length
(11);
9、 char将值转换成字符，小数取整四舍五入
select
char
(65);
select
char
(65.4);
select
char
(65.5);
select
char
(65.6);
select
char
(65, 66, 67.4, 68.5, 69.6,
'55.5'
,
'97.3'
);
10、 using改变字符集
select
charset(
char
(0*65)), charset(
char
(0*65
using
utf8));
11、 得到字符长度char_length,
character_length
select
char_length
(
'abc'
);
select
character_length
(
'eft'
);
12、 compress压缩字符串、uncompress解压缩
select
compress(
'abcedf'
);
select
uncompress(compress(
'abcedf'
));
13、 concat_ws分隔字符串
select
concat_ws(
'#'
,
'first'
,
'second'
,
'last'
);
select
concat_ws(
'#'
,
'first'
,
'second'
,
null
,
'last'
);
Ø 事务处理
动作
开始事务：
start
transaction
提交事务：
commit
回滚事务：
rollback
设置自动提交：
set
autocommit 1 | 0
atuoCommit系统默认是1立即提交模式；如果要手动控制事务，需要设置set autoCommit 0;
这样我们就可以用commit、rollback来控制事务了。
在一段语句块中禁用autocommit 而不是set autocommit
start
transaction
;
select
@
result
:=
avg
(age)
from
temp;
update
temp
set
age = @
result
where
id = 2;
select
*
from
temp
where
id = 2;
//值被改变
rollback
;
//回滚
select
*
from
temp
where
id = 2;
//变回来了
在此期间只有遇到commit、
rollback
，
start
Transaction的禁用autocommit才会结束。然后就恢复到原来的autocommit模式；
不能回滚的语句
有些语句不能被回滚。通常，这些语句包括数据定义语言（DDL）语句，比如创建或取消数据库的语句，
和创建、取消或更改表或存储的子程序的语句。
您在设计事务时，不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句，
则后部的其它语句会发生错误，在这些情况下，通过发布ROLLBACK语句不能 回滚事务的全部效果。
一些操作也会隐式的提交事务
如alter、
create
、
drop
、rename
table
、lock
table
、
set
autocommit、
start
transaction
、
truncate
table
等等,
在事务中出现这些语句也会提交事务的
事务不能嵌套事务
事务的保存点
Savepoint
pointName/
Rollback
to
savepoint
pointName
一个事务可以设置多个保存点，rollback可以回滚到指定的保存点，恢复保存点后面的操作。
如果有后面的保存点和前面的同名，则删除前面的保存点。
Release savepoint会删除一个保存点，如果在一段事务中执行commit或rollback，则事务结束，所以保存点删除。
Set
Transaction设计数据库隔离级别
SET
[
GLOBAL
|
SESSION
]
TRANSACTION
ISOLATION
LEVEL
{
READ
UNCOMMITTED |
READ
COMMITTED | REPEATABLE
READ
| SERIALIZABLE }
本语句用于设置事务隔离等级，用于下一个事务，或者用于当前会话。
在默认情况下，
SET
TRANSACTION会为下一个事务（还未开始）设置隔离等级。
如果您使用GLOBAL关键词，则语句会设置全局性的默认事务等级，
用于从该点以后创建的所有新连接。原有的连接不受影响。使用SESSION关键测可以设置默认事务等级，
用于对当前连接执行的所有将来事务。
默认的等级是REPEATABLE READ全局隔离等级。
Ø 注释
select
1+1; # 单行注释
select
1+1;
-- 单行注释
select
1
/* 多行注释 */ + 1;
Ø 基本数据类型操作
字符串
select
'hello'
,
'"hello"'
,
'""hello""'
,
'hel'
'lo'
,
'\'
hello
';
select "hello", "'hello
'", "'
'hello'
'", "hel""lo", "\"hello";
\n换行
select 'This\nIs\nFour\nLines
';
\转义
select 'hello \ world!
';
select 'hello \world!
';
select 'hello \\ world!
';
select 'hello \
' world!'
;
Ø 设置数据库mode模式
SET
sql_mode=
'ANSI_QUOTES'
;
create
table
t(a
int
);
create
table
"tt"(a
int
);
create
table
"t""t"(a
int
);
craate talbe tab("a""b"
int
);
Ø 用户变量
set
@num1 = 0, @num2 = 2, @
result
= 0;
select
@
result
:= (@num1 := 5) + @num2 := 3, @num1, @num2, @
result
;
Ø 存储过程
创建存储过程：
delimiter
//
create
procedure
get
(
out
result
int
)
begin
select
max
(age)
into
result
from
temp;
end
//
调用存储过程：
call
get
(@temp);
查询结果：
select
@temp;
删除存储过程：
drop
procedure
get
;
查看存储过程创建语句：
show
create
procedure
get
;
select
…
into
可以完成单行记录的赋值：
create
procedure
getRecord(sid
int
)
begin
declare
v_name
varchar
(20)
default
'jason'
;
declare
v_age
int
;
declare
v_sex
bit
;
select
name, age, sex
into
v_name, v_age, v_sex
from
temp
where
id = sid;
select
v_name, v_age, v_sex;
end
;
call
getRecord(1);
Ø 函数
函数类似于存储过程，只是调用方式不同
例如：
select
max
(age)
from
temp;
创建函数：
create
function
addAge(age
int
)
returns
int
return
age + 5;
使用函数：
select
addAge(age)
from
temp;
删除函数：
drop
function
if
exists
addAge;
drop
function
addAge;
显示创建语法：
show
create
function
addAge;
Ø 游标
声明游标：
declare
cur_Name
cursor
for
select
name
from
temp;
打开游标：
open
cur_Name;
Fetch游标：
fetch
cur_Name
into
@temp;
关闭游标：
close
cur_Name;
示例：
CREATE
PROCEDURE
cur_show()
BEGIN
DECLARE
done
INT
DEFAULT
0;
DECLARE
v_id, v_age
INT
;
DECLARE
v_name
varchar
(20);
DECLARE
cur_temp
CURSOR
FOR
SELECT
id, name, age
FROM
temp;
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'02000'
SET
done = 1;
OPEN
cur_temp;
REPEAT
FETCH
cur_temp
INTO
v_id, v_name, v_age;
IF
NOT
done
THEN
IF
isnull(v_name)
THEN
update
temp
set
name = concat(
'test-json'
, v_id)
where
id = v_id;
ELSEIF isnull(v_age)
THEN
update
temp
set
age = 22
where
id = v_id;
END
IF
;
END
IF
;
UNTIL done
END
REPEAT;
CLOSE
cur_temp;
END
Ø 触发器
触发器分为insert、
update
、delete三种触发器事件类型
还有after、before触发时间
创建触发器：
create
trigger
trg_temp_ins
before
insert
on
temp
for
each
row
begin
insert
into
temp_log
values
(
NEW
.id,
NEW
.name);
end
//
删除触发器：
drop
trigger
trg_temp_ins
