创建 and Altering 表
设计表 使用 CREATE TABLE, 修改它们使用 ALTER TABLE, and drop safely.
你将学到
- CREATE TABLE 语法使用列 and 数据类型
- 常见列 options: NOT NULL, DEFAULT, UNIQUE, CHECK
- ALTER TABLE 到添加, rename, and drop 列
- DROP TABLE and 危险它 carries
- 最佳实践 for 表 and 列 naming
概念讲解
CREATE TABLE
CREATE TABLE defines a new table with its columns, data types, and constraints. Each column declaration specifies a name, a type, and optional modifiers like NOT NULL, DEFAULT, UNIQUE, or CHECK. The table name and column names should be descriptive, lowercase, and use underscores (order_items, not OrderItems).
Column Modifiers
- NOT NULL — the column must always have a value; never NULL
- DEFAULT — a value used when the column is omitted from INSERT
- UNIQUE — no two rows can have the same value in this column
- CHECK (condition) — the value must satisfy a boolean expression
- REFERENCES table(col) — foreign key constraint
These modifiers are constraints that the database enforces on every insert and update. They are your first line of defense against bad data.
ALTER TABLE
ALTER TABLE modifies an existing table. You can add columns (ADD COLUMN), drop columns (DROP COLUMN), rename columns (RENAME COLUMN), change data types (ALTER COLUMN ... TYPE), and add or remove constraints. In production, altering a large table can be expensive and may briefly lock it — plan migrations carefully.
DROP TABLE
DROP TABLE permanently removes the table and all its data. There is no undo (unless you are inside a transaction). DROP TABLE IF EXISTS avoids an error if the table does not exist. Always double-check before dropping — this is the most destructive SQL command.
Naming Conventions
Use singular, lowercase, snake_case names: 用户 or 用户, order_item or order_items. Pick one convention (singular vs plural) and stick with it across the project. Primary keys are typically named id; foreign keys are <referenced_table>_id (e.g. user_id).
示例代码
-- Create a table with various column modifiers
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
category VARCHAR(50) NOT NULL DEFAULT 'misc',
sku VARCHAR(50) UNIQUE,
in_stock BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Add a new column
ALTER TABLE products ADD COLUMN weight DECIMAL(6, 2);
-- Rename a column
ALTER TABLE products RENAME COLUMN sku TO product_code;
-- Drop a column
ALTER TABLE products DROP COLUMN weight;
-- Drop the table (destructive!)
DROP TABLE IF EXISTS products;
完整表 定义使用 six 列 modifiers, 然后 three ALTER TABLE 操作 and safe DROP TABLE IF EXISTS.
动手试一试
- SQL Formatter
格式 CREATE and ALTER TABLE 语句.
- Code Diff
Compare 旧 and 新 Schema 定义边 通过边.
常见错误
错误写法
Using FLOAT or REAL for monetary 值
正确做法
Floating-点类型 introduce rounding 错误. Use DECIMAL(p, s) for money — e.g. DECIMAL(10, 2) 存储上 到 99,999,999.99 精确地.
错误写法
运行 DROP TABLE 不使用 IF EXISTS
正确做法
如果表 做不 exist, DROP TABLE 错误. Use DROP TABLE IF EXISTS 到制作 scripts idempotent and safe 到 re-运行.
练习 创建 and Altering 表
2 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。