MySQL 第四章：插入、更新和删除数据

本文链接：https://blog.csdn.net/Alex_sdhjkb/article/details/136158260

内容来自：
1、b站mosh老师的SQL课程（第三章）【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！_哔哩哔哩_bilibili www.bilibili.com/video/BV1UE41147KC/?p=17&spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=171e84ea90c06aa5a434d7fa2502e75c
2、菊花酱数据分析

1 MySQL基础知识

1.1常见数据类型

数据类型是指某个变量或值的类型，通常情况下，绝大多数的变量都属于字符型、数值型和日期时间型三种。详细来说，数值型有整数型int、浮点型float等，日期时间型有日期date、时间戳类型timestamp等

1.1.1 数值型

数值型就是指变量或值以数字的形式呈现，且用四则运算进行计算之后的结果也是有意义的，比如用户的年龄、收入、商品的价格等，都属于数值型数据

关于表中的几种数值类型，有几点需要说明：

如果在实际应用中，限定变量为非负的数值型时，必须在数据类型前面加上关键词UNSIGNED
如果使用DECIMAL表示浮点型，需要指定参数M和D的值，其中M表示浮点数值中包含的所有数字个数，D表示浮点数值中小数位的数字个数，例如，DECIMAL(5,2)表示数值最多包含5个数字，其中小数位占两位，也就是说DECIMAL(5,2)的取值范围为[-999.99,999.99]
tinyint(4): 有符号的范围为 -128 到 127，无符号的范围为 0 到 255( $2^{8}$ )。占用 1 字节的存储空间。
smallint(6): 有符号的范围为 -32,768 到 32,767，无符号的范围为 0 到 65,535( $2^{16}$ )。占用 2 字节的存储空间。
mediumint(9): 有符号的范围为 -8,388,608 到 8,388,607，无符号的范围为 0 到 16,777,215( $2^{24}$ )。占用 3 字节的存储空间。
int(11): 有符号的范围为 -2,147,483,648 到 2,147,483,647，无符号的范围为 0 到 4,294,967,295( $2^{32}$ )。占用 4 字节的存储空间。
bigint(20): 有符号的范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807( $2^{64}$ )，无符号的范围为 0 到 18,446,744,073,709,551,615。占用 8 字节的存储空间。
float(10,2):
- 10: 总位数，表示浮点数的总位数，包括整数位和小数位。在这个例子中，总位数为10
- 2: 小数位数，表示浮点数中小数部分的位数。在这个例子中，小数位数为2。

1.1.2 字符串类型

字符串数据主要是指离散的类别型数据，并且这些数据以字符串的形式呈现，比如用户的姓名、性别、
产品的名称等。

对于常用的字符型数据类型，有几点需要注意：

如果变量的类型为CHAR(n)或VARCHAR(n) ，n表示该变量的每一个观测值最多可以存储n个长度的字符。
- 如果实际长度超过指定长度，它们均会将超过的部分截断
- 如果实际长度小于指定的长度，两者的处理就会有一些不同了
- 对于CHAR(n)来说，会以空格填满
- 对于VARCHAR(n)来说，该是多少的长度就是多少的长度，并不会作任何处理，这就是可变长的缘由。
如果变量的数据类型为ENUM(enumerate的简写)，表示该变量所对应的每一个观测值最多可以在65535（ $2^{16}$ ）个不同的值中选择一个（类似于单选问题）或使用NULL值，并且这些数值必须提前通过ENUM类型指定。例如比如用户性别，我们可以从ENUM(“男”，“女”)进行枚举。ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。除此之外，MySQL还保留了一个元素，用来存储错误信息这个错误值用索引 0 或者一个空字符串表示。因为MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。
如果变量的数据类型为SET，表示该变量所对应的每一个观测值最多可以在64个不同的值中选择多个（类似于多选问题），并且这些值需通过SET类型指定。比如用户的兴趣爱好，我们可以用SET(“足球”，“篮球”，“乒乓球”，“羽毛球”，“游泳”，“骑行”)来进行枚举，可以选择一个或者多个。需要注意的是，SET具有去重的功能，所以SET类型中不可能包含相同的元素。此外，如果希望从SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行

1.1.3 日期时间类型

