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      BMW    12L    80w    P001
C002      Benz   12L    100w   P001
C003      Benz   12L    100w   P001
C004      BMW    12L    80w    P002
C005      Benz   10L    60w    P002
C006      Adio   10L    70w    P003
C007      QQ     8L     6.5w   NULL

3)SQL代码实现
create table person2(
  id varchar(32) primary key,
  name varchar(30),
  sex char(1),
  age int
);
insert into person2 values('P1001','小花','0',25);
insert into person2 values('P1002','玉芬','0',24);
insert into person2 values('P1003','Tom','1',25);
insert into person2 values('P1004','Rose','0',23);


create table car(
  id varchar(32) primary key,
  name varchar(30),
  price numeric(10,2),
  pid varchar(32),
  constraint car_fk foreign key(pid) references person2(id)
);
insert into car values('C001','BMW',82.5, 'P1001');
insert into car values('C002','BMW',111.5, 'P1001');
insert into car values('C003','Benz',78.5, 'P1001');
insert into car values('C004','BMW',55.5, 'P1002');
insert into car values('C005','Audio',82.5, 'P1002');
insert into car values('C006','QQ',6.5, 'P1003');
insert into car(id,name,price) values('C007','ABC',6.6);



※关联(Join):把多个表的数据获取出来，在内存中生成一个新的表
Left Join(左关联):以左表为主(左表完整。右表中，跟左表有依赖关系的那些记录加进来，形成新表）
Right Join(右关联):以右表为主(右表完整。左表中，跟右表有依赖关系的那些记录加进来，形成新表）
Inner Join(内关联):两个表中存在依赖关系的那些记录抽取出来形成新表

Full Join(全关联)和 Outter Join(外关联)，MySQL不支持!
4)查询
//i. 查哪些人有哪些车
旧版: select person2.name, car.name, car.price
        from person2,car  where person2.id= car.pid;


新版(推荐):
select person2.name, car.name, car.price from 
    person2 inner join car on person2.id= car.pid;


//ii. 查询小花有哪些车
旧版:
select person2.name, car.name, car.price 
  from person2,car where person2.id= car.pid and person2.name ='小花';


新版(推荐):
select person2.name, car.name, car.price from
  person2 inner join car on person2.id = car.pid 
  where person2.name ='小花';


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

过渡1,在car表中查出哪些pid是有两辆及以上的车：
  SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;
过渡2,用in子句查询
SELECT NAME FROM person2 WHERE id IN('p1001'); //in()中写死了

合并:
SELECT NAME FROM person2 WHERE id IN(
  SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2
); 


//iv. 查询哪些人没有车
SELECT NAME FROM person2 WHERE id not IN(
  SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=1
); 


新版(用左关联):


SELECT person2.name FROM person2 
      LEFT JOIN car ON person2.id = car.pid
      WHERE car.pid IS NULL; 


新版(用右关联):  
SELECT person2.name FROM car 
      RIGHT JOIN person2 ON person2.id = car.pid

      WHERE car.pid IS NULL;