多條件查詢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
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類