MySQL（學生表、教師表、課程表、成績表）多表查詢

来源:https://www.cnblogs.com/donleo123/archive/2019/10/09/11642510.html
-Advertisement-
Play Games

1、表架構 student(sid,sname,sage,ssex) 學生表 course(cid,cname,tid) 課程表 sC(sid,cid,score) 成績表 teacher(tid,tname) 教師表 2、建表sql語句 3、問題：（1）查詢“30001”課程的所有學生的學號與分數 ...


1、表架構

student(sid,sname,sage,ssex) 學生表 
course(cid,cname,tid) 課程表 
sC(sid,cid,score) 成績表 
teacher(tid,tname) 教師表

2、建表sql語句

 

 1 CREATE TABLE student 
 2   ( 
 3      sid INT PRIMARY KEY NOT NULL,
 4      sname VARCHAR(30), 
 5      sage INT, 
 6      ssex VARCHAR(8) 
 7   )  
 8  
 9 CREATE TABLE course 
10   ( 
11      cid INT PRIMARY KEY NOT NULL, 
12      cname VARCHAR(30), 
13      tid INT 
14   ) 
15  
16 CREATE TABLE sc 
17   ( 
18      sid INT NOT NULL, 
19      cid INT NOT NULL, 
20      score INT 
21   )  
22  
23 CREATE TABLE teacher 
24   ( 
25      tid INT PRIMARY KEY NOT NULL, 
26      tname VARCHAR(30) 
27   )

 

3、問題：
（1）查詢“30001”課程的所有學生的學號與分數； 

SELECT sid,score FROM sc WHERE cid="30001"

 

（2）查詢“001”課程比“002”課程成績高的所有學生的學號與分數；

SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="30001") a,

      (SELECT sid,score FROM sc WHERE cid="30002") b

     WHERE a.score>b.score AND a.sid=b.sid

 

（3）查詢平均成績大於60分的同學的學號和平均成績；

SELECT sid,AVG(score)

FROM sc

GROUP BY sid HAVING AVG(score)>60

 

（4）查詢所有同學的學號、姓名、選課數、總成績

SELECT s.sid AS "學號", s.sname AS "姓名", COUNT(sc.cid) AS "課程數目", SUM(sc.score) AS "總分數"

FROM student s, sc sc

WHERE s.sid=sc.sid

GROUP BY s.sid

 

（5）查詢姓“李”的老師的個數；

select count(distinct(Tname))

  from teacher

  where tname like '李%';

 

（6）查詢學過“張三”老師課的同學的學號、姓名

SELECT s.sid AS "學號", s.sname AS "姓名"

FROM student s, sc sc, course c, teacher t

WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="張三"


（7）查詢沒有學過“張三”老師課的同學的學號、姓名

SELECT s.sid, s.sname

FROM student s

WHERE s.sid NOT IN (

SELECT s.sid

FROM student s, sc sc, course c, teacher t

WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="張三"

)


（8）查詢學過“30001”並且也學過編號“30002”課程的同學的學號、姓名 

SELECT s.sid, s.sname

FROM student s, sc sc

WHERE s.sid=sc.sid AND sc.cid="30001" AND EXISTS(

     SELECT * FROM sc AS sc2 WHERE sc2.sid=sc.sid AND sc2.cid="30002"

)


（9）查詢學過“葉平”老師所教的所有課的同學的學號、姓名；

SELECT sid, sname

FROM student

WHERE sid IN (

SELECT sc.sid

FROM sc sc, course c, teacher t

WHERE sc.cid=c.cid AND c.tid=t.tid AND t.tname="張二"

)


（10）查詢所有課程成績小於60分的同學的學號、姓名

SELECT sid, sname FROM student

WHERE sid NOT IN (

SELECT DISTINCT(sc.sid) FROM student s, sc sc

WHERE sc.sid=s.sid AND sc.score>60)

 

（11）查詢沒有學全所有課的同學的學號、姓名；