日期时间型的数据是非常常见的，比如用户的出生日期、注册日期、登陆时间、产品的订单时间等等，都属于日期时间型数据。

注意：

这种时间戳类型可以将客户端当前时区转化为UTC（世界标准时间）；
对于跨时区业务来说，TIMESTAMP类型通常为首选；
此外，TIMESTAMP时间戳类型具有自动初始化和更新的功能，也就是说这种类型的数据在没有赋值时，会以系统时间填补；
当数据表中其他字段的数据发生变化时（也就是被修改时），TIMESTAMP这个类型所对应的字段的观测值也会被更新成系统时间。

1.2 数据类型的应用场景

根据经验，通常在数据库操作中有3种情况会涉及数据类型：

情况	说明
新建数据表	如果需要手工方式新建一张表，表中的字段名称和字段类型是必须要指定的
查询	在查询过程中，如果原始数据类型无法参与计算，需要进行类型转换
字段类型更改	如果原始表中某个字段的数据类型不符合实际情况，可以直接对其字段类型进行修改

1.3 创建数据库和数据表

1.3.1 创建数据库代码

创建数据库：create database 数据库名称
查看创建好的数据库:show create database 数据库名称
查看所有数据库列表:show databases
删除数据库:删除数据库
使用数据库:use 数据库名称

# 创建数据库
# 语法：create database 数据库名称;
create database test; -- 创建名为test的数据库

# 查看创建好的数据库
# 语法：show create database 数据库名称;
show create database test; -- 查看创建好的test数据库

# 查看所有数据库列表
show databases;

# 使用数据库
use test; -- 使用创建好的test数据库

# 删除数据库
drop database test; -- 删除创建好的test数据库

1.3.2 创建数据表代码

数据库是由多个数据表构成的，每张数据表中存储着不同的字段，每个字段由不同的字段名和记录构成，并且每个字段有自己的数据结构和约束条件。比如

图解外键：

现在在之间创建的test数据库中，创建一个员工信息表emp，表中信息如下

按照上图创建数据库框架

-- 使用test数据库（如果数据库已经删掉，需要重新创建）
use test;

-- 创建员工信息表
create table emp(
depid char(3),
depname varchar(20),
peoplecount int
);

-- 查看表是否创建成功
show tables;

-- 删除数据表
drop table emp;

1.4 约束条件

约束是在表上强制执行的数据检验规则，用来保证创建的表的数据完整和正确。
MySQL数据库常用约束条件：

1.4.1 主键约束

主键约束：保证表中每行记录都不重复

主键（primary key）
一列（或一组列），其值能够唯一区分表中每个行

注意：

没有主键，更新或者删除表中特定的行会比较困难，因为没有安全的方法保证只涉及相关的行。使用主键，能够惟一地标识表中的一条记录，并且可以结合外键来定义不同数据表之间的关系，还可以加快数据库查询的速度。
主键分为两种类型：单字段主键和多字段主键
对于单字段主键，主键通常定义在表的一列上，比如：对于下面的员工表来说，部门ID就可以作为主键
对于多字段主键，必须保证构成主键的所有列值的组合是唯一的（单个列值可以不唯一）。

原则上，表中的任何列都可以作为主键，只需要满足以下条件：
任意两行都不具有相同的主键值（唯一）
每个行都必须具有一个主键值，且主键值不允许NULL值（非空）
主键列中的值不允许修改或更新；
主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）；
在使用多列作为主键时，上述条件必须应用到作为主键的所有列，所有列值的组合必须是唯一的（但其中单个列的值可以不唯一）

主键（primary key）
一列（或一组列），其值能够唯一区分表中每个行

现在在之间创建的test数据库中，创建一个员工信息表emp，表中信息如下

实例：

按照上图创建数据库框架：

主键为depid
主键为depname和depid

-- 单字段主键
create table emp(
depid char(3) primary key,
depname varchar(20),
peoplecount int
);

# 多字段联合主键
create table emp(
depid char(3),
depname varchar(20),
peoplecount int,
primary key(depname,depid)
);

1.4.2 非空约束

非空约束，指的是字段的值不能为空：

语法：字段名字段类型 not null

实例：

按照上图创建数据库框架：

