mysql使用篇(基础概念、视图、触发器、存储过程、用户与权限管理)

总结下mysql基础概念，及一些相对比较不常用的知识模块，如视图，触发器(程序员不推荐用)，存储过程（偶尔造数据，也曾线上需要改数据用过）。

mysql 基础

RDBMS:关系形数据库管理系统

C/S :通过专有协议

关系模型：表（行，列），二维关系

范式

第一范式：字段不可再分
第二范式：不能有部分依赖，唯一主键即可满足，mysql天然满足这个条件
第三范式：不能出现传递依赖（字段不能冗余）

就是主键，非主键1，非主键2三者之间不能出现传递依赖关系，如果出现由主键可以推出非主键1,然后由非主键1可以推出非主键2，那么主键与非主键2就产生了传递依赖关系，这就不满足三范式，通俗来讲，在一个表的，当然以一条记录为单位，主键和非主键之间可以产生父子关系，但是非主键之间是不能出现父子关系的！

三层模型

1.物理层：存储，存储引擎

2.逻辑层：表，索引，视图，数据库内部组建

3.视图层：就是用户看到的界面

MariaDB

插件式存储引擎
单进程多线程
- 连接线程、守护线程等

配置文件

 1配置段：
 2[mysqld]
 3[mysqld_safe]
 4[mysql_multi]
 5[mysql]
 6[mysqldump]
 7[client]
 8配置语法
 9    parameter=value
10    skip-name-resolve,skip_name_resolve  格式都支持                                
11    查找路径
12        /etc/my.cnf-->/etc/mysql/my.cnf-->$MYSQL_HOME/my.cnf--> --default-extra-file=/path/to/somedir/my.cnf-->~/.my.cnf   都会找一遍， 相同配置，后者覆盖前者

1-- 主：从5.7.18开始不在二进制包中提供my-default.cnf文件
2-- 进入MySQL的bin目录
3-- 查看MySQL对于配置文件的查找路径，越左优先查找
4/bin/mysql --help | grep 'Default options' -A 1
5/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

安装方法

os vendor:rpm
mysql:
- rpm
- 展开可用（二进制包）
- 源码（编译安装）

安装后的设定：

(1) 为所有root用户设定密码：

1.mysql>SET PASSWORD

2.mysql> update mysq.user set password=PASSWORD(‘your_passwd’) where …

3.mysqladmin

(2) 删除所有匿名用户

mysql>DROP USER ‘’@’localhost’;

上述两步可以运行命令mysql_secure_installation

(3) 建议关闭主机名反解功能

客户端工具

mysql:交互式的cli工具
mysqldump: 备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中
mysqladmin：基于mysql协议管理mysqld；
mysqlimport: 数据导入工具；

非客户端类的管理工具：

myisamchk
myisampack

如何获取程序默认使用的配置

1]$mysql --print-defaults
2]$mysqld --print-defaults

客户端类应用程序的可用选项：

1-u, —user=
2-h, —host=
3-p, —passowrd=
4-P, —port=
5—protocol=
6-S, —socket=
7-D, —database=
8-C, —compress

mysql -hlocalhost -uroot -p -D newblog //进入默认使用newblog数据库

mysql的使用模式

交互式模式：可运行命令有两类：
- 客户端命令： \h, help
- 服务器端命令:SQL, 需要语句结束符；

脚本模式：

1]$mysql -uUSERNAME -hHOST -pPASSWORD < /path/from/somefile.sql
2]$mysql> source /path/from/somefile.sql

服务器端(mysqld)：工作特性有多种定义方式

命令行选项
配置文件参数

获取可用参数列表：mysqld —help —verbose

获取运行中的mysql进程使用各服务器参数及其值：

1mysql> SHOW GLOBAL VARIABLES;
2mysql> SHOW [SESSION] VARIABLES;

注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效;有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独的设置

修改服务器变量的值

 1mysql> help SET    
 2全局：
 3mysql> SET GLOBAL system_var_name=value;
 4mysql> SET @@global.system_var_name=value;
 5会话：
 6mysql> SET [SESSION] system_var_name=value;
 7mysql> SET @@[session.]system_var_name=value;
 8状态变量：用于保存mysqld运行中的统计数据的变量；
 9mysql> SHOW GLOBAL STATUS;
10mysql> SHOW [SESSION] STATUS;

MySQL 数据类型

字符型
- CHAR, BINARY：定长数据类型；
- VARCHAR, VARBINARY：变长数据类型；需要结束符；
- TEXT：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
- BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
数值型
- 精确数值型
  - 整型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
  - 十进制型：DECIMAL
- 近似数值型
  - 浮点型：FLOAT,DOUBLE
  - BIT
日期时间型
- DATE
- TIME
- DATETIME
- TIMESTAMP
- YEAR(2), YEAR(4)
内建类型
- ENUM, SET

SQL MODE

