MySQL 多表操作(一对一 一对多 多对多)
参考:https://blog.****.net/zuosixiaonengshou/article/details/53011452
一,1对1
1方建主表(id为主键字段), 一方或多方建外键字段(参考主表的主键id,加unique)
示例:一个女人(woman)对应一个丈夫(man)不能对应多个丈夫,一个丈夫也不能对应多个女人,妻子
创建man表(建表前创建一个test库)
mysql> create table man( id varchar(32) primary key , name varchar(30) );
创建woman表
mysql> create table woman( id varchar(32) primary key , name varchar(30), husband varchar(32) unique, constraint wm_fk foreign key(husband) references man(id) );
建表语句解析
unique # 设置约束 才是1对1否则为1对多 constraint wm_fk foreign key(husband) references man(id) #创建外键名为wm_fk 本表字段husband关联表man的id字段
查看建表语句
一一对应关系
插入数据
首先插入3个男人
mysql> insert into man values('1', '小明'); Query OK, 1 row affected (0.00 sec) mysql> insert into man values('2', '小聪'); Query OK, 1 row affected (0.01 sec) mysql> insert into man values('3', '老王'); Query OK, 1 row affected (0.00 sec)
插入女人并设置对应丈夫关系
mysql> insert into woman values('1', '小花', 2); Query OK, 1 row affected (0.00 sec) mysql> insert into woman values('2', '小静', 1); Query OK, 1 row affected (0.00 sec)
以下插入报错
husband可以为空代表为单身狗
mysql> insert into woman values('3', '小红', null);
查看数据
查询夫妻信息
mysql> SELECT man.name AS 丈夫, woman.name AS 妻子 FROM man INNER JOIN woman ON man.id=woman.husband;
当man.id和woman.huaband相同时查询数据即显示丈夫和妻子对应信息
查询语句解析
SELECT man.name AS 丈夫, woman.name AS 妻子 # 把表man的name字段以丈夫显示 woman的name字段以妻子显示 FROM man INNER JOIN woman # 内联查询查询两个表有值相同的字段 ON man.id=woman.husband; # 设置查询条件即woman的husband字段和man的id字段相同的则满足条件即夫妻的一对一关系
查询小花的丈夫是谁
mysql> SELECT man.name AS 丈夫, woman.name AS 妻子 FROM man INNER JOIN woman ON woman.name='小花' and man.id=woman.husband;
注意:需要加and同时满足条件man.id=woman.husband 否则会在表man查询出3跳数据
二,1对多
1方建主表(id为主键字段), 一方或多方建外键字段(参考主表的主键id,不加unique)
创建人员表
CREATE TABLE `person2` ( `id` varchar(32) primary key, `name` varchar(30), `sex` char(1), );
创建对应汽车表,外键为pid连接表person2的主键id 外键未加unique参数代表一个car可以对应多个person即多辆汽车可以对应1个人即一个人可以拥有多辆汽车
REATE TABLE `car` ( `id` varchar(32) PRIMARY KEY, `name` varchar(30), `price` decimal(10,2), `pid` varchar(32) , CONSTRAINT `car_fk` FOREIGN KEY (`pid`) REFERENCES `person2` (`id`) )
插入数据
首先插入人员数据
mysql> insert into person2 values('P01', 'Jack', 1); Query OK, 1 row affected (0.00 sec) mysql> insert into person2 values('P02', 'Tom', 1); Query OK, 1 row affected (0.00 sec) mysql> insert into person2 values('P03', 'Rose', 0); Query OK, 1 row affected (0.01 sec)
插入汽车数据
# C001 002 003属于人P01 mysql> insert into car values('C001', 'BMW', 30, 'P01'); Query OK, 1 row affected (0.01 sec) mysql> insert into car values('C002', 'BEnZ', 40, 'P01'); Query OK, 1 row affected (0.00 sec) mysql> insert into car values('C003', 'Audi', 40, 'P01'); Query OK, 1 row affected (0.00 sec) # C004属于人员P02 mysql> insert into car values('C004', 'QQ', 5.5, 'P02'); Query OK, 1 row affected (0.00 sec) # 也可以插入两辆汽车不属于任何人 mysql> insert into car values('C005', 'ABC', 10, null); Query OK, 1 row affected (0.00 sec) mysql> insert into car values('C006', 'BCD', 10, null); Query OK, 1 row affected (0.44 sec)
查询那些人有那些车
mysql> select person2.name, car.name from person2 inner join car on person2.id=car.pid; +------+------+ | name | name | +------+------+ | Jack | BMW | | Jack | BEnZ | | Jack | Audi | | Tom | QQ | +------+------+ 4 rows in set (0.00 sec)
查询Jack有哪些车
mysql> select person2.name, car.name from person2 inner join car on person2.id=car.pid and person2.name='Jack'; +------+------+ | name | name | +------+------+ | Jack | BMW | | Jack | BEnZ | | Jack | Audi | +------+------+ 3 rows in set (0.00 sec)
注意:这里条件也可以使用where
mysql> select person2.name, car.name from person2 inner join car on person2.id=car.pid where person2.name='Jack'; +------+------+ | name | name | +------+------+ | Jack | BMW | | Jack | BEnZ | | Jack | Audi | +------+------+ 3 rows in set (0.00 sec)
查询谁有两辆及两辆以上的汽车
mysql> SELECT person2.name, car.NAME,car.price FROM car INNER JOIN person2 ON car.pid=person2.id WHERE personn2.id IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2 ); +------+------+-------+ | name | NAME | price | +------+------+-------+ | Jack | BMW | 30.00 | | Jack | BEnZ | 40.00 | | Jack | Audi | 40.00 | +------+------+-------+ 3 rows in set (0.00 sec)
其中语句把两辆汽车以上的pid取到,然后在使用person2.id进行匹配
SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;
演示左关联
mysql> select * from person2 left join car on car.pid=person2.id; +-----+------+------+------+------+-------+------+ | id | name | sex | id | name | price | pid | +-----+------+------+------+------+-------+------+ | P01 | Jack | 1 | C001 | BMW | 30.00 | P01 | | P01 | Jack | 1 | C002 | BEnZ | 40.00 | P01 | | P01 | Jack | 1 | C003 | Audi | 40.00 | P01 | | P02 | Tom | 1 | C004 | QQ | 5.50 | P02 | | P03 | Rose | 0 | NULL | NULL | NULL | NULL | +-----+------+------+------+------+-------+------+ 5 rows in set (0.00 sec)
左关联得到左边表全部数据以及满足某一条件的右边表数据,如果不存在则填充null
由全表可知只需条件是car.id或NAME或price或pid为空即可查出谁没有车
查询那些人没有车
mysql> select person2.name from person2 left join car on car.pid=person2.id where car.name is null; +------+ | name | +------+ | Rose | +------+ 1 row in set (0.00 sec)
其实右关联跟左关联一样,只需要把左关联的表调换一下位置便成了右关联的结果,所以只要会了左关联,右关联也是一样的。
使用右连接查询那些车没有卖出去
mysql> select * from person2 right join car on car.pid=person2.id where person2.id is null; +------+------+------+------+------+-------+------+ | id | name | sex | id | name | price | pid | +------+------+------+------+------+-------+------+ | NULL | NULL | NULL | C005 | ABC | 10.00 | NULL | | NULL | NULL | NULL | C006 | BCD | 10.00 | NULL | +------+------+------+------+------+-------+------+ 2 rows in set (0.00 sec) mysql> select car.name from person2 right join car on car.pid=person2.id where person2.id is null; +------+ | name | +------+ | ABC | | BCD | +------+ 2 rows in set (0.00 sec)
三, 多对多
两个实体都建成独立的主表, 另外再单独建一个关系表(采用联合主键)
1、分别建议两个实体表(没有外键,但有自己的主键, 没有冗余信息)
//DROP TABLE stud; //学生表 CREATE TABLE stud( id VARCHAR(32) PRIMARY KEY, NAME VARCHAR(30) ); //课程表 CREATE TABLE ject( id VARCHAR(32) PRIMARY KEY, NAME VARCHAR(30) );
2,另外补建一个关系表
CREATE TABLE sj( studid VARCHAR(32) NOT NULL, jectid VARCHAR(32) ); //注意,要先建联合主键,再添加外键。顺序不能反了。 ALTER TABLE sj ADD CONSTRAINT sj_pk PRIMARY KEY(studid,jectid); ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studid) REFERENCES stud(id); ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectid) REFERENCES ject(id); //删除外键约束 //ALTER TABLE sj DROP FOREIGN KEY sj_fk1; //ALTER TABLE sj DROP FOREIGN KEY sj_fk2;
3,添加一些演示数据
//实体表1 INSERT INTO stud VALUES('S001','Jack'); INSERT INTO stud VALUES('S002','Rose'); INSERT INTO stud VALUES('S003','Tom'); //实体表2 INSERT INTO ject VALUES('J001','Java'); INSERT INTO ject VALUES('J002','Oracle'); INSERT INTO ject VALUES('J003','XML'); INSERT INTO ject VALUES('J004','JSP'); INSERT INTO ject VALUES('J005','Game'); //关系表 INSERT INTO sj VALUES('S001','J001'); INSERT INTO sj VALUES('S001','J003'); INSERT INTO sj VALUES('S001','J004'); INSERT INTO sj VALUES('S002','J002'); INSERT INTO sj VALUES('S002','J003'); INSERT INTO sj VALUES('S002','J004');
查询那些人选了那些课
SQL组织的1992标准,可用,但效率不高
mysql> SELECT stud.name, ject.NAME FROM stud,ject,sj WHERE stud.id=sj.studid AND ject.id=sj.jectid; +------+--------+ | name | NAME | +------+--------+ | Jack | Java | | Jack | XML | | Jack | JSP | | Rose | Oracle | | Rose | XML | | Rose | JSP | +------+--------+ 6 rows in set (0.00 sec)
//SQL组织的1996标准,效率高,推荐使用---关联
mysql> SELECT stud.name, ject.NAME FROM stud INNER JOIN sj ON stud.id=sj.studid INNER JOIN ject ON ject.id=sj.jectid; +------+--------+ | name | NAME | +------+--------+ | Jack | Java | | Jack | XML | | Jack | JSP | | Rose | Oracle | | Rose | XML | | Rose | JSP | +------+--------+ 6 rows in set (0.00 sec)
查询那些人没有选课
首先使左连接查询学生表和关系表对应表
mysql> select * from stud left join sj on stud.id=sj.studid; +------+------+--------+--------+ | id | name | studid | jectid | +------+------+--------+--------+ | S001 | Jack | S001 | J001 | | S001 | Jack | S001 | J003 | | S001 | Jack | S001 | J004 | | S002 | Rose | S002 | J002 | | S002 | Rose | S002 | J003 | | S002 | Rose | S002 | J004 | | S003 | Tom | NULL | NULL | +------+------+--------+--------+ 7 rows in set (0.00 sec)
当学生表对应的关系表stuid或者jectid为空则代表该学生未选择任何课程
即可知道学生Tom未选择任何课程
mysql> select * from stud left join sj on stud.id=sj.studid where sj.studid is NULL; +------+------+--------+--------+ | id | name | studid | jectid | +------+------+--------+--------+ | S003 | Tom | NULL | NULL | +------+------+--------+--------+ 1 row in set (0.00 sec)
同理,查询那些课程没有人选
mysql> select * from ject left join sj on ject.id=sj.jectid where sj.studid is null; +------+------+--------+--------+ | id | name | studid | jectid | +------+------+--------+--------+ | J005 | Game | NULL | NULL | +------+------+--------+--------+ 1 row in set (0.00 sec)
上一篇: 内存容量、最后地址、首地址 算术方法
下一篇: 数据库表设计中的一对一关系
推荐阅读
-
远程一对多(模式联接 3)
-
趣谈留言队列,搞清楚留言队列到底是什么!-说到消息队列,洪觉大概能猜到人们听到消息队列的反应,大致可以分为以下几类人。 第一类人,懵懵懂懂,刚上大学接触编程,还没用过消息队列,甚至还以为消息队列就是代码里面要新建一个List之类的;第二类人,听过消息队列,了解消息队列,但具体是什么还不是太明白,只知道一说到消息队列,脑海里马上出现了三组词,削峰、异步、解耦;第三类人,用过消息队列,对它有一定了解,但不知道为什么要这样设计,消息队列有什么样的前世今生,是如何演化到现在的模式的?**第四类人,已经对消息队列有了足够的了解,可以阅读本帖作为复习和温习。**你属于哪一类?无论你对消息队列了解多少,读完这篇文章后,我相信你都会有所收获。 什么是消息队列?我们为什么要使用消息队列?真的只是因为它看起来很勉强、很常用吗?当然不是,一项技术的出现往往是为了解决某种痛点,我们就从这个痛点出发,看看消息队列到底是为了解决什么问题而诞生的。 相信大家在工作之前,或者工作中接触单片机的次数会多一点,不管什么业务都一股脑塞进一个系统里,这种情况下接触消息队列的场景会比较少。但随着业务的增长,量上去了,单机系统就很难维护了,也扛不住并发量的增长,就需要把原来的单体应用拆分成多个服务。例如,牛奇网采用分布式架构,将原来的单体系统拆分成用户服务、题库服务、求职服务、论坛服务等,每个分布式节点都有一个集群,保证高可用性。 那虽然在这样的微服务架构下,如果某个核心业务并发量过大,系统就扛不住了。比如淘宝、淘票票、拼多多、京东等电商场景中的支付场景,你在某宝下单并支付后,调用支付服务,完成支付后,还需要更新订单的状态,这个时候就需要调用订单服务,那我们平时也下单,除了简单完成这些操作外,还会给你相应的积分;商家也会收到订单消息,并给您发送旺旺消息,确认订单无误;同时,也会给您发送消息,确认订单无误。确认订单无误;同时您还可以查看您的物流状态;还有系统为了给您推荐更适合您的商品,会根据您的订单做类似的推荐等等,我说的这些都是当我们下单后,肉眼可以感知到系统所做的动作。 **一个支付动作如果还需要调用那么多服务,等他们响应成功,最后再告诉用户你支付成功了,用户在系统中的整个体验会非常糟糕。**设想一下,假设请求服务+处理请求+响应总共需要 50ms,我们上面列出的场景:支付服务、订单服务、积分服务、商家服务、物流服务、推荐服务,总共需要 300ms。
-
Android 开发中 nodpi、xhdpi、hdpi、mdpi、ldpi 的概念 - 术语和概念 屏幕尺寸 屏幕的物理尺寸,基于屏幕的对角线长度(如 2.8 英寸、3.5 英寸)。 简而言之,安卓系统将所有屏幕尺寸简化为三大类:大、普通和小。 程序可以为这三种屏幕尺寸提供三种不同的布局选项,然后系统会以合适的方式将布局选项呈现到相应的屏幕上,这个过程不需要程序员用代码进行干预。 屏幕纵横比 屏幕的物理长度与物理宽度之比。程序只需使用系统提供的资源分类器 long(长)和 notlong(不长),就能为具有特定长宽比的屏幕提供配制材料。 分辨率 屏幕的像素总数。请注意,分辨率并不意味着长宽比,尽管在大多数情况下,分辨率表示为 "宽度 x 长度"。在安卓系统中,程序一般不直接处理分辨率。 密度 根据屏幕分辨率,沿屏幕宽度和长度排列的像素数量。 密度较低的屏幕在长度和宽度方向上的像素都相对较少,而密度较高的屏幕通常会在同一区域内排列很多甚至非常非常多的像素。屏幕的密度非常重要;例如,一个界面元素(如按钮)的长度和宽度以像素为单位,在低密度屏幕上会显得很大,但在高密度屏幕上就会显得很小。 独立于密度的像素(DIP)是指程序用来定义界面元素的抽象意义上的像素。它作为一个与实际密度无关的单位,帮助程序员构建布局方案(界面元素的宽度、高度和位置)。 与密度无关的像素在逻辑上与像素密度为 160 DPI 的屏幕上的像素大小相同,而 160 DPI 是安卓平台默认的显示设备。在运行时,平台会以目标屏幕的密度为基准,"透明 "地处理所有所需的 DIP 缩放操作。要将与密度无关的像素转换为屏幕像素,可以使用一个简单的公式:像素 = DIP * (密度 / 160)。例如,在 240 DPI 的屏幕上,1 个 DIP 等于 1.5 个物理像素。强烈建议使用 DIP 来定义程序界面的布局,因为这样可以确保用户界面在所有分辨率的屏幕上都能正常显示。 为了简化程序员在面对各种分辨率时的麻烦,也为了让各种分辨率的平台都能直接运行这些程序,Android 平台将所有屏幕以密度和分辨率作为分类方式,分别分为三类:- 三大尺寸:大、普通、小;- 三种不同密度:高(hdpi)、中(mdpi)和低(ldpi)。DPI 表示 "每英寸点数",即每英寸的像素数。如果需要,程序可以为不同的屏幕尺寸提供不同的资源(主要是布局),为不同的屏幕密度提供不同的资源(主要是位图)。除此之外,程序无需对屏幕尺寸或密度进行任何额外处理。执行时,平台会根据屏幕本身的尺寸和密度特性自动加载相应的资源,并将其从逻辑像素(DIP,用于定义界面布局)转换为屏幕上的物理像素。
-
信天翁:(七)一对多,多对一
-
MyBatis 中的关联关系 一对一 一对多 多对多
-
一对一[Java 的多表操作实现说明 III
-
c++ 学习笔记(套接字编程一对多通信)
-
Spring Boot 入门系列 (xxviii) JPA 实体映射关系、一对一、一对多、多对多关系映射!
-
C++(16) TCP 一对多通信
-
MySQL 表关系示例解析(一对一、一对多)