Navicat工具单表查询
精确查询:
相等:=
不相等:!=、<>
例:select * from xyb where xm='XXX'
select * from xyb where xm!='XXX'/xm<>'XXX'
模糊查询:like/ not like
%:匹配多个字符
_:匹配单个字符,且必须有单个字符
例:select xm from xyb where xm like '%X%'/xm like 'X_'
比较查询:>,<,>=,<=
例:select nl from xyb where nl>18/ nl<18/ nl<=18/ nl>=18
逻辑运算:and/or
and优先级高于or
例:select xm from xyb where jg='上海' or jg='北京'
select xm from xyb where xm='XXX' and jg='上海'
区间运算:between...and
例:select * from xyb where nl between 18 and 20
集合运算:in/not in
例:select xm from xyb where jg in('北京','上海')
select xm from xyb where jg ont in('上海','北京')
非空运算:is null/is not null
例:select xm from xyb where xm is null
select xm from xyb where xm is not null
排序:order by 字段名
desc:降序
asc:升序,默认为升序
例:select nl from xyb where nl order by nl desc
例:select nl from xyb where nl order by nl asc
去重关键字:distinct 字段名
例:select distinct jg from xyb
聚合函数:
max(字段名):最大值
min(字段名):最小值
avg(字段名):平均值
sum(字段名):总数/和
count(*):表中有多少条数据/记录
例:select max(nl),min(nl),avg(nl),sum(nl) from xyb
select count(*) from xyb
分组:group by 字段名,分组后条件用having
例:select count(*) from xyb group by xb