-- 11、查询没有学全所有课程的同学的信息 -- 解法一:所学课程数量 < 课程数量 SELECT s.* FROM student s LEFT JOIN score s1 ON s.`s_id` = s1.`s_id` GROUP BY s1.`s_id` HAVING COUNT(s1.`c_id`) < (SELECT COUNT(1) FROM course) -- 解法二 SELECT * FROM student WHERE s_id NOT IN( SELECT s_id FROM score t1 GROUP BY s_id HAVING COUNT(*) =(SELECT COUNT(DISTINCT c_id) FROM course)) -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 -- 解法一:左连接 SELECT DISTINCT s.* FROM student s LEFT JOIN score s1 ON s.`s_id` = s1.`s_id` WHERE s1.`s_id` <> ‘01‘ AND s1.`c_id` IN (SELECT c_id FROM score WHERE s_id = ‘01‘) -- 解法二:子查询 SELECT * FROM student WHERE s_id IN( SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN(SELECT a.c_id FROM score a WHERE a.s_id=‘01‘) ) AND s_id <> ‘01‘; -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 -- 解法一 SELECT s_name FROM student WHERE s_id NOT IN ( SELECT s.`s_id` FROM student s LEFT JOIN score s1 ON s.`s_id` = s1.`s_id` WHERE s1.`c_id` IN (SELECT c_id FROM teacher t LEFT JOIN course c ON t.`t_id` = c.`t_id` WHERE t.t_name = ‘张三‘) ) -- 解法2 SELECT a.s_name FROM student a WHERE a.s_id NOT IN ( SELECT s_id FROM score WHERE c_id = (SELECT c_id FROM course WHERE t_id =( SELECT t_id FROM teacher WHERE t_name = ‘张三‘))); -- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 -- mark SELECT a.s_id 学号,a.s_name 姓名,ROUND(AVG(b.s_score)) 平均成绩 FROM student a LEFT JOIN score b ON a.s_id = b.s_id WHERE a.s_id IN( SELECT s_id FROM score WHERE s_score<60 GROUP BY s_id HAVING COUNT(1)>=2) GROUP BY a.s_id,a.s_name
在做了一些sql练习题,心中有一些疑问,查询资料后记录如下。
1、count(1) count(*) count(列名) 有什么区别?
(1)执行结果上
测试数据如下图:
SELECT COUNT(列名)的结果为 5; SELECT COUNT(1)的结果为 10; SELECT COUNT(*)的结果为 10, 也是count(列名)不会包括为null的字段。
那select count(NULL) 结果是什么?
原因是当count()括号内的值为null时,mysql内部自动返回0,不进行进一步查询。
(2)执行效率上
原理有点复杂,《高性能MySQL》推荐写法是count(*)
2、怎么描述内连接和外连接的区别 ?
一、inner join(内连接、等值连接,也可以省略 INNER 使用 JOIN,效果一样):只返回两个表中联结字段相等的行。
二、外连接分为左外连接、右外连接 和 全外连接;
(1)left join(左联接):返回包括左表中的所有记录和右表中联结字段相等的记录。
(2)ight join(右联接):返回包括右表中的所有记录和左表中联结字段相等的记录。
(3)full join(全连接)
下面展示一个简单的栗子:
现在有两张表分别为student、student2,如下所示:
student表:
student2表:
SELECT * FROM student s1 INNER JOIN student2 s2 ON s1.s_id = s2.s_id
查询结果如下:
SELECT * FROM student s1 LEFT JOIN student2 s2 ON s1.s_id = s2.s_id
查询结果如下:
SELECT * FROM student s1 RIGHT JOIN student2 s2 ON s1.s_id = s2.s_id
查询结果如下:
(3)全外连接(MySQL目前不支持此种方式,可以用其他方式替代解决。)
那么问题就来了:Full Join的问题该如何解决呢?我们可以用UNION ALL操作来间接使用Full Join。
SELECT * FROM student s1 LEFT JOIN student2 s2 ON s1.s_id = s2.s_id UNION ALL SELECT * FROM student s1 RIGHT JOIN student2 s2 ON s1.s_id = s2.s_id
查询结果如下:
如果想去掉重复的数据呢? 用union即可。
三、Cross Join
交叉连接,又称笛卡尔连接(cartesian join)或叉乘(Product),如果A和B是两个集合,它们的交叉连接就记为:A x B。
SELECT * FROM student s1 CROSS JOIN student2 s2