一、基本查询
1、查询多个字段
SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;
二、条件查询
1、语法
SELECT 字段列表 FROM 条件 WHERE 条件列表;
2、条件
1)比较运算符:>, <, >=, <=, =, !=, <>
查询大于20岁的信息
select * from students where age>20;
select id, name,gender from students where age>20;
查询小于20岁的信息
select * from students where age<20;
查询年龄为20岁的所有学生的名字
select * from students where age=20;
2)逻辑运算符:and, or, not
-–18到20之间的学生信息
select * from students where age>18_and age<20;
-–18岁以上的女性
select * from students where age>18 and gender="女";
select * from students where age>18 and gender=2;
-–18以上或者身高查过180(包含)以上
select * from students where age>18 or height>=180;
--不在18岁以上的女性这个范围内的信息
select * from students where not (age>18 and gender=2);
年龄不是小于或者等于18并且是女性
select * from students where (not age<=18) and gender=2;
3)模糊查询:like, rlike
% 替换1个或者多个
_ 替换1个
查询姓名中 以“小”开始的名字
select name from students where name="小";
select name from students where name like"小%";
查询姓名中有“小”所有的名字
select name from students whece name like "%小%";
查询有2个字的名字
select name from students where name like "__";
查询有3个字的名字
select name from students where name like "__";
查询至少有2个字的名字 select name from
students where name like "__%";
rlike正则
查询以王开始的姓名
select name from students where name rlike "^王.*";
查询以王开始、洛结尾的姓名
select name from students where name rlike "^王.*洛$";
4)范围查询:in,not in,between…and,not between…and
查询年龄为18、24的姓名
select name, age from students where age=18 or age=24;
select name,age from students where age in (18,24);
not in不非连续的范围之内
年龄不是 18、24岁之间的信息
select name,age from students where age not in (18,24);
between … and …表示在一个连续的范围内
查询年龄在18到24之间的的信息
select name,age from students where age between 18 and 24;
not between … and …表示不在一个连续的范围内
查询年龄不在在18到24之间的的信息
select * from students where age not between 18 and 24;