基本查询:
查询students表所有字段:
select * fromstudents;
查询students表中的列1、列2:
select *列1, 列2from 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;