Summary of MySQL's advanced queries (2)

Knowledge points: EXISTS subquery, NOT EXISTS subquery, paging query, UNION joint query

1. Word part

①exist exists ②temp temporary ③district area

④content ⑤temporary

2. Preview part

1. Can table joins be replaced with subqueries?

Yes

2. Detect a certain Whether the column exists in a certain range and what keywords can be used in the subquery

EXISTS

3. Which sql statements can nest subqueries

More complex data query statements When data from multiple tables is needed

The subquery can appear where any expression appears

3. Exercise part

1. Query S2 student test score information

#Get on the computer 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. Make student transcript

#On the computer 2
SELECT `studentName` AS name, `gradeName` AS grade of course, `subjectName` AS course name, `exameDate` AS exam date 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. Statistics of the number of students who should have attended the latest exam of the Logic Java course, the actual number of students who attended and the number of absentees

Extract the results to the temporary table

#On the computer 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 of people who should be present
SELECT COUNT(*) AS number of people who should be present FROM student
WHERE `gradeID`=(SELECT ` gradeID` FROM `subject` WHERE `subjectName`='Logic Java');
#number of actual arrivals
SELECT COUNT(*) AS actual number of arrivals 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');
#Number of absentees
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 Number of students absent;

/*select studentName,student.studentNo,studentResult
from student,result
where student.`studentNo`=result.`studentNo`*/
# Add to table
DROP TABLE IF EXISTS tempResult;
CREATE TABLE tempResult(
SELECT studentName,student.studentNo,studentResult
FROM student,result
WHERE student.`studentNo`=result.` studentNo`
)

4. Paging query displays rental house information

#On the computer 4
DROP DATABASE IF EXISTS `house`;

CREATE DATABASE house ;
USE house;
#Customer information table
DROP TABLE IF EXISTS `sys_user`;

CREATE TABLE `sys_user`(

`uid` INT(4) NOT NULL COMMENT 'Customer number' AUTO_INCREMENT PRIMARY KEY,

`uName` VARCHAR(50) COMMENT 'Customer name',

`uPassWord` VARCHAR(50) COMMENT 'Customer password'
);

#District and County Information Table
DROP TABLE IF EXISTS `hos_district`;

CREATE TABLE `hos_district`(

`did` INT (4) NOT NULL COMMENT 'District and county number' AUTO_INCREMENT PRIMARY KEY,

`dName` VARCHAR(50) NOT NULL COMMENT 'District and county name'
);

#Street information There is a foreign key in the table
DROP TABLE IF EXISTS `hos_street`;

CREATE TABLE `hos_street`(

`sid` INT(4) NOT NULL COMMENT 'street number' AUTO_INCREMENT PRIMARY KEY,

`sName` VARCHAR(50) COMMENT 'Street name',

`sDid` INT(4) NOT NULL COMMENT 'District and county number'
);

#House type table
DROP TABLE IF EXISTS `hos_type`;

CREATE TABLE `hos_type`(

`hTid` INT(4) NOT NULL COMMENT 'House type number' AUTO_INCREMENT PRIMARY KEY,

`htName` VARCHAR(50) NOT NULL COMMENT 'House type name'
);

#Rental house information table
DROP TABLE IF EXISTS `hos_house`;

CREATE TABLE `hos_house`(

`hMid` INT(4) NOT NULL COMMENT 'Rental house number' AUTO_INCREMENT PRIMARY KEY,

`uid` INT(4) NOT NULL COMMENT 'Customer number',

`sid` INT(4) NOT NULL COMMENT 'District and county number',

`hTid` INT (4) NOT NULL COMMENT 'House type number',

`price` DECIMAL NOT NULL COMMENT 'Monthly rent',

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

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

`hTime` TIMESTAMP NOT NULL COMMENT 'Release time' DEFAULT NOW(),

`copy` VARCHAR(255) NOT NULL COMMENT 'Remarks'
);

#Each constraint information

# District and county number foreign key id of street information
ALTER TABLE `hos_street` ADD CONSTRAINT fk_stree_distr
FOREIGN KEY (`sDid`) REFERENCES `hos_district` (`did`);

# Rental house information and the relationship between various tables
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`);

#Default constraints
ALTER TABLE `hos_house` ALTER COLUMN `price` SET DEFAULT 0;

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

#Add information

#User table
INSERT INTO `house`.`sys_user` (`uName`, `uPassWord`) VALUES ('Xiaomo ', '123'),
('Baishun', '123'),
('Lianji', '123'),
('Dongmei', '123');

#District and County Information Table
INSERT INTO `house`.`hos_district` (`dName`) VALUES ('Haidian District'),
('Dongcheng District'),
(' Nancheng District'),
('Xicheng District'),
('Development Zone');

#street information table
INSERT INTO `house`.`hos_street` (`sName `) VALUES ('Wanquan');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('Wanquan', '1');
INSERT INTO `house `.`hos_street` (`sName`, `sDid`) VALUES ('Zhongguan', '3');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES (' Wanjia', '4');
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES ('Haifeng', '5');

#House type Table
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('One bedroom and one living room');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('Two rooms One living room');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('Three bedrooms and one living room');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ( 'Two bedrooms and one bathroom');
INSERT INTO `house`.`hos_type` (`htName`) VALUES ('One bedroom and one bathroom');

#Rental house information table
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (' 1', '1', '1', '530', 'View room', 'Balcony to watch the sea', '2017-7-7', 'Buy as fast as you need');
INSERT INTO `house `.`hos_house` (`uid`, `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES ('2', '2' , '2', '430', 'King bed room', 'Comfortable sleeping', '2017-6-9', 'So comfortable');
INSERT INTO `house`.`hos_house` (`uid` , `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES ('3', '3', '3', '480', 'Double room', 'Hey hey hey', '2016-9-9', 'Do you understand');
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`, `price`, `topic`, `contents`, `hTime`, `copy`)
VALUES ('4', '4', '4', '360', 'Single room', 'Travel essential Select', '2015-8-8', 'Waiting for you to choose');

# on the machine 4
CREATE TEMPORARY TABLE temp_house
(SELECT * FROM `hos_house` LIMIT 2,2 );
SELECT * FROM temp_house;

5. Query the rental house information published by the specified customer

#Go to the computer 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` 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='大 desert ');

6. Make a house rental list by district and county

#Go to the computer 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. Make a house rental list by district and county

#上机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有点陌生其它没什么。。。。。

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

The above is the detailed content of Summary of MySQL's advanced queries (2). For more information, please follow other related articles on the PHP Chinese website!