MySQL 数据库管理系统(四）

过 滤 数 据
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 '王_' ;

下划线（_）只匹配单个字符