oracle常用语句自我小结

（对oracle9/10g使用的总结，部分sql语句都实用）

-------------第一步oracle的启动过程-----------
在window下：

启动数据库实例
oradim -startup -sid orcl(数据库实例名称);
关闭数据库实例
oradim -shuntdown -sid orcl;
启动监听
lnsrctl -start ;
关闭监听
lnsrctl -shuntdown;

在Linux下启动过程：
lsnrctl start --启动监听
        //sqlplus sys/manager as sysdba --oracle10中可以这么写
        sqlplus /nolog
        conn sys/oracle as sysdba
        startup --启动数据库实例

------进入oracle使用system---------
一般来说，在window下：
启动中的框中输入：sqlplus 进入到oracle的sqlplus登录界面中。

sys/ as sysdba //此登录命令为 用户名sys/密码为空 以数据库管理员的身份进入到oracle。
对于一般的当前window登录用户为sysdba的话，都能登录成功的。不过这中登录方式不够安全。

system/meiling //此登录命名为，使用系统管理员的身份进入到oracle中，密码为meiling（此密码是在）
安装oracle的时候自己设置的一定要记住。

---------用户操作------------------
SQL> create user test1 identified by test1 ;//创建一个test1用户密码为test1
SQL> grant r1 to test1;//把角色r1赋予test1用户
SQL> grant unlimited tablespace to test1;//给test1用户赋予不限制使用表空间的权限，此权限不能赋予给角色。只能给用户。
SQL> drop user test1;//删除用户

一般来说创建一个用户的过程如下：
SQL> create user test identified by test;//创建用户

用户已创建。

SQL> grant connect to test;//给用户赋予登录的角色

授权成功。

SQL> grant resource to test;//给用户赋予使用资源的角色，也就是可以对表的曾删改查创建序列视图等。

授权成功。

-----------角色操作
SQL> create role r1 ;//创建了一个r1角色；
SQL> grant create table to r1;//给r1赋予创建表的权限
SQL> grant create session to r1;//给r1赋予创建回话的权限，也就是登录权限。
SQL> drop role r1;//删除角色；
SQL> revoke dba from test;//把test用户的dba角色撤销
数据库中预先定义的角色：

1）CONNECT、RESOURCE、DBA  这几个权限比较常见

2）SELECT_CATALOG_ROLE：查询数据字典的权限

3）DELETE_CATALOG_ROLE：删除数据字典的权限

4）EXECUTE_CATALOG_ROLE：执行数据字典包的权限

---创建数据（一般对于程序员来说这步不是必须的，是由dba来创建的）
一般来说，创建数据库有三种方式：
1，使用GUI视图界面方式创建，也就是在：“Oracle Database Configuration Assisstant”中创建。
2，用命令行方式创建数据库。
3，通过指定批处理文件进行数据库的创建。

------------表操作（对于程序员来书是必须会的）
create table usertable(
userid number(9) primary key ,
username varchar2(64) not null,
password varchar2(128) not null,
sex varchar2(10),
age number(3),
address varchar2(1000)
)//创建一个usertable表

SQL> alter table usertable add phone varchar2(32);//修改表结构：给表添加一行phone
SQL> alter table usertable modify phone varchar2(40);//修改表结构：修改phone的数据类型大小为varchar2(40)
SQL> alter table usertable drop column phone;//修改表结构：删除了字段phone。
SQL> insert into usertable values(1,'yxh','yxh','男',26,'138956322114');//插入数据
SQL> update usertable set username='yuanxh' where userid=1;//修改数据
SQL> select * from usertable;//查询数据
SQL> delete from usertable where userid=1;//删除数据
1* insert into usertable values(user_sequence.nextval,'yxh','yxh','男',26,'ads
fdsfa')//使用创建的序列作为主键插入表中。
SQL> alter table usertable rename to users;//更改表名
SQL> comment on table users is '用户表';//给表添加注释
SQL> comment on column users.userid is '用户表id';//给users表userid字段添加注释

-----------序列操作（程序员必须会）
SQL> create sequence user_sequence start with 1 increment by 1 maxvalue 1000000;//创建序列

SQL> alter sequence user_sequence minvalue 0;//修改序列但是，start with 这项是不能修改的。

SQL> select user_sequence.nextval from dual;//使用sql获取序列user_sequence的下一个值

------------视图的操作（程序员必会）

SQL> create view user_view  as select * from usertable;//创建视图
SQL> create or replace view user_view as
  2  select "USERID","USERNAME","PASSWORD","SEX","AGE" from usertable;//修改视图的内容
SQL> select * from user_view;//使用视图

---------存储过程的使用（程序员必会）
  创建存储过程包含输入输出的参数
  1  create or replace procedure user_proc(userid out number,username in varchar
2,password in varchar2)
  2  is begin
  3  insert into usertable(userid,username,password) values(user_sequence.nextva
l,username,password);
  4  select user_sequence.currval into userid from dual;
  5* end user_proc;

执行存储过程
在command界面中：
exc user_proc('','');//只适用于足有输入参数的时候
execute user_proc('','');//只适用于足有输入参数的时候
call user_proc('','');//只适用于足有输入参数的时候
begin user_proc('','') end; //只适用于足有输入参数的时候

//带有输入输出的参数的执行：
SQL> set serveroutput on
SQL> DECLARE
  2    spno number;
  3    spname varchar2(10);
  4  BEGIN
  5    sp_pro10 (spno, spname);
  6   
  7    dbms_output.put_line(spname);
  8  END;
  9  /

-----用户对象的导入导出也就是exp、imp命令的使用此命令相当于备份还原了
导出exp命令的使用
数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
   exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
   exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
    exp aichannel/aichannel@TESTDB2 file= d:\datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
   exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=" where filed1 like '00%'"

导出远程主机上的方法：

C:\Users\zdvictory>exp gds/123@192.168.3.193/orcl file=e:\data\gddzjc.dmp tables
=(om_organization)

数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
   imp system/manager@TEST  file=d:\daochu.dmp
   imp aichannel/aichannel@TEST  full=y  file=d:\datanewsmgnt.dmp ignore=y
   上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
   在后面加上 ignore=y 就可以了。
2 将d:daochu.dmp中的表table1 导入
imp system/manager@TEST  file=d:\daochu.dmp  tables=(table1)