總結MySQL的高階查詢(二)

知識點：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 grade=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';

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`.`subject``subject`.
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`

)

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 '客戶密碼'
#`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``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 (`hos_house` ADD CONSTRAINT fk_house_type

FOREIGN KEY (`hTid`ho) REFER ` (`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_dist`ere `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`)
按季度分組(`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`)
哪裡季度(`hTime`)
按季度分組(`hTime`),hos_district.`dName`
UNION

選擇季度(` hTime`) `),'總計','  ','  ',COUNT(*) AS '房屋數量'
FROM hos_house
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime` )
ORDER BY 1,2,3,4

#五。總結

UNION有點陌生什麼沒什麼。學習資訊有的私聊發佈評論即可需要回覆的

以上是總結MySQL的高階查詢(二)的詳細內容。更多資訊請關注PHP中文網其他相關文章！