(目录)

8.1 开端

细说查询语句

8.2 select

可以查询不存在的表，也可以做计算，别名操作使用as 或者省略

mysql> select 2*7 as res;
+-----+
| res |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

8.3 from

来自哪张表，返回笛卡尔积

mysql> select * from stu;
+-------+-------+
| stuId | name  |
+-------+-------+
|     4 | frank |
+-------+-------+
1 row in set (0.00 sec)

mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)

mysql> select * from stu,eatery;
+-------+-------+----+----------+-------+
| stuId | name  | id | money    | stuId |
+-------+-------+----+----------+-------+
|     4 | frank |  1 |  20.5000 |  NULL |
|     4 | frank |  2 |  78.6000 |     4 |
|     4 | frank |  3 |  99.9000 |  NULL |
|     4 | frank |  4 | 748.4000 |     4 |
|     4 | frank |  5 | 748.4000 |  NULL |
+-------+-------+----+----------+-------+
5 rows in set (0.00 sec)

8.4 dual

dual表丝滑默认的伪表，你可以在没有表的情况下指定一个虚拟的表名

mysql> select 2*7 as res from dual; /*计算器*/
+-----+
| res |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

8.5 where

1.用来筛选

mysql> select * from teacher;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
|  4 | Tom  | NULL   | NULL     |
+----+------+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from teacher where id = 1;
+----+------+-------+----------+
| id | name | phone | address  |
+----+------+-------+----------+
|  1 | Tom  | NULL  | 暂时未知 |
+----+------+-------+----------+
1 row in set (0.00 sec)

2.设置< > = != and or 等条件

mysql> select * from teacher where id = 1 or phone = '123456';
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+
2 rows in set (0.00 sec)

8.6 in

用in代替等号的一种，或者用not in代替不等号

mysql> select * from teacher where address in ('ShangHai');
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+
1 row in set (0.00 sec)

mysql> select * from teacher where address not in ('ShangHai');
+----+------+-------+----------+
| id | name | phone | address  |
+----+------+-------+----------+
|  1 | Tom  | NULL  | 暂时未知 |
|  2 | Tom  | NULL  | 暂时未知 |
+----+------+-------+----------+
2 rows in set (0.00 sec)

8.7 between...and

与 > and < 不同，between and表示在什么之间，会取等于两个数字之间的值

mysql>  select * from teacher where id >1 and id < 4;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+
2 rows in set (0.00 sec)

mysql>  select * from teacher where id between 1 and 4;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
|  4 | Tom  | NULL   | NULL     |
+----+------+--------+----------+
4 rows in set (0.00 sec)

mysql>  select * from teacher where id not between 1 and 4;
Empty set (0.00 sec)

8.8 is null

查询数据是否为空

mysql>  select * from teacher;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
|  4 | Tom  | NULL   | NULL     |
+----+------+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from teacher where address is null;
+----+------+-------+---------+
| id | name | phone | address |
+----+------+-------+---------+
|  4 | Tom  | NULL  | NULL    |
+----+------+-------+---------+
1 row in set (0.00 sec)

mysql> select * from teacher where address is not null;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+
3 rows in set (0.00 sec)

8.9 聚合函数

sum、avg、count等函数的使用

mysql> select * from score;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      99 |      99 |   99 |
|    2 |      77 |      94 |   96 |
+------+---------+---------+------+
2 rows in set (0.00 sec)

mysql> select sum(chinese) from score;
+--------------+
| sum(chinese) |
+--------------+
|          176 |
+--------------+
1 row in set (0.00 sec)

mysql> select avg(chinese) from score;
+--------------+
| avg(chinese) |
+--------------+
|      88.0000 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(chinese) from score;
+----------------+
| count(chinese) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

统计所有数据的总数

count(1)计算一共有多少符合条件的行，1并不是表示第一个字段，而是表示一个固定值

count(*)（是针对全表）将返回表格中所有存在的行的总数包括值为null的行

count(列名)（是针对某一列）将返回表格中某一列除去null以外的所有行的总数

mysql> select count(*) from score;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from score;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

8.10 第三方客户端的使用

学习使用Navicat Premium 15

8.11 like模糊查询

% 表示任意0个或多个字符

_ 表示任意单个字符

