1. 重命名表:
eg:select s.name as 姓名, s.age as 年龄 from students as s;
2. 去重查询:
select distinct 字段 from 数据表名;
3. 条件查询:
select from 数据表名 where (not) ... and(or) ... ;
4. 模糊查询
like: select 字段 from 数据表 where 字段 like “...” 用%代替未知的部分 ,_ 表示位数。
eg:查询姓名中以“小”开始的名字:select name from students where name like “小%”;
查询姓名中有“小”的名字:select name from students where name like “%小%”;
查询姓名为2个字的名字: select name from students where name like “__”;
查询姓名至少2个字的名字: select name from students where name like “__%”;
rlike 正则: select 字段 from 数据表 where 字段 rlike “...” ^ 表示以什么开头,$表示以什么结尾 ,.*代替未知部分。
eg: select name from students where name rlike “^周.*伦$”; 查询名字以周为开头,伦为结尾,中间可能有字的名字。
5. 范围查询:
- 查询非连续范围
select * from 数据表名 where 字段 in(...,...);
eg: select name,age from students where age in(12,24,34);
eg:select name,age from students where age not in(12,24,34);
- 查询连续的范围
select * from 数据表名 where 字段 between ... and ...;
eg: select name,age from students where age between 12 and 24; 查询年龄在12至24的信息
eg: select name,age from students where age not between 12 and 24; 查询年龄不在12至24的信息
- 空判断
select * from 数据表名称 where 字段 is null;
select * from 数据表名称 where 字段 is not null;