多條件查詢SQL語句

来源:https://www.cnblogs.com/songbao/archive/2019/07/09/11158786.html
-Advertisement-
Play Games

表結構如下： –1.學生表 Student(s_id,s_name,s_birth,s_sex) –學生編號,學生姓名, 出生年月,學生性別 –2.課程表 Course(c_id,c_name,t_id) – –課程編號, 課程名稱, 教師編號 –3.教師表 Teacher(t_id,t_name) ...


表結構如下：

–1.學生表

Student(s_id,s_name,s_birth,s_sex) –學生編號,學生姓名, 出生年月,學生性別

–2.課程表

Course(c_id,c_name,t_id) – –課程編號, 課程名稱, 教師編號

–3.教師表

Teacher(t_id,t_name) –教師編號,教師姓名

–4.成績表

Score(s_id,c_id,s_score) –學生編號,課程編號,分數

--建表SQL

--學生表

CREATE TABLE `Student`(

    `s_id` VARCHAR(20),

    `s_name` VARCHAR(20) NOT NULL DEFAULT '',

    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',

    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',

    PRIMARY KEY(`s_id`)

);

--課程表

CREATE TABLE `Course`(

    `c_id`  VARCHAR(20),

    `c_name` VARCHAR(20) NOT NULL DEFAULT '',

    `t_id` VARCHAR(20) NOT NULL,

    PRIMARY KEY(`c_id`)

);

--教師表

CREATE TABLE `Teacher`(

    `t_id` VARCHAR(20),

    `t_name` VARCHAR(20) NOT NULL DEFAULT '',

    PRIMARY KEY(`t_id`)

);

--成績表

CREATE TABLE `Score`(

    `s_id` VARCHAR(20),

    `c_id`  VARCHAR(20),

    `s_score` INT(3),

    PRIMARY KEY(`s_id`,`c_id`)

);

--插入學生表測試數據

insert into Student values('01' , '趙雷' , '1990-01-01' , '男');

insert into Student values('02' , '錢電' , '1990-12-21' , '男');

insert into Student values('03' , '孫風' , '1990-05-20' , '男');

