SQLの窓

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_mydql.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 | このブログの読者になる | 更新情報をチェックする

2016年06月18日


MySQL で、主キーの作り変えのプロセス / serial では、自動的に インデックスが作成されます

serial は、自動採番列を作成する為に使いますが、その際列名でユニークインデックスが作成されています。しかし、bigint(20) unsigned NOT NULL AUTO_INCREMENT で作成した場合は何も作成されないので、削除しても動作します。

ストーリー

掲示板テーブルを作成しましたが、主キーを自動採番列としているので、一つの掲示板でしか使用する事が出来ないため、掲示板(スレッド)を区別する為の ID として no 列を追加する事が目的です。

現状確認

show create table board で、MySQL に登録されている create table 文を取得します。この時、UNIQUE KEY `row_no` (`row_no`) が定義されている事が解ります。

列追加

alter table `board` add `no` VARCHAR(20) after `row_no` で、元の主キーである row_no の後ろに no 列を追加します。この時、既にデータが投入されている場合は、no の中は null が設定されます。

主キー削除

主キーを作り変える為に、いったん現在の主キーを alter table board drop primary key で削除します。そして、show create table board で定義の変化を確認します。

主キー作成

既にデータが投入されている場合は、no 列が null の為、主キーが作成できません。なので、全ての列のデータに何かデータをセットします( update board set no = '0' )

その後、alter table board add primary key(`row_no`,`no`) で主キーを作成します。

serial で作成されたユニークキーの削除

特に実行する必要はありませんが、それほど必要なインデックスでは無いので削除する場合は、show index from board でインデックス名を確認して、alter table board drop index row_no で削除します。

データの復帰

既に新しいフォーマットで作成されたデータがある場合は、insert into board select * from board2 という SQL でデータを投入できます。
※ board2 が保存されていた新しいフォーマットのデータ

-- 最初に作成したテーブルのSQL
CREATE TABLE `board` (
  `row_no` serial,
  `body` varchar(1000),
  `subject` varchar(200),
  `from` varchar(20),
  `pdate` datetime,
  `cdate` datetime,
  primary key(`row_no`)
);

show create table board;

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

-- 主キーに掲示板IDを追加する為に、no 列を row_no の後に追加
alter table `board` add `no` VARCHAR(20) after `row_no`;

-- 主キーの削除
alter table board drop primary key;

show create table board;

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

--※ すでにデータがある場合( no にセットするのは適当な文字列 )
update board set no = '0';

-- 主キー作成
alter table board add primary key(`row_no`,`no`);

show create table board;

CREATE TABLE `board` (
  `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `no` varchar(20) NOT NULL DEFAULT '',
  `body` varchar(1000) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `from` varchar(20) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `cdate` datetime DEFAULT NULL,
  PRIMARY KEY (`row_no`,`no`),
  UNIQUE KEY `row_no` (`row_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- インデックスの確認
show index from board;

-- インデックスの削除( ここは内容確認の為 )
alter table board drop index row_no;

CREATE TABLE `board` (
  `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `no` varchar(20) NOT NULL DEFAULT '',
  `body` varchar(1000) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `from` varchar(20) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `cdate` datetime DEFAULT NULL,
  PRIMARY KEY (`row_no`,`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 保存していたデータの復帰
insert into board select * from board2;




タグ:SQL
posted by lightbox at 2016-06-18 13:38 | 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 ドロップシャドウの参考デモ
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり