まえがき 前回の投稿でphpMyAdminに手こずったと記述しました。 ? RDS - 既存のRDSを削除して新たにRDSを作成しWordPress.orgと接続させる localhost上でphpMyAdminに触れ、MySQLの管理・可視化の大切さを感じ、 安心感を持つことができました。CUIに少しは慣れたつもりですが、 phpMyAdminみたいにデータベースのGUIツールがあればなぁと思って辿り着いたのが Sequel Aceでした。アプリケーションかつSSHキーでの認証なので安全性の確立と、 ブラウザにバーンとエラーが出ないのでそちらの安心感もあります笑 本編のSequel Aceは1時間ほどで設定が完了しました。 おまけではMAMPを使用し、表題のTCP/IPでlocalhost上のMySQLと接続します。 さあ、ドーレ港に到着しました！一本杉を目指しましょう? もうレオリオとクラピカがいるから安心ですねo(^o^)o ※※ TCP/IP接続なのですが、127.0.0.1をlocalhostと統一しています。 ※※ 説明 localhost 127.0.0.1 種類 ホスト名 IPアドレス 接続 UNIXソケット TCP/IP 目的 自分自身を指すホスト名（名前） 自分自身を指すIPアドレス（住所） 動作環境 ・MacBook Air (Retina, 13-inch, 2020) ・Big Sur11.4 前提 ・AWSでEC2インスタンス（サーバ）を作成済み ・RDSインスタンス（データベース）がMySQLで作成済み 1. App StoreでSequel Aceをインストール Homebrewでのインストールもあるのでお好きな方法でインストールしてください。 2. Sequel Aceの設定 （1）インストールが完了したらアプリを開きます （2）各項目に入力していきます ①上部タブのSSHをクリックします ②Name：任意のお名前 ・RDS情報 ③MySQL Host：RDS DBインスタンスのエンドポイント（○○○rds.amazonaws.com） ④Username：RDS DBインスタンスのマスターユーザー名 ⑤Password：RDS DBインスタンスのマスターパスワード -> RDS作成時に設定したマスターユーザー名とマスターパスワードです。 ⑥Database：任意のお名前（空白可） ⑦Port：空欄（3306） ⑧Time Zone:デフォルトでOK ・EC2情報 ⑨SSH Hostname: EC2インスタンスのパブリックDNS名 ⑩SSH Username: EC2インスタンスのユーザー名（ec2-user ※※ EC2 Linuxのユーザー名 ※※） ⑪SSH Password: EC2インスタンス作成時に任意の場所に保存した秘密鍵（○○○.pem） ※※ 右にある鍵マーク?をクリックして選択。直接貼り付けは失敗しました。 ※※ ⑫SSH Port：空欄（22） ⑬Add to Favoritesをクリックしてお気に入りに追加しておけば 入力が保存され、次回からログインが楽になります。 （3）以下のように表示されたらYesをクリックします （見切れてしまいました（；；）見辛くて申し訳ございません。ふにゃふにゃ赤線‪︎ 〰︎‬〰︎‬） 接続が成功すればデータベースを参照することが出来ます。 おまけ①：localhostに接続する （´-`）.｡o（ localhostに接続してphpMyAdminで作成したデータベースって見れるんかなぁ） とふと思い、phpMyAdminと決着着けな！と勝手に闘争心燃やして決行することにしました。 かなり苦戦してしまいましたが、Sequel Aceの公式ドキュメントにて、 同じコンピューターで実行されているMAMPまたはXAMPPのMySQLサーバーに接続する方法 でphpMyAdminで作成したデータベース・テーブルを参照することが出来たので こちらも書いておこうと思います！！！！参考になれば嬉しいです? 本題よりも時間がかかってしまいました。。 参考 ? MAMPまたはXAMPPに接続する 苦戦の最中でMAMP接続の公式ドキュメントと運命の出合いを果たしたものの 上手くいかず、またエラーなるもんなぁと思ってテスト接続したら「接続出来ません！」 ではなく「接続が成功しました」と表示され思わず「えっっっっっっっっっっっっっ」って 大きめの声が出ました。もともと声大きいんですがだいぶ家に響きました笑 考えられる理由としてはphp.iniを触ったときにApacheを2回再起動したこと、ぐらいです。。 調べた際のほとんどのエラーはMySQL8.0の認証プラグインで当てはまりませんでした。 MySQL [(none)]> SELECT user, host, plugin FROM mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | wordpress_user | % | mysql_native_password | ~ | | | | mysql.infoschema | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | hoge | localhost | mysql_native_password | +------------------+-----------+-----------------------+ 5 rows in set (0.01 sec) 表示されたエラー MySQL said:っておちゃめ。 MySQL said: Can't connect to MySQL server on '127.0.0.1' (61) Can't connect to MySQL server on '127.0.0.1' (61) SequelAce Unable to connect to host 127.0.0.1, or the request timed out. Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds). MySQL said: Can't connect to MySQL server on '127.0.0.1' (61) Can't connect to MySQL server on '127.0.0.1' (61) Unable to connect to host 127.0.0.1, or the request timed out. Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds). MySQL said: Can't connect to MySQL server on '127.0.0.1' (61) Can't connect to MySQL server on '127.0.0.1' (61) Unable to connect to host 127.0.0.1, or the request timed out. Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds). MySQL said: Can't connect to MySQL server on '127.0.0.1' (61) Can't connect to MySQL server on '127.0.0.1' (61) Unable to connect to host 127.0.0.1, or the request timed out. Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds). MySQL said: Can't connect to MySQL server on '127.0.0.1' (61) Can't connect to MySQL server on '127.0.0.1' (61) 1.TCP/IP接続を介してMAMPに接続 （1）MAMPを起動させます Start -> WebStart✈️で準備OKです。 開いたMAMPページのMySQLを参考に入力していきます。 2.各項目に入力 （1）上部タブのTCP/IPをクリック ①Name：任意のお名前 ②Host：127.0.0.1 ③Username：root ④Password：root（設定していなければ） ⑤Database：任意 ⑥Port：8889（MAMPが使用するデフォルトのMySQLポート） 8889の方は⑦へ✈️ MAMPの設定で3306に変更している方は3306です。 MAMP -> Preferences -> Ports で確認できます。 ⑦Test connection をクリック ⑧接続が成功しましたと表示されれば、 ⑨の接続をクリックします これでphpMyAdminで作成したデータベースやテーブルを参照することが出来ます！ もちろんSequel Ace内で作成することも可能です。ものすんごい成長した気分です。。 おまけ②：WordPressのデータベースのテーブル 頭にある❓マークはそのように解釈しているんですが、 全然ちゃうわ！でしたらご教示くださるとめちゃくちゃ嬉しいです。 テーブル名 保存しているデータ ❓wp_as3cf_items プラグインのWP Offload Media＋S3にアップロードしたメタデータ（本体であるデータに関する付帯情報が記載されたデータ） wp_commentmeta 各コメントのメタデータ情報 wp_comments WordPressで投稿した記事のコメント・トラックバック（他のブログの記事を自分のブログで引用・参考にしたときに、そのブログに対して通知するしくみ。通知を受けたブログは自分の記事が紹介された記事を関連記事としてその記事内で表示することでお互いに記事をリンクさせることができる）・ピンバックデータ（記事内に記載されたリンク先に対して自動で送信されるデータ）を格納 wp_options サイトURL・ホームURL、サイト名などの基本情報に加え、一部プラグインの設定といったWordPressの各種設定が保存されている wp_postmeta wp_postsのメタデータ。主にカスタムフィールド（記事のタイトルや本文以外に別の情報を追加してサイトに掲載する属性を設定する機能） wp_posts 投稿、固定ページ、メディア、カスタムメニュー、カスタム投稿、リビジョン（執筆・編集した記事の内容をDBに保存して差分を見たり、復元したりする機能）etcの投稿関連の主要データ wp_term_relationships カテゴリー・タグ・カスタムタクソノミー（taxonomy、情報やデータなどを階層構造で整理したもの）と投稿を関連付けるためのデータ wp_term_taxonomy カテゴリー・タグ・カスタムタクソノミーのターム名（タクソノミーの中で追加する個々の項目名）やそのスラッグの情報 wp_termmeta プラグインや個別にカスタマイズする際に使用 wp_terms カテゴリー・タグ・カスタムタクソノミーとターム名や、そのスラッグ（記事やカテゴリなどに付与される名前のひとつ、英語名）の情報 wp_usermeta 各ユーザー独自の、ユーザー・メタデータを格納 wp_users 管理者等のユーザー情報を格納しているテーブル。ユーザー名・パスワード・メールアドレスetcを保存している あとがき すんなりと接続が成功し感動の連続でした。Yesとクリックしたあとスッと表示されたので 「えっ」と言ってしまうぐらいあっという間に設定が完了して中身を見ることが出来ました。 Sequel Aceがあれば一括で管理出来るので非常に便利なアプリケーションだと思います。 何故こんなにもデータベースが気になるのか。。 まるでやたらとカカリコ村の井戸の底が気になるナビィみたいです? ここまでお読みいただきありがとうございました。 参考

問題 CASEを使った既存の体系を新たな体系に変換した集計をやっていきます。 Testsという仮のテーブルがあるとします。 key greatest A 2 B 5 C 7 D 3 任意の順番（B→A→D→C）に並び替えるクエリを書いてください。 補足 そもそも、こんなクエリを発行させないといけないようなテーブルがおかしいですが、クエリの勉強だと思ってください。 仮定として、テーブル自体を見直した方がいいような設計であるとします。今回は仮に、ソート用の列が存在せず、ソートさせた結果が欲しいとします。 本来、SQL（SELECT）の仕事はデータ検索です。結果の見た目の整形は本来の役割ではありませんが、時としてクエリに頼らざるを得ない場面はありますよね？（僕はありました） そういうシーンであると仮定して欲しいです。 SQL SELECT key FROM Tests ORDER BY CASE key WHEN 'B' THEN 1 WHEN 'A' THEN 2 WHEN 'D' THEN 3 WHEN 'C' THEN 4 ELSE NULL END; ソート列も出力させたいのなら SELECT key, CASE key WHEN 'B' THEN 1 WHEN 'A' THEN 2 WHEN 'D' THEN 3 WHEN 'C' THEN 4 ELSE NULL END AS sort_col FROM Tests ORDER BY sort_col; ちなみにこの書き方は 以前書いた記事? に似てますね。これらは、最初に書いたSQLよりも標準的なSQLにのっとってます。 なぜなら、ORDER BYはSELECTよりも後に実行されるので、SELECT句で作られた計算列（今回なら: sort_col）を参照できるからです。 SELECT文の処理の実行順序 FROM // 検索対象のテーブルが決定 ↓ WHERE // 条件による絞り込み ↓ GROUP BY // グループ化 ↓ SELECT // SELECTに記述された計算が実行 ↓ HAVING // グループ化された結果を条件によって絞り込み ↓ ORDER BY // ソート ↓ LIMIT // 指定された行数で絞り込む 参照 25p アウトプット100本ノック実施中

問題 PopTbl2（再掲）という仮のテーブルがあるとします。 pref_name（県名） sex（性別） population（人口） 徳島 1 60 徳島 2 40 香川 1 100 香川 2 100 愛媛 1 100 愛媛 2 50 高知 1 100 高知 2 100 東京 1 250 東京 2 150 これを、以下のような表頭に合計や再掲の列を持つようなクロス表を作ってください。 結果 性別 全国 徳島 香川 愛媛 高知 四国（再掲） 男 610 60 100 100 100 360 女 440 40 100 50 100 290 SQL この問題は、下記の記事の復習くらいの難易度なので、解説は省略します。 SELECT sex, SUM(population) AS '全国', SUM(CASE WHEN pref_name = '徳島' THEN population ELSE NULL END) AS '徳島', SUM(CASE WHEN pref_name = '香川' THEN population ELSE NULL END) AS '香川', SUM(CASE WHEN pref_name = '愛媛' THEN population ELSE NULL END) AS '愛媛', SUM(CASE WHEN pref_name = '高知' THEN population ELSE NULL END) AS '高知', SUM(CASE WHEN pref_name IN ('徳島','香川','愛媛','高知') THEN population ELSE NULL END) AS '四国（再掲）' FROM PopTbl2 GROUP BY sex 参照 24p アウトプット100本ノック実施中

目的 Node.jsを用いて簡易なWebシステムを構築する。Node.jsで簡易なWebシステムの構築①のアプリの拡張で、今回はデータの登録を可能にする。 環境条件 Node.jsで簡易なWebシステムの構築①で作業した環境をそのまま利用。 構築手順 ec2-userでログイン # rootユーザにスイッチ sudo su - 1.基本的な環境設定 #/opt/nodejsのディレクトリに移動 cd /opt/nodejs express-generatorを用いてアプリケーションのベースを構築。 #expressのインストール npm install express --save npm WARN saveError ENOENT: no such file or directory, open '/opt/nodejs/package.json' npm notice created a lockfile as package-lock.json. You should commit this file. npm WARN enoent ENOENT: no such file or directory, open '/opt/nodejs/package.json' npm WARN nodejs No description npm WARN nodejs No repository field. npm WARN nodejs No README data npm WARN nodejs No license field. express@4.17.1 added 50 packages from 37 contributors and audited 50 packages in 2.319s found 0 vulnerabilities #express-generatorのインストール npm install -g express-generator npm WARN deprecated mkdirp@0.5.1: Legacy versions of mkdirp are no longer supported. Please update to mkdirp 1.x. (Note that the API surface has changed to use Promises in 1.x.) /usr/local/bin/express -> /usr/local/lib/node_modules/express-generator/bin/express-cli.js + express-generator@4.16.1 added 10 packages from 13 contributors in 0.715s #myapp2という名前でアプリケーションのベースを構築 express -e myapp2 warning: option --ejs' has been renamed to--view=ejs' create : myapp2/ create : myapp2/public/ create : myapp2/public/javascripts/ create : myapp2/public/images/ create : myapp2/public/stylesheets/ create : myapp2/public/stylesheets/style.css create : myapp2/routes/ create : myapp2/routes/index.js create : myapp2/routes/users.js create : myapp2/views/ create : myapp2/views/error.ejs create : myapp2/views/index.ejs create : myapp2/app.js create : myapp2/package.json create : myapp2/bin/ create : myapp2/bin/www change directory: $ cd myapp2 install dependencies: $ npm install run the app: $ DEBUG=myapp2:* npm start #myapp2ディレクトリに移動 cd myapp2/ #npmパッケージのインストール npm install npm notice created a lockfile as package-lock.json. You should commit this file. added 54 packages from 38 contributors and audited 55 packages in 2.187s found 0 vulnerabilities #mysql関連ライブラリとexpress-validatorのインストール npm install --save mysql express-validator 2.アプリケーションの開発 今回のアプリケーションは、http://ホスト名:3000にアクセスするとNode.jsで簡易なWebシステムの構築①と同様に商品（果物）リストが表示され、その画面上にボタンを追加し、当該ボタンから商品登録画面に遷移し、登録が完了すると、登録後の情報を元の画面を更新して表示するという仕様とする。遷移先のURLはhttp://ホスト名:3000/insertとする。 express-generatorで生成された各種ファイルに対し、追記や変更、ファイル追加を実施することで構築している。 追記・変更したもの public/stylesheets/style.css routes/index.js views/index.ejs 追加したもの views/insert.ejs routes/index.js // 必要なライブラリの呼び出し const express = require('express'); const router = express.Router(); const mysql = require('mysql'); const { check, validationResult } = require('express-validator/check'); // mysql接続用の設定定義 const mysql_setting = { host: 'localhost', user: 'root', password: 'password', database: 'myappdb' } // http://<hostname>:3000にアクセスがきた際のレスポンス router.get('/', function(req, res, next) { // DBコネクションの生成 const connection = mysql.createConnection(mysql_setting); connection.connect(); // SQLの実行と結果の取得とindex.ejsへの伝達 connection.query('select * from myapptbl1', function (err, results, fields) { if (err) throw err res.render('index', { content: results }) }); // DBコネクションの破棄 connection.end(); }); //　http://<hostname>:3000/insertにアクセスがきた際のレスポンス（insert.ejs） router.get('/insert', function (req, res, next) { const data = { errorMessage: '' } res.render('./insert', data); }); //　http://<hostname>:3000/insertへアクセスが来た際のレスポンス // web画面上で入力された値が空になっていないかを確認し、エラーメッセージを表示 router.post('/insert', [check('name').not().isEmpty().trim().escape().withMessage('名前を入力して下さい'),check('price').not().isEmpty().trim().escape().withMessage('値段を入力して下さい'),], (req, res, next) => { const errors = validationResult(req); // 値が空の場合 if (!errors.isEmpty()) { const errors_array = errors.array(); res.render('./insert', { errorMessage: errors_array, }) } else { // 値が入力されている場合 // 画面上で入力された値を変数として定義 const name = req.body.name; const price = req.body.price; // SQL用に配列の作成と変数の入力 const post = { 'name': name, 'price': price }; // DBコネクションの生成 const connection = mysql.createConnection(mysql_setting); connection.connect(); // プレースホルダを用いてSQLを発行し、データを登録する connection.query('INSERT INTO myapptbl1 SET ?', post, function (error, results, fields) { if (error) throw error; // http://<ホスト名>:3000にリダイレクト（Insert後のデータを出力） res.redirect('./'); console.log('ID:', results.insertId); }); // DBコネクションの破棄 connection.end(); } }) module.exports = router; 以下、ejsファイルは大したこと書いてないので、細かい説明は割愛 views/index.ejs <!DOCTYPE html> <html> <head> <link rel='stylesheet' href='/stylesheets/style.css' /> </head> <body> <table> <thead> <tr> <th scope="col">id</th> <th scope="col">name</th> <th scope="col">price</th> </tr> </thead> <% for(let i in content) { %> <tr> <% let obj = content[i]; %> <th> <%= obj.id %> </th> <th> <%= obj.name %> </th> <th> <%= obj.price %> </th> </tr> <% } %> </table> <p class="bottom_space"></p> <button class="b1" onclick="location.href='./insert'">商品登録画面</button> </body> </html> views/insert.ejs <!DOCTYPE html> <html> <head> <link rel='stylesheet' href='/stylesheets/style.css' /> </head> <body> <p class="p1">商品を登録してください</p> <form action="/insert" method="post"> <input type="text" name="name" value="名前"> <input type="text" name="price" value="値段"> <button class="b2">登録</button> </form> <% if(errorMessage) { %> <ul> <% for (let n in errorMessage) { %> <li> <%= errorMessage[n].msg %> </li> <% } %> </ul> <% } %> </body> </html> 最低限の見栄えのために以下追記 （aタグまではデフォルト） public/stylesheets/style.css body { padding: 50px; font: 14px "Lucida Grande", Helvetica, Arial, sans-serif; } a { color: #00B7FF; } table, tr, th { border-collapse: collapse; border:1px solid; font-size: 30px; } th { width: 400px; height: 50px; } table thead tr th { color: #fff; background: #000000; } .bottom_space { margin-bottom: 1em; } .b1 { width: 1204px; height: 50px; font-size: 30px; } .b2 { width: 400px; height: 50px; font-size: 30px; } .p1 { font-size: 50px; } input { width: 400px; height: 50px; font-size: 30px; } 3.画面イメージ http://<ホスト名>:3000 http://<ホスト名>:3000/insert 値の入力 登録後のリダイレクト

バルクアップデートとは バッチ処理などでは、データベースに複数の行を挿入したり、更新したりしたい場合がよくあると思います。挿入に関しては、大抵のRDBMSではINSERT INTO table (...) VALUES (...), (...)のような形で簡単に実行可能ですし、Laravelでもinsert()の第1引数に連想配列の配列を渡せば可能です。 更新に関してはそう簡単ではありませんが、MySQLに限っては、ELT()とFIELD()という2つの関数を使った方法で可能です。ただし、これをLaravelで実行するのはだいぶややこしいので、簡単に実行できるようにしてみました。 使い方 上記にあるサービスプロバイダクラスのファイルを、app/Providers以下に配置し、config/app.phpのprovidersにApp\Providers\MySqlBulkUpdateServiceProvider::classを追加、その上で、 <?php DB::table('users')->whereNull('email_verified_at')->updateBulk([ ['id' => 1, 'name' => 'admin1', 'email' => 'admin@example.com'], ['id' => 2, 'name' => 'admin2', 'email' => 'admin@example.com'], ['id' => 3, 'name' => 'admin3', 'email' => 'admin@example.com'], ]); あるいは、 <?php App\Models\User::whereNull('email_verified_at')->updateBulk([ ['id' => 1, 'name' => 'admin1', 'email' => 'admin@example.com'], ['id' => 2, 'name' => 'admin2', 'email' => 'admin@example.com'], ['id' => 3, 'name' => 'admin3', 'email' => 'admin@example.com'], ]); のように実行します。 詳細 第1引数で渡した配列から、id(Eloquent\Builderからの場合は、モデルに設定された主キー)あるいは第2引数で渡したカラムで検索し、残りの値を更新します。上記のQuery\Builderの例の場合は、以下のようなSQLにコンパイルされます。 UPDATE `users` SET `name` = ELT(FIELD(`id`, ?, ?, ?), ?, ?, ?), `email` = ? WHERE `email_verified_at` IS NULL AND `id` IN (?, ?, ?) emailは全行で同じとなっているため、ELT(), FIELD()は使わない形にします。また、updateBulk()以前に追加したWHERE句も別途設定されます。 さらに、Eloquent\Builder経由の場合はupdated_at等も自動で設定されます。 Query\Builder経由の実行時に、主キーがid以外の場合、あるいはEloquent\Builderで、主キー以外で検索したい場合は、第2引数にカラム名を指定します。 DB::table('users')->updateBulk([ ['name' => 'admin1', 'email' => 'admin1@example.com'], ['name' => 'admin2', 'email' => 'admin2@example.com'], ['name' => 'admin3', 'email' => 'admin3@example.com'], ], 'email'); この記事のライセンス この文書はCC BY(クリエイティブ・コモンズ表示4.0国際ライセンス)で公開します。