八、匹配条件（查看 修改 删除记录时可以加条件）

1.1 数值比较 字段名 符号 数字

= != < <= > >=

select username from usertab where uid=10;
select id,username,uid from usertab where uid=1001;
select * from usertab where id<=10;

1.2 字符比较 字段名 符号 “字符串”
= !=
select username from usertab where username="apache";
select username,shell from usertab where shell="/bin/bash";
select username,shell from usertab where shell!="/bin/bash";

1.3 范围内比较
字段名 between 数字1 and 数字2 在...之间...
字段名 in (值列表) 在...里
字段名 not in (值列表) 不在...里
select username from usertab where uid between 100 and 150;

select username,uid from usertab where uid in (10,20,30,50);

select username,uid from usertab where username in ("root","rsync","mysql");

select username from usertab where username not in ("root","bin");

1.4 逻辑比较（就是有个查询条件）
逻辑与 and 多个条件同时成立 才匹配
逻辑或 or 多个条件,某一个条件成立 就匹配
逻辑非 ！ 或 not 取反

select username,uid from usertab where username="root" and uid=0 and shell="/bin/bash";

select username,uid from usertab where username="root" or uid=1 or shell="/bin/bash";

select username,uid from usertab where username="root" or username="apache" or username="bob";

1.5 匹配空 字段名 is null
匹配空 字段名 is not null

select username,uid,gid from usertab
where
uid is null and gid is null;

mysql> update usertab set uid=3000,gid=3000 where username="lucy";

select id from usertab where name="yaya" and uid is not null;

update usertab set username=null where id=2;

1.6 模糊匹配
字段名 like '表达式';
% 表示零个或多个字符
_ 表任意一个字符

select username from usertab where username like '_ _ _ _';
select username from usertab where username like 'a_ _t';

insert into usertab(username)values("a");

select username from usertab where username like 'a%';
select username from usertab where username like '_%_';

1.7 正则匹配
字段名 regexp '正则表达式';
^ $ . * [ ]
select username from usertab where username regexp '[0-9]';
select username from usertab where username regexp '^[0-9]';
select username from usertab where username regexp '[0-9]$';

select username from usertab where username regexp 'a.*t';

select username from usertab where username regexp '^a.*t$';

select username,uid from usertab where uid regexp '..';
select username,uid from usertab where uid regexp '^..$';

1.8 四则运算(select 和 update 操作是可以做数学计算)
字段类型必须数值类型（整型 或浮点型）
+ - * / %

select id,username,uid from usertab where id <=10;
update usertab set uid=uid+1 where id <=10;

select username ,uid,gid from usertab where usernane="mysql";
select username ,uid,gid, uid+gid as zh from usertab where username="mysql";

select username ,uid,gid, uid+gid as zh , (uid+gid)/2 as pjz from usertab where username="mysql";

alter table usertab add age tinyint(2) unsigned default 21 after username;

mysql> select username,age from usertab;
select username , age , 2018-age s_year from usertab where username="root";

1.9聚集函数（对字段的值做统计，字段的类型要求是数值类型）
count(字段名）统计字段值的个数
sum(字段名） 求和
max(字段名） 输出字段值的最大值
min(字段名） 输出字段值的最小值
avg(字段名） 输出字段值的平均值
select max(uid) from usertab;
select sum(uid) from usertab;
select min(uid) from usertab;
select avg(uid) from usertab;
select count(id) from usertab;
select count(username) from usertab where shell="/bin/bash";

1.10 查询不显示字段重复值 distinct 字段名

select distinct shell from usertab;

select distinct shell from usertab where uid >10 and uid<=100;

1.11查询分组
sql查询 group by 字段名；

select shell from usertab where uid >10 and uid<=100
group by shell;

1.12 查询排序 (按照数值类型的字段排队)
sql查询 order by 字段名 asc|desc;

select username,uid from usertab where uid >10 and uid<=100
order by uid;

select username,uid from usertab where uid >10 and uid<=100
order by uid desc;

1.13 限制查询显示行数(默认显示所有查询的记录)
sql查询 limit 数字； 显示查询结果的前几行
sql查询 limit 数字1，数字2； 显示查询结果指定范围的行

select username,uid from usertab where uid >10 and uid<=100
order by uid desc limit 1;

select username,uid from usertab where uid >10 and uid<=100
order by uid desc limit 2,3;