SELECTのTOP N分析(Oracle)

ITコーディネータのシュウです。

公園_4

サッカーワールドカップは、日本は十分力を出し切れず、一次予選敗退。本当に残念でした。最後のコロンビア戦は結構頑張っていたし、前半最後に追いついて、これはやってくれるんじゃないかと思ったのですが、カウンターを見事に決められ、終わってみれば完敗でした。アジアのチームはどこも1勝もできず、世界の壁はやはり厚いです。

また、忙しかったお客様の開発案件も、いよいよ運用開始が近くなってきました。まだ気が抜けません。

<本日の題材>
SELECTのTOP N分析について

前回、Microsoft SQL ServerのSELECTでのTOP句について見ましたが、今回はこれと同様のことをOracleで行う場合について考えてみます。Oracleの場合には、ROW_NUMBER関数を使うと同様なことが可能です。

例)今日の受注テーブルから受注番号順に先頭5件を抽出する。 SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY 受注NO) JNO, 受注NO, 受注日, 顧客番号, 決済金額 FROM 受注テーブル)
WHERE JNO <=5 ORDER BY JNO;

Oracle_row_number1

また、ROW_NUMBER関数を使用せずに、インラインビューを使用した問い合わせでも可能です。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 受注NO
)
WHERE ROWNUM <= 5
ORDER BY 受注NO;

Oracle_インラインビュー1

顧客番号順に並べた最初の5件ということにしたい場合には、以下のようになります。

SELECT * FROM (
SELECT * FROM 受注テーブル
ORDER BY 顧客番号
)
WHERE ROWNUM <= 5
ORDER BY 顧客番号;

Oracle_インラインビュー2

OracleにはTOP句はないですが、上記のような方法で同様のことができますね。
※ちなみに、SQL ServerでもROW_NUMBER関数は使えます。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SELECT TOP句について

ITコーディネータのシュウです。

1402143571315

いよいよサッカーワールドカップが始まりました。
今回は、逆転勝ちの試合も結構多いようですね。スペインがオランダに5対1で敗れた衝撃的な試合もありました。日本も初戦のコートジボアール戦は逆転負けしてしまいましたが、2戦目のギリシャ戦は、是非勝利を飾ってほしいものです。


<本日の題材>
SELECTのTOP句について

Microsoft SQL Serverの場合には、SELECTでのクエリの結果の行数を指定した行数、または割合(何パーセントか)に制限するTOP句という便利なものがあります。

今回はこれについて見てみたいと思います。

例)今日の受注テーブルから先頭5件を抽出する。
SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

sqlserver_9回目_1

例)先頭の5%を抽出するという場合は以下:
SELECT TOP 5 percent * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112);

これを、さらに、顧客番号順に並べた最初の5件を表示したいという場合には、以下のようになります。

SELECT TOP 5 * FROM 受注テーブル
WHERE 受注日 = CONVERT(CHAR(8) , GETDATE(), 112)
ORDER BY 顧客番号;

sqlserver_9回目_2

結構便利に使えます。
次回は、これをOracleで表現する場合を題材にする予定です。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

よもやまばなし

ITコーディネータのシュウです。

IMG_0839

娘が高校の遠足でディズニーランドに行ってきました。
同じ日に複数の学校から遠足で学生が来て、人も多かったみたいですが、楽しかったようです。

5月は、開発案件の関係で忙しく、緊張した日々が続きました。まだ一部残っている部分がありますが、最後まで気を抜かず、お客様に喜んでもらえるよう頑張らねば!

開発で使用しているツールはdbSheetといい、ExcelやAccessを使いながらWebシステムを構築できるツールです。なかなかおもしろいですよ。
http://www.newcom07.jp/dbsheetclient/index.html

it-coordinator①-1

今日は、少し話題を変えて我が家の様子などを紹介してみたいと思います。

仕事が遅くなって家に帰ると、妻がご飯を用意して待ってくれています。疲れて寝てるときもあったり、ときに遅くまで出かけてる時もありますが。。。