定义mysqld对约束等的响应行为

SQL MODE：定义mysqld对约束等的响应行为；修改方式：

1 mysql> SET GLOBAL sql_mode=`MODE`;
2 mysql> SET @@global.sql_mode=`MODE`;#需要修改权限；仅对修改后新创建的会话有效；对已经建立的会话无效；

1mysql> SET SESSION sql_mode='MODE';
2mysql> SET @@session.sql_mode='MODE';

常用MODE：TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES

SQL:DDL,DML

DDL: 数据定义语言；

CREATE, ALTER, DROP
- CREATE相关的常用命令：

 1     CREATE DATABASE
 2      CREATE EVENT
 3      CREATE FUNCTION
 4      CREATE FUNCTION UDF
 5      CREATE INDEX
 6      CREATE PROCEDURE
 7      CREATE SERVER
 8      CREATE TABLE
 9      CREATE TABLESPACE
10      CREATE TRIGGER
11      CREATE USER
12      CREATE VIEW

DB组件：数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器等

DML：数据操作语言
- INSERT, DELETE, UPDATE, SELECT

表

设计表：遵循规范
定义：字段，索引
- 字段：字段名，字段数据类型，修改符
- 约束，索引：应该创建在经常用作查询条件的字段上；
- 索引：实现级别在存储引擎；
  
  分类：
  - 稠密索引、稀疏索引
  - B+索引、hash索引、R树索引、FULLTEXT索引
  - 聚集索引、非聚集索引
  - 简单索引、组合索引
创建表 CREATE TABLE 3种方式
- 直接创建
- 通过查询现存的表创建；新表会被直接插入查询而来的数据
  - CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)][table_options] [partition_options] select_statement
- 通过复制现存的表的表结构创建；不复制数据；
  - CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
查看表结构 DESCRIBE tbl_name;
查看表状态信息
- SHOW [FULL] TABLES [{FROM | IN} db_name][LIKE ‘pattern’ | WHERE expr] show tables like ’table_name'
修改表：ALTER TABLE
删除表：DROP TABLE

注意

Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎；

**同一个库中表要使用同一种存储引擎类型**

修饰符

字符类型修饰符：
- NOT NULL: 非空约束；
- NULL；
- DEFAULT ‘STRING’: 指明默认值；
- CHARACTER SET ‘’：使用的字符集；
- COLLATION：使用的排序规则 mysql> SHOW CHARACTER SET; mysql> SHOW COLLATION;
整型数据修饰型：
- NOT NULL
- NULL
- DEFAULT NUMBER
- AUTO_INCREMENT
- UNSIGNED
- PRIMARY KEY|UNIQUE KEY
- NOT NULL
日期时间型修饰符：
- NOT NULL
- NULL
- DEFAUL
内建类型SET和ENUM的修饰符：
- NOT NULL
- NULL
- DEFAULT

视图

一、创建视图

1CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
2    VIEW view_name [(column_list)]
3    AS select_statement
4   [WITH [CASCADED | LOCAL] CHECK OPTION]

1）OR REPLACE：表示替换已有视图

2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法；merge合并；temptable临时表

3）select_statement：表示select语句

