mysql数据库 学习（三）主外键创建

主外键关系

-- 副表
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);

外键的创建

查询结果