mysql实战指令--带你快速掌握mysql语法 进阶篇

该文是mysql实战知识点
适合有一定基础，代码实战教学为主，基础知识比较少，可以帮助大家有效巩固mysql基础操作。

该文是进阶篇
基础篇包括对数据库，表的结构操作，查询及增删改。
进阶篇包括视图，触发器，函数以及存储过程。

文章目录

5.MySQL函数和存储过程

5.1mysql流程控制函数

1.IF 函数

IF (expr1,expr2,expr3) 1是真 返回2 否则返回3

select name,if(age>18,'成年'，'未成年') from user;

2.IFNULL函数

IFNULL(expr1,expr2); 如果1不是null，就返回1，是空就返回2

select name ifnull(age,0)from user;

3.nullif函数

NULLIF(expr1,expr2); 如果1=2 就返回null 否则返回1

select name ,nullif(name=loginName) from user;

4.case函数

case[expr] when[value] then[result] else[default] END;

value 是真就返回result 否则返回default默认值

select name ,case sex when'男'then'男生'when'女'then'女生' else'未知' end
as result from user;

实战–使用流程控制函数

mysql -u root -p 123456;
use shop;
select * from user;
#判断年龄大于18时，输出成年，否则输出未成年。
select name,if(age>18,'成年'，'未成年') from user;
#判断年龄为select name IFNULL(age,0)from user;null时输出默认0。
select name ifnull(age,0)from user;
#判断用户名name和loginName是否相等，相等返回null，否则返回用户。
select name ,nullif(name=loginName) from user;
#判断性别为女输出女生，男输出男生，否则输出未知。
select name ,case sex when'男'then'男生'when'女'then'女生' else'未知' end as result from user;

5.2MYSQL常用函数

5.2.1数学函数

常用的数学函数如下

1.ceiling(x) 向上取整，返回大于x最小整数值

2.floor(x) 向下取整

3.round(x,y) 返回x四舍五入值，保留y位小数

4.truncate(x,y) 截断x，保留y位小数（不四舍五入）

5.mod(x,y) 返回x/y的模 取余数 等于x%y

实战—使用数学函数

mysql -u root -p 123456;
#12.6向上取整
select ceiling(12.6);
#27.4向下取整
select floor(27.4);
#54362.8792四舍五入 保留两位
select round(45362.8792,2);
#678.3478不四舍五入 保留两位小数
select truncate(678.3484,2);
#3对5去余数
select mod(3,5);
select 3%5;
#3除以5
select 3/5;

5.2.2字符串函数

常用字符串函数如下

1.length(str) 获取字符串长度

2.lower(str) upper(str) 进行大小写转换

3.strcmp(s1,s2) 比较两个字符串大小 大于1 小于-1 等于0

4.replace(s,s1,s2) 替换字符串

5.concat(s1,s2,…,sn) 连接字符串

6.concat_ws(sep,s1,s2…sn) 使用分隔符合并拼接字符串

7.trim(str) rtrim(str) ltrim(str) 去除空格

8.substring(s,n,len) 截取字符串

实战–使用字符串函数

mysql -u root -p 123456;
#获取字符串 hello的长度
select length('hello');
#hello变为大写
select upper('hello');
#WORDL变为小写
select lower('WORLD');
#比较hello 和world的大小
select strcmp('hello','world')
#将hello中的e 替换为abc
select replace('hello','e','abc');
#将hello和world拼接
select concat('hello','world');
#将hello和world使用分隔符-拼接成一个字符串
select concat_ws('-','hello','world');
#截取hello world字符串  从第二个到第五个
select substring('hello world',2,5);

5.2.3 时期和时间函数

常用日期和时间函数如下

1.NOW() 获取当前日期与时间

2.CURDATE() 当前日期

3.CURTIME() 当前时间

4.YEAR(d) 提取d中的年份

5.MONRH(d) 提取d中的月份