4）[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内

cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件

local表示更新视图的时候，要满足该视图定义的一个条件即可

TIPS：推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性

基本格式：

create view <视图名称>[(column_list)]

  as select语句

 with check option;

1、在单表上创建视图

 1mysql> create view v_F_players(编号,名字,性别,电话)
 2    -> as
 3    -> select PLAYERNO,NAME,SEX,PHONENO from PLAYERS
 4    -> where SEX='F'
 5    -> with check option;
 6Query OK, 0 rows affected (0.00 sec)
 7
 8mysql> desc v_F_players;
 9+--------+----------+------+-----+---------+-------+
10| Field  | Type     | Null | Key | Default | Extra |
11+--------+----------+------+-----+---------+-------+
12| 编号    | int(11)  | NO   |     | NULL    |       |
13| 名字    | char(15) | NO   |     | NULL    |       |
14| 性别    | char(1)  | NO   |     | NULL    |       |
15| 电话    | char(13) | YES  |     | NULL    |       |
16+--------+----------+------+-----+---------+-------+
174 rows in set (0.00 sec)
18
19mysql> select * from  v_F_players;
20+--------+-----------+--------+------------+
21| 编号    | 名字      | 性别    | 电话        |
22+--------+-----------+--------+------------+
23|      8 | Newcastle | F      | 070-458458 |
24|     27 | Collins   | F      | 079-234857 |
25|     28 | Collins   | F      | 010-659599 |
26|    104 | Moorman   | F      | 079-987571 |
27|    112 | Bailey    | F      | 010-548745 |
28+--------+-----------+--------+------------+
295 rows in set (0.02 sec)

2、在多表上创建视图

 1mysql> create view v_match
 2    -> as 
 3    -> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
 4    -> from 
 5    -> PLAYERS a,MATCHES b,TEAMS c
 6    -> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;
 7Query OK, 0 rows affected (0.03 sec)
 8
 9mysql> select * from v_match;
10+----------+-----------+---------+-----+------+--------+----------+
11| PLAYERNO | NAME      | MATCHNO | WON | LOST | TEAMNO | DIVISION |
12+----------+-----------+---------+-----+------+--------+----------+
13|        6 | Parmenter |       1 |   3 |    1 |      1 | first    |
14|       44 | Baker     |       4 |   3 |    2 |      1 | first    |
15|       83 | Hope      |       5 |   0 |    3 |      1 | first    |
16|      112 | Bailey    |      12 |   1 |    3 |      2 | second   |
17|        8 | Newcastle |      13 |   0 |    3 |      2 | second   |
18+----------+-----------+---------+-----+------+--------+----------+
195 rows in set (0.04 sec)

视图将我们不需要的数据过滤掉，将相关的列名用我们自定义的列名替换。视图作为一个访问接口，不管基表的表结构和表名有多复杂。

如果创建视图时不明确指定视图的列名，那么列名就和定义视图的select子句中的列名完全相同；

如果显式的指定视图的列名就按照指定的列名。

注意：显示指定视图列名，要求视图名后面的列的数量必须匹配select子句中的列的数量。

1ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
2    VIEW view_name [(column_list)]
3    AS select_statement
4    [WITH [CASCADED | LOCAL] CHECK OPTION]

 11. 简化逻辑
 2create view stu_info as select * from select_student as s left join 
 3select_class using(class_id);
 4select * from stu_info;
 5
 6上面2句话等于
 7select * from (select * from select_student as s left join select_class
 8using(class_id)) as stu_info;
 9
10
112. 隐藏表的结构
12管理员 ：
13 create view admin_student as select * from select_student;
14 普通用户：
15 create view normal_student as select id, stu_name, gender from select_student; //没有操作金钱的权利，看不到
16
17
18取得每个班里最高的信息
19select * from select_student group by class_id order by height desc;
20                             取第一条   语法是对的，要的结果不对
21
22子查询 select * from (select * from select_student order by height desc) as
23tmp group by class_id;
24
25
26错误的视图
27create view height_student as select * from select_student order by height desc;
28select * from height_student group by class_id; //默认采用merge方式 
29
30正确的
31create algorithm=temptable view height_student_tmp as select * from
32select_student order by height desc;
33select * from height_student_tmp group by class_id;
34
35视图其实一共有三种执行方式 temptable merge undefined
36							临时       合并  未定义(就是默认的  也是合并的)

二、查看视图

show tables | show table status like ‘view_name’\G

show create view view_name \G

通过系统表 infomation_schema.views 也可以查看视图相关信息

select * from views where table_name =‘view_name’ \G

三、视图的更改

1、CREATE OR REPLACE VIEW语句修改视图

基本格式：

create or replace view view_name as select语句;

在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图

2、ALTER语句修改视图

1ALTER
2    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
3    [DEFINER = { user | CURRENT_USER }]
4    [SQL SECURITY { DEFINER | INVOKER }]
5VIEW view_name [(column_list)]
6AS select_statement
7    [WITH [CASCADED | LOCAL] CHECK OPTION]

注意：修改视图是指修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致

3、DML操作更新视图

因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中

 1mysql> create view v_student as select * from student;
 2
 3mysql> select * from v_student;
 4+--------+--------+------+
 5| 学号    | name   | sex  |
 6+--------+--------+------+
 7|      1 | 张三    | M    |
 8|      2 | 李四    | F    |
 9|      5 | 王五    | NULL |
10+--------+--------+------+
11
12mysql> update v_student set name='钱六' where 学号='1';
13
14mysql> select * from student;
15+--------+--------+------+
16| 学号    | name   | sex  |
17+--------+--------+------+
18|      1 | 钱六    | M    |
19|      2 | 李四    | F    |
20|      5 | 王五    | NULL |
21+--------+--------+------+

当然，视图的DML操作，不是所有的视图都可以做DML操作。

有下列内容之一，视图不能做DML操作：

①select子句中包含distinct

②select子句中包含组函数

③select语句中包含group by子句

④select语句中包含order by子句

⑤select语句中包含union 、union all等集合运算符

⑥where子句中包含相关子查询

⑦from子句中包含多个表

⑧如果视图中有计算列，则不能更新

⑨如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

四、drop删除视图

删除视图是指删除数据库中已存在的视图，删除视图时，只能删除视图的定义，不会删除数据，也就是说不动基表：

1DROP VIEW [IF EXISTS]   
2view_name [, view_name] ...

mysql> drop view v_student;

如果视图不存在，则抛出异常；使用IF EXISTS选项使得删除不存在的视图时不抛出异常。

with [CASCADED|LOCAL] CHECK OPTION

 1对于可以执行DML操作的视图，定义时可以带上WITH CHECK OPTION约束
 2
 3作用：
 4
 5　　对视图所做的DML操作的结果，不能违反视图的WHERE条件的限制。
 6
 7示例：创建视图，包含1960年之前出生的所有球员（老兵）
 8mysql> create view v_veterans
 9    -> as
10    -> select * from PLAYERS
11    -> where birth_date < '1960-01-01'
12    -> with check option;
13Query OK, 0 rows affected (0.01 sec)
14
15mysql> select * from v_veterans;
16+----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
17| PLAYERNO | NAME    | INITIALS | BIRTH_DATE | SEX | JOINED | STREET         | HOUSENO | POSTCODE | TOWN      | PHONENO    | LEAGUENO |
18+----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
19|        2 | Everett | R        | 1948-09-01 | M   |   1975 | Stoney Road    | 43      | 3575NH   | Stratford | 070-237893 | 2411     |
20|       39 | Bishop  | D        | 1956-10-29 | M   |   1980 | Eaton Square   | 78      | 9629CD   | Stratford | 070-393435 | NULL     |
21|       83 | Hope    | PK       | 1956-11-11 | M   |   1982 | Magdalene Road | 16A     | 1812UP   | Stratford | 070-353548 | 1608     |
22+----------+---------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
233 rows in set (0.02 sec)

此时，使用update对视图进行修改：

1mysql> update v_veterans
2    -> set BIRTH_DATE='1970-09-01'
3    -> where PLAYERNO=39;
4ERROR 1369 (HY000): CHECK OPTION failed 'TENNIS.v_veterans'

因为违反了视图中的WHERE birth_date < ‘1960-01-01’子句，所以抛出异常；

利用with check option约束限制，保证更新视图是在该视图的权限范围之内。

嵌套视图：定义在另一个视图的上面的视图

1mysql> create view v_ear_veterans
2    -> as
3    -> select * from v_veterans
4　　 -> where JOINED < 1980;

使用WITH CHECK OPTION约束时，(不指定选项则默认是CASCADED)

可以使用CASCADED或者 LOCAL选项指定检查的程度：

①WITH CASCADED CHECK OPTION：检查所有的视图

例如：嵌套视图及其底层的视图

②WITH LOCAL CHECK OPTION：只检查将要更新的视图本身

对嵌套视图不检查其底层的视图

五、定义视图时的其他选项

1CREATE [OR REPLACE]   
2　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
3　　[DEFINER = { user | CURRENT_USER }]  
4　　[SQL SECURITY { DEFINER | INVOKER }]
5VIEW view_name [(column_list)]  
6AS select_statement  
7　　[WITH [CASCADED | LOCAL] CHECK OPTION]

1、ALGORITHM选项：选择在处理定义视图的select语句中使用的方法

①UNDEFINED：MySQL将自动选择所要使用的算法

②MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分

③TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句

缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED

2、DEFINER选项：指出谁是视图的创建者或定义者

①definer= ‘用户名’@‘登录主机’

②如果不指定该选项，则创建视图的用户就是定义者，指定关键字CURRENT_USER(当前用户)和不指定该选项效果相同

3、SQL SECURITY选项：要查询一个视图，首先必须要具有对视图的select权限。

但是，如果同一个用户对于视图所访问的表没有select权限，那会怎么样？

SQL SECURITY选项决定执行的结果：

①SQL SECURITY DEFINER：定义(创建)视图的用户必须对视图所访问的表具有select权限，也就是说将来其他用户访问表的时候以定义者的身份，此时其他用户并没有访问权限。

②SQL SECURITY INVOKER：访问视图的用户必须对视图所访问的表具有select权限。

缺省SQL SECURITY选项等同于SQL SECURITY DEFINER

视图权限总结：

使用root用户定义一个视图(推荐使用第一种)：u1、u2

1）u1作为定义者定义一个视图，u1对基表有select权限，u2对视图有访问权限：u2是以定义者的身份访问可以查询到基表的内容；

2）u1作为定义者定义一个视图，u1对基表没有select权限，u2对视图有访问权限，u2对基表有select权限：u2访问视图的时候是以调用者的身份，此时调用者是u2，可以查询到基表的内容。

触发器

用代码去控制，这个尽量不用

查看触发器

1.show triggers;

2.information_schema.triggers

desc triggers;

select * from triggers where trigger_name =‘trigger_name’\G

创建使用触发器

 1create trigger test_trigger
 2after insert -- 事件
 3on select_student for each row -- 哪个表的记录在监听
 4insert into student_log values(null,'op',now(),'new ID'); -- 执行的sql集合
 5
 6
 7create table student_log(
 8id int primary key atuo_increment
 9op varchar(10),
10op_time datatime,
11ps varchar(255)
12);
13
14insert into select_student values(null,'欧阳锋','male',22,1234,56,178.00);
15select * from student_log; //就有数据了 因为上面insert 触发了 事件

在触发程序中得到当前触发的记录信息

有，两个

new（新的），odd（旧的）

new和old，都表示触发程序的记录

new：新的记录，old：旧的记录

取决于当前操作（insert，update，delete）去使用其中哪个

insert ,增加记录，，没有旧记录，只有new关键字可以使用

delete，删除记录，没有新纪录，只有old可用！

update 更新，既有新纪录，也有旧记录，更新前是旧记录，而更新后是新纪录！因此可以new和old

 11.记录，当前学生被筛除之后，记录日志，要求记录学生的id 
 2
 3create trigger log_del_stu after delete
 4on select_student for each row
 5insert into student_log values(null,'delete',now(),old.id);// old.id 很关键id 为select_student
 6测试
 7delete from select_student where id=100;
 8select * from select_student;
 9select * from student_log;
10 // student_log 表中 ps 字段多了  一个100 的值在最后一排；
11
12此时应该留意一下 ： 触发器宇具体的语法的执行时机：
13insert into table 操作
14判断，是否有before insert 触发器有则触发程序
15真正执行 insert into 
16判断 是否有 after insert 触发器，有则执行触发程序
17
18
19更新日志：
20记录更新日志，要求是，只对某部分同学完成更新日志
21只记录，身高超过175的学生的更新记录！记录学生id和修改前的身高与之后的身高
22需要额外的增加条件判断！
23
24create trigger log_upd_stu after updata
25on select_student for each row 
26if old.height >175 then 
27				insert into student_log values(null,'upata',now(),concat(old.id,':',old.height,'---',new.height));
28	end if;
29;
30
31
32 逻辑分之语句
33  if 条件 then 
34   语句体
35   else if 条件 then
36     语句体
37	 ......
38	 else
39	 语句体
40	 end if；
41
42if old.height >175 then 
43				insert into student_log values(null,'upata',now(),concat(old.id,':',old.height,'---',new.height));
44	end if;
45
46
47	sql语句结束符问题：
48	可以修改，最外层的语句结束符达到目的！
49    delimiter $$    //典型的用两个$$
50
51	所以可以这样写
52
53delimiter $$
54create trigger log_upd_stu after update
55on select_student for each row 
56if old.height >175 then 
57				insert into student_log values(null,'update',now(),concat(old.id,':',old.height,'---',new.height));
58	end if;
59$$
60delimiter ;          /// 用完之后要修改回来
61
62
63
64如果 触发程序由多条语句组成块。此时就需要使用
65begin
66end 讲语句块包裹
67
68
69展示如下：************ 
70
71①delimiter $$
72
73②create trigger log_upd_stu after update
74on select_student for each row 
75begin
76if old.height >175 then 
77				insert into student_log values(null,'upata',now(),concat(old.id,':',old.height,'---',new.height));
78	end if;
79end
80$$
81
82③delimiter ;
83
84//上面注意 select_student 应该创建好
85
86delimiter $$
87create trigger log_upd_stu after update
88on select_student for each row 
89 begin
90if old.height >175 then 
91				insert into student_log values(null,'upata',now(),concat(old.id,':',old.height,'---',new.height));
92	end if;
93end
94$$
95delimiter ;
96
97然后  updata select_student set money=money+100,height=height+2.0 where id=11; 
98然后  select * from student_log;

触发器，注意事项

1.一个表上的一个事件只能有一个触发器

如果需要，只能将原始的删除掉，新增删除触发器,drop trigger 触发器名字

2.只要事件发生，触发程序就可能执行！一条语句可能

触发多个触发程序！

例如

insert into table_name on duplicate key update set 这个语句有2种触发器流程 insert 成功和失败

before insert trigger

insert 操作失败

before update trigger

update操作

after update

before insert trigger,

insert 操作成功

after insert trigger

下单修改库存示例

 1delimiter $
 2create trigger t4 
 3before 
 4update 
 5on ord 
 6for each row 
 7begin 
 8update goods set num=num +old.much-new.much where gid=new.gid 
 9end$
10create trigger t5 
11after 
12insert 
13on ord 
14declare   #声明变量
15rnum int;
16for each row 
17begin  #rnum是商品表的库存
18select num into rnum from goods where gid=new.gid
19if new.much > rnum then 
20	set new.much = rnum;
21end if;
22update goods set num=num-new.much where gid=new.gid;
23end$
24
25报错如下:
26原因:insert 之后,new 行已经插入到表中,成为事实,改new已经晚了
27
28 create trigger t6
29before 
30insert 
31on ord 
32declare   #声明变量
33rnum int;
34for each row 
35begin  #rnum是商品表的库存
36select num into rnum from goods where gid=new.gid
37if new.much > rnum then 
38	set new.much = rnum;
39end if;
40update goods set num=num-new.much where gid=new.gid;
41end$

存储过程

创建查看

1delimiter $
2create procedure p1()
3begin
4select 'hello' from dual;
5end$
6//查看已有 存储过程
7show  procedure status\G
8//调用存储过程
9call p1$

引入变量

1delimiter $
2create procedure p2() 
3 begin 
4   	 declare age int default 18;
5	 declare height int default 180;
6 select concat('年龄是',age,'身高是',height);
7 end$
8 call p2$

运算

存储过程中 ,变量sql 语句中合法的运算,如+-*/

注意的是,运算的结果,如果赋值给变量

set 变量名 :=expression

p3 变量开始运算

1 create procedure p3() 
2 begin 
3    declare age int default 18;
4    set age :=age + 20;
5    select concat('20年后年龄是',age);
6    end$
7call p3$

if/else 控制结构

1if condition then
2    statement
3else
4    ...
5end;

1create procedure p4() 
2begin 
3    declare age int default 18;
4    if age >=18 then 
5   		 select '已成年';
6    else 
7       select '未成年';
8    end if;
9end$

声明参数类型

语法是[in/out/inout] 参数名参数类型

 1delimiter $
 2create procedure p5(width int ,height int) 
 3begin 
 4    select concat('你的面积是',width * height) as area;
 5    if width > height then 
 6        select '你挺胖';
 7    elseif width <height then 
 8  	  select '你挺瘦';
 9    else 
10  	  select '你挺方';
11    end if;
12    end$
13call p5(200,100)$

控制结构顺序,选择,循环

 1create procedure p7() 
 2begin 
 3    declare total int default 0;
 4    declare num int default 0;
 5    while num<=100 do
 6         set total := total + num;
 7         set num := num +1;
 8    end while;
 9         select total;
10end$

输出性参数案列

in — 传入 out — 输出到这个变量

 1create procedure p8(in n int , out total int) 
 2begin 
 3    declare num int default 0;
 4    set total :=0;
 5    while num < n  do 
 6   		 set num :=num+1;
 7   		 set total :=total + num; 
 8    end while;
 9 end$
10    call p8(100,@sumary);
11    SELECT @sumary$

inout 型

 1delimiter $
 2create procedure p9(inout age int) 
 3begin 
 4    set age := age + 20;
 5end$
 6set @nihao=18$
 7call p9(@nihao)$
 8SELECT @nihao$   //38
 9call p9(@nihao)$
10select @nihao$   //58

case用法

选择

 1delimiter $
 2create procedure p10() 
 3begin 
 4    declare pos int default 0;
 5    set pos := floor(5*rand());
 6    case pos 
 7   		 when 1 then select 'still flying';
 8   		 when 2 then select 'fall in sea';
 9    	 when 3 then select 'in the island';
10  	else 
11  		 	select 'I dont know';
12	end case;
13end$
14call p10()$
15call p10()$
16call p10()$
17call p10()$
18call p10()$

repeat 循环

 1delimiter $
 2create procedure p11() 
 3begin 
 4    declare total int default 0;
 5    declare i int default 0;
 6     repeat 
 7     set i :=i+1;
 8     set total :=total + i;
 9     until i>100 end repeat;
10     select total;
11     end$
12call p11()$   //5151

游标

 1cursor 游标 游标的标志
 2
 3 1条sql,对应N条结果集的资源,取出资源的接口/句柄,就是游标
 4
 5 沿着游标,可以一次取出一行
 6
 7 declare声明 ;declare 游标名cursor_name cursor for select statement
 8
 9open 打开;open 游标名
10
11fetch 取值 ;fetch 游标名 into var1,var2[,,,]
12
13close 关闭; close 游标名;

 1create table goods(gid int,num int ,name varchar(20));
 2insert into goods values(1,14,'pig');
 3insert into goods values(2,19,'dog');
 4insert into goods values(3,20,'cat');
 5delimiter $
 6create procedure p12() 
 7begin 
 8declare row_gid int;
 9declare row_num int;
10declare row_name varchar(20);
11declare getgoods cursor for select gid,num,name from goods; 
12open getgoods;
13    fetch getgoods into row_gid,row_num,row_name;
14    select row_num,row_name; -- pig
15    fetch getgoods into row_gid,row_num,row_name;
16    select row_num,row_name;--  dog
17    fetch getgoods into row_gid,row_num,row_name;
18    select row_num, row_name; -- cat
19    fetch getgoods into row_gid,row_num,row_name;
20    select row_num, row_name; -- ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
21close getgoods;
22end$

循环方式游标

 1create procedure p14()
 2begin 
 3    declare row_gid int;
 4    declare row_num int;
 5    declare row_name varchar(20);
 6    declare cnt int default 0; #声明总行数
 7    declare i int default 0;
 8    declare getgoods cursor for select gid,num,name from goods;
 9    select count(*) into cnt from goods;
10    open getgoods;
11    repeat 
12        set i:= i+1;
13        fetch getgoods into row_gid,row_num,row_name;
14        select row_num,row_name;
15        until i>=cnt end repeat;
16        close getgoods;
17    end$

游标取值越界时,有没有标识,利用标识来结束

在 mysql cursor 中可以 declare continue handler 来操作 1个越界标识

declare continue handler for NOT FOUND statement

 1create procedure p15() 
 2begin
 3    declare row_gid int;
 4    declare row_num int;
 5    declare row_name varchar(20);
 6    declare you int default 1;
 7    declare getgoods cursor for select gid,num,name from goods;
 8    declare continue handler for NOT FOUND set you :=0;
 9    open getgoods;
10    repeat 
11         fetch getgoods into row_gid,row_num,row_name;
12         select row_num,row_name;
13    until  you=0 end repeat;
14    close    getgoods;
15    end$

4行数据 ,最后一行取出2次

repeat 时,fetch—> 没数据 — >触发 NOT FOUND —>set you :=0—>continue—>继续执行后面的sql语句,—> select row_num,row_name—>最后一行,被取出2次

exit continue 区别

取出3行

 1create procedure p16() 
 2begin
 3    declare row_gid int;
 4    declare row_num int;
 5    declare row_name varchar(20);
 6    declare you int default 1;
 7    declare getgoods cursor for select gid,num,name from goods;
 8    declare exit handler for NOT FOUND set you :=0;
 9    open getgoods;
10    repeat 
11         fetch getgoods into row_gid,row_num,row_name;
12         select row_num,row_name;
13    until  you=0 end repeat;
14    close    getgoods;
15    end$

游标循环正确逻辑处理

取三条

先fetch 一行.

 1delimiter $
 2create procedure p17() 
 3begin
 4    declare row_gid int;
 5    declare row_num int;
 6    declare row_name varchar(20);
 7    declare you int default 1;
 8    declare getgoods cursor for select gid,num,name from goods;
 9    declare continue handler for NOT FOUND set you :=0;
10    open getgoods;
11     fetch getgoods into row_gid,row_num,row_name;
12    repeat 
13         select row_num,row_name;
14         fetch getgoods into row_gid,row_num,row_name;
15    until  you=0 end repeat;
16    close    getgoods;
17    end$
18call p17()$

while 逻辑

repeat 根 do while 差不多

 1delimiter $
 2create procedure p18() 
 3begin
 4    declare row_gid int;
 5    declare row_num int;
 6    declare row_name varchar(20);
 7    declare you int default 1;
 8    declare getgoods cursor for select gid,num,name from goods;
 9    declare continue handler for NOT FOUND set you :=0;
10    open getgoods;
11     fetch getgoods into row_gid,row_num,row_name;
12    while you=1 do  
13         select row_num,row_name;
14         fetch getgoods into row_gid,row_num,row_name;
15end while;
16    close    getgoods;
17    end$
18call p18()$
19delimiter $
20create function rand_string(n int)
21returns varchar(255)
22begin 
23declare  chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
24declare return_str varchar(255) default '';
25declare i int default 0;
26    while i < n do
27    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
28    set i =i+1;
29    end while;
30    return return_str;
31end$
32select  rand_string(10)$ //这么用

show procedure status like procedure_name\G
show create procedure procedure_name \G
通过 information_schema.Routines 得到存储过程的定义

select * from routines where ROUTINE_NAME =procedure_name\G

当sql 状态是23000 错误的时候就设置变量

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ set @x2=1;

实战案例

处理历史数据

一个功能上线要处理历史数据

 1DROP PROCEDURE IF EXISTS `pr_order_proof`;
 2CREATE PROCEDURE `pr_order_proof`()
 3BEGIN
 4declare total_count int default 0;
 5declare new_group_id int default 0;
 6declare item_total int default 0;
 7declare group_begin_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
 8declare group_end_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
 9SELECT COUNT(0) INTO total_count FROM order_proof WHERE `status` = 1 AND `group_id` = 0;
10WHILE total_count DO
11     SET item_total = IF(total_count > 100,100,total_count);
12     SET total_count = total_count - item_total;
13     INSERT INTO `order_proof_group`(`type`,`total_count`,`created_at`) VALUES(1,item_total,CURRENT_TIMESTAMP);
14     SELECT MAX(`id`) INTO new_group_id FROM `order_proof_group`;
15     UPDATE `order_proof` SET `group_id` = new_group_id WHERE `status` = 1 AND `group_id` = 0  ORDER BY `id` LIMIT item_total;
16     SELECT MIN(created_at),MAX(created_at) INTO group_begin_at,group_end_at FROM order_proof WHERE `group_id` = new_group_id;
17     UPDATE `order_proof_group` SET `begin_at` = group_begin_at,`end_at`=group_end_at WHERE `id` = new_group_id;
18END WHILE;
19END;
20CALL `pr_order_proof`();
21DROP PROCEDURE IF EXISTS `pr_order_proof`;

处理历史数据demo2

 1DELIMITER ;;
 2CREATE PROCEDURE init_admin_operate_user_relation()
 3BEGIN
 4  DECLARE res_user_id INT;
 5  DECLARE res_admin_id INT;
 6  DECLARE done INT DEFAULT 0;
 7	  --  取非机器人的用户记录
 8  DECLARE get_user CURSOR FOR SELECT user_id FROM `user` WHERE refer_id !=3;
 9  DECLARE EXIT HANDLER FOR NOT FOUND SET done = 1;
10  open get_user;
11	REPEAT
12         FETCH get_user INTO res_user_id;
13      select
14            case ceil(RAND()*10)
15               when  1  then 4
16               when  2 then 4
17               when 3  then 10
18               when 4  then 10
19               when 5 then 27
20               when 6 then 27
21               when 7 then 28
22               when 8 then 28
23               when 9 then 29
24               when 10 then 29
25               end  as tmp
26               INTO res_admin_id;
27         INSERT INTO `admin_operate_user_relation` (`user_id`) VALUES(res_user_id) ON DUPLICATE KEY UPDATE  admin_id =  res_admin_id;
28  UNTIL  done=1 END REPEAT;
29  CLOSE  get_user;
30END;;
31DELIMITER ;
32CALL init_admin_operate_user_relation();
33
34DROP PROCEDURE  init_admin_operate_user_relation;

大表的更新和删除

 1DELEMITER $$
 2USE `DATABASE_NAMNE`$$
 3DROP PROCEDURE IF EXISTS `p_delete_rows`$$
 4CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `p_delete_rows`
 5BEGIN
 6    DECLARE v_rows INT;
 7    SET v_rows=1;
 8    WHILE v_rows > 0
 9    DO 
10        DELETE FROM test_table where id  >=10000 and id <=19000 LIMIT 5000;
11        SELECT ROW_COUNT() INTO v_rows;
12        select SLEEP(5);
13    END WHILE;    
14END$$

mysql中的ROW_COUNT()可以返回前一个SQL进行UPDATE，DELETE，INSERT操作所影响的行数。

用户与权限管理

权限类别：

库级别
表级别
字段级别
管理类
程序类

管理类:

CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS

程序类 :

FUNCTION
PROCEDURE
TRIGGER

CREATE, ALTER, DROP, EXCUTE

库和表级别

ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能够把自己获得的权限赠经其他用户一个副本

数据操作：

SELECT
INSERT
DELETE
UPDATE

字段级别：

SELECT(col1,col2,…)
UPDATE(col1,col2,…)
INSERT(col1,col2,…)

所有权限：ALL PRIVILEGES, ALL

元数据数据库：mysql

授权表：

db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv

用户账号：

‘USERNAME’@’HOST’
- @’HOST’: 主机名； IP地址或Network; 通配符：%, _: 172.16.%.%
创建用户：CREATE USER
- CREATE USER ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘password’]；
查看用户获得的授权：SHOW GRANTS FOR
- SHOW GRANTS FOR ‘USERNAME’@’HOST’
用户重命名：RENAME USER
- RENAME USER old_user_name TO new_user_name
删除用户：DROP USER ‘USERNAME’@’HOST’
- drop user chen;
修改密码：
- (1) SET PASSWORD FOR
  - set password for USER_NAME=password(‘123456’);
