数据备份

将数据里的数据进行保存到外部文件，从而在数据库内部数据丢失或者出错的情况下能够通过备份文件进行还原操作，从而将损失降低到最小。

对单表内的纯数据进行备份

将表中的数据（不包含结构，没有字段头信息）保存到外部文件。

1. 当外部文件不存在的时候，系统会自动创建该文件。
2. 如果外部文件存在，那么备份失败。

备份语法：select *[字段列表] into outfile 文件路径 [字段处理][行处理] from 表名

还原语法：load data infile 文件名 into table 表名 [字段处理][行处理]

利用数据备份和还原的主意事项：

1. 数据还原的过程中，一定要按照数据备份时的结构进行操作
2. 备份的字段信息应该和还原时的字段信息一致

字段处理：fields，对备份或者还原的字段数据进行相应的处理

terminated by：以什么结束 \t

enclosed by：以什么包裹，默认没有

escaped by：碰到转义字符怎么处理

行处理：lines

terminated by：以什么结束行 \r\n

starting by：以什么开始，默认没有

备份

还原

对表的结构和数据进行备份

会将表中的数据以及表的结构全部备份下来。备份的是SQL语句。

mysql提供了一个客户端对数据进行备份：mysqldump

使用mysqldump进行备份的过程？

1. 连接认证
2. a)–h：host
3. b)–P：Port
4. c)–u：username
5. d)–p：password
6. 进行备份

备份语法：在控制台下，使用命令

mysqldump/mysqldump.exe –h –P –u –p 数据库 [数据表] > 文件路径

备份文件内容

数据还原

mysql.exe进行还原，外部还原

mysql/mysql.exe –h –P –u –p 数据库 < 文件路径

数据库的备份和还原

在数据库内部进行还原

语法：source 数据源（文件路径）

以上备份方式是全表备份，往往在大项目的时候使用的比较少，因为效率比较低。

mysql还支持增量备份，对mysql的日志文件进行更新部分的备份。

安全问题

思考：李白欠李清照500，李白发工资了，想通过转账的形式把钱还给李清照。

personal_bank表：id，用户名，账号信息，余额

1. 首先，从李白账户里扣掉500
2. 第二步，将500添加到李清照的账户

当第一步操作完之后，第二步没来得及处理就失败了。

如何解决以上问题？

事务处理

事务：为了完成某个功能的一系列操作就称之为一组事务。

事务能干啥？

保证一系列操作是一个完整的整体，如果其中某个部分出现错误，那么整个过程全部失效，只有当全部操作都成功的时候，才会最终成功。

mysql是如何支持事务的？

1. 自动提交：默认不采用事务，即每条SQL语句执行完毕之后，都自动提交
2. 手动提交：系统不自动提交，需要用户在操作完成之后，手动的提交结果。

事务原理

自动提交

手动提交

mysql默认是自动提交的

查看：show variables like ‘autocommit%’;

修改自动提交机制

使用事务的客户端与新开的客户端结果不同的原因

1. 手动提交事务的客户端，并没有真正删除数据库里的数据
2. 手动提交事务的客户端，执行完SQL操作之后，将结果保存到日志文件中，然后将执行结果返回给客户端，所以客户端看到结果是执行成功的
3. 手动提交事务的客户端，再次去查询数据的时候，得到并不是最终数据库的完整数据，而是数据库的数据+日志文件里的操作结果结合之后的数据信息

如何执行手动提交？

事务失败，不进行处理：回滚，rollback，将当前事务清楚，所有之前的执行结果，都取消

事务成功，写入数据库：提交，commit，将当前事务从日志文件写入到最终数据库

以上修改自动提交的机制不是很实用，所以一般不这么用，而是使用mysql提供另外的事务机制：transaction

1. 把自动提交给启动

2. 在需要使用事务的时候，先开启事务
3. a)告诉服务器档期操作是事务类型，不要直接写入表，写到日志文件

start/begin transaction

3. 执行SQL语句

