Mysql（二）

一、插入、更新、删除

1、插入单行

VALUES里面写的一定要和列相同，DEFAULT系统自动填写（只适用主键和NULL））
INSERT INTO customers
VALUES(
    DEFAULT,
    'Jjohn',
    'smith',
    NULL,
)
也可以规定插入的列
INSERT INTO 
    customers（
        name,
        first_name,
        last_name,
        city
    ）
VALUES(
    'city',
    'Jjohn',
    'smith',
    'va',
)

2、插入多行

INSERT INTO shipper(name)
    VALUES ('va'),
           ('ve'),
           ('vr')

3、插入分层行

LAST_INSTERT_ID()  (最近插入的ID)  (数据库引擎自带的内置功能)

INSERT INTO orders (customer_id,order_data,status)
    VALUES (1,'1999-04-12',1);
INSERT INTO Order_iteams
    VALUES
        (LAST_INSTERT_ID(),1,1,2.95),
        (LAST_INSTERT_ID(),2,1,3.95)

4、创建表复制

(快速建立一张表的复制，但是没有主键，和自动递增，所以新增如果有主键递增需要自己手动输入)
(复制orders创建一个新表order_archived)
CREATE TABLE order_archived AS SELECT * FROM orders
(将自己需要的信息导入新表)       
INSERT INTO order_archived
SELECT *
FROM orders
WHERE order_data < '1999-04-12'

5、更新单行

UPDATE invoices
SET
    payment_total = invoice_total * 0.5,
    patment_date = due_date
WHERE invoice_id = 3

6、更新多行

UPDATE invoices
SET
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN (3,4)

7、在UPDATE中使用子查询

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

8、删除行

DELETE FROM invoices  (表名)
WHERE client_id = (
    SELECT *
    FROM clients
    WHERE name = 'wang'
)