Oracle学习04【持续更新】

前端之家收集整理的这篇文章主要介绍了Oracle学习04【持续更新】前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

Oracle语句继续学习中。。。。。。

打开sql执行时间的开关。
set time on;//显示当前时间
set timing on;//显示sql执行的时间
关闭sql执行时间的开关,默认是关闭的。
set time off;
set timing off;//关闭sql执行的时间

1.查询10号部门和20号部门的员工信息,使用集合运算,并集.

1)合二为一:
select * from emp where deptno=10
union
select * from emp where deptno=20;
union二个集合中的公共部门,只取一次
当集合运算和多表查询都可以完成任务时,最好选用:多表查询
【多表查询】->子查询->集合 .

  1. sql> select * from emp where deptno=10 union select
  2. 2 * from emp where deptno=20;
  3.  
  4. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  5. ----- ---------- ---------- ----- ------------ ----- ----- ------
  6. 7369 SMITH CLERK 7902 17-12月-80 800 20
  7. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  8. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  9. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  10. 7839 KING PRESIDENT 17-11月-81 5000 10
  11. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  12. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  13. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  14.  
  15. 已选择8行。
2.查询工资在1000-2000和1500-2500之间的员工信息(方式一:使用集合运算,交集)

select * from emp where sal between 1000 and 2000
intersect
select * from emp where sal between 1500 and 2500;

  1. 22:08:20 sql> select * from emp where sal between 1000 and 2000
  2. 22:10:45 2 intersect
  3. 22:10:53 3 select * from emp where sal between 1500 and 2500;
  4.  
  5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  6. ----- ---------- ---------- ----- ------------ ----- ----- ------
  7. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  8. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  9.  
  10. 已用时间: 00: 00: 00.01
3.查询工资在1000-2000和1500-2500之间的员工信息(方式二:使用行过滤)
select *from empwhere (sal between 1000 and 2000) and (sal between 1500 and 2500);
  1. 22:11:21 sql> select * from emp where (sal between 1000 and 2000) and (sal betwe
  2. en 1500 and 2500);
  3.  
  4. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  5. ----- ---------- ---------- ----- ------------ ----- ----- ------
  6. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  7. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  8.  
  9. 已用时间: 00: 00: 00.00
4.查询工资在1000-2000,但不在1500-2500之间的员工信息,使用集合运算,差集
select ename "姓名",sal "工资" from emp where sal between 1000 and 2000
minus
select ename,sal from emp where sal between 1500 and 2500;
  1. 22:12:42 sql> select ename "姓名",sal "工资" from emp where sal between 1000 and
  2. 2000
  3. 22:14:43 2 minus
  4. 22:14:43 3 select ename,sal from emp where sal between 1500 and 2500;
  5.  
  6. 姓名 工资
  7. -------------------- ----------
  8. ADAMS 1100
  9. MARTIN 1250
  10. MILLER 1300
  11. WARD 1250
  12.  
  13. 已用时间: 00: 00: 00.00
  1.  
  1. <span style="font-family: Arial,Helvetica,sans-serif; background-color: rgb(255,255,255);">select *from emp</span>
where (sal between 1000 and 2000) and (sal not between 1500 and 2500);
  1. 22:15:49 2 where (sal between 1000 and 2000) and (sal not between 1500 and 25
  2. 00);
  3.  
  4. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  5. ----- ---------- ---------- ----- ------------ ----- ----- ------
  6. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  7. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  8. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  9. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  10.  
  11. 已用时间: 00: 00: 00.00
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
创建新表:

创建新表new_emp,复制emp表中的数据
create table new_empasselect * from emp;

向emp表中插入一条记录,注意维护表完整性约束(方式一)
insert into empvalues(4444,'JACK','IT',7788,sysdate,4000,100,40);

向emp表中插入一条记录,注意维护表完整性约束(方式二)
insert into emp(ename,empno,job,mgr,hiredate,sal,deptno,comm)values('MARRY',5555,3000,40,100);

  1. sql> select * from emp;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 4444 JACK IT 7788 22-6 -16 4000 100 40
  6. 5555 MARRY IT 7788 22-6 -16 3000 100 40
  7. 7369 SMITH CLERK 7902 17-12月-80 800 20
  8. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  9. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  10. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  11. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  12. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  13. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  14. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  15. 7839 KING PRESIDENT 17-11月-81 5000 10
  16. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  17. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  18. 7900 JAMES CLERK 7698 03-12月-81 950 30
  19. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  20. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  21.  
  22. 已选择16行。
向emp表中插入NULL值,显示插入
insert into emp(ename,comm)
values('SISI',6666,3500,NULL);
向emp表中插入NULL值,隐式插入
insert into emp(ename,deptno)
values('SOSO',7777,40);

插入成功:

  1. sql> select * from emp;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 4444 JACK IT 7788 22-6 -16 4000 100 40
  6. 5555 MARRY IT 7788 22-6 -16 3000 100 40
  7. 6666 SISI IT 7788 22-6 -16 3500 40
  8. 7777 SOSO IT 7788 22-6 -16 3500 40
使用&占位符,动态输入值,&可以运用在任何一个DML语句中,在values子句中使用
insert into emp(ename,comm)
values('&ename',&empno,'&job',&mgr,&hiredate,&sal,&deptno,&comm);
使用&占位符,动态输入值,&可以运用在任何一个DML语句中,在from子句中使用
select * from &emp;
使用&占位符,动态输入值,&可以运用在任何一个DML语句中,在select子句中使用
select empno,ename,&no from emp;

