MySQL札记7_DML（数据操作语言）

DML，data manipulation language，数据操作语言， 主要是对数据库中的表记录进行操作的语言，包含表中插入数据、表中数据的更新、表的删除等：

• 表中插入数据
• 表中数据更新
• 删除表

---

复习创建表

在对表中的数据进行操作之前复习DDL（数据定义语言）中关于如何在数据库中创建一个表user：

# 一个主键+6个字段
mysql> create table user(
    -> id int(10) unsigned not null auto_increment comment "user_id",
    -> user_name varchar(20) not null comment "user_name",
    -> user_email varchar(20) not null comment "user_email",
    -> user_age tinyint unsigned not null comment "user_age",
    -> fee decimal(10,2) not null default 0.00 comment "user_fee",
    -> created_at timestamp not null comment "created_time",
    -> primary key(id) 
    -> );
Query OK, 0 rows affected (0.07 sec)

删除和增减字段

将created_at字段进行删除，同时在指定位置添加字段：

• 删除字段drop

mysql> alter table user drop created_at;    # 删除字段
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

• 指定位置添加字段add

mysql> alter table user add password char(30) not null comment "user_password" after user_age;

查看重新建好的表

mysql> desc user;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_name  | varchar(20)         | NO   |     | NULL    |                |
| user_email | varchar(20)         | NO   |     | NULL    |                |
| user_age   | tinyint(3) unsigned | NO   |     | NULL    |                |
| password   | char(30)            | NO   |     | NULL    |                |
| fee        | decimal(10,2)       | NO   |     | 0.00    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

插入数据

经常说的“增”就是指向某个表中插入数据，关键词：insert，格式为：

insert into 表名 (字段1，字段2， 字段3....)  values (值1， 值2， 值3....)   

特点

• 字段中id可以不用写，默认是自增的
• 字段和值一一对应关系，否则插入失败
• 字符串的字段必须加上引号
• 密码字段有时需要使用password()函数
• 如果省去字段部分，values后面需要加上id

栗子

列出字段和值进行插入

mysql> insert into user (user_name, user_email, user_age, password, fee) values("peter", "12345@163.com", 28, "123321", 25.28);    # 插入数据
Query OK, 1 row affected (0.01 sec)

# 看结果
mysql> select * from user;   # 查看数据，属于DQL（数据查询语言）内容，先学习下
+----+-----------+---------------+----------+----------+-------+
| id | user_name | user_email    | user_age | password | fee   |
+----+-----------+---------------+----------+----------+-------+
|  1 | peter     | 12345@163.com |       28 | 123321   | 25.28 |
+----+-----------+---------------+----------+----------+-------+
1 row in set (0.00 sec)

插入部分字段

mysql> insert into user (user_name, user_age) values("Jack", 24);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from user;
+----+-----------+---------------+----------+----------+-------+
| id | user_name | user_email    | user_age | password | fee   |
+----+-----------+---------------+----------+----------+-------+
|  1 | peter     | 12345@163.com |       28 | 123321   | 25.28 |
|  2 | Jack      |               |       24 |          |  0.00 |
+----+-----------+---------------+----------+----------+-------+
2 rows in set (0.00 sec)

直接插入values

mysql> insert into user values(3, "Tom", "987123@163.com", 20, "678543", 87.24);
Query OK, 1 row affected (0.01 sec)

# 查看
mysql> select * from user;
+----+-----------+----------------+----------+----------+-------+
| id | user_name | user_email     | user_age | password | fee   |
+----+-----------+----------------+----------+----------+-------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 25.28 |
|  2 | Jack      |                |       24 |          |  0.00 |
|  3 | Tom       | 987123@163.com |       20 | 678543   | 87.24 |
+----+-----------+----------------+----------+----------+-------+
3 rows in set (0.00 sec)

用这种方式插入需要注意的是：一定要带上id，不然报错

image.png

更新表中数据

• 更新数据的关键词是update；
• 同时一定要带上where条件，一般是id号；不然后果严重
• 语法：

update 表名 set 字段1=值1，字段2=值2 where 条件

修改一个字段

mysql> update user set user_name="Jim" where id=3;     # id为3的名字修改
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+-----------+----------------+----------+----------+-------+
| id | user_name | user_email     | user_age | password | fee   |
+----+-----------+----------------+----------+----------+-------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 25.28 |
|  2 | Jack      |                |       24 |          |  0.00 |
|  3 | Jim       | 987123@163.com |       20 | 678543   | 87.24 |
+----+-----------+----------------+----------+----------+-------+
3 rows in set (0.00 sec)

• 字段作为where条件

