#1.表与表之间的关系
#一对一
#1.1.一对一主键关联关系
#【主键关联主键,主键是主键也是外键】
#创建表
create table t_user(
u_id int primary key auto_increment,
u_name varchar(20),
u_age int
);
create table t_card(
c_id int primary key auto_increment,
c_nameber varchar(18),
c_arg varchar(10),
foreign key t_card(c_id)references t_user(u_id)
);
#1.2一对一的外键关联【专门创建外键列,外键列关联主键列】
#被用来充当外键的列需要使用唯一约束修饰【unique】
#如果没有【unique】就会变成一对多的关系
#所以一对一的外键关联关系实际上是一对多的特殊情况
create table t_lock(
l_id int primary key auto_increment,
l_name varchar(20)
);
create table t_key(
k_id int primary key auto_increment,
k_name varchar(20),
lock_id int unique,
foreign key t_key(lock_id)references t_lock(l_id)
);
#一对多
#外键的建立要放在多方表中维护
create table t_class(
c_id int primary key auto_increment,
c_name varchar(20)
);
create table t_student(
s_id int primary key auto_increment,
s_name varchar(20),
s_age int,
class_id int,
foreign key t_student(class_id)references t_class(c_id)
);
#测试数据的添加
insert into t_class values(null,'java');
insert into t_class values(null,'苹果');
insert into t_class values(null,'橘子');
insert into t_student values(null,'zhangsan',23,1);
insert into t_student values(null,'lisi',20,1);
insert into t_student values(null,'wangwu',24,2);
insert into t_student values(null,'liuli',24,2);
insert into t_student values(null,'tom',25,3);
insert into t_student values(null,'wuxin',20,3);
#多对多关系
create table t_role(
r_id int primary key auto_increment,
r_name varchar(10),
r_desc varchar(30)
);
create table t_group(
g_id int primary key auto_increment,
g_name varchar(20),
g_desc varchar(30)
);
create table t_rolegrop(
z_id int primary key auto_increment,
role_id int,
grop_id int
);
alter table t_rolegrop add constraint fkl foreign key(role_id) references t_role(r_id);
alter table t_rolegrop add constraint fk2 foreign key(grop_id) references t_group(g_id);
#测试数据
insert into t_role values(null,'java程序员','负责开发java程序');
insert into t_role values(null,'测试员','负责测试程序');
insert into t_group values(null,'CRM组','负责开发CRM系统');
insert into t_group values(null,'ERP组','负责开发ERP系统');
insert into t_rolegrop values(null,1,1);
insert into t_rolegrop values(null,1,2);
insert into t_rolegrop values(null,2,1);
insert into t_rolegrop values(null,2,2);
#2.联合查询
#内连接查询【inner join】
#左连接查询【left join】左外连接查询【left outer join】
#右连接查询【right join】左外连接查询【right outer join】
#格式
/*
select [col1,col2...coln来自多张表的列名【最好使用别名】]
from 表1
inner join/
left outer join/left join/
right outer join/right join
表2
on table1.co1=table2.co1
where 查询条件
*/
#查询Java班所有学生信息
#内连接查询
select 学生表.s_name as 学生姓名,学生表.s_age as 学生年龄,班级表.c_name as 班级名称
from t_student as 学生表
inner join
t_class as 班级表
on 学生表.class_id = 班级表.c_id
where 班级表.c_name='java';
#左连接查询
select 学生表.s_name as 学生姓名,学生表.s_age as 学生年龄,班级表.c_name as 班级名称
from t_student as 学生表
left outer join
t_class as 班级表
on 学生表.class_id = 班级表.c_id
where 班级表.c_name='java';
#右连接查询
select 学生表.s_name as 学生姓名,学生表.s_age as 学生年龄,班级表.c_name as 班级名称
from t_student as 学生表
right outer join
t_class as 班级表
on 学生表.class_id = 班级表.c_id
where 班级表.c_name='java';
#查询姓名是tom的所在班级
select 学生表.s_name as 学生姓名,学生表.s_age as 学生年龄,班级表.c_name as 班级名称
from t_student as 学生表
right outer join
t_class as 班级表
on 学生表.class_id = 班级表.c_id
where 学生表.s_name='tom';
/*
内连接与左连接和右链接的区别
–内连接:显示左边右边共有的
–左连接:左边有的显示,右边没有的为null
–有链接:右边有的显示,左边没有的为null
*/