LoginSignup
5

More than 5 years have passed since last update.

MySQL便利な小技

Last updated at Posted at 2018-10-22

備忘録

使っていて便利だと思った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に一時的に変換できます。


Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5