accp8.0轉換教材第4章MySQL高級查詢(二)理解與練習

来源:http://www.cnblogs.com/a782126844/archive/2017/06/29/7095581.html
-Advertisement-
Play Games

知識點：EXISTS子查詢、NOT EXISTS子查詢、分頁查詢、UNION聯合查詢 一.單詞部分 ①exist存在②temp臨時的③district區域 ④content內容⑤temporary暫時的 二.預習部分 1.表連接都可以用子查詢替換嗎 是的 2.檢測某列是否存在某個範圍可以在子查詢中使 ...


知識點：EXISTS子查詢、NOT EXISTS子查詢、分頁查詢、UNION聯合查詢

一.單詞部分

①exist存在②temp臨時的③district區域

④content內容⑤temporary暫時的

二.預習部分

1.表連接都可以用子查詢替換嗎

是的

2.檢測某列是否存在某個範圍可以在子查詢中使用什麼關鍵字

EXISTS

3.哪些sql語句可以嵌套子查詢

較複雜的數據查詢語句  需要多個表的數據的時候

子查詢可以出現在任何表達式出現的位置

三.練習部分

1.查詢S2學員考試成績信息

#上機1
SELECT `studentNo`,`subjectNo`,`studentResult`,`exameDate` FROM `result`
WHERE EXISTS(SELECT `studentNo` FROM `student` WHERE gradeId=2)
AND studentNo IN(SELECT `studentNo` FROM `student` WHERE gradeId=2)

2.製作學生成績單

#上機2
SELECT `studentName` AS 姓名,`gradeName` AS 課程所屬年級,`subjectName` AS 課程名稱,`exameDate` AS 考試日期 FROM (
SELECT `studentName`,`gradeName`,`subjectName`,`exameDate` FROM `grade` AS gr,`result` AS re,`student` AS stu,`subject` AS sub
WHERE gr.`gradeID`=stu.`gradeID` AND re.`studentNo`=stu.`studentNo`
AND re.`subjectNo`=sub.`subjectNo`
) AS tempt;

3.統計Logic Java課程最近一次考試學生應到人數，實到人數和缺考人數

提取結果到臨時表


#上機3
#select subjectNo from `subject` where `subjectName`='Logic Java';

#select max(`exameDate`) from result inner join `subject` on `result`.`subjectNo`=`subject`.`subjectNo`
#where `subjectName`='Logic Java';

#select `gradeID` from `subject` where `subjectName`='Logic Java';
#應到人數
SELECT COUNT(*) AS 應到人數 FROM student
WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE `subjectName`='Logic Java');
#實到人數
SELECT COUNT(*) AS 實到人數 FROM result
WHERE `subjectNo`=(SELECT subjectNo FROM `subject` WHERE `subjectName`='Logic Java')
AND exameDate=(SELECT MAX(`exameDate`) FROM result INNER JOIN `subject` ON `result`.`subjectNo`=`subject`.`subjectNo`
WHERE `subjectName`='Logic Java');
#缺考人數
SELECT((SELECT COUNT(*) FROM student
WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE `subjectName`='Logic Java'))-
(SELECT COUNT(*) FROM result
WHERE `subjectNo`=(SELECT subjectNo FROM `subject` WHERE `subjectName`='Logic Java')
AND exameDate=(SELECT MAX(`exameDate`) FROM result INNER JOIN `subject` ON `result`.`subjectNo`=`subject`.`subjectNo`
WHERE `subjectName`='Logic Java'))) AS 缺考人數;

/*select studentName,student.studentNo,studentResult
from student,result
where student.`studentNo`=result.`studentNo`*/
#添加到表
DROP TABLE IF EXISTS tempResult;
CREATE TABLE tempResult(
SELECT studentName,student.studentNo,studentResult
FROM student,result
WHERE student.`studentNo`=result.`studentNo`
)

4.分頁查詢顯示出租房屋信息

#上機4
DROP DATABASE IF EXISTS `house`;

CREATE DATABASE house;
USE house;
#客戶信息表
DROP TABLE IF EXISTS `sys_user`;

CREATE TABLE `sys_user`(

`uid` INT(4) NOT NULL COMMENT '客戶編號' AUTO_INCREMENT PRIMARY KEY,

`uName` VARCHAR(50) COMMENT '客戶姓名',

`uPassWord` VARCHAR(50) COMMENT '客戶密碼'
);


#區縣信息表
DROP TABLE IF EXISTS `hos_district`;

CREATE TABLE `hos_district`(

`did` INT(4) NOT NULL COMMENT '區縣編號' AUTO_INCREMENT PRIMARY KEY,

`dName` VARCHAR(50) NOT NULL COMMENT '區縣名稱'
);

