前言

本文将本学期九次自己做的数据库作业归纳整理，如果发现有错误，欢迎给我留言指正哦。

第一次作业

1. 解锁scott用户，连接scott用户，用数据字典user_tables查看系统自带的四张表。

- alter user scott account unlock;
- conn scott/tiger
- /*(更改密码)*/
- conn scott/123
- select table_name  from  user_tables;

2. 在管理员用户下创建一个永久表空间和一个临时表空间

- conn / as sysdba
- create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;
- create temporary tablespace temptest1_tb tempfile 'tempfile.dbf' size 10m;

3. 用数据字典dba_tablespaces 查看管理员账户下的所有表空间。

- select tablespace_name from dba_tablespaces;

4. 用管理员用户SYS创建一个普通用户user01，（选做：将刚才创建的永久表空间，临时表空间制定给新用户）

- create user user01 identified by 123 default tablespace test1_tablespace temporary tablespace temptest1_tb;

5. 给4小题创建的普通用户user01分配权限（赋予连接的角色），让他能够登陆成功。

- grant connect to user01;

6. 更改普通用户user01的密码。

- alter user user01 identified by 123;

7. 创建一个用户user02 ,创建一个角色 （manager），给角色授权创建表的权限，然后将角色manager 同时赋予给用户user01和user02.

- create role manager;
- create user user01 identified by 123;
- create user user02 identified by 123;
- grant create table to manager;
- grant manager to user01,user02;

8. 将user01 的角色manager和connect 收回。

- revoke manager from user01;
- revoke connect from user01;

9. 将角色manager的权限收回。

- revoke create table from manager;

10. 删除角色manager ，删除用户user02(连同用户相关的数据一起删除)

- drop role manager;
- drop user user02 cascade;

第二次作业

一：如何在一个新用户中创建数据表？

1. 创建一个新用户user01 ,（选做，并为其制定默认表空间和临时表空间）

- create tablespace test1_moren datafile 'test1_moren.dbf' size 10m;
- create temporary tablespace temptest tempfile 'temptest.dbf' size 10m;
- create user user01 identified by 123 default tablespace test1_moren temporary tablespace temptest

2. 给新用户user01 授予标准角色 connect

- grant connect to user01;

3. 创建一个角色manager，赋予角色创建表的权限(create table)

- create role manager;
- grant create table to manager;

4. 将角色赋予给用户user01

- grant manager to user01;

5. 在用户user01 中创建两张表（学生表、老师表）：

学生表要求如下：
①表名符合语法规则，合理选择字段数据类型。
②字段包括学生编号、姓名、性别，出生日期。
③使用 desc 指令 查看学生表结构

- conn user01/123
- create table student( id number(4),name varchar2(20),gender char(2),birthdate date);
- desc student

老师表要求如下：
①表名符合语法规则，合理选择字段数据类型。
②字段包括学生编号、姓名、性别，入职日期。
③使用 desc 指令 查看教师表结构

- create table teacher(id number(4),name varchar2(20),gender char(2),hiredate date );
- desc teacher

二、数据字典的练习

1. 利用学过的数据字典，查看scott用户、user01下面的所有的表名。

- select table_name from user_tables;
- alter user scott account unlock;
- conn scott/123
- select table_name from user_tables;

2. 利用学过的数据字典，查看所有的对象权限。

- select*from system_privilege_map; 

3. 利用数据字典查看所有的用户名。

- select username from all_users;

第三次作业

一，表数据插入练习

1. 创建一个普通用户user02,如果用户已经存在，先删除用户再重新创建。

- create user user02 identified by 123;

2. 再用户user02下，创建一张学生表（表名自定义），表包括学号、姓名、性别、入学日期四个字段。

- grant connect,resource to user02;
- conn user02/123;
- create table student(idnum number(4),name varchar2(20),gender char(2),birthdate date);

3. 向学生表插入一条完整的记录，入学时间为系统时间。

- insert into student values(1,'小王','男',sysdate);

4. 向学生表插入一条完整的记录，入学时间自定义（要求使用to_date()函数转换）。

- insert into student values(2,'小红','女',to_date('2001-2-2','yyyy-mm-dd'));

5. 向学生表插入一条记录，该条记录只包括，学号和姓名两个字段的数据。

- insert into student(idnum,name) values(3,' 王伟');

6. 设置性别字段的默认值为‘男’。

- alter table student modify(gender default '男');

7. 修改学生表入学日期字段，为该字段设置默认值为系统时间。

