sql-子查询基础操作_sql子查询作为操作数-CSDN博客

本文链接：https://blog.csdn.net/LuSHui_1997/article/details/104815441

所用表及数据：

CREATE TABLE `stu` (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  score double(4,2) DEFAULT NULL,
  address_id int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT `FK_stu_address` FOREIGN KEY (address_id) REFERENCES address (`id`) ON UPDATE CASCADE
) ;


create table address(
        id int primary key auto_increment, # id自增
        description varchar(20)
);
插入的数据：
insert into `address` (`id`, `description`) values('1','France');
insert into `address` (`id`, `description`) values('2','England');
insert into `address` (`id`, `description`) values('3','China');
insert into `address` (`id`, `description`) values('4','');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('1','li','19','90.80',NULL);
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('2','wang','19','89.40','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('3','gu','18','94.00','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('4','luo','19','95.00','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('5','cui','20','96.00','1');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('6','zhang','21','96.00','2');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('7','chen','22','97.00','2');
insert into `stu` (`id`, `name`, `age`, `score`, `address_id`) values('8','yao','23','98.00','2');
insert into `stu` ( `name`, `age`, `score`, `address_id`) values('lu','23','97.00','2');

标题查询结果有一条数据作为条件参与另外一个查询：

创建：select * from 表1 where 表1.列1=（select 表2.列1 from 表2）;
例子：查询得分最高的同学的地址

mysql> select * from address where id=(select address_id from stu where score =(select max(score) from stu));
+----+-------------+
| id | description |
+----+-------------+
|  2 | England     |
+----+-------------+
1 row in set (0.00 sec)

使用内连接的方式查询：
mysql> select t1.* from address t1,stu t2 where t1.id = t2.address_id and t2.score=(select max(score) from stu);
+----+-------------+
| id | description |
+----+-------------+
|  2 | England     |
+----+-------------+
1 row in set (0.29 sec)

查询结果有多行单列数据作为条件参与另外一个查询

创建：select * from 表1 where 表1.列1 in(select 表2.列1 from 表2 where 表2.列2 in('条件1','条件2'));
例子：查询来自法国和英国的同学的信息

mysql> select * from stu where address_id in (select id from address where description in('France','England'));
+----+-------+------+-------+------------+
| id | name  | age  | score | address_id |
+----+-------+------+-------+------------+
|  2 | wang  |   19 | 89.40 |          1 |
|  3 | gu    |   18 | 94.00 |          1 |
|  4 | luo   |   19 | 95.00 |          1 |
|  5 | cui   |   20 | 96.00 |          1 |
|  6 | zhang |   21 | 96.00 |          2 |
|  7 | chen  |   22 | 97.00 |          2 |
|  8 | yao   |   23 | 98.00 |          2 |
+----+-------+------+-------+------------+

内查询同样可做到：
mysql> select t1.* from stu t1,address t2 where t1.address_id=t2.id and t2.description in('France','England');
+----+-------+------+-------+------------+
| id | name  | age  | score | address_id |
+----+-------+------+-------+------------+
|  2 | wang  |   19 | 89.40 |          1 |
|  3 | gu    |   18 | 94.00 |          1 |
|  4 | luo   |   19 | 95.00 |          1 |
|  5 | cui   |   20 | 96.00 |          1 |
|  6 | zhang |   21 | 96.00 |          2 |
|  7 | chen  |   22 | 97.00 |          2 |
|  8 | yao   |   23 | 98.00 |          2 |
+----+-------+------+-------+------------+
7 rows in set (0.00 sec)

不可以用外连接进行查询，这样子不管是左连接还是右连接都会把一些非空的数据查询出来
mysql> select t1.* from stu t1 left join address t2 on t1.address_id=t2.id and t2.description in('France','England');
+----+-------+------+-------+------------+
| id | name  | age  | score | address_id |
+----+-------+------+-------+------------+
|  1 | li    |   19 | 90.80 |       NULL |
|  2 | wang  |   19 | 89.40 |          1 |
|  3 | gu    |   18 | 94.00 |          1 |
|  4 | luo   |   19 | 95.00 |          1 |
|  5 | cui   |   20 | 96.00 |          1 |
|  6 | zhang |   21 | 96.00 |          2 |
|  7 | chen  |   22 | 97.00 |          2 |
|  8 | yao   |   23 | 98.00 |          2 |
+----+-------+------+-------+------------+
8 rows in set (0.00 sec)

标题查询结果有多行多列数据作为条件参与另外一个查询

把查询的结果作为一个虚拟表，对这个虚拟表进行操作
例子：查询年龄等于23岁的学生的姓名和地址。

mysql> select * from stu where age=23;
+----+------+------+-------+------------+
| id | name | age  | score | address_id |
+----+------+------+-------+------------+
|  8 | yao  |   23 | 98.00 |          2 |
|  9 | lu   |   23 | 97.00 |          2 |
+----+------+------+-------+------------+
2 rows in set (0.00 sec)

mysql> select t1.name,address.description from address,(select * from stu where age=23) t1 where t1.address_id=address.id;
+------+-------------+
| name | description |
+------+-------------+
| yao  | England     |
| lu   | England     |
+------+-------------+
2 rows in set (0.04 sec)

使用内连接查询
mysql> select * from stu t1,address t2 where t1.address_id=t2.id and t1.age=23;
+----+------+------+-------+------------+----+-------------+
| id | name | age  | score | address_id | id | description |
+----+------+------+-------+------------+----+-------------+
|  8 | yao  |   23 | 98.00 |          2 |  2 | England     |
|  9 | lu   |   23 | 97.00 |          2 |  2 | England     |
+----+------+------+-------+------------+----+-------------+
2 rows in set (0.00 sec)