Oracle学习笔记

Dear 丶 2022-08-07 13:47 497阅读 0赞

\-1.开启Oracle监听器        lsnrctl start/stop              
\--------------------------------------------------------------------------------------------------------------  
0.开启Oracle主程序        net start/stop OracleServiceORCL  
\--------------------------------------------------------------------------------------------------------------  
1.断开当前的连接        disc;  
\--------------------------------------------------------------------------------------------------------------  
2.转换登录角色            conn 用户名/密码;  
\--------------------------------------------------------------------------------------------------------------  
3.修改当前登录账户的密码    password 用户名;  
\--------------------------------------------------------------------------------------------------------------  
4.创建登录角色            create user 用户名 identified by 密码 \[default tablespace 表空间名\];  
\--------------------------------------------------------------------------------------------------------------  
5.修改指定用户的密码        alter user 用户名 identified by 密码;  
\--------------------------------------------------------------------------------------------------------------  
6.删除角色            drop user 用户名 \[cascade\];    //cascade用于删除有内容的角色  
\--------------------------------------------------------------------------------------------------------------  
7.权限的分配            grant connect/dba/resource/debug connect session to 用户名;      
\--------------------------------------------------------------------------------------------------------------  
8.权限的撤销            revoke connect/dba/resource from 用户名;  
\--------------------------------------------------------------------------------------------------------------  
9.指定某个用户在某个表上做某事    grant select/delete/update/insert/execute on 表名/存储过程/视图 to 用户名;  
\--------------------------------------------------------------------------------------------------------------  
10.允许对象/系统权限传递    grant select on 表名to 用户名 with grant/admin option;  
\--------------------------------------------------------------------------------------------------------------  
11.账户锁定机制            create profile 锁文件名 limit failed\_login\_attempts 3 password\_lock\_time 2;  
\--------------------------------------------------------------------------------------------------------------  
12.指定用户实现锁定机制        alter user 用户名 profile 锁文件名;  
\--------------------------------------------------------------------------------------------------------------  
13.给指定用户解锁        alter user 用户名 account unlock;  
\--------------------------------------------------------------------------------------------------------------  
14.删除锁            drop profile 锁文件名 \[cascade\];  
\--------------------------------------------------------------------------------------------------------------  
15.添加一个字段            alter table 表名 add 字段名 数据类型;  
\--------------------------------------------------------------------------------------------------------------  
16.修改字段的长度.类型/或是名字    alter table 表名 modify 字段名 数据类型;  
\--------------------------------------------------------------------------------------------------------------  
17.删除一个字段            alter table 表名 drop column 字段名;  
\--------------------------------------------------------------------------------------------------------------  
18.修改表的名字            rename 原表名 to 新表名;  
\--------------------------------------------------------------------------------------------------------------  
19.删除表            drop table 表名;  
\--------------------------------------------------------------------------------------------------------------  
20.查看表的结构            desc 表名  
\--------------------------------------------------------------------------------------------------------------  
21.修改日期的默认格式        alter session set nls\_date\_format='yyyy--MM--dd';  
\--------------------------------------------------------------------------------------------------------------  
22.创建回滚点            savepoint 回滚点名;  
\--------------------------------------------------------------------------------------------------------------  
23.返回至回滚点            rollback to 回滚点名;  
\--------------------------------------------------------------------------------------------------------------  
24.运行脚本            start或@  
\--------------------------------------------------------------------------------------------------------------  
25.通过记事本编辑指定的SQL脚本        ed 或edit  
\--------------------------------------------------------------------------------------------------------------  
26.将屏幕的内容输入到指定的文件夹    spool    spool off  
\--------------------------------------------------------------------------------------------------------------  
27.清空回收站            purge recyclebin  
\--------------------------------------------------------------------------------------------------------------  
28.导出表            exp userid=user\_name/user\_pwd@ORCL  tables=(tables\_Name) filed=d:\\file\_name.dmp rows=n(导出表结构) direct=y(直接到导出方式)  
\--------------------------------------------------------------------------------------------------------------  
29.导出方案            exp userid=user\_name/user\_pwd owner=user\_name filed=d:\\file\_name.dmp  
\--------------------------------------------------------------------------------------------------------------  
30.查看角色的系统权限        dba\_sys\_privs  
\--------------------------------------------------------------------------------------------------------------  
31.查看角色的对象权限        dba\_tab\_privs  
\--------------------------------------------------------------------------------------------------------------  
32.使表空间脱机            alter tablespace name offline  
\--------------------------------------------------------------------------------------------------------------  
33.使表空间联机            alter tablespace name online  
\--------------------------------------------------------------------------------------------------------------  
34.显示错误信息            show error;  
\--------------------------------------------------------------------------------------------------------------  
35.创建存储过程            create procedure name is begin......end  
\--------------------------------------------------------------------------------------------------------------  
36.打印内容            dbms\_output.put\_line('')  
\--------------------------------------------------------------------------------------------------------------  
37.添加注释            comment on table table\_name is ''  
\--------------------------------------------------------------------------------------------------------------  
38.添加列名的注释        comment on column table\_name.column\_name is ''  
\--------------------------------------------------------------------------------------------------------------  
39.两日期间的礼拜6,7        select \*from  
                    (select decode(trim(to\_char(sysdate + rownum,'day', 'nls\_date\_language=American')),'saturday',  
                            sysdate + rownum, 'sunday',  
                            sysdate + rownum, null) t  
                    from dual connect by rownum < 100) a where a.t is not null;  
