# 基础篇 - SELECT 语句详解

#### 1、基本的SELECT语句

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

``````#从employee表中选择name、age列
SELECT name,age FROM employee;
``````

#### 2、数学符号

SELECT 语句常常会有 WHERE 限制条件，用于达到更加精确的查询。WHERE限制条件可以有数学符号 (=,<,>,>=,<=)

``````#从employee表中选择name、age列而且age要大于25
SELECT name,age FROM employee WHERE age>25;
``````
``````#从employee表中选择name、age、phone列而且name为‘Mary’
SELECT name,age,phone FROM employee WHERE name='Mary';
``````

#### 3、“AND”与“OR”

``````#筛选出 age 小于 25，或 age 大于 30
SELECT name,age FROM employee WHERE age<25 OR age>30;
``````
``````#筛选出 age 大于 25，且 age 小于 30
SELECT name,age FROM employee WHERE age>25 AND age<30;
``````

``````SELECT name, age FROM employee WHERE age BETWEEN 25 AND 30;
``````

#### 4、IN 和 NOT IN

``````SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
``````
Paste_Image.png

NOT IN的效果则是，如下面这条命令，查询出了不在dpt1也不在dpt3的人：

``````SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');
``````
Paste_Image.png

#### 5、通配符

``````SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
``````
Paste_Image.png

``````SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
``````
Paste_Image.png

#### 6、对结果排序

``````SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
``````
Paste_Image.png

#### 7、SQL 内置函数和计算

SQL 允许对表中的数据进行计算。对此，SQL 有 5 个内置函数，这些函数都对 SELECT 的结果做操作：

``````SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
``````

Paste_Image.png

#### 8、子查询

``````SELECT of_dpt,COUNT(proj_name) AS count_project FROM
projectWHERE of_dpt IN(SELECT in_dpt FROM employee WHERE name='Tom');
``````
13

#### 9、连接查询

``````SELECT id,name,people_numFROM employee,department
WHERE employee.in_dpt = department.dpt_nameORDER BY id;
``````

14

``````SELECT id,name,people_numFROM employee JOIN
department ON employee.in_dpt = department.dpt_nameORDER BY id;
``````