DML语句

最新推荐文章于 2024-08-05 19:15:13 发布
最新推荐文章于 2024-08-05 19:15:13 发布
阅读量179 收藏
点赞数
分类专栏： 2021-SRE # SRE用功的笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hu_nil/article/details/114638465
版权

文章目录

DML:INSERT UPDATE DELETE

INSERT语句

功能 一次插入一行或多行数据

语法

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

实例:插入数据

-- 插入多条数据
16:50:45 (root@(none)) [db1]> select * from student;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 18  | F      |
+----+----------+-----+--------+
2 rows in set
Time: 0.009s

16:55:22 (root@(none)) [db1]> insert into student (name,age,gender) values ('xiaolan',17,'m'),('xiaolv',22,'f');
Query OK, 2 rows affected
Time: 0.065s
16:55:26 (root@(none)) [db1]> select * from student;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 18  | F      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.008s

-- 从现有表(student)中批量添加数据到新表(teacher)中
17:10:30 (root@(none)) [db1]> select * from teacher;
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
0 rows in set
Time: 0.009s
17:10:56 (root@(none)) [db1]> insert into teacher select * from student;
Query OK, 4 rows affected
Time: 0.051s
17:10:58 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 18  | F      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.009s

UPDATE语句

语法

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

注意⚠️: 一定要限制条件，否则将修改字段名内所有匹配行的数据

可使用mysql选项避免此错误发生

-- 方法一:直接在mysql命令行添加参数
mysql -U｜--safe-updates| --i-am-a-dummy   Only allow UPDATE and DELETE that uses keys.

-- 方法二:在my.cnf的配置文件中修改
vim /etc/my.cnf
[mysql]
safe-updates

17:31:41 (root@localhost) [db1]> update teacher set age=18;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

-- 方法三: 登录到数据库中修改sql_safe_updates选项
17:54:23 (root@(none)) [(none)]> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+
1 row in set
Time: 0.018s
17:54:28 (root@(none)) [(none)]> set sql_safe_updates= ON;
Query OK, 0 rows affected
Time: 0.001s
17:54:29 (root@(none)) [(none)]> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set
Time: 0.010s
17:54:30 (root@(none)) [(none)]>
17:54:30 (root@(none)) [(none)]>
17:54:30 (root@(none)) [(none)]>
17:54:35 (root@(none)) [(none)]> use db1;
You are now connected to database "db1" as user "root"
Time: 0.001s
17:54:40 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 36  | M      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.012s
17:54:46 (root@(none)) [db1]> update teacher set age=22;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
(1175, 'You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. ')
17:55:13 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 36  | M      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.009s

实例:修改多个数据

-- 修改teacher表中xiaohong的age为36，gender为M
17:19:21 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 18  | F      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.008s
17:19:34 (root@(none)) [db1]> update teacher set age=36,gender='M' where name='xiaohong';
Query OK, 1 row affected
Time: 0.015s
17:19:37 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 36  | M      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.008s

DELETE语句

删除表中数据，但不会自动缩减数据文件大小

语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

注意⚠️: 一定要限制条件，否则将清空表中所有数据

如果想清空表，保留表结构，也可以使用下面的语句，此语句会自动缩减数据文件的大小

TRUNCATE [TABLE] tbl_name

优化表空间
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...

实例:

-- 删除id为13的数据信息
10:23:35 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 36  | M      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.008s
10:24:24 (root@(none)) [db1]> delete from teacher where id = 13;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 1 row affected
Time: 0.079s
10:24:28 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 36  | M      |
| 12 | xiaolan  | 17  | M      |
+----+----------+-----+--------+
3 rows in set
Time: 0.009s

-- 清空表内容
10:27:02 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 36  | M      |
| 12 | xiaolan  | 17  | M      |
+----+----------+-----+--------+
3 rows in set
Time: 0.008s
10:27:14 (root@(none)) [db1]> delete from teacher;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 3 rows affected
Time: 0.019s
10:27:19 (root@(none)) [db1]> select * from teacher;
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
0 rows in set
Time: 0.008s

-- 清空表的另一种用法，这种方法可以缩减数据文件的大小
10:28:05 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 18  | F      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.008s
10:28:22 (root@(none)) [db1]> truncate table teacher;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 0 rows affected
Time: 0.092s
10:28:28 (root@(none)) [db1]> select * from teacher;
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
0 rows in set
Time: 0.009s

