前端学习之后端java小白（三）-sql外键约束一对多

一、 外键约束：链接两个表之间的数据，保证数据一致性

<1>在已存在的表中添加字段

1. 基本语法

 ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 [约束];

2. 事例

-- 在已存在的表中添加字段使用 ALTER TABLE 语句的 ADD COLUMN
ALTER TABLE empTest ADD COLUMN user_id INT;
-- 添加邮箱字段
ALTER TABLE users ADD COLUMN email VARCHAR(100);
-- 添加年龄字段
ALTER TABLE users ADD COLUMN age INT;
-- 添加创建时间字段
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

3. 带约束字段添加

 -- 添加不能为空的字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;

-- 添加唯一约束的字段
ALTER TABLE users ADD COLUMN username VARCHAR(30) UNIQUE;

-- 添加带默认值的字段
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- 添加检查约束的字段
ALTER TABLE users ADD COLUMN salary DECIMAL(10,2) CHECK (salary > 0);

4. 指定字段位置

 -- 添加到表的最前面：
 ALTER TABLE users ADD COLUMN user_code VARCHAR(20) FIRST;
 -- 添加到指定字段后面
 ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) AFTER name;

5. 一次添加多个字段

ALTER TABLE users 
ADD COLUMN email VARCHAR(100),
ADD COLUMN phone VARCHAR(20),
ADD COLUMN address TEXT,
ADD COLUMN birthday DATE;

<2>已经存在的表可以通过 ALTER TABLE 语句添加外键约束

1. 基本语法

ALTER TABLE 子表名 
ADD CONSTRAINT 约束名 
FOREIGN KEY (列名) REFERENCES 父表名(列名);

2. 案例：假设我们有两个已经存在的表

-- 用户表（已存在）
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 订单表（已存在，但没有外键约束）
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,      -- 这列存在但没有外键约束
    product_name VARCHAR(100)
);
-- 添加外键约束：
-- 给 orders 表的 user_id 列添加外键约束
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

3. 完整操作示例

-- 1. 创建父表（用户表）
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 2. 创建子表（订单表），暂不添加外键约束
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product_name VARCHAR(100),
    amount DECIMAL(10,2)
);

-- 3. 插入一些测试数据
INSERT INTO users VALUES 
(1, '张三', 'zhangsan@email.com'),
(2, '李四', 'lisi@email.com');

INSERT INTO orders VALUES 
(101, 1, 'iPhone', 7999.00),
(102, 2, 'iPad', 4999.00);

-- 4. 现在添加外键约束
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

4. 多种添加方式

-- 1. 指定约束名（推荐）
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);
-- 2.不指定约束名（系统自动生成）
ALTER TABLE orders 
ADD FOREIGN KEY (user_id) REFERENCES users(id);
-- 3.添加多个外键约束
-- 假设订单表还有 category_id 列
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user_id 
    FOREIGN KEY (user_id) REFERENCES users(id),
ADD CONSTRAINT fk_orders_category_id 
    FOREIGN KEY (category_id) REFERENCES categories(id);

5. 验证外键约束是否添加成功

SHOW CREATE TABLE orders;

-- 或者查询约束信息
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'orders' AND CONSTRAINT_NAME LIKE 'fk_%';

二、其他小点

1. 创建表时设置自动增长

-- ✅ 推荐：ID自动增长
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,    -- 注意这里加了 AUTO_INCREMENT
    name VARCHAR(50),
    email VARCHAR(100)
);

插入数据时不需要指定ID

-- ✅ 不需要指定ID，系统自动分配
INSERT INTO users (name, email) VALUES 
('张三', 'zhangsan@email.com'),
('李四', 'lisi@email.com'),
('王五', 'wangwu@email.com');

查看结果

SELECT * FROM users;
/*
+----+------+---------------------+
| id | name | email               |
+----+------+---------------------+
|  1 | 张三 | zhangsan@email.com  |
|  2 | 李四 | lisi@email.com      |
|  3 | 王五 | wangwu@email.com    |
+----+------+---------------------+
*/

三、外键约束案例

1. 创建表结构

-- 父表：用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

-- 子表：订单表（包含外键约束）
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,                        -- 外键约束字段
    product_name VARCHAR(100),
    amount DECIMAL(10,2),
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- 外键约束
);

