LiuZhaoSen
文章背景图

MySQL常用语句

发表于 2026-04-05后端

MySQL 常用语句

1. 引言

MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），广泛应用于各种规模的应用程序中。本文档旨在为初学者提供一份详细的 MySQL 常用语句指南，涵盖数据库和表的创建、管理，以及最核心的增删改查（CRUD）操作。

2. 数据库操作

2.1 创建数据库

使用 CREATE DATABASE 语句来创建一个新的数据库。建议指定字符集和排序规则，以避免未来的乱码问题。

CREATE DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
  • database_name: 你希望创建的数据库的名称。
  • CHARACTER SET utf8mb4: 指定数据库的字符集为 utf8mb4，支持存储包括表情符号在内的所有 Unicode 字符。
  • COLLATE utf8mb4_unicode_ci: 指定数据库的排序规则，unicode_ci 表示不区分大小写和重音。

2.2 选择数据库

在对数据库中的表进行操作之前，需要先选择要使用的数据库。

USE database_name;
  • database_name: 你希望使用的数据库的名称。

2.3 删除数据库

使用 DROP DATABASE 语句来删除一个数据库。此操作不可逆，请谨慎使用。

DROP DATABASE database_name;
  • database_name: 你希望删除的数据库的名称。

3. 表操作

3.1 创建表

使用 CREATE TABLE 语句在选定的数据库中创建新表。创建表时需要定义表的结构，包括列名、数据类型和约束。

CREATE TABLE table_name (
    column1_name data_type [constraints],
    column2_name data_type [constraints],
    ...
    PRIMARY KEY (column_name)
);
  • table_name: 你希望创建的表的名称。
  • column_name: 列的名称。
  • data_type: 列的数据类型。
  • constraints: 列的约束条件，如 PRIMARY KEY, NOT NULL, UNIQUE 等。

常用数据类型

类型分类 数据类型 描述
数值类型 INT 整型，通常用于存储整数
BIGINT 大整型，用于存储更大的整数
DECIMAL(M, D) 精确小数，M 是总位数，D 是小数点后的位数
FLOAT 单精度浮点数
DOUBLE 双精度浮点数
字符串类型 VARCHAR(L) 可变长度字符串，L 是最大长度
CHAR(L) 固定长度字符串，L 是长度
TEXT 大文本数据
BLOB 二进制大对象，用于存储图片、文件等
日期和时间类型 DATE 日期，格式为 'YYYY-MM-DD'
TIME 时间，格式为 'HH:MM:SS'
DATETIME 日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP 时间戳，自动更新
布尔类型 BOOLEAN 布尔值，等同于 TINYINT(1)

常用约束

约束 描述
PRIMARY KEY 主键，唯一标识表中的每一行，不能为空
FOREIGN KEY 外键，用于建立两个表之间的关联，并确保参照完整性
NOT NULL 确保列中的值不能为空
UNIQUE 确保列中的所有值都是唯一的
DEFAULT value 为列设置默认值
AUTO_INCREMENT 自动递增，通常用于主键，每次插入新行时自动生成唯一值

示例： 创建一个 users 表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3.2 修改表结构

使用 ALTER TABLE 语句来修改已存在的表结构，例如添加、删除或修改列。

添加列

ALTER TABLE table_name
ADD COLUMN new_column_name data_type [constraints];

示例： 为 users 表添加 age 列

ALTER TABLE users
ADD COLUMN age INT;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

示例： 从 users 表删除 age 列

ALTER TABLE users
DROP COLUMN age;

修改列的数据类型或约束

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type [new_constraints];

示例： 修改 users 表中 username 列的长度

ALTER TABLE users
MODIFY COLUMN username VARCHAR(100) NOT NULL UNIQUE;

3.3 删除表

使用 DROP TABLE 语句来删除一个表。此操作不可逆，请谨慎使用。

DROP TABLE table_name;
  • table_name: 你希望删除的表的名称。

4. 数据操作 (CRUD)

4.1 插入数据 (CREATE - INSERT)

使用 INSERT INTO 语句向表中插入新行。

插入单行数据

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • 如果为所有列插入数据，并且顺序与表定义一致，可以省略列名。

示例： 向 users 表插入一条数据

INSERT INTO users (username, email, password_hash)
VALUES ('alice', 'alice@example.com', 'hashed_password_alice');

插入多行数据

INSERT INTO table_name (column1, column2, ...)
VALUES
    (value1_row1, value2_row1, ...),
    (value1_row2, value2_row2, ...),
    ...
