月別アーカイブ: 2013年3月

8.MySQL入門 並び替え、範囲、グループ化

ここではMySQLでの並び替え、範囲、グループ化ついて解説していきます。

mysql

tb1

id name age
1 田中 27
2 山田 28
3 佐藤 29
4 鈴木 30
5 中村 30

今回はこのテーブルを例として解説していきます。

並び替え

SELECT *
FROM tb1
ORDER BY id ASC;

書式
SELECT カラム名 FROM テーブル名 ORDER BY カラム名;

「ORDER BY id;」もしくは「ORDER BY id ASC;」とすることで、指定したカラムで降順に並び替えて表示します。
昇順で並び替えるには「ORDER BY id DESC;」とします。

降順「ASC」は省略できますが、昇順「DESC」は省略できません。

表示する範囲の指定

SELECT *
FROM tb1
ORDER BY id
LIMIT 2;

実行結果

id name age
1 田中 27
2 山田 28

書式
SELECT カラム名 FROM テーブル名 LIMIT 表示件数;

「LIMIT」を使うことによって、表示するレコード数を指定することができます。
又、「LIMIT」後の数値をコンマで区切り指定することで、表示範囲を指定することができます。

SELECT *
FROM tb1
ORDER BY id
LIMIT 1,2;

実行結果

id name age
2 山田 28
3 佐藤 29

レコードの最初の番号は「0」となるので、「LIMIT 1, 2;」と指定することで、1~2番目のデータが表示されます。

グループ化

tb1

id sales
1 1000
1 2000
2 500
2 200
3 5000
3 6000

今回はこのテーブルを例として解説していきます。

SELECT id, AVG(sales)
FROM tb1
GROUP BY id
ORDER BY AVG(sales);

実行結果

id sales
2 350.0000
1 1500.0000
3 5500.0000

書式
SELECT カラム名 FROM テーブル名 GROUP BY カラム名;

「GROUP BY」を使用することでカラム名が同じものだけグループ化して表示させることができます。
例では「AVG()」関数を使用しておりグループ化したデータの平均でソートして表示しています。

グループ化後の条件

SELECT id, AVG(sales)
FROM tb1
GROUP BY id
HAVING AVG(sales) <= 1500
ORDER BY AVG(sales);

実行結果

id sales
2 350.0000
1 1500.0000

書式
SELECT カラム名 FROM テーブル名 GROUP BY カラム名 HAVING 条件式;

「GROUP BY」でグループ化したデータに条件式を定義する場合「HAVING」句を使用します。
例では売り上げの平均値が1500以下のものを表示させています。

記述の順序

いままで解説してきたもので記述の順序が違うとエラーになるのでMySQLの順番として
①SELECT
②FROM
③WHERE
④GROUP BY
⑤HAVING
⑥ORDER BY
⑦LIMIT
この順番で書くようにしましょう。

7.MySQL入門 条件

ここではMySQLでの条件付のデータの表示ついて解説していきます。

mysql

tb1

id name age
1 田中 27
2 山田 28
3 佐藤 29
4 NULL 30

今回はこのテーブルを例として解説していきます。

比較演算子を使った抽出

SELECT *
FROM tb1
WHERE id < 2;

実行結果

id name age
1 田中 27

書式
SELECT カラム名 FROM テーブル名 WHERE 条件式;

「ELECT * FROM テーブル名」の後に「WHERE」をつけることで条件を使ったデータの抽出ができるようになります。

比較演算子まとめ

= 等しい
> より大きい
>= 以上
< より小さい
<= 以下
<> 等しくない
!= 等しくない

AND比較演算子を使った抽出

SELECT *
FROM tb1
WHERE id < 3 AND id > 1; 

実行結果

id name age
2 山田 28

「WHERE」句の条件を「AND」でつなげると左の条件式と右の条件式に当てはまる物のみ抽出されます。

OR比較演算子を使った抽出

SELECT *
FROM tb1
WHERE id = 3 OR name = '田中'; 

実行結果

id name age
1 田中 27
3 佐藤 29

