Python学习之复习MySQL-Day2(DML)

2023-12-17 14:27:37


文章声明???

  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)

文章来源:https://blog.csdn.net/Richieeea/article/details/134976063
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。