妻は、話をし始めたら止まらなくなるとてもおしゃべりタイプで、いろいろとその日の出来事などをおもしろおかしく教えてくれます。仕事のことでいっぱいだった頭も、いろいろな妻の話で気分転換されることが多く、内心はとても助かっています。

仕事で心配なことでもときに話してみると、結構冷静に、また前向きに元気づけてくれます。私よりも多分に気が強く、男勝りな部分もあるので、結構力強く背中を押してくれるので、ありがたい。ただ、機嫌が悪くなるとなかなか手が付けられなくなる部分もあるのが困りものですが。(ハイ~~)

また、子供たちも上は専門学校生、下は小学3年生と、なかなか幅が広く人数も多いのですが、それぞれに性格が違って、なかなかおもしろいです。
何も言わなくても一生懸命に勉強を頑張って、成績表をうれしそうに見せてくれる子もいれば、勉強はきらいだと、なかなかやる気が出ないけれど、遊ぶことについてはいろいろと知恵を使って新しい遊びを考えたりする子とか。。。

皆をまっすぐに育てるのが親の責任なんでしょうが、なかなか大変ですね。

ITコーディネータの資格の更新も6月末まで(毎年5月末までだったのが、今年は事情があって6月末まで)なので、遅れないように更新手続きもしないと!

ながながと痴話話につきあって頂き、ありがとうございました。
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL 比較関数(Greatest、Least)について

ITコーディネータのシュウです。 公園_4

お久しぶりです。

<本日の題材>
比較関数について

データベースの違いによって、あるDBでは使用できる関数が、別のDBでは使用できないケースがあります。その一例として、複数項目の最大値・最小値を取得するSQLを、今回は見てみたいと思います。

データベースがOracleの場合には、GREATEST関数・LEAST関数というのがあります。

例)金額に関する3つの項目の中から最大値を求める。具体的には

item_no | priceA | priceB | priceC |
-----------------------------------------------------
item01  |  6000 |  2000 |  3000
item02  |  1000 |  7500 |  4000
item03  |  1500 |  3000 |  8000

というテーブルがあった場合に、以下のような結果を出したいとします。

item_no | 最大値 | 最小値
------------------------------------------
item01  |  6000 |  2000
item02  |  7500 |  1000
item03  |  8000 |  1500

SQL> SELECT item_no
, GREATEST( priceA, priceB, priceC ) AS 最大値
, LEAST( priceA, priceB, priceC ) AS 最小値
FROM テーブル名;

greatest_ora

これは、MySQLでも同様の関数があります。
 GREATEST()、LEAST()

上記をSQL Serverで行おうとすると、同様の関数がないため、下記のようなSQLを実行するかたちになります。

SELECT  item_no, MAX(price1) AS 最大値, MIN(price2) AS 最小値 FROM
(           SELECT item_no, priceA AS price1, priceA AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceB AS price1, priceB AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceC AS price1, priceC AS price2 FROM テーブル名
) AS TBL
GROUP BY item_no
ORDER BY item_no;

sqlserver_greatest

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL日付関数(年齢計算)

ITコーディネータのシュウです。
untitled

春先に京都に行った時の金閣寺の写真です。せっかく撮っていたのでアップします。

金閣寺は正式には鹿苑寺といい、写真の金閣(舎利殿)を含む鹿苑寺が世界文化遺産として登録されています。現在の金閣は、三島由紀夫の小説金閣寺にもあるように、1950年の失火で国宝であった当時の金閣が全焼した後、1955年に再建されたものです。
再建にあたっては焼失直前の姿ではなく創建時の姿を再現するとの方針が採られ、現在のような金色に光る豪華なかたちになったとのこと。
ただ、国宝や重要文化財がこのような人為的な火災や、災害などで損失してしまうことは本当に残念です。文部科学省でもこのような文化財の総合防災対策などリスク管理についてはいろいろと検討・対策を取られているようです。

実は、最近仕事も忙しくなり、少々疲れ気味。久しぶりの投稿です。
体調管理も含め、リスク管理は本当に重要ですね!

