一、 密码与连接
- 修改密码:
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结构
CNGAHEKQMFWLTZDPRXYS
2.3.2 B+TREE结构
2.3.3 Mysql中的B+TREE
2.4 索引的分类
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;
格式化show index from project_user_visit\G;
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 索引的设计原则
三、 视图
视图就是一张虚拟的表，里面封装了一条select语句。
- 优点
- 操作
# 创建视图 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 ... - 给变量赋值:
SET variable=xxx;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;
4.5 more ... skip ...
五、 触发器
- 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, '站长');
日志记录成功。
- 记录更新操作的触发器
-- 记录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;
- 记录删除操作的触发器
-- 记录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';
- 其他命令
-- 查看触发器 show triggers; -- 删除触发器 drop trigger trigger_delete_puv;