\--------------------------------------------------------------------------------------------------------------  
40.建立数据表的主键约束        alert table table\_name add constraint pk\_name primary key(column\_name);  
\--------------------------------------------------------------------------------------------------------------  
41.建立数据表的外键约束        alert table table\_name add constraint fk\_name foregin key(column\_name) references table\_name(column\_name);  
\--------------------------------------------------------------------------------------------------------------  
42.建立数据表的check约束    alert table table\_name add constraint check\_name check(column\_name in('','','',''));  
\--------------------------------------------------------------------------------------------------------------  
43.建立数据表的唯一约束        alert table table\_name add constraint nu\_name unique();  
\--------------------------------------------------------------------------------------------------------------  
44.利用现有的表创建新表        create table table\_name as select \* from other\_table\_name;  
\--------------------------------------------------------------------------------------------------------------  
45.创建索引            create nique/bitmap index index\_name on table\_name(column\_name) \[reverse\];  
\--------------------------------------------------------------------------------------------------------------  
46.创建分区的方法        create table table\_name(column\_name type....)  
                partition by range(column\_name)  
                \[interval(numtoyminterval(3,'month'))\]    --自动间隔分区    //当使用纯数字分区的时候,interval后面的参数函数可以不用写  
                (  
                    partition p1 values less than(to\_date("2013-4-1","yyyy-MM-dd")),  
                    \[partition p2 values less than(to\_date("2013-7-1","yyyy-MM-dd")),\]  
                    \[partition p3 values less than(to\_date("2013-10-1","yyyy-MM-dd")),\]  
                    \[partition p4 values less than(to\_date("2014-1-1","yyyy-MM-dd")),\]  
                    \[partition p5 values less than(maxvalue)\]  
                )  
                \[as select \* from sales;\]    --sales表示已经创建的表  
        //查询自定标的分区结构  select table\_name,partition\_name from user\_tab\_partitions where  table\_name=upper('sale');  
        //查看某一季度的数据    select \* from table\_name partition(p1);  
        //删除某一季度的数据    delete from table\_name partition(p1);  
\--------------------------------------------------------------------------------------------------------------  
47.各种系统表    1.用户表        dba\_users  
        2.表空间限额表        dba\_ts\_quotas      
        3.表空间表        dba\_data\_files  
        4.用户间隔分区表    user\_tab\_partitions  
        5.视图            user\_views  
        6.索引            user\_indexes;  
        7.触发器        user\_tiggers;  
        8.同义词        syn;  
        9.序列            seq;  
        10.当前用户所有的表    user\_tables;  
        11.数据库链路        user\_db\_links;  
        12.约束限制        user\_constraints;  
