oracle笔记

oracle的卸载如下的步骤完成：

1.直接运行卸载程序；

2.删除硬盘上的残留文件，如果删除不掉先进入到安全模式下删除

3.同时删除注册表中所有与oracle有光的配置项；

PS：如果没有正常的安装成功，除了操作系统的问题之外，直接采用以上的第二和第三两个步骤就可以完成卸载

当网络环境发生变化的时候（监听无法启动）

计算机名称被改变，10g及以前的版本，将NETWORK\ADMIN中两个配置文件：listener.ora和tnsnames.ora修改HOST

选择“NET Manager”添加全局数据库名及sid

格式化命令：

设置每行显示的记录长度：SET LINESIZE 300;

设置每页显示的记录长度：SET PAGESIZE 30;

使用ed XXX;编写sql语句

使用Save XXX 把SQL存到文件

使用@ XXX;来执行程序。执行硬盘上的文件，则必须写上完整的路径；

使用/  运行上一条语句

SELECT * FROM tab;查看所有的数据表

show user;显示当前的用户

CONN 用户/密码 [AS SYSDBA]; 进行用户的登陆切换。如果要访问其他用户的表，使用"用户名.表名"

用超级管理员登陆，可以通过命令手工控制数据库实例的打开和关闭；

关闭数据库实例：SHUTDOWN IMMEDIATE;

要打开数据库必须先使用"/nolog"登陆，之后使用超级管理员登陆。启动数据库实例：STARTUP;

用"HOST"为作为前缀。

常用用户：

超级管理员：sys/change_on_install;

普通管理员：system/manager;

普通用户：scott/tiger;

大数据用户：sh/sh;

DESC 表名称;  查询表结构

可以用||显示原样字符串 比如：SELECT empno||','||ename 表头信息 FROM emp;PS：在sql语句中单引号表示字符串。

PS：别名上的内容不要使用"'"括起来，而只有在SELECT子句中出现的内容使用"'";

在oracle数据库中所有的数据都是区分大小写的。

如果现在使用的是NOT IN的操作符，如果查询范围之中有null，则不会有任何的查询结果返回。

如果在模糊查询上不设置任何的查询关键字的话('%%')则表示查询全部记录。

查询表中字段带有%的like查询

对于特殊符号可使用ESCAPE 标识符来查找，escape表示*后面的那个符号不当成特殊字符处理。

1.用 ESCAPE 转义  比如 LIKE '%/%%' ESCAPE '/';

2.用 [   ] 通配符 比如 LIKE '%[%]%';

字符函数基本：

1.UPPER(字符串 | 列)：将输入的字符串变为大写返回；

2.LOWER(字符串 | 列)：将输入的字符串变为小写返回；

3.INITCAP(字符串 | 列)：开头首字母大写；

4.LENGTH(字符串 | 列)：求取字符串的长度；

5.REPLACE(字符串 | 列)：进行替换；

6.SUBSTR(字符串 | 列，开始点[.结束点])：字符串截取；

PS:在oracle中为了查询方便，专门提供了一个“dual”的虚拟表。

用"&"的操作代替变量的内容

在oracle数据库中，SUBSTR()函数从0或1开始都是一样的。

截取字符串后三个字母

SUBSTR(字符串,LENGTH(字符串)-2);  或者 SUBSTR(字符串,-3);

数字函数基本：

1.ROUND(数字 | 列 [.保留小数的位数]);   四舍五入的操作；

2.TRUNC(数字 | 列 [.保留小数的位数]);   舍弃指定位置的内容;

3.MOD(数字1,数字2);  取模，取余数;

4.ABS(数字 | 列 [.保留小数的位数]) 取绝对值;

5.CEIL(数字 | 列 [.保留小数的位数]) 向上取整;

6.FLOOR(数字 | 列 [.保留小数的位数]) 向下取整;

7.SQRT(数字 | 列 [.保留小数的位数]) 开平方;

8.SIGN(数字 | 列 [.保留小数的位数]) 判断数值正负;

9.POWER(数字1,数字2) 幂运算;

10.EXP(数字 | 列 [.保留小数的位数]) 基数为e的幂;

11.LOG(数字 | 列 [.保留小数的位数]) 对数运算;

12.LN(数字 | 列 [.保留小数的位数]) 自然对数;

当前日期可以使用"SYSDATE"取得。

日期计算(只有这三个)：

1.日期+数字 = 日期，表示若干天之后的日期；

2.日期-数字 = 日期，表示若干天之前的日期；

3.日期-日期 = 数字，表示两个日期的天数；

日期函数基本：

1.LAST_DAY(日期); 求出指定日期的最后一天;

2.NEXT_DAY(日期,星期数); 求出下一个指定星期X的日期;

