首页 > 数据库 >2022-8-17数据库查询联系

2022-8-17数据库查询联系

时间：2022-08-17 22:01:36浏览次数：48  
标签：JOIN NAME 17 数据库 score 2022 id SELECT LEFT

DQL查询语言

子查询

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

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

  • 列子查询：结果集有一列多行

  • 行子查询：结果集有一行多列

  • 表子查询：结果集多行多列

-- 查询比小虎年龄大的所有学生
-- 标量子查询
SELECT
    * 
FROM
    student 
WHERE
    age > ( SELECT age FROM student WHERE NAME = '小虎' );
-- 查询有一门学科分数大于90分的学生信息
-- 列子查询
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        s_id 
    FROM
        scores 
WHERE
    score > 90);
-- 查询男生且年龄最大的学生
-- 行子查23询
SELECT
    * 
FROM
    student 
WHERE
    age = (
    SELECT
        max( age ) 
    FROM
        student 
    GROUP BY
        gender 
    HAVING
    gender = '男' 
    )
    
-- 优化
SELECT
    * 
FROM
    student 
WHERE
    ( age, gender ) = (
    SELECT
        max( age ),
        gender 
    FROM
        student 
    GROUP BY
        gender 
    HAVING
    gender = '男' 
    )

总结：

  • where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。

  • where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。

-- 取排名数学成绩前五的学生，正序排列
SELECT
    * 
FROM
    (
    SELECT
        s.*,
        sc.score score,
        c.NAME 科目 
    FROM
        student s
        LEFT JOIN scores sc ON s.id = sc.s_id
        LEFT JOIN course c ON c.id = sc.c_id 
    WHERE
        c.NAME = '数学' 
    ORDER BY
        score DESC 
        LIMIT 5 
    ) t 
WHERE
    t.gender = '男';

经验分享：

  1. 分析需求

  2. 拆步骤

  3. 分步写sql

  4. 整合拼装sql

-- 查询每个老师的代课数
SELECT t.id, t.NAME,( SELECT count(*) FROM course c WHERE c.id = t.id ) AS 代课的数量 
FROM
    teacher t;
----------------------------------------------------------------------------
SELECT
    t.id,
    t.NAME,
    count(*) '代课的数量' 
FROM
    teacher t
    LEFT JOIN course c ON c.t_id = t.id 
GROUP BY
    t.id,
    t.NAME;
-- exists
SELECT
    * 
FROM
    teacher t 
WHERE
    EXISTS ( SELECT * FROM course c WHERE c.t_id = t.id );
----------------------------------------------------------------------------SELECT
    t.*,
    c.`name` 
FROM
    teacher t
    INNER JOIN course c ON t.id = c.t_id;

总结：如果一个需求可以不用子查询，尽量不使用。

sql可读性太低。

需求

-- 3.查询每个同学的最高成绩和科目名称****
SELECT
    t.id,
    t.NAME,
    c.id,
    c.NAME,
    r.score 
FROM
    (
    SELECT
        s.id,
        s.NAME,(
        SELECT
            max( score ) 
        FROM
            scores r 
        WHERE
            r.s_id = s.id 
        ) score 
    FROM
        student s 
    ) t
    LEFT JOIN scores r ON r.s_id = t.id 
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = c.id;
-- 5.查询每个课程的最高分的学生信息*****
SELECT
    * 
FROM
    student s 
WHERE
    id IN (
    SELECT DISTINCT
        r.s_id 
    FROM
        (
        SELECT
            c.id,
            c.NAME,
            max( score ) score 
        FROM
            student s
            LEFT JOIN scores r ON r.s_id = s.id
            LEFT JOIN course c ON c.id = r.c_id 
        GROUP BY
            c.id,
            c.NAME 
        ) t
        LEFT JOIN scores r ON r.c_id = t.id 
    AND t.score = r.score 
    )
-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT
    s.id,
    s.NAME sname,
    sc.score,
    c.NAME 
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id
    LEFT JOIN course c ON sc.c_id = c.id 
WHERE
    s.NAME LIKE '%张%' 
    OR s.NAME LIKE '%李%';
-- 7.查询平均成绩及格的同学的信息。（子查询）
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        sc.s_id 
    FROM
        scores sc 
    GROUP BY
        sc.s_id 
    HAVING
    avg( sc.score ) >= 70 
    )
-- 8.将学生按照总分数进行排名。（从高到低）
SELECT
    s.id,
    s.NAME,
    sum( sc.score ) score 
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id 
GROUP BY
    s.id,
    s.NAME 
ORDER BY
    score DESC,
    s.id ASC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
    c.NAME,
    max( sc.score ),
    min( sc.score ),
    avg( sc.score ) 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
WHERE
    c.NAME = '数学';