主键为depid
depname不能为空

create table emp(
depid char(3) primary key,
depname varchar(20) not null,
peoplecount int
);

1.4.3 唯一性约束

唯一性约束，要求该列的值必须是唯一的：

允许为空，但只能出现一个空值;
一个表中可以有多个字段声明为唯一的;
唯一约束确保数据表的一列或几列不出现重复值

语法：字段名数据类型 unique

实例：

按照上图创建数据库框架：

主键为depid
depname不能为空
peoplecount符合唯一性约束

create table emp(
depid char(3) primary key,
depname varchar(20) not null,
peoplecount int unique
);

主键 VS 唯一键

1.4.4 默认约束

默认约束，指定某个字段的默认值；如果新插入一条记录时没有为默认约束字段赋值，那么系统就会自动为这个字段赋值为默认约束设定
的值

语法：字段名数据类型 default 默认值

实例：

按照上图创建数据库框架：

主键为depid
depname符合默认约束（‘-’）
peoplecount符合唯一性约束

create table emp(
depid char(3) primary key,
depname varchar(20) not null default '-',
peoplecount int unique
);

1.4.5 自增字段

一个表只能有一个自增字段，自增字段必须为主键的一部分。默认情况下从1开始自增。

实例：

按照上图创建数据库框架：

主键为id字段，并符合自增约束，类型为int
name不能为空，类型为varchar
math默认值为0，类型为int
minamx类型为float，符合唯一约束

# 创建含各种约束条件的数据表
CREATE TABLE example(id INT PRIMARY KEY AUTO_INCREMENT, #创建整数型自增主键
name VARCHAR(4) NOT NULL, #创建非空字符串字段
math INT DEFAULT 0, #创建默认值为0的整数型字段
minmax FLOAT UNIQUE #创建唯一约束小数型字段
);

1.5 手工建表

create table fruits(
f_id char(10) not null,
s_id int not null default 0,
f_name char(255) ,
f_price decimal(8,2) not null,
primary key(f_id)
);

创建好之后，其实只是创建了一个表的框架，目前表里面是空的、还没有数据

尝试用insert into语句为表插入数据

语法：insert into table_name(variable_list) values (value_list),(value_list),……,(value_list);

例1

为fruit表格插入这些记录：