;

示例： 向 users 表插入多条数据

INSERT INTO users (username, email, password_hash)
VALUES
    ('bob', 'bob@example.com', 'hashed_password_bob'),
    ('charlie', 'charlie@example.com', 'hashed_password_charlie');

从其他表插入数据

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

4.2 查询数据 (READ - SELECT)

使用 SELECT 语句从表中检索数据。这是 SQL 中最常用的语句。

基本查询

查询所有列和所有行：

SELECT * FROM table_name;

查询指定列：

SELECT column1, column2 FROM table_name;

示例： 查询 users 表的所有用户

SELECT * FROM users;

示例： 查询 users 表的 username 和 email

SELECT username, email FROM users;

条件查询 (WHERE 子句)

使用 WHERE 子句来过滤结果集，只返回满足指定条件的行。

SELECT column1, column2 FROM table_name
WHERE condition;

常用条件运算符：

运算符 描述
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
!= 或 <> 不等于
BETWEEN value1 AND value2 在某个范围内
LIKE pattern 模糊匹配
IN (value1, value2, ...) 在列表中
IS NULL 值为 NULL
IS NOT NULL 值不为 NULL
AND 逻辑与
OR 逻辑或
NOT 逻辑非

示例： 查询 id 大于 1 的用户

SELECT * FROM users WHERE id > 1;

示例： 查询 username 以 'a' 开头的用户

SELECT * FROM users WHERE username LIKE 'a%';

排序结果 (ORDER BY 子句)

使用 ORDER BY 子句对结果集进行排序。默认为升序 (ASC)，可以使用 DESC 指定降序。

SELECT column1, column2 FROM table_name
ORDER BY column_name [ASC|DESC];

示例： 按 username 升序排列用户

SELECT * FROM users ORDER BY username ASC;

限制结果数量 (LIMIT 子句)

使用 LIMIT 子句限制返回的行数，常用于分页。

SELECT column1, column2 FROM table_name
LIMIT [offset,] row_count;
  • offset: 可选，指定从哪一行开始返回（第一行为 0）。
  • row_count: 指定要返回的最大行数。

示例： 查询前 2 条用户数据

SELECT * FROM users LIMIT 2;

示例： 查询从第 2 条开始的 3 条用户数据

SELECT * FROM users LIMIT 1, 3; -- 从索引1（第二条）开始，取3条

聚合函数

聚合函数对一组值执行计算，并返回单个值。

函数 描述
COUNT(column) 返回指定列的行数
SUM(column) 返回指定列的总和
AVG(column) 返回指定列的平均值
MAX(column) 返回指定列的最大值
MIN(column) 返回指定列的最小值

示例： 查询用户总数

SELECT COUNT(*) FROM users;

分组结果 (GROUP BY 子句)

使用 GROUP BY 子句将结果集按照一个或多个列进行分组，常与聚合函数一起使用。

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1;

示例： 假设 users 表有 city 列，查询每个城市的总用户数

-- 假设 users 表有 city 列
-- SELECT city, COUNT(*) FROM users GROUP BY city;

过滤分组 (HAVING 子句)

HAVING 子句用于过滤 GROUP BY 后的分组结果，类似于 WHERE 子句，但作用于分组后的聚合值。

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1
HAVING condition_on_aggregate;

示例： 查询用户数大于 1 的城市

-- 假设 users 表有 city 列
-- SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 1;

连接查询 (JOIN)

连接查询用于将两个或多个表中的行基于相关列组合起来。

假设有两张表：

users 表：id, username, email
orders 表：order_id, user_id, amount

INNER JOIN (内连接)

返回两个表中都有匹配的行。

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

示例： 查询有订单的用户及其订单信息

SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
LEFT JOIN (左连接)

返回左表的所有行，以及右表中匹配的行。如果右表中没有匹配，则右表列显示 NULL。

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

示例： 查询所有用户及其订单信息（包括没有订单的用户）

SELECT u.username, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
RIGHT JOIN (右连接)

返回右表的所有行，以及左表中匹配的行。如果左表中没有匹配，则左表列显示 NULL。

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

示例： 查询所有订单及其对应的用户信息（包括没有对应用户的订单，虽然这种情况通常不应该发生）

SELECT u.username, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

子查询

子查询是嵌套在另一个 SQL 语句中的查询，它可以返回单个值、一行、一列或一个表。

示例： 查询购买金额大于平均购买金额的订单

-- 假设 orders 表有 amount 列
-- SELECT * FROM orders
-- WHERE amount > (SELECT AVG(amount) FROM orders);

