2.3 修改数据

准备工作：创建两个数据表。

CREATE TABLE students(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    class_id int NOT NULL,
    name varchar(20) NOT NULL,
    gender varchar(1) NOT NULL DEFAULT 'M' COMMENT '性别：M-男性；F-女性',
    score int NOT NULL
);
INSERT INTO students(class_id,name,gender,score) VALUES (1,"小明",'M',90),
    (1,"小红",'F',95),(1,"小军",'M',88),(1,"小米",'F',73),(2,"小白",'F',81),
    (2,"小兵",'M',55),(2,"小林",'M',85),(3,"小新",'F',91),(3,"小王",'M',89),
    (3,"小丽",'F',88);

CREATE TABLE classes(
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL
);
INSERT INTO classes(name) VALUES ('一班'),('二班'),('三班'),('四班');

INSERT插入数据

# 插入一行数据
INSERT INTO students (class_id,name,gender,score) values (2,'大牛','M',80);
# 插入多条数据
INSERT INTO students (class_id,name,gender,score) values 
    (1, '大宝', 'M', 87),
    (2, '二宝', 'M', 81);
SELECT * FROM students;
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    90 | 小明   |
|  2 |        1 | F      |    95 | 小红   |
|  3 |        1 | M      |    88 | 小军   |
|  4 |        1 | F      |    73 | 小米   |
|  5 |        2 | F      |    81 | 小白   |
|  6 |        2 | M      |    55 | 小兵   |
|  7 |        2 | M      |    85 | 小林   |
|  8 |        3 | F      |    91 | 小新   |
|  9 |        3 | M      |    89 | 小王   |
| 10 |        3 | F      |    88 | 小丽   |
| 12 |        2 | M      |    80 | 大牛   |
+----+----------+--------+-------+--------+
*/

更新

# 更新id=1的记录
UPDATE students SET name='大牛',score=66 WHERE id=1;
SELECT * FROM students WHERE id=1;
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    66 | 大牛   |
+----+----------+--------+-------+--------+
*/

# 更新成绩
UPDATE students SET score=score+10 WHERE id>=2 AND id<=5;
SELECT * FROM students;
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    66 | 大牛   |
|  2 |        1 | F      |   105 | 小红   |
|  3 |        1 | M      |    98 | 小军   |
|  4 |        1 | F      |    83 | 小米   |
|  5 |        2 | F      |    91 | 小白   |
|  6 |        2 | M      |    55 | 小兵   |
|  7 |        2 | M      |    85 | 小林   |
|  8 |        3 | F      |    91 | 小新   |
|  9 |        3 | M      |    89 | 小王   |
| 10 |        3 | F      |    88 | 小丽   |
| 12 |        2 | M      |    80 | 大牛   |
+----+----------+--------+-------+--------+
*/

# 没有where语句，默认更高所有数据。
UPDATE students SET score=10;
SELECT * FROm students;
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  1 |        1 | M      |    10 | 大牛   |
|  2 |        1 | F      |    10 | 小红   |
|  3 |        1 | M      |    10 | 小军   |
|  4 |        1 | F      |    10 | 小米   |
|  5 |        2 | F      |    10 | 小白   |
|  6 |        2 | M      |    10 | 小兵   |
|  7 |        2 | M      |    10 | 小林   |
|  8 |        3 | F      |    10 | 小新   |
|  9 |        3 | M      |    10 | 小王   |
| 10 |        3 | F      |    10 | 小丽   |
| 12 |        2 | M      |    10 | 大牛   |
+----+----------+--------+-------+--------+
*/

删除

# 删除语句
DELETE FROM students WHERE id=1;
SELECT * FROM students;
/*
+----+----------+--------+-------+--------+
| id | class_id | gender | score | name   |
+----+----------+--------+-------+--------+
|  2 |        1 | F      |    10 | 小红   |
|  3 |        1 | M      |    10 | 小军   |
|  4 |        1 | F      |    10 | 小米   |
|  5 |        2 | F      |    10 | 小白   |
|  6 |        2 | M      |    10 | 小兵   |
|  7 |        2 | M      |    10 | 小林   |
|  8 |        3 | F      |    10 | 小新   |
|  9 |        3 | M      |    10 | 小王   |
| 10 |        3 | F      |    10 | 小丽   |
| 12 |        2 | M      |    10 | 大牛   |
*/

# 删除所有数据
DELETE FROM students;
SELECT * FROM students;
/*
Empty set (0.00 sec)

*/