Oracle 知识点速查

序列的语法是

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

例如

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

length( string1 );

---

SELECT length('hello world') FROM dual;

这将返回 11，因为参数由 11 个字符组成，包括空格

SELECT lengthb('hello world') FROM dual;
SELECT lengthc('hello world') FROM dual;
SELECT length2('hello world') FROM dual;
SELECT length4('hello world') FROM dual;

这些也返回 11，因为调用的函数是等价的

replace(string1, string_to_replace, [ replacement_string ] );

replace('i am here','am','am not');

这返回 i am not here

SELECT substr( 'oracle pl/sql cheatsheet', 8, 6) FROM dual;

返回 pl/sql，因为 pl/sql 中的 p 在字符串中的第 8 个位置（从 oracle 中的 o 处的 1 开始计算）

SELECT substr( 'oracle pl/sql cheatsheet', 15) FROM dual;

返回 cheatsheet，因为 c 在字符串中的第 15 个位置，t是字符串中的最后一个字符。

SELECT substr('oracle pl/sql cheatsheet', -10, 5) FROM dual;

返回 cheat，因为 c 是字符串中的第 10 个字符，从字符串末尾以 t 作为位置 1 开始计算。

这些函数可用于从字符串中过滤不需要的字符。默认情况下，它们会删除空格，但也可以指定要删除的字符集

trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed );
trim ('   删除两侧的空格     ');

这将返回“删除两侧的空格”

ltrim ( string-to-be-trimmed [, trimming-char-set ] );
ltrim ('   删除左侧的空格     ');

这将返回“删除左侧的空格      ”

rtrim ( string-to-be-trimmed [, trimming-char-set ] );
rtrim ('   删除右侧的空格     ');

这将返回“      删除右侧的空格”

创建表的语法

CREATE TABLE [table name]
      ( [column name] [datatype], ... );

示例：

 CREATE TABLE employee
       (id int, name varchar(20));

添加列的语法

ALTER TABLE [table name]
    ADD ( [column name] [datatype], ... );

示例：

ALTER TABLE employee
    ADD (id int)

修改列的语法

ALTER TABLE [table name]
    MODIFY ( [column name] [new datatype]);

ALTER 表语法和示例：

ALTER TABLE employee
    MODIFY( sickHours s float );

删除列的语法

ALTER TABLE [table name]
    DROP COLUMN [column name];

示例：

ALTER TABLE employee
    DROP COLUMN vacationPay;

类型代码	类型描述	作用于级别
C	检查表	Column
O	在视图上只读	Object
P	首要的关键	Object
R	参考 AKA 外键	Column
U	唯一键	Column
V	检查视图上的选项	Object

以下语句显示了系统中的所有约束：

SELECT
    table_name,
    constraint_name,
    constraint_type
FROM user_constraints;

以下语句显示了源和目标表/列对的所有引用约束（外键）：

SELECT
    c_list.CONSTRAINT_NAME as NAME,
    c_src.TABLE_NAME as SRC_TABLE,
    c_src.COLUMN_NAME as SRC_COLUMN,
    c_dest.TABLE_NAME as DEST_TABLE,
    c_dest.COLUMN_NAME as DEST_COLUMN
FROM ALL_CONSTRAINTS c_list,
    ALL_CONS_COLUMNS c_src,
    ALL_CONS_COLUMNS c_dest
WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
    AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
    AND c_list.CONSTRAINT_TYPE = 'R'

使用 CREATE TABLE 语句创建检查约束的语法是：

CREATE TABLE table_name
(
    column1 datatype null/not null,
    column2 datatype null/not null,
    ...
    CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

例如：

CREATE TABLE suppliers
(
    supplier_id  numeric(4),
    supplier_name  varchar2(50),
    CONSTRAINT check_supplier_id
    CHECK (supplier_id BETWEEN 100 and 9999)
);

使用 CREATE TABLE 语句创建唯一约束的语法是：

CREATE TABLE table_name
(
    column1 datatype null/not null,
    column2 datatype null/not null,
    ...
    CONSTRAINT constraint_name UNIQUE (column1, column2, column_n)
);

例如：

CREATE TABLE customer
(
    id   integer not null,
    name varchar2(20),
    CONSTRAINT customer_id_constraint UNIQUE (id)
);

唯一约束的语法是：

ALTER TABLE [table name]
    ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX [index name];

例如：

ALTER TABLE employee
    ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX ourcompanyIndx_tbs;

foregin 约束的语法是：

ALTER TABLE [table name]
    ADD CONSTRAINT [constraint name] FOREIGN KEY (column,...) REFERENCES table [(column,...)] [ON DELETE {CASCADE | SET NULL}]

例如：

ALTER TABLE employee
    ADD CONSTRAINT fk_departament FOREIGN KEY (departmentId) REFERENCES departments(Id);

删除（删除）约束的语法是：

ALTER TABLE [table name]
    DROP CONSTRAINT [constraint name];

例如：

ALTER TABLE employee
    DROP CONSTRAINT uniqueEmployeeId;

创建索引的语法是：

CREATE [UNIQUE] INDEX index_name
    ON table_name (column1, column2, . column_n)
    [ COMPUTE STATISTICS ];

UNIQUE 表示索引列中值的组合必须是唯一的

COMPUTE STATISTICS 告诉 Oracle 在创建索引期间收集统计信息。 然后优化器使用这些统计信息来选择执行语句时的最佳执行计划。例如：

CREATE INDEX customer_idx
    ON customer (customer_name);

在此示例中，已在名为 customer_idx 的客户表上创建了一个索引。它仅包含 customer_name 字段

下面创建一个包含多个字段的索引：

CREATE INDEX customer_idx
    ON supplier (customer_name, country);

以下内容在创建索引时收集统计信息：

CREATE INDEX customer_idx
    ON supplier (customer_name, country)
    COMPUTE STATISTICS;

重命名索引的语法是：

ALTER INDEX index_name
    RENAME TO new_index_name;

例如：

ALTER INDEX customer_id
    RENAME TO new_customer_id;

在此示例中，customer_id 重命名为 new_customer_id

如果您需要在索引首次创建后收集统计信息或者您想要更新统计信息，您总是可以使用 ALTER INDEX 命令来收集统计信息。 您收集统计信息以便 oracle 可以有效地使用索引。 这将重新计算表大小、行数、块数、段数并更新字典表，以便 oracle 在选择执行计划时可以有效地使用数据。

收集索引统计信息的语法是：

ALTER INDEX index_name
    REBUILD COMPUTE STATISTICS;

例如：

ALTER INDEX customer_idx
    REBUILD COMPUTE STATISTICS;

在此示例中，为名为 customer_idx 的索引收集统计信息

删除索引的语法是：

DROP INDEX index_name;

例如：

DROP INDEX customer_idx;

在此示例中，删除了 customer_idx

创建用户的语法是：

CREATE USER username IDENTIFIED BY password;

例如：

CREATE USER brian IDENTIFIED BY brianpass;

授予权限的语法是：

GRANT privilege TO user;

例如：

GRANT dba TO brian;

更改用户密码的语法是：

ALTER USER username IDENTIFIED BY password;

例如：

ALTER USER brian IDENTIFIED BY brianpassword;