4.3 更新数据 (UPDATE)

使用 UPDATE 语句修改表中已存在的行。

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;
  • WHERE 子句非常重要，如果没有它，将更新表中的所有行。

示例： 更新 id 为 1 的用户的邮箱

UPDATE users
SET email = 'new_alice@example.com'
WHERE id = 1;

4.4 删除数据 (DELETE)

使用 DELETE FROM 语句从表中删除行。

DELETE FROM table_name
WHERE condition;
  • WHERE 子句非常重要，如果没有它，将删除表中的所有行。

示例： 删除 id 为 3 的用户

DELETE FROM users
WHERE id = 3;

清空表 (TRUNCATE TABLE)

TRUNCATE TABLE 语句用于快速删除表中的所有行，并重置 AUTO_INCREMENT 值。它比 DELETE FROM 更快，因为它不记录单个行删除的日志，但不能回滚。

TRUNCATE TABLE table_name;

5. 其他常用操作

5.1 索引 (Indexes)

索引用于提高数据库查询的速度。它们是特殊查找表，数据库搜索时可以利用它们来加快数据检索。

创建索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

示例： 为 users 表的 email 列创建索引

CREATE INDEX idx_email ON users (email);

删除索引

DROP INDEX index_name ON table_name;

示例： 删除 users 表的 idx_email 索引

DROP INDEX idx_email ON users;

5.2 视图 (Views)

视图是基于 SQL 查询结果的虚拟表。它不存储数据，而是存储查询定义。每次查询视图时，都会执行其底层查询。

创建视图

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

示例： 创建一个只显示活跃用户的视图

-- 假设 users 表有 is_active 列
-- CREATE VIEW active_users AS
-- SELECT id, username, email FROM users WHERE is_active = 1;

删除视图

DROP VIEW view_name;

5.3 事务 (Transactions)

事务是一组 SQL 语句，这些语句作为一个单一的逻辑工作单元执行。事务要么全部成功提交，要么全部失败回滚。

事务的 ACID 特性：

  • 原子性 (Atomicity)：事务是最小的执行单位，不可再分。要么全部成功，要么全部失败。
  • 一致性 (Consistency)：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态。
  • 隔离性 (Isolation)：并发事务之间相互隔离，一个事务的执行不影响其他事务。
  • 持久性 (Durability)：事务提交后，对数据库的改变是永久的，即使系统故障也不会丢失。

事务控制语句

START TRANSACTION; -- 或 BEGIN;

-- SQL 语句 1
-- SQL 语句 2
-- ...

COMMIT; -- 提交事务，使所有更改永久生效
-- 或
ROLLBACK; -- 回滚事务，撤销所有更改

示例： 模拟转账操作

-- 假设 accounts 表有 account_id, balance 列

START TRANSACTION;

-- 从账户 A 扣除 100
-- UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- 向账户 B 增加 100
-- UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- 检查是否有错误，如果没有则提交
-- COMMIT;

-- 如果有错误，则回滚
-- ROLLBACK;

5.4 用户管理与权限

MySQL 允许创建用户并授予他们对数据库和表的特定权限。

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • username: 新用户的名称。
  • host: 用户可以从哪个主机连接，'localhost' 表示本地，'%' 表示任何主机。
  • password: 用户的密码。

示例： 创建一个名为 dev_user 的用户，只能从本地连接

CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'dev_password';

授予权限

GRANT privilege_type ON database_name.table_name TO 'username'@'host';
  • privilege_type: 要授予的权限类型，如 SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 等。
  • database_name.table_name: 指定权限作用的数据库和表。*.* 表示所有数据库和所有表。

示例： 授予 dev_user 对 mydatabase 数据库中所有表的 SELECT 和 INSERT 权限

GRANT SELECT, INSERT ON mydatabase.* TO 'dev_user'@'localhost';

撤销权限

REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

示例： 撤销 dev_user 对 mydatabase 数据库中所有表的 INSERT 权限

REVOKE INSERT ON mydatabase.* FROM 'dev_user'@'localhost';

刷新权限

在修改权限后，需要刷新权限才能生效。

FLUSH PRIVILEGES;

删除用户

DROP USER 'username'@'host';

6. 总结

本文档详细介绍了 MySQL 的核心操作，包括数据库和表的管理，以及数据操作（增删改查）。掌握这些基本语句是进行数据库开发和管理的基础。建议通过实际操作来加深理解和熟练运用这些语句。

7. 参考文献

#mysql#数据库
目录