Oracle常用sql总结

一、常用查询语句
1、oracle查询伪列
查询伪列的目的:
（1）物理文件上区分一条记录的唯一标识，使用ROWID可以快速定位表中的某一行，
（2）想快速为一个表添加几个字段无需手动写sql,前提是使用plsql工具。

select t.*,t.rowid from 表名 t;

2、查询指定表字段名称和字段类型

select t.COLUMN_NAME,t.DATA_TYPE from USER_TAB_COLUMNS t where t.TABLE_NAME = '表名'

注意：此处的表名需要大写！
3、查询所有数据库的表名和表名描述

select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;

4、查询指定表名和字段描述

select t.column_name,t.comments from user_col_comments t where t.table_name = '表名';

5、查询数据库表的字段名称，字段类型，字段描述

select t.column_name,f.DATA_TYPE,t.comments from user_col_comments t
inner join USER_TAB_COLUMNS f on t.table_name = f.TABLE_NAME
where t.table_name = '表名';

二、oracle表空间操作
1、创建表空间：
create tablespace 表空间名 datafile ‘空间物理存储（f:\ts_zzg\zzg_data.dbf后缀随意）’ size 大小（200M）;

create tablespace SERVER datafile 'D:\xxxSoftware\oracle\tablespace\abc.dbf' size 200M;

2、删除表空间：

DROP TABLESPACE  表空间名 INCLUDING CONTENTS AND DATAFILES;

3、给用户分配表空间:

	alter user 用户名 default tablespace 表空间名;
	alter user xiaofeng default tablespace SERVER;

4、查看所有用户所属表空间：

	select username,default_tablespace from dba_users;

5、扩展表空间
当表空间满了报错：表 SERVER.表名 无法通过 1024 (在表空间 SERVER 中) 扩展
alter tablespace 表空间名称 add datafile ‘表空间存放路径’ size 表空间大小

alter tablespace SERVER add datafile 'D:\xxxxSoftware\oracle\tablespace\abc2.dbf' size 10g;

6、查询数据库中所有字段重复的次数

-- 如果你想查询多个列，group by后面必须跟多个列明否则报错
select inpat_dept_cd,inpat_dept_name,count(*) from 表名 
group by inpat_dept_cd,inpat_dept_name having count(*) > 1 

三、触发器
1、当我们执行添加和修改操作时给表中的创建时间和修改时间插入当前系统时间

CREATE OR REPLACE trigger 触发器名称
    before INSERT OR UPDATE ON 表名 FOR EACH ROW
BEGIN
     IF INSERTING THEN
        :NEW.idx_create := SYSDATE;
        :NEW.idx_update := SYSDATE;
    ELSIF UPDATING THEN
        :NEW.idx_update := SYSDATE;
    END IF;    
END;

---例子：
CREATE OR REPLACE trigger mytriger
    before INSERT OR UPDATE ON index_statistics FOR EACH ROW
BEGIN
     IF INSERTING THEN
        :NEW.idx_create := SYSDATE;
        :NEW.idx_update := SYSDATE;
    ELSIF UPDATING THEN
        :NEW.idx_update := SYSDATE;
    END IF;    
END;

四、创建序列

CREATE SEQUENCE gety_index
increment by 1                  -- 每次加几个
start with 1                    -- 从1开始计数
nomaxvalue                      -- 不设置最大值
nocycle                         -- 一直累加，不循环
nocache                         -- 不建缓冲区

五、倒叙取第一行

CREATE TABLE Employee
(
  ID number(10) not null primary key,
  EmpName varchar(20),
  EmpSalary varchar(10),
  EmpDepartment varchar(20)
);
insert into Employee values(1,'张三','5000','开发部');
insert into Employee values(2,'李四','2000','销售部');
insert into Employee values(3,'王麻子','2500','销售部');
insert into Employee values(4,'张三表叔','8000','开发部');
insert into Employee values(5,'李四表叔','5000','开发部');
insert into Employee values(6,'王麻子表叔','5000','销售部');


-- PARTITION BY EmpDepartment 表示分组各组的和，
-- order by EmpDepartment 表示分组和累加 把by后面相同的字段，一个组组累加起来
--over order by partition by 联合使用，就是上面两功能的和，既然分组统计又累积（注意order by 需要放后面）

SELECT EmpSalary,EmpDepartment,SUM(EmpSalary) OVER(PARTITION BY EmpDepartment order by EmpDepartment)  sum_sala FROM Employee

select t.* from (
SELECT EmpSalary,EmpDepartment,row_number() OVER(order by EmpSalary desc)  rn FROM Employee
) t where t.rn = 1