函数

-----------------------------------基础函数
1.lower函数用小写形成替换字符串的大写字符
2.upper函数用大写形式替换字符串的小写字符
3.initcap函数将字符串转换为首写字母大写的形式
4.concat函数连接两个字符面值，列或者表达式从而生成一个更大的字符表达式
5.length函数返回组成字符串的字符数。空格，制表符和特殊字符都被length函数计算自啊内。
6.lpad rpad函数也被称为左填充和右填充函数，他们分别返回在给定字符串左边或右边填充固定数量的字符后形成的合成字符串。用于填充的字符串包括字符子面值，列值，表达式，空格，制表符和特殊字符。
   lpad(s,n,p)s表示源字符串，n表示返回字符串的最终长度，p指定用于填充的字符串。
7.trim函数从字符值的开头或者结尾删除一些字符，从而生成一个更简短的字符项。
   trim(s)删除输入字符串两边的空格
   trim(trailing trimstring from s)从字符串s的结尾删除所以trimstring(如果存在的话)
   trim(leading trimstring from s)从字符串s的开头删除所以trimstring(如果存在的话)
   trim(both trimstring from s)从字符串s的两边删除所以trimstring(如果存在的话)
8.instr函数确定搜索字符串在在给定字符串的位置，它返回数字位置，在这个位置上，搜索字符串开始第n次出现（相对指定的起始位置而言）。如果搜索字符串不存在，那么instr函数就返回零。
   select instr('1#3#5#7#9#','#',3,4) from dual;从位置3开始查找，查找字符串‘#’第4次出现的位置
9.substr函数从给定源字符串中给定的位置开始，提取指定长度的子字符串。如果起始位置大于源字符串的长度，就会返回null。如果从给定起始位置提取的字符数大于源字符串的长度，
   返回的部分就是从起始位置到字符串结尾的子字符串。
   select substr('1#3#5#7#9#',5,3) from dual;从位置5开始，在位置5开始后移3个位置里面的字符串
10.replace函数用替换项取代源字符串中出现的所有搜索项。
   select replace('1#3#5#7#9#','#','*') from dual; #要被替换的字符，*替换成的字符
11.round函数依据指定的小数精度对数值进行舍入运算。返回依据有效数字以指定的小数精度进行上舍入或者下舍入的值。
   如果指定的小数精度为n，则要舍入的有效数字在小数点右边（n+1）的位置。
   如果n为负数，则要舍入的有效数字在小数点左边的位置。
   如果有效数值大于或者等于5，就进行“上舍入”，其他情况进行“下舍入”
   round（s，d）s参数表示任何数字值，d参数指定舍入的精度，它时可选的。如果没有指定d参数，则舍入的默认精度是零，也就是说将源数字舍入为最接近的整数。
   select round(1601.916,1) from dual;  1601.9
   select round(1601.916,2 ) from dual;  1601.92
   select round(1601.916,-3) from dual;  2000
   select round(1601.916) from dual;   1602
12.trunc函数依据指定的小数精度对数值执行截取运算。数字截取不同于舍入，因为如果小数精度是正数的话，最后的值依据指定的小数精度删除数字，并不进行向上或向下舍入。
   然而，如果指定的小数精度（n）为负数，输入值从小数点左边第n个数位开始向后归0.
   select trunc(1601.916,1) from dual;  1601.9
   select trunc(1601.916,2 ) from dual;  1601.91
   select trunc(1601.916,-3) fom dual;  1000
   select trunc(1601.916) from dual;   1601
13.mod函数返回除法运算的余数。提供两个数---被除数和除数，执行除法运算。
   如果除数时被除数的因数，mod就返回零，因为没有余数。
   如果除数等于零，则返回no division by zero错误，mod函数也返回零。
   如果除数大于被除数，那么mod函数返回被除数作为结果。这是因为它将除数除以零次，余数等于被除数。
   select mod(6,2) from dual;  0
   select mod(5,3)   from dual; 2
   select mod(7,35) from dual;  7 用7除以35，因为除数大于被除数，所以返回数字7作为模板
   select mod(5.2,3) from dual; 2.2 小数作为被除数。5.2除以3商为1，余数为2.2
