SQLの窓

2020年05月11日


MySQL : DB のデータを簡単に沢山作る方法は inner join

ページ処理のテストを行う為に、元を 7 件として、56 件のデータに増やす方法です。それぞれの SQL は、RDBMS 毎に動作するように書き換えれば実行可能です。

元のテーブル

既に 7 件のテストデータがあり、phpMyAdmin でエクスポートしています。phpMyAdmin はたいていのレンタルサーバで用意されていると思いますし、XAMPP をインストールして Windows のローカルでも利用可能です。
CREATE TABLE IF NOT EXISTS `board` (
  `row_no` serial,
  `body` varchar(1000),
  `subject` varchar(200),
  `from` varchar(20),
  `pdate` datetime,
  `cdate` datetime,
  primary key(`row_no`)
)

※ row_no は自動採番列です

データ

以下はエクスポートしたデータです。
-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- ホスト: 127.0.0.1
-- 生成日時: 
-- サーバのバージョン: 10.4.10-MariaDB
-- PHP のバージョン: 7.3.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- データベース: `lightbox`
--

-- --------------------------------------------------------

--
-- テーブルの構造 `board`
--

CREATE TABLE `board` (
  `row_no` bigint(20) UNSIGNED NOT NULL,
  `body` varchar(1000) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `from` varchar(20) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `cdate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- テーブルのデータのダンプ `board`
--

INSERT INTO `board` (`row_no`, `body`, `subject`, `from`, `pdate`, `cdate`) VALUES
(1, 'ある日の事でございます。御釈迦様は極楽の蓮池のふちを、独りでぶらぶら御歩きになっていらっしゃいました。池の中に咲いている蓮の花は、みんな玉のようにまっ白で、そのまん中にある金色の蕊からは、何とも云えない好い匂が、絶間なくあたりへ溢れて居ります。極楽は丁度朝なのでございましょう。\r\n', '蜘蛛の糸-1', '芥川龍之介-1', '2016-05-28 13:15:04', '2016-05-09 11:52:51'),
(2, 'やがて御釈迦様はその池のふちに御佇みになって、水の面を蔽っている蓮の葉の間から、ふと下の容子を御覧になりました。この極楽の蓮池の下は、丁度地獄の底に当って居りますから、水晶のような水を透き徹して、三途の河や針の山の景色が、丁度覗き眼鏡を見るように、はっきりと見えるのでございます。', '蜘蛛の糸-2', '芥川龍之介-2', '2016-05-28 13:22:37', '2016-05-14 20:07:14'),
(3, 'するとその地獄の底に、陀多と云う男が一人、ほかの罪人と一しょに蠢いている姿が、御眼に止まりました。この陀多と云う男は、人を殺したり家に火をつけたり、いろいろ悪事を働いた大泥坊でございますが、それでもたった一つ、善い事を致した覚えがございます。と申しますのは、ある時この男が深い林の中を通りますと、小さな蜘蛛が一匹、路ばたを這って行くのが見えました。そこで陀多は早速足を挙げて、踏み殺そうと致しましたが、「いや、いや、これも小さいながら、命のあるものに違いない。その命を無暗にとると云う事は、いくら何でも可哀そうだ。」と、こう急に思い返して、とうとうその蜘蛛を殺さずに助けてやったからでございます。', '蜘蛛の糸-3', '芥川龍之介-3', '2016-05-28 13:18:36', '2016-05-14 20:08:07'),
(4, '御釈迦様は地獄の容子を御覧になりながら、この陀多には蜘蛛を助けた事があるのを御思い出しになりました。そうしてそれだけの善い事をした報には、出来るなら、この男を地獄から救い出してやろうと御考えになりました。幸い、側を見ますと、翡翠のような色をした蓮の葉の上に、極楽の蜘蛛が一匹、美しい銀色の糸をかけて居ります。御釈迦様はその蜘蛛の糸をそっと御手に御取りになって、玉のような白蓮の間から、遥か下にある地獄の底へ、まっすぐにそれを御下しなさいました。', '蜘蛛の糸-4', '芥川龍之介-4', '2016-05-28 13:17:26', '2016-05-14 20:08:11'),
(5, 'こちらは地獄の底の血の池で、ほかの罪人と一しょに、浮いたり沈んだりしていた陀多でございます。何しろどちらを見ても、まっ暗で、たまにそのくら暗からぼんやり浮き上っているものがあると思いますと、それは恐しい針の山の針が光るのでございますから、その心細さと云ったらございません。その上あたりは墓の中のようにしんと静まり返って、たまに聞えるものと云っては、ただ罪人がつく微な嘆息ばかりでございます。これはここへ落ちて来るほどの人間は、もうさまざまな地獄の責苦に疲れはてて、泣声を出す力さえなくなっているのでございましょう。ですからさすが大泥坊の陀多も、やはり血の池の血に咽びながら、まるで死にかかった蛙のように、ただもがいてばかり居りました。', '蜘蛛の糸-5', '芥川龍之介-5', '2016-05-28 13:16:09', '2016-05-09 11:52:51'),
(6, 'ところがある時の事でございます。何気なく陀多が頭を挙げて、血の池の空を眺めますと、そのひっそりとした暗の中を、遠い遠い天上から、銀色の蜘蛛の糸が、まるで人目にかかるのを恐れるように、一すじ細く光りながら、するすると自分の上へ垂れて参るのではございませんか。陀多はこれを見ると、思わず手を拍って喜びました。この糸に縋りついて、どこまでものぼって行けば、きっと地獄からぬけ出せるのに相違ございません。いや、うまく行くと、極楽へはいる事さえも出来ましょう。そうすれば、もう針の山へ追い上げられる事もなくなれば、血の池に沈められる事もある筈はございません。', '蜘蛛の糸-6', '芥川龍之介-6', '2016-05-28 13:19:27', '2016-05-14 20:08:07'),
(7, 'こう思いましたから陀多は、早速その蜘蛛の糸を両手でしっかりとつかみながら、一生懸命に上へ上へとたぐりのぼり始めました。元より大泥坊の事でございますから、こう云う事には昔から、慣れ切っているのでございます。', '蜘蛛の糸-7', '芥川龍之介-7', '2016-05-28 13:20:24', '2016-05-28 13:20:24');

--
-- ダンプしたテーブルのインデックス
--

--
-- テーブルのインデックス `board`
--
ALTER TABLE `board`
  ADD PRIMARY KEY (`row_no`),
  ADD UNIQUE KEY `row_no` (`row_no`);

--
-- ダンプしたテーブルのAUTO_INCREMENT
--

--
-- テーブルのAUTO_INCREMENT `board`
--
ALTER TABLE `board`
  MODIFY `row_no` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


inner join で増殖して追加

inner join で条件を書かなければ、n 行あれば、n x n 行の結果を返します。その際、同じ表を a と b で別名定義して自己結合させます。

結果の行は、board の 定義に合わせて a 表のみから取得して、row_no は 自動採番なので null as row_no として先頭に作成します。

その結果をさらに board に insert する事によって、row_no は、新しく自動採番された行として挿入されます。その結果 a( 最初の行数 ) + n( 元の行数 ) * n( 元の行数 ) の行を登録する事になります。
insert into board
select
	null as row_no,a.body,a.subject,a.from,a.pdate,a.cdate
	from board a
inner join `board` b
※ もう一回すると 3192 件になるので、これ以上は注意して実行する必要があります。

乱数関数で日付データのみ、ばらつかせる

ここではさほど厳密にバラつかせる必要は無かったので、時間部分に適用しています。
※ PIPES_AS_CONCAT は、文字列結合に Oracle と同じ || を使用可能にする設定です。
SET sql_mode='PIPES_AS_CONCAT';

update board set pdate = cast('2016/05/28 0' || truncate(Rand()*10,0) || ':00' as datetime)





posted by lightbox at 2020-05-11 19:36 | MySQL | このブログの読者になる | 更新情報をチェックする

2018年06月18日


MySQL で、Group by して合算された行内で最新データの明細を再び取り出す SQL

MySQL のドキュメントでは、『3.6.4 特定のカラムのグループごとの最大値が格納されている行』 として解説されていますが、それとは違ったアプローチで。( 全ての元の列を取り出します )

目的の SQL を実行する為に以下のようなテーブルを作成しました。

1) 同一取引先で、複数の日付で明細が存在します。
2) それぞれの行は code というユニークな主キーをもっています。
3) 取引先コードで group by すると、MySQL の拡張仕様で取得されるデータはどのような値になるか解りません
列名 型名 最大桁 NULL 主キー
1 code int N 1
2 取引日付 datetime
3 取引先コード varchar 4
4 明細 varchar 100
5 数量 int
set @@session.sql_mode='PIPES_AS_CONCAT' で連結を容易にできるようにします

日付を文字列として考えると、常に10桁で、その後にキーを結合しても max である事には変わりが無いので、インラインビューとしての結果を分割してキーを得ます。

そして、それに対して再び本体を結合させて全ての列データを取得します。
set @@session.sql_mode='PIPES_AS_CONCAT';
select
 * from
	(select
		最大日付とキー.取引先コード,
		substring_index(target, ',', -1) as key_data
		from
		(select
			取引先コード,max(取引日付||','||code) as target from
			一行伝票
			group by 取引先コード
		) 最大日付とキー
	) キーを取り出す

inner join 一行伝票

on key_data = code

order by キーを取り出す.取引先コード

取引先コード key_data code 取引日付 取引先コード 明細 数量
1 476 476 2005/8/17 1 この日が最新 14
2 997 997 2005/9/8 2 この日が最新 12
3 521 521 2005/9/8 3 この日が最新 9
4 820 820 2005/8/24 4 この日が最新 16



posted by lightbox at 2018-06-18 17:19 | MySQL | このブログの読者になる | 更新情報をチェックする

2018年05月05日


MySQL のコマンドラインの操作

mysql.exe のあるフォルダにパスを通さずに運用する為に、db_mysql.bat を作成します。
@setlocal
@path = %path%;C:\pleiades\xampp\mysql\bin
@cmd

endlocal
※ C:\pleiades\xampp\mysql\bin は、テストした PC で mysql.exe があるフォルダです

このバッチファイルをエクスプローラから実行して、コマンドラインから mysql に接続しますが、SQL を実行して表示するという目的に対していくつかのアプローチがあります。

-D オプションで データベースを選択して接続



接続してから mysql.exe のコマンドで  データベースを選択



最も単純な接続(可能な場合)



標準出力をパイプで渡して SQL 文を実行



テキストファイルをリダイレクトで渡して SQL 文を実行



テキストファイルを source コマンドで読み込んで SQL 文を実行




※ テキストファイルから SQL ステートメントを実行する

※ mysql コマンド






posted by lightbox at 2018-05-05 14:09 | MySQL | このブログの読者になる | 更新情報をチェックする

2017年05月19日


Windows 環境の MySQL から日本語名のテーブルを コマンドプロンプトでエクスポート(mysqldump.exe)する

Windows であっても、MySQL Community Edition(GPL) に対する操作はコマンドプロンプトから行います。Windows では、日本語は SHIFT_JIS( MySQL では cp932 を使用します )で表現されるので、一般的にサーバ側で UTF-8 で構築される MySQL では日本語のテーブル名を mysqldump.exe で認識してくれません。
(mysql.exe では cp932 で最初から調整されています)

show variables like 'cha%'


そこで、mysqldump で以下のようなコマンドラインを作成してデータのエクスポートを行います
mysqldump.exe --host=localhost --user=root --password=パスワード --add-drop-table --force --quote-names --default-character-set=cp932 lightbox 商品マスタ 得意先マスタ > export.sql
▼ 上記コマンドラインに改行を入れて解りやすく並べています
mysqldump.exe
 --host=localhost
 --user=root
 --password=パスワード
 --add-drop-table
 --force
 --quote-names
 --default-character-set=cp932
 lightbox 商品マスタ 得意先マスタ > export.sql
lightbox は、DB名で、商品マスタと得意先マスタはテーブル名です この結果取得される export.sql は以下のようになります
-- MySQL dump 10.13  Distrib 5.7.17, for Win64 (x86_64)
--
-- Host: localhost    Database: lightbox
-- ------------------------------------------------------
-- Server version	5.7.17-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES cp932 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `商品マスタ`
--

DROP TABLE IF EXISTS `商品マスタ`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `蝠・刀繝槭せ繧ソ` (
  `蝠・刀繧ウ繝シ繝荏 varchar(4) NOT NULL,
  `蝠・刀蜷港 varchar(50) DEFAULT NULL,
  `蝨ィ蠎ォ隧穂セ。蜊倅セ。` int(11) DEFAULT NULL,
  `雋ゥ螢イ蜊倅セ。` int(11) DEFAULT NULL,
  `蝠・刀蛻・。杼 varchar(3) DEFAULT NULL,
  `蝠・刀蛹コ蛻・ varchar(1) DEFAULT NULL,
  `菴懈・譌・` datetime DEFAULT NULL,
  `譖エ譁ー譌・` datetime DEFAULT NULL,
  `蛯呵?チ mediumtext,
  `蜑企勁繝輔Λ繧ー` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`蝠・刀繧ウ繝シ繝荏)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `商品マスタ`
--

LOCK TABLES `商品マスタ` WRITE;
/*!40000 ALTER TABLE `商品マスタ` DISABLE KEYS */;
省略(cp932 の insert)
/*!40000 ALTER TABLE `商品マスタ` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `得意先マスタ`
--

DROP TABLE IF EXISTS `得意先マスタ`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `蠕玲э蜈医・繧ケ繧ソ` (
  `蠕玲э蜈医さ繝シ繝荏 varchar(4) NOT NULL,
  `蠕玲э蜈亥錐` varchar(50) DEFAULT NULL,
  `蠕玲э蜈亥玄蛻・ varchar(1) DEFAULT NULL,
  `諡・ス楢?・ varchar(4) DEFAULT NULL,
  `驛オ萓ソ逡ェ蜿キ` varchar(7) DEFAULT NULL,
  `菴乗園・疏 varchar(100) DEFAULT NULL,
  `菴乗園・蛋 varchar(100) DEFAULT NULL,
  `菴懈・譌・` datetime DEFAULT NULL,
  `譖エ譁ー譌・` datetime DEFAULT NULL,
  `邱譌・` int(11) DEFAULT NULL,
  `邱譌・蛹コ蛻・ int(11) DEFAULT NULL,
  `謾ッ謇墓律` int(11) DEFAULT NULL,
  `蛯呵?チ varchar(100) DEFAULT NULL,
  PRIMARY KEY (`蠕玲э蜈医さ繝シ繝荏)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `得意先マスタ`
--

LOCK TABLES `得意先マスタ` WRITE;
/*!40000 ALTER TABLE `得意先マスタ` DISABLE KEYS */;
省略(cp932 の insert)
/*!40000 ALTER TABLE `得意先マスタ` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-05-19 17:34:14

このテキストファイルは、SHIFT_JIS として表示した場合、CREATE TABLE の部分が化けたように見えますが、その直前で /*!40101 SET character_set_client = utf8 */; として utf8 として判断されるようになっているので、MySQL サーバであれば Windows でもレンタルサーバでも正しく動作します(インポート時はレンタルサーバの phpmyadmin で SHIFT_JIS として読み込む)

コマンドプロンプト環境の作成

仮に、c:\user\dos というフォルダにパスを通したとして、この中に以下のバッチファイルを保存します

1) MySQL のコマンドプロンプトからの処理用バッチファイル
(MySQL のコマンドが保存されているパスは PC によって変わります)

2) 管理者権限のコマンドプロンプトを開くバッチファイル

そして、普段 MySQL のサービスは停止しているとして、コマンドプロンプトからサービスを開始する為、ファイル名を指定して実行から、admin と入力して管理者権限でコマンドプロンプトを開きます。

1) sc start MySQL57


2) sc query MySQL57

※ MySQL57 は、システムに登録されたサービス名

表示結果より、『STATE : 4  RUNNING』である事を確認したら、mysql_cmd と入力します(サービスが起動済みならば、ファイル名を指定して実行より、mysql_cmd と入力)。

そして、mysqldump を使用してバックアップを行います。

補足

MySQL で使用可能なキャラクタセット文字列は、SHOW CHARACTER SET で取得できます




posted by lightbox at 2017-05-19 18:05 | MySQL | このブログの読者になる | 更新情報をチェックする

2017年04月25日


MySQLの日本語オンラインマニュアル、5.6 とその中のいくつかの重要なリンク

オンラインは最新が 5.7 で、英文のみですが、5.6 は日本語版があります

アーカイブ
日本語の 5.1 と 4.1 のマニュアルを PDF でダウンロード(表示)できるようです

▼ よく使うセクション
第13章 SQL ステートメントの構文

※ こちらも重要なので
4.5.1. mysql − MySQL コマンド行ツール
4.5.4 mysqldump − データベースバックアッププログラム

▼ Windows の 5.6 のコマンドラインオプションです
http://winofsql.jp/mysqlhelp.txt

▼ Windows の コマンドプロンプトで、SHIFT_JIS(cp932) に設定されている事を確認した処理です


関連する記事

MySQL のコマンドプロンプトからの処理用バッチファイル


どうしても当時のオリジナルを確認したい場合

WEB アーカイブより http://dev.mysql.com/doc/refman/5.1/ja/ で確認できます( 2014/7/3 ぶんより )



タグ:MySQL
posted by lightbox at 2017-04-25 10:52 | MySQL | このブログの読者になる | 更新情報をチェックする

2017年04月24日


MySQL のコマンドプロンプトからの処理用バッチファイル

普通に MySQL をインストールすると、bin フォルダまでは階層が深いので、以下のように専用のバッチファイルを作れば、PATH 環境変数を使う必要もありません。

最初に mysql -u root -p でテストするといいと思います。

※ バッチファイルの終了は、exit です。
mysql_cmd.bat
@echo off
setlocal
set path=C:\Program Files\MySQL\MySQL Server 5.7\bin;%path%
prompt cmd(mysql)$G

cmd.exe /k cls

endlocal

MySQL に接続(パスワードは入力)
cmd(mysql)>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

この後、use データベース と show tables; で動作確認。

※ mysql終了は、exit または quit です。



タグ:MySQL
posted by lightbox at 2017-04-24 17:17 | MySQL | このブログの読者になる | 更新情報をチェックする
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定> 詳細設定> ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

Seesaa のページで、アーカイブとタグページは要注意です。タグページはコンテンツが全く無い状態になりますし、アーカイブページも歯抜けページはコンテンツが存在しないのにページが表示されてしまいます。

また、カテゴリページもそういう意味では完全ではありません。『カテゴリID-番号』というフォーマットで表示されるページですが、実際存在するより大きな番号でも表示されてしまいます。

※ インデックスページのみ、実際の記事数を超えたページを指定しても最後のページが表示されるようです

対処としては、このようなヘルプ的な情報を固定でページの最後に表示するようにするといいでしょう。具体的には、メインの記事コンテンツの下に『自由形式』を追加し、アーカイブとカテゴリページでのみ表示するように設定し、コンテンツを用意するといいと思います。


※ エキスパートモードで表示しています

アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります

<% if:page_name eq 'archive' -%>
アーカイブページでのみ表示される内容
<% /if %>

<% if:page_name eq 'category' -%>
カテゴリページでのみ表示される内容
<% /if %>

<% if:page_name eq 'tag' -%>
タグページでのみ表示される内容
<% /if %>
この記述は、以下の場所で使用します
container 終わり



フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

CSS ドロップシャドウの参考デモ
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり