えっへん！


GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';


mysql グループ毎、最初のレコードを取得 Select first recorder in every group

方法１：

SELECT * FROM
(
SELECT * FROM `table`
ORDER BY AnotherColumn
) t1
GROUP BY SomeColumn
;





方法２：

SELECT somecolumn, anothercolumn
FROM sometable
WHERE id IN (
SELECT min(id)
FROM sometable
GROUP BY somecolumn
);


DBバックアップスクリプト

backup_mydb.sh、※「--ignore-table」は一部のテーブルを除外するときに使う

#!/bin/sh
dirpath='/home/admin/mydb_dump/dump'
filename=`date +%Y%m%d`
mysqldump -uDB_USER -pDB_PASSWORD DB_NAME --ignore-table=mydb.table1 --ignore-table=mydb.table2 > $dirpath/mydb_$filename.dump
chmod 700 $dirpath/mydb_$filename.dump
tar cpzvf $dirpath/mydb_$filename.dump.tar.gz $dirpath/mydb_$filename.dump
rm -f $dirpath/mydb_$filename.dump
find $dirpath/mydb_????????.dump.tar.gz -type f -mtime +365 -ls -exec rm -f -- {} \;

実行権限を付けます

chmod +x backup_mydb_db.sh

そしてcron設定、毎月１日零時10分くらいバックアップを取る＋一年前のバックアップを削除

crontab -e
10 0 1 * * /home/admin/mydb_dump/backup_mydb.sh


INSERT INTO 複数VALUES時のON DUPLICATE KEY UPDATEの書き方

CREATE TABLE table_a (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  UNIQUE (a, b)
);

INSERT INTO table_a (a, b, c) VALUES (1, 2, 0),(3, 4, 5) ON DUPLICATE KEY UPDATE c = VALUES(c);

ado,sqlのunderline…自分かバカすぎで笑えない

sqlではアンダーライン"_"は、任意の一文字って意味か、ADOでもMysqlでも...、やられた、ADOでは"[]"でエスケープする、sqlでは"\"でエスケープする。
正規表現とごっちゃまぜしちゃダメよ。(;_;)


SELECT * FROM tbl WHERE tname LIKE 'abc[_]%'

SQL

SELECT * FROM tbl WHERE tname LIKE 'abc\_%'


OSが64bitでも64bitのODBC Connectorを使ってはいけません！Officeのbit数を確認べし…そうなの？！！

ぬぬぬ、罠だ！
64bitのOSも絶対64bitのMySQL Connector/ODBC

https://dev.mysql.com/downloads/connector/odbc/

64bitを使うと
「データ ソース名および指定された既定のドライバーが見つかりません」がでます

32bitを使うとすーっと通る

なぜた？！！！

うわー、Officeが32bitじゃん！！バカが！！！っと脱力した…

Another MySQL daemon already running with the same unix socket.

mysqlを起動するとき、このエラーメッセージがでたら
Another MySQL daemon already running with the same unix socket.

service mysqld stop
mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak
service mysqld start

で解決できるらしい

VBAでクエリーが日本語を含めると結果が文字化ける件

Sub hh()
Dim sql As String
Dim rs As New ADODB.Recordset
Dim con As ADODB.Connection
Dim dbConnStr As String
dbConnStr = "Driver={MySQL ODBC 5.2 ANSI DRIVER}; SERVER=localhost; DATABASE=landscape; USER=root; PASSWORD=mypass;"
Set con = New ADODB.Connection
con.Open dbConnStr
sql = "SELECT '東京都' AS tokyou"
rs.Open sql, con
Debug.Print rs!tokyou
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Sub

結果表示

東・

ここでは「Driver={MySQL ODBC 5.2 ANSI DRIVER}」を「Driver={MySQL ODBC 5.2 UNICODE DRIVER}」に変更すればOK
また、「rs!tokyou」は「rs.Fields("tokyou")」で書いたほうがいいと思います。

