13 语法：数据查询语言（DQL）

一、比较条件

1.Between...and...

例：select employee_id,last_name,salary from employees where salary between 3000 and 8000;

表示：查询 employees 表，薪水在 3000-8000 之间的雇员ID、名字与薪水。

2.IN(set)

例：select employee_id,last_name,salary from employees where salary in(5000,6000,8000);

表示：查询 employees 表，找出薪水是 5000,6000,8000 的雇员ID、名字与薪水。

3.LIKE

例：select last_name from employees where last_name like ‘%e%’;

表示：查询 employees 中雇员名字字母含有 e 的雇员名字。

例：select last_name from employees where last_name like ‘_e%’;

表示：查询 employees 中雇员名字第二个字母是 e 的雇员名字。

4.IS NULL

例：select employee_id,last_name,commission_pct from employees where commission_pct is null;

表示：找出 emloyees 表中那些没有佣金的雇员雇员ID、名字与佣金。

例：select employee_id,last_name,commission_pct from employees where commission_pct is not null;

表示：找出 employees 表中那些有佣金的雇员ID、名字与佣金。

二、逻辑条件

可以在 WHERE 子句中用 AND 和 OR 运算符使用多个条件。

1.AND

例：select last_name,salary from employees where salary = 8000 and last_name like '%e%';

表示：查询 employees 表中雇员薪水是 8000 的并且名字中含有e 的雇员名字与薪水。

2.OR

例：select last_name,salary from employees where salary = 8000 or last_name like '%e%';

表示：查询 employees 表中雇员薪水是 8000 的或者名字中含有e 的雇员名字与薪水。

3.NOT

例：select last_name from employees where last_name not like '%u%';

表示：查询 employees 表中雇员名字中不包含 u 的雇员的名字。

三、ORDER BY排序

1.ASC升序（由大到小）

例：1.select employee_id,last_name,salary from employees order by salary;（默认升序排序ASC）

2.select employee_id,last_name,salary from employees order by salary asc;

表示：查询 employees 表中的所有雇员，显示他们的ID、名字与薪水，并按薪水升序排序。

2.DESC降序（有小到大）

例：select employee_id ,last_name from employees order by last_name desc;

表示：查询 employees 表中的所有雇员，显示他们的ID与名字，并按雇员名字降序排序。

3.使用别名排序（用列别名排序）：

例：select employee_id,last_name, 12*salary annsal from employees order by annsal desc;

表示：显示雇员ID，名字。计算雇员的年薪，年薪列别名为annsal，并对该列进行降序排序

4.多列排序：

多列排序按ORDER BY 列表的顺序就是排序的顺序；并且可以排序一个不在SELECT列表中的列；

例：select department_id ,salary from employees order by department_id asc,salary desc;

表示：以升序排序显示department_id列后，再根据前一个排序的基础上，以降序排序显示salary列；

四、单行函数

1.字符函数

CONCAT，s2（s1...）、LENGTH（s）

例：SELECT CONCAT(FIRST_NAME,LAST_NAME), LENGTH(EMPLOYEE_ID) LENGTH , INSTR(LAST_NAME,'a') "CONTAINS 'a'?" from employees where SUBSTR(JOB_ID,4) = 'rep';

表示：显示所有工作岗位名称从第 4 个字符位置开始，包含字符串 REP的雇员的ID信息，将雇员的姓和名连接显示在一起，还显示雇员名的4的长度，以及名字中字母 a 的位置。

2.三个常用数字函数

2.1.ROUND(column|expression, n) 函数

ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个

参数是 0 或者缺少，值被四舍五入为整数。如果第二个参数是 2值

被四舍五入为两位小数。如果第二个参数是–2，值被四舍五入到小

数点左边两位。

例：SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1);

（1）表示保留小数点后两位（2）表示只保留整数位（3）表示小数点向左移一位再四舍五入，即值为5.

2.2.TRUNCATE(column|expression,n) 函数

TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0

或者缺少，值被截断为整数。如果第二个参数是 2，值被截断为两

位小数。如果第二个参数是–2，值被截断到小数点左边两位。与

ROUND 最大的区别是不会进行四舍五入。

例：SELECT TRUNCATE(45.923,2);

表示值为45.92

2.3.使用MOD(m,n) 函数

MOD 函数找出m 除以n的余数。

例：SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';

3.日期函数

1.SYSDATE()当前日期

例：SELECT last_name, (SYSDATE()-hire_date)/7 AS WEEKS FROM employees WHERE department_id =90;

表示：显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作时间以周计算，用当前日期 (SYSDATE) 减去雇员的受顾日期，再除以 7。

4.转换函数

1.STR_TO_DATE(str,format) 将字符串转换成日期;

例：insert into

employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID) values(400,'oldlu','oldlu@sxt.cn',STR_TO_DATE('2049 年 5 月 5 日','%Y 年%m 月%d日'),'IT_PROG');

表示：向 employees 表中添加一条数据，雇员ID：400，名字：oldlu ，email：oldlu@sxt.cn ，入职时间：2049 年 5 月 5 日，工作部门：‘IT_PROG’。

