SQL 巢狀查詢的具體使用

#建立表及插入記錄
CREATE TABLE class (
  cid int(11) NOT NULL AUTO_INCREMENT,
  caption varchar(32) NOT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;
 
INSERT INTO class VALUES
(1, '三年二班'), 
(2, '三年三班'), 
(3, '一年二班'), 
(4, '二年九班');
 
CREATE TABLE course(
  cid int(11) NOT NULL AUTO_INCREMENT,
  cname varchar(32) NOT NULL,
  teacher_id int(11) NOT NULL,
  PRIMARY KEY (cid),
  KEY fk_course_teacher (teacher_id),
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO course VALUES
(1, '生物', 1), 
(2, '物理', 2), 
(3, '體育', 3), 
(4, '美術', 2);
 
CREATE TABLE score (
  sid int(11) NOT NULL AUTO_INCREMENT,
  student_id int(11) NOT NULL,
  course_id int(11) NOT NULL,
  num int(11) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_score_student (student_id),
  KEY fk_score_course (course_id),
  CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);
 
 
CREATE TABLE student(
  sid int(11) NOT NULL AUTO_INCREMENT,
  gender char(1) NOT NULL,
  class_id int(11) NOT NULL,
  sname varchar(32) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_class (class_id),
  CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO student VALUES
(1, '男', 1, '理解'), 
(2, '女', 1, '鋼蛋'), 
(3, '男', 1, '張三'), 
(4, '男', 1, '張一'), 
(5, '女', 1, '張二'), 
(6, '男', 1, '張四'), 
(7, '女', 2, '鐵錘'), 
(8, '男', 2, '李三'), 
(9, '男', 2, '李一'), 
(10, '女', 2, '李二'), 
(11, '男', 2, '李四'), 
(12, '女', 3, '如花'), 
(13, '男', 3, '劉三'), 
(14, '男', 3, '劉一'), 
(15, '女', 3, '劉二'), 
(16, '男', 3, '劉四')
 
CREATE TABLE teacher(
  tid int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(32) NOT NULL,
  PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO teacher VALUES
(1, '張磊老師'), 
(2, '李平老師'), 
(3, '劉海燕老師'), 
(4, '朱雲海老師'), 
(5, '李傑老師');

​
select * from course c where c.teacher_id='1'; 
select * from teacher t where t.tid='1';
 
​​-- 最後拼接
select * ,(select tname from teacher t where t.tid=c.teacher_id)name from course c;

-- 選擇了體育學生的id
select sc.student_id from course co 
left join score sc on co.cid=sc.course_id where cname='體育'
-- 通過學生表用學生id匹配學生姓名
select st.sname from student st where st.sid in(
select sc.student_id from course co 
left join score sc on co.cid=sc.course_id where cname='體育')

select a.* from (select st.sname,cl.caption from score sc 
left join course co on sc.course_id=co.cid
left join student st on st.sid=sc.student_id
left join class cl on cl.cid=st.class_id
where co.cname='體育')a where a.caption='三年二班';

select * from student st where exists(
select *from score sc where course_id='1' and sc.student_id=st.sid
);

select *from course co
left join teacher te on te.tid=co.teacher_id;