case when嵌套子查询_025 SQL通用函数以及多表查询

1. 字符到日期的转换

(1) 在Oracle中将字符转换为日期的函数是什么？

TO_DATE(‘arg1’,’fmt’)：将字符串转换为日期格式。带格式化样式fmt。

Arg1:字符串类型。需要转换的数据。

Fmt：转换格式。

实例：将2019年3月11点30分转换为DATE类型。

select to_date('2019年3月9日11点30分','YYYY"年"mm"月"dd"日"hh"点"mi"分"') from dual;

2. 函数嵌套

(1) 什么是函数嵌套？

就是在一个函数里调用另一个函数。

(2) 嵌套函数的执行顺序是什么？

单行函数能够被嵌套任意层次，嵌套函数的计算是从最里层到最外层。

实例：显示受雇日期6个月后的下一个星期五的日期。结果日期将应该是：星期，月，日，年。使用Next 6 Month Review作为列别名。结果按受雇日期排序。

SQL> select to_char(next_day(add_months(hire_date,6),'星期五'),'day,mm"月"dd"日"yyyy"年"') as "Next 6 Month Review" from employees order by hire_date;

3. 通用函数NVL与NVL2

(1) 在Oracle中通用函数有哪些？

通用函数：可用于任意数据类型，并且适用于空值。

NVL(expr1,expr2)

NVL2(expr1,expr2,expr3)

NULLIF(expr1,expr2)

COALESCE(expr1,expr2,...,exprn)

(2) NVL与NVL2的作用是什么？ 他们之间的区别是什么?

NVL(expr1,expr2):转换空值为一个实际值。两个参数的数据类型必须匹配。

Expr1:是包含空值的源值或者表达式。 expr2:是用于转换空值的目的值。

实例：计算所有员工的年薪，如果有佣金包含佣金。

SQL> select last_name,salary,commission_pct,12*salary*nvl(1+commission_pct,1) as annualsalary from employees;

NVL2(expr1,expr2,expr3)：如果expr1非空，NVL2返回expr2;如果expr1为空，NVL2返回expr3。参数expr1可以是任意数据类型。最好三个参数的数据类型一致。

实例：查询雇员信息，如果有佣金的显示SAL+COMM，如果没有佣金则显示SAL。

SQL> select last_name , nvl2(commission_pct,salary*(1+commission_pct),salary) as actulsalary from employees;

4. 通用函数NULLIF与COALESCE

(1) NULLIF函数的作用是什么？

NULLIF(expr1,expr2)：比较两个表达式，如果相等返回空；如果不相等，返回第一个表达式。第一个参数不能为空。

实例：查询雇员，显示他们的first_name与长度，长度列命名为expr1。Last_name与长度，长度命名为expr2.判断他们的first_name与last_name的长度，如果长度相同返回空，否则返回first_name的长度。判断结果列命名为result。

SQL> select first_name,length(first_name) as expr1,last_name,length(last_name) as expr2,nullif(length(first_name),length(last_name)) as result from employees;

(2) COALESCE函数的作用是什么？

COALESCE(expr1,expr2,...,exprn)：返回表达式列表中的第一个非空表达式。

实例：查询雇员表，如果COMMISSION_PCT值是非空，显示他。如果COMMISSION_PCT值是空，则显示salary。如果COMMISSION_PCT和SALARY值都是空，那么显示10.

SQL> select last_name,coalesce(commission_pct,salary,10) from employees;

5. 条件表达式CASE与DECODE函数

(1) CASE表达式的作用是什么？

使得if-then-else条件判断容易实现。如果没有when...then满足条件，并且else子句存在，Oracle返回else_expr。否则，Oracle返回null。所有的表达式（expr、comparison_expr和return_expr）必须是相同的数据类型。

Case expr when comparison expr1 then return_expr1

[when comparison_expr2 then return_expr2

When comparison_exprn then return_exprn

Else else_expr]

End

实例：查询雇员，显示last_name,job_id,salary 如果JOB_ID是IT_PROG，薪水增加10%;如果job_id是ST_CLERK,薪水增加15%；如果JOB_ID还SA_REP,薪水增加20%。对于所有其他的工作角色，不增加薪水。

SQL> select last_name,job_id,salary,CASE job_id WHEN 'IT_PROG' THEN salary*1.1 WHEN 'ST_CLERK' THEN salary*1.15 WHEN 'SA_REP' THEN salary*1.2 END from employees;

(2) DECODE函数的作用是什么？

简化case表达式

DECODE（col|expression,search1,result1[,search2,result2,...,default]）

