MYSQL学习笔记-列属性（插入/更新/删除数据）

看表的定义：

• PK——primary key
• NN——not null
• UQ——unique
• BIN——binary
• UN——unsigned
• ZF——zero fill
• AI——auto increment

一、 插入行

1.1 插入单行

INSERT INTO customers
VALUES ()

1.2 插入多行

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

1.3 插入分层行

设有表格orders

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

INSERT INTO order_items
VALUES 
	(LAST_INSERT_ID(), 1, 1, 2.2),
	(LAST_INSERT_ID(), 2, 1, 2.3)

结果如下：
orders表格：

order_items表格：

二、创建表复制

-- 首先创建
CREATE TABLE orders_archived AS
SELECT * FROM orders

• 但是复制的时候默认会忽略表格的属性，例如PK、AI

• 第二句称为子查询，子查询也可以写在INSERT INTO 语句之后，比如给orders_archived表格里插入orders中的部分数据

练习：

复制invoices表格的部分记录置于新表，命名为invoices_archive
client_id换成client_name
只要支付过的，payment_date不为空

解题思路：

• 首先要先把需要的数据SELECT *
• 然后根据要求JOIN，把表格连接起来，查看运行结果
• 修改SELECT语句，选择需要的列，并把clients中的name列加进来，重命名
• 加上payment_date非空的条件，查看运行结果
• 最后CREAT TABLE AS
• DROP TABLE是删除表格

CREATE TABLE invoices_archieved 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
	USING (client_id)
WHERE payment_date IS NOT NULL

三、更新行

3.1 更新单行

UPDATE invoices
--设置更新的内容
SET payment_total =10, payment_date = '2019-03-01'
--选择更新的行序号
WHERE invoice_id = 1

3.2 更新多行

直接修改WHERE条件即可，但要注意MySQL里不允许，需要设置

设置完要重新连接。

UPDATE invoices
--设置更新的内容
SET payment_total =10, payment_date = duedate
--选择更新的行序号,对应多行
WHERE client_id = 3
--WHERE client_id IN（3），WHERE语句的相关内容都可以用

3.3 在UPDATES中使用子查询

在具体使用时，可以先运行SELECT语句，观察查询的结果是否正确，然后再UPDATE。

--单个记录
USE sql_invoicing;
UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = 
	(SELECT client_id
	FROM clients
	WHERE name = 'Myworks')

--多个记录
USE sql_invoicing;
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'))

练习

将积分超过3000分的顾客更新订单注释，标注为金牌用户

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

删除行

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

不写WHERE的话删除全记录