「WHERE」句の条件を「OR」でつなげると左の条件式と右の条件式に当てはまる物のみ抽出されます。

「AND」と「OR」を両方使う場合、「AND」を優先して比較します。
なので優先的に比較したいものは「()」括弧で囲み定義してください。

IN比較演算子を使った抽出

SELECT *
FROM tb1
WHERE id IN (1, 3); 

実行結果

id name age
1 田中 27
3 佐藤 29

書式
SELECT カラム名 FROM テーブル名 WHERE カラム名 IN (データ1, データ2, ・・・);

「IN」演算子を使う場合カラム名を指定して括弧内に当てはまるデータを「,」で区切り記述します。
「NOT IN ();」とすることで括弧内にないデータを抽出することもできます。

BETWEEN比較演算子を使った抽出

SELECT *
FROM tb1
WHERE id BETWEEN 1 AND 2; 

実行結果

id name age
1 田中 27
2 山田 28

書式
SELECT カラム名 FROM テーブル名 WHERE カラム名 BETWEEN 数値1 AND 数値2;

「BETWEEN」演算子を使う場合カラム名を指定して「AND」で数値の幅を指定します。
数値1以上、数値2以下のデータを抽出することができます。

「NOT BETWEEN」を使用すると数値1未満、数値2を超えるものが抽出されます。

NULLを使った条件

SELECT *
FROM tb1
WHERE name IS NULL; 

実行結果

id name age
4 NULL 30

書式
SELECT カラム名 FROM テーブル名 WHERE カラム名 IS NULL;

データが「NULL」のものを抽出する場合「IS NULL」を使用します。
逆に「NULL」以外のものを抽出する場合「IS NOT NULL」を使用します。

6.MySQL入門 データの表示

ここではMySQLでのデータの表示ついて解説していきます。

mysql

tb1

id name age
1 田中 27
2 山田 28
3 佐藤 29

今回はこのテーブルを例として解説していきます。

テーブル全てのデータ表示

SELECT * FROM tb1;

実行結果

id name age
1 田中 27
2 山田 28
3 佐藤 29

書式
SELECT * FROM テーブル名

テーブル内の全てのデータを表示させるには「*」アスタリスクを使用します。
アスタリスクはワイルドカードで「SELECT」文の後につけると、全てのカラムを選択したことになります。

カラム名を指定して表示

SELECT id, name FROM tb1;

実行結果

id name
1 田中
2 山田
3 佐藤

書式
SELECT カラム1, カラム2, ・・・ FROM テーブル名;

「SELECT」文の後に表示させるカラム名を「,」区切りで指定します。
カラム名を指定することで取得するデータ量が少なくなるので、パフォーマンス面で取得するカラム名を指定してデータの取得をするようにしましょう。

5.MySQL入門 データの挿入

ここではMySQLでのデータの挿入ついて解説していきます。

mysql

tb1

id name age
INT VARCHAR(10) INT

今回はこのテーブルを例として解説していきます。

データの挿入

INSERT INTO tb1 VALUES(1, '田中', 27);

実行結果

id name age
1 田中 27

テーブルにデータを挿入するには「INSERT INTO」コマンドを使います。

書式
INSERT INTO テーブル名 VALUES(データ,・・・);

挿入するデータを「,」で区切り記述してください。
文字列の挿入を行う場合文字列は「”」シングルクォーテーション、「””」ダブルクォーテーションで囲んで記述してください。

カラムを指定してデータの挿入

INSERT INTO tb1 (id, name, age) VALUES(1, '田中', 27);

書式
INSERT INTO テーブル名 (カラム名, ・・・) VALUES(データ, ・・・);

実行結果はデータの挿入と同じになります。
テーブル名後に半角スペースでカラム名をしていすればカラム名を指定してデータを挿入できます。

複数のデータを挿入

INSERT INTO tb1 (id, name, age) VALUES(1, '田中', 27), (2, '山田', 33);

実行結果

id name age
1 田中 27
2 山田 33

書式
INSERT INTO テーブル名 (カラム名, ・・・) VALUES(データ, ・・・), (データ, ・・・);

