Oracle命令集

一.系统权限管理:

使用sys登陆:sqlplus / as sysdba;   
启动/停止默认监听:lsnrctl start/stop;       
启动/停止指定监听:lsnrctl start/stop [监听名] ;

查看监听器命令:lsnrctl status;
启动/停止oracl实例:oradim –starup/-shutdown -sid orcl,net start/stop OracleServiceORCL;

//oradim –starup/-shutdown -sid (数据库实例的sid);

二.对象权限:

赋予权限时,可同时赋予给该用户多个权限,权限之间用","隔开;

系统权限:

创建用户及密码：create user (用户名) identified by (密码);//刚创建好的用户是没有任何权限的,必须得用sys用户赋给一定的权限才能使用;
赋予用户对话的权限：grant create session to (用户名);//只有会话权限;
赋予用户创建表的权限:grant create table to (用户名);//可以创建表,但是没有存放表空间的权限;
赋予用户使用表空间的权限:grant unlimited tablespace to (用户名);    //不受上限的表空间权限;    至此可以创建一个正常的表;
所有权限:grant all to (用户名)[public];//赋予所有权限给(指定用户)[所有用户];

删除权限:revoke create table from (用户名)://revoke (相关权限) from (用户名):撤销该用户相关权限;

查看用户权限:
用户系统权限:select * from user_sys_privs;//当前用户系统权限表;
用户对象权限:select * from user_tab_privs;//当前用户对象权限表;

对象权限:对于表而言,限定该表的访问权限;未经授权的用户不可访问该用户所属的表;最高权限的sys可以访问所有权限的用户表;

授权给某用户查看指定表的权限:grant select on (表名) to (用户名);
授权给某用户修改指定表的权限:grant insert on (表名) to (用户名);
授权给某用户指定表的所有权限:grant all on (表名) to (用户名);

授权给某用户指定表的指定列的权限:    grant (权限) on (表名) to (用户名);    //例子:grant update(name) on testTable to user_1;授权给user_1

更新表名为testTable的权限(仅限于更新和插入);(貌似有点过于BT了)

查看某个用户的指定表的内容:select (内容) from (用户名).(表名);
在某个用户的指定表内插入内容:insert into (用户名).(表名) (列1 类型,列2 类型,列3 类型...);

谁拥有该表,谁就可以给其它的用户授权;

//grant create session to (用户名);

三.表的操作;
创建序列：create sequence StudentPKSequence start with  1 increament by 1;!
取序列的当前值；select StudentPKSequence.currVal from dure;
创建一个表:create table (表名) (列1 类型,列2 类型,列3 类型...);//数字为int型,字符为char(长度);
在表中插入一行数据:insert into (表名) values(列1 值,列2 值,列3 值...);//列的值对应表的列值类型;
在表中插入一列数据:alter table(表名) add (列名2 类型,列名3 类型...);//数字为int型,字符为char(长度);
提交保存文件:commit;//如果未提交,数据是不会保存到表中的;
删除表:drop table (表名);//一个用户有创建表的权限,就有增/删/改/查该表的权限;
从指定文件中运行sqlplus命令:start (filename);//注意文件后缀名;
增/删/改之后必须要提交更改;

角色是权限的集合;对象权限是用户之间表的访问权限;

事务:

安全点: savepoint (安全点名称);//安全点可以有多个;
回滚:rollback[安全点名称];//回滚到已经设定的安全点;

Oracle中的函数;

显示系统日期:(日期转字符串)

select to_char(sysdate,'yyyy"年"-mm-dd hh24:mi:ss') from dual;

select to_char(sysdate,'yyyy"年"-mm-dd hh24:mi:ss')"现在时间是:" from dual

(字符串转日期)
inster into emp values (0884,'aa','aa',1234,to_date('2008-12-10 13:22:33','yyyy-mm-dd hh24:mi:ss'),'','');

sys  网络管理员
system  普通管理员
scott   普通用户

commit rollback
oracle中
只有增删改查 是手动提交。
其他是自动提交。

查询部分用rownum,但是rownum不支持大于和大于等于，只支持小于和小于等于。
select count(*) from emp where rownum<=20;
select count(*) from emp where rownum<=20;

insert into emp values (8005,'aa','aa',7369, to_date('2008-12-10 12:23:13','yyyy-mm-dd hh24:mi:ss'),'1000','20',null);
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

update emp set sum=(sal+nvl(comm,0));

insert into dept values(21,'aa','null');
insert into dept values(21,'aa',null);
insert into dept (deptno,dname) values(12,'itcast');

update dept set dname='itcast+++' where deptno<30;

delete from emp where empno<900;

create table temp as select deptno,dname from dept;

update (select dname from dept) tab set tab.dname='--';
update dept set (dname,loc) = (select ename,job from emp where empno = 7369) where deptno=41;
==============================================================表的导入和导出：
导入imp；导出exp;

夫妻：一对一关系；
仓库与产品：一对多关系；
学生与课程：多对多关系；

