Mysql-匹配条件

基本匹配条件

1.数值比较::= != >= <=

# 查询uid小于13的用户
mysql> select name,uid from user where uid<=13;

# 查询id=10的用户
mysql> select name,uid from user where id=10;

# 查询uid和gid不相等用户
mysql> select name,uid from user where uid != gid;

2.字符比较: = !=

# 查询shell不等于/bin/bash的用户
mysql> select name,shell from user where shell !="/bin/bash";

# 查询name是mysql的用户
mysql> select name from user where name="mysql";

3.匹配空或者非空:is null或 is not null

mysql> insert into user(name) values(""),(null),("null");
# 查询name为非空
mysql> select id,name from user where name is not null;

# 查询name为
mysql> select id,name from user where name is null;

# 查询字段为空值
mysql> select id,name from user where name =  "";

4.逻辑匹配:and or !/not

# 查询name等于root id=0 shell等于/bin/bash的字段
mysql> select id,name from user where name="root" and id=0 and shell="/bin/bash";

# 查询name等于null 或者name 等于""的字段
mysql> select id,name from user where name="null" or name="";

# 查询name不等于"/bin/bash"
mysql> select id,name from user where shell!="/bin/bash";

5.匹配任意范围内的值
in (值列表):在…里
not in (值列表): 不在…里…
between 数字 and 数字:在…之间

# 查询uid 在10和50之间的用户
mysql> select id,name from user where uid between 10 and 50;

# 查询uid 在3,6,9,12中的用户
mysql> select id,name from user where uid in (3,6,9,12);

# 查询name是"sync","apache","bin" 
mysql> select id,name from user where name in ("sync","apache","bin");

# 查询shell不在"/bin/bash"和"/sbin/nologin"里面
mysql> select id,name from user where shell not in ("/bin/bash","/sbin/nologin");

模糊匹配

where 字段名 like ‘通配符’
-:表示一个字符

%:表示0~n个字符

#匹配名字中油三个字符的
mysql> select name from user where  name like '___';

#匹配名字中包含a的
mysql> select name from user where name like "%a%";

 #匹配任意字符
 mysql> select name from user where name like "_%_";

#匹配以a开头的
mysql> select name from user where name like "a%";

正则表达式

where 字段名 regexp '正则表达式

正则元字符:^ $ . [] * |

#插入字段
mysql> insert into user(name) values("yaya9"),("y6aya"),("*tom5");

#查找包含任意两个字符的字段
mysql> select id,name from user where uid regexp "..";

# 查询任意字段开头 任意字段结尾
mysql> select id,name from user where uid regexp "^..$";

操作查询结果

distinct 字段名 去重显示

mysql> select distinct uid from user;

四则运算

• • • / %

#求Uid是偶数的
mysql> select id,name from user where uid %2=0;

#求uid是奇数的
mysql> select id,name from user where uid %2 !=0;

#添加字段 age 默认值为19
mysql> alter table user add
    -> age int default 19 after name;
#求用户的生日
mysql> select id,name,2020 -age as birthday  from user;

#求用户uid和gid的平均值,默认保留四位小数
mysql> select uid,gid,(uid+gid)/2 from user;

聚集函数

avg:统计字段平均值
sum:统计字段之和
min:统计字段最小值
max:统计字段最大值
count:统计字段的个数

# 统计shell != "/bin/bash"的数量
mysql> select count(name) from user where shell !="/bin/bash";

# 统计user表中的数量
mysql> select count(*) from user;

#求uid的最大值
mysql> select sum(uid) from user ;

#求uid的最小值
mysql> select min(uid) from user;

#求uid的平均值 结果保留四位小数
mysql> select avg(uid) from user; 

排序

order by 字段名 [asc|desc]

mysql> select uid,name from user where uid>=10 and uid <=500 #默认升序
    -> order by uid;

mysql> select uid,name from user where uid>=10 and uid<=500  
    -> order by uid desc;

查询结果分组

group by 字段;

mysql> select shell from user where uid<=10 
    -> group by shell;

mysql> select shell from user group by shell;

查询结果过滤

having 条件表达式

mysql> select name from user where shell != "/bin/bash" having name="mysql";

mysql> select name from user where uid <=10 having name="bin";

限制查询结果显示行数

limit 数字;
limit 起始行,总行数;起始行是从0开始的

mysql> select name from user where id<10 limit 3;
mysql> select * from user where id <10 limit 2,2;

其他查询方式:多表查询,嵌套查询 连接查询(左连接 右连接 内连接 外连接数)