SQLでテーブルを作成および管理する方法
序章
テーブルは、SQLデータベースの主要な組織構造です。 これらは、テーブル内の各行またはレコードの個々の属性を反映するいくつかの列で構成されます。 データ編成のこのような基本的な側面であるため、リレーショナルデータベースを使用するすべての人が、必要に応じてテーブルを作成、変更、および削除する方法を理解することが重要です。
このガイドでは、SQLでテーブルを作成する方法と、既存のテーブルを変更および削除する方法について説明します。
前提条件
このガイドに従うには、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
tablesDB
という名前のデータベースを作成します。
- CREATE DATABASE tablesDB;
データベースが正常に作成されると、次のような出力が表示されます。
OutputQuery OK, 1 row affected (0.01 sec)
tablesDB
データベースを選択するには、次のUSE
ステートメントを実行します。
- USE tablesDB;
OutputDatabase changed
これで、ガイドの残りの部分に従い、SQLでテーブルを作成および管理する方法について学習を開始する準備が整いました。
テーブルの作成
SQLでテーブルを作成するには、CREATE TABLE
コマンドを使用し、その後にテーブルの名前を入力します。
- CREATE TABLE table_name;
すべてのSQLステートメントと同様に、CREATE TABLE
ステートメントはセミコロン(;
)で終了する必要があることに注意してください。
この構文例では、列のない空のテーブルが作成されます。 列を含むテーブルを作成するには、テーブル名の後に、列名とそれに対応するデータ型および制約のリストを、括弧で囲み、コンマで区切って続けます。
- CREATE TABLE table_name (
- column1_name column1_data_type,
- column2_name column2_data_type,
- . . .
- columnN_name columnN_data_type
- );
例として、ニューヨーク市のお気に入りの公園に関する情報を記録するためのテーブルを作成したいとします。 各公園について記録する属性を決定したら、次に、それらの各属性の列名と、各属性の適切なデータ型を決定します。
parkName
:各公園の名前。 パーク名の長さには大きなばらつきがあるため、最大長が30
文字のvarchar
データ型が適切です。yearBuilt
:公園が建設された年。 MySQLにはyear
データ型がありますが、これは1901
から2155
までの値のみを許可します。 ニューヨーク市には1901年より前に建設された公園がいくつかあるため、代わりにint
データ型を使用できます。firstVisit
:各公園への最初の訪問日。 MySQLには、この列に使用できるdate
データ型があります。YYYY-MM-DD
の形式でデータを保存します。lastVisit
:各公園への最後の訪問の日付。 この場合も、date
タイプを使用できます。
これらの名前とデータ型を持つ列を持つfaveParks
という名前のテーブルを作成するには、次のコマンドを実行します。
- CREATE TABLE faveParks (
- parkName varchar(30),
- yearBuilt int,
- firstVisit date,
- lastVisit date
- );
OutputQuery OK, 0 rows affected (0.01 sec)
テーブルにデータを追加していないため、これはテーブルの構造のみを作成することに注意してください。
CREATE TABLE AS
構文を使用して、既存のテーブルから新しいテーブルを作成することもできます。
- CREATE TABLE new_table_name AS (
- SELECT column1, column2, . . . columnN
- FROM old_table_name
- );
新しいテーブルの名前の後に列とそのデータ型のリストを続ける代わりに、AS
を続けてから、括弧内にSELECT
ステートメントを続けて元の列とデータを返します。新しいテーブルにコピーするテーブル。
元のテーブルの列にデータが含まれている場合、そのすべてのデータも新しいテーブルにコピーされることに注意してください。 また、わかりやすくするために、この構文例には、必要なFROM
句のみを持つSELECT
クエリが含まれています。 ただし、有効なSELECT
ステートメントはこの場所で機能します。
説明のために、次のコマンドは、前に作成したfaveParks
テーブルの2つの列からparkInfo
という名前のテーブルを作成します。
- CREATE TABLE parkInfo AS (
- SELECT parkName, yearBuilt
- FROM faveParks
- );
OutputQuery OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
faveParks
テーブルにデータが含まれている場合、そのparkName
列とyearBuilt
列のデータもparkInfo
テーブルにコピーされますが、この場合、両方のテーブルは空になります。
既存のテーブルの名前を使用してテーブルを作成しようとすると、エラーが発生します。
- CREATE TABLE parkInfo (
- name varchar(30),
- squareFootage int,
- designer varchar(30)
- );
OutputERROR 1050 (42S01): Table 'parkInfo' already exists
このエラーを回避するには、CREATE TABLE
コマンドにIF NOT EXISTS
オプションを含めることができます。 これにより、指定された名前のデータベースがすでに存在するかどうかを確認し、存在する場合はエラーではなく警告を発行するようにデータベースに指示します。
- CREATE TABLE IF NOT EXISTS parkInfo (
- name varchar(30),
- squareFootage int,
- designer varchar(30)
- );
OutputQuery OK, 0 rows affected, 1 warning (0.00 sec)
parkInfo
という名前のテーブルがまだ存在するため、このコマンドは新しいテーブルの作成に失敗します。 ただし、この出力は、CREATE TABLE
ステートメントが警告につながったことを示していることに注意してください。 警告メッセージを表示するには、診断SHOW WARNINGS
ステートメントを実行します。
- SHOW WARNINGS;
Output| Level | Code | Message |
+-------+------+---------------------------------+
| Note | 1050 | Table 'parkInfo' already exists |
+-------+------+---------------------------------+
1 row in set (0.00 sec)
この出力が示すように、IF NOT EXISTS
オプションが含まれているため、以前に受け取ったのと同じエラーが警告として登録されています。 これは、トランザクションを実行する場合など、特定の場合に役立ちます。 エラーが発生するとトランザクション全体が失敗しますが、警告はそれを引き起こしたステートメントのみが失敗することを意味します。
テーブルの変更
テーブルの定義を変更する必要がある場合があります。 これは、テーブル内のデータを更新することとは異なります。 代わりに、テーブル自体の構造を変更する必要があります。 これを行うには、ALTER TABLE
構文を使用します。
- ALTER TABLE table_name ALTER_OPTION sub_options . . . ;
ALTER TABLE
ステートメントを開始した後、変更するテーブルの名前を指定します。 次に、RDBMSで使用可能なオプションを渡して、考えている変更を実行します。
たとえば、テーブルの名前を変更したり、新しい列を追加したり、古い列を削除したり、列の定義を変更したりできます。 テーブルの作成セクションで以前に作成したfaveParks
テーブルで、これらの例を練習するために読み続けることができます。
faveParks
テーブルの名前を変更するには、RENAME TO
構文を使用できます。 この例では、faveParks
テーブルの名前をfaveNYCParks
に変更します。
警告:テーブルの名前を変更するときは注意してください。 これを行うと、アプリケーションがテーブルを使用する場合、またはデータベース内の他のテーブルがそれを参照する場合に問題が発生する可能性があります。
- ALTER TABLE faveParks RENAME TO faveNYCParks;
OutputQuery OK, 0 rows affected (0.01 sec)
新しい列を追加するには、ADD COLUMN
オプションを渡します。 次の例では、borough
という名前の列を追加します。この列は、varchar
タイプのデータを保持しますが、最大長は20
文字です。faveNYCParks
テーブルに:
- ALTER TABLE faveNYCParks ADD COLUMN borough varchar(20);
OutputQuery OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
列とその列が保持するデータをテーブルから削除するには、DROP TABLE
構文を使用できます。 このコマンド例では、borough
列を削除します。
- ALTER TABLE faveNYCParks DROP COLUMN borough;
多くのSQL実装では、ALTER TABLE
を使用して列の定義を変更できます。 次の例では、MySQLのMODIFY COLUMN
句を使用し、yearBuilt
列を変更して元のint
タイプではなくsmallint
データ型を使用します。
- ALTER TABLE faveNYCParks MODIFY COLUMN yearBuilt smallint;
すべてのRDBMSには、ALTER TABLE
ステートメントで変更できるオプションが異なることに注意してください。 ALTER TABLE
でできることの全範囲を理解するには、RDBMSの公式ドキュメントを参照して、ALTER TABLE
オプションで利用できるものを確認する必要があります。
いくつかの人気のあるオープンソースデータベースの主題に関する公式ドキュメントは次のとおりです。
テーブルの削除
テーブルとそのすべてのデータを削除するには、DROP TABLE
構文を使用します。
警告:DROP TABLE
コマンドを実行すると、テーブルとそのすべてのデータが完全に削除されるため、注意してください。
- DROP TABLE table_name;
次のように、名前をコンマとスペースで区切ることにより、単一のDROP
ステートメントで複数のテーブルを削除できます。
- DROP TABLE table1, table2, table3;
説明のために、次のコマンドは、このガイドで前に作成したfaveNYCParks
およびparkInfo
テーブルを削除します。
- DROP TABLE IF EXISTS faveNYCParks, parkInfo;
この例にはIF EXISTS
オプションが含まれていることに注意してください。 これは、CREATE TABLE
で使用可能なIF NOT EXISTS
オプションとは逆の機能を備えています。 このコンテキストでは、IF EXISTS
により、指定されたテーブルの1つが存在しない場合、DROP TABLE
ステートメントはエラーメッセージではなく警告を返します。
結論
このガイドを読むことで、SQLベースのデータベースでテーブルを作成、変更、および削除する方法を学びました。 ここで概説するコマンドは、SQLを使用するすべてのデータベース管理システムで機能するはずです。 すべてのSQLデータベースは独自の言語実装を使用しているため、各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。
SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。