mysql> update user set user_email="987456@163.com" where user_name="Jack";   # 字段作为where条件
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from  user;
+----+-----------+----------------+----------+----------+--------+
| id | user_name | user_email     | user_age | password | fee    |
+----+-----------+----------------+----------+----------+--------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 500.00 |
|  2 | Jack      | 987456@163.com |       22 | 101010   | 500.00 |
|  3 | Jim       | 987123@163.com |       20 | 678543   | 500.00 |
+----+-----------+----------------+----------+----------+--------+
3 rows in set (0.00 sec)

image.png

修改多个字段

mysql> update user set user_age=22,password="101010" where id=2;   # 多个字段修改
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+-----------+----------------+----------+----------+-------+
| id | user_name | user_email     | user_age | password | fee   |
+----+-----------+----------------+----------+----------+-------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 25.28 |
|  2 | Jack      |                |       22 | 101010   |  0.00 |
|  3 | Jim       | 987123@163.com |       20 | 678543   | 87.24 |
+----+-----------+----------------+----------+----------+-------+
3 rows in set (0.00 sec)

注意：多个字段之间用逗号隔开，否则报错

image.png

使用运算符修改

• !=：不等于

mysql> update user set fee=28 where id!=1;    # 不等于运算符
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from user;
+----+-----------+----------------+----------+----------+-------+
| id | user_name | user_email     | user_age | password | fee   |
+----+-----------+----------------+----------+----------+-------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 25.28 |
|  2 | Jack      |                |       22 | 101010   | 28.00 |
|  3 | Jim       | 987123@163.com |       20 | 678543   | 28.00 |
+----+-----------+----------------+----------+----------+-------+
3 rows in set (0.00 sec)

image.png

• in的用法

mysql> update user set fee=100 where id in(1, 3);   # in的用法
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from user;
+----+-----------+----------------+----------+----------+--------+
| id | user_name | user_email     | user_age | password | fee    |
+----+-----------+----------------+----------+----------+--------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 100.00 |
|  2 | Jack      |                |       22 | 101010   |  28.00 |
|  3 | Jim       | 987123@163.com |       20 | 678543   | 100.00 |
+----+-----------+----------------+----------+----------+--------+
3 rows in set (0.00 sec)

image.png

• between...and...的用法

mysql> update user set fee=500 where id between 1 and 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from user;
+----+-----------+----------------+----------+----------+--------+
| id | user_name | user_email     | user_age | password | fee    |
+----+-----------+----------------+----------+----------+--------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 500.00 |
|  2 | Jack      |                |       22 | 101010   | 500.00 |
|  3 | Jim       | 987123@163.com |       20 | 678543   | 500.00 |
+----+-----------+----------------+----------+----------+--------+
3 rows in set (0.00 sec)

image.png

小结

• 插入形式多样
• 同时插入多个，一定要带上逗号
• where条件不能省略

---

删除表

删除表有两种情况：

• delete：删除表，插入数据从上一次结束的id号开始继续插入；删除的记录仍存在
• truncate：清空表，重新插入数据id从1开始；不占内存空间

delete

mysql> delete from user where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+-----------+----------------+----------+----------+--------+
| id | user_name | user_email     | user_age | password | fee    |
+----+-----------+----------------+----------+----------+--------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 500.00 |
|  2 | Jack      | 987456@163.com |       22 | 101010   | 500.00 |
+----+-----------+----------------+----------+----------+--------+
2 rows in set (0.00 sec)

mysql> insert into user (user_name, user_email, fee) values("Tom", "987123@163.com", 87.24);
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> select * from user;
+----+-----------+----------------+----------+----------+--------+
| id | user_name | user_email     | user_age | password | fee    |
+----+-----------+----------------+----------+----------+--------+
|  1 | peter     | 12345@163.com  |       28 | 123321   | 500.00 |
|  2 | Jack      | 987456@163.com |       22 | 101010   | 500.00 |
|  4 | Tom       | 987123@163.com |        0 |          |  87.24 |
+----+-----------+----------------+----------+----------+--------+
3 rows in set (0.00 sec)

image.png

• truncate

# 删除数据
mysql> truncate table user;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from user;
Empty set (0.00 sec)

# 重新插入数据
mysql> insert into user (user_name, user_email, user_age, password, fee) values ("peter", "123456a@163.com", 27, password("101010"), 28.87);
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> select * from user;
+----+-----------+-----------------+----------+--------------------------------+-------+
| id | user_name | user_email      | user_age | password                       | fee   |
+----+-----------+-----------------+----------+--------------------------------+-------+
|  1 | peter     | 123456a@163.com |       27 | *C3BC3E91915DCAE22014892F9827D | 28.87 |
+----+-----------+-----------------+----------+--------------------------------+-------+
1 row in set (0.00 sec)

image.png