数据库设计（一对一，一对多，多对多）关联查询

※表与表之间的关系 1、一对一 需要两个表。当然做项目时为了省空间，通常只建一个表，如果要实现一对一的查询，可以建两个视图。示例如下： 1)建物理表，初始化数据 CREATE TABLE person( id INT, NAME VARCHAR(10), sex CHAR(1), wife INT, husband INT ); INSERT INTO person VALUES(1,'小花','0',0,3); INSERT INTO person VALUES(2,'玉芬','0',0,4); INSERT INTO person VALUES(3,'张三','1',1,0); INSERT INTO person VALUES(4,'李四','1',2,0); INSERT INTO person VALUES(5,'王五','1',0,0); 2) 建立两个视图 create view women as select * from person where sex='0'; create view men as select * from person where sex='1'; 3) 查询夫妻信息 //旧版本 SELECT women.name AS 妻子, men.name AS 丈夫 FROM women, men WHERE women.husband = men.id; //采用内联接--98以后的新方式--效率更高 SELECT women.name AS 妻子, men.name AS 丈夫 FROM women INNER JOIN men ON women.husband = men.id; <pre name="code" class="sql">3、多对多 数据库设计分析 ※案例：一个人可以选择多门课程，一门课程又可以被很多人选择。 方案一(差的设计)： 1)学生表 编号 姓名 性别 年龄 电话 ... P001 Jack 男 25 P002 Tom 男 25 P003 Rose 女 25 -------------------------- 2)课程表 编号 名称 教材 学分... 学生 S001 Java ... ...... P001 S001 Java ... ...... P002 S001 Java ... ...... ... S002 数据库 ... ...... P001 S002 数据库 ... ...... P002 ...... 方案二(好的设计:两个实体表+一个关系表)： 1)学生表(独立)---实体 编号 姓名 性别 年龄 电话 ... P001 Jack 男 25 P002 Tom 男 25 P003 Rose 女 25 2)课程表(独立)---实体 编号 名称 教材 学分... S001 Java ... ...... S002 数据库 ... ...... S003 XML ... ...... 3)选课表(专为体现多对多的关系而新增的表)--关系 课程编号 学生编号 S001 P001 S001 P002 ... S002 P002 S002 P003 ... S003 P001 ... 外键 外键 | | |━━━━| | 联合主键 4) 代码实现 CREATE TABLE stud( id varchar(32) primary key, NAME VARCHAR(30), age INT ); CREATE TABLE ject( id varchar(32) primary key, NAME VARCHAR(30) ); CREATE TABLE sj( studid varchar(32), subjectid VARCHAR(32) ); //单独添加约束(注意顺序: 要先添加联合主键，再添加外键) //创建联合主键 ALTER TABLE sj ADD CONSTRAINT pk_sj PRIMARY KEY(studid,subjectid); //创建两个外键 alter table sj add constraint fk_stud foreign key(studid) references stud(id); alter table sj add constraint fk_subj foreign key(subjectid) references ject(id); //删除外键---先添加外键再添加联合主键时，不行。删除外键，重来 ALTER TABLE sj DROP FOREIGN KEY fk_stud; ALTER TABLE sj DROP FOREIGN KEY fk_subj; INSERT INTO stud VALUES('P001','小花',25); INSERT INTO stud VALUES('P002','Jack',23); INSERT INTO stud VALUES('P003','Tom',24); INSERT INTO stud VALUES('P004','张三',24); INSERT INTO stud VALUES('P005','赵子龙',26); INSERT INTO ject VALUES('S001','Java'); INSERT INTO ject VALUES('S002','JavaEE'); INSERT INTO ject VALUES('S003','XML'); INSERT INTO ject VALUES('S004','数据库'); INSERT INTO ject VALUES('S005','JQuery'); INSERT INTO sj VALUES('P001','S001'); INSERT INTO sj VALUES('P001','S003'); INSERT INTO sj VALUES('P002','S001'); INSERT INTO sj VALUES('P002','S002'); INSERT INTO sj VALUES('P002','S003'); INSERT INTO sj VALUES('P003','S001'); INSERT INTO sj VALUES('P004','S002'); INSERT INTO sj VALUES('P004','S003'); 关联查询： //SQL加强 关联(连接)：左关联(left join) 右关联(right join) 内关联(inner join) mysql不支持: 全关联(full join) 外关联(outter join) //关联，可理解成把几个合成一个新的表，然后在新表中进行查询 //1查询哪些人选了哪些课 //92标准 select stud.name,ject.name from stud,ject,sj where stud.id=sj.studid and ject.id = sj.subjectid; //96标准 select stud.name,ject.name from stud inner join sj on stud.id=sj.studid inner join ject on ject.id = sj.subjectid; //2查询哪些人没有选课 //92标准 SELECT stud.name FROM stud WHERE stud.id NOT IN ( SELECT studid FROM sj ); //96标准--左关联 SELECT stud.name FROM stud LEFT JOIN sj ON stud.id=sj.studid LEFT JOIN ject ON ject.id = sj.subjectid WHERE ject.NAME IS NULL; //3查询哪些课程没人选 //96标准--左关联 SELECT ject.name FROM ject LEFT JOIN sj ON ject.id=sj.subjectid LEFT JOIN stud ON stud.id = sj.studid WHERE stud.NAME IS NULL; //96标准--右关联 SELECT ject.name,stud.name FROM stud RIGHT JOIN sj ON stud.id=sj.studid RIGHT JOIN ject ON ject.id = sj.subjectid WHERE stud.NAME IS NULL; ※演示自动增长列与字段值唯一性约束 CREATE TABLE aa( id INT AUTO_INCREMENT PRIMARY KEY, nm VARCHAR(32) UNIQUE ); INSERT INTO aa(nm) VALUES('uuu');