in、、not in、exists、not exists
mysql> select * from student;
+----+----------+------+-------+---------+------+------+
| id | name | age | class | servlet | jsp | sex |
+----+----------+------+-------+---------+------+------+
| 2 | 李四 | 29 | 三班 | 4 | 5 | 女 |
| 4 | NULL | 10 | NULL | NULL | NULL | 未知 |
| 5 | | 10 | NULL | NULL | NULL | 未知 |
| 6 | 大张伟 | 39 | 二班 | 9 | 9 | 男 |
| 7 | 大小张伟 | 50 | 二班 | 9 | 9 | 女 |
+----+----------+------+-------+---------+------+------+
5 rows in set (0.00 sec)
mysql> select * from student where id = 2 or id = 4 or id = 5;
+----+------+------+-------+---------+------+------+
| id | name | age | class | servlet | jsp | sex |
+----+------+------+-------+---------+------+------+
| 2 | 李四 | 29 | 三班 | 4 | 5 | 女 |
| 4 | NULL | 10 | NULL | NULL | NULL | 未知 |
| 5 | | 10 | NULL | NULL | NULL | 未知 |
+----+------+------+-------+---------+------+------+
3 rows in set (0.00 sec)
mysql> select * from student where id in (2, 4, 5);
+----+------+------+-------+---------+------+------+
| id | name | age | class | servlet | jsp | sex |
+----+------+------+-------+---------+------+------+
| 2 | 李四 | 29 | 三班 | 4 | 5 | 女 |
| 4 | NULL | 10 | NULL | NULL | NULL | 未知 |
| 5 | | 10 | NULL | NULL | NULL | 未知 |
+----+------+------+-------+---------+------+------+
3 rows in set (0.01 sec)
not in
mysql> select * from student where id <> 2 and id <> 4;
+----+----------+------+-------+---------+------+------+
| id | name | age | class | servlet | jsp | sex |
+----+----------+------+-------+---------+------+------+
| 5 | | 10 | NULL | NULL | NULL | 未知 |
| 6 | 大张伟 | 39 | 二班 | 9 | 9 | 男 |
| 7 | 大小张伟 | 50 | 二班 | 9 | 9 | 女 |
+----+----------+------+-------+---------+------+------+
3 rows in set (0.00 sec)
mysql> select * from student where id not in (2, 4);
+----+----------+------+-------+---------+------+------+
| id | name | age | class | servlet | jsp | sex |
+----+----------+------+-------+---------+------+------+
| 5 | | 10 | NULL | NULL | NULL | 未知 |
| 6 | 大张伟 | 39 | 二班 | 9 | 9 | 男 |
| 7 | 大小张伟 | 50 | 二班 | 9 | 9 | 女 |
+----+----------+------+-------+---------+------+------+
3 rows in set (0.00 sec)
exists
括号里面的查询语句是否能够查询到结果,能返回true,不能返回false
括号里面的SQL查询语句具体能查出什么不用在意,能查出来即是true就行
mysql> select * from dept;
+----+--------+------+----------+
| id | d_name | d_no | d_desc |
+----+--------+------+----------+
| 2 | 人事部 | d002 | 人事管理 |
| 3 | 研发部 | d001 | 软件研发 |
+----+--------+------+----------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+--------+------+------+------+
| id | e_name | sex | e_no | d_id |
+----+--------+------+------+------+
| 1 | 张三 | 男 | 001 | 3 |
| 2 | 李四 | 女 | 002 | 3 |
+----+--------+------+------+------+
2 rows in set (0.00 sec)
mysql> select * from emp where id = 1 and exists (select * from dept where id = 3);
+----+--------+------+------+------+
| id | e_name | sex | e_no | d_id |
+----+--------+------+------+------+
| 1 | 张三 | 男 | 001 | 3 |
+----+--------+------+------+------+
1 row in set (0.00 sec)
not exists相反
mysql> select * from emp where id = 1 and exists (select * from dept where id = 3);
+----+--------+------+------+------+
| id | e_name | sex | e_no | d_id |
+----+--------+------+------+------+
| 1 | 张三 | 男 | 001 | 3 |
+----+--------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from emp where id = 1 and exists (select * from dept where id = 3);
+----+--------+------+------+------+
| id | e_name | sex | e_no | d_id |
+----+--------+------+------+------+
| 1 | 张三 | 男 | 001 | 3 |
+----+--------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from emp where id = 1 and exists (select * from dept where id = 4);
Empty set (0.00 sec)
mysql> select * from emp where id = 1 and not exists (select * from dept where id = 4);
+----+--------+------+------+------+
| id | e_name | sex | e_no | d_id |
+----+--------+------+------+------+
| 1 | 张三 | 男 | 001 | 3 |
+----+--------+------+------+------+
1 row in set (0.00 sec)