oracle数据库学习笔记

1、注意:学习的朋友要先在数据库中创建这2个表,后边是示例都按照这2个表演示的;-样例表1:部门表CREATE TABLE dept(deptno NUMBER(2 PRIMARY KEY,dname VARCHAR2(14 ,loc VARCHAR2(13 ;-部门表中的数据INSERT INTO dept VALUES(10,'ACCOUNTING','beijing'INSERT INTO dept VALUES (20,'RESEARCH','tianjin'INSERT INTO dept VALUES(30,'SA

2、LES','shanghai'INSERT INTO dept VALUES(40,'OPERATIONS','guangzhou'-样例表2: 职员表empCREATE TABLE emp(empno NUMBER(4 PRIMARY KEY,ename VARCHAR2(10,job VARCHAR2(9,mgr NUMBER(4,hiredate DATE,sal NUMBER(7,2,comm NUMBER(7,2,deptno NUMBER(2 CONSTRAINT FK_DEPTNO REFERENCES DEPT; -给数据

3、表emp增加记录INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy',800,NU LL,20;INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-02-1981','dd-mm-yyyy',160 0,300,30;INSERT INTO emp VALUES(7521,'

4、WARD','SALESMAN',7698,to_date('22-02-1981','dd-mm-yyyy',1250 ,500,30;INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('02-04-1981','dd-mm-yyyy',2975 ,NULL,20;INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_

5、date('28-09-1981','dd-mm-yyyy',12 50,1400,30;INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('01-05-1981','dd-mm-yyyy',2850 ,NULL,30;INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,to_date('09-06-1981','dd-mm-y

6、yyy',2450 ,NULL,10;INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('09-12-1982','dd-mm-yyyy',3000 ,NULL,20;INSERT INTO emp VALUES(7839,'KING','PRESIDENT',UNLL,to_date('17-11-1980','dd-mm-yyyy',500 0,NULL,10;INSERT INTO em

7、p VALUES(7844,'TURNER','SALESMAN',7698,to_date('08-09-1981','dd-mm-yyyy',15 00,0,30;INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('12-01-1983','dd-mm-yyyy',1100,N ULL,20;INSERT INTO emp VALUES(7900,'JAMES','CLERK&

8、#39;,7698,to_date('03-12-1981','dd-mm-yyyy',950,NU LL,30;INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('03-12-1981','dd-mm-yyyy',3000, NULL,20;INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-01-1982',&#

9、39;dd-mm-yyyy',1300, NULL,10;insert into emp(empno,ename,job,hiredate,sal,deptnovalues(7839,'KING','PRESIDENT',to_date('17-11-1981','dd-mm-yy' ,5000,10;如何创建表:create table users_chang(id number(4,namevarchar2(20,password char(6,phone char(11,email varchar2(50;如何删除表

10、:drop table users_chang;如何向表中增加数据insert intousers_chang(id,name,password,phone,emailvalues(1002,'liucs','1234','136*',''如何查询:select*from users_chang;-*表示查询全部列select id,pasword,name from users_chang;-查询id,password,name 这个三个;select name from users_chang wher

11、e id = 1001 and password = '1234'column name format a9;desc emp ; 显示表结构创建表:ID 不能重复,也不能为空:主键PASSWORD:不能为空非空EMAIL不能重复:唯一具体操作:create table users_chang(id number(4 PRIMARY KEY,name varchar2(20,password char(6 NOT NULL,phone char(11,email varchar2(50 UNIQUE;PRIMARY KEY 主键create table emp(empno nu

12、mber(4 primary key,ename varchar2(10,job varchar2(9,mgr number(4,hiredate date,sal number(7,2,comm number(7,2,deptno number(2 constraint fk_deptnon references dept;insert into emp(empno,ename,job,mgr,hiredate,sal,deptnovalues (7369,'smith','clerk' ,7902,'17-DEC-80',800,20;ins

13、ert into emp (empno,ename,job,mgr,sal,deptno values (7369, 'smith','clerk' ,7902,800,20;select ename,sal,comm,sal + nvl(comm,0 month_sal from emp;/nvl(comm,0如果comm是0 输出salselect ename,sal,comm,sal + comm month_sal from emp; /空值和任何数据做算数运算,都是null 解决方案:nvl函数nvl(comm,0;-字符串连接| 相当于JAVA中的+

14、select ename | ' work as ' | job employee from emp; / '|' * '|' 字符串连接smith work as clerk-小写:函数lowerselect lower(ename | ' work as ' | job employee from emp;-全大写upper-首写字母大写initcap-select distinct job from emp;不重复的职位-select distinct deptno from emp;-select distinct dep

15、tno,job from emp;-条件查询whereselect ename,sal from emp where sal >= 300;-名字是scott 的员工(oracle 中的数据大小写敏感。select ename from emp where ename = 'SCOTT'-不确定大小写select * from emp where lower(ename = 'scott'-查询工资在2000-3000之间的人员select ename,sal from emp where sal>=2000 and sal<=3000;-闭区

16、间【2000,3000】select ename,sal from emp where sal between 2000 and 3000;select ename,sal from emp where sal not between 2000 and 3000; 不在这个范围-在部门10和部门20工作的员工select ename,deptno from emp_chang where deptno = 10 or deptno = 20;-在deptno 列表中:inselect ename,deptno from emp_chang where deptno in (10,20;sele

17、ct ename,deptno from emp_chang where deptno not in (10,20; 不在这个范围中-模糊查询like-通配符:%(任意字符_(一个字段where name like '%zhangsan%' 只要包含zhangsan 都查出来; -有多少个表名字包含EMPselect count(* from user_tables where table_name like '%EMP%'-员工名字中有字符A的数据select ename from emp_chang where ename like '%A%'

18、; select ename from emp_chang where ename not like '%A%' 不包含-ename :S_Scott 只查找_S的数据select ename from emp where ename like '%_S%' escape ''-数据排序默认由小到大select ename,sal from emp_chang order by sal;由大到小排列select ename,sal from emp_chang order by sal desc; oracle 把空值看为最大去掉空值然后排序se

19、lect ename,sal from emp_chang where sal is not null order by sal desc;-select空值的处理空值的比较:is null is not null不要用=或<>比较空值-SQL条件中的否定不等于:<> 或者!=-user_tables: 当前用户的表select count(* from user_tables;/当前用户表的总数-desc user_tables; 显示表字段的-表的复制create table emp_chang as select * from emp;Day02二、函数-函数小结

20、(单行函数字符函数:upper (lower / trim / lpad*length / replace / substr /rpad数字函数:round / trunc / mod日期函数:months_between / last_day / add_months / next_day转换函数:to_date* / to_char* /to_number通用函数:nvl / coalesce / decode-空值处理函数nvl-用法:nvl(p1,p2-p1/p2 类型务必保持一致;if p1 si null retrun p2else return p1;计算工资空的按0计算结果放在

21、salsryselect ename, nvl(sal,0 + nvl(comm,0 salary from emp; 职位为空的写为'dazade'select ename,nvl(job,'dazade' from emp_chang;/sysdate 系统时间select ename,nvl(hiredate,sysdate from emp_chang; -查找系统时间默认格式DD -MDN -RRselect sysdate from dual;更改时间格式只对当前会话有效alter session set nls_date_format = '

22、;yyyy-mm-ddhh24:mi:ss'select sysdate + 7 from dual; 7天后的时间-add_monthsselect add_months (sysdate,12 from dual;-month_between :2个日期间隔多少个月,小数形式select ename,months_between (sysdate,hiredate hiredate from emp_chang;-round; 四舍五入select ename,round(months_between(sysdate,hiredate,0 hiredate from emp_cha

23、ng; select round (1234.567, -1 from dual; /1230 四舍五入到十位;-trunc 截取select trunc (1234.789,0 from dual; /1234-mod 取余select mod(17,5 from dual;-字符函数-length 长度select ename,job,length(job from emp_chang;-upper / lower / initcap-trim(去除前后的空格 /lpad(左补位 /rpad(右补位 select ename,lpad(sal,'10','$'

24、; from emp_chang;-last_day: 最后一天select last_day (sysdate from dual;-next_dayselect next_day(sysdate,3 from dual;/-日期格式:DD-MON-RRinsert intoemp_chang(empno,ename,hiredatevalues(5566,'jerry', sysdate;to_date:字符串->日期日期的插入格式、数据要对应insert intoemp_chang(empno,ename,hiredatevalues(5566,'jerry

25、', to_date('2010-10-10','yyyy-mm-dd'insert intoemp_chang(empno,ename,hiredatevalues(5566,'jerry', to_date('08/08/2009','dd/mm/yyyy'to_char(p1,p2 日期/数字-> 字符select to_char(sysdate,'yyyy-mm-dd' from dual; select to_char (sysdate,'year month dd

26、day hh12:mi:ss pm' from dual;year:全拼年 twenty elevenyyyy:四位数字年2011mm:两位数字月06mon:简拼月JUN *6月month:全拼月june *6月dd:两位数字日30day:全拼星期Thursday *星期四hh12:12小时制的小时1:30hh24:24小时制的小时13:30dy:星期的缩写THU *星期四2011年6月30select to_char (sysdate,'yyyy"nian"mm"yue"dd"ri"' from dual;s

27、elect to_char (sysdate,'yyyy"年"mm"月"dd"日"' from dual;select ename,to_char(hiredate,'yyyy"nian"mm"yue"dd"ri"' from emp;-数字-> 字符9: 对应位有数据则显示,没有不显示0: 对应位有数据则显示,没有显示0$: 美元L: 本地货币Local,: 千位符,G.: 小数点,D123456.78 $123,456.78selec

28、t to_char (123456.78,'$00,000,000.00' from dual;/ $00,123,456.78select to_char (123456.78,'$99,999,999.99' from dual;/ $123,456.78-to_number (p1,P2$123,456.78 -> 123456.78select to_number ('$123,456.78','$999,999.99' from dual;/123456.78-转换函数to_date (字符,日期格式to_char

29、(日期,日期格式to_char(数字,数字格式to_number(字符,数字格式-通用函数nvl(p1,p2 -p1和p2类型必须一致-返回第一个非空表达式coalesce(p1,p2,p3,.select ename,sal,comm,coalesce(comm,sal,1000 bonus from emp;/如果comm为空返回sal的值如果sal也为空返回1000 /bonus 年终奖工资奖金都为空发1000 有奖金发commselect ename ,coalesce(comm + sal,sal,0 from emp;-case 语句-decode函数job : MANAGER:

30、20%analyst: 15%clerk: 10%select ename,job,sal,casewhen job = 'MANAGER' then sal * 1.2when job = 'ANALYST' then sal * 1.15when job = 'CLERK' then sal * 1.1else sal end salaryfrom emp_chang;select ename,job,sal,decode(job,'MANAGER',sal * 1.2,'ANALYST',sal * 1.15

31、,'CLERK',sal * 1.1,sal salary from emp;-函数小结(单行函数字符函数:upper (lower / trim / lpad*length / replace / substr /rpad数字函数:round / trunc / mod日期函数:months_between / last_day / add_months / next_day转换函数:to_date* / to_char* /to_number通用函数:nvl / coalesce / decode-组函数-计算表一共有多少行select count(* from emp;

32、/ emp表中行数select count(* from user_tables; /当前用户下所有的表select count(* from all_tables; /当前用户下和允许当前用户访问的表的总和;select count(* from dba_tables; / 数据库中所有的表user_tables / all_tables /dba_tables (DBA权限 select count(comm from emp;/不是null值的记录是多少条-组函数忽略空值select count(* from emp where comm is not null;-avg 平均sum 总

33、和max 最大min 最小select avg(sal from emp_chang;select sum(sal from emp_chang;select sum(sal,avg(sal,max(sal,min(sal,count(* from emp_chang;-avg/sum 只操作数字-max/min 数字日期select max(ename,min(ename from emp_chang;select avg(comm from emp_chang;/不合理select sum(comm/count(* from emp_chang;select avg(nvl(comm,0

34、from emp_chang;/对数据求平均值要注意,默认是忽略空值的。select deptno,sum(sal from emp_chang group by deptno; /计算各个部门工资总和;select job,avg(sal,sum(sal,count(* from emp group by job;-哪些职位人数小于3-分组后的结果再过滤,使用having 短语-where短语不允许出现组函数select job,count(* from emp group by job having count (* < 3;-哪些部门的平均薪水<2000;select dep

35、tno,avg(nvl(sal,0from emp group by deptno havingavg(nvl(sal,0 < 2000;-去除部门为空的select deptno,avg(nvl(sal,0 from empwhere deptno is not nullgroup by deptnohaving avg(nvl(sal,0 < 2000;Day03-查询emp 表,按职位分组,计算每个职位的薪水总和,职位是PRESIDENT的员工不算在内,返回薪水总和>5000元的列表,并按薪水总和排序;select job,sum(sal from emp_changw

36、here job <> 'PRESIDENT'group by jobhaving sum(sal > 5000order by sum(sal;/*select 短语中的非组函数列,必须出现在group by短语中*/ select 列1,列2 组函数from 表名group by 列1,列2 ;select deptno,job,sum(salfrom empgroup by deptno,job;2个操作把部门里的人数统计出来排除空部门的人;select deptno,count(*from emp where deptno is not null gr

37、oup by deptno;-低效的操作select deptno,count(* from emp group by deptno having deptno is not null;where 里有组函数是不能执行的二。表的关联外键FK 主键PK 主表(父表从表(子表-查询员工SCOTT所在部门的工作地点emp 和dept-T1 JOIN T2 NO T1的列= T2的列;-内连接:等值连接、非等值连接、自连接-先做关联查询,-1-等值连接Select emp.ename, dept.locfrom emp JOIN dept ON emp.deptno = dept.deptno whe

38、re emp.ename = 'SCOTT'-先用e.ename = 'SCOTT'过滤驱动表,驱动表只剩一条,再和匹配表做关联查询Select emp.ename, dept.locfrom emp JOIN dept ON emp.deptno = dept.deptno AND emp.ename = 'SCOTT' /先过滤这个(执行这个 -用表别名简化查询Select e.ename, e.deptno, d.locfrom emp e JOIN dept d ON e.deptno = d.deptno where e.ename =

39、 'SCOTT'-驱动表和匹配表互换位置,对查询结果集无影响;Select e.ename, e.deptno, d.locfrom dept d JOIN emp e ON e.deptno = d.deptno; -最终的结果集,一定在两个表中能找到匹配记录-没有部门的员工,没有员工的部门-select e.ename,e.deptno,e.job,d.locfrom emp e join dept d on e.deptno = d.deptno and job = 'MANAGER'-自关联select worker.ename,manager.enam

40、efrom emp worker join emp manageron worker.mgr = manager.empno;-查看薪水等级-薪水等级表:salgrade-非等值连接select e.ename, e.sal,s.gradefrom emp e join salgrade s on e.salbetween s.losal and s.hisal;-外连接select e.ename,d.dname,d.locfrom emp e left outer join dept don e.deptno = d.deptno;/*在驱动表中在匹配表中找不到匹配记录,则匹配一行空值;外

41、连接的结果集= 内连接的结果集+ 驱动表中匹配不上的记录和空值记录的组合。外连接的本质把驱动表中的匹配不上的数据找回来,一个都不能少。-查询员工所在的部门,把部门表中没有员工的部门也查出来-部门表做驱动表select e.ename,d.deptno,d.dname,d.locfrom dept d left outer join emp eon e.deptno = d.deptno;-t1做驱动表t1 left outer join t2t2 right outer join t1-在外连接中驱动表和匹配表不能互换-如何写外连接:先写出内连接,再确定哪张表做驱动表。-选择使用left或者r

42、ight outer join 不重要,重要的是选择哪张表做驱动表-full outer join :全外连接select e.ename,d.deptno,d.dname,d.locfrom dept d full outer join emp eon e.deptno = d.deptno;结果= 内连接结果集+ 没有部门的员工+ 没有员工的部门;- 哪些部门没有员工的?select d.deptno,d.dnamefrom emp e right outer join dept don e.deptno = d.deptnowhere e.empno is null;-哪些员工是没有分配

43、部门的?select e.ename,d.deptnofrom emp e left outer join dept don e.deptno = d.deptnowhere e.deptno is null;-那个部门没有名字是SMITH的员工?-先用e.ename = 'SMITH'过滤匹配表-外连接,dept做驱动表-过滤匹配表的PK列is null;select d.deptno,d.dnamefrom emp e right outer join dept don e.deptno = d.deptnoand e.ename = 'SMITH'wher

44、e e.empno is null;三、子查询-非关联子查询先执行子查询再执行主查询-谁的薪水比SMITH高select ename,sal from empwhere ename = 'SMITH'select ename from emp_changwhere sal > 1200;select ename from empwhere sal > (select sal from emp where ename = 'SMITH' select ename,salfrom empwhere sal = (select min(salfrom em

45、p;- = > < <> 单行比较运算符select ename,salfrom emp where sal = (select sal from empwhere deptno = 10;/错误的-修正为用in操作符select ename,salfrom emp where sal in (select sal from empwhere deptno = 10 ;-每个部门薪水最高的是谁?select deptno,max(salfrom empgroup by deptno;select ename, sal, deptnofrom empwhere (dept

46、no,salin (select deptno, max(salfrom empgroup by deptno;-那个部门的人数比部门20的人数多select deptno, count(* from empwhere deptno =20;select deptno,count(*from empgroup by deptnohaving count(* > (select count(* from emp where deptno = 20 ;DAY 04-关联子查询-从主查询开始遍历;-哪些员工比本部门的平均工资低?select ename,sal from emp owhere

47、sal < (select avg(salfrom emp where deptno = o.deptno;/从主查询开始把主查询的部门传给子查询-emp 表中哪些人是其他人的经理?(即:找到有下属的员工 select ename,job from emp owhere exists (select 1 from emp where mgr = o.empno; / 1 是一个常量有多少行常量就出现多少次;-哪些人没有下属;select ename,job from emp owhere not exists (select 1 from emp where mgr = o.empno;

48、 -那个部门没有员工select d.deptno,d.dname fromemp e right outer join dept don e.deptno = d.deptnowhere e.empno is null;-关联查询select deptno,dname from dept dwhere exists (where 100 from empwhere deptno = d.deptno;-集合差(务必是同构的才能做集合操作select deptno from deptminusselect distinct deptno from emp;集合操作: 并,交,差并集:union

49、(去掉重复的/ union all(不去重复的交集:intersect差集:minusA select empno,ename,sal,deptnofrom emp where deptno = 10;EMPNO ENAME SAL DEPTNO- - - -7782 CLARK 2450 107934 MILLER 1300 10B select empno,ename,sal,deptnofrom emp where sal > 2000;EMPNO ENAME SAL DEPTNO- - - -7566 JONES 2975 207698 BLAKE 2850 307782 CLA

50、RK 2450 107788 SCOTT 3000 207902 FORD 3000 20并集:union / union allselect empno,ename,sal,deptnofrom emp where deptno = 10unionselect empno,ename,sal,deptnofrom emp where sal > 2000;select empno,ename,sal,deptnofrom emp where deptno = 10union allselect empno,ename,sal,deptnofrom emp where sal >

51、2000;SQL调优假设:A和B没有重复记录,没有排序要求,首选union all交集:intersectselect empno,ename,sal,deptnofrom emp where deptno = 10intersectselect empno,ename,sal,deptnofrom emp where sal > 2000;差集:minusselect empno,ename,sal,deptnofrom emp where deptno = 10minusselect empno,ename,sal,deptnofrom emp where sal > 2000

52、;insert into / update / deleteinsert into 插入数据-列名和列数据必须对应:个数/数据类型-如果新增全部数据,列名可以省略insert into 表名(列名1,列名2.values (列数据1,列数据2.;insert intoemp(empno,ename,job,hiredate,sal,deptnovalues(7839,'KING','PRESIDENT',to_date('17-11-1981' ,'dd-mm-yy',5000,10;-把emp表中的部分数据复制到表users_c

53、hang中。insert into users_chang(id,name,password(select empno,ename,'1234'from emp;-原表复制create table user_chang1 as (select empno id,ename name,'1234' password from emp;update 修改数据注意where判断语句不写的话默认就更改表中所有的数据;update users_changset password = '12345'where id = 1003;-修改多行数据注意updat

54、e users_chang set name = 'tom',password =delete 删除数据注意where判断语句不写的话默认就删除表中所有的数据;delete from users_chang where id = 1003;事务Transaction- 一组DML操作的集合,一起成功或是一起失败-事务内的数据改动事务之外看不到。-事务的边界-起点:第一条DML操作-终点:显示终止事务commit(确认/ rollback(回滚;隐式终止事务:DDL语句/ 终止会话savepoint:保存点create table mytemp(id number;insert

55、into mytemp values(1;savepoint A; 保存点Ainsert into mytemp values(2;savepoint B; 保存点Binsert into mytemp values(3;savepoint C;insert into mytemp values(4;rollback to B; 回滚到保存点B;-C已经被取消insert.update.delete.update.create table.(DDL隐式终止事务truncate (DDL 截取保留表结构,删除表的数据不需提交(commit 立即生效;truncate table mytemp;a

56、lter 修改表结构drop table mytemp;create table mytemp(id number;alter table mytemp add name varchar2(20; add-查看结构desc mytemp;-修改列的数据类型定义modify(修改,更改alter table mytemp modify name varchar2(30;-新增一条记录insert into mytemp values(1,'abc'-再改一次,有可能会失败alter table mytemp modify name varchar2(2; /失败-删除name列a

57、lter table mytemp drop column name;-alter 可以用来增加约束条件-给mytemp表增加主键约束条件alter table mytemp add constraintmytemp_pk primary key(id;- -| |约束条件的名字建立约束条件的列-加减列的替代做法rename to 改名-create table mytemp(id number,name varchar2(20; insert into mytemp (select deptno,dname from dept; -建立新表,增加birth列create table myte

58、mp_1asselect id,name,sysdate birth from mytemp;drop table mytemp;-删除表rename mytemp_1 to mytemp;-改名-DAY05一、约束-主键:primary key ,简称:PK-非空:not null 非空可以重复不能放入自定义约束条件名字中;-唯一:unique-检查:checkcreate table student(id number(4 primary key,name varchar2(20 not null,email varchar2(50unique,classid number(2;insert into student values(1,'tom','t',10; insert into student values(2,'rom','r',30;-email列有重复insert into student values(4,'fom','r',50;-自定义约束条件名字;create table