（2）Oracle——————单行函数和多表查询

末蓝、 2023-07-13 12:17 135阅读 0赞

1，SQL函数的介绍

*  1.1，SQL函数的介绍
 *  1.2，两种SQL函数

2，单行函数

*  2.1，大小写控制函数
 *  2.2，字符控制函数
 *  2.3，数字函数
 *  2.4，日期函数
 *  2.5，转换函数
 *  2.6，通用函数
 *  2.7，条件表达式

3，多表查询

*  3.1，笛卡儿积
 *  3.2，表的别名
 *  3.3，连接多个表
 *  3.4，非等值连接
 *  3.5，内连接和外连接
 *  3.6，自然连接
 *  3.7，ON 子句创建连接
 *  3.8，自连接
 *  3.9，左（右）外连接和满外连接

#### SQL函数的介绍 ####

**1，什么是SQL函数？**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70]

**2，两种SQL函数：**

单行函数和多行函数

#### 单行函数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 1]

**1，大小写控制函数**

![在这里插入图片描述][2020031200232547.png]  
\----initcap(ename)首字母大写 ,upper(大写)，lower(小写)\----  
select a.\* from emp a where initcap(a.ename)= ‘James’ and upper(a.ename) = ‘JAMES’

**2，字符控制函数**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 2]  
例子：

\----名字中有A的全部替换0\----  
select replace(a.ename,‘A’,‘0’) ename from emp a  
\----薪水为5位，若不够5位从左边开始用0添加\----  
select lpad(a.sal,‘5’,‘0’) sal from emp a

**3，数字函数**

*  DUAL 是一个‘伪表’，可以用来测试函数和表达式

----round：四舍五入\----  
        select round(45.625, 2) from dual; -->45.63

----trunc：截断\----  
        select trunc(45.625, 2) from dual; -->45.62

----mod：求余\----  
        select mod(500, 300) from dual; -->200

**4，日期**

*  Oracle 中的日期型数据实际含有两个值: 日期和时间。
 *  函数SYSDATE 返回：日期，时间 例如：select sysdate from dual; --> 2020/3/12 00:00:00
 *  日期函数  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 3]
    
    **5，转换函数**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 4]

*  Oracle 自动完成下列转换（隐式数据类型转换）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 5]  
    例如：select ‘11’+2 from dual; -->13;
 *  显示转换  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 6]  
            ----to\_char日期转字符串,数字转字符串\----  
            select to\_char(sysdate,‘yyyy-MM-dd’),to\_char(2121.13,‘L000,999.99’) from dual; --> 2020-03-12, ￥002,121.13  
            ----to\_date字符串转日期\----  
            select to\_date(‘2020-03-12’,‘yyyy-MM-dd’) from dual; --> 2020/3/12  
            ----to\_date字符串转数字\----  
            select to\_number(‘￥002,121.13’,‘L000,999.99’) from dual; --> 2121.13  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 7]
 *  日期格式元素  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 8]
    
    **6，通用函数**
 *  NVL (expr1, expr2) -----> 如果expr1为空，则选择expr2的值，否则选择自身

\----查询员工表中没有部门号的员工，部门号写没有部门----  
        select a.ename,nvl(to\_char(a.mgr),‘没有部门’) job from emp a;

*  NVL2 (expr1, expr2, expr3) -----> 若expr1不为空选择expr2，若为空选择expr3

\----查询部门中无奖金的奖金为1，有奖金的加0.5----  
        select a.ename,nvl2(a.comm,a.comm+0.5,1) from emp a

*  NULLIF (expr1, expr2) -----> 相等返回NULL，不等返回expr1
 *  COALESCE (expr1, expr2, …, exprn) -----> COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE

**7，条件表达式**

*  在 SQL 语句中使用IF-THEN-ELSE 逻辑
 *  使用两种方法:  
    **CASE** 表达式  
    **DECODE** 函数

\-----查询部门号为10，20，30的员工信息，部门为10打印1.1倍工资，其他部门打印1.2倍工资（CASE）----  
        select a.ename,a.deptno,case a.deptno when 10 then a.sal \* 1.1 else a.sal \* 1.2 end new\_sal from emp a ;

\-----（DECODE）----  
        select a.ename,a.deptno,decode(a.deptno,10,a.sal \* 1.1,a.sal \* 1.2) new\_sal from emp a ;

