ラベル SQL の投稿を表示しています。 すべての投稿を表示
ラベル SQL の投稿を表示しています。 すべての投稿を表示

2016年3月22日火曜日

CASE式を使用して、主キーを入れ替える方法 SQL

こんにちは山田です。

気温もぐーんとあがり、
やっと春らしくなってきました。今日この頃。

さて、SQLの話題です。既存コードをリファクターできそうなものを見つけたので、その備忘録になります。

■目的
「主キー」もしくは「ユニークキー」の値を入れ替えて、
並び替え機能をつくります。

■テーブルの前提
 1. ソートキーはユニークである
 2. ソートキーが大きいものが上(降順)で並べる

表であらわすと↓こんな感じになります。
+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 1   | ゼンサイ      | 100    |
| 2   | メインリョウリ    | 10     |
| 3   | スープ       | 1      |
+-----+------------+--------+
このソートキー「100」と「10」を入れ替えて登録して、並び替え機能をつくります
         ↓
+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 2   | メインリョウリ    | 100    |
| 1   | ゼンサイ      | 10     |
| 3   | スープ       | 1      |
+-----+------------+--------+
                ※IDとNameは対応しています。

■仮数字を一時的に代入する方法(元々の方法)
update mst_data
   set Sorter = '-1'
  where where ID = '1'  /*ID「1」のSoterを一時的に「-1」を代入*/
update mst_data
   set Sorter = '100'
  where where ID = '2' /*ID「2」のSorterに、空いた「100」を代入*/
update mst_data
   set Sorter = '10'
  where where ID = '1' /*ID「1」のSorterに目的の「10」を代入*/
この3つのクエリ文でトランザクションを作ってやっていました。
動作するものの、以下の懸念点がありました。
 ・クエリ文が3つできてしまう
 ・仮数字がすでに使われていた場合、ユニーク制約のため、-1が代入できなくなる


この解決方法として、CASE式を使用した方法をとります。

■CASE式を使って、ユニークキーを入れ替える方法
update mst_data
 set sorter = CASE WHEN sorter = '100' THEN '10'
                WHEN sorter = '10'  THEN '100'
               ELSE sorter END
 where sorter in( '100' , '10');
CASE式を使用することによって、一度のUPDATE文で入れ替えが完了できてしまいました。
どうしてこんなことが出来てしまうかというと、
CASE式の分岐による更新は「一気」におこなわれるので、
ユニークキーの重複エラーを回避することができるのです。


実はこの方法、主キーを入れ替えるという荒業も簡単にできてしまいます。
■CASE式を使って、主キーを入れ替える方法

操作対応表 
・SQL Server: ○
・Oracle  : ○
・MySQL  : ×
update mst_data
 set sorter = CASE WHEN id = '1' THEN '2'
                WHEN id = '2' THEN '1'
               ELSE id END
 where sorter in( '1' , '2');
その場合の実行結果
+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 1   | ゼンサイ      | 100    |
| 2   | メインリョウリ    | 10     |
| 3   | スープ       | 1      |
+-----+------------+--------+

+-----+------------+--------+
| ID  | Name       | Sorter |
+------------------+--------+
| 2   | ゼンサイ      | 100    |
| 1   | メインリョウリ    | 10     |
| 3   | スープ       | 1      |
+-----+------------+--------+

■参考サイト
『達人に学ぶ SQL徹底指南書』サポートページ
http://www.geocities.jp/mickindex/database/db_support_sinan.html

2016年3月14日月曜日

開発初心者がこれだけは知っておいた方が良いこと④

こんにちは!Sです。

今回はデータベースのロックについて書きたいと思います。

DBで行う主な操作としては
①新規登録 ②更新 ③削除 ④参照
だと思いますが、同じテーブルに対して複数の人が同時に様々な操作を行うとデータの整合性がとれません。

そんなことにならないようにDBにはロックという機能が存在します。

ロックの粒度(行に対してのロックなのか、テーブルに対してのロックなのかなど)やルールは
使っているDBによって多少違うようですが、ここからはわたしが使っているSQL Serverについて書きます♪

