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

一、多表设计

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.id
UNION -- all
SELECT * 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;
-- 从选课表中找到符合条件的学生id
SELECT 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 , studentcource 
	WHERE studentcource.score < 60 AND studentcource.student_id = student.id;

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

SELECT MAX(tmp.totalScore) FROM 
 ( SELECT student_id , SUM(score) AS totalScore 
 FROM 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 );