データベースの世界観に戻る

MySQLの世界観（A．基本）

MySQLによるデータベース操作に関する世界観（A．基本）です。

関連ページ

MySQLとSQL文の基本

「いちばんやさしいPHPの教本 人気講師が教える実践Webプログラミング (「いちばんやさしい教本」シリーズ)」を参考に執筆しました。

以下はSQL文の例。

データベースの設計

まず、データベースを作成します。データベースの名前は「db1」とします。照合順序（データの検索する際の並び順を決める）には一般的な「utf8_general_ci」を指定します。

このデータベースの中に、テーブルと呼ばれる個々のデータの保管場所を作ります。

テーブルは、カラム（列）とレコード（行）から成り立ちます。まず、カラムを入念に設計し、テーブルを作成して、その中にレコードを追加していきます。

以下はカラムの例。テーブルの名前は「bbs」にします。

役割 カラム名 型 長さ A_I
通し番号 id INT 11 あり
名前 name VARCHAR 45 -
メールアドレス email VARCHAR 45 -
投稿日 time DATETIME 8 -
テキスト text VARCHAR 300 -
いいねの数 like_count MEDIUMINT 9 -

型は、データの種類のことで、主に数値、文字列、日付と時刻という風に分かれます。また、長さとはデータの桁数のことで、数値や文字列の場合に設定します。

また、idカラムにはオートインクリメント（A_I）とプライマリーキー（主キー）を設定します。オートインクリメントを設定すると、レコードが追加されるたびに1ずつ自動的に数値が加算されていきます。また、主キーを設定することで、主キー制約により、重複した値やNULL値が入らなくなります。

テーブルの設計が決まったら、CREATE TABLE文あるいはphpMyAdminなどのツールを用いてテーブルを作成します。

後日注記：5ちゃんねるのようなスレッドフロート型掲示板（たくさんのスレッドがあり、スレッドごとにレスがつくタイプの掲示板）を作る場合、たとえば2つの方法があります。まず、CREATE TABLE文を使って、bbs1, bbs2, bbs3のように、スレッドごとに別のテーブルを作成する方法。もうひとつは、bbsテーブルにスレッドIDを格納するtidカラムを追加し、スレッド一覧を格納するthreadsテーブルの外部キーとして、リレーションで表現し、必要に応じてテーブルを結合したり、WHERE句で抽出してSELECTしたりする方法です。後者の方法は処理効率が落ちますが、コメントを最小限のテーブルで管理できるため、掲示板ではなく学生の個人情報などの場合はこちらの方が適切かもしれません。

（「いちばんやさしいPHPの教本 人気講師が教える実践Webプログラミング (「いちばんやさしい教本」シリーズ)」を参考に執筆しました。）

プロンプトとテーブルの作成

mysqlプロンプトを起動するには、

mysql -u ユーザー名 -p

と入力して、パスワードを聞かれたらパスワードを入力する。

この状態で、SHOW DATABASESと実行すれば、データベース一覧が表示できる。また、CREATE DATABASE文でデータベースの作成、CREATE TABLE文でテーブルの作成ができる。

たとえば、mysqlプロンプトで、テーブルの作成は以下のようにする。

mysql> CREATE TABLE テーブル名 (
    列1 型(長さ) オプション,
    列2 型(長さ) オプション,
    列3 型(長さ) オプション
);

詳しくは以下が参考になる。

ユーザーの作成

また、新しいデータベースには必ずCREATE USER命令とGRANT命令でrootでない新しいユーザーを作って権限を付加すること。

後日注記：ユーザーの作成や権限の付加はphpMyAdminを用いて行うこともできる。動画の解説サイトなどで、GRANT命令で権限を付加するようなSQL文が示されている時は、phpMyAdminを用いて同じようにユーザーとテーブルを作成し、権限を付加することができることを知っておくと、XAMPPなどを導入した際の初期設定にも活用できる。

後日注記：rootユーザーには絶大な権限があり危険であるため、ユーザーを作って権限を付加します。権限は、「データベース全体」「特定のテーブル」「特定のカラム」に対して設定できます。

四大命令

「3ステップでしっかり学ぶ MySQL入門 (今すぐ使えるかんたんプラス)」を参考に執筆しました。

SELECT（取り出し）

データベースから項目を取得する。

SELECT text FROM bbs;
SELECT name,email FROM bbs;
SELECT * FROM bbs;

全てのデータを取得するには*（アスタリスク）を使うが、必要のない時はできるだけアスタリスクを用いず、明確に取り出す列を指定するべきである。

後日注記：MySQLでは、SELECT文を用いることで関係演算の「選択」が、カラム名を「,」で指定して選択することで関係演算の「射影」が可能。また、JOIN句と主キー・外部キーを用いることで関係演算の「結合」が可能である。

後日注記：特によく使うのはidを指定した選択。取り出す条件式を指定できるWHERE句を使う。idの値の部分はプレースホルダを用いて?などとする（?の部分が後でPHPの変数の値に置き換えられる）。

SELECT * FROM bbs WHERE id = ?;

以下は参考文献。

2023.04.15編集

2024.05.25編集

INSERT INTO（挿入）

データベースに項目を追加する。

