Oracle数据库

2017/04/11 Database

用户

默认用户

用户名 密码 备注
sys 安装时设置 as sysdba
system manager  
scott tigger  

创建用户

sqlplus / as sysdba
set linesize 300

select * from dba_users;
select * from all_users;
select * from user_users;

create user username identified by password default tablespace users;
drop user username cascade;
-- 解锁用户
alter user scott account unlock;

表空间

select username,default_tablespace from   dba_users;
select table_name from user_tables;

sqlplus sys/sys as sysdba
--创建默认表空间
create tablespace mytbs datafile '/home/oracle/oradata/data.dbf' size 300M;
--创建用户
create user mytbs identified by mytbs default tablespace mytbs;
--赋权 需访问到dba_directories
grant dba to mytbs; 


desc user_tables;
SELECT * FROM DICTIONARY;-- 数据字典表
SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES;
SELECT VIEW_NAME FROM USER_VIEWS;
SELECT TABLE_NAME,CONSTRAINT_NAME FROM USER_CONSTRAINTS;
SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES;
SELECT SEQUENCE_NAME FROM USER_SEQUENCES;

权限

系统权限

  • 登陆权限
grant create session to username;
  • 创建表
grant create table to username;
  • 表空间
grant unlimited tablespace to username;
  • 撤销权限
revoke create table from username;
  • 赋予所有用户权限
grant create any table to public;
  • 查询当前用户系统权限
select * from user_sys_privs;

对象权限

  • 赋予用户对象权限
grant select on tablename to username;
grant insert on tablename to username;
grant all on tablename to username;
revoke all on tablename to username;
  • 列权限

查询和删除不能控制到列

grant insert(columnname) on tablename to username;
grant update(columnname) on tablename to username;
  • 查询当前用户对象权限
select * from user_tab_privs;-- 表
select * from user_clo_privs;-- 列

权限传递

  • 赋予用户权限,被赋权限用户可以继续赋予其他用户
grant select on tablename to username with admin option;
grant alter any table to username with admin option;
grant select on tablename to username with grant option;

角色

create role rolename;
grant create session to rolename;
grant rolename to username;
-- 有些权限无法赋给角色,如unlimited tablespace
drop role rolename;

登陆验证机制

  • 操作系统验证(首先采用)
  • 密码文件验证
  • 数据库验证

  • 启动过程

linux

# new version
lsnrctl start
sqlplus sys/oracle as sysdba
startup
# old version
lsnrctl start
sqlplus /nolog
conn sys/oracle as sysdba
startup

windows

lsnrctl start
oradmin -startup -sid orcl
  • 修改密码
alter user username identified by password;

表操作

CREATE TABLE tablename AS SELECT * FROM tablename1; -- 创建表并复制数据
INSERT INTO tablename (SELECT * FROM tablename1);
DROP TABLE tablename

ALTER TABLE tablename ADD (id NUMBER);
ALTER TABLE tablename MODIFY (id VARCHAR(10));
ALTER TABLE tablename DROP COLUMN ID;
ALTER TABLE tablename ADD CONSTRAINT pk_tablename PRIMARY KEY (id);
ALTER TABLE TABLENAME ADD CONSTRAINT fk_tablename FOREIGN KEY (EMPNO) REFERENCES EMP(EMPNO);
ALTER TABLE tablename DROP CONSTRAINT pk_tablename;

SELECT * FROM tablename;
DELETE FROM tablename;

索引

增加了查询效率,降低了插入效率

CREATE INDEX idx_tablename_columnname ON TABLENAME (columnname);
show index from tablename;
show keys from tablename;

序列

CREATE SEQUENCE SEQUENCE_NAME;

SELECT SEQUENCE_NAME.nextval FROM dual;
SELECT SEQUENCE_NAME.currval FROM dual;

SELECT SEQUENCE_NAME FROM USER_SEQUENCES;

字符串

select to_char(sal,'999,999,999.99') FROM EMP;-- 2,975.00
select to_char(sal,'L999,999,999.99') FROM EMP;-- ¥2,975.00
select to_char(sal,'000000.00') FROM EMP;-- 002975.00
select to_number('002975.00','000000.00') FROM dual;-- 2975
select col1 || col2 from tablename;
-- 两个引号转义成一个引号
select 'a' || 'b''c' from dual;-- abc
-- 指定转义字符
SELECT * FROM BONUS WHERE BONUS.ENAME LIKE '%$%%' ESCAPE '$';
-- 使用'转义'
SELECT * FROM BONUS WHERE BONUS.ENAME LIKE '%''%';

组函数

SELECT max(SAL) FROM EMP;
SELECT min(SAL) FROM EMP;
SELECT avg(SAL) FROM EMP;
SELECT round(avg(SAL),2) FROM EMP;-- 四舍五入两位
SELECT sum(SAL) FROM EMP;
SELECT count(*) FROM EMP;-- 非空字段
SELECT count(DISTINCT JOB) FROM EMP;