「VALUES」以下の「()」を1レコードとし「,」で区切り複数定義します。

INSERTのもうひとつの書き方

INSERT INTO tb1 SET id=1, name='田中', age=27;

書式
INSERT INTO テーブル名 SET カラム名=データ;

このように「SET」後を「カラム名=データ」とすることでもデータの挿入が行えます。
実行結果はデータの挿入と同じになります。

4.MySQL入門 主キー インデックスの設定

ここではMySQLでの主キーの設定ついて解説していきます。

mysql

主キーの設定

CREATE TABLE tb1(id INT PRIMARY KEY);

実行結果

Field Type Null Key Default Extra
id int(11) No PRI

書式
CREATE TABLE テーブル名(カラム名 データ型 PRIMARY KEY);

テーブルを作成したときのコマンドの最後に半角スペースで「PRIMARY KEY」を追加します。
複数定義する場合は、「,」で区切り定義します。

主キーとは

主キーとはレコードのうち1つを特定するのに使われる識別子で、
「値の重複をしない」、「空(null)にすることはできない」とカラムに設定することです。
主キーを設定することで、社員IDや商品IDなど重複しない1つのデータを定義することが可能です。
又、主キーを設定したカラムに重複した値やnullを登録しようとした場合はエラーになり登録することができません。

一意キーの設定

CREATE TABLE tb1(id INT UNIQUE);

実行結果

Field Type Null Key Default Extra
id int(11) YES UNI NULL

書式
CREATE TABLE テーブル名(カラム名 データ型 UNIQUE);

テーブルを作成したときのコマンドの最後に半角スペースで「UNIQUE」を追加します。
複数定義する場合は、「,」で区切り定義します。

一意キーとは

一意キーとは、重複はできませんが、「空(null)」を入力することはできます。

連続番号の設定

CREATE TABLE tb1(id INT AUTO_INCREMENT PRIMARY KEY);

実行結果

Field Type Null Key Default Extra
id int(11) No PRI NULL auto_increment

書式
CREATE TABLE テーブル名(カラム名 データ型 AUTO_INCREMENT PRIMARY KEY);

テーブルを作成したときのコマンドの最後に半角スペースで「AUTO_INCREMENT PRIMARY KEY」を追加します。
複数定義する場合は、「,」で区切り定義します。

連続番号とは

連続番号付のカラムを指定する場合は、データ型は「INT」など整数値に定義します。
連続番号を指定したカラムはデータ挿入時に自動的に「1」、「2」・・・と入力されていきます。

連続番号の初期値を設定

ALTER TABLE tb1 AUTO_INCREMENT=0;

書式
ALTER TABLE テーブル名 AUTO_INCREMENT=初期値;

すでに存在するデータの数値より大きい値であれば「AUTO_INCREMENT」を初期値として設定することができます。

インデックスを設定

CREATE INDEX index_name ON tb1(name);

書式
CREATE INDEX インデックス名 ON テーブル名(カラム名);

インデックスとは

データを検索するとき、データの量が多い場合、全てのデータを検索すると時間がかかってしまいます。
これを解消するためにインデックスを設定し、データの検索にはインデックスを利用し素早い検索を行います。
又、主キーの設定時にインデックスは自動的に追加されます。

インデックスの削除

DROP INDEX index_name tb1;

書式
DROP INDEX インデックス名 テーブル名;

「DROP INDEX」コマンドでインデックスを削除します。

インデックスの確認

SHOW INDEX FROM tb1;

書式
SHOW INDEX FROM テーブル名;

「SHOW INDEX」コマンドでインデックスの確認ができます。

インデックスのまとめ

インデックスを設定することで検索速度を早くすると先ほど解説しましたが、インデックスを設定する項目が適していなかったり、インデックスをたくさんのカラムに設定してしまうと、逆に検索速度が遅くなってしまいます。

例えば重複するデータが多いカラムにはインデックスを設定しないほうがいいです。
重複するデータが少なければ少ないほど、インデックスの効果を発揮します。