SQL子查询

create database db16sub;
use db16sub;
create table t_class(
  pk_class_id int primary keyauto_increment,
  c_classname varchar(50) not null,
  c_teachername varchar(50) not null,
  c_time date,
  c_desc varchar(200) default '狼窝教育'
)engine=innodb default charset=utf8;
create table t_student(
  pk_student_id int primary keyauto_increment,
  c_studentname varchar(50) not null,
  fk_class_id int,
  c_birthday date,
  c_address varchar(255) default '四川省成都市',
  c_qq bigint
)engine=innodb default charset=utf8;
create table t_exam(
  pk_exam_id int primary key auto_increment,
  fk_student_id int,
  c_subject varchar(30),
  c_time datetime,
  c_score decimal(4,1)
)engine=innodb default charset=utf8;
alter table t_student
  add constraint fk_class_student_ref foreignkey(fk_class_id)
    referencest_class(pk_class_id);
alter table t_exam
  add constraint fk_student_exam_ref foreignkey(fk_student_id)
    referencest_student(pk_student_id);
insert into t_class  VALUES(null,'JG16','刘阳老师','2013-07-01',default),
  (null,'JG17','弯弯老师','2013-07-10',default),
  (null,'JG18','刘阳老师','2013-07-16',default),
  (null,'JG19','周周老师','2013-07-23',default),
  (null,'JG20','娜娜老师','2013-08-01',default),
  (null,'JG21','周周老师','2013-08-15',default),
  (null,'JG22','弯弯老师','2013-08-26',default),
  (null,'JG23','燕妮老师','2013-09-12',default),
  (null,'JG24','刘阳老师','2014-07-03',default),
  (null,'JG25','刘阳老师','2014-07-15',default);
insert into t_student  VALUES
 (null,'张三',1,'1995-01-11','四川省成都市温江区',666555),
 (null,'天天',1,'1994-12-01','四川省成都市温江区',6665551),
 (null,'依然',1,'1995-07-01','四川省成都市温江区',6665552),
 (null,'外套',1,'1995-05-01','四川省成都市武侯区',6665553),
 (null,'我有',1,'1995-06-22','四川省成都市温江区',6665554),
 (null,'卡卡',1,'1995-03-06','四川省成都市武侯区',6665555),
 (null,'宝宝',1,'1995-04-01','四川省成都市温江区',6665556),
 (null,'东方',2,'1995-03-11','四川省成都市武侯区',666555),
 (null,'哈哈',2,'1994-11-01','四川省成都市武侯区',6665551),
 (null,'记录',2,'1995-08-01','四川省成都市温江区',6665552),
 (null,'天天',2,'1995-04-01','四川省成都市武侯区',6665553),
 (null,'由于',2,'1995-07-22','四川省成都市温江区',6665554),
 (null,'哥哥',2,'1995-02-06','四川省成都市温江区',6665555),
 (null,'妹妹',2,'1995-05-01','四川省成都市温江区',6665556);
insert into t_student  VALUES
 (null,'呵呵',3,'1995-02-11','四川省成都市温江区',6665557),
 (null,'双双',3,'1995-06-11','四川省成都市温江区',6665558);
select pk_class_id, c_classname, c_teachername, c_time, c_descFROM t_class;
select pk_student_id, c_studentname, fk_class_id, c_birthday,c_address, c_qq FROM t_student;

-- 显示每个学生的姓名及其所在班级名称
-- 相关子查询:1)它不是一个独立的查询语句，必须依赖于外部查询的某一列的数据值
--           2)当外部查询返回一行数据，该子查询将执行一次

select s.c_studentname 学生姓名,
  (select c_classname from t_class
    where pk_class_id=s.fk_class_id) 班级名称
FROM t_student s;

-- 查询学生‘依然’的班主任姓名
-- 非相关子查询：1）它是一个独立的查询语句，不依赖于外部的其他数据
           --   2）该子查询只执行一次

select c_teachername FROM t_class
  where pk_class_id=(select fk_class_id fromt_student
    where c_studentname='依然');

-- from 后面跟子查询
-- 该位置的子查询后面必须加上一个别名

select * from
     (select c_classname, c_teachername, c_time FROM t_class) temp;

-- 查询学生‘天天’所在的班级名称
-- 子查询返回多行数据值，这时候只能用范围匹配关键字来对应

select c_classname from t_class
  where pk_class_id in
    (selectfk_class_id from t_student where c_studentname='天天');

-- 获取比JG18班所有人年龄都小的学生信息

select pk_student_id, c_studentname, fk_class_id, c_birthday,c_address, c_qq FROM t_student
  where c_birthday>(select max(c_birthday) fromt_student where fk_class_id=(
    selectpk_class_id from t_class where c_classname='JG18'));

-- 当子查询返回多行记录进行比较时，可以使用all或者any
-- all：比较所有的值，只有当所有的都满足条件时才算条件成立，这个相当于全部都是and连接比较

select pk_student_id, c_studentname, fk_class_id, c_birthday,c_address, c_qq FROM t_student
  where c_birthday>all(select c_birthday fromt_student where fk_class_id=(
    selectpk_class_id from t_class where c_classname='JG18'));

-- any：比较所有的值，但是只需要满足任何一个都算条件成立，这个相当于全部都是or的连接比较

select pk_student_id, c_studentname, fk_class_id, c_birthday,c_address, c_qq FROM t_student
  where c_birthday>any(select c_birthday fromt_student where fk_class_id=(
    selectpk_class_id from t_class where c_classname='JG18'));

-- exists 它相当于C语言中if的逻辑
-- 当exists它后面的子查询有返回数据时，主查询正常秩序
-- 当exists它后面的子查询没有返回数据时，主查询也不返回任何结果

select pk_class_id, c_classname, c_teachername, c_time, c_desc FROMt_class
   where exists(select * fromt_student where fk_class_id=1);
select pk_class_id, c_classname, c_teachername, c_time, c_descFROM t_class
   where exists(select * fromt_student where fk_class_id=6);

-- 组合查询union
-- union是把2个查询结果做一个组合投影显示
-- union组合显示后会把重复的数据去掉，而union all则显示所有，包括重复

select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<5
union
select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<8 and pk_class_id>3;
select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<5
union all
select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<8 and pk_class_id>3;

-- union 组合查询的结果列名，是提取自第一个查询语句

select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<5
union
select pk_student_id, c_studentname, c_birthday
  from t_student
  where pk_student_id<5;

 
-- 使用union时，组合的查询结果数据类型要对应

select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<5
union
select pk_student_id, c_birthday, c_studentname
  from t_student
  where pk_student_id<5;

-- 使用union组合时，列的数目必须一致

select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<5
union
select pk_student_id, c_studentname
  from t_student
  where pk_student_id<5;

-- union组合数据可以多合并，无限制，但是必须符合组合基本规则

select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<5
union
select pk_student_id, c_studentname, c_birthday
  from t_student
  where pk_student_id<5
union all
select pk_class_id, c_classname, c_time
 FROM t_class
 where pk_class_id<8;