SQL 嵌套查询的具体使用

目录

• 插入数据 
•  select子查询(外语句先执行，内语句后执行)
• 总结

#创建表及插入记录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;

-- 选择了体育学生的idselect 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.cidleft join student st on st.sid=sc.student_idleft join class cl on cl.cid=st.class_idwhere 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 coleft join teacher te on te.tid=co.teacher_id;