收款定制开发查询又称子查询,有select子查询,where子查询,from子查询,exists子查询。
插入数据
- #收款定制开发创建表及插入记录
- 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子查询(外语句先执行,内语句后执行)
例1. 查询课程名并显示课程老师的名称
-
- 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;
where 子查询(先执行子查询,再执行外查询)
例2.查询学习了体育的学生
- -- 选择了体育学生的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='体育')
部分结果:
from 子查询(先执行子查询,再执行外查询)
例3.列出三年二班学了体育的学生
- 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='三年二班';
exists 子查询
(先执行外语句,再执行子查询,根据子查询返回结果判断是否保留外查询结果)
例4.查询学了课程di为1的学生的姓名
- select * from student st where exists(
- select *from score sc where course_id='1' and sc.student_id=st.sid
- );
总结
sql查询本质就是对各种表进行裁剪和拼接,最后得到我们想要的数据。
所有的能通过子查询完成的查询都能用连接查询完成,如例1:查询课程名并显示课程老师的名称
- select *from course co
- left join teacher te on te.tid=co.teacher_id;
所以要灵活运用查询方式,才能更高效的查询。