it-coordinator①-1

<本日の題材>
日付関数についての続き(年齢計算)

システムでは、生年月日をデータベースの項目に持つテーブルが存在するケースが非常に多いですね。日付の計算に関連して、今回は年齢の計算について見てみたいと思います。

データベースがOracleであれ、SQL Server であれ、年齢計算においては、以下の2つの内容を確認することになるかと思います。
1)生年月日と基準日(通常は今日)の「年」の差
2)生年月日と基準日(通常は今日)の「月日」の大小を比較し、基準日が誕生日に達してなければ -1

SQLとしてはいろいろなやり方があると思いますが、以下に例を挙げます。

Oracle:
SELECT
EXTRACT(YEAR FROM 基準日) - EXTRACT(YEAR FROM 誕生日) -CASE WHEN TO_CHAR(基準日, 'MMDD') < TO_CHAR(誕生日, 'MMDD') THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

SQL Server:
SELECT YEAR(基準日) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , 基準日, 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

※YEAR(基準日) - YEAR(生年月日) は、DATEDIFF(YEAR,生年月日,GETDATE())でも同じ

例)
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, YEAR(GETDATE()) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , getdate(), 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

年齢結果2_result

また、簡単に計算する方法として、日付型(yyyymmdd)を8桁数値に変換後に10000で割って小数点を切り捨てる方法もよく使われるようです。

Oracle:
SELECT TRUNC((TO_CHAR(基準日,'YYYYMMDD') - TO_CHAR(生年月日, 'YYYYMMDD')) /10000, 0) AS 年齢 FROM テーブル名;

※TRUNC関数は切り捨てで、第2引数が少数第何位での切り捨てかを示す。

SQL Server:
SELECT
ROUND((CONVERT(INT,CONVERT(VARCHAR(8),基準日,112))-CONVERT(INT,CONVERT(VARCHAR(8),生年月日,112))/10000,0,1) AS 年齢
FROM テーブル名;

※ROUND関数の第3引数が0以外の場合は切り捨てになります。また、FLOOR関数を使用すると、引数に対して、それ以下で最も大きい整数値を取るので同じことになります。
※閏年生まれの方も、3月1日時点できちんと年齢が上がります。

例)
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, ROUND((CONVERT(INT,CONVERT(VARCHAR,getdate(),112))-CONVERT(INT,CONVERT(VARCHAR,生年月日,112)))/10000,0,1) AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

結果は最初の処理と同じ。

注意)日本の法律(年齢計算に関する法律)上は、年齢というのは誕生日の前日で加算されるもののため、計算結果の利用目的によっては上記のような計算に1日加算するなど調整が必要になります。年齢を元に何らかの計算するような場合、年齢計算を法律に則って行うのか、一般慣習に基づいて行っていいのかよく調査してから計算式を決定する必要があります。

★また、EXCELからデータをアクセスできる環境であれば、EXCELのほうで年齢計算をできる(DATEDIF関数)ので、EXCEL側で年齢を表示させることが簡単にできます。
年齢結果Excel_result
【書式】
DATEDIF(開始日,終了日,単位)
【単位の引数】
"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の日数
"MD" 開始日から終了日までの日数
"YM" 開始日から終了日までの月数
"YD" 開始日から終了日までの日数

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL 日付関数について(2)

ITコーディネータのシュウです。

桜2__

先週末の雨と風で桜が結構散ってしまいましたね。これはその前に撮った家の近くの庁舎にある桜の写真です。

4月に入り、子供たちもいよいよ始業式、入学式の時期になりました。わが家も子供たちが学校へ行く準備で慌ただしくしている朝の日々が戻ってきました。

朝になって、子供たちからあれがない、これがない、これを準備して!とか言われると本当に大変ですよね。前の日の夜に次の日の準備をしっかりさせるように躾けることが大事だとつくづく感じます。

it-coordinator①-1

<本日の題材> 日付関数についての続き

前回、日付型関数について少し話をしましたが、日付の計算などについて今回は見てみたいと思います。

