windows下的MySQL-子查询

来源：互联网发布：c语言学会了可以干吗编辑：程序博客网时间：2024/06/04 18:12

首先，准备两张表

mysql> SELECT * FROM tdb_goods;
+----------+---------------------------------------+---------+----------+-------------+---------+------------+
| goods_id | goods_name                            | cate_id | brand_id | goods_price | is_show | is_saleoff |
+----------+---------------------------------------+---------+----------+-------------+---------+------------+
|        1 | 笔记本电脑                                    |       5 |        1 |    3000.000 |       1 |          0 |
|        2 | 足球                                      |       1 |        9 |    3000.000 |       1 |          0 |
|        3 | 洗发水                                    |       3 |        2 |      50.000 |       1 |          0 |
|        4 | 红米2A增强版                                   |       5 |        4 |     800.000 |       1 |          0 |
|        5 | 蒙古牛肉干                                    |       4 |        6 |     145.000 |       1 |          0 |
|        6 | 蒙古奶酪                                     |       4 |        6 |      74.000 |       1 |          0 |
|        7 | 十八街麻花                                  |       2 |        5 |      80.000 |       1 |          0 |
|        8 | 联想游戏本                                     |       5 |        7 |    7000.000 |       1 |          0 |
|        9 | 苹果笔记本                                   |       5 |        8 |    8000.000 |       1 |          0 |
|       10 | 外星人II                                    |       5 |        3 |   20000.000 |       1 |          0 |
|       11 | 小米4                                    |       5 |        4 |    2000.000 |       1 |          0 |
|       13 | LaserJet Pro　Ｐ1606dn 黑白激光打印机                |      12 |        4 |    1849.000 |       1 |          0 |
|       14 | 小米4s                                   |       9 |        9 |    5000.000 |       1 |          0 |
|       18 | 华为                                     |      10 |       10 |    5000.000 |       1 |          0 |
+----------+---------------------------------------+---------+----------+-------------+---------+------------+
14 rows in set (0.00 sec)

mysql> SELECT * FROM tdb_goods_cates;
+---------+------------+
| cate_id | cate_name |
+---------+------------+
|       1 | 体育用品          |
|       2 | 天津特产          |
|       3 | 日用品          |
|       4 | 食品         |
|       5 | 高科技产品        |
|       6 | 路由器          |
|       7 | 交换机           |
|       8 | 网卡           |
+---------+------------+
8 rows in set (0.01 sec)

接下来是简单的操作

第一类子查询：由比较运算符引发

算平均值
SELECT AVG(goods_price) FROM tdb_goods

对取得的结果四舍五入，保留小数点后两位有效数字
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;

那么哪些商品的价格大于这个数字？
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

SOME，ANY 大于最小值，小于最大值，ALL相反
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>= ALL[SOME][ANY](SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

第二类子查询：使用[NOT]IN的子查询

1 SELECT goods_id ,goods_name ,goods_price FROM tdb_goods WHERE goods_price =ALL(SELECT goods_price FROM tdb_goods WHERE cate_id=5);

2 SELECT goods_id ,goods_name ,goods_price FROM tdb_goods WHERE goods_price !=ALL(SELECT goods_price FROM tdb_goods WHERE cate_id=5);

1 0