一、多表设计

1、表设计分析介绍

在项目中，，需要对项目的业务模块进行分析，需要知道当前需要几张表来描述业务（功能）中的数据，这些表中的数据之间是否存在一定的关联性，需要考虑多张表之间是什么关系？

例如：一个JD账号可以有多个收货地址；

2、表与表之间的关系

多张表之间存在的关系：

一对一：（开发时几乎没有了）
一对多：
多对多：
自关联：

要将表之间这些关系维护清楚，必须使用表之间的约束条件。

约束条件：

单表：主键约束、唯一约束、非空约束
多表：外键约束（互联网企业表之间的外键约束不会进行设置，但是会有相关的列表示外键，防止级联删除操作）外键：在一张表中增加一列，数据引用的是另外一张表的主键值。

3、一对一的表设计

例如：

一夫一妻：一个丈夫对应一个妻子。
个人与身份证：每个人都唯一的一个身份证。

一对一建表的原则：

在任意一张表中增加一列，引用另外一张的主键即可。

建立外键列名的时候，一般是：表名_主键列名。

外键列必须添加唯一约束，因为它必须不可重复，且非空。（not null，unique）

-- 一对一CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT,p_name VARCHAR(50) NOT NULL,sex CHAR(1),birthday DATE,address VARCHAR(100));CREATE TABLE idcard(id INT PRIMARY KEY AUTO_INCREMENT,cardNb VARCHAR(18) NOT NULL UNIQUE,createTime DATE,-- 外键，引用的是person表中的主键-- 它的类型必须和person表中的主键的一致-- 一对一的外键需要加唯一约束p_id INT NOT NULL UNIQUE,FOREIGN KEY(p_id) REFERENCES person(id));

4、一对多的表设计

例如：

程序员和项目：一个程序员可能对应不同的项目任务。
老师和课程：一个老师对应多个课程。
用户和收货地址：一个用户可以添加多个收货地址。

一对多建表的原则：

在多的表中添加一列作为外键，引用一的表中的主键

-- 一对多的建表CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT,uname VARCHAR(50),upassword VARCHAR(32),registerTime DATETIME,ulevel INT);CREATE TABLE receAddr(id INT PRIMARY KEY AUTO_INCREMENT,receName VARCHAR(10),receAddr VARCHAR(100),receTel VARCHAR(12),u_id INT NOT NULL,FOREIGN KEY(u_id) REFERENCES users(id));

5、多对多的表设计

例如：

学生与课程：一个学生可以选修多门课程，每门课程可以被不同的学生选择。
订单和商品：订单：在京东、淘宝、外卖平台上一次可以提交一个订单，但是这个订单中可以包含若干个商品。
商品：每个类别的商品，可以被购买，但是在购买的过程中，每个类别的商品可以出现在多个订单中。

-- 多对多的表设计-- 订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,totalPrice DOUBLE,createTime DATETIME,payStatus INT,u_id INT NOT NULL,FOREIGN KEY(u_id) REFERENCES users(id));-- 商品表CREATE TABLE products(id INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(100),price DOUBLE,total INT);-- 订单明细表CREATE TABLE orders_products(o_id INT,p_id INT,number INT,PRIMARY KEY(o_id,p_id),FOREIGN KEY(o_id) REFERENCES orders(id),FOREIGN KEY(p_id) REFERENCES products(id));

6、多表设计练习

需求：学生成绩管理系统数据表

分析：

学生表：
课程表：
老师表：

表与表之间的关系：

一个学生可以选修多门课程，学生与课程是一对多。

一门课程可以被多个学生选修，课程与学生是一对多。

学生和课程之间是多对多关系。学生和课程表之间需要中间表（学生选课表）。

学生与老师没有直接关系，而是通过课程来达到间接的联系。

一个老师可以讲授多门课程，一门课程可以被多个老师讲授，多对多关系。

业务数据分析完之后，需要画出E-R（实体关系映射图）图。

矩形：实体（Java类、表）
菱形：实体与实体之间的关系
椭圆：实体的属性（表的列）

二、多表查询

CREATE TABLE teacher (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE);CREATE TABLE student (id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,city VARCHAR(40) NOT NULL,age INT) ;CREATE TABLE cource(id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL UNIQUE,teacher_id INT(11) NOT NULL,FOREIGN KEY (teacher_id) REFERENCES teacher (id));CREATE TABLE studentcource (student_id INT NOT NULL,cource_id INT NOT NULL,score DOUBLE NOT NULL,FOREIGN KEY (student_id) REFERENCES student (id),FOREIGN KEY (cource_id) REFERENCES cource (id));

