MySQL常用SQL语句和使用场景代码示例实用指南

佚名 2026-08-28 17:10:01

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL常用SQL语句和采用场景代码示例”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

1. 数据库操作

新建数据库

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看所有数据库

SHOW DATABASES;

选择数据库

USE mydb;

删除数据库

DROP DATABASE mydb;

修改数据库

ALTER DATABASE mydb CHARACTER SET utf8mb4;

2. 表操作

新建表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    age INT DEFAULT 18,
    status TINYINT DEFAULT 1 COMMENT '1:active 0:inactive',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查看表结构

DESC users;
-- 或
DESCRIBE users;
-- 或
SHOW COLUMNS FROM users;

查看所有表

SHOW TABLES;

修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改列类型
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);

-- 重命名列
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(50);

-- 添加索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 添加外键
ALTER TABLE orders ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id);

删除表

DROP TABLE users;
-- 或
DROP TABLE IF EXISTS users;

清空表（保留表结构）

TRUNCATE TABLE users;

重命名表

RENAME TABLE old_name TO new_name;

3. 数据操作 (CRUD)

插入数据 (INSERT)

-- 插入单条记录
INSERT INTO users (username, email, password) VALUES ('zhangsan', '[email protected]', '123456');

-- 插入多条记录
INSERT INTO users (username, email, password) VALUES
    ('lisi', '[email protected]', '123456'),
    ('wangwu', '[email protected]', '123456');

-- 从另一表插入数据
INSERT INTO users_backup SELECT * FROM users;

更新数据 (UPDATE)

-- 更新单个字段
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- 更新多个字段
UPDATE users SET email = '[email protected]', status = 0 WHERE id = 1;

-- 批量更新
UPDATE users SET status = 0 WHERE age < 18;

删除数据 (DELETE)

-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 批量删除
DELETE FROM users WHERE status = 0;

-- 清空表（DELETE方式，会重置自增ID）
DELETE FROM users;

4. 查询语句

基本查询

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 去重查询
SELECT DISTINCT status FROM users;

条件查询 (WHERE)

-- 等于
SELECT * FROM users WHERE id = 1;

-- 不等于
SELECT * FROM users WHERE status != 0;

-- 多条件 AND
SELECT * FROM users WHERE age >= 18 AND age <= 60;

-- 多条件 OR
SELECT * FROM users WHERE age < 18 OR age > 60;

-- IN 查询
SELECT * FROM users WHERE id IN (1, 2, 3);

-- NOT IN 查询
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- BETWEEN 范围查询
SELECT * FROM users WHERE age BETWEEN 18 AND 60;

-- LIKE 模糊查询
SELECT * FROM users WHERE username LIKE 'zhang%';  -- 以zhang开头
SELECT * FROM users WHERE username LIKE '%san';     -- 以san结尾
SELECT * FROM users WHERE username LIKE '%an%';     -- 包含an
SELECT * FROM users WHERE username LIKE 'zh_ng';    -- _匹配单个字符

-- IS NULL 查询
SELECT * FROM users WHERE phone IS NULL;

-- IS NOT NULL 查询
SELECT * FROM users WHERE phone IS NOT NULL;

排序 (ORDER BY)

-- 升序
SELECT * FROM users ORDER BY age ASC;

-- 降序
SELECT * FROM users ORDER BY created_at DESC;

-- 多字段排序
SELECT * FROM users ORDER BY status ASC, age DESC;

限制结果集 (LIMIT)

-- 查询前N条
SELECT * FROM users LIMIT 10;

-- 分页查询（跳过M条，取N条）
SELECT * FROM users LIMIT 0, 10;   -- 第1页，每页10条
SELECT * FROM users LIMIT 10, 10;  -- 第2页，每页10条

分组查询 (GROUP BY)

-- 按状态分组统计
SELECT status, COUNT(*) as count FROM users GROUP BY status;

-- 多字段分组
SELECT status, age, COUNT(*) as count FROM users GROUP BY status, age;

-- 分组后过滤（HAVING）
SELECT status, COUNT(*) as count FROM users
GROUP BY status
HAVING count > 5;

5. 聚合函数

-- COUNT 统计数量
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;

-- SUM 求和
SELECT SUM(amount) FROM orders;

-- AVG 平均值
SELECT AVG(age) FROM users;

-- MAX 最大值
SELECT MAX(price) FROM products;

-- MIN 最小值
SELECT MIN(price) FROM products;

-- 组合使用
SELECT
    COUNT(*) as total,
    AVG(age) as avg_age,
    MAX(age) as max_age,
    MIN(age) as min_age
FROM users;

6. 连接查询

内连接 (INNER JOIN)

-- 只返回匹配的记录
SELECT u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

左连接 (LEFT JOIN)

-- 返回左表所有记录，右表没有匹配则为NULL
SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

右连接 (RIGHT JOIN)

