Oracle数据库学习笔记（二）

二十六、SUBSTR(char,m,n)函数：

1．字符串的首位从1开始计数。

2．用于截取字符串，返回char中从m开始取n个字符。

3．如果m=0，则从首字母开始取，若m为负数，则从尾部开始取。

4．如果n不写，或者n超过了char的长度，则取到字符串末尾为止。若n为负数，则返回null。

二十七、INSTR(char1,char2,n,m)函数，返回子串char2在char1中的位置。

1．从n位开始搜索，没有指定n，则从首字母（1）开始搜索。

2．m用于指定子串第m次出现的位置，如果不写，则为第1次出现的位置。

3．如果在char1中没有找到char2，则返回0。

二十八、NUMBER(precision , scale)：

1．precision表示数字的位数（包含小数），取值范围[1,38]。

2．scale表示精度，即小数位数。

3．指定了s没有指定p，即NUMBER(*,s)，p的默认为38，即NUMBER(38,s)。

二十九、ROUND(n,m)，四舍五入函数：

1．n是被处理的数字，可以使任意数字。

2．m指要四舍五入到小数点后第m位：

（1）m不写则表示默认为0，即取到整数。若m大于等于n的小数位数，则返回原来的n。

（2）m取负数，则表示取到小数点前第m位。若m取负数时，绝对值大于等于n的整数位数，则返回0。

三十、TRUNC(n,m)函数，n、m含义与ROUND函数相同，只是按照截取的方式返回数字。

三十一、MOD(m,n)，求余函数：

1．若n为0，则返回m。

2．若n为负数，则与正数时结果相同。

三十二、CEIL(n)向上取整，FLOOR(n)向下取整。n为整数时返回n。

三十三、日期可以进行计算：

1．对日期加减一个数字，等于加减了天数。

2．两个日期可以进行减法，差为相差的天数。

三十四、LAST_DAY(date)：

1．返回给定日期date所在那一个月的最后一天。

2．date可以是DATE值，也可以是’20-2月-49’格式的字符串。

三十五、ADD_MONTHS(date,i)：返回该日期date加上i个月后的日期。

1．如果i是小数，则被截取整数部分后再参与运算。

2．如果i是负数，则获得的是减去i个月后的日期。

三十六、MONTHS_BETWEEN(date1,date2)：计算date1与date2之间隔了几个月。返回一个NUMBER型。

1．实际运算是date1-date2。

2．只要不是相隔整数个月，都会返回小数。

三十七、NEXT_DAY(date,char)：返回距离date最近的还没有过的周char。char表示周日到周六，取值范围[1,7]。

三十八、LEAST(expr1,expr2,...)、GREATEST(expr1,expr2,...)：比较函数，返回参数中最小（大）的那个值。

1．所有参数类型必须一致。

2．在比较之前，第二个参数会被隐含的转换成第一个参数的数据类型，如果可以转换，则继续比较，如果不能，则会报错。

三十九、EXTRACT(date FROM datetime)：从参数datetime中提取date指定的数据，如年、月、日。date取值为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。

四十、NULL不等于任何值。在条件查询语句中，判断列值是否为NULL，应该使用’IS’、’IS NOT’关键字，而不是等号。如：WHERE NAME IS NULL。但是在给列赋值时，如果要赋值为NULL，则需要使用等号，而不是’IS’关键字。

四十一、NULL与字符串拼接等于什么也没做。NULL与数字运算，结果还是NULL。

四十二、NVL(expr1,expr2)：该函数的作用是将一个NULL值替换为非NULL值。若expr1为NULL，函数返回expr2。否则返回expr1。

四十三、NVL2(expr1,expr2,expr3)：若expr1不是NULL，则返回expr2，否则返回expr3。

NVL2能够实现NVL的功能，NVL(expr1,expr2) = NVL2(expr1,expr2,expr1)。

四十四、SELECT <*,column1 [other_name1],column2 [other_name2],...>

 FROM table_name

 WHERE condition;

1．SELECT语句用于指定要查询的列。

2．FROM指定从哪个表中查询。

3．WHERE指定查询条件。

4．可为column指定别名，当SELECT中查询的字段含有函数或者表达式时，查询出来的结果集中对应的该字段就是这个函数或者表达式，那么影响结果集的可读性，甚至在某些时候影响SQL语句的意义（子查询中）。所以通常我们会给该函数或者表达式添加别名，这样在结果集中该字段使用别名作为该列的名字。

四十五、查询条件：

1．不等于符号，写成：<>。

2．LIKE用于模糊匹配字符串。支持两个通配符：

（1）表示0~多个字符：% 。

（2）表示一个字符：_ 。

3．比较操作符：IN(value_list)。用来取出符合value_list表范围的数据。相反还有NOT IN(value_list)。

