MySQL 基础用法:CRUD

CRUD 是对表中数据的四种基本操作:

操作 含义 SQL 语句
Create 增加数据 INSERT
Read 查询数据 SELECT
Update 修改数据 UPDATE
Delete 删除数据 DELETE

本文只练习这四种操作。下面的例子使用同一张 student 表;按顺序执行建表和第 1 节的两条插入语句,就能得到后续查询使用的三行数据。

准备 student 表

在练习用的 MySQL 数据库中执行一次:

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(10)
) DEFAULT CHARSET=utf8mb4;

id 是自增主键,插入学生时可以不填写。后面写 SQL 时,字段名要与建表语句一致:这里是 gender,不是 sex。

1. 增:INSERT

指定字段,再按相同顺序填写值:

INSERT INTO student (name, age, gender)
VALUES ('张三', 18, '男');

基本格式是 INSERT INTO 表名 (字段...) VALUES (值...);。因为 id 会自动生成,字段列表里不需要写它。

一次也可以插入多行。接着执行下面这条语句:

INSERT INTO student (name, age, gender)
VALUES
    ('李四', 20, '男'),
    ('小红', 19, '女');

此时 student 表中的数据是:

id name age gender
1 张三 18 男
2 李四 20 男
3 小红 19 女

2. 查:SELECT

查询所有行、所有字段:

SELECT * FROM student;

* 表示所有字段。只需要姓名和年龄时,明确列出字段:

SELECT name, age
FROM student;

查询特定行时,用 WHERE 写条件:

SELECT name, age
FROM student
WHERE age > 18;

这条语句返回李四和小红;张三的年龄等于 18,不满足 > 18。如果希望包含 18 岁,改用 >= 18。

多个条件同时成立,用 AND 连接:

SELECT name, age
FROM student
WHERE age >= 18 AND gender = '男';

这条语句只返回张三和李四的 name、age。可以把查询读作:SELECT 选字段,FROM 找表,WHERE 筛行。日常查询需要哪些字段,通常就明确写出哪些字段。

3. 改:UPDATE

修改前,先用相同的 WHERE 条件确认会选中哪几行:

SELECT id, name, age
FROM student
WHERE id = 2;

确认只选中李四后,将他的年龄改为 21:

UPDATE student
SET age = 21
WHERE id = 2;

基本格式是 UPDATE 表名 SET 字段 = 新值 WHERE 条件;。修改多个字段时,在 SET 后用逗号分隔:

UPDATE student
SET name = '李明',
    age = 22
WHERE id = 2;

WHERE id = 2 只匹配一行。若省略 WHERE,例如 UPDATE student SET age = 20;,就会把表中所有学生的年龄改为 20。有 WHERE 也不一定只改一行:WHERE gender = '男' 会匹配所有男生。

4. 删:DELETE

删除前,也先用 SELECT 确认条件命中的行:

SELECT id, name
FROM student
WHERE id = 3;

确认只选中小红后,再删除:

DELETE FROM student
WHERE id = 3;

基本格式是 DELETE FROM 表名 WHERE 条件;。

如果写成 DELETE FROM student;,表中的所有行都会被删除,但 student 表本身仍然存在。WHERE gender = '男' 则会删除所有男生,不只是第一位男生。

四条语句速记

INSERT INTO 表名 (字段...) VALUES (值...);  增加行
SELECT 字段... FROM 表名 WHERE 条件;       查询行
UPDATE 表名 SET 字段 = 值 WHERE 条件;     修改行
DELETE FROM 表名 WHERE 条件;             删除行

写 INSERT 时核对字段名和对应的值;写 UPDATE、DELETE 时先核对 WHERE 命中的范围。

练习

以下四题各自以第 1 节插入完成后的三行数据为起点,不用把前一题的改动带到下一题。先自己写,再看参考答案。

  1. 插入“王五,21 岁,男”,由数据库生成 id。
  2. 只查询年龄至少 18 岁的男生的 name 和 age。原始数据中会返回谁?
  3. 将 id = 2 的学生年龄改成 21。若漏掉 WHERE,会发生什么?
  4. 删除 id = 3 的学生。若改为 WHERE gender = '男',会删掉谁?

<details> <summary>参考答案</summary>

-- 1
INSERT INTO student (name, age, gender)
VALUES ('王五', 21, '男');

-- 2
SELECT name, age
FROM student
WHERE age >= 18 AND gender = '男';

-- 3
UPDATE student
SET age = 21
WHERE id = 2;

-- 4
DELETE FROM student
WHERE id = 3;

第 2 题返回张三和李四。第 3 题若漏掉 WHERE,所有学生的年龄都会变成 21。第 4 题改用 WHERE gender = '男',会删除张三和李四。

</details>

下一步可以学习排序、分页和聚合查询。建表与索引的练习见下一篇:建表与索引。