Oracle学习心得

1、Oracle进入数据库的命令：

Sqlplus “as sysdba”

2、检查数据库是否已经成功安装：

SQL>select status from v$instance;

3、删除数据库：

SQL>exit

4、动态表：Oracle的动态表也是属于字典表，属于Oracle内部的表，不能手工更改数据，

动态表是因为表中的数据根据其他表的数据而变化，在Oracle中动态表通常使用“v$”

开头，v$instance为Oracle数据库的动态表之一，该表记录Oracle数据库实例的各种信息。

5、关闭数据库数据库的命令：

SQL>shutdown immediate

6、继续输入启动数据库的命令：

SQL>startup

7、查看数据库名称的命令

SQL>show parameter db_name

Show parameter :表示查看Oracle数据库中的各种参数值。

8、如何查询数据库中的一个用户是什么状态？

可以查询数据字典表dba_users来了解用户的状态，dba_users表存储着Oracle数据库所有用户的信息。

9、字典表：是Oracle数据库的内表，在Oracle数据库中不需要创建就存在的表，不能更改

其信息。

10、查询数据库默认SCOTT用户的状态：

SQL>SELECT username,account_status

FROM dba_users

WHERE username=’scott’

运行情况如下：

未能查询出任何数据，这是因为：where条件后面的每个字段所表示的值是区分大小写写的，如username字段的值应该为SCOTT,如下：

SQL>SELECT username,account_status

FROM dba_users

WHERE username=’SCOTT’;如下：

11、如何对一个数据库用户进行解锁？

解锁语法：

alter user 数据库用户名account unlock;

12、EXPIRED:表示数据库用户处于到期状况，不能正常连接到数据库，如下：

13、查看数据库的命令，如下：

图示显示用户为SYS.

14、用户连接语法：

SQL>conn scott/tiger

如：

修改SCOTT用户密码成功后，可以使用SYS 用户查看SCOTT用户的状态，首先需要使用SYS连接上数据库，当前的用户是SCOTT用户，在Oracle中输入以下指令：

SQL>conn /as sysdba.

继续查看SCOTT用户的状态，执行如下指令：

QL>SELECT username,account_status

FROM dba_users

WHERE username=’SCOTT’;视图如下：

表示已经打开。

最简单的查询、插入、更新、删除SQL语句：

16、查看用户有哪些表的指令：

SQL>SELECT table_name

FROM user_names;

如下：

说明SCOTT用户共有DEPT、EMP、BOUNS、SALGRADE四个表。

17、Oracle 11g每次启动后默认的用户是SYS用户，要对其他用户进行操作必须进行用户

切换，如切换到scott用户：SQL>conn scott/cat。

18、设置查询数据的中国时间格式：

SQL>alter session

set nls_date_format=’YYYY_MM_DD’;

如下：

19、session：表示一个会话，表示只更改当前会话的时间格式，其他用户连接数据库则不会

更改时间格式。

20、nls_date_format,Oracle数据库的时间参数，日期虽然一样，但可以有不一样的时间日期

格式。

21、YYYY_MM_DD,时间格式，分别表示年、月、日。

22、round(X,Y)是Oracle数据库的一个四舍五入函数，X表示这个数字需要四舍五入，Y则表

示要在那位数上进行四舍五入，Y=0，表示在个位数上四舍五入，Y=1，表示在小数点第一位四舍五入。

Oracle数据库的内存结构：

23、在Or acle9i及以上版本中，提供了一个DB_CACHE_SIZE参数来设置Oracle数据库高速

缓存区的大小，该参数可以动态更改，之后可以通过查询指令查看更改后的参数，指令如下：

SQL>show parameter db_cache_size.

因为在Oracle 11g中，SGA(系统全局区)为数据库服务器自动管理，所以该参数值为0，当然在运行Oracle 数据库时，数据库高速缓存是一定分配的，所以使用show sga 指令可以查看数据库高速缓存区分配的内存大小，如下：

24、在Oracle 中引入了Buffer Cache Advisory Parameter参数，目的是让Oracle对于数据库

缓冲区的内存分配提供一些建议，可以通过动态性能视图v$db_cache_advice查看缓存区的建议信息，如下：

25、重做日志缓存区参数log_buffer 是静态参数，不能动态修改，如下：

