当前位置：首页 > 数据库 > Mysql > 详细页面

mysql子查询(单行子查询,多行子查询,多列子查询)

时间：2023-10-27来源：系统城装机大师作者：佚名

子查询使用规则：

子查询放在圆括号中
子查询放在比较条件右边（非强制）
子查询中不需要ORDER BY 子句
在单行子查询中使用单行运算符，在多行子查询中用多行运算符。
单行运算符：子查询结果只有一个：< > = <= >= !=
多行子查询：子查询结果是单列多行：in ， any，all
多列子查询：子查询为多列，一定要在FROM后作为表，且一定要取别名，否则无法访问这张表中的字段。

创建初始表

1
2
3
4 CREATE TABLE tbl1 (num1 INT NOT NULL);
CREATE TABLE tbl2 (num2 INT NOT NULL);
INSERT INTO tbl1 VALUES(1),(5),(13),(27);
INSERT INTO tbl2 VALUES(6),(14),(11),(20);

ANY SOME 关键字的子查询

返回tbl2的所有num2列，将tbl1中的num值与之进行比较，只要大于num2的任意一值，即为符合条件的结果。

1 2	`SELECT` `num1` `FROM` `tbl1` `WHERE` `num1 >` `SOME(SELECT` `num2` `FROM` `tbl2);`

1 2	`SELECT` `num1` `FROM` `tbl1` `WHERE` `num1 >` `ANY` `(SELECT` `num2` `FROM` `tbl2);`

ALL 关键字

1 2	`SELECT` `num1` `FROM` `tbl1` `WHERE` `num1 >` `ALL` `(SELECT` `num2` `FROM` `tbl2);`

新建一个orderitems表

1
2
3
4
5
6
7
8 CREATE TABLE orderitems(
o_num INT NOT NULL,
o_item INT NOT NULL,
f_id CHAR(10) NOT NULL,
quantity INT NOT NULL,
item_price DECIMAL(8,2) NOT NULL,
PRIMARY KEY(o_num,o_item)
);

填入数据

1
2
3
4
5
6
7
8
9
10
11
12 INSERT INTO orderitems(o_num,o_item,f_id,quantity,item_price)
VALUES(3001,1,'a1',10,5.2),
(3001,2,'b2',3,7.6),
(3001,3,'bs1',5,11.2),
(3001,4,'bs2',15,9.2),
(3002,1,'b3',2,20.0),
(3003,1,'c0',100,10),
(3004,1,'o2',50,2.50),
(3005,1,'c0',5,10),
(3005,2,'b1',10,8.99),
(3005,3,'a2',10,2.2),
(3005,4,'m1',5,14.99);

带 IN 关键字的子查询

#在orderitem表中查询f_id为c0 的订单号，并根据订单号查询具有订单号的客户c_id，SQL语句如下：

1
2
3 SELECT c_id FROM orders
WHERE o_num IN
(SELECT o_num FROM orderitems WHERE f_id = 'c0');

NOT IN

1
2
3 SELECT c_id FROM orders
WHERE o_num NOT IN
(SELECT o_num FROM orderitems WHERE f_id = 'c0');

连接查询

（也可以解决这个问题，但有空值，但子查询更容易阅读和编写）

1
2
3
4 SELECT c_id
FROM orderitems left JOIN orders
ON orders.o_num = orderitems.o_num
AND orderitems.f_id = 'c0';

多列子查询

在 orderitems 表中查询 f_id 为 c0 的订单号的所有信息，并根据订单号查询具有订单号的客户的所有信息，SQL语句如下：

1	`SELECT * FROM orders ord ,(SELECT * FROM orderitems AS o WHERE f_id='c0') ite WHERE ord.o_num=ite.o_num ;`

*注意：子查询结果为多列，一定在FROM后做为表，且一定要取别名，否则无法访问这张表中的字段。

带比较运算符的子查询

#在suppliers表中查询s_city等于‘tianjin’的供应商s_id，然后在fruits表中，查询所有非该供应商供应的水果种类名称。

1
2
3
4 SELECT s_id,f_name FROM fruits f
WHERE f.s_id !=
(SELECT s1.s_id
FROM suppliers AS s1 WHERE s1.s_city = 'tianjin');