使用的是MySQL数据库
- 如果在插入记录前,删除了原有最后记录行,如最后一行的id为12,新插入的数据id会从13开始。
1. 插入单行 INSERT INTO
- customers表结构
INSERT INTO customers (
-- 列和对应数据可以不按照表结构中列的顺序写,一样可以插入数据
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (
-- 第一种写法:上面所有的列名都写了,下面每一列数据也写全
-- 第二种写法:上面只写有确切值的列名称,下面的值就可以不用写DEFAULT和NULL值,没有写的列MySQL会自动生成默认值
-- 第一列customer_id设置了自增,不要写确切值,可以写DEFAULT或者不写,MySQL会自动生成这个值
-- DEFAULT,
-- 第二列first_name不为空
'John',
-- 第三列last_name不为空
'Smith',
-- 第四列birth_date默认为空 可以写确切的日期,可以赋NULL关键字省略这个值
'1990-01-01',
-- 第五列phone默认为空 没有确切号码,可以赋NULL或者DEFAULT关键字,让MySQL给这列空值
-- NULL,
-- 第六列address不为空
'address',
-- 第七列city不为空
'city',
-- 第八列state不为空
'VA')
-- 最后一列points不为空,默认值为0,可以写确切值,也可以用DEFAULT关键字让MySQL生成0
-- DEFAULT)
- 结果显示:插入了一条customer_id为11的记录,customer_id自增。
2. 插入多行
- shippers表结构
-- 插入多行数据,只要在每个数据之间加逗号
INSERT INTO shippers (name)
VALUES ('shipper1'),
('shipper2'),
('shipper3')
- 结果显示:插入了shipper_id为6、7、8的三条记录,shipper_id自增
3. 插入分层行
- 往多张表中插入数据,或者是往两个互为亲子关系的表中插入数据,使用到数据库引擎自带的内容功能 LAST_INSERT_ID() 最近插入的id
-- LAST_INSERT_ID()是数据库引擎自带的内置功能,表示获取最近插入的id 可以用SELECT LAST_INSERT_ID()查询
-- 因为orders表和order_items表存在'亲子关系',orders表是母表,order_items表是子表;
-- 就是在orders表中生成一个订单时,订单中存在一个或多个商品,因此会同时在order_items表中生成一个或多个订单项目。
-- 可以理解为先插入订单数据,再插入订单项目数据。
-- 在orders表中插入单行记录顾客id,下单时间,订单状态(订单状态有单独的表)
INSERT INTO orders (customer_id,order_date,status)
VALUES (1,'2021-11-26',1);
-- 在执行语句时,会先执行上面的语句,在orders表最后一行插入记录,order_id自增
-- 我们需要这个自增的order_id,以便我们生成订单项目记录,因此使用LAST_INSERT_ID()找到最近插入的id,也就是上面的order_id
-- 下面写order_items表中插入多行数据,order_items表中没有自增的id,四列都不为空,因此可以省略列名称
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,2.95),
(LAST_INSERT_ID(),2,1,3.95)
-- 此时就成功向order_items表中插入了两行订单项目数据
4. 创建表复制 快速将数据从一张表复制到另一张新表
- 创建表复制的缺点,MySQL会忽略id的主键和自增属性,手动到设计模式中勾上。
- 创建表复制的书写步骤,先写子查询确认查询的内容正确,再写CREATE TABLE
-- 用选择语句作为创建表里的子查询
-- 将orders订单表复制给另一张新表orders_archived订单存档表
-- 创建表orders_archived
CREATE TABLE orders_archived AS
-- 下面的选择语句称为子查询
SELECT * FROM orders;
-- 使用这个技巧创建的表的缺点,**MySQL会忽略id的主键和自增属性
-- 将orders_archived表中的数据截断(TRUNCATE table) 此时得到一个空表
-- 用选择语句作为插入语句里的子查询 可以将满足条件的数据插入到orders_archived表中
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date > '2019-01-01';
5. 更新单行 UPDATE
-- 发票1中付款金额和付款日期都没填,更新发票1后发现更新错了发票
-- 由于发票设计模式中 payment_total默认值为0,payment_date默认为空,可以写成:
UPDATE invoices
SET payment_total = DEFAULT,
payment_date = NULL
WHERE invoice_id = 1;
-- 更新发表3中付款金额为原先的50%,付款日期为截止日期
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE invoice_id = 3;
6. 更新多行
- MySQL Workbentch软件执行更新多行记录会报错,其他的应用程序不会;可以通过以下方式解决,记得保存后重新连接MySQL instance才奏效。这原本是防止意外更新或删除了一些记录。
-- 所有client_id为3的发票都更新了
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
-- WHERE子句是可选的,不写WHERE子句表示更新所有发票
WHERE client_id = 3;
7. 在UPDATE语句中使用子查询
使用子查询或者更新的条件不是确切值时,一定要先执行选择语句,检查是否是要更新的记录,只有确定是要更新的记录,再执行更新语句!!!
-- 用选择语句作为UPDATE语句中的子查询
-- 如果客户输入用户名,我们需要根据用户名查询客户id,根据客户id更新发票信息
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id =
(SELECT client_id
FROM clients
WHERE name = 'Myworks');
-- 子查询使用IN运算符,得到的client_id不止一个,因此更细语句中的条件可以使用IN运算符
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id IN
(SELECT client_id
FROM clients
WHERE state IN ('NY','CA'));
-- 不清楚哪些发票的付款日期为空时,先查询是否是要更新的发票,确认好,再删除选择语句
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE payment_date IS NULL;
8. 删除行 DELETE
-- 如果不写WHERE子句,这个语句会删除表里的全部内容,比较危险
DELETE FROM invoices;
-- 删除发票id为1的记录
DELETE FROM invoices
WHERE invoice_id = 1;
-- DELETE语句中可以使用子查询
-- 删掉名字为Myworks的客户的所有发票
DELETE FROM invoices
WHERE client_id =
(SELECT client_id
FROM clients
WHERE name = 'Myworks');
9. 恢复数据库
File----->Open SQL Script----->在存储SQL脚本的目录位置选择要恢复的数据库----->执行脚本