-- 聯(lián)合查詢
select * from my_class
union -- 默認去重
select * from my_class;
select * from my_class
union all -- 不去重
select * from my_class;
select id,c_name,room from my_class
union all -- 不去重
select name,number,idfrom my_student;
-- 需求：男生升序睦尽，女生降序（年齡）(
select * from my_studentwhere sex='男' order by age asc limit 9999999)
union(
select *from my_studentwhere sex='女' order by agedesc limit 9999999);
-- 標量子查詢
select * from my_studentwhere c_id=(select id from my_classwhere name='python1910');
-- 列子查詢
select * from my_studentwhere c_idin (select id from my_class);
-- any some all
select * from my_studentwhere c_id =any (select id from my_class);
select * from my_studentwhere c_id =some (select id from my_class);
select * from my_studentwhere c_id =all (select id from my_class);
select * from my_studentwhere c_id !=any (select id from my_class);-- 所有結(jié)果（null除外）
select * from my_studentwhere c_id !=some (select id from my_class);-- 所有結(jié)果（null除外）
select * from my_studentwhere c_id !=all (select id from my_class);-- 1,2不等于345（null除外）
select * from my_studentwhere age=(select max(age)from my_student)and height=(select max(height)from my_student);
-- 行子查詢
select * from my_studentwhere (age,height)= (select max(age),max(height)from my_student);
select *from my_studentorder by age desc,heightdesc limit 1;-- 可能查詢結(jié)果不是預想的
-- 表子查詢
select * from my_studentgroup by c_idorder by heightdesc;
-- 插入學生
insert into my_studentvalues (null,'bc20200007','小紅','女',25,185,1);
-- 查找每個班身高最高的學生
select * from (select * from my_studentorder by heightdesc limit 9999999)as studentgroup by c_id;
--
select exists(select * from my_student);
select exists(select * from my_studentwhere id=100);
-- exists子查詢
select * from my_studentwhere? exists(select * from my_class);-- 判斷括號里是否成立
select * from my_studentwhere? exists(select * from my_classwhere id=3);
select * from my_studentwhere? exists(select * from my_classwhere id=2);
-- 視圖：單表+多表
create view my_v1as select *? from my_student;
create view my_v2as select *? from my_class;
create view my_v3as select *? from my_studentas sleft join my_classas con s.c_id=c.id;-- 錯誤：id重復
-- 多表視圖
create view my_v3as select s.*,c.name as c_name,c.room from my_studentas sjoin my_classas con s.c_id=c.id;
show tables;
-- 查看視圖創(chuàng)建語句
show create view my_v3\G
-- 視圖的使用
select *? from my_v1;
select *? from my_v2;
select *? from my_v3;
-- 修改視圖
alter view my_v1as select id,name,age,sex,height,c_idfrom my_student;
-- 刪除視圖
create view my_v4as select * from my_student;
drop view my_v4;
-- 多表視圖插入數(shù)據(jù) : 不能插入數(shù)據(jù)
insert into my_v3values (null,'bc20200008','張三豐','男',150,180,3,'python1910','a204');
-- 將學生表的學號字段設置成不能為空
alter table my_studentmodify number char(10)not null unique ;
-- 單表視圖插入數(shù)據(jù)： 視圖不包括所有不允許為空的字段
insert into my_v1values (null,'bc20200008','張三豐',150,'男',180,3);
-- 單表視圖插入數(shù)據(jù)
insert into my_v2values (2,'python1903','a204');
-- 多表視圖刪除數(shù)據(jù): 不能刪除數(shù)據(jù)
delete from mydb.my_v3where id=1;
-- 單表視圖刪除數(shù)據(jù)
delete from my_v2where id=4;
-- 多表視圖更新數(shù)據(jù)
update my_v3set c_id=4 where id=6;
-- 視圖： age 字段限制更新
create view my_v4as select * from my_studentwhere age>30 with check option;
-- 表示視圖的數(shù)據(jù)來源都是年齡大于30歲的器净，是由where age>30 決定的
-- with check option決定通過視力更新的時候，不能將已經(jīng)得到的數(shù)據(jù)age>30的改成<30的
-- 將視圖可以查看到的數(shù)據(jù)改成年齡小于30
update my_v4set age=29 where id=5;
-- 可以修改數(shù)據(jù)讓視圖可以查到： 可以改当凡，但是沒有效果
update my_v4set age=32 where id=3;
