備忘録
使っていて便利だと思ったSQL文を忘れないために
特定のカラムを探す
SELECT
*
FROM
information_schema.columns
WHERE
column_name = '探したいカラム名'
and table_schema = 'カラムを探すDB名';
テーブル名で検索
--検索したいワードを含むテーブル
SHOW TABLES like '%検索したいワード%';
--検索したいワードで始まるテーブル
SHOW TABLES like '検索したいワード%';
--検索したいワードで終わるテーブル
SHOW TABLES like '%検索したいワード';
テーブルにカラムを追加
--テーブルの最後尾に追加
ALTER TABLE テーブル名 ADD 追加するカラムの情報;
--テーブルの先頭に追加
ALTER TABLE テーブル名 ADD 追加するカラムの情報 FIRST;
--指定したカラムの後ろに追加
ALTER TABLE テーブル名 ADD 追加するカラムの情報 AFTER カラム名;
指定範囲の更新
UPDATE テーブル名 SET 更新する値 WHERE 範囲設定するカラム名 BETWEEN X AND Y;
--XとYは更新する範囲
複数カラムを一つにまとめる
以下のような,果物の個数を記録するテーブルがあります。
apple_num | peach_num | lemon_num | orange_num |
---|---|---|---|
1 | 0 | 2 | 3 |
2 | 1 | 0 | 0 |
0 | 0 | 0 | 0 |
テーブル名 : fruit
このテーブルをまとめて名前と個数を一つのカラムにして表示するためにCONCATを使い結合します。
また0個の要素は表示しないようにCASE文を使用します。
--結合後のデータを格納するカラムを生成
ALTER TABLE fruit ADD fruit_info varchar(100);
--果物の名前と数を結合する
UPDATE fruit
SET
fruit_info = CONCAT(
CASE apple_num
WHEN 0 THEN ''
ELSE CONCAT('りんご ', apple_num, '個 ')
END,
CASE peach_num
WHEN 0 THEN ''
ELSE CONCAT('もも ', peach_num, '個 ')
END,
CASE lemon_num
WHEN 0 THEN ''
ELSE CONCAT('れもん ', lemon_num, '個 ')
END,
CASE orange_num
WHEN 0 THEN ''
ELSE CONCAT('おれんじ ', orange_num, '個')
END
);
このSQLを実行すると以下のようになります。
apple_num | peach_num | lemon_num | orange_num | fruit_info |
---|---|---|---|---|
1 | 0 | 2 | 3 | りんご 1個 れもん 2個 おれんじ 3個 |
2 | 1 | 0 | 0 | りんご 2個 もも1個 |
0 | 0 | 0 | 0 |
テーブル名 : fruit
改行コードやカンマを付けると見やすくなるかもしれません。
全角・半角区別せずに検索
データ検索をする際に全角・半角を区別せずに検索したい時が多々あります。
その際は,以下のようにすれば区別せずに検索できます。
SELECT
*
FROM
テーブル名
WHERE
vSinseiName collate utf8_unicode_ci like '%検索したいワード%'
collate utf8_unicode_ci
によって大文字・小文字・全角・半角を区別せずに検索するようになります。
しかし,このコードはutf8のデータベースでしか使用できないのでそれ以外のデータベースは文字コードをutf8に変換する必要があります
SELECT
*
FROM
テーブル名
WHERE
vSinseiName convert(namae using utf8) collate utf8_unicode_ci like '%検索したいワード%'
convert(namae using utf8)
によって文字コードをutf8に一時的に変換できます。