1.3 数据插入、更新和删除

目录

1. Column Attribute列属性

2. Inserting a Single Row插入单行

方式一：

方式二：

3. Inserting Multiple Rows插入多行

【练习】写一段SQL，在products表插入三行.

4. Inserting Hierarchical Rows插入分层行

5. Creating a Copy of the Table创建表复制

【练习】写一段SQL，将sql_invoicing库中的invoices表，部分复制至新表invoices_archive中，新表中不要client_id，将其显示为客户名称，并且只复制付款过发票。

6. Updating a Single Row更新单行

7. Updating Multiple Rows更新多行

【练习】写一段SQL，选择sql_store库，写一段查询所有在1990年前出生的客户，给他们增加50点积分。

8. Using Subqueries in UPdates在Updates中使用子查询

【练习】写一段SQL，判断sql_store库中的orders表中comments为NULL的，points超过3000的更新comments。

9. Deleting Rows删除行

10. Restoring The Databases恢复数据库

---

1. Column Attribute列属性

2. Inserting a Single Row插入单行

INSERT INTO customers -- 插入哪个表
VALUES() -- 写入每一列的值

可以通过两种方式插入单行：

方式一：

-- 方式一：
INSERT INTO customers
VALUES(
	DEFAULT,
    'John',
    'Smith',
    '1990-01-01',
    NULL,
    'address',
    'city',
    'CA',
    200);

方式二：

 -- 方式二：   
INSERT INTO customers(
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES(
    'John',
    'Smith',
    '1990-01-01',
    'address',
    'city',
    'CA',
    200);

通过上述2种方式，都能完成插入。

3. Inserting Multiple Rows插入多行

给表【shippers】插入多个值，可以先观察表属性：

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

【练习】写一段SQL，在products表插入三行.

INSERT INTO products(
	name,
    quantity_in_stock,
    unit_price)
VALUES
	('product1',10,1.95),
    ('product2',12,8.21),
    ('product3',43,97)

4. Inserting Hierarchical Rows插入分层行

本节将讲述如何在多表中插入数据。

可以看出，实际上每一笔订单可以有多个product订单，所以说orders为母表，order_items为子表。

oders表的一行，在order_items中有多个行。

INSERT INTO orders(customer_id, order_date, status)
VALUES(1,'2019-01-02',1);  -- 运行至此处，会生成一个orders_id,要将其插入order_items中

INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(),1,1,2.95), -- MySQL中的内置函数，可以返回最新插入时生成的id
	(LAST_INSERT_ID(),2,1,3.95)

观察结果可以发现，当orders表中创建了id为11的信息时，对应的order_items也创建出id为11的两条信息。

5. Creating a Copy of the Table创建表复制

要求复制表【orders】的内容至新创建表【orders_archived】。

CREATE TABLE orders_archived AS 
SELECT * FROM orders -- 子查询

观察【orders_archived】可看到，与表【orders】的内容完全一致，但是打开设计模式：

表【orders_archived】缺少主键，也没有自动递增列AI，所以利用该方法创建表时，会忽略掉这些属性。

选中【orders_archived】点击鼠标右键，点击Truncate Table，删除该表中所有的行。

要求筛选【orders】表中所有2019年前的订单，复制至【orders_archived】。

INSERT orders_archived
SELECT *
	FROM orders
    WHERE order_date < '2019-01-01';
    

【练习】写一段SQL，将sql_invoicing库中的invoices表，部分复制至新表invoices_archive中，新表中不要client_id，将其显示为客户名称，并且只复制付款过发票。

USE sql_invoicing;

CREATE TABLE invoices_archive AS
SELECT 
	i.invoice_id,
	i.number,
    c.name AS client,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.due_date,
    i.payment_date
	FROM invoices i
    JOIN clients c
		ON i.client_id = c.client_id 
	WHERE i.payment_date IS NOT NULL;

6. Updating a Single Row更新单行

打开表【invoices】，看到payment_total为0，现在想对它进行更新。

UPDATE invoices
SET  payment_total = 10, payment_date = '2019-03-01'-- 用以制定一列or多列新值
WHERE invoice_id = 1;

假如这里更新错了id，应该是3而不是1，则需要先将payment_total, payment_date 进行重置，再重新更新，如下所示：

UPDATE invoices
SET  payment_total = DEFAULT, payment_date = NULL-- 用以制定一列or多列新值
WHERE invoice_id = 1;

刷新后可以看到其恢复，然后再次更新id3：

UPDATE invoices
SET  
	payment_total = invoice_total * 0.5, 
    payment_date = due_date-- 用以制定一列or多列新值
WHERE invoice_id = 3;

刷新后可以看到id3已经完成了更新。

7. Updating Multiple Rows更新多行

与更新单行语法一致，只需要对条件进行修改。比如，可以对client_id=3的所有发票进行更新。

UPDATE invoices
SET  
	payment_total = invoice_total * 0.5, 
    payment_date = due_date-- 用以制定一列or多列新值
WHERE client_id = 3;

【练习】写一段SQL，选择sql_store库，写一段查询所有在1990年前出生的客户，给他们增加50点积分。

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

8. Using Subqueries in UPdates在Updates中使用子查询

在刚才的例子中，我们对client_id为3的信息进行了更新，如果此时只有地址，没有id应该如何？

USE sql_invoicing;
UPDATE invoices
SET  
	payment_total = invoice_total * 0.5, 
    payment_date = due_date-- 用以制定一列or多列新值
WHERE client_id IN 
		(SELECT client_id
			FROM clients
			WHERE state IN ('CA','NY'))

注：建议在执行UPDATE之前先执行查询，检查会更新什么记录。

【练习】写一段SQL，判断sql_store库中的orders表中comments为NULL的，points超过3000的更新comments。

USE sql_store;

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

9. Deleting Rows删除行

DELETE FROM invoices
WHERE invoice_id = 1; -- 使用DELETE FROM表 WHERE子查询进行删除

10. Restoring The Databases恢复数据库

打开File-->Open SQL Script

找到create-databases.sql，运行脚本，重建所有数据库。

再打开导航面板，就可以看到数据库消失，再点击刷新，练习数据库就会出现。