①    何日後・何日前など、日にちを足し引きする場合:
Oracle:
 DATE型の項目に対して、足し引きしたい日数を単純にプラス 、マイナスすればよい。

SQL Server:
  DATEADD関数 (構文:DATEADD(datepartパラメータ, number, date) )でパラメータに dayを使用する。

※追記)
Oracleと同様に、足し引きしたい日数をプラスマイナスも可能です。ただし、Oracleの場合は、日数だけでなく、時間・分・秒なども 1/24 、1/24/60、1/24/60/60 などを足し引きすることで計算が可能ですが、SQL Serverでは日数までです。

例)購入日(DATE型)の7日後、7日前
Oracle:
    SELECT 購入日
      , 購入日+7  AS 購入日の7日後
      , 購入日-7  AS 購入日の7日前
      FROM テーブル名;

購入日が ’2014-03-25’ だった場合、結果は以下のようになる:
   購入日            購入日の7日後    購入日の7日前
   -----------------      -------------------     --------------------
   14-03-25         14-04-01           14-03-18

SQL Server:
    SELECT  購入日
      ,  DATEADD(day, 7, 購入日)  AS 購入日の7日後
      ,  DATEADD(day, -7, 購入日)  AS 購入日の7日前
      FROM テーブル名;

※Oracleと同様の、+7、-7 などの計算も可能です。

 
②    何か月後・何か月前など、月数を足し引きする場合:
Oracle:
ADD_MONTHS関数(構文:ADD_MONTHS (date型項目, integer) )。

SQL Server:
  DATEADD関数(構文:DATEADD(datepartパラメータ, number, date型項目) )でパラメータに month を使用する。

例)購入日(DATE型)の月末日の1ヶ月後、1ヶ月前
Oracle:
  SELECT   LAST_DAY(購入日)  AS 購入日の月末日
 ,  LAST_DAY(ADD_MONTHS(購入日,1))  AS 購入日の翌月末日
 ,  LAST_DAY(ADD_MONTHS(購入日,-1))  AS 購入日の前月末日  FROM テーブル名;

   購入日が ’2014-03-25’ だった場合、結果は以下のようになる:
     購入日の月末日     購入日の翌月末日     購入日の前月末日
       --------------------      ----------------------         -----------------
       14-03-31                  14-04-30                        14-02-28

SQL Server (2012):
    SELECT      EOMONTH(購入日)  AS 購入日の月末日
  ,  EOMONTH(DATEADD(month, 1,購入日))  AS 購入日の翌月末日
  ,  EOMONTH(DATEADD(month, -1,購入日))  AS 購入日の前月末日     FROM テーブル名;

Excel月末日_result3

※ただし、上記の月末日を取得する EOMONTH関数は、SQL Server2012から使える機能です。それ以前の場合には、翌月1日から1日引いた日付を取得するかたちが一般的ですね。いろいろな書き方があると思いますが、例えば以下のような文で今月の月末日を抽出できます。

SELECT DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,+1,GETDATE())) + '-' + DATENAME(MONTH,DATEADD(MONTH,+1,GETDATE())) + '-' + '01') as 当月末

★前回と同様、EXCELからデータをアクセスできる環境であれば、EXCELのほうで月末日を取得する関数(EOMONTH関数)があるので、EXCEL側で月末日を表示させることが簡単にできます。

Excel月末日_result2

今日は以上まで

 

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL 日付関数について(1)

ITコーディネータのシュウです。

DSC00680

会社の近くで撮った写真、桜が見事に咲きました!
遠方に見えるのは、さいたま新都心のビル群

WindowsXPのサポート期限切れが迫ってきました。4月9日です。Office2003も同一日でサポートが切れますね。

先日(3/6~7)、東京駅のすぐ隣のJPタワーで行われたSecurity Days 2014でもそのことを取り上げて、セキュリティに注意を促すセッションを行うところも結構ありました。日本MSの発表では、XPとWindows8.1ではウィルス感染率に21倍もの差があるとか。。。

