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

潭时录 2021-05-19

1063

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

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

你好，我是在学mysql的小潭。在上一期08-mysql基础-mysql中的DQL-联合查询的推文中我们学习了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='xxx@xx.com',phone='11111111111',fkey=1;
INSERT INTO temp2 
SET id=2,email='xx2@xx.com',phone='21111111111',fkey=2;
SELECT * FROM temp2;

方式一和方式二的区别：

#方式一和二的比较：
#区别1：方式一支持插入多条
INSERT INTO temp
VALUES(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的密码为9999
UPDATE temp SET PASSWORD='9999'
WHERE username LIKE '%D%';
SELECT * FROM temp;


#案例2：修改temp表中id为7的名称为潭时录，密码为6666
UPDATE temp SET username='潭时录',PASSWORD=6666
WHERE id=7;
SELECT * FROM temp;


#修改多表的记录
#案例1：修改名称为小潭A的电话号码为3111111111
UPDATE temp2 t2
INNER JOIN temp t1 ON t2.`fkey` = t1.`id`
SET t2.`phone`='3111111111'
WHERE t1.`username`='小潭A';
SELECT * FROM temp2;


#案例2：修改与temp2无关联的temp表中的密码都为123456
UPDATE temp2 t2
RIGHT JOIN temp t1 ON t2.`id`=t1.`id`
SET t1.`password`=123456
WHERE 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 t2
FROM temp2 t2
INNER 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 salary
1  patel      Ralph     Rpatel 895
2  Dancs      Betty     Bdancs 860
3  Biri       Ben       Bbiri  1100
4  Newman     Chad      Cnewman750
5  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_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Rope','Audrey','Arope',1550;
SELECT * FROM my_employees;


#插入以下测试数据到users表中
/*
1 Rpatel 10
2 Bdancs 10
3 Bbiri  20
4 Cnewman30
5 Arope  40
*/
INSERT INTO users
VALUES(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的员工的工资修改为1000
UPDATE my_employees SET salary=1000
WHERE salary<900;
SELECT * FROM my_employees;


#将userid为Bbiri的user表和my_employees表的记录全部删除
DELETE u,e
FROM users u
JOIN 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_employees
TRUNCATE TABLE my_employees;
TRUNCATE TABLE users;

至此，mysql中的DML部分将告一段落，下一期我们将开始学习mysql中的DDL，即数据定义语言部分的知识，敬请期待。

下期预告：mysql中的数据定义

如果你喜欢本文,

请长按二维码，关注 潭时录.

转发至朋友圈，是对我最大的支持。

点个在看

喜欢是一种感觉

在看是一种支持

↘↘↘

mysql

文章转载自潭时录，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论