mysql-黑马-day01

一、 密码与连接

• 修改密码:
  set password = password('xxx');
• 授权远程访问: grant all privileges on *.* to 'root' @'%' identified by 'pwd';
  • 刷新权限: flush privileges;

二、 索引

2.1 索引(index)的概述:

索引是帮助mysql高效查询的数据结构！这些索引以某种方式指向(或引用)了mysql中的数据。

2.2 索引的优势与劣势

• 优势
  • 由于索引类似于目录，所以可以快速查询数据
  • 通过索引对数据进行排序，降低数据排序成本，降低CPU消耗。
• 劣势:
  • 索引也需要占用空间。
  • 加快了查询速度，但是降低了新增/修改/删除的效率，因为更新数据时，索引也需要相应更新。

2.3 索引的结构

Mysql中的索引是在存储引擎中实现的。

• mysql支持的4中索引:
  • BTREE索引(B+TREE): 最常见的索引类型，大部分存储引擎都支持。
  • HASH索引: 只有Memory引擎支持，使用场景简单。
  • R-tree索引: 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据。
  • Full-text: 全文索引也是MyISAM引擎的一个特殊索引类型，主要用于全文索引，Innodb从5.6版本也开始支持全文索引。

2.3.1 BTREE结构

image.png

• CNGAHEKQMFWLTZDPRXYS

  
  image.png

2.3.2 B+TREE结构

image.png
image.png

2.3.3 Mysql中的B+TREE

image.png image.png

2.4 索引的分类

image.png

2.5 索引操作语法
2.5.1 创建索引

• create [UNIQUE|FULLTETXT|SPATIAL] index index_name [USING index_type] ON table_name(index_col_name,...)
create index idx_city_name using btree on project_user_visit (visit_user_name);
• mysql中主键会自动创建主键索引

2.5.2 查看索引

• show index from project_user_visit;
  image.png
• 格式化show index from project_user_visit\G;
  image.png

2.5.3 删除索引

• drop index index_name on table_name;
• drop index idx_city_name on project_user_visit;

2.5.4 通过alter命令修改表-修改索引

• alter table table_name add index index_name(col_list)

2.6 索引的设计原则

image.png image.png

三、 视图

视图就是一张虚拟的表，里面封装了一条select语句。

• 优点

  
  image.png

• 操作

# 创建视图
create view view_supervisor as
select visit_user_id, visit_user_name, visit_user_position_name, visit_user_mobile
from project_user_visit
where creation_date < current_date;

# 从视图查询数据
select *
from view_supervisor
where visit_user_name like '%宋%';

# 操作视图更新数据源的数据
update view_supervisor
set visit_user_name='小宋宋'
where visit_user_name = '小宋';

# 修改视图
alter view view_supervisor as select...;

# 查询视图和查看表一样
show tables;

# 查看创建视图的语句
show create view view_supervisor;

# 删除视图
drop view view_supervisor;

四、 存储过程和存储函数

事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化开发人员的工作，减少数据传输，对于提高数据处理的效率是有好处的。

• 存储过程无返回值，存储函数有返回值。

4.1 操作

# 创建存储过程
create procedure pro_test1()
begin
    # SQL语句
    select * from project_user_visit;
end;

# 调用
call pro_test1();

# 查询存储过程
show procedure status;

# 查询存储过程创建语句
show create procedure pro_test1;

# 删除存储过程
drop procedure pro_test1;

4.2 存储过程变量

• 变量声明在begin和end之间
• 定义: declare ...
• 给变量赋值:
  1. SET variable=xxx;
  2. SELECT INTO variable FROM ...

create procedure pro_test2()
begin
   # 声明一个变量
   declare num int default 10;
   # 变量赋值
   set num = 100;
   select count(*)
   # 将查询结果赋值给变量
   into num
   from project_user_visit;
   select concat('当前num值为:', num);
end;

4.3 条件

• 语法

if condition then statement_list;
  elseif condition then statement_list;
  else statement_list;
end if;

• demo

create procedure pro_test2()
begin
    # 声明一个变量
    declare num int default 10;
    declare msg varchar(255);
    # 变量赋值
    set num = 100;
    select count(*)
           # 将查询结果赋值给变量
    into num
    from project_user_visit;
    select concat('当前num值为:', num);
    if num > 10000 then
        SET msg = '多于1000条';
    elseif num = 10000 then
        SET msg = '等于10000条';
    else
        SET msg = '少于10000条';
    end if;
    select concat(msg);
end;

4.4 传递参数给存储过程

• 语法

create procedure p_name([in/out/inout] 参数名 参数类型)
begin

end;

• demo

# 演示入参与出参
create procedure pro_test2(in height int, out formatted_str varchar(255))
begin
    if height <= 150 then
        SET formatted_str = '袖珍小人';
    elseif height < 190 then
        SET formatted_str = '标准身材';
    else
        SET formatted_str = '实在是高🐂';
    end if;
end;

# 调用
call pro_test2(151, @_desc);
# 读取返回值
select @_desc;

image.png

4.5 more ... skip ...

五、 触发器

image.png
image.png

• Demo: 演示使用触发器 记录增/改/删日志
  • 增

-- 记录日志，增/改/删
create trigger trigger_insert_puv
    -- before/after insert/update/delete
    after insert
    -- 操作的表
    on project_user_visit
    -- 行级触发器
    for each row
-- 具体逻辑
begin
    insert into project_user_visit_ops_log(id, operation_type, operation_time, operation_table_id,
                                           operation_params)
                                           -- 使用new来引用新插入的数据
    values (null, 'insert', now(), new.id, concat('附加信息:', new.visit_user_name));
end;


insert into project_user_visit(id, visit_user_name)
values (1, '喜欢'),
       (2, '天文'),
       (3, '的'),
       (4, 'Pony'),
       (5, '站长');

image.png
image.png

日志记录成功。

• 记录更新操作的触发器

-- 记录update操作
create trigger trigger_update_puv
    after update
    on project_user_visit
    for each row
begin
    insert into project_user_visit_ops_log(id, operation_type, operation_time, operation_table_id, operation_params)
    values (null, 'update', now(), old.id,
            concat('修改之前的name为:', old.visit_user_name, '；修改之后的name为: ', new.visit_user_name));
end;

update project_user_visit
set visit_user_name='热爱'
where id = 1;

image.png image.png

• 记录删除操作的触发器

-- 记录delete操作
create trigger trigger_delete_puv
    after delete
    on project_user_visit
    for each row
begin
    insert into project_user_visit_ops_log(id, operation_type, operation_time, operation_table_id, operation_params)
    VALUES (null, 'delete', now(), old.id, concat('被删除的name为:', old.visit_user_name));
end;

delete
from project_user_visit
where id = '1';

image.png
image.png

• 其他命令

-- 查看触发器
show triggers;
-- 删除触发器
drop trigger trigger_delete_puv;