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

艺帆风顺 发布于 2025-04-18 31 次阅读

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'。
  • 列名：每列需命名。
  • 数据类型（Datatype）：
  • 主键（PK, Primary Key）：唯一标识列，非空，可多列组合。常设 AUTO_INCREMENT（AI），自动递增。
  • 非空约束（MN, Must Not Null）：决定列是否允许空值。

  1. 插入单行

  • INSERT INTO 指定表名，VALUES 提供数据。
  • 可全列插入，顺序匹配表定义，含 DEFAULT（如主键自动递增）或 NULL。工具如 DataGrip 会标注列名参考。
  • 也可选择性插入，指定部分列名。

  • 插入多行

    • 单条 INSERT 语句支持多行，用逗号分隔。

  • 多表插入

    • 插入关联表数据，LAST_INSERT_ID() 获取最新自动递增值。
    • 示例：先插 orders 获取订单 ID，再插 order_items 添加产品。

  • 表复制

    • 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 数据库。