Oracle数据库记录

一 数据库基本知识 1，数据集成和共享。 2，软件：物理数据库和数据库系统用户之间的一层（软件层）：DBMS(数据库管理系统)，所有访问数据库的请求都是由DBMS处理的，DBMS为用户提供一种在硬件层之上观察数据库的高级别方式，并且支持用户以这种高级别方式表达操作请求。 3，数据管理指的是对数据的组织、编目、定位、存储，检索和维护等，随着硬件的发展，经历三个阶段人工管理（50年代中期以前）、文件系统阶段（50年代后期、60年代中期）、数据库系统阶段。 二 SQL基本命令分类 1 类别 SQL语句 数据定义语言（DDL） DROP、CREATE、ALTER、GRANT、REVOKE 数据操纵语言（DML） SELECT、INSERT、UPDATE、DELETE、 SET TRANSACTION、 EXPLAINPLAN 事物控制 COMMIT ROLLBACK SAVEPOINT 会话控制 ALTER SESSION 、SET ROLE 系统控制 ALTER SYSTEM 嵌入式SQL CONNECT、DECLARE CURSOR 、ALLOCATE     三 查询基础 3.1 3.1 33..11、入门语句 普通用户连接：Conn scott/tiger超级管理 员连接：Conn“sys/sysassysdba” Disconnect; 断开连接 Savec:\1.txt 把SQL存到文件 Edc:\1.txt 编辑SQL语句 @c:\1.txt 运行SQL语句 Descemp; 描述Emp结构 Select*fromtab;查看该用户下的所有对象 Showuser; 显示当前用户 如果在sys用户下：查询Select*fromemp; 会报错，原因：emp是属于scott，所以此时必 须使用：select*fromscott.emp; / 运行上一条语句 四 单行函数 4．1、字符函数 Upper 转换成大写（Select Upper(‘abcd’) from dual; Lower 转换成小写 （select Lower(‘ABCD’) from dual; Initcap 首个单词字母大写其他小写 （select initcap(ename) from emp；）; Concat 连接字符串  (select concat(‘abbb’,’ccc’) from dual;); Substr 从第几个字符(负数是倒数第几个)开始截取几个Select substr(‘abcdefg’,2,1) from dual; Length 字符的长度  select length(ename) from emp; Replace  替换      select replace(ename,’a’,’A’) from emp; Trim    过滤首尾空格  select trim(‘Mr Smith  ’) from dual; 4．2、数值函数 Round select round(412,-2) from dual; select round(412.312,2) from dual; Trunc Select trunc(412,13,-2) from dual; 4．3、日期函数 Months_between() Select months_between(sysdate,hiredate) from emp; Add_months() 加X个月 Select add_months（sysdate,1） from dual; Next_day() Select next_day(sysdate,’星期一’) from dual; Last_day  最后一天 Select last_day(sysdate) from dual; 4．4、转换函数 To_char Select to_char(sysdate,’yyyy’) from dual; Select to_char(sysdate,’fmyyyy-mm-dd’) form dual; Select to_char(sal,’L999,999,999’) from emp; select to_char((sysdate-1),'D') from dual;//返回星期 To_number Select to_number(‘  13’)+to_number(‘14’) from dual; To_date Select to_date(‘20090210’,’yyymmdd’) from dual; 4．5、通用函数 Nvl() Select nv(comm.,0) from emp; Nullif() Select nullif(exp1,exp2) from emp 如果exp1与exp2的值相等则返回null，否则返回exp Nvl2() Select empno,ename,sal,comm.,nvl2(comm.,comm+sal,sal)  total from emp; Coalesce() Select  empno,ename,sal,comm.,coalesce(sal+comm,sal,0) 总收入 from emp; Case表达式 select empno,ename,sal, case deptno when 10 then '财务部' when 20 then '研发部' when 30 then '销售部' else '未知部门' end 部门 from emp Decode() 函数 和case表达式类似，decode()函数也用于实现多路分支结构 4. 6 、练习 找 出 每 个 月 倒 数 第 三 天 受 雇 的 员 工 （ 如 ： 2 009 - 5 - 29 ） selec t * from emp where last_day(hiredate ) -2=hiredate; 找出 25 年 前 雇 的 员 工 selec t * from emp where hiredate<=add_months(sysdate , - 25*12); 所有员工名字前加上Dear,并且名字首字母大写 Select ‘Dear’|| initcap(ename) from emp; 找出姓名为5个字母的员工 Select * from emp where length(ename)=5; 找出姓名不带R这个字母的员工 Select * from emp where ename not like ‘%R%’; 显示所有员工的姓名的第一个字 Select substr(ename,0,1) from emp; 显示所有员工，按名字降序排列，若相同，则按工资升序排序 Select * from emp order by ename desc,sal 假设一个月为30天，找出所有员工的日薪，不计小数 select ename,round(sal/30) from emp; 找出2月份受雇的员工 Select * from emp where to_char(hiredate,’fmmm’) =’2’; 列出员工加入公司的天数（四舍五入） Select ename,round(sysdate-hiredate) from emp 第五章 分组函数 5.1 COUNT 如果数据库表的没有数据，count(*)返回的不是null，而是0 5.2 Avg max min sum 5.3、分组函数与空值 分组函数省略列中的空值 selectavg(comm)fromemp; selectsum(comm)fromemp; 可使用NVL()函数强制分组函数处理空值 Select avg(nvl(comm,0)) from emp; 5.4 GROUP BY 子句 出现在SELECT列表中的字段或者出现在order by后面的字段，如果不是包含在分组函数 中，那么该字段必须同时在GROUPBY子句中出现。 包含在GROUPBY子句中的字段则不必须出现在SELECT列表中。 可使用where字句限定查询条件 可使用Orderby子句指定排序方式 如果没有GROUPBY子句，SELECT列表中不允许出现字 段（单行函数）与分组函数混用的情况。 Select empno,sal from emp;//合法 Select avg(sal) from emp;//合法 Select empno,init cap(ename), avg(sal) from emp;//非法 不允许在WHERE 子句中使用分组函数。 Select deptno,avg(sal) from emp where avg(sal)>2000; group by deptno; 5.5 HAVING子句 Select deptno,job,avg(sal) from emp where hiredate>=to_date('1981-05-01','yyyy-mm-dd') group by deptno,job having avg(sal)>1200 order by deptno,job; 5.6，分组函数嵌套 Select max(avg(sal)) from emp group by deptno; 5.7,练习 分组统计各部门下工资>500的员工的平均工资， Select avg(sal) from emp where sal>500 group by deptno; 统计各部门下平均工资大于500的部门 Select deptno,avg(sal) from emp group by deptno having avg(sal)>500; 算出部门30中得到最多奖金的员工奖金 Select max(comm) from emp where deptno=30; 算出部门30中得到最多奖金的员工姓名 Select ename from where comm=(select max(comm) from emp where deptno=30); Select job,min(sal),count(*) from emp group by job; 列出每个部门的员工数和部门no Select count(*),deptno from emp group by deptno; 得到工资大于自己部门平均工资的员工信息 Select * from emp e1,(select deptno,avg(sal) as avgsal from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.avgsal; 分组统计每个部门下，每种职位的平均奖金(也要算奖金的人) 和总工资（包括奖金） Select deptno,job,avg(nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by deptno,job; 第六章 6.1、笛卡尔集（Cross Join） Select * from emp,dept; 6.2、等值连接（Equijoin）(Natural join..on) Select empno,ename,sal,emp deptno,dname from emp,dept where emp.deptno=dept.deptno; 6.3、非等值连接（Non0equijoin） Select ename,empno,grade from emp,salgrade where sal between losal and hisal; 6.4、自连接（Self join） Select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno; Select e.empno,e.ename,m.empno,m.ename from emp e,emp m where m.mgr=e.empno; 6.5、左外连接（Left Outer Join） Select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid=s1.sid(+); Select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno; 6.6 右外联接（Right Outer Join） Select s.sid,s.sname,sq.sname from student s,studnet1 s1 where s.sid(+)=s1.sid; Select empno,ename,dname from emp right outer join dept on emp.deptno = dept.deptno; 6.7 满外联接 （Full Outer Join） Select empno,ename,dname from emp full outer join dept on emp.deptno = dept.deptno; 第七章 子查询 7.1、单行子查询 Select * from emp where sal>(select sal from emp where empno=7566); 7..2、子查询空值/多值问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 如果子查询未返回任何行，则主 查询也不会返回任何结果(空值)select*fromempwheresal>(selectsal fromempwhereempno=8888); 如果子查询返回单行结果，则为单行子查询，可以在主查 询中对其使用相应的单行记录比较运算符 (正常)select * from emp where sal>(select sal from emp where empno=7566); 如果子查询返回多行结果，则为多行子查询，此时不允许 对其使用单行记录比较运算符 (多值)select*fromempwheresal>(selectavg(sal)from emp group by deptno);//非法 7.3 多行子查询 Select * from emp where sal>any(select avg(sal) from emp group by deptno); Select * from emp where sal>all(select avg(sal) from emp group by deptno); Select * from emp where job in(select job from emp where ename=’MARTIN’ or ename=’SMITH’); 7.4、TopN查询 Select * from emp where rownum=1 or rownum = 2; Select * from (select * from emp order by sal desc) where rownum<=5; 7.5、分页查询 Select * from (select rownum no,e.* from (select * from emp order by sal desc) e where rownum<=5 ) where no>=3; 7.6、exists EXISTS的执行 流程 快递问题件怎么处理流程河南自建厂房流程下载关于规范招聘需求审批流程制作流程表下载邮件下载流程设计 Select * from t1 where exists (select null from t2 where y=x) 7.7、练习 列出员工表中每个部门的员工数，和部门no Select deptno,count(*) from emp group by deptno; 列出员工表中每个部门的员工数（员工数必须大于3），和部门名称 Select d.* ed.cou from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>3) ed where d.deptno=ed.deptno; 找出工资比jones多的员工 Select * from emp where sal>=(select sal from emp where lower(ename)=’jones’); 列出所有员工的姓名和其上级的姓名 Select e1.ename as lower,e2.ename as upper from emp e1,emp e2 where e1.mgr=e2.empno; Select e1.ename as lower e2.ename as upper from emp e1.emp e2 where e1.mgr=e2.empno(+); 以职位分组，找出平均工资最高的两种职位 Select * from (select avg(sal) from emp order by job desc) where rownum<3; 查找出不在部门20，且比部门20中任何一个人工资都高的员工的姓名、部门名称 Select e.ename,d.dname from emp e,dept d where e.deptno<>20 and e.sal>(select max(sal) from emp where deptno=20) and e.deptno=d.deptno; 得到平均工资大于2000的工作职种 Select job from emp group by job having avg(sal)>2000; 分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 Select deptno,avg(sal) from emp where sal>2000 group by deptno having avg(sal)>2500; 得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 Select * from dept where deptno=(select deptno,min(count(sal)) form emp group by deptno); 得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 select * from dept where deptno=(select e.deptno from (select deptno,sum(sal) from emp group by deptno order by sum(sal)) e where rownum=1); 分别得到平均工资等级为4级(等级表) 的部门编号 select new.dno from salgrade sa,(select deptno as dno,avg(sal) as avgsal from emp group by deptno) new where sa.grade=4 and new.avgsal between sa.losal and sa.hisal； 查找出部门10和部门20中，工资最高第三名到工资第5名的员工的员工名字，部门名字，部门位置 select emp.ename,dept.dname,dept.loc from emp,dept,(select rownum no,new.* from (select * from emp where emp.deptno=10 or deptno=20 order by emp.sal desc) new) e where emp.deptno=dept.deptno and e.no>=3 and e.no<=5 and e.empno = emp.empno 查找收入（工资加奖金），下级比自己上级还高的员工编号，员工名字，员工收入 Select e.ename,e.empno,e.sal+nvl(e.comm,0) from emp e,emp m where e.mgr=m..empno and (e.sal+nvl(e.comm,0))>(m.sal+nvl(m.comm,0));