mysql 高级查询语句（1））

mysql 查询语句

目录

一 基本匹配条件

1.1  数值比较

要求字段必须是数值类型

类型 比较 例子
= 相等 uid = 3
> 大于 id > 3
>= 大于等于 id >= 3
< 小于 id < 3
<= 小于等于 id<= 3
!= 不相等 id != 3 
复制#查找passwd表中uid字段值 等于 2 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid=2;

#查找passwd表中uid字段值 小于 2的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid<2;

#查找passwd表中uid字段值 小于等于 2的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid<=2;

#查找passwd表中uid字段值 大于1000 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid>1000;

#查找passwd表中uid字段值 大于等于1000 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid>=1000;

#查找passwd表中uid字段值 不等于1000 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid!=1000;

#查找passwd表中uid字段值 不等于0 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid <> 0;

image-20210905143851151

image-20210905144219348

image-20210905144340214

image-20210905145640482



1.2 字符比较/匹配空/非空/null值

字段类型为字符类型

类型 比较 例子
= 相等 city='Beijing'
!= 不相等 city != 'Nanjing'
<> 不相等 city <> 'Nanjing'
is null null值 city is null
is not null 不是null值 city is not null

1.2.1 null值和空值的区别

空值（无值） 的长度为0，是不占用空间的。 Null 值的长度为Null，是占用空间的

空值（无值）可以参与运算，Null值不可以参与运算

复制insert into city values ('Nanjing');
insert into city values ('Beijing');
#添加空值
insert into city values (''); 
#添加null 值
insert into city values ();

#查看空值长度
select length('');
#查看Null值长度
select length(Null);

image-20210905151345237

image-20210905151441896



1.2.2 字符串匹配/比较

复制#查找name字段是'Nanjign'
select * from city where name = 'Nanjing';
#查找name字段 不是'Nanjign'
select * from city where name != 'Nanjing';
select * from city where name <> 'Nanjing';

#查找name字段值为空值
select * from city where name  =  '';
#查找name字段值不为空值
select * from city where name  <>  '';

#查找name字段值为Null值
select * from city where name  is null;
#查找name字段值 不为 Null值
select * from city where name  is  not null;

image-20210905152131021

image-20210905152904642

image-20210905153002062



1.3 逻辑匹配

多个判断条件时使用

类型 用途 格式
or 逻辑或 条件1 or 条件2 or 条件3.....
and 逻辑与 条件1 and 条件2 and 条件3.....
! 或 not 逻辑非 
复制#查找 uid小于2 且 gid 小于 2 的表记录
mysql> select  user_name,uid,gid from school.passwd where  uid<2 and gid<2;
+-----------+------+------+
| user_name | uid  | gid  |
+-----------+------+------+
| root      |    0 |    0 |
| bin       |    1 |    1 |
+-----------+------+------+
2 rows in set (0.00 sec)

#查看 uid 小于 2 或gid大于1000 的表记录
mysql> select  user_name,uid,gid from school.passwd where  uid<2 or  gid >1000;
+-----------+-------+-------+
| user_name | uid   | gid   |
+-----------+-------+-------+
| root      |     0 |     0 |
| bin       |     1 |     1 |
| nfsnobody | 65534 | 65534 |
| mysql     |  1001 |  1001 |
+-----------+-------+-------+

" NOT " 和 " ! "都是逻辑非运算符，返回和操作数相反的结果，具体语法规则为：

  • 当操作数为 0（假）时，返回值为 1；
  • 当操作数为非零值时，返回值为 0；
  • 当操作数为 NULL 时，返回值为 NULL。
复制# not和 ！ 返回操作数相反的结果，0则返回1，非0 则返回0
mysql> select not 1;
+-------+
| not 1 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select not 0;
+-------+
| not 0 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> select ! 1;
+-----+
| ! 1 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

mysql> select ! 0;
+-----+
| ! 0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)


#当 not 和！ 分别和数学运算符一起用。
#not 和 ！ 优先级不同. not 的优先级低于  + ，而！ 的优先级则高于+

#not 1+1 , 先计算1+1 ，在将结not 运算 ,即等同于 not 2，非零值取反则结果为 0
mysql> select not 1 + 1  ;
+-----------+
| not 1 + 1 |
+-----------+
|         0 |
+-----------+
1 row in set (0.01 sec)

# ! 1 + 1  先计算 ！1 ，在计算 ！1 结果和 1 相加。相当于  (!1) +1 ,(!1)结果为0，(!1)+1，等于0+1
mysql> select ! 1 + 1  ;
+---------+
| ! 1 + 1 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

#当操作数为Null 时，依旧返回Null
mysql> select ! NUll  ;
+--------+
| ! NUll |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select  not  NUll  ;
+-----------+
| not  NUll |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