昨年の11月の調査時点で、サポート終了予定から約1年が経過した2015年3月時点でも、18.2%のPCがWindows XPで稼動予定という矢野経済研究所の調査報告(http://enterprisezine.jp/article/detail/5403)があり、総務省の2013/11/22の発表では自治体でも26万6千台が期限切れになるという予想報告や、最近でも個人でXPを使用している人がまだ4人に一人という調査結果を出しているところもあります。

問題は、サポートが切れることで、新しく発見された脆弱性に対してXPについてはセキュリティパッチが提供されなくなるため、その脆弱性を狙った攻撃があればそれによる被害が発生してしまう危険性が高いということです。最近は標的型攻撃とよばれるサイバー攻撃が非常に増えてきているということなので、それに対応した防御も十分考える必要が出てきていますね。

it-coordinator①-1

<本日の題材>
日付関数について

前回、日付型にどういうものがあるかという話をしましたが、日付型項目の値を表現したり計算したりする場合に、関数を使用するケースが随所に出てきます。その関数についても、OracleとSQL Serverでは違いがあるので、整理しておくと便利かと思います。

まず、よく使うのがシステム日付。

Oracle:SYSDATE
SQL Server:GETDATE()

実際にSQLでシステム日付を確認するのは、
Oracleでは、
SELECT SYSDATE FROM dual;
SQL Serverでは、
SELECT GETDATE();

そして、日付型項目を日本語の日付表現に直して表すとき、西暦の4桁の年月日までか、年月までか、年だけの表記にするか、それとも時刻まですべて表現するのかなど、いろいろとシステムによって項目ごとに使い分ける場合があると思います。

今回は、この日付のフォーマットに関して少し上げてみたいと思います。

Oracleの場合は、TO_CHAR関数を使用して、フォーマットを設定しますが、SQL Serverの場合は、CONVERT関数を利用することが多いと思います。

例)
①yyyy/mm/dd という表示にしたい
Oracle:
SELECT TO_CHAR(日付項目, ‘YYYY/MM/DD’) FROM テーブル名;
SQL Server:
SELECT CONVERT(VARCHAR, 日付項目, 111) FROM テーブル名;

②yyyy-mm-dd hh:mi:ss というように日付+時刻(24時間表記)で表示したい
Oracle:
SELECT TO_CHAR(日付項目, ‘YYYY-MM-DD HH24:MI:SS’) FROM テーブル名;

SQL Server:
SELECT CONVERT(VARCHAR, 日付項目, 120) FROM テーブル名;

結果はこんな感じ:
2014-03-31 14:46:39

③和暦で表示したい
Oracle:
SELECT TO_CHAR(日付項目, 'EEYY"年"mm"月"dd"日"', 'nls_calendar = ''Japanese Imperial''') 日付 FROM テーブル名;

結果はこんな感じ:
日付
----------------------------
平成26年3月31日

※Oracleの書式について
E : 元号の略語(大正:T、昭和:S、平成:H)
EE: 元号(大正、昭和、平成)

和暦変換する場合は'nls_calendar = ''Japanese Imperial'''で「nls_calendarオプション」のカレンダを指定するかたちになります。

SQL Server:
SQL Serverでは、実は和暦に変換する関数は持っていないため、SQLを駆使してこれを実現するケースが多いと思います。例えば元号を表すためには

SELECT
Case when 日付項目 > '19890107' then '平成'
when日付項目 > '19261224' then '昭和'
when日付項目 > '19120729' then '大正'
when日付項目 > '18680124' then '明治'
else 'その他' end 年号
というようなかたちでSQLで設定が必要です。

ただし、例えば結果をEXCELで表示するというように、EXCELからデータをアクセスできる環境であれば、EXCELのほうで日付データに対して和暦の表示を行うことができるので、SQL Serverのデータでも、簡単に和暦に変換することが可能になります。

和暦全体

EXCELはいろいろと関数を持っているので、使えば結構便利ですよね。

