MySQL – 集約関数

概要

集約関数(aggregate functions)は、テーブルの全レコードまたはグループ化されたグループごとの集約値を返す。

COUNT 指定したカラムがNULL以外の行数
MIN 指定したカラムの最小値(NULLの行は含めない)
MAX 指定したカラムの最大値(NULLの行は含めない)
SUM 指定したカラムの合計値(NULLの行は含めない)
AVG 指定したカラムの平均値(NULLの行は含めない)

実行例

以下のテーブルを使う。

COUNT~個数

CONT()は引数に指定した列のうちNULL以外の個数を数える。

以下の例ではCOUNT(subject)はレコード数と同じ6となるが、COUNT(score)NULLが対象とならないため5となっている。

MIN/MAX~最小値・最大値

MIN()は引数にした列のうち最小の値を返し、MAX()は最大の値を返す。NULLは対象に含まれない。

以下の例では列としてscoreを指定し、最小値と最大値が得られている。

なおMIN()/MAX()sucjectを渡すと、文字列の照合順序に従って大小関係が判定される。

SUM~合計

SUM()は引数の合計を返す。NULLは無視される。

以下の例では6つのレコードのscoreのうちNULLを除いた5つの合計が得られている。

なお文字列データの列を引数に与えるとSUM()は0を返す。

AVG~平均値

AVG()は引数の列データの平均値を返す。NULLは無視されるため、割る数からものぞかれる。

以下の例では6つのscoreのうち1つがNULLのため、平均値は410/5で計算される。

文字列型の列を引数に指定すると、結果は0となる。

 

 

MySQL – 初期値の設定

列の初期値を設定するには、対象の列でDEFAULT VALUEを定義する。

 

MySQL – 主キーの設定

列の設定で宣言する方法

書式

CREATE TABLEの列の定義の中で以下を設定する。

  • NOT NULL
  • PRIMARY KEY
  • AUTO INCREMENT(必要な場合)

実行例

PRIMARY KEYを別に宣言する方法

書式

列の宣言の最後で以下を設定する。複合キーの場合はキーを並べる。

  • PRIMARY KEY(key1 [, key2, ...]);

実行例

 

MySQL – 複数レコードの一括登録

記法

複数レコードを登録するには、VALUESの値をカンマで区切って並べる。

INSERT INTO table (columns) VALUES (values), (values), ... (values);

実行例

 

MySQL – COLLATE~照合順序

概要

  • COLLATE文は文字の照合順序を指定する
  • 照合順序は文字の大小比較をする際の基準
  • 検索時のWHEREや並べ替えORDER BYなどで適用される

書き方

WHERE

WHERE colname COLLATE collateion = 'expression';

WHERE colname COLLATE collateion LIKE 'expression';

ORDER BY

ORDER BY colname COLLATE collateion;

Collation

  • utf8mb4_unicode_ci
  • utf8mb4_general_ci
  • utf8mb4_bin
  • utf8mb4_0900-ai_ci(デフォルト)

デフォルトの照合順序

UTF-8のデフォルトはutf8_general_ciだが、utf8mb4の場合utf8mb4_0900-ai_ciと照合順序が異なる。

確認

以下のテーブルを使う。

区別

utf8mb4_0900_ai_ci

  • 大文字と小文字は区別される
  • 全角と半角は区別される
  • 平仮名と片仮名は区別される
  • 濁点の有無は区別される

utf8mb4_general_ci

  • 大文字と小文字は区別されない
  • 全角と半角は区別される
  • 平仮名と片仮名は区別される
  • 濁点の有無は区別される

utf8mb4_unicode_ci

  • 大文字と小文字は区別されない
  • 全角と半角は区別されない
  • 平仮名と片仮名は区別されない
  • 濁点の有無は区別されない

utf8mb4_bin

  • 大文字と小文字は区別される
  • 全角と半角は区別される
  • 平仮名と片仮名は区別される
  • 濁点の有無は区別される

順序

utf8mb4_0900_ai_ci

utf8mb4_unicode_ciと同じ。

  • アルファベットは小文字・大文字順→アルファベット順
  • 仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順

utf8mb4_general_ci

  • アルファベットは小文字・大文字順→アルファベット順
  • 仮名はユニコード文字順

utf8mb4_unicode_ci

Unicodeの文字順と異なる。

  • アルファベットは小文字・大文字順→アルファベット順
  • 仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順

