匹配条件
基本匹配条件(适用于 select、 update、 delete 记录操作)
- 数值比较
- 字符比较/匹配空/匹配非空
- 逻辑匹配
- 范围匹配/去重显示
高级匹配条件(适用于 select、 update、 delete 记录操作)
- 模糊查询
- 正则表达式
- 四则运算
操作查询结果(对查询后的数据做处理)
- 聚集函数
- 查询结果排序
- 查询结果分组
- 查询结果过滤
- 限制查询结果显示行数
基本匹配条件
-
数值比较
字段必须是数值类型
类型 比较 例子 = 相等 id = 3 > 大于 id > 3 >= 大于或小于 id >=3 < 小于 id < 3 <= 小于或等于 id <= 3 != 不相等 id != 3 mysql> select * from user where id=5; mysql> select * from user where id>5; mysql> select * from user where id<=5; # 也可以int字段来进行比较 mysql> select * from user where uid>id; -
字符比较/匹配空/匹配非空
mysql> select * from user where shell="/bin/bash"; mysql> select * from user where shell!="/bin/bash"; mysql> select * from user where shell is null; mysql> select * from user where shell is not null; -
范围匹配/去重显示
类型 比较 in(值列表)在...里... not in(值列表)不在...里.. between 数字 and 数字在...之间 distinct 字段名去重显示 mysql> select * from user where username in ("root","mysql","daemon"); mysql> select * from user where username not in ("root","mysql","daemon"); mysql> select * from user where uid in (0,1,2,3); mysql> select * from user where uid between 1 and 10; mysql> select distinct shell from user; -
逻辑匹配(多个匹配条件)
or 逻辑或 # 多个匹配条件，某一个条件成立即可 and 逻辑与 # 多个条件匹配 必须同时成立 ! 或 not 逻辑非 # 取反 =, !=, not, in, is, not nullmysql> select * from user where id=1 or id=2; mysql> select * from user where id=1 and username="root";
高级匹配条件
-
模糊查询
where 字段名 like '表达式'; '_' 表示1个字符 '%' 表示0~n个字符mysql> select * from user where username like "____"; mysql> select * from user where username like "%oo%"; mysql> select * from user where username like "r%"; mysql> select * from user where username like "__%__"; -
正则匹配
where 字段名 regexp "正则表达式"; 元字符: ^ $ . [] * |# 查询username字段以r或m开头的字段 mysql> select * from user where username regexp "^r|^m"; # 查询username字段以t或l结尾的字段 mysql> select * from user where username regexp "[tl]$"; -
四则运算
符号 用途 例子 + 加法 uid + gid- 减法 uid - gid* 乘法 uid * gid/ 除法 uid / gid% 取余数(求模) uid % gid() 提高优先级 ( uid + gid ) / 2# 准备条件: 为user表添加age字段 mysql> alter table user add age tinyint unsigned default 19 after username; # 显示出生年份(用当前年份减去age字段) # birthday_year: 为“2021-age”的结果取一个名词，用来显示 mysql> select username,2021 - age birthday_year,age from user where username="root"; mysql> select username,2021 - age birthday_year,age from user; # 为uid字段小于等于5的加一 mysql> select username,uid from user where uid <= 5; mysql> update user set uid=uid+1 where uid <= 5; mysql> select username,uid from user; # 计算uid和gid的平均值 mysql> select username,uid,gid,(uid+gid)/2 pjz from user; # 计算uid字段位偶数时, uid和gid的平均值 mysql> select username,uid,gid,(uid+gid)/2 pjz from user where uid % 2 = 0;
操作查询结果
-
聚集函数
MySQL内置数据统计函数 - avg(字段名) # 统计字段平均值 - sum(字段名) # 统计字段之和 - min(字段名) # 统计字段最小值 - max(字段名) # 统计字段最大值 - count(字段名) # 统计字段值个数mysql> select max(uid) from user; mysql> select min(uid) from user; mysql> select max(uid) from user where uid >=10 and uid <=20; mysql> select count(*) from user; mysql> select count(username) from user where shell="/sbin/nologin"; -
查询结果排序
SQL查询 order by 字段名 [asc|desc]; - asc 升序排序 # 默认选择是升序排序 - desc 降序排序 - 字段名 通常是数值类型字段mysql> select username,uid from user where uid<=1000 order by uid; mysql> select username,uid from user where uid<=1000 order by uid desc; -
查询结果分组
group by 字段名; 分组处理结果与去重相同，但是执行的过程不一样 - 去重是对每一行的数据进行判断 - 分组是对查出来的结果进行判断 效率更高，占用更少mysql> select shell from user group by shell; mysql> select shell from user where uid <=1000 group by shell; -
查询结果过滤
having 条件 同理 在查询的结果中进行查询，不用检索全表的信息，效率更快，节省系统的资源mysql> select username,uid from user where uid <=1000 having uid=59; -
限制查询结果显示行数
limit 只显示查询结果的前几行: limit 数字; 只显示查询结果指定范围的行: limit 数字1, 数字2; - 数字1 显示的起始行 - 数字2 显示的总行数 - limit 3,3; 从第三行开始，显示三行 # mysql的下标从0开始mysql> select id,username,uid from user where uid <=1000 limit 2,3;