なお、SQL ServerのCONVERT関数についての仕様やパラメータについては、以下のサイトで確認できます。
http://msdn.microsoft.com/ja-jp/library/ms187928.aspx

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

Oracle・SQL Server 日付型について

ITコーディネータのシュウです。

花_2

ソチオリンピック、パラリンピックが終わってしばらくたちましたが、まだ感動が残っていますね。

羽生選手のように、力を存分に発揮できた人もいれば、練習してきた成果を完全には発揮できなかった人もいましたが、浅田選手のように、前半の失敗で絶望のような状況になりながらも、気持ちを切り替えて後半は全てを出し切って表現し、皆に深い感動を与えてくれた選手もいました。最後笑顔で終えることができて、本当によかったと思います。日本中の人たちがそう思ったでしょう。ジャンプ団体も夜中ずっとテレビで応援してましたが、インタビューにも感動しました。

オリンピックに出ること自体がとても大変なことでしょうが、本番で自分の力を出し切ることはどれほど難しいことかと思います。しかし、それぞれに見るものに感動を与える力があり、やっぱりスポーツとはすばらしいですね。選手一人ひとりに拍手を送りたいです。

it-coordinator①-1

<本日の題材> 【日付型について】

日付・時刻に関する型は、

Oracle:
  date、timestamp、timestamp with time zone

SQL Server:
  datetime、smalldatetime
                                    -- SQL Server2005まで
  date、time、datetime2、datetimeoffset
                                    -- SQL Server2008から追加
などがあります。

私も過去、システム開発でよく使用していたのは、Oracleでは「date」型、SQL Serverでは「datetime」型(どちらも、日付と時刻の両方の値を保持)だったのですが、他にも上記のような型が存在しています。

SQL Serverで「datetime」型を使用していた時に、たまたま登録しようとしたデータに 1753/1/1 よりも古い値が存在していたときに、エラーになったことがあり、そんな制限があるんだとびっくりしたことがありました。

ちなみに、エラーメッセージはこんな感じ:

メッセージ 242、レベル 16、状態 3、行 3 varchar データ型から datetime データ型への変換の結果、範囲外の値になりました。

なぜ 1753 年から開始なのか? それは datetime 型が「グレゴリオ暦」であることに起因しているようで、当時のイギリス帝国 (とその植民地) でグレゴリオ暦を採用した年が1752 年であり、通年としてグレゴリオ暦が始まったのが 1753 年からだからとのこと。 (ちなみに Oracleの「date」型は -4712/01/01 ~ 9999/12/31 までの範囲)

SQL Server2008からは、新しく 「datetime2」型:西暦1/1/1以降のデータを扱えるもの、 「date」型: 時刻の部分はなく日付部分のみをデータとして持つもの、 「time」型: 時刻の部分のみ持ち、時間も100ナノ秒まで扱えるもの などが増えています。

これに対して、Oracleも「timestamp」型というもので、日付+時刻で最少ナノ秒単位まで扱える型があります。

また、タイムゾーン・オフセットを考慮した型もあり、下記のようなものがあります。

Oracle:timestamp with time zone、timestamp with local time zone SQL Server:datetimeoffset

タイムゾーン・オフセットとは、ローカル時間とUTC(協定世界時、以前のグリニッジ標準時)との時差(時間および分単位)になり、日本の標準時間はグリニッジ標準時に9時間プラスした時間となります。

どういうときにこの型を使うのか?というと、グローバルに展開する企業のシステムなどで、時差がある国や地域で作成した部品を組み立てて製品を完成しユーザに届けるような場合、各地の時刻で日付を登録するとわかりにくくなるところを、全てある地域の時刻に変換して表現することで、システムとしてわかりやすくするような場合など、この型が必要な場合があるようです。

日付関数などについて、続きは次回

 

にほんブログ村 IT技術ブログへ
にほんブログ村

SQL主キーの追加・削除

ITコーディネータのシュウです。

untitled

京都に行ったときに撮った梅の花です。

今年も資格更新のためのポイント取得の期限が迫ってきました。3月末までに、何とか頑張って、3年間30ポイントに不足している分を取らないと! やっと何とかクリアできそうなところに来たんですけどね。。。

