sql练习题（2）
时间：2021-07-01 10:21:17
帮助过：5人阅读
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
sql练习题（2）
标签：tin 想去 inner 成绩 join 查询 记录 自动 bsp
