mysql数据库基本操作命令语句

博主：小蓝
发布时间：2025 年 09 月 15 日
86 次浏览
暂无评论
4474字数
分类： Linux 技术杂烩

下面这份 **MySQL 数据库基本操作命令语句（实战版）** 覆盖：连接、库表、CRUD、事务锁、索引优化、用户权限与备份。所有代码后均附解释；关键处用 红色 标注。🧰

---

## 一、连接与库的创建

```bash
mysql -h 127.0.0.1 -P 3306 -u root -p
```

\*\*解释：\*\*连接服务器；`-h/-P/-u/-p` 分别是主机、端口、用户与密码提示。

```sql
CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
USE shop;
```

\*\*解释：\*\*创建库并指定 utf8mb4（完整支持表情符与多语言），`COLLATE` 设定排序/比较规则。

---

## 二、建表与约束（8.0 推荐写法）

```sql
CREATE TABLE product (
  id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  name      VARCHAR(128) NOT NULL,
  price     DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  status    TINYINT NOT NULL DEFAULT 0,  -- 0:下架 1:上架
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                              ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_name(name),
  INDEX idx_status_time(status, created_at)
) ENGINE=InnoDB;
```

**解释：**

* `DECIMAL` 确保金额精度；`CHECK` 从 8.0 起生效；`ON UPDATE` 自动维护更新时间。
* 唯一键 保证商品名不重复；联合 索引 为状态+时间查询加速。
* `InnoDB` 默认引擎，支持 事务 和外键。

---

## 三、增删改查（CRUD）

```sql
-- 增：插入与去重写入(UPSERT)
INSERT INTO product(name, price, status)
VALUES ('Keyboard', 199.00, 1);

INSERT INTO product(id, name, price, status)
VALUES (1,'Keyboard', 199.00, 1)
ON DUPLICATE KEY UPDATE price=VALUES(price), status=VALUES(status);
```

\*\*解释：\*\*常规插入；若主键/唯一键冲突则更新（高并发去重的简法）。

```sql
-- 查：条件/排序/分页
SELECT id,name,price FROM product
WHERE status=1 AND price BETWEEN 100 AND 500
ORDER BY updated_at DESC
LIMIT 10 OFFSET 0;
```

**解释：**`ORDER BY` 命中 `idx_status_time` 的前缀列，分页建议配合主键游标进一步优化。

```sql
-- 改：批量更新
UPDATE product SET status=0
WHERE updated_at < NOW() - INTERVAL 90 DAY;
```

\*\*解释：\*\*带条件更新，避免全表误改；条件列需有索引支撑。

```sql
-- 删：按主键安全删除
DELETE FROM product WHERE id=123;
```

\*\*解释：\*\*删除必须带 WHERE；批量删除建议分批或加限制。

---

## 四、事务与锁（资金/库存等强一致场景）

```sql
START TRANSACTION;
SELECT price FROM product WHERE id=123 FOR UPDATE;  -- 行锁
UPDATE product SET price=price+10 WHERE id=123;
COMMIT;  -- 异常则 ROLLBACK;
```

**解释：**`FOR UPDATE` 在当前事务内锁定目标行，防止并发覆盖；多表更新务必保持相同的锁定顺序以降低死锁概率。必要时设置隔离级：`SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;`

---

## 五、关联查询（JOIN）与聚合

```sql
-- 假设有订单表 order_item(user_id, product_id, qty, created_at)
SELECT p.id,p.name,SUM(o.qty) AS sold_qty
FROM product p
JOIN order_item o ON o.product_id=p.id
WHERE o.created_at >= NOW() - INTERVAL 30 DAY
GROUP BY p.id,p.name
HAVING SUM(o.qty) > 100
ORDER BY sold_qty DESC;
```

**解释：**`JOIN` 字段需有索引；`GROUP BY + HAVING` 先聚合再过滤，计算近 30 天热销榜。

---

## 六、索引与执行计划

```sql
CREATE INDEX idx_product_name ON product(name);
EXPLAIN ANALYZE
SELECT id FROM product WHERE name='Keyboard';
```

**解释：**创建单列索引；`EXPLAIN ANALYZE` 给出**实际**执行开销，关注是否 Using index、扫描行数与耗时，从而判断是否需要复合索引或改写语句。

---

## 七、用户与权限（8.0 缺省认证）

```sql
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass#2025';
GRANT SELECT, INSERT, UPDATE, DELETE
  ON shop.* TO 'app'@'%';
-- 8.0 中 GRANT 立即生效，无需手动 FLUSH
```

\*\*解释：\*\*创建业务账号并授予最小权限（最小化授权）；生产环境禁止给广泛的 `ALL PRIVILEGES`。

---

## 八、备份与恢复（命令行）

```bash
# 备份某库
mysqldump -h127.0.0.1 -uroot -p --single-transaction --routines --triggers shop > shop_$(date +%F).sql
# 恢复
mysql -h127.0.0.1 -uroot -p shop < shop_2025-09-13.sql
```

**解释：**`--single-transaction` 在 InnoDB 上实现一致性快照备份；同时包含存储过程与触发器；恢复时指定目标库。

---

## 九、常见目标与做法对照表（vditor/Markdown）

| 目标       | 推荐语句                               | 关键点             | 验证                          |
| ---------- | -------------------------------------- | ------------------ | ----------------------------- |
| 去重写入   | `INSERT ... ON DUPLICATE KEY UPDATE` | 依赖主键/唯一键    | 观察受影响行数                |
| 强一致更新 | 事务 +`FOR UPDATE`                   | 严格锁顺序、防死锁 | `SHOW ENGINE INNODB STATUS` |
| 热榜统计   | `JOIN + GROUP BY + HAVING`           | 连接列建索引       | `EXPLAIN ANALYZE`           |
| 查询提速   | 复合索引                               | 遵循最左前缀       | 计划是否走索引                |
| 最小授权   | `GRANT`精确到库/表                   | 禁止滥给 ALL       | 用业务账号验证                |

