SQLにデータを挿入する方法

序章

構造化照会言語は、より一般的には SQL として知られ、データをテーブルに挿入する方法に関して非常に柔軟性があります。たとえば、VALUESキーワードを使用してデータの個々の行を指定したり、SELECTクエリを使用して既存のテーブルからデータのセット全体をコピーしたり、SQLが挿入する方法で列を定義したりできます。それらに自動的にデータ。

このガイドでは、SQLのINSERT INTO構文を使用して、これらの各メソッドでテーブルにデータを追加する方法について説明します。

前提条件

このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム（RDBMS）を実行しているコンピューターが必要です。このガイドの手順と例は、次の環境を使用して検証されています。

Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。このガイドは、ステップ3で説明されているプロセスを使用して作成された非ルートMySQLユーザーで検証されました。

注：多くのRDBMSは、独自のSQL実装を使用していることに注意してください。このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。

また、データの挿入を練習できるデータベースとテーブルも必要です。これらがない場合は、次の MySQLへの接続とサンプルデータベースのセットアップセクションを読んで、このガイドで例全体で使用するデータベースとテーブルを作成する方法の詳細を確認してください。

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

インタラクティブターミナルを起動します！

MySQLへの接続とサンプルデータベースの設定

SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。

ssh sammy@your_server_ip

次に、MySQLサーバープロンプトを開き、sammyをMySQLユーザーアカウントの名前に置き換えます。このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

mysql -u sammy -p

insertDBという名前のデータベースを作成します。

CREATE DATABASE insertDB;

データベースが正常に作成されると、次のような出力が表示されます。

OutputQuery OK, 1 row affected (0.01 sec)

insertDBデータベースを選択するには、次のUSEステートメントを実行します。

USE insertDB;

OutputDatabase changed

insertDBデータベースを選択した後、そのデータベース内にテーブルを作成します。例として、あなたが工場を所有していて、従業員に関する情報を格納するためのテーブルを作成したいとします。このテーブルには、次の5つの列があります。

name：最大30文字のvarcharデータ型を使用して表された各従業員の名前
position：この列には、各従業員の役職が格納されます。これも、最大30文字のvarcharデータ型を使用して表されます。
department：各従業員が勤務する部門。varcharデータ型を使用して表現されますが、最大20文字です。
hourlyWage：各従業員の時給を記録する列。decimalデータ型を使用し、この列の値は最大4桁に制限され、そのうち2桁は右側にあります。小数点の。したがって、この列で許可される値の範囲は、-99.99から99.99になります。
startDate：各従業員が雇用された日付。dateデータ型を使用して表されます。このタイプの値は、YYYY-MM-DDの形式に準拠している必要があります

次の5つの列を持つfactoryEmployeesという名前のテーブルを作成します。

CREATE TABLE factoryEmployees (
name varchar(30),
position varchar(30),
department varchar(20),
hourlyWage decimal(4,2),
startDate date
);

これで、ガイドの残りの部分に従い、SQLを使用してデータを挿入する方法について学習を開始する準備が整いました。

データを手動で挿入する

SQLにデータを挿入するための一般的な構文は次のようになります。

INSERT INTO table_name
(column1, column2, . . . columnN)
VALUES
(value1, value2, . . . valueN);

説明のために、次のINSERT INTOステートメントを実行して、factoryEmployeesテーブルに単一行のデータをロードします。

INSERT INTO factoryEmployees
(name, position, department, hourlyWage, startDate)
VALUES
('Agnes', 'thingamajig foreman', 'management', 26.50, '2017-05-01');

OutputQuery OK, 1 row affected (0.00 sec)

このステートメントは、INSERT INTOキーワードで始まり、その後にデータを挿入するテーブルの名前が続きます。テーブル名の後には、ステートメントがデータを追加する列のリストが括弧で囲まれています。列リストの後にはVALUESキーワードがあり、次に括弧で囲まれ、コンマで区切られた値のセットがあります。

列をリストする順序は重要ではありません。指定する値の順序は列の順序と一致することを覚えておくことが重要です。 SQLは常に、リストされている最初の列に指定された最初の値を挿入し、次の列に2番目の値を挿入しようとします。説明のために、次のINSERTステートメントはデータの別の行を追加しますが、列を異なる順序でリストします。

INSERT INTO factoryEmployees
(department, hourlyWage, startDate, name, position)
VALUES
('production', 15.59, '2018-04-28', 'Jim', 'widget tightener');

OutputQuery OK, 1 row affected (0.00 sec)

値を正しく配置しないと、SQLがデータを間違った列に入力する可能性があります。さらに、次の例のように、値のいずれかが列のデータ型と競合する場合、エラーが発生します。

INSERT INTO factoryEmployees
(name, hourlyWage, position, startDate, department)
VALUES
('Louise', 'doodad tester', 16.50, '2017-05-01', 'quality assurance');

