トップへ戻る
BLOGS

MySQL 基礎文法

MySQL 基礎文法

前回構築したMySQLの環境で今回は、mysqlクライアントをを利用して、基本的なコマンドを使用しながら、新規データベース/テーブルの作成からデータの入力、確認までの手順を見ていくことにします。
自分の手を動かすことはもちろん、ここでデータベース操作には欠かせないさまざまな基礎概念を理解できるようにしましょう。

環境構築がまだの方は以下の記事を参考にしてください。

関連記事

MySQLを起動する

まずはデータベースに接続するためのMySQLを起動しましょう。

mysql.server start

Starting MySQL

.. SUCCESS!

と表示されたら、無事に起動できています。
次に、MySQLに接続します

 mysql -u root -p  

前回の記事でパスワードを設定している方は、Enter password:とパスワードを聞かれるため、入力してください。画面のプロンプトがmysql>になっていれば接続はOKです。

データベースを作成する

それでは学習に使用するデータベースを作成します
以下の2つのコマンドを入力します。

CREATE DATABASE selfjsp; //データベースの作成
USE selfjsp;             //使用するデータベースを決定

これで使用するデータベースの作成ができました。

データベースに対してなにか操作を行うためには、まずデータベースを選択していなければいけないため、今回作成したデータベースを選択しておきます。

ユーザーを作成する

すべての実行権限を持つrootユーザーをいつまでも使用するべきではないので、データベースを使用するユーザーを設定します。

/* 書式 */
GRANT 権限 ON データベース.テーブル TO 'ユーザ'@'ホスト' identified by 'パスワード';
 
/* 例1)「sampleDB」の全テーブルに対する全ての権限を付与 */
GRANT ALL ON sampleDB.* TO user@localhost identified by 'pass';

新規のテーブルを作成する

次にselfjspデータベースの配下に新規のテーブルを作成します。

作成するのは会員情報テーブルを想定してmemberテーブルとします。
memberテーブルのフィールドレイアウトは以下のようにします

フィールド名データ型概要
idINT会員ID(主キー/自動連番)
nameVARCHAR(255)氏名
sexCHAR(2)性別
oldINT年齢
enterDATE入会日
memoVARCHAR(255)備考
// 構文
CREATE TABLE テーブル名 (フィールド名 データ型 列フラグ[,...])テーブルオプション;
CREATE TABLE memver (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex CHAR(1) DEFAULT '男 ', old INT NOT NULL, enter DATE NOT NULL, memo VARCHAR(255) DEFAULT NULL);

テーブルを作成するのは、CREATE TABLE命令の役割です。複雑に見えますが、今後も頻繁に使用するため覚えておきましょう。

しかしテーブルの名前を間違えてしまいました。。。
「member」にしたいのが「memver」になってしまっています。
じゃあテーブル名を変更しましょう。

テーブルの名前を変更する

テーブル名を変更するためには以下のコマンドを実行します

 RENAME TABLE memver TO member;
// 構文
RENAME TABLE 現在のテーブル名 TO 変更後のテーブル名;

TOはASでも良いし、省略でもOKですが視認性が悪くなるため、慣例的にはわかりやすいTOを付けるようです。

テーブルを確認する

それでは正しくテーブルが作成できたか、確認してみましょう。データベース内のテーブルをリスト表示するのは下記のコマンドです。

 SHOW TABLES;
+-------------------+
| Tables_in_selfjsp |
+-------------------+
| address           |
| book              |
| member            |
| usr               |
| usr_role          |
+-------------------+
5 rows in set (0.00 sec)

SHOW TABLES句を入力すると一覧で表示されます。
私は参考にテーブルをたくさん作成していますが、先程作成した「member」テーブルがあれば、問題ないです。

個々のテーブルのフィールド構成を確認するためには、SHOW FIELDS句で確認できます

SHOW FIELDS FROM member;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| sex   | char(1)      | YES  |     | 男      |                |
| old   | int          | NO   |     | NULL    |                |
| enter | date         | NO   |     | NULL    |                |
| memo  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

データの挿入/検索を行う

作成したmemberテーブルに対して、新規データを挿入してみましょう。

 INSERT INTO member (id, name, sex, old, enter, memo) VALUES(1, "山田太郎", "男", 20, "2020-10-25", NULL);

これで正しくデータを挿入できたか確認してみましょう。

SELECT * FROM member;
+----+--------------+------+-----+------------+------+
| id | name         | sex  | old | enter      | memo |
+----+--------------+------+-----+------------+------+
|  1 | 山田太郎     | 男   |  20 | 2020-10-25 | NULL |
+----+--------------+------+-----+------------+------+
1 row in set (0.00 sec)

問題なくデータを入れることができたようですね。

SQLの基本文法

データベースやテーブルを作成/削除する、、、
ログインするためのユーザーを定義する、、、、
テーブルに対して新規にデータを追加する、、、、、
あるいは既存のデータを検索する。。。。