4. 事务处理完毕

a)回滚：rollback，回到开启事务的地方，什么都没做

b)提交：commit，将日志文件内容写到数据库

事务处理流程：

1. 开启：start/begin  transaction
2. 执行SQL语句（多条）
3. 设置回滚点：savepoint SP名称
4. 执行SQL语句（多条）
5. 第四条执行错误，需要回到第四条执行的地方：rollback to SP名称
6. a)将SP回滚点之后写入到日志的操作结果给清楚，保留了前面部分
7. 执行SQL语句（多条）
8. 选择结果处理：rollback或者commit

只有InnoDB支持事务，Myisam不支持事务。

触发器

当某个时间发生的时候，自动触发的一段代码。

触发器由事件主体（对象），事件类型，事件触发时间

用户购物

商品表：商品信息，商品库存

订单表：订单信息，商品信息，商品数量

当用户下订单之后，商品表的商品库存需要减少

用户退订，商品的商品库存需要增加

1. 订单表增加记录（有商品数量）
2. 商品表减少库存（订单表对应商品信息变化）

语法：

create trigger 触发器名字 事件触发时间 事件类型 on 表 for each row

[begin]

//触发体，如果触发体里只有一条语句，那么begin和end可以没有

//如果触发体里有多条语句，那么begin和end是必须的

[end]

事件触发时间：before在操作之前，after在操作之后

事件类型：insert插入，update更改，delete删除

创建一个最简触发器

调用触发器：系统自动调用

-- 当订单表插入一条数据后，触发定义的触发器order_goods_t

触发器里的操作不能固定，应当根据当前实际变更的数据信息来进行操作。

old：代表被操作之前的记录对应是数据，触发器针对的记录被变更之前

new：代表被操作之后的记录对应的数据，触发器针对的记录被变更之后

删除触发器：

语法：drop trigger 触发器名字

验证

在触发器中使用对应的触发触发器的记录

触发器使用

触发器有多少种？

六种：触发时间  和 触发事件类型 的积

before/after  insert/update/delete;

哪些事件是没有old，哪些事件没有new

insert操作没有old，delete没有new

一张表最多可以有6个触发器。

作业：前提是一张表有六个触发器

1. insert into表名 (值列表) on duplicate key update，会执行什么触发器？
2. replace into表名(值列表)

if结构

语法：

if 条件判断 then

执行相应于巨

end if;

如果商品表中的数量不足了，订单则不应该生成？触发器该怎么操作。

在订单表插入数据之前，先对商品表商品数量进行判断。

1. 因为是在插入之前先执行触发器，那么还没有在订单表里插入数据
2. 判断失败之后，触发器不能中断操作，没有办法限制后续插入订单表的SQL语句的执行

变量

SQL分为两种变量：系统变量，自定义变量

系统变量：autocommit，auto_increment_increment…

访问：系统变量可以直接访问

修改：set 系统变量名 = 值（通过该方式修改的变量全部都是会话级别）

有些系统变量不能直接修改，需要通过global关键字：set global 变量名 = 值

set @@变量名 = 值；

自定义变量：全局变量

语法：set @变量名 = 值；

访问自定义变量：

语法：select @变量名;

修改变量

语法：set @变量名 = 值;

与关键字冲突或者与系统变量名字一致，不受影响

在mysql中=多用于比较运算，mysql为了区分比较符号和赋值符号，特意定义了一个赋值符号：   :=

从数据记录里获取数据并保存到相应的变量

语法：select 字段列表 from 表名 where条件 limit 1 into 变量列表

1. 变量列表必须与字段列表数量一致
2. select语句所获得的记录只能是一条

select只能获取一行记录对变量进行赋值

自定义变量是会话级别（当前客户端的当前连接），但是可以跨数据库

函数

SQL里函数分为两类：系统函数，自定义函数

系统函数：系统已经定义好，可以直接调用

自定义函数：需要在使用之前先定义

常用系统函数

数值函数

