Oracle 11g 总结篇2

男娘i 2021-11-22 06:12 330阅读 0赞

**第一部分：**

1.  字段名的别名用“”括起来，如:last\_name as “姓名”。
2.  去除重复：在投影的字段名前加上 distinct 就可以了。
3.  比如：select distinct em.last\_name "姓名" from employees em;
4.  日期格式的查询语句：‘00-0月-00’  分别对应的是日、月、年 。例如select \* from employees em where em.hire\_date = '24-1月-98';
5.  不等号的三种表达方式：<>、!=、^=
6.  between… and… (包含两个值)
7.  空值判断：is null;   非空判断：is not null;
8.  escape：可以用 escape标识符搜索实际的 % 和 \_ 符号。使用 ESCAPE 选项， 该选项指定换码符是什么。如果你想要搜索包含‘SA\_’的字符串可以使用 escape 对\\表 示该符号为转义符号。like '%SA\\\_%'  escape '\\';  例如：select \* from jobs where job\_id like 'S\_\\\_%' escape '\\';
9.  排序对于空值：升序排序时显示在最后，降序排序时显示在最前面  
    
10. 升序降序：升序是asc, 降序是desc

**第二部分：**

1.  查看表的结构：desc 表名；
2.  dual 表：dual 是一张只有一个字段，一行记录的表。dual 表也称之为'伪表'，因为它不存储主题数据。如果我们不需要从具体的表来取得表中数据，而是单纯地为了得到一些我们 想得到的信息,并要通过 select 完成时,就要借助 dual 表来满足结构化查询语言的格式。  
    
3.  单行函数与多行函数由于太多太杂，就不再这里说明了。
4.  sysdate 是一个日期函数，它返回当前数据库服务器的日期和时间。例如：select to\_char(sysdate,'YYYY-MM-DD  HH:MI:SS AM') from dual;  
    
5.  通用函数：nvl、nvl2、nullif、coalesce[![image][]][image 1]
6.  if-then-end 结构的判断：case 或 decode[![image][image 2]][image_image 2][![image][image 3]][image_image 3]例如：case：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;

decode：select last\_name,job\_id,salary, decode(job\_id,'IT\_PROG',salary\*1.1,'ST\_CLER K',salary\*1.15,'SA\_REP',salary \* 1.2) from employees;

多表查询中：为了连接 n 个表在一起，你最少需要 n-1 个连接条件。例如，为了连接 4 个表， 最少需要 3 个连接条件。 多表连接：自连接（与自己连接where条件中用等值连接，或用 natural join）、外连接（外连接是指查询出符合连接条件的数据同时还包含孤儿数据。左外链接包含左表的孤儿 数据，右外连接包含右表的孤儿数据，全外连接包含两个表中的孤儿数据。孤儿数据是指被连接的列的值为空的数据。）、内连接 inner join（内连接(INNERJOIN)： 内连接通过 INNERJOIN 来建立两个表的连接。在内连接中使 用 INNER JOIN 作为表的连接，用 ON 子句给定连接条件。INNERJOIN 语句在性能上其他 语句没有性能优势。）扩展的外连接：在 Oracle 数据库中对外连接中的左外与右外连接做了扩展，可以简化外连接的语法。 通过在连接条件的后侧使用(+)来表示是否显示孤儿数据，有(+)表示不显示孤儿数据而另一 侧则显示孤儿数据。但是该种写法仅能在 Oracle 数据库中使用。  
举例：

*   *  自连接等值连接（select worker.last\_name,manager.last\_name from employees worker,employees manager where worker.manager\_id = manager.employee\_id;）
     *  自连接 natural join（select d.department\_id,d.department\_name,l.city from departments d natural join locations l;）
     *  左外链接（selecte.last\_name,d.department\_namefrom employees e left outer join departments d on e.department\_id = d.department\_id;
     *  右外链接：right outer join;全外链接：full outer join）
     *  扩展的外连接（selecte.last\_name,d.department\_namefrom employees e ,departments d where e.department\_id(+) = d.department\_id; ）
     *  内连接 三个表（select e.last\_name,d.department\_name,l.city from employees e inner join departments d using(department\_id) inner join locations l using(location\_id) where e.employee\_id = 202; ）