DECODE函数以一种类似于在多种语言中使用的IF-THEN-ELSE逻辑的方法判断一个表达式。DECODE函数在比较表达式（expression）和每个查找(search)值后，如果表达式与查找相同，返回结果。如果省略默认值，当没有查找值与表达式相匹配是返回一个空值。

实例：查询雇员，显示last_name,job_id,salary 如果JOB_ID是IT_PROG，薪水增加10%;如果job_id是ST_CLERK,薪水增加15%；如果JOB_ID还SA_REP,薪水增加20%。对于所有其他的工作角色，不增加薪水。

SQL> select last_name,job_id,salary,DECODE(job_id,'IT_PROG',salary*1.1,'ST_CLERK',salary*1.15,'SA_REP',salary*1.2) from employees;

小节练习：

1-写一个查询显示当前日期，列标签显示为Date。

SQL> select sysdate "Date" from dual;

2-对每一个雇员，显示employee number、last_name、salary和salary增加15%，并且表示成整数，列标签显示为New Salary。

SQL> select employee_id,last_name,salary,round(salary*1.15) "New Salary" from employees;

3-查询所有名字开始字母是J A或M的雇员，用首字母大写，其他字母小写来显示雇员的last_name，显示名字的长度，用雇员的last_name排序结果。

SQL> select initcap(last_name),length(last_name) from employees where last_name like 'J%' or last_name like 'A%' or last_name like 'M%'order by last_name;

4-对每一个雇员，显示其的last_name,并且计算从雇员受雇日期到今天的月数，列标签MONTHS WORKED。按受雇月数排序结果，四舍五入月数到最靠近的整数月。

SQL> select last_name,round(months_between(sysdate,hire_date)) "MONTHS WORKED" from employees order by months_between(sysdate,hire_date);

5-写一个查询对每个雇员做计算：<雇员的last_name> earns<salary>monthly but wants<3倍salary>。要求薪水中包含$与千位符。列标签Dream Salaries。

SQL> select last_name||' earns '||to_char(salary,'fm$999,999.00')||' monthly but wants '||to_char(salary*3,'fm$999,999.00') "Dream Salaries" from employees;

6-创建一个查询显示所有雇员的last_name和salary。对salary格式化为15个字符长度，用$左填充，列标签SALARY。

SQL> select last_name,lpad(to_char(salary),15,'$') salary from employees;

7-显示每一个雇员的last_name、hire_date和salary检查日期，该日期是服务六个月后的第一个星期一，列标签REVIEW。格式化日期显示看起来像“2019年3月9日 星期六”的样子。

SQL> select last_name,to_char(hire_date,'yyyy"年"mm"月"dd"日" day'),salary,to_char(next_day(add_months(hire_date,6),'星期一'),'yyyy"年"mm"月"dd"日" day') review from employees;

8-显示last_name、hire_date和雇员开始工作的周几，列标签day，用星期一作为周的起始日排序结果。

SQL> select last_name,hire_date,to_char(hire_date,'day') from employees order by to_char(hire_date-1,'d');

注：hire_date-1 周日变周六，周一变周日。因为SQL默认一周第一天是星期日，即星期日的‘d’是1，周一的‘d’是2。

9-创建一个查询显示雇员的last_name和commission（佣金）比率。如果雇员没有佣金，显示“No Commission”，列标签COMM。

SQL> select last_name,nvl(to_char(commission_pct,'0.99'),'No Commission') comm from employees;

10-用DECODE函数，写一个查询，按照下面的数据显示所有雇员的基于JOB_ID列值的级别。