INSERT INTO bbs (name,email,text) VALUES ('Assy','hoge@mail.hogehoge.com','応援しています！');
INSERT INTO bbs VALUES (1,'Schwarz','schwarz@mail.hogehoge.com','2020-10-18 16:30:00','頑張れ',0);

二行目は省略構文。全ての列にデータを順番に登録する場合、省略構文を使うこともできる。ただし、データベースの構造が変わってしまった時などに命令の意味も変わってしまうことから、省略構文はあまり使うことを推奨されない。

注意すべきなのは、文字列には''をつけるが、数値やNULL値には''をつけない。パスワードのように数値に見えても本当は文字列である場合には''をつけなければならない。

UPDATE（更新）

データベースの項目を変更する。

UPDATE bbs SET text = '削除されました', name = '削除済み' WHERE id = 3;

どの行のレコードであるかをWHERE句で指定する。

DELETE（削除）

データベースの項目を削除する。

DELETE FROM bbs WHERE id = 3;

どの行のレコードであるかをWHERE句で指定する。

その他のSQL文

SQL文一覧

以下はSQL文の一覧。

データ定義言語（DDL）：

SQL文説明
CREATEテーブル（あるいはデータベース）の作成
DROPテーブル（あるいはデータベース）の削除
ALTERテーブルの設計の変更

データ操作言語（DML）：

SQL文説明
SELECT行（レコード）の抽出
INSERT行（レコード）の挿入
UPDATE行（レコード）の更新
DELETE行（レコード）の削除

データ制御言語（DCL）：

SQL文説明
BEGINトランザクションの開始
COMMITコミット
ROLLBACKロールバック

特に、「既存のデータを変更することなく、新しい列をテーブルに追加したい」などといった場合はALTER命令を使うことができる。また列を削除するにもALTER命令を使う。また、列の名前を変えたい場合や、データ型を別の型に変更したい場合もALTER命令を使う。

後日注記：トランザクションを開始するにはBEGIN命令を使う。BEGINを実行して、なんらかのデータ操作をした後、COMMIT命令でトランザクションをコミットする。もしCOMMITが行われずにROLLBACK命令が実行された時は、BEGINが実行された状態のデータベースに戻る。

他にも、MySQLにはユーザーに権限を与えるGRANT命令などがある。

2023.03.25編集

WHERE句

SELECT文にWHERE句をつけることで、条件式を指定して検索できる。

SELECT name,email FROM bbs WHERE name='Assy';

条件式には、一致と不一致に使う=と<>のほか、比較演算として>, <, >=, <=を使用できる。

以下は条件式の絞り込みに使う演算子。

演算子説明
ANDとOR検索する条件式を複数指定できる。
BETWEEN検索するデータの数値における範囲（どこからどこまで）を指定できる。
IN検索する数値の含まれるリスト（AかBのいずれかなど）を指定できる。
LIKE検索する文字列の条件を指定できる。
%はなんらかの複数の文字数の文字、_はなんらかの1文字の文字を表す。

また、サブクエリは以下のようにできる。

SELECT * FROM bbs WHERE name=(SELECT name FROM bbs WHERE time = '2020-10-18 16:30:00');

詳しくは以下のページが参考になります。（上記は以下を参考に執筆しました。）

2024.05.25編集

ORDER BYとGROUP BYと集計関数

ORDER BY句を使うことで、データをソート（並べ替え）することができる。ASCはアルファベット昇順、DESCは降順にソートする。

SELECT * FROM bbs ORDER BY like_count ASC;

GROUP BY句を使うことで、集計の際にグループ化を行える（集計はグループごとに行われる）。

SELECT name,SUM(like_count) FROM bbs GROUP BY name;

グループ化を行うことで、集計関数を使うことができる。集計関数は、

SQL文説明
MAX()最大値を得る
MIN()最小値を得る
SUM()合計を求める
AVG()平均を求める
COUNT()個数を数える

などが用意されている。

このほか、以下のような関数がMySQLには用意されている。

関数説明
ROUND()四捨五入する
CHAR_LENGTH()文字列の文字数を得る
REPLACE()文字列を置換する
CEILING()小数点以下を切り上げする
FLOOR()小数点以下を切り捨てする
SQRT()平方根を計算する
DATE_FORMAT()日付をフォーマットする
NOW()現在の日付・時刻を得る

詳しくは以下のページ・書籍が参考になります。（上記は以下を参考に執筆しました。）

2023.03.25編集

2024.10.29編集

LIMIT句、DISTINCT句

このほか、MySQLには件数に上限をかけて取得するLIMIT句や、重複した行を削除して一行にまとめるDISTINCT句などがある。

AS句

AS句を用いることで、結合などで用いるテーブル名などが長い場合、繰り返す際に別名をつけることができる（FROM threads AS tとしてt.idとしたり、JOIN comments AS cとしてc.tidとしたりする）。

また、AS句を用いることでカラムに別名をつけることもできる（カラムbasenameに対して別の名前addressなど）。

2023.03.26編集

関連ページ

結合

結合とリレーションについてはリレーションを参照のこと。

インデックス

インデックスについてはデータベースインデックスを参照のこと。

サンプルコード

PHPからのSQLの利用

PHPからのSQL文の利用については、PHPデータベースの世界観を参照のこと。

参考文献

参考文献

以下は参考文献。