1 单行函数 1.1 字符函数 字符函数：输入是字符，输出是字符或者number upper：字符大写显示 SQL> select upper('abc') from dual;UPP---AB
1 单行函数
1.1 字符函数
字符函数：输入是字符，输出是字符或者number
upper：字符大写显示
SQL> select upper('abc') from dual;UPP---ABCSQL> select upper('aA,%c') from dual;UPPER-----AA,%Clower：字符小写显示
SQL> select lower('ABC,a/') from dual;LOWER(------abc,a/initcap：首字母大写
SQL> select initcap('abc,344%acc') from dual;INITCAP('AB-----------Abc,344%Acc注意：特殊字符后定也会被认为是首字母，也会大写concat：字符链接显示
SQL> select concat('aa','bb') from dual;CONC----aabbSQL> select concat('aa',234) from dual;CONCA-----aa234length：统计字符长度
SQL> select length('slfjsdklfjd') from dual 2 ;LENGTH('SLFJSDKLFJD')--------------------- 11# 按照字符统计lengthb：统计字符长度
SQL> select lengthb('adbc') from dual;LENGTHB('ADBC')--------------- 4#按照字节统计lengthc：统计字符长度。asic码统计
GBK 字符编码中一个汉字字符两个字节，UTF-8 字符编码中一个汉字占3个字节
在英文中length，lengthb，lengthc 统计定结果是一样定，在中文中就不一样的。
这个在工作环境中只来比较length和lengthb是否一致，如果不一致，说明里面包含中文。
substr：字符截取
SQL> select substr('abcdef',1,3) from dual;SUB---abc#1 表示从第一个开始截取，3表示截取3个字符。如果3不写表示截取全部SQL> select substr('abcde',-3,2) from dual;SU--cd# 截取定开始位置可以从后往前定位，但是还是向后截取SQL> select ename from emp where substr(ename,-1,1) = 'T';ENAME----------SCOTT# 在实际工作中使用截取能替代like 的部分功能，效率比like高很多instr：显示字符在字符串中定索引位置
SQL> select instr('abcde','c') from dual;INSTR('ABCDE','C')------------------ 3# c 在第三个。默认是从第一个开始找，第一次出现的。SQL> select instr('abcdea','a',2) from dual;INSTR('ABCDEA','A',2)--------------------- 6# oracle11g 可以制定找第几次，这里就是找到列a第二次出现在6的位置SQL> select instr('abcdea','a',2,2) from dual;INSTR('ABCDEA','A',2,2)----------------------- 0# 从第二个字符开始找，找第二次出现的atrim（）：截断字符，截断两端
SQL> select trim('a' from 'aaaabddea') from dual;TRIM----bdde# 只要a字符中间的SQL> select trim(trailing 'a' from 'aabcdefa') from dual;TRIM(TR-------aabcdef# 只截断右边SQL> select trim(leading 'a' from 'aabcdefa') from dual;TRIM(L------bcdefa# 只截左边#-------------------- 上面这些功能只有在oracle10G以后 有ltrim：oracle11g的高级截断，全部截断
SQL> select ltrim('abababaacbda','ab') from dual;LTRI----cbdalpad：左填充函数
SQL> select lpad('abc',5,'*') from dual;LPAD(-----**abc# 默认是用空格填充rpad：右填充函数
SQL> select rpad('abcd',10,'-') from dual;RPAD('ABCD----------abcd------replace：替换函数
SQL> select replace('abcd','c','*') from dual;REPL----ab*d
1.2 数值函数
abs：绝对值
SQL> select abs(1.2) from dual; ABS(1.2)---------- 1.2SQL> select abs(-12) from dual; ABS(-12)---------- 12round：保留几位小数
SQL> select round(1.2345,3) from dual;ROUND(1.2345,3)--------------- 1.235SQL> select round(123.4567,-2) from dual;ROUND(123.4567,-2)------------------ 100#小数点向前向后都可以SQL> select trunc(234.567,2) from dual;TRUNC(234.567,2)---------------- 234.56# round 和trunc 的区别，round会四舍入，trunc不会SQL> select ceil(12.13) from dual;CEIL(12.13)----------- 13# ceil 直接向上去整数SQL> select floor(12.57) from dual;FLOOR(12.57)------------ 12# floor 向下取整mod：取余
SQL> select mod(11,3) from dual; MOD(11,3)---------- 21.3 日期函数
sysdate：显示当前日期
SQL> select sysdate from dual;SYSDATE---------22-NOV-16to_char():转换为字符函数
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;TO_CHAR(SY----------2016-11-22 1* select to_char(11,'XXXX') from dualSQL> /TO_CH----- B# 十进制转换为16进制 1* select to_number(10,'XXXX') from dualSQL> /TO_NUMBER(10,'XXXX')-------------------- 16# 16进制准换为10进制to_date() :转换为日期
SQL> select to_date('2016-11-22','yyyy-mm-dd') +1 from dual;TO_DATE('---------23-NOV-16add_month():加几个月
SQL> select add_months(sysdate,1) from dual;ADD_MONTH---------22-DEC-16 1* select add_months(sysdate,-1) from dualSQL> /ADD_MONTH---------22-OCT-16next_day():下一个星期几是那一天
SQL> select next_day(sysdate,1) from dual;NEXT_DAY(---------27-NOV-16 1* select next_day(sysdate,2) from dualSQL> /NEXT_DAY(---------28-NOV-16SQL> select last_day(sysdate) from dual;LAST_DAY(---------30-NOV-16# 当年定最后一天SQL> select trunc(sysdate,'yyyy') from dual;TRUNC(SYS---------01-JAN-16# 当年第一天SQL> select trunc(sysdate,'mm') from dual;TRUNC(SYS---------01-NOV-16# 当月第一天
SQL> select current_date from dual;CURRENT_D---------22-NOV-16# 查看时区month_between():记录当前多少个月
SQL> select months_between(sysdate,to_date('2008-05-12','yyyy-mm-dd')) from dual;MONTHS_BETWEEN(SYSDATE,TO_DATE('2008-05-12','YYYY-MM-DD'))---------------------------------------------------------- 102.343878# 512到现在过去列多少个月NVL2：
SQL> select nvl2('a',1,2) from dual;NVL2('A',1,2)------------- 1SQL> select nvl2('',1,2) from dual;NVL2('',1,2)------------ 2# 如果空，返回第一个值，否则返回第二个值nullif:
SQL> select nullif('a','b') from dual;N-aSQL> select nullif('a','a') from dual;N-# 如果两个值相等，则返回空，否则返回第一个值decpde：
SQL> select deptno,decode(deptno,10,1,20,2,3) cc from emp; DEPTNO CC---------- ---------- 20 2 30 3 30 3 20 2 30 3 30 3 10 1 20 2 10 1 30 3 20 2 30 3 20 2 10 1# 如果deptno 是10则返回10，如果是20则返回2，否则返回3case：
SQL> l 1 select ename,sal,deptno, 2 case deptno 3 when 10 then sal*1.1 4 when 20 then sal*1.5 5 else 6 sal*2 7* end from empENAME SAL DEPTNO TMP---------- ---------- ---------- ----------SMITH 800 20 1200ALLEN 1600 30 3200WARD 1250 30 2500JONES 2975 20 4462.5MARTIN 1250 30 2500BLAKE 2850 30 5700CLARK 2450 10 2695SCOTT 3000 20 4500KING 5000 10 5500TURNER 1500 30 3000ADAMS 1100 20 1650JAMES 950 30 1900FORD 3000 20 4500MILLER 1300 10 1430# 上面定是等值链接，下面定可以用case 不等值链接SQL> select ename,sal, 2 case 3 when sal < 2000 then sal*1.5 4 when sal >=2000 and sal <3000 then sal*1.8 5 else 6 sal*2 7 end as tmp from emp;ENAME SAL TMP---------- ---------- ----------SMITH 800 1200ALLEN 1600 2400WARD 1250 1875JONES 2975 5355MARTIN 1250 1875BLAKE 2850 5130CLARK 2450 4410SCOTT 3000 6000KING 5000 10000TURNER 1500 2250ADAMS 1100 1650JAMES 950 1425FORD 3000 6000MILLER 1300 19501.4 正则表达式
SQL> select ename from emp where regexp_like(ename,'^s','i');ENAME----------SMITHSCOTT# 正则表达式里面i 忽略大小写，c 区分大小写 1* select ename from emp where regexp_like(ename,'^s','c')SQL> /no rows selectedSQL> select ename from emp where regexp_like(ename,'?c','i');ENAME----------CLARKSCOTT# 名字当中包含定有c字母的，且不区分大小写 1* select ename,regexp_instr(ename,'S') from empSQL> /ENAME REGEXP_INSTR(ENAME,'S')---------- -----------------------SMITH 1ALLEN 0WARD 0JONES 5# 搜索S 在字字符从中出现定位置 1* select ename ,regexp_count(ename,'T') from empSQL> /ENAME REGEXP_COUNT(ENAME,'T')---------- -----------------------SMITH 1ALLEN 0WARD 0JONES 0MARTIN 1BLAKE 0CLARK 0SCOTT 2# 统计字符出现定次数2 多行函数
2.1 count() 求数量
SQL> select count(*),count(1),count(comm) from emp; COUNT(*) COUNT(1) COUNT(COMM)---------- ---------- ----------- 14 14 4# count(*) 不处理空值，count（comm） 会不记空值2.2 max() 求最大
1* select max(comm) from empSQL> / MAX(COMM)---------- 14002.3 min() 求最小
SQL> select min(sal) from emp; MIN(SAL)---------- 8002.4 avg() 求平均
SQL> select avg(sal) from emp; AVG(SAL)----------2073.214292.5 sum() 求和
SQL> select sum(comm) from emp; SUM(COMM)---------- 22002.6 group() 分组函数
SQL> select deptno,max(sal) from emp group by deptno; DEPTNO MAX(SAL)---------- ---------- 30 2850 20 3000 10 5000SQL> select job,max(sal) from emp group by job;JOB MAX(SAL)--------- ----------CLERK 1300SALESMAN 1600PRESIDENT 5000MANAGER 2975ANALYST 30002.7 having() 分组后筛选
SQL> select job,max(sal) from emp group by job having max(sal) >2000;JOB MAX(SAL)--------- ----------PRESIDENT 5000MANAGER 2975ANALYST 30002.8 rollup() 滚动分组
SQL> select deptno,max(sal) from emp group by rollup(deptno); DEPTNO MAX(SAL)---------- ---------- 10 5000 20 3000 30 2850 5000# 滚动分组就是对rollup() 里面定每一个条件都分组一次，上面是对部门分组求最高薪水，并对所有员工求了一次最大薪水。 1* select deptno,job,max(sal) from emp group by rollup(deptno,job)SQL> / DEPTNO JOB MAX(SAL)---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 5000 20 CLERK 1100 20 ANALYST 3000 20 MANAGER 2975 20 3000 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1600 30 2850 5000# 这个有两个条件，先对部门里面每一个job 最大薪水进行分组统计，最后在全部统计一次最高薪水2.9 cube() :交叉分组
SQL> select deptno,job,max(sal) from emp group by cube(deptno,job); DEPTNO JOB MAX(SAL)---------- --------- ---------- 5000 CLERK 1300 ANALYST 3000 MANAGER 2975 SALESMAN 1600 PRESIDENT 5000 10 5000 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 3000 20 CLERK 1100 20 ANALYST 3000 20 MANAGER 2975 30 2850 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 160018 rows selected.# 这个是先对所有求最大，在对deptno求最大，在对deptno 下的job求最大2.10 grouping sets 合并分组
1* select deptno,job,max(sal) from emp group by grouping sets(deptno,job)SQL> / DEPTNO JOB MAX(SAL)---------- --------- ---------- 30 2850 20 3000 10 5000 CLERK 1300 SALESMAN 1600 PRESIDENT 5000 MANAGER 2975 ANALYST 3000# 相当与对两个条件分别进行分组并把结果合并在一起 1* select deptno,job,max(sal) ,grouping(deptno),grouping(job) from emp group by grouping sets(deptno,job)SQL> / DEPTNO JOB MAX(SAL) GROUPING(DEPTNO) GROUPING(JOB)---------- --------- ---------- ---------------- ------------- 30 2850 0 1 20 3000 0 1 10 5000 0 1 CLERK 1300 1 0 SALESMAN 1600 1 0 PRESIDENT 5000 1 0 MANAGER 2975 1 0 ANALYST 3000 1 0# grouping 能检验字段是否参与分组2.11 分析函数和开窗
row_number() over()
-- 组内排序select a.*,row_number() over(partition by a.deptno order by a.sal desc ) num from emp a;
rank() over():
-- 组内排序，处理并列select a.*,rank() over(partition by a.deptno order by a.sal desc ) num from emp a;
dense_rank() over():
-- 组内排序，处理并列，不跳过后面的排名select a.*,dense_rank() over(partition by a.deptno order by a.sal desc) num from emp a;
-- 开窗select a.*,avg(a.sal) over() from emp a;
3多表查询
3.1 自然连接natural join
-- 自然连接-- 自然连接的两个表字段名称相同，属性相同的连个字段会自动连接select * from emp natural join dept; 
3.2 join using 等值连接
-- 等值连接-- join using 要求名称相同，属性可以不同select * from emp join dept using (deptno);
3.3 join on
-- join on 字段名称可以不同，属性也可以不同.select * from emp a join dept b on a.deptno = b.deptno;
3.4 union和union all 求合集
-- union 和 union all -- union 剔除重复并排序select 1 from dualunionselect 2 from dualunionselect 1 from dual;
-- union all 不剔重，且不排序 select 1 from dualunion allselect 2 from dualunion allselect 1 from dual;
3.5 intersect 求交集
-- intersect 求交集select * from emp a where a.ename in ('SMITH','JONES')intersectselect * from emp a where a.ename in ('SMITH')
3.6 minus 求差集
-- minus 求差集select * from emp a where a.ename in ('SMITH','JONES')minusselect * from emp a where a.ename in ('SMITH');
3.7 时间戳
-- 时间戳select systimestamp from dual;
注意：表用delete 删除之后数据所在的子块还在，只是打上一个标记，还是可以通过日志找回来，如果想要彻底删除，则 alter table_name <> shrink space 删除表之后 空间的回收
融合语句：mergo into ，存在的做更新，不存在的做插入
merge into table_name1 using table_name2 on 条件
when matchd then update set 列名=列名
when not matchd then insert values (列值)
SQL> l 1 merge into emp1 2 using emp 3 on (emp1.empno = emp.empno) 4 when matched then update set sal=sal/2 5* when note matched then insert values (emp.ename,emp.sal,emp.deptno,emp.empno)SQL> select * from emp1;ENAME SAL DEPTNO EMPNO---------- ---------- ---------- ----------ALLEN 800 30 7499WARD 625 30 7521MARTIN 625 30 7654BLAKE 1425 30 7698TURNER 750 30 7844JAMES 475 30 7900KING 5000 10 7839CLARK 2450 10 7782SCOTT 3000 20 7788JONES 2975 20 7566MILLER 1300 10 7934FORD 3000 20 7902SMITH 800 20 7369ADAMS 1100 20 7876# emp1 表里面有的薪水减半，没有的插入4 事务控制语言TCL
事务是如何产生的：DML，DDL，DCL
事务结束：commit，ddl，exit，conn，rollback
事务主要针对DML
commit 事务完成
rollback 只能在当前事务中回滚，会滚到上一次commit的位置。
savepoint s1 设置保存点，以后可以回滚到当前位置，而不是上一次commit的位置
rollback to s1
用户做DML 的时候会申请锁死，有行级锁，有表级锁，update 是表锁 等事务完成之后才解锁。
5 系统权限
select * from system_privilege_map 查看系统权限
查看当前用户有那些权限：
select * from dba_sys_privs；
select * from user_sys_privs;
SQL> select * from user_sys_privs;USERNAME PRIVILEGE ADM------------------------------ ---------------------------------------- ---SCOTT UNLIMITED TABLESPACE NO# 只有一个，使用表空间权限查看当前会话下的权限
SQL> select * from session_privs;PRIVILEGE----------------------------------------CREATE SESSION # 创建会话UNLIMITED TABLESPACE # 使用表空间CREATE TABLE # 建表CREATE CLUSTER # 创建游标CREATE SEQUENCE # 创建序列CREATE PROCEDURE # 创建CREATE TRIGGERCREATE TYPECREATE OPERATORCREATE INDEXTYPE10 rows selected.# 当前会话下的所有系统权限schema ------ username
系统权限的授权
grant 系统权限1，系统权限2，…… to 用户名
SQL> create user test identified by test;SQL> conn test/testERROR:ORA-01045: user TEST lacks CREATE SESSION privilege; logon deniedSQL> grant create session to test;Grant succeeded.SQL> conn test/testConnected.# 授权以后已经可以链接了create table tmp1 d as select 1 from dual *ERROR at line 1:ORA-00922: missing or invalid optiongrant 系统权限,…… to 用户名 with admin option 授予系统权限级联
注意：当有级联权限的用户的权限被收回，那他授权其他用户的权限是不会被回收的。
权限回收：
revoke 系统权限 from username
6 对象权限
用户要查看其他用户下的对象，则需要拥有对象权限
SQL> select * from table_privilege_map; PRIVILEGE NAME---------- ---------------------------------------- 0 ALTER # 修改 1 AUDIT # 审计 2 COMMENT # 注释 3 DELETE 4 GRANT 5 INDEX 6 INSERT 7 LOCK 8 RENAME 9 SELECT 10 UPDATE PRIVILEGE NAME---------- ---------------------------------------- 11 REFERENCES # 重定义 12 EXECUTE #执行 16 CREATE 17 READ 18 WRITE 20 ENQUEUE 21 DEQUEUE 22 UNDER 23 ON COMMIT REFRESH 24 QUERY REWRITE 26 DEBUG PRIVILEGE NAME---------- ---------------------------------------- 27 FLASHBACK # 闪回 28 MERGE VIEW 29 USE 30 FLASHBACK ARCHIVE26 rows selected.# 查看当前系统有那些对象权限select * from dba_tab_privs; 表示dba 当前有那些对象权限SQL> select * from user_tab_privs;no rows selected# 普通用户下没有任何对象权限授予对象权限：
grant 对象权限 on 方案名.对象名 to 用户名
SQL> grant select on test.tmp1 to scott ;Grant succeeded.# 下面可以在scott 用户下面查看test 用户下的表tmp1SQL> select * from test.tmp1; D---------- 1SQL> show user;USER is "SCOTT"# scott 用户已经可以访问test用户的表tmp1SQL> insert into test.tmp1 select 2 from dual;insert into test.tmp1 select 2 from dual *ERROR at line 1:ORA-01031: insufficient privileges# 但是不能修改别的用户的表，需要进行授权SQL> grant update(p) on test.tmp1 to scott;Grant succeeded.# 重新授权了对单个列定修改权限SQL> update test.tmp1 a set a.p = 0 where a.d = 1;1 row updated.SQL> select * from test.tmp1; D P---------- ---------- 1 0# 授权列修改权限的列可以被修改，SQL> update test.tmp a set a.d = 0 where a.d = 1;update test.tmp a set a.d = 0 where a.d = 1 *ERROR at line 1:ORA-00942: table or view does not exist# 没有授权被修改的列，不可以被修改
1* select * from user_tab_privsSQL> /GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---SCOTT TEST TMP1 TEST SELECT NO NO# scott 有用户test 用户下表tmp1 的 select 权限SQL> select * from user_col_privs;GRANTEE OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---SCOTT TEST TMP1 P TEST UPDATE NO# scott 有用户test 的表tmp1 的p列的update 权限对象也有级联权限
grant 对象权限 on 方案名.对象名 to 用户名 with grant option
SQL> grant select on test.tmp1 to scott with grant option;Grant succeeded.# 其他用户对用户test用户下表tmp1 的select 权限授予了用户scott，并级联GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---HIE---SCOTT TEST TMP1 TEST SELECT YESNO# 上面的yes 就是有权限级联
7 角色权限
是对象权限和系统权限的打包
7.1 创建角色
create role 角色的名字 identified by 密码。密码可以不用授权。
查看当前系统有那些角色
select * from role_sys_privs
1* select distinct role from role_sys_privsSQL> /ROLE------------------------------EXP_FULL_DATABASEAQ_ADMINISTRATOR_ROLEDBAOEM_ADVISORRECOVERY_CATALOG_OWNERSCHEDULER_ADMINOLAP_USERRESOURCEIMP_FULL_DATABASE # 导入数据库OWB$CLIENT # 客户端操作DATAPUMP_EXP_FULL_DATABASEROLE------------------------------CONNECT # 链接角色OLAP_DBA # 数据仓促挂历角色JAVADEBUGPRIVDATAPUMP_IMP_FULL_DATABASEOEM_MONITOR # OEM 监控角色MGMT_USERLOGSTDBY_ADMINISTRATOR18 rows selected.查看角色下面有那些权限
SQL> select distinct GRANTED_ROLE from dba_role_privs where grantee = 'DBA';# 查看dba 下有那些角色SQL> select * from role_sys_privs where role = 'RESOURCE';ROLE PRIVILEGE ADM------------------------------ ---------------------------------------- ---RESOURCE CREATE SEQUENCE NORESOURCE CREATE TRIGGER NORESOURCE CREATE CLUSTER NORESOURCE CREATE PROCEDURE NORESOURCE CREATE TYPE NORESOURCE CREATE OPERATOR NORESOURCE CREATE TABLE NORESOURCE CREATE INDEXTYPE NO# 看到resource 角色下面有8个系统权限查看当前用户有那些角色：
SQL> conn scott/tigerConnected.SQL> select * from user_role_privs;USERNAME GRANTED_ROLE ADM DEF OS_------------------------------ ------------------------------ --- --- ---SCOTT CONNECT NO YES NOSCOTT RESOURCE NO YES NOSQL> select * from role_sys_privs;ROLE PRIVILEGE ADM------------------------------ ---------------------------------------- ---RESOURCE CREATE TRIGGER NORESOURCE CREATE SEQUENCE NORESOURCE CREATE TYPE NORESOURCE CREATE PROCEDURE NORESOURCE CREATE CLUSTER NOCONNECT CREATE SESSION NORESOURCE CREATE OPERATOR NORESOURCE CREATE INDEXTYPE NORESOURCE CREATE TABLE NO# 查看当前scott 用户下所有的角色系统权限SQL> select * from role_tab_privs;no rows selected# scott用户下没有角色对象权限SQL> select * from role_role_privs;no rows selected# scott 用户没有角色下的角色系统权限授予角色
grant 系统权限 to 角色名称
grant 对象权限 on 方案名.对象名 to 叫
SQL> create role test_role;Role created.# 创建角色SQL> grant create session,create table to test_role;Grant succeeded.# 角色授予系统权限SQL> grant select,insert,update on test.tmp1 to test_role;Grant succeeded.# 角色授权对象权限SQL> select role,privilege from role_sys_privs where role = 'TEST_ROLE';ROLE PRIVILEGE------------------------------ ----------------------------------------TEST_ROLE CREATE SESSIONTEST_ROLE CREATE TABLE# 查看角色下的系统权限SQL> select role,owner,table_name,column_name,privilege from role_tab_privs where role = 'TEST_ROLE';ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------TEST_ROLE TEST TMP1 SELECTTEST_ROLE TEST TMP1 UPDATETEST_ROLE TEST TMP1 INSERT# 查看角色下的对象权限对象权限授予角色
grant 对象权限 on 方案名.对象名 to 角色
