OracleSQL基础要点

Oracle SQL 基础要点

本文是学习《程序员的SQL金典》时的读书摘要，记录一些自己不太熟悉或者很重要的知识点。方便后期对照复习。

1.各种主流数据库的优缺点比较
- DB2由IBM开发，功能和性能非常优秀，不过对开发人员的要求比较高。其在SQL扩展上比较保守，对数据类型要求也非常严格，经常被称为“最难用的数据库系统”。
- Oracle吸收了DB2的优点，同时避免了IBM的官僚体制与过度学术化。因此，其无论是功能、性能还是可用性都是非常好的。
- Microsoft SQL Server可用性非常好，学习成本低。但是只能运行于Windows平台，在大数据量情况下表现都不是很好。
- MySQL是开源的，适用于小型的系统架构。

2.关于“索引”的详细理解
索引是在数据插入或者删除时候就更新的，它对需要建立索引的一列事先进行了排序操作，并记录在磁盘空间中，而表中原始的数据行之间是没有顺序这个概念的。因此，当查找按照索引走的时候，会按照某种算法（比如，二分查找）以最快效率找到需要的记录，而并非全表扫描。

3.插入或者删除数据时的“外键约束”
A表中的记录有一列k是外键，引用了B表中的某一列z，在往A表中插入数据的时候，k列的值必须在B表的z列存在，否则就不能将此记录插入A表。
同样的，如果删除B表中的数据时，要先删除A表中对B表z列的外键约束记录，然后才能删除B表中的某行数据。

4.SQL中的聚合函数
MAX、MIN、AVG、SUM、COUNT（count会忽略null的记录）

5.BETWEEN … AND …的用法
between … and … 是包含边界值的，比如between 2 and 4，代表的是[2,4]。一般而言，数据库系统对此进行了专门的优化，因此在检测范围值的时候应该优先使用between … and …，当然，如果你想使用开区间的话，就不适合使用这个了。

6.Where 1 = 1的由来及其危害
来源于“聪明的”程序员便于随时增加where后面的条件。详情查看《程序员的SQL金典》4.2.6节“低效的Where 1=1”。
之所以说它低效有危害，是因为在where后面添加了“1=1”之后，其余的条件就无法使用索引等优化查询策略，数据库会被迫对每行数据进行扫描。

7.关于rownum和row_number()的区别和使用
rownum和row_number()都是用来限制结果集行数的，推荐使用窗口函数row_number()，使用方法如下：

select * from 
(select row_number() over(order by salary desc) row_num,name,salary,age from employee) a 
where a.row_num >=3 and a.row_num <= 5

如上SQL使用between … and … 也是可以的。
但是rownum就不是那么容易的了。
首先oracle中无需我们自己计算行号，它为每个结果集都增加一个默认的表示行号的列，这个列的名称就是rownum。一般地，关于rownum的使用如下：

select rownum,name,salary,age from employee where rownum <6;

但是这样使用就会出现问题：

select rownum,name,salary,age from employee where rownum >3 and rownum <6;

或者

select rownum,name,salary,age from employee where rownum between 3 and 6;

以上两种由于加了下限，所以检索的结果一直都是空集。这是为什么？
书上说，当检索第一行记录时，其rownum为1，不符合条件，所以没有被放到检索结果中，当rownum为2时，因为第一条记录没有被放到结果集中，所以此时的rownum仍然为1。如此类推，永远没有记录符合rownum的条件。
所以，碰到同时需要满足上限和下限的限制记录数的情况，请使用窗口函数row_number()。

8.distinct的一些说明
distinct是对整个结果集进行数据重复抑制的，意思就是它去重去的是，结果集中所有列的值都相同的记录。

9.关于MySQL和Oracle中字符拼接的不同之处。
MySQL中的字符拼接不能使用“+”进行，当使用“+”时，MySQL会尝试将字段值转换为数字类型，如果转换失败，则认为该字段为0。比如，“123”+“17”的结果不是“12317”，而是数值130；age+“2”的结果是将原有的age字段的值都加上2；name+“3”的结果是3，因为name转换为数字失败，被认为是0。
MySQL中拼接字符可以是有CONCATE函数，具体用法请自行百度。
Oracle中拼接字符是使用“||”符号，也支持CONCATE函数，但具体函数的用法和MySQL的CONCATE略微不同。

