1、基本查询条件
l 数值比较:字段类型必须数据数值类型
= != < <= > >=
exp:
select username from usertab where uid=10;
select id,username,uid from usertab where uid=1001;
select * from usertab where id<=10;
l 字符比较 / 匹配空 / 非空
= 相等; != 不相等
匹配空 字段名 is null
匹配非空 字段名 is not null
exp:
select username from usertab where username="apache";
select username,shell from usertab where shell!="/bin/bash";
update user1 set uid=132 where uid is null;
select name,uid from user1 where uid is not null;
l 逻辑比较(就是有个查询条件)
逻辑与 and 多个条件同时成立 才匹配
逻辑或 or 多个条件,某一个条件成立 就匹配
逻辑非 ! 或 not 取反
提高优先级 ( )
exp:
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";
select id,name,uid from user1 where (uid=2 or id=1 ) and name="root"
l 范围内匹配 / 去重显示: 匹配范围内的任意一个值即可
字段名 between 数字1 and 数字2 //在...之间...
字段名 in (值列表) //在...里
字段名 not in (值列表) //不在...里
DISTINCT 字段名 //去重显示,查询不显示字段重复值
exp:
select username from usertab where uid between 10 and 25;
select username,uid from usertab where uid in (10,2,5,50);
select username,uid from usertab where username in (“root”,”bin”,”hash”);
select username from usertab where username not in (“root”,”bin”);
select distinct shell from usertab;
select distinct shell from usertab where uid>10 and uid<=100;
2、高级查询条件
l 模糊匹配
基本用法
– WHERE 字段名 LIKE ' 通配字串 '
– 通配符 _ //匹配单个字符(表任意一个字符)
– % //匹配 0~N 个字符(表示零个或多个字符)
exp:
select username from usertab where username like “_ _ _”;
select username from usertab where username like “a_ _t”;
select username from usertab where username like 'a%';
select username from usertab where username like '_%_';
l 正则匹配
基本用法
– WHERE 字段名 REGEXP ‘正则表达式’
– ^ $ . [ ] *
exp:
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 '^..$';
l 四则运算 : (select 和 update 操作是可以做数学计算)
运算操作
– 字段必须是数值类型(整型 或浮点型)
+ - * / %
exp:
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;
select username , age , 2018-age s_year from usertab where username="root";