insert into Student values('04' , '李雲' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');

insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--課程表測試數據

insert into Course values('01' , '語文' , '02');

insert into Course values('02' , '數學' , '01');

insert into Course values('03' , '英語' , '03');

 

--教師表測試數據

insert into Teacher values('01' , '張三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

 

--成績表測試數據

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

 

（一）評分標準：

  1. 學習每門課程的學生人數。(3分)

select COUNT(c_name),c_name from course join score on course.c_id=score .c_id join student on score.s_id=student.s_id

 

 

  1. 查詢1990年出生的學生信息。(3分)

select * from student WHERE s_birth=1990

 

 

  1. 查詢同名同姓且同性別的信息及各自的數量。(3分)

select COUNT(s_name&&s_sex),s_name,s_sex from student GROUP BY s_name,s_sex

 

  1. 查詢名字中有‘風’字的學生信息。(3分)

select * from student WHERE s_name LIKE '%風'

 

 

  1. 查詢出男生及女生的數量。(3分)

select COUNT(s_sex) from student GROUP BY s_sex='男',s_sex='女'

 

  1. 檢索"01"課程分數小於60，按分數降序排列的學生信息。（5分）

select t2.* from score t1,student t2 where t2.s_id = t1.s_id and t1.s_score < '60' and t1.c_id = '01' order by t1.s_score;

 

  1. 查詢"01"課程比"02"課程成績低的學生的信息及課程分數。（5分）

select s1.*,s2.01_score,s2.02_score from student s1,

(select t1.s_id as s_id,t1.s_score

as 01_score,t2.s_score

as 02_score from score t1,score t2

where t1.s_id = t2.s_id

and t1.c_id = '01'

and t2.c_id = '02'

and t1.s_score > t2.s_score ) s2

where s1.s_id = s2.s_id;

 

  1. 查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績。（5分）

select t1.s_id,t2.s_name,avg(t1.s_score) as avg_score from score t1

left join student t2 on t1.s_id = t2.s_id

group by t1.s_id

having avg(t1.s_score) >= 60

 

  1. 查詢學過"張三"老師授課的同學的信息。（5分）

select * from student JOIN score on student.s_id=score.s_id

JOIN course on score.c_id=course.c_id

JOIN teacher on course.t_id=teacher.t_id

WHERE t_name='張三'

 

  1. 查詢至少有一門沒有考試的學生信息。（5分）

select s_score,s_name,c_name from score JOIN student on student.s_id=score.s_id

JOIN course on course.c_id=score.c_id

WHERE s_score=0

 

  1. 查詢所有學生的出生日期及年齡。（5分）

select (YEAR(CURDATE())-YEAR(s_birth)) as age,s_name FROM student

 

  1. 查詢半月過生日的學生信息。（5分）

select *,DATE_FORMAT(s_birth,"%d") as day from student as ab inner join score as bb on ab.s_id=bb.s_id join course as cb on bb.c_id=cb.c_id join teacher as db on cb.t_id=db.t_id having day < 15

 

  1. 績。（10分）

select c_name,student.s_id,s_name,s_score from course JOIN score on course.c_id=score.c_id

JOIN student on student.s_id=score.s_id

WHERE s_score<60

 

  1. 查詢各科成績最高分、最低分和平均分：（10分）

以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

--及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

select

    a.c_id,

    b.c_name,

    max(s_score),

    min(s_score),

    round(avg(s_score), 2),

    round(100 *(sum(case when a.s_score >= 60 then 1 else 0 end)/ sum(case when a.s_score then 1 else 0 end)), 2) as 及格率,

    round(100 *(sum(case when a.s_score >= 70 and a.s_score <= 80 then 1 else 0 end)/ sum(case when a.s_score then 1 else 0 end)), 2) as 中等率,

    round(100 *(sum(case when a.s_score >= 80 and a.s_score <= 90 then 1 else 0 end)/ sum(case when a.s_score then 1 else 0 end)), 2) as 優良率,

    round(100 *(sum(case when a.s_score >= 90 then 1 else 0 end)/ sum(case when a.s_score then 1 else 0 end)), 2) as 優秀率

from

    score a

left join course b on

    a.c_id = b.c_id

group by

    a.c_id,

    b.c_name

 

  1. 查詢學生的總成績併進行排名（高到低），顯示排名數、姓名、性別、學號、總分。（10分）

select s_name,s_sex,student.s_id,SUM(s_score) from course JOIN score on course.c_id=score.c_id

JOIN student on score.s_id=student.s_id GROUP BY s_name

ORDER BY  SUM(s_score) DESC

 

  1. 統計各科成績各分數段人數：課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比。（10分）

select course.c_name, course.c_id,

sum(case when score.s_score<=100 and score.s_score>85 then 1 else 0 end) as "[100-85]",

sum(case when score.s_score<=85 and score.s_score>70 then 1 else 0 end) as "[85-70]",

sum(case when score.s_score<=70 and score.s_score>60 then 1 else 0 end) as "[70-60]",

sum(case when score.s_score<=60 and score.s_score>0 then 1 else 0 end) as "[60-0]"

from score left join course

on score.c_id = course.c_id

group by score.c_id;

 

  1. 查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績。（10分）

select t5.*,t4.s_score as MAX_score,t4.c_id,t6.c_name

from (score t4,student t5)

right join course t6 on t6.c_id = t4.c_id

where t4.s_score = (select max(t3.s_score)

from teacher t1,course t2,score t3

where t1.t_name = '張三' and t1.t_id = t2.t_id and t2.c_id = t3.c_id)and t4.s_id = t5.s_id

 


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

-Advertisement-
Play Games
更多相關文章
  • MySql-8.0.16版本部分安裝問題修正
    本帖參考網站<https://blog.csdn.net/lx318/article/details/82686925>的安裝步驟，並對8.0.16版本的部分安裝問題進行修正 在MySQL 8.0.16版本中安裝可能會出現部分錯誤提示已經不使用“UTF8B3”而是使用了“UTF8B4” #///// ...
  • 資料庫基本概念（四）
    二、資料庫設計 資料庫設計是將數據對象轉換為數據表等資料庫對象的過程，是資料庫應用系統開發過程中首要的和基本的內容。 按照規範的設計方法，考慮資料庫及其應用系統開發全過程，將關係資料庫的設計分為六個階段：需求分析、概念結構設計、邏輯結構設計、物理結構設計、資料庫實施和數控運行和維護。其中需求分析的任 ...
  • python之mysqldb模塊安裝
    之所以會寫下這篇日誌，是因為安裝的過程有點虐心。目前這篇文章是針對windows操作系統上的mysqldb的安裝。安裝python的mysqldb模塊，首先當然是找一些官方的網站去下載：https://pypi.python.org/pypi/MySQL-python。下載後，cmd進入MySQL- ...
  • MySQL資料庫筆記四：MySQL的約束
    <1>概念 <2>使用場景 <3>分類 1.default 2.not null 3.unique 4.主鍵約束 5.auto_increment:自增長約束 6.unsigned：無符號約束 7.zerofill：零填充約束 8.外鍵約束 總結：資料庫的約束 ...
  • mssql sqlserver if exists 用法大彙總
    摘要: 下文講述sqlserver中，更新腳本中常用if exists關鍵字的用法說明，如下所示: 實驗環境:sql server 2008 R2 一、檢測資料庫是否存在於當前資料庫引擎下 二、檢測數據表是否存在於指定資料庫下 三、檢測存儲過程是否存在的方法 四、臨時表是否存在的方法 五、視圖是否存 ...
  • Java簡單介紹運行機制
    1. 基礎知識點圖解 編程語言核心結構：變數、基本語法、分支、迴圈、數組、…… Java面向對象的核心邏輯：OOP、封裝、繼承、多態、介面…… 開發Java SE高級應用程式：異常、集合、I/O、多線程、反射機制、網路編程…… 2. 各種語言對比 3. Java誕生 Sun公司（斯坦福大學網路，不是 ...
  • 博客園隨筆初體驗（MySQL+MySQL Workbench+Eclipse+JDBC）
    寫作於：2019-07-09 關鍵字：1.MySQL、 2.MySQL Workbench、 3.eclipse、 4.JDBC連接測試 1.MySQL 一、資源下載 三步下載： 第一步：選擇安裝程式頁面（https://dev.mysql.com/downloads/installer/） 第二步 ...
  • 連接池技術
    連接池技術 連接池技術的核心思想是：連接復用，通過建立一個資料庫連接池以及一套連接使用、分配、管理策略，使得該連接池中的連接可以得到高效、安全的復用，避免了資料庫連接頻繁建立、關閉的開銷。另外，由於對JDBC中的原始連接進行了封裝，從而方便了資料庫應用對於連接的使用（特別是對於事務處理），提高了獲取 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類