10.dual的说明
MySQL中允许不使用from的select语句，比如select 1,2,’a’,’c’;这是合法的语句；但是Oracle中不允许，此时可以使用系统表dual，比如select 1 from dual;

11.Oracle中的一些函数
11.1 部分数学函数
- 绝对值：ABS()
- 指数：POWER()
- 平方根：SQRT()
- 随机数：dbms_random.value、dbms_random.value(num1,num2)、dbms_random.normal、dbms_random.string(opt,len)
- 向上及向下取整：CEIL()、FLOOR()
- 四舍五入：ROUND(m,d)
- 三角函数：这个一般用不到，忽略
- 求符号：SIGN()
- 取余：MOD()

11.2 部分字符串函数
- 计算长度：LENGTH()
- 小写大写：LOWER()、UPPER()
- 截去空格：LTRIM()、RTRIM()、TRIM()
- 取子字符串：SUBSTRING(string,start,length)
- 取子字符串在字符串中的位置：INSTR(string,substring)
- 替换子串：REPLACE(string,origin,current)

11.3 日期时间函数
根据表示的类型和精度的不同，数据库中关于时间的数据类型主要有：日期（Date）、时间（Time）、日期时间（DateTime）、时间戳（TimeStamp）。
- 将字符串转换为时间类型：TO_DATE(‘2016-10-26 12:12:12’,’YYYY-MM-DD HH24:MI:SS’)
- 获取当前时间：SYSTIMESTAMP、SYSDATE，如果想获取日期类型或者时间类型，请使用TO_CHAR(SYSDATE,’YYYY-MM-DD’)或者TO_CHAR(SYSDATE,’HH24:MI:SS’)
- 日期的运算：FBirthday+3（3天后）、FBirthday-10（10天前）、FBirthday+（2/24+10/60/24）（2小时10分钟后）、ADD_MONTHS(FBirthday,2)（两个月后）、ADD_MONTHS(FBirthday,-10)（10个月以前）、ADD_MONTHS(FBirthday,-3)-(10/24)（3个月零10小时之前）
- 日期的显示：可以使用to_char(date,’format’)，其中format不同可以有多不同的显示方式。

11.4 部分类型转换函数
在一些情况下数据库系统会替我们自动将目标类型转换为实际需要的类型，这种转换成为隐式转换，但是在有的情况下系统不会转换，需要你使用转换函数，这种方式成为显式转换。
- TO_CHAR()
- TO_DATE()
- TO_NUMBER()

11.5 空值处理函数
一般而言，你可以使用宿主语言中的流程控制语句来进行处理，但数据量大的情况下，会降低运行的速度，此时最好在SQL在这一层处理，也就是使用函数。
- COALESCE(expression,value1,value2,…valuen)：如果expression不为空，则返回expresion,否则判断value1是否为空，不为空返回value1，否则判断value2，value2不为空返回value2，否则判断value3…，直至valuen，如果所有的都为空，则最终返回NULL。
- NVL(expression,value)：是COALESCE(expression,value1,value2,…valuen)的简化版。只接受两个变量。
- NULLIF(e1,e2)：e1与e2不等价，则返回e1；等价则返回e1同类型的空值。
- CASE expression WHEN value1 THEN returnValue1 WHEN value2 THEN returnValue2 … ELSE defaultReturnValue END
- CASE WHEN condition1 THEN returnValue1 WHEN condition2 THEN returnValue2 … ELSE defaultReturnValue END
区别：COALESCE函数和其简化版的NVL函数主要用来进行空值的判定处理，不能用于处理非空值的比较逻辑；第一个CASE用法适用于一个条件的等价对比逻辑，不能用于处理多个条件的非等价对比逻辑。

11.6 Oracle中独有的函数
- 填充函数：LPAD(str1,n[,str2])、RPAD(str1,n[,str2])。比如，LPAD(name,15,’‘)表示将name字段左边用’‘填充到15个字符。如果n小于str1的字符个数，str1会从后面被截断。
- LAST_DAY(date)：返回date所在月份的最后一天。
- GREATEST(n1,n2,n3,n4)和LEAST(n1,n2,n3,n4)：计算一个集合中最大值和最小值。
- USER：用来取得当前登录的用户名。
- USERENV(option)：用来取得登录用户的相关环境信息。

