SQL 语言

后端核心 SQL CRUD

---

一、SQL 概览

SQL = Structured Query Language — 用于操作关系型数据库的专用语言。

• 关键字通常 大写（SELECT, FROM, WHERE...）
• 每条语句以 分号 结尾
• W3Schools SQL 教程：w3schools.com/sql

---

二、CREATE — 创建表

CREATE TABLE products (
  id INT NOT NULL,
  name VARCHAR(100),
  price MONEY,
  PRIMARY KEY (id)
);

关键概念：

概念	说明	示例
PRIMARY KEY	唯一标识每条记录	id
NOT NULL	字段不能为空	id INT NOT NULL
数据类型	定义字段存储格式	VARCHAR, INT, MONEY

注意事项

• 最后一个字段后面 不能有逗号
• 语句末尾必须有 分号

---

三、INSERT — 插入数据

-- 方式 1：全部字段插入（不需要指定列名）
INSERT INTO products
VALUES (1, 'Pen', 1.20);

-- 方式 2：指定列名（可跳过某些字段）
INSERT INTO products (id, name)
VALUES (2, 'Pencil');
-- price 会是 NULL

如果字段设置了 NOT NULL 但未提供值，会报错：

NOT NULL constraint failed: products.id

---

四、SELECT — 读取数据

4.1 基础查询

-- 查询所有数据（* 通配符 = 所有列）
SELECT * FROM products;

-- 查询特定列
SELECT name, price FROM products;

-- 查询多列（逗号分隔）
SELECT country, wheat_production FROM world_food;

4.2 条件查询（WHERE）

-- 精确匹配
SELECT * FROM products WHERE id = 1;

-- 获取特定值
SELECT rice_production FROM world_food
WHERE country = 'United States';

-- 比较操作符
SELECT country FROM world_food
WHERE wheat_production > 20;
-- 可用: =, <, >, <=, >=, <>（不等于）

4.3 模糊匹配（LIKE）

-- 查找以 'U' 开头的国家
SELECT country FROM world_food
WHERE country LIKE 'U' || '%';
-- || 是 SQL 的字符串拼接符
-- 等同于: WHERE country LIKE 'U%'

-- 查找以 'a' 结尾的国家
SELECT country FROM world_food
WHERE country LIKE '%' || 'a';
-- 结果: Australia, China, Ethiopia, India

-- 查找包含 'united' 的国家（忽略大小写）
SELECT * FROM countries
WHERE LOWER(country_name) LIKE '%' || 'united' || '%';

通配符规则：

模式	含义	示例
'A%'	以 A 开头	Alice, Angela
'%a'	以 a 结尾	China, India
'%an%'	包含 an	France, Japan

💡 || 在 SQL 中是 拼接（concatenate），不是 JavaScript 中的"或"。

---

五、UPDATE — 更新数据

5.1 更新记录

UPDATE products
SET price = 0.80
WHERE id = 2;

危险操作

-- ❌ 没有 WHERE → 更新所有记录！
UPDATE products SET price = 0.80;

-- ✅ 始终加 WHERE 条件
UPDATE products SET price = 0.80 WHERE id = 2;

5.2 添加列（ALTER TABLE）

ALTER TABLE products
ADD stock INT;

-- 然后更新数据
UPDATE products SET stock = 32 WHERE id = 1;
UPDATE products SET stock = 12 WHERE id = 2;

---

六、DELETE — 删除数据

DELETE FROM products WHERE id = 2;

危险操作

-- ❌ 删除所有数据
DELETE FROM products;

-- ✅ 始终指定条件
DELETE FROM products WHERE id = 2;

---

七、外键与关系

7.1 PRIMARY KEY vs FOREIGN KEY

概念	作用	位置
PRIMARY KEY	唯一标识本表中的记录	当前表
FOREIGN KEY	引用另一个表的 PRIMARY KEY	关联表

7.2 建立关系

-- 订单表：通过外键关联客户表和产品表
CREATE TABLE orders (
  id INT NOT NULL,
  order_number INT,
  customer_id INT,
  product_id INT,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

┌─ Customers ──┐     ┌─ Orders ─────────┐     ┌─ Products ──┐
│ id (PK)      │←────│ customer_id (FK)  │     │ id (PK)     │
│ name         │     │ product_id (FK)   │────→│ name        │
│ address      │     │ order_number      │     │ price       │
└──────────────┘     └───────────────────┘     └─────────────┘

7.3 INNER JOIN — 表连接

-- 将订单与客户信息连接
SELECT orders.order_number,
       customers.first_name,
       customers.last_name,
       customers.address
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id;

-- 将订单与产品信息连接
SELECT orders.order_number,
       products.name,
       products.price,
       products.stock
FROM orders
INNER JOIN products
ON orders.product_id = products.id;

结果： 从多个表中提取相关数据，组合成一张完整的视图。

---

八、三种关系类型

8.1 一对一（1:1）

一个学生 → 一条联系方式记录

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT
);

CREATE TABLE contact_details (
  id INT REFERENCES students(id) UNIQUE,
  tel VARCHAR(15),
  address TEXT
);

用途： 拆分大表，模块化数据（表太宽时拆成多个表）。

8.2 一对多（1:N） ⭐ 最常用

一个学生 → 多份作业提交

CREATE TABLE homework_submissions (
  id SERIAL PRIMARY KEY,
  mark INT,
  student_id INT REFERENCES students(id)
  -- student_id 可以重复！（多份作业指向同一学生）
);

其他示例：

• 一个用户 → 多篇博客文章
• 一个客户 → 多个订单
• 一个分类 → 多个产品

8.3 多对多（M:N）

多个学生 ↔ 多门课程（通过 中间表 连接）

CREATE TABLE student_courses (
  id SERIAL PRIMARY KEY,
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id)
);

Students ──── student_courses ──── Courses
  (M)          (中间表)              (N)

---

九、SQL 速查表

操作	SQL 语句
创建表	CREATE TABLE name (columns...);
插入	INSERT INTO table VALUES (...);
查询全部	SELECT * FROM table;
查询特定列	SELECT col1, col2 FROM table;
条件查询	SELECT * FROM table WHERE condition;
模糊匹配	WHERE col LIKE 'pattern%';
更新	UPDATE table SET col = val WHERE condition;
删除	DELETE FROM table WHERE condition;
添加列	ALTER TABLE table ADD column TYPE;
内连接	SELECT ... FROM A INNER JOIN B ON A.id = B.fk;
主键	PRIMARY KEY (id)
外键	FOREIGN KEY (fk) REFERENCES table(pk)

---

← 返回 Web 开发研究