过 滤 数 据
1.使用WHERE子句
select student_id,student_name,student_sex from students where student_id=1;
2.列出年龄小于20岁的学生
select student_id,student_name,student_sex ,student_age from students where student_age<20;
3.列出年龄不等于20岁的学生
select student_id,student_name,student_sex ,student_age from students where student_age<>19;
4.列出年龄在18-21岁的学生
select student_id,student_name,student_sex ,student_age from students where student_age between 18 and 21;
(between --and --匹配范围中所有的值,包括指定的开始值和结束值。)
5.列出年龄为空值的学生
select student_id,student_name,student_sex ,student_age from students where student_age is null;
6.AND操作符
select student_id,student_name,student_sex ,student_age from students where student_age >20 and student_sex='女';
7.or操作符
(or指示MySQL检索匹配任一条件的行)
select student_id,student_name,student_sex ,student_age from students where student_age >20 or student_sex='女';
8.or–and操作符
(and在计算次序中优先级更高)
select student_id,student_name,student_sex ,student_age from students where (student_id=1 or student_id=2) and student_age >20 ;
9.in操作符
select student_id,student_name,student_sex ,student_age from students where student_id in (1 ,2) order by student_age ;
10.NOT操作符
select student_id,student_name,student_sex ,student_age from students where student_id not in (1 ,2) order by student_age ;
11.LIKE操作符
1)百分号(%)操作符
select student_id,student_name,student_sex ,student_age from students where student_name like '王%' ;
百分号(%)匹配多个字符
2)下划线(_)操作符**
select student_id,student_name,student_sex ,student_age from students where student_name like '王_' ;
下划线(_)只匹配单个字符