SELECT sid, sname FROM student 

WHERE sid NOT IN(

SELECT s.sid FROM student s, sc sc

WHERE sc.sid=s.sid

GROUP BY s.sid

HAVING COUNT(sc.cid)=(

SELECT COUNT(cid) FROM course))


（12）查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

SELECT cid AS "課程ID", MAX(score) AS "最高分", MIN(score) AS "最低分"
FROM sc
GROUP BY cid


（13）按各科平均成績從低到高和及格率的百分數從高到低順序

(方式一)
SELECT sc.cid AS "課程ID",c.cname AS "課程名", AVG(sc.score) AS "平均成績",
SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 AS "及格百分數"
FROM sc sc, course c
WHERE sc.cid=c.cid
GROUP BY sc.cid
ORDER BY AVG(sc.score) ASC,
SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 DESC


(方式二)
SELECT sc.cid AS "課程ID",c.cname AS "課程名", IFNULL(AVG(sc.score),0) AS "平均成績",
100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS "及格百分數"
FROM sc sc, course c
WHERE sc.cid = c.cid
GROUP BY sc.cid
ORDER BY AVG(sc.score) ASC,
100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC


（14）查詢所有學生的所有科目的成績單（學號、姓名、語文、數學、英語、物理、平均分、總分（按照總分由高到低排序））

SELECT s.sid AS "學號", s.sname AS "姓名",
SUM(CASE c.cname WHEN "語文" THEN sc.score ELSE 0 END) AS "語文",
SUM(CASE c.cname WHEN "數學" THEN sc.score ELSE 0 END) AS "數學",
SUM(CASE c.cname WHEN "英語" THEN sc.score ELSE 0 END) AS "英語",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "總分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
ORDER BY IFNULL(SUM(sc.score),0) DESC


（15）查詢總分排名在200-300（包含200和第300）之間的學生所有成績單信息

SELECT s.sid AS "學號", s.sname AS "姓名",
SUM(CASE c.cname WHEN "語文" THEN sc.score ELSE 0 END) AS "語文",
SUM(CASE c.cname WHEN "數學" THEN sc.score ELSE 0 END) AS "數學",
SUM(CASE c.cname WHEN "英語" THEN sc.score ELSE 0 END) AS "英語",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "總分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
HAVING IFNULL(SUM(sc.score),0) BETWEEN 200 AND 300
ORDER BY IFNULL(SUM(sc.score),0) DESC


（16）查詢總分排名在前四名的學生所有成績單信息

SELECT s.sid AS "學號", s.sname AS "姓名",
SUM(CASE c.cname WHEN "語文" THEN sc.score ELSE 0 END) AS "語文",
SUM(CASE c.cname WHEN "數學" THEN sc.score ELSE 0 END) AS "數學",
SUM(CASE c.cname WHEN "英語" THEN sc.score ELSE 0 END) AS "英語",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "總分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
ORDER BY IFNULL(SUM(sc.score),0) DESC
LIMIT 0,4


（17）查詢總分排名在前二名到四名的學生所有成績單信息（limit 1,3表示從第二條數據開始，連續三條數據）

SELECT s.sid AS "學號", s.sname AS "姓名",
SUM(CASE c.cname WHEN "語文" THEN sc.score ELSE 0 END) AS "語文",
SUM(CASE c.cname WHEN "數學" THEN sc.score ELSE 0 END) AS "數學",
SUM(CASE c.cname WHEN "英語" THEN sc.score ELSE 0 END) AS "英語",
SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.score),0) AS "平均分",
IFNULL(SUM(sc.score),0) AS "總分"
FROM student s
LEFT OUTER JOIN sc sc ON s.sid=sc.sid
LEFT OUTER JOIN course c ON sc.cid=c.cid
GROUP BY s.sid, s.sname
ORDER BY IFNULL(SUM(sc.score),0) DESC
LIMIT 1,3


（18）查詢學生平均成績及其名次