4．BETWEEN...AND...查询符合某个范围的数据。可以用在数字、字符和日期型数据上。

5．ANY、ALL不能单独使用，要配合比较操作符<、>、<=、>=一起使用。

（1）<ANY小于最大的

（2）>ANY大于最小的

（3）<ALL小于最小的

（4）>ALL大于最大的

四十六、ORDER BY column [ASC|DESC]：排序。ASC升序排序，在ORDER BY语句后不写 [ASC|DESC]，默认为ASC；DESC降序排序，若要排为降序，必须写在ORDER BY语句之后。

四十七、聚合函数：将表的全部数据划分为几组数据，每组数据统计出一个结果。一般是以字段（列）为一组，统计行数据，返回一行结果。又称作分组函数、多行函数或者集合函数。

1．MAX(column)、MIN(column)：取得最大值、取得最小值。可以用来统计任何可以比较的数据类型。字符串比较与JAVA中字符串比较规则一致。

2．AVG(column)、SUM(column)：统计column的平均值、和。只能用作数字类型。忽略NULL值。

3．COUNT(column)：计算column中的记录条数。忽略NULL值。

4．对空值的处理：若column列中有空值，而统计数据时要求不能忽略空值，则将column代替成NVL(column,no_null)。其中，no_null为column属性的非NULL值。

5．SELECT中出现了聚合函数，后面还有不是聚合函数的字段，该字段必须在GROUP BY中，否则保错。

四十八、GROUP BY子句：当我们在统计时需要对数据细分，这时可以配合这个子句使用。GROUP BY子句可以根据其后制定的字段值相同的记录看做一组。

四十九、HAVING子句：

1．WHERE是在查找的过程中，取出条件过滤的结果。而GROUP BY是分组以后的结果，在GROUP BY中使用WHERE会发生错误。WHERE中不允许使用聚合函数进行过滤！

2．WHERE是在第一个从表中查询数据时进行过滤的，只会讲满足条件的记录查询出来。而聚合函数的过滤式要先有数据，并且进行统计后得到结果才过滤的，所以这是在WHERE之后进行的过滤

3．所以在GROUP BY后使用HAVING来对分组之后的结果进行过滤。HAVING必须跟在GROUP BY之后，不能单独使用。

五十、查询语句的执行顺序：

1．FROM子句：从后往前，从右往左。数据量较少的表放后面。

2．WHERE子句：从下到上，从右往左。能过滤掉最大数量记录的条件放后面。

3．GROUP BY子句：从左往右分组。在GROUP BY子句之前使用WHERE，将不需要的记录先过滤掉。

4．HAVING子句：消耗资源，应尽量避免使用，HAVING会在检索出所有记录后猜对结果惊醒过滤，需要排序等操作。

5．SELECT子句：少用*号，尽量取字段名。Oracle在解析过程中，通过查询数据字典将*号一次转换成所有列名，消耗时间。

6．ORDER BY子句：执行顺序从左往右，消耗资源。

五十一、关联查询：

1．在FROM table_name other_name上，可以给表名也取一个别名，方便使用。这里不能使用AS关键字，应使用空格。

2．若不给定连接条件，就会将两张表的记录，两两相连，出现笛卡尔积，通常笛卡尔积是无意义的结果集。

3．关联查询中会忽略不满足连接条件的记录。

五十二、内连接：返回所有满足连接条件的记录。

SELECT

column1,

column2,

...

FROM

table_name1

JOIN

table_name2

ON

connect_condition;

五十三、外连接：作用是在关联查询中，将不满足连接条件的记录也查询出来。但是由于不满足连接条件，其中一张表中的数据无法对应找到另一张表中的数据时，那么根据外连接的类型，会将来着不满足连接条件一方表中的字段值设置为NULL。

1．

SELECT

column1,

column2,

...

FROM

table_name1

LEFT | RIGHT | FULL [OUTER]  --指定驱动表

JOIN

table_name2

ON

connect_condition;

2． 

SELECT

column1,

column2,

...

FROM

table_name1,

table_name2

WHERE

table1_value = table2_value(+);

五十四、自连接：用于解决相同类型数据，但是有存在父子关系，层级不确定时使用。

五十五、子查询：是一条select语句，该语句的查询结果并不是我们实际要执行SQL要求的结果，而是利用该查询结果为我们实际执行的SQL提供数据以便我们进行实际SQL的执行。

子查询是嵌套在其他SQK置中的。子查询常用来为SQL语句服务，但是也可以再DML、DDL中使用。

五十六、在DDL使用子查询：创建一张表myemployee，该表中包含的字段：empno、ename、sal、job、deptno、dname、loc要求含有emp表中所有数据。可以叫子查询的结果集当成一张表创建出来。要求：若查询的字段是一个函数或表达式，必须取别名。

