基本的なSQL構文一覧（CRUD、テーブル結合、ウィンドウ関数など）はこちらで書きました。

DBの作成・削除

データベースの操作やテーブルの管理は、データベース管理システム（DBMS）を使用して行います。以下に、一般的なデータベース操作のSQLコマンドの例を示します。

 

SHOW DATABASES;

このコマンドは、MySQLやMariaDBなどのデータベース管理システムで使用されるもので、利用可能なデータベースの一覧を表示します。

SHOW DATABASES;

このコマンドを実行すると、サーバ上のデータベースのリストが表示されます。

 

Oracleの場合、SHOW　DATABASES；は使えないので、後にあげるテーブル情報を表示するSQLで補完しましょう。

USER_TABLE,ALL_TAB_COLUMNS

 

CREATE DATABASE データベース名;

新しいデータベースを作成するには、CREATE DATABASE コマンドを使用します。

CREATE DATABASE データベース名;

このコマンドにより、データベース名 という名前のデータベースが作成されます。

 

USE データベース名;

作成したデータベースにアクセスするには、USE コマンドを使用します。

USE データベース名;
SELECT DATABASE();

USEでデータベース名 データベースがアクティブになり、SELECT DATABASEで利用している DB名を表示します。

 

DROP DATABASE データベース名;

データベースを削除するには、DROP DATABASE コマンドを使用します。

DROP DATABASE データベース名;

 

テーブルの作成・削除

 

CREATE テーブル名

テーブルを作成するには、CREATE テーブル名 コマンドを使用します。

  CREATE テーブル名 users(
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department_no INT
  );
  

このコマンドにより、users テーブルが作成されます。

 

以下は主に利用するデータ型の一覧です。

データ型	説明	使用例
INT or INTEGER	整数	10, -5, 1000
BIGINT	大きな整数	9223372036854775807
SMALLINT	小さな整数	5, -2
DECIMAL or NUMERIC	固定小数点数	10.50, -3.14
FLOAT or DOUBLE	浮動小数点数	3.14, -0.001
CHAR	固定長文字列	'Hello', 'A'
VARCHAR or TEXT	可変長文字列	'John Doe', 'Example Text'
DATE	日付	'2023-12-01'
TIME	時刻	'12:30:00'
DATETIME or TIMESTAMP	日時	'2023-12-01 12:30:00'
BOOLEAN	真偽値	true, false

 

NOT NULL 制約

NOT NULL 制約は、列の値が NULL になることを許可しない制約です。

CREATE TABLE employees (
  employee_name VARCHAR(255) NOT NULL,
  salary INT　DEFAULT 'データなし' NOT NULL
);

DEFAULTを指定すると、NULLの時に指定した文字が設定されるようになります。

 

UNIQUE 制約

UNIQUE 制約は、指定された列に一意の値しか含まれないことを保証します。

CREATE TABLE students (
  student_name VARCHAR(255) UNIQUE,
  email VARCHAR(255) UNIQUE
);

UNIQUEしたものは同じ値が入らなくなります。

 

CHECK 制約

CHECK 制約は、指定した条件の値しか更新できないようにする。

CREATE TABLE products (
  price DECIMAL(10, 2) CHECK (price > 0)
  CONSTRAINT check_range CHECK (value >= 0 AND value <= 100)
);

CONSTRAINT を使用することで、CHECK 制約に複数の条件を指定することができます。

 

主キー制約

主キー制約は、一意性と NOT NULL 制約の組み合わせで、指定された列は一意であり、NULL 値を含むことはできません。

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
);

 

外部キー制約

異なるテーブル間の関係を定義する制約で、外部キーが存在する場合、参照先のレコードがDELETEされるとエラーになる。

 

orders テーブルの customer_idが customersテーブルの customer_idを対象に外部キーの設定が行われます。

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

 

ON　UPDATE, ON DELETE

外部キー制約で参照元のデータが更新された時、または削除された時にどうするか事前に設定できます。

CCREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