\--------------------------------------------------------------------------------------------------------------  
48.创建序列  
        CREATE SEQUENCE seq\_name  
        START WITH 10  
        INCREMENT BY 1  
        MAXVALUE 2000  
        NOCYCLE  
        CACHE 30;  
\--------------------------------------------------------------------------------------------------------------  
49.自定义异常的名字    pragma exception\_init('自定义异常的名字',错误的编号);  
\--------------------------------------------------------------------------------------------------------------  
50.系统预定义异常      
    1.access\_into\_null         //未初始化对象  
    2.case\_not\_found        //case语句的选项与用户输入的数据不匹配  
    3.no\_data\_found            //在表中不存在用户请求的数据  
    4.value\_error            //数据的大小限制出现错误  
    5.collection\_is\_null        /给尚未初始化的表和数组赋值  
    6.cursor\_already\_open        //游标已经打开      
    7.dup\_val\_on\_index;        //将重复的值存在唯一索引的列上  
    8.invalid\_cursor;        //执行非法游标运算  
    9.invalid\_number;        //将字符串转换成数字  
    10.login\_denied;        //输入的用户名或密码无效  
    11.storage\_error;        //内存损坏,PL/SQL耗尽内存  
    12.too\_many\_rows        //执行select into返回多行数据  
    13.zero\_divide            //0作为除数进行运算  
\--------------------------------------------------------------------------------------------------------------  
51.自定义动态游标  
    declare  
        type type\_name is ref cursor \[reurn emp%rowtype\];        //\[..\]的内容为类型的限制  
        cursor\_name type\_name;                        //自定义游标的使用  
        cursor\_name sys\_refcursor;                    //系统自定的游标类型  
    begin  
        open cursor\_name for select \* from emp;  
    end;  
\--------------------------------------------------------------------------------------------------------------  
52.执行SQL语句的DDL操作  
    execute immediate sql\_name \[using\] \[v\_name\]  
\--------------------------------------------------------------------------------------------------------------  
53.表级锁的建立  
    lock table table\_name in share mode;  
\------------------------------------------------------------->>>>>>>>>>>>>>>>>>>>>>解锁的机制为rollback/commit;  
54.行级锁的建立  
    select \* from table\_name where empno=v\_no for update;        //锁定empno为v\_no的这一行纪录  
\--------------------------------------------------------------------------------------------------------------  
55.存储过程的创建  
    create or replace procedure proc\_name( cur\_name in/out sys\_refcursor,v\_name in/out type\_name)  
    is/as  
    begin  
        //open cur\_name for select \* from table\_name \[where\]  
        //执行体  
    end;  
\--------------------------------------------------------------------------------------------------------------  
56.触发器的建立  
    create or replace trigger biud\_trig\_name  
        before/after insert or update or delete of columns\_name on table\_name  
        for each row            //设置该触发器是行级触发器还是语句级触发器  
        when()                 //设置该触发器触发的条件限制  
    begin  
        //触发后的执行体部分.下面为一些事例  
        if inserting then ....  
        end if;  
        if updating then ....  
        end if;  
        if deleting then ....  
        emd if;  
    end;  
\--------------------------------------------------------------------------------------------------------------  
57.程序包的建立  
    create or replace package package\_name  
    is/as  
        type test\_cursor is ref cursor;            //游标的建立  
        minsal emp%rowtype;  
        maxsal emp%rowtype;  
        procedure add\_emp(empno number,ename varchar2,sal number,dno number);  
        procedure upd\_sal(empno number,sal number);  
        procedure upd\_sal(ename varchar,sal number);  
        function get\_sal(empno number) return number;  
    end;  
    ------------  
    create or replace package body  package\_name  
    is/as  
        type test\_cursor id ref cursor;  
    end;  