OutputERROR 1366 (HY000): Incorrect decimal value: 'doodad tester' for column 'hourlyWage' at row 1

指定するすべての列に値を指定する必要がありますが、データの新しい行を追加するときに、必ずしもテーブル内のすべての列を指定する必要はないことに注意してください。省略した列のいずれにも、この場合にエラーを引き起こす制約（NOT NULLなど）がない限り、MySQLはNULLを指定されていない列に追加します。

INSERT INTO factoryEmployees
(name, position, hourlyWage)
VALUES
('Harry', 'whatzit engineer', 26.50);

OutputQuery OK, 1 row affected (0.01 sec)

テーブル内のすべての列の値を含む行を入力する場合は、列名を含める必要はありません。入力する値は、テーブルの定義で列が定義された順序と一致している必要があることに注意してください。

この例では、リストされている値は、factoryEmployeeテーブルのCREATE TABLEステートメントで列が定義された順序と一致しています。

INSERT INTO factoryEmployees
VALUES
('Marie', 'doodad welder', 'production', 27.88, '2018-03-29');

OutputQuery OK, 1 row affected (0.00 sec)

次のように、各行をコンマで区切ることにより、一度に複数のレコードを追加することもできます。

INSERT INTO factoryEmployees
VALUES
('Giles', 'gizmo inspector', 'quality assurance', 26.50, '2019-08-06'),
('Daphne', 'gizmo presser', 'production', 32.45, '2017-11-12'),
('Joan', 'whatzit analyst', 'quality assurance', 29.00, '2017-04-29');

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

`SELECT`ステートメントを使用したデータのコピー

データを行ごとに指定するのではなく、SELECTクエリを使用して、1つのテーブルから複数のデータ行をコピーし、それらを別のテーブルに挿入できます。

この種の操作の構文は次のようになります。

INSERT INTO table_A (col_A1, col_A2, col_A3)
SELECT col_B1, col_B2, col_B3
FROM table_B;

VALUESキーワードで列リストをフォローする代わりに、この構文例ではSELECTステートメントをフォローします。この構文例のSELECTステートメントには、FROM句のみが含まれていますが、任意の有効なクエリが機能します。

説明のために、次のCREATE TABLE操作を実行して、showroomEmployeesという名前の新しいテーブルを作成します。このテーブルの列は、前のセクションで使用したfactoryEmployeesテーブルの3つの列と同じ名前とデータ型であることに注意してください。

CREATE TABLE showroomEmployees (
name varchar(30),
hourlyWage decimal(4,2),
startDate date
);

OutputQuery OK, 0 rows affected (0.02 sec)

これで、INSERT INTOステートメントにSELECTクエリを含めることで、以前に作成したfactoryEmployeesテーブルのデータをこの新しいテーブルにロードできます。

SELECTクエリがターゲットテーブルの列と同じ順序で同じ数の列を返し、それらに互換性のある一致するデータ型がある場合は、INSERT INTOステートメントから列リストを省略できます。

INSERT INTO showroomEmployees
SELECT
factoryEmployees.name,
factoryEmployees.hourlyWage,
factoryEmployees.startDate
FROM factoryEmployees
WHERE name = 'Agnes';

OutputQuery OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

注：この操作のSELECTクエリにリストされている列の前には、それぞれテーブル名factoryEmployeesとピリオドが付いています。このような列を参照するときにテーブル名を指定すると、完全修飾列参照と呼ばれます。この特定のケースでは、これは必要ありません。実際、次の例のINSERT INTOステートメントは、前のステートメントと同じ結果を生成します。

INSERT INTO showroomEmployees
SELECT
name,
hourlyWage,
startDate
FROM factoryEmployees
WHERE name = 'Agnes';

このセクションの例では、わかりやすくするために完全に修飾された列参照を使用していますが、そうすることは練習するのに良い習慣です。 SQLの理解とトラブルシューティングを容易にするだけでなく、JOIN句を含むクエリなど、複数のテーブルを参照する特定の操作では、完全修飾列参照が必要になります。

この操作のSELECTステートメントには、WHERE句が含まれています。これにより、クエリは、name列に値Agnes。ソーステーブルにはそのような行が1つしかないため、その行だけがshowroomEmployeesテーブルにコピーされます。

これを確認するには、次のクエリを実行して、showroomEmployeesテーブルのすべてのレコードを返します。

SELECT * FROM showroomEmployees;

Output+-------+------------+------------+
| name  | hourlyWage | startDate  |
+-------+------------+------------+
| Agnes |      26.50 | 2017-05-01 |
+-------+------------+------------+
1 row in set (0.00 sec)

ソーステーブルから複数の行を返すクエリを使用して、複数行のデータを挿入できます。たとえば、次のステートメントのクエリは、name列の値がJで始まらないfactoryEmployeesデータベースのすべてのレコードを返します。

