MySQL分支(核心知识点)

这是一篇关于MySQL分支(核心知识点)的思维导图，主要内容包括：核心知识点、索引、视图、储存过程、触发器、函数，便于大家系统地掌握MySQL的核心知识点。

MySQL分支(核心知识点)

• 核心知识点
  • 索引
    • 语句
      • 创建索引
        • -- 单列索引 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 标记纯计算函数可优化性能