SQL雑記帳 ITかあさん

ITかあさん

Mysql 重複している数順でソート

Mysql 重複している数順でソート

Mysqlで重複している数順にソートするSQLのサンプルです。あんまり使いどころは多くないですが、ブログのタグやカテゴリーなど、使いどころがありますね。

SELECT count(*),text
FROM table_name
GROUP BY text
HAVING count(*)>1
ORDER BY count(*) DESC

これで、textのデータの重複している数順にソートします。HAVING count(*)>1を除くと、重複していないデータも結果として返ってきます。

メモメモ。

Mysqlで便利な文字列一括置き換えのSQL

Mysqlで一括で文字列操作したい!

いかにもPHPっぽい考え方ですが、Mysqlにも便利な文字列操作がありまして、仕事でこの度すっごく役立ちましたのでメモしておきます

TRIM関数

文字列の左右の空白を除去

//フィールドから末尾の全角空白を取り除く場合
UPDATE table_name SET field_name = TRIM(TRAILING ' ' FROM field_name);
//フィールドの先頭からの全角空白を取り除く場合
UPDATE table_name SET field_name = TRIM(LEADING ' ' FROM field_name);

REPLACE関数

文字の置き換えを行う マルチバイト対応

//データの検索
SELECT REPLACE(field_name,'置換対象','置換後の文字') FROM table_name;
//データの更新
UPDATE `table_name` SET `field_name` = REPLACE(`field_name`,'置換対象','置換後の文字'); 
データを直接いじるのはちょっと乱暴かな~

なんて時はSELECTで利用して、表示の時に文字列置き換えすればよいのか。

PHPは元々そんなに速い言語ではないので、Mysqlで直接やった方がいいケースもありますね。
便利な構文はまたありましたら追加します。

PostgreSQL ユニークの追加

PostgreSQL ユニークの追加

テーブル名hogefieldにユニークを追加

ALTER TABLE hoge ADD UNIQUE (field)

PostgreSQL ユニークの削除

テーブル名hogefieldのユニークを削除

ALTER TABLE agency DROPUNIQUE (login)

なお、phppgadminにて確認すると、ユニークが追加されるとこんな表示になります。

phpPgAdminのインストールメモ

phpMyadminはず~~っと使っていましたが、PostgreSQLはごぶさただったもので、
今回初めてphpPgAdminをインストールしたので、メモとして残します。

phpPgAdminをダウンロード


phpPgAdminをダウンロード
PHP5系ならphpPgAdmin-5.0を選択。


今回はphpPgAdmin-5.0.2.zipをダウンロード。

ファイルを解凍する

phpPgAdmin configを変更する

$conf[‘servers’][0][‘host’] = ‘localhost';//ホストの設定
$conf[‘extra_login_security’] = false;//postgresユーザでのログインを許可
$conf[‘owned_only’] = true;//ログインユーザの所有データベース以外は表示しない

後は丸ごと閲覧可能ディレクトリにアップロードして終了。

(コマンドからやった方が早いっていうツッコミは無しでお願いします。)

SQL UNIQUE 追加

ALTER TABLE `table_name` ADD UNIQUE (
`field_name`
)

ユニークのフィールドを追加したい時はこれ。

Mysqlの予約語とその回避

今日は朝から超脱力。何度やってもMysqlのSQLがエラーが返ってきます。

INSERT INTO test_table(name,age,title,info_text,release) values (‘名前’,25,’タイトル’,’テキスト’,’2010-11-05′)

これを見て、あなたはパッとこのSQLの何がいけないか理解できますか?フィールドの型の問題とかバージョンの問題ではありません。もっと根本的なことが間違っていました。

正解は・・・

INSERT INTO test_table(name,age,title,info_text,release) values (‘名前’,25,’タイトル’,’テキスト’,’2010-11-05′)

こいつです。releaseはMysqlの予約語だったんです。フィールド名にこの予約語を使うと、フィールド名自体は設定できても、INSERTを実行するときにエラーが出てINSERTできません。

Mysqlの予約語

私はものの見事にMysqlの予約語にはまってしまいました。
以下予約言語一覧です。フィールド名をつけるときは以下の予約名とかぶっていないかよく確認したほうがよさそうです。

Mysql予約後一覧
http://dev.mysql.com/doc/refman/4.1/ja/reserved-words.html

あれ?でもreleaseは予約語としてかかれていない。でもGoogleで
Mysql release 予約語
と検索したら同じような方がたくさんいました。

Mysqlの予約語を使いたいときの回避方法

ここまで書くと、もう予約語自体は使わないほうがよさそうですが、調べてみると、

INSERT INTO table名 (`release`) VALUSE(’hoge’);

バッククオートで囲ってあげると使えるようです。

売り切れじまいメデタ屋のブログより

ORDER BY で0を以外でソートする

今まで登録順にしか表示順を取っていなかったが、新たにsortフィールド(int)を追加し、デフォルト値を0にして、sortフィールドの数字でORDER BYしようと思ったときのメモ。(長い。)0を除外してソートしたいわけです。

問題だったのが普通に

ORDER BY sort;

って書いても、0が上に来てしまう。1、2、3とちゃんと入力した数字が上に来るようにしてほしい。と、いうことで、

 ORDER BY sort >0 DESC,sort

先に0より大きい数字DESCでソートして、もう一度ソート。2回に別けたらうまくいきました。

Mysql UNIQUE(ユニーク)フィールドの追加

あかんあかん、いつも悩むのでメモします。

Mysql ユニークフィールドの追加

ALTER TABLE (テーブル名) ADD (フィールド名) (データ型) UNIQUE

これでよし!っと。一番最後にユニークキーで追加されます。
重複チェックにはよく使いますね。PHP側でプログラム書かなくていいから楽。
私はよく会員登録のロジックを作るとき、ユーザーIDの重複チェックによく使います

PostgreSQL NULL判定

すでにいくつものデータが登録されていて、そこにデフォルト値を設定したフィールドを追加しても追加以前のデータはNullになっています。

有料掲載か無料掲載かで有料掲載を上位表示したいのに、どうもうまくいかない。それがどうやらnullのせいではないかと目をつけたわけです。

SELECT shop_id,pay FROM shop_table WHERE pay = null;

おや、うまくいきません。

SELECT shop_id,pay FROM shop_table WHERE pay is null;

これで成功!

Nullはイコールでは抽出できないのですね!う~ん初歩的ミス