\--------------------------------------------------------------------------------------------------------------  
58.自定义函数的建立  
    create or replace function function\_name(name in varchar2) return number  
    is  
    result number;  
    begin  
        select count(\*) into result from student where name=name;  
        return(name);  
    end;  
    --执行        var result number;  
            exec :result:=function\_name('scott');  
\--------------------------------------------------------------------------------------------------------------  
59.Oracle的数据类型  
    VARCHAR2(size)        可变长度的字符串,其最大长度为size个字节;size的最大值是4000,而最小值是1;你必须指定一个VARCHAR2的size;  
    NVARCHAR2(size)        可变长度的字符串,依据所选的国家字符集,其最大长度为size个字符或字节;size的最大值取决于储存每个字符所需的字节数,其上限为4000;你必须指定一个NVARCHAR2的size;  
    NUMBER(p,s)        精度为p并且数值范围为s的数值;精度p的范围从1到38;数值范围s的范围是从-84到127;  
    LONG   可变长度的字符数据,其长度可达2G个字节;  
    DATE   有效日期范围从公元前4712年1月1日到公元后4712年12月31日;  
    RAW(size)   长度为size字节的原始二进制数据,size的最大值为2000字节；你必须为RAW指定一个size;  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
Oracle中的LOB数据类型          
                1.CLOB  存储大量的字符数据  
                2.BLOB    存储大量的二进制数据  
                3.BFILE 能够将二进制文件存储在数据库外部的操作系统的文件中  
                4.NCLOB 存储大 的NCHAR字符数据  
Oracle中的集合操作符  
                1.UNION        联合并集....取得两个集合中不重复的数据  
                2.UNION ALL    联合所有....取得两个集合中所有的数据,包含重复的数据  
                3.INTERSECT    交集    ....取得两个集合中相同的数据的部分内容  
                4.MINUS        减集    ....取得前个集合中与后个集合中.不相同的内容  
\--------------------------------------------------------------------------------------------------------------  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#常用的字符函数\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\--------------------------------------------------------------------------------------------------------------  
1.首字母大写            initcap('hello')                                 Hello  
\--------------------------------------------------------------------------------------------------------------  
2.转为为大写            upper('hello')                            HELLO  
\--------------------------------------------------------------------------------------------------------------  
3.转换为小写            lower('HELLO')                            hello  
\--------------------------------------------------------------------------------------------------------------  
4.左裁剪            ltrim('    hello')                        hello  
\--------------------------------------------------------------------------------------------------------------  
5.右裁剪            rtrim('hello    ')                        hello  
\--------------------------------------------------------------------------------------------------------------  
6.两边裁剪            trim('    hello    ')                        hello  
\--------------------------------------------------------------------------------------------------------------  
7.按字符翻译            translate('hello','helo','1234')                12334  
\--------------------------------------------------------------------------------------------------------------  
8.替换字符串            replace('hello','l','p')                    heppo  
\--------------------------------------------------------------------------------------------------------------  
9.查找子串的位置        instr('hello','e')                        2  
\--------------------------------------------------------------------------------------------------------------  
10.取子字符串            substr('hello',2,2)                        el  
\--------------------------------------------------------------------------------------------------------------  
11.连接字符串            concat('hello','word')                        helloword  
\--------------------------------------------------------------------------------------------------------------  
12.字符串的长度            length('hello')                            5  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#常用的数字函数\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\--------------------------------------------------------------------------------------------------------------  
1.取绝对值            abs(-15)                            15  
\--------------------------------------------------------------------------------------------------------------  
2.向上取整            ceil(15.5)                            16  
\--------------------------------------------------------------------------------------------------------------  
3.向下取整            floor(15.5)                            15  
\--------------------------------------------------------------------------------------------------------------  
4.正弦                sin(1.571)                            .999999  
\--------------------------------------------------------------------------------------------------------------  
5.余弦                cos(0)                                1  
\--------------------------------------------------------------------------------------------------------------  
6.取符号            sign(-32)                            -1  
\--------------------------------------------------------------------------------------------------------------  
7.m的n次幂            power(4,2)                            16  
\--------------------------------------------------------------------------------------------------------------  
8.取余数            mod(10,3)                            1  
\--------------------------------------------------------------------------------------------------------------  
9.四舍五入            round(1.437,2)                            1.44  
\--------------------------------------------------------------------------------------------------------------      
10.截断                trunk(1.437,2)                            1.43                      
\--------------------------------------------------------------------------------------------------------------  
11.平方根            sqrt(4)                                2  
\--------------------------------------------------------------------------------------------------------------  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#常用的日期函数\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
1.返回两个日期间的月份            months\_between('01-01月-2013','01-01月-2014')        -12  
2.返回把月份数加到日期上        add\_months('01-01月-2013',1)                01-02月-2013  
3.返回指定日期后的星期对应的新日期    next\_day('06-2月-03','星期一')                10-2月-03  
4.返回指定日期所在月的最后一天        last\_day('06-2月-03')                    28-2月-03  
5.按指定格式对日期进行四舍五入        round(to\_date('13-2月-03'),'year')            01-1月-03  
                    round(to\_date('13-2月-03'),'month')            01-2月-03  
                    round(to\_date('13-2月-03'),'year')            16-2月-03  
