oracle sql函数以及用法

--******************************--sql字符型单行函数
--LOWER,函数格式LOWER(column|expression),功能：把字符串转换成小写
 select LOWER('A B C') from dual;


--INITCAP,函数格式INITCAP(column|expression),功能：把字符串首字母大写
select INITCAP('abcdef') from dual;


--concat,函数格式CONCAT（column1|expression1,column2|expression2）,该函数用于连接两个字符串，或者连接两个列中的数据。
select concat('abc','def')from dual;


--SUBSTR,函数格式：SUBSTR（column|expression,m[,n]）,该函数从一个字符串中获取一个子串，该子串从expression的第m个字符开始，到第n个字符结束，如果不指定n则从第m个字符开始到expression表达式到结尾
1、select substr('abcdef',2) from dual;
2、select substr('abcdef',2,4) from dual;


--length，函数格式length(column|expression),功能技术字符串的字符个数
select length('abcdef')from dual;


--replace,函数格式replace(text,search_string,replacement_string),功能把源字符串（text）中的某个字符串（search_string）替换为另一个字符串（replacement_string）
select replace('abcdef','abcd','dcba') from dual


//sql数字型号单行函数
--round,功能是对一个数字，输出用户指定的小数位,会有四舍五入
select round(1.234567,4) from dual;


--trunc,功能是截断一个数字，只保留小数点后一定的位数，该函数处理数字时不使用四舍五入规则
select trunc(1.234567,4) from dual;


--mod,功能是求余数
select mod(1000,400) from dual;


//sql日期型单行函数
--sysdate,功能返回系统当前日期，该日期受操作系统限制，即Oracle数据库读取操作系统时间
select sysdate+20/24 from dual;
select sysdate from dual;


--months_between,功能的参数为两个日期，得到两个日期之间的月数，即两个日期之间相差几个月
select months_between('06-jun-10','06-jun-09') from dual;


--add_months(date,n),参数为日期型数据，和一个数字型数据n,函数功能是把n个月添加到日期型数据上，输出结果为日期型数据
select add_months(sysdate,4)from dual;


--next_day(date,string),该函数的参数是一个日期型数据，输出为该日期的下一个指定日期
select next_day(sysdate,'Saturday') from dual;


--last_day(date),功能是返回参数中日期当月的最后一天的日期。
select last_day(sysdate) from dual;




--*********************************************--条件表达式
--case表达式用于逻辑判断，语法结构：case expr when comparsion_expr1 then return_expr1 [when comparison_expr2] then return_expr2 [when comparison_expr3] then return_expr3 else else_expr end as new_expr


select case t.refid when 1 then '普通户'
       when 2    then '重点户'
       when 3    then '风险户'
       when 4    then '小额户'
       when 5    then '贷款户'
       end as new_refid
  from ebs_ref_keyflag t


--decode函数同case表达式具有相同的功能，其语法格式为decode(column|expression,search1,result1[,search2,result2,...,][default]), 该函数的执行过程是首先判断search1的值是否和col或expression的值相等，如果相等，则返回result1,否则判断search2的值是否和col或expression的值相等，如果相等则返回result2,依次判断，如果不相等，则返回默认值default.
select decode(t.refid,1,'普通户',2,'重点户',3,'风险户',4,'小额户',5,'贷款户') [as] new_refid_name from  ebs_ref_keyflag t


--*********************************************--常用DML语句
--select 
select * from table_name;


--insert 
insert into table_name (column1[,column2,...]) values (value1[,value2,...]);


--update
update 表名称 set 列名称 = 新值 WHERE 列名称 = 某值;


--delete 
delete from 表名称 where 列名称 = 值




--*********************************************--常用DDL语句
--create:创建数据库对象，如，表空间，表、索引、视图等
create table 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)


create tablespace user_data  
logging  
datafile 'D:\oradata\user_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 




--alter:改变系统参数，如改变用户默认表空间、SGA的大小 ALTER USER 用户名 DEFAULT TABLESPACE 表空间名; 




--drop:删掉一个对象，如删除一个表、索引或序列号等,其中在删除主键约束时使用cascade关键字可以删除参照该列的那些外键和使用on delete cascade关键字来级联删除参照该关键字的数据
DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES CASC
ADE CONSTRAINTS;


--rename:重命名一个对象,如重命名表名或列名
rename table tbl_name to new_tbl_name;
sp_rename ‘列名', '新列名','column'


--delete:删除表中的数据
DELETE FROM 表名称 WHERE 列名称 = 值 --删除表中指定行
delete from 表名称   --删除表所有行




--*********************************************--null和null值处理函数
--null空值处理函数,空值是一类没有定义的，具有不确定性的值。在数据表中，这类值无法表示，更无法显示。
select * from emp e where e.ename = null;


--nvl函数和nvl2函数，nvl函数使得空值可以进行运算，它是空值转换函数。如果不使用空值转换函数，空值是无法进行运算的，语法格式nvl(expr1,expr2),其计算规则是如果expr1的值为空值（null）,则返回expr2的值，否则返回expr1的值。其中表达式expr1和expr2的数据类型必须相同，它们可以是数字类型、字符类型和日期类型。
select nvl(ad.faceflag,'null') as null_faceflag from accmdte ad


