OracleSQL技術勉強メモ

S.S

こんにちは。エンジニアのS.Sです。
今回は、ORACLE MASTER Silver SQL 2019 (Oracle Database SQL Certified Associate)について勉強したことをメモをまとめました！

UNION ALL句

UNION ALL句は、複数のSELECT文の結果を単一の結果に結合するために使用されますが、UNION句と異なり、重複する行を除外せずにすべての行を含めます。特に重複したデータが結果で必要な場合や、重複を回避してパフォーマンスを向上させたい場合に有用です。

使用方法

UNION ALLの基本的な使用法は以下の通りです。

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

このクエリは、table1とtable2から選択された列のデータを結合し、重複する行も含めてすべての行を結果に含めます。

使用例

employeesテーブルとcontractorsテーブルがあり、どちらもname列を持っているとします。これら二つのテーブルから、重複した名前も含めてすべての名前を取得したい場合、次のようにUNION ALLを使用できます。

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

このクエリは、employeesテーブルとcontractorsテーブルの両方から名前を取得し、重複した名前も含めた全ての名前を返します。もし両方のテーブルに同じ名前が存在する場合、その名前は結果に複数回表示されます。

注意点

  • UNION ALLを使用する際も、SELECT文で指定される各列の数とデータ型が一致している必要があります。

  • UNION ALLはUNIONよりも通常パフォーマンスが良いです。これは、UNIONが重複を除去するために追加の処理を必要とするのに対し、UNION ALLではそのような処理が不要だからです。

  • UNION ALLは、特に大量のデータを扱うクエリにおいて、より効率的な選択肢となることが多いです。

UNION句

OracleのUNION句は、複数のSELECT文の結果を単一の結果に結合するために使用されます。UNION句を使用する主な目的は、異なるテーブルから取得したデータを、重複を除外して一つの結果として表示することです。UNIONは、それぞれのSELECT文によって返される行を結合し、重複する行を自動的に除外します。

使用方法

基本的な使用法は以下の通りです。

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

このクエリは、table1とtable2から選択された列のデータを結合し、重複する行を除外した結果を返します。

使用例

想定するシナリオとして、employeesテーブルとcontractorsテーブルがあり、どちらもname列を持っているとします。これら二つのテーブルから、重複を除外してすべての異なる名前を取得したい場合、次のようにUNIONを使用できます。

SELECT name FROM employees
UNION
SELECT name FROM contractors;

このクエリは、employeesテーブルとcontractorsテーブルの両方から名前を取得し、重複を除外した一覧を返します。

注意点

  • UNIONを使用する際、SELECT文で指定される各列の数とデータ型が一致している必要があります。

  • 重複した行を保持したい場合は、UNION ALLを使用します。UNION ALLはUNIONと同様に動作しますが、重複を除外しません。

  • 性能の観点から、不必要にUNIONを使用するとクエリの実行時間が長くなる可能性があるため、必要な場合にのみ使用することが推奨されます。

INTERSECT句

INTERSECT句は、二つ以上のSELECT文によって返される結果の共通部分、つまり両方の結果に存在する行のみを取得するために使用されます。これは、異なるデータソースからの共通データを見つけ出したい場合に特に有用です。

使用方法

INTERSECTの基本的な使用法は以下の通りです。

SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

このクエリは、table1とtable2の両方から選択された列に含まれる共通のデータを返します。

使用例

例えば、employeesテーブルとproject_membersテーブルがあり、どちらもemployee_id列を持っているとします。特定のプロジェクトに参加している従業員のみを特定したい場合にINTERSECTを使用できます。

SELECT employee_id FROM employees
INTERSECT
SELECT employee_id FROM project_members;

このクエリは、employeesテーブルとproject_membersテーブルの両方に存在するemployee_idのみを返します。つまり、特定のプロジェクトに参加している従業員のIDを特定するのに役立ちます。

注意点

  • INTERSECTを使用する際、SELECT文で指定される各列の数とデータ型が一致している必要があります。

  • INTERSECTは内部的に重複を除外します。つまり、結果には重複する行が含まれません。

  • INTERSECTの使用は、比較的少ない場合が多いですが、特定の条件下で非常に便利なツールとなります。

  • INTERSECTのパフォーマンスは、使用するデータベースの実装やデータの量によって異なるため、大量のデータを扱う場合はパフォーマンスを考慮する必要があります。

