- 带关键字WHERE的查询，语法select<字段名>from<表或视图名>where<字段名>=’某某’;
- 带关键字IN的查询，语法select<字段名>from<表或视图名>where<字段名>in(‘某某’,’某某1′);
- 带关键字BETWEEN AND的范围查询（闭区间），语法select<字段名>from<表或视图名>where<字段名>between’某某’and’某某2‘;
- 带关键字LIKE的字符匹配查询，语法select<字段名>from<表或视图名>where<字段名>like’某%’;
- 带关键字IS NULL空值查询或IS NOT NULL非空值查询，语法select<字段名>from<表或视图名>where<字段名>IS NULL;
- 带关键字AND的多条件查询，语法select<字段名>from<表或视图名>where<字段名>=’某某’ and <字段名1>=’某某1′;
- 带关键字OR的多条件查询，语法select<字段名>from<表或视图名>where<字段名>=’某某’ or <字段名1>=’某某1′;
- 用关键字DISTINCT去除结果集重复行，语法select distinct<字段名>from<表或视图名>;
- 用关键字ORDER BY对查询结果排序，语法select<字段名>from<表或视图名>order by<字段名>asc或desc;
- 用关键字LIMIT限制查询结果的数量，语法select<字段名>from<表或视图名>limit 1;
mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.01 sec)
mysql> select * from houses where purchasing_year='1998';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from houses where purchasing_year in ('1997','1998','1999');
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses where purchasing_year between '1996' and '1997';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from houses where purchasing_year like '199%';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses where purchasing_year like '1997';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from houses where purchasing_year like "1%";
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses where purchasing_year like '%19';
Empty set (0.00 sec)
mysql> select * from houses where purchasing_year like '%97';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from houses where purchasing_year is null;
Empty set (0.00 sec)
mysql> select * from houses where purchasing_year is not null;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses where name='甲' and purchasing_year='1997';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from houses where name='乙' or (house_location='天河' and purcha
sing_year='1997');
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select name from houses;
+------+
| name |
+------+
| 甲 |
| 甲 |
+------+
2 rows in set (0.00 sec)
mysql> select distinct name from houses;
+------+
| name |
+------+
| 甲 |
+------+
1 row in set (0.00 sec)
mysql> select * from houses order by purchasing_year asc;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses order by purchasing_year desc;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1998 |
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses limit 1;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from houses limit 1,1;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
mysql> select * from houses limit 0,2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql>