一、基础连接操作

1. 本地连接 MySQL

# 基础连接（默认端口3306）
mysql -u root -p

# 指定端口/主机连接（远程连接）
mysql -h 192.168.1.100 -P 3306 -u root -p

• -u：指定用户名
• -p：回车后输入密码（直接写密码：-p123456，不推荐，会明文显示）
• -h：指定数据库主机（远程连接必填）
• -P：指定端口（默认 3306，非默认时必填）

2. 退出 MySQL

exit;-- 或 quit;

二、数据库（库）管理

1. 查看所有数据库

SHOW DATABASES;

2. 创建数据库

-- 基础创建（指定字符集和排序规则，避免中文乱码）

CREATE DATABASE IF NOT EXISTS test_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

• IF NOT EXISTS：避免重复创建报错

• utf8mb4：支持 emoji 和所有中文，推荐替代utf8

3. 切换数据库

USE test_db;

4. 删除数据库（谨慎！）

DROP DATABASE IF EXISTS test_db;

5. 查看当前数据库

SELECT DATABASE();

三、数据表（表）管理

1. 查看当前库所有表

SHOW TABLES;

2. 创建表（示例：用户表）

CREATE TABLE IF NOT EXISTS user (

   id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
   username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
   password VARCHAR(100) NOT NULL COMMENT '密码（建议加密存储）',
   age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
   create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

• ENGINE=InnoDB：默认存储引擎，支持事务、外键、行锁
• AUTO_INCREMENT：自增主键
• DEFAULT CURRENT_TIMESTAMP：默认当前时间
• ON UPDATE CURRENT_TIMESTAMP：更新时自动刷新时间

3. 查看表结构

-- 方式1：简易版
	DESC user;
-- 方式2：详细版（含注释）
	SHOW CREATE TABLE user;

4. 修改表（常用）

-- 1. 添加字段
ALTER TABLE user ADD COLUMN email VARCHAR(100) DEFAULT NULL COMMENT '邮箱' AFTER age;

-- 2. 修改字段类型/属性
ALTER TABLE user MODIFY COLUMN email VARCHAR(150) NOT NULL COMMENT '邮箱（非空）';

-- 3. 重命名字段
ALTER TABLE user CHANGE COLUMN email user_email VARCHAR(150) DEFAULT NULL COMMENT '用户邮箱';

-- 4. 删除字段
ALTER TABLE user DROP COLUMN user_email;

-- 5. 重命名表
ALTER TABLE user RENAME TO sys_user;

5. 删除表（谨慎！）

DROP TABLE IF EXISTS user;

四、数据增删改查

1. 新增数据（INSERT）

-- 方式1：指定字段（推荐，字段顺序可调整）
INSERT INTO user (username, password, age)
VALUES ('zhangsan', '123456', 20), ('lisi', '654321', 25);

-- 方式2：不指定字段（需按表字段顺序）
INSERT INTO user VALUES (NULL, 'wangwu', '888888', 30, NOW(), NOW());

• 自增主键可填NULL，会自动生成

2. 查询数据（SELECT）

-- 1. 查询所有字段（生产环境避免*，指定字段）
SELECT * FROM user;

-- 2. 指定字段查询
SELECT id, username, age FROM user;

-- 3. 条件查询
SELECT * FROM user WHERE age > 20 AND username LIKE 'z%';

-- 4. 排序
SELECT * FROM user ORDER BY age DESC, create_time ASC;

-- 5. 分页（MySQL特有，LIMIT 起始位置, 条数）
SELECT * FROM user LIMIT 0, 10; -- 第1页，每页10条
SELECT * FROM user LIMIT 10, 10; -- 第2页

-- 6. 去重
SELECT DISTINCT age FROM user;

-- 7. 聚合查询
SELECT COUNT(*) AS total, MAX(age) AS max_age, AVG(age) AS avg_age FROM user;

-- 条件修改（必须加WHERE，否则全表更新！）
UPDATE user SET password = '999999', age = 22 WHERE id = 1;

-- 全表更新（谨慎！）
UPDATE user SET update_time = NOW();

4. 删除数据（DELETE）

-- 条件删除（必须加WHERE，否则全表删除！）
DELETE FROM user WHERE id = 3;

-- 全表删除（清空表，自增主键不重置）
DELETE FROM user;

-- 清空表（自增主键重置，速度更快）
TRUNCATE TABLE user;

• DELETE：可回滚，自增主键不重置
• TRUNCATE：不可回滚，自增主键重置，适合清空大表

5.核心时间函数（按用途分类）

1. 获取当前时间

2. 日期格式化 / 解析

• DATE_FORMAT (日期，格式)：将时间转为指定格式（高频）

  格式符：%Y(4 位年)、%m(2 位月)、%d(2 位日)、%H(24 小时)、%i(分)、%s(秒)

• STR_TO_DATE (字符串，格式)：将字符串转为日期类型

3. 日期计算 / 提取

6.SQL 示例（直接套用）

假设存在 orders 表，含字段 order_id(订单 ID)、create_time(创建时间，datetime 类型)、pay_time(支付时间)。

1. 基础查询：格式化时间

-- 将创建时间格式化为「2026年03月09日 15:30」

SELECT order_id, DATE_FORMAT(create_time, '%Y年%m月%d日 %H:%i') AS create_time

FROM orders;

-- 将字符串「2026-03-09」转为日期类型

SELECT STR_TO_DATE('2026-03-09', '%Y-%m-%d') AS date;

2. 时间范围查询（高频）

-- 查询今天创建的订单

SELECT * FROM orders WHERE DATE(create_time) = CURDATE();

-- 查询近7天（含今天）的订单

SELECT * FROM orders WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY);

