一、多表设计
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 );