Oracle学习笔记

ORACLE采用自右向左的顺序连接表，自下向上的顺序解析WHERE子句。根据这个原理,表之间的连接时，数据量较小的表应该放在最右边 作为基础表，连接必须写在其他WHERE查询条件之前，能过滤掉大部分数据的条件要放在句末

注意：1）不要在select添加查表的子查询，否则量级会达到两个表的笛卡儿积，尽量用表连接

如：
select a.a1,(select b.b2 from b where a.b1 = b.b1)  from a; 
--错误写法，除非有关于b的where条件使得b的select集很小，否则查询的量级为 a的记录数 乘 b的记录数；
应改为：
select a.a1,b.b2 from a,b where a.b1 = b.b1(+); --a右连接b

命令行下

1、命令行中SQL语句可以用回车来分行，*表示当前行

2、-- 之后是注释

3、[ ]的内容为可省略内容

4、Dual表是一个Oracle一个特殊空表，常用来通过select语句计算常数表达式；

登陆系统

（备注：注意空格）

语法：sqlplus 用户名/密码 [@host_string] [用户身份]

登陆系统（普通用户）：sqlplus 用户/密码 

登陆系统（管理员）**必须指定身份（as sysdba或者as sysoper）**：

    sqlplus sys/密码 as sysdba  或者 sqlplus sys/密码 as sysoper

    如果当前操作系统用户是操作系统管理员可以免密码：sqlplus sys/ as sysdba  （跳出输入密码不用管，直接回车）

用户相关

（备注：oracle在用户下创建表；创建用户操作必须是管理员或者有创建用户权限的用户；注意分号结尾）

查看所有用户：select * from all_users;

查看用户权限：select * from user_sys_privs;

创建用户：create user 用户名 identified by 密码;（新建用户不具备任何权限）

给用户添加权限：grant connect,resource to 用户名;（connect：连接到数据库权限，resource：能使用数据库资源权限）

切换用户：connect 用户名/密码;（切换到的用户 需要connect权限）

查看所有用户：select * from all_users;

查询语句相关

（备注：最新的查询语句会缓存在系统）

查看表结构：desc 表名

查看缓存内查询语句：list

执行缓存内查询语句：r  或者  / （前者有显示sql查询语句，后者没有）

删除缓存中几行的查询语句中第n行：del n

定位到查询语句第n行：n

先定位到第n行，然后语句第几行添加条件语句：a  where 条件 （a后面两个空格）

修改缓存内查询语句：c /被改部分/改成部分

表相关

（备注：primary key主键约束，对数据改变需要commit提交，不然可以rollback回滚）

创建表：create table [用户名.]表名(列名 数据类型(长度)[default 默认值][,……]);

利用子查询创建表：create table 表名[(列名),……] as select * from 其他表名;

表新增列：alter table 表名 add（列名 数据类型（长度）[default 默认值][,列名……]）;

修改表列类型：alter table 表名 modify（列名 数据类型（长度）[default 默认值][,列名……]）;

删除列：alter table 表名 drop column 列名;

删除表1：drop table 表名;（放入回收站）

删除表2：drop table 表名 [cascade constraints] [purge];（cascade级联，constraints限制，purge不放入回收站）

删除所有表数据，保留表结构：truncate table 表名;（无法恢复数据）

查看回收站表：show recyclebin;

从回收站恢复表：flashbace table 表名 to before drop;

清空回收站：purge recyclebin;

插入数据：insert into 表名 （列名[,列名]） values （value[,value]）;

更新数据：update 表名 set 列名=value[，列名=value] [where 条件];

删除数据：delete from 表名 [where 条件];

Oracle约束（constraint）

1、非空（NOT NULL）：所定义的列不能为空

2、唯一（UNIQUE）：列值不能重复

3.主键（PRIMARY KEY，完整性约束）：唯一标识表中的一行

4、外键（FOREIGN KEY）：用来维护子表和浮标父表之间引用的完整性

5、条件（CHECK）：表中每行都要满足该约束条件

给已创建好的表添加约束：alter table 表名 add constraint 约束名 primary key（列名）; 

表1（子表）中列1为外键：create table 表1 （列1，列2，constraint 约束名 foreign key（列1） references 表2（列3））; 

删除约束：alter table 表名 drop constraint 约束名;

查看约束：select owner,constraint_name,constraint_type,table_name from user_constraints;

查看约束：select owner,constraint_name table_name,column_name from user_cons_columns;

给select出来的字段定长（这里定10位）：col 字段名 for a10;

select语句语法

语法：select [distinct 去重]列名，…… from 表名 [where 语句] [group by分组列名] [having函数] [order by排序];

