sql 基础--mysql 5 （6）

12.子查询

子查询进行过滤

mysql> select msg from pw_luck where name = 'wang5'-> ;
+------+
| msg  |
+------+
| 1001 |
| 1000 |
| 1000 |
|  100 |
|  100 |
+------+
5 rows in set (0.03 sec)mysql> select uid from pw_luck where msg in (select msg from pw_luck where name = 'wang5');
+-----+
| uid |
+-----+
|   0 |
|   1 |
|   2 |
|   3 |
|   7 |
|   8 |
|   9 |
|  10 |
|  12 |
|  13 |
|  14 |
|  15 |
+-----+
12 rows in set (0.02 sec)

作为计算字段使用子查询 ,联表查询

mysql> select uid,price,(select count(*) from pw_luck where pw_luck.uid=pw_price.uid) as pw_luck_cou
nt from pw_price order by uid;
+-----+-------+---------------+
| uid | price | pw_luck_count |
+-----+-------+---------------+
|   1 |   100 |             1 |
|   2 |  1000 |             1 |
|   3 |  2000 |             1 |
+-----+-------+---------------+
3 rows in set (0.02 sec)

13联结表

外键：某个表中的一列，他包含另一个表中的主键值，定义了两表之间的关系。

mysql> select pw_luck.uid,name,msg,price from pw_luck,pw_price where pw_luck.uid=pw_price.uid order
by pw_luck.uid;
+-----+----------+------+-------+
| uid | name     | msg  | price |
+-----+----------+------+-------+
|   1 | zhangsan |  100 |   100 |
|   2 | lisi     | 1001 |  1000 |
|   3 | wang5    | 1001 |  2000 |
+-----+----------+------+-------+
3 rows in set (0.01 sec)

卡迪尔积： 无 where的行数是table1和table2行数的乘积

mysql> select pw_luck.uid,name,msg,price from pw_luck,pw_price order by pw_luck.uid;
+-----+-----------+------+-------+
| uid | name      | msg  | price |
+-----+-----------+------+-------+
|   0 | Wilson    |  100 |   100 |
|   0 | Wilson    |  100 |  1000 |
|   0 | Wilson    |  100 |  2000 |
|   1 | zhangsan  |  100 |  2000 |
|   1 | zhangsan  |  100 |   100 |
|   1 | zhangsan  |  100 |  1000 |
|   2 | lisi      | 1001 |  2000 |
|   2 | lisi      | 1001 |   100 |
|   2 | lisi      | 1001 |  1000 |
|   3 | wang5     | 1001 |  1000 |
|   3 | wang5     | 1001 |  2000 |
|   3 | wang5     | 1001 |   100 |
|   7 | zhangsan7 | 1000 |   100 |
|   7 | zhangsan7 | 1000 |  1000 |
|   7 | zhangsan7 | 1000 |  2000 |
|   8 | zhangsan8 | 1000 |  2000 |
|   8 | zhangsan8 | 1000 |   100 |
|   8 | zhangsan8 | 1000 |  1000 |
|   9 | zhangsan9 | 1000 |  2000 |
|   9 | zhangsan9 | 1000 |   100 |
|   9 | zhangsan9 | 1000 |  1000 |
|  10 | wang5     | 1000 |  1000 |
|  10 | wang5     | 1000 |  2000 |
|  10 | wang5     | 1000 |   100 |
|  11 | wang5%    | 2000 |  1000 |
|  11 | wang5%    | 2000 |  2000 |
|  11 | wang5%    | 2000 |   100 |
|  12 | WANG5     | 1000 |   100 |
|  12 | WANG5     | 1000 |  1000 |
|  12 | WANG5     | 1000 |  2000 |
|  13 | wang5     |  100 |  2000 |
|  13 | wang5     |  100 |   100 |
|  13 | wang5     |  100 |  1000 |
|  14 | wang5     |  100 |  2000 |
|  14 | wang5     |  100 |   100 |
|  14 | wang5     |  100 |  1000 |
|  15 | zhangsan7 | 1000 |  1000 |
|  15 | zhangsan7 | 1000 |  2000 |
|  15 | zhangsan7 | 1000 |   100 |
|  16 | zhangsan7 | 1002 |  1000 |
|  16 | zhangsan7 | 1002 |  2000 |
|  16 | zhangsan7 | 1002 |   100 |
|  17 | zhangsan7 | 1002 |  2000 |
|  17 | zhangsan7 | 1002 |   100 |
|  17 | zhangsan7 | 1002 |  1000 |
+-----+-----------+------+-------+
45 rows in set (0.03 sec)

联结多个表  where语句用and 连接