mysql>


1.4 范围匹配

类型 比较
in (值列表） 在。。。里
not in (值列表） 不在。。。。里
between 。。。and 。。。。 在 。。。。之间 
复制#查找uid 值时 0，1，2，3，4，5的表记录
select user_name,uid,gid  from school.passwd where uid in (0,1,2,3,4,5);
#查找uid 值不是 0,1,2,3,4,5的表记录
select user_name,uid,gid  from school.passwd where uid not in (0,1,2,3,4,5);
#查找uid 值在 0到 5 之间的表记录
select user_name,uid,gid  from school.passwd where uid between 0  and 5 ;

image-20210905161141927

+---------------------+-------+-------+

image-20210905161307728

image-20210905161258105




二 高级匹配条件

2.1 like 模糊匹配

用法:

where 字段名 like ‘字符串或通配符’

通配符 "_" :表示一个字符

通配符"%" : 表示0-n个字符

复制#模糊匹配，user_name字段值包含st 字符串
select user_name,uid,gid from school.passwd where user_name like '%st%';

#模糊匹配，user_name字段值以字符串ro 开头
select user_name,uid,gid from school.passwd where user_name like 'ro%';

#模糊匹配，user_name字段值以 字符串 st 结尾
select user_name,uid,gid from school.passwd where user_name like '%st';

#模糊匹配，user_name字段值是 4 个字符串的
select user_name,uid,gid from school.passwd where user_name like '____';

#模糊匹配，user_name字段是 4 个字符串，且以st 结尾
select user_name,uid,gid from school.passwd where user_name like '__st';

image-20210905175512988

image-20210905175404158



2.2 正则表达式

用法:

where 字段名 regexp ‘正则表达式';

匹配模式 描述
^ 匹配文本的开始字符
$ 匹配文本的结束字符
. 匹配任意单个字符
+ 匹配前面字符1此或者多次
* 匹配前面字符任意次数（可以是0次）
字符串 匹配包含指定的字符串
a|b 匹配a或b
[.....] 匹配字符集中任意一个字符
[^....] 不匹配字符集中的字符
匹配前面字符串n次
匹配前面字符串 n次以上
匹配前面字符串 n次到 m次 
复制#匹配user_name字段ro开头的行
select user_name from school.passwd where user_name regexp '^ro';
#匹配user_name字段以st结尾的行
select user_name from school.passwd where user_name regexp 'st$';
#匹配user_name字段包含x或y或z的行
select user_name from school.passwd where user_name regexp '[xyz]';
#匹配user_name字段不包含字母a-w的行
select user_name from school.passwd where user_name regexp '[^a-w]';
#匹配user_name字段 包含字符ro，o的个数为任意个数，可以为0
select user_name from school.passwd where user_name regexp 'ro*';
#匹配user_name字段，包含字符ro的行
select user_name from school.passwd where user_name regexp 'ro.*';
#匹配包含o的行，o为连续的1个或连续的2个
select user_name from school.passwd where user_name regexp 'o{1,2}';
#匹配包含o的行，o为连续的2个
select user_name from school.passwd where user_name regexp 'o{2}';

image-20210905203203124

image-20210905203525842

image-20210905204528612

image-20210905204030151



三：操作查询结果

3.1 数学函数

数学函数 返回的结果
abs(x) 返回 x 的绝对值
rand() 返回 0 到 1 的随机数
mod(x,y) 返回 x 除以 y 以后的余数
power(x,y) 返回 x 的 y 次方
round(x) 返回离 x 最近的整数
round(x,y) 保留 x 的 y 位小数四舍五入后的值
sqrt(x) 返回 x 的平方根
truncate(x,y) 返回数字 x 截断为 y 位小数的值(直接接续小y位，不四舍五入）
ceil(x) 返回大于或等于 x 的最小整数
floor(x) 返回小于或等于 x 的最大整数
greatest(x1,x2…) 返回集合中最大的值
least(x1,x2…) 返回集合中最小的值 
复制mysql> select abs(-1), rand(),
    -> mod(10,2),power(2,5),
    -> round(1.234),round(1.234,2),
    -> sqrt(4),truncate(1.892,2),
    -> ceil(1.23),floor(1.985),
    -> greatest(1,2,3,4),least(1,2,3,4) \G
*************************** 1. row ***************************
          abs(-1): 1                    #取绝对值
           rand(): 0.7493982471054403    #返回0到1,之间任意值
        mod(10,2): 0                     #取余
       power(2,5): 32                   #返回次方结果
     round(1.234): 1                    #四舍五入取整
   round(1.234,2): 1.23                 #精确到多少位小数
          sqrt(4): 2                    #返回平方根
truncate(1.892,2): 1.89                  #直接截取多少位小数（不进行四舍五入）
       ceil(1.23): 2                    #返回大于或等于该数的整数
     floor(1.985): 1                    #返回小于或等于该数的整数
greatest(1,2,3,4): 4                    #返回集合中最大的值
   least(1,2,3,4): 1                    #返回集合中最小的值


3.2 聚合函数

聚合函数 返回的结果
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum(x) 返回指定列的所有值之和 
复制#school.class 表
mysql> select * from class;
+----+-------------+-------+
| id | name        | score |
+----+-------------+-------+
|  1 | zhangsan    |   100 |
|  2 | lisi        |    70 |
|  3 | wanglei     |    80 |
|  4 | dingcong    |    90 |
|  5 | linrui      |    60 |
|  6 | zhoujiazhen |    75 |
|  7 | houlu       |    55 |
+----+-------------+-------+


#查看socre 字段平均值
mysql> select  avg(score)  from school.class;
+------------+
| avg(score) |
+------------+
|    75.7143 |
+------------+
1 row in set (0.00 sec)

#查看非null的表记录总数
mysql> select  count(score) from school.class;
+--------------+
| count(score) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

#查看你socre 字段最小值
mysql> select  min(score)  from  school.class;
+------------+
| min(score) |
+------------+
|         55 |
+------------+
1 row in set (0.00 sec)

#查看score字段最大值
mysql> select  max(score) from school.class;
+------------+
| max(score) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

#查看score字段总和
mysql> select  sum(score) from school.class;
+------------+
| sum(score) |
+------------+
|        530 |
+------------+
1 row in set (0.00 sec)


3.3 字符串函数

字符串函数 返回的结果
trim() 返回去除指定格式的值
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x) 返回字符串 x 的长度
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
upper(x) 将字符串 x 的所有字母变成大写字母
lower(x) 将字符串 x 的所有字母变成小写字母
left(x,y) 返回字符串 x 的前 y 个字符
right(x,y) 返回字符串 x 的后 y 个字符
repeat(x,y) 将字符串 x 重复 y 次
space(x) 返回 x 个空格
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
reverse(x) 将字符串 x 反转 
复制#从字符串'my class' 中去除 字符串’ss‘
mysql> select trim( 'ss' from 'my class');
+-----------------------------+
| trim( 'ss' from 'my class') |
+-----------------------------+
| my cla                      |
+-----------------------------+

#将字符创 'my',字符串'class' 拼接
mysql> select concat('my','class');
+----------------------+
| concat('my','class') |
+----------------------+
| myclass              |
+----------------------+

#截取字符串'myclass',从第三个字符截取到末尾
mysql> select substr('myclass',3);
+---------------------+
| substr('myclass',3) |
+---------------------+
| class               |
+---------------------+
1 row in set (0.00 sec)

#截取字符串’myclass‘ 从第3个字符开始截取，截取2位
mysql> select substr('myclass',3,2);
+-----------------------+
| substr('myclass',3,2) |
+-----------------------+
| cl                    |
+-----------------------+
1 row in set (0.00 sec)

#获取字符串'myclass'的长度
mysql> select length('myclass');
+-------------------+
| length('myclass') |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

#将字符串'myclass'中的 ’my' 使用 'your' 替换
mysql> select replace('myclass','my','your');
+--------------------------------+
| replace('myclass','my','your') |
+--------------------------------+
| yourclass                      |
+--------------------------------+
1 row in set (0.00 sec)


3.4 四则运算

运算操作

符号 用途 例子
+ 加法 uid + gid
- 减法 uid - gid
* 乘法 uid * gid
/ 除法 uid / gid
% 取余（求模） uid % gid
（） 提高优先级 (uid +gid) / 2 
复制#显示socre字段值 +100 后的结果
mysql> select score+100 from school.class;
+-----------+
| score+100 |
+-----------+
|       200 |
|       170 |
|       180 |
|       190 |
|       160 |
|       175 |
|       155 |
+-----------+
7 rows in set (0.00 sec)

#显示score字段值除以2后的结果
mysql> select score/2 from school.class;
+---------+
| score/2 |
+---------+
| 50.0000 |
| 35.0000 |
| 40.0000 |
| 45.0000 |
| 30.0000 |
| 37.5000 |
| 27.5000 |
+---------+
7 rows in set (0.00 sec)

#显示score字段值取余2 的结果
mysql> select score%2 from school.class;
+---------+
| score%2 |
+---------+
|       0 |
|       0 |
|       0 |
|       0 |
|       0 |
|       1 |
|       1 |
+---------+
7 rows in set (0.00 sec)

#显示score字段值先加100，在除以2 的结果
mysql> select (score+100)/2 from school.class;
+---------------+
| (score+100)/2 |
+---------------+
|      100.0000 |
|       85.0000 |
|       90.0000 |
|       95.0000 |
|       80.0000 |
|       87.5000 |
|       77.5000 |
+---------------+
7 rows in set (0.00 sec)

3.5 order by 查询结果排序

用法

sql 查询 order by 字段名 [asc|desc];

asc ： 升序排列（默认，可以省略）

desc：降序排列

复制#将查询结果，按照字段 score 的值，升序排列
mysql> select * from school.class order by score;
+----+-------------+-------+
| id | name        | score |
+----+-------------+-------+
|  7 | houlu       |    55 |
|  5 | linrui      |    60 |
|  2 | lisi        |    70 |
|  6 | zhoujiazhen |    75 |
|  3 | wanglei     |    80 |
|  4 | dingcong    |    90 |
|  1 | zhangsan    |   100 |
+----+-------------+-------+
7 rows in set (0.00 sec)

#将查询结果，按照字段score的值降序排列
mysql> select * from school.class order by score desc;
+----+-------------+-------+
| id | name        | score |
+----+-------------+-------+
|  1 | zhangsan    |   100 |
|  4 | dingcong    |    90 |
|  3 | wanglei     |    80 |
|  6 | zhoujiazhen |    75 |
|  2 | lisi        |    70 |
|  5 | linrui      |    60 |
|  7 | houlu       |    55 |
+----+-------------+-------+
7 rows in set (0.00 sec)


3.6group  by  查询结果分组

用法：

sql 查询 group by 字段名；

对GROUP BY 后面的查询结果进行汇总分组，通常是结合聚合函数一起使用

GROUP BY有个原则，select 后面的所有列中，没有用聚合函数的列，必须出现在GROUP BY后面

复制#数据表内容如下
mysql> select * from project;
+---------+-------------+-------+
| project | name        | score |
+---------+-------------+-------+
| chanese | houlu       |   100 |
| chanese | linrui      |    80 |
| chanese | wanglei     |    40 |
| chanese | zhoujiazhen |    60 |
| math    | zhoujiazhen |    60 |
| math    | linrui      |    60 |
| math    | wanglei     |   100 |
| math    | houlu       |    90 |
| english | houlu       |    80 |
| english | wanglei     |    60 |
| english | zhoujiazhen |    80 |
| english | linrui      |   100 |
+---------+-------------+-------+
12 rows in set (0.00 sec)

#以name字段分组，查看name字段和score字段的总和，
mysql> select name,sum(score) from project group by name;
+-------------+------------+
| name        | sum(score) |
+-------------+------------+
| houlu       |        270 |
| linrui      |        240 |
| wanglei     |        200 |
| zhoujiazhen |        200 |
+-------------+------------+
4 rows in set (0.00 sec)


3.7 distinct 去重显示

distinct 不显示重复的资料

格式:

select distinct 字段名 from 表名 ;

复制#从project表中，查询project字段的值，并且不显示重复的
mysql> select distinct  project from project;
+---------+
| project |
+---------+
| chanese |
| math    |
| english |
+---------+
3 rows in set (0.00 sec)


3.8 having  查询结果过滤

用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用

HAVING的存在弥补了WHERE关键字不能和聚合函数联合使用的不足，如果被select的只有函数栏，那就不需要GROUP BY子句

格式：

sql查询 having 条件表达式

 
复制#按照 name字段分组，查询name字段，和score字段值最小值，然后显示score最小值，小于或等于60的数据
mysql> select name,min(score) from project  group by name having min(score) <= 60;
+-------------+------------+
| name        | min(score) |
+-------------+------------+
| linrui      |         60 |
| wanglei     |         40 |
| zhoujiazhen |         60 |
+-------------+------------+
3 rows in set (0.00 sec)

#先查询socre字段值小于等于60的表记录，然后，在查找name字段是'zhoujiazhen'的
mysql> select *  from project where score  <= 60 having name='zhoujiazhen';
+---------+-------------+-------+
| project | name        | score |
+---------+-------------+-------+
| chanese | zhoujiazhen |    60 |
| math    | zhoujiazhen |    60 |
+---------+-------------+-------+


3.9  as  别名

栏位別名 表格別名

语法：SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";　　

复制#将 avg_score作为avg(score)的别名
mysql> select project,avg(score) avg_score from project group by project;
+---------+-----------+
| project | avg_score |
+---------+-----------+
| chanese |   70.0000 |
| english |   80.0000 |
| math    |   77.5000 |
+---------+-----------+
3 rows in set (0.00 sec)
posted @   知己一语  阅读(206)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 25岁的心里话
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示