MySQLの覚書

GROUP BY と ORDER BYの組み合わせ

集計結果でソートすることが可能である。

集計値であるcountでソートする例

	SELECT
	    DATE_FORMAT(test_date, '%Y-%m') as test_ym,
	    COUNT(id) as count,
	    SUM(sale_amount) as sum_sale_amount,
	FROM
	    shops
	WHERE
	    DATE_FORMAT(test_date, '%Y') = '2015'
	GROUP BY
	    DATE_FORMAT(test_date, '%Y-%m')
	ORDER BY count
	

SELECTに定義していないフィールドでもソートすることが可能だが、 idなど一意のフィールドのみが対象である。
SELECTで定義していないidでソートする例

	SELECT
	    DATE_FORMAT(test_date, '%Y-%m') as test_ym,
	    COUNT(id) as count,
	    SUM(sale_amount) as sum_sale_amount,
	FROM
	    shops
	WHERE
	    DATE_FORMAT(test_date, '%Y') = '2015'
	GROUP BY
	    DATE_FORMAT(test_date, '%Y-%m')
	ORDER BY id
	

IN句にNULLを含めることはできるか？ | IN(NULL)

結論からいうとできない。

× status_id=NULLのデータは取得はできない。(status_id=2または3のデータのみ）

SELECT * FROM animals WHERE status_id IN(2,3,NULL) 

代替手段

SELECT * FROM animals WHERE status_id IN(2,3)  || status_id IS NULL

月別集計

年月別で集計するSQL

	SELECT
	    DATE_FORMAT(test_date, '%Y-%m') as test_ym,
	    COUNT(*) as count,
	    SUM(val1)
	FROM
	    nekos
	GROUP BY
	    DATE_FORMAT(test_date, '%Y-%m')
	

並び替えもできる

	SELECT
	    DATE_FORMAT(test_date, '%Y-%m') as test_ym,
	    COUNT(*) as count,
	    SUM(val1)
	FROM
	    nekos
	GROUP BY
	    DATE_FORMAT(test_date, '%Y-%m')
	ORDER BY count
	

年で集計する場合

	SELECT
	    DATE_FORMAT(test_date, '%Y') as test_y,
	    COUNT(*) as count,
	    SUM(val1)
	FROM
	    nekos
	GROUP BY
	    DATE_FORMAT(test_date, '%Y')
	ORDER BY count
	

0埋めによる桁数固定 | lpad

lpad関数を使って、0埋めによる桁固定ができる。

	select lpad(id,2,'0') from animals;
	

例：3→03


rpad関数は右側を指定文字で埋める。

	select rpad(id,2,'0') from animals;
	

例：3→30

MySQLのパスワード変更（変更によるアクセス拒否対応）

1. phpMyAdminのパスワード変更画面を開く
   任意のデータベースを選択→特権→任意のユーザーの「特権を編集」→上部にある「パスワードを変更する」
2. パスワード変更画面でパスワードを入力

---

---

注意：パスワード変更後、以下のようなメッセージが表示されたら・・・
phpMyAdminのconfig.inc.phpファイルを修正せねばならない。

1. config.inc.phpを開く。
   場所の例→C:\xampp\phpMyAdmin\config.inc.php
2. config.inc.phpファイル内の下記項目のパスワードを変更する。

   	/* Authentication type and info */
   	$cfg['Servers'][$i]['auth_type'] = 'config';
   	$cfg['Servers'][$i]['user'] = 'root';
   	$cfg['Servers'][$i]['password'] = '変更したパスワード';
   	$cfg['Servers'][$i]['extension'] = 'mysqli';
   	$cfg['Servers'][$i]['AllowNoPassword'] = true;
   	$cfg['Lang'] = '';
   			

パスワード設定方法の参照：DBOnline IT技術全般の学習サイト
パスワード設定後の拒否対策：PHPプログラミングの教科書 [php1st.com]

日時フィールドを日付で検索するときの注意点

日時フィールド「created→2015-10-06 16:17:54」を「日付」で検索する場合の注意点

以下のように日付を日時に対して直接指定しても取得できない。

SELECT * FROM animals WHERE created='2015-10-06'

「2015-10-06」は「2015-10-06 00:00:00」と同じであるためである。

日付で日時を検索する場合、以下のようにする。

SELECT * FROM animals WHERE created >= '2015-10-06 00:00:00' AND created <= '2015-10-06 23:59:59'

以下の方法でも可

SELECT * FROM animals WHERE created >= '2015-10-06' AND created < '2015-10-07'

浮動小数floatのフィールドをWHERE検索する方法

DECIMALにCASTしてから検索すればよい。

SELECT * FROM animal WHERE CAST(price AS DECIMAL) = CAST(123.45 AS DECIMAL);

テーブルコピー

テーブルコピーは構造コピーとデータコピーで分ける。

nekosテーブルをkanisテーブルとしてコピーする例

	/* 構造コピー */
	CREATE TABLE kanis LIKE nekos;
	
	/* データコピー */
	INSERT INTO kanis SELECT * FROM nekos;
	

カラムを指定してテーブルコピー

データコピーする際、カラムを指定することができる。

nekosテーブルをkanisテーブルとしてコピーする例

	/* 構造コピー */
	CREATE TABLE kanis LIKE nekos;
	
	/* データコピー */
	INSERT INTO kanis(id, kani_name,value1) SELECT id,neko_name,value1 FROM nekos;
	

2017-2-3

別データベースのテーブルをコピーする

	# 構造コピー
	# parkデータベース・animalsテーブルの構造をpark2データベースへコピーする。
	CREATE table park2.animals LIKE park.animals;
	
	# データコピー
	# parkデータベース・animalsテーブルのデータをpark2のanimalsテーブルへコピーする。
	INSERT into park2.animals SELECT * from park.animals;
	

2018-9-1

MySQL:テーブル一覧を表示

SQL「SHOW TABLES FROM DB名」を実行するとテーブル一覧を表示できる。

SQLの例

DB:cake_demoのテーブル一覧を表示するSQL

SHOW TABLES FROM cake_demo

フィールド一覧を表示

SQL「SHOW FULL COLUMNS FROM テーブル名」を実行すると、 指定したテーブルに属するフィールド一覧を表示できる。

SQLの例

animalsテーブルのフィールド一覧を表示するSQL

SHOW FULL COLUMNS FROM animals

TEST

テスト

xx