Mysql 成长之路（四）使用where按条件查询数据

原始表

mysql> select * from simple_account;
+----+-----------+-------+-----+---------+
| id | name      | sex   | age | money   |
+----+-----------+-------+-----+---------+
|  1 | Tom       | man   |  20 | 4539.02 |
|  2 | Jack      | man   |  19 | 2109.50 |
|  3 | 原氏      | man   |  17 |  666.66 |
|  4 | 半藏      | man   |  22 | 1666.66 |
|  5 | 天使      | woman |  23 | 9700.00 |
|  6 | 莫伊拉    | woman |  30 | 5980.02 |
|  7 | 张三      | man   |  20 | 1000.00 |
|  8 | 张四      | woman |  21 | 2000.00 |
|  9 | 李明      | man   |  20 | 1500.00 |
| 10 | Jenny     | woman |  21 | 1200.00 |
+----+-----------+-------+-----+---------+
10 rows in set (0.00 sec)

查询age大于20的数据

where age > 20

mysql> select * from simple_account where age > 20;
+----+-----------+-------+-----+---------+
| id | name      | sex   | age | money   |
+----+-----------+-------+-----+---------+
|  4 | 半藏      | man   |  22 | 1666.66 |
|  5 | 天使      | woman |  23 | 9700.00 |
|  6 | 莫伊拉    | woman |  30 | 5980.02 |
|  8 | 张四      | woman |  21 | 2000.00 |
| 10 | Jenny     | woman |  21 | 1200.00 |
+----+-----------+-------+-----+---------+
5 rows in set (0.00 sec)

查询age大于20的数据并将结果按照age降序排列

where age > 20 order by age desc

mysql> select * from simple_account where age > 20 order by age desc;
+----+-----------+-------+-----+---------+
| id | name      | sex   | age | money   |
+----+-----------+-------+-----+---------+
|  6 | 莫伊拉    | woman |  30 | 5980.02 |
|  5 | 天使      | woman |  23 | 9700.00 |
|  4 | 半藏      | man   |  22 | 1666.66 |
|  8 | 张四      | woman |  21 | 2000.00 |
| 10 | Jenny     | woman |  21 | 1200.00 |
+----+-----------+-------+-----+---------+
5 rows in set (0.00 sec)

查询age大于20且性别为男性的数据

运用and关键字拼接，注意判断相等只有一个=符号。

where age > 20 and sex = “man”

mysql> select * from simple_account where age > 20 and sex = "man";
+----+--------+-----+-----+---------+
| id | name   | sex | age | money   |
+----+--------+-----+-----+---------+
|  4 | 半藏   | man |  22 | 1666.66 |
+----+--------+-----+-----+---------+
1 row in set (0.00 sec)