6.DAYOFYEAR(d) 提取d是一年中的第几天

7.DAYOFWEEK(d) 提取d是一星期的第几天

8.HOUR(d) 提取d时间中的小时

9.MINUTE(d) 提取d时间中的分钟

10.SECOND(d) 提取d时间中的秒

11.DATE_ADD() 向后推时间

12.DATE_SUB() 向前推时间

实战–使用日期和时间函数

mysql -u root -p 123456;
#获取当前日期与时间
select now();
#获取当前日期
select curdate();
#获取当前时间
select curtime();
#提取年份
select year('2017-11-30');
#提取月份
select month('20170819');
#提取d是一年中的第几天
select dayofyear('2017-11-30');
#提取d是一星期的第几天
select dayofweek('2017-11-30');
#提取d时间中的小时
select hour('11.20');
#提取d时间中的分钟
select minute('11.30.29');
#提取d时间中的秒
select second('11.30.59');
#时间向后推3年
select date_add(now(),interval 3 year);
#时间想提前3年
select date_sub(now(),interval 3 year);

5.2.4系统信息函数

函数名称含义示例VERSION()返回数据库版本号select Version();CONNECTION_ID()返回服务器的连接数select connection_id ();DATABASE() SCHEMA()返回当前数据库名select schema ();USER() SYSTEM_USERA() SESSION_USER()返回当前用户，系统用户，回话用户select user ();CURRENT_USER()返回当前用户select current_user ();CHARSET(STR)返回当前字符串str的字符集select charset (“hello”);COLLACTION(STR)返回字符串str的字符排列方式LAST_INSERT_ID()返回最近生成的AUTO_INCREMENET值select last_insert_id ();
实战–使用系统信息函数

mysql -u root -p 123456;
当前数据库的版本号
select Version();
服务器的连接数
select connection_id ();
当前数据库名
select schema ();
当前用户
select user ();
select current_user ();
字符串编码集
select charset ("hello");
最近生成的auto_increment的值
select last_insert_id ();

5.2.5加密函数

1.PASSWORD(str)加密函数

可以对str字符串进行加密

采用mysql的sha1加密方式 生成41位字符串

加密函数常用于对用户密码进行加密

select password('12346');

2.MD5(str)加密函数

可以对字符串str进行散列加密

常用于对一些不需要解密的数据

select md5('123456');

3.ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str)加解密函数

加密结果是二进制数字，使用blob类型存储 使用encode加密

加密后生成解密字符串秘钥然后用decode解密

select encode('xiaogang','key');
select decode(encode('xiaogang','key'),'key');

4.AES_ENCRYPT(str,key)与AES_DECRYPT(str,key)加解密函数

AES_ENCRYPT用秘钥key对字符串利用高级加密算法进行加密 结果是一个二进制字符 以blob类型存储

AES_DECRYPT用秘钥key对字符串str加密算法进行解密

select aes_encrypt('xiaogang','key');
select aes_decrypt(select aes_encrypt('xiaogang','key'),'key');

5.SHA(str)加解密函数

计算字符串str安全散列算法sha校验和

select sha('123456');

5.2.6格式化函数

1.格式化日期函数-DATE_FORMAT(date,fmt)

根据fmt日期格式对date日期进行格式转换

select date_format(now(),'%W,%D,%M,%Y,%r');
--Thursday,30th,November 2017 08:44:29 PM
select date_format(now(),'%Y-%m-%d');
--2017-11-30
select date_format(19990330,'%Y-%m-%d');
--1999-03-30

2.格式化时间函数TIME_FORMAT(time,fmt)

根据fmt时间格式对time时间进行格式转换

select time_format(now(),'%h %i');
--09 33
select time_format('21:34:45','%h %i');
--09 34
select time_format('21:34:45','%h-%i-%s');
--09-34-45

3.格式化IP函数INET_AUTO(ip) 和INET_NTOA(num)

INET_AUTO(ip) ip转换为数字

