数据库MySQL零基础-下【详细】

原子性(Atomicity)：事务必须是原子工作单元，一个事务中的所有语句，应该做到：要么全做，要么一个都不做
一致性(Consistency)：让数据保持逻辑上的“合理性”，比如：小明给小红打10000块钱，既要让小明的账户减少 10000，又要让小红的账户上增加10000块钱
隔离性(Isolation)：如果多个事务同时并发执行，但每个事务就像各自独立执行一样
持久性(Durability)：一个事务执行成功，则对数据来说应该是一个明确的硬盘数据更改，而不仅仅是内存中的变化

注意：如果要使用事务，表的引擎需要为innodb引擎

2、事务的应用

（1）事务的开启与提交

# 语法

事务的开启：begin; start transaction;

事务的提交：commit;

事务的回滚：rollback;

# 示例

/*创建一个账户表模拟转账*/
create table account (
	id tinyint(5) zerofill auto_increment not null comment 'id编号',
	name varchar(20) default null comment '客户姓名',
	money decimal(10,2) not null comment '账户金额',
	primary key (id)
)engine=innodb charset=utf8;

/*开启事务*/
begin;

/*插入一条数据*/
insert into account values ('1','张三','20000');

/*提交事务*/
commit;
--------------------------------------------------------
/*开启事务*/
begin;

/*删除一条数据*/
delete from account where id = '00001';

/*回滚事务*/
rollback;

注意：开启事务后，进行的操作必须提交事务才能生效；若回滚事务，则之前所做的操作无效

（2）开启autocommit（临时生效）

/*关闭*/
set autocommit=0;

/*开启*/
set autocommit=1;

/*查看autocommit状态*/
show variables like 'autocommit';

OFF（0）：表示关闭
ON （1）：表示开启

开启 autocommit 后，在事务不 begin 的时候进行操作也会生效

（3）开启autocommit（永久生效）

修改配置文件：vi /home/data/mysql/my.cnf ，在[mysqld]下面加上：autocommit=1 ，需要重启服务才会生效

注意：系统默认就是 autocommit 为ON，因此配置文件中可忽略

3、视图的应用

（1）视图理解

视图（view）是一种虚拟存在的表，是一个逻辑表，它本身是不包含数据的。

作为一个select语句保存在数据字典中，通过视图，可以展现基表（用来创建视图的表叫做基表base table）的部分数据，说白了视图的数据就是来自于基表

（2）视图创建及修改

# 创建语法

/*语法*/
create view <视图名称> as select 语句;
create view <视图名称> (字段) as select 语句;
create or replace view <视图名称>;

/*示例*/
create view employ as select empno,ename,job,mgr,deptnu from employee;

# 修改语法

/*语法*/
alter view <视图名称> as select 语句;

/*示例*/
alter view employ (id,name) as select empno,ename from  employee;

# 删除语法

/*语法*/
drop view <视图名称> ;

/*示例*/
drop view employ;

（3）视图优点

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响，源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
不占用空间：视图是逻辑上的表，不占用内存空间

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率

（4）视图缺点

性能差：sql server必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，sql server也要把它变成一个复杂的结合体，需要花费一定的时间
修改限制：当用户试图修改试图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的试图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的

4、触发器介绍

（1）触发器理解

触发器就是监视某种情况，并触发某种操作

（2）创建触发器

create trigger 触发器名称 after/before insert/update/delete on 表名
	for each row
	begin
	sql语句;
	end

语法各字段解析：

after/before：可以设置为事件发生前或后
insert/update/delete：它们可以在执行insert、update或delete的过程中触发
for each row：每隔一行执行一次动作

（3）删除触发器

drop trigger 触发器名称;

（4）示例

/*创建一个员工迟到表*/
create table work_time_delay(
	empno int not null comment '雇员编号',
	ename varchar(50) comment '雇员姓名',
	status int comment '状态'
);

/*自定义语句的结束符号*/
delimiter 任意符号
/*示例*/
delimiter //

/*创建触发器*/
create trigger trig_work after insert on work_time_delay
	for each row
	begin
	update employee set sal=sal-100 where empno=new.empno;
	end
    //

new：指的是事件发生 before 或者 after 保存的新数据

5、存储过程介绍

（1）存储过程理解

存储过程就是把复杂的一系列操作，封装成一个过程，类似于shell，python脚本等

（2）存储过程优缺点

# 优点

复杂操作，调用简单
速度快