SQLでbit演算,合計値格納項目のクエリー方法

DB定義書ではこのような項目がありました

my_field NULL →　1:ああああ　2:いいいい　4:うううう　8:ええええ　16:おおおお　(合計値格納)

つまり複数選択可能な列です。
例えば「1:ああああ　2:いいいい」が選択されてDBに登録したらmy_fieldの値は1+2=「3」です。

そうするとクエリーの書き方は：
例えば「8:ええええ」に含まれるレコードを抽出したいときは：

......... where my_field & 8 = 8

でOKです。

SQLクエリー、リレーションテーブルの行を列にして、マトリックスを作る

こういうことをやりたい：

ダイナミックで縦列を作成するにはこのスレ
シンプルで手作業でいいんなら


まずサンプルデータを作る

create table user (id int , name varchar(10));
insert into user values
(1,'AA'),
(2,'BB'),
(3,'CC'),
(4,'DD'),
(5,'EE'),
(6,'FF'),
(7,'GG');

create table role (id int ,role varchar(10));
insert into role values
(1,'FW'),(2,'DF'),(3,'GK'),(4,'MF') ;

create table user_role (user_id int ,role_id int);
insert into user_role values
(1,1),(1,2),(2,1),(2,3),(3,4),(4,2),(5,1),(5,2),(6,2),(6,3),(7,1),(7,4);


そしてクエリーは

select
id,
name,
coalesce(max(t.FW),'No') as FW,
coalesce(max(t.DF),'No') as DF,
coalesce(max(t.GK),'No') as GK,
coalesce(max(t.MF),'No') as MF
from user u
left join (
select
case
when r.id = 1 AND ur.role_id is not null then 'Yes'
else null
end `FW`,

case
when r.id = 2 AND ur.role_id is not null then 'Yes'
else null
end `DF`,

case
when r.id = 3 AND ur.role_id is not null then 'Yes'
else null
end `GK`,

case
when r.id = 4 AND ur.role_id is not null then 'Yes'
else null
end `MF`,
user_id
from role r
left join user_role ur on ur.role_id = r.id
)t
on t.user_id = u.id
group by u.id


クエリー結果

ID	NAME	FW	DF	GK	MF
1	AA	Yes	Yes	No	No
2	BB	Yes	No	Yes	No
3	CC	No	No	No	Yes
4	DD	No	Yes	No	No
5	EE	Yes	Yes	No	No
6	FF	No	Yes	Yes	No
7	GG	Yes	No	No	Yes

質問したURL
http://stackoverflow.com/questions/24425234/how-can-i-get-a-matrix-table-from-two-related-table-by-one-query-statement/24426021#24426021
Abhik Chakrabortyさん、ありがとうございました。

DEMO

クエリーシミュレーション、すげー使えるサイト
http://sqlfiddle.com

create a super user can login from every where


GRANT ALL PRIVILEGES ON *.* TO myuser@'%' IDENTIFIED BY 'some_password' WITH GRANT OPTION;


mysqlの正規表現REGEXPと文字列抽出SUBSTRING,LOCATEで括弧の中の内容を抽出する

mysqlの正規表現と文字列抽出で括弧の中の内容を抽出する

共同住宅(45戸)
↓
45戸

一般の正規表現では数字を表すのは'\d'で、非数字は'\D'で行けるはずだが、
mysqlでは'[0-9]'が数字みたい。本当？
また括弧'('を表示するのに、'\('ではなく、'[.(.]'と'[.).]'です。
http://dev.mysql.com/doc/refman/5.1-olh/ja/regexp.html
クエリーはこんな感じ

SELECT stringWithBracket,
TRIM(SUBSTRING(stringWithBracket, LOCATE('(',stringWithBracket)+1, LOCATE(')',stringWithBracket)- LOCATE('(',stringWithBracket)-1)) AS house
FROM myTable
WHERE t_gyoumu.shuyou_youto REGEXP '[.(.][0-9]+戸[.).]';


