SQLによるMySQLの基本操作

本章ではMySQLの基本操作と実際にデータベースにデータを登録するのに必要な準備段階について解説します。
本章の目標
  • クライアントからのMySQL接続方法をおぼえる
  • ユーザーの作成/削除の方法をおぼえる
  • データベースの作成方法をおぼえる
  • テーブルの概念を理解する
  • テーブルの作成方法をおぼえる
ページの先頭へ
ページの先頭へ

1.MySQLへの接続とユーザーの作成

実際にMySQLを操作してみましょう。まずはMySQLの起動方法、mysqlクライアントによるMySQLデータベースへの接続・ユーザーの作成について学習します。

1. MySQLの起動

デスクトップ上の「xampp-controlショートカット」をダブルクリックし、xamppのコントロールパネルを起動します。


xamppコントロールパネルのMySQLを開始します。



                  



2. MySQLデータベースへの接続

デスクトップ上の「MySQL(コマンドプロンプト)」をダブルクリックして、コマンドプロンプトを起動します。






次のコマンドを入力し、「Enter」キーを押します。

mysql -u root -p



パスワードの入力を促されますので、「root123」と入力します。


下の画面が出れば、MySQLへの接続は完了です。


3. ユーザーの作成

ここまでは「root」ユーザーでデータベースに接続しましたが、「root」ユーザーは本来管理者が使うIDですので、
通常の作業をする場合に「root」ユーザーで行うのはあまり好ましくありません。
そこで、新しくユーザーを作成してみましょう。
MySQLでユーザーを新規作成するには、「GRANT」コマンドを使用します。
下記が「GRANT」コマンドの書式です。

GRANT ALL PRIVILEGES ON データベース名.* TO ユーザー名@ホスト名
IDENTIFIED BY 'パスワード';

たとえば、データベース名が「test」、追加するユーザー名が「testuser」、ホスト名が「localhost」、
パスワードが「kanda123」だとすると、

GRANT ALL PRIVILEGES ON test.* TO testuser@localhost IDENTIFIED BY 'kanda123';

となります。

ホスト名とは、データベースにアクセスするコンピュータの名前を表すものです。今回の例では「localhost」を指定していますので、MySQLデータベースサーバーと同じコンピュータからアクセスすることを意味します。





ユーザーが正常に作成出来ているか確認してみましょう。一度ログアウトし、先程作成したユーザーで、MySQLに再接続してみます。ログアウトするには、「QUIT」コマンドを用います。

QUIT;




先程作成したユーザーでログインします。

mysql -u testuser -p






                                     





4. ユーザーの削除

作成されたユーザーは、「MySQL」データベースの「user」テーブルに保存されています。
「user」テーブルを参照して見ましょう。
まずは一旦ログアウトし、「root」ユーザーでMySQLに接続しなおしてください。
その後、下記のSQLコマンドを入力してみてください。

USE mysql;
SELECT user FROM user;





ユーザーを削除するには、「user」テーブルからユーザーのデータを削除します。
下記のSQLコマンドを入力し、データを削除してください。

DELETE FROM user WHERE user='testuser' AND host='localhost';





実際に削除されているか確認してみましょう。

SELECT user FROM user;




「testuser」が存在していません。

ページの先頭へ

2. 新しいデータベースの作成

初期状態ではmysqlデータベース、information_schemaデータベース、testデータベースの3つがあるというお話をしましたが、 ここでは実際にこれから自分が使うデータベースを作成してみましょう。

1. データベースの作成

データベースを作成するには、「CREATE DATABASE」文を使用します。
書式は下記の通りです。

CREATE DATABASE データベース名;

ここでは、「mybookdb」という名前のデータベースを作成してみましょう。

CREATE DATABASE mybookdb;



実際にデータベースが作成されているかを確認してみます。
現在あるデータベースを確認するには「SHOW DATABASES」文を使用します。

SHOW DATABASES;


先程作成した「mybookdb」が作成されています。


「mybookdb」データベースを使用するユーザーを作成してください。
ユーザー名は「bms」、パスワードは「bms123」とします。
作成し終えたら、「bms」ユーザーでログインし直してください。

GRANT ALL PRIVILEGES ON mybookdb.* TO bms@localhost IDENTIFIED BY 'bms123'

QUIT;





データベースを削除するには、「DROP DATABASE」文を使用します。

DROP DATABASE データベース名;

例えば先程作成した「mybookdb」を削除する場合は、

DROP DATABASE mybookdb;

となります。
但し、一度データベースを削除してしまうと、そのデータベースの中に作成したテーブル・ビュー・インデックス削除され、元に戻せませんので注意が必要です。
※ここではデータベースの削除は行いません


2.データベースの選択

作成したデータベースで作業するには、使用するデータベースを選択する必要があります。
データベースを選択するには、「USE」コマンドを使用します。
書式は下記の通りです。

USE データベース名;

「mybookdb」データベースを選択する場合は、

USE mybookdb;

となります。




正常に選択された場合、「Database changed」と表示されます。

ページの先頭へ

3. テーブルの理解とテーブル作成

