一、登录/退出 MySQL登录命令# 基础登录本地回车输密码 mysql -u用户名 -p # 指定密码直接登录不推荐明文 mysql -uroot -p123456 # 远程连接指定IP、端口 mysql -h192.168.1.100 -P3306 -uroot -p # 指定数据库登录 mysql -uroot -p test_db # 免密本地登录无密码root mysql退出exit; quit; \q二、数据库操作Database-- 查看所有数据库 SHOW DATABASES; SHOW SCHEMAS; -- 创建数据库指定字符集utf8mb4支持emoji CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 删除数据库 DROP DATABASE IF EXISTS test_db; -- 使用/切换数据库 USE test_db; -- 查看当前正在使用的库 SELECT DATABASE(); -- 修改数据库字符集 ALTER DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;三、用户与权限管理DBA常用用户创建/删除/修改-- 创建用户本地登录 CREATE USER user1localhost IDENTIFIED BY 123456; -- 创建远程登录用户任意IP % CREATE USER user1% IDENTIFIED BY 123456; -- 修改用户密码 ALTER USER user1% IDENTIFIED BY newpass; SET PASSWORD FOR user1% PASSWORD(newpass); -- 删除用户 DROP USER IF EXISTS user1%; -- 查看所有用户 SELECT user,host FROM mysql.user;权限授予/回收-- 授予全部权限所有库所有表 GRANT ALL PRIVILEGES ON *.* TO user1%; -- 仅授予单库所有表增删改查 GRANT SELECT,INSERT,UPDATE,DELETE ON test_db.* TO user1%; -- 授予建表、索引权限 GRANT CREATE,DROP,INDEX ON test_db.* TO user1%; -- 授予管理员权限创建用户、授权 GRANT CREATE USER,GRANT OPTION ON *.* TO user1%; -- 刷新权限授权后必须执行生效 FLUSH PRIVILEGES; -- 回收权限 REVOKE DELETE ON test_db.* FROM user1%; REVOKE ALL PRIVILEGES ON *.* FROM user1%; -- 查看用户权限 SHOW GRANTS FOR user1%;四、数据表操作Table基础表操作-- 查看当前库所有表 SHOW TABLES; -- 查看表结构 DESC student; DESCRIBE student; SHOW COLUMNS FROM student; -- 创建数据表 CREATE TABLE IF NOT EXISTS student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, name VARCHAR(50) NOT NULL COMMENT 姓名, age TINYINT DEFAULT 0 COMMENT 年龄, create_time DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表; -- 删除表 DROP TABLE IF EXISTS student; -- 清空表数据自增归零 TRUNCATE TABLE student; -- 清空表不自增归零支持事务回滚 DELETE FROM student; -- 复制表结构无数据 CREATE TABLE student_copy LIKE student; -- 复制表结构数据 CREATE TABLE student_back AS SELECT * FROM student; -- 重命名表 RENAME TABLE student TO stu; ALTER TABLE stu RENAME TO student;修改表结构 ALTER-- 新增字段 ALTER TABLE student ADD COLUMN phone VARCHAR(11) AFTER age; -- 字段放最前面 ALTER TABLE student ADD COLUMN sex TINYINT FIRST; -- 删除字段 ALTER TABLE student DROP COLUMN phone; -- 修改字段类型/名称 ALTER TABLE student MODIFY COLUMN name VARCHAR(100); ALTER TABLE student CHANGE COLUMN name username VARCHAR(80); -- 修改表注释 ALTER TABLE student COMMENT学生信息总表; -- 修改字段注释 ALTER TABLE student MODIFY COLUMN age TINYINT COMMENT 学生年龄; -- 修改引擎/字符集 ALTER TABLE student ENGINEInnoDB; ALTER TABLE student CHARACTER SET utf8mb4;五、索引操作优化查询-- 查看表索引 SHOW INDEX FROM student; SHOW KEYS FROM student; -- 创建普通索引 CREATE INDEX idx_name ON student(name); -- 唯一索引值不能重复 CREATE UNIQUE INDEX uk_phone ON student(phone); -- 联合索引 CREATE INDEX idx_name_age ON student(name,age); -- 删除索引 DROP INDEX idx_name ON student; ALTER TABLE student DROP INDEX uk_phone; -- 主键索引创建/删除 ALTER TABLE student ADD PRIMARY KEY (id); ALTER TABLE student DROP PRIMARY KEY;六、增删改查 CRUD最常用INSERT 插入-- 单行插入 INSERT INTO student(name,age) VALUES (张三,18); -- 多行批量插入 INSERT INTO student(name,age) VALUES (李四,19),(王五,20); -- 插入默认值 INSERT INTO student() VALUES(); -- 复制查询结果插入 INSERT INTO student_copy SELECT id,name,age FROM student WHERE age18;SELECT 查询核心-- 查询全部 SELECT * FROM student; -- 指定字段别名 SELECT id,name AS 姓名 FROM student; -- 条件查询 SELECT * FROM student WHERE age 18 AND name LIKE 张%; -- 模糊匹配 %任意字符 _单个字符 SELECT * FROM student WHERE name LIKE _三; -- 范围 IN SELECT * FROM student WHERE age IN (18,19,20); SELECT * FROM student WHERE age BETWEEN 18 AND 22; -- 空值判断 IS NULL / IS NOT NULL SELECT * FROM student WHERE phone IS NULL; -- 排序 ORDER BY SELECT * FROM student ORDER BY age DESC, id ASC; -- 分页 LIMIT 偏移量,条数 SELECT * FROM student LIMIT 0,10; -- 第1页10条 SELECT * FROM student LIMIT 10 OFFSET 10; -- 第二页 -- 去重 DISTINCT SELECT DISTINCT age FROM student; -- 聚合函数 count/sum/avg/max/min SELECT COUNT(*) total,AVG(age) avg_age,MAX(age),MIN(age) FROM student; -- 分组 GROUP BY HAVING 分组过滤 SELECT age,COUNT(*) num FROM student GROUP BY age HAVING COUNT(*)2;UPDATE 更新-- 基础更新务必加WHERE否则全表更新 UPDATE student SET age20 WHERE id1; -- 多字段更新 UPDATE student SET name张三三,age19 WHERE id2;DELETE 删除-- 删除指定数据 DELETE FROM student WHERE id10; -- 删除符合条件多条 DELETE FROM student WHERE age16;七、联表查询多表JOIN-- 内连接 INNER JOIN 两边匹配数据才展示 SELECT s.id,s.name,c.class_name FROM student s INNER JOIN class c ON s.class_id c.id; -- 左连接 LEFT JOIN 左表全部右表无匹配显示null SELECT s.*,c.class_name FROM student s LEFT JOIN class c ON s.class_idc.id; -- 右连接 RIGHT JOIN SELECT s.*,c.class_name FROM student s RIGHT JOIN class c ON s.class_idc.id; -- 子查询 SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE school_id1); -- 别名简化 SELECT a.name,b.score FROM student a,score b WHERE a.idb.stu_id;八、视图 VIEW-- 创建视图 CREATE VIEW v_student AS SELECT id,name,age FROM student WHERE age18; -- 查询视图和查表一样 SELECT * FROM v_student; -- 修改视图 ALTER VIEW v_student AS SELECT id,name FROM student; -- 删除视图 DROP VIEW IF EXISTS v_student; -- 查看所有视图 SHOW FULL TABLES WHERE TABLE_TYPEVIEW;九、存储过程 PROCEDURE-- 创建存储过程 DELIMITER // -- 修改语句结束符为// CREATE PROCEDURE get_stu(IN p_age INT) BEGIN SELECT * FROM student WHERE age p_age; END // DELIMITER ; -- 恢复分号结束 -- 调用存储过程 CALL get_stu(18); -- 查看存储过程 SHOW PROCEDURE STATUS; -- 删除存储过程 DROP PROCEDURE IF EXISTS get_stu;十、事务InnoDB引擎-- 开启事务 START TRANSACTION; BEGIN; -- 执行增删改 UPDATE account SET moneymoney-100 WHERE id1; UPDATE account SET moneymoney100 WHERE id2; -- 提交事务永久生效 COMMIT; -- 回滚事务撤销所有操作 ROLLBACK; -- 设置自动提交默认开启1关闭0 SET autocommit 0; SET autocommit 1;十一、备份与恢复mysqldump 外部命令备份整个数据库mysqldump -uroot -p test_db test_back.sql备份单张表mysqldump -uroot -p test_db student student.sql备份所有库mysqldump -uroot -p --all-databases all_db.sqlSQL文件恢复数据库mysql -uroot -p test_db test_back.sql # MySQL客户端内恢复 USE test_db; source /data/test_back.sql;十二、常用运维查看命令-- 查看MySQL版本 SELECT VERSION(); -- 查看当前会话字符集 SHOW VARIABLES LIKE %character%; -- 查看存储引擎 SHOW ENGINES; -- 查看当前连接进程 SHOW PROCESSLIST; -- 杀死卡死连接 KILL 进程ID; -- 查看慢查询配置 SHOW VARIABLES LIKE slow_query%; -- 查看数据库大小、表大小 SELECT TABLE_NAME,DATA_LENGTH/1024/1024 data_mb FROM information_schema.TABLES WHERE TABLE_SCHEMAtest_db; -- 刷新日志、权限 FLUSH LOGS; FLUSH PRIVILEGES; FLUSH TABLES;十三、常用函数字符串函数CONCAT(name,-,age) 拼接字符串 LENGTH(name) 字符长度 SUBSTR(name,1,2) 截取 REPLACE(name,张,李) 替换 LOWER/UPPER 大小写转换 TRIM() 去除首尾空格时间函数NOW() 当前年月日时分秒 CURDATE() 当前日期 CURTIME() 当前时间 DATE_ADD(now(),INTERVAL 1 DAY) 加1天 DATE_SUB(now(),INTERVAL 1 MONTH) 减1月 DATEDIFF(2026-01-01,NOW()) 日期差值数值函数ROUND(1.234,2) 四舍五入保留2位 FLOOR() 向下取整 CEIL()向上取整 SUM/AVG/MAX/MIN/COUNT 聚合十四、条件控制IF CASE-- 查询条件判断 SELECT name,IF(age18,成年,未成年) AS status FROM student; -- CASE多分支 SELECT name, CASE WHEN age18 THEN 未成年 WHEN age18 AND age22 THEN 青年 ELSE 成年 END AS age_group FROM student;十五、常用关键字优先级/注意事项UPDATE/DELETE 必须加WHERE否则清空全表InnoDB支持事务、外键MyISAM不支持事务字符集统一使用utf8mb4原生utf8不支持emoji批量操作建议加索引大表避免SELECT *线上禁止使用TRUNCATE生产核心表优先DELETE事务