CREATE TABLE talbe_name_new

AS

(SELECT * FROM table_name_old) --子查询语句

五十七、子查询根据查询结果分为：

1．单行单列子查询：查询结果为一个值。

2．多行单列子查询：查询结果为多个值。

3．多行多列子查询：查询结果为多个字段。

通常多列的子查询我们当做一张表看待，而单列则常用在WHERE子句中作为过滤条件用。

五十八、分页：分页查询时将表中的数据分段查询出来，这样做的好处在于减少数据量，从而可以减少内存压力，网络连接传输速度快，响应速度好。分页在不同的数据库中的实现不一样。

Oracle中使用ROWNUM实现分页。ROWNUM是一个伪列，不存在于任何一张表中，但是所有表都可以查询到该列，该列的值由查询出来的记录决定，每当从表中中查询出一条记录，ROWNUM字段则作为该条记录的行号显示，自动增加，从1开始。

注意：当使用个ROWNUM对结果集编号的过程中，不应当使用ROWNUM与大于1以上的数字过滤，否则查询不出任何数据！

取按工资高低排序，5到10的人的名字和工资表：

SELECT rn,ENAME,SAL

FROM (SELECT ROWNUM rn,*

FROM( SELECT ENAME,SAL

  FROM EMP

  ORDER BY SAL DESC

)

)

WHERE rn BETWEEN 5 AND 10;

但是这样效率低下，在数据量大的时候，第二层嵌套，ROWNUM其实只需要5~10条，往后的编号都不需要了，所以可以再第二层嵌套时加上过滤，使ROWNUM编号小于10即可。

SELECT rn,ENAME,SAL

FROM (SELECT ROWNUM rn,*

 FROM( SELECT ENAME,SAL

      FROM EMP

   ORDER BY SAL DESC

)

WHERE ROWNUM<=10

)

WHERE rn>5;

 

计算行号的范围公式：

Page：当前页数

PageSize：每页显示的条目数

第N页显示的条目范围：[ (page-1)*pagesize+1 , page*pagesize ]。

五十九、DECODE[expr,search1,result1,search2,result2,...[,deafult] ]函数：可以有多个search和result对应关系，如果任何一个search都没有匹配到，则返回最后的dafault，如果dafult没有，则返回NULL。

CASE语句与DECODE效果相同：

CASE expr WHEN search1 THEN search1

WHEN search1 THEN search1

...

[ELSE default] END

CASE语句没有逗号，用空格。

六十、在排序中，NULL值视作最大值。

六十一、ROW_NUMBER()函数，可以根据指定字段分组，再按照指定字段对组内记录排序，然后改函数为每条记录生成一个组内连续且唯一的数字。这个函数返回的与ROWNUM已有，是排序的数字。

六十二、RANK()函数，生成组内不连续也不唯一的数字。相同数据的多个行，排名相同，但是下一位的排名不变。

六十三、DENSE_RANK()函数，生成组内连续但是不唯一的数字。在并列排名之后的一名，会接着上一名排序。

六十四、合并多个SELECT语句的结果，可以使用集合操作符，UNION、UNION ALL、INTERSECT、MINUS。多条要操作的SELECT语句的列的个数和数据类型必须匹配。ORDER BY语句只能放在最后一个SELECT语句的后面。

1．UNION会自动去掉合并后重复记录。

2．UNION ALL返回两个集合中所有的行，包括重复的行。

3．UNION对查询结果进行排序，UNION ALL不排序。

六十五、高级分组函数：

1．ROLLUP()函数：GROUP BY ROLLUP(a,b,c,...)

2．CUBE()函数：GROUP BY CUBE(a,b,c,...)分成2的参数个数次方个组合。

3．GROUPING SETS()函数：按照指定的分组方式进行分组，并将结果并在一个结果集里显示。

六十六、视图VIEW：也被称作虚表，是一组数据的逻辑表示。视图在SQL语句中体现的样子是一张表，但它并不是数据库中真是存在的一张表，而只是一个SELECT语句对应的查询结果集。

   视图的作用：

1．重用子查询，简化SQL复杂度。视图的作用相当于C++的内联函数。

2．限制数据访问。

创建视图：

CREATE [OR REPLACE] VIEW view_name [alias] AS subquery

六十七、视图中对应的查询语句的字段若含有表达式、函数等操作时，必须对该字段添加别名。

六十八、对视图进行DML操作，只能对简单视图进行DML操作，对视图进行DML操作就是对视图数据来源的基础表进行操作。而视图看不到的字段在进行插入时，会插入默认值，对此若存在有NOT NULL约束时，会引发非空约束错误。所以在进行DML操作时，不能违反基础表的约束。

