Oracle 学习（一）---数据库基本操作

Oracle查询工具的使用

1. 点击“开始”->“运行”，输入cmd命令进入DOS环境，然后执行SQL PLUS命令登录数据库，并使用CONNECT命令切换当前的连接用户，具体操作如下：

（1） SQL PLUS system/密码

（2） Connect sys/密码 as sysdba

（3） Alter user scott account unlock identified by tiger;

（4） Connect scott/tiger@orcl

（5） Exit

 

表的创建与管理

1.创建表并添加相应的约束。要求：

（1）创建名为student的表，表中各列要求如下：

字段名称	字段类型	大小	说明
Sno	CHAR	10	主键
Sname	VARCHAR2	8
Sex	CHAR	2	默认值为男，只能输入男或女
Birthday	DATE
Sdept	CHAR	20

 

（2）创建名为course的表，表中各列要求如下：

字段名称	字段类型	大小	说明
Cno	CHAR	10	主键
Cname	CHAR	30	唯一值
Ccredit	NUMBER	3

 

（2）创建名为score的表，表中各列要求如下：

字段名称	字段类型	大小	说明
Sno	CHAR	10	数据来自student
Cno	CHAR	10	数据来自student
grade	NUMBER	3	0~100

 

3. 增加、修改和删除字段，要求：

 

（1） 给student表增加一个memo字段，类型为varchar2(200)。

（2） 将memo字段的类型修改为varchar2(300)。

（3） 删除memo字段。

 

4. 向表中添加数据、更新数据、删除数据，并验证约束。要求：

 

（1） 使用insert into命令向三个表中分别插入若干行数据，验证主键约束、唯一性约束以及默认值约束。

（2） 使用update命令更新数据，验证外键约束。

（3） 使用delete命令删除数据。

 

5. 删除表。要求：

 

利用drop table语句删除表。

 SQL语句基本查询语句

二、实验内容：

使用scott下的emp表和dept表，完成以下操作：

1. 查询部门编号是20的员工信息。(大小写未改)

Select * from emp where deptno=’20’;

2. 查询工作为CLERK的员工的员工号、员工名和部门号。

    select empno,ename,deptno from emp where job='CLERK'

3. 查询奖金COMM高于工资sal的员工信息。

       Select * from emp where  COMM>SAL;

4. 查询奖金高于工资20%的员工信息。

      Select * from emp where  COMM>SAL*0.2;

 

5. 查询部门编号是10并且工作为MANAGER的员工和部门编号是20并且工作为CLERK的员工的信息。

select * from emp where deptno=’10’ and job =’MANAGER’ or deptno=’20’ and job=’CLERK’;

 

6. 查询工作不是MANAGER和CLERK，并且工资大于或等于2000的员工信息。

 

Select * from emp where JOB!=’MANAGER’ and JOB!=’CLERK’ and SAL>=’2000’;

7. 查询有奖金的员工信息。

    Select * from emp where COMM IS NOT NULL;

 

8. 查询没有奖金或奖金低于100的员工信息。

  Select * from emp where  COMM IS NULL or comm<’100’;

9. 查询最近两年入职的员工信息。

Select * from emp where (sysdate-hiredate)/365>=2;

10. 查询工龄大于或等于10年的员工信息。

Select * from emp where (sysdate-hiredate)/365>=10;

11. 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

Select INITACP(ename) from emp;

 

12. 查询员工名正好为6个字母的员工信息。

Select ename from emp where length(ename)=6;

13. 查询员工名字中不包含字母S的员工。

select * from emp where ename not like '%Ｓ%'

14. 查询员工姓名的第二个字母为M的员工信息。

Select * from emp where ename like ‘_M%’;

15. 查询所有员工姓名的前三个字符。

Select substr(ename,1,3) from emp;

 

16. 查询所有员工的姓名，如果包含字母s，则用S替换。

Select replace(name,’S’,’s’) from emp;

  

17. 查询员工的的姓名和入职日期，并按入职日期从先到后进行排序。

Select ename,hiredate from emp order by hiredate;

18. 显示所有员工的项目、工作、工资，按工作降序排序，若工作相同则按工资升序排序。

Select job,sal from emp order by sal job, sal ASC;

19. 显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序。

Select ename,