mysql、ユーザを作成して、そして同名データベースを作成して、すべての特権を付与する


CREATE USER 'lands'@'%' IDENTIFIED BY '***'; GRANT USAGE ON *.* TO 'lands'@'%' IDENTIFIED BY '***';
CREATE DATABASE IF NOT EXISTS `lands`; GRANT ALL PRIVILEGES ON `lands`.* TO 'lands'@'%';


mysqlの日付の比較メソッド,datediffとperiod_diff


select abs(datediff('2013-01-01','2014-01-01'));
#365
select period_diff(date_format('2014-03-06', '%Y%m'), date_format('2012-03-06', '%Y%m')) as month;
#24


mysqldump コマンド常用パターン

引用元（LAYER8）：
mysqldumpで複数テーブルもしくは特定のテーブルなど条件指定でレコードを出力する方法



特定のテーブル(複数可)のレコードのみをdumpする場合(テーブル作成情報を書き込まない)
$ mysqldump -u ユーザ名 -p -t データベース名 テーブル1 テーブル2...> ファイル名

データベース全体のレコードのみをdumpする場合(テーブル作成情報を書き込まない)
$ mysqldump -u ユーザ名 -p -t データベース名 > ファイル名

データベース全体のテーブル構造のみをダンプする場合(レコード情報を一切書き込まない)
$ mysqldump -u ユーザ名 -p -d データベース名 > ファイル名

一定の条件を満たすレコードのみdumpする
$ mysqldump -u ユーザ名 -p -t "--where=カラム名='文字列'" データベース名 テーブル名 > ファイル名
$ mysqldump -u ユーザ名 -p -t "-wカラム名>数値" データベース名 テーブル名 > ファイル名


rubyでFTPからCSV(?)をゲットして、DBに突っ込む

ftp.yml

remote_path: pub/test
retry: 3
host: 192.168.1.70
username: anonymous
password: anonymous


get_ftp.rb
sample usage: # ruby get_ftp.rb index_hist
will get 'index_hist.gz' from ftp and gunzip it to index_hist

require 'net/ftp'
require 'yaml'
cnt_retry = 0
puts "start process..."
begin
  ftp_cfg = YAML.load_file("ftp.yml")
  fn = ARGV[0]

  ftp = Net::FTP.open(ftp_cfg["host"])
  ftp.login(ftp_cfg["username"],ftp_cfg["password"])
  ftp.chdir(ftp_cfg["remote_path"])
  puts "getting #{fn}.gz from FTP for attempt #{cnt_retry}..."
  ftp.getbinaryfile("#{fn}.gz")
  system("gunzip -f #{fn}.gz")
  ftp.close
rescue => err
  if cnt_retry + 1 < 3
    cnt_retry += 1
    sleep 5
    retry
  else
    raise err
    log.error "There was an error: #{err.message}"
  end
else
  puts "Job done."
end

import to a mysql database db.yml host: 192.168.1.70
username: kagen
password: kagen
database: db_development import_indices.rb
sample usage1: # ruby import_indices.rb master index_master
will import data from index_master into table 'indices'
sample usage2: # ruby import_indices.rb hist index_hist
will import data from index_hist into table 'index_value_hist'