ON UPDATE CASCADE は、参照先の行が変更されたときに依存する行も変更されることを示しています。
ON DELETE SET NULL は、参照先の行が削除されたときに依存する行の customer_id 列が NULL に設定されることを示しています。

制約	ON UPDATE	ON DELETE
CASCADE	変更に従い変更	削除に従い削除
SET NULL	変更に従いNULL設定	削除に従いNULL設定
SET DEFAULT	変更に従いデフォルト値設定	削除に従いデフォルト値設定
RESTRICT	変更制限(エラー)	削除制限(エラー)

 

AUTO_INCREMENT

値がNULLでINSERTした時に自動でカウントアップしてくれる。

 

user_idがAUTO_INCREMENT プロパティを持ち、主キーとして使用されます。新しくデータが挿入される度、user_idは前の最大値に1を加えて自動的に増えます。

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

 

COMMENT

テーブルや列にコメントを追加できる。

-- テーブルにコメントを追加する
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255)
) COMMENT='This is my table';

-- 列にコメントを追加する
ALTER TABLE my_table
MODIFY COLUMN id INT COMMENT 'Primary key identifier';

-- データベースにコメントを追加する
CREATE DATABASE my_database COMMENT 'This is my database';

 

DROP TABLE　データベース名;

テーブルを削除するには、DROP テーブル名 コマンドを使用します。

DROP テーブル名 users;

これにより、users テーブルがデータベースから削除されます。

 

SHOW TABLES;

テーブルを一覧表示するにはSHOW TABLES; コマンドを使用します。

SHOW TABLES;

 

OracleDBを使用している場合は、USER_TABLEを使って、テーブルを一覧表示できます。

 

USER_TABLE

SELECT ＊　FROM USER_TABLE;

現在のユーザーが所有するテーブルの一覧を取得します。

 

DESCRIBE データベース名;

テーブル定義を確認するにはDESCRIBE コマンドを使用します。

DESCRIBE users;

これにより、users テーブルのテーブル定義を確認できます。

 

OracleDBを使用している場合は、ALL_TAB_COLUMNSを使って、テーブル定義を確認できます。

 

ALL_TAB_COLUMNS

SELECT ＊　FROM ALL_TAB_COLUMNS　WHERE TABLE_NAME = 'データベース名';

データベース名に関する詳細な情報（各列の名前、データ型など）を取得できます。

 

テーブル定義の変更

 

RENAME TO

テーブル名を変更するには、RENAME TO を使用します。

  RENAME テーブル名 old_table_name TO new_table_name;
  

このコマンドにより、old_table_name を new_table_name に変更します。

 

DROP COLUMN

テーブルからカラムを削除するには、DROP COLUMN を使用します。

  ALTER テーブル名 table_name
  DROP COLUMN column_name;
  

このコマンドにより、table_name から column_name を削除します。

 

ADD

テーブルに新しいカラムを追加するには、ADD を使用します。

  ALTER テーブル名 table_name
  ADD new_column_name datatype;
  

このコマンドにより、table_name に new_column_name という新しいカラムを追加します。

末尾に「FIRST」を追加すると、テーブルの一番最初の列にカラムを追加し、

「AFTER カラム名」を追加すると、指定した列の後にカラムを追加します。

 

MODIFY

既存のカラムのデータ型や制約を変更するには、MODIFY を使用します。

  ALTER テーブル名 table_name
  MODIFY column_name new_datatype;
  

このコマンドにより、table_name の column_name のデータ型を new_datatype に変更します。

 

CHANGE COLUMN

既存のカラムの名前や場所定義を変更するには、CHANGE COLUMN を使用します。

  ALTER テーブル名 table_name
  CHANGE COLUMN old_column_name new_column_name datatype;
  

このコマンドにより、table_name の old_column_name を new_column_name に変更し、データ型も指定します。

 

DROP PRIMARY KEY

テーブルの主キーを削除するには、DROP PRIMARY KEY を使用します。

  ALTER テーブル名 table_name
  DROP PRIMARY KEY;
  

このコマンドにより、table_name の主キーが削除されます。