- alter table student modify(birthdate date default sysdate);

8. 向学生表插入一条记录，该条记录只包括，学号和姓名两个字段的数据。

- insert into student(idnum,name) values(4,'王二');

9. 查看学生表的全部数据。

- select * from student;


     IDNUM NAME                 GE BIRTHDATE
---------- -------------------- -- --------------
         1 小王                 男 27-6月 -21
         2 小红                 女 02-2月 -01
         3 王伟
         4 王二

二、表数据的复制

1. 复制一张学生表表，表名为学生表名_copy。

- create table student_copy as select * from student;

2. 创建一个新表 test,该表包括学号、姓名连个字段。

- create table test(idnum number(4),name varchar2(20));

将学生表，学号和姓名两个字段的所有数据，复制到test表。

- insert into test(idnum,name) select idnum,name from student;

三、表数据的更新、删除

1. 将test 表中的姓名字段数据，全部更新为‘张三’。

- update test set name='张三';

2. 将test表中第3条记录，姓名字段更新为李四。

- update test set name='李四' where idnum=3;

3.查询显示当前表的全部数据（要求将结果一并复制过来）

- select * from test;
     IDNUM NAME
---------- --------------------
         1 张三
         2 张三
         3 李四
         4 张三

4. 删除test表中，姓名为‘张三’全部记录。

- delete from test where name='张三';

5. 将test 表中剩余数据，全部截断。

- truncate table test;

6. 删除test表。

- drop table test;

四、Oracle SQL developer 软件 安装练习。

1. JDK 的安装

下载Java，我这里选择的java8

安装

安装完毕之后找到java的安装路径C:\Program Files\Java\jre1.8.0_231，然后复制路径。

2.Oracle SQL developer 软件 及使用

解压sqldeveloper。

双击exe文件

如果弹出输入框，粘贴刚才复制的路径，没弹出则不管。

单击左上角的“+”号。

登录scott账户，如果登录不了，先在CMD下解锁scott再连接。

登录成功！

第四次作业

一、连接登录scott用户完成以下操作

1. 创建学生stu表（包括至少四个字段,学号、姓名、性别、注册日期）的同时添加主键约束（列级约束）

- conn scott/123;
- create table stu (idnum number(4),name varchar2(20),gender char(2),birthdate date,constraint idnum_pk primary key(idnum));

2. 创建表stu1的同时添加表级主键约束，该主键约束由姓名和id两个字段同时作用（表级约束）

- create table stu1 (idnum number(4),name varchar2(20),gender char(2),birthdate date,constraint idnum1_pk primary key(idnum,name));

3. stu表已经存在，添加设置姓名字段为非空，插入一条记录测试效果。

- alter table stu modify (name varchar2(20) not null);
- insert into stu(idnum) values (1)
/*下面为输出*/
insert into stu(idnum) values (1)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("USER02"."STU"."NAME")

4. stu表已经存在，为性别字段添加检查约束，要求该字段的值只能是'男'或者'女'，插入一条记录测试效果。

- alter table stu add constraint ck_gender check(gender='男' or gender='女');
- insert into stu values(2,'王二','蓝'，sysdate);
/*下面为输出*/
insert into stu values(2,'王二','蓝'，sysdate)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (USER02.CK_GENDER) 

5. stu表已经存在，设置注册日期的默认值为系统时间，插入一条记录测试效果。

- alter table stu modify(birthdate default sysdate);
- insert into stu(idnum,name,gender) values(3,'小红','男');
已创建 1 行。

6. 外键约束练习，理解主从表关系。

①创建主表 学校部门表 department，要求该表至少有两个字段，部门编号和部门名称

- create table department(id number(4)primary key,name varchar2(20));

②创建从表student，并在从表中添加指向主表的外键约束

- create table student(idnum constraint f_stu references department(idnum),name varchar2(20));

7. 使用数据字典user_constraints 查看stu表的所有约束的名称，类型、状态。

- select constraint_name,constraint_type,status from user_constraints where table_name='STU';

8. 暂时让stu表的检查约束失效（禁用），插入一条记录测试效果。

- alter table stu disable constraint ck_gender;
- insert into stu values(4,'小明','蓝',sysdate);
已创建 1 行。

9. 删除表stu1中的约束（要求使用删除主键约束的方法）。

- alter table stu drop primary key;

10. 查询表stu的全部记录，并将查询结果保存。

- select * from stu;
     IDNUM NAME                 GE BIRTHDATE
