oracle学习笔记
来源：互联网 发布：360软件卸载大师 编辑：程序博客网 时间：2024/06/08 09:46
基础
SQL> --清屏SQL> host clsSQL> --当前用户SQL> show userUSER 为 "SCOTT"SQL> --当前用户的表SQL> select * from tab;SQL> --设置行宽SQL> show linesizelinesize 80SQL> set linesize 150SQL> --设置列宽SQL> col ename format a8SQL> col sal for 9999SQL> --设置分页显示大小SQL> set pagesize 20SQL> --员工表的结构SQL> desc emp--SQL> --查询员工信息：员工号 姓名 月薪SQL> select empno,ename,sal 2 form emp;form emp *第 2 行出现错误: ORA-00923: 未找到要求的 FROM 关键字 SQL> --c命令 changeSQL> 2 2* form empSQL> c /form/from 2* from empSQL> /SQL> ed #编辑上一条sql语句SQL> / #执行上一条sql语句SQL> --连接符 ||SQL> -- concatSQL> select concat('Hello',' World');select concat('Hello',' World') *第 1 行出现错误: ORA-00923: 未找到要求的 FROM 关键字 SQL> select concat('Hello',' World') from dual; #select 必须要有fromSQL> --dual表：伪表SQL> --伪列SQL> select 'Hello'||' World' 字符串 from dual;基本查询
SQL> --distinct 作用于后面所有的列SQL> select distinct deptno,job from emp;过滤和排序
字符串大小写敏感
--SQL> --字符串大小写敏感SQL> --查询名叫KING的员工SQL> select * from emp where ename ='KING';日期格式敏感
----日期格式敏感SQL> --查询入职日期是17-11月-81的员工SQL> select * 2 from emp 3 where hiredate='17-11月-81';--where hiredate='1981-11-17' #报错：文字与格式字符串不匹配 SQL> --修改日期格式 默认：DD-MON-RRSQL> select * from v$nls_parameters; -- session 只在当前会话有效， system全局有效--alter session|system set NLS_DATE_FORMAT='yyyy-mm-dd';--会话已更改。--SQL> select * 2 from emp 3 where hiredate='1981-11-17';between and
--between and: 1. 含有边界 2. 小值在前 大值在后in
--in 在。。。之中模糊查询
--模糊查询 like % _ 查询名字中含有下划线的员工SQL> --转意字符SQL> select * 2 from emp 3* where ename like '%\_%' escape '\';order by
1--order by后面 + 列、表达式、别名、序号SQL> select empno,ename,sal,sal*12 年薪 2 from emp 3* order by 5 descorder by 5 desc *第 3 行出现错误: ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目 2--order by作用于后面所有的列；desc只作用于离他最近的列select * from emp order by deptno desc,sal desc单行函数
定义：函数对当行数据操作返回结果仍为一行
字符函数
1.lower upper initcap
2.substr length lengthb
3.instr lpad rpad
4.replace trim
SQL> select lower('Hello WOrld') 转小写,upper('Hello WOrld') 转大写,initcap('hello world') 首字母大写 2 from dual;转小写 转大写 首字母大写 ----------- ----------- ----------- hello world HELLO WORLD Hello World SQL> --substr(a,b) 从a中，第b位开始取SQL> select substr('Hello World',3) 子串 from dual;子串 --------- llo World SQL> --substr(a,b,c) 从a中，第b位开始取,取c位SQL> select substr('Hello World',3,4) 子串 from dual;子串 ---- llo SQL> --length 字符数 lengthb字节数SQL> select length('Hello World') 字符,lengthb('Hello World') 字节 from dual; 字符 字节 ---------- ---------- 11 11 SQL> ed已写入 file afiedt.buf 1* select length('北京') 字符,lengthb('北京') 字节 from dualSQL> / 字符 字节 ---------- ---------- 2 4 SQL> --instr(a,b) #从1开始SQL> select instr('Hello World','ll') 位置 from dual; 位置 ---------- 3 SQL> --lpad 左填充 rpad 右填充SQL> -- abcd ---> 10位SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;左 右 ---------- ---------- ******abcd abcd****** SQL> --trim 去掉前后指定的字符SQL> select trim('H' from 'Hello WorldH') from dual;TRIM('H'FR ---------- ello World SQL> --replace 替换SQL> select replace('Hello WOrld','l','*') from dual;REPLACE('HE ----------- He**o WOr*d 数值
round
trunc
SQL> --四舍五入SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 2 from dual; 一 二 三 四 五 ------------------------------------------------------- 45.93 45.9 46 50 0 SQL> --截断SQL> ed已写入 file afiedt.buf 1 select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五 2* from dualSQL> / 一 二 三 四 五 ------------------------------------------------------- 45.92 45.9 45 40 0 日期
SQL> --当前时间SQL> select sysdate from dual;SYSDATE ---------- 01-7月 -16 SQL> --格式化时间SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY ------------------- 2016-07-01 12:06:30 SQL> --昨天 今天 明天SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;昨天 今天 明天 --------------------------------------- 30-6月 -16 01-7月 -16 02-7月 -16 SQL> --计算员工的工龄：天 星期 月 年SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 2 from emp;ENAME HIREDATE 天 星期 月 年 ------------------------------------------------------------------- SMITH 17-12月-80 12980.5068 1854.35812 432.683561 35.5630324 ALLEN 20-2月 -81 12915.5068 1845.0724 430.516894 35.3849502 WARD 22-2月 -81 12913.5068 1844.78669 430.450228 35.3794708 JONES 02-4月 -81 12874.5068 1839.21526 429.150228 35.2726214 MARTIN 28-9月 -81 12695.5068 1813.64383 423.183561 34.7822105 BLAKE 01-5月 -81 12845.5068 1835.0724 428.183561 35.1931694 CLARK 09-6月 -81 12806.5068 1829.50098 426.883561 35.0863201 SCOTT 19-4月 -87 10666.5068 1523.78669 355.550228 29.2233064 KING 17-11月-81 12645.5068 1806.50098 421.516894 34.6452242 TURNER 08-9月 -81 12715.5068 1816.50098 423.850228 34.837005 ADAMS 23-5月 -87 10632.5068 1518.92955 354.416894 29.1301557 ENAME HIREDATE 天 星期 月 年 ------------------------------------------------------------------- JAMES 03-12月-81 12629.5068 1804.21526 420.983561 34.6013886 FORD 03-12月-81 12629.5068 1804.21526 420.983561 34.6013886 MILLER 23-1月 -82 12578.5068 1796.92955 419.283561 34.4616625 已选择 14 行。SQL> select sysdate+hiredate from emp;select sysdate+hiredate from emp *第 1 行出现错误: ORA-00975: 不允许日期 + 日期 SQL> --months_between 相差的月数SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二 2 from emp;ENAME HIREDATE 一 二 -------- -------------- ---------- ---------- SMITH 17-12月-80 432.683622 426.50028 ALLEN 20-2月 -81 430.516956 424.403505 WARD 22-2月 -81 430.450289 424.338989 JONES 02-4月 -81 429.150289 422.984151 MARTIN 28-9月 -81 423.183622 417.145441 BLAKE 01-5月 -81 428.183622 422 CLARK 09-6月 -81 426.883622 420.758344 SCOTT 19-4月 -87 355.550289 350.435764 KING 17-11月-81 421.516956 415.50028 TURNER 08-9月 -81 423.850289 417.790602 ADAMS 23-5月 -87 354.416956 349.306731 ENAME HIREDATE 一 二 -------- -------------- ---------- ---------- JAMES 03-12月-81 420.983622 414.951893 FORD 03-12月-81 420.983622 414.951893 MILLER 23-1月 -82 419.283622 413.306731 已选择 14 行。SQL> host clsSQL> --add_monthsSQL> --53个月后SQL> select add_months(sysdate,53) from dual;ADD_MONTHS(SYS -------------- 01-12月-20 SQL> --last_daySQL> select last_day(sysdate) from dual;LAST_DAY(SYSDA -------------- 31-7月 -16 SQL> --next_daySQL> --下一个星期五SQL> select next_day(sysdate,'星期五') from dual;NEXT_DAY(SYSDA -------------- 08-7月 -16 SQL> select next_day(sysdate,'星期六') from dual;NEXT_DAY(SYSDA -------------- 02-7月 -16 SQL> /*SQL> next_day的应用：每个星期一自动备份表中的数据SQL> 1. 分布式数据库SQL> 2. 触发器 快照SQL> */SQL> select round(sysdate,'month'),round(sysdate,'year') from dual;ROUND(SYSDATE, ROUND(SYSDATE, -------------- -------------- 01-7月 -16 01-1月 -17 转换
to_char
SQL> --2016-07-01 12:26:12今天是星期五SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss今天是day') from dual;select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss今天是day') from dual *第 1 行出现错误: ORA-01821: 日期格式无法识别 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI ---------------------------------- 2016-07-01 12:27:22今天是星期五 SQL> --查询员工薪水：两位小数，千位符，本地货币代码SQL> select to_char(sal,'L9,999.99') from emp;TO_CHAR(SAL,'L9,999 ------------------- ￥800.00 ￥1,600.00 ￥1,250.00 ￥2,975.00 ￥1,250.00 ￥2,850.00 ￥2,450.00 ￥3,000.00 ￥5,000.00 ￥1,500.00 ￥1,100.00 TO_CHAR(SAL,'L9,999 ------------------- ￥950.00 ￥3,000.00 ￥1,300.00 已选择 14 行。SQL> host cls通用
nvl(a,b)
nvl2(a,b,c)
nullif(a,b)
coalesce
SQL> --通用函数SQL> --nvl2(a,b,c) 当a=null的时候，返回c；否则返回bSQL> select sal*12+nvl2(comm,comm,0) from emp;SAL*12+NVL2(COMM,COMM,0) ------------------------ 9600 19500 15500 35700 16400 34200 29400 36000 60000 18000 13200 SAL*12+NVL2(COMM,COMM,0) ------------------------ 11400 36000 15600 已选择 14 行。SQL> --nullif(a,b) 当a=b的时候，返回null；否则返回aSQL> select nullif('abc','abc') 值 from dual;值 --- SQL> select nullif('abc','abcd') 值 from dual;值 --- abc SQL> --coalesce 从左到右 找到第一个不为null的值SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp; COMM SAL 第一个不为null的值 ---------- ----- ------------------ 800 800 300 1600 300 500 1250 500 2975 2975 1400 1250 1400 2850 2850 2450 2450 3000 3000 5000 5000 0 1500 0 1100 1100 COMM SAL 第一个不为null的值 ---------- ----- ------------------ 950 950 3000 3000 1300 1300 已选择 14 行。SQL> host cls条件表达式
case when ... then ... when ... then ... else ...enddecode()SQL> --涨工资，总裁1000 经理800 其他400SQL> select empno,ename,job,sal 涨前, 2 case job when 'PRESIDENT' then sal+1000 3 when 'MANAGER' then sal+800 4 else sal+400 5 end 涨后 6 from emp; EMPNO ENAME JOB 涨前 涨后 ------------------------------------------------------ 7369 SMITH CLERK 800 1200 7499 ALLEN SALESMAN 1600 2000 7521 WARD SALESMAN 1250 1650 7566 JONES MANAGER 2975 3775 7654 MARTIN SALESMAN 1250 1650 7698 BLAKE MANAGER 2850 3650 7782 CLARK MANAGER 2450 3250 7788 SCOTT ANALYST 3000 3400 7839 KING PRESIDENT 5000 6000 7844 TURNER SALESMAN 1500 1900 7876 ADAMS CLERK 1100 1500 EMPNO ENAME JOB 涨前 涨后 ------------------------------------------------------ 7900 JAMES CLERK 950 1350 7902 FORD ANALYST 3000 3400 7934 MILLER CLERK 1300 1700 已选择 14 行。SQL> select empno,ename,job,sal 涨前, 2 decode(job,'PRESIDENT',sal+1000, 3 'MANAGER',sal+800, 4 sal+400) 涨后 5 from emp; EMPNO ENAME JOB 涨前 涨后 ---------------------------------------------------- 7369 SMITH CLERK 800 1200 7499 ALLEN SALESMAN 1600 2000 7521 WARD SALESMAN 1250 1650 7566 JONES MANAGER 2975 3775 7654 MARTIN SALESMAN 1250 1650 7698 BLAKE MANAGER 2850 3650 7782 CLARK MANAGER 2450 3250 7788 SCOTT ANALYST 3000 3400 7839 KING PRESIDENT 5000 6000 7844 TURNER SALESMAN 1500 1900 7876 ADAMS CLERK 1100 1500 EMPNO ENAME JOB 涨前 涨后 ---------------------------------------------------- 7900 JAMES CLERK 950 1350 7902 FORD ANALYST 3000 3400 7934 MILLER CLERK 1300 1700 已选择 14 行。多行函数
对多行数据操作返回一行结果
sum avg max min
分组数据
SQL> --group by :先按照第一个列分组；再按照第二列分组，以此类推--where和having的区别：where后面不能使用多行函数SQL> /*SQL> group by的增强SQL> select deptno,job,sum(sal) from emp group by deptno,jobSQL> +SQL> select deptno,sum(sal) from emp group by deptnoSQL> +SQL> select sum(sal) from empSQL> SQL> ==SQL> SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);SQL> SQL> SQL> 抽象SQL> group by rollup(a,b)SQL> =SQL> group by a,bSQL> +SQL> group by aSQL> +SQL> 没有分组SQL> SQL> */SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); DEPTNO JOB SUM(SAL) -------------------------------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) -------------------------------- 30 9400 29025 已选择 13 行。显示样式设置
SQL> break on deptno skip 2 # break on nullSQL> / DEPTNO JOB SUM(SAL) -------------------------------- 10 CLERK 1300 MANAGER 2450 PRESIDENT 5000 8750 20 CLERK 1900 ANALYST 6000 MANAGER 2975 10875 DEPTNO JOB SUM(SAL) -------------------------------- 30 CLERK 950 MANAGER 2850 SALESMAN 5600 9400 29025 已选择 13 行。SQL中的null值问题
1.包含null的表达式都为null
2.null永远！=null
SQL> select * 2 from emp 3 where comm=null;未选定行SQL> select * 2 from emp 3 where comm is null; SQL> --已选择 10 行。3.如果集合中含有null，不能使用not in；但可以使用in
--原因where A not in (a,b,null)#等价:A!=a and A!=b and A!=null #A!=null永远为假4.null的排序 [null最大]
select * from emp order by comm; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------------------------- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 select * from emp order by comm desc EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------------------------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7902 FORD ANALYST 7566 03-12月-81 3000 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 select * 2 from emp 3 order by comm desc 4* nulls last EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------------------------- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7369 SMITH CLERK 7902 17-12月-80 800 20 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 5.组函数（多行函数）自动滤空;嵌套滤空函数来屏蔽他的滤空功能
SQL> select count(*),count(nvl(comm,0)) from emp; COUNT(*) COUNT(NVL(COMM,0)) ---------- ------------------ 14 14 SQL优化原则
1.尽量使用列名，不是用 “*”号
2.where解析的顺序： 右–》 左
3.尽量使用where
阅读全文
0 0
- Oracle学习笔记--Oracle入门
- Oracle学习笔记
- 学习ORACLE笔记
- Oracle 学习笔记
- ORACLE学习笔记
- Oracle 认证学习笔记
- Oracle学习笔记
- oracle学习笔记[转]
- Oracle学习笔记
- 转(oracle学习笔记)
- Oracle学习笔记一
- ORACLE学习笔记
- Oracle游标学习笔记
- Oracle 认证学习笔记
- oracle 学习笔记
- oracle学习笔记(一)
- oracle学习笔记(二)
- oracle学习笔记(三)
- 洛谷：P1046 陶陶摘苹果 题解
- MD5加密算法流程
- 街区最短路径问题
- 结构、联合、位字段
- Godsend CodeForces
- oracle学习笔记
- 安卓自动化之uiautomator(Python篇)三 Uiobject
- 悬浮上去有效果的照片墙
- java:String、StringBuffer与StringBuilder
- IOS开发
- 用python中的list实现用户管理系统（面向对象 CRUD）
- 调和级数（harmony）
- 读书笔记《Inside the C++ Object Model》：关于对象
- Java之IO（一） FileInputStream、FileOutputStream、BufferedInputStream、BufferedOutputStream
