入门
SELECT 语句
SELECT * FROM beverages WHERE field1 = 'Kona' AND field2 = 'coffee' AND field3 = 122;
SELECT INTO 语句
SELECT name,address,phone_number INTO v_employee_name,v_employee_address,v_employee_phone_number FROM employee WHERE employee_id = 6;
INSERT 语句
使用 VALUES 关键字插入
INSERT INTO table_name VALUES ('Value1', 'Value2', ... );
INSERT INTO table_name(Column1, Column2, ... ) VALUES ( 'Value1', 'Value2', ... );
使用 SELECT 语句插入
INSERT INTO table_name(SELECT Value1, Value2, ... from table_name );
INSERT INTO table_name(Column1, Column2, ... ) ( SELECT Value1, Value2, ... from table_name );
DELETE 语句
DELETE FROM table_name WHERE some_column=some_value
DELETE FROM customer WHERE sold = 0;
UPDATE 语句
-- 更新该表的整个列,设置 `state` 列所有值为 `CA`
UPDATE customer SET state='CA';
-- 更新表的具体记录eg:
UPDATE customer SET name='Joe' WHERE customer_id=10;
-- 当 `paid` 列大于零时,将列 `invoice` 更新为 `paid`
UPDATE movies SET invoice='paid' WHERE paid > 0;
SEQUENCES
CREATE SEQUENCE
序列的语法是
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;
ALTER SEQUENCE
将序列增加一定数量
ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 10;
改变序列的最大值
ALTER SEQUENCE <sequence_name> MAXVALUE <integer>;
ALTER SEQUENCE seq_maxval MAXVALUE 10;
设置序列循环或不循环
ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>;
ALTER SEQUENCE seq_cycle NOCYCLE;
配置序列以缓存值
ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE;
ALTER SEQUENCE seq_cache NOCACHE;
设置是否按顺序返回值
ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>;
ALTER SEQUENCE seq_order NOORDER;
ALTER SEQUENCE seq_order;
从字符串生成查询
有时需要从字符串创建查询
PROCEDURE oracle_runtime_query_pcd IS
TYPE ref_cursor IS REF CURSOR;
l_cursor ref_cursor;
v_query varchar2(5000);
v_name varchar2(64);
BEGIN
v_query := 'SELECT name FROM employee WHERE employee_id=5';
OPEN l_cursor FOR v_query;
LOOP
FETCH l_cursor INTO v_name;
EXIT WHEN l_cursor%NOTFOUND;
END LOOP;
CLOSE l_cursor;
END;
这是一个如何完成动态查询的非常简单的示例
字符串操作
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
,因为调用的函数是等价的
Instr
Instr
(在字符串中)返回一个整数,该整数指定字符串中子字符串的位置。程序员可以指定他们想要检测的字符串的外观以及起始位置。不成功的搜索返回 0
instr( string1, string2, [ start_position ], [ nth_appearance ] )
instr( 'oracle pl/sql cheatsheet', '/');
这将返回 10
,因为第一次出现的 /
是第十个字符
instr( 'oracle pl/sql cheatsheet', 'e', 1, 2);
这将返回 17
,因为第二次出现的 e
是第 17
个字符
instr( 'oracle pl/sql cheatsheet', '/', 12, 1);
这将返回 0
,因为第一次出现的 /
在起点之前,即第 12
个字符
Replace
replace(string1, string_to_replace, [ replacement_string ] );
replace('i am here','am','am not');
这返回 i am not here
Substr
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
这些函数可用于从字符串中过滤不需要的字符。默认情况下,它们会删除空格,但也可以指定要删除的字符集
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 (' 删除右侧的空格 ');
这将返回“ 删除右侧的空格
”
DDL SQL
创建表
创建表的语法
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;
INDEXES
创建索引
创建索引的语法是:
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;
创建基于函数的索引
在 Oracle
中,您不仅限于在列上创建索引。您可以创建基于函数的索引
创建基于函数的索引的语法是:
CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
例如:
CREATE INDEX customer_idx
ON customer (UPPER(customer_name));
-- 已创建基于 customer_name 字段的大写评估的索引
为确保 Oracle
优化器在执行 SQL 语句时使用此索引,请确保 UPPER(customer_name)
的计算结果不为 NULL
值。 为确保这一点,请将 UPPER(customer_name) IS NOT NULL
添加到 WHERE
子句中,如下所示:
SELECT customer_id, customer_name, UPPER(customer_name)
FROM customer
WHERE UPPER(customer_name) IS NOT NULL
ORDER BY UPPER(customer_name);
重命名索引
重命名索引的语法是:
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
DBA 相关
创建用户
创建用户的语法是:
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;
这是一个入门级别的快速参考备忘单,为开发人员分享各种常用工具的快速参考信息。它提供了各个工具的简介和常见用法,帮助开发人员快速查找和使用相关工具。