mysql 高级查询总结_总结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 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有点陌生其它没什么。。。。。

欢迎提问，欢迎指错，欢迎讨论学习信息 有需要的私聊 发布评论即可 都能回复的