会社の仕事もまた忙しくなって来たところなんだけど、頑張るぞ!

ちなみに、ITコーディネータは、プロセスガイドラインというものがあり、IT経営を推進するため、ITコーディネータの備えるべき実践力や仕事の進め方についてガイドラインとしてまとめ、これを公開しています。かなり広範囲でボリュームもあります。
http://www.itc.or.jp/about/guideline/

it-coordinator①-1

<本日の題材>
前回のテーマだった、テーブル作成後の定義変更のSQLの続き

【主キーの追加】

Oracle/SQL Server:
ALTER TABLE テーブル名 ADD CONSTRAINT 主キー制約名 PRIMARY KEY (列名);

例)社員マスタの社員コードを主キーにする
Oracle:
ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);

Oracleの場合はNOT NULL制約をつけていない項目でも一発で主キーを追加できる(自動的にNOT NULLに変更してくれる)。

SQL> desc 社員マスタ

名前 NULL? 型
------------------ -------- ----------------------------
社員コード VARCHAR2(10)
社員名 VARCHAR2(40)

SQL> ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);

表が変更されました。

SQL> desc 社員マスタ

名前 NULL? 型
------------------- -------------- ----------------------------
社員コード NOT NULL VARCHAR2(10)
社員名 VARCHAR2(40)

しかし、SQL ServerではNOT NULL制約がない場合には、一旦NOT NULLに変更してからでないと主キーを追加できない。

SQL Server:

ALTER TABLE 社員マスタ ALTER COLUMN 社員コード VARCHAR(10) NOT NULL;

ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);

最初にNOT NULL制約をつけてないと、PRIMARY KEYを追加しようとしたときに、エラーになってしまう。

ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);
メッセージ8111、レベル16、状態1、行1

テーブル'社員マスタ' のNULL 値が許可される列にPRIMARY KEY 制約を定義できません。

※SQL Serverでも、最初のCREATE TABLE文で主キーを設定する場合は、NOT NULLは記述してなくても自動でNOT NULLになります。

【主キーの削除】

Oracle:以下のどちらかで可能
ALTER TABLE テーブル名 DROP PRIMARY KEY;
ALTER TABLE テーブル名 DROP CONSTRAINT 主キー制約名;

例)
ALTER TABLE 社員マスタ DROP PRIMARY KEY;
または、
ALTER TABLE 社員マスタ DROP CONSTRAINT PK_社員;

ちなみに、制約名がわからないときは、下記のコマンドで検索できる。
SQL>
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
FROM user_constraints
WHERE TABLE_NAME = '社員マスタ';

CONSTRAINT_NAME C TABLE_NAME
---------------------------- -- ------------------------------
PK_社員 P 社員マスタ

ちなみに、CONSTRAINT_TYPEというのは制約定義のタイプで、値は以下のようなものなどがあります。
「C」CHECK制約
「P」主キー制約(プライマリキー)
「U」一意のキー
「R」参照整合性
「V」ビューでのチェック・オプション付き
「O」ビューで読取り専用

SQL Server:
ALTER TABLE テーブル名 DROP CONSTRAINT 主キー制約名;

例)
ALTER TABLE 社員マスタ DROP CONSTRAINT PK_社員;
もちろん、SQL Serverの場合はGUIツールが使いやすいので、Enterprise Managerでもキーを簡単に削除できる。

主キーの設定し直しも、削除⇒追加で基本問題ないはずです。

RDBの場合、テーブルに主キーを持たせるのは多いとは思いますが、今までACCESSやEXCELで行ってきた業務を、SQL ServerやOracleに移行する場合に、もともとのmdb側で主キーを持たせていない場合とか、見受けられることもありますね。後から主キーを追加したりするときに、覚えておくといいのではないかと思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

テーブル作成後の定義変更のSQL

ITコーディネータのシュウです。 空_1

ぽかぽか天気、あったかそうでしょ!

  <自己紹介>