リレーショナルデータベースに対するこれらの操作は、すべてSQLという言語を使って行います。
SQL言語は以下の分類に分けることができます。

  • データ操作言語(Data Manipulation Language)
  • データ定義言語(Data Definition Language)
  • データ制御言語(Data Control Language)

この中でも、アプリを構築する際によく使用するのはデータ操作言語です。
まずは基本的とも言える「SQL4句」を解説します。

INSERT命令

テーブルに新しいレコードを挿入するのは、INSERT命令の役割です。
以下は基本的な構文です。

INSERT INTO テーブル名(フィールド名1, ...)VALUES(フィールド値1, ...)

指定されたテーブルの各列に対して、VALUES句で指定された値を登録して行く形です。
「フィールド名1, フィールド名2, …」としたら、VALUESの方も「値1,値2, …」の並びで、それぞれ対応関係にある必要があります。
もしも互いの個数が異なる場合はINSERT命令はエラーで失敗します。

以下はmemberテーブルに対して、4つのレコードを挿入する例です。値が文字列か日付の場合はシングルクォーテーションで括る必要があります。

自動連番列を省略

mysql> INSERT INTO member (name, sex, old, enter, memo) VALUES('斎藤花子', '女', 20, '2021-05-10', '紹介割引適用');
Query OK, 1 row affected (0.00 sec)

ここからは、順を追って例を見ていきます。
まずはINSERT命令は上記のように自動連番列(id列)が省略された場合には、自動的に採番された値が設定されます。

規定値列を省略

mysql>  INSERT INTO member(name, old, enter, memo) VALUES('鈴木次郎', 30, '2020-11-21', '再入会');
Query OK, 1 row affected (0.00 sec)

また、DEFAULTを設定した上で上記のように規定値がある列(sex列)が省略された場合は、対応する既定値(ここでは「男」)が設定されます。

未定義列の省略

mysql>  INSERT INTO member(name, old, enter) VALUES('佐藤一男', 40, '2021-05-07');
Query OK, 1 row affected (0.00 sec)

また、AUTO_INCREMENTや、DEFALUT属性のいずれも定義されていない列(memo列)が省略された場合には、NULL値が設定されます。
ただし、その列がNULL値を許可しておらず、既定値も定義されていないという場合は、INSERT命令はエラーで失敗します。

フィールド名を省略

mysql> INSERT INTO member VALUES(5, '山本和美', '女', 32, '2021-06-01', NULL);
Query OK, 1 row affected (0.00 sec)

テーブル名の後に記述するフィールド名をすべて省略したい場合は、すべてのフィールド値を設定することで省略できます。

UPDATE命令

テーブルに登録済みのレコードを更新するにはUPDATE命令を使用します。
基本的な構文は以下です。

UPDATE テーブル名 SET フィールド名1= フィールド値, ...[WHERE条件]

指定されたテーブルから、条件式に合致するレコードを抜き出し「フィールド名1 = 値1, フィールド名2 = 値2, …」の指定に従って更新します。
「フィールド名1 = 値1」はそのとおり、フィールド名1に値1をセットするという意味になります。

今回のテーブルでの例文を見てみましょう、
「会員コード(id列)が1のレコードの年齢(old列)を21に更新する」例です。

id 1 をUPDATE

mysql> UPDATE member SET old = 21 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

WHERE句で指定された条件式「id = 1」は「id列が1に等しいかどうか」という意味です。
UPDATE命令ではWHERE句がTrueになるレコードだけを取り出し、更新の処理を行います。

条件式であるWHERE句を省略した場合、たとえば以下の命令は、

UPDATE member SET old = 21;

これを実行すると無条件ですべてのレコードが「old = 21」で更新されてしまうため注意です。

DELETE命令

既存のレコードを削除するのはDELETE命令の役割です。
以下の構文です。

DELETE FROM テーブル名 WHERE 条件式

指定されたテーブル名から条件式に合致するレコードだけを削除します。

たとえば、「会員コードが2の会員情報を削除する」例です。

idが2のレコードを削除

mysql> DELETE FROM member WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

UPDATE命令と同様にWHERE句は省略可能ですが、その場合テーブル内のすべてのレコードが無条件に削除されます。

SELECT命令

既存のテーブルからレコードを検索/取得するのはSELECT命令の役割です。
この命令が最も複雑で色々な書き方がありますが、具体的な例とともに代表的な記法だけでもしっかりと押さえておきましょう。

すべての列を取得する

まずは一番簡単な、すべての列を取得する例です

mysql> SELECT * FROM member;
+----+--------------+------+-----+------------+--------------------+
| id | name         | sex  | old | enter      | memo               |
+----+--------------+------+-----+------------+--------------------+
|  1 | 山田太郎     | 男   |  21 | 2020-10-25 | NULL               |
|  3 | 鈴木次郎     | 男   |  30 | 2020-11-21 | 再入会             |
|  4 | 佐藤一男     | 男   |  40 | 2021-05-07 | NULL               |
|  5 | 山本和美     | 女   |  32 | 2021-06-01 | NULL               |
|  6 | 斎藤花子     | 女   |  20 | 2021-05-10 | 紹介割引適用       |
+----+--------------+------+-----+------------+--------------------+
5 rows in set (0.00 sec)