26、大池是SGA的一段可选内存区，只在共享服务器环境中配置large pool,在共享服务器环

境下，Oracle 在共享池中分配额外的空间用户进程和服务器进程之间的会话信息。

27、PGA(进程全局区)是服务器进程专用的一块内存，系统中的其他进程是无法访问这块内

存的。PGA独立于SGA，PGA不会再SGA中出现，他是由操作系统在本地分配的。

PGA中存储了服务器进程或单独的后台进程的数据信息和控制信息，它随着服务器进程的创立而被分配，随着服务器的进程的终止而释放内存。

查询PGA中排序区的大小的指令：

SQL>show parameter sort_area_size;

28、在向表中插入数据时，表对应有几列VALUES括号里就必须有几列，如：

29、启动spool并将查询记录到.tex文件中，如下：

图中，首先查看spool的状态，处于关闭状态，然后开启spool并把接下来的用户输入的SQL语句和查询的结果存储到指定的d:\spool_test文件中，查看参数spool的状态为开启。

在D盘的根目录下生成的一个spool_test.lst文件。

29（1）、数据分组（max、min、avg、sum、count）只能出现在查询的列、having、order by 中。Having、group by 、order by的顺序一定是：group by 、having、order by。

2）、在emp表中查询某员工的上级的名称，用到自连接查询，如下：

Select worker.enmae, boss.ename from emp worker, emp boss

Where worker.mgr=boss.mgr;

3)查出工资比自己部门员工平均工资高的员工信息：

第一步：查询各个部门员工的平均工资，如下：

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

第二步：将以上查询作为一张子表：

最总语句：

Select a2.ename,a2.deptno,a2.sal,a1.mysal

from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a1.deptno=a2.deptno

and a2.sal>a1.mysal;

4）、当在from中使用子查询时，该子查询会被当作一个视图来对待，因此叫做内嵌视图，当在from中使用子查询时，必须给子查询起别名，并且不能使用as。

5）、分页查询：

第一步：rownum分页：

（Select * from emp）a1；

第二步：显示分页：

Select a1.*, rownum rn from （Select * from emp）a1;

第三步：查询分页：

Select a1.*, rownum rn from (select * from emp) a1 where rownum<12;

第四步：如果要在以上的结果中再查询出后面的六页，可以再将上步的查询语句作为内嵌视图：

Select * from （select a1.*, rownum rn from (select * from emp) a1 where rowwnum<12）where rownum>5;

SQL语言概述：

30、表dept是scott用户的表，如果用system用户登录，然后操作表deptde 话，就会报错，

如下：

但是，在system用户模式下使用“模式名.表名”的方式可以查询表数据，如下：

PL/SQL Developer的使用：

31、All objects(所有对象)：表示scott用户可以访问数据库的所有对象，

My object(我的对象)：表示scott用户自己拥有的对象。

32、如下，scott用户有四个表：

分别为BONUS、DEPT、EMP、SALGRADE。

33、在PL/SQL Developer 中查看EMP有哪些列？

使用scott用户及其密码cat通过PL/SQL Dev eloper登录到Oracle数据库中，选择左侧下拉列表框中的【My object】选项，单击加号后面的【Tables】,然后选中EMP表，右击，从快捷菜单中选择【查看】命令，弹出如下图的对话框：

在图中单击【列】标签，可以看到如下图所示的【列】选项卡界面：在图中可

以很直观地看到表的所有列，包括哪些列值为空，哪些不能为空。单击图中的

如何新增、修改、删除用户及如何授权

34、创建一个数据库用户时，所使用登录的用户必须有创建数据库用户的权限，即有

Create user的权限，如果没有，则会报错，如使用SCOTT登录到PL/SQL Developer, 如下：

查看scott用户拥有的权限，输入指令select * from session_privs,结果如下，表中显示scott 没有创建用户的权限。

35、现在已经使用sys用户通过PL/SQL Developer登录创建了lisan用户，但是还必须赋予

它的连接权限，才能连接到数据库中，所使用的指令为：grant connect to lisan，如下：

36、当增加一个数据库用户，首先要创建该用户，然后通过也有的用户赋予它的权限，如

查询、插入的权限。

37、session_privs是Oracle 数据库的字典表，字典表其实就是Oracle内部的表，不用创建

