数据操作语言DML
Data Manipulation Language概述
即"数据操纵语言"，简称"DML"。
用户通过它可以实现对数据库的基本操作(例如，对表中数据的插入(INSERT)、删除(DELETE),修改(UPDATE)和查询(SELECT)等操作)。但由于SELECT操作是非常频繁的，因此一般单独拿出来讲，称之为数据查询语言(Data Query Language，简称"DQL")。
综上所述，此文章介绍插入(INSERT)、删除(DELETE),修改(UPDATE)的操作，以及基础的查询(SELECT)语句使用
插入-INSERT
# 插入单行：
mysql> INSERT INTO student (id,name,age,gender,time_of_enrollment,address,mobile_number,remarks)VALUE (1,'唐三',21,'Male','2017-04-01 10:00:00','斗罗大陆','18866669999','男一号');
# 插入多行：
mysql> INSERT INTO student (id,name,age,gender,time_of_enrollment,address,mobile_number,remarks)VALUES (2,'小舞',18,'Female','2017-04-29 10:00:00','星斗大森林','17766668888','女一号'),(3,'戴沐白',23,'Male','2017-05-20 10:00:00','星罗帝国','18877779999','男二号'),(4,'宁荣荣',19,'Female','2017-06-03 10:00:00','七宝琉璃宗',19966665555,'女二号');
更新-UPDATE
UPDATE student SET name='千手修罗唐三' WHERE id=1;
删除-DELETE
DELETE FROM student WHERE id=8;
扩展知识
伪删除-将DELETE语句更换为UPDATE语句
# 原表结构如下所示:
mysql> DESC student;
+--------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('Male','Female') | YES | | Male | |
| time_of_enrollment | datetime | YES | | NULL | |
| address | varchar(255) | NO | | NULL | |
| mobile_number | bigint(20) | NO | UNI | NULL | |
| remarks | varchar(255) | YES | | NULL | |
+--------------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
# 原表数据如下所示:
mysql> SELECT * FROM student;
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+
| 1 | 千手修罗唐三 | 21 | Male | 2017-04-01 10:00:00 | 斗罗大陆 | 18866669999 | 男一号 |
| 2 | 小舞 | 18 | Female | 2017-04-29 10:00:00 | 星斗大森林 | 17766668888 | 女一号 |
| 3 | 戴沐白 | 23 | Male | 2017-05-20 10:00:00 | 星罗帝国 | 18877779999 | 男二号 |
| 4 | 宁荣荣 | 19 | Female | 2017-06-03 10:00:00 | 七宝琉璃宗 | 19966665555 | 女二号 |
| 5 | 朱竹青 | 20 | Female | 2017-06-10 10:00:00 | 星罗帝国 | 15566669999 | 女二号 |
| 6 | 马红俊 | 20 | Male | 2017-07-01 10:00:00 | 天斗帝国 | 18888888888 | 男二号 |
| 7 | 奥斯卡 | 22 | Male | 2017-07-08 10:00:00 | 天斗帝国 | 19999999999 | 男二号 |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+
7 rows in set (0.00 sec)
# 删除id为2的数据的常规操作：DELTE FROM student WHRER id=2;
# 伪删除id为2的数据操作流程:
# 1. 修改表原表结构:
mysql> DESC student;
+--------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('Male','Female') | YES | | Male | |
| time_of_enrollment | datetime | YES | | NULL | |
| address | varchar(255) | NO | | NULL | |
| mobile_number | bigint(20) | NO | UNI | NULL | |
| remarks | varchar(255) | YES | | NULL | |
+--------------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> ALTER TABLE student ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0 COMMENT '标记改行是否已经被删除，如果为1表示被标记已经删除，如果为0则表示未删除.';
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC student;
+--------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('Male','Female') | YES | | Male | |
| time_of_enrollment | datetime | YES | | NULL | |
| address | varchar(255) | NO | | NULL | |
| mobile_number | bigint(20) | NO | UNI | NULL | |
| remarks | varchar(255) | YES | | NULL | |
| deleted | tinyint(4) | NO | | 0 | |
+--------------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM student;
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks | deleted |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| 1 | 千手修罗唐三 | 21 | Male | 2017-04-01 10:00:00 | 斗罗大陆 | 18866669999 | 男一号 | 0 |
| 2 | 小舞 | 18 | Female | 2017-04-29 10:00:00 | 星斗大森林 | 17766668888 | 女一号 | 0 |
| 3 | 戴沐白 | 23 | Male | 2017-05-20 10:00:00 | 星罗帝国 | 18877779999 | 男二号 | 0 |
| 4 | 宁荣荣 | 19 | Female | 2017-06-03 10:00:00 | 七宝琉璃宗 | 19966665555 | 女二号 | 0 |
| 5 | 朱竹青 | 20 | Female | 2017-06-10 10:00:00 | 星罗帝国 | 15566669999 | 女二号 | 0 |
| 6 | 马红俊 | 20 | Male | 2017-07-01 10:00:00 | 天斗帝国 | 18888888888 | 男二号 | 0 |
| 7 | 奥斯卡 | 22 | Male | 2017-07-08 10:00:00 | 天斗帝国 | 19999999999 | 男二号 | 0 |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
7 rows in set (0.00 sec)
# 将删除语句更换为UPDATE
mysql> SELECT * FROM student;
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks | deleted |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| 1 | 千手修罗唐三 | 21 | Male | 2017-04-01 10:00:00 | 斗罗大陆 | 18866669999 | 男一号 | 0 |
| 2 | 小舞 | 18 | Female | 2017-04-29 10:00:00 | 星斗大森林 | 17766668888 | 女一号 | 0 |
| 3 | 戴沐白 | 23 | Male | 2017-05-20 10:00:00 | 星罗帝国 | 18877779999 | 男二号 | 0 |
| 4 | 宁荣荣 | 19 | Female | 2017-06-03 10:00:00 | 七宝琉璃宗 | 19966665555 | 女二号 | 0 |
| 5 | 朱竹青 | 20 | Female | 2017-06-10 10:00:00 | 星罗帝国 | 15566669999 | 女二号 | 0 |
| 6 | 马红俊 | 20 | Male | 2017-07-01 10:00:00 | 天斗帝国 | 18888888888 | 男二号 | 0 |
| 7 | 奥斯卡 | 22 | Male | 2017-07-08 10:00:00 | 天斗帝国 | 19999999999 | 男二号 | 0 |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
7 rows in set (0.01 sec)
mysql> UPDATE student SET deleted=1 WHERE id=2;
Query OK, 1 row affected (0.28 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM student;
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks | deleted |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| 1 | 千手修罗唐三 | 21 | Male | 2017-04-01 10:00:00 | 斗罗大陆 | 18866669999 | 男一号 | 0 |
| 2 | 小舞 | 18 | Female | 2017-04-29 10:00:00 | 星斗大森林 | 17766668888 | 女一号 | 1 |
| 3 | 戴沐白 | 23 | Male | 2017-05-20 10:00:00 | 星罗帝国 | 18877779999 | 男二号 | 0 |
| 4 | 宁荣荣 | 19 | Female | 2017-06-03 10:00:00 | 七宝琉璃宗 | 19966665555 | 女二号 | 0 |
| 5 | 朱竹青 | 20 | Female | 2017-06-10 10:00:00 | 星罗帝国 | 15566669999 | 女二号 | 0 |
| 6 | 马红俊 | 20 | Male | 2017-07-01 10:00:00 | 天斗帝国 | 18888888888 | 男二号 | 0 |
| 7 | 奥斯卡 | 22 | Male | 2017-07-08 10:00:00 | 天斗帝国 | 19999999999 | 男二号 | 0 |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
7 rows in set (0.00 sec)
3. 将SELETE语句加上WHERE子句进行过滤
mysql> SELECT * FROM student;
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks | deleted |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| 1 | 千手修罗唐三 | 21 | Male | 2017-04-01 10:00:00 | 斗罗大陆 | 18866669999 | 男一号 | 0 |
| 2 | 小舞 | 18 | Female | 2017-04-29 10:00:00 | 星斗大森林 | 17766668888 | 女一号 | 1 |
| 3 | 戴沐白 | 23 | Male | 2017-05-20 10:00:00 | 星罗帝国 | 18877779999 | 男二号 | 0 |
| 4 | 宁荣荣 | 19 | Female | 2017-06-03 10:00:00 | 七宝琉璃宗 | 19966665555 | 女二号 | 0 |
| 5 | 朱竹青 | 20 | Female | 2017-06-10 10:00:00 | 星罗帝国 | 15566669999 | 女二号 | 0 |
| 6 | 马红俊 | 20 | Male | 2017-07-01 10:00:00 | 天斗帝国 | 18888888888 | 男二号 | 0 |
| 7 | 奥斯卡 | 22 | Male | 2017-07-08 10:00:00 | 天斗帝国 | 19999999999 | 男二号 | 0 |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM student WHERE deleted=0;
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks | deleted |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
| 1 | 千手修罗唐三 | 21 | Male | 2017-04-01 10:00:00 | 斗罗大陆 | 18866669999 | 男一号 | 0 |
| 3 | 戴沐白 | 23 | Male | 2017-05-20 10:00:00 | 星罗帝国 | 18877779999 | 男二号 | 0 |
| 4 | 宁荣荣 | 19 | Female | 2017-06-03 10:00:00 | 七宝琉璃宗 | 19966665555 | 女二号 | 0 |
| 5 | 朱竹青 | 20 | Female | 2017-06-10 10:00:00 | 星罗帝国 | 15566669999 | 女二号 | 0 |
| 6 | 马红俊 | 20 | Male | 2017-07-01 10:00:00 | 天斗帝国 | 18888888888 | 男二号 | 0 |
| 7 | 奥斯卡 | 22 | Male | 2017-07-08 10:00:00 | 天斗帝国 | 19999999999 | 男二号 | 0 |
+----+--------------------+------+--------+---------------------+-----------------+---------------+-----------+---------+
6 rows in set (0.00 sec)
DELETE FROM student, DROP TABLE student, TRUNCATE TABLE student有何区别？
首先DELETE FROM student, DROP TABLE student, TRUNCATE TABLE student这三条SQL语句均能删除全表数据，但他们的确是存在一定的差异，这也是不争的事实。
DELETE FROM student:
- 是逻辑上的删除，并没有真正从磁盘上删除，只是在存储层面打标记，磁盘空间不立即释放，高水位线(HWM)不会降低;
- 当数据量较大时，操作会很慢，因为他要将全表进行扫描，而后对每行打标记改行被删除，理论上这种删除通过一定的手段是可以恢复数据的;
DROP TABLE student:
- 将表结构(元数据)和数据行物理层次删除;
- 该删除操作是不可恢复的，因为已经在物理层上删除数据，想要恢复也只能通过备份来进行恢复了;
TRUNCATE TABLE student:
- 清空表中所有的数据页，物理层次删除全表数据，磁盘空间立即释放，高水位线(HWM)会初始化为0;
- 其实TRUNCATE我们可以理解为将原表的表结构复制并创建了一张新表，新数据直接写入到新建的表中，他不会去逐行删除之前的数据(之前的原始数据也不会有相应的引用，从而被MySQL进行垃圾回收);
浙公网安备 33010602011771号