INET_NTOA(num) 数字转换为ip

select inet_aton('192.168.1.100');
--3232235876
select inet_ntoa(3232235876);
--192.168.1.100

4.格式化浮点数函数FORMAT(‘36828.67628’,3)

FORMAT(‘36828.67628’,3)

5.3自定义函数

5.3.1 函数的基本语法

1.创建自定义函数

delimiter $$
create function fun(al varchar(255))
returns varchar(255)
begin
declare x varchar(255) default'';
set x=concat(al,'word');
return x;
end$$

delimter 用来修改sql语句的修饰符

function x x是函数名 用来标识函数

参数列表 参数名 参数类型

return 返回函数的类型

begin end 标志开始和结束

set 设置变量的值

return 返回函数体内的返回值

2.查看自定义函数

show function status like 'name';    --                
    eg    show function status like 'fun';
show create function name;            --                
    eg    show create function fun;

show function status 是查看自定义函数的状态

包括所属的数据库，类型，函数名称，修改时间状态信息

show create function 查看函数的信息

包括函数内容

3.调用自定义函数

select functionname(x1,x2)
select fun()

有参数带参数

4.修改自定义函数

alter function name[ characteristic...]

eg  alter function fun
    reads sql data    
    comment '字符串链接';

只能修改一些特征，不能修改函数体，

characteristic包括
characteristic参数意义contains sql表示子程序包含sql语句不包含读写数据的语句no sql子程序中不包括sql语句reads sql data子程序中包含读数据的语句modifies sql data表示子程序中包含写数据的语句comment ‘string’注释信息sql security(definer|invoker)指明谁有权限执行definer定义者可以执行invoker调用者可执行

5.删除自定义函数

drop function name;
drop function if exists name;
eg  drop function fun;

function后面可以不含括号，可以先判断是否存在再删除

5.3.2创建不带参数的自定义函数

实战–

delimiter $$
create function getFormatDatetime())
returns varchar(255)
begin
return date_format(now(),'%Y年%m月%d日');
end$$

select getFormatDatetime();

5.3.3创建带参数的自定义函数

实战–

delimiter $$
create function fun(al varchar(255))
returns varchar(255)
begin
declare x varchar(255) default'';
set x=concat(al,'word');
return x;
end$$
select fun('hello');

5.4存储过程

5.4.1存储过程的基本语法

1.创建存储过程

create procedure name([in/out/inout] name  type)
begin
***
end

eg
delimiter $$
create procedure hello(in name varchar(255),out re varchar(255))
begin
declare a varchar(255);
set a='hello';
set re=a+name;
end
end$$    i8=--++

declare 声明变量

set 设置变量

begin…end 用来存放存储过程函数体

call 调用存储过程

用户变量 一般以@开头，如输入参数变量@p_sex,输出参数变量@p_count

delimiter 改变结束符

2.查看存储过程

show procedure status like 'name'    
#查看自定义存储过程的状态，包括数据库，类型，函数名称，修改时间。
show create procedure name;            
#查看存储过程的信息，包括存储过程的内容。

3.调用存储过程

如果有参数则存储过程名称后要添加参数。

存储过程的变量分为用户变量和局部变量

局部变量是用declare定义在begin–end内使用的，只在begin-end内有效

用户变量是和客户端有关，通常使用@作为变量的前缀

call procedure_name();              #无参
call procedure_name(a1,a2);            #有参

4.修改存储过程

只能修改存储过程的一些特征，不能修改过程体，如果要修改过程体要先删除原存储过程重建。

ALTER PROCEDURE procedure_name [characteristic ...]

eg:
alter procedure getUserCountBySex
reads sql data
comment'字符串 拼接'

procedure_name 存储过程名

characteristic参数 指定存储函数的特征

