oracle实战练习--初级

港控/mmm° 2022-05-15 01:23 339阅读 0赞

> 前言：分享是一种坚持，分享给大家，也分享给自己！不管别人怎么说，怎么做，勇敢做自己，按照自己的心去做，虽然会碰到各种各样的挫折，但人生就是这样，不经历风雨，怎么能见彩虹。oracle是一门大学问，一辈子也学不完，博主这里也是对 oracle的知识掌握的不够好，大家一起共同进步学习。本篇文章适合初级的学习练习。

在学习本篇的时候，还是需要一点相应的数据库知识，前提已经安装了oracle数据，如果不知道怎么安装可以参考博主的文章：也是有介绍的！！[https://blog.csdn.net/qq\_30764991/article/details/80183754][https_blog.csdn.net_qq_30764991_article_details_80183754]

那么博主也在这里老话长谈，简单的再介绍下oracle。

Oracle 是殷墟出土的甲骨文(oracle bone inscriptions)的英文翻译的第一个单词 Oracle 公司是全球最大的信息管理软件及服务供应商，成立于1977年，总部位于美国加州 Redwood shore Oracle 公司因其复杂的关系数据库产品而闻名。Oracle的关系数据库是世界第一个支持SQL语言的数据库

Oracle数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置)。

### oracle的连接方式：5种方式 ###

sqlplus

sql develop

oracle提供的企业web页面端

以上三种是oracle官方提供的：

第三方工具：plsql develop

navicat  也支持了 ，企业实际常用第三方提供的plsql develop.所以博主这里也是基于plsql develop 做一个简单的演示：

### Oracle用户权限介绍： ###

oracle默认提供三个用户sys  、system、scott,但tiger是锁定的，需要通过sys 受权开启。

给scott授权：**grant create session to scott;**

**当然你也可以自己创建用户：**

语法\[创建用户\]： create user 用户名 identified by 口令\[即密码\]；

**如：**create user zhangsan identified by 123456;

授权登录：**grant create session to zhangsan;**

**当然还需要授权其他操作....**

博主这里就全部授权了，如果是多个用户呢？我们是可以直接给个相应的角色权限，这样方便，不管有多个用户，反正都是一样的如开发人员，你可编辑一个如worker ，然后授于相应的增删修改等需要的权限。

![70][]

实战练习如下：首先创建表：

员工表建表语句:

CREATE TABLE EMP (  
ENAME VARCHAR2(30),  
EMPNO NUMBER(5),  
DEPTNO NUMBER(5),  
JOB VARCHAR2(20),  
HIREDATE DATE,  
COMM NUMBER(6,2),  
SAL NUMBER(6,2)  
);

部门表建表语句:

CREATE TABLE DEPT (  
DNAME VARCHAR2(30),  
DEPTNO NUMBER(5),  
LOC VARCHAR2(50)  
);

员工表插入数据:

INSERT INTO EMP VALUES ('Zhou润发', 10001, 10, '办事员', TO\_DATE('20161130131322', 'YYYYMMDDHH24MISS'), 2500, 2400);  
INSERT INTO EMP VALUES ('Liu德华', 10002, 10, '办事员', TO\_DATE('20170105131334', 'YYYYMMDDHH24MISS'), 1800, 2250);  
INSERT INTO EMP VALUES ('Li连杰', 20001, 20, '办事员', TO\_DATE('20170313131339', 'YYYYMMDDHH24MISS'), 2200, 2350);  
INSERT INTO EMP VALUES ('xiang华强', 20000, 20, '经理', TO\_DATE('20160130131343', 'YYYYMMDDHH24MISS'), 3980, 3500);  
INSERT INTO EMP VALUES ('Zhang柏芝', 30001, 30, '办事员', TO\_DATE('20170314131346', 'YYYYMMDDHH24MISS'), 1300, 2200);  
INSERT INTO EMP VALUES ('成long', 10000, 10, '经理', TO\_DATE('20151031133724', 'YYYYMMDDHH24MISS'), 4800, 4000);  
INSERT INTO EMP VALUES ('zhang子怡', 30000, 30, '经理', TO\_DATE('20161011133915', 'YYYYMMDDHH24MISS'), 3500, 3480);  
INSERT INTO EMP VALUES ('zhang家辉', 20002, 20, '办事员', TO\_DATE('20170330134519', 'YYYYMMDDHH24MISS'), 1600, 2000);  
INSERT INTO EMP VALUES ('Liu亦菲', 30002, 30, '驾驶员', TO\_DATE('20170330135457', 'YYYYMMDDHH24MISS'), NULL, 2500);  
INSERT INTO EMP VALUES ('peng于晏', 20003, 20, '驾驶员', TO\_DATE('20170411135625', 'YYYYMMDDHH24MISS'), NULL, 2500);  
INSERT INTO EMP VALUES ('Zhang涵予', 10003, 10, '驾驶员', TO\_DATE('20170408135706', 'YYYYMMDDHH24MISS'), NULL, 2500);

部门表插入数据:

INSERT INTO DEPT VALUES ('市场部', 10, '北京');  
INSERT INTO DEPT VALUES ('公关部', 20, '上海');  
INSERT INTO DEPT VALUES ('研发部', 30, '南京');

**表结构数据图如下：**

