数据库和表的概念

嗨！我是长安。现在PostgreSQL已经装好了，让我们开始真正的学习之旅！

🏗️ 数据库的层级结构

想象一下你在整理文件：

📁 电脑硬盘 (PostgreSQL服务器)
  └─ 📁 我的文档 (数据库Database)
      ├─ 📄 工作.xlsx (表Table)
      ├─ 📄 学习.xlsx (表Table)
      └─ 📄 生活.xlsx (表Table)

PostgreSQL服务器：就像你的电脑硬盘
数据库（Database）：就像一个文件夹
表（Table）：就像文件夹里的Excel表格
数据行（Row）：就像表格里的每一行

🎯 什么是数据库（Database）

数据库是一个数据的容器，用来组织和管理相关的数据。

举个例子

假设你要做一个博客网站，你可能需要：

一个数据库叫blog_system
里面有多个表：
- users表：存储用户信息
- posts表：存储文章
- comments表：存储评论

-- 这就是数据库的样子
blog_system
├── users（用户表）
├── posts（文章表）
└── comments（评论表）

📝 创建你的第一个数据库

方法一：使用psql命令行

-- 创建数据库
CREATE DATABASE my_blog;

-- 查看所有数据库
\l

-- 连接到数据库
\c my_blog

-- 显示当前数据库
SELECT current_database();

方法二：使用pgAdmin

在左侧找到"Databases"
右键 → "Create" → "Database"
输入数据库名：my_blog
点击"Save"

长安提醒：数据库名建议用小写字母+下划线，比如my_blog、user_management，不要用空格或中文！

🎯 什么是表（Table）

表是数据库里的具体存储单元，就像Excel表格一样。

表的结构

users表：
┌────┬──────────┬───────────────────┬──────────┐
│ id │ username │ email             │ age      │
├────┼──────────┼───────────────────┼──────────┤
│ 1  │ changan  │ changan@email.com │ 25       │
│ 2  │ xiaoming │ xm@email.com      │ 22       │
│ 3  │ lily     │ lily@email.com    │ 28       │
└────┴──────────┴───────────────────┴──────────┘

列（Column）/字段（Field）：id、username、email、age
行（Row）/记录（Record）：每一行数据
单元格（Cell）：具体的值，比如"changan"

📝 创建你的第一个表

最简单的例子

-- 创建一个用户表
CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    email VARCHAR(100)
);

让我一行一行解释：

CREATE TABLE users：创建一个名叫users的表
id INT：创建一个叫id的列，类型是整数
username VARCHAR(50)：创建一个叫username的列，类型是最多50字符的字符串
email VARCHAR(100)：创建一个叫email的列，最多100字符

更专业的例子

-- 创建一个完整的用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- 自增ID，主键
    username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名，不能为空，唯一
    email VARCHAR(100) NOT NULL UNIQUE,    -- 邮箱，不能为空，唯一
    password VARCHAR(255) NOT NULL,        -- 密码，不能为空
    age INT CHECK (age >= 0 AND age <= 150),  -- 年龄，有范围限制
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 创建时间，默认当前时间
);

关键字解释：

SERIAL：自动递增的整数，1、2、3、4...
PRIMARY KEY：主键，每行的唯一标识
NOT NULL：这个字段不能为空
UNIQUE：这个字段的值不能重复
CHECK：添加约束条件
DEFAULT：默认值

长安的经验：一般每个表都会有一个id作为主键，这是个好习惯！

🎮 实战练习：创建博客系统的表

让我们一起创建一个简单博客系统需要的表！

第一步：创建数据库

CREATE DATABASE blog_system;
\c blog_system

第二步：创建用户表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    bio TEXT,  -- 个人简介
    avatar_url VARCHAR(255),  -- 头像URL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

第三步：创建文章表

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,  -- 作者ID
    title VARCHAR(200) NOT NULL,  -- 文章标题
    content TEXT NOT NULL,  -- 文章内容
    views INT DEFAULT 0,  -- 浏览次数
    likes INT DEFAULT 0,  -- 点赞数
    published BOOLEAN DEFAULT FALSE,  -- 是否发布
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- 外键关联
);

第四步：创建评论表

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,  -- 文章ID
    user_id INT NOT NULL,  -- 评论者ID
    content TEXT NOT NULL,  -- 评论内容
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),  -- 关联文章
    FOREIGN KEY (user_id) REFERENCES users(id)   -- 关联用户
);

看懂了吗？ 这就是一个完整的博客系统的数据结构！

🔑 重要概念详解

主键（Primary Key）

主键是表中唯一标识每一行的列。

