sql练习题(2)

-- 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)执行结果上

测试数据如下图:

sql练习题(2)

 SELECT COUNT(列名)的结果为 5;  SELECT COUNT(1)的结果为 10;  SELECT COUNT(*)的结果为 10, 也是count(列名)不会包括为null的字段。

那select  count(NULL) 结果是什么?

sql练习题(2)

 原因是当count()括号内的值为null时,mysql内部自动返回0,不进行进一步查询。

(2)执行效率上

原理有点复杂,《高性能MySQL》推荐写法是count(*)

2、怎么描述内连接和外连接的区别 ?

一、inner join(内连接、等值连接,也可以省略 INNER 使用 JOIN,效果一样):只返回两个表中联结字段相等的行。

二、外连接分为左外连接、右外连接 和 全外连接;

(1)left join(左联接):返回包括左表中的所有记录和右表中联结字段相等的记录。

(2)ight join(右联接):返回包括右表中的所有记录和左表中联结字段相等的记录。

(3)full join(全连接)

下面展示一个简单的栗子:

现在有两张表分别为student、student2,如下所示:

student表:

sql练习题(2)

 student2表:

sql练习题(2)

SELECT * FROM student s1 INNER JOIN student2 s2 ON s1.s_id = s2.s_id

查询结果如下:

sql练习题(2)

 SELECT * FROM student s1 LEFT JOIN student2 s2 ON s1.s_id = s2.s_id

查询结果如下:

sql练习题(2)

SELECT * FROM student s1 RIGHT JOIN student2 s2 ON s1.s_id = s2.s_id

查询结果如下:

sql练习题(2)

sql练习题(2)sql练习题(2)sql练习题(2)

(3)全外连接(MySQL目前不支持此种方式,可以用其他方式替代解决。)

 sql练习题(2)

那么问题就来了: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

 查询结果如下:

 sql练习题(2)

如果想去掉重复的数据呢? 用union即可。

 三、Cross Join

交叉连接,又称笛卡尔连接(cartesian join)或叉乘(Product),如果A和B是两个集合,它们的交叉连接就记为:A x B。

SELECT * FROM student s1 CROSS JOIN student2 s2

sql练习题(2)

相关推荐