MySQL の高度なクエリの概要 (2)

知識ポイント: EXISTS サブクエリ、NOT EXISTS サブクエリ、ページング クエリ、UNION 結合クエリ

1. ワード部分

①exist 存在する ②一時一時領域

④コンテンツコンテンツ ⑤一時一時領域

2. プレビュー部分

1 .テーブル結合可能サブクエリに置き換えることができます

はい

2. 特定の範囲に列が存在するかどうかを検出するためにサブクエリで使用できるキーワード

EXISTS

3. サブクエリをネストできる SQL ステートメント

データが複数のテーブルからのクエリが必要です

サブクエリは式が出現する場所に出現できます

1. S2 学生テストのスコア情報をクエリします

#コンピュータにアクセスします 1

`studentNo`、`subjectNo`、`studentResult`を選択します,`exameDate` FROM `result`

WHERE EXISTS(SELECT `studentNo` FROM `student` WHERE GradeId=2)
AND St​​udentNo 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 一時的;

3. 統計ロジック 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';

#Number出席すべき人の数

SELECT COUNT(*) AS 出席すべき人の数 FROM Student
WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE `subjectName`='Logic Java') ;
#実際の到着数
SELECT COUNT(*) AS 実際の到着数 FROM 結果
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 欠席者数;

/*selectstudentName,student.studentNo,studentResult

from students,result

where students.`studentNo`=result.`studentNo`*/
# 追加to Drop Table If Exist Tempresult;
Create Table Tempresult t.`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 'House type 番号',

`価格` DECIMAL NOT NULL コメント '月額家賃',

`topic` VARCHAR(50) NOT NULL COMMENT 'title',

`contents` VARCHAR(255) NOT NULL COMMENT 'description',

`hTime` TIMESTAMP NOT NULL COMMENT 'Publish time' 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` ) 参考文献` 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 ('Xiaomo', '123'),
('Baishun', '123'),
('Lianji', '123'),
('Dongmei', '123');

#地区および県の情報テーブル
INSERT INTO `house`.`hos_district` (`dName`) VALUES ('Haidian District ' ),
('東城区'),
('南城区'),
('西城区'),
('開発区');

#ストリート情報テーブル
INSERT INTO `house`.`hos_street ` (`sName`) VALUES ('万春');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('万春', '1'); .`hos_street` (`sName`, `sDid`) VALUES ('中関', '3');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('Wanjia ', ' 4');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('海风', '5');

#ハウスタイプテーブル

INSERT INTO `hos_type` (`htName`) VALUES ('1 つのベッドルームと 1 つのリビング ルーム');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('2 つのベッドルームと 1 つのリビング ルーム'); . `hos_type` (`htName`) VALUES ('3 つのベッドルームと 1 つのリビングルーム');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('2 つのベッドルームと 1 つのバスルーム'); `house`.` hos_type` (`htName`) VALUES ('1 ベッドルーム、1 バスルーム');


#賃貸住宅情報テーブル

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` 、`トピック`、`コンテンツ`、`hTime`、`コピー`)
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
一時作成TABLE temp_house
(SELECT * FROM `hos_house` LIMIT 2 ,2);

SELECT * FROM temp_house;

5. 指定された顧客が投稿した賃貸住宅情報をクエリします

#上机5

#select `uid` from ` sys_user` where uName='Desert';

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` および hou.`hTid`=ty.`hTid` AND hou.`sid`=str.`sid` AND str .`sDid`=dist.`did`
AND hou.`uid`=(SELECT `uid` FROM `sys_user` WHERE uName='Desert');

6. 地区と郡ごとに住宅賃貸リストを作成します

#Up Machine 6

/*sid が count(sid)>1 であることによって `hos_house` グループから sid を選択します;

`hos_street` から `sDid` を選択します

ここで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 には、他のものは何も発生しません。

以上がMySQL の高度なクエリの概要 (2)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。