3.ADD_MONTHS(日期,数字); 求出若干月之后的日期;

4.MONTHS_BETWEEN(日期1,日期2); 求出两个日期之间所经历的月份。

ps：这些函数可以避免闰年问题。

转换函数基本：

TO_CHAR(字符串 | 列，格式字符串);  将日期或者数字变为字符串显示；PS：一些格式字符串[年(yyyy),月(mm),日(dd),时(hh[24]),分(mi),秒(ss)].但是这个时候的显示数据之中可以发现会存在前导0，如果想消除这个0的话，可以加入一个"fm";数字格式化上，每一个"9"表示一位数字的概念。其中的字母"L",表示的是"Locale"的含义，即：当前的所在的语言环境下的符号。

TO_DATE(字符串，格式字符串); 将字符串变为DATE数据显示;

TO_NUMBER(字符串); 将字符串变为数字显示;基本不用，因为在oracle中不用也可以完成以上操作。

日期综合列子：求准确的年月日

SELECT hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months,TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) days FROM emp;

通用函数基本：

1.NVL()函数，处理null;

2.DECODE()函数，多数值判断;

3.case

左右链接：(+) oracle所独有。

SQL：1999语法

SELECT table1.column,table2.column FROM table1[CROSS JOIN table2]|[NATURAL JOIN table2]|[JOIN table2 USING(column_name)]|[JOIN table2 ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];

1.交叉连接(CROSS JOIN):用于产生笛卡儿积

2.自然连接(NATURAL JOIN):自动找到匹配的关键字段，消除掉笛卡儿积

3.JOIN...USING子句:用户自己指定一个消除笛卡儿积的关键字段

4.JOIN...ON子句:用户自己指定一个可以消除笛卡儿积的关联条件

5.连接方向的改变:左(外)连接: LEFT OUTER JOIN...ON; 右(外)连接 RIGHT OUTER JOIN...ON; 全(外)连接: FULL OUTER JOIN...ON 把两张表中没有的数据类型显示出来

统计函数

COUNT();   AVG();   SUM();   MAX();   MIN();

PS:如果一张表中没有统计记录，COUNT()也会返回数据，只是这个数据是"0"，如果是其他的函数，则有可能返回是null。

PS:分组函数可以在没有分组的时候单独使用，可是却不能出现其他的查询字段。如果现在要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现。分组函数允许嵌套，但是嵌套之后的分组函数的查询中不能再出现任何的其他字段。WHERE子句中不能使用统计函数。

WHERE:子查询一般只返回单行单列，多行单列，单行多列的数据。

FROM：子查询返回的一般是多行多列的数据，当作一张临时表出现。

如果现在的子查询返回的是多行单列数据的话，这个时候就需要使用三种判断符：IN (=ANY), ANY , ALL;

CREATE TABLE 复制表名称 AS 子查询; --表复制操作(oracle特有)PS:将一个表的结构复制出来，不需要数据，需要写一个永远满足不了条件。

所有的事务处理操作都是针对于每一个session进行的，在oracle数据库之中，把每一个连接到数据库上的用户都称为一个session，每一个session之间彼此独立，不会有任何的通讯，而每一个session独享自己的事务控制，例如某一个session在更新数据库的时候还没有提交事务，其他session是无法更新，必须等待之前的session提交后才可以。(rollback,commit)

oracle特别支持伪列(ROWID),(ROWNUM)

常用数据类型：

varchar2 变长字符型，最大长度4000B

number(m,n)  数值型。m为总位数，n为小数位数。总长度最大为38位

date 日期型。有效表示范围公元前4712年1月1日到公元4712年12月31日

blob 二进制大对象类型，最大长度4GB

clob   字符大对象类型，最大长度4GB

oracle所独有：

在oracle数据库之中，所有的数据实际上都是通过数据字典保存的，最常用数据字典是:dba_,user_;

例如：SELECT * FROM tab;   SELECT * FROM user_tables;

改变表名称  RENAME 旧的表名称 TO 新的表名称;

截断表 TRUNCATE TABLE 表名称;   彻底释放表资源

查看回收站 SHOW RECYCLEBIN;

恢复回收站表 FLASHBACK TABLE 表名称 TO BEFORE DROP;

删除回收站中的表 PURGE TABLE 表名称;

清空回收站 PURGE RECYCLEBIN;

INSERT INTO 表名称() VALUES();

UPDATE 表名称 SET ... WHERE ...

DELETE FROM 表名称 WHERE ...

删除表 DROP TABLE 表名称 [PURGE];

重命名表 Rename XXX to XXX;

ALTER TABLE 表名称 ADD[|MODIFY](.....);

IBM DB2 世界上性能最高，不允许修改表结构。

