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

作者：袖梨 2026-08-27

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

目录
  • 1. 数据库操作
    • 新建数据库
    • 查看所有数据库
    • 选择数据库
    • 删除数据库
    • 修改数据库
  • 2. 表操作
    • 新建表
    • 查看表结构
    • 查看所有表
    • 修改表结构
    • 删除表
    • 清空表（保留表结构）
    • 重命名表
  • 3. 数据操作 (CRUD)
    • 插入数据 (INSERT)
    • 更新数据 (UPDATE)
    • 删除数据 (DELETE)
  • 4. 查询语句
    • 基本查询
    • 条件查询 (WHERE)
    • 排序 (ORDER BY)
    • 限制结果集 (LIMIT)
    • 分组查询 (GROUP BY)
  • 5. 聚合函数
  • 6. 连接查询
    • 内连接 (INNER JOIN)
    • 左连接 (LEFT JOIN)
    • 右连接 (RIGHT JOIN)
    • 多表连接
  • 7. 索引操作
    • 新建索引
    • 查看索引
    • 删除索引
  • 8. 视图操作
    • 新建视图
    • 查看视图
    • 删除视图
  • 9. 事务处理
  • 10. 用户权限管理
    • 新建用户
    • 授权
    • 查看权限
    • 撤销权限
    • 删除用户
    • 修改密码
  • 11. 常用场景示例
    • 场景1：用户注册与登录
    • 场景2：数据分页查询
    • 场景3：数据统计报表
    • 场景4：排行榜
    • 场景5：查找重复数据
    • 场景6：数据导入导出
    • 场景7：批量更新
    • 场景8：时间范围查询
    • 场景9：树形结构查询（分类）
    • 场景10：软删除与恢复
    • 常用函数速查
      • 字符串函数
      • 日期时间函数
      • 数值函数
      • 性能优化建议
  • 总结

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()                  -- 随机数

性能优化建议

  • 采用索引：为经常查询的WHERE、JOIN、ORDER BY字段添加索引

  • 避免 SELECT *：只查询需的字段

  • 合理采用 LIMIT：限制得到结果数量

  • 避免在WHERE中采用函数：会导致索引失效

  • 采用 EXPLAIN：分析查询执行计划

  • 优化表结构：选择合适的数据类型

  • 分页优化：大数据量分页采用子查询或游标

  • 批量操作：采用批量插入代替单条插入

总结

到此这篇关于MySQL常用SQL语句和采用场景的文章就介绍到这了,更多相关MySQL常用SQL语句采用场景内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！

您可能感兴趣的文章:
  • MySQL 最基本的SQL语法/语句
  • 浅谈MySQL中优化sql语句查询常用的30种方法
  • Mysql查询最近一条记录的sql语句(优化篇)
  • MySQL查询语句大全集锦
  • MySQL 截取字符串函数的sql语句

相关文章

精彩推荐