数据过滤（MySQL）

数据过滤

数据过滤用在WHERE表达式里，常用的有基本查询过滤、条件查询过滤、模糊查询过滤、字段查询过滤以及正则表达式查询过滤。

一、基本查询过滤

基本查询过滤可以查询所有字段数据或指定一个字段或者多个字段的数据。

附带建表

mysql> create table user (
    ->  id int(11) not null primary key,
    -> name nvarchar(255) default null,
    -> sex nvarchar(255) default null,
    -> age int(11) default null,
    -> password int(11) default null,
    -> phone int(11) default null,
    -> loginname nvarchar(255) default null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into user values(1,"david","男","19","123456","5555555","hfhhj");
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(2,"小明","男","22","123456","444444","hfhhj"),( 3,"小花","女","21","123456","6666666","hfhhkj"),(4,"小敏","女","25","123456","8888888","hfhhj"),( 5,"小华","男","26","123456","999999","hfhhj");
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select*from user;
+----+-------+------+------+----------+---------+-----------+
| id | name  | sex  | age  | password | phone   | loginname |
+----+-------+------+------+----------+---------+-----------+
|  1 | david | 男   |   19 |   123456 | 5555555 | hfhhj     |
|  2 | 小明  | 男   |   22 |   123456 |  444444 | hfhhj     |
|  3 | 小花  | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏  | 女   |   25 |   123456 | 8888888 | hfhhj     |
|  5 | 小华  | 男   |   26 |   123456 |  999999 | hfhhj     |
+----+-------+------+------+----------+---------+-----------+
5 rows in set (0.00 sec)

mysql> select name,sex,age from user;
+-------+------+------+
| name  | sex  | age  |
+-------+------+------+
| david | 男   |   19 |
| 小明  | 男   |   22 |
| 小花  | 女   |   21 |
| 小敏  | 女   |   25 |
| 小华  | 男   |   26 |
+-------+------+------+
5 rows in set (0.00 sec)

二、条件查询过滤

条件查询过滤关键字为AND、OR、IN、NOT IN、IS NULL、IS NOT NULL、BETWEEN AND。按关键字可以进行条件查询过滤。

1、AND（与），两个条件同房时满足的情况下使用。

select*from user where age=20 and name='kevin';

2、OR（或），只要求满足一个条件。

select*from user where age=20 name='kevin';

3、IN（在范围内），在其范围内就可。

select*from user where id in (3,5,7);

4、NOT IN（不在范围内），查询不在其范围内。

select*from user where id not in(3,5,7);

5、IS（为空），查询某字段为空用is null，而不能使用"=null"，因为在MySQL中的null不等于任何其它值。

select*from user where name is null;

6、BETWEEN AND（在…区间），查询出来的数据在其区间。

select*from user where age between 10 and 20;

mysql> select*from user where sex='女' and age>20;
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  3 | 小花 | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏 | 女   |   25 |   123456 | 8888888 | hfhhj     |
+----+------+------+------+----------+---------+-----------+
2 rows in set (0.00 sec)

mysql> select*from user where name='小花'or name='小明';
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  2 | 小明 | 男   |   22 |   123456 |  444444 | hfhhj     |
|  3 | 小花 | 女   |   21 |   123456 | 6666666 | hfhhkj    |
+----+------+------+------+----------+---------+-----------+
2 rows in set (0.00 sec)

mysql> select*from user where id in(1,3,5);
+----+-------+------+------+----------+---------+-----------+
| id | name  | sex  | age  | password | phone   | loginname |
+----+-------+------+------+----------+---------+-----------+
|  1 | david | 男   |   19 |   123456 | 5555555 | hfhhj     |
|  3 | 小花  | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  5 | 小华  | 男   |   26 |   123456 |  999999 | hfhhj     |
+----+-------+------+------+----------+---------+-----------+
3 rows in set (0.00 sec)

mysql> select*from user where id not in(1,3,5);
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  2 | 小明 | 男   |   22 |   123456 |  444444 | hfhhj     |
|  4 | 小敏 | 女   |   25 |   123456 | 8888888 | hfhhj     |
+----+------+------+------+----------+---------+-----------+
2 rows in set (0.00 sec)

mysql> select*from user where phone is not null;
+----+-------+------+------+----------+---------+-----------+
| id | name  | sex  | age  | password | phone   | loginname |
+----+-------+------+------+----------+---------+-----------+
|  1 | david | 男   |   19 |   123456 | 5555555 | hfhhj     |
|  2 | 小明  | 男   |   22 |   123456 |  444444 | hfhhj     |
|  3 | 小花  | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏  | 女   |   25 |   123456 | 8888888 | hfhhj     |
|  5 | 小华  | 男   |   26 |   123456 |  999999 | hfhhj     |
+----+-------+------+------+----------+---------+-----------+
5 rows in set (0.00 sec)

mysql> select*from user where age>20 and age<30;
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  2 | 小明 | 男   |   22 |   123456 |  444444 | hfhhj     |
|  3 | 小花 | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏 | 女   |   25 |   123456 | 8888888 | hfhhj     |
|  5 | 小华 | 男   |   26 |   123456 |  999999 | hfhhj     |
+----+------+------+------+----------+---------+-----------+
4 rows in set (0.00 sec)

mysql> select*from user where age between 20 and 30;
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  2 | 小明 | 男   |   22 |   123456 |  444444 | hfhhj     |
|  3 | 小花 | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏 | 女   |   25 |   123456 | 8888888 | hfhhj     |
|  5 | 小华 | 男   |   26 |   123456 |  999999 | hfhhj     |
+----+------+------+------+----------+---------+-----------+
4 rows in set (0.00 sec)

mysql>  select*from user where sex !='男';
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  3 | 小花 | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏 | 女   |   25 |   123456 | 8888888 | hfhhj     |
+----+------+------+------+----------+---------+-----------+
2 rows in set (0.00 sec)

mysql> select*from user where  sex <> '男';
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  3 | 小花 | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏 | 女   |   25 |   123456 | 8888888 | hfhhj     |
+----+------+------+------+----------+---------+-----------+
2 rows in set (0.00 sec)

三、模糊查询过滤

模糊查询过滤使用关键字LIKE进行查询。

1、select*from user where name like '张%';
2、select*from user where name like '%明';
3、select*from user where name like '%明%';

mysql> select*from user where name like '小%';
+----+------+------+------+----------+---------+-----------+
| id | name | sex  | age  | password | phone   | loginname |
+----+------+------+------+----------+---------+-----------+
|  2 | 小明 | 男   |   22 |   123456 |  444444 | hfhhj     |
|  3 | 小花 | 女   |   21 |   123456 | 6666666 | hfhhkj    |
|  4 | 小敏 | 女   |   25 |   123456 | 8888888 | hfhhj     |
|  5 | 小华 | 男   |   26 |   123456 |  999999 | hfhhj     |
+----+------+------+------+----------+---------+-----------+
4 rows in set (0.00 sec)

mysql> select*from user where name like '%明';
+----+------+------+------+----------+--------+-----------+
| id | name | sex  | age  | password | phone  | loginname |
+----+------+------+------+----------+--------+-----------+
|  2 | 小明 | 男   |   22 |   123456 | 444444 | hfhhj     |
+----+------+------+------+----------+--------+-----------+
1 row in set (0.00 sec)

四、字段控制查询过滤

字段控制查询过滤可以使用DISTINCT去除重复过滤，使用AS设置别名。

1、DISTINCT：去除重复的列值。

select distinct age from user;

2、AS：可以设置列的别名，也可以省略AS来设置关键字。

select name as 姓名 from user;

或者

select name 姓名 from user;

mysql> select distinct sex from user;
+------+
| sex  |
+------+
| 男   |
| 女   |
+------+
2 rows in set (0.00 sec)

mysql> select distinct age from user;
+------+
| age  |
+------+
|   19 |
|   22 |
|   21 |
|   25 |
|   26 |
+------+
5 rows in set (0.00 sec)

mysql> select*,id+age from user;
+----+-------+------+------+----------+---------+-----------+--------+
| id | name  | sex  | age  | password | phone   | loginname | id+age |
+----+-------+------+------+----------+---------+-----------+--------+
|  1 | david | 男   |   19 |   123456 | 5555555 | hfhhj     |     20 |
|  2 | 小明  | 男   |   22 |   123456 |  444444 | hfhhj     |     24 |
|  3 | 小花  | 女   |   21 |   123456 | 6666666 | hfhhkj    |     24 |
|  4 | 小敏  | 女   |   25 |   123456 | 8888888 | hfhhj     |     29 |
|  5 | 小华  | 男   |   26 |   123456 |  999999 | hfhhj     |     31 |
+----+-------+------+------+----------+---------+-----------+--------+
5 rows in set (0.00 sec)

mysql> select*,id+age as total from user;
+----+-------+------+------+----------+---------+-----------+-------+
| id | name  | sex  | age  | password | phone   | loginname | total |
+----+-------+------+------+----------+---------+-----------+-------+
|  1 | david | 男   |   19 |   123456 | 5555555 | hfhhj     |    20 |
|  2 | 小明  | 男   |   22 |   123456 |  444444 | hfhhj     |    24 |
|  3 | 小花  | 女   |   21 |   123456 | 6666666 | hfhhkj    |    24 |
|  4 | 小敏  | 女   |   25 |   123456 | 8888888 | hfhhj     |    29 |
|  5 | 小华  | 男   |   26 |   123456 |  999999 | hfhhj     |    31 |
+----+-------+------+------+----------+---------+-----------+-------+
5 rows in set (0.00 sec)