MySQL笔记(3)--增删改

1 列属性

Data type

👉 VARCHAR: 可变长度字符串（节省空间）
👉 CHAR: 字符串，不满指定长度就会填充满

Properties

👉 PK: Primary Key
👉 NN: Not Null
👉 AI: Automatic Increment

2 插入

2.1 插入单行

INSERT INTO customers
VALUES (
    DEFAULT, 
    'John', 
    'Smith', 
    '1990-01-01',
    NULL,
    'address',
    'city',
    'CA',
    DEFAULT)

有些不是必填的, 可以skip掉:

Another Way

INSERT INTO customers (
    first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES (
    'John', 
    'Smith', 
    '1990-01-01',
    'address',
    'city',
    'CA')

2.2 插入多行

INSERT INTO shippers (name)
VALUES ('Shipper1'), 
       ('Shipper2'), 
       ('Shipper3')

2.3 插入分层行

order_items是orders的子表, 列出更为详细的信息.在orders插入行, 相应的在order_items也要插入行:

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)

LAST_INSERT_ID()相当于一个method/function, 直接调用获取最近插入的record的id.

3 创建复制表(a copy of table)

CREATE TABLE orders_archived AS
SELECT * FROM orders

通过这种方式创建的表复制会没有PK, AI这些属性

3.1 子查询

上例中的SELECT * FROM orders是子查询.

INSERT INTO也可以用子查询:

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

orders_archived表中只有满足条件的records.

4 更新

4.1 更新单行

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

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 invoice_id = 3

4.2 更新多行

在MySQL workbench需要取消safe update选项才能更新多行.

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5, 	-- 使用表达式更新
    payment_date = due_date
WHERE client_id = 3
-- WHERE client_id IN (3, 4)

client_id=3对应有多项record, 都会被update

4.3 在UPDATE中使用子查询

将上例改为:

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5, 	-- 使用表达式更新
    payment_date = due_date
WHERE client_id = 
        (SELECT client_id
         FROM clients
         WHERE name = 'Myworks')

如果子查询返回的是多个client_id:

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5, 	-- 使用表达式更新
    payment_date = due_date
WHERE client_id IN							-- 用IN
        (SELECT client_id
         FROM clients
         WHERE state IN ('CA', 'NY'))

⚠ 在执行查询之前, 应该先执行子查询看看对不对.

5 删除行

根据条件删除某些行

DELETE FROM invoices_archived
WHERE invoice_id = 2

子查询

DELETE FROM invoices
WHERE invoice_id = 
        (SELECT client_id
         FROM clients
         WHERE name = 'Vinte')

删除整表所有records

DELETE FROM invoices_archived