# 缺点

封装复杂
没有灵活性

（3）存储过程创建

# 语法

创建存储过程的语法：

create procedure 名称 (参数....)
	begin
	过程体;
	过程体;
	end
	//

参数语法：

in|out|inout 参数名称 类型（长度）

in：表示调用者向过程传入值（传入值可以是字面量或变量）
out：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
inout：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

声明变量：

declare 变量名 类型(长度) default 默认值;

给变量赋值：

set @变量名=值;

调用存储命令：

call 名称(@变量名);

删除存储过程命令：

drop procedure 名称;

查看创建的存储过程命令：

show create procedure 名称\G;

# 示例

新建一个名叫name的存储过程，接着给对应变量赋值，最后调用该存储过程

/*1.自定义语句结束符*/
delimiter //

/*2.新建存储过程name*/
create procedure name(in n int)
	begin
	select * from employee limit n;
	end
	//

/*3.给变量n赋值*/
set @n=5;
//

/*4.调用name*/
call name(@n);
//

/*5.删除name存储过程*/
drop procedure name;
//

新建一个名叫name的存储过程（新建时声明变量并给出默认值），调用该存储过程

/*1.自定义语句结束符*/
delimiter //

/*2.新建存储过程name*/
create procedure name()
	begin
	declare n int default 6;
	select * from employee limit n;
	end
    //

/*3.调用name*/
call name();
//

/*4.删除name存储过程*/
drop procedure name;
//

七、索引与存储引擎

1、存储引擎介绍

（1）数据库存储引擎理解

数据库引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的数据库引擎，可以获得特定的功能

（2）查看引擎

/*查看数据库支持的引擎*/
show engines;

/*查看当前数据的引擎*/
show create table 表名\G

/*查看当前库所有表的引擎*/
show table status\G

（3）建表时指定引擎

create table yinqing (id int,name varchar(20)) engine='InnoDB';

（4）修改表的引擎

alter table 表名 engine='引擎';

修改默认引擎

vi /home/data/mysql/my.cnf
[mysqld]下面增加一行：default-storage-engine=MyISAM
记得保存后重启服务

（5）MyISAM与InnoDB的区别

MyISAM：支持全文索引（full text）；不支持事务；表级锁；保存表的具体行数；崩溃恢复不好
Innodb：支持事务；以前的版本是不支持全文索引，但在5.6之后的版本就开始支持这个功能了；行级锁（并非绝对，当执行sql语句时不能确定范围时，也会进行锁全表例如： update table set id=3 where name like 'a%';）；不保存表的具体行数；崩溃恢复好

（6）选择引擎

MyISAM：

一般来说MyISAM不需要用到事务的时候会使用
做很多count计算

InnoDB：

可靠性要求高的，或者要求支持事务
想要用到外键约束的时候

注意：推荐用InnoDB

2、常用索引

（1）索引理解

索引是一个单独的，存储在磁盘中的数据库结构，它们包含着对数据表里的所有记录的引用指针，使用索引可以快速的找出在某列或多列中有特定值的行

（2）索引的优缺点

优点：

通过创建唯一索引，来保证数据库表中的每一行数据的唯一性
可以加快数据的检索速度
可以保证表数据的完整性与准确性

缺点：

索引需要占用物理空间
对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度

（3）索引常见类型

索引	索引名称
index	普通索引
unique	唯一索引
primary key	主键索引
foreign key	外键索引
fulltext	全文索引
	组合索引

（4）创建表

create table test (
	id int(7) zerofill auto_increment not null,
	username varchar(20),
	servnumber varchar(30),
	password varchar(20),
	createtime datetime,
	primary key (id)
)DEFAULT CHARSET=utf8;

（5）生成多表sql的shell脚本

第一步：新建test脚本，即vi test.sh

第二步：执行shell脚本，即sh test.sh

第三步：在mysql中进行插数操作，即source /var/lib/mysql/sql.txt（注意：这里的路径为docker下存放该文件的路径）

【test.sh】脚本内容如下：

#!/bin/bash

echo "请输入字段servnumber的值："
read serber
echo "请输入创建sql语句的数量："
read number

# char=`head /dev/urandom | tr -dc 0-9 | head -c 11`

for (( i=0;i<$number;i++ ))
	do
	pass=`head /dev/urandom | tr -dc a-z | head -c 8`
	let serber=serber+1
	echo "insert into test(id,username,servnumber,password,createtime) values ('$i','user${i}','${serber}','$pass',now());" >>sql.txt
	done

