关于oracle的一些基本知识点和操作

关于oracle的一些基本东西

Oracle的安装：

 首先检查机器上是否安装过oracle若果安装过，则卸载后注意删除注册表（具体位置上网查）中有关oracle中的一切和本地磁盘中oracle的文件夹。安装oracle，记住oracle的实例名（这个很重要）用户可以后来添加。一个oracle可以对应n个用户。配置环境变量。

 

Oracle创建用户：

创建临时表空间

创建临时表空间
CREATE   TEMPORARY  TABLESPACE test_temp
TEMPFILE  ' C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf '
SIZE 32M
AUTOEXTEND  ON
NEXT  32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

创建用户表空间

          创建用户表空间
CREATE  TABLESPACE test_data
LOGGING
DATAFILE  ' C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF '  
SIZE 32M 
AUTOEXTEND  ON  
NEXT  32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

 

创建用户并制定表空间

CREATE   USER  username IDENTIFIED  BY  password
DEFAULT  TABLESPACE TEST_DATA
TEMPORARY  TABLESPACE TEST_TEMP;

 

给用户授予系统权限

GRANT  
　　 CREATE  SESSION,  CREATE   ANY   TABLE ,  CREATE   ANY   VIEW  , CREATE   ANY   INDEX ,  CREATE   ANY   PROCEDURE ,
　　 ALTER   ANY   TABLE ,  ALTER   ANY   PROCEDURE ,
　　 DROP   ANY   TABLE ,  DROP   ANY   VIEW ,  DROP   ANY   INDEX ,  DROP   ANY   PROCEDURE ,
　　 SELECT   ANY   TABLE ,  INSERT   ANY   TABLE ,  UPDATE   ANY   TABLE ,  DELETE   ANY   TABLE
　　 TO  username;

为用户创建角色权限

grant connect tousername;

grant dba to username;

grant resource tousername;

将role这个角色授与username,也就是说，使username这个用户可以管理和使用role所拥有的资源

GRANT  role  TO  username;

-----------------------------------------------查看用户权限---------------------------------------------------------

查看所有用户

SELECT   *   FROM  DBA_USERS;
SELECT   *   FROM  ALL_USERS;
SELECT   *   FROM  USER_USERS;

查看用户系统权限

SELECT   *   FROM  DBA_SYS_PRIVS;
SELECT   *   FROM  USER_SYS_PRIVS;

查看用户对象或角色权限

SELECT   *   FROM  DBA_TAB_PRIVS;
SELECT   *   FROM  ALL_TAB_PRIVS;
SELECT   *   FROM  USER_TAB_PRIVS;

查看所有角色

SELECT   *   FROM  DBA_ROLES;

查看用户或角色所拥有的角色

SELECT   *   FROM  DBA_ROLE_PRIVS;
SELECT   *   FROM  USER_ROLE_PRIVS;

 

当用户处于不用的计算机时需要建立计算机之间的一个链接dataBaseLink

Drop existing database link

drop public database link XXDLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM;

Create database link

create public database link XXDLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM

connect to WOXINGTONG

using '192.168.95.212/itsdb';

访问同一个机器上的不同用户是前面加username.用户下的表。

访问不同机器上的用户需要添加username.用户的表@链接的名称（例如XXDLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM）这样就能访问了。

Sql的一些语法：

ddl语句：

数据库的数据类型介绍：最常用的number(n),varchar varchar2(n) char date Boolean

数据库中的数据类型是存储在dba_types中可用sysdba登录后查询：select type_name  from dba_types where  owner  is  null;

建表：

Create table  tablename(

Id  number(2)  primary  key  not  null,

Username  varchar2(20)  not  null,

)

修改表的字段: alter table  tablename  rname  column  lastName  to  newname;

修改字段的长度只能改大：alter table  tablename  modify(name  type(n));

表外添加主键： alter table  tablename  add constraints  name  primary  key(id);

创建主键时直接创建索引空间和数据存储空间：

Alter table tablename  add constraints  name  primary  key(id)  using  index  tablespace  index storate(initial  10M next  5M pctincrese  0)

Tablespace user storate( initial  10M next  5M  pcincrese 0)

Oracle建议任何一个表至少建立两个表空间 一个存储索引另外一个存储数据这样物理层的存储就会把两个文件放在不同的地方就能避免I/O操作所引起的竞争，提高查询效率。

再有就是在批量插入数据的时候主键的可设置为disable不起作用这样会提高效率等数据插入完毕后在吧主键设置为enable：alter table tablename  disable  constraint  name;

                      Alter  table  tablename  enable  primary  key;

若存在主键是外键的关键这样主键就不能设置为disable。

删除主键：alter table tablename drop constraint  name;

                      Alter  table  tablename  drop  primary  key;

 

外键：alter table tablename  add constraint  name  foreign key(外键字段) references anothertable(关联字段);

Oracle无法修改已经创建的外键（外键列，名称）；所有只有一种方法就是重新创建外键把原有的删除掉；先设置外键无效：alter table  tablename  disable constraint 外键名再删除：alter table  tablename  drop constraint  外键名；

添加列的字段说明：comment on column tablename.line is  ‘说明的文字’;

 

