SQL专项练习11

将学生信息表的班级列默认值设为“暂未输入”

alter table 表名 modify 列名 数据类型default '默认值'

ALTER TABLE student MODIFY class CHAR(10) DEFAULT '暂未输入'；

创建视图

create view 视图名 as select ....

MySQL 8.0. 不支持 full join

('1004' , '张三' ,'2000-08-06' , '男'); ('1009' , '李四', '2000-01-01', '男');

('1010' , '李四', '2001-01-01', '男'); ('1013' , '赵六' ,'2000-09-06' , '男');

('1006' , '王五', '2000-08-06' , '女'); ('1008' , '张三', '2002-12-01', '女');

('1012' , '张三', '2001-12-01', '女'); ('1011' , '李四', '2002-08-06' , '女');

select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1 
full join 
(select * from student_table where sex = '女')t2 
on  t1.name = t2.name ; 

# 等价于

select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
right join
(select * from student_table where sex = '女')t2
on t1.name = t2.name

union all

select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.name = t2.name

where t2.name is null ;