SQL 查询命令

distinct 去重（查询行是否重复）

例

select distinct age,name from pserson;

>>> | age | name |
>>> | 18  | Jon  |
>>> | 19  | Tom  |
>>> | 18  | Tom  |

as 替换字段名

select name as 姓名,age from pserson where name='Tom';

>>> |  姓名 | age |
>>> |  Tom | 19  |
>>> |  Tom | 18  |

• 表名也可以用as替换

<小于 >大于 =等于 >=大于等于 <=小于等于 !=或 <>不等于-- 比较运算符

select * from pserson where age = 18;

逻辑运算符and or not

select * from pserson where age >= 18 and name='Tom' ;

like 模糊查询

• %匹配任意个
• _匹配一个

匹配开头

select * from pserson where name like 'T%';

匹配开头并且之后只有一个

select * from pserson where name like 'T_';

匹配包含

select * from pserson where name like '%T%';

至少匹配两个

select * from pserson where name like '__%';

rlike 正则查询

匹配开头

select * from pserson where name like '^T.*';

匹配结尾

select * from pserson where name like '.*T$';

in非连续范围内 not in不非连续范围内（非连续范围外） between...and...连续范围内 not between...and...连续范围外 -- 范围查询

select * from pserson where name in ('Tom', 'Marry');
select * from pserson where name not in ('Tom', 'Marry');
select * from pserson where id between 3 and 8;
select * from pserson where id not between 3 and 8;

is null空判断 is not null 非空判断

select * from pserson where name is null;