MINUS句

MINUS句は、Oracle SQLにおいて、第一のSELECT文によって返される結果から、第二のSELECT文によって返される結果の行を除外するために使用されます。この操作により、最初のクエリの結果に含まれ、かつ第二のクエリの結果には含まれない行のみが取得されます。MINUS句は、特定のデータセットから別のデータセットのデータを"引く"ために役立ちます。

使用方法

MINUSの基本的な使用法は以下の通りです。

SELECT column_name(s) FROM table1
MINUS
SELECT column_name(s) FROM table2;

このクエリは、table1の選択された列に含まれるデータのうち、table2の同じ列には含まれないデータを返します。

使用例

例えば、all_employeesテーブルには企業の全従業員のリストが、current_project_employeesテーブルには現在プロジェクトに割り当てられている従業員のリストが含まれているとします。プロジェクトにまだ割り当てられていない従業員のリストを取得したい場合、次のようにMINUSを使用できます。

SELECT employee_id FROM all_employees
MINUS
SELECT employee_id FROM current_project_employees;

このクエリは、all_employeesテーブルに含まれる従業員のうち、current_project_employeesテーブルには含まれない、つまり現在プロジェクトに割り当てられていない従業員のIDを返します。

注意点

  • MINUSを使用する際、SELECT文で指定される各列の数とデータ型が一致している必要があります。

  • MINUSは内部的に重複を除外します。つまり、結果には重複する行が含まれません。

  • MINUS句の使用は、データの差分を特定する場合に特に有効です。

  • MINUSのパフォーマンスは、操作するデータの量やデータベースの実装によって異なるため、大規模なデータセットに対して使用する場合は慎重に検討する必要があります。

集合演算を使う際の注意点

集合演算（UNION, UNION ALL, INTERSECT, MINUS）を使用する際に考慮すべき主な注意点は以下の通りです。それぞれの注意点について、簡単な説明と具体例を挙げてみましょう。

列の数とデータ型の一致

  • 説明: 集合演算を行う際、各SELECT文で指定される列の数とデータ型が一致している必要があります。これは、結果を正しく結合するためです。

  • 具体例: employeesテーブルからemployee_id（数値型）を選択し、projectsテーブルからproject_id（数値型）とproject_name（文字列型）を選択する場合、UNIONやINTERSECTなどを使用するとエラーになります。両方のSELECT文で列の数が異なるためです。

索引

索引（インデックス）は、データベースにおいてテーブルのデータへのアクセス速度を向上させるために使用されるデータ構造です。データベースの索引は、本の索引や図書館のカードカタログに似ており、特定の情報を迅速に見つけるためのポインターを提供します。索引を適切に使用することで、大量のデータの中から特定のレコードを検索、ソート、または絞り込む際のパフォーマンスを大幅に向上させることができます。

索引の種類

  • 主キー索引: テーブルの主キーに自動的に作成される。主キーは各行を一意に識別します。

  • 一意索引（ユニーク索引）: 列の値がテーブル内で一意であることを保証します。主キー索引も一種の一意索引ですが、任意の列に一意索引を設定することもできます。

  • 非一意索引: 列の値が重複しても良い場合に使用します。検索やソートを高速化する目的で作成されます。

  • 複合索引: 2つ以上の列を組み合わせた索引。特定のクエリ操作で複数の列が頻繁に使用される場合に有効です。

具体例

想定するシナリオとして、employeesテーブルがあり、以下の列が含まれているとします。

  • employee_id (従業員ID)

  • last_name (姓)

  • department_id (部署ID)

このテーブルでの索引使用例は以下の通りです。

  • 主キー索引: employee_id列に設定される。これにより、特定の従業員IDに基づいてデータを迅速に検索できます。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    last_name VARCHAR(100),
    department_id INT
);

  • 一意索引: last_name列に設定され、従業員の姓がテーブル内で一意であることを保証します（現実世界では姓が一意であることは稀ですが、例として挙げています）。

CREATE UNIQUE INDEX idx_lastname ON employees (last_name);

  • 非一意索引: department_id列に設定される。これにより、特定の部署に所属する従業員を検索するクエリのパフォーマンスが向上します。

CREATE INDEX idx_department_id ON employees (department_id);

  • 複合索引: last_nameとdepartment_idの両方の列を含む複合索引を作成することで、これらの列を組み合わせた検索やソート操作を高速化できます。