六十九、对视图插入数据，可能导致对基表的污染。

七十、对视图添加检查选项后，可以做到避免视图污染基表数据。复杂视图不能进行DML操作。

1．插入数据：之恩能够插入视图看得到的数据。

2．更新数据：更新视图数据后视图必须对其可见。

七十一、创建具有CHECK OPTION约束的视图：CHECK OPTION表示通过视图所做的修改，必须在视图所见的范围内。修改前后的数据都必须在视图可见范围内。

七十二、创建具有READ ONLY约束的视图：将视图设置为只读后，不能进行DML操作。

七十三、和视图相关的数据字典：

1．USER_OBJECTS：用户创建的所有数据库对象。

2．USER_VIEWS：用户创建过的所有视图。（所有表：USER_TABLES）

3．USER_UPDATE_COLUMNS：

七十四、【非SQL通用】序列：可以按照指定的规律生成数字。通常使用序列生成的数字作为表的主键值使用。

七十五、序列SEQUENCE：

 CREATE SEQUENCE [schema.] sequence_name

 [ START WITH i ] [ INCREMENT BY j ]

 [ MAXVALUE m | NOMAXVALUE ]

 [ MINVALUE n | NOMINVALUE ]

 [ CYCLE | NOCYCLE ] [ CACHE p | NOCACHE ]

1．i是起始值，不设置，默认为1，j是步长值，不设置，默认为1。

2．如果j为正，则序列递增，j为负，序列递减。

3．m为最大值，n为最小值。

4．CYCLE表示在递增至最大值或递减至最小值之后是否重用序列。若没有设置START WITH开始的值，则默认是NOCYCLE。

5．CACHE序列缓存，默认是20。若是默认缓存的情况下，在生成序列对象的时候，序列的数据字典中自动生成了1~20，在调取NEXTVAL时，是将缓存中的1~20依次赋给NEXTVAL。但是这样会有断链的情况发生，①、若在使用NEXTVAL到7这个值的时候，服务器断电，重启服务器之后，由于缓存清零，此时数据字典中认为之前的1~20已经取完，将重新生成20个数字：21~40，分配给缓存，从21开始赋给NEXTVAL使用。（这种情况的解决办法之一是，使用没有缓存的序列）②、若有两个表同时使用一个序列作为主键生成ID，两个表都调用NEXTVAL，则也会发生断链。（这种情况的解决办法之一是，两个表不要同时调用NEXTVAL，一个调用NEXTVAL，另一个调用CURRVAL）

6．序列有两个伪列：

（1）NEXTVAL：获取序列下一个值（当前值+步长）。

（2）CURRVAL：获取序列当前的值。在创建之初不能直接使用，要先调用一次NEXTVAL生成一个数字后才能得到。

七十六、索引：提高查询效率。

1．在表table的column1字段上加上索引：

CREATE [UNIQUE] INDEX index_name

ON table(column1[,column2,...])

2． 符合索引，也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用多个列作为排序依据，可以建立这几个列的符合索引，ORDER BY子句中的列的顺序应与索引中的列顺序一致。

3．对于函数也可以加索引，加上索引以后，若要使用索引，在使用时（如查询的过滤条件中）必须与所加索引的类型形式一致。

4．经常对表进行DML操作，需要定期重建索引，提高索引的空间利用率：

ALTER INDEX index_name REBUILD;

5．表上有不合理的索引，会导致操作性能下降，删除索引的语法：

DROP INDEX index_name;

6．使用索引的原则：

（1）不要在小表上建立索引。

（2）经常出现在WHERE、ORDER BY、DISTINCT子句中的列和经常作为表的连接条件的列，可以增加索引。

七十七、约束（CONSTRAINT）：约束条件，完整性约束条件。

1．非空约束（NN）：NOT NULL。只能是列级约束，可以取消。

2．唯一性约束（UK）：UNIQUE。用于保证字段不出现重复值。表级约束，不能取消。

3．主键约束（PK）：PRIMARY KEY。在主键约束下的字段，不允许为空且不能重复。主键可以用来唯一确定一行数据。一个表上只能有一个主键。主键不应该更新，不包含动态数据，应为自动生成。

4．外键约束（FK）：FOREIGN KEY。

5．检查约束（CK）：CHECK。强制要求字段上的每个值都要满足check的条件。

6．在创建字段时使用全名增加约束：

（1）NN：直接跟在字段属性之后，不加逗号

CONSTRAINT anyString NOT NULL

（2）UK：加逗号，在建表以后再增加

CONSTRAINT anyString UNIQUE(column)

或者 ALTER TABLE table_name

  ADD CONSTRAINT anyString UNIQUE(column);