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

转自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;