characteristic包括
characteristic参数意义contains sql表示子程序包含sql语句不包含读写数据的语句no sql子程序中不包括sql语句reads sql data子程序中包含读数据的语句modifies sql data表示子程序中包含写数据的语句comment ‘string’注释信息sql security(definer|invoker)指明谁有权限执行definer定义者可以执行invoker调用者可执行

5.删除自定义函数

drop procedureprocedure_name;
drop procedureif exists procedure_name;

eg:
drop procedure getUserByCountSex;

存储过程名后不带括号

也可以先判断后删除

5.4.2创建不带参数的存储过程

delimiter $$
create procedure getUserInfo()
begin
select * from user;
end
$$
delimiter ;

call getUserInfo();
#不带参数时，可以把括号省略
call getUserInfo;

5.4.3创建带in类型的存储过程

delimiter $$
create procedure getUserBySex(IN sex0 varchar(255))
begin
select sex0;
select * from user where sex=sex0;
end
$$
delimiter ;

call  getUserBySex('女');
#in类型参数 可以通过用户自定义变量来传递参数
set @p_sex='男';
call  getUserBySex(@p_sex);

5.4.4创建带in和out的存储过程

in类型参数值在修改后不能被返回

out类型在修改后可以被返回

#创建一个in和out类型的存储过程  传入用户性别，返回该性别的用户数量
delimiter $$
create procedure getCountBySex(IN sex0 varchar(255),OUT count0 int)
begin
select count(*) into count0 from user where sex=sex0;
end
$$
delimiter ;

call getCountBySex('女',@p_count);
select @p_count;

5.4.5创建带有多个out类型的存储过程

可以有多个输出参数

#输入id 查询小于该id的男生和女生数量分别是多少
delimiter $$
create procedure getSexCountById(IN id0 int,OUT count_nan int,OUT count_nv int)
begin
select count(*) into count_nan from user where id<id0 and sex='男';
select count(*) into count_nv from user where id<id0 and sex='女';
end
$$
delimiter ;

call getSexCountById(8,@count_nan,@count_nv);
select @count_nan;
select @count_nv;

5.4.6创建带有inout类型参数的存储过程

inout类型可以同时输入和输出

#根据用户id查询用户user记录
delimiter $$
create procedure getUserById(INOUT p_id int)
begin
select * from user where id=p_id;
set p_id=100;
end
$$
delimiter ;

set @p_id=8;
call getUserById(@p_id);
select @p_id;                       --100

5.4.7创建if语句的存储过程

条件控制语句

IF  THEN
ELSEIF  THEN
ELSE
END IF

#判断用户的年龄  0-6输出童年   7-17输出少年    其余输出成年
delimiter $$
create procedure getTypeByAge(IN age0 int)
begin
declare type varchar(255) default'';
if(age0>0 and age0<=6)
then set type='童年';
elseif(age0>6 and age0<=17) 
then set type='少年';
else
set type='成年';
end if;
select type;
end
$$
delimiter ;

call getTypeByAge(5);
--童年
call getTypeByAge(15);
--少年
call getTypeByAge(25);
--成年

5.4.8创建带case语句的存储过程

case多分支语句

CASE X
WHEN X1 THEN
WHEN X2 THEN
ELSE
END CASE

#输入0输出2条记录   输入1输出5条记录  否则输出使用记录
delimiter $$
create procedure getUserByType(IN type0 int)
begin
case type0
when 0 then
select * from user limit 0,2;
when 1 then
select * from user limit 0,5;
else
select * from user;
end case;
end
$$
delimiter ;

call getUserByType(0);
--输出两条记录
call getUserByType(1);
--输出五条记录
call getUserByType(2);
--输出使用记录

5.4.9创建while循环语句的存储过程

循环语句

WHILE  
DO
END WHILE

#根据输入参数值，计算累加和
delimiter $$
create procedure computeSun(IN n int)
begin
declare i int default 0;
declare s int default 0;
while i<=n do
set s=s+i;
set i=i+1;
end while;
select s;
end
$$
delimiter ;

call computeSun(100)
--5050