20. 查询每个部门中的员工数量、平均工资和平均工作年限。

Select count(ename),avg(sal),avg((sysdate-hiredate)/365) from emp group by deptno;

21. 查询各个部门的人数及平均工资。

Select count(empno),avg(sal) from emp group by deptno;

 

22. 查询各种工作的最低工资，并输出最低工资低于3000的工作名称。

Select min (sal),job from emp group by job having min(sal)<3000;

 

23. 查询各个部门中不同工种的最高工资。

Select max(sal) from emp group by deptno;

 

24. 统计各个工种的员工人数与平均工资。

Select count(empno),avg(sal) from emp group by job;

 select语句高级查询

根据oracle数据库scott下的emp表和dept表，完成下列操作：

1. 查询所有工种为CLERK的员工的姓名及其部门名称。

Select ename,dname from emp,dept where emp.deptno = dept.deptno and job =’CLERK’;

2. 查询所有部门及其员工信息，包括那些没有员工的部门。

        select dept.dname,emp.ename from dept.deptno=emp.deptno(+);

3. 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

Select dept.dname,emp.ename from emp,dept where emp.deptno=deptno(+);

4. 查询在SALES部门工作的员工的姓名信息。

Select ename from emp,dept where emp.deptno = dept.deptno and dname =’sales’;

5. 查询所有员工的姓名及其直接上级的姓名。

select e1.ename 员工，e2.ename 上级 from emp e1,emp e2 where e1.mgr= e2.empno(+);

6. 查询入职日期早于其上级领导的所有员工信息。

Select e1.ename 员工,e1.hiredate  员工入职日期，e2.ename 上级，e2.hiredate 上级入职日期 from emp e1,emp e2 where e1.mgr =e2.empno and e1.hiredate<e2.hiredate;

7. 查询从事同一种工作但不属于同一部门的员工信息。

Select distinct e1.ename,e1.job,e1.deptno from emp e1,emp e2 where e1.job=e2.job and e1.deptno<>e2.deptno;

8. 查询10号部门员工及其领导的信息。

         Select e1.ename 员工,e2.ename 上级，e2.deptno 部门 from  emp e1,emp e2 where e1.mgr = e2.empno and e2.deptno=10;

9. 使用UNION将工资大于2500的雇员信息与工作为ANALYST的雇员信息合并。

SQL> select * from emp where sal>2500 union select * from emp where job='ANALYST

';

     

10. 通过INTERSECT集合运算，查询工资大于2500，并且工作为ANALYST的雇员信息。

Select * from emp where sal>2500 intersect select * from emp where job=’ANALYST’;

11. 使用MINUS集合查询工资大于2500，但工作不是ANALYST的雇员信息。

Select * from emp where sal >2500 minus select * from emp where job =’ANALYST’;

12. 查询工资高于公司平均工资的所有员工信息。

Select * from emp where sal >(select avg(sal) from emp);

 

13. 查询与SMITH员工从事相同工作的所有员工信息。

Select * from emp where job = (select job from emp where ename =’SMITH’;

 

14. 查询工资比SMITH员工工资高的所有员工信息。

Select * from where sal >(select sal from emp where ename =’SMITH’);

 

15. 查询比所有在30号部门中工作的员工的工资都高的员工姓名和工资。

        Select ename ,sal from emp where sal >all(select sal from emp where deptno=30);

 

16. 查询部门人数大于5的部门的员工信息。

Select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5);

17. 查询所有员工工资都大于2000的部门的信息。

Select * from dept where deptno in (select deptno from emp group by deptno having min(sal)>1000);

 

18. 查询人数最多的部门信息。

Select * from dept where deptno in(select deptno from emp group by deptno having count(*)>=all(select count(*) from emp group by deptno));

 

19. 查询至少有一个员工的部门信息。

          Select * from dept where deptno in (select deptno from emp group by deptno having count(*) >=1);

          

20. 查询工资高于本部门平均工资的员工信息。

Select * from emp e1 where sal >(selecet avg(sal) from emp e2 where e1.deptno= e2.deptno group by deptno);

21. 查询工资高于部门平均工资的员工信息及其部门的平均工资。

Select t.*,a,avg_sal 部门平均工资 from (select * from emp e1 where sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno group by deptno))t left outer join (select deptno,avg(sal) avg_sal from emp group by deptno) a on t.deptno = a.deptno;

