Python学习之复习MySQL-Day2（DML）

文章声明⭐⭐⭐

1. 该文章为我（有编程语言基础，非编程小白）的 MySQL复习笔记
2. 知识来源为 B站UP主（黑马程序员）的MySQL课程视频，归纳为自己的语言与理解记录于此并加以实践
3. 此前我已经学习过了MySQL，现在是在复习阶段，所以不是面向小白的教学文章
4. 不出意外的话，我大抵会 持续更新
5. 想要了解前端开发（技术栈大致有：Vue2/3、微信小程序、uniapp、HarmonyOS、NodeJS、Typescript）与Python的小伙伴，可以关注我！谢谢大家！

让我们开始今天的学习吧！

DML介绍

DML全称Data Manipulation Language，即数据操作语言，用来对表中的数据进行增删改的操作

• 添加数据（insert）
• 修改数据（update）
• 删除数据（delete）

添加数据

给指定字段添加数据

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into emp
    -> (id,username,age,gender) values
    -> (1,'Richie',21,'男');
Query OK, 1 row affected (0.03 sec)

mysql> select * from emp;
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    1 | Richie   |   21 | 男     |
+------+----------+------+--------+
1 row in set (0.00 sec)

给全部字段添加数据

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into emp values
    -> (2,'Mike',24,'男');
Query OK, 1 row affected (0.03 sec)

mysql> select * from emp;
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    1 | Richie   |   21 | 男     |
|    2 | Mike     |   24 | 男     |
+------+----------+------+--------+
2 rows in set (0.00 sec)

给指定字段添加多条数据

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into emp (id,username,age,gender)
    -> values
    -> (3,'Nancy',23,'女'),
    -> (4,'Jack',28,'女');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    1 | Richie   |   21 | 男     |
|    2 | Mike     |   24 | 男     |
|    3 | Nancy    |   23 | 女     |
|    4 | Jack     |   28 | 女     |
+------+----------+------+--------+
4 rows in set (0.00 sec)

给全部字段添加多条数据

mysql> insert into emp values
    -> (5,'Taylor',23,'女'),
    -> (6,'Joker',25,'男');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    1 | Richie   |   21 | 男     |
|    2 | Mike     |   24 | 男     |
|    3 | Nancy    |   23 | 女     |
|    4 | Jack     |   28 | 女     |
|    5 | Taylor   |   23 | 女     |
|    6 | Joker    |   25 | 男     |
+------+----------+------+--------+
6 rows in set (0.00 sec)

修改数据

修改指定条件的记录的数据

# 将id为1的记录的age改为18
update emp set age = 18 where id = 1;
# 将username为Jack的记录的username改为Pig，age改为38
update emp set username = 'Pig',age = 38 where username = 'Jack';
-------------------------------------
# 原来的
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    1 | Richie   |   21 | 男     |
|    2 | Mike     |   24 | 男     |
|    3 | Nancy    |   23 | 女     |
|    4 | Jack     |   28 | 女     |
|    5 | Taylor   |   23 | 女     |
|    6 | Joker    |   25 | 男     |
+------+----------+------+--------+
# 更改后
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    1 | Richie   |   18 | 男     |
|    2 | Mike     |   24 | 男     |
|    3 | Nancy    |   23 | 女     |
|    4 | pig      |   38 | 女     |
|    5 | Taylor   |   23 | 女     |
|    6 | Joker    |   25 | 男     |
+------+----------+------+--------+

修改全部记录的数据

mysql> update emp set age = 18;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 7  Changed: 6  Warnings: 0

mysql> select * from emp;
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    1 | Richie   |   18 | 男     |
|    2 | Mike     |   18 | 男     |
|    3 | Nancy    |   18 | 女     |
|    4 | Pig      |   18 | 女     |
|    5 | Taylor   |   18 | 女     |
|    6 | Joker    |   18 | 男     |
|    7 | Dragon   |   18 | 男     |
+------+----------+------+--------+
7 rows in set (0.00 sec)

删除数据

mysql> delete from emp where gender = '男'; # 删除所有gender为男的记录
Query OK, 4 rows affected (0.01 sec)

mysql> select * from emp;
+------+----------+------+--------+
| id   | username | age  | gender |
+------+----------+------+--------+
|    3 | Nancy    |   18 | 女     |
|    4 | Pig      |   18 | 女     |
|    5 | Taylor   |   18 | 女     |
+------+----------+------+--------+
3 rows in set (0.00 sec)

mysql> delete from emp; # 删除所有记录
Query OK, 3 rows affected (0.03 sec)

mysql> select * from emp;
Empty set (0.03 sec)