11.常用命令:

*  建表：create table 表名 (列名 数据类型 约束 \[,列名n 数据类型 约束\]);
 *  建视图：create view 视图名 as select 投影列 from 表 where 条件 order by 按某个列进行排序;
 *  插入数据：insert into 表名 (列名  \[,列名n\]) values(值1 \[,值n\])       【插入指定日期的值：to\_date('2019-03-01','yyyy-MM-dd')。默认格式日期：'01/3 月 /2019'】

12.注意：

*  当有多个列匹配时，用 USING 子句匹配唯一的列。 如果某列在 USING 中使用，那么在引用该列时不要使用表名或者别名。 NATURALJOIN 和 USING 子句是相互排斥的（select d.department\_name,l.city from departments d **join** locations l **using**(location\_id) where location\_id = 1800; ）

**第三部分：**

1.  组函数|聚合函数（对数据进行分组,一组返回一个数据）：avg()、count()、max()、min()、sum()
2.  所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。
3.  count 函数有三种格式： count(\*) 、count(expr) 、count(distinct expr) 【count(\*)计算的次数是满足where的所有情况，包括重复行；count(字段名)：字段不为空的计数】  
    
4.  如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现 在 GROUP BY 子句中。如果未能在 GROUP BY 子句中包含一个字段列表，你会收到一个 错误信息。 (mysql不会报错，但返回的数据也不是正确的)
5.  分组原则：使用 where 子句，你可以在划分行成组以前过滤行。在 GROUP BY 子句中必须包含列。在 GROUP BY 子句中你不能用列别名。默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。可以用 ORDER BY 子句覆盖这个默认值。
6.  进行多个列分组：也就是对第一个字段名分组之后，再对每个组进行第二个字段名进行分组。
7.  先进行数据查询，在对数据进行分组，然后执行组函数。(where ---> group by ---> 组函数)
8.  having 语句通常与 GROUP BY 语句联合使用，用来过滤由 GROUPBY 语句返回的记录集。 having 语句的存在弥补了  where 关键字不能与聚合函数联合使用的不足。having 只能组函数的判断。[![image][image 4]][image_image 4]
9.  执行顺序：from ---> where ---> group by ---> having ---> select ---> order by
10. 子查询原则：子查询放在圆括号中。将子查询放在比较条件的右边。在单行子查询中用单行运算符，在多行子查询中用多行运算符。【可以放在 from、where、having字句中。】

**第四部分：**

1.  多行子查询：【其中：<ANY 意思是小于最大值。 >ANY 意思是大于最小值。<ALL 意思是小于最小值。>ALL 意思是大于最大值。】                                    举例（any）：select e.employee\_id,e.last\_name,e.job\_id,e.salary from employees e where e.job\_id <> 'IT\_PROG' and e.salary < any (select em.salary from employees em where em.job\_id = 'IT\_PROG') [![image][image 5]][image_image 5]
2.  常见子查询错误：使用子查询的一个常见的错误是单行子查询返回了多行

转载于:https://www.cnblogs.com/cgntiger/p/11089863.html

[image]: /images/20211122/77855a34005e43c3b4007c3b5544795f.png
[image 1]: https://img2018.cnblogs.com/blog/1657446/201906/1657446-20190626145257758-1620137181.png
[image 2]: https://img2018.cnblogs.com/blog/1657446/201906/1657446-20190627150044503-605775285.png
[image_image 2]: https://img2018.cnblogs.com/blog/1657446/201906/1657446-20190627150033517-1961934405.png
[image 3]: https://img2018.cnblogs.com/blog/1657446/201906/1657446-20190627150104643-1087487755.png
[image_image 3]: /images/20211122/a6f18d987fc744bfa867841200e05c90.png
[image 4]: https://img2018.cnblogs.com/blog/1657446/201906/1657446-20190629153334799-1468259184.png
[image_image 4]: /images/20211122/13353cae3ce04b8aaa510e38e630d79c.png
[image 5]: https://img2018.cnblogs.com/blog/1657446/201906/1657446-20190629153349998-146356281.png
[image_image 5]: /images/20211122/a1ba936f66dd4c8cafa30a46bbd44319.png