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