合并AB两列取别名C（||还可以合并普通字符串）：select A || B C from 表名;

select的like通配符：“%”->0或多个字符，“_”->一个且只是一个字符，“\”可以转义“_”和“%”；（例：select * from table1 where name like '%\%%' escape '\'；（能匹配“name=我%你”记录）其中\可以用$等字符代替）

升序（默认）：order by 列名（或者列号） asc；  降序：order by 列名 desc；

别名：select 语句中 列名或表名 +“as 别名”，as 可省略；

函数

必须要有select才能计算结果

concat（A，B）：可以连接AB两列字符串，与 || 类似；

initcap（A）：把A列中第一个字符转换成大写；

instr（x，y，m，n）：y在x列的值中从第m个字符串开始第n次出现的位置；

length（n）：求出字符串n的长度；

lower（n）：把字符串n的各个字符装换成小写；upper相反；

lpad（x，n，y）：把x列中，把长度不足n的值用y字符在坐标补全到n位；rpad是右边补全；

trim（[leading | trailing | both [ z ]] from n）：去除n字符串中前/后/前后 存在的z字符；

ltrim（x，y）：去除x左边存在的y字符，没填y默认空格；rtrim为右边；

nvl（x，y）：如果x为null，返回y，否则返回x；

nvl2（x，y，z）：如果x不为null，返回y，为空返回z；

nanvl（x，y）：如果x值为NAN（非数字），返回y，否则返回x；

replace（x，y，z）：把x值中的y字符替换成z；

substr（x，m，n）：从字符串x中第m个位置起去长度为n的字符串；不写n截取到字符串尾部；

abs（x）：取数字绝对值；  ceil（x）：向上取整；  floor（x）：向下取整；

round（x，y）：取数值x四舍五入保留y位小数；（没写y默认为y=0，round（5.88，-1）=10）

trunc（x，y）：截断数值x保留y位小数；（trunc（5.88，-1）=0）

add_months（sysdate，1）：给当前月份添加一个月的日期；

last_day（sysdate）：当前月最后一天的日期； 

next_day（sysdate，5）：当天所在周的第5天所在的日期；（周日为第一天）

months_between（sysdate+60，sysdate）：前者-后者 相差多少个月；（60单位为天）

round（sysdate，‘yyyy’）：以年为标准，对月进行四舍五入（月份大于6，年份加一）

to_char（sysdate，‘yyyy-MM-dd HH24:mi:ss’）：当前时间格式化；

count（A）：A列非空记录数；  max（A）：A列中数值最大的数；min最小；

sum（A）：A列数值总和；  avg（A）：A列数值的平均值；

group by A：把A列中相同值的分到一组；（常与前几个函数连用）

having 条件：对聚合函数进行约制；

表连接

join、inner join：内连接（两个表都有的）

natural join、using（列名）：相同列名连接（两个表都有的）

left outer join：左外连接（两个表都有的在前，左边的表有的在后）；右外连接和全连接类似

集合操作

union：两个select语句结果拼接成新表（列数一样的select结果  加记录数拼接，不是加字段拼接）

intersect：取两个select语句结果的交集；（整条记录结果都一样）minus取不相同的集

子查询

后面可以加子查询的关键字：where、having、from（把子查询结果当做表）

select 列名 from 表名 where 列名 < （select 单返回值查询）

select A，B，C from table1 where （A，B） in或exists （select子查询）

大于all：大于集合中最大值，小于all：小于集合最小值；

大于any：大于集合最小值即可，小于any：小于集合最大值即可；

视图

create [or replace] [force | noforce] view 视图名（列名……） as select语句 [with check option]；（or replace：如果所创建的视图已存在，会重建该视图；force：不管表存不存在都创建视图；视图列名和select列名要一致；with check option：给视图做DML语句操作时，要符合select里的条件；with readonly和select多表不能做DML操作）

 

复杂查询语句

Merge into ：（有则更新，没有则插入）

    MERGE INTO 要操作的表名 T1
        USING (SELECT 语句) T2
        ON ( 判断要更新还是插入的条件 )
    WHEN MATCHED THEN
         UPDATE SET 列名 = 新值 
     WHEN NOT MATCHED THEN 
         INSERT (列名) VALUES(新值)

WM_CONCAT：（连接同一列n行数据，逗号分隔）

    SELECT WM_CONCAT(列1)，列2 FROM 表名 GROUP BY 列2

锁表问题

--查出所有被锁住的表
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY,c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL

from v$locked_object a,dba_objects b, v$session c 
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;
--通过SID, SERIAL解锁 
--alter system kill session 'SID, SERIAL';