---------- -------------------- -- --------------
         3 小红                 男 27-6月 -21
         4 小明                 蓝 27-6月 -21

二、在SQL developer工具中，查看管理STU表中的约束

第五次作业

一、基础查询

1. 查询部门表 dept 中的全部数据。

- conn scott/123;
- select * from dept;

2. 查询员工表中员工的，姓名，职位，入职日期。

- select ename,job,hiredate from emp;

3. 利用去除重复行关键字，查询出公司的全部职位。

- select distinct job from emp;

4. 在回话中设置自定艺日期格式为(yyyy-mm-dd)。

- alter session set nls_date_format='yyyy-mm-dd';

二、COL指令的使用

1. 设置字段的显示格式，将ename,job,sal,hiredate 字段分别设置成为中文的，姓名，职位，月薪，入职日期。

- col ename heading 姓名;
- col job heading 职位;
- col sal heading 月薪;
- col hiredate heading 入职日期;

2. 调整字段的显示长度，将姓名字段的显示长度设置为6个字节。

- col ename format a6;

3. 设置数值类型字段的显示格式，将月薪字段的显示格式设置为四位整数+加小数点后面保留三位。

- col sal format 9999.999;

4. 清楚列格式

- clear col

三、算数运算符的使用

1. 查询员工表中，所有员工的年薪（年薪的计算方式是，年薪*12个月+半个月的工资）。

- select ename,sal*12+sal/2 年薪 from emp;

2. 设置字段的别名，查询员工表中员工的，姓名，职位，收入、入职日期。

- select ename 姓名,job 职位,sal 收入,hiredate 入职日期 from emp;

3. 连接符和字符串的使用，将查询结果用一句完整的话描述，并将表头设置为“员工基本信息”。

- select '员工'||ename||'的工作职位是：'||job||'入职时间为：'||hiredate||'月收入是'||sal||'美元' 员工信息表 from emp;

4. 查询员工编号为7698 的员工的所有信息。

- select * from emp where empno=7698;

5. 查询入职时间为17-NOV-81 的员工的姓名和职位。

- alter session set nls_date_format='DD-MON-RR';
- select ename,job from emp where hiredate='17-11月-81' ;

6. 查询名字叫SCOTT的员工的所有信息。

- select * from emp where ename='SCOTT';

7. 查询工资收入低于1500的所有员工的姓名，职位、入职时间。

- select * from emp where sal<1500;

8. 查询工资收入在1000-3000之间的所有员工姓名。

- select ename from emp where sal between 1000 and 3000;

第六次作业

一、基础查询

1. 从员工表中查询员工姓名，职位，要求查询结果ename，job字段值全部小写。

- select lower(ename),lower(job) from emp;

2. 从员工表中查询员工姓名和职位，使得ename 首字母大写其余小写，职位显示为大写。

- select initcap(ename),upper(job) from emp;

3. 使用sql语句按要求输出字符串格式如将源字符串‘hello world hello oracle'，转换为‘Hello World Hello Oracle’。

- select concat(concat(initcap('hello '),initcap('word ')),concat(initcap('hello '),initcap('oracl '))) from dual;
- select initcap('hello world hello oracle') from dual;

4. 查询员工表所有职员的姓名和职位，按以下格式输出查询结果（例：员xxx的工作职位是：xxx），要求使用学过的两种方法。

- select '员工'||ename||'的工作职位是：'||job from emp;
- select concat(concat('员工'，ename)，concat（'的工作职位是：',job)） from emp;

5. 使用sql 语句操作源字符串‘hello oracle’（注：虚拟表dual）

①从源字符串第七位开始截取出一个长度为6的子字符串。

- select substr('hello oracle',7,6) from dual;

②从源字符串倒数第3位开始截取出一个长度为3的子字符串。

- select substr('hello oracle',-3,3) from dual;

③从源字符串开头截取出一个长度为5的字符串。

- select substr('hello oracle',1,5) from dual;

6. 源字符串为‘hello world hello world’，按要求完成以下题目。

①返回源字符串的长度。

- select length('hello world hello world') from dual;

②从头开始遍历源字符串，返回子字符串'world'第一次出现的位置。

- select instr('hello world hello world','world',1,1) from dual;

③从源字符串第七位开始遍历，返回子字符串‘world’第一次和第二次出现的位置。

- select instr('hello world hello world','world',7,1) 第一次,instr('hello world hello world','world',7,2) 第二次 from dual;

