MySQL 单表过滤方式详解

一天一个关于测试知识点，5分钟内讲解你最关心的软件测试问题，今天就接着来谈谈关于软件测试中的“MySQL 单表过滤方式详解”。

　　要对表里的数据进行过滤，则在select语句后边加上where和过滤条件。

　　过滤原理有两种：全表扫描过滤、索引扫描过滤。

　　全表扫描过滤的原理：

从表的第一行的第一列开始扫描数据，一直扫描到第一行的最后一列，如果满足过滤条件(条件为真)，则取出该行数据，如果不满足过滤条件(条件为假)，则不要改行数据，接着从第二行第一列扫描到第二行的最后一列，判断是否满足过滤条件，满足取出改行数据，不满足不要改行数据，一直重复这个操作到表的最后一行的第一列到最后一列。把所有满足条件的行的数据取出来。这个过程叫全表扫描过滤。

　　索引扫描过滤：

如果过滤时进行的是索引扫描，则从表的第一行的索引列开始扫描判断是否满足过滤条件，其他列不扫描判断，满足过滤条件取出改行数据，不满足过滤条件不要改行数据。接着扫描第二行索引列，第三索引列，一致到表的最后一行的索引列，把满足过滤条件的行取出来，这个过程叫索引扫描过滤，索引扫描过滤要比全表扫描过滤快很多。

　　索引：将表的某一列作为了表的索引，相当于把该列作为了表的目录一样。

　　过滤方式通常包括以下7类：使用比较谓词进行过滤，使用范围谓词进行过滤，使用集合成员谓词进行过滤，使用模糊匹配谓词进行过滤，对空值进行过滤，使用not关键字过滤，使用limit子句过滤。

　　1)使用比较谓词进行过滤;

　　比较谓词有：> < >= <= = <>或!=

　　语法：ve(代表列名) 比较谓词 常量。

　　常量：就是一个由字符串或者数字，或者日期时间构成的一个固定值,字符串常量和日期时间常量都要用单引号或双引号括起来。

　　查找王五同学的信息。

　　select * from student where sname="王五";

　　查找年龄大于20岁的学生有哪些?

　　select * from student where sage>20;

　　查找市场价格大于1000的商品有哪些?

　　select * from ecs_goods where market_price>1000;

　　2)使用范围谓词BETWEEN进行过滤

　　语法：ve BETWEEN A and B; 等价于 ve>=A and ve<=B;

　　查找年龄在15到18岁之间的学生有哪些?

　　select * from student where sage BETWEEN 15 and 18;

　　select * from student where sage >= 15 and sage<=18;

　　查找工资在2000到3000之间的员工有哪些?

　　select * from emp where sal BETWEEN 2000 and 3000;

　　select * from emp where sal>=2000 and sal<=3000;

　　3)使用集合成员谓词in进行过滤

　　语法：ve in (x1,x2,...,xn); 等价于 ve=x1 or ve=x2 or ... or ve=xn;

　　查找刘关张三兄弟的信息。

　　select * from student where sname in ("刘备","关羽","张飞");

　　select * from student where sname="刘备" or sname="关羽" or sname="张飞";

　　查找岗位为销售、经理的人有哪些?

　　select * from emp where job="salesman" or job="manager";

　　select * from emp where job in ("salesman" , "manager");

　　4)使用模糊匹配谓词like进行过滤

　　语法：ve like "普通字符+通配符";

　　通配符：%,_

　　%代表匹配任意0个或多个字符 。比如： abc%代表以abc开头的字符。 %abc代表以abc结尾的字符。

　　%abc%代表包含了abc的字符。

　　_代表匹配任意单个字符。 比如：abc_ 代表以abc开头紧接着后边还有一字符的字符，abc_h

　　查找姓张的同学有哪些。

　　select * from student where sname like "张%";

　　查找姓张的同学且姓名由三个字构成。

　　select * from student where sname like "张__";

　　查找商品描述中包含诺基亚三个字的商品有哪些?

　　select * from ecs_goods where goods_desc like "%诺基亚%";

　　5)对空值进行过滤

　　mysql中空值有两种类型：一种是什么都不显示的空，一种是显示为null的空,某一列值只可能会出现其中一种空值类型。而oracle数据库中只有null的空值。

　　什么都不显示的空值过滤语法：ve="";

　　显示为null的空值过滤语法为：ve is null;

　　查找商品描述为空的商品有哪些?

　　select * from ecs_goods where goods_desc="";

　　查找用户表中没有设置密保的用户有哪些?

　　select * from ecs_users where passwd_question is null;

　　6)使用not关键字进行过滤

　　not关键字就是取非的意思，一般和和上边我们学到的过滤方式结合起来使用。

　　not ve 比较谓词 常量

　　什么都不显示的空值过滤语法：ve<>"";

　　显示为null的空值过滤语法为：ve is not null;

　　ve not like "普通字符+通配符"

　　ve not in (x1,x2,...,xn); 等价于 ve<>x1 and ve<>x2 and ... and ve<>xn;

　　ve not BETWEEN A and B; 等价于 veB;

　　查找商品搜索关键字非空的商品有哪些。

　　select * from ecs_goods where keywords<>"";

　　select * from ecs_goods where not keywords="";

　　查找商品描述非空的商品有哪些。

　　select * from ecs_goods where goods_desc<>"";

　　查找设了密码问题的用户有哪些?

　　select * from ecs_users where passwd_answer is not null;

　　查找不姓张的同学有哪些?

　　select * from student where sname not like "张%";

　　查找年龄不是15岁，14岁，13岁学生有哪些。

　　select * from student where sage not in(13,14,15);

　　select * from student where sage <>13 and sage<>14 and sage<>15;

　　查找商品售价不在1000到2000之间的商品有哪些?

　　select * from ecs_goods where shop_price<1000 or shop_price>2000;

　　select * from ecs_goods where shop_price not BETWEEN 1000 and 2000;

　　7)使用limit子句进行过滤

　　想从查询结果集里边取出部分连续的行时，在语句后半加上limit X，Y;

　　语法：limit X,Y;

　　X 代表偏移量，偏移X行则从X+1行开始取数据,当X为0的时候表示取前Y行，这时X可以省略。

　　Y 代表取多行数据。

　　比如：limit 10;代表取前10行数据。 limit 4,8; 代表从第五行开始取8行。

　　查找年龄最大的前三个学生。

　　select * from student order by sage desc limit 0,3;

　　按照年龄从大到小排列，请取出排名7到第10学生。

　　select * from student order by sage desc limit 4;