String

Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 15:10:04 | 最終更新:2015-11-20 (金) 15:17:40

面白い関数もあったよ~♪

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.5 String Functions
 
文字列処理関数。

REPLACE

REPLACE

書式
  1. REPLACE(str,from_str,to_str)

第1引数:対象文字列
第2引数:検索文字列
第3引数:置換文字列

第1引数の対象文字列には、もちろん直接文字列を指定してもいいんだけど
要はデータを扱いたいので、そこにフィールド名を指定する。
フィールド名の場合は、”” は無し。

  1. UPDATE sample SET name=REPLACE(name, 'Tanaka', 'Yamada');
Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 15:10:04 | 最終更新:2015-11-20 (金) 15:17:40

CASE

Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 14:39:18 | 最終更新:2016-01-16 (土) 23:52:17


いわゆる CASE 文。
こんなこともできるなんてね~。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.6.5.1 CASE Syntax

書式

THEN の後ろの値が戻ってくる。

  1. CASE case_value
  2.     WHEN when_value THEN statement_list
  3.     [WHEN when_value THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE sex
  9.     WHEN 1 THEN '男'
  10.     WHEN 2 THEN '女'
  11.     ELSE 'その他'
  12. END
条件式
  1. CASE
  2.     WHEN search_condition THEN statement_list
  3.     [WHEN search_condition THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE
  9.     WHEN sex = 1 THEN '男'
  10.     WHEN sex = 2 THEN '女'
  11.     ELSE 'その他'
  12. END
  13.  
  14. // 不等号はもちろん、IN 句も使える
  15. CASE
  16.     WHEN age IN (13, 14, 15) THEN '中学生'
  17.     WHEN age IN (16, 17, 18) THEN '高校生'
  18.     ELSE 'その他'
  19. END
利用
SELECT

散らばった値を集約して表示できる。

点数に応じて振り分け

80点を超えた人は合格。
60点を超えた人は再テスト。
それ以外の人は不合格。

  1. SELECT
  2.     name, result,
  3.     CASE
  4.         WHEN result > 80 THEN 'Pass'
  5.         WHEN result > 60 THEN 'ReTest'
  6.         ELSE 'Fail'
  7.     END as judgment
  8. FROM test;
集約関数併用
  1. SELECT  dept_nbr,
  2.     COUNT(CASE WHEN sex = 1 THEN 1 ELSE 0 END), // 男の人数
  3.     COUNT(CASE WHEN sex = 2 THEN 1 ELSE 0 END)  // 女の人数
  4. FROM Table_A
  5. GROUP BY dept_nbr;
GROUP BY

散らばった値を集約できる。
ただし、SELECT にも同内容の指定をすること。

豪快に GROUP BY句にも SELECT句の CASE式をコピーしてやるのがポイントです。
単純に「GROUP BY pre_name」と変換前の列を指定すると、
正しい結果が得られませんので注意してください。

ミックのページ:CASE式のススメ

ORDER BY

任意のソート順を設定できる。

CHECK 制約との併用

CASE式は CHECK制約と非常に相性が良いのです。
実際に CASE式を使う局面の半分は CHECK制約の中ではないかと思うほどです。

ミックのページ:CASE式のススメ

と、あるけど、まだ理解できていない。

複数カラムの最大値を返す

ミックのページ:CASE式のススメ 参照。

参考サイト

DBOnline:カラムの値に応じて異なる結果を返す条件式の記述

サンプルコードによるPerl入門:SQLのhaving句はcase式で書くと直感的に記述できる(2011-04-13)

DN-Web64:MySQL : CASE文で複雑な条件を指定
→簡潔で読みやすい。SELECT, GROUP BY, ORDER BY 別にサンプルあり

ミックのページ:CASE式のススメ
→いつもお世話になっております。相変わらずの詳細な解説をありがとうございます。

Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 14:39:18 | 最終更新:2016-01-16 (土) 23:52:17

TRUNCATE

Posted by muchag | MySQL |
初回投稿:2015-09-28 (月) 18:37:18 | 最終更新:2015-09-28 (月) 18:38:49

データの全削除。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
書式
  1. TRUNCATE TABLE tbl_name;
リレーション付きの際のエラー

当該テーブルにリレーションを貼ってあると下記のようなエラーが出る。

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

こういうときは、リレーションに対するチェック機能を一旦 OFF に。

  1. SET foreign_key_checks = 0;
  2. TRUNCATE TABLE table_name;
  3. SET foreign_key_checks = 1;
参考サイト

ミラボ:mysqlでrelation貼ってあるテーブルに対してtruncate tableを強制的に行うときの処理

Posted by muchag | MySQL |
初回投稿:2015-09-28 (月) 18:37:18 | 最終更新:2015-09-28 (月) 18:38:49

設定 -> 文字コード

Posted by muchag | MySQL |
初回投稿:2015-05-22 (金) 22:39:16 | 最終更新:2015-05-22 (金) 23:07:53

最近の XAMPP に入っている MySQL は、my.ini をいじらなくても
phpMyAdwmin からデータベースを作成する際に
文字コードを指定することで、文字化けをしなかったので
そのままで利用していたけど、久しぶりに文字化けに遭った。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
デフォルト?

何もしてない気がするので、多分下記がデフォルト。

  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | cp932                          |
  7. | character_set_connection | cp932                          |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | cp932                          |
  11. | character_set_server     | latin1                         |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+
my.ini

インストール 1.7.3
以前、こちらの記事で書いたのが基本。

  1. [mysqld]
  2. # 2015-05-22 追記
  3. character-set-server=utf8
  4. skip-character-set-client-handshake
  5.  
  6. [mysqldump]
  7. # 2015-05-22 追記
  8. default-character-set=utf8
  9.  
  10. [mysql]
  11. # 2015-05-22 追記
  12. default-character-set=utf8
以前との変更点

以前 の通りに my.ini をいじったら、MySQL が起動しなくなった。

[ERROR] (xampp)\mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8’

MySQL 5.5 から仕様が変更になったらしい。

[mysqld] については
default-character-set=utf8 ではなく
character-set-server=utf8 と記述することで無事に起動。

  1. [mysqld]
  2. - default-character-set=utf8
  3. + character-set-server=utf8
参考サイト

cyberarchitect:[ERROR] mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8′

my.ini 編集後
  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

むw 1つだけ latin1。

global 値

参考サイトにしたがって、global も覗いてみる。

  1. mysql> show global variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | utf8                           |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

ほー。こちらはちゃんとなっている。

my,ini 設定前の global 値も見ておけばよかった~。

character_set_database
原因

character_set_database だけが値が異なっている原因は・・・

この値はどこを参照しているかというと、create database した時の値です。

よかろうもん!:MySQLでcharacter_set_databaseがlatin1になってしまう問題の対応方法

だそうです。

あれれ。
データベースを作成した時に、文字コードを設定しそこねたのかしら。

確認方法
  1. mysql> show create database `databese1`;
  2.  
  3. +-----------+----------------------------------------------------------------------+
  4. | Database  | Create Database                                                      |
  5. +-----------+----------------------------------------------------------------------+
  6. | databese1 | CREATE DATABASE `databese1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  7. +-----------+----------------------------------------------------------------------+

うほ!確かに。。。

解決方法
  1. mysql> ALTER DATABASE `databese1` default character set utf8;

直った~♪

経緯

問題解決にはあまり関係ないので
記事末に覚え書き用経緯を。

データ情報

駅データ.jp
こちらのサイトから、全国の駅データをいただいてきた。

編集

配布されているデータは、ちゃんと UTF8 なのだけど
データを追加する都合上、Excel で編集してから DB へ流し込んだ。

データが多く、phpMyAdmin からではタイムアウトになってしまったので
コマンドラインから実行。

手順
  1. 配布元よりデータを DL
  2. Terapad で、SHIFT-JIS、CR+LF へ変換
  3. Excel で、データ編集
  4. Excel から CSV 出力
  5. Terapad で、UTF-8N、LF へ変換
  6. コマンドラインより DB へ流し込み

すると、途中から文字化けという事態が起こった。
これはこれで不思議。。。

Posted by muchag | MySQL |
初回投稿:2015-05-22 (金) 22:39:16 | 最終更新:2015-05-22 (金) 23:07:53

CLI -> 基本

Posted by muchag | MySQL,コマンドプロンプト |
初回投稿:2015-05-22 (金) 11:41:07 | 最終更新:2015-05-22 (金) 13:43:32

MySQL のコマンドラインからの操作。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
開始

私は MySQL を XAMPP から利用しているので
MySQL も XAMPP 内にある。

コマンドプロンプトを開いたら、下記3種の利用方法がある。

  • そのまま記述(環境変数を登録している場合)
  • MySQL\bin まで cd
  • フルパスで記述
コマンドラインツールの起動
  1. $ mysql -u ユーザ名 -p

これでパスワードの入力を促されるので
パスワードを入力し、認証が通ったら
ツールを利用できるようになる。

  1. mysql>

プロンプトが上記のようになれば、起動成功。

パスワードも含めて起動
  1. $ mysql -u ユーザ名 -pパスワード

-p オプション指定の直後にパスワードを記述する。
スペースを開けずに記述する ところがミソ。

データベースを指定して起動
  1. $ mysql -u ユーザ名 -D データベース名 -p
データベースの選択
  1. mysql> use データベース名
テーブル一覧表示
  1. mysql> show tables
終了
  1. mysql> quit
  2.  
  3. または
  4.  
  5. mysql> \q
書式
文末

SQL 文の最後に ;(セミコロン)を付けること。
なぜって、下記のようにカスケード記述ができるから?

カスケード記述

長い SQL 文を分割して入力できる。

  1. mysql> SELECT a, b, c, d, e FROM table_a, table_b WHERE table_a.id = table_b.table_a_id AND table_a.id = 1;
  2.  
  3. // 途中で Enter キーを押下すると、継続して SQL 文を記述できる
  4. mysql> SELECT a, b, c, d, e(Enter)
  5.     -> FROM table_a, table_b(Enter)
  6.     -> WHERE table_a.id = table_b.table_a_id(Enter)
  7.     -> AND table_a.id = 1;

というわけで、文末にセミコロンを付けないと、永遠に -> が出てくる。 🙄

メモ
コマンドエイリアス
コマンド エイリアス
quit \q
Posted by muchag | MySQL,コマンドプロンプト |
初回投稿:2015-05-22 (金) 11:41:07 | 最終更新:2015-05-22 (金) 13:43:32

GROUP BY

Posted by muchag | MySQL |
初回投稿:2015-04-05 (日) 9:32:20 | 最終更新:2015-12-28 (月) 10:25:05

レコードのグループ化

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.9 SELECT 構文

書式
  1. SELECT * FROM table_name GROUP BY (カラム名)
複数設定
  1. SELECT * FROM table_name GROUP BY (カラム名1), (カラム名2), ...

こうすることで、まず (カラム名1) でグループ化され
その後 (カラム名1) の中で (カラム名2) でグループ化され・・・
ということになる。

挙動

グループ化されたカラム以外の値はどうなるのか。

id amount
101 1
101 2
101 3
  1. SELECT * FROM table_name GROUP BY id

id:101, amount:1 となる。

つまり、先頭行 の値を取ってくる。

最大値、最小値

但し、ちゃんと指定すれば、期待する値を取ることもできる。

  1. SELECT id, MAX( amount ) FROM table_name GROUP BY id

id:101, amount:3 となる。

最小値は MIN( amount )

その他の指定

MySQL:12.19.1 GROUP BY (集約) 関数
最大値、最小値の他には、下記の指定ができる。
合計:SUM( amount ):6
平均:AVG( amount ):2
レコード数:COUNT( amount ):3

併用も可能
  1. SELECT id, MAX( amount ), MIN( amount ) FROM table_name GROUP BY id
それ以外

私の場合は、当該グループの最初と最後のデータが欲しかったので
最初はノーマルに取得。
最後はサブクエリでテーブルを逆順にソートして取得。

ORDER BY との併用

ORDER BY と併用する場合、グループ化された後にソートされる。
つまり! 無意味

あかつきのお宿:MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと

Posted by muchag | MySQL |
初回投稿:2015-04-05 (日) 9:32:20 | 最終更新:2015-12-28 (月) 10:25:05

UNION

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 17:28:26 | 最終更新:2015-02-13 (金) 19:31:28

他の記事もそうなんだけど、この記事は特に勉強中で自信がないので、内容が間違っている可能性大。 8-X

【環境】
MySQL:5.5.27

MySQL:13.2.9.4 UNION Syntax

概要

クエリ結果が同一構成となる2つ以上のクエリ結果を合成する。
 
JOIN による結合と違って、リレーションしているわけではない。
単に2つ以上のクエリ結果を1つのテーブル上にする。
 

注意
  1. それぞれのクエリが同カラム数であること
  2. 各カラムが同じ順番に並んでいること
  3. 各カラムのデータ型が同一であること
  4. 場合によって、メイン SELECT の部分にも()が必要
  5. FROM 句サブクエリ内で利用する場合は、サブクエリ全体にエイリアスをつけること

 

基本
  1. SELECT id, name, created_at FROM table_a
  2.     UNION SELECT id, name, modified_at FROM table_b

2つの SELECT がある。
カラム数は同じく 3。
カラム名は異なっていてもよい。→メインの SELECET 文のカラム名が返る。
2015-02-13_UNION_1

 

重複

この項は勉強、実証ともに特に不十分

UNION は、そのまま使うと重複した行は無視されるらしい。

重複した行を拾いたいときは、下記のように ALL をつける。

  1. SELECT id, name, created_at FROM table_a
  2.     UNION ALL SELECT id, name, modified_at FROM table_b

 

注文

こうして合成されたクエリ結果に ORDER BY, LIMIT などの注文を付けたい場合は、更にその後ろに続ける。
そして、SELECT 文を()で括る

  1. ( SELECT id, name, created_at FROM table_a )
  2.     UNION ( SELECT id, name, modified_at FROM table_b )
  3.     ORDER BY id

 

FROM 句

FROM 句にて UNION を利用するときは、エイリアスを付ける。

  1. SELECT id, name, created_at
  2.     FROM (
  3.     SELECT id, name, created_at FROM table_a
  4.     UNION SELECT id, name, modified_at FROM table_b
  5.     ) as sub

このとき、UNION SELECT 文にエイリアスを付けてしまわないように注意。
あくまでも FROM 句全体 に()を付ける。
 
これはサブクエリのルールだから当然か~。
サブクエリ

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 17:28:26 | 最終更新:2015-02-13 (金) 19:31:28

サブクエリ

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 1:38:03 | 最終更新:2015-12-28 (月) 18:55:50

まだおぼろげにしか捉えられていないけど、メモ。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.10 Subquery Syntax

概要

SQL 文の中に、独立した SQL 文を入れ込む。

  1. SELECT *
  2.     FROM hoge
  3.     WHERE user_id = ( SELECT id FROM user WHERE name = "田中" )

例えば、こんな風に書くと、user テーブルで田中さんの id を突き止め
hoge テーブルにある、田中さんの情報だけを取り出すことができる。 😎
 

使い方
ルール

サブクエリは()で括りましょう。
 

SELECT 句

言葉でうまく説明できないので、参考サイト参照。

でも、こういう使い方ができると便利だね~。
 

FROM 句

本来テーブル名を書くはずの FROM 句。
ここにサブクエリを置くことで、クエリをテーブルとして扱うことができる。
 

ルール

必ずエイリアスをつけましょう。
 
私は見事にはまりました。 👿
サブクエリ -> エイリアス
 

WHERE 句、HAVING 句

クエリ結果を条件に設定することができる。
 
まだまだこの程度しかわかっていないので
また判明したことが出てきたら追記!

相関サブクエリ

メインクエリとサブクエリに共通のレーブルが登場するタイプ?
MySQL は苦手とするみたい。
 

参考サイト

Smart:SELECT構文:サブクエリ構文

TECHSCORE:7. サブクエリ

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 1:38:03 | 最終更新:2015-12-28 (月) 18:55:50

サブクエリ -> エイリアス

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 1:21:21 | 最終更新:2015-02-13 (金) 1:21:21

FROM 句に入れるサブクエリではまった。

【環境】
MySQL:5.5.27
現象
  1. SELECT * FROM ( SELECT a, b RROM hoge );

この SQL 文が通らない。

  1. SELECT a, b RROM hoge

これが通っていることは確認済みなのに、何でか通らない。
 

原因

サブクエリにエイリアスがついていなかった。
FROM 句内でサブクエリを使っているサイトを見ていると
どれにもエイリアスがついているので、もしや・・・と思い試してみたらビンゴ。

どこにも使わないから、エイリアスをつけてなかったんだけど
それが原因でした。

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 1:21:21 | 最終更新:2015-02-13 (金) 1:21:21

カラムの演算 -> 負の数

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 0:54:53 | 最終更新:2015-02-13 (金) 0:54:53

負の数の結果を得る SQL が通らなくて難儀した。

【環境】
MySQL:5.5.27
現象

あるカラムの値を負の数として取得したくて、下記のような SQL を投げた。

  1. SELECT num * -1 FROM hoge;

しかし、これが通らない。

  1. SELECT num - 100 FROM hoge;

これもだめ。
 

原因

num カラムの属性を unsigned にしていたことにあったようだ。
属性を外したら結果を取得できた。

属性が unsigned だと、演算結果が負の数になるような演算を受け付けてくれないみたい。
テーブルの属性が、クエリにも影響するとは思わなかった。
エイリアスまでつけたのにな~。 😯

Posted by muchag | MySQL |
初回投稿:2015-02-13 (金) 0:54:53 | 最終更新:2015-02-13 (金) 0:54:53
« 前ページへ次ページへ »