SELECTの後にある「*」アスタリスクはテーブル内のすべての列を意味します。

取得列を指定する

「*」で無条件にすべての列にアクセスするのは簡単ですが、テーブルに沢山の列が含まれている場合など、余計な列まで取得するため、リソースの無駄遣いです。
取得列を視覚的に分かりやすくするという意味でも、手軽にテーブルの中身を確認したいという場合を除いては「*」は使用すべきではありません。
以下は氏名(name列)と年齢(old列)を取り出す例です。

mysql> SELECT name, old FROM member;
+--------------+-----+
| name         | old |
+--------------+-----+
| 山田太郎     |  21 |
| 鈴木次郎     |  30 |
| 佐藤一男     |  40 |
| 山本和美     |  32 |
| 斎藤花子     |  20 |
+--------------+-----+
5 rows in set (0.00 sec)

氏名と年齢を取り出せました。ちなみにカラムを指定する場合は、登録した順番などはなく、このSELECT句を作成ときに記述した順番で取得されます。

条件式で取得行を絞り込む

特定の条件に合致したレコードだけを取得したい場合はWHERE句を使用します。
たとえば、年齢(old列)が30以上の会員だけを取得する場合は以下のような記述になります。

mysql> SELECT name, old FROM member WHERE old >= 30;
+--------------+-----+
| name         | old |
+--------------+-----+
| 鈴木次郎     |  30 |
| 佐藤一男     |  40 |
| 山本和美     |  32 |
+--------------+-----+
3 rows in set (0.00 sec)

「>=」は比較演算子で、比較演算子の種類は以下のようなものがあります。

演算子概要条件式の例
=等しいname = ‘山田太郎’
>右辺より大きいold > 30
<右辺より小さいold < 30
>=右辺以上old >= 30
<=右辺以下old <= 30
<>等しくないname <> ‘山田太郎’
IS [NOT] NULLNULLである(ない)memo IS NULL
[NOT] LIKE指定パターンに一致(一致しない)name LIKE ‘山%’
[NOT] BETWEEN指定範囲に含まれる(含まれない)old BETWEEN 25 AND 35
[NOT] IN候補地のいずれかである(いずれでもない)old IN(20, 30, 40)

複数の演算子を組み合わせる

論理演算子を活用することで複数の条件式を組み合わせ、データを絞り込み、目的のデータを取り出しやすくすることができます。

以下は、「性別が男性で、かつ備考欄が空でない(memo列がNULLでない)会員の氏名」を取り出す例文です。

mysql> SELECT name, sex, memo FROM member WHERE sex = '男' AND memo IS NOT NULL; 
+--------------+------+-----------+
| name         | sex  | memo      |
+--------------+------+-----------+
| 鈴木次郎     | 男   | 再入会    |
+--------------+------+-----------+
1 row in set (0.00 sec)

上記の例文では「AND演算子」を使用しています。また、論理和である「OR演算子」を使用すると「または」の意味になります。

データを並び替える

データを並び替えることをソート言います。ソートを行うのはORDER BY句の役割です。
以下は「年齢について昇順で、会員コードに付いて降順」で並び替えた例文です。

mysql> SELECT old, name, id FROM member ORDER BY old, id DESC;
+-----+--------------+----+
| old | name         | id |
+-----+--------------+----+
|  20 | 斎藤花子     |  6 |
|  21 | 山田太郎     |  1 |
|  30 | 鈴木次郎     |  3 |
|  32 | 山本和美     |  5 |
|  40 | 佐藤一男     |  4 |
+-----+--------------+----+
5 rows in set (0.00 sec)

ORDER BY句には「ソート列 並び順」の形式でソート式を指定します。
今回のように複数のキーでソートを行う場合は、ソート式を優先するキーの順にカンマ区切りで指定を列記します。

並び順にはASC(昇順)、DESC(降順) のいずれかを指定でき、省略した場合はASCとして処理されます。

データを集計する

特定のキーでレコードをまとめて集計を行うことをグループ化といいます。
グループ化にはGROUP BY句を使用します。
ココではGROUP BY句を利用して、男女別の年齢平均を集計してみます。

mysql> SELECT sex, AVG(old) FROM member GROUP BY sex;
+------+----------+
| sex  | AVG(old) |
+------+----------+
| 男   |  30.3333 |
| 女   |  26.0000 |
+------+----------+
2 rows in set (0.01 sec)

GROUP BY句にはグループ化するキー(列名)を指定します。
複数の列をキーとする場合には、ORDER BY句と同様に、列名をカンマ区切りで列記します。

また、GROUP BY句は集計関数と一緒に使用することが一般的です。集計関数はさまざまなものが利用できるため、個々調べてみてください。

コメントをお待ちしております

お気軽にコメントをどうぞ。

CAPTCHA