3、普通索引与唯一索引

（1）普通索引理解

普通索引（index）顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度，其特点是允许出现相同的索引内容，允许空（null）值

（2）唯一索引理解

唯一索引：（unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值

（3）创建普通索引/唯一索引

# 创建表的时候创建

create table test (
	id int(7) zerofill auto_increment not null,
	username varchar(20),
	servnumber varchar(30),
	password varchar(20),
	createtime datetime,
	unique (id)
)DEFAULT CHARSET=utf8;

# 通过alter添加

/*语法：添加普通索引*/
alter table 表名 add index 索引名称 (字段名称);
/*语法：添加唯一索引*/
alter table 表名 add unique 索引名称 (字段名称);

/*示例*/
alter table test add unique unique_username (username);

注意：假如没有指定索引名称时，会以默认的字段名命名为索引名称

# 直接创建索引

/*语法*/
create index 索引 on 表名 (字段名);

/*示例*/
create index index_createtime on test (createtime);

（4）查看索引

# 语法

show index from 表名\G

# 示例

show index from test\G

（5）删除索引

# 语法

drop index 索引名称 on 表名;
alter table 表名 drop index 索引名;

# 示例

drop index unique_username on test;
alter table test drop index createtime;

4、主键索引

（1）主键索引理解

把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique）是允许为空值的，主键索引指定为“PRIMARY KEY”

主键：主键是表的某一列，这一列的值是用来标志表中的某一行数据的

注意：每一张表只能拥有一个主键

（2）创建主键索引

# 创建表的时候创建

create table test (
	id int(7) zerofill auto_increment not null,
	username varchar(20),
	servnumber varchar(30),
	password varchar(20),
	createtime datetime,
	primary key (id)
)DEFAULT CHARSET=utf8;

# 通过alter添加

/*语法*/
alter table 表名 add primary key (字段名);

/*示例*/
alter table test add primary key (id);

（3）删除主键

/*语法*/
alter table 表名 drop primary key;

/*示例*/
alter table test drop primary key;

/*删除自增*/
alter table test change id id int(7) unsigned zerofill not null;

注意：在有自增的情况下，必须先删除自增，才可以删除主键

5、全文索引

创建练习表的sql：

create table command (
id int(5) unsigned primary key auto_increment,
name varchar(10),
instruction varchar(60)
)engine=MyISAM;

插入数据sql：

