增、删、改

子查询是SELECT语句建立在其他语句之上

表属性

Inserting a Single Row

INSERT INTO customers ( last_name, first_name, birth_date, address, city, state )
VALUES
	(
		'last_name',
		'first_name',
		'2000-01-01',
		'address',
		'city',
		'广州')

Inserting Multiple Rows

INSERT INTO products (`name`,`quantity_in_stock`,`unit_price`)
VALUES ('name1',50,5), 
	   ('name2',50,5), 
	   ('name3',50,5)

Inserting Hierarchical Rows（插入层级数据）

我们知道订单的状态、备注和承运商信息，但是具体的信息并不在这个表内，他们在订单产品表中

INSERT INTO orders(customer_id,order_date,`status`)
-- 给一号顾客添加订单信息
VALUES ('1','2019-01-02',1);

-- 将最新的订单消息添加进去
INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(),1,1,2.95),
	(LAST_INSERT_ID(),2,1,3.95)

Creating a Copy of a Table（创建表的副本/存档）

CREATE TABLE orders_archived AS
-- 子查询
SELECT * FROM orders	-- 此时mysql会忽略原本的主键和自增

-- 截断表。删除表内所有数据

-- 复制一部分数据
INSERT INTO orders_archived

SELECT *
FROM orders
WHERE order_date < '2019-01-01'

-- 练习
CREATE TABLE invoices_archived AS
SELECT 
	invoice_id,
	number,
	c.`name` AS client,
	invoice_total,
	payment_total,
	invoice_date,
	due_date,
	payment_date
FROM invoices i
JOIN clients c
	USING (client_id)		-- 可以得到客户的名字
-- 	ON i.client_id = c.client_id 
WHERE i.payment_date IS NOT NULL	-- 选择payment不为空

Updating a Single Row（更新单一记录）

UPDATE invoices
-- SET payment_total = 10, payment_date = '2019-03-01'
SET payment_total = DEFAULT, payment_date = NULL
WHERE invoice_id = 1

SELECT * FROM invoices

-- 到期还款只还了50%，将直到现在还款时间设置为截止时间
UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE client_id = 3

Updating Multiple Rows（更新多个记录）

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

Using Subqueries in Updates（用子查询更新数据）

-- 只知道顾客i名字，不知道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'
)

-- 只知道一些州的名字，不知道id，此时是一个范围 更新这两个地址
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 ('CA','NY')
)

-- 将points>3000的客户改为金牌客户
UPDATE orders
SET 
	comments = 'Gold'
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE points > 3000
)

Deleting RowS（删除记录）

DELETE FROM invoices
WHERE client_id = (
    SELECT client_id
    FROM clients
    WHERE `name` = 'Myworks'
)

Restoring the Databases