基本的SELECT语句
基本格式
1
2
3
4
5
| SELECT [DISTICT | ALL] 字段名1 [别名], 字段名2, ...
FROM 表名 [别名]
[WHERE 条件表达式]
[GROUP BY 列名1 [HAVING 条件表达式]
[ORDER BY 列名2 [ASC | DESC]]
|
列的别名
AS关键字
AS全称：Alias（别名）
1
2
3
4
5
6
7
8
| # lname为别名
SELECT employee_id, last_name AS lname, department_id
FROM employees;
+
| employee_id | lname | department_id |
+
| 100 | King | 90 |
| 101 | Kochhar | 90 |
|
字段后空一格为别名（AS可省）
别名可以用双引号（别用单引号）引起来
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| # emp_id为别名
SELECT employee_id emp_id, last_name, department_id
FROM employees;
+
| emp_id | last_name | department_id |
+
| 100 | King | 90 |
| 101 | Kochhar | 90 |
# department id为别名
SELECT employee_id, last_name, department_id "department id"
FROM employees;
+
| employee_id | last_name | department id |
+
| 100 | King | 90 |
| 101 | Kochhar | 90 |
|
去除重复行DISTINCT
DISTINCT关键字：
1
2
| SELECT DISTINCT department_id
FROM employees;
|
显示表结构DESCRIBE
DESCRIBE或者DESC关键字，显示表的详细信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| DESCRIBE employees;
+
| Field | Type | Null | Key | Default | Extra |
+
| employee_id | int | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int | YES | MUL | NULL | |
| department_id | int | YES | MUL | NULL | |
+
|
过滤数据WHERE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;
+
| employee_id | last_name | department_id |
+
| 100 | King | 90 |
| 101 | Kochhar | 90 |
| 102 | De Haan | 90 |
+
SELECT employee_id, last_name, department_id
FROM employees
WHERE LAST_NAME = 'King';
+
| employee_id | last_name | department_id |
+
| 100 | King | 90 |
| 156 | King | 80 |
+
|
注意：
NULL参与运算，结果一定为NULL；100+‘1’会隐式转换，结果为101；100+'abc'不能隐式转换的看作0，结果为100- 反引号为着重号，若一个字段名/表名与保留字重复，用着重号规避
DUAL表示伪表