where子查询
where子查询
相同库下的相同表
把内层的查询结果，作为外层的查询条件
mysql> select * from db4.user2;
+----+---------------------+------+----------+-------+-------+---------+---------------------------+----------------+
| id | name | age | password | uid | gid | comment | homedir | shell |
+----+---------------------+------+----------+-------+-------+---------+---------------------------+----------------+
| 1 | root | 18 | x | 1 | -1 | root | /root | /bin/bash |
| 2 | bin | 18 | A | 2 | 0 | student | /bin | /sbin/nologin |
| 3 | daemon | 18 | A | 3 | 1 | student | /sbin | /sbin/nologin |
| 4 | adm | 18 | A | 4 | 3 | student | /var/adm | /sbin/nologin |
| 5 | lp | 18 | A | 5 | 6 | student | /var/spool/lpd | /sbin/nologin |
| 6 | sync | 18 | A | 6 | -1 | student | /sbin | /bin/sync |
| 7 | shutdown | 18 | A | 7 | -1 | student | /sbin | /sbin/shutdown |
| 8 | halt | 18 | A | 8 | -1 | student | /sbin | /sbin/halt |
| 9 | mail | 18 | A | 9 | 11 | student | /var/spool/mail | /sbin/nologin |
| 10 | operator | 18 | A | 12 | -1 | student | /root | /sbin/nologin |
| 11 | games | 18 | A | 13 | 99 | student | /usr/games | /sbin/nologin |
| 12 | ftp | 18 | A | 15 | 49 | student | /var/ftp | /sbin/nologin |
| 13 | nobody | 18 | A | 100 | 98 | student | / | /sbin/nologin |
| 14 | systemd-network | 18 | A | 193 | 191 | student | / | /sbin/nologin |
| 15 | dbus | 18 | A | 82 | 80 | student | / | /sbin/nologin |
| 16 | polkitd | 18 | A | 1000 | 997 | student | / | /sbin/nologin |
| 17 | libstoragemgmt | 18 | A | 999 | 995 | student | /var/run/lsm | /sbin/nologin |
| 18 | rpc | 18 | A | 33 | 31 | student | /var/lib/rpcbind | /sbin/nologin |
| 19 | colord | 18 | A | 998 | 994 | student | /var/lib/colord | /sbin/nologin |
| 20 | saslauth | 18 | A | 997 | 75 | student | /run/saslauthd | /sbin/nologin |
| 21 | abrt | 18 | A | 174 | 172 | student | /etc/abrt | /sbin/nologin |
| 22 | rtkit | 18 | A | 173 | 171 | student | /proc | /sbin/nologin |
| 23 | radvd | 18 | A | 76 | 74 | student | / | /sbin/nologin |
| 24 | chrony | 18 | A | 996 | 992 | student | /var/lib/chrony | /sbin/nologin |
| 25 | tss | 18 | A | 60 | 58 | student | /dev/null | /sbin/nologin |
| 26 | usbmuxd | 18 | A | 114 | 112 | student | / | /sbin/nologin |
| 27 | geoclue | 18 | A | 995 | 990 | student | /var/lib/geoclue | /sbin/nologin |
| 28 | qemu | 18 | A | 108 | 106 | student | / | /sbin/nologin |
| 29 | rpcuser | 18 | A | 30 | 28 | student | /var/lib/nfs | /sbin/nologin |
| 30 | nfsnobody | 18 | A | 65535 | 65533 | student | /var/lib/nfs | /sbin/nologin |
| 31 | setroubleshoot | 18 | A | 994 | 989 | student | /var/lib/setroubleshoot | /sbin/nologin |
| 32 | pulse | 18 | A | 172 | 170 | student | /var/run/pulse | /sbin/nologin |
| 33 | gdm | 18 | A | 43 | 41 | student | /var/lib/gdm | /sbin/nologin |
| 34 | gnome-initial-setup | 18 | A | 993 | 986 | student | /run/gnome-initial-setup/ | /sbin/nologin |
| 35 | sshd | 18 | A | 75 | 73 | student | /var/empty/sshd | /sbin/nologin |
| 36 | avahi | 18 | A | 71 | 69 | student | /var/run/avahi-daemon | /sbin/nologin |
| 37 | postfix | 18 | A | 90 | 88 | student | /var/spool/postfix | /sbin/nologin |
| 38 | ntp | 18 | A | 39 | 37 | student | /etc/ntp | /sbin/nologin |
| 39 | tcpdump | 18 | A | 73 | 71 | student | / | /sbin/nologin |
| 40 | lisi | 18 | A | 1001 | 999 | student | /home/lisi | /bin/bash |
| 41 | mysql | 18 | A | 28 | 26 | student | /var/lib/mysql | /bin/false |
| 42 | bob | 18 | A | 3002 | 3000 | student | /home/bob | /bin/bash |
+----+---------------------+------+----------+-------+-------+---------+---------------------------+----------------+
mysql> select avg(age) from user2;
+----------+
| avg(age) |
+----------+
| 18.0000 |
+----------+
mysql> select name,age from user2
-> where age < (select avg(age) from user2);
Empty set (0.00 sec)
mysql> select name,uid from user2
-> where uid < (select avg(uid) from user);
mysql> select avg(uid) from user2;
+-----------+
| avg(uid) |
+-----------+
| 1888.7857 |
+-----------+
mysql> select name,uid from user2 where uid > (select avg(uid) from user2);
+-----------+-------+
| name | uid |
+-----------+-------+
| nfsnobody | 65535 |
| bob | 3002 |
+-----------+-------+
###############################################################################################
不同库下的不同表
mysql> select name from db4.t2;
+--------+
| name |
+--------+
| root |
| bin |
| daemon |
+--------+
mysql> select name from db3.user
-> where name in (select name from db4.t2); 用到了in ()
+--------+
| name |
+--------+
| root |
| bin |
| daemon |
+--------+
----------------------------------------------------------------------------------------------------
mysql> select uid from db3.user
-> where uid in (select uid from db4.t2);
+------+
| uid |
+------+
| 1 |
| 2 |
| 3 |
+------+
mysql> select uid,name from db3.user where uid in (select uid from db4.t2); 显示uid和name，查询条件决定了显示的结果！！！
+------+--------+
| uid | name |
+------+--------+
| 1 | root |
| 2 | bin |
| 3 | daemon |
+------+--------+
mysql> select name,uid from db3.user where uid in (select uid from db4.t2); 显示name和uid
+--------+------+
| name | uid |
+--------+------+
| root | 1 |
| bin | 2 |
| daemon | 3 |
+--------+------+