---

## 十、日常操作流程（vditor/Markdown）

```mermaid
flowchart TD
  A[确定业务目标] --> B[写出最简单SQL]
  B --> C[为过滤/连接列建索引]
  C --> D[EXPLAIN ANALYZE 校验]
  D --> E{需要事务?}
  E -- 是 --> F[加事务与行锁/顺序]
  E -- 否 --> G[保持幂等与分页]
  F --> H[压测与慢日志观察]
  G --> H[压测与慢日志观察]
```

---

### 一句话总结

以 utf8mb4 建库、以 约束+索引 保证正确与速度，读写遵循 CRUD + 事务行锁 的基本盘，结合 `EXPLAIN ANALYZE` 与 最小权限 运维，即可把 MySQL 的日常开发与上线需求稳稳拿下。✅

蓝易云是一家专注于香港及国内数据中心服务的提供商，提供高质量的服务器租用和云计算服务、包括免备案香港服务器、香港CN2、美国服务器、海外高防服务器、国内高防服务器、香港VPS等。致力于为用户提供稳定,快速的网络连接和优质的客户体验。

最后修改：2025 年 09 月 15 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

mysql数据库基本操作命令语句

小蓝 • 2025 年 09 月 15 日

---

## 一、连接与库的创建

```bash
mysql -h 127.0.0.1 -P 3306 -u root -p
```

\*\*解释：\*\*连接服务器；`-h/-P/-u/-p` 分别是主机、端口、用户与密码提示。

```sql
CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
USE shop;
```

\*\*解释：\*\*创建库并指定 utf8mb4（完整支持表情符与多语言），`COLLATE` 设定排序/比较规则。

---

## 二、建表与约束（8.0 推荐写法）

**解释：**

---

## 三、增删改查（CRUD）

```sql
-- 增：插入与去重写入(UPSERT)
INSERT INTO product(name, price, status)
VALUES ('Keyboard', 199.00, 1);

INSERT INTO product(id, name, price, status)
VALUES (1,'Keyboard', 199.00, 1)
ON DUPLICATE KEY UPDATE price=VALUES(price), status=VALUES(status);
```

\*\*解释：\*\*常规插入；若主键/唯一键冲突则更新（高并发去重的简法）。

```sql
-- 查：条件/排序/分页
SELECT id,name,price FROM product
WHERE status=1 AND price BETWEEN 100 AND 500
ORDER BY updated_at DESC
LIMIT 10 OFFSET 0;
```

**解释：**`ORDER BY` 命中 `idx_status_time` 的前缀列，分页建议配合主键游标进一步优化。

```sql
-- 改：批量更新
UPDATE product SET status=0
WHERE updated_at < NOW() - INTERVAL 90 DAY;
```

\*\*解释：\*\*带条件更新，避免全表误改；条件列需有索引支撑。

```sql
-- 删：按主键安全删除
DELETE FROM product WHERE id=123;
```

\*\*解释：\*\*删除必须带 WHERE；批量删除建议分批或加限制。

---

## 四、事务与锁（资金/库存等强一致场景）

```sql
START TRANSACTION;
SELECT price FROM product WHERE id=123 FOR UPDATE;  -- 行锁
UPDATE product SET price=price+10 WHERE id=123;
COMMIT;  -- 异常则 ROLLBACK;
```

---

## 五、关联查询（JOIN）与聚合

**解释：**`JOIN` 字段需有索引；`GROUP BY + HAVING` 先聚合再过滤，计算近 30 天热销榜。

---

## 六、索引与执行计划

```sql
CREATE INDEX idx_product_name ON product(name);
EXPLAIN ANALYZE
SELECT id FROM product WHERE name='Keyboard';
```

**解释：**创建单列索引；`EXPLAIN ANALYZE` 给出**实际**执行开销，关注是否 Using index、扫描行数与耗时，从而判断是否需要复合索引或改写语句。

---

## 七、用户与权限（8.0 缺省认证）

```sql
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass#2025';
GRANT SELECT, INSERT, UPDATE, DELETE
  ON shop.* TO 'app'@'%';
-- 8.0 中 GRANT 立即生效，无需手动 FLUSH
```

\*\*解释：\*\*创建业务账号并授予最小权限（最小化授权）；生产环境禁止给广泛的 `ALL PRIVILEGES`。

---

## 八、备份与恢复（命令行）

```bash
# 备份某库
mysqldump -h127.0.0.1 -uroot -p --single-transaction --routines --triggers shop > shop_$(date +%F).sql
# 恢复
mysql -h127.0.0.1 -uroot -p shop < shop_2025-09-13.sql
```

**解释：**`--single-transaction` 在 InnoDB 上实现一致性快照备份；同时包含存储过程与触发器；恢复时指定目标库。

---

## 九、常见目标与做法对照表（vditor/Markdown）

---

## 十、日常操作流程（vditor/Markdown）

---

### 一句话总结

mysql数据库基本操作命令语句

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

微信视频号视频下载工具WeChatVideoDownloader

Win11系统优化工具-Windows11一键设置

YDArk - 系统内核工具

XPay-自建个人免签收款支付系统

Windows微软常用运行库合集一键安装

优雅回答HashSet与HashMap的区别方法

Node.js的介绍：如何使用nvm进行安装以及npm的常用命令

在Ubuntu系统中为目录创建快捷方式的方法

详细解读Android中iptables的常用命令

国内最便宜的云服务器怎么租用

mysql数据库基本操作命令语句

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

mysql数据库基本操作命令语句

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款