5.5自定义函数和存储过程的区别

自定义函数只能有一个返回值，可以看作为内置函数的扩展，针对性较强

存储过程是为了解决复制的sql语句或业务逻辑，可以有多个返回值，预编译保存在数据库中，自己在数据库中调用数据，可以提高执行效率，可以独立运行，降低网络的数据传输量。

6.MySQL高级特征

6.1视图

6.1.1什么是视图

视图是一条select语句查询测结果集，这个结果集被保存为一张虚拟表 ，叫视图。

可以进行增删改查操作

存放的是查询结果 不是查询数据

基本表数据改变，视图也跟着变

6.1.2创建视图

create view name
as select 语句
[with [cascaded|local]check option]

name:表名称

with check option 更新视图必须在权限范围内

cascaded 更新视图时候 要满足所有相关视图和表的条件

local 更新视图时候要满足视图本身定义条件

实战–创建视图

mysql -uroot -p123456;
use shop;
#创建表dept  paroll  employee

create view edp_view 
as select e.id,e.name,d.deptname,p.salary 
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

#查询视图
select * from edp_view where salary>1000;

6.1.3修改视图

第一种

使用create or replace view 语句–视图存在进行修改，不存在进行创建

create or replace view edp_view
as select e.id,e.name,d.deptname,p.salary ,d.manager
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

第二种

alter语句

alter view edp_view
as select e.id,e.name,d.deptname,p.salary ,d.manager
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

实战–修改视图

mysql -uroot -p123456;
use shop;
#创建表dept  paroll  employee

create view edp_view 
as select e.id,e.name,d.deptname,p.salary 
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

#通过create or replace view修改视图 添加部门经理 manager列
create or replace view edp_view
as select e.id,e.name,d.deptname,p.salary ,d.manager
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

#通过alter修改视图添加sex列
alter view edp_view
as select e.id,e.name,d.deptname,p.salary,e.sex
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

6.1.4更新视图数据

包括修改视图数据，插入视图里的数据，删除视图里的数据

视图会跟着基本表改变自动更新数据

但是更新视图有很多限制

修改视图中的某项值 基本表也会跟着改变

mysql -uroot -p123456;
use shop;
#创建表dept  paroll  employee

create view edp_view 
as select e.id,e.name,d.deptname,p.salary,d.manager
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

#更新视图将软件开发部门经理改为李华
update edp_view set manager='李华'where depname='软件开发部门';
**基本表中数据也变了
#多表关联的视图 ，插入数据，删除数据会失败 ，单表查询就可以

6.1.5删除视图和数据

如果视图是基于某个基本表建立的可以自己删除数据

如果是依赖多个表建立的 不能删除数据

删除视图

drop view if exists work_view;
drop view work_view;

删除视图数据

delete from work_view;

实战–删除视图和数据

mysql -uroot -p123456;
use shop;
#创建表dept  paroll  employee

create view edp_view 
as select e.id,e.name,d.deptname,p.salary,d.manager
from employee e,paroll p,dept d
where e.deptid=d.deptid and e.id=p.emid;

#基于多表的数据是不能自己删除的

#如果视图存在删除视图
drop view if exists emp_view;

6.2游标

6.2.1游标的使用

mysql查询中查询出多条数据无法一行一行地获取数据，所以引入了游标，可以从结果集里获取一行数据，做一些复杂处理，mysql中国游标只能用于存储过程或函数。

delimiter $$
create procedure p1()
begin
declare c_name varchar(20);
declare cursor1 cursor;
for
select name from employee;
open cursor1;
fetch cursor1 into c_name;
select c_name;
close cursor1
end
$$
delimiter ;

创建游标

DECLARE cursor1 cursor;

使用declare创建游标，使用for关键字来定义相应的select语句

可以在存储过程或函数使用

存储过程处理完成后游标消失

打开游标

使用游标前要打开游标

open cursor1;

使用游标