-- 10.将各科目按照平均分排序。
SELECT
    c.id,
    c.NAME,
    avg( sc.score ) score 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
    c.id,
    c.NAME 
ORDER BY
    score DESC;
-- 11.查询老师的信息和他所带的科目的平均分
SELECT
    t.id,
    t.NAME,
    c.id cid,
    c.NAME cname,
    avg( r.score ) 
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id
    LEFT JOIN scores r ON r.c_id = c.id 
GROUP BY
    t.id,
    t.NAME,
    c.id,
    c.NAME;
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT
    t.id,
    t.NAME,
    c.id cid,
    c.NAME cname,
    max( r.score ),
    min( r.score ) 
FROM
    teacher 
    LEFT JOIN course  ON teacher.id = curse.t_id
    LEFT JOIN scores  ON scores.c_id = course.id 
GROUP BY
    t.id,
    t.NAME,
    c.id,
    c.NAME 
HAVING
    t.NAME IN ( 'Tom', 'Jerry' );
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT
    t.id,
    t.sname,
    r.c_id,
    c.NAME,
    t.score 
FROM
    (
    SELECT
        s.id,
        s.NAME sname,
        max( r.score ) score 
    FROM
        student s
        LEFT JOIN scores r ON r.s_id = s.id 
    GROUP BY
        s.id,
        s.NAME 
    ) t
    LEFT JOIN scores r ON r.s_id = t.id 
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = c.id;
-- 14.查询所有学生的课程及分数
SELECT
    s.id,
    s.NAME,
    c.id,
    c.NAME,
    r.score 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
    LEFT JOIN course c ON c.id = r.c_id;
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT
    * 
FROM
    student s 
WHERE
    s.id IN (
    SELECT
        r.s_id 
    FROM
        scores r 
    WHERE
    r.c_id = 1 
    AND r.score > 60)
--------------------------------------------------------
SELECT
    s.*,
    r.* 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
WHERE
    r.c_id = 1 
    AND r.score > 60
-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
SELECT
    s.id,
    s.NAME,
    t.score 
FROM
    student s
    LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id 
WHERE
    t.score >= 70;
-- 17.查询有不及格课程的学生信息
SELECT
    * 
FROM
    student s 
WHERE
    id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );
-- 18.查询每门课程有成绩的学生人数
SELECT
    c.id,
    c.NAME,
    t.number 
FROM
    course c
    LEFT JOIN ( SELECT r.c_id, count(*) number FROM scores r GROUP BY r.c_id ) t ON c.id = t.c_id;
----------------------------------------------------
SELECT
    c.id,
    c.NAME,
    count(*) 
FROM
    course c
    LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
    c.id,
    c.NAME;
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT
    c.id,
    c.NAME,
    avg( score ) score 
FROM
    course c
    LEFT JOIN scores r ON c.id = r.c_id 
GROUP BY
    c.id,
    c.NAME 
ORDER BY
    score DESC,
    c.id ASC;
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT
    s.id,
    s.NAME sname,
    avg( r.score ) score 
FROM
    student s
    LEFT JOIN scores r ON r.s_id = s.id
    LEFT JOIN course c ON c.id = r.c_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    score > 65;
-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*) = 1 );
----------------------------------------------------------------------------
SELECT
    s.id,
    s.NAME,
    s.gender 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
WHERE
    r.score > 80 
GROUP BY
    s.id,
    s.NAME,
    s.gender 
HAVING
    count(*) = 1
-- 22.查询出只有三门课程的学生的学号和姓名
SELECT
    * 
FROM
    student s 
WHERE
    id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING count(*) = 3 );
----------------------------------------------------------------------------
SELECT
    s.id,
    s.NAME,
    s.gender 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME,
    s.gender 
HAVING
    count(*) = 3
-- 23.查询有不及格课程的课程信息
SELECT
    * 
FROM
    course c 
WHERE
    id IN (
    SELECT
        r.c_id 
    FROM
        scores r 
    GROUP BY
        r.c_id 
    HAVING
    min( r.score ) < 60 
    )
----------------------------------------------------------------------------
SELECT
    c.id,
    c.NAME 
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id 
GROUP BY
    sc.c_id,
    c.NAME 
HAVING
    min( sc.score ) < 60;
-- 24.查询至少选择4门课程的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        r.s_id 
    FROM
        scores r 
    GROUP BY
        r.s_id 
    HAVING
    count(*) >= 4 
    )
----------------------------------------------------------------------------
SELECT
    s.id,
    s.NAME 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    count(*) >= 4;
-- 25.查询没有选全所有课程的同学的信息
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        r.s_id 
    FROM
        scores r 
    GROUP BY
        r.s_id 
    HAVING
    count(*) != 5
    )
-- 26.查询选全所有课程的同学的信息
SELECT
    s.id,
    s.NAME,
    count(*) number 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME 
