SQLデータベースを管理する方法
SQLチートシート
序章
SQLデータベースには、データの追加、変更、削除、およびクエリに必要なすべてのコマンドがインストールされています。 このチートシートスタイルガイドは、最も一般的に使用されるSQLコマンドのいくつかへのクイックリファレンスを提供します。
このガイドの使用方法:
- このガイドは、自己完結型のコマンドラインスニペットを含むチートシート形式です。
- 完了しようとしているタスクに関連するセクションにジャンプします
- このガイドのコマンドに
highlighted text
が表示されている場合、このテキストは独自のデータベースの列、テーブル、およびデータを参照している必要があることに注意してください。 - このガイド全体を通して、示されているデータ値の例はすべてアポストロフィ(
'
)で囲まれています。 SQLでは、文字列で構成されるデータ値をアポストロフィでラップする必要があります。 これは数値データには必要ありませんが、アポストロフィを含めても問題は発生しません。
SQLは標準として認識されていますが、ほとんどのSQLデータベースプログラムには独自の拡張機能があることに注意してください。 このガイドでは、リレーショナルデータベース管理システム(RDBMS)の例としてMySQLを使用していますが、提供されているコマンドは、PostgreSQL、MariaDB、SQLiteなどの他のリレーショナルデータベースプログラムでも機能します。 RDBMS間に大きな違いがある場合は、代替コマンドを含めました。
データベースプロンプトを開く(ソケット/信頼認証を使用)
Ubuntu 18.04のデフォルトでは、 root MySQLユーザーは、次のコマンドを使用してパスワードなしで認証できます。
sudo mysql
PostgreSQLプロンプトを開くには、次のコマンドを使用します。 この例では、含まれているスーパーユーザーの役割である postgres ユーザーとしてログインしますが、これを作成済みの役割に置き換えることができます。
sudo -u postgres psql
データベースプロンプトを開く(パスワード認証を使用)
root MySQLユーザーがパスワードで認証するように設定されている場合は、次のコマンドを使用して認証できます。
mysql -u root -p
データベースにroot以外のユーザーアカウントを既に設定している場合は、次の方法を使用してそのユーザーとしてログインすることもできます。
mysql -u user -p
上記のコマンドを実行すると、パスワードの入力を求められます。 コマンドの一部としてパスワードを指定する場合は、-p
オプションの直後にパスワードを入力し、間にスペースを入れないでください。
mysql -u root -ppassword
データベースの作成
次のコマンドは、デフォルト設定でデータベースを作成します。
CREATE DATABASE database_name;
データベースでデフォルトとは異なる文字セットと照合を使用する場合は、次の構文を使用してそれらを指定できます。
CREATE DATABASE database_name CHARACTER SET character_set COLLATE collation;
データベースの一覧表示
MySQLまたはMariaDBのインストールに存在するデータベースを確認するには、次のコマンドを実行します。
SHOW DATABASES;
PostgreSQLでは、次のコマンドで作成されたデータベースを確認できます。
\list
データベースの削除
データベース内に保持されているテーブルとデータを含むデータベースを削除するには、次の構造に従うコマンドを実行します。
DROP DATABASE IF EXISTS database;
ユーザーの作成
データベースの権限を指定せずにデータベースのユーザープロファイルを作成するには、次のコマンドを実行します。
CREATE USER username IDENTIFIED BY 'password';
PostgreSQLは、類似しているがわずかに異なる構文を使用します。
CREATE USER user WITH PASSWORD 'password';
新しいユーザーを作成し、1つのコマンドでそれらのユーザーに特権を付与する場合は、GRANT
ステートメントを発行することで実行できます。 次のコマンドは、新しいユーザーを作成し、RDBMS内のすべてのデータベースとテーブルに対する完全な特権をユーザーに付与します。
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
この前のGRANT
ステートメントのPRIVILEGES
キーワードに注意してください。 ほとんどのRDBMSでは、このキーワードはオプションであり、このステートメントは同等に次のように記述できます。
GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
ただし、厳密SQLモードがオンになっている場合、このような特権を付与するにはPRIVILEGES
キーワードが必要であることに注意してください。
ユーザーの削除
データベースユーザープロファイルを削除するには、次の構文を使用します。
DROP USER IF EXISTS username;
このコマンドは、デフォルトでは、削除されたユーザーによって作成されたテーブルを削除しないことに注意してください。そのようなテーブルにアクセスしようとすると、エラーが発生する可能性があります。
データベースの選択
テーブルを作成する前に、まずテーブルを作成するデータベースをRDBMSに通知する必要があります。 MySQLとMariaDBでは、次の構文でこれを行います。
USE database;
PostgreSQLでは、次のコマンドを使用して目的のデータベースを選択する必要があります。
\connect database
テーブルの作成
次のコマンド構造は、table
という名前の新しいテーブルを作成し、それぞれが固有のデータ型を持つ2つの列を含みます。
CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_taype );
テーブルの削除
すべてのデータを含むテーブルを完全に削除するには、次の手順を実行します。
DROP TABLE IF EXISTS table
テーブルへのデータの挿入
次の構文を使用して、テーブルに1行のデータを入力します。
INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_A', 'data_B', 'data_C' );
次のように、1つのコマンドを使用して、テーブルに複数行のデータを入力することもできます。
INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_1A', 'data_1B', 'data_1C' ), ( 'data_2A', 'data_2B', 'data_2C' ), ( 'data_3A', 'data_3B', 'data_3C' );
テーブルからのデータの削除
テーブルからデータの行を削除するには、次のコマンド構造を使用します。 value
は、削除する行の指定されたcolumn
に保持されている値である必要があることに注意してください。
DELETE FROM table WHERE column='value';
注:次の例のように、WHERE
句をDELETE
ステートメントに含めないと、テーブルに保持されているすべてのデータが削除されますが、列またはテーブル自体:
DELETE FROM table;
テーブル内のデータの変更
次の構文を使用して、特定の行に保持されているデータを更新します。 コマンドの最後にあるWHERE
句は、更新する行をSQLに指示することに注意してください。 value
は、column_A
に保持されている値で、変更する行と一致します。
注: WHERE
句をUPDATE
ステートメントに含めないと、コマンドはテーブルのすべての行に保持されているデータを置き換えます。
UPDATE table SET column_1 = value_1, column_2 = value_2 WHERE column_A=value;
列の挿入
次のコマンド構文は、テーブルに新しい列を追加します。
ALTER TABLE table ADD COLUMN column data_type;
列の削除
この構造に従うコマンドは、テーブルから列を削除します。
ALTER TABLE table DROP COLUMN column;
基本クエリの実行
テーブルの単一の列からすべてのデータを表示するには、次の構文を使用します。
SELECT column FROM table;
同じテーブルから複数の列をクエリするには、列名をコンマで区切ります。
SELECT column_1, column_2 FROM table;
列の名前をアスタリスク(*
)に置き換えることにより、テーブル内のすべての列を照会することもできます。 SQLでは、アスタリスクは「すべて」を表すプレースホルダーとして機能します。
SELECT * FROM table;
WHERE句の使用
次のように、SELECT
ステートメントにWHERE
句を追加することで、クエリの結果を絞り込むことができます。
SELECT column FROM table WHERE conditions_that_apply;
たとえば、次のような構文を使用して、単一の行からすべてのデータをクエリできます。 value
は、指定されたcolumn
とクエリする行の両方に保持される値である必要があることに注意してください。
SELECT * FROM table WHERE column = value;
比較演算子の操作
WHERE
句の比較演算子は、指定された列を値と比較する方法を定義します。 一般的なSQL比較演算子は次のとおりです。
オペレーター | それが何をするか |
---|---|
=
|
平等のテスト |
!=
|
不等式のテスト |
<
|
未満のテスト |
>
|
より大きいテスト |
<=
|
以下または等しいかどうかをテストします |
>=
|
以上または等しいかどうかをテストします |
BETWEEN
|
値が指定された範囲内にあるかどうかをテストします |
IN
|
行の値が指定された値のセットに含まれているかどうかをテストします |
EXISTS
|
指定された条件で行が存在するかどうかをテストします |
LIKE
|
値が指定された文字列と一致するかどうかをテストします |
IS NULL
|
NULL 値のテスト
|
IS NOT NULL
|
NULL 以外のすべての値をテストします
|
ワイルドカードの操作
SQLでは、ワイルドカード文字を使用できます。 これらは、テーブル内の特定のエントリを検索しようとしているが、そのエントリが正確に何であるかがわからない場合に役立ちます。
アスタリスク(*
)は、「すべて」を表すプレースホルダーです。これにより、テーブル内のすべての列がクエリされます。
SELECT * FROM table;
パーセンテージ記号(%
)は、0個以上の不明な文字を表します。
SELECT * FROM table WHERE column LIKE val%;
アンダースコア(_
)は、単一の不明な文字を表すために使用されます。
SELECT * FROM table WHERE column LIKE v_lue;
列のエントリを数える
COUNT
関数は、特定の列のエントリ数を見つけるために使用されます。 次の構文は、column
に保持されている値の総数を返します。
SELECT COUNT(column) FROM table;
次のようにWHERE
句を追加することにより、COUNT
関数の結果を絞り込むことができます。
SELECT COUNT(column) FROM table WHERE column=value;
列の平均値を見つける
AVG
関数は、特定の列に保持されている値の平均(この場合は平均)を見つけるために使用されます。 AVG
関数は、数値を保持する列でのみ機能することに注意してください。 文字列値を保持する列で使用すると、エラーまたは0
が返される場合があります。
SELECT AVG(column) FROM table;
列の値の合計を見つける
SUM
関数は、列に保持されているすべての数値の合計を見つけるために使用されます。
SELECT SUM(column) FROM table;
AVG
関数と同様に、文字列値を保持する列でSUM
関数を実行すると、RDBMSに応じて、エラーまたは0
のみが返される場合があります。
列内の最大値を見つける
列内の最大の数値またはアルファベット順の最後の値を見つけるには、MAX
関数を使用します。
SELECT MAX(column) FROM table;
列内の最小値を見つける
列内の最小の数値またはアルファベット順の最初の値を見つけるには、MIN
関数を使用します。
SELECT MIN(column) FROM table;
ORDERBY句を使用した結果の並べ替え
ORDER BY
句は、クエリ結果を並べ替えるために使用されます。 次のクエリ構文は、column_1
およびcolumn_2
から値を返し、column_1
に保持されている値で昇順、または文字列値の場合はアルファベット順に結果を並べ替えます。
SELECT column_1, column_2 FROM table ORDER BY column_1;
同じアクションを実行しますが、結果をアルファベットの降順または逆順で並べ替えるには、クエリにDESC
を追加します。
SELECT column_1, column_2 FROM table ORDER BY column_1 DESC;
GROUPBY句を使用した結果の並べ替え
GROUP BY
句はORDER BY
句に似ていますが、COUNT
、[X162Xなどの集計関数を含むクエリの結果を並べ替えるために使用されます]、MIN
、またはSUM
。 前のセクションで説明した集計関数自体は、単一の値のみを返します。 ただし、GROUP BY
句を含めることで、列内の一致するすべての値に対して実行された集計関数の結果を表示できます。
次の構文は、column_2
で一致する値の数をカウントし、それらを昇順またはアルファベット順にグループ化します。
SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2;
同じアクションを実行しますが、結果をアルファベットの降順または逆の順序でグループ化するには、クエリにDESC
を追加します。
SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2 DESC;
JOIN句を使用した複数のテーブルのクエリ
JOIN
句は、2つ以上のテーブルの行を結合する結果セットを作成するために使用されます。
SELECT table_1.column_1, table_2.column_2 FROM table_1 JOIN table_2 ON table_1.common_column=table_2.common_column;
これは、INNER JOIN
句の例です。 INNER JOIN
は、両方のテーブルで値が一致するすべてのレコードを返しますが、値が一致しないレコードは表示しません。
external JOIN
句を使用すると、2つのテーブルの一方からすべてのレコードを返すことができます。これには、もう一方のテーブルに対応する一致がない値も含まれます。 外側のJOIN
句は、LEFT JOIN
またはRIGHT JOIN
のいずれかとして記述されます。
LEFT JOIN
句は、「左」テーブルからすべてのレコードを返し、「右」テーブルから一致するレコードのみを返します。 外側のJOIN
句のコンテキストでは、左側のテーブルはFROM
句で参照されるテーブルであり、右側のテーブルはJOIN
ステートメントの後に参照される他のテーブルです。 以下に、table_1
のすべてのレコードと、table_2
の一致する値のみを示します。 table_2
に一致しない値は、結果セットにNULL
として表示されます。
SELECT table_1.column_1, table_2.column_2 FROM table_1 LEFT JOIN table_2 ON table_1.common_column=table_2.common_column;
RIGHT JOIN
句はLEFT JOIN
と同じように機能しますが、右側のテーブルからすべての結果を出力し、左側から一致する値のみを出力します。
SELECT table_1.column_1, table_2.column_2 FROM table_1 RIGHT JOIN table_2 ON table_1.common_column=table_2.common_column;
複数のSELECTステートメントとUNION句の組み合わせ
UNION
演算子は、2つ(またはそれ以上)のSELECT
ステートメントの結果を1つの結果セットに結合するのに役立ちます。
SELECT column_1 FROM table UNION SELECT column_2 FROM table;
さらに、UNION
句は、異なるテーブルをクエリする2つ(またはそれ以上)のSELECT
ステートメントを組み合わせて同じ結果セットにすることができます。
SELECT column FROM table_1 UNION SELECT column FROM table_2;
結論
このガイドでは、データベース、ユーザー、およびテーブルを管理し、それらのテーブルに保持されているコンテンツをクエリするために使用されるSQLの一般的なコマンドのいくつかについて説明します。 ただし、すべてが一意の結果セットを生成する句と演算子の多くの組み合わせがあります。 SQLの操作に関するより包括的なガイドをお探しの場合は、OracleのデータベースSQLリファレンスを確認することをお勧めします。
さらに、このガイドに表示したい一般的なSQLコマンドがある場合は、以下のコメントで質問または提案を行ってください。