12.索引与约束
12.1 索引的创建与删除
创建索引的语法：create index 索引名称 on 表名(字段1，字段2，……字段n)
索引名称必须唯一，可以多个字段组成一个索引。
删除索引的语法：drop index 索引名称

12.2 约束的类别及其特点
- 非空约束：NOT NULL，对插入和更新数据有效
- 唯一约束：UNIQUE，只是对约束的单个字段有效，复合唯一约束请使用CONSTRAINT 约束名 UNIQUE(字段1，字段2…，字段n)
- CHECK约束：CHECK(FAge>10)表示该字段的值必须都大于10，否则插入或者修改数据失败。CHECK(LENGTH(FNumber)>12)表示 FNumber的长度必须大于12。如果约束涉及到多个列，需要使用CONSTRAINT关键字，比如CONSTRAINT 约束名 CHECK(FAge>FWorkYear)。
- 主键约束：PRIMARY KEY，复合主键必须使用CONSTRAINT 约束名 PRIMARY KEY(FNumber,FName)。当然，也可以通过ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(n1,n2…)来为一个没有主键的表增加主键，但是一定要求那个待成为主键的列必须有NOT NULL约束。
- 外键约束：FOREIGN KEY 外键字段 REFERENCES 外键表名(外键表的主键字段)。

13.表连接
13.1 INNER JOIN：内连接，为数据库默认的连接方式，与省略INNER关键字是等价的。其中分为等值连接和非等值连接，值得注意的是，非等值连接做的是笛卡尔积运算。

13.2 CROSS JOIN：交叉连接，分为隐式使用和显式使用。 结果是两张表的笛卡尔积。

13.3 自连接：容易出现数据行重复的问题。自身与自身连接产生的重复以及A与B连接，B与A连接产生的重复。

13.4 外连接：用来解决内连接无法匹配空值的情况。即会返回主表中在从表中匹配不到的记录。
- 左外连接：返回左表中不符合连接条件的数据；
- 右外连接：返回右表中不符合连接条件的数据；
- 全外连接：左外连接以及右外连接结果的并集。

14.子查询
SQL语句允许将一个查询语句作为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当做结果集的查询语句被称为子查询。

14.1 标量子查询（单值子查询）：子查询的返回值必须只有一行记录，而且只能有一个列。

14.2 表子查询（列值子查询）：可以看成一个临时的数据表来使用。

14.3 IN运算符：可用于集合或者是多行单列的子查询结果集。

14.4 ANY和SOME运算符是同义词。在使用它们的时候，前面必须加上比较运算符。

select * from t_book where year =any (select year from t_reader)

这种用法等同于使用IN关键字。
值得注意的是，后面不能是固定的集合，这点区别于IN的用法。

14.5 ALL运算符：使用方法同ANY。但值得注意的是，如果子查询的结果集返回为空，那么ALL前面不管是什么运算符，其返回值永远为TRUE。

14.6 EXISTS：不要求待匹配的集合是单列的，该关键字是用来检查外部查询结果的每一行是否匹配子查询，如果子查询结果为空，则返回结果为FALSE，否则，返回结果为TRUE。

14.7 子查询在其它语句中的使用：
- insert … select …
- update
- delete

15.主流数据库的SQL语法差异以及解决方案
- ORM框架的使用可以让程序员避免直接书写SQL语句。
- ORMSQL的使用。
- SQL翻译器，接受开发人员编写的SQL，然后会将此SQL翻译成目标数据库支持的SQL。（有开源项目）

16.高级话题

16.1 SQL注入漏洞攻防
select (password='pass') as pwdCorrect form t_user where account='guest';
如果用户输入的密码为“1’ or ‘1’ = ‘1”，那么SQL语句就变成了：
select (password='1' or '1' = '1') as pwdCorrect from t_user where account='guest';
如此，任何账户都能登录成功。

16.1.1 过滤敏感字符
所有用户输入的内容都是不可信的，在代码中过滤掉“select,or,and,delete”等敏感字符，可以避免大部分的注入攻击，但同时也有一些缺陷：
1，有些用户太聪明了，总有一些办法绕过系统对于关键字的检测；
2，用户几乎不能发表关于SQL的文章，因为敏感字符，文章和标题提交会报错，用户体验极差。