SQL> select employee_id,last_name,job_id,decode(job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP','D','ST_CLERK','D',‘0’) grade from employees;

11-用case语法，实现前面的查询。

SQL> select employee_id,last_name,job_id,CASE job_id WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK' THEN 'D' ELSE '0' END grade from employees;

6. 什么是多表查询

(1) 什么是多表查询？

多表查询：当查询的数据并不是来源一个表时，需要使用多表链接操作完成查询。根据不同表中的数据之间的关系查询相关联的数据。

(2) 多表查询链接方式有哪些？

内连接：连接两个表，通过相等或不等判断连接列，称为内连接。在内连接中典型的连接运算有=或<>之类的比价运算符。包括等值连接和自然连接。

等值连接

非等值连接

自连接

SQL99：交叉连接（CROSS JOIN）

SQL99:内连接（INNER JOIN）

SQL99:自然连接（NATURAL JOIN）

外连接：在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左（或右）表的连接，称为左（或右）连接。返回内连接的结果，同时还返回左和右连接，称为全连接。

左外连接

右外连接

全外连接

子查询：当一个查询是另一个查询的条件时，称之为子查询。

7. 笛卡尔乘积

(1) 什么是笛卡尔乘积？

笛卡尔乘积是指在数学中，两个集合X和Y的迪尔卡积（Cartesian product），又称直积，表示为X*Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

(2) 如何避免笛卡尔乘积？

当一个连接条件无效或被遗漏是，其结果是一个笛卡尔乘积，其中所有行的组合都被显示。第一表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没什么用。应该在WHERE子句中始终包含一个有效的连接条件。

8. 多表连接语法

(1) 多表连接查询的语法结构是什么？

SQL86的多表查询语法结构：

SELECT table1.column,table2.column

FROM table1,table2

WHERE table1.column1 = table2.column2;

在WHERE子句中写连接条件；当多个表中有相同的列名是，将表名或者表的别名作为列名的前缀。

(2) 什么是定义连接？

当数据从多表中查询时，要使用连接（join）条件。一个表中的行按照存在于相应列中的值被连接到另一个表中的行。

(3) 多表连接的原则是什么？

在写一个连接表的select语句时，在列名前面用表名或者表别名可以使语义清楚，并且加快数据库访问。

为了连接n个表在一起，你最少需要n-1个连接条件。例如，为了连接4个表，最少需要3个连接条件。

9. 等值连接

(1) 什么是等值连接？

等值连接也被称为简单连接（simple joins）或内连接（inner joins）。是通过等号来判断连接条件中的数据值是否相匹配。

(2) 什么是抉择矩阵？

Decision matrix

是通过行与列来分析一个查询的方式。

例如，如果你想显示同一个部门中所有名字为Talor的雇员的名字和部门名称，可以写出下面的决策矩阵。

投影列 源表 条件

Last_name employees last_name=’Taylor’

Department_name departments employees.department_id=departments.department_id

实例：查询所有雇员以及他们所在的部门名称。

SQL> select last_name,department_name from employees,departments where employees.department_id=departments.department_id;

使用AND操作符附加搜索条件：除链接之外，还可以要求用WHERE子句在链接中限制一个或多个表中的行。虽然链接条件和限制条件先后顺序无影响，但我们一般将链接条件写在前，限制选择条件写在后。

实例：显示同一个部门中所有名字为Taylor的雇员的名字及部门号。

SQL> select last_name,department_name from employees,departments where employees.department_id=departments.department_id and last_name='Taylor';

10. 使用表别名

(1) 表别名的作用是什么？

使用表别名简化查询语句的长度。可以使用表别名代替表名。就像给列别名给列起另一个名字一样。表别名有助于保持SQL代码较小，因此使用的存储器也少。

(2) 使用表别名的原则是什么？

表别名最多可以有30个字符，但短一些更好；

如果在FROM子句中表别名被用于指定的表，那么在整个SELECT语句中都可以使用表别名；

表别名应该是有意义的。

表别名只对当前的SELECT语句有效。

实例：使用表别名方式改写显示同一个部门中所有名字为Taylor的雇员的名字和部门号。

SQL> select em.last_name,de.department_name from employees em,departments de where em.department_id=de.department_id and em.last_name='Taylor';

查询每个雇员的last_name、department_name和city(city来源于locations表)。

SQL> select last_name,department_name,city from employees em,departments de,locations lo where em.department_id=de.department_id and de.location_id=lo.location_id;

实例：查询Taylor的雇员ID、部门名称和工作的城市。

SQL> select em.employee_id,em.last_name,de.department_name,lo.city from employees em,departments de,locations lo where em.department_id=de.department_id and de.location_id=lo.location_id and em.last_name='Taylor';

11. 非等值连接

(1) 什么是非等值连接？

一个非等值连接是一种不使用相等（=）作为连接条件的查询。如！=、>、<、>=、<=、BETWEEN AND等都是非等值连接的条件判断。

实例：查询所有雇员的工资级别。

SQL> select em.last_name,em.salary,jg.gra from employees em,job_grades jg where em.salary between jg.lowest_sal and jg.highest_sal;

12. 自连接

(1) 什么是自连接？

使用一个表连接它自身的操作。

实例：查询每个雇员的经理的名字以及雇员的名字。

SQL> select worker.last_name workername,manager.last_name managername from employees worker,employees manager where worker.manager_id=manager.employee_id;

实现自连接的关键是表内含有一个自连接列manager_id，它与本表内的manager的employee_id形成表内连接。在逻辑上你可以把employees表理解为两个表workers和managers这两个表，workers.manager_id=managers.employee_id，这两个列是外键连接。