2. 插入数据

-- 先插入父表数据（用户）
INSERT INTO users (name, email, phone) VALUES 
(1, '张三', 'zhangsan@email.com', '13800138001'),
(2, '李四', 'lisi@email.com', '13800138002'),
(3, '王五', 'wangwu@email.com', '13800138003'),
(4, '赵六', 'zhaoliu@email.com', '13800138004');

-- 再插入子表数据（订单）
INSERT INTO orders (user_id, product_name, amount, order_date) VALUES 
(1, 'iPhone 15', 7999.00, '2024-01-10'),
(1, 'AirPods', 1299.00, '2024-01-15'),
(2, 'MacBook Pro', 12999.00, '2024-01-20'),
(2, 'iPad', 4999.00, '2024-01-25'),
(3, '小米手机', 2999.00, '2024-02-01'),
(1, 'Apple Watch', 2999.00, '2024-02-05');

3. 数据展示

users 表（父表）：

 SELECT * FROM users;

orders 表（子表）：

SELECT * FROM orders;

4. 关联查询展示

显示每个订单的用户信息：

SELECT 
    o.id AS 订单ID,
    u.name AS 用户姓名,
    u.email AS 邮箱,
    o.product_name AS 商品名称,
    o.amount AS 金额,
    o.order_date AS 订单日期
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.order_date;

5. 数据关系图

四、实践

-- 查看有哪些数据库
SHOW DATABASES;
-- 假设要使用名为 'TestData' 的数据库
USE TestData; 
-- 现在可以进行表操作了
CREATE TABLE empTest (
	id INT PRIMARY KEY, -- 员工id,主键且自增长
	ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
	join_date DATE NOT NULL, -- 入职日期， 非空
	salary DOUBLE(7,2) NOT NULL, -- 工资 非空
	bonus DOUBLE(7,2) DEFAULT 0, -- 奖金 非空 
	product_name VARCHAR(100) DEFAULT 0, -- 产品名称 非空
	amount DECIMAL(10,2)  -- 金额
);
-- 查看当前选择的数据库
SELECT DATABASE();
-- 查看当前数据库中的所有表
SHOW TABLES;
-- 不选择数据库，直接指定
CREATE TABLE myshop.users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 链接两个表之间的数据

-- 已经存在的表可以通过 ALTER TABLE 语句添加外键约束

-- 在已存在的表中添加字段使用 ALTER TABLE 语句的 ADD COLUMN
ALTER TABLE empTest ADD COLUMN user_id INT;
-- 给empTest表的userid添加外键约束
ALTER  TABLE empTest
ADD CONSTRAINT fk_empTest_user_id
FOREIGN KEY (user_id) REFERENCES users(id)

-- 修改已经存在表的字段约束
ALTER TABLE empTest MODIFY COLUMN id INT   AUTO_INCREMENT;
-- 查看表数据
DESCRIBE empTest;
-- 插入表数据
INSERT INTO empTest(ename,join_date, salary, bonus, product_name, amount) VALUES ('zhangsan', '2025-09-02', 80, 20, '产品', 60);

SELECT * FROM empTest;
SELECT * FROM users;

ALTER TABLE empTest DROP FOREIGN KEY fk_empTest_user_id;
-- 添加表数据
INSERT INTO users (user_id, username, email, password) VALUES (1,'外链','www.badu.com','456');

ALTER TABLE users ADD COLUMN user_id INT;

五、外键约束 vs 外连接的区别

1. 外键约束 (FOREIGN KEY)

作用：数据完整性约束，确保引用关系的正确性
目的：防止插入无效的关联数据

2. 外连接 (OUTER JOIN)

作用：查询操作，用于连接两个表获取数据
目的：展示数据关系，包括不匹配的记录

六、总结：FOREIGN KEY 的核心价值

• 数据一致性：防止无效的关联数据
• 业务规则执行：自动执行业务约束（如"订单必须属于某个用户"）
• 运维安全：防止误删除重要数据
• 查询可靠性：保证JOIN查询的结果是可信的
  简单总结：
• 没有外键 = 数据可能不一致，业务逻辑漏洞，财务对不上账
• 有外键 = 数据完整可靠，业务规则自动执行，系统更稳定