SQL入门教程（四）：数据库基本查询

1.基本查询

--查询表下所有数据
select * from students;

--查询指定字段
select name, age from students;

--给字段重命名
select name as '姓名', age as '年龄' from students;

--给表重命令
select students.name, students.age from students;
select s.name, s.age from students as s;

--去除重复数据
select distinct gender from students;

2.条件查询

--查询条件 > < = !=
--查询大于18岁的信息
select * from students where age>18;
select id, name, gender from students where age>18;

3.逻辑查询

--and or not 
--18到28岁之间所有学生
select * from students where age>18 and age<28;

--不在 18岁以上的女性这个条件
select * from students where not (age>18 and gender='女');

4.模糊查询

--模糊查询 like
-- % 替换1个或者多个
-- _替换1个
-- 查询姓名中 以“小”开始的名字
select name from students where name like "小%";

--查询有2个字的名字
select name from students where name like "__";

--查询至少有2个字的名字
select name from students where name like "__%";

--正则表达式
--以周开始的姓名
select name from students where name rlike "^周.*";


--以周开始,伦结尾的姓名
select name from students where name rlike "^周.*伦$";

5.范围查询

--in (1,3,8) 表示在一个非连续的范围内取值
select name, age from students where age in (12, 118, 34);

--not in 不在这个非连续范围
select name, age from students where age not in (12, 118, 34);

--between    and   表示在一个连续的范围内，年龄在[18,34]
select name, age from students where age between 18 and 34;

--not between   and    表示不在一个连续的范围内
select * from students where age not between 18 and 34;

6.空判断

-- 判断为空
select * from students where height is null;

--判断不为空
select * from students where height is not null;