mysql的安全和结构

1.用户权限管理

用户管理

mysql需要客户端下连接认证才能对服务器进行操作，用户信息被保存在mysql数据库下的user表中
select *from mysql.user
user 和host为复合主键，只能有一个用户
user表示用户的用户名
host 代表本质是允许访问的客户端（IP或主机地址），host如果为%则所有的用户都可以访问

创建用户

理论上讲可以采用两种方式创建用户:
1、直接使用root用户在mysql.user,表中插入记录（不推荐)
2、专门创建用户的 SQL指令
语法：create user 用户名 identified by’明文密码’;
用户：用户名@主机地址
主机地址：‘’或‘%’
create user ‘user1’@‘%’ identified by ‘123456’；
create user ‘user2’；（没有密码的用户，很危险）

删除用户

drop user ‘用户名’@host
drop user ‘user2’@ ‘%’；

修改用户密码

password（）函数
1.指令
语法 set password for 用户 =password （‘新的明文密码’）
set password for ‘user1’@‘%’=password（‘654321’）；
2 update 更新
语法：update mysql.user set password = password('新的明文密码‘）where user = “and host”

权限管理

1.数据权限 （select 、upload、delete、insert）
2.数据权限（create、drop）
3.管理权限（create user、grant、revoke）通常只给管理员
权限处理不需要刷新，马上处理

授予权限grant

语法 grant 权限列表 on 数据库/* 表名 /* to 用户；
权限列表：用逗号分隔，但可以用all privileges 代表全部权限
数据库.表名可以是单表（数据库名.表名）也可以为今天某个数据库(数据库，），也可以为整库（，*）
grant select on mydb.my_student to ‘user1’@’%’
user1只能看到my_studnet这一张表

取消权限：revoke

语法 ：revoke 权限列表 on 数据库/* 表名 /* from 用户；
revoke select on my_student from ‘user1’@’%’;

flush刷新权限

语法：flush privileges；

忘记密码

1.停止服务
net stop mysql
2.重新启动服务
mysqld.exe --skip-grant-tables
3.当前启动的服务器没有权限概念，非常危险，任何人都可以登录mysql且为root权限
4.修改密码
update mysql.user set password =password(‘root’) where user=‘root’ and host =‘localhost’;
5.关闭服务器
Ctrl＋shift +ESC 找到进程中的mysqld.ese
6.启动服务器
net start mysql；

外键

一张表（主表）的字段保存值指向另一张表（从表）的主键

添加外键

1.在建立时添加
语法：在字段后添加一条语句
[constraint `外键名`] foreign key(外键名) rederences 主键(主键);
–反引号
2.创建表之后添加外键
alter table 从表add[constraint `外建名`] froeign key(外键字段) rederences 主表(主键)
alter table my_student add constraint `student_class` foreign key(class_id) references my_class(class_id);
外键名字可指定
MUL 多索引

修改 删除并添加外键

外键不允许修改，只能 删除后添加
语法：alter table 从表 drop foreign key 外键名字
alter table my_student drop foreign key student_class
外键删除会自动增加一个索引但外键删除只会删除自己
删除索引
alter table 表名 drop index 索引名字；
要求
1.外键字段需要保证与关联的主表的主键字段类型完全一致；
2.基本属性相同
3.如果在表后加外键，对数据还有要求
4外键只能使用innodb存储引擎，myisam不支持
外键约束
1、当一个外键产生时:处键所在的表(从表)会受制于主表数据的存在从而导致数据不能进行某些不符合规范的操作（不能插入主表不存在的数据);
2、如果一张表被其他表外键引入，那么该表的数据操作就不能随意:必须保证从表数据的有效性（不能随便删除一个被从表引入的记录)）
可以在创建外键的时候，对外键约束进行选择性的操作。
基本语法:
add foreign key(处键完段)references主表(主键) on 约束模式;
约束模式有三种:
1、district:严格模式，默认的，不允许操作,
2、cascade:级联模式，一起操作，主表变化，从表数据跟着变化
3、set null:置空模式，主表变化（删除)，从表对应记录设置为空:前提是从表中对应的外键字段允许为空
通常在进行约束时候的时候，需要指定操作: update和 delete
常用的约束模式: on update cascade, on delete set null，更新级联，删除置空,
约束作用：保证数据的完整性
正是因为外键有非常强大的数据约束作用，而且可能导致数据在后台变化的不可控。导致程序在进行设计开发逻辑的时候，没有办法去很好的把握数据(业务)，所以外键比较少使用。

视图

视图创建

视图的本质是SQL指令( select 语句)
基本语法:create view视图名字as select 指令; //可以是单表数据，也可以是连接查询，联合查询或者子查询。

--创建视图
create view student_class_v as
select s.* ,c.namelfrom my_student as s left joinmy_class as c
on
s.class_id = c.class_id;

使用视图

视图是―张虚拟表:可以直接把视图当做“表”操作，但是视图本身没有数据，是临时执行select 语句得到对应的结果。视图主要用户查询操作。
基本语法: select字段列表 from视图名字[字句]I

修改视图

修改视图:本质是修改视图对应的查询语句
基本语法:alter view视图名字as新select 指令;

alter view student_class_v as
select *from my_student as s left joinmy_class as c
using(class_id)；

删除视图

drop view 视图表

drop view student_class_v

transaction 安全

transaction概念

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言书写的用户程序的执行所引起。事务由事务开始(begintransaction)和事务结束(end transaction)之间执行的全体操作组成。

事务基本原理

基本原理:Mysql.允许将事务统一进行管理（存储引擎INNODB)，将用户所做的操作，暂时保存起来，不直接放到数据表（更新)，等到用于确认结果之后再进行操作。
事务通常自动提交，但也可以手动事务

自动事务

自动事务:autocommit，当客户端发送一条SQL指令（写操作:增删改）给服务器的时候，服务器在执行之后，不用等待用户反馈结果，会自动将结果同步到数据表。
利用两个客户端，一个客户端执行SQL指令，另外一个客户端查看执行结果,系统做了额外的步骤来帮助用户操作，系统是通过变量来控制的
show variables like ‘autocommit’;
set autocommit =off;
一旦自动事务关闭，那么需要用户提供是否同步的命令。
Commit:提交（同步到数据表:事务也会被清空）
Rollback:回滚（清空之前的操作，不要了)

手动事务

手动事务:不管是开始还是过程还是结束都需要用户（程序员)，手动的发送事务操作指令来实现。
手动事务对应的命令:
1、start transaction; //开启事务:从这条语句开始，后面的所有语句都不会直接写入到数据表（保存在事务日志中)
2、事务处理:多个写指令构成
3、事务提交: commit/rollback，到这个时候所有的事务才算结束

开启事务

start transaction

回滚点

回滚点:savepoint，当有一系列事务操作时,而其中的步骤如果成功了,没有必要重新来过，可以在某个点（成功)，设置一个记号(回滚点)，然后如果后面有失败，那么可以回到这个记号位置。
增加回滚点:savepoint回滚点名字;/字母数字和下划线构成
回到回滚点:rollback to回滚点名字;//那个记号（回滚点）之后的所有操作没有了
注意:在一个事务处理中，如果有很多个步骤，那么可以设置多个回滚点。但是如果回到了前面的回滚点，后面的回滚点就失效﹔

事务特点

原子性（atomicity）
一致性（consistency）
隔离性（isolation）被索引的值在其他用户不能修改，如果无索引则该字段所有都被锁定
持久性（durability）

变量

系统变量

show variables [like ‘pattern’];
语法：select @@变量名
修改系统变量:分为两种修改方式s
1、局部修改（会话级别):只针对当前自己客户端当次连接有效
基本语法: set变量名=新值;
2、全局修改:针对所有的客户端(仅对新客户端有效)，“所有时刻”都有效。
基本语法:set global变量名=值; l l set @@global.变量名=值}

对话变量

也叫用户变量与mysql客户端绑定，设置的变量，只对当前用户有效
语法：set @变量名=值
在mysal.中因为没有比较符号==，所以是用=代替比较符号:有时候在赋值的时候，会报错:mysql为了避免系统分不清是赋值还是比较:特定增加一个变量的赋值符号:∶=（突然想起海象运算符）
mysql没有数组所以一对一
1、赋值且查看赋值过程:select @变量1:=字段1,@度量2:=字段2 from 数据表where条件;
2、只赋值，不看过程: select字段1，字段2… from 数据源 where条件 into @变量1，@变量2…

查看变量

select @变量

局部变量

作用范围在begin到end语句块之间。在该语句块里设置的变量，declare语句专门用于定义局部变量。
1、局部变量是使用declare关键字声明
2、局部变量declare语句出现的位置一定是在 begin和end之间(beginend.,是在大型语句块中使用:函数/存储过程/触发器)
3、声明语法: declare变量名数据类型[属性];

流程结构

If分支

基本语法

lf在Mysql中有两种基本用法v
1、用在select查询当中，当做一种条件来进行判断,基本语法:if(条件,为真结果,为假结果)
select *,if(age>20,‘true’,‘flase’)as judge from my_student
–会出现一条数据来
2、用在复杂的语句块中（函数/存储过程/触发器）
if 条件表达式 then
满足条件后执行语句；
end if；

复合语法

if 条件表达式 then
满足条件后执行语句；
else
不满足条件后执行语句；
if then……
end if；

while

while 条件 do
循环
end while；

结构标识符

结构标识符:为某些特定的结构进行命名，然后为的是在某些地方使用名字,
基本语法
标识名字:while条件do
循环体
End while[标识名字];
标识符的存在主要是为了循环体中使用循环控制。在mysql中没有continue和 break，有自己的关键字替代:
lterate:迭代，就是以下的代码不执行，重新开始循环（continue）Leave:离开，整个循环终止(break)
标识名字:
While条件do
lf 条件判断thene
循环控制;
lterate/leave标识名字;
End if;
循环体
End while [标识名字];

函数

select 函数名（参数列表）

内置函数

字符串函数

Char length():判断字符串的字符数
Length():判断字符串的字节数（与字符集)
Concat():连接字符串
Instr():判断字符在目标字符串中是番存在，存在返回其位置（从一开始），不存在返回0
Lcase():全部小写
Left():从左侧指定位置开始截取字符串。
Ltrim():消除左边对应的空格
Mid():从中间指定位置开始截取，如果不指定截取长度，直接到最后

时间函数

Now():返回当前时间，日期时间
Curdate():返回当前日期
Curtime():返回当前时间
Datediff(“大时间”，“小时间”):判断两个日期之间的天数差距，参数日期必须使用字符串格式（用引号）
Date add(日期，interval 时间数字 type):进行时间的增加
select date add(2000-1-1,interval 20 year);
type：day/hour/minute/second
unix_timestamp()时间戳
from_unixtime()将时间戳转化为时间

数学函数

Abs():绝对值
Ceiling():向上取整,
Floor():向下取整
Pow():求指数，多少次方
Rand():获取一个随机数（0-1之间）
Round():四舍五入函数

其他函数

Md5():对数据进行md5加密(mysal,中的md5与其他任何地方的md5加密出来的内容是完全相同的)。
Version():获取版本号
Databse():显示当前所在数据库
uuID():生成一个唯一标识符（自增长):自增长是单表唯一，uUID是整库（数据唯一同时空间唯一）

自定义函数

1.自定义函数是属于用户级别的:只有当前客户端对应的数据库中可以使用
2.可以在不同的数据库下看到对应的函数,但是不可以调用。
3.函数因为必须规范返回值:那么在函数内部不能使用select 指令: select 一旦执行就会得到一个结果（result set ): select字段into@变量;(唯一可用）。

自定义函数:实现某种功能的语句块（由多条语句组成)
1、函数内部的每条指令都是一个独立的个体:需要符合语句定义规范:需要语句结束符分号;
2、函数是一个整体，而且函数是在调用的时候才会被执行，那么当设计函数的时候，意味着整体不能被中断;
3、Mysql一旦见到语句结束符分号，就会自动开始执行
解决方案:在定义函数之前，尝试修改临时的语句结束符基本语法:delimiter
修改临时语句结束符:delimiter新符号[可以用系统内置$$]
中间为正常sQL指令:使用分号结束（系统不会执行:不认识分号） 使用新符号结束
修改回语句结束符:delimiter ；

创建函数

修改语句结束符
create function 函数名(形参)returns 返回值类型
begin
函数体
return 返回值数据；数据必须与返回值类型一致
end
语句结束符
修改语句结束符

delimiter $$
create function my_func1() returns int
begin
	return 10;
end
$$
delimiter ;
--若只需返回值return可以不需要begin 和end
create function my_func2() returns int
	return 10;
--形参定义，名在前数据类型在后
create function my_func3(int_1 int,int_2 int) returns int
	return int_1+int_2;

查看函数

show function status;
show create function my_func1;

删除函数

drop function my_func1;

eg

delimiter $$
create function my_first(value_end int) returns int
begin
	declare result int default 0;
	declare st int defalut 1;
	if result %5 =0	then
		set st =st+1; --mysql居然没有++
		iterate my_first;
	end if;
	while value_end>= result do
		set result = result +st;
		set st=st+1;
	end while my_first
	return result;
end
$$
delimiter ;

变量作用域

局部作用域

使用declare关键字声明(在结构体内:函数/存储过程/触发器)，而且只能在结构体内部使用
declare 关键字声明的变量没有任何符号修饰，就是普通字符串，如果在外部访问该变量，系统会自动认为是字段。

会话作用域

用户定义的，使用@符号定义的变量，使用set关键字
会话作用域:在当前用户当次连接有效，只要在本连接之中，任何地方都可以使用（可以在结构内部，也可以跨库)

全局作用域

所有的客户端所有的连接都有效:需要使用全局符号来定义,set global变量名=值;
Set @@global.变量名=值;
通常，在SQL编程的时候，不会使用自定义变量来控制全局。一般都是定义会话变量或者在结构中使用局部变量来解决问题。

存储过程 store procedure

简称过程

与函数的区别

相同点
1、存储过程和函数目的都是为了可重复地执行操作数据库的sql语句的集合。
2、存储过程函数都是一次编译,后续执行
不同点
1、标识符不同。函数的标识符为FUNCTION，过程为:PROCEDURE。
2、函数中有返回值，且必须返回，而过程没有返回值。
3、过程无返回值类型,不能将结果直接赋值给变量;函数有返回值类型,调用时,除在select中，必须将返回值赋给变量。
4、函数可以在select语句中直接使用，而过程不能:函数是使用select 调用，过程不是。

操作

创建过程

create procedure 过程名字(参数列表)
begin
过程
end
结束符
若只有一条过程可以不用begin和end

eg

delimiter $$
create procedure my_pro()
begin 
	declare i int defulit 1;
	set @sum =0;
	while i<=100 do
		set sum=sum+i;
		set i=i+1;
	end while;
	select @sum;
end
$$
delimiter ;

查看过程

show procedure status [like ‘pattern’];
show create procedure 过程名字;
与函数一致

调用过程

语法：call 过程名字();

删除过程

drop procedure 过程名;与函数一致

存储过程形参类型

In

表示参数从外部传入到里面使用（过程内部使用):可以是直接数据也可以是保存数据的变量

Out

表示参数是从过程里面把数据保存到变量中，交给外部使用:传入的必须是变量。
如果说传入的out变量本身在外部有数据，那么在进入过程之后，第一件事就是被清空，设为NULL

Inout

数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返还给外部

eg

set @n1=1;
set @n2=2;
set @n3=3;
delimiter $$
create procedure my_pro1(in int_1 int ,out int_2 int,inout int_3 int)
begin 
	select int_1,int_2,int_3;--int_1=1 int_2=null int_3=3
	set int_1=10;
	set int_2=100;
	set int_3=1000;
	select int_1,int_2,int_3;--int_1=10 int_2=100 int_3=1000
	select @n1,@n2,@n3;--@n1=1 @n2=2 @n3=3
	set @n1='a';
	set @n2='b';
	set @n3='c';
	select @n1,@n2,@n3;	--@n1=a @n2=b @n3=c
end
$$
delimiter ;
call my_pro(@n1,@n2,@n3);
select @n1,@n2,@n3;--@n1=a @n2=100 @n3=1000
--在end后会找到out和inout的数据将形参赋值给实参 

触发器trigger

特殊的存储类型 触发器通过事件进行触发而被执行，（js中的事件）
触发器:提前给某张表的所有记录（行）绑定一段代码，如果改行的操作满足条件（触发)，这段提前准备好的代码就会自动执行。
作用
1、可在写入数据表前，强制检验或转换数据。(保证数据安全),绕过？
2、触发器发生错误时，异动的结果会被撤销。(如果触发器执行错误，那么前面用户已经执行成功的操作也会被撤销:事务安全),
3、部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
4、可依照特定的情况，替换异动的指令(INSTEAD OF)。(mysal.不支持)
优点
1、触发器可通过数据库中的相关表实现级联更改。(如果某张表的数据改变，可以利用触发器来实现其他表的无痕操作[用户不知道])
2、保证数据安全:进行安全校验
缺点
1、对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度
2、造成数据在程序层面不可控。(PHP层）

创建触发器

语法：

create trigger 触发器名字 触发时机 触发事件 on 表 for each row
begin

end

触发对象 on 表 for each row ，触发器绑定所有行，当每一行发生指定的改变时，则会触发触发器

触发时机
触发时机:每张表中对应的行都会有不同的状态，当SQL指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态:数据操作前和操作后
Before:在表中数据发生改变前的状态
After:在表中数据已经发生改变后的状态
触发事件
触发事件:mysql.中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改)
lnsert:插入
Update 更新
Delete: 删除
注意事项
一张表中每个绑定的触发事件对应的触发器只能有一个：一张表中只能有一个对应的after insert 触发器
所以一张表对应的触发器最多只能有6个 ：
before insert ,before update ,before delete,after lnsert ,after Update,after Delete

eg

--货物表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null,
inv int
)charset utf8;
--商品表
create table my_orders(
id int primary key auto_increment,
goods_id int not null,
goods_num int not null)
charset utf8;
insert into my_goods('null','phone',1000),('null','computer',5000),('null','television',3000)
--创建触发器
delimiter $$
create trigger after_insert_order after insert on my_orders for each row
begin 
	update my_goods set inv =inv -1 where id =1;--每次插入减一
end
$$
delimiter ;
--当发生插入操作后，goods会减少

查看触发器

show trigger \G
show create trigger 触发器名字

触发触发器

等达成条件即可

删除

drop trigger 触发器名字

记录关键字: new、old.

触发器针对的是数据表中的每条记录（每行)，每行在数据操作前后都有一个对应的状态，触发器在执行之前就将对应的状态获取到了，将没有操作之前的状态（数据）都保存到old关键字中，而操作后的状态都放到new中。
在触发器中，可以通过old和new来获取绑定表中对应的记录数据。
基本语法:关键字.字段名。
old和new并不是所有触发器都有:
Insert:插入前全为空，没有old
Delete:清空数据，没有new

eg

delimiter $$
create table auto after insert on my_orders for each row
begin
	update my_goods set inv = inv - new.goods_num where id = new.good_id
end
$$
delimiter ;
--判断库存
delimiter $$
create trigger out_er before insert on my_orders for each row
begin
--取出库存数据进行判断
select inv from my_goods where id = new.goods_id into @inv;
	if @inv < new.goods_num then
		--中断操作:暴力解决，主动出错,前面的操作都不会执行
		insert into XX values( 'XX');
	end if;
end
$$
delimiter ;