表就存在，而这个表可以看到许多权限，比如创建用户、删除用户，如下，查看数据库用户scott拥有的权限，指令如下：select * from session_privs;

而查询scott用户的角色的指令是：select * from user_role_privs;常用的三个角色：connect、DBA、resource。查看角色的权限又怎么操作呢？

如查询connect的权限，可以通过如下指令进行：select * from dba_sys_privs where Grantee=’CONNECT’;

视图

38、查询视图accounting_view的定义语句时，在视图user_view中使用text属性列记录该

视图的定义，如下图：

39、要快速地查询某个部门的数据，可以创建该部门的视图，如要查询sal部门的数据，

可以创建sales_view的视图，如下图：

40、

上列视图中，where字句只选择表emp中job为salesman和manager的选项，即只有job为salesman和manager的数据才可以插入，否则不允许插入，如下：

？？？？

41、Oracle使用视图查询数据的过程：

（1）、读取数据字典，获得该视图的定义，查找到该视图所引用的表，、

（2）、从数据字典中查询当前用户对于该视图所引用的表的权限，

（3）、执行定义该视图的sql语句。

42、物化视图：物化视图是基于表、视图或其他视图而创建的，当创建一个物化视图时，

Oracle会自动创建一个内部表来存放物化视图的数据，物化视图同源表进行同步，不断地刷新物化视图中的数据。

43、对于公司的表，因为人员处于流动状态，因此，有可能每个月都要对边进行查询，每

次查询都需要输入相应的SQL语句，这就比较繁琐，通过建立视图，然后查看相应的视图就能轻松解决上述问题。

序列号

45、CACHEn:Oracle服务器会预分配n个序列号并保存在在内存中，

NOCACHE:Oracle服务器不会预分配序列号并保存在内存中。

46、CYCLEn:定义序列号在达到最大值或最小值时，将继续产生序列号，

NOCYCLE:定义序列号达到最大或最小值时，不在继续产生序列号。

47、使用currval伪列查询当前序列号的值,如下：

Select emp_seq.currval,emp_seq,nextval from dual；

48、修改序列号emp_seq的参数：

alter sequence emp_seq

incremenet by 2

cache 30，如下：

49、在不适用序列号时，如果多个用户同时向EMP中插入一条员工记录时，用户必须

等待以得到下一个可用的员工号，而一旦使用序列号，则用户无需相互等待就可以得到下一个可用的员工号，序列生成器会自动为每个用户创建正确的员工编好，如下一个以图书号的序列号：

函数

50、公司人数很多，知道每个员工的的id号，就可以知道其薪水多少，也可以查看emp

表，但因为员工数较多，这就不方便，因此，可以通过创建一个函数来实现，可以创建一个获取员工薪水的函数。

51、创建过程如下：

因此，可以通过查询所创建的函数get_empsal来查询数据，如下：

存储过程

52、存储过程是一组为了完成特定功能而建立的SQL语句群，经编译后存储在数据

中心。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数），

公司员工离职时，每次都要从数据库中删除员工，每次删除员工的SQL语句

比较麻烦，可以写一个根据员工号来删除员工信息的存储过程，以后删除员工

直接调用存储过程就可以了。

53、oracle 数据库中函数和存储过程的区别：

（1）、函数必须有返回值，而存储过程没有返回值，

（2）、函数可以单独执行，而过程必须通过execute执行。

（3）、函数可以嵌入SQL查询，而过程不行。

同义词

54、同义词是现有对象的一个别名，如表中的salgrade表比较长，可以为其建一个容易记得

的同义词sg。

55、在PL/SQL Developer中输入select * from session_privs where privilege like ‘%SYNONY%’;

的语句，查询scott用户是否具有创建同义词的权限，如下：

由图可知，scott用户没有创建同义词的权限。

56、使用数据库超级用户sys及其密码通过PL/SQL Developer 登录到数据库，可以给scott

用户赋予创建同义词的权限，指令如下：

Grant create any synonym to scott，如下图：

57、创建完成后，再次使用scott用户通过PL/SQL Developer 登录到数据库，重新输入指令

Select * from session_privs where privilege like ‘%SYNONYM%’;如下图：

从图中，可以看出scott用户拥有创建任何同义词的权限。