utf8mb4_bin

  • アルファベット・仮名ともUnicode文字順

 

Vagrant – 仮想環境構築 – CentOS7

概要

Windows10にVagrant + VirtualBoxでCentOS7の仮想環境を構築したときの記録。

Vagrant/VirtualBoxの導入

Vagrantのインストール

VirtualBoxのサイトのダウンロードページからインストールファイルをダウンロード。

  • Windows/64bitを選択してDownload
  • vagrant_2.2.14_x86_64.msi(232MB)

ダウンロードしたmsiファイルを実行

  1.  Welcome to ….→Next
  2. End-User Lisence Agreement→accept & Next
  3. C:\HashiCorp\Vagrant→Next
  4. Ready to install Vagrant→Install
  5. Completed …→Finish
  6. You must restart your system …→Yes/No
  7. Restart

コンソールを起動し、インストールされたVagrantのバージョンを確認。

VirtualBoxのインストール

VirtualBoxのサイトからパッケージをダウンロード。

  1. VirtualBox 6.1.18 platform packages
  2. Windows hosts
  3. VirtualBox-6.1.18-142142-Win.exe

インストール

  1. Welcome to …→Next
  2. Custom Setup
    • インストール先はC:\Progrm Files\Oracle\VirtualBox
  3. ショートカット作成選択
  4. Warning Network Interfaces→Yes
  5. Ready to Install→Install
  6. … installation is complete→Finish

VisuaruBoxを起動してバージョン確認。

  • バージョン 6.1.18 r142142 (Qt5.6.2)

拡張版のインストール

本体ダウンロードのリンクの下に拡張版のダウンロードがある。説明書きに”Support for USB 2.0 and USB 3.0 devices, …”とあって、USBを有効が有効になる。

  1. VirtualBox 6.1.18 Oracle VM VirtualBox Extension Pack→download
  2. VirtualBox起動
    1. ファイル→環境設定→機能拡張
    2. 右方のアイコン→ファイル選択→インストール

この段階ででSSD 147/237GB freeでインストール前と容量が変わっていない。

Boxの導入

Vagrant導入当初BoxとしてVagrant CloudのCentos/7を選んだが、共有フォルダ-設定がうまくいかなかったのでbento/Centos-7.7に変更した。

Boxのインストール

仮想環境のBoxをインストール。Vagrant Cloudで提供されているbento/CentosOS-7.7を選んだ。

  • Vagrantからのインストールの場合は以下のコマンド。
    • >vagrant box add bento/centos-7.7
    • ダウンロードに10分程度かかる
  • 他のサイトからの場合、名前を指定してインストールする場合は以下のコマンド。
    • >vagrant box add name url/local_file

インストールされたBoxの存在確認(前にインストールしたCentos/7のBoxも残っている)。

Boxファイルの格納場所

Windows

  • ユーザーフォルダー\.vagrant.d\boxes

仮想環境のVagrant設定

ドライブ直下にVagrantディレクトリー、その下にCentOS7のディレクトリーを作成し、そこに移動。

移動後のディレクトリー内で初期設定。この操作によってディレクトリー内にvagrantfileが作成されて、仮想環境が利用可能になる。

この時点でSSD 140/237GB free。

仮想環境の利用

仮想環境の起動

vagrant upコマンドで仮想環境を起動。

仮想環境へのログイン

現バージョンでは、Windowsでもvagrant sshで接続できる。

バージョンを確認。

実行中の仮想環境の確認

仮想マシンを立ち上げたコンソールとは別のコンソールを立ち上げ、vagrant statusで確認。

仮想環境からのログアウト

CentOSのexitコマンドでログアウト。

仮想環境の停止

Windowsプロンプトでvagran haltコマンドを実行して停止。

仮想環境停止の確認

停止後のWindows環境でvagrant statusを実行すると仮想環境の停止を確認できる。

ロケール/タイムゾーンの設定

ロケール/タイムゾーンの確認

現在のロケール、タイムゾーンはdatelocalelocalectlコマンドで確認できる。インストール直後はUSになっている。

ロケールの設定

ロケールの設定はlocalectlをsuで実行。

sudo localectl set-locale LANG=ja_JP.UTF-8

設定の反映には再接続が必要。

タイムゾーンの設定

現在のタイムゾーンはtimedatectlコマンドで確認できる。

とりあえずWarningは置いておく。

設定可能なタイムゾーンの一覧は以下のとおり。