22. 查询每个员工的领导所在的部门的信息。

Select e1.ename 员工,e2.ename 上级,d.* from emp e1,emp e2,dept d where e1.mgr= e2.empno and e2.deptno =d.deptno order by d.deptno;

 

23. 查询平均工资低于2000的部门及其员工信息。

Select * from dept left outer join emp on dept.deptno=emp.deptno where dept.deptno in (select deptno from emp group by deptno having avg(sal)<2000);

索引与视图

1. 在数据库中创建student表，包括学号，姓名，性别，班级。在学号字段上创建唯一索引。

2. （1）创建简单视图。

Connect scott/tiger;

Grant insert,update,delete on emp to system;

Connect system/oracle;

Create or replace view v_emp

As

Select empno,ename,job,hiredate,deptno from scott.emp;

(2)向视图中增加数据

Insert into v_emp values(1234,’JACK’,’CLERK’,’29-4月-1963’，10)；

（3）利用视图修改数据

Update v_emp set ename=’MARK’ WHERE empno=1234;

 

3. 使用新建序列的值，为新表填充字段值。

（1）

创建一张新表。

Create table my_table(id int primary key,

Name varchar2(20));

(2)创建序列id_seq

(3)将序列的值插入新表中。

4.以scott登录，创建emp表的同义词s_e.

(1)以system用户登录后授予scott用户创建同义词的权限。

Connect system/oracle;

Grant create public synonym to scott;

 

(2)以scott登录，创建同义词。

Connect scott/tiger;

Create or replace public synonym s_e for emp;

 

(3)以system用户登录，利用同义词来操作原表。

Connect system/oracle

Desc s_e;

Select * from s_e;

1. 使用标量变量。标量变量是最简单的变量，它只能存在单个值，它的数据类型也是系统预定义的。以system连接数据库，在SQL*Plus中输入如下语句：

DECLARE

V_sal:=2000;

UPDATE scoot.emp SET sal=v_sal WHERE empno=7369;

以上代码定义了一个NUMBER数据类型的变量，并为其赋值2000，在代码被执行时，程序块中的UPDATE语句将员工编码为7369的员工工资更新为该变量值。

2. 使用%TYPE类型的变量。使用%TYPE定义变量后，如果用户以后修改数据库中该列的数据类型，则该变量的类型也会随之改变。在SQL*Plus中输入如下语句：

DECLARE

V_sal scott.emp.sal%TYPE;

BEGIN

V_sal:=20000;

UPDATE scott.emp SET sal= v_sal WHERE empno=7369;

END;

变量v_sal的数据类型会随着Scott.emp表中的sal字段的数据类型变化而变化。

3. 使用记录变量。记录变量可以使用%ROWTYPE关键字定义，若记录的结构和现有的任何表结构都不相同，那么需要自定义记录类型，人后才可以声明该记录类型的变量。在SQL*PLUS中输入如下程序，以查询某员工的信息。

DECLARE

V_row scott.emp%ROWTYPE;

BEGIN

Slect*into v_row frow scott.emp where empno=&no;

Dbms_output.put_line(‘编号’||v_row.empno);

Dbms_output.put_line(‘姓名’||v_row.ename);

Dbms_output.put_line(‘工资’||v_row.sal);

END;

 游标和触发器

1.利用游标输出EMP表的内容，包含EMPNO,ENAME,SAL三个字段。

2.先建立一个日志表emp_log，然后建立DML语句级触发器，对表emp上执行的DML操作进行监控，并将操作人员、操作类型和操作时间写入日志表中。

 存储过程与函数

1. 创建存储过程，根据职工编号删除scott.emp表中的相关记录。

（1） 以scott用户连接数据库，然后为system用户授予DELETE权限。

Connect scott/tiger;

GRANT DELETE ON emp TO system;

（2） 以system用户连接数据库,创建存储过程。

CONNECT system/abcdef;

CREATE OR REPLACE PROCEDURE delete_emp

(id scott.emp.empno%TYPE)

IS

BEGIN

 DELETE FROM scott.emp WHERE empon=id;

EXCEPTION

WHEN OTHERA THEN

Dbme_output.put_line(‘errors’);

