Mysql 基本的な使い方
Mysqlのインストールや、基本的な使い方や関数の使用例などを記述してます。ページ下部には、プログラム言語から接続して使用するサンプルコードもあります。
Mysqlとは
オラクル社により開発・提供されているオープンソースのリレーショナルデータベース管理システム。
WORDPRESSやECCUBEなどのCMSなどにも利用され、世界中で使用されています。
プラットフォームは「Windows」や「Linux」や「mac」にも、対応しています。
環境構築
環境の設定や構築に関連することをまとめてます。インストールしたmysqlを使用するまでの手順となります。
- windows11 mysqlのインストールを行う
- windows11 scoopを使用してmysqlをインストールする
- windows11 mysqlコマンドが使用できない
- CentOs8 Mysql 8をインストールする
- AlmaLinux Mysql8をインストールする
- Ubuntu21.10 mysqlをインストールする
- docker composeを使って「mysql」と「phpmyadmin」を構築する
- クエリでバージョンを確認する方法
- windows10 mysqlのバージョンを確認する
- デフォルト認証方式「caching_sha2_password」を変更する
- AlmaLinux Mysql8のアンインストールを行う
- Mysql8の「my.cnf」の場所を調べる
ツール系
MysqlをGUIで操作するツールをまとめてます。個人的には「Workbench」が一番使いやすいと思います。
- AlmaLinux MySQL Workbenchをインストールする
- mac TablePlusを使用する
- VSCODE mysqlに接続する
- VSCODE 拡張機能「mysql(cweijan)」を使ってmysqlに接続する 個人的にこちらの方が使いやすい
- MySQL Workbenchでステータスとシステムの設定値を確認する
- MySQL Workbenchでユーザーの権限を確認する
- MySQL Workbenchで現在のサーバーの状態を確認する
- MySQL Workbenchで接続されているクライアント情報を確認する
- MySQL WorkbenchでEER図をデータベースから作成する
- MySQL Workbenchでコメントアウトをショートカットキーで行う
コマンド系
Mysqlシェルコマンドに関することをまとめてます。
- 接続時にメッセージを非表示にする
- コマンドでログイン・ログアウトする
- ワンライナーでログインする
- コマンドでサーバーが起動しているかを確認する
- コマンドで現在のLoad Average(ロードアベレージ)を確認する
- コマンドでOSシェルコマンドを実行する
- コマンドを途中でキャンセルする
ユーザー操作
ユーザーに関連することを、まとめてます。
レコードやデータ操作
実際にレコードを操作するときに、使用する関数やクエリなどをまとめてます。
基本
- 使用するDBを指定する
- 取得する件数を指定する
- countの結果が0でも表示する方法
- selectの結果に項番を振る
- like実行時にアンダーバー「_」を指定する
- 条件別でcountを同時に取得する
- 重複を除いてcountをする
- 全テーブル一括削除コマンド
- nullを判定する
- 演算結果がnullであれば任意の値に置換する
- 列の平均値を求める
- 列の最大値を取得する
- 列の最小値を取得する
- 値をシングルクオートで囲む
- 最初に見つかったnull以外の値を取得する
- データを取得する開始位置と件数を指定する
- 複数行コメントアウトを行う
- 変数を使用する
- データーベースのサイズを一覧で取得する
文字データ
- 文字列の最初から指定した文字数を抽出する
- 文字数を取得する
- 文字列のバイト数を取得する
- マルチバイト文字(日本語の全角文字等)の文字コードを取得する
- アルファベットの大文字を小文字に変更する
- アルファベットの小文字を大文字に変更する
- 文字列を反転して表示する
- 文字列を指定した範囲で抽出する
- 文字のアスキーコードを取得する
- 文字列を結合する
- パイプを2つ使って文字列を結合する
- 区切り文字を指定して文字列を結合する
- 区切り文字から指定した数だけ文字列を抽出する
- 最初にある半角スペースを除去する
- 最後にある半角スペースを除去する
- 文字列を置換する
- 文字列を指定した回数繰り返す
- 文字列のビット数を取得する
- 文字列の後ろから指定した文字数を抽出する
- 指定した文字列が最初に見つかった位置を取得する
- 指定した文字列を最初に追加する
- 指定した文字列を最後に追加する
- 文字列のリストから文字列を検索して位置を取得する
- 文字列のリストから指定した位置の値を取得する
- 指定した間隔で文字列を置換する
- 両端から半角スペースを削除する
- 両端から指定した文字列を削除する
- 指定した数の半角スペースを生成する
- 正規表現の結果を取得する
- 列の値を全て区切り文字で抽出する
- カンマ区切りの文字列から指定した文字列の位置を取得する
- BASE64にエンコードする
数値
- 絶対値を取得する
- 整数をカンマ区切りで表示する
- 割り算の余りを取得する
- 平方根を求める
- 最大の値を取得する
- 最大値を他のカラムと一緒に取得する
- 最小の値を取得する
- 列の合計値を取得する
- 10進数を2進数に変更する
- 10進数を8進数に変更する
- 10進数を16進数に変更する
- 小数点以下を切り下げる
- 小数点以下を切り上げる
- 小数点以下を四捨五入する
- 指定した少数桁数で切り捨てを行う
- 指定した進数に変更する
- 第1引数と比較して最初の大きい値の位置を取得する
- べき乗を求める
- 円周率を求める
- ラジアン値から角度を求める
- 角度からラジアン値を求める
- サインの値を求める
- コサインの値を求める
- タンジェントの値を求める
- アークサインの値を求める
- アークコサインの値を求める
- アークタンジェントの値を求める
- コタンジェントの値を求める
日付
- 現在の日時を取得する
- 現在時刻を取得する
- 日付から一部の値を取得する
- その月の最終日を取得する
- 年月単位で集計を行う
- 日付から曜日を取得する
- 日付から曜日を数値で取得する
- 日付から月のみを取得する
- 日付から月の英語表記を取得する
- 日付から年度のみを取得する
- 日付を日時分秒で取得する
- 日付を加算する
- 時間を加算する
- 年月に月を加算する
- 日付の差分を取得する
- YYYYMM形式の日付の差分を取得する
- 日時データに指定した単位で加算を行う
- 日時データを指定した単位で差分を取得する
- 時刻から時のみを取得する
- 時刻から分のみを取得する
- 時間を秒のみに変換する
- 日付から曜日を取得する
- 日時から日付のみを取得する
- 日時から日のみを取得する
- 日付を指定して西暦0年から経過した日数を取得する
- 日時データから時分秒のみを取得する
- 日時データからunix時間を取得する
- 時刻から秒のみを取得する
- 日時分秒の差分を計算する
- 年単位で集計を行う
- 日付を四半期単位で取得する
- 年度からの経過日数を取得する
- 初年度から日数を指定して日付を取得する
- 西暦から経過した日数を日付として取得する
- 世界標準時UTCの日付を取得する
- 世界標準時であるUTCを取得する
- 世界標準時UTCの時間だけを取得する
- 秒数から時刻に変換する
- unixtimeから日時に変換する
ネットワーク系
トリガー
その他
- uuidを生成する
- テキストファイルからクエリを実行する
- MD5値を取得する
- 0埋めを行う
- in句で指定した順番通りにソートする
- nullを置換する
- ZEROFILLを使って0埋めを行う
- パーセント表記に変換する
- レコードをランダムに取得する
- SHA1値を取得する
- 範囲パーティションを作成する
- タイムゾーンを確認する
- 待機時間を作成する
JSONデータ操作
JSONデータを扱うときに、使用する関数やクエリなどをまとめてます。
- jsonリストを生成する
- JSON形式のデータから値を抽出する
- jsonデータの妥当性を確認する
- json型データを抽出する
- jsonデータからkeyのみを取得する
- jsonオブジェクトまたはリストであるかを判定する
- jsonオブジェクト同士を結合する
- レコードの値をjsonリストとしてまとめる
- jsonオブジェクトをマージする
- jsonデータから指定したkeyとvalueが存在するか判定する
- jsonデータに指定したkeyが存在するか判定する
- jsonオブジェクトにkeyとvalueを追加する
- json同士のkeyとvalueを比較する
- jsonデータからvalueを検索する
- keyを指定して値を置換する
- keyを指定して値を置換してkeyがなければ追加する
- keyを指定して削除する
- jsonデータからkeyを指定してvalueを取得する
- リストから指定した値の位置を取得する
- リストの指定した位置の最後に要素を追加する
- リストに指定した値が含まれているかを判定する
- リストの指定した位置に要素を追加する
- jsonオブジェクトを生成する
- レコードの値をjsonオブジェクトとしてまとめる
データ作成
サンプルデータやストアドを使ったデータ作成について、まとめてます。
データベース管理
データベース管理/保守するときに、必要なものをまとめてます。
- 現在使っているDB名を取得する
- 認証時に使用したユーザーを取得する
- 接続中のidを取得する
- 接続しているDBを取得する
- DB一覧を表示する
- ストアドプロシージャを一覧で取得する
- DBの情報をクエリで取得する
- テーブル名を変更する
- カラムの名前を指定する
- クエリでデータがあるパスを確認する
- insert後のオートインクリメント値を確認する
- オートインクリメント値(AUTO_INCREMENT)を初期化する
- DBにあるテーブルごとのレコード数を取得する
- スロークエリを設定する
- スロークエリログの状態を取得する
- 使用している文字コードを確認する
- AUTOCOMMIT値を確認する
- データファイルのパス情報を取得する
- generalクエリのlogの設定と場所をクエリで確認する
- 実行時間の長いクエリの上位を取得する
- クエリでテーブルの構造を確認する
- テーブルからcreate文を作成する
- テーブルにある全てのパーティションを削除する
- ビューを一覧で取得する
- ビューで使用されているテーブルを一覧で取得する
- トリガーの情報を一覧で取得する
- テーブルのキー制約を一覧で取得する
- テーブルのカラムの情報を取得する
- 作成したパーティションを削除する
- 指定したテーブルのdumpファイルを作成する
- クエリでポート番号を確認する
- リソースグループ情報を取得する
- INNODBのテーブルに関する情報を取得する
- INNODBテーブルのインデックス情報を取得する
- INNODBのパフォーマンス情報を取得する
- INNODBのテーブルの更新回数や行数やサイズ情報を取得する
- INNODBのカラム情報を取得する
- INNODBでVIRTUALで作成した情報を取得する
- INNODBのテーブルスペース情報のセブセットを取得する
- INNODBのテーブルスペース情報を取得する
- INNODBテーブルのインデックスで使われているカラム情報を取得する
- 現在のAUTO_INCREMENT値を取得する
- 現在のsql_safe_updatesを確認する
- プラグインを一覧で取得する
- 文字セットを取得する
- サーバーのホスト名を確認する
- サーバーでサポートされている照合順序(collations)を確認する
- secure-file-privに設定されている値を取得する
- indexを一覧で確認する
- indexの最適化を実行する
- indexの統計情報の取得日を確認する
- レコードデータをコピーして挿入する
- テーブルをコピーして新しいテーブルを作成する
- テーブルデータをcsv形式で出力する
- クエリ結果をテーブル形式でファイルに出力する
- テーブルごとのサイズを一覧で取得する
- テーブルのインデックス情報を取得する
- テーブルの権限情報を取得する
- テーブルの統計が最後に更新された日時を取得する
- 照合順序を取得する
- バッファープールのページ情報を取得する
- バッファープールのページサイズや空きページ情報を取得する
- インデックスごとのバッファプールのキャッシュを取得する
- 現在の同時接続数を確認する
- 最大同時接続数の設定を確認する
- SRS情報を取得する
- リアルタイムでクエリの進捗を確認する
- 使用しているパスワードのルール設定を確認する
- 現在のパケットの最大値を取得する
- 現在のSQLモードを確認する
- コマンドの履歴と結果をファイルに記述する
- 予約語の一覧を取得する
- エンジン情報を確認する
- タイムアウトの値を取得する
- 空間関数ST_Distance()で使用できる単位と値を取得する
- 公式のサンプルデータを使用する
- テーブルデータを倍々にしていく
プログラムから使用
プログラム言語からMysqlを操作する方法をまとめてます。
- node.js mysqlに接続する
- Node.js expressを使ってMysqlに接続してデータを表示する
- rails6でmysqlを使用するまでの手順
- php PDOを使ってmysqlに接続する
- python mysqlと接続する
- python mysqlと接続してcreate文を実行する
- python mysqlでselectを実行してデータを取得する
- python mysqlでinsert文を実行する
- python mysqlでupdateを実行してデータを更新する
- python mysqlでdeleteを実行してデータを削除する
- C# mysqlに接続してテーブルを作成する
- C# mysqlに接続してselectを実行する
- C# mysqlに接続してinsertを実行する
- C# mysqlに接続してdeleteを実行する
- C# mysqlでトランザクションを使用する
- C# mysqlに接続してテーブル一覧を確認する
- ASP.NET core mysqlに接続してAPIを作成する
- go言語 mysqlに接続してselectを実行する
- go言語 mysqlに接続してinsertを実行する
- go言語 mysqlに接続してupdateを実行する
- go言語 mysqlに接続してdeleteを実行する
小ネタ
Mysqlに関する小ネタをまとめてます。
- クエリをターミナルから実行する
- ワンライナーでクエリを実行する
- ワンライナーでテーブル一覧をファイルに書き出す
- ページャーを使用してクエリの実行結果を見やすくする
- 「G」を使用して結果を縦に表示する
- 枠線なしでクエリの結果を表示する
- ヘッダーなしでクエリの結果を表示する
エラー対応
エラーが発生した際の、対応についてまとめてます。
- エラー「ERROR 1698 (28000): Access denied for user ‘root’@’localhost’」が発生した場合の対処法
- エラー「ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement」が発生した場合の対処法
- エラー「Error Code: 1054. Unknown column ‘xxx’ in ‘field list’」が発生した場合の対処法
- エラー「Error Code: 1062. Duplicate entry ‘xxx’ for key ‘xxx.PRIMARY’」が発生した場合の対処法
- 「Error Code: 1583. Incorrect parameters in the call to native function」が発生した場合の対処法
- 「mysql: [ERROR] unknown variable slow_query_log=1.」が発生した場合の対処法
- エラー「Error Code: 1074. Column length too big for column」が発生した場合の対処法
- エラー「Error Code: 1146. Table ‘xxx’ doesn’t exist」が発生した場合の対処法
- エラー「Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-xxx’ at line 1」が発生した場合の対処法
- MySQL Workbenchでのみ「Error Code: 1044. Access denied for user」が発生
- MySQL Workbench 「Error Code: 2013. Lost connection to MySQL server during query」が発生した場合の対処法
- MySQL Workbench 「Error Code: 1175」が発生した場合の対処法