--nullif函数，比较两个表达式，语法格式为：nullif(expr1,expr2)如果二者相等则返回空值null,如果二者不等则返回第一个表达式的值。要求第一个表达式的值不能为null。


--coalesce函数返回该函数中第一个为null的表达式的值，其语法格式为：coalesce(expr1,expr2,...exprn)




--*********************************************--分组函数，分组函数对表中的多行进行操作，而每组返回一个计算结果。
--avg，其语法格式为avg([distinct|all]expr)，计算某列中某种分组后，每组的平均值，计算时会忽略空值（null）,用于计算数字类型


--sum,其语法格式为sum([distinct|all]expr),计算某列中某种分组后，每组的和，用于计算数字类型。


--max，计算最大值


--min，计算最小值


--count,行计数


--distinct 去掉列中的重复的值


--*********************************************--连接查询：乘积连接、相等连接、自连接、不等连接、外连接
--乘积连接，笛卡尔积是数据库关系运算中的概念，笛卡尔积的结果是将两个表中的所有行的一个组合，如果表1有n行，表2有m行，则笛卡尔积的结果是n*m行。
select a.id,a.name,b.id,b.gender from table_a a ,table_b b order by a.id


--相等连接，是使用where字句中两个表或多个表中相应列的值相等条件来选择数据。以两个表的相等连接为例，首先找到两个表中满足相等条件的记录（表中相应的行），然后再根据select语句选择相应列的值
select e.ename,d.deptno,d.dname,d.loc from dept d,emp e where d.deptno = e.deptno




--自然连接，是一个重要的连接运算，在oracle数据库中自练接可以通过相等连接实现，如果有两个表做自连接，一般这两个表有至少一个相同的属性，首先将相同属性的相同值的行连接起来，然后去掉一个重复的相同属性，如果表1有n个属性，而表2有m个属性，二者具有r个相同属性，则自然连接的结果具有（n+m-r）个属性的记录。
select a.id,a.name,b.id,b.gender from table_a a ,table_b b where a.id = b.id;


--自连接，顾名思义就是一个表和自己连接，通过一些限制性的查询满足查询需要，在emp表中，我们知道manager对于salesman具有管理权，而我们需要一张表说明每一个销售员与他经理之间的关系，则可以使用自连接实现这个查询
select e1.ename,e1.jod,e2.mgr,e2.ename,e2.job from emp e1,emp e2 where e1.mgr = e2.empno and e1.jod like 'salesman'


--不等连接，和相等连接对应，即两个表连接操作时，限制条件是不等条件，下面查询表emp中每个员工的工资属于哪个等级，并且按岗位排序，此时需要和表salgrade做不等连接。
select e.empno,e.ename,e.job,e.hiredate,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal order by e.job 


--外连接，为了更直观的说明外连接，通过该例子的分析说明外连接的概念是实现方法。在该例中，没有员工的deptno为40的部门，但是在表emp和表dept做等值连接时希望显示表dept中的这个deptno为40的部门信息，使用外连接可以实现
select ename,job,emp.deptno,dept.deptno,dname,loc from emp,dept where emp.deptno(+) = dept.deptno




----*********************************************--简单数据查询，简单查询只涉及到单个表的查询


--连接运算符，顾名思义是起到连接作用的看，在查询输出中为了使得输出结果更易于阅读，更像自然语音的方式。
select ename||'的雇佣日期是：'||hiredate from emp;
select '第'||t4.c1_1||'行第2列的值是：'||t4.c1_2 as "查询结果" from table_4 t4


--distinct,功能是使得输出的结果中对于某列的值不允许重复，也就是该列的输出值应该是独一无二的。
select distict job from emp;


----*********************************************--子查询
--where字句单行子查询
--having字句单行子查询，having字句用来限制分组函数
select job,min(sal),avg(sal),max(sal) from emp group by job having avg(sal)>2000;


--in比较符，in比较符返回子查询中的每一个值，一旦有与该值相等的数据行则输出这些满足条件的数据行
select ename,job,sal,hiredate from emp where sal in(select min(sal) from emp group by job)


--all比较符,例子中>=all的为大于或者等于最大的含义，而<=为小于或等于最小的含义
select ename,job,sal from emp where sal>=all(select avg(sal) from emp group by job)


--any是任何一个的意思，所以<any表示比多行返回结果中的任何一个值都小，所以<any表示小于最大的，而相反>any表示大于最小的
select ename,job,sal from emp where sal>any(select avg(sal) from emp group by job)




----*********************************************--索引
--索引index是oracle的一个对象，一旦建立索引是否使用由oracle决定，索引中存储了特定列的排序数据，实现对表的快速访问。使用索引可以很快查到建立索引时列的值所在的行，而不必对表实现全扫描，所以适当的使用索引可以减少磁盘I/O量。特点:1、对于具有只读特性或较少插入、更新或删除操作的大表通常可以提高查询速度。2、可以对表的一列或多列建立索引。3、建立索引的数量没有限制。4、索引需要磁盘存储，由oracle数据库服务器自动维护。5、索引对用户透明，是否使用索引是oracle决定的。索引作用类似字典，如果要你在一个字典里找一个字，而且是死板的从第一个字开始一个个的找是不是很慢？但是通过字典前面的拼音索引和笔画索引查找就要快很多不是？这个索引就是起到那个效果。