16.1.2 使用参数化SQL
Java，C#等语言支持参数化SQL的机制，即开发人员先写好SQL语句，相应的参数位置使用占位符，待程序运行时再指定这些占位符所代表的值。但，此时并不是将真实值取代占位符拼接到SQL上，而是将带有占位符的SQL和参数传递给DBMS，DBMS会将参数当成一个普通的值来处理，非简单地拼接到SQL语句中，由此可以从根本上避免注入攻击。

16.2 SQL调优
决定DBMS性能的因素主要有硬件和软件两个部分，如果硬件条件很好，但是书写的SQL质量非常差，则系统的性能不会有明显的提升；相反，即使是略差的硬件条件，但是SQL语句进行了充分的优化，系统的性能提升将是惊人的。

16.2.1 SQL调优的基本原则
20%的代码消耗了80%的系统资源，对所有的SQL进行优化是不切实际的，因此可以使用DBMS中的一些工具先分析出最耗费系统资源的SQL，然后尝试对它们进行优化。

16.2.2 索引
根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引和非聚簇索引。
- 聚簇索引：数据表的物理顺序与索引顺序相同，比如汉语词典的拼音目录；
- 非聚簇索引：数据表的物理顺序与索引顺序不相同，比如汉语词典的偏旁部首目录。
最佳实践是在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引，而且一张表只能创建一个聚簇索引，因为表中记录的物理顺序只有一个。而非聚簇索引可以创建多个。
索引会占据一定的存储空间，会造成数据的插入，更新以及删除操作速度变慢，所以应该只创建必要的索引。
索引会造成碎片的问题，即当删除某条记录的时候，它对应的索引并没有被删除，久而久之，这些碎片会占据大量的存储空间，并在一定程度上降低系统速度。
如果发现索引中存在过多的碎片就要进行“碎片整理”，主流数据库都提供“重建索引”的功能。

16.2.3 表扫描和索引查找
全表扫描和索引查找，数据量少的情况下，全表扫描很有用，使用索引不见得就比全表扫描好。
DBMS中都有查询优化器，由他生成执行计划，决定使用全表扫描还是索引查找。

16.2.4 常用的优化手法
- 创建必要的索引，在经常需要进行检索的字段上创建索引。
- 参数化SQL不仅能够避免SQL注入漏洞攻击，还能让DBMS对该SQL执行预编译，下次再使用该SQL就可以直接使用预编译的结果，加快速度。
- DBMS一般采用自下而上，从右到左的顺序解析where子句，所以，如果Where中如果存在多个过滤条件，并且其中有表连接的过滤条件，请把该条件放在前面，这样，DBMS就会先过滤其它的条件，最后再做连接操作，大大减少了表连接的数量。
- 避免使用select *，即使需要检索所有的列，也不要使用，因为DBMS在解析过程中，会将*依次解析转换成所有的列名，降低查询速度。
- 尽量将多条SQL合并成一条SQL。
- 可以的话，用where替换having，where是在结果之前进行过滤，having是在结果之后过滤。
- 使用表的别名。
- 用exists代替in，因为in会对子查询结果进行排序和合并。
- 使用表连接替换exists。
- 避免在索引列上使用计算，在where子句中，如果索引列是计算或者函数的一部分，DBMS优化器将不会使用索引而使用全表扫描。比如select * from t_employee where salary*12 > 25000优化成select * from t_employee where salary > 25000/12将会从全表扫描转换为走索引。
- 在确定检索结果中不会用重复记录的情况下，使用union all替换union。
- 避免隐式类型转换造成的全表扫描，比如select * from t_person where level = 10，其中level列为字符串类型，且有索引，因为与数字10进行了比较，DBMS进行了隐式类型转换，所以相当于执行了select * from t_person where to_int(level) = 10，可以看出，因为对索引列使用了计算（函数），造成原来走索引的SQL现在变成了全表扫描。应该这样改select * from t_person where level = '10'。
- 如果DBMS优化器认为检索范围过宽，那么它将放弃索引而使用全表扫描。比如，使用is not null或者!=，可能造成优化器假设匹配的记录数太多。

16.3 事务

16.3.1 事务的4个基本特性：ACID。