14.sysdate函数没有参数，它返回数据库服务器当前的系统日期和时间。
15.months_between函数返回表示两个强制日期参数之间月数的数值。
16.add_months函数返回日期项，这个日期项通过将指定月数添加到给定日期值计算得出。
17.next_day函数返回的日期时星期内指定的日子下一次出现时的日期。
18.last_day函数返回指定日子所属的月的最后一天的日期。
19.round日期round函数依据指定的日期精度格式对值进行舍入运算。
20.trunc日期trunc函数依据指定的日期精度格式对值进行截取运算。
21.使用to_char函数将数字转换为字符
22.使用to_char函数将日期转化为字符
23.使用to_date函数将字符转换为日期
24.使用to_number函数将字符转换为数字
25.nvl函数评估任何数据类型的列或者表达式是不是空值。如果原始项是空值，选择备选的非空值；否则，返回原始项。
   nvl函数有两个强制参数，其语法是nvl（original，ifnull），其中original表示要测试的项，如果origuanl项计算为空，就返回ifnull；original和ifnull参数的数据类型必须一致。
   他们必须时相同的类型，或者可能将ifnull隐式转换为original参数的类型。nvl函数返回值的数据类型与original参数的数据类型相同。
26.nvl2函数评估任何数据类型的列火灾表达式是不是空值，如果第一项不是空值，那么返回第二个参数，否则返回第三个参数。注意，nvl函数不是这样，因为如果原始项不是空值，它就返回原始项。
   nvl2函数有3个强制参数，其语法时nvl（original，ifnotnull，ifnull），其中original表示被测试的项。如果original不是空值，就返回ifnotnull；如果original是空值就返回
   ifnull。ifnotnull和ifnull参数的数据类型必须一致，他们不能时long数据类型。
27.nullif函数厕所两项的相等性。如果他们相等，函数就返回空值，否则返回这两个测试项的第一项。
28.coalesce函数从参数列表中返回第一个非空值。如果所有参数为空，那么返回空值。
29.decode函数通过测试前两项的相等性来实现if-then-else条件逻辑，如果他们相等，则返回地三个参数，如果他们不相等，可能返回另一项。decode函数至少使用三个强制参数。

-----------------------------------分组函数
1.count
count(*)
count(distinct expr)
count(all expr)
count(expr)
调用count（*）时，组中的所有行（包括具有空值和重复值的行）都计算在内。执行count（distinct expr）时，只计算每组总expr唯一出现的次数。all关键字是默认语法的一部分，因此
count（all expr）和count（expr）是等效的表达式。如果expr基于命名的列，就忽略空值，如果expr基于各种情况的列，将为每一行进行计算，而不管行中是否有空值。expr的数据类型
number,date,char,varchar2
2.sum
sum(distinct expr)
sum(all expr)
sum(expr)
sum(distinct expr)提供在对组中每一行计算expr之后返回的唯一值进行相加得到的和。sum（expr）和sum（all expr）提供组中各行的expr相加的和，并且忽略空值。expr参数的数据类型必须时munber。
3.avg
avg(distinct expr)
avg(all expr)
avg(expr)
调用avg（distinct expr）时，将expr的不同值相加，并除以expr唯一出现的次数。avg（all expr）和avg（expr）将各行中expr的非空值相加，用求得的和除以组中的非空行数。expr参数的数据类型是mumer。
4.max min
5.group by
在select列表中不是分组函数的所有项必定是group by子句的分组特性。
使用group by子句将数据集划分为多个组。分组特性时各组成员共享的公共键。分组特性通常是单列，但也可能是多列或者一个不能基于分组函数的表达式。注意，当使用group by时，
只有分组特性和分组函数才能在select子句中出现。
6.having
having子句用来限制组级结果。
-----------------------------------------------------------------sql联接
第一个表称为源表；第二个表称为目标表
使用三种可能的联接子句实现自然联接。natural join,using和on
当源表和目标表共享相同名称的列时，就可以在它们之间执行自然联接，而无须指定联接列，这种联接有时称为纯自然链接。在这种情况下，在源表和目标表中具有相同名称的列自动彼此关联。

（1）natural join子句
select table1.column ,table2.column from table1 natural join table2;

（2）join using子句
select table1.column ,table2.column from table1  join table2 using (join_column1,join_column2....);
join using子句允许在using关键字之后在括号内显示指定一个或者多个同等连接列。在using关键字之后只允许出现具有共享名称的列。

（3）join on子句
select table1.column ,table2.column from table1  join table2 on (table1.column_name=table2.column_name);
纯自然联接和join using子句依赖具有相同列名的联接列。join on子句允许显示指定联接列，不管他们的列名时什么。这是联接子句最灵活，最常用的形式。

