--創(chuàng)建外鍵
# create table my_foreignl(
# id int primary key auto_increment,
# name varchar(20) not null comment
# '學生姓名',
# c_id int comment '班級id',
# --普通字段
# )charset utf8;
# --增加外鍵
# alter table my_foreign2 add
# --指定外鍵名
# constraint atudent_class_1
# --指定外鍵字段
# foreign key(c_id)
# --引用父表主鍵
# references my_class(id);
# --刪除外鍵
# alter table my_foreignl drop
# foreign key my_foreignl_ibfk_l;
# --插入數(shù)據(jù):外鍵字段在父表不存在
# insert into my_foreign2 values(null,'郭富城',4)--沒有四號班級
# insert into my_foreign2 values(null,'項羽',1);
# insert into my_foreign2 values(null,'劉邦',2);
# insert into my_foreign2 values(null,'韓信',3);
# update my_foreign2 set c_id=2
# where id=4;
# --更新父表記錄
# update my_class set id=4 where id=1; --失敗草丧；id=1記錄已經(jīng)被學生引用
# update my_class set id=4 where id=3; --可以：沒有學生引用此班級
# --插入數(shù)據(jù)
# insert into my_foreignl values(null,'馬超',3);
# --增加外鍵
# alter table my_foreignl add foreign key(c_id)references my_class(id);
# --失敳础：因為沒有三號班
# --創(chuàng)建外鍵：指定模式：刪除置空，更新級聯(lián)
# 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
# )charset utf8;
# --插入數(shù)據(jù)
# insert into my_foreign3 values(
# null,'劉備'煤禽，1)岖赋，
# (null,'曹操',1),
# (null,'孫權(quán)',1),
# (null,'諸葛亮',2),
# (null,'周瑜',2)；
# --解除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;
# --聯(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,id from my_student;
# --需求：男生升序选脊，女生降序（年齡）
# (select * from my_student
# where sex='男'
# order by age asc limit 999999)
# union
# (select * from my_student
#? where sex='女'
#? order by age desc limit 9999999);
# select * from my_student where c_id=(
# --標量子查詢
# select id from my_class where c_name='Python1903');--id一定只有一個值(一行一列)
# insert into my_class values(1,'python1907','B407');
# --列子查詢
# select * from my_student where c_id in(select id from my_class);
# --any,some,all
# 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); --所有結(jié)果(NULL除外)
# select * from my_student where c_id!=some(select id from my_class); --所有結(jié)果(NULL除外)
# select * from my_student where c_id!=all(select id from my_class); --2號班級（NULL除外）
# update? my_student set height=188
# where name='王五';
# select * from my_student where age=(select max(age) from my_student)
# and
# height=(select max(height)from my_student);
# --行子查詢
# select * from my_student
# --（age,height）稱之為行元素
# where(age,height)=(select max(age),max(height)from my_student);
# select *from my_student order by age desc,height desc limit 1;
# --表字查詢
# select * from my_student group by c_id order by height desc;--每個班選出第一個學生再按身高排序
# selecr * from (select * from my_student order by height desc)
# as student group by student.c_id;