◆ロックの種類
ロックには共有ロック、排他ロック、更新ロック、インテントロックなどがありますが、
難しいので詳細の説明は省きます。
初心者の方にまず覚えて頂きたいのは

①データの参照中は共有ロックがかかり、その間他の人が更新などできない
②削除などデータの書き換え中は排他ロックがかかり、他の人が何もできない
ということです。
(詳しく知りたい人はこちらこちらを!)

バッチのスケジュールを組む際にはこれらのことを考慮しないといけないですね。

ちなみに排他ロックをどの粒度でかけるかは、SQL Serverが決めてくれます。
https://technet.microsoft.com/ja-jp/library/ms189286(v=sql.105).aspx

◆安全なおまじない
DBで行う操作の中で更新や削除はとても怖いですよね。。。
出来ればやりたくないですが、避けることはできません。
そんなとき上記のロックを利用して安全に操作を行うことができる魔法の呪文を教えてもらったので紹介します♪

1.まずはこれを書く
begin transaction

2.やりたいことをやる
update文 や delete文など

3.select文で思い通りの結果になっているか確認

4-A.やばい!思った通りの動きじゃない!というときは
ROLLBACK TRANSACTION
→これで元通り。なんの心配もいりません!

4-B.意図通り!成功!というとき
COMMIT TRANSACTION
→これを忘れずに!忘れると反映されません。


...余談ですが、わたしは今朝通勤途中に電車にすべての荷物を忘れてきました。
 (別に寝ぼけていたわけでもないのに。。。)

電車を降りるところからROLLBACKしたいです。。。




2015年12月7日月曜日

【SQLserver】DBテーブル内のカラムにコメントを登録する

こんにちわ、開発者のⅠです。

さて、今回はDB操作に関するお話です。
クエリ文を作成しそれをプログラム上で実行することで、新規テーブルの作成やカラム追加など、様々な操作が可能です。

その中で、今回はカラムにコメントを登録する方法を伝えします。
カラムの定義変更や追加には【ALTER TABLE】を利用することになりますが、新規にカラムを追加する場合はコメントには何もない状態です。

このコメント、カラム自身がなにを格納するものなのか、それを説明するのに結構重要なものです。コメント自体は手動で登録・編集が可能ですが、カラム作成と同時に登録できれば手間を省けますね。

それでは、早速書いてみたいと思います。
環境は、
・管理プログラム:SQL Server Management Studio
・対象テーブル:日本の観光名所を登録している「dbo.tbl_attractions」
・追加するカラム:設立した年を格納する『year』 とします。
/* カラム『year』の追加 */ 
ALTER TABLE tbl_attractions ADD year datetime

/* カラム『year』にコメントを登録する */
EXEC sys.sp_addextendedproperty  @name=N'MS_Description'
                                ,@value=N'設立した年'
                                ,@level0type=N'SCHEMA'
                                ,@level0name=N'dbo'
                                ,@level1type=N'TABLE'
                                ,@level1name=N'tbl_attractions'
                                ,@level2type=N'COLUMN'
                                ,@level2name=N'year'


これで、カラムにコメントが登録できるはずです。
登録されたかどうかを確認するには、対象のテーブルで右クリックし、デザインを開くとカラムの設定情報の一覧が表示されますから、その中の”説明”に登録したコメントが入っているはずです。

それでは、今日はこの辺りで失礼します。

2015年9月30日水曜日

datetimeの列でデータを抽出したい!

岡田です。

データを抽出するときってだいたい数値か文字列で検索を書けるのですが、今回あまりなじみのないdatetime型の列で抽出しなくてはいけない作業があったので、メモしておきます。

例えばその日時が2000年12月31日だった場合は、、、


select top 100 * from datatable
where (DATEPART(yy, datecolmun) = 2000
AND    DATEPART(mm, datecolmun) = 12
AND    DATEPART(dd, datecolmun) = 31)


という感じ。

あまり使わなさそうなので忘れちゃいそうです。



エクセルやブログなどで「カラム」と呼びますが、雑誌やサイトで連載されている「コラム」と同じ単語なんですね。
もともと「column」には「柱」や「段」という意味があって、新聞で記事が段分けされて書かれていることから、その1段分の短い記事のことを「colmun」と呼んだんですね、きっと。