10年前、「ITコーディネータは、真に経営に役立つIT利活用に向け、経営者の立場に立った助言・支援を行い、IT経営を実現する人材です。」という紹介にあるような人材となるべく、高額のセミナーに参加し(ただし会社に出してもらったのだけど)、必死になって新しいことを勉強した日々。。。
ITCのホームページ:http://www.itc.or.jp

けれども、ずっと委託業務で他の会社に常駐しての仕事だったため、ITコーディネータを前面に出すような仕事は一切なく、システムの開発とデータベースからのデータの抽出・分析などの仕事をする毎日でした。 ただ、資格を継続するのも実は大変なんですね。

過去3年間で30ポイントを取っていることが継続の条件ですが、セミナー代金や資格更新料など、経費もかかっちゃいます。

  自己紹介はこれくらいにして・・・

it-coordinator①-1

せっかくなので、システムの開発とデータベースからのデータの抽出・分析などの仕事で学んだ内容を、少し整理しようと思っています。

<本日の題材>
【テーブル作成後の定義変更のSQL】

仕事によってOracle、SQL Serverを使うことがよくありますが、テーブル作成後の定義変更の仕方が、OracleとSQL ServerなどRDBによって微妙に異なるため、ときにすぐに思い出せなくて困ってしまうことがあります。
それで、ちょっと覚書の意味もあわせてまとめてみます。

【項目の追加】
Oracle:
ALTER  TABLE  テーブル名  ADD (列名 列定義);
SQL Server:
ALTER  TABLE テーブル名  ADD  列名 列定義;

例)追加する項目が複数の場合
Oracle:
ALTER  TABLE  社員マスタ  ADD (性別 VARCHAR2(4), 備考 VARCHAR2(100));

SQL Server:
ALTER  TABLE  社員マスタ  ADD  性別 VARCHAR(4), 備考 VARCHAR(100);

追加する項目にNOT NULL制約をつけて、デフォルト値を設定したい場合

例)test_tabテーブルにcol_a という数値項目をNOT NULL制約(デフォルト値 0)をつける

Oracle:
ALTER  TABLE  test_tab  ADD (col_a  NUMBER(4) DEFAULT 0  NOT NULL);

SQL Server:
ALTER  TABLE  test_tab  ADD  col_a  INT  DEFAULT 0 NOT NULL;

【列定義の変更】
Oracle: ALTER  TABLE  テーブル名 MODIFY (列名 列定義); SQL Server: ALTER  TABLE  テーブル名 ALTER COLUMN  列名 列定義;

例)文字項目の桁数を増やす場合(社員名を40バイトから50バイトに変更)
Oracle:
ALTER TABLE 社員マスタ MODIFY (社員名 VARCHAR2(50));

SQL Server:
ALTER TABLE  社員マスタ  ALTER  COLUMN  社員名 VARCHAR(50);

【項目の削除】
Oracle: ALTER  TABLE  テーブル名 DROP (列名);
SQL Server: ALTER  TABLE  テーブル名 DROP COLUMN 列名;

例)社員マスタに「性別」「備考」の削除
Oracle: ALTER  TABLE  社員マスタ  DROP (性別, 備考);
SQL Server: ALTER  TABLE 社員マスタ  DROP  COLUMN  性別, 備考;

【項目名の変更】
Oracle:
ALTER  TABLE  テーブル名 RENAME COLUMN  列名 TO  新列名;

SQL Server:
sp_rename @objname='テーブル名.元項目名', @newname='新項目名', @objtype='COLUMN';

例) 社員マスタの「備考」という項目名を「備考1」という名前に変更する。
Oracle:
ALTER  TABLE  社員マスタ RENAME COLUMN 備考 TO 備考1;

SQL Server:
sp_rename @objname='dbo.社員マスタ.備考', @newname='備考1', @objtype='COLUMN';

※ただし、SQLServerではManagement StudioなどのGUIツールで変更できますし、実際の運用では、項目名の変更を後から行うことはあまりないとは思いますが。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

主にSQLについて書いていきたいと思います。