8：单表查询 -MySQL
作者：Yeats_Liao
- 2022-10-26 江西
本文字数：5688 字
阅读完需：约 19 分钟
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)
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【Yeats_Liao】的原创文章。
原文链接:【http://xie.infoq.cn/article/ac0bde277f5f6b9bb92be8978】。文章转载请联系作者。
Yeats_Liao
关注
Hello，World！ 2022-10-02 加入
这里更多的是记录个人学习，如果有侵权内容请联系我！ 个人邮箱是：yeats_liao@foxmail.com
评论