08いろんな条件で抽出する(備忘録)→ > 01p > 02p > 03p > 04p > 05p > END
08いろんな条件で抽出する(p110)
★テーブルの準備★
(1)《カラムの表示を工夫して「SELECT」を実行する》
(2)《計算したり、文字列を処理したりして表示する》
(3)《条件を指定した選択》
(4)《複数の条件を指定した選択》
(5)《並べ替え》
(6)《グループごとに表示する》
(7)《条件付グループで表示p152》
<まとめ>
<チェック>
<練習問題>
END
08 いろんな条件で抽出する
単純にINSERT、SELECT、DELETEするだけでデータベースが運用できるわけではありません。実践では複雑に絡み合った複数のテーブルを使って大量のデータを扱い、処理の効率を考え、そして不慮のデータ消滅も許されません。MySQLの勉強も佳境を迎えています。条件を設定した抽出、編集から、リレーショナルの名にふさわしい複数のテーブルの利用。そして「ビュー」「ストアドプロシージャ」「トランザクション」「ファイル操作」という、より実践的で必要とされる内容を解説します。
★CHAPTER08⇒いろんな条件で抽出する
☆CHAPTER09⇒テーブルを編集する
☆CHAPTER10⇒複数のテーブルを利用する
☆CHAPTER11⇒ビューを使いこなす
☆CHAPTER12⇒ストアドプロシージャを使いこなす
☆CHAPTER13⇒トランザクションを使いこなす
☆CHAPTER14⇒ファイルを使ったやり取り
★CHAPTER08⇒いろんな条件で抽出する(p110)★
レコードを表示するときのコマンドは「SELECT * FROM tb1;」のような単純な「SELECT」コマンドを使っていました。しかし、実際にデータベースを使うとなると、ただ全部のレコード、全部のカラムを表示するだけでは用が足りません。求める情報を素早く探し出さなくてはなりません。「SELECT」はSQLの基本です。どんなレコードを、どんな形式で取り出したいのかを明確にし、自由自在にSELECTを操れるようにしましよう。
★テーブルの準備★
①MySQLモニタの起動(p29)
mysql -u root |
②存在するデータベースの確認
SHOW DATABASES; |
③使用するデータベースを決める
USE db1; |
④テーブルの作成
CREATE TABLE tb (bang VARCHAR(20),uria INT,tuki INT); |
⑤カラム構造の確認
DESC tb; |
⑥レコードの挿入
INSERT INTO tb VALUES ('A103','101',4); |
⑦↑キーで前の行を表示して修正後エンターキーを押し、以下同様に繰り返す
INSERT INTO tb VALUES ('A102','54',5); INSERT INTO tb VALUES ('A104','181',4); |
★テーブル「tb」の作成
mysql> CREATE TABLE tb (bang VARCHAR(20),uria INT,tuki INT); Query OK, 0 rows affected (0.08 sec) mysql> DESC tb; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | bang | varchar(20) | YES | | NULL | | | uria | int(11) | YES | | NULL | | | tuki | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.05 sec) mysql> INSERT INTO tb VALUES ('A103','101',4); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A102','54',5); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A104','181',4); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A101','184',4); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO tb VALUES ('A103','17',5); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A101','300',5); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A102','205',6); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A104','93',5); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A103','12',6); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb VALUES ('A107','87',6); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 101 | 4 | | A102 | 54 | 5 | | A104 | 181 | 4 | | A101 | 184 | 4 | | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A104 | 93 | 5 | | A103 | 12 | 6 | | A107 | 87 | 6 | +------+------+------+ 10 rows in set (0.00 sec) mysql> |
⑧レコードの表示
SELECT * FROM tb; mysql> SELECT * FROM tb; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 101 | 4 | | A102 | 54 | 5 | | A104 | 181 | 4 | | A101 | 184 | 4 | | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A104 | 93 | 5 | | A103 | 12 | 6 | | A107 | 87 | 6 | +------+------+------+ 10 rows in set (0.00 sec) mysql> |
(1)《カラムの表示を工夫して「SELECT」を実行する》
(1-1)テーブルの準備
(1-2)カラムを入れ替えて表示する
●実行される内容 p111
●操作手順 p112
①次を実行する SELECT uria,bang FROM tb; |
■実行結果
mysql> SELECT uria,bang FROM tb; +------+------+ | uria | bang | +------+------+ | 101 | A103 | | 54 | A102 | | 181 | A104 | | 184 | A101 | | 17 | A103 | | 300 | A101 | | 205 | A102 | | 93 | A104 | | 12 | A103 | | 87 | A107 | +------+------+ 10 rows in set (0.00 sec) mysql> |
(1-3)エイリアスを使う
●書式⇒カラム名をエイリアスにする
SELECT カラム名 AS エイリアス FROM テーブル名; |
※複数のカラムを定義するときは「,」で区切って、さらに「カラム名 AS エイリアス」を付け加えていきます。エイリアス名に特殊記号などを使うときは、エイリアス名を「""」で囲みます。それでは、カラム「bang」に「社員番号」、「uria」に「売上」というエイリアスを付け、テーブル「tb」の全レコードを表示してみましょう。
●実行される内容 p113
●操作手順
①次を実行する SELECT bang AS '社員番号',uria AS '売上' FROM tb; |
【実行結果】
mysql> SELECT bang AS '社員番号',uria AS '売上' FROM tb; +----------+------+ | 社員番号 | 売上 | +----------+------+ | A103 | 101 | | A102 | 54 | | A104 | 181 | | A101 | 184 | | A103 | 17 | | A101 | 300 | | A102 | 205 | | A104 | 93 | | A103 | 12 | | A107 | 87 | +----------+------+ 10 rows in set (0.01 sec) mysql> |
(2)《計算したり、文字列を処理したりして表示する》
(2-1)カラムの値で計算して表示
●実行される内容 p115_2
●操作手順
①次を実行する SELECT uria*10000 as '売上' FROM tb; mysql> SELECT uria*10000 as '売上' FROM tb; +---------+ | 売上 | +---------+ | 1010000 | | 540000 | | 1810000 | | 1840000 | | 170000 | | 3000000 | | 2050000 | | 930000 | | 120000 | | 870000 | +---------+ 10 rows in set (0.00 sec) mysql> |
(2-2)関数を使って計算する
●実行される内容 p117
●操作手順
①次を実行する SELECT AVG(uria) FROM tb; |
■実行結果
mysql> SELECT AVG(uria) FROM tb; +-----------+ | AVG(uria) | +-----------+ | 123.4000 | +-----------+ 1 row in set (0.06 sec) mysql> |
●合計を表示する
①次を実行する SELECT SUM(uria) FROM tb; |
■実行結果
mysql> SELECT SUM(uria) FROM tb; +-----------+ | SUM(uria) | +-----------+ | 1234 | +-----------+ 1 row in set (0.00 sec) mysql> |
●個数を表示する
①次を実行する SELECT COUNT(uria) FROM tb; |
■実行結果
mysql> SELECT COUNT(uria) FROM tb; +-------------+ | COUNT(uria) | +-------------+ | 10 | +-------------+ 1 row in set (0.00 sec) mysql> |
(2-3)各種情報を表示する関数(p119/省略)
※
SELECT PI();■実行結果
mysql> SELECT PI(); +----------+ | PI() | +----------+ | 3.141593 | +----------+ 1 row in set (0.02 sec) mysql> |
●各種関数の例
①MySQLサーバのバージョンを表示 SELECT VERSION(); ②現在使っているデータベースを表示 SELECT DATABASE(); ③現在のユーザーを表示 SELECT USER(); ④引数で指定した文字の文字コード(文字セット)を表示 SELECT CHARSET('この文字'); |
■実行結果
①mysql> SELECT VERSION(); +---------------------+ | VERSION() | +---------------------+ | 5.0.45-community-nt | +---------------------+ 1 row in set (0.00 sec) ②mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec) ③mysql> SELECT USER(); +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) ④mysql> SELECT CHARSET('この文字'); +---------------------+ | CHARSET('この文字') | +---------------------+ | latin1 | +---------------------+ 1 row in set (0.00 sec) mysql> |
(2-4)文字列を結合する(p120/省略)
●実行される内容 p121
●操作手順
①次を実行する SELECT CONCAT(bang,nama,'さん') FROM tb1; |
□操作結果
mysql> SELECT CONCAT(bang,nama,'さん') FROM tb1; +--------------------------+ | CONCAT(bang,nama,'さん') | +--------------------------+ | A101佐藤さん | | A101佐藤さん | | A102高橋さん | | A103中川さん | | A104渡辺さん | | A105西沢さん | +--------------------------+ 6 rows in set (0.08 sec) mysql> |
(2-5)文字列操作で活躍する関数(p122/省略)
※
●各種関数の例
①右から取り出す:RIGHT関数 ※次は、カラム「bang」の右から2文字を表示 SELECT RIGHT(bang,2) FROM tb1; ②左から取り出す:LEFT関数 ※次は、カラム「bang」の左から2文字を表示 SELECT LEFT(bang,2) FROM tb1; ③x番号から△個取り出す:SUBSTRING関数 ※次は、カラム「bang」の2文字目から3文字文を表示 SELECT SUBSTRING(bang,2,3) FROM tb1; ④繰り返して表示:REPEAT関数 ※次は、「♪」の文字をカラム「tosi」の回数だけ繰り返して表示 SELECT REPEAT('♪',tosi) FROM tb1 ; ⑤逆さから表示:REVERSE関数 ※次は、カラム「nama」の文字列を逆さに表示 SELECT REVERSE(bang) FROM tb1 ; |
●操作結果
①mysql> SELECT RIGHT(bang,2) FROM tb1; +---------------+ | RIGHT(bang,2) | +---------------+ | 01 | | 01 | | 02 | | 03 | | 04 | | 05 | +---------------+ 6 rows in set (0.00 sec) ②mysql> SELECT LEFT(bang,2) FROM tb1; +--------------+ | LEFT(bang,2) | +--------------+ | A1 | | A1 | | A1 | | A1 | | A1 | | A1 | +--------------+ 6 rows in set (0.01 sec) ③mysql> SELECT SUBSTRING(bang,2,3) FROM tb1; +---------------------+ | SUBSTRING(bang,2,3) | +---------------------+ | 101 | | 101 | | 102 | | 103 | | 104 | | 105 | +---------------------+ 6 rows in set (0.00 sec) ④mysql> SELECT REPEAT('♪',tosi) FROM tb1 ; +------------------------------------------------------------------------------- ---+ | REPEAT('♪',tosi) | +------------------------------------------------------------------------------- ---+ | ♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪ ♪ | | ♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪ ♪ | | ♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪| | ♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪| | ♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪| | ♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪♪| +------------------------------------------------------------------------------- ---+ 6 rows in set (0.15 sec) ⑤mysql> SELECT REVERSE(bang) FROM tb1 ; +---------------+ | REVERSE(bang) | +---------------+ | 101A | | 101A | | 201A | | 301A | | 401A | | 501A | +---------------+ 6 rows in set (0.00 sec) mysql> |
(2-6)日付・時刻の関数
※
●実行される内容 p124
●操作手順
①次を実行する CREATE TABLE ima (a INT AUTO_INCREMENT PRIMARY KEY,b DATETIME); ②次を実行する INSERT INTO ima (b) VALUES(NOW()); ③②を4回繰り返す ④SELECT * FROM ima; |
【実行結果】
mysql> CREATE TABLE ima (a INT AUTO_INCREMENT PRIMARY KEY,b DATETIME); Query OK, 0 rows affected (0.16 sec) mysql> INSERT INTO ima (b) VALUES(NOW()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO ima (b) VALUES(NOW()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO ima (b) VALUES(NOW()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO ima (b) VALUES(NOW()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO ima (b) VALUES(NOW()); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ima; +---+---------------------+ | a | b | +---+---------------------+ | 1 | 2010-03-21 10:15:52 | | 2 | 2010-03-21 10:16:12 | | 3 | 2010-03-21 10:16:17 | | 4 | 2010-03-21 10:16:26 | | 5 | 2010-03-21 10:16:30 | +---+---------------------+ 5 rows in set (0.00 sec) mysql> |
(3)《条件を指定した選択》
※
(3-1)レコード数を決めて表示する
●書式⇒表示するレコード数を限定する
SELECT カラム名 FROM テーブル名 LTMIT 表示するレコード数; |
●実行される内容 p126
●操作手順
次を実行する SELECT * FROM tb LIMIT 3; |
【実行結果】
mysql> SELECT * FROM tb LIMIT 3; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 101 | 4 | | A102 | 54 | 5 | | A104 | 181 | 4 | +------+------+------+ 3 rows in set (0.00 sec) mysql> |
(3-2)WHEREを使った抽出
WHEREを使うことで、条件を設定し、それに一致したレコードだけを取り出すことができます。 データの上手な抽出は、WHEREの使い方次第ともいえます。例えば削除の「」や、更新の「」は、単純に実行すれば、すべてのレコードが削除・更新されてしまいます。これらにWHEREで条件を設定して、必要なレコードだけを対象とすることができます。ある条件の一致するレコードだけに絞るときには、次のようにしてWHEREを使います。
●書式⇒条件に一致したレコードだけを表示する
SELECT カラム名 FROM テーブル名 WHERE 条件; |
●実行される内容 p128
●操作手順
①「uria」が100以上のレコードを表示する SELECT * FROM tb WHERE uria>=100; |
■実行結果
mysql> SELECT * FROM tb WHERE uria>=100; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 101 | 4 | | A104 | 181 | 4 | | A101 | 184 | 4 | | A101 | 300 | 5 | | A102 | 205 | 6 | +------+------+------+ 5 rows in set (0.14 sec) mysql> |
(3-3)比較演算子
※
●比較演算子 p129
※ |
●操作手順
①「uria」が50より小さい SELECT * FROM tb WHERE uria<50; ②カラム「tuki」が4以外 SELECT * FROM tb WHERE tuki<>4; ③カラム「」が50と100の間にある(50以上、100以下) SELECT * FROM tb WHERE uria BETWEEN 50 AND 100; ④カラム「」が50と200の間にない(50未満、または200を超える) SELECT * FROM tb WHERE uria NOT BETWEEN 50 AND 200; ⑤カラム「」が5か6のどちらか SELECT * FROM tb WHERE tuki IN (5,6); |
■実行結果
①mysql> SELECT * FROM tb WHERE uria<50; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 17 | 5 | | A103 | 12 | 6 | +------+------+------+ 2 rows in set (0.44 sec) ②mysql> SELECT * FROM tb WHERE tuki<>4; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A102 | 54 | 5 | | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A104 | 93 | 5 | | A103 | 12 | 6 | | A107 | 87 | 6 | +------+------+------+ 7 rows in set (0.00 sec) ③mysql> SELECT * FROM tb WHERE uria BETWEEN 50 AND 100; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A102 | 54 | 5 | | A104 | 93 | 5 | | A107 | 87 | 6 | +------+------+------+ 3 rows in set (0.13 sec) ④mysql> SELECT * FROM tb WHERE uria NOT BETWEEN 50 AND 200; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A103 | 12 | 6 | +------+------+------+ 4 rows in set (0.01 sec) ⑤mysql> SELECT * FROM tb WHERE tuki IN (5,6); +------+------+------+ | bang | uria | tuki | +------+------+------+ | A102 | 54 | 5 | | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A104 | 93 | 5 | | A103 | 12 | 6 | | A107 | 87 | 6 | +------+------+------+ 7 rows in set (0.00 sec) mysql> |
(3-4)文字列を使った条件(p130/省略)
※
SELECT * FROM tb WHERE bang='A101';
●LIKE:あいまい検索(p131/省略)
※ SELECT * FROM tb WHERE bang LIKE '%1'; |
●実行される内容 p132
●操作手順
①次を実行する SELECT * FROM tb1 WHERE nama LIKE '%川%'; |
【実行結果】
mysql> SELECT * FROM tb1 WHERE nama LIKE '%川%'; +------+------+------+ | bang | nama | tosi | +------+------+------+ | A103 | 中川 | 20 | +------+------+------+ 1 row in set (0.17 sec) mysql> ※ |
●指定した文字列を含まないものを取り出す(p133/省略)
※・・・ SELECT * FROM tb1 WHERE nama NOT LIKE '佐%'; |
(3-5)NULLを使った条件(p133/省略)
※・・・
INSERT INTO tb1J (nama) VALUES('氏名のみ');
【実行結果】
mysql> CREATE TABLE tb1J SELECT * FROM tb1; Query OK, 6 rows affected (0.56 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> INSERT INTO tb1J (nama) VALUES('氏名のみ'); Query OK, 1 row affected (0.00 sec) mysql> select * from tb1J; +------+----------+------+ | bang | nama | tosi | +------+----------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | | NULL | 氏名のみ | NULL | +------+----------+------+ 7 rows in set (0.00 sec) mysql> |
●NULLの場合(p133/省略)
※・・・ SELECT * FROM tb1J WHERE tosi IS NULL; |
●NULLでない場合(p133/省略)
※・・・ SELECT * FROM tb1J WHERE tosi IS NOT NULL; ※ |
(4)《複数の条件を指定した選択》
「○○であり、かつ××である」を表すAND、「○○であるか、または××」を表すORは、多くのプログラミング言語が持つ「論理演算子」です。このANDやORを組合せて、WHEREなどで設定する複数の条件を使ってみましょう。
(4-1)ANDを使う
①ANDを使う 例えば、「50以上、100以下」の条件で、 「SELECT * FROM tb WHERE uria BETWEEN 50 AND 100;」 を紹介しました。これをANDを使って記述すると次のとおり SELECT * FROM tb uria>=50 AND uria<=100; |
●実行される内容 p136
●操作手順
SELECT * FROM tb WHERE bang LIKE '%1' AND tuki=4; |
【実行結果】
mysql> SELECT * FROM tb WHERE bang LIKE '%1' AND tuki=4; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A101 | 184 | 4 | +------+------+------+ 1 row in set (0.01 sec) mysql> |
(4-2)ORを使う
②ORを使う 今度は「○○であるか、または××である」というような設定です。 このような場合にはORを使います。p130では、「50未満、または200を超える」という例で 「SELECT * WHERE uria NOT BETWEEN 50 AND 200;」を使いました。 ここでも同様に、ORで処理することができます。テーブル「」で、カラム「」が50未満、または200を超えるレコードを表示させてみましょう。 SELECT * FROM tb WHERE uria<50 OR uria>200; |
●実行される内容 p137
●操作手順
①次を実行する SELECT * FROM tb WHERE uria<50 OR uria>200; |
【実行結果】
mysql> SELECT * FROM tb WHERE uria<50 OR uria>200; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A103 | 12 | 6 | +------+------+------+ 4 rows in set (1.01 sec) mysql> |
(4-3)ANDやORを複数使う
③ANDやORを複数使う 「AND」や「OR」の条件は、何重にも設定することができます。 例えば次は、「AND」と「OR」の両方を使って処理する例です。 「bangが'A101'」でなをかつ「tuki4」、あるいは「uriaが200以上」 これは、最初に「200以上の売上と、A101さん」の両方を抽出し、 その中で4月に該当するものだけを改めて抽出するという処理です。 最初に「OR」の部分を優先して処理したいところですが、このままでは「AND」が優先されてしまいます。このような場合は、優先したい処理を()で囲みます。 SELECT * FROM tb WHERE (uria>=200 or bang LIKE '%1') AND tuki=4; |
【実行結果】
mysql> SELECT * FROM tb WHERE (uria>=200 or bang LIKE '%1') AND tuki=4; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A101 | 184 | 4 | +------+------+------+ 1 row in set (0.07 sec) mysql> |
(4-4)CASE WHENを使う
※
④CASE WHENを使う 「条件によって入力する値を変化させる」というやや高度な方法があります。 例えば、80点以上なら「優」、60以上なら「良」、40点以上なら「可」、それ以外なら「不可」を入力する、というような処理です。 この場合はCASE WHENを使います。 |
●書式⇒条件によって値を変えて表示
CASE WHEN 条件1 THEN 表示する値 WHEN 条件2 THEN 表示する値 WHEN 条件3 THEN 表示する値 ・・・ ELES すべての条件に当てはまらないときの値 END 例:売上(uria)が100以上の場合「多い」、50以上なら「中ぐらい」、そうでなければ「少ない」とする 。 ○これを表示するSQL文は次のようになります。 SELECT CASE WHEN uria>=100 THEN '多い' WHEN uria>=50 THEN '中ぐらい' ELSE '少ない' END FROM tb; 「CASE~END」に「評価」のエイリアスを付けてみます。また、ただ評価だけ表示しても、それが何のレコードだかわかりません。そこで、カラム「bang」と「uria」も表示します。 |
【実行結果】
mysql> SELECT -> CASE -> WHEN uria>=100 THEN '多い' -> WHEN uria>=50 THEN '中ぐらい' -> ELSE '少ない' -> END -> FROM tb; +------------------------------------------------------------------------------- --+ | CASE WHEN uria>=100 THEN '多い' WHEN uria>=50 THEN '中ぐらい' ELSE '少ない' END | +------------------------------------------------------------------------------- --+ | 多い | | 中ぐらい| | 多い| | 多い| | 少ない| | 多い| | 多い| | 中ぐらい| | 少ない| | 中ぐらい| +------------------------------------------------------------------------------- --+ 10 rows in set (0.18 sec) mysql> ※・・・ |
●実行される内容 p141
●操作手順
次を実行する SELECT bang,uria, CASE WHEN uria>=100 THEN '多い' WHEN uria>=50 THEN '中ぐらい' ELSE '少ない' END AS '評価' FROM tb; |
【実行結果】
mysql> SELECT bang,uria, -> CASE -> WHEN uria>=100 THEN '多い' -> WHEN uria>=50 THEN '中ぐらい' -> ELSE '少ない' -> END AS '評価' -> FROM tb; +------+------+----------+ | bang | uria | 評価 | +------+------+----------+ | A103 | 101 | 多い | | A102 | 54 | 中ぐらい | | A104 | 181 | 多い | | A101 | 184 | 多い | | A103 | 17 | 少ない | | A101 | 300 | 多い | | A102 | 205 | 多い | | A104 | 93 | 中ぐらい | | A103 | 12 | 少ない | | A107 | 87 | 中ぐらい | +------+------+----------+ 10 rows in set (0.02 sec) mysql> これを一行で書くと SELECT bang,uria,CASE WHEN uria>=100 THEN '多い' WHEN uria>=50 THEN '中ぐらい' ELSE '少ない' END AS '評価' FMOM tb; 改行やインデントを付けた方が見やすい。 |
(5)《並べ替え》
(5-1)レコードを昇順に表示させる
「SELECT」によって表示されたレコードの順番は不規則です。
レコードを指定したカラムの値の順に表示させるときは、ORDERを使います。
●書式⇒レコードを昇順に表示させる
SELECT カラム名 FROM テーブル名 ORDER BY キーとなるカラム; この場合は、小さいものから大きものへ、つまり昇順に並べ替えられます。例えば、テーブル「tb」のうち売上「uria」の昇順にレコードを表示させる |
●実行される内容 p144
●操作手順
次を実行する SELECT * FROM tb ORDER BY uria; |
■実行結果
mysql> SELECT * FROM tb ORDER BY uria; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A103 | 12 | 6 | | A103 | 17 | 5 | | A102 | 54 | 5 | | A107 | 87 | 6 | | A104 | 93 | 5 | | A103 | 101 | 4 | | A104 | 181 | 4 | | A101 | 184 | 4 | | A102 | 205 | 6 | | A101 | 300 | 5 | +------+------+------+ 10 rows in set (0.12 sec) mysql> 何も指定しないと昇順になりますが、昇順であることを明示するときは次のようにASCをつけます。結果は同じです。 SELECT * FROM tb ORDER BY uria ASC; |
(5-2)降順に並べ替えて表示する
降順に並べ替えるときは、DDSEを付けます。降順にするときは、必ず「DESC」をつける。
「DESC」や「ASC」は、「ORDER BY カラム名」の後に記述することに注意する。
※売上情報テーブル「tb」で、並べ替えとレコード数の両方を設定して表示してみます。テーブル「tb」で、カラム「uria」の値の多いものから順に5レコードを表示させてみよう。
●実行される内容 p145
●操作手順
次を実行する SELECT * FROM tb ORDER BY uria DESC LIMIT 5; |
【実行結果】
mysql> SELECT * FROM tb ORDER BY uria DESC LIMIT 5; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A101 | 300 | 5 | | A102 | 205 | 6 | | A101 | 184 | 4 | | A104 | 181 | 4 | | A103 | 101 | 4 | +------+------+------+ 5 rows in set (0.48 sec) mysql> |
(5-3)表示するレコードの範囲を決めるp146
順番に表示する場合は、例えば「3番目から7番目まで」というような表示ができると便利。「LIMIT」を使うことで表示するレコード数を制限しました。P126の例では単純に10件を選んだだけでしたが、OFFSETを使うと、さらに、表示の範囲を限定することができます。
●書式⇒範囲を決めて表示する
SELECT カラム名 FROM テーブル名 LIMIT 表示するレコード数 OFFSET表示する開始レコードのシフト数; ※「表示開始レコードのシフト数」というのは、「何個移動したところから表示させるか」を指定する数です。例えば「OFFSET 3」とすれば、「本来であれば1番から表示させるところを、3個後の4レコード目から表示」ということになります。 中途半端ですが、売上情報テーブル「tb」のうち、売上(カラム「uria」)の多い順に4番目と5番目の2レコードを表示させてみましよう。 |
●実行される内容 p147
●操作手順
次を実行する SELECT * FROM tb ORDER BY uria DESC LIMIT 2 OFFSET 3; |
●【実行結果
mysql> SELECT * FROM tb ORDER BY uria DESC LIMIT 2 OFFSET 3; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' LIM IT 2 OFFSET 3' at line 1 mysql> ※エラー? |
(6)《グループごとに表示する》
(6-1)グループごとに表示する
テーブル「tb」において、カラム「bang」が同じ「A101」であるレコードは複数あります。
このように、同じ「A101」が入っているレコードを、グループにすることができます。「グループ」として扱うことで、そのグループのレコードだけの合計や平均をはじめ、グループ単位での処理が可能になります。なお、常に対象が何であるか意識しておく必要があります。
グループ化を行なうときは、GROUP BYを使います。
●書式⇒グループごとに表示する SELECT カラム名 FROM テーブル名 GROUP BY グループ化するカラム名; ※SELECT * FROM tb GROUP BY bang; この命令であれば、社員番号である「」で1種類ずつ表示されます。これではあまり意味がありません。処理の対象は、「同じグループに所属するすべてのレコード」です。 |
●【実行結果
mysql> SELECT * FROM tb GROUP BY bang; +------+------+------+ | bang | uria | tuki | +------+------+------+ | A101 | 184 | 4 | | A102 | 54 | 5 | | A103 | 101 | 4 | | A104 | 181 | 4 | | A107 | 87 | 6 | +------+------+------+ 5 rows in set (0.50 sec) mysql> |
(6-2)グループごとの数を数える》
今度はカラム「bang」ごと、つまり社員番号ごとに何件のレコードがあるかを調べてみましょう。個数を調べるには「COUNT」関数です。
SELECT COUNT(*) FROM tb GROUP BY bang;
カラム「bang」ごとに存在するレコードの数が表示されました。でもこれでは、何の数が表示されたかよくわかりません。そこで次のように変更します。
SELECT bang,count(*) AS '件数' FROM tb GROUP BY bang; 表示するのは、グループごとのカラム「」と「」の値、さらにわかりやすくするために「件数」というエイリヤスも設定しました。 |
●【実行結果
mysql> SELECT COUNT(*) FROM tb GROUP BY bang; +----------+ | COUNT(*) | +----------+ | 2 | | 2 | | 3 | | 2 | | 1 | +----------+ 5 rows in set (0.58 sec) mysql> SELECT -> bang,count(*) AS '件数' -> FROM tb -> GROUP BY bang; +------+------+ | bang | 件数 | +------+------+ | A101 | 2 | | A102 | 2 | | A103 | 3 | | A104 | 2 | | A107 | 1 | +------+------+ 5 rows in set (0.01 sec) mysql> ※・・・ |
(6-3)グループごとの合計・平均を表示する
今度は、社員ごとの売上の合計を出してみます。
テーブル「tb」で、カラム「bang」ごとのカラム「uria」の合計を表示しましょう。ただし、カラム「bang」には「合計」のエイリヤスを付けます。
●実行される内容 p151
●操作手順 ①次を実行する SELECT bang,SUM(uria) AS '合計' FROM tb GROUP BY bang; ②社員1人ずつの売上合計を表示しています。 ※次は同じ様にカラム「uria」の平均を、「bang」でグループ化して計算する例です。 SELECT bang,AVG(uria) FROM tb GROUP BY bang; |
●【実行結果
mysql> SELECT -> bang,SUM(uria) AS '合計' -> FROM tb -> GROUP BY bang; +------+------+ | bang | 合計 | +------+------+ | A101 | 484 | | A102 | 259 | | A103 | 130 | | A104 | 274 | | A107 | 87 | +------+------+ 5 rows in set (0.14 sec) mysql> SELECT -> bang,AVG(uria) -> FROM tb -> GROUP BY bang; +------+-----------+ | bang | AVG(uria) | +------+-----------+ | A101 | 242.0000 | | A102 | 129.5000 | | A103 | 43.3333 | | A104 | 137.0000 | | A107 | 87.0000 | +------+-----------+ 5 rows in set (0.00 sec) mysql> |
(7)《条件付グループで表示p152》
(7-1)グループごとに処理する
「GROUP BY」によるグループ化に、さらに抽出の条件を付けてみましょう。例えば、「売上の合計を社員番号ごとに処理するけれど、表示するのは合計××以上のものだけ」という処理をしたいときは、グループ化した値の抽出条件を設定するHAVINGを使います。
●書式⇒グループに分けて条件を付けて取り出す
SELECT 集計したカラム FROM テーブル名 GROUP BY グループ化するカラム HAVING 条件;
|
●実行される内容 p153
●操作手順 次を実行する SELECT bang,SUM(uria) FROM tb GROUP BY bang HAVING SUM(uria)>=200; 「HAVING」による抽出は、「グループ化した後で抽出」したということです。次の項で扱う「抽出してからグループ化」との違いをここで確認しておきまsひょう。 |
■実行結果
mysql> SELECT -> bang,SUM(uria) -> FROM tb -> GROUP BY bang -> HAVING SUM(uria)>=200; +------+-----------+ | bang | SUM(uria) | +------+-----------+ | A101 | 484 | | A102 | 259 | | A104 | 274 | +------+-----------+ 3 rows in set (0.00 sec) 【VING SUM(uria)>=200;】を記述しない例 mysql> SELECT -> bang,SUM(uria) -> FROM tb -> GROUP BY bang; +------+-----------+ | bang | SUM(uria) | +------+-----------+ | A101 | 484 | | A102 | 259 | | A103 | 130 | | A104 | 274 | | A107 | 87 | +------+-----------+ 5 rows in set (0.00 sec) mysql> |
(7-2)抽出してからグループ化する
前項の「グループごとに分けてから抽出」するのに対して、今度は「抽出してからグループ化」します。例えば、「売上が10000以上の取引だけを抽出し、そのグループだけを対象にして社員ごとの売上平均を出す」 という例です。
グループ化の前の抽出、「WHERE」を使って行ないます。異なる場合は、最後に「GROUP BY」でグループ化する点です。
では、「売上(uria)が50以上の取引だけを抽出し、そのコードだけを対象にして社員ごと(bang)の売上平均」を処理してみましょう。
●実行される内容 p155
●操作手順
次を実行する SELECT bang,AVG(uria) FROM tb WHERE uria>=50 GROUP BY bang; |
■実行結果
mysql> SELECT -> bang,AVG(uria) -> FROM tb -> WHERE uria>=50 -> GROUP BY bang; +------+-----------+ | bang | AVG(uria) | +------+-----------+ | A101 | 242.0000 | | A102 | 129.5000 | | A103 | 101.0000 | | A104 | 137.0000 | | A107 | 87.0000 | +------+-----------+ 5 rows in set (0.00 sec) 【すべてのコードを表示】 mysql> SELECT bang,uria FROM tb; +------+------+ | bang | uria | +------+------+ | A103 | 101 | | A102 | 54 | | A104 | 181 | | A101 | 184 | | A103 | 17 | | A101 | 300 | | A102 | 205 | | A104 | 93 | | A103 | 12 | | A107 | 87 | +------+------+ 10 rows in set (0.00 sec) mysql> ※・・・ |
(7-3)グループ化してから並べ替える
今度は、グループ化した結果を並べ替えて表示してみることにしましょう。「GROUP BY・・・」と「ORDER BY・・・」の両方を使ったSELECTになります。
記述する順序は「GROUP BY・・・」が最初、次は「ORDER BY・・・」とする必要があります。テーブル「tb」で、「社員ごとの売上の平均(AVG(uria))を、売上の平均の多いものから順番に表示」してみましょう。具体的には、テーブル「tb」で、カラム「bang」ごとに、カラム「bang」の平均をカラム「uria」の降順に並べ替えて表示します。
●実行される内容 p157
●操作手順
次を実行する SELECT bang,AVG(uria) FROM tb GROUP BY bang ORDER BY AVG(uria) DESC; |
●操作手順
mysql> SELECT -> bang,AVG(uria) -> FROM tb -> GROUP BY bang -> ORDER BY AVG(uria) -> DESC; +------+-----------+ | bang | AVG(uria) | +------+-----------+ | A101 | 242.0000 | | A104 | 137.0000 | | A102 | 129.5000 | | A107 | 87.0000 | | A103 | 43.3333 | +------+-----------+ 5 rows in set (0.00 sec) mysql> |
(7-4)グループ化手順のまとめ>
ここまで扱った、グループ化で条件を設定する手順をまとめておきます。
①抽出してからグループ化に分ける
②グループごとに分けてから抽出する
①と②の手順を組み合わせた処理を行なってみましょう。売上が50以上のデータだけを対象にして、社員「bang」ごとの売上の平均(AVG(uria))を、売上の平均の多いものから順番に表示しましょう。
●実行される内容 p159
●操作手順
SELECT bang,AVG(uria) FROM tb WHERE uria>=50 GROUP BY bang ORDER BY AVG(uria) DESC; |
【実行結果】
mysql> SELECT -> bang,AVG(uria) -> FROM tb -> WHERE uria>=50 -> GROUP BY bang -> ORDER BY AVG(uria) -> DESC; +------+-----------+ | bang | AVG(uria) | +------+-----------+ | A101 | 242.0000 | | A104 | 137.0000 | | A102 | 129.5000 | | A103 | 101.0000 | | A107 | 87.0000 | +------+-----------+ 5 rows in set (0.00 sec) mysql> ※・・・ |
<まとめ>
●条件を設定して抽出する方法
●関数を利用して抽出
●指定した条件で並べ替えて表示する方法
●表示するレコード数を限定して抽出する方法
●複数の条件を設定して抽出する方法
●グループごとに表示する方法
<チェック>
□WHEREで条件を設定してSELECTできる
□LIMITでレコード数を制限してSELECTできる
□「ORDER BY…(DESC)」で昇順(降順)にSELECTできる
□「GROUP BY」でグループごとにSELECTできる
□WHEREやHAVINGで条件をつけて「GROUP BY」でグループごとにSELECTできる
□「WHERE」「GROUP BY」「ORDER BY」を記述する順番を理解している
<練習問題>
問題1
テーブル「tb」の「uria」の合計値の前後に「合計は」「万円です」の文字列を付けて表示して下さい。 なお、表示する項目名には「売上」のエイリアスを付けること。
問題2
次のテーブル「tb」で、カラム「uria」が50以上で、「bang」ごとの「uria」の平均で120以上のものだけを、「」の平均の多いものから表示して下さい。
テーブル p162
解答1
SELECT CONCAT('合計',SUM(uria),'万円です')AS '売上' FROM tb;
mysql> SELECT CONCAT('合計',SUM(uria),'万円です')AS '売上' FROM tb; +------------------+ | 売上 | +------------------+ | 合計1234万円です | +------------------+ 1 row in set (0.44 sec) mysql> |
解答2
SELECT bang,AVG(uria)
FROM tb
WHERE uria>=50
GROUP BY bang
HAVING AVG(uria)>=120
ORDER BY AVG(uria) DESC;
mysql> SELECT bang,AVG(uria) -> FROM tb -> WHERE uria>=50 -> GROUP BY bang -> HAVING AVG(uria)>=120 -> ORDER BY AVG(uria) DESC; +------+-----------+ | bang | AVG(uria) | +------+-----------+ | A101 | 242.0000 | | A104 | 137.0000 | | A102 | 129.5000 | +------+-----------+ 3 rows in set (0.01 sec) mysql> |