主外键关系
-- 副表
create table stu
(
s_no int(8) not null primary key auto_increment,
s_name varchar(12),
s_sex varchar(4),
s_score double(6,1)
);
-- 主表
create table tea
(
t_no int(8) not null primary key auto_increment,
t_name varchar(12),
t_sex varchar(4),
t_age varchar(4),
t_tel varchar(11)
);
-- 添加数据
insert into tea
(`t_name`,`t_sex`,`t_age`,`t_tel`)
values
("张老师","男","39","132***"),
("吴老师","男","39","132***"),
("周老师","男","39","132***"),
("王老师","男","39","132***"),
("向老师","男","39","132***");
insert into stu
(`s_name`,`s_sex`,`s_score`)
values
("小一","男","89"),
("小二","男","89"),
("小三","男","89"),
("小四","男","89"),
("小五","男","89");
-- 查询数据
select * from st stu,tea t where s.t_id = t.t_no;
-- 返回两张表的(数据相乘) 笛卡儿积
select * from stu s,tea t;
-- 左连接
select * from stu s left join tea t on s.t_id = t.t_no;
-- 右连接
select * from stu s right join tea t on s.t_id = t.t_no;
-- 内连接
select * from stu s inner join tea t on s.t_id = t.t_no;
-- 全连接
select * from stu s left join tea t on s.t_id = t.t_no
union all
select * from stu s right join tea t on s.t_id = t.t_no;
-- 去重复:union
select * from stu s left join tea t on s.t_id = t.t_no
-- 去重复
union
select * from stu s right join tea t on s.t_id = t.t_no;
-- 添加 unioue 该字段不能重复
alter table stu add unique(s_no);
alter table stu add unique(s_no,s_name);
-- 添加外键
alter table stu add foreign key (t_id) references tea(t_no);
-- 查询 表结构
desc stu;
-- 创建索引
create index score_index on stu(s_score);
-- 查询
select * from stu where s_score >80;
-- 动态添加字段
alter table tea add column t_address varchar(20);
外键的创建
查询结果