2.DATE_FORMAT(date,format) 将日期转换成字符串;

例：select DATE_FORMAT(hire_date,'%Y 年%m 月%d日') from employees where last_name = 'King';

表示：查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。

5.通用函数

1.IF（expr，v1，v2）、ISNULL（expression）

例：

SELECT last_name, salary, commission_pct, if(ISNULL(commission_pct), 'SAL','SAL+COMM') income FROM employees WHERE department_id IN (50, 80);

表示：查询部门编号是50或者80的员工信息，包含他们的名字、薪水、佣金。在income列中，如果有佣金则显示‘SAL+COMM’，无佣金则显示'SAL'。

2.IFNULL（v1，v2）

例：SELECT last_name, salary, IFNULL(commission_pct, 0), (salary*12) +(salary*12*IFNULL(commission_pct, 0)) AN_SAL FROM employees;

表示：计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金百分比)。

3.Case expression when condition1 then result1

when condition2 then result2

......

when conditionN then resultN

Else result end

例：.写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。

注意：JOB_ID后面跟着逗号，每层的when....then....后面没有逗号

五、多表查询

1.等值连接：用=号

例；SELECT e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

表示：显示每个雇员的 last name、departmentname 和 city。

2.非等值连接：不用=号

例：select e.last_name,j.grade_level from employees e ,job_grades j where e.salary between j.lowest_sal and j.highest_sal;

表示:查询所有雇员的薪水级别。

3.自连接：一个表自己连接自己

例：SELECT worker.LAST_NAME,manager.LAST_NAME from employees worker,employees manager where worker.MANAGER_ID = manager.EMPLOYEE_ID AND worker.LAST_NAME = 'Fox';

表示：查询Fox的经理是谁？显示他的名字。

4.自然连接（工作用的少，多用等值连接）

例：select e.last_name,d.department_name from employees e natural join departments d;

表示：使用自然连接查询所有有部门的雇员的名字以及部门名称。

5.内连接

INNER JOIN... ON....

例：select e.employee_id,e.salary,d.department_name from employees e inner JOIN departments d on e.department_id = d.department_id where e.last_name = 'Fox';

表示：查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

6、外连接

6.1.左外连接

语法：SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名ON 连接条件

左外连接定义：

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的

行，该查询也会取回 EMPLOYEES 表中所有的行（即孤儿数据（为null）也会被取出）。

例：select e.last_name,d.department_name

From employees e LEFT OUTER JOIN departments d

On e.dept_id = d.department_id;

表示：查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

6.2右外连接

语法：SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件

右外连接定义：

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的

行，该查询也会取回 DEPARTMENTS 表中所有的行。

例：select e.last_name,d.department_name

From employees e RIGHT OUTER JOIN departments d

on e.DEPARTMENT_ID = d.department_id;

表示：查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

6.3全外连接

UNION（不包含重复数据）：合并结果集并剔除重复数据

UNION ALL（包含重复数据）合并结果集并不剔除重复数据

语法：

(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名ON 连接条件)

UNION

(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件)

例：(select e.last_name,d.department_name From employees e LEFT OUTER JOIN departments d one.department_id = d.department_id)

UNION

(select e1.last_name,d1.department_name from employees e1 RIGHT OUTER JOIN departments d1 on d1.department_id =e1.department_id);

表示:查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。

六、聚合函数

1.AVG 和 SUM 函数

例：SELECT AVG(salary),SUM(salary)

FROM employees

WHERE job_id LIKE '%REP%';

表示：计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。

2.MIN 和 MAX 函数

例：SELECT MIN(hire_date), MAX(hire_date) FROM employees;

表示：查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。hire_date为日期类型

3.COUNT 函数：返回分组中的总行数。

例：SELECT COUNT(commission_pct) FROM employees

WHERE department_id = 80;

表示：显示员工表中部门编号是80中有佣金的雇员人数。

七、数据分组

1.GROUP BY...HAVING...

例：SELECT MANAGER_ID,min(SALARY)

FROM employees

where MANAGER_ID is not null

GROUP BY MANAGER_ID having MIN(SALARY)>6000;

ORDER BY MIN(salary) DESC;

表示：显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人，排除最低薪水小于等于￥6,000的组，按薪水降序输出。

八、子查询

1.单行子查询

例：SELECT

e.LAST_NAME,e.DEPARTMENT_ID FROM employees e

where e.DEPARTMENT_ID =

(SELECT e1.department_id FROM employees e1 where e1.last_name = 'fox')；

表示：查询与Fox同一部门的同事，并显示他们的名字与部门ID。

分析：先查出Fox的部门编号，再查与Fox部门编号相同的同事；

2.多行子查询

例：SELECT e.LAST_NAME,e.SALARY,e.DEPARTMENT_ID

FROM employees e

WHERE e.SALARY IN (SELECT MIN(e1.salary) FROM employees e1 GROUP BY e1.JOB_ID);

表示：查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门ID。

分析：先用子查询找出各部门薪资最低的工资，再用多行函数表示出薪资在最低工资范围的那些名字、工资、部门