16.3.2 隔离级别
- 脏读
- 不可重复读
- 幻读
- 使用READ_UNCOMMITED级别，会存在导致脏读、不可重复读、幻读
- 使用READ_COMMITED级别可以解决脏读问题，但会有不可重复读、幻读
- 使用REPEATABLE_READ级别可以解决脏读和不可重复读，但会有幻读
- 使用SERIALIZABLE级别可以解决以上三种问题

16.4 自动增长字段
ORACLE中使用自动增长字段需要首先定义一个SEQUENCE：

create sequence squence_id increment by 1 start with 1;

然后如下使用：

insert into t_person(FId,FName,FAge)
values(squence_id.nextval,'mason',19);

缺点是每次向表中插入记录的时候都要显式得到，这里可以使用一个触发器：

create or replace trigger trigger_personIdAutoIncre
before insert on t_person
for each row 
declare
begin
select squence_id.nextval into :new.FId from dual;
end trigger_personIdAutoIncre;

16.5 业务主键和逻辑主键
- 建议尽量不要使用业务字段作为主键，而是使用没有业务意义的逻辑主键。
- 逻辑主键的生成主要分为自增类型和UUID。自增类型的主键在表合并的时候可能会发生错误，UUID可以确保逻辑主键的唯一性。

16.6 NULL
NULL不是表示没有值，而是表示值未知。
- 比较运算符并不能检索出NULL，只能使用is null。
- 计算字段中如果有NULL，那么计算结果永远为NULL。（包括与数值，字符串，函数等的计算）
- 聚合函数中如果出现NULL，那么会被忽略。

16.7 开窗函数
与聚合函数一样，开窗函数也是对行集组进行聚合计算，但区别是，聚合函数每组只返回一个值，开窗函数可以为每组返回多个值，开窗函数所执行聚合计算的行集组是窗口。开窗函数也叫做分析函数。

TIPS：所有不包含在聚合函数中的列必须声明在GROUP BY字句中，如下写法是错误的：

select FCITY,FAGE,COUNT(*) from t_person where FSALARY < 5000

应该改为：

select FCITY,FAGE,COUNT(*) from t_person where FSALARY < 5000 group by FCITY,FAGE

这样的话就是按照FCITY和FAGE进行分组，然后在每个小的分组内进行select操作。

16.7.1 开窗函数的使用
开窗函数是在聚合函数后面增加一个OVER关键字，其使用格式为：
函数名（列） OVER（选项）
其中OVER关键字表示把函数当成开窗函数而不是聚合函数。SQL标准允许将所有的聚合函数用作开窗函数，仅仅使用OVER来区分。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围，如果选项为空，则表示对结果集中所有行进行聚合运算。

16.7.2 PARTITION BY
PARTITION BY是用来定义行的分区来供进行聚合计算，与GROUP BY不同的是，PARTITION BY创建的分区是独立于结果集的，它创建的分区仅仅供聚合计算使用，而且不同的开窗函数中的PARTITION BY所创建的分区互不影响。

16.7.3 ORDER BY
ORDER BY用来指定排序规则，对结果集按照指定的规则进行排序，并且在一个指定范围内进行聚合运算。
ORDER BY 字段名 RANGE|ROWS BETWEEN 上界 AND 下界
其中RANGE表示按照值的范围进行范围定义，ROWS表示按照行的范围进行范围的定义。
上界和下界可取值有：
- CURRENT ROW ：当前行
- N PRECEDING : 前n行
- UNBOUNDED PRECEDING ： 一直到第一条记录
- N FOLLOWING ： 后n行
- UNBOUNDED FOLLOWING ：一直到最后一条记录

TIPS:”ORDER BY FNAME RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”可以省略为”ORDER BY FNAME”。

16.7.4 高级开窗函数
- 排名，RANK(),DENSE_RANK(),ROW_NUMBER()
- 百分比，RATIO_TO_REPORT()
- NTILE(n)，将结果集平均分割成n个部分，返回当前行所属于的部分号
- LEAD()，LAG()，访问当前行之前或者之后某个指定列
- FIRST_VALUE(),LAST_VALUE()
……

16.8 WITH子句与子查询
为了避免多次使用类似的子查询，于是就有了WITH子句，用法：

with 别名(l1,l2,l3) as (子查询)
select …

其中别名后面的参数可以省略，不省略的话代表子查询结果中的列，需要一一对应。