start nomount 仅仅启动列程
alter database mount装载数据库
select * from v$database;可以查看数据库信息。
alter database archivelog 归档数据库 ; 不归档就是noarchivelog
archivelog log list;
alter database open;打开数据库；

linux或unit下分步骤启动数据库

lsnrctl start （启动监听）
sqlplus / as sysdba （用sys用户连接数据库）
startup nomount; （打开例程）
alter database mount;  （装载数据库）
alter database open;（打开数据库）

window 系统下分步骤启动数据库

lsnrctl start
oradim -startup -sid orcl （window比较特殊，要先启动服务再用shutdown 关闭，才能分步骤启动）
shutdown;
startup nomount;
alter database mount;
alter database open;
==============================================================Where和Having的区别:
1.分组前,不能使用聚合函数
2.分组后,使用聚合函数.
事务和SQL函数:
只有增/删/改才需要提交;
可以设置默认提交:
Set autocommit on;打开默认提交;
Set autoeommit off;关闭默认提交;
打开默认提交后,对数据库进行增/删/改后,数据库会自动帮用户提交该文件的操作;
获得当前时间: select sysdate from dual;
虚拟表:dual;
BLOB,CLOB用来存放大型数据;用来存放电话或是图片数据;
BLOB,存放二进制文件.CLOB用来存放(文本)字符文件.最大上限4G;
一般不会直接把文件放进数据库里,再用流读出来.常用方法是把路径写进表中.然后读出来,找到相关路径下去;
==============================================================数据类型
number(p,s)
char(n) nchar(n) varchar(n) nvarchar(n) varchar2(n) nvarchar2(n)
date
blob clob nclob
主键约束：create table demo(id int constraint demo_pk primary key,name varchar(10));  特点：不能为空;
  create table demo(id int,name varchar(10),constraint demo_PK primary key(id));
外键约束: 1 插入数据的时候外键的值必须在对应的主键中可以找到，或插入null
  2 删除数据的时候，必先删除外键，再删除主键。
    create table employees(
               id int,
              name varchar(10),
               deptno int,
              foreign key(deptno) references dept(deptno)
)

    create table employees1(
               id int,
               name varchar(10),
               deptno int  references dept(deptno)
            )

唯一约束: create table demo1 (id int ,name varchar(10),primary key (id),unique(name)); （可以为null）
      create table demo2 (id int ,name varchar(10) unique,primary key (id));
非空约束：create table demo(id int.name varchar(10) not null);
默认约束：create table demo(id int,name varchar(10)  default 'lise');name默认的是李四；
check约束：create table demo(id int,name varchar(10) default 'lisi' not null,age int check (age>0 and age<150));
查询约束：desc user_constraints(描述当前用户下的约束条件)

修改表：
添加列：alter table demo add adress char(10);
删除列：alter table demo drop column address;
删除主键约束：alter table demo drop constraints PK_demo;
修改列的类型：alter table demo modify name char(20);
        SQL> alter table demo2 add constraint PK_demo2 primary key (id);
        表已更改。
        SQL> alter table demo2 drop constraints PK_demo2;
              alter table demo2 add primary key (id);
==============================================================连接分类：
内连接：主外键都能对应上才能联合起来。select * from dept inner join emp on dept.deptno=emp.deptno
外连接：
    左外连接：以左边表为主select * form emp,dept where emp.deptno=dept.deptno(+);
  select * from emp left outer join dept on emp.deptno=dept.deptno ;

    又外连接：以右边表为主select * form emp,dept where emp.deptno(+)=dept.deptno;
  select * from emp rigth outer join dept on emp.deptno=dept.deptno ;
    完全外连接：select * from emp full outer join dept on
交叉连接：select * from emp,dept;(没有意义的查询)emp20,dept10,总共200行
子查询：
    相关子查询：select * from emp e where sal> (select avg(sal) from emp e1 where e1.deptno=e.deptno);括号里的查询不能单独查询。
    嵌套子查询：select danme from dept where deptno=(select deptno from demp where ename='SMITH');(子查询是可以单独执行查询的)

三范式：就是三个原则，
1.在列里是单值，不能为双值。
2.表里的每列要完全依赖于主键，而不能部分依赖。
3.不允许依赖传递。

==============================================================

PL/SQL面向过程的语言：(银行里有可能用到)
1.
begin
   null;
end;
2.
begin
dbms_output.put_line('hello world');(line相当于回车换行)
end;
3.==========================set serveroutput on(打开服务器段的输出打开);
4.declare 
      id int;(变量的声明)
    begin
        id:=5;
        dbms_output.put_line(id);
    end;
5.
declare
id int:=4;
name varchar2(10);(查询赋值)
begin
select ename into name from emp where empno=7369;
dbms_output.put_line(name);
end;
6.
declare
id int:=4;
name emp.ename%type;(定义的列类型声明变量)
begin
select ename into name from emp where empno=7369;
dbms_output.put_line(name);
end;
7.
declare
    id int:=7369;
    emprow emp%rowtype;(行级变量的赋值)