将'SMITH'的工资增加20%
update emp set sal=sal*1.2where ename='SMITH';

  1. sql> select * from emp;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 4444 JACK IT 7788 22-6 -16 4000 100 40
  6. 5555 MARRY IT 7788 22-6 -16 3000 100 40
  7. 6666 SISI IT 7788 22-6 -16 3500 40
  8. 7777 SOSO IT 7788 22-6 -16 3500 40
  9. 7369 SMITH CLERK 7902 17-12月-80 960 20
将'SMITH'的工资设置为10号部门的平均工资
子:select avg(sal) from emp where deptno=10;
主:update emp set sal=trunc(2916.66667,0) where ename='SMITH';
update emp
set sal=(select avg(sal) from emp where deptno=10)
where ename='SMITH';
  1. sql> select * from emp;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 4444 JACK IT 7788 22-6 -16 4000 100 40
  6. 5555 MARRY IT 7788 22-6 -16 3000 100 40
  7. 6666 SISI IT 7788 22-6 -16 3500 40
  8. 7777 SOSO IT 7788 22-6 -16 3500 40
  9. 7369 SMITH CLERK 7902 17-12月-80 2917 20
删除无佣金的员工
delete from emp where comm is null;
  1. sql> delete from emp where comm is null;
  2.  
  3. 删除12行。
  4.  
  5. 已用时间: 00: 00: 00.00
  6. sql> select * from emp;
  7.  
  8. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  9. ----- ---------- ---------- ----- ------------ ----- ----- ------
  10. 4444 JACK IT 7788 22-6 -16 4000 100 40
  11. 5555 MARRY IT 7788 22-6 -16 3000 100 40
  12. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  13. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  14. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  15. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  16.  
  17. 已选择6行。
  18.  
  19. 已用时间: 00: 00: 00.01
删除工资比所有部门平均工资小的员工
delete
from emp
where sal < (
select min(avg(sal))
from emp
group by deptno
--1567元
);
  1. sql> delete
  2. 2 from emp
  3. 3 where sal < (
  4. 4 select min(avg(sal))
  5. 5 from emp
  6. 6 group by deptno
  7. 7 --1567
  8. 8 );
  9.  
  10. 删除2行。
  11.  
  12. 已用时间: 00: 00: 00.00
  13. sql> select * from emp;
  14.  
  15. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  16. ----- ---------- ---------- ----- ------------ ----- ----- ------
  17. 4444 JACK IT 7788 22-6 -16 4000 100 40
  18. 5555 MARRY IT 7788 22-6 -16 3000 100 40
  19. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  20. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  21.  
  22. 已用时间: 00: 00: 00.01
删除emp表
drop table emp;
  1. sql> drop table emp;
  2.  
  3. 表已删除
  4.  
  5. 已用时间: 00: 00: 00.55
  6. sql> select * from emp;
  7. select * from emp
  8. *
  9. 1 行出现错误:
  10. ORA-00942: 表或视图不存在
  11.  
  12.  
  13. 已用时间: 00: 00: 00.00
根据new_emp表,创建emp表的结构,但不会插入数据
create table empasselect * from new_emp where 1=2;
  1. sql> create table emp
  2. 2 as
  3. 3 select * from new_emp where 1=2;
  4.  
  5. 表已创建。
  6.  
  7. 已用时间: 00: 00: 00.08
  8. sql> select * from emp;
  9.  
  10. 未选定行
  11.  
  12. 已用时间: 00: 00: 00.00
向emp表,批量插入new_emp表中部门号为10的员工信息
insert into emp
select *from new_empwhere deptno=10;
  1. sql> insert into emp
  2. 2 select * from new_emp where deptno=10;
  3.  
  4. 已创建3行。
  5.  
  6. 已用时间: 00: 00: 00.03
  7. sql> select * from emp;
  8.  
  9. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  10. ----- ---------- ---------- ----- ------------ ----- ----- ------
  11. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  12. 7839 KING PRESIDENT 17-11月-81 5000 10
  13. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  14.  
  15. 已用时间: 00: 00: 00.00
删除emp表
drop table emp;
依据new_emp表,创建emp表,且emp表只包括empno,ename字段
create table emp
as
select empno,ename from new_emp where 1!=1;
  1. sql> create table emp as select empno,ename from new_emp where 1!=1;
  2.  
  3. 表已创建。
  4.  
  5. 已用时间: 00: 00: 00.01
向emp表,批量插入emp表中部门号为10的员工信息
insert into emp(empno,ename)
select empno,ename
from new_emp
where deptno=10;
  1. sql> insert into emp(empno,ename)
  2. 2 select empno,ename
  3. 3 from new_emp
  4. 4 ;
  5.  
  6. 已创建14行。
  7.  
  8. 已用时间: 00: 00: 00.03
  9. sql> select * from emp;
  10.  
  11. EMPNO ENAME
  12. ----- ----------
  13. 7369 SMITH
  14. 7499 ALLEN
  15. 7521 WARD
  16. 7566 JONES
  17. 7654 MARTIN
  18. 7698 BLAKE
  19. 7782 CLARK
  20. 7788 SCOTT
  21. 7839 KING
  22. 7844 TURNER
  23. 7876 ADAMS
  24. 7900 JAMES
  25. 7902 FORD
  26. 7934 MILLER
  27.  
  28. 已选择14行。
删除今天所创建的新表new_emp

drop table new_emp;

  1. sql> drop table new_emp;
  2.  
  3. 表已删除
  4.  
  5. 已用时间: 00: 00: 00.03

猜你在找的Oracle相关文章