视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
创建视图：
|
1
2
|
create
view
v1
as
select
*
from
student
where
id > 100;
|
今后可以直接通过:
|
1
|
select
*
from
v1; #直接访问学生ID大于100的信息
|
删除视图：
|
1
|
drop
view
v1;
|
修改视图：
|
1
2
|
alter
view
v1
as
select
id,
name
from
student
where
id<10
and
name
=dandan;
|
注意：使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以使用其对真实表进行创建、更新和删除操作，仅能做查询用。
触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
创建触发器：
|
1
2
3
4
5
6
7
|
delimiter //
create
trigger
tg_before_insert_student before
insert
on
student
for
each row
begin
if new.
name
==
'alex'
then
insert
into
student_back (
name
)
values
(
'alex_back'
);
end
//
delimiter ;
|
也可以用 after insert 表示插入之后执行触发器中语句，还有其他操作：before update、after update、before delete 、after delete
注意：NEW表示即将插入的数据行，OLD表示即将删除的数据行
删除触发器：drop tgigger tg_before_insert_student
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
存储过程
存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
-- 创建存储过程
delimiter //
create
procedure
p1()
BEGIN
select
*
from
t1;
END
//
delimiter ;
-- 执行存储过程
call p1()
|
对于存储过程，可以接受参数，其参数类型有三类：
in 只用于传入参数
out 用于返回值
inout 即可以传入又可以当作返回值
有参数的存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
-- 创建存储过程
delimiter \\
create
procedure
p1(
in
i1
int
,
in
i2
int
,
inout i3
int
,
out
r1
int
)
BEGIN
DECLARE
temp1
int
;
DECLARE
temp2
int
default
0;
set
temp1 = 1;
set
r1 = i1 + i2 + temp1 + temp2;
set
i3 = i3 + 100;
end
\\
delimiter ;
-- 执行存储过程
set
@t1 =4;
set
@t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT
@t1,@t2;
|
结果集加返回值：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
delimiter //
create
procedure
p2(
in
n1
int
,
inout n3
int
,
out
n2
int
,
)
begin
declare
temp1
int
;
declare
temp2
int
default
0;
select
*
from
v1;
set
n2 = n1 + 100;
set
n3 = n3 + n1 + 100;
end
//
delimiter ;
|
事务型存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
delimiter \\
create
PROCEDURE
p1(
OUT
p_return_code tinyint
)
BEGIN
DECLARE
exit handler
for
sqlexception
BEGIN
-- ERROR
set
p_return_code = 1;
rollback
;
END
;
DECLARE
exit handler
for
sqlwarning
BEGIN
-- WARNING
set
p_return_code = 2;
rollback
;
END
;
START
TRANSACTION
;
DELETE
from
tb1;
insert
into
tb2(
name
)
values
(
'seven'
);
COMMIT
;
-- SUCCESS
set
p_return_code = 0;
END
\\
delimiter ;
|
游标：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
delimiter //
create
procedure
p3()
begin
declare
ssid
int
;
-- 自定义变量1
declare
ssname
varchar
(50);
-- 自定义变量2
DECLARE
done
INT
DEFAULT
FALSE
;
DECLARE
my_cursor
CURSOR
FOR
select
sid,sname
from
student;
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done =
TRUE
;
open
my_cursor;
xxoo: LOOP
fetch
my_cursor
into
ssid,ssname;
if done
then
leave xxoo;
END
IF;
insert
into
teacher(tname)
values
(ssname);
end
loop xxoo;
close
my_cursor;
end
//
delimter ;
|
动态执行SQL：
|
1
2
3
4
5
6
7
8
9
10
|
delimiter \\
CREATE
PROCEDURE
p4 (
in
nid
int
)
BEGIN
PREPARE
prod
FROM
'select * from student where sid > ?'
;
EXECUTE
prod USING @nid;
DEALLOCATE
prepare
prod;
END
\\
delimiter ;
|
删除存储过程：drop procedure proc_name;
执行存储过程：
|
1
2
3
4
5
6
7
8
9
10
|
-- 无参数
call proc_name()
-- 有参数，全in
call proc_name(1,2)
-- 有参数，有in，out，inout
set
@t1=0;
set
@t2=3;
call proc_name(1,2,@t1,@t2)
|
函数
MySql中的函数分为：内置函数、自定义函数
内置函数参考：
http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions
自定义函数：
|
1
2
3
4
5
6
7
8
9
10
11
|
delimiter \\
create
function
f1(
i1
int
,
i2
int
)
returns
int
BEGIN
declare
num
int
;
set
num = i1 + i2;
return
(num);
END
\\
delimiter ;
|
