两表联查分组_分组查询，多表联查

使用方法：

constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

create table department(

id int auto_increment primary key,

name varchar(32) not null default ''

)charset utf8;

insert into department (name) values ('研发部');

insert into department (name) values ('运维部');

insert into department (name) values ('前台部');

insert into department (name) values ('小卖部');

create table userinfo (

id int auto_increment primary key,

name varchar(32) not null default '',

depart_id int not null default 1,

constraint fk_user_depart foreign key (depart_id) references department(id),

#constraint fk_user_depart foreign key (depart_id) references department(id),

#constraint fk_user_depart foreign key (depart_id) references department(id),

)charset utf8;

insert into userinfo (name, depart_id) values ('zekai', 1);

insert into userinfo (name, depart_id) values ('xxx', 2);

insert into userinfo (name, depart_id) values ('zekai1', 3);

insert into userinfo (name, depart_id) values ('zekai2', 4);

insert into userinfo (name, depart_id) values ('zekai3', 1);

insert into userinfo (name, depart_id) values ('zekai4', 2);

insert into userinfo (name, depart_id) values ('zekai4', 5);

多对多：

create table boy (

id int auto_increment primary key,

bname varchar(32) not null default ''

)charset utf8;

insert into boy (bname) values ('zhangsan'),('lisi'),('zhaoliu');

create table girl (

id int auto_increment primary key,

gname varchar(32) not null default ''

)charset utf8;

insert into girl (gname) values ('cuihua'),('gangdan'),('jianguo');

create table boy2girl (

id int auto_increment primary key,

bid int not null default 1,

gid int not null default 1,

constraint fk_boy2girl_boy foreign key (bid) references boy(id),

constraint fk_boy2girl_girl foreign key (gid) references girl(id)

)charset utf8;

insert into boy2girl (bid, gid) values (1,1),(1,2),(2,3),(3,3),(2,2);

select * from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;

mysql> select * from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;

+----+----------+------+------+------+------+---------+

| id | bname | id | bid | gid | id | gname |

+----+----------+------+------+------+------+---------+

| 1 | zhangsan | 1 | 1 | 1 | 1 | cuihua |

| 1 | zhangsan | 2 | 1 | 2 | 2 | gangdan |

| 2 | lisi | 5 | 2 | 2 | 2 | gangdan |

| 2 | lisi | 3 | 2 | 3 | 3 | jianguo |

| 3 | zhaoliu | 4 | 3 | 3 | 3 | jianguo |

+----+----------+------+------+------+------+---------+

5 rows in set (0.00 sec)

mysql> select bname, gname from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;

+----------+---------+

| bname | gname |

+----------+---------+

| zhangsan | cuihua |

| zhangsan | gangdan |

| lisi | gangdan |

| lisi | jianguo |

| zhaoliu | jianguo |

+----------+---------+

5 rows in set (0.00 sec)

mysql> select bname, gname from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid where bname='zhangsan';

+----------+---------+

| bname | gname |

+----------+---------+

| zhangsan | cuihua |

| zhangsan | gangdan |

+----------+---------+

2 rows in set (0.02 sec)

一对一：

user :

id name age

1 zekai 18

2 zhangsan 23

3 xxxx 19

由于salary是比较敏感的字段，因此我们需要将此字段单独拆出来， 变成一张独立的表

private：

id salary uid (外键 + unique)

1 5000 1

2 6000 2

3 3000 3

create table user (

id int auto_increment primary key,

name varchar(32) not null default ''

)charset=utf8;

insert into user (name) values ('zhangsan'),('zekai'),('kkk');

create table priv(

id int auto_increment primary key,

salary int not null default 0,

uid int not null default 1,

constraint fk_priv_user foreign key (uid) references user(id),

unique(uid)

)charset=utf8;

insert into priv (salary, uid) values (2000, 1);

insert into priv (salary, uid) values (2800, 2);

insert into priv (salary, uid) values (3000, 3);

insert into priv (salary, uid) values (6000, 1);

ERROR 1062 (23000): Duplicate entry '1' for key 'uid'