Oracle个人笔记

Oracle个人笔记一、创建表create table USER(UID VARCHAR2(16) not null,USERNAME VARCHAR2(32),PASSWORD VARCHAR2(32),AGE NUMBER(12,3))主键设置：Alter table USER Add Constraint PrimaryKey Primary Key (UID)二、基本查询命令解释cmd>sqlplus scott/tiger 开启Oracle数据库scott,密码：tiger SQL>spool c:\1.txt 把cmd操作保存到1.txt文件里SQL>show user 显示当前用户SQL>select * from tab; 查询当前用户下的表SQL> host cls 清屏SQL> desc emp 查询员工表结构SQL> select * from emp; 查询员工的所有信息SQL> select empno,ename,job from emp; 查询员工某些列的信息SQL> show linesize 显示cmd窗口的显示行宽SQL> set linesize 150 设置cmd窗口的显示行宽SQL> col ename for a8 设置某一列的列宽为8个字符串长SQL> col sal for 9999 设置某一列的列宽4位数字长SQL> /再次执行上一次sql语句SQL> select ename,job,sal,sal*122 form emp;第 2 行出现错误:ORA-00923: 未找到要求的FROM 关键字SQL> 22* form empSQL> c /form/from2* from empSQL> /c命令( change )如果sql语句很长再次修改很麻烦，所以可以通过定位到第2行修改某一行就行了SQL> select distinct deptno from emp;去除重复，distinct作用于后面所有的列SQL> select 'Hello'||' World' from dual; || 为连接字符串，输出为：HelloWorld SQL>set timing on；开始计时SQL>set timing off 结束计时SQL>@c:\1.sql oracl执行c盘下1.sql脚本mysql执行脚本source c:\1.sqlSQL>--行注释/*块注释*/ 在Oracle中-- 和/**/就是注释Cmd>lsnrctl status 查看监听器Cmd>tnsping 服务名查看该服务名是否配置正确1.1、Rownum使用案例Rownum必须嵌套使用select u.*from(select rownum a,t.*from uap_sb t)u where a> 11.2、Insert into批量插入数据创建与student表结构一样的表stu123,并导入student的所有表数据create table stu123as(select*from STUDENT);已经创建结构相同的表stu123导入全部数据insert into stu123(select*from STUDENT);提交事务1.3、SQL优化1.尽量使用列名代替* ( Oracle9i之后,一样)2.理论上,尽量使用多表查询3.where 解析执行顺序右--> 左4.尽量不要使用集合运算1.4、SQL中的null值1.包含null值的表达式都为null2.null永远!= null，要判断是否为null用is null、is not null，null进行的算术运算=、!=后结果都为false3.组函数会自动滤空null1.5、Oracle提供了一个默认数据库dual方便用于查询没有指定哪个数据库用例：SQL> select 'Hello'||' World' 列from dual;测试连接数据库时，因为select的格式必须为select *** from 表名，为了满足这个条件，所以用dual1.6、创建用户与授权1，创建用户：创建admin用户，密码为admincreate user admin identified by admin;2，授权：给admin用户授予数据库最高权限dbagrant dba to admin;3，oracle用户查看当前自己的权限和角色select*from user_tab_privs;select*from user_role_privs;4，sys用户查看所有用户的权限和角色select*from dba_tab_privs;select*from dba_role_privs;二、过滤和排序2.1、where的使用，where 解析顺序右--> 左SQL> select * from emp where empno=7902 or empno=7900;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- --------- ---------- -------------- ----- ---------- ----------7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 2.2、sysdate关键字，表示查询当前时间查询当前时间SQL> select sysdate from dual;修改日期的格式SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';获取年月日时分秒：to_char(a,b)把a转换成字符串bSQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')现在时间from dual;现在时间-------------------2014-02-11 15:21:262.3、通过查询Oracle系统参数设置SQL> select * from v$nls_parameters;PARAMETER V ALUE------------------------- ----------NLS_CURRENCY ￥NLS_TIME_FORMAT HH . MI . SSXFF AM2.4、[ not ]between ** and **使用，表示[ 不]在多少到多少之间select * from emp where sal between 2000 and 10002.5、[ not ] in (a,b)使用，表示是a或是b [ 不是a和b ]注意：not in (a,b) 中如果a或b为null表示判断条件都为null，返回结果为空SQL> select * from emp where empno not in (7369,7566,7788,null);未选定行SQL> select * from emp where deptno not in (10,20)7698 BLAKE MANAGER 7839 01-5月-81 2850 302.6、模糊查询like: % _select * from emp where ename like '%S%';7369 S MITH CLERK 7902 17-12月-80 4000 202.7、排序order by，默认升序以sal升序select * from emp order by sal;以deptno默认升序，以sal降序select * from emp order by deptno,sal desc以deptno降序，以sal降序select * from emp order by deptno desc , sal desc2.8、a命令追加(append)SQL>select * from emp order by sal;SQL>a desc //注意中间空两个字符，否则拼接不正常1* select * from emp order by sal desc;三、单行函数3.1、转换字符串大小写lower(a)、upper(b)、initcap(a)1、lower(a)：把字符串a全都转成小写2、upper(a)：把字符串a全都转成大写3、initcap(a)：把字符串a首字母转成大写SQL>select lower('Hello World') 转小写, upper('Hello World') 转大写,initcap('hello world') 首字母大写from dual;转小写转大写首字母大写hello world HELLO WORLD Hello World3.2、substr(a,b) 从a中，第b位开始取取右边所有字符，下标从1开始SQL> select substr('Hello World',3) from dual;llo World3.3、substr(a,b,c) 从a中，第b位开始取取c位SQL> select substr('Hello World',3,,5) from dual;llo W3.4、length 字符数lengthb 字节数SQL> select length('中国') 字符,lengthb('中国') 字节from dual字符字节2 43.5、instr(a,b): 在a中，查找b；找到返回下标；否则返回0SQL> select instr('Hello World','ll') from dual;INSTR('HELLOWORLD','LL')------------------------------------------33.6、LPAD(a,b,c) 左填充rpad(a,b,c)右填充lpad：如果a的字符长度小于b就截取b长度的字符作为返回值；如果a的字符长度大于b 就在左边补充c；rpad一样SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右from dual;左右---------- ----------******abcd abcd******3.7、trim(a from b) 去掉b字符串前后指定的a字符SQL> select trim('H' from 'Hello HWorldH') from dual;TRIM('H'FR----------ello HWorld3.8、replace(a,b,c) 把a中的b替换cSQL> select replace('Hello World','l','*') from dual;REPLACE('HE-----------He**o Wor*d3.9、round（a,b）数字运算函数----四舍五入SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三, round(45.926,-1) 四, round(45.926,-2) 五from dual一二三四五---------- ---------- ---------- ---------- ----------45.93 45.9 46 50 04.0、trunc(a,b) 舍去a数字小数点b位后的所有数SQL>select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三, trunc(45.926,-1) 四, trunc(45.926,-2) 五from dual一二三四五---------- ---------- ---------- ---------- ----------45.92 45.9 45 40 04.1、sysdate当前系统时间SQL> select sysdate from dual;SYSDATE--------------08-2月-14 //默认格式:日月年4.2、to_char(a,b) 把a转化成b格式字符串SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR-------------------2014-02-08 15:37:524.2、to_number(a,b) 把a数字字符串转化成b数字（有问题）SQL> select to_number(“123”,123) from dual;TO_NUMBER-------------------1234.3、systimestamp当前系统时间戳,精度更高SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') from dual;TO_CHAR(SYSTIMESTAMP,'YYYY-MM-----------------------------2014-02-08 15:39:05:6970004.4、时间的加减运算，不支持乘除SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天from dual;昨天今天明天-------------- -------------- --------------07-2月-14 08-2月-14 09-2月-144.5、months_between(a,b)精确计算a时间到b时间之间有多少个月SQL> select ename,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二from emp;ENAME 一二-------- ---------- ----------SMITH 403.555255 397.7308924.6、add_months(a,b) 计算在a日期的基础上加b个月后的日期SQL> select add_months(sysdate,97) from dual;ADD_MONTHS(SYS--------------08-3月-224.7、last_day(a)表示计算a日期该月的最后一天是几号SQL> select last_day(sysdate) from dual;LAST_DAY(SYSDA--------------28-2月-144.8、next_day(a,b)表示从a时间后星期b是几号系统默认支持星期为：星期一... 星期日SQL> select next_day(sysdate,'星期六') from dual;NEXT_DAY(SYSDA--------------15-2月-144.9、查询员工的薪水：两位小数(.99) 千位符( , ) 本地货币代码( L )SQL> select to_char(sal , ' L9, 999 . 99 ' ) from emp;TO_CHAR(SAL,'L9,999-------------------￥1,600.005.0、nvl(a,b)和nvl2(a,b,c)的使用1、nvl2(a,b) 当a=null时候,返回b；否则返回a2、nvl2(a,b,c) 当a=null时候,返回c；否则返回bSQL> select sal*12+nvl2(comm,comm,0) from emp;5.1、nullif(a,b) 当a=b时候，返回空；否则返回aSQL> select nullif('abc','abc') from dual;NUL---------5.2、coalesce(a,b) 从左往右找到第一个不为null的值SQL> select comm,sal,coalesce(comm,sal) from emp;5.3、case a when b then c else d end相当于if（a==b）{ c } else { d };SQL> select ename,job,sal 涨前,2 case job when 'PRESIDENT' then sal+10003 when 'MANAGER' then sal+8004 else sal+4005 end 涨后6 from emp;5.4、decode(a,b,c)等价于caseSQL> select ename,job,sal 涨前,2 decode(job,'PRESIDENT',sal+1000,3 'MANAGER',sal+800,4 sal+400) 涨后5 from emp;四、多行函数（函数）4.1、sum(a)求a的总和SQL> select sum(sal) from emp;4.2、count(a)求a 的除去为null值的个数SQL> select count(*) from emp;4.3、avg(a)求a 的平均数SQL> select sum(sal)/count(*) 一,avg(sal) 二from emp;一二---------- ----------2073.21429 2073.21429SQL> select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三from emp;一二三---------- ---------- ----------157.142857 550 550因为count()函数会过滤掉值为null的数，所以count(*)为所有个数，而count(comm)会过滤掉comm=null的数，所以平均值也会过滤掉4.4、group by单列分组SQL> select deptno,avg(sal) from emp group by deptno;DEPTNO A VG(SAL)---------- ----------30 1566.6666720 217510 2916.666674.5、group by多列分组，组函数前的列都要加到group by后SQL> select deptno,job,avg(sal) from emp group by deptno,job order by 1;DEPTNO JOB A VG(SAL)---------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500020 ANALYST 300020 CLERK 95020 MANAGER 297530 CLERK 95030 MANAGER 285030 SALESMAN 14004.6、group by 列名having 条件SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; having 和where的区别：1、having后面的条件可以是组函数，where后条件不能为组函数4.7、group by rollup(deptno,job)（group by 增强）抽象为：group by rollup(a,b) =group by a,b + group by a + grouo by null SQL>select deptno,job from emp group by rollup(deptno,job);DEPTNO JOB---------- ---------10 CLERK10 MANAGER4.8、break on 列skip 2设置显示格式表示以deptno分组，每组中间空2行SQL> break on deptno skip 2SQL>select deptno,job,sum(sal) from emp group by rollup(deptno,job);DEPTNO JOB SUM(SAL)---------- --------- ----------10 CLERK 1300MANAGER 2450PRESIDENT 5000875020 CLERK 1900ANALYST 6000MANAGER 29751087530 CLERK 950MANAGER 2850SALESMAN 56009400DEPTNO JOB SUM(SAL)---------- --------- ----------29025SQL> break on nullSQL>select deptno,job,sum(sal) from emp group by rollup(deptno,job);DEPTNO JOB SUM(SAL)---------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500010 875020 CLERK 190020 ANALYST 600020 MANAGER 297520 1087530 CLERK 95030 MANAGER 285030 SALESMAN 560030 9400290254.9、concat(a,b)连接字符串（与|| 相同）SQL> select concat('hello','world') from dual;CONCAT('HE----------helloworld5.0、wm_concat(a);以str1,str2...格式连接所有字符串把查询所有的数据以, 连接显示SQL> select wm_concat(ename) from emp;WM_CONCAT(ENAME)------------------------------------------SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,五、多表查询5.1、等值连接SQL> select e.empno,e.ename,e.sal,d.dname2 from emp e,dept d3 where e.deptno=d.deptno;EMPNO ENAME SAL DNAME---------- -------- ----- --------------7369 SMITH 800 RESEARCH. . .5.2、不等值连接SQL> select e.empno,e.ename,e.sal,s.grade2 from emp e,salgrade s3 where e.sal between s.losal and s.hisal;EMPNO ENAME SAL GRADE---------- -------- ----- ----------7369 SMITH 800 15.3、外连接外连接：通过外连接，我们可以把某些不成立的记录，包含在最后的结果中左外连接：当where e.deptno=d.deptno不成立的时候,等号左边的表包含在最后的结果中写法: where e.deptno=d.deptno(+)右外连接：当where e.deptno=d.deptno不成立的时候,等号右边的表包含在最后的结果中写法: where e.deptno(+)=d.deptno右外连接实例：SQL> select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数2 from emp e,dept d3 where e.deptno(+)=d.deptno4 group by d.deptno,d.dname;部门号部门名称人数---------- -------------- ----------10 ACCOUNTING 340 OPERATIONS 05.4、自连接：通过表的别名，将同一张表视为多张表SQL> select e.ename 员工姓名, b.ename 老板的姓名2 from emp e,emp b3 where e.mgr=b.empno;员工姓名老板的姓名---------- ----------FORD JONES5.5、层次查询connect by prior由于自连接不适合操作大表，所以大表就用层次查询，层次查询也是查询单表，但以树形结构从上到下查SQL>select level,empno,ename,mgr2 from emp3 connect by prior empno=mgr4 start with mgr is null5 order by 1;LEVEL EMPNO ENAME MGR---------- ---------- -------- ----------1 7839 KING2 7566 JONES 78392 7698 BLAKE 78392 7782 CLARK 78393 7902 FORD 75663 7521 WARD 76983 7900 JAMES 76983 7934 MILLER 77823 7499 ALLEN 76983 7788 SCOTT 75663 7654 MARTIN 76983 7844 TURNER 76984 7876 ADAMS 77884 7369 SMITH 7902这里level为伪列，不存在，但系统自动会加上，表示树的深度connect by prior empno=mgr：表示树父节点的empno=子节点的mgr就符合条件start with mgr is null：表示包含mgr为nullorder by 1：表示以level升序。