CREATE INDEX idx_lastname_department_id ON employees (last_name, department_id);

注意点

  • 索引は検索性能を向上させますが、テーブルにデータを挿入、更新、削除する際には追加の処理が必要になるため、書き込み操作のパフォーマンスに影響を与える可能性があります。

  • 必要以上に索引を作成すると、ディスクスペースの消費が増加し、管理が複雑になる可能性があるため、実際に必要な索引のみを慎重に選択することが重要です。

ビュー

ビュー（View）は、データベースにおいて一つまたは複数のテーブルから派生した仮想的なテーブルです。ビューは物理的にデータを格納しませんが、実行時にSQLクエリを介してデータを表示します。ビューを使用することで、複雑なクエリを単純化したり、データのセキュリティを向上させたり、データをより理解しやすい形で表示したりすることができます。

ビューの特徴

  • 仮想テーブル: ビューは物理的なデータを格納しない代わりに、定義されたSQLクエリに基づいてデータを生成します。

  • データの抽象化: ビューを使用することで、複数のテーブルにまたがる複雑なクエリを単純化し、必要なデータのみを表示できます。

  • セキュリティ: 特定のユーザーに対してテーブルの一部のデータのみを公開することで、データのアクセス制御を強化できます。

  • データの一貫性: ビューを使用すると、アプリケーションが参照するデータの表示方法を一貫させることができます。データベースの構造が変更された場合でも、ビューを更新することでアプリケーションコードの変更を最小限に抑えることができます。

ビューの作成

ビューの作成はCREATE VIEWステートメントを使用して行われます。以下に基本的な形式を示します

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

具体例

例えば、employeesテーブルがあり、employee_id、first_name、last_name、department_idの列を持っているとします。従業員のフルネームと所属部署だけを表示するビューを作成する例は以下の通りです

CREATE VIEW view_employee_details AS SELECT employee_id, first_name || ' ' || last_name AS full_name, department_id FROM employees;

このビューは、employeesテーブルから従業員ID、フルネーム（名と姓を結合したもの）、部署IDを選択しています。ビューを通じて、この情報を簡単にクエリすることができます。

注意点

  • ビューを過度に使用すると、クエリのパフォーマンスに影響を与えることがあります。特に、多数のテーブルを結合する複雑なビューや、大量のデータを扱うビューでは、パフォーマンスの低下に注意が必要です。

  • ビューはその定義に基づいてデータを表示するため、基になるテーブルのデータが変更されると、ビューを通じて見えるデータも変更されます。

  • 一部のデータベースシステムでは、ビュー上での更新操作（INSERT、UPDATE、DELETE）が制限される場合があります。これは、ビューが複数のテーブルからデータを結合している場合など、基になるデータに対する操作が複雑になるためです。

データベースオブジェクトとDDL

DDL（Data Definition Language）は、データベーススキーマを定義、変更、削除するためのSQL（Structured Query Language）の一部です。DDLには主に、テーブル、ビュー、インデックス、シーケンス、シノニム、トリガーなどのデータベースオブジェクトの作成や削除、変更を行うコマンドが含まれます。以下、主要なデータベースオブジェクトについて説明します。

テーブル (Tables)

  • 説明: データベースの基本的なデータ格納構造です。行（レコード）と列（フィールド）でデータを格納します。

  • DDLコマンド: CREATE TABLE, ALTER TABLE, DROP TABLE,TRAUCATE TABLE

ビュー (Views)

  • 説明: 一つまたは複数のテーブルから派生した仮想的なテーブルで、特定のSQLクエリに基づいたデータを表示します。物理的にデータを格納しません。

  • DDLコマンド: CREATE VIEW, DROP VIEW,ALTER VIEW

インデックス (Indexes)

  • 説明: テーブルのデータへのアクセス速度を向上させるためのデータ構造。特定の列に対して作成され、データ検索の効率を高めます。

  • DDLコマンド: CREATE INDEX, DROP INDEX,ALTER INDEX

シーケンス (Sequences)

  • 説明: 一連の数値を自動的に生成するためのオブジェクトで、主に一意の識別子（IDなど）を生成するのに使用されます。

  • DDLコマンド: CREATE SEQUENCE, ALTER SEQUENCE, DROP SEQUENCE

