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

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;

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

 

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 语句的合并。中间加入了一些逻辑控制。

 

存储过程的优缺点

优点:

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

缺点:

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

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

创建存储过程:

 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   

查看更多关于MYSQL 索引,视图,存储过程,触发器,用户权限,数据库备份的详细内容...