SQL 入门 5：SQL 表操作：定义、插入与复制

以下为 SQL 中表定义、数据插入及表复制的用法解析。

学习内容

1. 表定义
• CHAR(n)：固定长度字符串，最多 255 字符。
• VARCHAR(n)：可变长度字符串，最多 65,535 字符。
• FLOAT：单精度浮点数，4 字节。
• DOUBLE：双精度浮点数，8 字节。
• INT/INTEGER：4 字节，范围 -2,147,483,648 到 2,147,483,647。
• DATE：日期，格式 'YYYY-MM-DD'。
• TIME：时间，格式 'HH:MM:SS'。
• DATETIME：日期时间，格式 'YYYY-MM-DD HH:MM:SS'。
• 列名：每列需命名。
• 数据类型（Datatype）：
• 主键（PK, Primary Key）：唯一标识列，非空，可多列组合。常设 AUTO_INCREMENT（AI），自动递增。
• 非空约束（MN, Must Not Null）：决定列是否允许空值。

 

1. 插入单行
• INSERT INTO 指定表名，VALUES 提供数据。
• 可全列插入，顺序匹配表定义，含 DEFAULT（如主键自动递增）或 NULL。工具如 DataGrip 会标注列名参考。
• 也可选择性插入，指定部分列名。
2. 插入多行
• 单条 INSERT 语句支持多行，用逗号分隔。
3. 多表插入
• 插入关联表数据，LAST_INSERT_ID() 获取最新自动递增值。
• 示例：先插 orders 获取订单 ID，再插 order_items 添加产品。
4. 表复制
• CREATE TABLE AS 复制表结构和数据，不保留主键等约束。
• 可结合 INSERT INTO 和条件复制部分数据，如存档旧订单。

---

示例代码与讲解

1. 插入单行

INSERT INTO customers
VALUES (DEFAULT, 'shaun', 'livingtson', '1988-03-14', NULL, 'king street', 'Oakland', 'CA', 999);

• 全列插入 customers，主键（顾客 ID）用 DEFAULT，因设 AUTO_INCREMENT 自动递增。NULL 表示电话为空。
• 顺序对应表定义：customer_id, first_name, last_name, birth_date, phone, address, city, state, points。
• DataGrip 可显示列名参考。

INSERT INTO customers (first_name, last_name, birth_date, address, city, state)
VALUES ('shaun', 'livingtson', '1988-03-14', 'king street', 'Oakland', 'CA');

• 选择性插入，省略 customer_id（自动递增）、phone（默认 NULL）、points。

2. 插入多行

INSERT INTO shippers (name)
VALUES ('SH1'), ('SH2'), ('SH3');

• 插入 shippers 三行，仅指定 name，其他列（如主键）自动处理。

3. 多表插入

INSERT INTO orders (customer_id, order_date, shipper_id)
VALUES (1, '1999-01-01', 1);

• 插入 orders 一行，指定客户 ID、订单日期和运输商 ID。

SELECT LAST_INSERT_ID();

• 返回当前会话最后插入的 order_id（自动递增）。

INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 2.344),
       (LAST_INSERT_ID(), 3, 4, 3.322);

• 用最新 order_id 插入 order_items，添加两个产品。
• 列顺序：order_id, product_id, quantity, unit_price。

4. 表复制

CREATE TABLE orders_archive AS
SELECT *
FROM orders;

• 复制 orders 到 orders_archive，含所有数据，但不保留主键等设置。

INSERT INTO orders_archive
SELECT *
FROM orders
WHERE order_date <= '2019-01-01';

• 将 2019 年 1 月 1 日前的订单插入 orders_archive，实现存档。

---

作业

1. 插入多行

INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('product1', 34, 2.13),
       ('product2', 243, 9.98),
       ('product3', 43, 2.64);

• 插入 products 三行，指定产品名、库存量和单价。

2. 表复制

USE sql_invoicing;
CREATE TABLE invoices_archive AS
SELECT invoice_id, i.number, c.name, invoice_total, payment_total, invoice_date, due_date, payment_date
FROM invoices i
JOIN clients c USING (client_id)
WHERE i.payment_date IS NOT NULL;

• 复制 invoices 已支付记录到 invoices_archive，含发票信息和客户名。

---

总结

本次解析了表定义（列名、数据类型、主键、非空约束）、单行与多行插入、多表关联插入及表复制。基于 sql_store 和 sql_invoicing 数据库。