数据库设计(表与表之间的3种关系)

来源:互联网 发布:怎么找淘宝差评师 编辑:程序博客网 时间:2024/03/29 18:09

表与表之间一般存在三种关系,即一对一,一对多,多对多关系。 
下面分别就三种关系讲解数据库相关设计的思路和思考过程;

(1)一对一关系 
例如,下面的一张表,保存了人的相关信息,有男有女,要求查处所有的夫妻。 
sql代码:

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);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

sql代码对应的person表: 
这里写图片描述 
从表中可以看出,小花和张三是夫妻,小明和小丽是夫妻。通常为了查询方便,需要两个表,但实际项目中为了省空间,通常只建一个表,要实现一对一查询,可以建立两个视图(虚表) 
* 有个小细节提一下,视图只是个逻辑概念,并不独立占用物理内存,它依附于数据表,对视图进行修改时相应的表数据也会盖改动*

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

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;

二、一对多关系 
例如:一个人可以拥有多辆汽车,要求查询某个人拥有的所有车辆。 
分析:这种情况其实也可以采用 一张表,但因为一个人可以拥有多辆汽车,如果采用一张表,会造成冗余信息过多。好的设计方式是,人和车辆分别单独建表,那么如何将两个表关联呢?有个巧妙的方法,在车辆的表中加个外键字段(人的编号)即可。 
* (思路小结:’建两个表,一’方不动,’多’方添加一个外键字段)*

sql代码:

    //建立人员表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');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

sql代码对应的人员表: 
这里写图片描述 
sql代码对应的车辆信息表: 
这里写图片描述 
执行查询:SELECT human.sname AS 车主,car.mark AS 车辆 FROM human,car WHERE human.id = car.hid;

`SELECT human.sname AS 车主,car.mark AS 车辆 FROM human INNER JOIN car WHERE human.id = car.hid;

三、多对多关系
例如:学生选课,一个学生可以选修多门课程,每门课程可供多个学生选择。 
分析:这种方式可以按照类似一对多方式建表,但冗余信息太多,好的方式是实体和关系分离并单独建表,实体表为学生表和课程表,关系表为选修表,其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。

sql代码:

    //建立学生表CREATE TABLE student(    id VARCHAR(10) PRIMARY KEY,    sname VARCHAR(12),    age INT,    sex CHAR(1),    class VARCHAR(6));INSERT INTO student VALUES('p0001','王军',20,1,'c101');INSERT INTO student VALUES('p0002','张宇',21,1,'c101');INSERT INTO student VALUES('p0003','刘飞',22,1,'c102');INSERT INTO student VALUES('p0004','赵燕',18,0,'c103');INSERT INTO student VALUES('p0005','曾婷',19,0,'c103');INSERT INTO student VALUES('p0006','周慧',21,0,'c104');INSERT INTO student VALUES('p0007','小红',23,0,'c104');INSERT INTO student VALUES('p0008','杨晓',18,0,'c104');INSERT INTO student VALUES('p0009','李杰',20,1,'c105');INSERT INTO student VALUES('p0010','张良',22,1,'c105');    //建立课程表CREATE TABLE course(    id VARCHAR(10) PRIMARY KEY,    sname VARCHAR(12),    credit NUMERIC(2,1),    teacher VARCHAR(12));INSERT INTO course VALUES('C001','Java',3.5,'李老师');INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');    //建立选修表CREATE TABLE sc(    sid VARCHAR(10),    cid VARCHAR(10));ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);ALTER TABLE sc ADD CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id);ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);INSERT INTO sc VALUES('S0001','C001');INSERT INTO sc VALUES('S0001','C002');INSERT INTO sc VALUES('S0001','C003');INSERT INTO sc VALUES('S0002','C001');INSERT INTO sc VALUES('S0002','C004');INSERT INTO sc VALUES('S0003','C002');INSERT INTO sc VALUES('S0003','C005');INSERT INTO sc VALUES('S0004','C003');INSERT INTO sc VALUES('S0005','C001');INSERT INTO sc VALUES('S0006','C004');INSERT INTO sc VALUES('S0007','C002');INSERT INTO sc VALUES('S0008','C003');INSERT INTO sc VALUES('S0009','C001');INSERT INTO sc VALUES('S0009','C005');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57

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

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

关联方式:

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;
  • 1
  • 2
原创粉丝点击