根据项目的业务要求，可能需要的数据来自多张表的某些列，就需要进行多表的连接查询，或者子查询等操作。

常见的多表查询：

连接查询（笛卡尔积）
内连接查询
外连接查询

左外连接
右外连接
全外连接

子查询

1、连接查询

关联查询：将多张表关联起来直接查询，这种查询的结果也称为笛卡尔积（查询的结果是没有任何意义的，必须添加额外的where条件进行数据过滤）。

-- 创建商品表CREATE TABLE goods(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));-- 创建价格表CREATE TABLE price(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE);-- 给表中插入数据INSERT INTO goods VALUES(NULL , \'苹果\');INSERT INTO goods VALUES(NULL , \'橘子\');INSERT INTO goods VALUES(NULL , \'香蕉\');INSERT INTO price VALUES(NULL , 3.4);INSERT INTO price VALUES(NULL , 5.8);INSERT INTO price VALUES(NULL , NULL);INSERT INTO price VALUES(NULL , 9.8);

进行数据连接查询得到笛卡尔积，这个结果在真实项目中没有意义，需要进行条件过滤。

-- 使用关联查询，获取两张表中的数据：SELECT * FROM goods , price ;-- 针对连接查询需要添加where条件进行过滤SELECT * FROM goods , price  WHERE goods.id = price.id;

2、内连接查询

连接查询可以使用内连接代替

语法：select * from 表名 inner join 表名 on 条件;

-- 使用内连接代替SELECT * FROM goods INNER JOIN price ON goods.id = price.id;

3、外连接查询

外连接查询：左外、右外、全外连接。

3.1、左外连接查询

语法：select * from 表名 left Outer join 表名 on 条件

左外连接：使用左边的表去查询右边的表，不管右边表有没有结果，都会显示左边表的全部记录。

-- 外连接查询-- 查询出所有商品和对应的价格SELECT * FROM goods LEFT OUTER JOIN price ON goods.id = price.id;

3.2、右外连接查询

右外连接：使用右边的表去查询左边的表，不管左边的表有没有结果，都会显示右边表的全部记录。

语法：select * from 表名 right outer join 表名 on 条件。

-- 右外查询SELECT * FROM goods RIGHT OUTER JOIN price ON goods.id = price.id;

3.3、全外连接查询

全外连接：将左连接、右连接的结果和在一起。并去掉重复数据。

语法：select * from 表名 full outer join 表名 on 条件。

SELECT * FROM goods FULL OUTER JOIN price ON goods.id = price.id;

全连接mysql数据库不支持，可以使用union 关键字将左、右连接合并到一起：

-- 左外连接和右外连接通过union进行组合之后去重，得到就是全外连接的结果SELECT * FROM goods LEFT OUTER JOIN price ON goods.id = price.idUNION -- allSELECT * FROM goods RIGHT OUTER JOIN price ON goods.id = price.id;

union 关键字后面可以跟着all 关键字：

union：将多个查询的结果合并，会去除重复的记录。

union all ：将多个查询的结果合并，但不去除重复记录

4、子查询

子查询：查询嵌套。

/*1、查询所有成绩小于60分的同学姓名。分析：1、最终需要的学生信息，数据在student表2、查询学生的条件是成绩小于60，成绩在studentcource表中*/-- 查询出成绩小于60分的选课表中的数据SELECT * FROM studentcource WHERE score < 60;-- 从选课表中找到符合条件的学生idSELECT student_id FROM studentcource WHERE score < 60;SELECT * FROM student WHERE id IN( SELECT student_id FROM studentcource WHERE score < 60 );SELECT student.* FROM student , studentcourceWHERE studentcource.score < 60 AND studentcource.student_id = student.id;

/*2、查询获得最高分的学生学号。分析：学生的最高分，应该是这个学生所选的所有课程的成绩总和需要按照学号分组，计算成绩*/SELECT student_id , SUM(score) AS totalScoreFROM studentcource GROUP BY student_id HAVING totalScore = (SELECT MAX(tmp.totalScore) FROM( SELECT student_id , SUM(score) AS totalScoreFROM studentcource GROUP BY student_id ) AS tmp)SELECT MAX(tmp.totalScore) FROM( SELECT student_id , SUM(score) AS totalScoreFROM studentcource GROUP BY student_id ) AS tmp

-- 3、查询平均成绩大于70分的同学的学号和姓名SELECT student_id ,AVG(score) FROM studentcource GROUP BY student_Id HAVING AVG(score) > 70;SELECT * FROM student WHERE id IN( SELECT student_id FROM studentcource GROUP BY student_Id HAVING AVG(score) > 70 );

小白学习之Mysql-day03（多表查询）