begin        (不能写查询语句)
    select * into emprow from emp where empno=id;
    dbms_output.put_line(emprow.ename||'--->'||emprow.job);
end;
8.
declare
    id int:=&请输入id;(用户输入的值)
    emprow emp%rowtype;
begin
    select * into emprow from emp where empno=id;
    dbms_output.put_line(emprow.ename||'--->'||emprow.job);
end;

10.
写个完整的:
declare
    id int:=&id;
    myrow emp%rowtype;
begin
    select * into myrow from emp  where empno<id;
    dbms_output.put_line(myrow.empno||--->||myrow.ename);
EXCEPTION
    when too_many_rows then
    dbms_output.put_line('toooooooooooooo  many!!!');
    when no_data_found then
    dbms_output.put_line('noooooooooooooo data!!!!!');
    when others then
    dbms_output.put_line('error');
end;
循环：四种
==========================loop循环：
declare
    id int:=1;
begin
    loop
        id:=id+1;
        dbms_output.put_line(id);
exit when
    id=20;
    end loop;
end;
==========================while循环：

declare
    id int:=1;
begin
    while(id<20)   
    loop
        id:=id+2;
        dbms_output.put_line(id);
    end loop;
end;
===========================for循环：
begin
    for i in 2..10
    loop
        dbms_output.put_line(i);
    end loop;
end;
===========================for控制反转循环，倒叙循环；
begin
    for i in reverse 1..10   (控制反转；倒叙的)
    loop
        dbms_output.put_line(i);
    end loop;
end;

条件：
declare
    id int:=0;
begin
    if(id=0) then
        dbms_output.put_line('id=0');
    else
        dbms_output.put_line('id<>0');
    end if；
end;

declare
    id int:=0;
begin
    if(id=0) then
        dbms_output.put_line('id=0');
    elsif(id=1) then
        dbms_output.put_line('id=1');
    elsif(id=2) then
        dbms_output.put_line('id=2');
    else
        null;(如果不写输出语句会包错)
    end if;
end;
===================================================
存储过程:
定义变量 declare
赋值 :=  &    select .. into...   (两种类型异常)
循环 loop  while for
分支 if   if  elsif  case
pl/sql中不能写查询

存储过程---函数（return）
存储过程：
格式（形参 in out in out 不写精度）  as/is
调用  exec pl/sql
事务
====================================================
如何调用存储过程：在sqlplus里调用：他可以写增，删，改，不能写查询语句。而这些对数据改动的需要对事物提交；
1.execute 存储名（ 里面的参数默认为向里传，如果写一个out是向外传）；
存储过程的参数可以向里传值，可向外传值，也可向里向外传值；       
    create or replace produre mypro(id int,name out varchar)
    as
    begin
        select dname into name from dept where deptno=id;
    end;
2.PL/SQL 代码块调用；(调用上面的存储过程向外传参数的列子)
    declare
        name varchar(10);
        id int:=10;
    begin
    mypro(id,name);
        dbms_output>put_line(name);
    end;
存储过程放在什么地方？

客户端的工具里面查看，原程序，过程，中有存储过程名。

函数：
return
调用函数:sql pl/sql
    create or replace procedure myproc(id int)as
        name dept.dname%type;
    begin
        select danme into name from dept;
        dbms_output.put_line(name);
    end;

有两中调用方式：1.代码块 2.sql语句调用
1.
    create or replace function myfun(name varchar2)return varchar2 is(as)
        i int;
    begin
        select count(*) into i from emp where ename=name;
    return(i);
    end myfun;

调用：
1.select myfun('smith') from dual(默认序列表);(sql调用)
2.declare（代码块调用）
    i int;
  begin
    i:=myfun('smith');
    dbms_output.put_line(i);
  end;

自治事务:pragma autonomous_transactions;

create or replace procedure log_errors(p_error_message in varchar2) as
    pragma autonomous_transaction;
begin
    insert into error_logs
        (id, log_timestamp, error_message)
    values
        (error_logs_seq.nextval, systimestamp, p_error_message);
    commit;
end;

CREATE TABLE error_logs (
      id NUMBER(10) NOT Null,
      log_timestamp TIMESTAMP NOT NULL,
      error_message VARCHAR2(4000),
      CONSTRAINT error_logs_pk PRIMARY KEY (id)
    );
CREATE SEQUENCE seq_error_logs;

BEGIN
insert into emp(empno,ename,deptno)values(seq_emp.nextval,'部门','exception');
EXCEPTION WHEN OTHERS THEN log_errors(p_error_message => SQLERRM); ROLLBACK; END;
查看错误信息：
show errors;
desc user_errors;
select * from user_errors;
SQL练习：
http://www.sqlzoo.cn/

转载于:https://www.cnblogs.com/sanic/archive/2011/12/11/2283813.html