数据库表关系(一)

数据库表关系的基本知识点
一、一对一
@案例需求:丈夫和妻子的对用关系@
//法一:分别建立两个独立的数据库表
//表一
CREATE TABLE wife(
id INT PRIMARY KEY,
NAME VARCHAR(20),
sex CHAR(1)
);
//表二
//通过CONSTRAINT外键约束体现一对一的关系
//wid字段是外键,且用UNIQUE修饰,表示唯一
CREATE TABLE husband(
id INT PRIMARY KEY,
NAME VARCHAR(20),
sex CHAR(1),
wid INT UNIQUE,
CONSTRAINT husband_fk FOREIGN KEY(wid) REFERENCES wife(id)
);
//插入数据
INSERT INTO wife VALUES(001,’Rose’,’女’);
INSERT INTO wife VALUES(002,’Alice’,’女’);
INSERT INTO wife VALUES(003,’Ann’,’女’);
INSERT INTO wife VALUES(004,’Hebe’,’女’);

INSERT INTO husband VALUES(011,’Jack’,’男’,001);
INSERT INTO husband VALUES(012,’Tom’,’男’,002);
INSERT INTO husband VALUES(013,’Mike’,’男’,003);
INSERT INTO husband VALUES(014,’Allen’,’男’,004);

//查询:丈夫和妻子的对应关系
SELECT husband.name AS 丈夫,wife.name AS 妻子 FROM husband,wife WHERE husband.wid=wife.id;

//法二:合并一个独立的表
//表三
CREATE TABLE person1(
id INT PRIMARY KEY,
NAME VARCHAR(10),
sex CHAR(1),
wife INT,
husband INT
);
//插入数据
INSERT INTO person1 VALUES(1,’小花’,’女’, 0,3);
INSERT INTO person1 VALUES(2,’玉芬’,’女’, 0,4);
INSERT INTO person1 VALUES(3,’张三’,’男’, 1,0);
INSERT INTO person1 VALUES(4,’李四’,’男’, 2,0);
INSERT INTO person1 VALUES(5,’王五’,’男’, 0,0);

//分别建立视图体现一对一的关系
CREATE VIEW women AS SELECT * FROM person1 WHERE sex=’女’;
CREATE VIEW men AS SELECT * FROM person1 WHERE sex=’男’;

//查询:丈夫和妻子的对应关系
//1.基本方法
SELECT men.name AS 丈夫,women.name AS 妻子 FROM women,men WHERE women.husband=men.id;
//2.关联
SELECT men.name AS 丈夫,women.name AS 妻子 FROM women INNER JOIN men ON women.husband=men.id;
TODO:数据库单表之间关系的演示,是多表关系处理的基础

---

关联(Join):把多个表的数据获取出来,在内存中生成一个新的独立表
图解(没有任何具体需求):select * from person Inner(Left/Right) Join car on person.id=car.pid

内关联(Inner Join on):把两个表中存在依赖关系的记录抽取出来形成一个新表

左关联(Left Join on):左表为主,左表完整,把右表中和左表有依赖关系的记录加入进来,形成新表(在Left左边为左表)

右关联(Right Join on):右表为主,右表完整,把左表中和右表有依赖关系的记录加入进来,形成新表(在Join和on之间为右表)

TODO:全关联(Full Join on)和外关联(Outter Join on)—MySql不支持

二、一对多
@案例需求:人和车的对应关系,一人可有多辆车也可没有车,车只有一个车主也可以没有车主@

//法一:合并一个表(这种方式的数据库设计较差,数据太冗余)
编号 姓名 年龄 汽车编号 车型 排量 价格
P001 Jack 25 C001 BMW 12L 80w
P001 Jack 25 C002 Benz 12L 100w
P001 Jack 25 C003 Benz 12L 100w
P002 Tom 26 C004 BMW 12L 80w
P002 Tom 26 C005 Benz 10L 60w
P003 Rose 24 C006 Adio 10L 70w

//法二:
1:)一方单独建表
编号 姓名 年龄
P001 Jack 25
P002 Tom 26
P003 Rose 24
P004 Mike 24

2:)多方也单独建表,但要添加一个外键(车主)
汽车编号 价格 车主
C001 80w P001
C002 100w P001
C003 100w P001
C004 80w P002
C005 60w P002
C006 70w P003
C007 6.5w NULL

3:)代码实现
//表四
create table person2(
id varchar(32) primary key,
name varchar(30),
age int
);
//插入数据
insert into person2 values(‘P001’,’Jack’,21);
insert into person2 values(‘P002’,’Rose’,22);
insert into person2 values(‘P003’,’Tom’,23);
insert into person2 values(‘P004’,’Anna’,24);

//表五
create table car(
id varchar(32) primary key,
price numeric(10,2),
pid varchar(32),
constreint car_fk foreign key(pid) references person2(id)
);
//插入数据
insert into car values(‘C001’,80,’P001’);
insert into car values(‘C002’,100.3,’P001’);
insert into car values(‘C003’,100.4,’P001’);
insert into car values(‘C004’,80.5,’P002’);
insert into car values(‘C005’,60.4,’P002’);
insert into car values(‘C006’,70.8,’P003’);
insert into car values(‘C007’,80.5,null);

//查询
//i.哪些人有那些车
//1.基本方法
SELECT person2.id,person2.name,car.id,car.price FROM person2,car WHERE person2.id=car.pid;
//2.关联
SELECT person2.id,person2.name,car.id,car.price FROM person2 INNER JOIN car ON person2.id=car.pid;

//ii.Jack有哪些车
//1.基本方法
SELECT person2.id,person2.name,car.id,car.price FROM person2,car WHERE person2.name=’Jack’ AND person2.id=car.pid;
//2.关联
SELECT person2.id,person2.name,car.id,car.price FROM person2 INNER JOIN car ON person2.name=’Jack’ AND person2.id=car.pid;

//iii.查询哪些人有两辆及以上的车

//1.先查car表中按pid分组且总数大于等于2的pid
SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;

//2.再到person2表中查在IN子句中出现的pid对应的NAME
SELECT NAME FROM person2 WHERE id IN(SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2);

//iv.哪些人没有车
//1.基本方法
SELECT NAME FROM person2 WHERE id NOT IN(SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=1);
//2-1.左关联
SELECT person2.name,car.id FROM person2 LEFT JOIN car ON person2.id=car.pid WHERE car.pid IS NULL;
//2-2.右关联
SELECT person2.name,car.id FROM car RIGHT JOIN person2 ON person2.id = car.pid WHERE car.pid IS NULL;

TODO:掌握基础操作，对更多具体实例进行实践操作