转载

【数据库实战 一】数据库表间关系

转自http://blog.csdn.net/lengjinghk/article/details/52140276

##一对一关系
定义:有两个表,在第一个表中的某一行只与第二个表中的一行相关,同时第二个表中的某一行,也只与第一个表中的一行相关,我们称这两个表为一对一关系。
例如:

CREATE TABLE IF NOT EXISTS person(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(10),
    sex CHAR(1),
    husband INT,
    wife INT 
);
INSERT INTO person VALUES(1,'小花','0',3,0);
INSERT INTO person VALUES(2,'小明','1',0,4);
INSERT INTO person VALUES(3,'张三','1',0,1);
INSERT INTO person VALUES(4,'小丽','0',2,0);
INSERT INTO person VALUES(5,'王五','1',0,0);

这里写图片描述

例如,上面的一张表,保存了人的相关信息,有男有女,要求查出所有的夫妻。
从表中可以看出,小花和张三是夫妻,小明和小丽是夫妻。通常为了查询方便,需要两个表,但实际项目中为了省空间,通常只建一个表,要实现一对一查询,可以建立两个视图(虚表)
有个小细节提一下,视图只是个逻辑概念,并不独立占用物理内存,它依附于数据表,对视图进行修改时相应的表数据也会盖改动

CREATE VIEW men AS (SELECT * FROM person WHERE sex='1');
CREATE VIEW women AS (SELECT * FROM person WHERE sex='0');

men视图

这里写图片描述

women视图

这里写图片描述

查询所有的夫妻配对

SELECT men.sname AS husband,women.sname AS wife FROM men ,women WHERE men.id=women.husband and women.id= men.wife;

SELECT men.sname AS husband,women.sname AS wife FROM men  INNER JOIN women on men.id = women.husband and  women.id = men.wife;

这里写图片描述

提示:一对一关系是比较少见的关系类型。但在某些情况下,还是会需要使用这种类型。
情况一:一个表包含了太多的数据列(需要拆分成子表)
情况二:将数据分离到不同的表,划分不同的安全级别。(设定不同的安全级别)
情况三:将常用数据列抽取出来组成一个表(常用的数据抽为一个表)
##一对多关系
定义:有多张表,第一个表中的行可以与第二个表中的一到多个行相关联,但是第二个表中的一行只能与第一个表中的一行相关联。
例如:
例如:一个人可以拥有多辆汽车,要求查询某个人拥有的所有车辆。
分析:这种情况其实也可以采用 一张表,但因为一个人可以拥有多辆汽车,如果采用一张表,会造成冗余信息过多。好的设计方式是,人和车辆分别单独建表,那么如何将两个表关联呢?有个巧妙的方法,在车辆的表中加个外键字段(人的编号)即可。 (思路小结:’建两个表,一’方不动,’多’方添加一个外键字段)
这里写图片描述

   //建立人员表
CREATE TABLE human(
    id VARCHAR(12) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1)
);
INSERT INTO human VALUES('H001','小王',27,'1');
INSERT INTO human VALUES('H002','小明',24,'1');
INSERT INTO human VALUES('H003','张慧',28,'0');
INSERT INTO human VALUES('H004','李小燕',35,'0');
INSERT INTO human VALUES('H005','王大拿',29,'1');
INSERT INTO human VALUES('H006','周强',36,'1');
    //建立车辆信息表
CREATE TABLE car(
    id VARCHAR(12) PRIMARY KEY,
    mark VARCHAR(24),
    price NUMERIC(6,2),
    hid VARCHAR(12),
    CONSTRAINT fk_human FOREIGN KEY(hid) REFERENCES human(id)
);
INSERT INTO car VALUES('C001','BMW',65.99,'H001');
INSERT INTO car VALUES('C002','BenZ',75.99,'H002');
INSERT INTO car VALUES('C003','Skoda',23.99,'H001');
INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');
INSERT INTO car VALUES('C005','Porsche',295.99,'H004');
INSERT INTO car VALUES('C006','Honda',24.99,'H005');
INSERT INTO car VALUES('C007','Toyota',27.99,'H006');
INSERT INTO car VALUES('C008','Kia',18.99,'H002');
INSERT INTO car VALUES('C009','Bentley',309.99,'H005');
select human.sname as person ,car.mark as paizi from car,human WHERE car.hid = human.id and human.id = "H001";

select human.sname as person ,car.mark as paizi from human INNER JOIN car WHERE car.hid = human.id and human.id = "H001";

这里写图片描述

这里的第一张表的一行可以与第二张表的多行相对应;第二张表的一行与第三张表的一行相对应。
提示:一对多关系是最常见的关系类型。

项目里的三张表,allcityaddress(城市信息)------aqidata(所有城市的全部空气质量信息)就是一对多的关系,这种关系是通过city_id维持的
##多对多关系
定义:有两个表,第一个表的一行可以与第二个表中的一到多个行相关联,同时,第二个表中的一行可以与第一个表中的一到多个行相关联。
例如:学生选课,一个学生可以选修多门课程,每门课程可供多个学生选择。
分析:这种方式可以按照类似一对多方式建表,但冗余信息太多,好的方式是实体和关系分离并单独建表,实体表为学生表和课程表,关系表为选修表,其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。

这里写图片描述

这里写图片描述
执行查询:(没有选课的学生信息和无学生选的课程信息)
普通方式:

SELECT * FROM student WHERE student.id NOT IN (SELECT sid FROM sc);
SELECT * FROM course WHERE course.id NOT IN (SELECT cid FROM sc);

关联方式:

SELECT student.* FROM student LEFT JOIN sc ON student.id=sc.sid LEFT JOIN course ON course.id = sc.cid WHERE course.sname IS NULL;

SELECT course.* FROM course LEFT JOIN sc ON course.id=sc.cid LEFT JOIN student ON student.id = sc.sid WHERE student.sname IS NULL;
文章最后发布于: 2017-09-20 20:45:44
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: Age of Ai 设计师: meimeiellie

分享到微信朋友圈

×

扫一扫,手机浏览