数据库：一文把握 Oracle 的各种指令（Oracle指令备忘）
入门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_tenINCREMENT BY 10;
改变序列的最大值
ALTER SEQUENCE <sequence_name> MAXVALUE <integer>;
ALTER SEQUENCE seq_maxvalMAXVALUE10;
设置序列循环或不循环
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
( , ... );
示例：
CREATE TABLE employee
(id int, name varchar(20));
添加列
添加列的语法
ALTER TABLE
ADD ( , ... );
示例：
ALTER TABLE employee
ADD (id int)
修改列
修改列的语法
ALTER TABLE
MODIFY ( );
ALTER 表语法和示例：
ALTER TABLE employee
MODIFY( sickHours s float );
删除列
删除列的语法
ALTER TABLE
DROP COLUMN ;
示例：
ALTER TABLE employee
DROP COLUMN vacationPay;
约束类型和代码
类型代码类型形貌作用于级别C查抄表ColumnO在视图上只读ObjectP首要的关键ObjectR参考 AKA 外键ColumnU唯一键ColumnV查抄视图上的选项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)
);
例如：
CREATE TABLE suppliers
(
supplier_idnumeric(4),
supplier_namevarchar2(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
ADD CONSTRAINT
UNIQUE()
USING INDEX ;
例如：
ALTER TABLE employee
ADD CONSTRAINT uniqueEmployeeId
UNIQUE(employeeId)
USING INDEX ourcompanyIndx_tbs;
添加外部约束
foregin 约束的语法是：
ALTER TABLE
ADD CONSTRAINT
FOREIGN KEY (column,...)
REFERENCES table [(column,...)]
;
例如：
ALTER TABLE employee
ADD CONSTRAINT fk_departament
FOREIGN KEY (departmentId)
REFERENCES departments(Id);
删除约束
删除（删除）约束的语法是：
ALTER TABLE
DROP CONSTRAINT ;
例如：
ALTER TABLE employee
DROP CONSTRAINT uniqueEmployeeId;
INDEXES
创建索引
创建索引的语法是：
CREATE 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 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;
检察表空间的名称以及巨细
SELECT t.table_name,
ROUND(SUM(bytes / (1024 * 1024)), 0) AS ts_size
FROM dba_tablespaces t,
dba_data_files d
WHERE t.table_name = d.table_name
GROUP BY t.table_name;
检察还没提交的事件
select * from v$locked_object;
select * from v$transaction;
检察数据库库对象
SELECT owner, object_type, status, COUNT(*) AS count#
FROM all_objects
GROUP BY owner, object_type, status;
检察数据库的版本
SELECT version
FROM Product_component_version
WHERE SUBSTR(PRODUCT, 1, 6) = 'Oracle';
检察数据库的创建日期和归档方式
SELECT created, Log_Mode, Log_Mode
FROM v$Database;
检察控制文件
select name from v$controlfile;
检察日记文件
select member from v$logfile;
检察表空间的使用情況
SELECT SUM(bytes)/(1024*1024) AS free_space,
tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
捕捉运行很久的SOL
COLUMN username FORMAT A12
COLUMN opname FORMAT A16
COLUMN progress FORMAT A8
SELECT username,
sid,
opname,
ROUND(sofar * 100 / totalwork, 0) || '%' AS progress,
time_remaining,
sql_text
FROM v$session_longops, v$sql
WHERE time_remaining <> 0
AND sql_address = address
AND sql_hash_value = hash_value;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]