Oracle数据库简单使用总结

叁歲伎倆 2021-09-07 06:04 363阅读 0赞

查询员工在1982-1-1年之后入职的人

![20210121164244863.jpeg][]

![20210121164244935.jpeg][]

Like模糊查询

![20210121164244941.jpeg][]

![20210121164244883.jpeg][]

第三个字母为O的员工姓名

![20210121164244942.jpeg][]

![20210121164244892.jpeg][]

需要用到多个or的时候用“in（）”代替

![20210121164244948.jpeg][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70][]

使用is null查询没有上级的雇员的情况

![20210121164244903.jpeg][]

![2021012116424567.jpeg][]

![2021012116424565.jpeg][]

Select \* from emp where (sal>500 or jop=’manager’ ) and ename like ‘J%’;

使用order by 实现降序desc排序 默认升序（asc）

![2021012116424562.jpeg][]

![20210121164244923.jpeg][]

按照部门升序 和 薪水降序

![20210121164244926.jpeg][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 1][]

使用别名进行排序

![20210121164244940.jpeg][]

工资高于平均工资的员工信息

![20210121164244949.jpeg][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 2][]

Group by 分组函数； 查询每个部门的最高工资和平均工资

![20210121164244965.jpeg][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 3][]

![20210121164244980.jpeg][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 4][]

![20210121164244984.jpeg][]

Select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)>2000;

![20210121164244980.jpeg][]

![20210121164244984.jpeg][]

Select a1.dname, a2.ename,a2.sal from dept a1, emp a2 where a1.deptno =a2.deptno and a1.deptno = 10;

Between使用

![20210121164244950.jpeg][]

Select a1.ename ,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

![20210121164244958.jpeg][]

Select a1.ename,a1.sal,a2.dname,a2.deptno from emp a1,dept a2 where a1.deptno=a2.deptno order by a2.deptno;

自链接

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 5][]

select w.ename,b.ename from emp w, emp b where w.mgr=b.empno and w.ename='FORD';

多行子查询

![20210121164245193.jpeg][]

select \* from emp where job in (select distinct job from emp where deptno=10);

distinct 去除重复行

![20210121164245137.jpeg][]

![2021012116424531.jpeg][]

多列子查询

![20210121164245190.jpeg][]

select \* from emp where (deptno, job)=(select deptno, job from emp where ename='SMITH');

![2021012116424527.jpeg][]

方法1

select \* from emp a1, (select avg(sal) mysal,deptno from emp group by deptno)a2 where a1.deptno = a2.deptno and a1.sal>a2.mysal;

方法2

select \* from emp , dept where emp.deptno=dept.deptno and emp.sal>(select avg(sa) deptno from emp group by deptno);

rownum 分页查询 当公式用来记忆

select \* from (select a1.\* ,rownum rn from (select \* from emp) a1 where rownum<=10) where rn>5;

![2021012116424521.jpeg][]

用查询结果创建表

![20210121164245192.jpeg][]

![2021012116424549.jpeg][]

合并查询

union

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 6][]

Union all

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 7][]

intersect

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 8][]

minus

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 6][]

添加

Insert into 表名(列名1，列名2) values （值1，值2）；

修改

Update 表名 sel 字段1=值1，字段2=值2 where 条件；

删除

Delete from 表名 where 条件；

[20210121164244863.jpeg]: /images/20210827/5dd2d227a4a2473ba7745f7a15168f3b.png
[20210121164244935.jpeg]: /images/20210827/b454fc89b34841ca9bc0ec8b607d53a0.png
[20210121164244941.jpeg]: /images/20210827/1fc5cc1c8c2241a9a420c1f403e0c22b.png
[20210121164244883.jpeg]: /images/20210827/04fefff67dd2437f9296535d62c4c70f.png
[20210121164244942.jpeg]: /images/20210827/04c6629ace55440eb07617a2d8da0f69.png
[20210121164244892.jpeg]: /images/20210827/609ad6031e964f63ac0c7439921c2100.png
[20210121164244948.jpeg]: /images/20210827/410af033ed084ef89965de92a6c723e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70]: /images/20210827/8d1ca5904bb64ec58ddf9e06e8ab1a42.png
[20210121164244903.jpeg]: /images/20210827/f453cd0308d942be869f1de3de23f20d.png
[2021012116424567.jpeg]: /images/20210827/b997a3ca90444f0e9f42e3d629ca3fb9.png
[2021012116424565.jpeg]: /images/20210827/78cc5a9e97b640f58f5a26edaf12bc5c.png
[2021012116424562.jpeg]: /images/20210827/b2ef8f9e3a124e7992edea29ec4cfa04.png
[20210121164244923.jpeg]: /images/20210827/cfc9439e9f0f46358c177c0c6f1d966c.png
[20210121164244926.jpeg]: /images/20210827/308b5c9f47ff4c009536242e885dae65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 1]: /images/20210827/a926be0959154f319479864f3e8610b5.png
[20210121164244940.jpeg]: /images/20210827/b4e1bf823a244e278074ffa0e6bd61bc.png
[20210121164244949.jpeg]: /images/20210827/13f89ca8c5894147b9a39ef7e74298a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 2]: /images/20210827/31a14977cb664b098bd0ec38fac31084.png
[20210121164244965.jpeg]: /images/20210827/47ec13cde3a24cd38319781bf04b3da7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 3]: /images/20210827/2850cfa2407a4c37a4e8b722d555cfa9.png
[20210121164244980.jpeg]: /images/20210827/963ae1c90ef84ef7a0e86ed249a36612.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 4]: /images/20210827/946cf468fd2440019f3d3c09234fbff6.png
[20210121164244984.jpeg]: /images/20210827/c8c4e9ac4c9a4165b874bada7171b0db.png
[20210121164244950.jpeg]: /images/20210827/0d7d678b115247c2b1307492217eae07.png
[20210121164244958.jpeg]: /images/20210827/fae7c782bcf641d081703543a1bd5b39.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 5]: /images/20210827/3617d488bfdd47a8ba27f2799d9ea426.png
[20210121164245193.jpeg]: /images/20210827/af749df045b349f086db6c62fd97dbee.png
[20210121164245137.jpeg]: /images/20210827/78fdb49deff8414eb8520164d98c2b54.png
[2021012116424531.jpeg]: /images/20210827/317c368e90c241b48f766dbdeb1449a7.png
[20210121164245190.jpeg]: /images/20210827/c505dddc4e1b4ed587e3242ab9fbd284.png
[2021012116424527.jpeg]: /images/20210827/d27d3af1702d4b8ea6786df4b1b59f0d.png
[2021012116424521.jpeg]: /images/20210827/6b5ccd0356c94b69b2401ae7239f5a85.png
[20210121164245192.jpeg]: /images/20210827/a26225ee89294d59aefcf7600b40758c.png
[2021012116424549.jpeg]: /images/20210827/a73c8b9f916043269ec8d87300cecf56.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 6]: /images/20210827/82da1eb20dcd4d8492a1c4b71605927d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 7]: /images/20210827/276be24f0a034e8a90b903e5ed0b8358.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTYxMjI2_size_16_color_FFFFFF_t_70 8]: /images/20210827/4f9419da0a7c4139ba0bca3e4b7a1632.png