-- 左连接
select s.*,c.name as c_name,c.room from my_class
as c left join my_class as s;
-- 左表为主表:最终记录数至少不少于左表已有的记录数
on s.c_id= c.id;

-- 右连接
select s.*,name as c_name,c.room from my_student
as s right join my_class as c;
-- 右表为主表:最终记录数至少不少于右表已有的记录数
on s.c_id= c.id;

--自然内连接
select * from my_student natural join my_class;

-- 修改班级表的name字段名为c_name
alter table my_class change name c_name varchar(20) not null;

-- 自然左外连接
select * from my_student natural left join my_class;

-- 外连接模拟自然外连接:using
select * from my_student left join my_class using(id);

-- 创建外键
create table my_foreign1(id int primary key auto_increment,
name varchar(20) not null comment'学生姓名',
c_id int comment'班级id' ,
-- 增加外键
foreign key(c_id) referrnces
my_class(id)
);

-- 创建表
create table my_foreign2(
id int primary key auto_increment,
name varchar(20) not null comment'学生姓名',
c_id int comment'班级id');

-- 增加外键
alter table my_foreign2 add
-- 指定外键名
constraint student_class_1
-- 指定外键字段
foreign  key(c_id)
-- 引用父表主键
references my_class(id);
-- 删除外键
alter table my_foreigh1 drop foreign key
my_foreign1_ibfk_1;

-- 插入数据：外键字段在父表中不存在
insert into my_foreign2 values(null,'郭富城',4);-- 没有4班级

insert into my_foreign2 values(null,'项羽',1);
insert into my_foreign2 values(null,'刘邦',2);
insert into my_foreign2 values(null,'韩信',2);

-- 更新父表记录
update my_class set id=4 where id=1;-- 失败：id=的记录已经被学生引用
update my_class set id=4 where id=3;-- 可以：没有引用

-- 插入数据
insert into my_foreign1 values(null,'马超',3);

-- 增加外键
alter table my_foreign1 add foreign key(c_id)
references my_class(id);

-- 创建外键：指定模式：删除置空，更新级联
create table my_foreign3(
id int primary key auto_increment,name varchar(20)
not null, c_id int,-- 增加外键
foreign key(c_id)
-- 引用表
references my_class(id)
-- 指定删除模式
on delete set null
-- 指定更新模式
on update cascade
);

-- 插入数据
insert into my_foreign3 values(null,'刘备',1),
(null,'曹操',1),
(null,'孙权',1),
(null,'诸葛亮',1),
(null,'周瑜',1);

-- 解除my_foreign2表的外键
alter table my_foreign2 drop foreign key
student_class_1;

-- 更新父表主键
update my_class set id=3 where id=1

-- 删除父表主键
delete from my_class where id=2;

-- 联合查询
select * from my_class union -- 默认去重
select * from my_class;

select * from my_class
union all -- 不去重
select name,number,id from my_student;

-- 需求：男生升序，女生降序（年龄）
(select * from my_student where sex='男' order
by age asc limit 9999999)union
(select * from my_student where sex='女' order
by age desc limit 9999999);

-- 标量子查询
select * from my_student where c_id= select
id fron mmy_class where c_name='python1809')-- id一定只有一个值(一行一列)

--  列子查询
select * from my_student where in (select id my_class);


-- any,some,
select * from my_student where c_id=any(
select id from my_class);
select * from my_student where c_id=some(
select id from my_class);
select * from my_student where c_id=all(
select id from my_class);

select * from my_student where c_id!=any(
select id from my_class);
select * from my_student where c_id!=any(
select id from my_class);
select * from my_student where c_id!=any(
select id from my_class);

select * from my_student where age=(select max(age)
 from my_student)
and
height=(select max(height) from my_student);

-- 行子查询
select * from my_student where --(age,height)称之为行元素
(age,height)=(select max(age),max(height) from my_student);

select * from my_student order by age desc,height desc limit 1;


-- 表子查询

insert into my_student values(null,'ba20190006','陈伟霆','男',52,165,1);
insert into my_student values(null,'ba20190006','陈伟','女',25,175,2);

select * from my_student group by c_id order by height desc;

select * from(select * from my_student order by height
desc) group by c_id -- 每个班选出第一个学生

-- exists子查询
select * from my_student where exists(select * from my_class where id=1);-- 是否成立
select * from my_student where exists(select * from my_class where id=2);-- 是否成立

-- 视图：单表+多表数据源
create view my_v1 as select * from my_student;

create view my_v2 as select * from my_student;

create view my_v3 as select * from my_student as s left
join my_class as c on s.c_id=c.id;-- id重复

create view my_v3 as s.*,c.c_name,c.room from my_student as s left
join my_class as c on s.c_id=c.id;-

--- 查看视图创建语句
show create view my_v3\G

-- 视图使用
select * from my_v1;
select * from my_v2;
select * from my_v3;

-- 修改视图
alter view my_v1 as select id,name,age,sex,height,c_id from my_student;

create view my_v4 as select * from my_student;

-- 删除数据
drop table my_v4;

-- 多表视图插入数据
insert into my_v3 values(null,'bc20190008','张三丰','男',150,180,1,'python1712','A204');

-- 将学生的学号字段设置成不允许为空
alter table my_student modify number char(10) not null unique;

-- 单表视图插入数据：视图不包含所有不允许为空的字段
insert into my_v1 values(null,'张三丰','男',150,180,1,);

-- 单表视图插入数据
insert into my_v2 values(2,'python0711','B201');

-- 多表视图删除数据
delete from my_v3 where id=1;

-- 单表视图删除数据
delete from my_v2 where id=1;

-- 多表视图更新数据
update my_v3 set c_id=3 where id=5;

-- 获取所有班级中最高的一个数据
create view my_v5 as select * from my_student order by height desc;

select * from my_v5 group by c_id;

-- 指定算法为临时表
create algorithm=tempable view my_v6 as select * from my_student
order by height desc;
select * from my_v6 group by c_id;