主键
1个字段为主键
create table person(
nid int not null auto_increment primary key,
name varchar(40) not null,
email varchar(40) not null,
partment_nid int not null
);
create table person(
nid int not null auto_increment primary key,
name varchar(40) not null,
email varchar(40) not null,
partment_nid int not null,
PRIMARY KEY (nid)
);
2个字段同时为主键
- 即nid&name不能同时相同
nid name
1 maotai
1 maotai # 第一条和第二条重复被限制
2 maotai
create table person(
nid int not null auto_increment,
name varchar(40) not null,
email varchar(40) not null,
partment_nid int not null,
PRIMARY KEY (nid, name)
);
- 这种是没办法被识别的, 也体现了PRIMARY KEY ()写法的用途.
create table person(
nid int not null auto_increment primary key,
name varchar(40) not null primary key,
email varchar(40) not null,
partment_nid int not null,
);
外键
- partment
1,重复太多
2,太占空间
person:
nid name email partment
1 maotai 1@qq.com 毛台科技创新公司-开发组
2 maotai 1@qq.com 毛台科技创新公司-技术部
3 maotai 1@qq.com 毛台科技创新公司-测试组
4 maotai 1@qq.com 毛台科技创新公司-测试组
- partment:(字典表)
nid part
1 毛台科技创新公司-开发组
2 毛台科技创新公司-测试组
- person:
nid name email partment # 外键: 关联另一张表的主键
1 maotai 1@qq.com 1
2 maotai 1@qq.com 1
3 maotai 1@qq.com 2
4 maotai 1@qq.com 2
- 默认2张表是没关系的,需要使用外键关联
create table partment(
nid int not null auto_increment primary key,
part varchar(40) not null
);
create table person(
nid int not null auto_increment,
name varchar(40) not null,
email varchar(40) not null,
partment_nid int not null,
PRIMARY KEY (nid),
CONSTRAINT fk_person_to_partment FOREIGN key (partment_nid) REFERENCES partment(nid)
);
insert into partment (part) values ('CEO');
insert into partment (part) values ('COO');
insert into partment (part) values ('CFO');
insert into person(name,email,partment_nid)values('maotai','1@qq.com',1);
insert into person(name,email,partment_nid)values('maotai2','2@qq.com',2);
insert into person(name,email,partment_nid)values('maotai3','3@qq.com',2); # partment_nid值必须是partment表里有的.否则报错
alter table person drop foreign key fk_person_to_partment;
- 先删除外键, 然后添加就不报错了
insert into person(name,email,partment_nid)values('maotai2','2@qq.com',4);
- 删除外键
alter table person drop foreign key fk_person_to_partment;
- 新建外键
alter table person add constraint fk_person_to_partment foreign key(partment_nid) references partment(nid);
连表查询(外键)
通过select方式连接
- 找到CEO部门的所有人员的信息
# 先找所有人name
select name from person;
# 找出id为1的name
select name from person where partment_nid in (1); # 过滤id为1的数据
# 找出ceo的id
select nid from partment where part='CEO'; # 得到CEO的id
# 找出ceo的名字
select name from person where partment_nid in (select nid from partment where part='CEO';);
通过join方式连接
select * from person left join partment on person.partment_nid = partment.nid;
select * from person left join partment on person.partment_nid = partment.nid where partment.part = 'CEO';
select person.name from person left join partment on person.partment_nid = partment.nid where partment.part = 'CEO';
join的3种方式
left join(最常用)
A left join B on 条件
以A为主
将A中所有字段罗列
B,则显示与A对应的数据
select * from person left join partment on person.partment_nid = partment.nid;
B left join A on 条件
以B为主
select * from partment left join person on person.partment_nid = partment.nid;
right join
- 以下效果一样: A left join B == B right join A
select * from partment left join person on person.partment_nid = partment.nid;
select * from person right join partment on person.partment_nid = partment.nid;
inner join(自动去掉脏数据)
- 自动忽略为建立关系的数据
select * from person inner join partment on person.partment_nid = partment.nid;
create table color(
nid int not null auto_increment primary key,
color varchar(30) not null
);
insert into color(color)values('pink');
insert into color(color)values('red');
insert into color(color)values('yellow');
alter table person add color_nid int(4) after partment_nid;
alter table person add CONSTRAINT fk_person_to_color FOREIGN key (color_nid) REFERENCES color(nid);
join多个表
- 是CEO且喜欢pink色的
select * from person
left join partment on person.partment_nid = partment.nid
LEFT JOIN color on person.color_nid = color.nid
select * from person
left join partment on person.partment_nid = partment.nid
LEFT JOIN color on person.color_nid = color.nid
where partment.part = 'CEO' and color.color='pink';
- 自定义列明
select
person.name as pname,
partment.part as cp,
color.color as cl
from person
left join partment on person.partment_nid = partment.nid
LEFT JOIN color on person.color_nid = color.nid
where partment.part = 'CEO' and color.color='pink';
- man
nid name 配偶
1 aaron 1
2 boby 2
3 lanny 1
- woman
nid name 配偶
1 emy 1
2 sara 1
3 zara 1
多对多关系
- 设计第三张关系表,为了逃避不确定因素
- man
nid name
1 aaron
2 boby
3 lanny
- woman
nid name
1 emy
2 sara
3 zara
- 关系表
男id 女id
1 1
1 2
2 2
3 3
- 本质上是两个外键
create table man(
nid int auto_increment not null primary key,
name varchar(40) not null
);
create table woman(
nid int auto_increment not null primary key,
name varchar(40) not null
);
create table relationship(
nid int auto_increment not null primary key,
man_nid int not null,
woman_nid int not null,
constraint fk_relationship_to_mannid FOREIGN key (man_nid) REFERENCES man(nid),
constraint fk_relationship_to_womannid FOREIGN key (woman_nid) REFERENCES woman(nid)
);
insert into man(name)values('aaron');
insert into woman(name)values('aaron');
insert into relationship(man_nid,woman_nid)values(1,1);
select nid from man where name='aaron';
select man_nid from relationship where man_nid = (select nid from man where name='aaron');
select * from relationship
LEFT JOIN man on relationship.man_nid = man.nid
LEFT JOIN woman on relationship.woman_nid = woman.nid;
select * from relationship
LEFT JOIN man on relationship.man_nid = man.nid
LEFT JOIN woman on relationship.woman_nid = woman.nid
where man.name = 'aaron';