游标被打开后 可以使用fetch语句访问sql查询出来的结果集中的每一行数据，他是按顺序读取的，不重复读取，执行fetch一次 读取一行数据。如果想读取多行数据 需要使用多次fetch语句。

fetch cursor1 into c_name;

关闭游标

close cursor1;

使用完后需要关闭游标

实战–使用游标

#使用游标输出employee表中的每一行的员工id，姓名name，工资salary
mysql -uroot -p123456
use shop;
#创建employee表 内容有id name salary  
#1，小明，3700    2，小红，5700     3，小蓝，7800
delimiter $$
create procedure p2()
begin
declare c_id int;
declare c_name varchar(20);
declare c_salary int;
declare cursor2 cursor;
for
select id,name,salary from employee;
open cursor2;
fetch cursor2 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
fetch cursor2 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
fetch cursor2 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
close cursor2
end
$$
delimiter ;

call p2();
#输出三条数据
1，小明，3700   
2，小红，5700 
3，小蓝，7800

6.2.2游标的while循环

用来获取多个数据

WHILE 
DO 
END WHILE

实战–while循环的游标

#使用while循环加游标输出employee表中的每一行的员工id，姓名name，工资salary
mysql -uroot -p123456
use shop;
#创建employee表 内容有id name salary  
#1，小明，3700    2，小红，5700     3，小蓝，7800
delimiter $$
create procedure p3()
begin
declare c_id int;
declare c_name varchar(20);
declare c_salary int;
declare cursor3 cursor;
declare sum int default 0;
for
select id,name,salary from employee;
select count(*) into sum from employee;
open cursor3;
while(sum>0) do
fetch cursor3 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
set sum=sum-1;
end while;
close cursor3;
end
$$
delimiter ;

call p3();
#输出三条数据
1，小明，3700   
2，小红，5700 
3，小蓝，7800

#创建存储过程 使用while循环，并且声明一个变量值，如果查不到数据，设置变量结束循环
delimiter $$
create procedure p3()
begin
declare c_id int;
declare c_name varchar(20);
declare c_salary int;
declare cursor3 cursor;
declare flag int default 0;
for
select id,name,salary from employee;
declare continue handler for not found set flag=1;
open cursor3;
while(flag=0) do
fetch cursor3 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
end while;
close cursor3;
end
$$
delimiter ;
call p3();
#输出三条数据
1，小明，3700   
2，小红，5700 
3，小蓝，7800

#使用exit来进行变量判断，如果找不到设置变量为1
delimiter $$
create procedure p3()
begin
declare c_id int;
declare c_name varchar(20);
declare c_salary int;
declare cursor3 cursor;
declare flag int default 0;
for
select id,name,salary from employee;
declare exit handler for not found set flag=1;
open cursor3;
while(flag=0) do
fetch cursor3 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
end while;
close cursor3;
end
$$
delimiter ;
call p3();
#输出三条数据
1，小明，3700   
2，小红，5700 
3，小蓝，7800

6.2.3游标的repeat循环

游标除了使用while循环 ，也可以使用repeat循环

REPEAT 
UNTIL
END REPEAT;

eg:
repeat 
select n;
set n=n-1;
until n<0
end repeat

实战–repeat循环的游标

#使用repeat循环加游标输出employee表中的每一行的员工id，姓名name，工资salary
mysql -uroot -p123456
use shop;
#创建employee表 内容有id name salary  
#1，小明，3700    2，小红，5700     3，小蓝，7800
delimiter $$
create procedure p4()
begin
declare c_id int;
declare c_name varchar(20);
declare c_salary int;
declare cursor4 cursor;
declare flag int default 0;
for
select id,name,salary from employee;
declare exit handler for not found set flag=1;
open cursor4;
repeat
fetch cursor3 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
until flag=1;
end repeat;
close cursor4;
end
$$
delimiter ;

call p3();
#输出三条数据
1，小明，3700   
2，小红，5700 
3，小蓝，7800

