1. MySQL 索引
1.1 什么是索引
在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询与运行更加高效。<br />
1.2 常见索引分类
1.2.1 主键索引（PRIMARY KEY）
特点：主键是一种唯一性质索引，用于标识数据库表中的某一条记录。
每个表只可以有一个主键，并且主键值不能包含NULL。
1) 创建db4数据库
CREATE DATABASE db4 CHARACTER SET utf8;
2) 创建demo01表
CREATE TABLE demo01(
did INT,
dname VARCHAR(20),
hobby VARCHAR(30)
);
3) 语法格式
创建表的时候直接添加主键索引（最常用）
CREATE TABLE 表名( -- 添加主键 (主键是唯一性索引,不能为null,不能重复,) 字段名 类型 PRIMARY KEY, );修改表结构 添加主键索引
ALTER TABLE ``表名 ``ADD PRIMARY KEY ``( ``列名 ``)
1.2.2 唯一索引（UNIQUE）
唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据重复。
1）语法格式
创建表的时候直接添加主键索引
CREATE TABLE 表名( 列名 类型(长度), -- 添加唯一索引 UNIQUE [索引名称] (列名) );使用create语句创建：在已有的表上创建索引
create unique index 索引名 on 表名(列名(长度))修改表结构添加索引
ALTER TABLE 表名 ADD UNIQUE ( 列名 )2) 为 hobby字段添加唯一索引
create unique index ind_hobby on demo01(hobby)
3) 向表中插入数据 ```sql INSERT INTO demo01 VALUES(1,’张三’,’DBJ’);报错Duplicate entry ‘DBJ’ for key ‘hobby’
唯一索引保证了数据的唯一性,索引的效率也提升了
INSERT INTO demo01 VALUES(2,’李四’,’DBJ’);
<a name="GSq3O"></a>
### 1.2.3 普通索引（INDEX）
普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，**应该只为那些最经常出现在查询条件（WHERE column=****）或排序条件（ORDERBY column）中的数据列创建索引**。<br />1) 语法格式<br />使用create index 语句创建: 在已有的表上创建索引
```sql
create index 索引名 on 表名(列名[长度])
2) 给 dname字段添加索引
# 给dname字段添加索引
alter table demo01 add index dname_indx(dname);
1.2.4 删除索引
- 由于索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应该及时删除不再使用的索引
1）语法格式
ALTER TABLE table_name DROP INDEX index_name;
2) 删除 demo01 表中名为 dname_indx 的普通索引
ALTER TABLE demo01 DROP INDEX dname_indx;
1.3 索引性能测试
1.3.1 导入数据表
1.3.2 测试
1.4 索引的优缺点总结
- 添加索引首先应考虑在
where及order by涉及的列上建立索引 - 索引的优点
- 大大的提高查询速度
- 可以显著的减少查询中分组和排序的时间。
- 索引的缺点
- 创建索引和维护索引需要时间，而且数据量越大时间越长
- 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度
2. MySQL 视图
2.1 什么是视图
- 视图是一种虚拟表。
2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
4. 视图向用户提供基表数据的另一种表现形式2.2 视图的作用
- 权限控制时可以使用
- 比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
简化复杂的多表查询
- 视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
- 视图主要就是为了简化多表的查询
2.3 视图的使用
2.3.1 创建视图
1) 语法格式
create view 视图名 [column_list] as select语句; view: 表示视图 column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询 的属性相同 as : 表示视图要执行的操作 select语句: 向视图提供数据内容2) 创建一张视图
#1. 先编写查询语句 #查询所有商品 和 商品的对应分类信息 SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`; #2.基于上面的查询语句,创建一张视图 CREATE VIEW products_category_view AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;3) 查询视图 ,当做一张只读的表操作就可以
2.3.2 通过视图进行查询
2.4 视图与表的区别
视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
- 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新）
-
3. MySQL 存储过程(了解)
3.1 什么是存储过程
MySQL 5.0 版本开始支持存储过程。
- 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。
3.2 存储过程的优缺点
优点:
- 存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）
- 存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与数据库服务器不在同一个地区）
- 缺点:
- 在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一
- 尽量在简单的逻辑中使用，存储过程迁移十分困难，数据库集群环境，保证各个库之间存储过程变更一致也十分困难。
- 阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦；
向商品表中添加3条数据
INSERT INTO goods VALUES(1,’奶茶’,20); INSERT INTO goods VALUES(2,’绿茶’,100); INSERT INTO goods VALUES(3,’花茶’,25);
2) 创建简单的存储过程
- 语法格式
```sql
DELIMITER $$ -- 声明语句结束符，可以自定义 一般使用$$
CREATE PROCEDURE 过程名称() -- 声明存储过程
BEGIN -- 开始编写存储过程
-- 要执行的操作
END $$
DELIMITER $$
CREATE PROCEDURE good_proc1()
BEGIN
SELECT * FROM goods;
END $$
3) 调用存储过程
语法格式
call 存储过程名
-- 调用存储过程 查询goods表所有数据
call goods_proc;
3.3.2 方式2
1) IN 输入参数：表示调用者向存储过程传入值
CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)
2) 创建存储过程
- 需求: 接收一个商品id, 根据id删除数据 ```sql DELIMITER $$ CREATE PROCEDURE good_proc02(IN goods_id INT) BEGIN DELETE FROM goods WHERE gid = goods_id; END $$
3) 调用存储过程并传递参数
```sql
# 删除 id为2的商品
CALL goods_proc02(2);
3.3.3 方式3
1) 变量赋值
SET @变量名=值
2) OUT输出参数：表示存储过程向调用者传出值
OUT 变量名 数据类型
3) 创建存储过程
需求：向订单表 插入一条数据, 返回1,表示插入成功
# 创建存储过程 接收参数插入数据、并返回插入的行数
DELIMITER $$
CREATE PROCEDURE orders_proc(IN o_oid INT, IN o_gid INT, IN o_price INT, OUT out_num INT)
BEGIN
-- 执行插入操作
INSERT INTO orders VALUES(o_oid, o_gid, o_price);
-- 设置num的值为1
SET @out_num = 1;
-- 返回out_num的值
SELECT @out_num;
END $$
4) 调用存储过程
# 调用存储过程插入数据,获取返回值
CALL orders_proc(1,2,30,@out_num);
4. MySQL触发器(了解)
4.1 什么是触发器
触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。——百度百科
简单理解：当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他相关的sql语句。
4.2 触发器创建的四个要素
- 监视地点（table）
2. 监视事件（insert/update/delete）
3. 触发时间（before/after）
4. 触发事件（insert/update/delete）4.3 创建触发器
1） 语法格式
2) 向商品中添加一条数据delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误 CREATE TRIGGER Trigger_Name -- 触发器名，在一个数据库中触发器名是唯一的 before/after（insert/update/delete） -- 触发的时机 和 监视的事件 on table_Name -- 触发器所在的表 for each row -- 固定写法 叫做行触发器, 每一行受影响，触发事件都执行 begin -- begin和end之间写触发事件 end $ -- 结束标记
3) 需求: 在下订单的时候，对应的商品的库存量要相应的减，卖出商品之后减少库存量# 向商品中添加一条数据 INSERT INTO goods VALUES(1,'book',40);
- 编写触发器
4) 查询goods表中的数据DELIMITER $ CREATE TRIGGER t2 AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE goods SET num = num - 1 WHERE gid = 1; END $
5) 向订单中添加一条数据
6) goods表中的数据随之-1INSERT INTO orders VALUES(3,1,25);
5. DCL(数据控制语言)
Mysql默认使用的都是root用户,超级管理员,拥有全部的权限.除了root用户以外，我们还可以通过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库。
5.1 创建用户
语法格式：
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
1) 创建admin1用户，只能在localhost这个服务器登录mysql服务器，密码为123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
- 创建的用户在名字为 mysql的 数据库中的 user表中
2) 创建 admin2 用户可以在任何电脑上登录 mysql 服务器，密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';
- % 表示 用户可以在任意电脑登录 mysql服务器
5.2 用户授权
创建好的用户，需要进行授权。
语法格式：
GRANT 权限1,权限2 ... ON 数据库.表名 TO '用户名'@'主机名';
1) 给admin1用户分配对db2数据库中account表的操作权限：查询
GRANT SELECT ON db2.`account` TO 'admin1'@'localhost';
2) 给admin2用户分配所有权限，对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%'
3) 使用admin1用户登录数据库 测试权限
4) 发现数据库列表中 只有db2, 表只有 account.
5) 执行查询操作
-- 查询account表
SELECT * FROM products;
6) 执行插入操作,发现不允许执行,没有权限
-- 向 products 表中插入数据
-- 不允许执行
INSERT INTO products VALUES('p010','小鸟伏特加',1000,1,NULL);
5.3 查看权限
语法格式：
SHOW GRANTS FOR '用户名'@'主机名';1) 查看root 用户权限
-- 查看root用户的权限 SHOW GRANTS FOR 'root'@'localhost';GRANT ALL PRIVILEGES 是表示所有权限
5.4 删除用户
- 语法格式：
1) 删除admin1用户DROP USER 'username'@'hostname';-- 删除admin1用户 DROP USER 'admin1'@'localhost';5.5 查询用户
选择名为mysql得数据库直接查询user表即可。-- 查询用户 SELECT * FROM mysql.user;6. 数据库备份与恢复
备份的应用场景，在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。 这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。
6.1 SQLYog 数据备份
1) 选中要备份的数据库,右键 备份导出 ——>选择 备份数据库
2) 指定文件位置,选择导出即可
6.2 SQLYog 数据恢复
1) 先删除 db2 数据库
2) 导入 之前备份的 SQL 文件
6.3 命令行备份
进入到Mysql安装目录的 bin目录下, 打开DOS命令行.
1） 语法格式：
mysqldump -u 用户名 -p 密码 数据库 > 文件路径
2) 执行备份, 备份db2中的数据 到 H盘的 db2.sql 文件中
mysqldump -uroot -p123456 db2 > H:/db2.sql
6.4 命令行恢复
注意：还原的时候需要先创建一个 db2数据库
**
source sql文件地址
若有收获，就点个赞吧
0 人点赞
