CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, age INTDEFAULT18, created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='用户表';
复制表结构：
1
CREATE TABLE users_copy LIKE users;
基于查询结果创建表：
1
CREATE TABLE users_backup ASSELECT*FROM users;
1.1.2 ALTER TABLE
ALTER TABLE 用于修改已存在表的结构，包括添加/删除列、修改列属性、添加索引等操作。
1 2 3 4 5 6 7 8
ALTER TABLE tbl_name [ADD|DROPCOLUMN col_name type] [MODIFY|CHANGE COLUMNoldnew type] [ADD|DROP INDEX idx_name (col, ...)] [ADD|DROPPRIMARY KEY (col, ...)] [ADD|DROPFOREIGN KEY (col) REFERENCES tbl(col)] [RENAME TO new_tbl] [ENGINE = engine_name]
子句
说明
`` ADD
DROP COLUMN ``
MODIFY COLUMN
修改列类型或属性（不改列名）
CHANGE COLUMN
修改列类型或属性（同时改列名）
`` ADD
DROP INDEX ``
`` ADD
DROP PRIMARY KEY ``
`` ADD
DROP FOREIGN KEY ``
RENAME TO
重命名表
ENGINE = ...
更换存储引擎
ALTER TABLE 示例：
1 2 3 4
ALTER TABLE users ADDCOLUMN phone VARCHAR(20) AFTER email; ALTER TABLE users MODIFY COLUMN age SMALLINTDEFAULT0; ALTER TABLE users CHANGE COLUMN created_at create_time TIMESTAMPDEFAULTCURRENT_TIMESTAMP; ALTER TABLE users ADD INDEX idx_name (name);
1.1.3 RENAME TABLE
RENAME TABLE 用于重命名一张或多张表，也可跨库移动表。
1
RENAME TABLE old_name TO new_name [, old_name2 TO new_name2] ...
CREATEPROCEDURE proc_name([IN|OUT|INOUT] param type, ...) BEGIN [DECLARE variable type [DEFAULTvalue]; ...] [DECLARE condition_name CONDITIONFOR {SQLSTATE'sqlstate'| MySQL_error_code};] [DECLARE handler_type HANDLER FOR condition_value [, ...] statement;] SET var =value; SELECT col INTO var FROM ...; IF conditionTHEN statements; [ELSEIF conditionTHEN statements;] [ELSE statements;] END IF; [label:] LOOP ... END LOOP [label]; [label:] WHILE condition DO ... END WHILE [label]; [label:] REPEAT ... UNTIL conditionEND REPEAT [label]; CASEvalueWHEN val THEN statements; [...] ENDCASE; [LEAVE label;] [ITERATE label;] END
子句
说明
IN param type
输入参数，调用时传入值
OUT param type
输出参数，过程中赋值供调用方接收
INOUT param type
既是输入也是输出
BEGIN...END
过程体，所有 SQL 逻辑写在此区间
DECLARE var type [DEFAULT val]
声明局部变量
SET var = value
直接赋值
SELECT col INTO var
从查询结果取列值赋给变量
IF/ELSEIF/ELSE END IF
条件分支
WHILE DO...END WHILE
条件为真时循环
REPEAT...UNTIL cond END REPEAT
先执行后判断
LOOP...END LOOP
无限循环，配合 LEAVE 退出
CASE WHEN...END CASE
多值分支
LEAVE label
退出指定标签的循环或块
ITERATE label
跳到指定标签继续下一次循环
DECLARE CONDITION
定义异常别名
`` DECLARE {EXIT
CONTINUE} HANDLER ``
使用 DELIMITER 临时修改语句分隔符，避免过程体内的分号被客户端提前解析。
DECLARE 与 SET 的区别：
DECLARE 在 BEGIN...END 块的最前面声明变量（创建），可选带默认值
SET 给已声明的变量赋值（使用），可多次执行
SELECT col INTO var 也是一种赋值方式，将查询结果赋给变量
DECLARE 变量声明：
1 2 3 4 5 6 7 8 9
DELIMITER // CREATEPROCEDURE add_user(IN p_name VARCHAR(50), IN p_age INT) BEGIN DECLARE v_count INTDEFAULT0; SELECTCOUNT(*) INTO v_count FROM users; INSERT INTO users (name, age) VALUES (p_name, p_age); SELECT CONCAT('当前用户数：', v_count +1) AS msg; END// DELIMITER ;
1
CALL add_user('Alice', 25);
SELECT INTO 赋值：
1 2 3 4 5 6
DELIMITER // CREATEPROCEDURE get_stats(OUT p_min INT, OUT p_max INT, OUT p_avg DECIMAL(10,2)) BEGIN SELECTMIN(age), MAX(age), AVG(age) INTO p_min, p_max, p_avg FROM users; END// DELIMITER ;
DELIMITER // CREATEPROCEDURE get_age_level(IN p_age INT, OUT p_level VARCHAR(10)) BEGIN IF p_age <18THEN SET p_level ='未成年'; ELSEIF p_age <30THEN SET p_level ='青年'; ELSEIF p_age <60THEN SET p_level ='中年'; ELSE SET p_level ='老年'; END IF; END// DELIMITER ;
1 2
CALL get_age_level(35, @level); SELECT@level;
循环（WHILE）：
1 2 3 4 5 6 7 8 9 10 11
DELIMITER // CREATEPROCEDURE sum_1_to_n(IN n INT, OUT p_result INT) BEGIN DECLARE v_i INTDEFAULT1; SET p_result =0; WHILE v_i <= n DO SET p_result = p_result + v_i; SET v_i = v_i +1; END WHILE; END// DELIMITER ;
1 2
CALL sum_1_to_n(100, @result); SELECT@result;
循环（REPEAT…UNTIL）：
1 2 3 4 5 6 7 8 9 10 11
DELIMITER // CREATEPROCEDURE sum_1_to_n_repeat(IN n INT, OUT p_result INT) BEGIN DECLARE v_i INTDEFAULT1; SET p_result =0; REPEAT SET p_result = p_result + v_i; SET v_i = v_i +1; UNTIL v_i > n END REPEAT; END// DELIMITER ;
循环（LOOP/LEAVE）：
1 2 3 4 5 6 7 8 9 10 11 12 13 14
DELIMITER // CREATEPROCEDURE sum_1_to_n_loop(IN n INT, OUT p_result INT) BEGIN DECLARE v_i INTDEFAULT1; SET p_result =0; sum_loop: LOOP SET p_result = p_result + v_i; SET v_i = v_i +1; IF v_i > n THEN LEAVE sum_loop; END IF; END LOOP sum_loop; END// DELIMITER ;
-- 1. 定义异常别名（可选） DECLARE cond_name CONDITIONFORSQLSTATE'sqlstate_value'; -- 2. 声明异常处理器 DECLARE {EXIT | CONTINUE} HANDLER FOR {condition_name |SQLSTATE'value'|SQLWARNING|NOT FOUND |SQLEXCEPTION} [, ...] BEGIN handler_body; END;
HANDLER 类型：
HANDLER 类型
说明
EXIT HANDLER
异常处理后退出当前 BEGIN...END 块
CONTINUE HANDLER
异常处理后继续执行下一条语句
condition_value（触发条件）：
condition_value
说明
cond_name
前面用 DECLARE CONDITION 定义的条件别名
SQLSTATE 'value'
直接指定 SQLSTATE 值（如 '45000' 自定义异常）
MySQL_error_code
直接指定 MySQL 错误码（如 1146 表不存在）
SQLEXCEPTION
捕获所有异常（SQLSTATE 不以 00、02、03 开头）
SQLWARNING
捕获所有警告（SQLSTATE 以 01 开头）
NOT FOUND
捕获游标或 SELECT INTO 无数据（SQLSTATE 02000）
CONDITION 与 HANDLER 的区别：
CONDITION：仅为异常定义一个别名，使代码更易读，不执行任何动作
HANDLER：定义当异常发生时执行的处理逻辑（处理体），必须配合 BEGIN...END 块
示例一：使用 SQLSTATE 值：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
DELIMITER // CREATEPROCEDURE safe_divide(IN a INT, IN b INT, OUT p_result DECIMAL(10,2), OUT p_error VARCHAR(100)) BEGIN DECLARE EXIT HANDLER FORSQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION1@errno= MYSQL_ERRNO, @msg= MESSAGE_TEXT; SET p_result =NULL; SET p_error = CONCAT('Error ', @errno, ': ', @msg); END; IF b =0THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT ='Division by zero'; END IF; SET p_result = a / b; SET p_error =NULL; END// DELIMITER ;
DELIMITER // CREATEPROCEDURE insert_user(IN p_name VARCHAR(50), OUT p_id INT) BEGIN DECLARE duplicate_entry CONDITIONFORSQLSTATE'23000'; DECLARE EXIT HANDLER FOR duplicate_entry BEGIN SET p_id =-1; END; INSERT INTO users (name) VALUES (p_name); SET p_id = LAST_INSERT_ID(); END// DELIMITER ;
示例三：NOT FOUND 处理：
1 2 3 4 5 6 7 8 9 10 11 12
DELIMITER // CREATEPROCEDURE find_user(IN p_name VARCHAR(50), OUT p_id INT) BEGIN DECLARE v_found INTDEFAULT0; DECLARE CONTINUE HANDLER FORNOT FOUND SET v_found =1; SELECT id INTO p_id FROM users WHERE name = p_name; IF v_found =1THEN SET p_id =NULL; END IF; END// DELIMITER ;
-- 查询使用 name + age，email 未用到 mysql> EXPLAIN SELECT*FROM users WHERE name ='Alice'AND age =18\G key: idx_name_age_email key_len: 47 ref: const,const -- 47 = name(42) + age(5)，符合预期
-- 查询只使用 name 前缀 mysql> EXPLAIN SELECT*FROM users WHERE name ='Alice'\G key: idx_name_age_email key_len: 42 ref: const -- 42 = name(42)，age 列未使用
-- 查询三列全部使用 mysql> EXPLAIN SELECT*FROM users WHERE name ='Bob'AND age =18AND email ISNULL\G key: idx_name_age_email key_len: 130 ref: const,const,const -- 130 = name(42) + age(5) + email(83)，符合预期
通过 key_len 值即可反推索引被使用了多少列。
4.4 SHOW
SHOW 用于查看数据库、表、列、索引、变量、状态等各类元数据信息，是日常开发和运维中最常用的诊断语句之一。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SHOW DATABASES [LIKE'pattern'|WHERE expr] SHOW TABLES [FROM db_name] [LIKE'pattern'|WHERE expr] SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE'pattern'|WHERE expr] SHOW INDEX FROM tbl_name [FROM db_name] SHOWCREATE TABLE tbl_name SHOWTABLE STATUS [FROM db_name] [LIKE'pattern'|WHERE expr] SHOW [FULL] PROCESSLIST SHOW [GLOBAL| SESSION] VARIABLES [LIKE'pattern'|WHERE expr] SHOW [GLOBAL| SESSION] STATUS [LIKE'pattern'|WHERE expr] SHOW GRANTS [FOR user_or_role] SHOW WARNINGS [LIMIT [offset,] row_count] SHOW ERRORS [LIMIT [offset,] row_count] SHOW ENGINES SHOW TRIGGERS [FROM db_name] [LIKE'pattern']
其中 LIKE 'pattern' 支持 % 和 _ 通配符，WHERE expr 支持更灵活的过滤条件。
SHOW DATABASES 示例：
1 2
SHOW DATABASES; -- 显示当前用户有权限访问的所有数据库
注意：用户只能看到有权限的数据库，除非拥有全局 SHOW DATABASES 权限。
SHOW TABLES 示例：
1 2 3
SHOW TABLES FROM learn; SHOW TABLES LIKE'%user%'; SHOW TABLES FROM learn WHERE Tables_in_learn LIKE'%user%';
不加 FULL 时仅显示表名；加 FULL 会额外显示 BASE TABLE（普通表）、VIEW（视图）或 SYSTEM VIEW（系统视图）类型。
SHOW COLUMNS / DESCRIBE 示例：
1 2 3 4
SHOW COLUMNS FROM users; SHOWFULL COLUMNS FROM users; DESCRIBE users; DESC users name;
有 PROCESS 权限可查看所有连接；否则只能看自己的。连接数满时，拥有 CONNECTION_ADMIN 权限的账户仍保留一个专属连接可用。
SHOW VARIABLES 示例：
1 2 3
SHOW VARIABLES; SHOW VARIABLES LIKE'max_connect%'; SHOWGLOBAL VARIABLES LIKE'innodb_buffer_pool_size';
无修饰符：默认显示当前会话的变量值
SESSION：显示当前连接的变量值
GLOBAL：显示服务器级别的初始值（供新连接使用）
SHOW STATUS 示例：
1 2 3
SHOW STATUS; SHOW STATUS LIKE'Threads%'; SHOWGLOBAL STATUS LIKE'Connections';
常见变量：
变量
说明
Threads_connected
当前打开的连接数
Threads_running
当前正在执行的连接数
Questions
服务器启动以来处理的查询总数
Connections
尝试连接的总次数
Aborted_connects
失败的连接尝试次数
Table_locks_immediate
立即获得的表锁次数
Table_locks_waited
需要等待的表锁次数（值越大竞争越严重）
SHOW WARNINGS / SHOW ERRORS 示例：
1 2 3 4 5 6
INSERT INTO users (name, email, age) VALUES ('TestUser', 'test@example.com', 300); SHOW WARNINGS; -- Level: Warning Code: 1264 Message: Out of range value for column 'age' at row 1
SHOWCOUNT(*) WARNINGS; SELECT @@warning_count;
字段
说明
Level
消息级别：Note、Warning、Error
Code
MySQL 错误码
Message
具体提示信息
SHOW WARNINGS 显示最近一条 SQL 产生的所有警告和提示；SHOW ERRORS 仅显示错误，不含警告。max_error_count 控制服务器存储的消息数量上限。
SHOW ENGINES 示例：
1
SHOW ENGINES;
显示服务器支持的存储引擎及当前状态（DEFAULT 表示默认引擎）。
SHOW TRIGGERS 示例：
1
SHOW TRIGGERS FROM learn LIKE'%user%';
显示当前数据库或指定数据库中的所有触发器列表。
五、账户管理语句
5.1 CREATE USER
CREATE USER 用于在 MySQL 中创建新账户，可设置密码、密码过期策略、账户锁定状态等。
1 2 3 4 5
CREATEUSER [IF NOTEXISTS] user [auth_option]... [DEFAULT ROLE role [, role]...] [PASSWORD EXPIRE INTERVAL n DAY| PASSWORD EXPIRE NEVER] [ACCOUNT {LOCK | UNLOCK}]