-- 防止勿清空表的安全策略选项
10:30:18 (root@(none)) [db1]> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+
1 row in set
Time: 0.011s
10:30:22 (root@(none)) [db1]> set sql_safe_updates=1;
Query OK, 0 rows affected
Time: 0.001s
10:30:24 (root@(none)) [db1]> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set
Time: 0.010s
10:30:51 (root@(none)) [db1]> insert into teacher select * from student;
Query OK, 4 rows affected
Time: 0.005s
10:30:53 (root@(none)) [db1]> select * from teacher;
+----+----------+-----+--------+
| id | name     | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20  | M      |
| 11 | xiaohong | 18  | F      |
| 12 | xiaolan  | 17  | M      |
| 13 | xiaolv   | 22  | F      |
+----+----------+-----+--------+
4 rows in set
Time: 0.009s
10:30:58 (root@(none)) [db1]> delete from teacher;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
(1175, 'You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. ')

实例:使用delete语句清空表，查看数据文件大小

-- 使用delete语句清空表后，数据文件是否减小
-- 在db1的数据库中，创建了一个testlog的表，并查看testlog数据文件的大小
11:26:35 (root@(none)) [db1]> create table testlog (id int auto_increment primary key,name char(100),age int default 20);
                           ->
                           -> delimiter $$
                           ->
                           -> create procedure sp_testlog()
                           -> begin
                           -> declare i int;
                           -> set i = 1;
                           -> while i <= 100000
                           -> do insert into testlog(name,age) values (concat('hooper',i),i);
                           -> set i = i +1;
                           -> end while;
                           -> end$$
                           ->
                           -> delimiter ;
Query OK, 0 rows affected
Time: 0.164s

Changed delimiter to $$
Time: 0.000s

Query OK, 0 rows affected
Time: 0.008s

Changed delimiter to ;
Time: 0.000s

