写点什么

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 聚合函数

sumavgcount等函数的使用


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
用户头像

Yeats_Liao

关注

Hello,World! 2022-10-02 加入

这里更多的是记录个人学习,如果有侵权内容请联系我! 个人邮箱是:yeats_liao@foxmail.com

评论

发布
暂无评论
8:单表查询-MySQL_数据库_Yeats_Liao_InfoQ写作社区