oracle笔记——第二天:基本操作(增删改查)

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:42

[color=#362e2b]--打开查询时间
[color=#362e2b]set timing on;
[color=#362e2b]--将查询出来的数据在插入进去
[color=#362e2b]insert into student (id,name) select *from student;
[color=#362e2b]--删除表 级联删除(就是把用户里面的表和数据一起删掉)
[color=#362e2b]drop user liu_1 cascade
[color=#362e2b]--处理为值得空
[color=#362e2b]select ename,sal,comm,sal+nvl(comm,0) from emp;
[color=#362e2b]--查询1982年1月1人后入职的员工
[color=#362e2b]select *from emp where hiredate>'1-1月-1982';
[color=#362e2b]--多条件查询
[color=#362e2b]select *from emp where sal>1000 and sal<5000; [color=#362e2b]--首字母为大写S的员工 [color=#362e2b]select *from emp where ename like 'S%'; [color=#362e2b]--第三个字符为O的人 [color=#362e2b]select *from emp where ename like '__O%'; [color=#362e2b]--查询员工号为 123、456,789; [color=#362e2b]select *from emp where empno in(123,465,798); [color=#362e2b]--如何显示没有上级的人(就是直列段为空) [color=#362e2b]select *from emp where mgr is null; [color=#362e2b]--工作该高于五百,岗位为manager,同时首之母为大写的J [color=#362e2b]select *from emp where (sal>500 or job='MANAGER') and ename like 'J%';
[color=#362e2b]--使用order by desc(高到低) asc(低到高)
[color=#362e2b]select *from emp order by sal [desc];
[color=#362e2b]-- 按照部门号升序 ,员工工资降序
[color=#362e2b]select *from emp order by deptno asc,sal desc;
[color=#362e2b]--使用列的别名排序
[color=#362e2b]select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";
[color=#362e2b]--复杂查询(子查询)
[color=#362e2b]select *from emp where sal in (select max(sal) from emp);
[color=#362e2b]--最高工资,最低工资
[color=#362e2b]select *from emp where sal in (select max(sal) from emp,select min(sal) from emp);
[color=#362e2b]--group by 和 having
[color=#362e2b]select avg(sal),max(sal),deptno from emp group by deptno;
[color=#362e2b]select avg(sal),max(sal),job,deptno from emp group by deptno,job;
[color=#362e2b]--平均工资小于两千的人员信息
[color=#362e2b]select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;
[color=#362e2b]--笛卡尔积
[color=#362e2b]select ename ,job,sal,comm,hiredate,loc,dname from emp,dept;
[color=#362e2b]--自连接查询(显示某员工的上级信息)
[color=#362e2b]select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='FORD';
[color=#362e2b]--单行子查询(与某人同一部门的员工)
[color=#362e2b]select *from emp where deptno=(select deptno from emp where ename='SMITH')
[color=#362e2b]--显示工资比三十号部门都要大的的人员信息
[color=#362e2b]select ename,sal,deptno from emp where sal> all(select sal from emp where deptno=30)
[color=#362e2b]--与三十号工资相同的人员信息
[color=#362e2b]select ename,sal,deptno from emp where sal in (select sal from emp where deptno=30)
[color=#362e2b]--多行子查询
[color=#362e2b]select ename,sal,deptno from emp where sal> any (select sal from emp where deptno=30)
[color=#362e2b]--部门岗位相同的员工
[color=#362e2b]select *from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
[color=#362e2b]--如何查询高于自己部门平均工资员工的信息(查询不要用as指定一个表)
[color=#362e2b]select a2.empno,a2.ename,a2.job,a2.sal,mysal from emp a2,(select avg(sal) mysal ,deptno from emp group by deptno)a1
[color=#362e2b]where a1.deptno=a2.deptno and a2.sal>a1.mysal;
[color=#362e2b]--分页查询(rownum 行号,是变化的,是oracle分配的)
[color=#362e2b]select rownum,emp.* from emp;
[color=#362e2b]--把查询结果当成一张表 复杂的查询
[color=#362e2b]---创建数据库dbca

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】