本文共 2380 字,大约阅读时间需要 7 分钟。
查询
(query) 任何SQL语句都是查询。但此术语一般指SELECT语句。
子查询(subquery)
,即嵌套
在其他查询中的查询。 美剧类型限制了字段的值,字段的值只能从限制的字符中取一个,可以使用值,也可以使用数字代替,1为第一个值,例如:
MariaDB [test]> create table ss (state enum('good','bad','secret'),user varchar(20))MariaDB [test]> insert into ss value('good','user1');Query OK, 1 row affected (0.00 sec)MariaDB [test]> insert into ss value('secret','user2');Query OK, 1 row affected (0.00 sec)MariaDB [test]> insert into ss value(3,'user3');Query OK, 1 row affected (0.00 sec)MariaDB [test]> insert into ss value(2,'user4');Query OK, 1 row affected (0.00 sec)MariaDB [test]> insert into ss value(1,'user5');Query OK, 1 row affected (0.00 sec)MariaDB [test]> insert into ss value(1,'user6');Query OK, 1 row affected (0.00 sec)MariaDB [test]> insert into ss value(2,'user7');Query OK, 1 row affected (0.00 sec)MariaDB [test]> select * from ss;+--------+-------+| state | user |+--------+-------+| good | user1 || secret | user2 || secret | user3 || bad | user4 || good | user5 || good | user6 || bad | user7 |+--------+-------+7 rows in set (0.00 sec)
它和集合的区别在于:
在集合字段中可以制定做个值,比如,上面枚举的状态只能有一个(1或者2或者3),而集合类型可以有多个:(1或者1,2或者1,2,3)
MariaDB [test]> select user from linux where sex = 'girl';+-------+| user |+-------+| user2 | /找出 linux 表中女生的 user 名| user7 || user6 |+-------+3 rows in set (0.00 sec)MariaDB [test]> select state from ss where user in ('user2','user6','user7');+--------+| state |+--------+| secret | /找出ss表中suer名为()内的状态| good || bad |+--------+3 rows in set (0.00 sec)MariaDB [test]> select state from ss where user in (select user from linux where sex = 'girl');+--------+| state |+--------+| secret | /使用子查询| good || bad |+--------+3 rows in set (0.00 sec)
在执行的时候会首先执行子查询。
MariaDB [test]> select * from hour;+-------+-------+| state | hours |+-------+-------+| good | 12 || bad | 8 || bad | 10 |+-------+-------+3 rows in set (0.00 sec)MariaDB [test]> select hours from hour where state in ( -> select state from ss where user in ( -> select user from linux where sex = 'girl') -> );+-------+| hours |+-------+| 12 || 8 || 10 |+-------+3 rows in set (0.00 sec)
我们在使用子查询的时候,一定要注意该保证SELECT语句具有与WHERE子句中相同数目的列
IN
操作符结合使用,但也可以用于测试等于(=)
、不等于(<>)
等。 转载地址:http://uyxli.baihongyu.com/