当前位置:Gxlcms > 数据库问题 > MYSQL 索引,视图,存储过程,触发器,用户权限,数据库备份

MYSQL 索引,视图,存储过程,触发器,用户权限,数据库备份

时间:2021-07-01 10:21:17 帮助过:30人阅读

TABLE 表名( -- 添加主键 (主键是唯一性索引,不能为null,不能重复,) 字段名 类型 PRIMARY KEY, );

修改表结构 ,添加主键索引:

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

唯一索引(UNIQUE):

创建表的时候添加唯一性索引:

CREATE TABLE 表名(
列名 类型(长度),
-- 添加唯一索引
UNIQUE [索引名称] (列名)
);

使用create语句创建: 在已有的表上创建索引:

create unique index 索引名 on 表名(列名(长度))

修改表结构添加索引:

ALTER TABLE 表名 ADD UNIQUE ( 列名 )

唯一索引保证了数据的唯一性,索引的效率也提升了

 

普通索引 (INDEX):

使用create index 语句创建: 在已有的表上创建索引:

create index 索引名 on 表名(列名[长度])
ALTER TABLE 表名 ADD INDEX 索引名 (列名)

删除索引:

ALTER TABLE table_name DROP INDEX index_name;

索引的优缺点总结:

  • 添加索引首先应考虑在 whereorder by 涉及的列上建立索引。
  • 索引的优点
  1.  大大的提高查询速度
  2.  可以显著的减少查询中分组和排序的时间。
  • 索引的缺点 
  1. 创建索引和维护索引需要时间,而且数据量越大时间越长
  2. 当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护 速度

 

MYSQL视图

 什么是视图:

  • 1.视图是一种虚拟表。
  • 2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
  • 3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
  • 4. 视图向用户提供基表数据的另一种表现形式

视图的作用:

  • 权限控制时可以使用

    比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的 作用

  • 简化复杂的多表查询

    视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图 就可以获取想要得到的信息(不需要再编写复杂的SQL) 视图主要就是为了简化多表的查询

创建视图:

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

测试示例:

#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`;

视图与表的区别:

  • 视图是建立在表的基础上,表存储数据库中的数据,而视图只是做一个数据的展示
  • 通过视图不能改变表中数据(一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许 更新)
  • 删除视图,表不受影响,而删除表,视图不再起作用

MySQL 存储过程:

    存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据 库对象。存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过 指定存储过程的名字并给定参数(需要时)来调用执行。

  简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。

 

存储过程的优缺点

优点:

  1. 存储过程一旦调试完成后,就可以稳定运行,(前提是,业务需求要相对稳定,没有变化)
  2. 存储过程减少业务系统与数据库的交互,降低耦合,数据库交互更加快捷(应用服务器,与 数据库服务器不在同一个地区)

缺点:

  1. 在互联网行业中,大量使用MySQL,MySQL的存储过程与Oracle的相比较弱,所以较少使 用,
  2. 并且互联网行业需求变化较快也是原因之一 尽量在简单的逻辑中使用,
  3. 存储过程移植十分困难,数据库集群环境,保证各个库之间存储 过程变更一致也十分困难。
  4. 阿里的代码规范里也提出了禁止使用存储过程,存储过程维护起来的确麻烦;

存储过程难以调试,不容易扩展.

创建存储过程:

DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN
select * from goods;
END $$

调用存储过程:

-- 调用存储过程 查询goods表所有数据
call goods_proc;

创建接收参数的存储过程:

DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
DELETE FROM goods WHERE gid = goods_id ;
END $$
# 删除 id为2的商品
CALL goods_proc02(2);

创建有返回值的存储过程:

# 创建存储过程 接收参数插入数据, 并返回受影响的行数
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 $$
# 调用存储过程插入数据,获取返回值
CALL orders_proc(1,2,30,@out_num);

 

MySQL触发器:

  触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事 件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对 一个表进行操作(insert,delete, update)时就会激活它执行。——百度百科

  简单理解: 当我们执行一条sql语句的时候,这条sql语句的执行会自动去触发执行其他的sql语句。

 

触发器4要素:

  • 监视地点(table)
  •  监视事件(insert/update/delete)
  •  触发时间(before/after)
  •  触发事件(insert/update/delete)
delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误
CREATE TRIGGER Trigger_Name -- 触发器名,在一个数据库中触发器名是唯一的
before/after(insert/update/delete-- 触发的时机 和 监视的事件
on table_Name -- 触发器所在的表
for each row -- 固定写法 叫做行触发器, 每一行受影响,触发事件都执行
begin
-- begin和end之间写触发事件
end
$ -- 结束标记
# 向商品中添加一条数据
INSERT INTO goods VALUES(1,book,40);
-- 1.修改结束标识
DELIMITER $
-- 2.创建触发器
CREATE TRIGGER t1
-- 3.指定触发的时机,和要监听的表
AFTER INSERT ON orders
-- 4.行触发器 固定写法
FOR EACH ROW
-- 4.触发后具体要执行的事件
BEGIN
-- 订单+1 库存-1
UPDATE goods SET num = num -1 WHERE gid = 1;
END$

 

DCL (数据控制语言):

  MySql默认使用的都是 root 用户,超级管理员,拥有全部的权限。除了root用户以外,我们还可以通
过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库。
CREATE USER 用户名@主机名 IDENTIFIED BY 密码;
CREATE USER admin1@localhost IDENTIFIED BY 123456;
创建 admin1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123456
CREATE USER admin2@% IDENTIFIED BY 123456;
建 admin2 用户可以在任何电脑上登录 mysql 服务器,密码为 123456

用户授权:

GRANT 权限 1, 权限 2... ON 数据库名.表名 TO 用户名@主机名;

技术图片

 

给 admin1 用户分配对 db4 数据库中 products 表的 操作权限:查询:

GRANT SELECT ON db4.products TO admin1@localhost;

给 admin2 用户分配所有权限,对所有数据库的所有表:

GRANT ALL ON *.* TO admin2@%;

查看权限:

SHOW GRANTS FOR 用户名@主机名;
-- 查看root用户的权限
SHOW GRANTS FOR root@localhost;

 删除用户:

DROP USER 用户名@主机名;

 

MYSQL 备份:

mysqldump -u 用户名 -p 密码 数据库 > 文件路径
mysqldump -uroot -p123456 db2 > H:/db2.sql

恢复:

source sql文件地址

 

MYSQL 索引,视图,存储过程,触发器,用户权限,数据库备份

标签:接收   sql视图   index   需求变化   mysql 索引   order by   数据库名   类型   column   

人气教程排行