資料函式庫操作中，刪除和更新紀錄以及查詢中繼資料是不可或缺的技能。刪除紀錄時，需根據不同情境選擇合適的 SQL 陳述式，例如使用 DELETE 搭配 WHERE 子句刪除特定紀錄，或使用 MERGE 陳述式進行條件式更新或刪除。中繼資料查詢則能提供資料函式庫結構資訊，例如使用系統表格或檢視查詢表格、欄位、索引和約束等，以便進行資料函式倉管理和效能調校。不同資料函式庫系統（如 DB2、Oracle、PostgreSQL、MySQL 和 SQL Server）的語法略有差異，需根據實際環境選擇正確的查詢方式。掌握這些技術有助於提升資料函式倉管理效率和資料完整性。
資料函式庫紀錄刪除與更新的實務操作
在資料函式倉管理中，刪除和更新紀錄是常見的操作。本文將介紹如何使用 SQL 陳述式來刪除和更新資料函式庫中的紀錄。
使用 MERGE 陳述式更新或插入紀錄
MERGE 陳述式可以用於根據特定條件更新或插入紀錄。以下是一個範例：
MERGE INTO emp_commission ec
USING (SELECT * FROM emp) emp
ON (ec.empno = emp.empno)
WHEN MATCHED THEN
UPDATE SET ec.comm = 1000
DELETE WHERE (sal < 2000)
WHEN NOT MATCHED THEN
INSERT (ec.empno, ec.ename, ec.deptno, ec.comm)
VALUES (emp.empno, emp.ename, emp.deptno, emp.comm);
內容解密：
MERGE INTO指定目標表格emp_commission。USING指定來源表格emp。ON指定匹配條件，即empno相同。WHEN MATCHED THEN更新匹配的紀錄，將comm設為 1000，如果sal小於 2000，則刪除該紀錄。WHEN NOT MATCHED THEN插入不匹配的紀錄。
刪除所有紀錄
要刪除表格中的所有紀錄，可以使用 DELETE 陳述式：
DELETE FROM emp;
內容解密：
DELETE FROM指定要刪除紀錄的表格。- 省略
WHERE子句將刪除所有紀錄。
刪除特定紀錄
要刪除符合特定條件的紀錄，可以在 DELETE 陳述式中使用 WHERE 子句：
DELETE FROM emp WHERE deptno = 10;
內容解密：
WHERE子句指定要刪除的紀錄條件。- 在此例中，刪除
deptno為 10 的所有紀錄。
刪除單一紀錄
要刪除單一紀錄，需要指定唯一條件，通常是主鍵：
DELETE FROM emp WHERE empno = 7782;
內容解密：
- 使用主鍵
empno指定要刪除的紀錄。 - 在此例中，刪除
empno為 7782 的紀錄。
刪除參照完整性違規的紀錄
要刪除參照完整性違規的紀錄，可以使用 NOT EXISTS 或 NOT IN 子句：
DELETE FROM emp
WHERE NOT EXISTS (
SELECT * FROM dept
WHERE dept.deptno = emp.deptno
);
或
DELETE FROM emp
WHERE deptno NOT IN (SELECT deptno FROM dept);
內容解密：
NOT EXISTS和NOT IN用於檢查emp表格中的deptno是否存在於dept表格中。- 如果不存在，則刪除該紀錄。
資料函式庫重複資料刪除與相關查詢技術
在資料函式倉管理中，刪除重複資料是一項常見且重要的任務。本篇文章將探討如何有效地刪除重複資料，並介紹相關的查詢技術。
刪除重複資料的方法
當資料表中存在重複的資料時，我們需要定義什麼是重複資料。在本例中，重複資料是指在 NAME 欄位中具有相同值的記錄。我們的目標是保留其中一筆記錄，並刪除其他重複的記錄。
使用子查詢與聚合函式
要刪除重複資料，可以使用子查詢與聚合函式（如 MIN）來選擇要保留的記錄。以下是一個 SQL 範例：
delete from dupes
where id not in (
select min(id)
from dupes
group by name
)
對於 MySQL 使用者，由於不能直接在 DELETE 陳述式中參照同一張表，因此需要使用一個臨時表來間接實作：
delete from dupes
where id not in (
select min(id)
from (select id, name from dupes) tmp
group by name
)
內容解密：
- 子查詢的作用：子查詢用於找出每個
NAME分組中最小的ID，代表要保留的記錄。 GROUP BY的應用：GROUP BY將資料按照NAME分組，以便對每個分組進行聚合運算。MIN聚合函式：選擇每個分組中最小的ID，作為保留的依據。DELETE陳述式：刪除ID不在子查詢結果中的記錄，即刪除重複資料。
刪除被其他表參照的記錄
在某些情況下，我們需要根據其他表的參考資料來刪除記錄。例如，刪除那些所屬部門發生過三次或以上事故的員工記錄。
使用子查詢與聚合函式進行條件刪除
首先，找出發生事故次數大於或等於三次的部門編號：
select deptno
from dept_accidents
group by deptno
having count(*) >= 3
然後，刪除所屬部門編號在上述結果中的員工記錄：
delete from emp
where deptno in (
select deptno
from dept_accidents
group by deptno
having count(*) >= 3
)
內容解密：
- 子查詢找出目標部門：透過
GROUP BY和HAVING COUNT(*) >= 3，找出發生事故次數達三次或以上的部門編號。 DELETE陳述式根據條件刪除：利用子查詢的結果，刪除emp表中屬於這些部門的員工記錄。
元資料查詢簡介
元資料查詢是用於取得資料函式庫結構資訊的查詢，例如表結構、索引、外部索引鍵等。不同資料函式倉管理系統（RDBMS）提供了不同的系統表或檢視來儲存這些元資料。
列出某個模式（Schema）中的所有表
要列出某個模式中的所有表，可以查詢相應的系統表或檢視。具體的 SQL 語法會根據所使用的 RDBMS 而有所不同。
結語
本篇文章介紹瞭如何刪除重複資料、根據其他表的參考刪除記錄，以及元資料查詢的基本概念。這些技術對於維護資料函式庫的一致性和完整性至關重要。掌握這些技能，可以幫助資料函式倉管理員更有效地管理和最佳化資料函式庫。
資料函式庫中繼資料查詢技術解析
在資料函式倉管理與開發過程中，查詢中繼資料（Metadata）是一項關鍵技術。中繼資料提供了有關資料函式庫結構、物件定義及相互關係的重要資訊。本文將探討如何在不同關聯式資料函式倉管理系統（RDBMS）中查詢表格、欄位、索引及約束等中繼資料。
查詢資料函式庫中的表格清單
要列出特定schema下的所有表格，不同的資料函式庫系統提供了各自的系統表格或檢視來查詢這些資訊。
DB2
查詢SYSCAT.TABLES系統表格：
SELECT tabname
FROM syscat.tables
WHERE tabschema = 'SMEAGOL'
內容解密：
SYSCAT.TABLES包含了資料函式庫中所有表格的資訊。tabname欄位儲存了表格的名稱。tabschema欄位用於過濾特定schema下的表格。
Oracle
查詢SYS.ALL_TABLES系統檢視：
SELECT table_name
FROM all_tables
WHERE owner = 'SMEAGOL'
內容解密：
SYS.ALL_TABLES檢視包含了當前使用者有權存取的所有表格資訊。table_name欄位儲存了表格的名稱。owner欄位用於指定表格所屬的schema。
PostgreSQL, MySQL, SQL Server
查詢INFORMATION_SCHEMA.TABLES標準檢視：
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'SMEAGOL'
內容解密：
INFORMATION_SCHEMA.TABLES是ISO SQL標準定義的檢視，用於提供資料函式庫中表格的中繼資料。table_name欄位儲存了表格的名稱。table_schema欄位用於過濾特定schema下的表格。
查詢表格中的欄位資訊
進一步地，我們可能需要查詢特定表格中的欄位資訊，包括欄位名稱、資料型別及在表格中的位置。
DB2
查詢SYSCAT.COLUMNS系統表格：
SELECT colname, typename, colno
FROM syscat.columns
WHERE tabname = 'EMP' AND tabschema = 'SMEAGOL'
內容解密：
SYSCAT.COLUMNS包含了資料函式庫中所有欄位的詳細資訊。colname、typename及colno分別代表欄位名稱、資料型別及欄位順序。
Oracle
查詢ALL_TAB_COLUMNS系統檢視：
SELECT column_name, data_type, column_id
FROM all_tab_columns
WHERE owner = 'SMEAGOL' AND table_name = 'EMP'
內容解密：
ALL_TAB_COLUMNS提供了當前使用者可存取的表格欄位資訊。column_name、data_type及column_id分別代表欄位名稱、資料型別及欄位順序。
PostgreSQL, MySQL, SQL Server
查詢INFORMATION_SCHEMA.COLUMNS標準檢視：
SELECT column_name, data_type, ordinal_position
FROM information_schema.columns
WHERE table_schema = 'SMEAGOL' AND table_name = 'EMP'
內容解密：
INFORMATION_SCHEMA.COLUMNS提供了符合ISO SQL標準的欄位中繼資料查詢介面。column_name、data_type及ordinal_position分別代表欄位名稱、資料型別及欄位順序。
索引資訊查詢
瞭解表格上的索引對於效能調優至關重要。不同資料函式庫系統提供了各自的方法來查詢索引資訊。
DB2
查詢SYSCAT.INDEXES和SYSCAT.INDEXCOLUSE系統表格：
SELECT a.tabname, b.indname, b.colname, b.colseq
FROM syscat.indexes a, syscat.indexcoluse b
WHERE a.tabname = 'EMP' AND a.tabschema = 'SMEAGOL'
AND a.indschema = b.indschema AND a.indname = b.indname
內容解密：
SYSCAT.INDEXES和SYSCAT.INDEXCOLUSE共同提供了索引的詳細資訊。- 查詢結果包含了索引名稱、所涉及的欄位及欄位在索引中的順序。
Oracle
查詢SYS.ALL_IND_COLUMNS系統檢視：
SELECT table_name, index_name, column_name, column_position
FROM sys.all_ind_columns
WHERE table_name = 'EMP' AND table_owner = 'SMEAGOL'
內容解密：
SYS.ALL_IND_COLUMNS檢視提供了索引所涉及的欄位資訊。- 查詢結果包含了索引名稱、欄位名稱及欄位在索引中的位置。
約束資訊查詢
瞭解表格上的約束對於維護資料完整性非常重要。
DB2
查詢SYSCAT.TABCONST和SYSCAT.COLUMNS系統表格：
SELECT a.tabname, a.constname, b.colname, a.type
FROM syscat.tabconst a, syscat.columns b
WHERE a.tabname = 'EMP' AND a.tabschema = 'SMEAGOL'
AND a.tabname = b.tabname AND a.tabschema = b.tabschema
內容解密：
- 查詢結果包含了約束名稱、所涉及的欄位及約束型別。
5.5 查詢未建立對應索引的外來鍵
問題
你想要列出具有外來鍵欄位但未建立索引的資料表。例如，你想確認 EMP 資料表上的外來鍵是否已建立索引。
解決方案
DB2
查詢 SYSCAT.TABCONST、SYSCAT.KEYCOLUSE、SYSCAT.INDEXES 和 SYSCAT.INDEXCOLUSE：
select fkeys.tabname,
fkeys.constname,
fkeys.colname,
ind_cols.indname
from (
select a.tabschema, a.tabname, a.constname, b.colname
from syscat.tabconst a,
syscat.keycoluse b
where a.tabname = 'EMP'
and a.tabschema = 'SMEAGOL'
and a.type = 'F'
and a.tabname = b.tabname
and a.tabschema = b.tabschema
) fkeys
left join (
select a.tabschema,
a.tabname,
a.indname,
b.colname
from syscat.indexes a,
syscat.indexcoluse b
where a.indschema = b.indschema
and a.indname = b.indname
) ind_cols
on (fkeys.tabschema = ind_cols.tabschema
and fkeys.tabname = ind_cols.tabname
and fkeys.colname = ind_cols.colname)
where ind_cols.indname is null;
Oracle
查詢 SYS.ALL_CONS_COLUMNS、SYS.ALL_CONSTRAINTS 和 SYS.ALL_IND_COLUMNS：
select a.table_name,
a.constraint_name,
a.column_name,
c.index_name
from all_cons_columns a,
all_constraints b,
all_ind_columns c
where a.table_name = 'EMP'
and a.owner = 'SMEAGOL'
and b.constraint_type = 'R'
and a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.owner = c.table_owner (+)
and a.table_name = c.table_name (+)
and a.column_name = c.column_name (+)
and c.index_name is null;
PostgreSQL
查詢 INFORMATION_SCHEMA.KEY_COLUMN_USAGE、INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS、INFORMATION_SCHEMA.COLUMNS 和 PG_CATALOG.PG_INDEXES：
select fkeys.table_name,
fkeys.constraint_name,
fkeys.column_name,
ind_cols.indexname
from (
select a.constraint_schema,
a.table_name,
a.constraint_name,
a.column_name
from information_schema.key_column_usage a,
information_schema.referential_constraints b
where a.constraint_name = b.constraint_name
and a.constraint_schema = b.constraint_schema
and a.constraint_schema = 'SMEAGOL'
and a.table_name = 'EMP'
) fkeys
left join (
select a.schemaname, a.tablename, a.indexname, b.column_name
from pg_catalog.pg_indexes a,
information_schema.columns b
where a.tablename = b.table_name
and a.schemaname = b.table_schema
) ind_cols
on (fkeys.constraint_schema = ind_cols.schemaname
and fkeys.table_name = ind_cols.tablename
and fkeys.column_name = ind_cols.column_name)
where ind_cols.indexname is null;
MySQL
使用 SHOW INDEX 命令檢索索引資訊，並與 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 的結果進行比較：
SHOW INDEX FROM EMP;
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'EMP';
SQL Server
查詢 SYS.TABLES、SYS.FOREIGN_KEYS、SYS.COLUMNS、SYS.INDEXES 和 SYS.INDEX_COLUMNS：
select fkeys.table_name,
fkeys.constraint_name,
fkeys.column_name,
ind_cols.index_name
from (
select a.object_id,
d.column_id,
a.name table_name,
b.name constraint_name,
d.name column_name
from sys.tables a
join sys.foreign_keys b on (a.name = 'EMP' and a.object_id = b.parent_object_id)
join sys.foreign_key_columns c on (b.object_id = c.constraint_object_id)
join sys.columns d on (c.constraint_column_id = d.column_id and a.object_id = d.object_id)
) fkeys
left join (
select a.name index_name,
b.object_id,
b.column_id
from sys.indexes a,
sys.index_columns b
where a.index_id = b.index_id
) ind_cols
on (fkeys.object_id = ind_cols.object_id and fkeys.column_id = ind_cols.column_id)
where ind_cols.index_name is null;
詳細討論
每個資料函式庫廠商在修改資料列時使用自己的鎖定機制。在有父子關係的情況下，子資料表的外來鍵欄位上建立索引可以減少鎖定（詳情請參閱特定 RDBMS 的檔案）。此外，當子資料表與父資料表在外來鍵欄位上進行連線時，索引也可以提高效能。
程式碼範例與解析
以下為Oracle版本程式碼範例與詳細解析：
select
-- 選取需要的欄位：資料表名稱、限制名稱、欄位名稱、索引名稱
a.table_name,
a.constraint_name,
a.column_name,
c.index_name
from
-- 使用ALL_CONS_COLUMNS取得限制的欄位資訊
all_cons_columns a,
-- 使用ALL_CONSTRAINTS取得限制的詳細資訊
all_constraints b,
-- 使用ALL_IND_COLUMNS取得索引的欄位資訊
all_ind_columns c
where
-- 設定要查詢的資料表名稱
a.table_name = 'EMP'
-- 設定資料表的所有者
and a.owner = 'SMEAGOL'
-- 篩選外來鍵限制（R代表Referential integrity）
and b.constraint_type = 'R'
-- 連線條件：所有者、資料表名稱、限制名稱需一致
and a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
-- 外連線ALL_IND_COLUMNS，檢查該欄位是否在某個索引中
and a.owner = c.table_owner (+)
and a.table_name = c.table_name (+)
and a.column_name = c.column_name (+)
-- 篩選出未被索引的欄位（即對應的index_name為空）
and c.index_name is null;
#### 程式碼解析：
選取欄位：從三個系統檢視中選取需要的資訊：
a.table_name：外來鍵所在的資料表名稱。a.constraint_name：外來鍵限制的名稱。a.column_name：外來鍵對應的欄位名稱。c.index_name：該欄位所屬的索引名稱（如果存在）。
關聯多個系統檢視：
all_cons_columns：提供限制對應的欄位資訊。all_constraints：提供限制的型別和所屬資料表資訊，用於過濾出外來鍵（constraint_type=‘R’）。all_ind_columns：提供索引對應的欄位資訊，用於檢查該外來鍵欄位是否已建立索引。
連線條件：
- 將外來鍵欄位與其所屬限制進行連線，以取得正確的外來鍵資訊。
篩選條件：
- 檢查未建立索引的外來鍵：透過左外連線將
all_ind_columns與前兩個檢視連線，並篩選出對應的index_name is null的情況，即該外來鍵欄位未被索引。