(目录)
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)