两张表已全部建好,自己用查询语句查看一下表结构和数据是否没问题.

Select \* From EMP Select \* From DEPT

![70 1][]

![70 2][]

下面是题目:可以复制下来做, 做完再和我做答案对照一下, 当然, 有的题目可以用多种方法去做, 看你自己选择了.

**还有一点需要注意的是:我是用的可视化界面输入的语句,别名中文不需要加引号,一开始我加引号老是报错,后来无意间去掉反而好了,不知道为什么.我开始学的时候,别名使用中文是需要加引号的,看你实际的情况吧!!**

**题目:**

\--1.选择30部门的职员  
\--2.列出所有办事员的姓名、编号和部门  
\--3.找出奖金高于薪资的雇员  
\--4.找出佣金高于薪金60%的雇员  
\--5.找出部门10中所有经理和部门20中的所有办事员的详细资料  
\--6.找出既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料  
\--7.找出收取佣金的雇员的不同工作  
\--8.找出不收取佣金或收取的佣金低于2000的雇员  
\--9.找出各月最后一天受雇的所有雇员  
\--10.找出早于1年之前受雇的雇员  
\--11.显示只有首字母大写的所有雇员的姓名  
\--12.显示正好为7个字符的雇员姓名  
\--13.显示不带有'Z'的雇员姓名  
\--14.显示所有雇员的姓名的前三个字符  
\--15.显示所有雇员的姓名，用小写 z 替换所有大写 Z .  
\--16.显示所有雇员的姓名以及满1年服务年限的日期  
\--17.显示雇员的详细资料，按姓名排序  
\--18.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面  
\--19.显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序  
\--20.显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面  
\--21.显示在一个月为30天的情况下所有雇员的日薪金  
\--22.找出在（任何年份的）1月受聘的所有雇员  
\--23.对于每个雇员，显示其加入公司的天数  
\--24.显示姓名字段的任何位置，包含 "i" 的所有雇员的姓名  
\--25.以年、月和日显示所有雇员的服务年限

**答案:**

1--

select ename,deptno from emp where deptno=30;  
2--

select e.ename,e.empno,d.dname,e.job from emp e,dept d where e.deptno=d.deptno and e.job='办事员';  
3--

select \* from emp where comm>sal order by comm desc;

4--

select \* from emp where comm>sal\*0.6;  
5.1--

select \* from emp e,dept d where e.deptno=10 and e.job='经理' and e.deptno=d.deptno  
union all  
select \* from emp e,dept d where e.deptno=20 and e.job='办事员' and e.deptno=d.deptno;  
5.2--

select \* from emp e,dept d where (e.deptno=10 and e.job='经理' and e.deptno=d.deptno)  
or (e.deptno=20 and e.job='办事员' and e.deptno=d.deptno);  
6--

select \* from emp e,dept d where e.sal>=2000 and e.job<>'办事员' and e.job<>'经理' and e.deptno=d.deptno;  
7--

select distinct job from emp where comm>0;  
8--

select ename,nvl(comm,0) comms from emp where nvl(comm,0)<2000;  
9--

select \* from emp where hiredate=last\_day(hiredate);  
10.1--

select \* from emp where months\_between(sysdate,hiredate)>12;  
10.2--

select \* from emp where hiredate 11--

select \* from emp where ename=initcap(ename);  
12--

select \* from emp where length(ename)=7;  
13--

select \* from emp where instr(ename,'Z')=0;  
14--

select substr(ename,0,3) from emp  
15--

select replace(ename,'Z','z') from emp  
16--

select ename,add\_months(hiredate,12) from emp  
17--

select \* from emp e,dept d where e.deptno=d.deptno order by e.ename  
18.1--

select ename,trunc(months\_between(sysdate,hiredate)/12,0) 服务年限 from emp order by 服务年限 desc  
18.2--

select ename,hiredate from emp order by hiredate  
19--

select ename,job,sal from emp order by job desc,sal asc  
20.1--

select ename,extract(year from hiredate) 年份,extract(month from hiredate) 月份 from emp order by 年份,月份  
20.2--

select ename,to\_char(hiredate,'yyyy') 年份,to\_char(hiredate,'mm') 月份 from emp order by hiredate  
21--

select ename,sal/30,trunc(sal/30,1),floor(sal/30),ceil(sal/30) from emp  
22.1--

select \* from emp where extract(month from hiredate)=1  
22.2--

select \* from emp where to\_char(hiredate,'mm')='01'  
23--

select ename,floor(sysdate-hiredate) 入职天数 from emp order by 入职天数 desc  
24.1--

select ename from emp where ename like '%i%';  
24.2--

select ename from emp where instr(ename,'i')>0  
25--

select ename,floor(months\_between(sysdate,hiredate)/12) 服务总年数,

floor(months\_between(sysdate,hiredate)) 服务总月数,

floor(sysdate-hiredate) 服务总天数 from emp order by 服务总天数;

[https_blog.csdn.net_qq_30764991_article_details_80183754]: https://blog.csdn.net/qq_30764991/article/details/80183754
[70]: /images/20220515/aad94df6592949f8888e58e8d1af309b.png
[70 1]: /images/20220515/275d955a3f6f488b89e44a95e1dbc886.png
[70 2]: /images/20220515/ed10ce82a8c04fef9f7383efc4d066e9.png