【MySQL】MySQL查询——进阶

重点：

1. 理解查询模型：
   A：把列看成变量，把where后面看成if(exp)里的exp 表达式
   哪些行被取出来？——哪一行能让exp为真，哪一行就能取出来
   B：查询结果集在结构上可以当成表看（在子查询中使用较多）
2. 列看作变量，那么列之间就能进行运算
   取出商品id，商品名，本店价比市场价省钱的商品
   select goods_id,goods_name,market_price-shop_price from goods where 1;
   表里面本来没有market_price-shop_price这一列，而这一列其实是一个运算结果，术语叫“广义投影”
   这个运算结果可以当成一个列，也可以给它起个别名（用as）
   select goods_id,goods_name,(market_price-shop_price) as discount from goods where 1;
3. 取出商品id，商品名，本店价比市场价省200元的商品
   select goods_id,goods_name,(market_price-shop_price) as discount from goods where discount > 200;

mysql> select goods_id,goods_name,(market_price-shop_price) as discount from goods where discount > 200;
ERROR 1054 (42S22): Unknown column 'discount' in 'where clause'

where是对表中的数据发挥作用，查询出数据来
where发挥作用时，表上并没有discount列，发挥完作用，形成的结果才有discount
对于结果中的列，如果再想筛选，须用having

小练习：

把good表中的商品名为'诺基亚xxxx'的商品，改为'HTCxxxx'

mysql> select goods_id,goods_name,concat('HTC',substring(goods_name,4)) from goods where goods_name like '诺基亚%';
+----------+------------------------------+---------------------------------------+
| goods_id | goods_name                   | concat('HTC',substring(goods_name,4)) |
+----------+------------------------------+---------------------------------------+
|        4 | 诺基亚n85原装充电器                       | HTCn85原装充电器                              |
|        3 | 诺基亚原装5800耳机                      | HTC原装5800耳机                             |
|        7 | 诺基亚n85原装立体声耳机hs-82                  | HTCn85原装立体声耳机hs-82                         |
|        9 | 诺基亚e66                         | HTCe66                                |
|       13 | 诺基亚5320 xpressmusic            | HTC5320 xpressmusic                   |
|       14 | 诺基亚5800xm                      | HTC5800xm                             |
|       23 | 诺基亚n96                         | HTCn96                                |
|       32 | 诺基亚n85                         | HTCn85                                |
+----------+------------------------------+---------------------------------------+
8 rows in set (0.00 sec)

面试题练习：

有如下表和数据
把num值处于[20,29]之间，改为20
num值处于[30,39]之间的，改为30

