MySQLの覚書

phpMyAdminで日本語をINSERTすると文字化け

phpMyAdminで日本語文字をINSERT使用とすると??となり文字化けしてしまう問題を解決する。

・解決方法

1. phpMyAdminでとりあえず文字化けするテーブルを表示する。
2. 構造タブをクリック。
3. 文字列系のフィールドの照合順序をutf32_general_ciに変更する。(マスにチェックを入れ変更リンクを押せば変更可能）

※一度、文字化けしたデータは一旦削除して再投入する必要がある。

MySQLにフィールドを追加する方法

ALTER TABLE テーブル名 ADD カラム名 型情報;

例：test_tblテーブルにtest_noフィールドを追加する場合。

	ALTER TABLE test_tbl ADD test_no int;
	

指定フィールドの後ろに新しいフィールドを挿入する場合。
下記の例ではrecsテーブルのnoteフィールドの後ろにcategory_id1フィールドを追加する場合。

	ALTER TABLE recs ADD category_id1 int AFTER note;
	

応用例

yagisテーブルに新フィールドを追加する例

int型

ALTER TABLE `yagis` ADD `test_value` INT NULL DEFAULT '0' COMMENT 'テスト数値' AFTER `modified`;

varchar型

ALTER TABLE `yagis` ADD `test_name` VARCHAR(256) CHARACTER SET utf16 COLLATE utf8mb4_general_ci NULL COMMENT 'テスト名' AFTER `test_value`;

tinyint型

ALTER TABLE `yagis` ADD `test_flg` TINYINT NULL DEFAULT '0' COMMENT 'test_flg' AFTER `test_name`;

double型

ALTER TABLE `yagis` ADD `test_amt` DOUBLE NULL DEFAULT '0' COMMENT 'テスト金額' AFTER `test_flg`;

date型

ALTER TABLE `yagis` ADD `test_date` DATE NULL COMMENT 'テスト日付' AFTER `test_amt`;

datetime型

ALTER TABLE `yagis` ADD `datetime` DATETIME NULL COMMENT 'テスト日時' AFTER `test_date`;

text型

	ALTER TABLE `yagis` ADD `test_text` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'テストテキスト' AFTER `datetime`;

2013-9-3 | 2021-8-15

集計と演算とソートを組みわせる

集計フィールドを演算し、さらにその値で並び替える。

	select
	    aa_id,
	    sum (sales) as sum_sales,
	    sum (imp) as sum_imp,
	    (sum (sales) / sum (imp)) as xxx
	from
	    sales
	group by
	    aa_id
	order by
	    xxx desc
	

フィールドを削除する方法

以下の例ではrecsテーブルからcreate_dateフィールドを削除する。

	ALTER TABLE recs DROP create_date
	

グループの中の最大値のデータを取得

シンプルなSQLではできない模様。 参考サイト

集計値に絞込をかける。～HAVING

グルーピングによる集計値に対して絞込ができる。
※WHEREでは集計値に絞込はできない。
※HAVINGは集計値以外のカラムに対しても絞込ができるが、無駄に時間かかるため、通常のカラムに対してはWHEREを使うべき。

	SELECT
	    anim_id,COUNT (id) as cnt,
	    anim_id
	FROM
	    animals
	GROUP BY
	    anim_id
	HAVING cnt > 1
	

ダメな例

	SELECT
	    COUNT (id) as cnt,
	    anim_id
	FROM
	    animals
	WHERE COUNT (id) > 1
	GROUP BY
	    anim_id
	

Null及び空文字の検索

Nullと空文字は扱いが異なる。


NULLで検索

SELECT * FROM tests WHERE animal IS NULL

NULL以外で検索

SELECT * FROM tests WHERE animal NOT IS NULL

空文字で検索

SELECT * FROM tests WHERE animal = ''

空文字以外で検索

SELECT * FROM tests WHERE animal ！= ''

NULLもしくは空文字で検索

SELECT * FROM tests WHERE (animal IS NULL OR animal = '')

NULL以外かつ空文字以外

SELECT * FROM tests WHERE animal IS NOT NULL AND animal != ''

WHEREのIF文？

IF文はないが、代わりとなる記述方法はある。

titleが空でないならflg_a=3で検索、
titleが空ならflg_a = 4で検索する場合、
以下のSQLのようになる。

	SELECT * FROM tests WHERE
	
	(
		(title IS NOT NULL AND flg_a = 3)
		OR
		(title IS NULL AND flg_a = 4)
	)
	

	

リストで比較 ― IN句とNOT IN句の使い方

idのリスト「1,4,6」で比較し、一致するデータを取得する。

	SELECT * FROM nekos WHERE id IN (1,4,6);
	

idのリスト「1,4,6」以外で比較し、リストに一致しないデータをすべて取得する。

	SELECT * FROM nekos WHERE id NOT IN (1,4,6);
	

TEST

テスト