多表查询
外键约束
使用方法:
constraint 外键名 foregin key (被约束的字段) references 约束的表(约束的字段)
- 外键约束:保持数据的一致性、一致性实现一对多关系
- 外键一般关联到另一张表的主键
- 因为一个表只存一类信息,用外键来约束,可以减少数据冗余
例子:
create table department(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset utf8;
create table useinfo(
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)
)charset utf8;
constraint fk_user_depart只是给外键约束起了一个名字,可写可不写
唯一约束
unique key
例子:
mysql> create table st1(
-> id int unique key,
-> name varchar(20) );
--方法二
create table department2(
id int,
name varchar(20),
comment varchar(100),
unique(id)
);
有唯一约束的字段,不能再添加id相同的值,确保唯一
一对一
user:
id | name | age |
---|---|---|
1 | 张三 | 18 |
2 | 李四 | 27 |
3 | 王五 | 23 |
由于salary是比较敏感的字段,因此我们需要将此字段单独拆出来
pri:
id | salary | uid |
---|---|---|
1 | 5000 | 1 |
2 | 6000 | 2 |
3 | 3500 | 3 |
create table user(
id int auto_increment primary key,
name varchar(32) not null default '',
age int not null
)charset utf8;
create table pri(
id int auto_increment primary key,
salary int not null default 1,
constraint fk_pri_user foregin key (uid) references user(id),
unique(uid)
)charset utf8;
一对多
多对多
--男生女生相亲,建立一张男生表,女生表,再建立一张相亲约会表,把男生女生联系在一起
create table boy(
id int auto_increment primary key,
bname varchar(32) not null default ''
)charset utf8;
create table girl(
id int auto_increment primary key,
gname varchar(32) not null default ''
)charset utf8;
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;
多表联查
--一张部门表,一张员工信息表,员工部门id外键关联部门表的id
mysql> select * from department;
+----+--------+
| id | name |
+----+--------+
| 1 | 研发部 |
| 2 | 运维部 |
| 3 | 前台部 |
| 4 | 小卖部 |
+----+--------+
mysql> select * from userinfo;
+----+--------+-----------+
| id | name | depart_id |
+----+--------+-----------+
| 1 | zekai | 1 |
| 2 | xxx | 2 |
| 3 | zekai1 | 3 |
| 4 | zekai2 | 4 |
| 5 | zekai3 | 1 |
| 6 | zekai4 | 2 |
+----+--------+-----------+
内连接
只显示连接匹配的行,如果不加匹配条件就相当于笛卡尔积
select userinfo.id,userinfo.name,department.name,userinfo.depart_id from userinfo inner join department on userinfo.depart_id=department.id;
+----+--------+-----------+-----------+
| id | name | name | depart_id |
+----+--------+-----------+-----------+
| 1 | zekai | 研发部 | 1 |
| 2 | xxx | 运维部 | 2 |
| 3 | zekai1 | 前台部 | 3 |
| 4 | zekai2 | 小卖部 | 4 |
| 5 | zekai3 | 研发部 | 1 |
| 6 | zekai4 | 运维部 | 2 |
+----+--------+-----------+-----------+
左连接
left join ...on..
优先显示左表的记录
本质就是:在内连接的基础上增加左边有右边没有的结果
右连接
right join... on...
优先显示右表的记录
本质就是:在内连接的基础上增加右边有左边没有的结果