timedatectl list-timezones

タイムゾーンの設定はtimdatectlをsuで実行。

sudo timedatectl set-timezone [タイムゾーン名]

 

Ruby – ファイル操作 – CSV

ライブラリーの読み込み

ライブラリーのcsvを読み込む。

require "csv"

CSVファイルの書き込み

ファイルのオープン

モードを指定してファイルを開き、CSVオブジェクトを取得(モードはファイル操作を参照)。

csv = CSV.open("ファイル名", "モード")

行単位の書き込み

カラム数を要素数とする配列でputs()メソッドを使う。

csv.puts([col1, col2, ... ])

'<<'演算子でもよい。

csv << ([col1, col2, ... ]

ファイルのクローズ

csv.close

CSVファイルの読み込み

ファイルのオープン/クローズは不要で、read()メソッドで直接一括読み出し。

array = csv.read

結果は2次元配列で、行数がレコード数、列数がカラム数。

 

Ruby – 日付と時刻

現在時刻

現在時刻はTimeクラスのnowメソッドで取得。

Time.now

フォーマッティング

Timeクラスのstrftime()メソッドで日付・時刻を書式整形した文字列が得られる。

time.strftime("書式文字列")

典型的な書式の例

time.strftime("%Y/%m/%d %H:%M:%S")

 

正規表現 – 雛形

一般

文字列の先頭と末尾の位置指定には^$ではなく\A\zと用いる。

参考:【PHP】マルチバイト(全角スペース等)対応のtrim処理

  • 半角英数記号
  • n文字以上m文字以下
    • \A(文字){n,m}\z
  • patternを含む
    • (?=.*pattern).*
  • patternを含まない
    • (?!.*pattern).*
  • pattern1を含みpattern2を含まない
    • (?=.*pattern)(?!.*pattern).*

雛形

英数字

  • 英文字のみ
    • [a-zA-Z]
  • 英数字
    • [0-9a-zA-Z]
  • \wはアンダースコアを含むので注意
    • [0-9a-zA-Z_]

正の整数

  • /\A[1-9][0-9]*\z/
    • 01や+1は通らない
  • /\A\+?[0-9]*[1-9][0-9]*\z/
    • +001を通す
  • /\A\+?0*[1-9]+(,?[0-9]+)*\z/
    • 先頭に+を許可、その後0の連続を許可、桁内任意位置のカンマを許可

スペース

  • 先頭/末尾の連続するスペース(半角・全角とも)。エスケープ解釈のためダブルクォート
    • "/\A[\x20\xE3\x80\x80]+|[\x20\xE3\x80\x80]+\z/u"

メールアドレス

  • ローカル部は英数字、_、+、-でドットは間に1つずつ、ドメインは英数字、-でドットは間に1つずつ
    • \A([\w+-]+.?[\w+-]+)+@([\w-]+.?[\w-]+)+\z

コメント

  • /*…*/→/\*[\s\S]*?\*/
    • 複数行にわたる場合も可

MySQL – データベースのバックアップと復元

データベースのバックアップ

特定のデータベースのバックアップ

バックアップにはコンソールからmysqldumpコマンドを使う。

以下のコマンドでダンプファイルが作成される(dumpファイルパスがファイル名だけならカレントディレクトリーに作成)。

全データベースのバックアップ

データベースの復元

データベースの復元には、コンソールからmysqlコマンドを使う(mysqldumpコマンドではない)。

あらかじめ復元先の空のデータベースを作成しておく。

特定のデータベースへの復元

全データベースの復元

ハマったこと

復元時に「データベースがない」

以下のエラー。

Unknown database ‘DB名’

復元先に空のデータベースがないと復元できないので、作成する必要がある。

復元時に'<‘が使えない (Windows PowerShell)

以下のエラー。

演算子 ‘<‘ は、今後の使用のために予約されています。

WindowsのPowerShellでは'<'は使えないらしい。コマンドプロンプトやLinuxのShellでは問題なし。

復元時に「ASCIIの’\0’があります」(Windows/PowerShell)

以下のエラー。

ERROR: ASCII ‘\0’ appeared in the statement, but this is not allowed unless option –binary-mode is enabled and mysql is run in non-interactive mode. Set –binary-mode to 1 if ASCII ‘\0’ is expected.

WindowsのPowerShellはデフォルトの文字コードがSJISのため、ダンプファイルを作成すると復元側で読み取れない。