mysql 条件查询

基本查询：

 

查询students表所有字段：

select * from students;

 

查询students表中的列1、列2：

select * 列1, 列2  from students;

 

查询class表中的id和name字段：

select id, name from class;

 

查询列并给列指定别名：

select name as "姓名", age as 年龄 from students;            #别名用不用引号都可以

 

distinct:去重复，例如查询性别一列时：

select gender from students;                      # 列出性别列所有内容，例如：男、男、男、女、女、男、保密、保密....

用distinct去掉重复：

select distinct gender from students;          # 男、女、保密

 

 

条件查询： where

where 后面加条件：

select ... from 表名 where ...

select id, name from students where age > 18;

条件：

>

<

>=

<=

=

!= 或者 <>

 

逻辑运算符：

and 

select name from students where age > 10 and age < 20;

查询18岁以上的女性：

select * from students where age > 18 and gender= "女";

 

or

select name from students where gender = "男" or gender="女";

 

not   

条件前面加not，取反

查询不大于18岁的记录：

select * from students where not age > 18;

排除大于18的女性：

select * from students where not (age>18 and gender="女");

 

模糊查询

like

%替换1个或多个

_替换1个

查询以“小”开头的名字的记录：

select * from students where name like "小%";

查询包含“小”的名字记录：

select * from students where name like "%小%";

查询只有两个字的名字：

select name from students where name like "__";

查询两个字以上的名字：

select name from students where name like "__%";

 

rlike 正则

查询以“周”开头的名字：

select name from students where name rlike "^周.*";

查询以“周”开头，以“伦”结尾的名字：

select name from students where name rlike "^周.*伦$";

 

范围查询

非连续：

查询年龄为18、28、35的记录：

1、select * from students where age = 18 or age = 28 or age=35;

2、select * from students where age in (18,28,35);

查询年龄不是18、28、35的记录：

select * from students where age not in (18,28,35);

连续：

查询18到25岁之间所有名字记录：

select * from students where age between 18 and 25;

查询非18～25岁的记录：

1、select * from students where not age between 18 and 25;

2、select * from students where age not between 18 and 25;

 

空判断：

is null 判空 

is not null 判非空

select * from students where height is null;

select * from students where height is not null;