Abs(X)，绝对值 abs(-10.9) = 10.9

Format(X，D)，格式化千分位数值 format(1234567.456, 2) = 1,234,567.46

Ceil(X),向上取整 ceil(10.1) = 11

Floor(X),向下取整 floor (10.1) = 10

Round(X),四舍五入去整

Mod(M,N) M%N M MOD N 求余 10%3=1

Pi(),获得圆周率

Pow(M,N) M^N

Sqrt(X)，算术平方根

Rand(),随机数

TRUNCATE(X,D) 截取D位小数

时间日期函数

Now(),current_timestamp(); 当前日期时间

Current_date();当前日期

current_time();当前时间

Date(‘yyyy-mm-dd HH;ii:ss’);获取日期部分

Time(‘yyyy-mm-dd HH;ii:ss’);获取时间部分

Date_format(‘yyyy-mm-dd HH;ii:ss’,’ %D %y %a %d %m %b %j');

Unix_timestamp();获得unix时间戳

From_unixtime();//从时间戳获得时间

字符串函数

LENGTH (string )   //string长度，字节

CHAR_LENGTH(string)    //string的字符个数

SUBSTRING (str , position [,length ])   //从str的position开始,取length个字符

REPLACE (str ,search_str ,replace_str )   //在str中用replace_str替换search_str

INSTR (string ,substring )   //返回substring首次在string中出现的位置

CONCAT (string [,... ])   //连接字串

CHARSET(str)  //返回字串字符集

LCASE (string )  //转换成小写

LEFT (string ,length )   //从string2中的左边起取length个字符

LOAD_FILE (file_name )   //从文件读取内容

LOCATE (substring , string [,start_position ] )   //同INSTR,但可指定开始位置

LPAD (string ,length ,pad )   //重复用pad加在string开头,直到字串长度为length

LTRIM (string )  //去除前端空格

REPEAT (string ,count )   //重复count次

RPAD (string ,length ,pad)   //在str后用pad补充,直到长度为length

RTRIM (string )   //去除后端空格

STRCMP (string1 ,string2 )   //逐字符比较两字串大小，按照对应的校对集

流程函数：

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END   多分支

IF(expr1,expr2,expr3)  双分支。如果第一个表达式成立，那么执行第二个表达式，否则执行第三个表达式

聚合函数

Count()

Sum();

Max();

Min();

Avg();

Group_concat()

其他常用函数

Md5();//对字符串进行md5加密，加密成32位字符串

//虽然md5是mysql的函数，PHP也有md5，两个函数不一样，但是加密生成的结果是一致。

Default();//默认值

自定义函数

语法：create function 函数名(形式参数) returns 返回值类型

begin

-- 函数体

-- return 跟返回值类型一致的数据或者变量

end

形参：变量名 数据类型,变量名 数据类型

返回值类型：数据类型

定义函数

调用函数

语法：select 函数名（实际参数）;

直接传值调用

传入变量

变量类型和范围

自定义变量分为全局变量和局部变量

全局变量：set @变量名

局部变量：在函数内部定义，declare 变量名 数据类型 默认值

范围：变量的范围跟js中变量的范围是一致

全局变量在函数内外都可以使用

局部变量只能在函数内部使用

在函数内部也可以定义全局变量：set @变量 = 值

定义函数

调用后变量的查看

删除函数

语法：drop function 函数名；

查看函数

1. 查看所有函数

show function status;

2. 查看指定函数

show create function 函数名；

函数流程控制

分支结构和循环结构

分支结构：if结构

语法：if 条件 then

执行语句

  else if 条件 then

执行语句

  end if;

end if;

循环结构：while循环

语法：while 循环条件 do

//循环体

//控制循环条件

  end while;

函数循环

验证

循环控制

PHP中循环控制，continue，break

mysql也支持中止当前循环和结束循环

iterate：continue

leave：break

SQL的循环控制，需要指定是哪个循环

语法：

循环名字：while 条件 do

iterate 循环名字;

leave循环名字;

end while

定义一个函数

调用

注意

1. 自定义函数的定义是永久性的，只要不删除都会存在
2. 自定义函数是属于某一个数据库的，跨库不能使用

存储过程

就是函数，存储过程往往是为了实现某个单一的功能而临时存在的。

存储过程与函数的区别

1. 实现功能不一样，存储过程是为实现某个单一功能而设计，函数是为了解决某类型的问题而存在
2. 存储过程没有返回值，函数必须有返回值
3. 存储过程与函数的参数不一样
4. 存储过程往往是对表数据进行操作，而函数不是

存储过程语法

create procedure 过程名字(参数列表)

begin

//过程体，跟函数体一样

end

定义存储过程

调用存储过程

语法：call 过程名(参数列表)

希望存储过程能够显示数据？

在存储过程体里使用select

存储过程的参数

语法：类型 参数名 数据类型

示例：int_1 int in

类型：三种类型：in，out，inout

in：是外部的参数传入到过程里面

out：过程里面的内容传到外部使用

inout：既可以传进去又可以传出来使用

定义

调用

错误：out和inout类型的参数必须使用变量来进行传递，不能直接使用值

正确：

执行后对外部变量查看

mysql用户管理

当前都是采用root，超级管理员权限对数据库进行操作。

当数据库比较大，而且分工非常明确的时候，通常会创建一些用户（登录用户）来交不同层次的数据用户，他们拥有的权限是不一样的。

用户权限管理。对用户的增删改查（包括权限）

查看数据库的用户信息：mysql->user表

select user,host,password from user;

三个字段的意思：

user：用户名

host：允许登录的主机

password：加密后密码

创建用户

语法：create user 用户名 identified by ‘密码’;

用户名：用户名不单止用户名字还应该包括主机名：’username’@’主机名’

主机名：可以是域名，可以是IP，也可以是类似通配符：’192.168.3.%’；’%’所有电脑都可以访问

密码：该处密码是明文，系统会自动使用password()函数进行加密

如果需要进行通配，那么需要使用%分号，不能使用*号

代表局域网内的用户可以访问

但是，这个时候，用户只有登录的权限，没有对应数据库的查看和操作权限

给用户分配权限

语法：grant 权限列表 on 数据库.数据表 to 用户

权限列表

语句

有一种简单的方式给全部权限给用户

语法：grant all privileges on 库.表 to 用户

效果

回收权限

语法：revoke 权限列表 on 库.表 from 用户

用户管理：用户是由用户名和主机名组成

用户管理的时候，要根据具体需求分配权限。

忘记密码

普通的密码忘记，只需要管理员进入到系统，对mysql库中的user表进行更新

update user set password = password(‘新密码’) where user=’用户名’ and host = ‘主机名’;

root用户忘记密码了该怎么办？

1. 重装，最简单暴力的操作（一定要注意数据的保护），一般不采用这种方式
2. 重置root用户密码

重置root用户密码

1. 停止mysql服务

2. 重新启动mysql服务端，但是不能使用服务管理，需要使用bin目录下的mysqld.exe

语法：mysqld.exe –skip-grant-tables 重启mysql服务，但是跳过权限管理

3. 直接使用客户端进入，而不需要密码

语法：mysql回车

查看当前用户

4. 进入到mysql数据库修改root用户的密码

语法：update user set password = password(‘新密码’) where user=’root’ and host = ‘localhost’;

5. 对权限进行刷新操作

语法：flush privileges;

6. 退出系统，重启mysql服务器，就可以使用新密码登录

注意：

1. 在修改root密码的时候，千万要保证其他用户进不去（防火墙）
2. 修改密码的时候一定要指定具体用户和主机名

索引

索引指的是一个二进制文件，保存的是二叉树结构。能够快速的定位到要查找的数据。类似书的目录结构。

mysql有以下几种索引：

主键索引，唯一键索引，普通索引，全文索引（优化关键字，分词技术sphinx）