シノニム (Synonyms)

  • 説明: データベースオブジェクト（テーブル、ビューなど）に別名を付けるオブジェクトです。オブジェクトへのアクセスを容易にするために使用されます。

  • DDLコマンド: CREATE SYNONYM, DROP SYNONYM,ALTER SYNONYM

スキーマ (Schemas)

  • 説明: 関連するデータベースオブジェクトの集まりを管理するための構造です。一般に、ユーザーまたはアプリケーションごとにスキーマが定義されます。

  • DDLコマンド: スキーマ自体に特定のDDLコマンドはありませんが、スキーマ内のオブジェクトを作成するにはCREATEコマンドを使用し、その所有者を指定します。

これらのデータベースオブジェクトを適切に使用することで、データの整理、アクセス制御、パフォーマンスの最適化など、効果的なデータ管理が可能になります。

表の作成と列のデフォルト値の指定

表を作成する際に、列にデフォルト値を指定することは、その列に対して値が明示的に提供されない場合に自動的に使用される値を設定する一般的な方法です。これは、データの整合性を保持し、アプリケーションが想定するデフォルトの振る舞いを保証するのに役立ちます。

表の作成

表を作成するにはCREATE TABLEステートメントを使用します。基本的な構文は以下の通りです。

CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ... );

  • table_name: 作成する表の名前です。

  • column1, column2, ... : 表の列の名前です。

  • datatype: 列に格納されるデータの型を指定します（例：VARCHAR, INT, DATEなど）。

  • constraint: 列に適用される任意の制約（例：PRIMARY KEY, NOT NULL, UNIQUEなど）。

列のデフォルト値の指定

列にデフォルト値を指定するには、列の定義の一部としてDEFAULTキーワードを使用します。構文は以下の通りです。

CREATE TABLE table_name ( column_name datatype DEFAULT default_value );

  • default_value: 列のデフォルト値です。これは列のデータ型に適合する値でなければなりません。

具体例

従業員の情報を格納するためのemployees表を作成し、status列にデフォルト値として'active'を指定する例を示します。このstatus列は従業員の現在の雇用状態を表し、新しいレコードが挿入されたときに明示的な値が提供されない場合、自動的に'active'として扱われます。

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), status VARCHAR(20) DEFAULT 'active' );

この例では、employees表にはemployee_id（主キー）、first_name、last_name、そしてデフォルト値'active'が設定されたstatus列が含まれています。employee_idは各従業員に一意の識別子を割り当てるために使用され、first_nameとlast_nameは従業員の名前を格納します。新しい従業員レコードがこの表に追加される際、status列に対して値が提供されなければ、自動的に'active'が使用されます。

制約キーの指定方法と構文について

制約キーはデータベーステーブルの列に適用されるルールや制限で、データの整合性と正確性を保証するために使用されます。以下は、一般的な制約キーとその指定方法に関する概要です。

主キー制約 (PRIMARY KEY)

  • 説明: 各行を一意に識別するための列（または列の組み合わせ）。テーブル内で重複やNULL値を許可しません。

  • 構文:

CREATE TABLE table_name (
    column_name datatype PRIMARY KEY,
    ...
);

複合主キーを作成する場合:

CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (column1, column2, ...) );

外部キー制約 (FOREIGN KEY)

  • 説明: 他のテーブルの行を参照する列（または列の組み合わせ）。参照整合性を保証します。

CREATE TABLE table_name (
    column_name datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES parent_table(parent_column_name),
    ...
);

一意制約 (UNIQUE)

  • 説明: 列内で各値が一意であることを保証します。主キーと異なり、NULL値の重複は許可される場合があります。

  • 構文:

CREATE TABLE table_name (
    column_name datatype UNIQUE,
    ...
);

チェック制約 (CHECK)

  • 説明: 列値が特定の条件を満たす必要があることを指定します。例えば、特定の範囲内の値のみを許可するなど。

  • 構文:

CREATE TABLE table_name (
    column_name datatype,
    ...
    CHECK (condition),
    ...
);

NOT NULL制約

  • 説明: 列がNULL値を持つことを禁止します。この制約がある列は、必ず値を持つ必要があります。

  • 構文:

CREATE TABLE table_name (
    column_name datatype NOT NULL,
    ...
);

具体例

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL CHECK (salary > 0),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

