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

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

1
USE sql_store;

日期：2025 年 3 月 30 日。以下为 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’。
  3. 列名：每列需命名。
  4. 数据类型（Datatype）：
  5. 主键（PK, Primary Key）：唯一标识列，非空，可多列组合。常设 AUTO_INCREMENT（AI），自动递增。
  6. 非空约束（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. 插入单行

1
2
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 可显示列名参考。
1
2
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. 插入多行

1
2
INSERT INTO shippers (name)
VALUES ('SH1'), ('SH2'), ('SH3');
  • 插入 shippers 三行，仅指定 name，其他列（如主键）自动处理。

3. 多表插入

1
2
INSERT INTO orders (customer_id, order_date, shipper_id)
VALUES (1, '1999-01-01', 1);
  • 插入 orders 一行，指定客户 ID、订单日期和运输商 ID。
1
SELECT LAST_INSERT_ID();
  • 返回当前会话最后插入的 order_id（自动递增）。
1
2
3
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. 表复制

1
2
3
CREATE TABLE orders_archive AS
SELECT *
FROM orders;
  • 复制 orders 到 orders_archive，含所有数据，但不保留主键等设置。
1
2
3
4
INSERT INTO orders_archive
SELECT *
FROM orders
WHERE order_date <= '2019-01-01';
  • 将 2019 年 1 月 1 日前的订单插入 orders_archive，实现存档。

作业

1. 插入多行

1
2
3
4
INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('product1', 34, 2.13),
       ('product2', 243, 9.98),
       ('product3', 43, 2.64);
  • 插入 products 三行，指定产品名、库存量和单价。

2. 表复制

1
2
3
4
5
6
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 数据库。