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の使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。