[源表和目标表之间的左外联接返回内联接的结果，以及被该内联接排除的源表的行；
源表和目标表之间的右外联接返回内联接的结果，以及被内联接排除的目标表中的行；
如果联接返回内联接的结果以及被该内联接排除的源表和目标表中的行，就实现了全外联接]
(1)左外联接left outer join
select table1.column,table2.column from table1 left outer join table2 on (table1.column=table2.column);
左外联接依据在on关键字之后指定的条件实现table1和table2之间的内联接。同时返回关键字左边表中因为不满足联接条件而被排除在外的行。

(2)右外联接right outer join
select table1.column,table2.column from table1 right outer join table2 on (table1.column=table2.column);
右外联接依据在on关键字之后指定的条件实现table1和table2之间的内联接。同时返回关键字右边表中因为不满足联接条件而被排除在外的行。

(3)全外联接full outer join
select table1.column,table2.column from table1 full outer join table2 on (table1.column=table2.column);
全外联接返回左外联接和右外联接的结合结果。在将由联接条件从两个表中排除的行结合到结果集中之前，实现table1和table2的内联接。

(4)使用交叉联接创建笛卡尔乘积 cross join
select table1.column,table2.column from table1 cross join table2;

-------------------------------------------------------------子查询
在from子句中，子查询可以嵌套到无限级，但在where子句中只能嵌套到255级。可以在select列表中使用子查询，也可以在查询的from，where，和having子句中使用子查询。
为了将子查询的结果传递给父查询，必须在父查询调用子查询之前执行语句中的子查询。
(1)将子查询的结果集用于比较
select last_name from employees where salary<(select avg(salary) from employess);
select department_name from department where department_id in (select distinct(department_id) from employees);
查找所有实际上拥有一名或者多名员工的部门。子查询被用作内联接的一种替代方法。
select department_name from department join employees on employees.department_id=departments.departmnet_id group by department_name;
(2)生成对其执行select语句的表
查找一个国家中员工的平均薪水
select avg(salary),country_id from (select salary,department_id,location_id,l.country_id from employees join department d using (departmnet_id)join
locations l using (location_id)) group by country_id;
(3)生成投影值
如何确定最高薪水和最高佣金率？如果薪水最高的员工同时有最高佣金率，那么支付的最大佣金率应该是多少？
select (select max(salary) from employees) * (select max(commission_pct)) from dual;
(4)生成传递给DML语句的值
insert into sales_hist select * from sales where date>sysdate-1;
update employees set salary=(select avg(salary)from employees);
delete from department where departmnet_id  not in (select department_id from employees);
insert into dates select sysdate from dual;
单行子查询返回一行，它返回只有一列的一行。
多行子查询返回行集合。这些查询通常用来生成结果集，这些结果集被传递到DML或者SELECT语句用作进一步处理。

可应用于单行子查询的比较运算符。
= > >= < <= <> !=
可应用于多行子查询的运算符
in ;等于列表中的任何成员
not in;不等于列表中的任何成员
any;返回匹配列表中任意值的行
all返回匹配列表中所有值的行
any和all的等价含义
<any 小于最高
>any 大于最低
=any  等于in
>all  大于最高
<All  小于最低

-------------------------------------------------------------------集合运算符
所有select语句都返回一组行，集合运算符将2个或者更多select语句的结果作为输入，并从中生成一个结果集。这就是复合查询。
复合查询中的每个查询都投影自己选中列的列表。这些列表的元素数量必须相同，排列顺序必须相同，数据类型也必须大致相同。
组成复合查询的查询中的列可以有不同的名称，但输出结果集使用第一个查询中列的名称。
组成复合查询的查询中的相关列必须是相同的数据类型组。
union，minus，intersect总是合并输入查询的结果集，然后排序结果，删除重复行。依据所有列，从左到右排序。

union返回两个查询的合并行，排序这些行删除重复行。
unionall返回两个查询的合并行，不排序，也不删除重复行。
intersect只返回同时出现在两个查询的结果集中的行，排序这些行，并删除重复行
minus只返回第一个结果集中的行，这些行没有出现在第二个结果集中，排序这些行并删除重复行。

符合查询会默认返回按所以列排序（从左到右)的行。唯一例外的是union all，在这种情况下不会对行进行排序。唯一允许使用order by子句的位置时复合查询的结尾。