09-mysql基础-mysql中的DML-数据操作语言

vlambda
2021-02-22

09-mysql基础-mysql中的DML-数据操作语言

点击蓝色“潭时录”关注我丫

每天和小潭一起快乐的学习~

你好，我是在学mysql的小潭。在上一期的推文中我们学习了mysql的联合查询知识，同时也在上期结束了DQL部分的学习，本期我们将开始学习mysql中的DML。

本期学习用到的测试数据表：

CREATE DATABASE `test`;use `test`;DROP TABLE IF EXISTS `temp`;CREATE TABLE `temp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `username` VARCHAR(10) NOT NULL, `password` VARCHAR(10) NOT NULL, `verify` BLOB,  `date` DATETIME DEFAULT '2021-02-18 00:00:00', PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `temp2`;CREATE TABLE `temp2` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `phone` VARCHAR(11) NOT NULL, `email` VARCHAR(16) NOT NULL, `fkey` int(11) NOT NULL, `date` DATETIME DEFAULT '2021-02-18 00:00:00', PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

如04期文章中的介绍，DML，即数据操作语言，分为插入(insert)，修改(update)和删除(delete)三种类型。

插入语句

语法：方式一： insert into 表名(列名,...) values(值1,...);  方式一特点： 1.插入的值的类型要与列的类型保持一致或兼容 2.不可以为null的列必须插入值，可以为null的列插入值可以使用以下方式： 1) 在insert语句的values中使用NULL代替 2) 在insert语句中列名和值都省略 3.列的顺序可以调换 4.列数和值的个数必须一致 5.可以省略列名，此时是默认所有列，且列的顺序和表中列的顺序一致 方式二： insert into 表名 set 列名=值,列名=值,.... 两种方式的比较：1.方式一支持插入多行，方式二不支持2.方式一支持子查询，方式二不支持

示例:

#方式一：#案例1：向temp表插入一条数据INSERT INTO temp(id,username,PASSWORD,verify,DATE) VALUES(1,'小潭','studymysql',NULL,'2021-02-19');SELECT * FROM temp;
#案例2：特点2举例INSERT INTO temp(id,username,PASSWORD,verify,DATE)VALUES(3,'小潭C','mysql',NULL,NULL);INSERT INTO temp(id,username,PASSWORD) VALUES(4,'小潭B','mysql');SELECT * FROM temp;
#案例3：特点3举例INSERT INTO temp(id,PASSWORD,username) VALUES(5,'pw','小潭D');SELECT * FROM temp;
#案例4：特点5举例INSERT INTO temp VALUES(2,'小潭A','python',NULL,'2021-02-19');SELECT * FROM temp;
################################################################################################################
#方式二：INSERT INTO temp SET id=6,username='小潭E',PASSWORD='pass';SELECT * FROM temp;
INSERT INTO temp2 SET id=1,email='[email protected]',phone='11111111111',fkey=1;INSERT INTO temp2 SET id=2,email='[email protected]',phone='21111111111',fkey=2;SELECT * FROM temp2;

方式一和方式二的区别：

#方式一和二的比较：#区别1：方式一支持插入多条INSERT INTO tempVALUES(7,'小潭F','pass1',NULL,NULL),(8,'小潭G','pass2',NULL,NULL),(9,'小潭H','pass3',NULL,NULL);SELECT * FROM temp;
#区别2：方式一支持子查询INSERT INTO temp(id,username,PASSWORD) SELECT 10,'小潭I','pass4';SELECT * FROM temp;

修改语句

语法：1.修改单表的记录 update 表名 set 列=新值,列=新值,... where 筛选条件; 执行顺序：update 表名 -> where 筛选条件 -> set 列=新值 2.修改多表的记录(级联更新)sql92： update 表1 别名,表2 别名 set 列=值,.... where 连接条件 and 筛选条件;sql99: update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,... where 筛选条件

示例:

#修改单表的记录#案例1：修改temp表中名字包含D的密码为9999UPDATE temp SET PASSWORD='9999'WHERE username LIKE '%D%';SELECT * FROM temp;
#案例2：修改temp表中id为7的名称为潭时录，密码为6666UPDATE temp SET username='潭时录',PASSWORD=6666WHERE id=7;SELECT * FROM temp;
#修改多表的记录#案例1：修改名称为小潭A的电话号码为3111111111UPDATE temp2 t2INNER JOIN temp t1 ON t2.`fkey` = t1.`id`SET t2.`phone`='3111111111'WHERE t1.`username`='小潭A';SELECT * FROM temp2;
#案例2：修改与temp2无关联的temp表中的密码都为123456UPDATE temp2 t2RIGHT JOIN temp t1 ON t2.`id`=t1.`id`SET t1.`password`=123456WHERE t2.`id` IS NULL;
SELECT * FROM temp;

删除语句

方式一：delete语法：1.单表删除 delete from 表名 where 筛选条件2.多表删除，如果两个关联表中的记录都要删除，delete后写两个表的别名如果只删除一个表，则delete后仅跟要删除记录的表的别名即可sql92: delete 表1的别名,表2的别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;sql99: delete 表1的别名,表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件;
方式二：truncate语法： truncate table 表名; delete和truncate的区别：1.delete可以加where 条件，truncate不能加2.truncate删除效率高一点，因为不需要做筛选3.假如要删除的表中有自增长的列，AUTO_INCREMENT修饰的列 用delete删除后，再插入数据，自增长列的值从断点(即删除时的值)开始 例： temp表最后一条记录id=5; delete from temp; #表数据被清空 insert temp... select * from temp; #第一条数据id=6; 用truncate删除后，再插入数据，自增长的值从1开始 例： temp表最后一条记录id=5; truncate table temp; #表数据被清空 insert temp... select * from temp; #第一条数据id=1;4.truncate删除没有返回值，delete删除有返回值 delete操作后，返回信息有n条记录受影响，而truncate操作后，返回信息有0条记录受影响5.truncate删除不能回滚，而delete删除可以回滚

示例：

#方式一：delete#单表的删除#案例1：删除temp表中姓名以H结尾的信息DELETE FROM temp WHERE username LIKE '%H';SELECT * FROM temp;
#多表的删除#案例2：删除temp2表中与小潭A有关联的信息DELETE t2FROM temp2 t2INNER JOIN temp t1 ON t1.`id`=t2.`fkey`WHERE t1.`username`='小潭A';SELECT * FROM temp;SELECT * FROM temp2;
#方式二：truncate，语句后不能加where，表结构保留，为清空表数据作用#案例1：将temp2表清空TRUNCATE TABLE temp2;SELECT * FROM temp2;

小练习：

/*练习表：运行以下脚本创建my_employees表use myemployees;create table my_employees( id int(10), first_name varchar(10), last_name varchar(10), userid varchar(10), salary double(10,2));create table users( id int, userid varchar(10), department_id int);#显示my_employees的结构desc my_employees;*/
#插入以下测试数据到my_employees/*ID first_name last_name userid salary1 patel Ralph Rpatel 8952 Dancs Betty Bdancs 8603 Biri Ben Bbiri 11004 Newman Chad Cnewman7505 Rope Audrey Arope 1550*/#方式一：INSERT INTO my_employees VALUES(1,'patel','Ralph','Rpatel',895),(2,'Dancs','Betty','Bdancs',860),(3,'Biri','Ben','Bbiri',1100),(4,'Newman','Chad','Cnewman',750),(5,'Rope','Audrey','Arope',1550);#方式二(子查询方式)：DELETE FROM my_employees;INSERT INTO my_employeesSELECT 1,'patel','Ralph','Rpatel',895 UNIONSELECT 2,'Dancs','Betty','Bdancs',860 UNIONSELECT 3,'Biri','Ben','Bbiri',1100 UNIONSELECT 4,'Newman','Chad','Cnewman',750 UNIONSELECT 5,'Rope','Audrey','Arope',1550;SELECT * FROM my_employees;
#插入以下测试数据到users表中/*1 Rpatel 102 Bdancs 103 Bbiri 204 Cnewman305 Arope 40*/INSERT INTO usersVALUES(1,'Rpatel',10),(2,'Bdancs',10),(3,'Bbiri',20),(4,'Cnewman',30),(5,'Arope',40);SELECT * FROM users;
#将3号员工的last_name修改为'drelxer'UPDATE my_employees SET last_name='drelxer'WHERE id=3;SELECT * FROM my_employees;
#将所有工资少于900的员工的工资修改为1000UPDATE my_employees SET salary=1000WHERE salary<900;SELECT * FROM my_employees;
#将userid为Bbiri的user表和my_employees表的记录全部删除DELETE u,eFROM users uJOIN my_employees e ON u.`userid`=e.`userid`WHERE u.`userid`='Bbiri';
#删除所有数据DELETE FROM my_employees;DELETE FROM users;SELECT * FROM my_employees;SELECT * FROM users;
#清空表my_employeesTRUNCATE TABLE my_employees;TRUNCATE TABLE users;