6.2.4游标的loop循环

除了while和repeat循环 也可以用loop循环

LOOP
END LOOP

eg:
declare i int default 1;
lp1:loop
set i=i+1;
if i>30 then
leave lp1;  #离开循环体
end if;
end loop;

实战–loop循环的游标

#使用loop循环加游标输出employee表中的每一行的员工id，姓名name，工资salary
mysql -uroot -p123456
use shop;
#创建employee表 内容有id name salary  
#1，小明，3700    2，小红，5700     3，小蓝，7800
delimiter $$
create procedure p5()
begin
declare c_id int;
declare c_name varchar(20);
declare c_salary int;
declare cursor5 cursor;
for
select id,name,salary from employee;
declare exit handler for not found set flag=1;
open cursor5;
loop_lable:loop
fetch cursor5 into c_id,c_name,c_salary;
select c_id,c_name,c_salary;
if(flag=1)then
leave loop_lable;
end loop;
close cursor5;
end
$$
delimiter ;

call p5();
#输出三条数据
1，小明，3700   
2，小红，5700 
3，小蓝，7800

6.3触发器

当表上出现指定事件，会调用触发器的对象。

TRIGGER 触发器的关键字

6.3.1创建触发器

create 
    [definer={user|current_user}]
    trigger trigger_name
    trigger_time trigger_event
    on tablename
    for each row
    body

trigger_time 触发器触发的时机 before ,after

trigger_event 触发器触发的事件 insert,update,delete

for each row 在表的每一行操作

一个表上不能建立两个类型相同的触发器

可以分为六种触发器

before insert ;before update; before delete;

after insert; after update; after delete;

举个栗子

create 
    trigger add_data
    after insert
    on t1
    for each row
    begin
    insert into t2 values(666); 
    end

实战–创建触发器

mysql -uroot -p123456;
use shop;
#创建表 user,stat
#创建一个触发器来计算用户的数量，统计数量的值放置在stat的num字段里
delimiter &&
create trigger cpuNum
after insert 
on user 
for each row
begin
update tat set num=num+1 where id=1;
end
&&

6.3.2new和old关键字

insert触发器来讲，new表示插入的数据

update触发器来讲，new 表示新改的数据，old表示修改过的数据

delete来讲 old表示删除过的数据

old 是只读的 而new可以在触发器中进行赋值

使用方法 new.columnName（columnName 列名）

实战–关键字new old

mysql -uroot -p123456;
use shop;
#创建表 employee dept payroll
#创建按部门删除员工的触发器，删除部门后，触发器会自动删除这个部门对的员工
delimiter &&
create trigger deleteemp
after delete
on dept for each row
begin
delete from employee where deptid=old.id;
end
&&

6.3.3查看和删除触发器

查看触发器

show triggers

删除触发器

drop trigger triggername；

6.3.4insert型触发器

insert型触发器分为插入之前和插入之后，before和after

new关键字表示插入的新数据

#创建员工表employee和记录表record，如果工资低于3500，则记录到记录表record表中
mysql -uroot -p123456;
use shop;
#创建表员工表employee（id，name，salary）和记录表record(in，name，salary)
#创建触发器，低于3500的工资记录到record表中
delimiter $$
create trigger addRecord
after insert
on employee 
for each row
begin
if(new.salary<3500) then
insert into record values(new.id,new.name,new.salary);
end if;
end
$$
delimiter ;
#插入一条2800工资的员工记录会在record表中显示
insert into employee values(1,'小明',2800);
select * from record;
--1,'小明',2800

6.3.5update型触发器

update分为更新前和更新后，before和after

old表示更改前的数据，new表示新更改的数据

replace可以用于插入或更新记录