(非本人)
SELECT 1+(SELECT COUNT( distinct 平均成績)
FROM (
SELECT sid,AVG(score) AS 平均成績
FROM sc
GROUP BY sid ) AS T1
WHERE 平均成績 > T2.平均成績) as 名次, S# as 學生學號,平均成績
FROM (SELECT sid,AVG(score) 平均成績
FROM sc GROUP BY sid ) AS T2
ORDER BY 平均成績 desc


原文鏈接：https://blog.csdn.net/PGY0000/article/details/83002561

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 乾貨，不小心執行了rm -f，除了跑路，如何恢復？https://www.cnblogs.com/justmine/p/10359186.html
    前言 每當我們在生產環境伺服器上執行rm命令時，總是提心吊膽的，因為一不小心執行了誤刪，然後就要準備跑路了，畢竟人不是機器，更何況機器也有bug，呵呵。 那麼如果真的刪除了不該刪除的文件，比如資料庫、日誌或執行文件，咋辦呢？欲知後事如何，請仔細看完本篇博客。 模擬場景 1. 刪除 誤刪除伺服器目錄/ ...
  • Mysql 單表查詢-排序-分頁-group by初識
    Mysql 單表查詢 排序 分頁 group by初識 對於select 來說, 分組聚合(((group by; aggregation), 排序 ( order by ), 分頁查詢 ( limit ), 等這些操作, 都是結合 where 過濾(算術表達式, 邏輯表達式, 判空, 範圍過濾, ...
  • Redis AOF 持久化詳解
    AOF( append only file )持久化以獨立日誌的方式記錄每次寫命令，併在 Redis 重啟時在重新執行 AOF 文件中的命令以達到恢複數據的目的。AOF 的主要作用是解決數據持久化的實時性 ...
  • SQL 增、刪、改、查語句
    1、SQL SELECT 語句 SELECT語句用於從表中選取數據。 結果被存儲在一個結果表中（稱為結果集）。 SQL SELECT語法 以及 註釋：SQL語句對大小寫不敏感。SELECT等效於select。 SELECT 之後跟“列名稱”只顯示查詢列，如果是“*”號那麼查詢的是所有列 2、SQL ...
  • SQL 去重 DISTINCT 語法
    SQL SELECT DISTINCT語句 在表中可能會包含重覆值。這並不成問題, 不過有時你也許希望僅僅列出不同（distinct）的值。 關鍵詞DISTINCT 用於返回唯一不同的值 語法 使用DISTINCT關鍵詞 如果要從Company列中選取所有的值，我們需要使用SELECT語句： “Or ...
  • Zookeeper工作過程詳解
    一、Zookeeper工作機制 分散式和集中式系統相比，有很多優勢，比如更強的計算能力，存儲能力，避免單點故障等問題。但是由於在分散式部署的方式遇到網路故障等問題的時候怎麼保證各個節點數據的一致性和可用性是比較關鍵的問題。 那麼，對於分散式集群來說，我們需要一個能夠在各個服務和節點之間進行協調和服務 ...
  • LeetCode——Duplicate Emails（使用group by以及having解決分組統計結果）
    題意：查找表中重覆的 . 此題是很典型的對分組結果進行統計篩選例題,因此可以利用 進行分組，然後使用 統計. 此處，對 與`group by`進行比較（引用自：）： 後不能跟聚合函數，因為 執行順序大於聚合函數。 子句的作用是在對查詢結果進行分組前，將不符合 條件的行去掉，即在分組之前過濾數據，條件 ...
  • 大數據量高併發的資料庫優化,sql查詢優化
    一、資料庫結構的設計 如果不能設計一個合理的資料庫模型，不僅會增加客戶端和伺服器段程式的編程和維護的難度，而且將會影響系統實際運行的性能。所以，在一個系統開始實施之前，完備的資料庫模型的設計是必須的。 在一個系統分析、設計階段，因為數據量較小，負荷較低。我們往往只註意到功能的實現，而很難註意到性能的 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類