HAVING
    number = ( SELECT count(*) FROM course );
-- 27.查询各学生都选了多少门课
SELECT
    s.id,
    s.NAME,
    count(*) number 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id 
GROUP BY
    s.id,
    s.NAME
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT
    s.id,
    s.NAME,
    r.score 
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
    LEFT JOIN course c ON r.c_id = c.id 
WHERE
    c.NAME = 'java' 
    AND r.score < 60;
-- 29.查询学过"Tony"老师授课的同学的信息
SELECT
    s.id,
    s.NAME 
FROM
    student s
    LEFT JOIN scores r ON r.s_id = s.id
    LEFT JOIN course c ON c.id = r.c_id
    LEFT JOIN teacher t ON t.id = c.t_id 
WHERE
    t.NAME = 'Tom';
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id NOT IN (
    SELECT DISTINCT
        s.id 
    FROM
        student s
        LEFT JOIN scores r ON r.s_id = s.id
        LEFT JOIN course c ON c.id = r.c_id
        LEFT JOIN teacher t ON t.id = c.t_id 
    WHERE
    t.NAME = 'Tom' 
    )

日期格式

格式描述
%a 缩写的星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值（00-31）
%e 月的天，数值（0-31）
%f 微秒
%H 小时（00-23）
%h 小时（01-12）
%I 小时（01-12）
%i 分钟，数值（00-59）
%j 年的天（001-366）
%k 小时（0-23）
%l 小时（1-12）
%M 月名
%m 月，数值（00-12）
%p AM或PM
%r 时间，12-小时 （hh:mm:ss AM或PM）
%S 秒（00-59）
%s 秒（0-59）
%T 时间，24-小时（hh:mm:ss）
%U 周（00-53）星期日是一周的第一天
%u 周（00-53）星期一是一周的第一天
%W 星期名
%Y 年，2022
%y 年，22

 

标签：JOIN,NAME,17,数据库,score,2022,id,SELECT,LEFT
From： https://www.cnblogs.com/sunnaiyu/p/16596920.html

相关文章

  • IOI 2022 简要题解
    考前写题解增加RP。D1T1：考虑按照列DP。对于每一列选择的鱼的区间进行决策。每列中被选择的y坐标最大的鱼，需要被左面或右面覆盖。假设我们决策好了前i列的方案，考虑第i列......
  • Codeforces 1713C - Build Permutation
    题意为给出一个长度为n的空数组，数组下标为0至n-1。我们需要在数组中的每个位置上填上合适的数A[i]，使得i+A[i]为完全平方数。并且数组最后需为0至n-1的一个排列。......
  • 2022-08-17 第三小组 陈迪 学习笔记
    --1根据学生学号查询2020年-1-1之后借过哪些书籍SELECTs.sid,s.sname,bk.bname,b.bdateFROMborrowrecoredbLEFTJOINstudentsONs.cid=b.cidLEFTJOINbook......
  • 2022-08-17 第六小组 高佳誉 学习笔记
    DQL查询语言子查询按照结果集的行列数不同，子查询可以分为以下几类：标量子查询：结果集只有一行一列（单行子查询）列子查询：结果集有一列多行行子查询：结果集有一行多列表子......
  • acwing2022秋招每日一题 1302. 层数最深叶子节点的和
    题目给你一棵二叉树的根节点 root ，请你返回 层数最深的叶子节点的和 。思路根据层序遍历，访问所有节点。将每一层上的结点，进行统计，直到最后一层时，统计所有节点数。......
  • 2022-08-16 第五组 赖哲栋 学习笔记
    DQL数据库查询语言重点，DQL是我们每天都要接触编写最多也是最难的SQL，该语言用来查询记录，不会修改数据库和表结构。构建数据库创建一张student表：DROPTABLEIFEXISTSst......
  • 2022-08-17 第五组 赖哲栋 学习笔记
    DQL查询语言子查询按照结果集的行列数不同，子查询可以分为以下几类：标量子查询：结果集只有一行一列（单行子查询）列子查询：结果集有一列多行行子查询：结果集有一行多列表子......
  • 8.17总结
    自动刷题机\(solution\)二分答案找最大最小值考试时二分写错了ACCode#include<bits/stdc++.h>usingnamespacestd;#definelllonglonginlinellread(){ ll......
  • 2022/8/17 总结
    A.P4343[SHOI2015]自动刷题机啊对对对，算法都对了，二分写挂了：）Solution二分答案，每次\(\mathtt{O(n)}\)判断当前的\(mid\)是否可行，最大和最小分开二分；注意:......
  • 【2022-08-17】mysql基础知识(四)
    mysql基础知识(四)mysql之操作表的多条SQL语句修改表名普通方法：altertabletest1renametest;进阶方法：renametabletesttotest1;可同时修改多个：renam......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99