-- 不使用组函数获取最大值
SELECT
  SAL
FROM (
  SELECT
    ROWNUM ,
    EMP.*
  FROM EMP ORDER BY SAL DESC
) t WHERE ROWNUM <2

分组

SELECT JOB,count(*) FROM EMP GROUP BY JOB;
SELECT JOB,DEPTNO,count(*) FROM EMP GROUP BY JOB,DEPTNO;
SELECT ENAME FROM EMP WHERE COMM = (SELECT max(COMM) FROM EMP);

SELECT DEPTNO,avg(COMM) AVG_COMM FROM EMP 
WHERE JOB!='KING' GROUP BY DEPTNO 
HAVING avg(COMM)>350 ORDER BY AVG_COMM DESC

子查询

-- 查询每个部分薪水最高的员工和薪水
SELECT
  EMP.ENAME,
  SAL,
  t.DEPTNO
FROM EMP, (SELECT
             DEPTNO,
             MAX(SAL) MAXSAL
           FROM EMP
           GROUP BY DEPTNO) t
WHERE EMP.DEPTNO = t.DEPTNO AND EMP.SAL = t.MAXSAL

-- 查询部门平均薪资等级
SELECT
  SALGRADE.GRADE,
  t.DEPTNO,
  t.AVGSAL
FROM SALGRADE
  JOIN
  (SELECT
     DEPTNO,
     avg(SAL) AVGSAL
   FROM EMP
   GROUP BY DEPTNO) t ON
                        t.AVGSAL > SALGRADE.LOSAL AND t.AVGSAL < SALGRADE.HISAL;

-- 查询领导姓名
SELECT
  t1.EMPNO,
  t1.ENAME,
  t2.ENAME MGRNAME
FROM EMP t1, EMP t2
WHERE t1.MGR = t2.EMPNO;

-- 部门员工薪水等级的平均值
SELECT
  avg(GRADE),
  DEPTNO
FROM (
       SELECT
         DEPTNO,
         SALGRADE.GRADE
       FROM EMP
         LEFT JOIN SALGRADE ON EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL) t
GROUP BY DEPTNO

-  不使用组函数获取最大值
SELECT SAL
FROM EMP
WHERE EMP.SAL NOT IN (
  SELECT DISTINCT e1.SAL
  FROM EMP e1, EMP e2
  WHERE e1.SAL < e2.SAL)

-- 平均薪水最高的部门
SELECT
  DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING avg(SAL) = (
  SELECT max(AVGSAL)
  FROM (
    SELECT
      DEPTNO,
      avg(SAL) AVGSAL
    FROM EMP
    GROUP BY DEPTNO
  )
)

SELECT DEPTNO
FROM (SELECT
        DEPTNO,
        avg(SAL) AVGSAL
      FROM EMP
      GROUP BY DEPTNO)
WHERE AVGSAL = (
  SELECT max(AVGSAL)
  FROM (
    SELECT
      DEPTNO,
      avg(SAL) AVGSAL
    FROM EMP
    GROUP BY DEPTNO
  )
)

空值

SELECT nvl(COMM, 0) FROM EMP;

时间

select sysdate from dual
SELECT to_date('1992-2-2','yyyy-MM-dd') FROM dual;
SELECT to_char(sysdate,'yyyy-MM-dd') FROM dual;

分页

-- 第一层:获取数据物理地址
-- 第二层:取得最大页数
-- 第三层:取得最小页数
-- 第四层:因为取得的页数都是物理地址,再根据物理地址,插叙出具体数据

--rowid分页,第一步
SELECT
  rowid rid
FROM emp
ORDER BY sal DESC;
--rowid分页,第二步
SELECT
  rownum rn,
  rid
FROM (SELECT
        rowid rid
      FROM emp
      ORDER BY sal DESC)
WHERE rownum < 10;
--rowid分页,第三步
SELECT rid
FROM (SELECT
        rownum rn,
        rid
      FROM (SELECT
              rowid rid
            FROM emp
            ORDER BY sal DESC)
      WHERE rownum < 10)
WHERE rn > 5;
--rowid分页,第四步
SELECT *
FROM emp
WHERE rowid IN (SELECT rid
                FROM (SELECT
                        rownum rn,
                        rid
                      FROM (SELECT
                              rowid rid
                            FROM emp
                            ORDER BY sal DESC)
                      WHERE rownum < 10)
                WHERE rn > 5);

修改编码

shutdown immediate;
startup mount;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
alter database open;
alter database character set INTERNAL_USE UTF8;
shutdown immediate;
startup;
exit;

备份还原

export ORACLE_SID=orcl
export ORACLE_HOME=/oracle/product/11gR1/db
export PATH=$PATH:.:/oracle/product/11gR1/db/bin

# 整个库备份还原
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;

ORA错误

ORA-00845

mount -o size=2G -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm

Search

    Post Directory