数据库创建脚本的格式：

1.本文件的文件名称后缀为"*.sql";

2.先删除相应的数据表；

3.编写创建表的语句；

4.增加测试数据；

5.提交事务；

定义约束的时候没有为约束指定一个名字，系统会默认分配，约束的名字建议的格式"约束类型_字段"，指定约束名称使用CONSTRAINT完成。

约束 NK(NOT NULL),PK(PRIMARY KEY),CK(CHECK),UK(UNIQUE),FK(FOREIGN KEY    REFERENCES  ).

在复合主键的使用之中，只有多个字段内容都一样的情况下，才被称为重复数据。

PS:删除数据的时候，如果主表中的数据有对应的子表数据，则无法删除。如果希望主表数据删除之后，子表中对应的数据也可以删除，则可以在建立外键约束的时候指定一个级联删除的功能(ON DELETE CASCADE),或者删除数据的时候，让子表中对应的数据设置为NULL(ON DELETE SET NULL)

oracle之中强制删除表的操作，不再关心约束，在删除的时候写上一句"CASCADE CONSTRAINT"

为表增加约束：ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段).

删除表中的约束：ALTER TABLE 表名称 DROP CONSTRAINT 约束名称.

可见如果要维护约束，肯定需要一个正确的名字才可以，可是在这五种约束之中，非空约束作为一个特殊的约束无法操作。

查询"user_constraints"数据字典表获得，药获取哪个字段上有次约束需要查询"user_cons_columns"表。

COL 列 FOR 大小(A15)  格式化某行

UNION:连接两个查询，相同的部分不显示

UNION ALL:连接两个查询，相同的部分显示

INTERSECT:返回两个查询的相同部分

MINUS:返回连个查询不同的部分

序列，在oracle之中如果要想操作currval，则首先必须先使用nextval

CREATE SEQUENCE 序列名称 INCREMENT BY 增长速率 START WITH 初始数值 MAXVALUE 最大数值 MINVALUE 最小数值 CYCLE NOCACHE;

CREATE [OR REPLACE] VIEW FROM 子查询

   WITH CHECK OPTION     WITH READ ONLY 

 如果现在视图中的查询语句是统计操作，则根本就不可能更新

 CREATE [PUBLIC] SYNONYM ... FOR ...

 oracle会在主键上自动建立索引，一般索引只使用在不会频繁修改的表中  CREATE INDEX 名称 ON 列<emp(sal)>

GRANT 权限[角色][访问其他用户的数据包<SELECT ,INSERT ON emp>] TO 用户名.    SESSION ,TABLE

REVOKE 

CREATE USER 用户名 IDENTIFIED BY 密码.

角色：CONNECT 和 RESOURCE

修改密码  ALTER USER 用户 IDENTIFIED BY 密码.

密码失效  ALTER USER 用户 PASSWORD EXPIRE.

锁定  ALTER USER 用户 ACCOUNT [UN]LOCK.

删除用户  DROP USER 用户名 [CASCADE]

数据的备份 

1.建立一个文件夹

2.输入exp指令

3.输入用户名/密码

4.设置导出文件的名称

数据的导入

1.进入导入的文件夹

2.输入imp指令

3.输入用户名/密码

数据库的第一范式就是数据表中的数据列不可再分(所谓的不可分是指使用的数据类型都使用数据库提供好的数据类型)

第二范式：数据表中的非关键字段存在对任一候选关键字段的部分依赖函数。

对于in 和 exists的性能区别:

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。 

其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 另外IN是不对NULL进行处理

随机返回5条记录

Select * from (select ename,job from emp order by dbms_random.value()) where rownum<=5;

把统计语句一起显示

select ename,sal,max(sal) over(), min(sal) over() from emp;

查询某用户下所有表

select table_name from all_tables where owner='SCOTT';

查询EMP表中所有字段（列）

select * from all_tab_columns where table_name='EMP';

列出表的索引列

select * from sys.all_ind_columns where table_name='EMP';

列出表中约束

select * from all_constraints where table_name='EMP'

oracle将执行结果保存到文件

sql> spool d:\ssss.txt
sql> select ...
sql> spool off

全局数据库名主要用于分布式数据库系统中，区分不同的数据库，它由数据库名和数据库域组成，格式为：database_name.database_domain。第一个句点前面指定的值将成为DB_NAME初始化参数的值，而之后指定的值将成为DB_DOMAIN初始化参数的值。SID是系统标识符(System Dentifier)的缩写，主要用于区分同一台计算机上的不同数据库的不同实例。

在删除表结构的行时注意要有column关键字。

在oracle中，删除基表后建立在此表上的视图定义仍然保留在数据字典中，而当用户引用该视图时会报错。

接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户。如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户，如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。