#街道信息表這裡有一個外鍵
DROP TABLE IF EXISTS `hos_street`;

CREATE TABLE `hos_street`(

`sid` INT(4) NOT NULL COMMENT '街道編號' AUTO_INCREMENT PRIMARY KEY,

`sName` VARCHAR(50) COMMENT '街道名稱',

`sDid` INT(4) NOT NULL COMMENT '區縣編號'
);


#房屋類型表
DROP TABLE IF EXISTS `hos_type`;

CREATE TABLE `hos_type`(

`hTid` INT(4) NOT NULL COMMENT '房屋類型編號' AUTO_INCREMENT PRIMARY KEY,

`htName` VARCHAR(50) NOT NULL COMMENT '房屋類型名稱'
);


#出租房屋信息表
DROP TABLE IF EXISTS `hos_house`;

CREATE TABLE `hos_house`(

`hMid` INT(4) NOT NULL COMMENT '出租房屋編號' AUTO_INCREMENT PRIMARY KEY,

`uid` INT(4) NOT NULL COMMENT '客戶編號',

`sid` INT(4) NOT NULL COMMENT '區縣編號',

`hTid` INT(4) NOT NULL COMMENT '房屋類型編號',

`price` DECIMAL NOT NULL COMMENT '每月租金',

`topic` VARCHAR(50) NOT NULL COMMENT '標題',

`contents` VARCHAR(255) NOT NULL COMMENT '描述',

`hTime` TIMESTAMP NOT NULL COMMENT '發佈時間' DEFAULT NOW(),

`copy` VARCHAR(255) NOT NULL COMMENT '備註'
);

#各個約束信息

#街道信息的區縣編號外鍵id
ALTER TABLE `hos_street` ADD CONSTRAINT fk_stree_distr
FOREIGN KEY (`sDid`) REFERENCES `hos_district` (`did`);


#出租屋信息和各個表的聯繫
ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_user
FOREIGN KEY (`uid`) REFERENCES `sys_user` (`uid`);

ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_street
FOREIGN KEY (`sid`) REFERENCES `hos_street` (`sid`);

ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_type
FOREIGN KEY (`hTid`) REFERENCES `hos_type` (`hTid`);

#預設約束
ALTER TABLE `hos_house` ALTER COLUMN `price` SET DEFAULT 0;

#ALTER TABLE `hos_house` ALTER COLUMN `hTime` SET DEFAULT now();

#添加信息

#用戶表
INSERT INTO `house`.`sys_user` (`uName`, `uPassWord`) VALUES ('小漠', '123'),
('百順', '123'),
('練基', '123'),
('冬梅', '123');

#區縣信息表
INSERT INTO `house`.`hos_district` (`dName`) VALUES ('海澱區'),
('東城區'),
('南城區'),
('西城區'),
('開發區');

#街道信息表
INSERT INTO `house`.`hos_street` (`sName`) VALUES ('萬泉');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('萬泉', '1');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('中關', '3');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('萬嘉', '4');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('海風', '5');

#房屋類型表
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('一室一廳');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('兩室一廳');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('三室一廳');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('兩室一衛');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('一室一衛');


#出租房屋信息表
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES ('1', '1', '1', '530', '觀景房', '陽臺觀賞大海', '2017-7-7', '需要的速度買');
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES ('2', '2', '2', '430', '大床房', '舒服睡覺', '2017-6-9', '好舒服');
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES ('3', '3', '3', '480', '雙人房', '嘿嘿嘿', '2016-9-9', '懂不懂');
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES ('4', '4', '4', '360', '單人房', '旅行必選', '2015-8-8', '等你來選');

#上機4
CREATE TEMPORARY TABLE temp_house
(SELECT * FROM `hos_house` LIMIT 2,2);
SELECT * FROM temp_house;

5.查詢指定客戶發佈的出租屋信息

#上機5
#select `uid` from `sys_user` where uName='大漠';
SELECT `dName`,`sName`,hou.`hTid`,`price`,`topic`,`contents`,`hTime`,`copy`
FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street` AS str,`sys_user` AS us,`hos_type` AS ty
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`
AND hou.`uid`=(SELECT `uid` FROM `sys_user` WHERE uName='大漠');

6.按區縣製作房屋出租清單

#上機6
/*select sid from `hos_house` group by sid having count(sid)>1;
select `sDid` from `hos_street`
where sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING COUNT(sid)>1);
select `dName` from `hos_district` where `did`=(SELECT `sDid` FROM `hos_street`
WHERE sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING COUNT(sid)>1));*/
SELECT `htName`,`uName`,`dName`,`sName`
FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street` AS str,`sys_user` AS us,`hos_type` AS ty
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`
AND hou.sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING COUNT(sid)>1);