*  练一练

\----1，打印出 “2020年03月12日 09:05:08” 格式的当前系统的日期和时间（注意: 使用双引号向日期中添加字符）  
        select to\_char(sysdate, ‘YYYY"年"MM"月"DD"日" HH:MI:SS’) from dual; ----> 2020年03月12日 09:05:08

\----2，格式化数字: 1234567.89 为 1,234,567.89  
        select to\_char(1234567.89, ‘999,999,999.99’) from dual;

\----3，字符串转为数字时，若字符串中没有特殊字符, 可以进行隐式转换:  
        select ‘1234567.89’ + 100 from dual;

\----4，若字符串中有特殊字符, 例如 ‘1,234,567.89’, 则无法进行隐式转换, 需要使用 to\_number() 来完成  
        select to\_number(‘1,234,567.89’, ‘999,999,999.99’) + 100 from dual; ----> 1234667.89

\----5，对于把日期作为查询条件的查询, 一般都使用 to\_date() 把一个字符串转为日期, 这样可以不必关注日期格式  
        select a.ename,a.hiredate from emp a where a.hiredate = to\_date(‘1981-2-20’,‘yyyy-mm-dd’);

\----6，查询每个月月末倒数第 2 天入职的员工的信息  
        select a.\* from emp a where a.hiredate = last\_day(a.hiredate) - 2;

\----7，计算公司员工的年薪  
        select a.\*,a.sal \* 12 + nvl(a.comm,0) 年薪 from emp a;

#### 多表查询 ####

**1，笛卡儿积**

*  表EMPLOYEES (20行)，表DEPARTMENTS (8行)，笛卡尔集：20x8=160行
 *  笛卡尔集会在下面条件下产生：

省略连接条件  
        连接条件无效  
        所有表中的所有行互相连接

*  如何避免笛卡尔集：

可以在 WHERE 加入有效的连接条件。

**2，表的别名**

*  使用别名可以简化查询。使用表名前缀可以提高执行效率。  
    如：

SELECT e.employee_id,
           e.last_name,
           e.department_id,
           d.department_id,
           d.location_id
      FROM employees e, departments d
     WHERE e.department_id = d.department_id

**3，连接多个表**

*  连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

**4，非等值连接**

*  查询员工表（emp）中的员工的工资属于工资级别表（sal\_grades）中的第几档：

select a.ename, a.sal, b.level
      from emp a, sal_grades b
     where a.sal between b.low_sal and hight_sal;

**5，内连接和外连接**

*  **内连接**：  
    合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
 *  **外连接**：  
    两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+)

例（右外连接）：

select * from emp a, dept b where a.deptno(+) = b.deptno;

**6，自然连接**

*  NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。在表中查询满足等值条件的数据。如果只是列名相同而数据类型不同，则会产生错误。

**7，ON 子句创建连接**

*  自然连接中是以具有相同名字的列为连接条件的。可以使用 ON 子句指定额外的连接条件。这个连接条件是与其它条件分开的。ON 子句使语句具有更高的易读性。
 *  使用 ON 子句创建多表连接

SELECT employee_id, city, department_name
      FROM employees e
      JOIN departments d
        ON d.department_id = e.department_id
      JOIN locations l
        ON d.location_id = l.location_id;

**8，自连接**

*  自己连接自己

例如：

select m.*
      from employees e, employees m
      where e.manager_id = m.employee_id and e.last_name = 'Chen'

**9，左（右）外连接和满外连接**

*  两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外连接。
 *  两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满 外连接。  
    ![在这里插入图片描述][20200312230753820.png]

--------------------

[下一章，（3）Oracle——————分组函数，子查询，创建和管理表和数据处理 ][3_Oracle_]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200312000421703.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200312001009161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[2020031200232547.png]: https://img-blog.csdnimg.cn/2020031200232547.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200312002528389.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200312004926977.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200312005119342.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200312005252961.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200312005955298.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200312201337519.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200312201044274.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTMwMDA0,size_16,color_FFFFFF,t_70
[20200312230753820.png]: https://img-blog.csdnimg.cn/20200312230753820.png
[3_Oracle_]: https://blog.csdn.net/qq_41530004/article/details/104849071