查询
- 查询所有字段
select * from students;
- 查询指定字段
select name from students;
- 使用as给字段起别名
select 别名, 字段 … from 表名 as 别名;
select name as 姓名, age as 年龄 from students;
select students.name, students.age from students;
- 可以通过as给表起别名
select 别名.字段 … from 表名 as 别名;
select s.name, s.age from students as s;
- 消除重复行
select distinct gender from students;
条件查询
- 比较运算符
- > : 大于
- < : 小于
- >= : 大于等于
- <= : 小于等于
- = : 等于
- != : 不等于
- 逻辑运算符
- and
-- 18到28之间的所有学生信息
select * from students where age>18 and age<28;
- or
-- 18以上或者身高超过180的
select * from students where age>18 or height>180;
- not
-- 不在18岁以上的女性 , 这个范围内的信息
select * from students where not age<=18;
- 模糊查询
- like和 % , %替换1个或者多个,
select name from students where name like "小%";
- like和_ , _ 替换1个
-- 查询有两个字的名字
select * from students where name like "__";
-- 查询至少有2个字的名字
select * from students where name like "__%";
- rlike 正则
-- 查询以 周开始的姓名
select * from students where name rlike "^周.*$";
- 范围查询
- in (1, 3, 8) 表示在一个非连续的范围内
select name,age from students where age in (13, 24, 38);
- not in 表示不在一个非连续的范围内
select name,age from studnets where age not in (12, 34, 38);
- between … and … 表示在一个连续的范围内
select * from students where age between 18 and 38;
- not between … and … 表示不在一个连续的范围内
select * from students where age not between 18 and 38;
- 空判断
- is null
-- 查询身高为空的信息
select * from students where height is null;
- is not null
-- 查询身高不为空的信息
select * from students where height is not null;