\--------------------------------------------------------------------------------------------------------------  
6.对日期按指定方式进行截断        trunc(to\_date('06-2月-03'),'year')            01-1月-03  
                    trunc(to\_date('06-2月-03'),'month')            01-2月-03  
                    trunc(to\_date('06-2月-03'),'day')            02-2月-03  
\--------------------------------------------------------------------------------------------------------------  
7.提取当前的日期的年月日        extract(year from sysdate)  
                    extract(month from sysdate)  
                    extract(day from sysdate)  
\--------------------------------------------------------------------------------------------------------------  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#常用的转换函数\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
1.转换成字符串类型            to\_char(1234.5,'$9999.9')                $1234.5  
2.转换成日起类型            to\_date('2013-1-1','yyy-MM-dd')                01-1月-2013  
3.转换成数值类型            to\_number('1234.5')                    1234.5  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#常用的其它函数\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
1.对空值的处理                nvl(exp1,exp2)//如果exp1为null.则返回exp2 否则返回exp1  
2.对空值的处理                nvl2(exp1,exp2,exp3)//如果exp1为null.则返回exp2 否则返回exp3  
3.对value值的多处理            decode(value,if1,then1,if2,then2...,else)//如果value1为if1,则返回then1,如果为if2,则返回then2,否则返回else  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#分组函数-聚合函数\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
1.求和函数                sum()  
2.平均函数                avg()  
3.记录函数                count()  
4.最大值函数                max()  
5.最小值函数                min()  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#常用的分析函数\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\--------------------------------------------------------------------------------------------------------------  
1.通过某字段排序(等值 不重复 顺排)    select row\_number() over (order by column\_name) from tables\_name  
\--------------------------------------------------------------------------------------------------------------  
2.通过某字段排序(等值 重复 跳跃性)        select rank() over (order by column\_name) from tables\_name  
\--------------------------------------------------------------------------------------------------------------  
3.通过某字段排序(等值 重复 顺排)        select dense\_rank() over(order by column\_name) from tables\_name  
\--------------------------------------------------------------------------------------------------------------  
4.通过分组,和某个字段来排序        select dense\_rank() over(partition by deptno order by column\_name) from tables\_name  
\--------------------------------------------------------------------------------------------------------------  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#PL/SQL DEVELOPER常用的操作技巧\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
1.调试SQL语句的性能            选中要执行的SQL语句   按F5  
2.将选中的SQL语句进行大小写转换        Edit ->Selection ->Uppercase/Lowercase  
3.将经常用的目录靠前显示        Tools ->Object Brower Folders  
4.双击显示表格的数据            Tools ->Preferences ->Browser  
5.SQL语句关键字大小写            Tools ->Preferences ->Editor ->KeyWord Case ->Uppercase  
6.快捷字符替换的设置            Tools ->Preferences ->Editor ->AuotReplace  ->Edit  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------  
\--------------------------------------------------------------------------------------------------------------