insert into command values('1','ls','list directory contents');
insert into command values('2','wc','print newline, word, and byte counts for each
file');
insert into command values('3','cut','remove sections from each line of files');
insert into command values('4','sort','sort lines of text files');
insert into command values('5','find','search for files in a directory hierarchy');
insert into command values('6','cp','复制文件或者文件夹');
insert into command values('7','top','display Linux processes');
insert into command values('8','mv','修改文件名，移动');
insert into command values('9','停止词','is,not,me,yes,no ...');

（1）全文索引理解

全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引，单位是词；全文索引也是目前搜索引擎使用的一种关键技术，指定为 fulltex

（2）添加全文索引

# 创建表的时候创建

这里就不做赘述了

# 通过alter添加

/*语法*/
alter table 表名 add fulltext(字段名);

/*示例*/
alter table command add fulltext(instruction);

（3）使用全文索引

/*语法*/
select * from 表名 where match (字段名) against ('检索内容');

/*示例*/
select * from command where match(instruction) against ('sections');

注意：全文索引单位为词，作用于词与词之间，若查询条件不是词，则查询为空

（4）查看匹配度

全文索引会按照匹配度来显示数据

select * from command where match(instruction) against ('directory');

select id,match(instruction) against ('directory') from command;

（5）停止词

若出现频率很高的词（即停止词），将会使全文索引失效

select * from command where match(instruction) against ('not');

（6）in boolean mode 模式

in boolean mode：指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）

/*语法*/
select * from 表名 where match (字段名) against ('检索内容*' in boolean mode);

/*示例*/

select * from command where match(instruction) against ('director*' in boolean mode);

注意：使用通配符 * 时，只能放在词的后边，不能放前边

（7）删除全文索引

alter table command drop index instruction;

注意点

一般情况下创建全文索引的字段数据类型为 char、varchar、text ；其它字段类型不可以
全文索引不针对非常频繁的词做索引，比如is，no，not，you，me，yes这些，我们称之为停止词
对英文检索时忽略大小写

6、外键约束

（1）外键理解

外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性

（2）添加外键约束

# 语法

foreign key (字段名) references 关联的表名(关联表的字段名)

# 示例

第一种：建表时直接添加外键约束

CREATE TABLE `employee` (
	`empno` int(11) NOT NULL COMMENT '雇员编号',
	`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
	`job` varchar(30) DEFAULT NULL,
	`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
	`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
	`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
	`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
	PRIMARY KEY (`empno`),
	foreign key (deptnu) references dept(deptnu)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

第二种：新建表后，为表添加外键约束

alter table employee add foreign key (deptnu) references dept(deptnu);

注意：主键跟外键的字段类型一定要相同

（3）删除外键约束

/*删除外键约束*/
alter table employee drop foreign key employee_ibfk_1;

/*删除外键索引*/
alter table employee drop index deptnu;

注意：在干掉外键索引之前必须先把外键约束删除，才能删除索引

注意点：

涉及到的俩个表，主键跟外键的字段类型一定要相同
要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的
在干掉外键索引之前必须先把外键约束删除，才能删除索引

7、联合索引

（1）联合索引理解

联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引

（2）创建联合索引

/*语法*/
alter table 表名 add index(字段1,字段2,字段3);

/*示例*/
alter table test add index(username,servnumber,password);

（3）删除联合索引

alter table test drop index username;

（4）使用联合索引的优点

联合索引的效率远远高于单列索引

联合索引的最左原则：联合索引不管字段有多少个，都会以最左边的字段为基准进行查询，若查询条件中无最左边字段，则该索引是无效的

（5）索引注意事项

索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费
当表的数据量很大的时候，可以考虑建立索引
表中经常查数据的字段，可以考虑建立索引
想要保证表中数据的唯一性，可以考虑建立唯一索引
想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束
经常对多列数据进行查询时，可以考虑建立联合索引

八、sql语句优化思路

1、慢查询日志开启与问题定位

（1）第一步

查看是否已经开启了慢查询日志

show variables like 'slow%';

（2）第二步

开启慢查询日志

set global slow_query_log = on ;

日志路径也可以自定义：
set global slow_query_log_file = '对应存放路径';

（3）第三步

查看慢查询的时间临界值，从图中可以看到，long_query_time=10，即超过10秒的语句会被记录在slow.log中

show variables like '%long%';

（4）第四步

设置自定义慢查询的临界时间标准

set long_query_time=0.2;

注意：重启mysql服务会让在交互界面设置的慢查询恢复到默认

永久生效的设置方法：

第一步：修改配置文件 vi /home/data/mysql/my.cnf

第二步：[mysqld]下增加三条内容

slow_query_log = 1
long_query_time = 0.1
slow_query_log_file =/var/lib/mysql/mysql_slow.log

第三步：重启服务

2、sql语句执行过程解析

（1）第一步

查看性能详情是否开启

show variables like '%profiling%';

（2）第二步

开启性能记录功能

set profiling = on ;

（3）第三步

查看性能的记录

show profiles;

（4）第四步

查看语句的执行性能详情

show profile for query 3;

3、sql优化的建议

（1）第一点

尽量避免使用select * from ，尽量精确到想要的结果字段

（2）第二点

尽量避免条件使用or

（3）第三点

查询时尽可能加上limit 限制行数，避免数据量过大消耗性能

select * from test_big limit 10;

（4）第四点

使用模糊查询时，%放在前面会使索引失效

explain select * from test_big where id like '%12876'\G

（5）第五点

要小心条件字段类型的转换

九、备份

1、备份的背景意义

数据备份的意义：

保护数据的安全
在出现意外时（硬盘的损坏，断电，黑客的攻击），以便数据的恢复
导出生产的数据以便研发人员或者测试人员测试学习
高权限的人员操作失误导致数据丢失，以便恢复

2、备份的介绍

（1）数据库备份类型

# 完全备份

对整个数据库的数据进行备份

# 部分备份

对部分数据进行备份（可以是一张表也可以是多张表）

增量备份：是以上一次备份为基础来备份变更数据的，节约空间
差异备份：是以第一次完全备份的基础来备份变更备份的，浪费空间

（2）数据库备份方式

逻辑备份：直接生成sql语句保存起来，在恢复数据的时候执行备份的sql语句来实现数据的恢复
物理备份：直接拷贝相关的物理数据

注意：逻辑备份效率低，但是逻辑备份节约空间；物理备份浪费空间，但是相对逻辑备份而言效率比较高

（3）数据库备份场景

热备份：备份时，数据库的读写操作不会受到影响
温备份：备份时，数据库的读操作可以进行，但是写操作不能执行
冷备份：备份时，数据库不能进行任何操作

3、mysqldump备份实例

（1）语法

mysqldump -u 用户 -h host -p 密码 库名 表名> 路径

（2）实例

# 远程备份单库

mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix | gzip > 
/mysql_data_back/zabbix.sql.gz

# 远程备份单库并保留创建库语句

mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix | gzip >
/mysql_data_back/zabbix_bak.sql.gz

# 远程备份单库单表

mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix users | gzip >
/mysql_data_back/zabbix_users.sql.gz

# 远程备份多库

mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix db | gzip >
/mysql_data_back/zabbix_db.sql.gz

# 远程备份全库

mysqldump -uroot -pabc123456 -h120.25.93.69 --all-databases | gzip >
/mysql_data_back/all.sql.gz

4、mysql数据的恢复

（1）远程恢复数据

备份的数据文件里有创建库的语句

mysql -uroot -pabc123456 -h120.25.93.69 < zabbix_bak.sql

（2）远程恢复数据

备份的数据文件里没有创建库的语句

mysql -uroot -pabc123456 -h120.25.93.69 zabbix < zabbix_bak.sql

注意：进行数据恢复时，若备份的数据文件里没有创建库的语句，会发生报错，这时候重新新建一个zabbix库即可

5、物理备份

（1）查看数据库源文件路径

/*法1*/
show variables like 'datadir%';

/*法2-直接查看配置文件*/
cat /home/data/mysql/my.cnf

（2）MyISAM表源文件理解

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
.frm ：记录着表结构信息的文件
.MYI：记录着索引的文件
.MYD :记录着表的数据

（3）InnoDB表源文件理解

InnoDB有着共享表空间跟独立表空间的概念

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
.frm ：记录着表结构信息的文件
.ibd ：独立表空间，里边记录这个表的数据和索引
ibdata1：共享表空间，里边记录表的数据和索引

注意：进行物理备份时，需要额外备份三个文件：ibdata1、ib_logfile0、ib_logfile1

不然会影响到InnoDB的查询

备份完后需要重启mysql

（4）请求全局读锁

当数据库在运行时，无法直接备份该数据库，那么就需要进行热备份，即全局读锁（但该操作对数据库有影响）

flush tables with read lock;

（5）解锁

备份完后需要及时解锁数据库

unlock tables;

6、利用二进制日志mysqlbinlog备份数据

（1）二进制日志理解

二进制日志就是记录着mysql数据库中的一些写入性操作，比如一些增删改，但是不包括查询！

（2）二进制日志功能

一般情况下，二进制日志有着数据复制和数据恢复的功能

注意：开启二进制日志会有1%的性能消耗！

（3）查看二进制是否开启

show variables like 'log_bin%';

（4）开启二进制日志

编辑配置文件【vi /home/data/mysql/my.cnf】

[mysqld]
log-bin=/data/mydata/log_bin/mysql-bin
server-id=1

（5）查看所有的binlog日志列表

show master logs;

（6）刷新二进制日志

flush logs;

（7）重置/清空二进制文件

show master logs;

（8）mysqldump备份数据优化点

使用mysqldump备份数据时，加上-F选项可以重新生成一个新的二进制日志文件

mysqldump -uroot -p db user -F > user_bak.sql

7、利用二进制日志mysqlbinlog恢复数据

（1）查看二进制文件内容

mysqlbinlog mysql-bin.000002

若查看二进制文件报错，解决办法：

第一种：在mysqlbinlog 后边加上 --no-defaults
第二种：注释掉配置文件里边的default-character-set=utf8

（2）把二进制日志文件导出成普通文件

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v mysql-bin.000002 > mysqlbin.sql

（3）找出要恢复的位置

# 找出关键字的行数

mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | grep -iw 'drop'

# 打印出相关内容

mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | sed -n '4170,4180p'

（4）恢复数据

# 第一步

把备份的数据表user恢复到数据库中

mysql -uroot -p db < /mysql_data_back/user_bak.sql

# 第二步

利用上面找到的删除的位置进行恢复数据

mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="59674" /data/mydata/log_bin/mysql-bin.000002 | mysql -uroot -p

cendy-LL

关注

14
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录