require 'yaml'
require 'kconv'
require 'mysql'
puts "start process of data import..."
begin
  my = Mysql::init()
  db_cfg = YAML.load_file("db.yml")
  if ARGV[0] == "master"
    tbl = "indices"
    fld = "(itemcode, jpname, engname, jpsourcename, engunitname, unit, decimalpoint, startmonth, updated_at)"
  else
    tbl = "index_value_hist"
    fld = "(itemcode, subcode, cycle, yyyy, mm, dd, val, updated_at)"
  end
  puts "Connecting to host #{db_cfg["host"]} with user #{db_cfg["username"]} using database #{db_cfg["database"]}..."
  my.real_connect(db_cfg["host"], db_cfg["username"], db_cfg["password"], db_cfg["database"])
  puts "Connected Successfully"
  my.query("SET AUTOCOMMIT=0")
  puts "Start transaction..."
  my.query("START TRANSACTION")
  if ARGV[0] == "master"
    puts "Processing Index master"
  elsif ARGV[0] == "hist"
    puts "Processing Index values history"
  end

  begin
    File.open(ARGV[1]) do |f|
      f.each_line do |row|
        sql = nil
        rows = row.split(":")
        #puts rows.inspect
        if ARGV[0] == "master"
          sql = "INSERT INTO #{tbl} #{fld} VALUES('#{rows[0]}', '#{rows[1].toutf8}', '#{rows[2]}', '#{rows[3].toutf8}', '#{rows[4].toutf8}', '#{rows[5]}', '#{rows[6]}', '#{rows[7]}', CURRENT_TIMESTAMP)"
        elsif ARGV[0] == "hist"
          if rows[3] == "D"
            if rows[0].strip == "DEL"
              sql = "DELETE FROM #{tbl} WHERE itemcode = '#{rows[1].strip}' AND subcode = '#{rows[2].strip}' AND cycle = 'D' AND yyyy = '#{rows[4][0..3]}' AND mm = '#{rows[4][4..5]}' AND dd = '#{rows[4][6..7]}'"
            elsif rows[0].strip == "UPD"
              sql = "INSERT INTO #{tbl} #{fld} VALUES('#{rows[1].strip}', '#{rows[2].strip}', 'D', '#{rows[4][0..3]}', '#{rows[4][4..5]}', '#{rows[4][6..7]}', '#{rows[5]}', CURRENT_TIMESTAMP)"
            end
          elsif rows[3] == "M"
            if rows[0].strip == "DEL"
              sql = "DELETE FROM #{tbl} WHERE itemcode = '#{rows[1].strip}' AND subcode = '#{rows[2].strip}' AND cycle = 'D' AND yyyy = '#{rows[4][0..3]}' AND mm = '#{rows[4][4..5]}'"
            elsif rows[0].strip == "UPD"
              sql = "INSERT INTO #{tbl} #{fld} VALUES('#{rows[1].strip}', '#{rows[2].strip}', 'M', '#{rows[4][0..3]}', '#{rows[4][4..5]}', '', '#{rows[5]}', CURRENT_TIMESTAMP)"
            end
          end
        end
        if sql
          #puts sql
          my.query(sql)
        end
      end
    end
    my.query("COMMIT")
  rescue => err
    puts "rollback changes..."
    my.query("ROLLBACK")
    raise err
    log.error "There was an error while insert db: #{err.message}"
  end
rescue => err
  raise err
  log.error "There was an error: #{err.message}"
else
  puts "Job done."
end

create tabless CREATE TABLE `indices` (
`itemcode` varchar(15) NOT NULL ,
`jpname` varchar(30) NOT NULL ,
`engname` varchar(28) NOT NULL ,
`jpsourcename` varchar(32) NOT NULL ,
`jpunitname` varchar(30) NOT NULL ,
`engunitname` varchar(30) NOT NULL ,
`unit` int(8) NOT NULL ,
`decimalpoint` int(8) NOT NULL ,
`startmonth` int(8) NOT NULL ,
`updated_at` datetime NOT NULL ,
PRIMARY KEY (`itemcode`)
) type=InnoDB;
CREATE TABLE `index_value_hist` (
`itemcode` varchar(15) NOT NULL ,
`subcode` varchar(5) NOT NULL ,
`cycle` varchar(1) NOT NULL ,
`yyyy` varchar(4) NOT NULL ,
`mm` varchar(2) NOT NULL ,
`dd` varchar(2) NOT NULL ,
`val` double(24,7) NOT NULL ,
`updated_at` date NOT NULL ,
PRIMARY KEY (`itemcode`, `subcode`, `cycle`, `yyyy`, `mm`, `dd`)
) type=InnoDB;
Sample Data to import (EUC, LF) No title row ☆index_value_hist only☆ UPD :AREGEN :A :M:201212:150463.7400000:20121218
UPD :AREGEN :C :M:201212:158030.9800000:20121218
UPD :AREGEN :C :W:2012124:158030.9800000:20121218
UPD :AREGEN :H :M:201212:158030.9800000:20121218
...