-- 查询2026年3月的订单

SELECT * FROM orders WHERE YEAR(create_time)=2026 AND MONTH(create_time)=3;

-- 查询昨天00:00到23:59的订单

SELECT * FROM orders

WHERE create_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE() - INTERVAL 1 SECOND;

五、索引管理（优化查询）

1. 创建索引

-- 1. 普通索引（最常用）
CREATE INDEX idx_user_username ON user(username);

-- 2. 唯一索引（字段值唯一）
CREATE UNIQUE INDEX idx_user_email ON user(email);

-- 3. 联合索引（多个字段）
CREATE INDEX idx_user_age_create_time ON user(age, create_time);

2. 查看索引

SHOW INDEX FROM user;

3. 删除索引

DROP INDEX idx_user_username ON user;

六、权限与用户管理

1. 创建用户

-- 创建用户（允许本地连接）
CREATE USER 'test_user'@'localhost' IDENTIFIED BY '123456';

-- 允许远程连接（% 表示所有主机）
CREATE USER 'test_user'@'%' IDENTIFIED BY '123456';

2. 授权

-- 授予test_db库所有权限给test_user（远程连接）
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'%';

-- 仅授予查询/插入权限
GRANT SELECT, INSERT ON test_db.user TO 'test_user'@'%';

-- 刷新权限（授权后必须执行）
FLUSH PRIVILEGES;

3. 撤销权限

REVOKE INSERT ON test_db.user FROM 'test_user'@'%';
FLUSH PRIVILEGES;

4. 修改密码

ALTER USER 'test_user'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

5. 删除用户

DROP USER 'test_user'@'%';

七、常用运维操作

1. 查看运行状态

-- 查看MySQL版本
SELECT VERSION();

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';

2. 导入 / 导出数据

# 导出整个数据库（终端执行）
mysqldump -u root -p test_db > test_db_backup.sql

# 导出指定表
mysqldump -u root -p test_db user > user_backup.sql

# 导入数据（先创建空库）
mysql -u root -p test_db < test_db_backup.sql

3. 事务操作

-- 开启事务
START TRANSACTION;

-- 执行操作
UPDATE user SET age = 21 WHERE id = 1;
INSERT INTO user (username, password) VALUES ('zhaoliu', '777777');

-- 提交事务
COMMIT;

-- 回滚（出错时）
ROLLBACK;