Hatena::ブログ(Diary)

konisimple log RSSフィード

はてなブログに移転しました!

2010年07月22日

SQL文だけで面倒な「今から10分以内の行のみを表示」を書く

よく今から10分以内に更新された行がほしいと思ったら

SELECT * FROM hoge WHERE datetime>'2010-07-23 0:49:33'

みたいに書くけど、これをPHPとかSQL呼び出し側で書くのは結構面倒。

そこで以下のように書けばちょっと楽!

SELECT * FROM hoge WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(datetime)<10*60

※書く分量は増えたけどこのほうが見やすい気がしました。

このようにSQLでは便利な関数をいっぱい使えるのでちょっとしたプログラムを書くのにはとても便利。

けど実用にはこの書き方は速度遅そうですね。

(追記)あんま楽じゃないような気がしてきましたw普通に前者でいいような気もしますw

2010年03月08日

INSERT文にSELECT文埋め込んでサブクエリを投げるのにあたって知っておくべき基本

まずSELECT文についておさらいから。

SELECTの直後には欲しい列を列挙

SELECTと書いた直後には返して欲しい列を列挙する。

SELECT item,price FROM items WHERE 1

とすると

itemprice
いちご100
みかん200

列名を変える!

ここで列のあとにスペースを開けると列に名前がつけられる。

SELECT item 商品名,price 値段 FROM items WHERE 1

とすると

商品名値段
いちご100
みかん200

これで列の名前を自由につけられるようになった。

これでサブクエリ(副問い合わせ)するときに列の名前をあわせることができる。

SELECT文には定数も使える!

実はSELECT文の結果の列には定数もつけられるのだ!例えば

SELECT item 商品名,price 値段,"201003" 販売月 FROM items WHERE 1

とすると

商品名値段販売月
item1100201003
item2200201003
item3300201003
item4400201003
item5500201003

という感じで定数の列がつけられる。

これはただSELECT文一個呼び出すときは無意味だけど、サブクエリ(副問い合わせ)をするとき便利。

また、計算もできる

SELECT 1+1, 2+2

とすると

1+12+2
24

が帰ってくる。

いざ、サブクエリ(副問い合わせ)

そこで以下のSQL文。

INSERT INTO `minilog`(keyword,total,date)
SELECT keyword,COUNT(keyword) total,'2010-03-08' date
 FROM `log`
 WHERE datetime BETWEEN '2010-03-08 00:00:00' AND '2010-03-08 23:59:59'
 GROUP BY keyword ORDER BY COUNT(keyword) DESC

これは

logテーブルの3月8日の行

keyworddatetime
あいう2010-03-08 12:34:56
かきく2010-03-08 12:34:56
あいう2010-03-08 12:34:56
さしす2010-03-08 12:34:56
さしす2010-03-08 12:34:56
あいう2010-03-08 12:34:56

から、

minilogテーブルに

keywordtotaldatetime
あいう32010-03-08
かきく12010-03-08
さしす22010-03-08

という行を追加するSQL文である。

要は例にグラビア画像サイトのランキング実装にあたってSQLの勉強をしてへーって思った部分をメモしてみましたってことです。

おまけ

これを毎日実行しておけば、

SELECT keyword, SUM( total ) total
FROM `minilog`
WHERE 1
GROUP BY keyword
ORDER BY SUM( total ) DESC

を投げればキーワードランキングが得られる!

参考

SQL プログラミング言語資料 - SAK Streets

2009年12月06日

MacBookのターミナルでJavaのJDBCからMySQLをいじるときに文字化けするときには

大学の課題で文字化けにはまった。

僕の場合結果的に以下のうち2つを実行すれば文字化けを解消することができたのですが、その過程でいろんな方法を見つけたのでメモしておきます。

文字化けしないための一番楽な方法は、全ての文字コードを統一すること。・・・ということで僕はUTF-8に統一しています。

まずMySQLのデータベースはUTF-8で作成。

SET CHARACTER SET utf8;
CREATE DATABASE Exercise default character set utf8;

次に.javaファイルもUTF-8で保存。そしてJDBCからデータベースに接続するときは以下のように文字コードを明示*1

DriverManager.getConnection("jdbc:mysql://localhost/Exercise?useUnicode=true&characterEncoding=UTF8", "root", "1234");

コンパイル&実行時にも指定しとく*2

javac -encoding UTF-8 Test.java
java -Dfile.encoding=UTF-8 Test 

※僕はJavaとかよくわからないのでいつもこれでもか!これでもか!と文字コードを指定しまくる戦法をとりますw

2009年11月29日

Macにmysql入れてて日本語データの文字化けする場合の処方箋

大学の「データベース」の授業でmacbookにMySql入れてデータベース作ったりいじったりしてるんだけど、そこで文字化けに悩んだのでメモ*1

本当は設定ファイルいじったりして根本的な解決策があるのかもしれないけど、面倒なので毎回このSQL文を実行してごまかしていますw

どうも外部ファイルから日本語が入ったtsvファイルからのLOAD DATA INFILEができない。そこで以下のようにしたらうまくいきました!

まずは、ターミナルからMySQLにログイン。次に以下のSQL文を実行。

SET CHARACTER SET utf8;
CREATE DATABASE Exercise default character set utf8;

これは僕はUTF-8が好きなので全部UTF-8に統一しちゃえ!という作戦です。

これで

USE Exercise;
CREATE TABLE Student (Student_ID CHAR(4) NOT NULL PRIMARY KEY,Student_name NCHAR(12),Entrance_year INT(4),Age INT(2) DEFAULT 18,Gender NCHAR(2),Birth_place NCHAR(10));
LOAD DATA INFILE '/Users/masa/student.tsv' INTO TABLE Student;

って感じでLOAD DATA INFILEしたらちゃんと入りましたっと。

それから日本語はちゃんとNCHAR型にしないとエラーになるっぽいです!CHARじゃだめよっ!!

*1:このメモは「こうすれば絶対できる」ではなくて、「僕はこうやったら出来たよ、よかったら参考にしてね」っていうメモです。