いろいろな条件で抽出する

テーブル「tb」の準備

★

USE db1;

CREATE TABLE tb (bang VARCHAR(10), uria INT, tuki INT)
CHARSET=utf8;

INSERT INTO tb(bang, uria, tuki) VALUES
('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);

カラムを入れ替えて表示する

★

SELECT uria, bang FROM tb;

カラム名をエイリアスにする

SELECT カラム名 AS エイリアス FROM テーブル名;

★

SELECT bang AS 社員番号, uria AS 売上 FROM tb;

カラムの値で計算して表示する

★

SELECT uria*10000 as 売上 FROM tb;

割るとき

SELECT a/b FROM テーブル名;

足すとき

SELECT a+b FROM テーブル名;

関数を使って計算する

★

SELECT AVG(uria) FROM tb;

合計を表示する

★

SELECT SUM(uria) FROM tb;

個数を表示する

★

SELECT COUNT(uria) FROM tb;

各種情報を表示する関数

★

SELECT PI();

MySQLのバージョンを表示

★

SELECT VERSION();

現在使っているデータベースを表示

★

SELECT DATABASE();

現在のユーザーを表示

★

SELECT USER();

引数で指定した文字の文字コード(文字セット)を表示

★

SELECT CHARSET('この文字');

文字列を結合する

★

SELECT CONCAT(bang,nama,'さん') FROM tb1;

右から取り出す:RIGHT関数

★

SELECT RIGHT(bang,2) FROM tb1;

左から取り出す:LEFT関数

★

SELECT LEFT(bang,2) FROM tb1;

×番目から△個取り出す:SUBSTRING関数

★

SELECT SUBSTRING(bang,2,3) FROM tb1;

繰り返して表示:REPEAT関数

★

SELECT REPEAT('.',tosi) FROM tb1;

逆から表示:REVERSE関数

★

SELECT REVERSE(nama) FROM tb1;

日付・時刻の関数

★

CREATE TABLE ima (a INT AUTO_INCREMENT PRIMARY KEY, b DATETIME);

INSERT INTO ima (b) VALUES(NOW());
INSERT INTO ima (b) VALUES(NOW());
INSERT INTO ima (b) VALUES(NOW());
INSERT INTO ima (b) VALUES(NOW());

SELECT * FROM ima;

表示するレコード数を限定する

SELECT カラム名 FROM テーブル名 LIMIT 表示するレコード数;

★

SELECT * FROM tb LIMIT 3;

条件に一致したレコードだけ表示する

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

★

SELECT * FROM tb WHERE uria>=100;

比較演算子

カラム「uria」が50より小さい

★

SELECT * FROM tb WHERE uria<50;

★

カラム「tuki」が4以外

★

SELECT * FROM tb WHERE tuki<>4;

カラム「uria」が50と100の間にある（50以上、100以下）

★

SELECT * FROM tb WHERE uria BETWEEN 50 AND 100;

カラム「uria」が50と200の間にない（50未満、または200を超える）

★

SELECT * FROM tb WHERE uria NOT BETWEEN 50 AND 200;

カラム「tuki」が5か6のどちらか

★

SELECT * FROM tb WHERE tuki IN (5,6);

文字列を使った条件

★

SELECT * FROM tb WHERE bang='A101';

LIKE：あいまい検索

★

SELECT * FROM tb WHERE bang LIKE 'A101';

★

SELECT * FROM tb WHERE bang LIKE '%1';

★

SELECT * FROM tb1 WHERE nama LIKE '%川%';

指定した文字列を含まないものを取り出す

★

SELECT * FROM tb1 WHERE nama NOT LIKE '佐%';

NULLを使った条件

★

CREATE TABLE tb1J SELECT * FROM tb1

INSERT INTO tb1J (name) VALUES ('氏名のみ');

NULLの場合

★

SELECT * FROM tb1J WHERE tosi IS NULL;

NULLでない場合

★

SELECT * FROM tb1J WHERE tosi IS NOT NULL;

重複したデータを省いて抽出する

★

SELECT DISTINCT bang FROM tb;

ANDを使う

★

SELECT * FROM tb WHERE uria>=50 AND uria<=100;

SELECT * FROM tb WHERE bang LIKE '%1' AND tuki=4;

ORを使う

★

SELECT * FROM tb WHERE uria<50 OR uria>200;

ANDやORを使う

★

SELECT * FROM tb WHERE bang LIKE '%1' AND tuki=4 OR uria>=200;

SELECT * FROM tb WHERE uria>=200 OR bang LIKE '%1' AND tuki=4;

SELECT * FROM tb WHERE (uria>=200 OR bang LIKE '%1') AND tuki=4;

CASE WHENを使う

CASE
	WHEN 条件1 THEN 表示する値
	WHEN 条件2 THEN 表示する値
	WHEN 条件3 THEN 表示する値
……
ELSE すべての条件に当てはまらないときの値
END

★

SELECT
CASE
	WHEN uria>=100 THEN '多い'
	WHEN uria>=50 THEN '中くらい'
	ELSE '少ない'
END
FROM tb;

★

SELECT bang,uria,
CASE
	WHEN uria>=100 THEN '多い'
	WHEN uria>=50 THEN '中くらい'
	ELSE '少ない'
END AS 評価
FROM tb;

レコードを昇順に表示させる

SELECT カラム名 FROM テーブル名 ORDER BY キーとなるカラム;

★

SELECT * FROM tb ORDER BY uria;

SELECT * FROM tb ORDER BY uria ASC;

降順に並べ替えて表示する

★

SELECT * FROM tb ORDER BY uria DESC LIMIT 5;

表示するレコードの範囲を決める

SELECT カラム名 FROM テーブル名 LIMIT 表示するレコード数 OFFSET 表示開始レコードのシフト数;

★

SELECT * FROM tb ORDER BY uria DESC LIMIT 2 OFFSET 3;

グループごとに表示する

SELECT カラム名 FROM テーブル名 GROUP BY グループ化するカラム名;

★

SELECT * FROM tb GROUP BY bang;

グループごとの数を数えてみる

★

SELECT COUNT(*) FROM tb GROUP BY bang;

SELECT bang, COUNT(*) AS 件数 FROM tb GROUP BY bang;

グループごとの合計・平均を表示する

★

SELECT bang,SUM(uria) AS 合計 FROM tb GROUP BY bang;

SELECT bang,AVG(uria) FROM tb GROUP by bang;

グループごとに分けて条件を付けて取り出す

SELECT 集計したカラム FROM テーブル名 GROUP BY グループ化するカラム HAVING 条件;

★

SELECT bang,sum(uria) FROM tb GROUP BY bang HAVING SUM(uria)>=200;

抽出してからグループ化する

★

SELECT bang,AVG(uria) FROM tb WHERE uria>=50 GROUP BY bang;

グループ化してから並べ替える

★

SELECT bang,AVG(uria) FROM tb GROUP BY bang ORDER BY AVG(uria) DESC;

グループ化手順のまとめ

★

SELECT bang,AVG(uria) FROM tb WHERE uria>=50 GROUP BY bang ORDER BY AVG(uria) DESC;

問

テーブル「tb」の「uria」の合計値の前後に「合計は」「万円です」の文字列を付けて表示してください。なお、表示する項目名には「売上」のエイリアスを付けること。

SELECT CONCAT('合計は',CAST(SUM(uria) AS CHAR),'万円です') AS 売上 FROM tb;

問

次のテーブル「tb」で、カラム「uria」が50以上で、「bang」ごとの「uria」の平均で120以上のものだけを、「uria」の平均の多いものから表示してください。

SELECT bang,AVG(uria) FROM tb WHERE uria>=50 GROUP BY bang HAVING AVG(uria)>=120 ORDER BY AVG(uria) DESC;