OracleSQL基础要点（二）

万望

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运算符是同义词。在使用它们的时候，前面必须加上比较运算符。

1. <span style="background-color: white;">select * from t_book where year =any (select year from t_reader)</span>

复制代码

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

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

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

14.7 子查询在其它语句中的使用：

1. <span style="background-color: white;">- insert … select …
   
2. - update
   
3. - delete</span>

复制代码

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语句中，由此可以从根本上避免注入攻击。

张兴康

学习了

fteair