约束：

Oracle约束的五种类型：主键，外键，check，not null, unique index;

Check约束：为特定级别的约束或者一个表的约束可以自定义：例如一个表中有性别字段这是可设置sex varchar2(2) check(sex=’男’  or sex=’女’)

                      age number(2) check(age between 10 and 20)

not null:是对单一的字段的约束；默认是可为空的；id number(2) not null;或者alter table tablename modify  name  not null;

unique:唯一性约束，一列或者一组列中的值都是唯一，用于一组列时，这组列组合起来有唯一值。唯一性约束是需要占用空间的所以需要为其分配空间例如创建主键；

 

索引：方便查询的，为一个字段添加索引：create unique  index  name on tablename(id);

                      删除索引drop index indexname;

 

 

视图：是“存储的查询”或者“虚拟的表”只是映射基表的信息；可以吧多个表的查询同时呈现出来，最后直接查询视图就行了。可以根据同义词创建视图

                      Create or replace view viewname as select name, from  tablename1, tablename2  where tablename1.name=tablename2.name；

删除视图：drop view viewname;

 

序列：生成唯一的整数，一般作为自增的id避免了应用层生成自增整数而降低性能。

Create sequence  sqname minvalue 1

                      Maxvalue 9999999999

start with 1

increment by 1

nocache;

修改：alter sequence sqname

Minvalue n

Maxvalue n

Increment by n;

删除：drop sequence  sqname;

 

同义词：数据库对象的别名，用户可以使用同义词为表另外起一个名字；多个用户可以通过同义词访问别的用户的表；

创建： create  or  replace  synonym  syname  for  targetname;

删除: drop synonym syname;

 

 

DML语句：

SELECT: select  需要查询的字段 from  tablename  where  条件

在where条件中可以使用运算符= ！= <>不等于 <   >   <=  >=  like 

多个条件之间的关系 or and  not   (between and)两个之间的所有值，in(几个特定的值) 

Order by  要排序的字段 desc（降序） asc（升序）

Distinct去重查询select distinct  字段 from  tablename;

基本运算符：+ -  *  /

select sal*2  from  tablename;

简单的基本函数：

字符函数：

ASCII(<c1>) c1是字符创，返回c1的第一个字母的ascII码 它的逆函数是chr(<i>) i是整数

Concat(c1,c2)将字符串c2连接到c1后面，若其中一个为null返回另一个,都为null则返回null

Initcap(c1)每个单词第一个字母大写，其他小写返回；

Instr（c1,[c2,<i>[j]]）c2在c1中第j此次出现的位置，从c1的i位置开始，没有返回0，为负，则从右往左开始检索但是位置顺序不变；

Instrb(c1,{c2,<i>[j]})同上，返回字节；

Length(c1)返回c1长度

Lengthb（）返回长度字节

Lower(c)返回小写字符；

Soundex(c)返回与c发音相似的词；

Translate（c1,c2,c3）c1与c2中相同的字符用c3代替；

Upper（c1）返回c1的大写；

 

数字函数：

Abs(n)n的绝对值；

Acos();反余弦函数；

Asin（）；

Atan（）；

Ceil(n);返回大于或者等于n的最小整数；

Cos();

Cosh();

Exp(n) n的n次幂；

Floor(n)返回小于等于n的最大整数；

Ln（n）返回n的自然对数n必须大于0；

Log（n1,n2）；返回n1,为底n2的对数；

Mod(n1,n2)返回n1除以n2的余数；

Power（n1,n2）；n1的n2次方；

 

日期函数：

Add_months(d,<i>)返回日期d加上i各月；

Last_day(d);返回日期d月份的最后一天；

Months_between(d1,d2);返回d1,d2之间月份的个数；

Sysdate；当前时间和日期；

 

组函数：

Avg（[{列}]）返回平均值；

Max([{}]);最大值；

Min([{}]);最小值；

Stddev([{}]);返回方差的平方根；

Sum();总和；

Count();个数；

Variance();统计方差；

 

Group by:分组：having限制条件

Group by 列 having  条件；

Union  union all合并；

 

 

Insert into tablename(id,name,…) values(‘1’,’zhangsan’,…);

多表同时插入：

无条件：insert all  into  tablename1 values(…) 

                  Into  tablename2 values(…);

有条件：insert all  where 条件1 into  tablename1(…)

                                       Where 条件2 into  tablename2(…);

 

Update tablename set  name=’’  where  id=?;

 

Delete from  tablename  where id=?;

 

Merge;

 

Truncate;

 

PL/SQL语法：

注释:

单行：--  多行：/*…….*/多行注释不能嵌套；

 

数据类型：number char  varchar2  long  nchar  Boolean  date

变量声明：declare;

流程：if-then-else;

Declate

…

Begin

If

Then

Else

End if

End;

Case: case name

        When  …then….

End case;

Loop:循环：for条件loop操作….end loop;

                    While 条件loop操作…end loop;

Loop

Goto name;

End loop;

 

过程：

Create or  replace  procedure  name

                参数名称 type

                  形参    type

Is|as

Begin

End;

 

函数：create or replace function   name

参数名称 type

                  形参    type

Return {is|as}

Begin

End;