7.按區縣製作房屋出租清單

#上機7 QUARTER(NOW())獲取季度
/*FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street` AS str,`sys_user` AS us,`hos_type` AS ty
GROUP BY hou.`hMid`
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`*/


SELECT QQ AS '季度',dist.`dName` AS '區縣',str.`sName` AS '街道',
ty.`htName` AS '戶型',CNT AS '房屋數量'
FROM
(
SELECT QUARTER(`hTime`) AS QQ,`sid` AS SI,`hTid` AS HT,COUNT(*) CNT
FROM `hos_house` AS hou
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`),`sid`,`hTid`
) AS temp,`hos_district` dist,`hos_street` AS str,`hos_type` AS ty,`hos_house` AS hou
WHERE hou.`hTid`=ty.`hTid` AND hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`

UNION

SELECT QUARTER(`hTime`),`hos_district`.`dName`,' 小計 ',' ',COUNT(*) AS '房屋數量'
FROM `hos_house`
INNER JOIN `hos_street` ON(hos_house.`sid`=hos_street.`sid`)
INNER JOIN hos_district ON(hos_street.`sDid`=hos_district.`did`)
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`),hos_district.`dName`
UNION

SELECT QUARTER(`hTime`),' 合計 ',' ',' ',COUNT(*) AS '房屋數量'
FROM hos_house
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`)
ORDER BY 1,2,3,4

五.總結

UNION有點陌生其它沒什麼。。。。。

歡迎提問，歡迎指錯，歡迎討論學習信息 有需要的私聊 發佈評論即可 都能回覆的

  原文在博客園有需要可以聯繫扣扣：2265682997


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

-Advertisement-
Play Games
更多相關文章
  • 玩轉UITableView系列（一）--- 解耦封裝、簡化代碼、適者生存！
    UITableView這個iOS開發中永遠繞不開的UIView，那麼就不可避免的要在多個頁面多種場景下反覆摩擦UITableView，就算是剛跳進火坑不久的iOS Developer也知道實現UITableView的數據源dataSource和代理delegate，寫出一個UITableView也就 ...
  • 【代碼筆記】iOS-獲得現在的時間
    一， 代碼。 - (void)viewDidLoad { [super viewDidLoad]; // Do any additional setup after loading the view, typically from a nib. //獲得現在的時間 [self currentTime ...
  • MySQL 5.7--------SSL連接最佳實戰
    MySQL 5.7 SSL連接最佳實戰 1. 背景 * 在生產環境下，安全總是無法忽視的問題，資料庫安全則是重中之重，因為所有的數據都存放在資料庫中 * 當使用非加密方式連接MySQL資料庫時，在網路中傳輸的所有信息都是明文的，可以被網路中所有人截取，敏感信息可能被泄露。在傳送敏感信息（如密碼）時， ...
  • Mysql優化之一（優化的目的以及方向）
    1.資料庫優化的目的 2.資料庫優化的方向 ...
  • mongodb表結構設計
    參考：http://www.cnblogs.com/WeiGe/p/4903850.html 一對多分為關聯模式和內嵌模式 內嵌與關聯什麼時候用： 存在雙向查詢則用關聯，還需用到索引。 當兩者是包含關係，並且被包含的對象不會經常的變化，並不會進行雙向查詢，被包含對象不會進行其他的關聯查 則用到內嵌模 ...
  • mac 安裝mysql忘記初始密碼解決辦法
    http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html親測方法3，已成功重置密碼。（感謝@非常，告訴我官網就有重置方法，網上搜了一堆堆...另外，step1和2來源於http://www.2cto.com/database/ ...
  • 筆記：Oracle查詢重覆數據並刪除，只保留一條記錄
    1、查找表中多餘的重覆記錄，重覆記錄是根據單個欄位（Id）來判斷 select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1) 2、刪除表中多餘的重覆記錄，重覆記錄是根據單個欄位（Id）來判斷，只留有ro ...
  • 資料庫表設計--備份記錄的表設計優化
    ## ## 需求場景： 由於MySQL沒有類似於SQL SERVER那樣的系統表來存放備份記錄，且大規模的MySQL伺服器需要集中管理和查看。 伺服器出現性能問題或複製延遲時，需要先判斷是否由數據備份引起。 ## ## 第一版 按照需求，考慮到需要記錄的備份信息有備份伺服器信息、備份開始結束時間、備 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類