7. 从员工表中查询所有员工的姓名，要求姓名显示长度为10字节，左对齐显示。

- select rpad(ename,10,' ') from emp;

8. 从员工表中查询所有员工的职位，要求职位显示长度为10字节，右对齐显示。

- select lpad(ename,10,' ')from emp;

9. 字符串替换将源字符串'hello,world' 中的‘world’替换为Oracle。

- select replace('hello,world','world','Oracle') from dual;

二、数值函数练习

使用四舍五入函数，将25.456分别保留到十位数，整数，小数点后一位，小数点后2位

- select round(25.456,-1) from dual;
- select round(25.456) from dual;
- select round(25.456,1) from dual;
- select round(25.456,2) from dual;

2. 使用截断函数，将25.456分别保留到十位数，整数，小数点后一位，小数点后2位。

- select trunc(25.345,-1) from dual;
- select trunc(25.456) from dual;
- select trunc(25.345,1) from dual;
- select trunc(25.456,2) from dual;

3. 使用取整函数将25.9999 和25.00001 分别向上，向下取整。

- select ceil(25.9999) from dual; 
- select floor(25.00001)from dual;

4. 常用的计算函数

①绝对值函数使用

- select abs(-2) from dual;

②次幂函数使用

- select power(3,3) from dual;

③开平方根函数使用。

- select sqrt(3) from dual;

5. 从员工表中，查询出员工编号为偶数的所有员工信息。

- select* from emp where mod(deptno,2)=0;

三、日期函数

1. 查询出今天，明天，后天，和昨天的日期。

- select sysdate from dual;
- select sysdate+1 from dual;
- select sysdate+2 from dual;
- select sysdate-1 from dual;

2. 将日期格式设置为yyyy-mm-dd 的格式。

- select to_char(sysdate,'yyyy-mm-dd')from dual; 

3. 从员工表中查询所有员工的工作年限，要求结果四舍五入为整数。

- select round(months_between(sysdate,hiredate)/12) from emp;

4. 查询每位员工入职30年后的年份。

- select substr(to_char(add_months(hiredate,30*12),'yyyy-mm-dd'),1,4) from emp;

5. 查询本月的最后一天的日期

- select last_day(sysdate) from dual;

6. 查询每个月最后一天入职的员工

- select * from emp where hiredate=last_day(hiredate);

7. 查询下周二的日期。

- select next_day(sysdate,'星期二') from dual;

第七次作业

一、函数综合练习

1.从员工表中查询姓名中包含大写字母D的员工的所有信息。

- conn scott/123;
- select * from emp where ename like('%D%');

2.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料

- select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

4.查询部门员工姓名,姓名长度，工作，编号为10或20，入职日期在1981年5月1日之后的所有记录

- select ename,hiredate,length(ename)as 姓名长度,job from emp where (deptno=10 or deptno=20) and hiredate > to_date('1981-5-1','yyyy-mm-dd');

5.查询每个员工截止到现在一共入职多少天(保留整数)？

- select round(sysdate-hiredate)as 入职多少天 from emp;

6.转换字符'01-1月-95' 转换为日期类型，要求显示结果为yyyy-mm-dd 格式。

- select to_char(to_date('01-1月-95','dd-mon-rr'),'YYYY-MM-DD') 日期 from dual ;

7.查询部门10,20的员工截止到现在，工作了多少个月，入职的月份。

- select round(months_between(sysdate,hiredate))as 月数,extract(year from hiredate) 年份 from emp;

8.从员工表中找到各月倒数第3天受雇佣的所有员工信息。

- select * from emp where hiredate=last_day(hiredate)-2;

9.查询出正好为5个字符的所有员工的姓名

- select ename from emp where length(ename)=5;

10.员工表中找到员工姓名中不含字母"R"的员工所有信息。

- select * from emp where ename not like('%R%');

11.找出员工姓名中最后一个字母为"N"的员工姓名 (使用substr或者instr)

- select ename from emp where substr(ename,-1,1)='N';
- select ename from emp where instr(ename,'N')=length(ename);

12.从员工表中查询所有员工的姓名，月薪，年薪（13个月工资+comm 奖金）。

-  select sal as 月薪,sal*13+nvl(comm,0) 年薪 from emp;

13.显示入职满10年的员工的姓名和受雇日期。

- select ename,hiredate from emp where months_between(sysdate,hiredate)/12 > 10;