#创建员工表employee和记录表record，如果员工表表里的工资涨到了3500，就让record表中记录删除，否则插入到记录中
mysql -uroot -p123456;
use shop;
##创建表员工表employee（id，name，salary）和记录表record(in，name，salary)
#创建触发器
delimiter $$
create trigger updateRecord
after update
on employee 
for each row
begin
if(new.salary>3500) then
delete fromk record where id=new.id;
else
replace record values(new.id,new.name,new.salary);
end if;
end
$$
delimiter ;
#插入一条2800工资的员工记录会在record表中显示
insert into employee values(1,'小明',2800);
select * from record;
--1,'小明',2800
#将工资从2800改为3800
update employee set salary=3800 where id=1;
select * from record;
--NULL

6.3.6delete型触发器

分为删除前和删除后

old表示已经删除的数据

#创建员工表employee和记录表record，如果员工表中删除了某员工，记录表中也要被删除
mysql -uroot -p123456;
use shop;
#创建表员工表employee（id，name，salary）和记录表record(in，name，salary)
#创建触发器
delimiter $$
create trigger deleteRecord
after delete
on employee 
for each row
begin
delete from record where id=old.id;
end
$$
delimiter ;
#删除id为1的用户
delete from employee where id=1;
select * from record where id=1;
--NULL

7.索引

7.1索引的基本语法

UNIQEU 代表索引是唯一的

FULLTEXT 代表索引是全文索引

SPATIAL 代表索引是空间索引

INDEX &KEY 用来指定某个字段为索引，效果一样

ASC 升序排列

DESC 降序排列

三种方法

第一种：在创建表的时候创建索引

create table student(
id int primary key,
name varchar(52),
index(name)
)

第二种：使用create index创建索引

create index name on tablename(column_list);
create unique index name on tablename(column_list);

eg:create index saex_index on student(sex);

不能用create index创建主键索引

第三种：使用alter table改变索引

alter table table_name add index index_name(column_list)
alter table table_name add unique(column_list)
alter table table_name add primary key(column_list)

eg:alter table student add index name_index(name);

可以用来创建普通索引，unique索引，primary key索引

7.1.2查看索引

show index from table_name;

show keys from table_name;

7.1.3删除索引

drop index index_name on table_name;
alter table table_name drop index index_name;
alter table table_name drop primary key;

实战-给用户user添加和删除索引

mysql -uroot -p123456;
use shop;
#创建表user
alter table user add  index nameindex(name);
alter table user add index sexindex(sex);
show index from user;
drop index nameindex on user;
drop index sexindex on user;

（id，name，salary）和记录表record(in，name，salary)
#创建触发器
delimiter $$
create trigger deleteRecord
after delete
on employee 
for each row
begin
delete from record where id=old.id;
end
$$
delimiter ;
#删除id为1的用户
delete from employee where id=1;
select * from record where id=1;
--NULL

7.索引

7.1索引的基本语法

UNIQEU 代表索引是唯一的

FULLTEXT 代表索引是全文索引

SPATIAL 代表索引是空间索引

INDEX &KEY 用来指定某个字段为索引，效果一样

ASC 升序排列

DESC 降序排列

三种方法

第一种：在创建表的时候创建索引

create table student(
id int primary key,
name varchar(52),
index(name)
)

第二种：使用create index创建索引

create index name on tablename(column_list);
create unique index name on tablename(column_list);

eg:create index saex_index on student(sex);

不能用create index创建主键索引

第三种：使用alter table改变索引

alter table table_name add index index_name(column_list)
alter table table_name add unique(column_list)
alter table table_name add primary key(column_list)

eg:alter table student add index name_index(name);

可以用来创建普通索引，unique索引，primary key索引

7.1.2查看索引

show index from table_name;

show keys from table_name;

7.1.3删除索引

drop index index_name on table_name;
alter table table_name drop index index_name;
alter table table_name drop primary key;

实战-给用户user添加和删除索引

mysql -uroot -p123456;
use shop;
#创建表user
alter table user add  index nameindex(name);
alter table user add index sexindex(sex);
show index from user;
drop index nameindex on user;
drop index sexindex on user;