ここでは、データを扱う基本的な単位であるテーブルについて学んでいきます。

1. テーブルとは

テーブルとは、実際にデータを格納する入れ物です、Excelに例えると、ワークブックがデータベースだとすると、ワークシートがテーブルに当たります。
Excelでもワークシートがないとデータを入力することができないのと同じで、実際にデータを格納するには、まずテーブルを作成しておく必要があります。





2.テーブル作成の準備

テーブルを作成するには、事前に決めておかなければならない項目があります。
1.テーブル名
テーブルの名前です。日本語も使えますが、アルファベットと「_」(アンダースコア)を組み合わせて命名するのが一般的です。
2.列名
データを格納する各列の名前です。テーブル名同様、日本語も使えますが、アルファベットと「_」(アンダースコア)を組み合わせて命名するのが一般的です。
3.列のデータ型
テーブルがExcelと違う点は、各列に格納するデータの型を、あらかじめ決めておき、一度決めたらそれ以外の型のデータは入れられないということです。 例えばExcelでは、同じ列に数値も文字も日付も入力することができますが、テーブルは数値型に設定した列には、文字のデータは入力できません。
MySQL使用できる列の型のうち、よく使われるものには次のようなものがあります。

* 整数型
データの範囲
INT -2147483648から2147483647 (符号無しの場合0から4294967295)

* 文字列型
データの範囲
CHAR 0から255文字
VARCHAR 0から65535バイト

CHAR型は指定した文字数以下の文字を格納した場合には文字列の末尾に空白を必要なだけ付け加えて指定の長さの文字列として格納します。ただし値を取得する場合は末尾にある空白は全て削除された上で取得されますし比較される時も同様です。
それに対してVARCHAR型は末尾に空白を付けるようなことはしません。また現行のバージョンでは末尾に空白がある文字列であっても空白が付いたまま格納されます。取得する時も空白付きで取得しますが、WHERE句で比較する時だけ末尾の空白を削除した上で比較されます。

* 日付時刻型
データの範囲
DATE '1000-01-01' から '9999-12-31'
DATETIME '1000-01-01 00:00:00' から '9999-12-31 23:59:59'


MySQLでは、上記の型の他にも、次のような型が扱えます。

* 整数型
データの範囲
TINYINT -128から127 (符号無しの場合0から255)
SMALLINT -32768から32767 (符号無しの場合0から65535)
MEDIUMINT -8388608から8388607 (符号無しの場合0から16777215)
BIGINT -9223372036854775808から9223372036854775807

* 浮動小数点型
データの範囲
FLOAT -3.402823466E+38 から -1.175494351E-38
0
1.175494351E-38 から 3.402823466E+38
DOUBLE -1.7976931348623157E+308 から -2.2250738585072014E-308
0
2.2250738585072014E-308 から 1.7976931348623157E+308

FLOAT型とDOUBLE型は精度と小数点以下の桁数を指定することができます。書式は次の通りです。
DOUBLE(桁数, 小数点以下の桁数)
桁数は全体の桁数で、小数点以下の桁数は桁数の中で小数点以下の桁数です。 例えば全体が5桁で小数点以下の桁数が3桁のものはdouble(5, 3)と記述し、34.567のように表示されます。

* 日付時刻型
データの範囲
TIMESTAMP '1970-01-01 00:00:01' から '2037-12-31 23:59:59'
TIME '-838:59:59' から '838:59:59'
YEAR 1901 から 2155、そして 0000

* BINARY型とVARBINARY型
データの範囲
BINARY 0から255文字
VARBINARY 0から65535バイト

BINARY型とVARBINARY型はデータをバイナリデータとして格納します。 どちらのデータ型も文字セットなどはなく、送られてきたデータをそのままバイナリデータとして格納します。

* BLOB型
データの範囲
TINYBLOB 最長255 (2の8乗 - 1) バイト
BLOB 最長65,535 (2の16乗 - 1) バイト
MEDIUMBLOB 最長65,535 (2の16乗 - 1) バイト
LONGBLOB 最長4,294,967,295、または4GB (2の32乗 - 1) バイト

BLOB型はバイナリデータを扱うデータ型で格納できるデータのサイズを指定しません。
巨大なバイナリデータを格納する時に利用されます。

* TEXT
データの範囲
TINYTEXT 最長255 (2の8乗 - 1) バイト
TEXT 最長65,535 (2の16乗 - 1) バイト
MEDIUMTEXT 最長65,535 (2の16乗 - 1) バイト
LONGTEXT 最長4,294,967,295、または4GB (2の32乗 - 1) バイト

TEXT型は文字列データを扱うデータ型で格納できるデータのサイズを指定しません。
TEXT型は巨大なテキストデータを格納する時に利用されます。


4.列の属性
列の属性は必須ではありませんが、実際の開発現場では列に属性を付けることがほとんどです。 列の属性にはいくつかの種類がありますが、一番重要なのが主キー(プライマリキー)です。
では、主キーとはなんでしょうか?
主キーとは、そのテーブルの中で1つのデータを特定する列の属性のことです。
下の例を見てみましょう。
社員の情報を格納するテーブルを作成した場合、姓名だけではデータを特定することができない場合があります。


