SELECT …

SELECT 1;  -- 返回1
SELECT 1+1;-- 返回2
SELECT 1*3;-- 返回3
1
2
3

SELECT … FROM

• DUAL 是伪表。
• SELECT 字段1,字段2,...字段n FROM 表名。
• SELECT * FROM 表名。其中*代表 表中的所有字段（或者所有列）。

SELECT 1
FROM DUAL;
-- 其中，DUAL是伪表。

DESCRIBE DUAL;
-- 报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DUAL' at line 1
1
2
3
4
5
6

SELECT *
FROM employees;
-- *，代表 表中的所有字段（或者所有列）
1
2
3

SELECT DISTINCT … （去除重复行）

• SELECT DISTINCT，去除重复行。
• SELECT DISTINCT department_id，去除department_id重复的行。
• SELECT DISTINCT department_id,salary，去除department_id、salary同时重复的行。

SELECT department_id
FROM employees; 
-- 返回107条记录

# 去除重复行
SELECT DISTINCT department_id
FROM employees;
-- 返回12条记录
1
2
3
4
5
6
7
8

# 反面案例
SELECT DISTINCT department_id
FROM employees; 
-- 返回12行记录

SELECT salary,department_id
FROM employees;
-- 返回106行记录

SELECT salary,DISTINCT department_id
FROM employees;
-- 报错
/*
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT department_id
FROM employees' at line 1
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 反面案例
SELECT department_id,salary
FROM employees;
-- 返回107行记录

SELECT DISTINCT department_id,salary
FROM employees;
-- 返回74行记录

SELECT department_id,salary
FROM employees
WHERE department_id = 90 
AND salary = 17000;
/*
返回2行记录，如下：
department_id salary
90	          17000.00
90	          17000.00
*/

SELECT DISTINCT department_id,salary
FROM employees
WHERE department_id = 90
AND salary = 17000;
/*
返回1行记录，如下，
department_id salary
90	          17000.00
即 SELECT DISTINCT department_id,salary  剔除了 department_id、salary同时重复的记录。
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

列的别名

给列取别名，有以下3种方式，

1. 列名 别名，比如 SELECT employee_id emp_id FROM employees;
2. 列名 AS 别名，比如 SELECT employee_id AS emp_id FROM employees;
3. 列名 “别名”，比如 SELECT employee_id "emp_id" FROM employees;

SELECT employee_id emp_id 
FROM employees;

SELECT department_name AS dep_name
FROM departments;

SELECT salary*12 "annual salary"
FROM employees;
1
2
3
4
5
6
7
8

使用WHERE过滤数据

SELECT … FROM … WHERE

1. 查询department_id等于90的员工信息。
   SELECT * FROM employees WHERE department_id = 90;
2. 查询last_name为’King’员工信息
   SELECT * FROM employees WHERE last_name = 'King';
SELECT * FROM employees WHERE last_name = 'king';
SELECT * FROM employees WHERE LAST_NAME = 'KING';
   MySQL对大小写不敏感。

# 查询department_id等于90的员工信息。
SELECT *
FROM employees
WHERE department_id = 90;

# 查询last_name为'King'员工信息
SELECT *
FROM employees
WHERE last_name = 'King';

SELECT *
FROM employees
WHERE last_name = 'king';

SELECT *
FROM employees
WHERE LAST_NAME = 'KING';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

WHERE中不能使用列的别名

SELECT last_name,salary,12*salary*(1+IFNULL(commission_pct,0)) annual_salary
FROM employees
WHERE annual_salary > 100000;
-- 报错：Unknown column 'annual_salary' in 'where clause'
1
2
3
4

以上示例报错，是因为MySQL的执行顺序是，
第1步：FROM。
第2步：WHERE。
第3步：SELECT。

空值参与运算

空值，即NULL。空值参与运算，结果仍然为空值。

SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+commission_pct) "年收入"
FROM employees;
-- 当commission_pct为NULL时，12*salary*(1+commission_pct)的运算结果仍然为NULL。
1
2
3

流控制函数

• IF(condition,value1,value2)，如果condition为true，则返回value1，否则返回value2。
• IFNULL(value1,value2)，如果value1不为NULL，则返回value1，否则返回value2。

SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+IF(commission_pct IS NOT NULL,commission_pct,0)) "年收入"
FROM employees;

SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+IFNULL(commission_pct,0)) "年收入"
FROM employees;
1
2
3
4
5

LIMIT语法

LIMIT语法，有以下两种，

• LIMIT 偏移量,条目数，比如，SELECT ... FROM ... LIMIT 44,2，偏移44，2条记录。
• LIMIT 条目数 OFFSET 偏移量。比如，SELECT ... FROM ... LIMIT 2 OFFSET 44，偏移44，2条记录。

SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+IF(commission_pct IS NOT NULL,commission_pct,0)) "年收入"
FROM employees 
LIMIT 2 OFFSET 44;

SELECT employee_id,salary "月工资",commission_pct,salary*(1+IFNULL(commission_pct,0)) "年收入"
FROM employees 
LIMIT 2 OFFSET 44;

SELECT employee_id,salary "月工资",commission_pct,salary*(1+IF(commission_pct IS NOT NULL,commission_pct,0)) "年收入"
FROM employees 
LIMIT 44,2;

/*
返回2行记录
employee_id  月工资     commission_pct 年收入
144	         2500.00    （Null）       30000.00
145	         14000.00	0.40	       235200.00
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

着重号（``）

给 数据库、表、字段 命名时，必须保证数据库名、表名、字段名，不和保留字、数据库系统或常用方法冲突。万一发生了冲突，在SQL语句中使用着重号（``）引起来。

# 错误示范
SELECT *
FROM order;
/*
报错如下，因为表名order 与 MySQL关键字(ORDER BY)冲突了
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order'
*/

# 正确示范
# 使用着重号``
SELECT *
FROM `order`;
1
2
3
4
5
6
7
8
9
10
11
12

查询常数

SELECT '快乐联萌',employee_id,department_id,last_name
FROM employees
LIMIT 3;

/*
返回3行记录，如下：
快乐联萌    employee_id department_id last_name
快乐联萌	100	        90	          King
快乐联萌	101	        90	          Kochhar
快乐联萌	102	        90	          De Haan
*/
1
2
3
4
5
6
7
8
9
10
11

显示表结构

显示表结构，语法如下：

• DESC 表名。比如，DESC employees;
• DESCRIBE 表名。比如，DESCRIBE employees;