首页 / ORACLE / oracle学习总结2
oracle学习总结2
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle学习总结2,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7069字,纯文字阅读大概需要11分钟。
内容图文
![oracle学习总结2](/upload/InfoBanner/zyjiaocheng/462/f24c806206004e3fbf9dfaa6347d73a3.jpg)
1:常用的函数
to_date()函数,将字符串转换为日期格式
select to_date(‘2015-09-12‘,‘yyyy-MM-dd‘) from dual; --其中后面的日期格式要和前面要转化的匹配
to_number()函数,将字符串转换为数字格式
select ename,sal from emp where sal>to_number(‘$5000.00‘,‘$9999.99‘);
清屏命令:clear screen;
2:常用的组函数 max() min() avg() sum() count()
select max(sal) from emp; --最高薪水是多少
select min(sal) from emp; --最低薪水是多少
select avg(sal) from emp; --平均薪水是多少
select sum(sal) from emp; --薪水总和是多少
select count(ename) from emp; --总共有多少员工
3:group by 求部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
4:执行select一般顺序
select deptno,avg(sal) from emp(表名) where sal>5000(where限定条件) group by deptno(按照部门分组) having avg(sal)>5000
order by avg(sal) desc;
5:处理重复记录的问题
select empno from emp group by empno having count(empno)>1; --查询出重复的empno
select * from emp where empno in(select empno from emp group by empno having count(empno)>1); --查询出重复的记录
delete from emp where empno in (select empno from emp group by empno having count(empno)>1) and rowid not in (select min
(rowid) from emp group by empno having count(empno)>1); --删除重复记录,保留第一条
6:子查询
select ename,sal from emp where sal=(select max(sal) from emp); --查询公司中薪水最高的员工姓名
select ename,sal from emp where sal in(select max(sal) from emp group by deptno);--查询每个部门薪水最高的员工姓名 --错误的
(所有员工只要和其中一个匹配就会被查出)
select ename,sal from emp e join (select max(sal) max_sal,deptno from emp group by deptno) t on (e.sal=t.max_sal and
e.deptno=t.deptno);
或者select ename,sal from emp e,(select max(sal) max_sal, deptno from emp group by deptno) t where e.sal=t.max_sal and
e.deptno=t.deptno;
select empno,count(1) from emp group by empno having count(1)>1; --查询员工号重复的员工
92年标准:
select ename,sal,grade from emp e,grade g where e.sal between g.lowsal and g.higsal; --求所有员工薪水等级
select ename,sal,grade from emp e join (select max(sal) max_sal,deptno from emp group by deptno) t on (e.sal=t.max_sal and
e.deptno=t.deptno) join grade g on (t.max_sal bwtween g.lowsal and g.higsal);--求部门中薪水最高员工姓名、等级
--求各部门平均工资等级
select deptno ,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on (t.avg_sal
between g.lowsal and g.higsal);
--求各部门的平均薪水等级
select ename,deptno,sal,grade from emp e join grade g on(e.sal between g.lowsal and g.higsal); --每个员工的薪水等级
select deptno,avg(grade) from (select ename,deptno,sal,grade from emp e join grade g on(e.sal between g.lowsal and
g.higsal)) t group by deptno;
99年sql标准:
select ename,sal,grade from emp e join grade g on (e.sal between g.lowsal and g.higsal);
自连接:在同一张表进行操作
select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr=e2.empno);或者select e1.ename,e2.ename from emp e1,emp e2 where
e1.mgr=e2.empno;
左外连接:
select e.ename,d.loc from emp e left join dept d on (e.deptno=d.deptno);--左外连接会把左边那张表中没有关联到的字段也给查询
出来
右外连接:
select e.ename,d.loc from emp e right join dept d on(e.deptno=d.deptno);--右外连接会把右边那张表中没有关联到的字段也给查询
出来
全外连接:
select e.ename,d.loc from emp e full join dept d on(e.deptno=d.deptno); --全连接会把左右两边表中没有关联到的字段都给查询出
来
7:--查询所有的经理人
select ename from emp where empno in(select distinct mgr from emp);
8:--不使用组函数,查询薪水的最高值(先按照薪水排序,然后查询第一条)
select sal from (select sal from emp order by sal desc) where rownum=1;
或者
select e1.sal from emp where sal not in (select e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal)); --自连接表,那么表e1中
只有最大值不会关联到
9:--求平均薪水最高的部门的名称
1:--求各部门平均薪水
select deptno,avg(sal) from emp group by deptno;
2: --求平均薪水最高为多少
select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno);
3: --求平均薪水最高部门的编号
select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal =(select max(avg_sal) from
(select deptno,avg(sal) avg_sal from emp group by deptno));
4:--求平均薪水最高部门的名称
select dname from dept where deptno = (select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno) where
avg_sal =(select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno)));
10: --求平均薪水等级最低的部门名称
1:--求各部门平均薪水
select avg(sal),deptno from emp group by deptno;
2:--求各部门平均薪水等级
select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal
between g.lowsal and g.higsal);
3:--求平均薪水等级最低
select min(grade) from (select grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on
(t.avg_sal between g.lowsal and g.higsal));
4:--求平均薪水等级最低的部门编号
select deptno from (select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade
g on(t.avg_sal between g.lowsal and g.higsal)) where grade =(select min(grade) from (select grade from (select avg(sal)
avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal between g.lowsal and g.higsal)));
5:--求平均薪水等级最低的部门编号
select dname from dept where deptno in (select deptno from (select deptno,avg_sal,grade from (select avg(sal)
avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal between g.lowsal and g.higsal)) where grade =(select
min(grade) from (select grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t join grade g on(t.avg_sal
between g.lowsal and g.higsal))));
11:oracle数据库用户切换
conn sys/root(用户名/密码) as sysdba;
授权语句:
grant create table,create view to system(用户名);
grant select,update,insert on emp to hr; --一次可以授权多种操作,但是只能针对一张表
12:--求比普通员工最高工资还要高的经理人
1:求普通员工的最高工资
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)
2:求还要高的经理人
select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (select max(sal) from
emp where empno not in(select distinct mgr from emp where mgr is not null));
13:--求薪水最高的前2名员工
select ename,sal from (select ename,sal from emp order by sal desc) where rownum <3;
14:rownum 子查询,实现分页,首先是排序,然后是查询rownum,第三步限定条件
select ename,sal from
(select ename,sal,rownum r from
(select ename,sal from emp order by sal desc)) where r>=2 and r<=3
对比:
mysql:select ename,sal from (select ename,sal from emp order by sal desc) t where id limit 2,2;--从2开始取,取2条
db2: 使用fetch
13:dml语句(数据操纵语句)
update emp set ename=‘tom‘ where empno=1001;
delete from emp where empno=1002;
insert into emp (empno,deptno) values(1005,20);
14:事务控制(dcl语句)
a:commit提交 rollback回滚 transcation事务结束
b:当执行ddl语句之后,之前的事务也会被提交
c:正常的退出连接,exit,也会自动提交事务
oracle学习总结2
标签:
本文系统来源:http://www.cnblogs.com/warrior4236/p/5376599.html
内容总结
以上是互联网集市为您收集整理的oracle学习总结2全部内容,希望文章能够帮你解决oracle学习总结2所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。