INSERT INTO showroomEmployees
SELECT
factoryEmployees.name,
factoryEmployees.hourlyWage,
factoryEmployees.startDate
FROM factoryEmployees
WHERE name NOT LIKE 'J%';

OutputQuery OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

このクエリを再度実行して、showroomEmployeesテーブルのすべてのレコードを返します。

SELECT * FROM showroomEmployees;

+--------+------------+------------+
| name   | hourlyWage | startDate  |
+--------+------------+------------+
| Agnes  |      26.50 | 2017-05-01 |
| Agnes  |      26.50 | 2017-05-01 |
| Harry  |      26.50 | NULL       |
| Marie  |      27.88 | 2018-03-29 |
| Giles  |      26.50 | 2019-08-06 |
| Daphne |      32.45 | 2017-11-12 |
+--------+------------+------------+
6 rows in set (0.00 sec)

名前の列にAgnesが含まれる2つの同一の行があることに注意してください。 SELECTを使用するINSERT INTOステートメントを実行するたびに、SQLはクエリの結果を新しいデータセットとして扱います。テーブルに特定の制約を課したり、より詳細なクエリを開発したりしない限り、このようなデータを追加するときにデータベースに重複レコードが読み込まれるのを防ぐことはできません。

データを自動的に挿入する

テーブルを作成するときに、特定の属性を列に適用して、RDBMSが列にデータを自動的に入力するようにすることができます。

説明のために、次のステートメントを実行して、internsという名前のテーブルを定義します。これにより、3つの列を持つinternsという名前のテーブルが作成されます。この例の最初の列internIDは、intタイプのデータを保持します。ただし、AUTO_INCREMENT属性も含まれていることに注意してください。この属性により、SQLは、デフォルトで1で始まり、後続のレコードごとに1ずつ増加して、新しい行ごとに一意の数値を自動的に生成します。

同様に、2番目の列departmentには、DEFAULTキーワードが含まれています。これにより、INSERT INTOステートメントの列リストからdepartmentを省略すると、RDBMSはデフォルト値（この例では'production'）を自動的に挿入します。

CREATE TABLE interns (
internID int AUTO_INCREMENT PRIMARY KEY,
department varchar(20) DEFAULT 'production',
name varchar(30)
);

注：AUTO_INCREMENT属性はMySQLに固有の機能ですが、多くのRDBMSには、整数をインクリメントするための独自のメソッドがあります。 RDBMSが自動インクリメントを管理する方法をよりよく理解するには、RDBMSの公式ドキュメントを参照する必要があります。

いくつかの人気のあるオープンソースデータベースの主題に関する公式ドキュメントは次のとおりです。

これらの機能を示すために、次のINSERT INTOステートメントを実行して、internsテーブルにデータをロードします。この操作は、name列の値のみを指定します。

INSERT INTO interns (name) VALUES ('Pierre'), ('Sheila'), ('Francois');

OutputQuery OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

次に、このクエリを実行して、テーブルからすべてのレコードを返します。

SELECT * FROM interns;

Output+----------+------------+----------+
| internID | department | name     |
+----------+------------+----------+
|        1 | production | Pierre   |
|        2 | production | Sheila   |
|        3 | production | Francois |
+----------+------------+----------+
3 rows in set (0.00 sec)

この出力は、列の定義により、前のINSERT INTOステートメントが、指定されていなくてもinternIDとdepartmentの両方に値を追加したことを示しています。

department列にデフォルト以外の値を追加するには、次のようにINSERT INTOステートメントでその列を指定する必要があります。

INSERT INTO interns (name, department)
VALUES
('Jacques', 'management'),
('Max', 'quality assurance'),
('Edith', 'management'),
('Daniel', DEFAULT);

OutputQuery OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

この例で提供されている値の最後の行には、文字列値の代わりにDEFAULTキーワードが含まれていることに注意してください。これにより、データベースはデフォルト値（'production'）を挿入します。

SELECT * FROM interns;

Output+----------+-------------------+----------+
| internID | department        | name     |
+----------+-------------------+----------+
|        1 | production        | Pierre   |
|        2 | production        | Sheila   |
|        3 | production        | Francois |
|        4 | management        | Jacques  |
|        5 | quality assurance | Max      |
|        6 | management        | Edith    |
|        7 | production        | Daniel   |
+----------+-------------------+----------+
7 rows in set (0.00 sec)

結論

このガイドを読むことで、VALUESキーワードを使用してデータの個々の行を指定する、SELECTクエリを使用してデータのセット全体をコピーする、列を定義するなど、テーブルにデータを挿入するいくつかの異なる方法を学びました。どのSQLがデータを自動的に挿入するか。

ここで概説するコマンドは、SQLを使用するすべてのデータベース管理システムで機能するはずです。すべてのSQLデータベースは独自の言語実装を使用しているため、INSERT INTOステートメントの処理方法と使用可能なオプションの詳細については、DBMSの公式ドキュメントを参照してください。

SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。