(1) In the SQL language, a select-from-where statement is called a query block, and a query block is nested in where or having of another query block, which is called nested query.
(2) Creating three data tables
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) DEFAULT NULL,
`sex` varchar(45) DEFAULT NULL,
`address` varchar(45) DEFAULT NULL,
`number` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `person` VALUES ('1', 'Kai AI', 'male', 'Changsha', '123');
INSERT INTO `person` VALUES ('2', 'Kobe', 'male', 'Beijing', '234');
INSERT INTO `person` VALUES ('3', 'Xiaoli', 'female', 'Wuhan', '345');
INSERT INTO `person` VALUES ('4', 'Xiao Wu ', 'male', 'Shenzhen', '456');
INSERT INTO `person` VALUES ('5', 'Zhang Yi Feng', 'male', 'Nanjing', '567');
INSERT INTO `person` VALUES ('6', 'He Zhizhang', 'male', 'Shanghai', '789');
INSERT INTO `person` VALUES ('7', 'Zhang Yishan', 'male', 'Beijing', '987');
INSERT INTO `person` VALUES ('8', 'Zhao Liying', 'female', 'Shanghai', '876');
INSERT INTO `person` VALUES ('9', 'Providence', 'male', '', null);
CREATE TABLE `phone` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`person_id` int(11) DEFAULT NULL,
`phone_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `person_id` (`person_id`),
CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
INSERT INTO `phone` VALUES ('12', '1', 'IPhone');
INSERT INTO `phone` VALUES ('13', '2', 'Meizu mobile phone');
INSERT INTO `phone` VALUES ('14', '3', 'HUAWEI mobile phone');
INSERT INTO `phone` VALUES ('15', '1', 'Samsung mobile phone');
INSERT INTO `phone` VALUES ('16', '4', 'Meizu mobile phone');
INSERT INTO `phone` VALUES ('17', '5', 'zte');
INSERT INTO `phone` VALUES ('18', '6', 'htc');
INSERT INTO `phone` VALUES ('19', '4', 'IPhone');
INSERT INTO `phone` VALUES ('20', '3', 'Samsung mobile phone');
INSERT INTO `phone` VALUES ('21', '7', 'Amphilochia');
INSERT INTO `phone` VALUES ('22', '8', 'Millet 4');
INSERT INTO `phone` VALUES ('23', '4', 'Meizu mobile phone');
CREATE TABLE `phone_info` (
`info_id` int(11) NOT NULL AUTO_INCREMENT,
`phone_id` int(11) DEFAULT NULL,
`phone_price` float DEFAULT NULL,
`phone_color` varchar(255) DEFAULT NULL,
PRIMARY KEY (`info_id`),
KEY `phone_id` (`phone_id`),
CONSTRAINT `phone_info_ibfk_1` FOREIGN KEY (`phone_id`) REFERENCES `phone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1008 DEFAULT CHARSET=utf8;
INSERT INTO `phone_info` VALUES ('1001', '12', '4999', 'black');
INSERT INTO `phone_info` VALUES ('1002', '13', '5288', 'Luxury gold color');
INSERT INTO `phone_info` VALUES ('1003', '14', '3588', 'silvery');
INSERT INTO `phone_info` VALUES ('1004', '15', '6288', 'Golden');
INSERT INTO `phone_info` VALUES ('1005', '16', '2998', 'black');
INSERT INTO `phone_info` VALUES ('1006', '17', '1999', 'silvery');
INSERT INTO `phone_info` VALUES ('1007', '18', '4999', 'gules');
(three) demonstration
1. Data from three tables
mysql> select*from person;
+----+--------+-----+---------+--------+
| id | name | sex | address | number |
+----+--------+-----+---------+--------+
| 1 | Kai AI | male | Changsha | 123 |
| 2 | Kobe | male | Beijing | 234 |
| 3 | Xiaoli | female | Wuhan | 345 |
| 4 | Xiao Wu | male | Shenzhen | 456 |
| 5 | Zhang Yi Feng | male | Nanjing | 567 |
| 6 | He Zhizhang | male | Shanghai | 789 |
| 7 | Zhang Yishan | male | Beijing | 987 |
| 8 | Zhao Liying | female | Shanghai | 876 |
| 9 | Providence | male | | NULL |
+----+--------+-----+---------+--------+
9 rows in set
mysql> select*from phone;
+----+-----------+------------+
| id | person_id | phone_name |
+----+-----------+------------+
| 12 | 1 | IPhone |
| 13 | 2 | Meizu mobile phone |
| 14 | 3 | HUAWEI mobile phone |
| 15 | 1 | Samsung mobile phone |
| 16 | 4 | Meizu mobile phone |
| 17 | 5 | zte |
| 18 | 6 | htc |
| 19 | 4 | IPhone |
| 20 | 3 | Samsung mobile phone |
| 21 | 7 | Amphilochia |
| 22 | 8 | Millet 4 |
| 23 | 4 | Meizu mobile phone |
+----+-----------+------------+
12 rows in set
mysql> select*from phone_info;
+---------+----------+-------------+-------------+
| info_id | phone_id | phone_price | phone_color |
+---------+----------+-------------+-------------+
| 1001 | 12 | 4999 | black |
| 1002 | 13 | 5288 | Luxury gold color |
| 1003 | 14 | 3588 | silvery |
| 1004 | 15 | 6288 | Golden |
| 1005 | 16 | 2998 | black |
| 1006 | 17 | 1999 | silvery |
| 1007 | 18 | 4999 | gules |
+---------+----------+-------------+-------------+
7 rows in set
2. Subqueries with IN predicates
In nested queries, sub-query results are often a collection, so we need to use the IN keyword.
mysql> select*from person ,phone where person.id=phone.person_id;
+----+--------+-----+---------+--------+----+-----------+------------+
| id | name | sex | address | number | id | person_id | phone_name |
+----+--------+-----+---------+--------+----+-----------+------------+
| 1 | Kai AI | male | Changsha | 123 | 12 | 1 | IPhone |
| 1 | Kai AI | male | Changsha | 123 | 15 | 1 | Samsung mobile phone |
| 2 | Kobe | male | Beijing | 234 | 13 | 2 | Meizu mobile phone |
| 3 | Xiaoli | female | Wuhan | 345 | 14 | 3 | HUAWEI mobile phone |
| 3 | Xiaoli | female | Wuhan | 345 | 20 | 3 | Samsung mobile phone |
| 4 | Xiao Wu | male | Shenzhen | 456 | 16 | 4 | Meizu mobile phone |
| 4 | Xiao Wu | male | Shenzhen | 456 | 19 | 4 | IPhone |
| 4 | Xiao Wu | male | Shenzhen | 456 | 23 | 4 | Meizu mobile phone |
| 5 | Zhang Yi Feng | male | Nanjing | 567 | 17 | 5 | zte |
| 6 | He Zhizhang | male | Shanghai | 789 | 18 | 6 | htc |
| 7 | Zhang Yishan | male | Beijing | 987 | 21 | 7 | Amphilochia |
| 8 | Zhao Liying | female | Shanghai | 876 | 22 | 8 | Millet 4 |
+----+--------+-----+---------+--------+----+-----------+------------+
mysql> select*from person where id IN(select person_
id from phone where phone_name='Meizu mobile phone');
+----+-------+-----+---------+--------+
| id | name | sex | address | number |
+----+-------+-----+---------+--------+
| 2 | Kobe | male | Beijing | 234 |
| 4 | Xiao Wu | male | Shenzhen | 456 |
+----+-------+-----+---------+--------+3. Subqueries with comparison operators (>,<,=,>=,>=,<=,!=)
mysql> select*from person where id =(select person_id from phone where id=15);
+----+------+-----+---------+--------+
| id | name | sex | address | number |
+----+------+-----+---------+--------+
| 1 | Kai AI | male | Changsha | 123 |
+----+------+-----+---------+--------+
1 row in set
mysql> select*from person where id >
(select person_id from phone where id=15);
+----+--------+-----+---------+--------+
| id | name | sex | address | number |
+----+--------+-----+---------+--------+
| 2 | Kobe | male | Beijing | 234 |
| 3 | Xiaoli | female | Wuhan | 345 |
| 4 | Xiao Wu | male | Shenzhen | 456 |
| 5 | Zhang Yi Feng | male | Nanjing | 567 |
| 6 | He Zhizhang | male | Shanghai | 789 |
| 7 | Zhang Yishan | male | Beijing | 987 |
| 8 | Zhao Liying | female | Shanghai | 876 |
| 9 | Providence | male | | NULL |
+----+--------+-----+---------+--------+
8 rows in set4. Subqueries with any and some keywords
mysql> select*from person ,phone where person.id=phone.person_id; +----+--------+-----+---------+--------+----+-----------+------------+ | id | name | sex | address | number | id | person_id | phone_name | +----+--------+-----+---------+--------+----+-----------+------------+ | 1 | Kenai | Male | Changsha | 123 | 12 | 1 | Apple Mobile| | 1 | Kanai | Male | Changsha | 123 | 15 | 1 | Samsung Mobile Phone| | 2 | Kobe | Male | Beijing | 234 | 13 | 2 | Meizu Mobile Phone| | 3 | Xiaoli | Female | Wuhan | 345 | 14 | 3 | Huawei Mobile Phone| | 3 | Xiaoli | Female | Wuhan | 345 | 20 | 3 | Samsung Mobile Phone| | 4 | Small Five | Male | Shenzhen | 456 | 16 | 4 | Meizu Mobile Phone| | 4 | Small Five | Male | Shenzhen | 456 | 19 | 4 | Apple Mobile| | 4 | Small Five | Male | Shenzhen | 456 | 23 | 4 | Meizu Mobile Phone| | 5 | Zhang Yifeng | Male | Nanjing | 567 | 17 | 5 | ZTE Mobile Phone| | 6 | He Zhizhang | Male | Shanghai | 789 | 18 | 6 | htc| | 7 | Zhang Yishan | Male | Beijing | 987 | 21 | 7 | Rockia| | 8 | Zhao Liying | Female | Shanghai | 876 | 22 | 8 | Millet 4| +----+--------+-----+---------+--------+----+-----------+------------+ Note: It is enough to exceed the minimum value in the sub-query. MySQL > select * from person where id > any (select person_id from phone where phone_name='Meizu mobile phone'); +----+--------+-----+---------+--------+ | id | name | sex | address | number | +----+--------+-----+---------+--------+ | 3 | Xiaoli | Female | Wuhan | 345| | 4 | Small Five | Male | Shenzhen | 456| | 5 | Zhang Yifeng | Male | Nanjing | 567| | 6 | He Zhizhang | Male | Shanghai | 789| | 7 | Zhang Yishan | Male | Beijing | 987| | 8 | Zhao Liying | Female | Shanghai | 876| | 9 | God bless | Male | NULL| +----+--------+-----+---------+--------+ 7 rows in set mysql> select*from person where id< any(select person_id from phone where phone_name='Meizu mobile phone'); +----+------+-----+---------+--------+ | id | name | sex | address | number | +----+------+-----+---------+--------+ | 1 | Kanai | Male | Changsha | 123| | 2 | Kobe Bryant | Male | Beijing | 234| | 3 | Xiaoli | Female | Wuhan | 345| +----+------+-----+---------+--------+
5. Subqueries using all predicates
mysql> select*from person ,phone where person.id=phone.person_id; +----+--------+-----+---------+--------+----+-----------+------------+ | id | name | sex | address | number | id | person_id | phone_name | +----+--------+-----+---------+--------+----+-----------+------------+ | 1 | Kenai | Male | Changsha | 123 | 12 | 1 | Apple Mobile| | 1 | Kanai | Male | Changsha | 123 | 15 | 1 | Samsung Mobile Phone| | 2 | Kobe | Male | Beijing | 234 | 13 | 2 | Meizu Mobile Phone| | 3 | Xiaoli | Female | Wuhan | 345 | 14 | 3 | Huawei Mobile Phone| | 3 | Xiaoli | Female | Wuhan | 345 | 20 | 3 | Samsung Mobile Phone| | 4 | Small Five | Male | Shenzhen | 456 | 16 | 4 | Meizu Mobile Phone| | 4 | Small Five | Male | Shenzhen | 456 | 19 | 4 | Apple Mobile| | 4 | Small Five | Male | Shenzhen | 456 | 23 | 4 | Meizu Mobile Phone| | 5 | Zhang Yifeng | Male | Nanjing | 567 | 17 | 5 | ZTE Mobile Phone| | 6 | He Zhizhang | Male | Shanghai | 789 | 18 | 6 | htc| | 7 | Zhang Yishan | Male | Beijing | 987 | 21 | 7 | Rockia| | 8 | Zhao Liying | Female | Shanghai | 876 | 22 | 8 | Millet 4| +----+--------+-----+---------+--------+----+-----------+------------+ Note: greater than the maximum of query results mysql> select*from person where id>all (select person_id from phone where phone_name='Meizu mobile phone'); +----+--------+-----+---------+--------+ | id | name | sex | address | number | +----+--------+-----+---------+--------+ | 5 | Zhang Yifeng | Male | Nanjing | 567| | 6 | He Zhizhang | Male | Shanghai | 789| | 7 | Zhang Yishan | Male | Beijing | 987| | 8 | Zhao Liying | Female | Shanghai | 876| | 9 | God bless | Male | NULL| +----+--------+-----+---------+--------+ mysql> select*from person where id<all (select person_id from phone where phone_name='Meizu mobile phone'); +----+------+-----+---------+--------+ | id | name | sex | address | number | +----+------+-----+---------+--------+ | 1 | Kanai | Male | Changsha | 123| +----+------+-----+---------+--------+ 1 row in set
5. Subqueries of exists keyword
The result of exists is true, and the outer query is executed. If the outer query for false is not executed.
mysql> select*from person ,phone where person.id=phone.person_id;
+----+--------+-----+---------+--------+----+-----------+------------+
| id | name | sex | address | number | id | person_id | phone_name |
+----+--------+-----+---------+--------+----+-----------+------------+
| 1 | Kai AI | male | Changsha | 123 | 12 | 1 | IPhone |
| 1 | Kai AI | male | Changsha | 123 | 15 | 1 | Samsung mobile phone |
| 2 | Kobe | male | Beijing | 234 | 13 | 2 | Meizu mobile phone |
| 3 | Xiaoli | female | Wuhan | 345 | 14 | 3 | HUAWEI mobile phone |
| 3 | Xiaoli | female | Wuhan | 345 | 20 | 3 | Samsung mobile phone |
| 4 | Xiao Wu | male | Shenzhen | 456 | 16 | 4 | Meizu mobile phone |
| 4 | Xiao Wu | male | Shenzhen | 456 | 19 | 4 | IPhone |
| 4 | Xiao Wu | male | Shenzhen | 456 | 23 | 4 | Meizu mobile phone |
| 5 | Zhang Yi Feng | male | Nanjing | 567 | 17 | 5 | zte |
| 6 | He Zhizhang | male | Shanghai | 789 | 18 | 6 | htc |
| 7 | Zhang Yishan | male | Beijing | 987 | 21 | 7 | Amphilochia |
| 8 | Zhao Liying | female | Shanghai | 876 | 22 | 8 | Millet 4 |
+----+--------+-----+---------+--------+----+-----------+------------+
mysql> select*from person where exists (select person_id from phone
where id=12);
+----+--------+-----+---------+--------+
| id | name | sex | address | number |
+----+--------+-----+---------+--------+
| 1 | Kai AI | male | Changsha | 123 |
| 2 | Kobe | male | Beijing | 234 |
| 3 | Xiaoli | female | Wuhan | 345 |
| 4 | Xiao Wu | male | Shenzhen | 456 |
| 5 | Zhang Yi Feng | male | Nanjing | 567 |
| 6 | He Zhizhang | male | Shanghai | 789 |
| 7 | Zhang Yishan | male | Beijing | 987 |
| 8 | Zhao Liying | female | Shanghai | 876 |
| 9 | Providence | male | | NULL |
+----+--------+-----+---------+--------+
9 rows in set