I am superuser


mysql> CREATE USER 'kagen'@'%' IDENTIFIED BY 'kagen';
GRANT ALL PRIVILEGES ON *.* TO 'kagen'@'%';


mysqlでCURSORを使ったループ(LOOP)でINSERT

#まず削除

DROP PROCEDURE IF EXISTS myProc;

#作成

#日付をプロシジャーの引数に設定、戻り値はない

CREATE PROCEDURE myProc(IN aft_date DATETIME)

BEGIN

  DECLARE done INT DEFAULT FALSE;#ループを抜けるフラグ

  DECLARE dt DATETIME;#日付ごとにループ

  DECLARE tdays CURSOR FOR SELECT aod as as_of_date FROM date_ots where aod > aft_date order by aod asc;#ループする日付たち

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;#日付が全部回ったら終了フラグ



  OPEN tdays;#取得する



  read_loop: LOOP#ループ開始

    FETCH tdays INTO dt;

    IF done THEN

      LEAVE read_loop;#ループを抜ける

    END IF;



insert into fund_correction_indices

select null,f.id,rt.aod,0,ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4,ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4,0

from 

(

    select dto.aod,f.fund_small_category_id,

        avg(ci0.correction_value/ci1.correction_value) as rate1,

        avg(ci1.correction_value/ci2.correction_value) as rate2,

        avg(ci2.correction_value/ci3.correction_value) as rate3,

        avg(ci3.correction_value/ci4.correction_value) as rate4,

        avg(ci0.correction_value/ci4.correction_value) as rate_,

        dto.4thd 

    from date_ots dto 

        inner join fund_correction_indices ci0 on ci0.as_of_date = dto.aod

        inner join funds f on f.id = ci0.fund_id and f.kind not in ('平均','指数') 

        inner join fund_correction_indices ci1 on ci1.as_of_date = dto.1std and ci1.fund_id = ci0.fund_id

        inner join fund_correction_indices ci2 on ci2.as_of_date = dto.2ndd and ci2.fund_id = ci0.fund_id

        inner join fund_correction_indices ci3 on ci3.as_of_date = dto.3rdd and ci3.fund_id = ci0.fund_id

        inner join fund_correction_indices ci4 on ci4.as_of_date = dto.4thd and ci4.fund_id = ci0.fund_id

    where dto.aod = dt

        and f.fund_small_category_id >=1 and f.fund_small_category_id <=57

    group by fund_small_category_id,dto.aod

) rt

    inner join funds f on f.fund_small_category_id = rt.fund_small_category_id and f.kind = '平均'

    inner join fund_correction_indices ci4 on ci4.as_of_date = rt.4thd and ci4.fund_id = f.id

order by f.id,rt.aod 

ON DUPLICATE KEY UPDATE 

  value=ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4,

  correction_value=ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4;

  END LOOP;

  CLOSE tdays;

END



呼び出し

CALL myProc('2012-02-01')


もしCREATE PROCEDUREの時「Column count of mysql.proc is wrong」みたいなエラーが出たら

$ mysql_upgrade -uroot -p
rootパスワード入力

で直してあげてください。

INSERT ON DUPLICATE KEY UPDATE

feel this useful,copy from here
http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update


CREATE TABLE `users_partners` (
`uid` int(11) NOT NULL DEFAULT '0',
`pid` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`,`pid`),
KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected


mysqlテーブルコピー


/* スキーマをコピーしてテーブル作成 */
> CREATE TABLE mytable_copy LIKE mytable;
/* mytableテーブルのデータをINSERT */
> INSERT INTO mytable_copy SELECT * FROM mytable;