- (2) UPDATE mysql.user SET password=PASSWORD(‘your_password’) WHERE clause;
- (3) mysqladmin password mysqladmin [OPTIONS] command command…. -u, -h, -p
忘记管理员密码的解决办法：
- (1) 启动mysqld进程时，为其使用：—skip-grant-tables —skip-networking
- (2) 使用UPDATE命令修改管理员密码
- (3) 关闭mysqld进程，移除上述两个选项，重启mysqld;

授权：GRANT

1GRANT priv_type[,…] ON [{table|function|procedure}] db.{table|routine} TO 'USERNAME'@'HOST' [IDENTIFIED BY 'password'][REQUIRE SSL] [WITH with_option]
2
3with_option:
4    GRANT OPTION
5    | MAX_QUERIES_PER_HOUR count
6    | MAX_UPDATES_PER_HOUR count
7    | MAX_CONNECTIONS_PER_HOUR count
8    | MAX_USER_CONNECTIONS count

取消授权：REVOKE

1REVOKE priv_type [(column_list)][, priv_type [(column_list)]] … ON [object_type] priv_level FROM user [, user] …
2revoke 权限 on 库名.表名 from ‘chen’@’%’;

links

MySQL变量的使用

https://www.cnblogs.com/Brambling/p/9259375.html

Mysql使用篇(基础概念、视图、触发器、存储过程、用户与权限管理)