-- 刚创建完成的表中没有数据，数据文件大小为96K
[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
96K	/data/mysql/data/db1/testlog.ibd
96K	total

-- 执行插入数据的脚本，并查看数据文件的大小
11:44:47 (root@(none)) [db1]> call sp_testlog;
Query OK, 1 row affected
Time: 775.133s

-- 实时查看数据文件的大小
[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
272K	/data/mysql/data/db1/testlog.ibd
272K	total
[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
288K	/data/mysql/data/db1/testlog.ibd
288K	total
[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
9.0M	/data/mysql/data/db1/testlog.ibd
9.0M	total
[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
11M	/data/mysql/data/db1/testlog.ibd
11M	total

.....................

[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
19M	/data/mysql/data/db1/testlog.ibd
19M	total
[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
21M	/data/mysql/data/db1/testlog.ibd
21M	total

-- 删除存储脚本
12:01:23 (root@(none)) [db1]> drop procedure if exists sp_testlog;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 0 rows affected
Time: 0.029s

-- 备份testlog到testlogbak
12:05:03 (root@(none)) [db1]> create table testlogbak like testlog;
Query OK, 0 rows affected
Time: 0.154s
12:05:16 (root@(none)) [db1]> desc testlogbak;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int(11)   | NO   | PRI | <null>  | auto_increment |
| name  | char(100) | YES  |     | <null>  |                |
| age   | int(11)   | YES  |     | 20      |                |
+-------+-----------+------+-----+---------+----------------+

3 rows in set
Time: 0.009s
12:05:22 (root@(none)) [db1]> select * from testlogbak;
+----+------+-----+
| id | name | age |
+----+------+-----+
0 rows in set
Time: 0.009s
12:05:59 (root@(none)) [db1]> insert into testlogbak select * from testlog;
Query OK, 100000 rows affected
Time: 2.649s
12:06:27 (root@(none)) [db1]> select * from testlogbak where id = 99999;
+-------+-------------+-------+
| id    | name        | age   |
+-------+-------------+-------+
| 99999 | hooper99999 | 99999 |
+-------+-------------+-------+
1 row in set
Time: 0.009s
12:06:35 (root@(none)) [db1]> select * from testlogbak where id = 100000;
+--------+--------------+--------+
| id     | name         | age    |
+--------+--------------+--------+
| 100000 | hooper100000 | 100000 |
+--------+--------------+--------+
1 row in set
Time: 0.009s

-- 使用delete语句，清空表，并查看数据文件的大小
12:08:53 (root@(none)) [db1]> delete from testlog;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 100000 rows affected
Time: 0.369s
12:09:21 (root@(none)) [db1]> select * from testlog;
+----+------+-----+
| id | name | age |
+----+------+-----+
0 rows in set
Time: 0.008s

[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
21M	/data/mysql/data/db1/testlog.ibd
21M	total

-- 使用OPTIMIZE语句优化表空间，数据文件的大小
12:11:01 (root@(none)) [db1]> optimize table testlog;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| db1.testlog | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| db1.testlog | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+

2 rows in set
Time: 0.146s

-- 再次查看数据文件大小
[root@magedu ~]# du -shc /data/mysql/data/db1/testlog.ibd
96K	/data/mysql/data/db1/testlog.ibd
96K	total

实例:使用truncate语句清空表，并查看数据文件的大小

-- 从刚才的实例中备份的testlogbak表，并查看testlogbak的数据文件大小
12:12:46 (root@(none)) [db1]> select * from testlogbak where id = 100000;
+--------+--------------+--------+
| id     | name         | age    |
+--------+--------------+--------+
| 100000 | hooper100000 | 100000 |
+--------+--------------+--------+
1 row in set
Time: 0.009s

[root@magedu ~]# du -shc /data/mysql/data/db1/testlogbak.ibd
21M	/data/mysql/data/db1/testlogbak.ibd
21M	total

-- 使用truncate语句，清空表，并查看数据文件的大小
12:16:47 (root@(none)) [db1]> truncate table testlogbak;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 0 rows affected
Time: 0.114s

[root@magedu ~]# du -shc /data/mysql/data/db1/testlogbak.ibd
96K	/data/mysql/data/db1/testlogbak.ibd
96K	total
hu_nil
关注
专栏目录
MySQL基础之DML语句
qigeminghao的博客
12-17 1789
DML 语句 DML（Data Manipulation Language）语句：数据操纵语句。 用途：用于添加、修改、删除和查询数据库记录，并检查数据完整性。 常用关键字：insert、update、delete、select等。 DML 操作的对象是库表的数据（记录）。 主要包括插入（insert）、更新（update）、删除（delete）和查询（select）。 DML 语句是开发人员使用最频繁的操作。 1.插入记录 插入一条记录 insert into tablename (f
MySQL DML语句整理汇总
09-09
MySQL DML（Data Manipulation Language）语句是用于管理和操作...了解并熟练掌握这些MySQL DML语句，对于数据库操作和管理将非常有益。在实际工作中，应根据具体需求灵活运用这些语句，确保数据的准确性和完整性。
MySQL：DML语句，查询，函数，集合运算
最新发布
燕双嘤
08-05 1115
2w说明白MySQL：DML语句，查询语句，函数，连接查询，子查询，集合运算（并集，差集，交集）。
5.DML语句
weixin_33750452的博客
01-23 217
DML语句用于操作数据表的数据，如：插入、修改、删除。insert into、update和delete from三个命令组成。 1、insert into INSERTINTO`grade`(`id`,`math`)VALUES('1','83'); 若省略表名后的括号及括号内的列名列表，默认将为所有列插入值。 若某列...
实训 sql 关系数据库实训练习含答案，DML语句及DDL语句.doc
05-04
"SQL 关系数据库实训练习含答案，DML 语句及 DDL 语句" 本实训目的是掌握 SQL 关系数据库的基本操作，包括 DML 语句和 DDL 语句。在本实训中，我们将学习如何使用 INSERT、UPDATE、DELETE 语句对数据进行添加、更新...
oracle操作数据DML语句.ppt
11-21
Oracle 操作数据 DML 语句 Oracle 操作数据 DML 语句是 Database Management System（数据库管理系统）中的一种基本语言，用于操作和管理数据库中的数据。DML 语句是 Data Manipulation Language（数据操作语言）...
在oracle11g中DML语句对索引的影响[定义].pdf
10-14
以下是关于DML语句对索引影响的详细解释，以INSERT语句为例进行阐述。 1. INSERT语句对索引的影响： 当我们执行INSERT语句向表中插入新的记录时，与记录关联的索引也需要更新。在上述示例中，创建了一个名为`text...
DML 语句
Hql_ztz的博客
04-08 6534
DML : 数据操作语言，对表格中的数据进行操作,insert、update、delete。 insert 插入: insert into 表名[字段1,字段2，字段3,…] values(值1,值2,值3,…); 语法1： 字段名称全写 insert into employee(字段1,字段2,…) values(值1,值2,…); 例如:insert into employee(id,name...
dml 语句
06-01
常见的 DML 语句包括： 1. SELECT：从一个或多个表中检索数据。 2. INSERT：向表中插入新的行。 3. UPDATE：更新表中现有的行。 4. DELETE：从表中删除行。 下面是这些语句的使用示例： 1. SELECT 语句： ``` ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值