58、创建公共同义词权限给scott用户：使用SYS用户及其密码通过PL/SQL登录

数据库，然后在sql窗口中输入下面的语句：

Grant create public synonym to scott，如下图：

再执行select * from session_privs where privilege like ‘%SYNONYM%’;

结果如下：

说明scott用户具有创建公共同义词的权限。

59、使用scott用户创建emp的公共同义词，指令如下：

create public synonym emp for scott.emp,结果如下：

再次用xiaoyilong用户登录到数据库，然后再SQL窗口输入以下语句：select * from emp，执行如下：

数据库启动的不同状态

60、关闭数据库的四个命令：

Shutdown immediate:不允许新的连接、不等待会话结束、不等待实物结束、做一个检查点并关闭数据文件。没有结束的事务是rollback的。启动时不需要对数据库进行恢复。

Shutdown abort:不允许新的连接、不等待会话结束、不等待事务结束，不做检查点并没有关闭数据文件，启动时数据库将进行实例恢复。一般数据库在运行复杂的业务，而Shutdown immediate 命令不能关闭数据库，并且需要快速关闭数据库时使用shutdown abort。

Shutdown transactionl：不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。在数据库的业务很繁忙时，需要等待事务处理完成才能关闭。

Shutdown nomal：不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据库文件。启动数据库时不需要实例恢复。

61、nomount状态一般在维护数据库时使用，比如对数据库重要参数的修改，恢复数据库的

的控制文件及数据库时都需要用到该指令。

62、默认的控制文件可能有几个，而这几个控制文件的内容是一模一样的。如果在数据库服

器中，可以把这几个文件分别放在不同的磁盘中，某个文件损坏时，复制一个过去即可。

63、查看控制文件的具体位置

select name from v$controlfile；

查看数据文件的具体位置

select name from v$datafile；

查看从做日志文件的位置

Select member from v$logfile；如下

64、查询数据库中的“是否允许远程登录”参数，即为remote_login_passwordfile,指令如下：

Show parameter remote_login_passwordfile,如下图：

当参数remote_login_passwordfile显示为EXCLUSIVE时，则允许远程登录。显示为NONE 时，为不允许登录。

Linux常有命令

65、查看Linux内核版本的命令：从弹出处菜单中选择【打开终端】，如下图：

在终端命令窗口中输入$ uname –r的命令即可查出Linux系统的版本，如下：

$cat /etc/redhat-release,如下：

67、具体版本号的指令为：

$ cat /proc/version

68、启动和关闭ftp的服务器的指令如下：

$ server vsftpd start;$ server vsftpd stop.如下：

69、检测window系统是否可以与Linux系统相通，可以看看windows中能否ping通Linux

的地址，如Linux的ip地址是192.168.128.128，则在windows系统的控制台命令窗口输入的指令如下：

>ping 192.168.128.128,图如下：

如图所示，表示windows系统与Linux系统可以互相通信了。

PL/SQL基础

70、字符串为键值的索引表：、

71、Varray变长数组：

72、IF…ELSE…结构语句：

DECLARE

v_if_con NUMBER(10);

BEGIN

v_if_con:=12;

IF v_if_con>20THEN

DBMS_OUTPUT.PUT_LINE('GOOD');

ELSE

DBMS_OUTPUT.PUT_LINE('BAD');

END IF;

DBMS_OUTPUT.PUT_LINE('执行完毕！');

END;

73、if…elsif…结构语句：

74、嵌套实用IF 语句：

DECLARE

v_product productinfo%ROWTYPE;

BEGIN

SELECT*INTO v_product FROM productinfo WHERE productid ='024*******'; IF v_product.productprice >3000THEN

DBMS_OUTPUT.PUT_LINE('改产品属于高价格产品！');

IF v_product.quantity >50THEN

DBMS_OUTPUT.PUT_LINE('该产品数量高于50，不缺货！');

ELSE

DBMS_OUTPUT.PUT_LINE('该产品数量低于50，补货！');

END IF;

ELSIF v_product.productprice BETWEEN1000AND3000THEN

DBMS_OUTPUT.PUT_LINE('改产品属于中间价格产品！');

IF v_product.quantity >200THEN

DBMS_OUTPUT.PUT_LINE('该产品数量高于80，不缺货！');