Oracle SQL 学习笔记

一.DDL

1. CREATE

1.1 创建表

最原始的创建表的方式

CREATE TABLE TABLE_NAME (
    COLUMN_NAME1 VARCHAR(2) DEFAULT 'A',
    COLUMN_NAME2 NUMBER DEFAULT 0,
    COLUMN_NAME2 DATE DEFAULT SYSDATE
);

复制其他表的数据和结构来创建表，但不复制约束和索引

CREATE TABLE TABLE_NAME2 AS SELECT * FROM TABLE_NAME2

1.2 创建视图

视图可以理解为是一张虚拟表

CREATE VIEW VIEW_NAME
AS 
    [SELECT ...]
    
[WITH READ ONLY]
​
//删除视图
DROP VIEW VIEW_NAME

1.3 创建索引

单一索引

索引列的值的唯一性

CREATE [UNIQUE] INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME1)

复合索引

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME1，COLUMN_NAME2)

聚簇索引

必须是唯一的，是按照数据物理存储进行划分的，迅速减少范围

聚簇索引是建立在聚簇列上的

CREATE CLUSTER INDEX_NAME (COLUMN DATATYPE [, COLUMN DATATYPE]...)[OTHER OPTIONS]

第一：建立一个聚簇

CREATE CLUSTER BOOKandAUTHOR( Col1 VARCHAR2(100)); SIZE 1024

第二：建立一个表 然后见表的时候把某个列放进那个聚簇

CREATE TABLE BOOKSHELF
(
    TITILE VARCHAR2(100) PRIMARY KEY,
    PUBLISHER VARCHAR2(20),
    CATEGORYNAME VARCHAR2(20),
    RATING VARCHAR2(2)
)
CLUSTER BOOKandAUTHOR(Title)

第三：在向BOOKSHELF表中插入数据行之前，必须建立一个聚簇索引

CREATE INDEX BOOKandAUTHORINDEX ON CLUSTER BOOKandAUTHOR

索引失效

0. 没有WHERE - 添加索引的字段必须要在WHERE 条件后适当使用才生效
1. 索引列上使用函数

SELECT COL FROM TABLE WHERE SUBSTE(COL,1,3) = 'ABC'

3. 索引列上进行计算

SELECT COL FROM TABLE WHERE COL/100 > 0.2

4. 索引列上使用 NOT , <> , !=

SELECT COL FROM TABLE WHERE COL != 10 

5. 索引列 Not NULL， NULL

SELECT COL FROM TABLE WHERE COL IS NOT NULL

其他索引失效的情况

0. % 开头的LIKE 语句，模糊匹配
1. OR 语句前后没有同时使用索引
2. 数据类型出现隐式转化
3. 对于多列索引，必须满足最左匹配原则

查看索引

SELECT * FRO ALL_INDEXED WHERE TABLE_NAME = '';
SELECT * FROM USER_IND_COULUMNS WHERE TABLE_NAME = '';

索引的优点：

0. 大大加快数据的检索速度
1. 加速表和表之间的连接
2. 在使用分组和排序子句进行数据检索时，同时可以显著减少查询中分组和排序的时间

索引的缺点

0. 时间：创建索引和维护索引要耗费时间，当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
1. 空间方面：索引需要占据物理空间

适合创建索引的字段

0. 经常作查询选择的字段

1. 经常用作表连接的字段

2. 经常出现在order by，group by， distinct后面的字段

1.4 创建分区

范围分区

CREATE TABLE TABLE_NAME(
    GRADE INT,
    NAME VARCHAR(2)
)PARTITION BY RANGE(GRADE)
(
    PARTITION FAIL VALUES LESS THAN (60) [TABLESPACE T1],
    PARTITION PASS VALUES LESS THAN (80) [TABLESPACE T2],
    PARTITION GREAT VALUES LESS THAN (MAXVALUE)) [TABLESPACE T3]    
)

SELECT * FROM TABLE_NAME PARTITION(PASS)

列表分区

根据某个字段的某个具体值进行分区 - 仅支持单列

CREATE TABLE TABLE_NAME(
    GRADE INT,
    NAME VARCHAR(2),
    COURSE VARCHAR(2)
)PARTITION BY LIST(COURSE)
(
    PARTITION P1 VALUES('CHINESE'),
    PARTITION P2 VALUES('MATH'),
    PARTITION P3 VALUES('ENGLISH')
)

SELECT * FROM TABLE_NAME PARTITION(CHINESE)

哈希分区

根据某个字段的HASH 值进行均匀分布，尽可能实现所散列的数据相等

具体分区由Oracle 决定，下一次搜索可能不就不是这些数据了

CREATE TABLE TABLE_NAME(
    GRADE INT,
    NAME VARCHAR(2),
    SNO VARCHAR2(10)
)PARTITION BY HASH(SNO)
(
    PARTITION P1,
    PARTITION P2,
    PARTITION P3    
)

SELECT * FROM TABLE_NAME PARTITION(P1)

组合分区

CREATE TABLE TABLE_NAME(
    GRADE INT,
    NAME VARCHAR(2),
    SNO VARCHAR2(10),
    COURSE VARCHAR(2)
) PARTITION BY RANGE(GRADE)
SUBPARTITION BY HASH(SNO)
(
    PARTITION P1 VALUES LESS THAN (60)
        (
            SUBPARTITION SP1),
    PARTITION P2 VALUES LESS THAN (80)
        (
            SUBPARTITION SP2),
    PARTITION P3 VALUES LESS THAN (MAXVALUE)
        (
            SUBPARTITION SP3),      
)

以GRADE 为划分范围，以SNO 划分散列分区

查看表分区情况

SELECT * FROM ALL_PART_TABLES WHERE TABLE_NAME =''; -查询用户所有分区表的信息
SELECT * FROM USER_TABLES A WHERE A.PARTITIONED = 'YES'; -所有的分区表

2. ALTER | RENAME

添加 | 更新

ALTER TABLE TABLE_NAME ADD COLUMN_NAME VARCHAR(20) [DEFAULT 1];
ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME_OLD TO COLUMN_NAME_NEW;

删除

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME

修改

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NUMBER;

3. DROP

删除某些数据用DELETE

DELETE FROM TABLE_NAME WHERE CONDITIONS;

删除整张表用DROP

DROP TABLE TABLE_NAME;

删除索引

DROP INDEX INDEX_NAME;