# 插入数据
insert into fruits(f_id,s_id,f_name,f_price) values
('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',25.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('b5',107,'xxxx',3.6);

注意：

必须确保待插入的变量个数（variable_list）与实际插入的每个观测值变量个数（value_list）完全一样
必须确保待插入的变量顺序与实际插入的观测值顺序完全一样
确保待插入的变量类型与实际插入的观测值类型完全一样，这一点非必须满足，但必须符合可转换性，也就是说如果变量类型是整型，插入的值可以是数值也可以是字符型的数值

练习

CREATE DATABASE train;
USE train;

CREATE TABLE film_top10(
	m_rank TINYINT AUTO_INCREMENT PRIMARY KEY,
	m_name VARCHAR(50),
	uptime YEAR,
	country VARCHAR(20),
	director VARCHAR(30),
	m_type SET('剧情','犯罪','爱情','同性','动作','喜剧','战争','动画','奇幻','灾难','历
史','悬疑','冒险','科幻'),
	score DECIMAL(2,1),
	Num_Commentaries INT,
	m_description VARCHAR(100)
	);
	
INSERT INTO
film_top10(m_name,uptime,country,director,m_type,score,Num_Commentaries,m_descri
ption) VALUES
('肖申克的救赎',1994,'美国','弗兰克·德拉邦特','犯罪,剧情',9.6,1034791,'希望让人自由。'),
('霸王别姬',1993,'中国大陆 香港','陈凯歌','剧情,爱情,同性',9.5,753297,'风华绝代。'),
('这个杀手不太冷',1994,'法国','吕克·贝松','剧情,动作,犯罪',9.4,968089,'怪蜀黍和小萝莉不得
不说的故事。'),
('阿甘正传',1994,'美国','Robert Zemeckis','剧情,爱情',9.4,824062,'一部美国近现代史。'),
('美丽人生',1997,'意大利','罗伯托·贝尼尼','剧情,喜剧,爱情,战争',9.5,481250,'最美的谎
言。'),
('千与千寻',2001,'日本','宫崎骏','剧情,动画,奇幻',9.3,771592,'最好的宫崎骏,最好的久石
让。'),
('泰坦尼克号',1997,'美国','詹姆斯·卡梅隆','剧情,爱情,灾难',9.3,763515,'失去的才是永恒
的。'),
('辛德勒的名单',1993,'美国','史蒂文·斯皮尔伯格','剧情,历史,战争',9.4,437907,'拯救一个人,就
是拯救整个世界。'),
('盗梦空间',2010,'美国 英国','克里斯托弗·诺兰','剧情,科幻,悬疑,冒险',9.3,861722,'诺兰给了
我们一场无法盗取的梦。'),
('机器人总动员',2008,'美国','安德鲁·斯坦顿','爱情,科幻,动画,冒险',9.3,565035,'小瓦力,大人
生。');

SELECT * FROM film_top10;

INSERT INTO film_top10(m_name,uptime,score,Num_Commentaries) VALUES
('三傻大闹宝莱坞','2009','9.2',775279),
('海上钢琴师','1998','9.2',663039);

SELECT * FROM film_top10;

1.6 外部数据的批量导入

1.6.1 通过纯SQL语句导入

在使用命令导入数据之前，需要确保数据库中已经有一张数据表来承接这些数据，所以首先我们在test数据库中建立一张空的数据表：

# 使用test数据库
use test；
# 新建存储二手房数据的表格
CREATE TABLE sec_buildings (
	fname VARCHAR(20),
	ftype VARCHAR(10),
	size DECIMAL(10,2),
	region VARCHAR(10),
	floor VARCHAR(20),
	direction VARCHAR(20),
	tot_amt INT,
	built_date VARCHAR(20),
	price_unit INT
);

由于8.0版本安全性提高，如果想要将本地数据导入到MySQL数据库，需要将文件放到安全路径下.

在workbench里面执行代码找到安全路径：

#查看安全路径
show variables like '%secure%';#查看安全路径

然后将需要导入的本地文件sec_buildings.txt，放到安全路径下，执行如下代码：

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/sec_buildings.txt" -- 是/
INTO TABLE sec_buildings
FIELDS TERMINATED BY ',' -- 指定字段之间的分隔符为英文状态下的","
LINES TERMINATED BY '\n' -- 指定记录航之间的分隔符为换行符"\n"
IGNORE 1 lines; -- 由于原始数据中第一行为表头,所以数据读取的时候需要忽略第一行
select * from sec_buildings; #查看导入的数据集

对于20275条数据来说，采用命令行的方式数据的导入效率还是非常高的显示只需要0.468秒就可以全部导入

1.6.2 基于Workbench图形化导入

如果需要导入的数据为Excel格式的，需要将该数据另存为csv格式，然后再利用Workbench的数据导入功能。这里以空气质量月度指标数据集为例（monthly_Indicator.csv,需要用记事本打开转化为ANSI编码，再另存为–替代）

操作：

找到在SCHEMAS中找到test数据库——鼠标右击，在出现的新文本框中选择Table Data Import
Wizard
选择导入的csv文件（这里不必放在安全路径下），然后点击下一步
接下来，如果没有问题的话，点击Next直到完成数据的导入。

最后，完成6432条数据的导入，共计耗费93秒，可见，图形化方式导入数据虽然操作比较简单，但是耗费时间

# 查看导入的数据表
select * from monthly_indicator;
# 检查导入数据总行数
select count(*) from monthly_indicator;
# 检查表结构(DESC:description)
Desc monthly_Indicator;

关于图形化导入数据，需要注意的一点是:

这种方式导入数据比较容易出现数据的丢失，比如，尝试导入泰坦尼克号数据集（共计891条数据），按照常规方式导入的话，最后会发现只有714条数据成功导入了数据库中

从上一步的操作日志logs中可以看到，原因是Age这个字段为空的数据都没有导入成功

这个时候的操作方法就是重新导入，在configure import settings这一步，把Age这个字段的数据类型由默认的Int改为text，这样就能把891条数据全部导入了。

【注意】MySQL中的大小写问题

在mysql数据库中，sql关键字是不区分大小写的
在windows系统下，MySQL代码中的数据表名、列名、索引名也是不区分大小写的
建议统一写成小写或者统一写成大写（因为数据库迁移的时候，可能会出现大小写问题）

2 插入、更新和删除数据

2.1 列属性

如何查看列属性？

点击右边模块（schemas）–进入某数据库–点击表右边的扳手按钮（中间的一个按钮）：打开设计模式，介绍了一些表中字段/列的属性。

解释：

第一列（Column Name):列名；

第二列（Datatype):表示每列的数据类型；

解释Datatype各取值的含义：

INT:整数类型（如：1、2、3），不接受小数；
VARCHAR（50）：可变字符的缩写，每个格子最多有50个字符；
CHAR（50）：字符的缩写,每个格子最多有50个字符，不足的会用空格符来填充。（相比VARCHAR会浪费空间，因此最好用VARCHAR来存储字符串或者文本数据）

PK：primary key（主键）的缩写（如果该字段是主键，列名的左边会有黄色钥匙🔑的图案）；

PK能唯一标识每一条记录

NN： Not Null（非空值），决定了该字段取值是否可以用空值；

在customers表中，顾客id为主键，姓氏、名字、（所居住地）址、（所居住）城市、（所居住）州都必须要有数据们不能为null，而生日、电话号码、积分可以为空值。

AI：auto increment（自动递增），通常用于主键列；

解释自动递增：

此时customers表中有10条记录，如果我们需要在表中再增加一条记录，那么主键也就是顾客id就是加1，为编号11。

Default/Expression:每列的默认取值

在customers表中，生日、电话号码的默认取值为NULL，如果某条记录中对于生日和电话号码字段没有提供任何值， DBMS会自动给这两个字段填充上空值（NULL）

为什么points的类型为int，但它的默认取值为’0’?

在数据库中，INT 是一个数值数据类型，它用来存储整数值。字段的默认值设置为 '0'（带有单引号）通常是为了确保当没有为该字段提供值时，它将自动填充为零。在 SQL 中，即使默认值写成了 '0'（通常意味着字符串），在存储到 INT 类型的字段时，字符串形式的数字会被自动转换为相应的整数类型。

2.2 插入单行

-- 语法格式：
INSERT INTO 目标表 (目标列，可选，逗号隔开)
VALUES (目标值，逗号隔开)

案列

题目：

用两种方法实现在顾客表里插入一个新顾客的信息:

first_name:‘Michael’;
last_name:‘Jackson’;
birth_date:‘1958-08-29’;
address:‘5225 Figueroa Mountain Rd’;
city:‘Los Olivos’;
state:‘CA’

法1. 若不指明列名，则插入的值必须按所有字段的顺序完整插入

USE sql_store;

INSERT INTO customers -- 目标表
VALUES (
    DEFAULT,-- 主键有自动递增属性，可以不用特地赋值，让MySQL为我们自动赋值
    'Michael',
    'Jackson',
    '1958-08-29',  -- DEFAULT/NULL/'1958-08-29'
    DEFAULT,
    '5225 Figueroa Mountain Rd', 
    'Los Olivos',
    'CA',
    DEFAULT
    );

注意：

对于PK列的赋值，建议还是只用默认值（让MySQL为PK字段在一条记录的基础自动递增赋值），防止你自己赋的值和其他记录的PK字段值重复，因为PK的取值不能有重复值，每个值都得是唯一的；
字符串和日期数据要加引号

观察结果可以发现，points的默认值（‘0’),对于MySQL来说就是数字0.

法2. 指明列名，可跳过取默认值的列且可更改顺序(一般用这种，更清晰)

INSERT INTO customers (
    address,
    city,
    state,
    last_name,
    first_name,
    birth_date
    )
VALUES (
    '5225 Figueroa Mountain Rd',
    'Los Olivos',
    'CA',
    'Jackson',
    'Michael',    
    '1958-08-29'  
    )
```

2.3 插入多行

VALUES …… 里一行内数据用括号内逗号隔开，而多行数据用括号间逗号隔开

案列

插入3条运货商(shipper)信息(其中shipper_id为PK),名字为shipper1，shipper2， shipper3。

INSERT INTO shippers (name)
VALUES ('shipper1'),
       ('shipper2'),
       ('shipper3');

练习

用两种方法实现插入多条产品信息，信息如下：

name quantity_in_stock unit_price
product1 1 10
product2 2 20
product3 3 30

name	quantity_in_stock	unit_price
product1	1	10
product2	2	20
product3	3	30

-- 法一
USE sql_store;

INSERT INTO products 
VALUES (DEFAULT, 'product1', 1, 10),
       (DEFAULT, 'product2', 2, 20),
       (DEFAULT, 'product3', 3, 30)

-- 法二
INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('product1', 1, 10),
       ('product2', 2, 20),
       ('product3', 3, 30)

注意：

对于AI (Auto Incremental 自动递增) 的id字段，MySQL会记住删除的/用过的id，并在此基础上递增。

2.4 插入分级行

订单表（orders表）里的一条记录对应订单项目表（order_items表）里的多条记录，一对多（一条订单【号】可能定了多个商品），是相互关联的父子表。通过添加一条订单记录和对应的多条订单项目记录，学习如何向父子表插入分级（层）/耦合数据：

关键：在插入子表记录时，需要用内建函数 LAST_INSERT_ID() 获取相关父表记录的自增ID（这个例子中就是 order_id，也就是order_id将两张表之间产生联系)；
内建函数：MySQL里有很多可用的内置函数，也就是可复用的代码块，各有不同的功能，注意函数名的单词之间用下划线连接；
LAST_INSERT_ID()：获取最新的成功的 INSERT 语句 中的自增id，在这个例子中就是父表里新增的 order_id。

案列

新增一个订单（orders），里面包含两个订单项目/两种商品（order_items），请同时更新订单表和订单项目表。其中新增的订单信息和订单项目信息：

customer_id order_date status
1 2019-01-01 1

product_id quantity unit_price
1 2 2.5
2 5 1.5

customer_id	order_date	status
1	2019-01-01	1

product_id	quantity	unit_price
1	2	2.5
2	5	1.5

USE sql_store;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);

-- 可以先试一下用 SELECT last_insert_id() 看能否成功获取到的最新的 order_id

INSERT INTO order_items  -- 全是必须字段（NN列都打勾了），就不用指定字段名了
VALUES 
    (last_insert_id(), 1, 2, 2.5),
    (last_insert_id(), 2, 5, 1.5)

注意：

对于order表里新增记录的1是对customer_id赋的值，而不是对order_id(PK)赋值

2.5 创建表的副本

-- 方式1
DROP TABLE 要删的表名; CREATE TABLE 新表名 AS 子查询
-- 方式2
TRUNCATE  TABLE '要清空内容的表名'; INSERT INTO 表名 子查询

子查询里当然也可以用WHERE语句进行筛选

注意：

truncate（截断）是删除表格内容而不删除表格（刷新后表格依旧存在，刷新键在左边schemas旁边）,但是drop语句是完全删除（刷新后表格不存在啦）,另外insert也是不复制表格属性（字段的PK和AI）的.

案列1

题目：运用 CREATE TABLE 新表名 AS 子查询 快速创建表 orders 的副本表 orders_archived（订单存档）

USE sql_store;

CREATE TABLE orders_archived AS -- as可省略
    SELECT * FROM orders  -- 子查询

代码优点：

可以避免写有一堆的INSERT语句

代码解析：

SELECT * FROM orders 选择了 orders 中所有记录，作为AS的内容，则SELECT * FROM orders 是一个子查询。子查询是指嵌套在其他SQL查询中的选择语句。

子查询可以出现在SELECT、INSERT、UPDATE、或DELETE语句中，以及在FROM子句中。子查询通常用于在执行主查询之前，筛选或处理数据。它们可以返回单个值、单列、多列或行集。

注意：

复制表的数据到新表后，新表会忽略掉原来表中的属性（字段的PK和AI）。也就是说，在新表中需要插入新的记录时，需要提供准确的id数据（没有自动自动递增属性）

子查询：任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询，子查询是一个很有用的技巧。

注意：

删除不存在的表的话可能会报错，所以建表和删表语句都最好加上条件语句（后面会讲）

案列2

题目：用2019年以前的 orders 订单创建一个副本表 orders_archived(用两种方法实现)

-- 法一 
USE sql_store;

DROP TABLE orders_archived;  -- 也可右键该表点击 drop
CREATE TABLE orders_archived AS
    SELECT * FROM orders
    WHERE order_date < '2019-01-01'

-- 法二
TRUNCATE TABLE orders_archived
-- 清空orders_archived中的内容
INSERT INTO orders_archived  
-- 不用指明列名，会直接用子查询表里的列名
-- 这里不用另外加values
    SELECT * FROM orders  
    -- 子查询，替代原先插入语句中VALUES(……,……),(……,……),…… 的部分
    WHERE order_date < '2019-01-01'

练习

题目：创建一个存档发票表，只包含有过支付记录的发票并筛选出发票编号、顾客名字、付费日期。

use sql_invoicing;

create table invoices_archived as
    select i.invoice_id, c.name as client, i.payment_date 
    from invoices as i
    inner join clients as c -- 也可以用left join
    on c.client_id = i.client_id -- 也可以用USING (client_id)
    where payment_date is not null

解题思路：

step 1：先创建子查询，确定新表内容：

A. 合并发票表和顾客表

B. 筛选支付记录不为空的行/记录

C. 筛选（并重命名）需要的列

step 2：将第一步得到的子查询内容存入新创建的副本订单存档表 CREATE TABLE 新表名 AS 子查询

注意：

第1步得到的查询内容，可以先运行看一下，确保准确无误后，再进行第二步操作。

注意：

若需要重复操作上面代码，需要先删除已经创建好的invoices_archived表。操作方法时，右击schemas界面中的invoices_archived，点击drop table。

2.6 更新单行

用 UPDATE …… 语句来修改表中的一条或多条记录，具体语法结构：

UPDATE 表名 
SET 要修改的字段 = 具体值/NULL/DEFAULT/列间数学表达式 （修改多个字段用逗号分隔）
WHERE 行筛选

实例

题目：

将发票编号为3的payment_total更新为invoice_total的一半，payment_date更新为due_date的日期。

USE sql_invoicing;

UPDATE invoices
SET 
    payment_total = 100 / 0 / DEFAULT / NULL / 0.5 * invoice_total, 

    payment_date = '2019-01-01' / DEFAULT / NULL / due_date
WHERE invoice_id = 3

注意：

若payment_total =0.5 * invoice_total改写为payment_total =50% * invoice_total则会报错，因为 % 被解释为取模运算符，而不是百分比。在 SQL 中，取模运算符用于计算两个数相除的余数；
由于首次对原表中的数据进行了更新，有时会出现警告⚠，但更新操作已经完成：1 row(s) affected。

2.7 更新多行

让 WHERE…… 的条件包含更多记录，就会同时更改多条记录了

注意：

Workbench默认开启了Safe Updates功能，不允许同时更改多条记录，要先关闭该功能（在 Edit-Preferences-SQL Editor-Safe Updates）,然后重新连接服务器

题目：

将发票编号为3和4的payment_total统一更新为233，payment_date统一更新为due_date的日期。

USE sql_invoicing;

UPDATE invoices
SET payment_total = 233, payment_date = due_date

-- 该客户的发票记录不止一条，将同时更改
WHERE client_id IN (3, 4) 
-- b站第二章 4~9 讲的那些写 WHERE 条件的方法均可用
-- 甚至可以直接省略 WHERE 语句，会直接更改整个表的全部记录

练习

让所有非90后顾客的积分增加50点

USE sql_store;

UPDATE customers
SET points = points + 50
WHERE birth_date < '1990-01-01'

2.8 在UPDATE中使用子查询

本质上是将子查询用在 WHERE…… 行筛选条件中

注意

括号的使用;

IN 后除了可接 （……, ……） 也可接由子查询得到的多个数据（一列多条数据），感觉和前面（创建表的副本一节中的法二中提到）insert into 表名 后可接子查询道理是相通的。

案列

题目：

更新发票记录表中名字叫 Yadel 的记录，将其 payment_total更新为567， payment_date更新为due_date对应的日期。

思路：

invoices表只有 client_id，故先要从另一个顾客表中查询叫 Yadel 人的 client_id

USE sql_invoicing;

UPDATE invoices
SET payment_total = 567, payment_date = due_date

WHERE client_id = 
    (SELECT client_id 
    FROM clients
    WHERE name = 'Yadel');
    -- 放入括号，确保先执行

-- 补充
-- 若子查询返回多个数据（一列多条数据）时就不能用等号而要用 IN 了：
WHERE client_id IN 
    (SELECT client_id 
    FROM clients
    WHERE state IN ('CA', 'NY'))

建议：

Update 前，最好先验证看一看子查询以及WHERE行筛选条件是不是准确的，筛选出的是不是我们的修改目标，确保不会改错记录，再套入 UPDATE SET 语句更新，如上面那个就可以先验证子查询：
SELECT client_id 
FROM clients
WHERE state IN ('CA', 'NY')
以及验证WHERE行筛选条件（即先不UPDATE，先SELECT，改之前，先看一看要改的目标选对了没）
SELECT *
FROM invoices
WHERE client_id IN (
    SELECT client_id 
    FROM clients
    WHERE state IN ('CA', 'NY')
)
确保WHERE行筛选条件准确准确无误后，再放到修改语句后执行修改：
UPDATE invoices
SET payment_total = 567, payment_date = due_date
WHERE client_id IN (
    SELECT client_id 
    FROM clients
    WHERE state IN ('CA', 'NY')

练习

题目：将 orders 表里那些分数>3k 的用户的订单 comments 改为 ‘gold customer’

思考步骤：

WHERE 行筛选出要求的顾客
SELECT 列筛选他们的id
将前两步作为子查询用在修改语句中的 WHERE 条件中，执行修改

USE sql_store;

UPDATE orders
SET comments = 'gold customer'
WHERE customer_id IN
    (SELECT customer_id
    FROM customers
    WHERE points > 3000)

2.9 删除行

语法结构：

DELETE FROM 表 
WHERE 行筛选条件
（当然也可用子查询）
（若省略 WHERE 条件语句会删除表中所有记录（和 TRUNCATE 等效））

注意：

where后面也可以用子查询
省略WHERE条件语句:运行后会删除表中所有记录（等同于TRUNCATE）

案列

删除顾客id为3的发票记录；

删除顾客名字叫’Myworks’的发票记录。

USE sql_invoicing;

DELETE FROM invoices
WHERE client_id = 3
-- WHERE可选，省略就是会删除整个表的所有行/记录
/WHERE client_id = 
    (SELECT client_id  
    /*Mosh 错写成了 SELECT *，将报错：
    Error Code: 1241. Operand should contain 1 column(s) 
    Operand n. [计] 操作数；[计] 运算对象；运算元 */
    FROM clients
    WHERE name = 'Myworks')

2.10 恢复数据库

重新运行那个 create-databases.sql 文件以重置数据库
.(img-bnp7q9pS-1708258515153)]

思考步骤：

WHERE 行筛选出要求的顾客
SELECT 列筛选他们的id
将前两步作为子查询用在修改语句中的 WHERE 条件中，执行修改

USE sql_store;

UPDATE orders
SET comments = 'gold customer'
WHERE customer_id IN
    (SELECT customer_id
    FROM customers
    WHERE points > 3000)

2.9 删除行

语法结构：

DELETE FROM 表 
WHERE 行筛选条件
（当然也可用子查询）
（若省略 WHERE 条件语句会删除表中所有记录（和 TRUNCATE 等效））

注意：

where后面也可以用子查询
省略WHERE条件语句:运行后会删除表中所有记录（等同于TRUNCATE）

案列

删除顾客id为3的发票记录；

删除顾客名字叫’Myworks’的发票记录。

[外链图片转存中…(img-kJUFZtl0-1708258515153)]

USE sql_invoicing;

DELETE FROM invoices
WHERE client_id = 3
-- WHERE可选，省略就是会删除整个表的所有行/记录
/WHERE client_id = 
    (SELECT client_id  
    /*Mosh 错写成了 SELECT *，将报错：
    Error Code: 1241. Operand should contain 1 column(s) 
    Operand n. [计] 操作数；[计] 运算对象；运算元 */
    FROM clients
    WHERE name = 'Myworks')