この例では、employeesテーブルに以下の制約が適用されています。

  • employee_idは主キー制約を持ち、各従業員を一意に識別します。

  • first_nameとlast_nameはNOT NULL制約を持ち、これらの列が空になることを禁止します。

  • email列には一意制約があり、全従業員で重複しないメールアドレスを保証します。

  • salary列にはチェック制約があり、給与が0より大きくなければなりません。

  • department_idは外部キー制約を持ち、departmentsテーブルのdepartment_id列を参照します。

CONSTRAINT句

CONSTRAINT句は、テーブルの列に対して名前付きの制約を設定するために使用されます。これにより、データベース内でデータの整合性を保証し、特定のビジネスルールや条件を強制することができます。CONSTRAINT句を使用して主キー、外部キー、一意制約、チェック制約、およびNOT NULL制約を含むさまざまな種類の制約を定義できます。

CONSTRAINT句の使用法

制約に名前を付けることで、後でその制約を容易に識別し、必要に応じて変更または削除することが可能になります。構文は以下の通りです。

CREATE TABLE table_name (
    column_name datatype CONSTRAINT constraint_name constraint_type (constraint_condition),
    ...
);

または、テーブルレベルで制約を定義する場合は以下のようになります。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name constraint_type (column_name(s)),
    ...
);

  • table_name: テーブルの名前です。

  • column_name: 列の名前です。

  • datatype: 列のデータ型です。

  • constraint_name: 制約の名前です。この名前はテーブル内で一意でなければなりません。

  • constraint_type: 制約の種類です（例：PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL）。

  • constraint_condition: CHECK制約の場合に指定する条件式です。

具体例

従業員テーブルを作成し、各種制約を名前付きで設定する例を以下に示します。

CREATE TABLE employees (
    employee_id INT CONSTRAINT pk_employees PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    email VARCHAR(100) CONSTRAINT unq_employees_email UNIQUE,
    salary DECIMAL CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

この例では、employeesテーブルに以下の名前付き制約が設定されています。

  • pk_employeesはemployee_id列に対する主キー制約です。

  • unq_employees_emailはemail列に対する一意制約です。

  • chk_salaryはsalary列に対するチェック制約で、給与が0より大きいことを保証します。

  • fk_departmentはdepartment_id列に対する外部キー制約で、departmentsテーブルのdepartment_id列を参照します。

名前付き制約を使用することで、制約管理が容易になり、データベースのメンテナンスやデバッグがより直感的に行えるようになります。

データディクショナリビューについて

データディクショナリビューは、データベースのメタデータ、つまりデータベースに関するデータを提供するシステムビューです。これにはテーブル、ビュー、インデックス、制約、ストアドプロシージャなど、データベースオブジェクトの構造や特性に関する情報が含まれています。データディクショナリビューを使用することで、データベース管理者や開発者はデータベースの設定や構造を理解し、分析することができます。

データディクショナリビューの主な種類

  • ユーザービュー: 現在のユーザーが所有するオブジェクトに関する情報を提供します。例えば、ユーザーが所有するテーブルやビューに関する詳細です。

  • 全オブジェクトビュー: データベース内のすべてのオブジェクトに関する情報を提供します。これには、他のユーザーが所有するオブジェクトも含まれます。

  • システムビュー: データベースのシステムレベルの情報、例えばデータベースの構成やパフォーマンスに関する統計などを提供します。

例: Oracleデータベースのデータディクショナリビュー

Oracleデータベースでは、以下のようなデータディクショナリビューがよく使用されます。

  • ALL_TABLES: ユーザーがアクセスできるすべてのテーブルに関する情報。

  • USER_TABLES: ユーザーが所有するテーブルに関する情報。

  • DBA_TABLES: データベース内のすべてのテーブルに関する情報（DBA権限を持つユーザーのみアクセス可能）。

  • ALL_TAB_COLUMNS: ユーザーがアクセスできるテーブルの列に関する情報。

  • USER_TAB_COLUMNS: ユーザーが所有するテーブルの列に関する情報。

  • ALL_CONSTRAINTS, USER_CONSTRAINTS: 制約に関する情報。

使用例

データディクショナリビューを使用して、特定のユーザーが所有するテーブルのリストを取得する例は以下の通りです。

SELECT table_name FROM user_tables;

このクエリは、現在のユーザーが所有するすべてのテーブルの名前を返します。

エンジニアファーストの会社 株式会社CRE-CO　S.S


この記事が気に入ったらサポートをしてみませんか？