mysql> create table main (
    -> num int
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into main
    -> values
    -> (3),(12),(15),(25),(23),(29),(34),(32),(37),(45),(48),(52);
Query OK, 12 rows affected (0.01 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from main;
+------+
| num  |
+------+
|    3 |
|   12 |
|   15 |
|   25 |
|   23 |
|   29 |
|   34 |
|   32 |
|   37 |
|   45 |
|   48 |
|   52 |
+------+
12 rows in set (0.00 sec)

我的做法(分成两步)：

mysql> update main set num=20 where num between 20 and 29;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> update main set num=30 where num between 30 and 39;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from main;
+------+
| num  |
+------+
|    3 |
|   12 |
|   15 |
|   20 |
|   20 |
|   20 |
|   30 |
|   30 |
|   30 |
|   45 |
|   48 |
|   52 |
+------+
12 rows in set (0.00 sec)

标准答案（一步操作）：
update main set num=(num/10)*10 where num between 20 and 39;

分组查询：group

1. 查询出最贵的商品的价格，即shop_price最大的值查询出来 max
   select max(shop_price) from goods;
   同理：min
   select min(shop_price) from goods;
   查出发布最早的商品，即goods_id的最小值
   select min(goods_id) from goods;
2. 求总和：sum
   如：求库存多少件商品
   select sum(goods_number) from goods;
3. 求平均值：avg
   select avg(shop_price) from goods;
4. 求有多少种商品(多少行)：count()
   select count(*) from goods;
   count()中的参数不同时的区别：
   A：select count() from 表名，查询的就是绝对的行数，哪怕某一行所有的字段全为NULL，也计算在内
   B：而select count(列名) from 表名，查询是的该列不为NULL的所有行数
   在myisam引擎的表中，count()和count(1)是没有区别的，因为这种引擎的内部有一计数器在维护着行数
   而Innodb的表，用count(*)直接读行数，效率很低，因为innodb真的要去数一遍

【注】以上五个函数，单独使用的意义不大，要和分组配合起来使用

1. 计算第三个栏目下的所有商品的库存量之和
   select sum(goods_number) from goods where cat_id=3;
2. 一次性计算完，每一个栏目下的库存量之和
   group分组查询
   select cat_id,sum(goods_number) from goods group by cat_id;
   这样分了组查，意义更大
   【注】
   select cat_id,sum(goods_number) from goods;

mysql> select cat_id,sum(goods_number) from goods;
+--------+-------------------+
| cat_id | sum(goods_number) |
+--------+-------------------+
|      4 |               313 |
+--------+-------------------+
1 row in set (0.00 sec)

对于sql标准来说，这个语句是错误的，不能执行的，但是在mysql中可以执行，
而goods_id是把“第一次出现”的值取出来
这是mysql的一个特点，出于可移植性和规范性，不推荐这么写。
严格的讲，以group by a,b,c为例，只能在a,b,c里选择，语义上才没有矛盾

having和group综合应用

having是对结果进行操作，where是对表进行操作

SET sql_mode='NO_UNSIGNED_SUBTRACTION';

1. 查询出每种商品所积压的货款（商品*价格）
   select goods_id,shop_price,goods_number,shop_price*goods_number as hk from goods;
2. 查询该店积压的总货款
   select sum(shop_price*goods_number) from goods;
3. 查询每个栏目下所积压的货款
   提示：分组即可group
   select cat_id,sum(shop_price*goods_number) from goods group by cat_id;
4. 查询出栏目的积压货款金额，且筛选出积压金额>20000的栏目
   使用group和having
   select cat_id,sum(shop_price*goods_number) from goods group by cat_id having sum(shop_price*goods_number) > 20000;
5. 查询本店价比市场价省的钱，且筛选出省钱200以上的商品（用where和having分别实现）
   where:
   select goods_id,goods_name,(market_price-shop_price) as discount from goods where market_price-shop_price > 200;
   having:
   select goods_id,goods_name,(market_price-shop_price) as discount from goods having discount > 200;

where-having-group综合练习题

mysql> select * from result;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三     | 数学       |    90 |
| 张三     | 语文        |    50 |
| 张三     | 地理        |    40 |
| 李四     | 语文        |    55 |
| 李四     | 政治        |    45 |
| 王五     | 政治        |    30 |
+------+---------+-------+
6 rows in set (0.02 sec)

要求：查询出2门及2门以上不及格者的平均成绩：

1. 我的答案：
   select name,avg(score),count(score<60) from result group by name having count(score<60) >= 2;
   错误：

mysql> insert into result
    -> values
    -> ('赵六','语文',100),('赵六','数学',99),('赵六','思修',98);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from result;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三     | 数学       |    90 |
| 张三     | 语文        |    50 |
| 张三     | 地理        |    40 |
| 李四     | 语文        |    55 |
| 李四     | 政治        |    45 |
| 王五     | 政治        |    30 |
| 赵六     | 语文        |   100 |
| 赵六     | 数学       |    99 |
| 赵六     | 思修       |    98 |
+------+---------+-------+
9 rows in set (0.00 sec)

mysql> select name,avg(score),count(score<60) from result group by name having count(score<60) >= 2;
+------+------------+-----------------+
| name | avg(score) | count(score<60) |
+------+------------+-----------------+
| 张三     |    60.0000 |               3 |
| 李四     |    50.0000 |               2 |
| 赵六     |    99.0000 |               3 |
+------+------------+-----------------+
3 rows in set (0.00 sec)

mysql>

【注】因为count是帮你数有几行，所以，当>=2行的数据就会被记录下来

2. 正解：
   第一步：查询所有的平均分：

mysql> select name,avg(score) from result group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    60.0000 |
| 李四     |    50.0000 |
| 王五     |    30.0000 |
| 赵六     |    99.0000 |
+------+------------+
4 rows in set (0.00 sec)

第二步：想办法计算出每个人挂科的情况
select name,subject,score,score<60 as g from result;

mysql> select name,subject,score,score<60 as g from result;
+------+---------+-------+------+
| name | subject | score | g    |
+------+---------+-------+------+
| 张三     | 数学       |    90 |    0 |
| 张三     | 语文        |    50 |    1 |
| 张三     | 地理        |    40 |    1 |
| 李四     | 语文        |    55 |    1 |
| 李四     | 政治        |    45 |    1 |
| 王五     | 政治        |    30 |    1 |
| 赵六     | 语文        |   100 |    0 |
| 赵六     | 数学       |    99 |    0 |
| 赵六     | 思修       |    98 |    0 |
+------+---------+-------+------+
9 rows in set (0.00 sec)

综上：
select name,avg(score),sum(score<60) from result group by name;

mysql> select name,avg(score),sum(score<60) from result group by name;
+------+------------+---------------+
| name | avg(score) | sum(score<60) |
+------+------------+---------------+
| 张三     |    60.0000 |             2 |
| 李四     |    50.0000 |             2 |
| 王五     |    30.0000 |             1 |
| 赵六     |    99.0000 |             0 |
+------+------------+---------------+
4 rows in set (0.00 sec)

最终结果：
select name,avg(score),sum(score<60) from result group by name having sum(score<60) >=2;
【注】此处sum(score<60) >=2不能用where，因为where使用的是表中的值，而having使用的是结果，而sum()就是得出的结果，而不是表中的值

3. 运用子查询的方法：
   select name,count(1) as gks from result where score<60 group by name having gks>=2;

mysql> select name,count(1) as gks from result where score<60 group by name having gks>=2;
+------+-----+
| name | gks |
+------+-----+
| 张三     |   2 |
| 李四     |   2 |
+------+-----+
2 rows in set (0.00 sec)

这里是求出了满足2门或2门以上不及格的人，但是并未求平均值，所以，将这里的结果积当成表用，再次查询
select name from (select name,count(1) as gks from result where score<60 group by name having gks>=2) as tmp;

mysql> select name from (select name,count(1) as gks from result where score<60 group by name having gks>=2) as tmp;
+------+
| name |
+------+
| 张三     |
| 李四     |
+------+
2 rows in set (0.35 sec)

得到了符合要求的人，再直接求这两人的平均值
select name,avg(score) from result where name in ('张三','李四') group by name;

mysql> select name,avg(score) from result where name in ('张三','李四') group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    60.0000 |
| 李四     |    50.0000 |
+------+------------+
2 rows in set (0.00 sec)

最后将3个命令合并，形成3层嵌套的子查询：

select name,avg(score) from result where name in (select name from (select name,count(1) as gks from result where score<60 group by name having gks>=2) as tmp) group by name;

mysql> select name,avg(score) from result where name in (select name from (select name,count(1) as gks from result where score<60 group by name having gks>=2) as tmp) group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    60.0000 |
| 李四     |    50.0000 |
+------+------------+
2 rows in set (0.39 sec)

order by：排序

最终结果集出来后，可以再排序，反过来说：排序是针对最终结果集。即order by要放在where/group/having后面，顺序不能乱
排序语法：
order by 结果集中的列名 desc/asc
desc:升序
asc:降序

1. 取出第4个栏目下的商品，并按价格由高到低排序
   select goods_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc;
2. 多字段排序
   order by 列1 desc/asc , 列2 desc/asc , 列3 desc/asc;

limit限制条目

limit在语句的最后，起到限制条目的作用
limit [offset],N
offset:是跳过的行数
N:是实际取的行数
select goods_id,goods_name,shop_price from goods where cat_id=3 order by shop_price asc limit 10;

1. 查询出本店价格最高的前三名
   select goods_id,goods_name,shop_price from goods where cat_id=3 order by shop_price asc limit 0,3;
2. 查询出本店价格最高的第三名到第五名的商品
   select goods_id,goods_name,shop_price from goods where cat_id=3 order by shop_price asc limit 2,3;
   偏移量是2：跳过了第一名、第二名
   N是3，第三、四、五名
3. 按价格降序排列，最高的排前面，取1个，即第一名
   select goods_id,goods_name,shop_price from goods order by shop_price desc limit 0,1;
   如果offset是0，可以不写，因为offset默认为0

顺序：where,group by,having,order by,limit

取出每个栏目下的最新商品，即每个栏目下goods_is最大的商品

1. 我的方法：（无效）
   select cat_id ,goods_id,goods_name,shop_price from goods group by cat_id order by goods_id asc limit 0,1;
select cat_id ,goods_id,goods_name,shop_price from goods group by cat_id order by goods_id asc;
2. 答案：
   分析：
   A：先按照cat_id分组：
   select cat_id ,goods_id,goods_name,shop_price from goods group by cat_id;
   B：因为是取每个栏目下goods_id最大的商品，并且上面的语句是取出每个栏目的第一行，所以如果将goods_id先从大到小排列，再取出第一行，
   select cat_id ,goods_id,goods_name,shop_price from goods order by cat_id,goods_id desc;

这道题用一行sql语句，写不出来，需要用到子查询，emmmm，请看下一节