-- 返回右表所有记录，左表没有匹配则为NULL
SELECT u.username, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

多表连接

SELECT u.username, o.order_no, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

7. 索引操作

新建索引

-- 普通索引
CREATE INDEX idx_username ON users(username);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 组合索引
CREATE INDEX idx_status_age ON users(status, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

查看索引

SHOW INDEX FROM users;

删除索引

DROP INDEX idx_username ON users;

8. 视图操作

新建视图

CREATE VIEW user_orders AS
SELECT u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

查看视图

SELECT * FROM user_orders;

删除视图

DROP VIEW user_orders;

9. 事务处理

-- 开启事务
START TRANSACTION;
-- 或
BEGIN;

-- 执行SQL
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

10. 用户权限管理

新建用户

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'testuser'@'%' IDENTIFIED BY 'password123';  -- 允许远程连接

授权

-- 授予所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'testuser'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'testuser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

查看权限

SHOW GRANTS FOR 'testuser'@'localhost';

撤销权限

REVOKE ALL PRIVILEGES ON mydb.* FROM 'testuser'@'localhost';

删除用户

DROP USER 'testuser'@'localhost';

修改密码

ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'newpassword';

11. 常用场景示例

场景1：用户注册与登录

-- 注册（插入用户）
INSERT INTO users (username, email, password) VALUES ('newuser', '[email protected]', MD5('password'));

-- 登录验证
SELECT * FROM users WHERE username = 'newuser' AND password = MD5('password');

场景2：数据分页查询

-- 通用分页公式：LIMIT (page-1)*pageSize, pageSize
SELECT * FROM users ORDER BY created_at DESC LIMIT 0, 10;   -- 第1页
SELECT * FROM users ORDER BY created_at DESC LIMIT 10, 10;  -- 第2页

场景3：数据统计报表

-- 每日新增用户统计
SELECT DATE(created_at) as date, COUNT(*) as new_users
FROM users
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- 各状态用户数量
SELECT
    CASE status
        WHEN 1 THEN '活跃'
        WHEN 0 THEN '禁用'
        ELSE '未知'
    END as status_name,
    COUNT(*) as count
FROM users
GROUP BY status;

场景4：排行榜

-- 用户消费排行TOP10
SELECT u.username, SUM(o.amount) as total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_amount DESC
LIMIT 10;

场景5：查找重复数据

-- 查找重复的邮箱
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 删除重复数据（保留ID最小的）
DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

场景6：数据导入导出

-- 导出数据到CSV文件（命令行）
mysql -u root -p -e "SELECT * FROM users" mydb > users.csv;

-- 导入CSV文件
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n';

场景7：批量更新

-- CASE WHEN 批量更新
UPDATE users
SET status = CASE
    WHEN age < 18 THEN 0
    WHEN age >= 18 AND age < 60 THEN 1
    ELSE 2
END;

场景8：时间范围查询

-- 查询最近7天的数据
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 查询本月数据
SELECT * FROM orders
WHERE YEAR(created_at) = YEAR(NOW())
AND MONTH(created_at) = MONTH(NOW());

-- 查询指定时间段
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

场景9：树形结构查询（分类）

-- 假设categories表有id, name, parent_id字段
-- 查找某分类的所有子分类（递归，MySQL 8.0+）
WITH RECURSIVE category_tree AS (
    SELECT * FROM categories WHERE id = 1
    UNION ALL
    SELECT c.* FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

场景10：软删除与恢复

-- 软删除（添加is_deleted字段）
UPDATE users SET is_deleted = 1, deleted_at = NOW() WHERE id = 1;
-- 查询未删除的数据
SELECT * FROM users WHERE is_deleted = 0;
-- 恢复数据
UPDATE users SET is_deleted = 0, deleted_at = NULL WHERE id = 1;

常用函数速查

字符串函数

CONCAT(str1, str2)      -- 连接字符串
LENGTH(str)             -- 字符串长度
UPPER(str)              -- 转大写
LOWER(str)              -- 转小写
TRIM(str)               -- 去除首尾空格
SUBSTRING(str, pos, len) -- 截取子串
REPLACE(str, old, new)  -- 替换

日期时间函数

NOW()                   -- 当前日期时间
CURDATE()               -- 当前日期
CURTIME()               -- 当前时间
DATE(datetime)          -- 提取日期部分
YEAR(date)              -- 提取年份
MONTH(date)             -- 提取月份
DAY(date)               -- 提提取日
DATE_FORMAT(date, format) -- 日期格式化
DATE_ADD(date, INTERVAL expr unit) -- 日期增加
DATE_SUB(date, INTERVAL expr unit) -- 日期减少

数值函数

ABS(num)                -- 绝对值
ROUND(num, decimals)    -- 四舍五入
CEIL(num)               -- 向上取整
FLOOR(num)              -- 向下取整
RAND()                  -- 随机数