-- 方式1：定义时指定
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- 方式2：单独添加
CREATE TABLE students (
    id SERIAL,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

-- 方式3：复合主键（多个列组成）
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

主键的特点：

✅ 不能为空（NOT NULL）
✅ 不能重复（UNIQUE）
✅ 一个表只能有一个主键
✅ 可以由多列组成（复合主键）

外键（Foreign Key）

外键用来建立表与表之间的关系。

-- posts表的user_id是外键，指向users表的id
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

这意味着：

你不能在posts表里插入一个不存在的user_id
如果你删除了users表里的某个用户，相关的文章会怎样？（可以设置级联删除）

-- 级联删除：删除用户时，自动删除他的文章
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

-- 限制删除：有文章的用户不能删除
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT

-- 设置为空：删除用户时，文章的user_id变成NULL
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL

约束（Constraints）

约束是对数据的规则限制。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,  -- 非空约束
    price DECIMAL(10, 2) CHECK (price > 0),  -- 检查约束
    sku VARCHAR(50) UNIQUE,  -- 唯一约束
    stock INT DEFAULT 0,  -- 默认值约束
    category VARCHAR(50) CHECK (category IN ('电子', '图书', '食品'))  -- 枚举约束
);

🛠️ 常用的表操作命令

查看表结构

-- 查看所有表
\dt

-- 查看某个表的结构
\d users

-- 用SQL查询查看
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users';

修改表结构

-- 添加新列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改列类型
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- 重命名列
ALTER TABLE users RENAME COLUMN username TO user_name;

-- 重命名表
ALTER TABLE users RENAME TO app_users;

删除表

-- 删除表（小心使用！）
DROP TABLE users;

-- 如果表存在才删除
DROP TABLE IF EXISTS users;

-- 删除表及所有依赖
DROP TABLE users CASCADE;

长安的忠告：DROP TABLE是不可逆的操作，删除前一定要确认！生产环境更要谨慎！⚠️

💡 设计表的最佳实践

1. 命名规范

-- ❌ 不好的命名
CREATE TABLE User (  -- 首字母大写
CREATE TABLE "user table" (  -- 有空格
CREATE TABLE yonghu (  -- 用拼音

-- ✅ 好的命名
CREATE TABLE users (  -- 小写，复数
CREATE TABLE user_profiles (  -- 下划线分隔
CREATE TABLE blog_posts (  -- 清晰明了

2. 主键设计

-- ✅ 推荐：使用SERIAL或BIGSERIAL
id SERIAL PRIMARY KEY

-- ✅ 也可以：使用UUID（适合分布式系统）
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

3. 时间戳

-- ✅ 每个表都应该有创建和更新时间
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

4. 软删除

-- ✅ 不直接删除数据，而是标记为删除
deleted_at TIMESTAMP,  -- 删除时间，NULL表示未删除
is_deleted BOOLEAN DEFAULT FALSE  -- 是否删除

5. 字段长度

-- 根据实际需求设置合理的长度
username VARCHAR(50),   -- 用户名一般不会太长
email VARCHAR(100),     -- 邮箱差不多够了
url VARCHAR(255),       -- URL可能比较长
description TEXT        -- 描述类的用TEXT

🎮 练习题

现在轮到你啦！试着完成这些任务：

任务1：创建学生管理系统

创建以下表：

students表：学生信息（id, name, age, grade, class）
courses表：课程信息（id, name, teacher, credits）
enrollments表：选课记录（student_id, course_id, score, enrolled_at）

点击查看参考答案

-- 学生表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 6 AND age <= 100),
    grade VARCHAR(20),
    class VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 课程表
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    teacher VARCHAR(50),
    credits INT CHECK (credits > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 选课表
CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score DECIMAL(5, 2) CHECK (score >= 0 AND score <= 100),
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE(student_id, course_id)  -- 一个学生不能重复选同一门课
);

任务2：思考题

为什么主键通常用自增ID而不是直接用email？
什么时候应该使用外键约束？
DELETE和TRUNCATE有什么区别？

点击查看答案

为什么用自增ID？
- ID是数字，查询速度快
- Email可能会改变，ID永远不变
- ID占用空间小，做外键更高效
什么时候用外键？
- 表之间有明确的关联关系
- 需要保证数据一致性
- 防止插入无效的关联数据
DELETE vs TRUNCATE
- DELETE：一行一行删除，可以回滚，可以加WHERE条件
- TRUNCATE：直接清空表，速度快，不能回滚，不能加条件

📚 下一步

恭喜！你已经掌握了数据库和表的基础知识。现在你知道：

✅ 如何创建数据库和表
✅ 什么是主键和外键
✅ 如何设计表结构
✅ 基本的表操作命令

接下来，让我们学习数据类型详解，了解PostgreSQL支持哪些数据类型！

长安的话：

数据库设计是一门艺术！一个好的表结构设计，能让后续的开发事半功倍。反之，糟糕的设计会让你痛苦不堪。
不过别担心，设计能力是练出来的！多做项目，多思考，慢慢就有感觉了。💪

有问题随时来编程指南找我！