一 , 检索表列数据
- 检索单个列:语法: SELECT 列名 FROM 表名;
- 检索多个列:语法:SELECT 列名,列名 FROM 表名;
- 检索所有列:语法:SELECT * FROM 表名;
- 查重:语法:SELECT DISTINCT 列名 FROM 表名;(得到所查列中每个数据都不同)
- 限制结果:(注:行从0开始检索的)
- 1.语法:** SELECT 列名 FROM 表名 LIMIT 5;**(得到所查列中第0行开始的前5行的数据)
- 2.语法:SELECT 列名FROM 表名 LIMIT 5,5;(得到的是所查列中行5开始的5条数据)
mysql> SELECT DISTINCT * FROM departments;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 20 | Mar | 201 | 1800 |
| 30 | Pur | 114 | 1700 |
| 40 | Hum | 203 | 2400 |
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 70 | Pub | 204 | 2700 |
| 80 | Sal | 145 | 2500 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT DISTINCT * FROM departments
-> LIMIT 5,5;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 60 | IT | 103 | 1400 |
| 70 | Pub | 204 | 2700 |
| 80 | Sal | 145 | 2500 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
+---------------+-----------------+------------+-------------+
二,排序检索数据
语法:SELECT 列名 FROM 表名 ORDER BY 列名字段
(ORDER BY子句取一个或多个列的名字,据此对输出进行排序。当按照多个字段列排序时,先按照第一个字段排序,当第一个字段列相同时,再按照第二个字段列排序)
mysql> SELECT * FROM departments;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 20 | Mar | 201 | 1800 |
| 30 | Pur | 114 | 1700 |
| 40 | Hum | 203 | 2400 |
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 70 | Pub | 204 | 2700 |
| 80 | Sal | 145 | 2500 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> ORDER BY department_name;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 110 | Acc | 205 | 1700 |
| 10 | Adm | 200 | 1700 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 40 | Hum | 203 | 2400 |
| 60 | IT | 103 | 1400 |
| 20 | Mar | 201 | 1800 |
| 70 | Pub | 204 | 2700 |
| 30 | Pur | 114 | 1700 |
| 80 | Sal | 145 | 2500 |
| 50 | Shi | 121 | 1500 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> ORDER BY location_id,department_id;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 60 | IT | 103 | 1400 |
| 50 | Shi | 121 | 1500 |
| 10 | Adm | 200 | 1700 |
| 30 | Pur | 114 | 1700 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
| 20 | Mar | 201 | 1800 |
| 40 | Hum | 203 | 2400 |
| 80 | Sal | 145 | 2500 |
| 70 | Pub | 204 | 2700 |
+---------------+-----------------+------------+-------------+
- 指定排序方向
数据排序默认排序方式为 升序排序(ASC)(从A到Z),还可以使用ORDER BY子句以降序(从Z到A)顺序排序。为了进行降序排序,必须指定 DESC 关键字,如果想在多个列上进行降序排序,必须对每个列指定DESC关键字。
mysql> SELECT * FROM departments
-> ORDER BY location_id DESC,department_id DESC;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 70 | Pub | 204 | 2700 |
| 80 | Sal | 145 | 2500 |
| 40 | Hum | 203 | 2400 |
| 20 | Mar | 201 | 1800 |
| 110 | Acc | 205 | 1700 |
| 100 | Fin | 108 | 1700 |
| 90 | Exe | 100 | 1700 |
| 30 | Pur | 114 | 1700 |
| 10 | Adm | 200 | 1700 |
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
+---------------+-----------------+------------+-------------+
- 使用ORDER BY和LIMIT的组合,能够找出一个列中最高或最低的值
mysql> SELECT * FROM departments
-> ORDER BY location_id,department_id
-> LIMIT 1;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 60 | IT | 103 | 1400 |
+---------------+-----------------+------------+-------------+
LIMIT 1告诉MySQL仅返回一行。
注: 在给出ORDER BY子句时,应该保证它位于FROM子句之后。如果使用LIMIT,它必须位于ORDER BY之后。