END

(3)system 用户调用delete_emp存储过程。

EXECUTE delete_emp(7369);

(4)scott用户调用delete_emp存储过程

GTANT EXECUTE ON delete_emp TO scott;

CONNECT scott/tiger;

EXECUTE system.delete_emp(7369);

2. 创建存储过程，根据职工编号修改Scott.emp表中该职工的其他信息。

（1） 创建新用户，并授予权限。

 CONNECT system/abcdef;

CREATE USER u1 IDENTIFIED BY abcdef;

GRANT CREATE SESSION,CREATE PROCEDURE TO u1;

GRANT SELECT,UPDATE ON scott.emp TO u1;

（2） 以新用户连接数据库，创建存储过程。

CONNECT u1/abcdef;

CREATE OR REPLACE PROCEDU update_emp

(no IN SCOTT.emp.empno%TYPE,  --引用emp表中的某字段的数据类型，必须对该表具有SELECT权限 

Name IN scott.emp.ename%TYPE DEFAULT NULL,

Jobl IN scott.emp.job%TYPE DEFAULT NULL,

mgrl IN scott.emp.mgr%TYPE DEFAULT NULL,

hiredatel IN scott.emp. hiredate%TYPE DEFAULT NULL,

salary IN scott.emp.sal%%TYPE DEFAULT NULL,

comm1 IN scott.emp.comm%%TYPE DEFAULT NULL,

deptno1 IN scott.emp. deptno%%TYPE DEFAULT NULL,

)

IS

BEGIN

IF name IS NOT NULL THEN

 UPDATE scott .emp SET ename=name WHERE empno=no;

END IF;

IF job1 IS NOT NULL THEN

UPDATE scott .emp SET mgr=mgr1 WHERE empno=no;

END IF;

IF mgr1 IS NOT NULL THEN

UPDATE scott .emp SET mgr=mgr1 WHERE empno=no;

END IF;

IF hiredate1 IS NOT NULL THEN

UPDATE scott .emp SET hiredate = hiredate 1 WHERE empno=no;

END IF;

IF salary IS NOT NULL THEN

UPDATE scott .emp SET sal= salary WHERE empno=no;

END IF;

IF comml IS NOT NULL THEN

UPDATE scott .emp SET comm= comml WHERE empno=no;

END IF;

IF deptnol IS NOT NULL THEN

UPDATE scott .emp SET deptno= deptnol WHERE empno=no;

END IF;

COMMIF;

EXCEPTION

WHEN others THEN

ROLLBACK;

END;

(3)U1 调用update_emp存储过程。

EXEC update_emp(7369,salary=>2000);

3. 创建存储过程，根据指定的职工编号查询该职工的详细信息。

（1） 创建存储过程。

CONNECT scott/tiger;

CRETE OR REPLACE PROCEDURE select_emp

(no IN scott.emp.empno%TYPE,emp_information OUT VARCHAR2)

IS

r scott,emp%ROWTYPE;

BEGIN

SELECT*INTO r FROM scott.emp WHERE empno=no;

Emp_information:=emp_information||r.ename||’’||r.job||’’||r.sal||’’||r.mgr||’’||r.hiredate||’’||r.comm||’’||r.deptno;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Emp_information:=’NO person!’;

WHEN others THEN

Emp_information:=’NO Errors!’;

END;

(2)调用存储过程。

SET SERVEROUTPUT ON

DECLARE

Info VARCHAR2(50);

BEGIN

Select_emp(7369,info);

Dbms_output.put_line(info);

END;

4. 创建函数，根据给定的部门编码计算该部门所有职工的平均工资。

（1） 创建函数。

CREATE OR REPLACE FUNCTION avg_sal

(no scott.emp.deptno%TYPE)

RETURN NUMBER

IS

Avgsal number(7,2);

BEGIN

SELECT AVG(sal)INTO avgsal FROM scott.emp WHERE deptno=no;

IF avgsal IS NOT NULL THEN  ————因为上面的语句不触发异常，因此用IF语句判断是否查询成功

RETURN avgsal;

ELSE

Avgsal:=-1;

RETURN avgsal;

END IF;

END ang_sal;

(2)调用函数。

BEGIN

Dbms_output.put_line(avg_sal(&deptno));

END;