mysql 索引视图 存储过程

发表评论
203 views

mysql 索引

索引可以提高查询速度，常见有的索引有 主键索引，唯一索引，普通索引 1. 主键索引 primary key 唯一索引，每张表只有一个主键，用来标识数据表中的每一条记录 2. 唯一索引 unique kye ,索引列的所有值都只能出现一次，必须唯一 3. 普通索引 index,常见索引，加快对数据的访问速度

mysql 将一个表的索引都保存在同一个索引文件中，如果对数据进行增删改操作，mysql都会自动更新索引。

DDL 操作

alter table demo1 add primary key (did);//主键索引
create unique index  索引名 On  表名(列名(长度))//唯一索引
create unique index  index_hobby on demo1(hobby);
添加唯一索引的列，列的所有值都只能出现一次
create index 索引名 On 表名（列名（长度））
alter table 表名 add index 索引名（列名）
alter table demo1 add index ind_dname(dname);
删除索引
alter table 表名 drop index 索引名
alter table demo01 drop index ind_dname;

创建索引的原则

优先选择为 经常出现在 查询条件或者排序 分组后面的字段 创建索引 索引的优点 1. 可以大大提高查询速度 2. 减少查询中的分组和排序的时间 3. 通过创建唯一索引保证数据的唯一性

索引的缺点 1. 创建和维护索引需要时间，数据量越大时间越长 2. 表中的数据进行增删改操作，索引也需要进行维护，降低了维护的速度 3. 文件变大

视图

视图是一种虚拟表，视图建立在已有表的基础上，赖以建立的这些表位基表，向视图提供数据内容的语句为select语句，可以将视图理解为存储的select 语句，其实视图就是向用户提供基表数据的另外一种形式

视图作用

  1. 权限控制时可以使用，例如某几个列可以运行用户查询，其他列不允许，可以开通视图，查询特定的列，起到权限控制的作用
  2. 简化复杂的多表查询，视图本身就是一条查询SQL，可以将一次复杂的查询构建成一张视图，用户只要查询视图就可以获取向要得到的信息，主要就是为了简化多表的查询

语法

create view 视图名[字段列表] as select 查询语句 view 表示视图 字段列表 一般跟后面的查询语句 相同，as select 查询语句

举例

    select c.cname ,avg(p.price) from products p left join category c on p.category_id = c.cid group by c.cname;
    //通过视图查询
    select pc.cname, avg(pc.price)  from products_category_view pc group by pc.cname;

视图和表的区别

  1. 视图是建立在表的基础之上
  2. 通过视图不要进行增删改操作，视图用来简化查询
  3. 删除视图，表不受影响，如果表被删除，视图没有作用

MySQL 存储过程

存储过程

  1. 5.0 版本之后开始支持存储过程
  2. 存储过程是在数据库中存储复杂程序让外部程序调用的一种数据库对象，存储过程是为了完成特定功能的SQL语句，编译创建保存在数据库中，用户可通过指定存储过程的名字并给定必要参数来调用运行
  3. 总的来说，存储过程就是一堆SQL语句合并，并在中间加了一些逻辑控制

存储过程的优缺点

优点: 存储过程一旦调试完成就可以文档运行，同时减少业务系统和数据库的交互，降低耦合，数据库交互更加快捷
缺点： mysql的存储过程比Oracle相比较弱，同时移植比较困难，数据库集群环境要保证各个库之间存储过程变更一致也十分困难

创建存储过程方式1： delimiter $$ create procedure 存储过程名称() begin sql语句 end $$

delimiter $$
create procedure goods_proc()
begin
    select * from goods;
end $$
//如何调用
call goods_proc;

创建存储过程方式2：创建一个接受参数的存储过程 create procedure 存储过程名（IN 参数名 参数类型）

delimiter $$
create procedure goods_proc02(IN goods_id INT)
begin
    delete from goods where gid = goods_id
end $$
call goods_proc02(1);

创建存储过程方式3 获取存储过程的返回值

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);

mysql 触发器

当我们执行一条sql语句，这条语句的执行会自动去触发执行其他sql 语句 触发器创建的四个要素 1.监视地点 table2.监视事件 insert/update/delete 3.触发时间 before/after 4.触发事件insert/update/delete

创建触发器

delimiter $-- 自定义结束符号
create trigger 触发器名
after/before（insert/update/delete) --触发的实际和监视的事件
on tableName -- 触发器所在表
for each row -- 固定写法，表示触发器
begin
    --触发事件
end $

    delimiter $
    create trigger t1
    after insert on orders 
    for each row 
    begin
    update goods set num = num -1 where gid  = 4;
    end $
    insert into orders values(1,4,25);

DCL

创建用户

create user 用户名@主机名(% 表示可以远程) identified by 密码;

用户授权

grant select on db4.prodcut to 用户名@主机名 grant all on . to 用户名@%

查看用户权限

show grants for 用户名@主机名

删除用户

drop user 用户名@主机名

数据库备份

mysqldump -uroot -p 数据库 > a.sql source a.sql