oracle数据库学习笔记.doc

注意：学习的朋友要先在数据库中创建这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,SALES,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);-给数据表emp增加记录INSERT INTO emp VALUES(7369,SMITH,CLERK,7902,to_date(17-12-1980,dd-mm-yyyy),800,NULL,20);INSERT INTO emp VALUES(7499,ALLEN,SALESMAN,7698,to_date(20-02-1981,dd-mm-yyyy),1600,300,30);INSERT INTO emp VALUES(7521,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_date(28-09-1981,dd-mm-yyyy),1250,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-yyyy),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),5000,NULL,10);INSERT INTO emp VALUES(7844,TURNER,SALESMAN,7698,to_date(08-09-1981,dd-mm-yyyy),1500,0,30);INSERT INTO emp VALUES(7876,ADAMS,CLERK,7788,to_date(12-01-1983,dd-mm-yyyy),1100,NULL,20);INSERT INTO emp VALUES(7900,JAMES,CLERK,7698,to_date(03-12-1981,dd-mm-yyyy),950,NULL,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,dd-mm-yyyy),1300,NULL,10); insert into emp(empno,ename,job,hiredate,sal,deptno) values(7839,KING,PRESIDENT,to_date(17-11-1981,dd-mm-yy) ,5000,10);如何创建表：create table users_chang(id number(4),name varchar2(20),password char(6),phone char(11),email varchar2(50);如何删除表：drop table users_chang;如何向表中增加数据insert into users_chang(id,name,password,phone,email)values(1002,liucs,1234);如何查询：select*from users_chang;-*表示查询全部列select id,pasword,name from users_chang;-查询 id,password,name 这个三个；select name from users_chang where 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 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_deptnon references dept);insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)values (7369, smith,clerk ,7902,17-DEC-80,800,20);insert 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中的+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 deptno,job from emp;-条件查询 where select 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;-闭区间 【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);select 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%;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 把空值看为最大去掉空值 然后排序select 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二、 函数-函数小结 (单行函数)字符函数：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计算 结果 放在salaryselect ename, nvl(sal,0) + nvl(comm,0) salary from emp;职位为空的 写为 dazadeselect 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 = yyyy-mm-dd hh24: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_chang;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,$) from emp_chang;-last_day: 最后一天select last_day (sysdate) from dual;-next_day select next_day(sysdate,3) from dual; / -日期格式 ：DD-MON-RRinsert into emp_chang(empno,ename,hiredate)values(5566,jerry,sysdate);to_date:字符串 -日期日期的插入 格式、数据要对应insert into emp_chang(empno,ename,hiredate)values(5566,jerry,to_date(2010-10-10,yyyy-mm-dd);insert into emp_chang(empno,ename,hiredate)values(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 day hh12:mi:ss pm) from dual;year:全拼年 twenty elevenyyyy:四位数字年 2011mm:两位数字月 06mon：简拼月 JUN *6月month:全拼月 june *6月dd:两位数字日 30 day:全拼星期 Thursday *星期四hh12:12小时制的小时 1:30hh24:24小时制的小时 13:30 dy:星期的缩写 THU *星期四2011年6月30select to_char (sysdate,yyyynianmmyueddri) from dual;select to_char (sysdate,yyyy年mm月dd日) from dual;select ename,to_char (hiredate,yyyynianmmyueddri) from emp;-数字 - 字符9: 对应位有数据则显示，没有不显示0: 对应位有数据则显示，没有显示0$: 美元L: 本地货币Local,: 千位符，G.: 小数点，D123456.78 $123,456.78select 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(日期，日期格式)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: 20% analyst: 15% clerk: 10%select ename,job,sal,case when 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, 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; / 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 总和 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) 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 deptno,avg(nvl(sal,0) from emp group by deptno having avg(nvl(sal,0) 2000;-去除部门为空的select deptno,avg(nvl(sal,0) from emp where deptno is not nullgroup by deptno having avg(nvl(sal,0) 5000元的列表，并按薪水总和排序；select job,sum(sal) from emp_chang where job PRESIDENT group by job having sum(sal) 5000order by sum(sal);/*select 短语中的非组函数列，必须出现在group by短语中*/select 列1， 列2 组函数from 表名group by 列1， 列2 ；select deptno,job,sum(sal)from empgroup by deptno,job;2个操作 把部门里的人数统计出来 排除空部门的人；select deptno,count(*)from emp where deptno is not nullgroup by deptno;-低效的操作select deptno,count(*) from emp group by deptnohaving 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.deptnowhere emp.ename = SCOTT;-先用e.ename = SCOTT过滤驱动表 ，驱动表只剩一条，再和匹配表做关联查询Select emp.ename, dept.locfrom emp JOIN dept ON emp.deptno = dept.deptnoAND emp.ename = SCOTT; /先过滤这个 （执行这个）-用表别名简化查询Select e.ename, e.deptno, d.locfrom emp e JOIN dept d ON e.deptno = d.deptnowhere e.ename = 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.deptnoand job = MANAGER;-自关联select worker.ename,manager.ename from 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;/*在驱动表中在匹配表中找不到匹配记录，则匹配一行空值；外连接的结果集 = 内连接的结果集 + 驱动表中匹配不上的记录和空值记录的组合。外连接的本质把驱动表中的匹配不上的数据找回来，一个都不能少。-查询员工所在的部门，把部门表中没有员工的部门也查出来-部门表做驱动表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或者right outer join 不重要，重要的是选择哪张表做驱动表-full outer join :全外连接select e.ename,d.deptno,d.dname,d.locfrom dept d full outer join emp e on e.deptno = d.deptno;结果 = 内连接结果集 + 没有部门的员工 + 没有员工的部门；- 哪些部门没有员工的？select d.deptno,d.dnamefrom emp e right outer join dept don e.deptno = d.deptnowhere e.empno is null;-哪些员工是没有分配部门的？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;where e.empno is null;三、子查询-非关联子查询 先执行子查询 再执行主查询-谁的薪水比SMITH高select ename,sal from empwhere ename = SMITH;select ename from emp_changwhere sal 1200;select ename from emp where sal (select sal from emp where ename = SMITH);select ename,sal from empwhere sal = (select min(sal) from emp);- = 单行比较运算符select ename,sal from emp where sal = (select sal from empwhere deptno = 10);/错误的-修正为用in操作符select ename,sal from emp where sal in (select sal from empwhere deptno = 10 ); -每个部门薪水最高的是谁？select deptno,max(sal)from empgroup by deptno;select ename, sal, deptnofrom emp where (deptno,sal)in (select deptno, max(sal)from empgroup by deptno);-那个部门的人数比部门20的人数多select deptno, count(*) from emp where deptno =20;select deptno,count(*)from emp group by deptnohaving count(*) (select count(*) from emp where deptno = 20);DAY 04-关联子查询-从主查询开始遍历；-哪些员工比本部门的平均工资低？select ename,sal from emp owhere sal 2000; EMPNO ENAME SAL DEPTNO- - - - 7566 JONES 2975 20 7698 BLAKE 2850 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7902 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 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;insert into / update / deleteinsert into 插入数据-列名和列数据必须对应：个数/数据类型-如果新增全部数据，列名可以省略insert into 表名（列名1，列名2.)values (列数据1，列数据2.); insert into emp(empno,ename,job,hiredate,sal,deptno) values(7839,KING,PRESIDENT,to_date(17-11-1981,dd-mm-yy) ,5000,10);-把emp表中的部分数据复制到 表users_chang中。insert into users_chang(id,name,password)(select empno,ename,1234from emp);-原表复制create table user_chang1 as (select empno id,ename name,1234 password from emp);update 修改数据 注意where判断语句 不写的话默认就更改表中所有的数据；update users_changset password = 12345where id = 1003;-修改多行数据 注意update users_chang set name = tom,password = 1234,phone = 12345678900 where id = 1003;delete 删除数据 注意 where判断语句 不写的话默认就删除表中所有的数据；delete from users_chang where id = 1003;事务 Transaction- 一组DML操作的集合，一起成功或是一起失败-事务内的数据改动事务之外看不到。-事务的边界 -起点：第一条DML操作-终点：显示终止事务commit(确认）/ rollback(回滚）；隐式终止事务：DDL语句 / 终止会话savepoint:保存点create table mytemp(id number);insert 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;alter 修改表结构drop