mysql> select * from teacher where name like 'T%';
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
|  4 | Tom  | NULL   | NULL     |
+----+------+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from teacher where name like 'T_';
Empty set (0.00 sec)

mysql> select * from teacher where name like 'To_';
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
|  4 | Tom  | NULL   | NULL     |
+----+------+--------+----------+
4 rows in set (0.00 sec)

8.12 order by 排序查询

desc表示降序，asc表示升序

mysql> select * from score;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      99 |      99 |   99 |
|    2 |      77 |      94 |   96 |
+------+---------+---------+------+
2 rows in set (0.00 sec)

mysql> select * from score order by chinese desc;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      99 |      99 |   99 |
|    2 |      77 |      94 |   96 |
+------+---------+---------+------+
2 rows in set (0.00 sec)


mysql> select * from score order by chinese asc;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    2 |      77 |      94 |   96 |
|    1 |      99 |      99 |   99 |
+------+---------+---------+------+
2 rows in set (0.00 sec)

8.13 group by 分组查询

mysql> select 聚合函数 as 'xx' ,分组字段 as 'xx' ' from info group by xx;

mysql> select avg(age) as '年龄' ,gender as '性别' ' from info group by gender;

查询的字段必须是分组字段和聚合函数

8.14 group_concat

concat()函数将多个字符串连接成一个字符串

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)

mysql> select stuId ,group_concat(money) from eatery group by stuId;
+-------+--------------------------+
| stuId | group_concat(money)      |
+-------+--------------------------+
|  NULL | 20.5000,99.9000,748.4000 |
|     4 | 78.6000,748.4000         |
+-------+--------------------------+
2 rows in set (0.00 sec)

通过使用distinct可以排除重复值

mysql> select stuId ,group_concat(distinct money) from eatery group by stuId;
+-------+------------------------------+
| stuId | group_concat(distinct money) |
+-------+------------------------------+
|  NULL | 20.5000,99.9000,748.4000     |
|     4 | 78.6000,748.4000             |
+-------+------------------------------+
2 rows in set (0.00 sec)

使用order by子句对结果中的值进行排序

mysql> select stuId ,group_concat(distinct money order by money desc) from eatery group by stuId;
+-------+--------------------------------------------------+
| stuId | group_concat(distinct money order by money desc) |
+-------+--------------------------------------------------+
|  NULL | 748.4000,99.9000,20.5000                         |
|     4 | 748.4000,78.6000                                 |
+-------+--------------------------------------------------+
2 rows in set (0.00 sec)

当然可以非常长，separator是一个字符串值，缺省为一个逗号

mysql> select stuId ,group_concat(distinct money order by money desc separator '||') from eatery group by stuId;
+-------+-----------------------------------------------------------------+
| stuId | group_concat(distinct money order by money desc separator '||') |
+-------+-----------------------------------------------------------------+
|  NULL | 748.4000||99.9000||20.5000                                      |
|     4 | 748.4000||78.6000                                               |
+-------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

8.15 having

在 SQL 中增加having子句原因是where关键字无法与合计函数一起使用

having子句可以让我们筛选分组后的各组数据。

mysql> select avg(money) as '$' ,stuId as 'stuId' from eatery group by stuId having $>400;
+--------------+-------+
| $            | stuId |
+--------------+-------+
| 413.50000000 |     4 |
+--------------+-------+
1 row in set (0.00 sec)

8.16 limit

limit子句被用于强制select语句返回指定的记录数，初始记录行的偏移量是0而不是1

mysql> select * from eatery limit 0,2;
+----+---------+-------+
| id | money   | stuId |
+----+---------+-------+
|  1 | 20.5000 |  NULL |
|  2 | 78.6000 |     4 |
+----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from eatery limit 3;
+----+---------+-------+
| id | money   | stuId |
+----+---------+-------+
|  1 | 20.5000 |  NULL |
|  2 | 78.6000 |     4 |
|  3 | 99.9000 |  NULL |
+----+---------+-------+
3 rows in set (0.00 sec)

8.17 distinct all

去重

mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)

mysql> select distinct money from eatery;
+----------+
| money    |
+----------+
|  20.5000 |
|  78.6000 |
|  99.9000 |
| 748.4000 |
+----------+
4 rows in set (0.00 sec)