数据库（查询）

数据库（查询）

一，子查询

按照结果集的行列不同，子查询可以分为以下几类：

• 标量子查询：结果集只有一行一列（单行子查询）
• 列子查询：结果集有一列多行
• 行子查询：结果集有一行多列
• 表子查询：结果集多行多列

 

-- 标量子查询
select *
from student
where age > (select age from student where ename='a')

-- 列子查询
select * 
from student
where id in(select s_id 
            from score
            where score > 90);

-- 男生中年龄最大的
-- 行子查询
select * from student
where (age,gender) =(select max(age),gender
    from student
    group by gender 
    having gender = '男');

-- 取数学成绩前五，正序排列
select * from 
(select s.*,s.score,c.name
from student s
    left join score sc on s.id=sc.s_id
    left join course c on c.id=sc.c_id
where c.name='数学'
order by sc.score desc
limit 0,5) s
where s.gender='男';

 

           

案例

-- 查询老师的信息和他们所带的科目平均分
SELECT t.name,AVG(sc.score)
FROM teacher t
	JOIN course c ON t.id=c.t_id
	JOIN score sc ON sc.c_id=c.id
GROUP BY c.t_id

-- 查询被‘aa’,'bb'教的课程的最高最低分
SELECT t.name,MAX(sc.score),MIN(sc.score)
FROM teacher t
	JOIN course c ON t.id=c.t_id
	JOIN score sc ON sc.c_id=c.id
GROUP BY c.t_id 
HAVING  t.name IN('aa','bb')

-- 查询每个学生的最好成绩的科目
SELECT  s.id, sc.score, c.name 
FROM student  s
      INNER JOIN score  sc
        ON s.id = sc.s_id
      INNER JOIN course  c
        ON c.id = sc.c_id
 WHERE sc.score IN (
	SELECT MAX(sc.score)
	FROM score  sc
	GROUP BY sc.s_id)
ORDER BY s.`id`,sc.score;


SELECT stu.id, stu.name, cou.name, stu.max_score FROM
 (SELECT s.id, s.name, sc.c_id, MAX(sc.score) AS max_score 
 FROM student AS s
    INNER JOIN score AS sc ON sc.s_id = s.id
    GROUP BY s.id) AS stu
    LEFT JOIN
     (SELECT c.id, c.name, sc.s_id, sc.score FROM course AS c
    INNER JOIN score AS sc ON sc.c_id = c.id) AS cou
    ON stu.max_score = cou.score AND stu.id = cou.s_id;
       
    
-- 查询所有学生的课程分数
SELECT s.name,c.name,sc.score
FROM student s
	JOIN score sc ON s.id=sc.s_id
	JOIN course c ON sc.c_id=c.id
ORDER BY s.name
-- 查询课程1，成绩在60分以上的学号，姓名
SELECT student.id,student.name,score.score,score.`c_id`
FROM student
	JOIN score ON student.id=score.`s_id`
WHERE score.`score`>=60 AND score.c_id=1;
-- 查询每个课程的最高分的学生信息*****
SELECT c.name, s.name, sc.score FROM student AS s
 INNER JOIN score AS sc
    ON s.id = sc.s_id
    INNER JOIN course AS c
    ON c.id = sc.c_id
    WHERE sc.score IN
 (SELECT MAX(sc.score)
    FROM score AS sc
    GROUP BY sc.c_id)
  ORDER BY c.id;

-- 查询平均成绩>=85的学生学号和平均成绩
SELECT s.`id`,AVG(sc.`score`)
FROM student s
	JOIN score sc ON s.id=sc.`s_id` 
GROUP BY s.`id`,sc.`s_id`
HAVING AVG(sc.`score`)>=85

-- 17.查询有不及格课程的学生信息
SELECT DISTINCT s.* 
FROM student s
	JOIN score sc ON s.id=sc.`s_id` 
WHERE sc.`score`<60

-- 18.查询每门课程有成绩的学生人数
SELECT c.id,c.name,COUNT(*) AS 个数 
FROM course c
JOIN score a ON a.c_id=c.id 
GROUP BY c.id ;

-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，
-- 如果平均成绩相同，再按照课程编号升序排列
SELECT c.`name`,AVG(sc.`score`)
FROM score sc
	JOIN course c ON  sc.`c_id`=c.`id`
GROUP BY c.`id`
ORDER BY AVG(sc.`score`) DESC,c.`id`

-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.`id`,s.name,AVG(sc.`score`)
FROM student s
	JOIN score sc ON s.id=sc.`s_id` 
GROUP BY s.`id`,sc.`s_id`
HAVING AVG(sc.`score`)>60

-- 查询仅有一门课程>80分的学生信息
SELECT student.id,student.`name`
FROM student
WHERE student.id IN 
(SELECT s_id 
FROM score 
WHERE score > 80 
GROUP BY s_id 
HAVING COUNT(score) = 1 );

-- 22.查询出只有三门课程的学生的学号和姓名
SELECT student.id,student.`name`
FROM student
WHERE student.id IN 
(SELECT s_id 
FROM score 
GROUP BY s_id 
HAVING COUNT(score) = 3 );

-- 23.查询有不及格课程的课程信息
SELECT DISTINCT c.* 
FROM student s
	JOIN score sc ON s.id=sc.`s_id` 
	JOIN course c ON sc.`c_id`=c.`id`
WHERE sc.`score`<60


-- 24.查询至少选择4门课程的学生信息
SELECT student.id,student.`name`
FROM student
WHERE student.id IN 
(SELECT s_id 
FROM score 
GROUP BY s_id 
HAVING COUNT(score) >= 4 );

-- 25.查询没有学全所有课程的同学的信息
SELECT student.id,student.`name`
FROM student
WHERE student.id IN 
(SELECT s_id 
FROM score 
GROUP BY s_id 
HAVING COUNT(score) <4 );

-- 查询学全所有课程的同学的信息
SELECT student.id,student.`name`
FROM student
WHERE student.id IN 
(SELECT s_id 
FROM score 
GROUP BY s_id 
HAVING COUNT(score) =(
	SELECT COUNT(*)
	FROM course
) );

-- 27.查询各学生都选了多少门课
SELECT s.`name`,COUNT(*)
FROM student s
	JOIN score sc ON s.id=sc.`s_id` 
	JOIN course c ON sc.`c_id`=c.`id`
GROUP BY s.`id`

-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT s.*
FROM student s
	JOIN score sc ON s.id=sc.`s_id` 
	JOIN course c ON sc.`c_id`=c.`id`
WHERE c.`name`='java' AND sc.`score`<60

-- 29.查询学过"Tony"老师授课的同学的信息
SELECT s.*
FROM student s
	JOIN score sc ON s.id=sc.`s_id` 
	JOIN course c ON sc.`c_id`=c.`id`
	JOIN teacher t ON  t.id=c.`t_id`
WHERE t.name IN ('bb')

-- 30.查询没学过"Tony"老师授课的学生信息
SELECT s.*
FROM student s
WHERE s.`id` NOT IN (
	SELECT  s.id
	FROM student s
		LEFT JOIN score sc ON s.id=sc.`s_id` 
		LEFT JOIN course c ON sc.`c_id`=c.`id`
		LEFT JOIN teacher t ON t.id=c.`t_id`
	WHERE t.name  ='bb' );