导图社区 MySQL分支(核心知识点)
这是一篇关于MySQL分支(核心知识点)的思维导图,主要内容包括:核心知识点、索引、视图、储存过程、触发器、函数,便于大家系统地掌握MySQL的核心知识点。
编辑于2025-06-03 15:32:49MySQL分支(核心知识点)
核心知识点
索引
语句
创建索引
-- 单列索引 create index idx_username on users(username); -- 多列组合索引 create index idx_name_age on users(username, age); -- 唯一索引(不允许重复值) create unique index idx_email on users(email); -- 主键索引(自动创建) alter table users add primary key (id);
查看索引
-- 查看表的索引 show index from users; -- 通过系统表查询索引信息 select * from information_schema.statistics where table_name = 'users';
删除索引
-- 删除普通索引 drop index idx_username on users; -- 删除主键索引 alter table users drop primary key;
索引类型对比
普通索引 加速查询,允许重复值 常用于 where 条件的列 唯一索引 不允许重复值,可加速查询 邮箱、手机号等唯一字段 主键索引 不允许 null 和重复值 表的主键(自动创建) 组合索引 多列组合,最左前缀匹配 多条件查询(如 where a=1 and b=2) 全文索引 对文本内容分词搜索 like '%keyword%' 场景
视图
创建视图
-- 简单视图(单表查询) create view active_users as select id, username, email from users where last_login > '2024-01-01'; -- 复杂视图(多表连接) create view user_orders as select u.username, o.order_id, o.amount from users u join orders o on u.id = o.user_id; -- 带计算字段的视图 create view order_summary as select user_id, count(*) as order_count, sum(amount) as total_amount from orders group by user_id;
查询视图
-- 查看视图定义 show create view active_users; -- 查询视图数据 select * from user_orders; -- 查看所有视图 select table_name from information_schema.views where table_schema = 'your_database';
删除视图
-- 修改视图(覆盖原有定义) create or replace view active_users as select id, username, email, last_login from users where last_login > '2024-01-01' and status = 'active'; -- 删除视图 drop view if exists inactive_users;
储存过程
创建储存过程
-- 简单存储过程(无参数) delimiter // create procedure get_active_users() begin select id, username, email from users where last_login > date_sub(now(), interval 30 day); end // delimiter ; -- 带输入参数的存储过程 delimiter // create procedure get_user_orders(in user_id int) begin select o.order_id, o.amount, p.product_name from orders o join products p on o.product_id = p.id where o.user_id = user_id; end // delimiter ; -- 带输出参数的存储过程 delimiter // create procedure count_active_users(out total int) begin select count(*) into total from users where last_login > date_sub(now(), interval 30 day); end // delimiter ;
调用储存过程
-- 查看存储过程定义 show create procedure get_active_users; -- 查看所有存储过程 select routine_name from information_schema.routines where routine_type = 'procedure' and routine_schema = 'your_database'; -- 删除存储过程 drop procedure if exists get_inactive_users;
删除储存过程
-- 调用无参数存储过程 call get_active_users(); -- 调用带输入参数的存储过程 call get_user_orders(1001); -- 调用带输出参数的存储过程 call count_active_users(@total); select @total;
触发器
创建触发器
-- before insert 触发器(数据插入前校验) delimiter // create trigger check_user_email before insert on users for each row begin if new.email not like '%@%.%' then signal sqlstate '45000' set message_text = 'invalid email format'; end if; end // delimiter ; -- after update 触发器(记录修改日志) delimiter // create trigger log_user_update after update on users for each row begin insert into user_audit_log set user_id = old.id, action = 'update', changed_fields = concat( if(old.username != new.username, 'username,', ''), if(old.email != new.email, 'email,', '') ), change_time = now(); end // delimiter ; -- after delete 触发器(数据归档) delimiter // create trigger archive_deleted_user after delete on users for each row begin insert into deleted_users_archive select *, now() as delete_time from users where id = old.id; end // delimiter ;
查看触发器
-- 查看所有触发器 show triggers; -- 查看特定触发器定义 show create trigger check_user_email; -- 通过系统表查看 select * from information_schema.triggers where trigger_schema = 'your_database';
删除触发器
drop trigger if exists outdated_trigger;
函数
创建函数
无参函数
-- 创建返回当前时间的函数 create function get_current_time() returns varchar(20) deterministic begin return date_format(now(), '%y-%m-%d %h:%i:%s'); end;
带参函数
-- 创建计算两数和的函数 create function add_numbers(a int, b int) returns int deterministic begin return a + b; end;
使用SQL查询的函数
-- 创建根据用户id返回用户名的函数 create function get_username_by_id(user_id int) returns varchar(50) reads sql data begin declare uname varchar(50); select username into uname from users where id = user_id; return uname; end;
调用函数
-- 调用无参函数 select get_current_time(); -- 调用带参函数 select add_numbers(5, 3); -- 返回8 -- 在查询中使用函数 select id, get_username_by_id(id) from users;
管理函数
-- 查看函数定义 show create function get_current_time; -- 删除函数 drop function if exists add_numbers; -- 列出所有函数 show function status where db = 'my_database';
注意事项: 函数必须使用 return 返回值 需要 create routine 权限 函数内不能执行 insert/update/delete 操作 使用 deterministic 标记纯计算函数可优化性能