SQLでテーブルを作成および管理する方法

投稿日: 2022-04-20 2022-04-20
タグ: Databases, Interactive, 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の使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。

getdocs

13036