そこで、社員番号のような値が重複しないユニークな列があれば、社員のデータを特定できます。


この場合、社員番号の列が主キーになります。

ポイント:主キーのルール
①重複する値を取らない
②空の状態は許されない


主キーはリレーショナルデータベースでは大変重要な概念です。
テーブルには必ず主キーを設定するようにしましょう。


主キーは1つの列とは限りません。2つ以上の列を組み合わせてデータを一意にする場合、複数の列を主キーとして設定できます。 このような主キーを複合主キー(連結キー)と言います。
例えば先程の社員テーブルの場合、グループ内の企業で同じ社員番号体系を持っていると、 グループ全体では社員番号だけでデータを特定できないことになります。



このような場合、表に会社コードを追加することで、データを一意に特定できます。


このとき、会社コードと社員番号の組み合わせで主キーとします。


列の属性は、主キー以外にも下記のようなものがあります。

属性 意味
NOT NULL 空のデータを許さない
UNIQUE データの重複を許さない
DEFAULT データが未指定の場合、指定した値を自動的に入力する
AUTO_INCREMENT 自動的に連番を入力する


お気づきかもしれませんが、「主キー属性」は「NOT NULL」属性と「UNIQUE」属性を併せ持つことになります

3.テーブルの作成

それでは実際にテーブルを作っていきましょう。
テーブルは、「mybookdb」に作成していきます。「bms」ユーザーでログインし、「USE」文で「mybookdb」を選択してください。

USE mybookdb;





これから下表のテーブルを作成していきます。

テーブル名:「bookinfo」
項目 データのタイプ 列名 データ型 属性
ISBN番号 文字列(最大半角20文字/全角10文字) isbn VARCHAR(20) PRIMARY KEY
タイトル 文字列(最大半角100文字/全角50文字) title VARCHAR(100)
価格 数値(整数) price INT NOT NULL


テーブルを作成するには、「CREATE TABLE」文を使用します。
「CREATE TABLE」文の構文は次の通りです。

CREATE TABLE テーブル名 ( 列名 データ型 属性, 列名 データ型 属性, 列名 データ型 属性, ・・・・ );


「bookinfo」テーブルを作成する場合は次のようになります。

CREATE TABLE bookinfo ( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(100), price INTEGER NOT NULL );

属性を設定しない列は、属性の記述を省けます。
それでは実際に作成してみましょう。




テーブルが作成されているか確認してみましょう。
現在あるテーブルの確認には「SHOW TABLES」文を使用します。

SHOW TABLES;





「bookinfo」テーブルが作成されているのが確認できます。
さらに作成したテーブルの詳細を確認するには、「SHOW FIELDS」文を使用します。
構文は下記の通りです。

SHOW FIELDS FROM テーブル名;

では「bookinfo」テーブルの詳細を確認してみましょう。

SHOW FIELDS FROM bookinfo;





「Field」は列名を表します。
「Type」はデータのタイプを表します。
「Null」は NULL 値がカラムの中に格納する事ができるかどうかを表します。
「Key」は列の制約を表します。「PRI」の値は、列がそのテーブルの主キー属性が設定されていることを指します。
「Default」は、カラムに割り当てられたデフォルト値を指示します。
「Extra」は列についてその他の情報を表します。例えば「AUTO_INCREMENT」属性が指定されていた場合、ここに表示されます。


テーブルを削除するには、「DROP TABLE」文を使用します。

DROP TABLE データベース名;

例えば先程作成した「mybookdb」を削除する場合は、

DROP TABLE bookinfo;

となります。



但し、一度テーブルを削除してしまうと、元に戻せませんので注意が必要です。
※ここではテーブルの削除は行いません


複合主キーを設定する場合、単一の主キーを設定する場合と「CREATE TABLE文」の書式が異なります。
例えば仮に「bookinfo」の主キーをISBN番号とタイトルに設定する場合、下記のような「CREATE TABLE」文を発行するとエラーになります。

CREATE TABLE bookinfo (
isbn VARCHAR(20) PRIMARY KEY,
title VARCHAR(100) PRIMARY KEY,
price INTEGER NOT NULL
)
;




複数主キーを設定するには、下記のような構文を使います。

CREATE TABLE bookinfo (
isbn VARCHAR(20),
title VARCHAR(100),
price INTEGER NOT NULL,
PRIMARY KEY(isbn,title)
)
;

オプションとして「PRIMARY KEY(列名1,列名2,・・・)」とする訳です。



ページの先頭へ

4. 本章のまとめ

本章ではデータベース、MySQLへの接続方法、ユーザーの作成、データベースの作成、テーブルの作成ついて実習を交えて説明しました。
これらは実際にデータベースを構築する際にどれも欠かせないステップになりますので、しっかりとマスターしてください。
特にテーブルの基本概念は、将来的にデータベース設計をする上での基礎知識ともなりますので、是非とも理解しておいてください。
ページの先頭へ