14.在员工表中查询出员工的工资，并计算应交税款：如果工0 资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。（要求分别使用case when 语句 和 decode（）函数）

- select sal,case trunc(sal/1000) when 0 then 0 when 1 then sal*0.1 when 2 then sal*0.15 else sal*0.2 end as 税率 from emp;
- select sal,decode(trunc(sal/1000),0,0,1,sal*0.1,2,sal*0.15,sal*0.2)  税率 from emp;

15.显示每个雇员的姓名、工资、职位，同时显示新的工资（新工资的标准为：办事员增长10%、销售人员增长20%、经理增长30%、其他职位的人增长50%

- select ename,sal,job,decode(job,'CLERK',sal+sal*0.1,'SALSEMAN',sal+sal*0.2,'MANAGER',sal+sal*0.3,sal+sal*0.5) 新工资 from emp;

第八次作业

一、函数综合练习

1. 列出每个部门最高、最低薪水的结果，要求显示部门号，最高工资，最低工资。

- conn scott/123;
- select deptno,max(sal) 最高工资,min(sal) 最低工资 from emp group by deptno;

2. 列出每个部门总工资、平均工资结果，要求显示部门号，总工资，平均工资。

- select deptno,sum(sal) 总工资,avg(sal) 平均工资 from emp group by deptno;

3. 统计员工表中一共有多少条记录。

- select count(*) from emp;

4. 查找雇员平均工资大于2500的部门的最高薪水和最低薪水。

-select deptno,max(sal) 最高薪水,min(sal) 最低薪水 from emp group by deptno having avg(sal)> 2500;

5. 查找雇员数超过5个的部门的员工姓名。

- select ename from emp where deptno in(select deptno from emp group by deptno having count(ename)>5);

6. 查询有津贴的员工人数。

- select count(ename) from emp where comm <>0;

7. 计算所有员工的平均补贴。

- select sum(comm)/count(*) from emp ;

8. 计算有津贴员工的平均补贴。

- select avg(comm) from emp where comm <>0;

9.查询统计职位相同员工的平均工资。

- select avg(sal) from emp group by job;

10.查询统计职位相同员工的工资总和。

- select sum(sal) from emp group by job;

11. 查询统计职位相同员工的平均工资、工资总和。(SAL+COMM)

- select job,sum(sal+nvl(comm,0))/count(*) 平均工资,sum(sal+nvl(comm,0)) 工资总和 from emp group by job;

12. 使用rollup 函数实现简单的工资报表，统计出员工表各个部门相同职位的工资总和、部门工资总和以及全部员工的总工资。

- select deptno,job,sum(sal) from emp group by rollup(deptno,job)

第九次作业

多表查询综合练习

1. 列出所有员工的姓名、部门名称和工资

- conn scott/123;
- select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

2. 查询emp表，dept表中雇员的编号、姓名、工作、部门名称。

- select e.deptno,e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno;

3. 列出所有部门的部门编号，部门名称，部门地址和部门人数

- select d.deptno,d.dname,d.loc,count(*) 部门人数 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname,d.loc;

4. 显示员工"SMITH"的姓名，部门名称

-select e.ename,d.dname from emp e,dept d where (e.deptno=d.deptno) and e.ename='SMITH';

5. 列出所有员工的姓名以及其直接上级的姓名

- select m.ename 上级, e.ename 员工   from emp e,emp m where e.mgr=m.empno(+);

6. 查询显示上级直接管理的所有下属员工姓名

- select m.ename 上级, wm_concat(e.ename)员工 from emp e,emp m where e.mgr=m.empno(+) group by m.ename;

7. 显示员工"KING"和"FORD"管理的员工姓名及其经理姓名

- select m.ename 上级 ,e.ename 员工 from emp e,emp m where e.mgr=m.empno and m.ename in ('KING','FORD')

8. 显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级

- select e.ename,d.dname,e.sal,s.grade from emp e,dept d,salgrade s where e.sal between s.losal and s.hisal and s.grade>4;

9. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

- select d.dname,e.* from emp e,dept d where d.deptno=e.deptno(+);

10. 列出各个部门的经理的最低薪金,显示部门名称，经理名称，和最低工资

- select d.dname,e.ename,min(e.sal) from emp e,dept d where e.deptno=d.deptno and job='MANAGER' group by d.dname,e.ename;

11. 列出至少有一位员工的多有部门

- select dname from dept where deptno in(select deptno from emp);

12. 列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

- select e.empno,e.ename,d.dname from emp e,emp m,dept d where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;