2

10：子查询-MySQL

 1 year ago
source link: https://blog.51cto.com/yeatsliao/5958281
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

10：子查询-MySQL

精选 原创

10.1 子查询基本语法

将一个查询的结果作为另一个查询的数据来源或判断条件

一般情况下子查询结果返回超过1行用in，只有一行可以用=

select * from stu where stuId  in (select stuId from eatery where money > 800);
select * from stu where stuId = (select stuId from eatery where money > 1000);

mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  0 | 999.0000 |     5 |
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
|  6 | 999.0000 |     5 |
|  7 | 345.0000 |     4 |
+----+----------+-------+
8 rows in set (0.00 sec)

mysql> select * from stu;
+-------+-------+
| stuId | name  |
+-------+-------+
|     4 | frank |
|     5 | Tom   |
+-------+-------+
2 rows in set (0.00 sec)


mysql> select stuId from eatery where money > 800;
+-------+
| stuId |
+-------+
|     5 |
|     5 |
+-------+
2 rows in set (0.00 sec)

mysql> select * from stu where stuId in (select stuId from eatery where money > 800);
+-------+------+
| stuId | name |
+-------+------+
|     5 | Tom  |
+-------+------+
1 row in set (0.00 sec)

10.2 in 和 not in

mysql> select * from stu where stuId not in (select stuId from eatery where money > 800);
+-------+-------+
| stuId | name  |
+-------+-------+
|     4 | frank |
+-------+-------+
1 row in set (0.00 sec)

10.3 exists 和 not exists

exists是一个存在的条件，只要子查询存在，就把主查询所有的列出来

mysql> select stuId from eatery where money > 900;
+-------+
| stuId |
+-------+
|     5 |
|     5 |
+-------+
2 rows in set (0.00 sec)

mysql> select * from stu where exists  (select stuId from eatery where money > 900);
+-------+-------+
| stuId | name  |
+-------+-------+
|     4 | frank |
|     5 | Tom   |
+-------+-------+
2 rows in set (0.00 sec)

10.4 基础结束语

多练习，现在如果你熟悉之前的课程，那么实习就基本达标了，后面的内容属于比较难的内容，对于实习生来说不是特别重要

  • 赞
  • 收藏
  • 评论
  • 分享
  • 举报

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK