MySQL的数据过滤

来源：互联网发布：辽宁北方广电网络编辑：程序博客网时间：2024/05/23 02:04

使用where子句

mysql> select * from user where id=3;+----+-------+| id | name  |+----+-------+|  3 | user3 |+----+-------+1 row in set (0.00 sec)//当order by和where同时使用的时候：order by要在where之后。

使用where子句进行不匹配检索

mysql> select * from user where id<>3;+----+-------+| id | name  |+----+-------+|  1 | user1 ||  2 | user2 ||  4 | user4 ||  5 | user5 |+----+-------+4 rows in set (0.00 sec)

mysql> select * from user where id!=3;+----+-------+| id | name  |+----+-------+|  1 | user1 ||  2 | user2 ||  4 | user4 ||  5 | user5 |+----+-------+4 rows in set (0.00 sec)//二者异曲同工

范围值检索

mysql> select * from user where id between 1 and 3;+----+-------+| id | name  |+----+-------+|  1 | user1 ||  2 | user2 ||  3 | user3 |+----+-------+3 rows in set (0.00 sec)

空值检索

mysql> select * from content where tittle is null;+------+--------+| uid  | tittle |+------+--------+|   22 | NULL   |+------+--------+1 row in set (0.00 sec)

where搭配and使用

mysql> select * from user where id=1 and name='user1' ;+----+-------+| id | name  |+----+-------+|  1 | user1 |+----+-------+1 row in set (0.00 sec)

where搭配or使用

mysql> select * from user where id=1 or name='user4' ;+----+-------+| id | name  |+----+-------+|  1 | user1 ||  4 | user4 |+----+-------+2 rows in set (0.00 sec)

where、and、or搭配使用

mysql> select * from user where id=1 or name='user4' and id=4;+----+-------+| id | name  |+----+-------+|  1 | user1 ||  4 | user4 |+----+-------+2 rows in set (0.00 sec)//错误示例，因为and的结合性比or要高。

mysql> select * from user where (id=1 or name='user4') and id=4;+----+-------+| id | name  |+----+-------+|  4 | user4 |+----+-------+1 row in set (0.00 sec)//正确实例！！

where搭配in使用

mysql> select * from user where id in (1,2,3) order by id desc;+----+-------+| id | name  |+----+-------+|  3 | user3 ||  2 | user2 ||  1 | user1 |+----+-------+3 rows in set (0.00 sec)

where搭配not使用

not可以对between、in、exists取反。

mysql> select * from user where id not in (1,2,3) order by id desc;+----+-------+| id | name  |+----+-------+|  5 | user5 ||  4 | user4 |+----+-------+2 rows in set (0.00 sec)

阅读全文

0 0