MySQL课程笔记---第三章：增查删改

主讲老师：Mosh；教程链接：【【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！-哔哩哔哩】 https://b23.tv/MqVRzdk 

-- 第三章:增查删改

-- 列属性：
-- VARCHAR : variable类型，VARCHAR(50)可以不达到50个空
-- 但是CHAR（50）,必须达到五十个空，当有效信息少于50时，会浪费空间
-- INT：整数数据类型
-- PK：主键；NN：非空值；AI：自动递增（像顾客编号，系统会自动递增更新，从1往后加）
-- Default/Expression:默认值

-- 插入单行
INSERT INTO customers
VALUES(
	DEFAULT, -- 采用默认值而不是给定数值，因为给的那个数值可能与程序后面自己生成的数值重复
    'John',
    'Smith',
    '1990-01-01',
    NULL, -- NULL和DEFAULT一样的作用
    'address',
    'city',
    'CA',
    DEFAULT)； -- 输入插入行每一列的数值
    -- 也可以对特定列插值
INSERT INTO customers(
		first_name,
        last_name,
        birth_date,
        address,
        city,
        state) -- 只对这六列排序，下面的数值要对应此处列的顺序
VALUES(
    'John',
    'Smith',
    '1990-01-01',
    'address',
    'city',
    'CA') 
    
-- 插入多行
INSERT INTO shippers(name)
VALUES('Shipper1'),
	('Shipper2'),
    ('Shipper3') -- 程序会为发货人id自动生成值，不需要自己添加
    
-- 练习
INSERT INTO products(name,quantity_in_stock,unit_price)
VALUES ('Alice',50,3.46),
	('Bob',30,2.21),
    ('Cindy',39,1.29)
    
-- 插入分层行
INSERT INTO orders(customer_id,order_date,status)
VALUES(1,'2019-01-02',1); -- 先对order表中行进行插入，自定义选中顾客1再买一单，此时新订单的订单id由程序自己生成，
-- 我们未知，故在下面操作时需用函数LSAT_INSERT_ID获取新订单的id
INSERT INTO order_items -- 对order_items进行插入，我们需要order_id,product_id,quantity,unit_price
VALUES 
	(last_insert_id(),1,1,2.95),
    (last_insert_id(),2,1,3.95) -- order_id由last_insert_id()导出
    
-- 表复制（不会保留表结构，只能复制表数值，表结构要自己去设计界面调整）
-- 两种方式：第一种：直接创建表；
CREATE TABLE orders_archived AS
SELECT * FROM orders -- 此时SELECT子句应用在另一个子句CREATE TABLE下，为子查询
-- 第二种：直接利用子查询进行插入多行操作（注意此时用的表是第一种得到的表进行了truncate操作之后的空白表，truncate操作只删除数值，不删除表结构）
INSERT INTO orders_archived()
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

-- 练习题
CREATE TABLE invoices_archive AS
SELECT
	i.invoice_id,
    i.number,
    i.invoice_total,
    i.invoice_date,
    i.payment_date,
    i.payment_total,
    i.due_date,
    c.name AS client
FROM invoices i
JOIN clients c
	USING (client_id)
WHERE payment_date IS NOT NULL

-- 更新单行
UPDATE invoices
SET 
	payment_total = DEFAULT, -- 也可以写数学表达式
    payment_date = NULL -- 更新内容
WHERE invoice_id = 1 -- 更新位置

-- 更新多行
UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = 3 -- 更新多个行
-- WHERE client_id IN (3,4) --多个客户

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

-- 在UPDATE中使用子查询
UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = -- 此处具体的客户id未知，故须子查询返回用户id
				(SELECT client_id
				FROM clients
				WHERE name = 'Myworks') -- 子查询返回名叫myworks的用户的id
-- 多个条件下时：
WHERE client_id IN -- 这里因为是对多个行调整，需要变成IN
				(SELECT client_id
				FROM clients
				WHERE state IN ('CA','NY')) -- 所属州来自CA，和NY

UPDATE orders -- 练习题
SET 
	comments = 'gold'
WHERE customer_id IN 
					(SELECT customer_id
					FROM customers
					WHERE points > 3000)
                    
-- 删除行
DELETE FROM invoices 
WHERE clients_id = 
					(SELECT clients_id
					FROM clients
					WHERE name = 'Myworks')