索引

可以提高查找速度
索引存放在索引文件中，对表中的数据进行操作，索引也要跟着变化。所以过多的索引反而会影响性能。

常见分类

主键索引唯一的索引。主键自带索引
唯一索引索引列的所有数据是unique的
普通索引最常见的索引，只能提示对数据的访问速度

# 创建删除唯一索引
建议只记alter那种方法
CREATE UNIQUE INDEX ind_hobby ON demo01(hobby);
/*
    创建普通索引
    create index 索引名 on 表名(列名[长度])
    ALTER TABLE 表名 ADD INDEX 索引名 (列名)
*/
ALTER TABLE demo01 ADD INDEX idx_dname(dname);
# 删除索引
ALTER TABLE demo01 DROP INDEX idx_dname;

视图

视图是一种虚拟表，由查询结果形成。

作用：

如果某个查询语句经常被执行，并且语法复杂。可以对这个查询语句建立一个视图。

语法：

create view 视图名 [column_list] as select语句;
view: 表示视图
column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询
的属性相同
as : 表示视图要执行的操作
select语句: 向视图提供数据内容

# 查询商品和商品对应的分类信息
SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;

# 创建视图
CREATE VIEW products_category_view AS 
SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;

# 操作视图就像操作read only table
SELECT * FROM products_category_view;

SQL示例

# 1) 需求: 查询各个分类下的商品平均价格
SELECT pc.`cname`, AVG(pc.`price`) FROM products_category_view pc GROUP BY pc.`cname`
# 2) 需求: 查询鞋服分类下最贵的商品的全部信息
SELECT * FROM products_category_view pc WHERE pc.`cname` = '鞋服' 
AND pc.`price` = (SELECT MAX(pc.price) FROM products_category_view pc WHERE pc.`cname` = '鞋服');

注意事项

不要在视图里改东西。它就是查询用的。
删除视图对表没有影响。但删除表视图就不再起作用了。

存储过程

可以理解为多条SQL语句的合并，其中有一些逻辑判断。

优缺点

优点:
存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）
存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与
数据库服务器不在同一个地区）
缺点:
在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使
用，并且互联网行业需求变化较快也是原因之一
尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储过程变更一致也十分困难。
阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦；

语法

DELIMITER $$ -- 声明语句结束符，可以自定义 一般使用$$
CREATE PROCEDURE 过程名称() -- 声明存储过程
BEGIN -- 开始编写存储过程
-- 要执行的操作
END $$ -- 存储过程结束

赋值    SET @变量名 = 值
输出    OUT 变量名 数据类型

例子

# 需求: 编写存储过程, 查询所有商品数据
DELIMITER $$
CREATE PROCEDURE goods_proc(IN 参数名 参数类型)
BEGIN
    SELECT * FROM goods;
END $$
# 调用
CALL goods_proc;

# 需求: 接收一个商品id, 根据id删除数据
DELIMITER $$
CREATE PROCEDURE goods_proc2(IN goods_id INT)
BEGIN
    DELETE FROM goods WHERE gid = goods_id;
END $$

CALL goods_proc2(1);

# 需求: 向订单表 插入一条数据, 返回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);
    SET @out_num = 1;
    SELECT @out_num;
END $$

CALL orders_proc(1, 2, 50, @out_num);

触发器

当事件来临时会触发一些SQL的执行。

语法

delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误
CREATE TRIGGER Trigger_Name -- 触发器名，在一个数据库中触发器名是唯一的
before/after（insert/update/delete） -- 触发的时机 和 监视的事件
on table_Name -- 触发器所在的表
for each row -- 固定写法 叫做行触发器, 每一行受影响，触发事件都执行
begin
-- begin和end之间写触发事件
end
$ -- 结束标记

例子

# 需求: 在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量。
DELIMITER $
CREATE TRIGGER t1
AFTER INSERT ON orders 
FOR EACH ROW
BEGIN
    UPDATE goods SET num = num - 1 WHERE gid = 4;    
END $

DCL

创建

# 1) 创建 admin1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';

# 2) 创建 admin2 用户可以在任何电脑上登录 mysql 服务器，密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';

授权

# 1) 给 admin1 用户分配对 db3 数据库中 products 表的 操作权限：查询
GRANT SELECT ON db3.`products` TO 'admin1'@'localhost';

# 2) 给 admin2 用户分配所有权限，对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';

查看权限

SHOW GRANTS FOR 'root'@'localhost';

查询/删除用户

DROP USER 'admin1'@'localhost';


use mysql;
SELECT * FROM USER;

数据库备份

mysqldump -u 用户名 -p 密码 数据库 > 文件路径
mysqldump -uroot -p123456 db2 > E:/db2.sql
在mysql client中    
create database db2;
use db2;
source E:/db2.sql

MYSQL 索引、视图、存储过程

索引