多表设计与多表查询指南

一、多表设计

在数据库设计中，多表设计是处理复杂数据关系的重要方式。根据数据关系可以分为一对一、一对多、多对多三种类型。

一对一

一对一关系通常用于用户与订单、商品分类与商品等场景。其实现方式是在主表中添加外键，关联到从表的主键。

代码示例

-- 创建db5数据库CREATE DATABASE db5;USE db5;-- 创建person表CREATE TABLE person(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20));INSERT INTO person VALUES (NULL, '张三'), (NULL, '李四');-- 创建card表CREATE TABLE card(    id INT PRIMARY KEY AUTO_INCREMENT,    number VARCHAR(50),    pid INT UNIQUE,    CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id));INSERT INTO card VALUES (NULL, '12345', 1), (NULL, '56789', 2);

一对多

一对多关系常见于用户与订单、商品分类与商品。多表中，多一方应建立外键关联到一的一方主键。

代码示例

-- 用户和订单示例CREATE TABLE USER(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20));INSERT INTO USER VALUES (NULL, '张三'), (NULL, '李四');CREATE TABLE orderlist(    id INT PRIMARY KEY AUTO_INCREMENT,    number VARCHAR(20),    uid INT,    CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id));INSERT INTO orderlist VALUES (NULL, 'hm001', 1), (NULL, 'hm002', 1), (NULL, 'hm003', 2), (NULL, 'hm004', 2);-- 商品分类和商品示例CREATE TABLE category(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(10));INSERT INTO category VALUES (NULL, '手机数码'), (NULL, '电脑办公');CREATE TABLE product(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(30),    cid INT,    CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id));INSERT INTO product VALUES (NULL, '华为P30', 1), (NULL, '小米note3', 1), (NULL, '联想电脑', 2), (NULL, '苹果电脑', 2);

多对多

多对多关系需要借助中间表。例如，学生和课程之间需要一个中间表来关联。

代码示例

-- 学生和课程示例CREATE TABLE student(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20));INSERT INTO student VALUES (NULL, '张三'), (NULL, '李四');CREATE TABLE course(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(10));INSERT INTO course VALUES (NULL, '语文'), (NULL, '数学');CREATE TABLE stu_course(    id INT PRIMARY KEY AUTO_INCREMENT,    sid INT,    cid INT,    CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),    CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id));INSERT INTO stu_course VALUES (NULL, 1, 1), (NULL, 1, 2), (NULL, 2, 1), (NULL, 2, 2);

二、多表查询

多表查询是处理多张表数据关联的重要技能。常见的查询类型包括笛卡尔积、内连接、外连接和子查询。

多表查询基础

多表查询的基本格式为：

SELECT 列名列表 FROM 表名列表 [WHERE 条件];

笛卡尔积查询

笛卡尔积查询会生成所有可能的组合数据，需通过WHERE条件清除无关数据。

示例

SELECT * FROM USER, orderlist;

内连接查询

内连接查询两张表的交集数据，适用于主外键关系。

显式内连接

SELECT * FROM USER INNER JOIN orderlist ON user.id = orderlist.uid;

隐式内连接

SELECT * FROM USER, orderlist WHERE user.id = orderlist.uid;

外连接查询

外连接查询保留一边的全部数据。

左外连接

SELECT * FROM USER LEFT OUTER JOIN orderlist ON user.id = orderlist.uid;

右外连接

SELECT * FROM USER RIGHT OUTER JOIN orderlist ON user.id = orderlist.uid;

子查询

子查询用于嵌套查询，常用于条件判断或数据汇总。

单行单列

SELECT NAME, age FROM USER WHERE age = (SELECT MAX(age) FROM USER);

多行单列

SELECT number, uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME = '张三' OR NAME = '李四');

多行多列

SELECT * FROM orderlist o, (SELECT * FROM orderlist WHERE id > 4) t WHERE o.id = t.id;

自关联查询

自关联查询用于同一表的多次查询，如员工与上级的关联。

示例

SELECT t1.name, t1.mgr, t2.name FROM employee t1 LEFT OUTER JOIN employee t2 ON t1.mgr = t2.id;

通过以上方法，可以高效处理多表数据的查询与分析需求。

转载地址：http://mybh.baihongyu.com/

你可能感兴趣的文章