001-基础

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 5.9k | 阅读时长 ≈ 5 分钟

创建表空间

创建临时表空间(TIPS：如果建立的是插接式数据库，表空间名、用户名、角色名必须C##打头)

1
2
3
4
5
6
7
CREATE TEMPORARY TABLESPACE C##APPSTABLESPACE_TEMP
TEMPFILE 'D:\Sortware\app\oracle\tablespace\apps\c##appstablespace_temp.dbf'
SIZE 32M      --初始大小
AUTOEXTEND ON --自动扩展
NEXT 32M      --每次拓展大小
MAXSIZE 5G    --最大存储值，MAXSIZE UNLIMITED则指不受限制
EXTENT MANAGEMENT LOCAL;  --EXTENT MANAGEMENT LOCAL(本地管理);EXTENT MANAGEMENT DICTIONARY(数据字典管理)

创建用户表空间

1
2
3
4
5
6
7
CREATE TABLESPACE C##APPSTABLESPACE
LOGGING 
DATAFILE 'D:\Sortware\app\oracle\tablespace\apps\c##appstablespace.dbf'
SIZE 32M  
AUTOEXTEND ON  
NEXT 32M MAXSIZE 10G 
EXTENT MANAGEMENT LOCAL;

删除表空间（包括物理数据文件）

1
DROP TABLESPACE C##APPSTABLESPACE INCLUDING CONTENTS AND DATAFILES;

创建用户

创建用户并指定表空间

1
2
3
CREATE USER C##APPS IDENTIFIED BY "apps"
DEFAULT TABLESPACE C##APPSTABLESPACE
TEMPORARY TABLESPACE C##APPSTABLESPACE_TEMP;

修改用户密码

1
ALTER USER C##APPS IDENTIFIED BY apps;

用户授权(初始创建用户未授权会导致无法登陆)

1
2
3
4
5
6
7
GRANT CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW, CREATE ANY INDEX, CREATE ANY SEQUENCE, CREATE ANY PROCEDURE,
　　  ALTER ANY TABLE, ALTER ANY INDEX, ALTER ANY SEQUENCE, ALTER ANY PROCEDURE,
　　  DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY SEQUENCE, DROP ANY PROCEDURE,
　　  SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE,
     exp_full_database,  --导出数据库的权限
     imp_full_database   --导入数据库的权限
　TO C##APPS;

取消用户授权

1
REVOKE CREATE ANY TABLE FROM C##APPS;

查看用户权限

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.Grantee
      ,a.Granted_Role
      ,a.Admin_Option
      ,a.Common
  FROM Dba_Role_Privs a
 WHERE a.Grantee = 'C##APPS'
UNION ALL
SELECT a.Grantee
      ,a.Privilege
      ,a.Admin_Option
      ,a.Common
  FROM Dba_Sys_Privs a
 WHERE a.Grantee = 'C##APPS';

删除用户

1
DROP USER C##APPS CASCADE;  --cascade参数是级联删除该用户所有对象

创建角色

三种标准的角色：CONNECT、RESOURCE、DBA
查看角色权限

1
SELECT * FROM Role_Sys_Privs WHERE Role = 'RESOURCE';

创建角色

1
CREATE ROLE C##APPS_ROLE;

授权角色

1
GRANT CREATE ANY TABLE C##APPS_ROLE;

角色授予用户

1
GRANT C##APPS_ROLE TO C##APPS;

取消角色授予用户

1
REVOKE C##APPS_ROLE FROM C##APPS;

删除角色

1
DROP ROLE C##APPS_ROLE;

DDL

TABLE

创建表

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Tablename(User_Id          NUMBER NOT NULL
                     ,User_Name         VARCHAR2(10) DEFAULT 'Zts'
                     ,Creation_Date     DATE DEFAULT SYSDATE NOT NULL
                     ,Created_By        NUMBER DEFAULT -1 NOT NULL
                     ,Last_Updated_By   NUMBER DEFAULT -1 NOT NULL
                     ,Last_Update_Date  DATE DEFAULT SYSDATE NOT NULL
                     ,Last_Update_Login NUMBER
                     ,Fictitious AS(User_Id * 10) Virtual --虚拟列
                      --虚拟列可以用在select，update，delete语句的where条件中，但是不能用于DML语句
                      --可以基于虚拟列来做分区、建索引、建约束
                       );

表/列说明

1
2
COMMENT ON TABLE tablename IS '表名';
COMMENT ON COLUMN tablename.user_id IS '列说明';

复制表(只复制表结构，不复制表数据)

1
2
CREATE TABLE new_tablename AS
  SELECT * FROM tablename WHERE 1 = 2;

添加、更改、删除列，修改列名，添加虚拟列

1
2
3
4
5
ALTER TABLE tablename ADD remarks NUMBER;
ALTER TABLE tablename MODIFY remarks Varchar2(4000);
ALTER TABLE tablename DROP COLUMN remarks;
ALTER TABLE tablename RENAME COLUMN remarks TO remark;
ALTER TABLE tablename ADD (fictitious AS (user_id * 10)); --虚拟列

删除表

1
DROP TABLE tablename;

更改表名

1
RENAME tablename TO new_tablename;

临时表

PRESERVE：会话断开后数据会自动删除
DELETE：COMMIT后会自动删除

1
CREATE Global Temporary TABLE Temptable(Id NUMBER) ON COMMIT PRESERVE/DELETE ROWS;

VIEW

创建视图

1
2
CREATE OR REPLACE VIEW tablename_v AS
   SELECT * FROM tablename;

删除视图

1
DROP VIEW tablename_v;

SEQUENCE

创建序列

1
2
3
4
5
6
7
CREATE SEQUENCE tablename_s
NOMINVALUE           --无最小值
NOMAXVALUE           --无最大值
START WITH 1         --从1开始
INCREMENT BY 1       --增量1
NOCYCLE              --序列不循环 
CACHE 20;            --指定Cache值

修改序列

1
2
3
4
5
6
ALTER SEQUENCE tablename_s
MINVALUE   0          --最小值
MAXVALUE   40         --最大值 
INCREMENT BY 2        --增量N
CYCLE                 --序列循环(设置为循环时必须有最大最小值)
NOCACHE;              --无Cache值

删除序列

1
DROP SEQUENCE tablename_s;

INDEX

创建索引

1
2
3
4
5
6
--唯一性索引
CREATE UNIQUE INDEX tablename_u1 ON tablename(user_id);
--普通索引
CREATE INDEX tablename_n1 ON tablename(user_name);
--创建函数索引
CREATE INDEX tablename_upper_n1 ON tablename(UPPER(user_name));

TIPS：什么样的情况下不适合创建索引

  1. 被查询的表本身就很小，即是是全表扫描也非常快
  2. 基于这张表的查询，大多数情况下需要获取的数据量都超过了总量的4%
  3. 这张表需要频繁的被更新，建立索引的话会引起索引的频繁更新，从而反而降低数据库的整体效率

SYNONYM

创建同义词

1
CREATE SYNONYM new_tablename FOR tablename;

删除同义词

1
DROP SYNONYM new_tablename;

DML

SELECT

查询

1
2
3
4
5
6
SELECT e.Empno 工号
      ,e.Ename "姓名"
      ,e.Job AS "职位"
      ,Nvl(e.Deptno, 0) 部门编号
      ,(SELECT d.Dname FROM Dept d WHERE d.Deptno = e.Deptno) 部门名称
  FROM Emp e;

INSERT

插入

1
INSERT INTO Tablename (User_Id, User_Name) VALUES (Tablename_s.Nextval, 'Zts');

复制另一个表数据

1
INSERT INTO New_Tablename SELECT * FROM Tablename;

UPDATE

更新

1
UPDATE Tablename SET User_Name = 'Zts' WHERE User_Id = 1;

DELETE/TRUNCATE

删除

1
2
DELETE Tablename WHERE User_Id = 1;
TRUNCATE TABLE Tablename; --Truncate只能删除一整个表的数据且无法回滚（谨慎使用）

DQL

DECODE

条件判断

1
SELECT Decode(e.Deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 'SALES') FROM Emp e;

CASE

条件判断

1
2
3
4
5
6
7
8
9
SELECT (CASE e.Deptno
        WHEN 10 THEN
          'ACCOUNTING'
        WHEN 20 THEN
          'RESEARCH'
        ELSE
          'SALES'
        END) Dname
  FROM Emp e;

WITH

WITH相当于把查询出来的数据存到临时表，后续查询直接从临时表里查询，能适当提高查询效率

1
2
3
4
5
6
7
8
WITH Tb1 AS
 (SELECT e.Empno FROM Emp e WHERE e.Sal > 1500),
     Tb2 AS
 (SELECT e.Empno FROM Emp e WHERE e.Deptno <> 10)
SELECT *
  FROM Tb1
      ,Tb2
 WHERE Tb1.Empno = Tb2.Empno;

LIKE

模糊匹配
‘ _ ‘ 代表单个字符
‘ % ‘ 代表0个或多个字符

1
SELECT e.* FROM Emp e WHERE e.Ename LIKE '_M%';

ESCAPE
使’ % ‘ 和 ‘ _ ‘不作为通配符使用

1
SELECT * FROM Emp e WHERE e.Ename LIKE '%\%%' ESCAPE '\'; --使\后面的%不作为通配符

BETWEEN

区间条件查询

1
SELECT * FROM Emp e WHERE e.Sal BETWEEN 1500 AND 3000;

EXISTS

判断子查询是否存在，查询性能比IN高

1
2
3
4
5
SELECT *
  FROM Emp e
 WHERE EXISTS (SELECT 1
                 FROM Dept d
                WHERE d.Deptno = e.Deptno);

GROUP BY

分组查询

1
2
3
4
5
SELECT中所有独立字段必须出现在Group By子句中，否则会报错
SELECT e.Deptno
      ,SUM(e.Sal) 
  FROM Emp e
 GROUP BY e.Deptno;

HAVING

条件判断
WHERE中不能使用分组计算函数，但HAVING可以，所以常跟GROUP BY连用

1
2
3
4
5
SELECT e.Deptno
      ,SUM(e.Sal)
  FROM Emp e
HAVING SUM(e.Sal) > 10000
 GROUP BY e.Deptno;