So-net無料ブログ作成

SQLの基本-テーブルの変更(ALTER TABLE) [SQL]


SQLの基本-テーブルの変更(ALTER TABLE)
ALTER TABLEは便利なコマンドですが、変更した後にCREATE TABLEを必ず修正しないと DBの再構築のときに痛い目にあいます。

テーブル名の変更

DBMS DDL
Oracle
PostgreSQL
ALTER TABLE テーブル名 RENAME TO 新テーブル名
MySQL ALTER TABLE テーブル名 RENAME [AS] 新テーブル名


列の追加
DBMS DDL
Oracle ALTER TABLE テーブル名 ADD (列名 列定義)
MySQL
PostgreSQL
ALTER TABLE テーブル名 ADD [COLUMN] 列名 列定義
SQL Server ALTER TABLE テーブル名 ADD 列名 列定義


列を削除
DBMS DDL
Oracle ALTER TABLE テーブル名 DROP 列名
MySQL
PostgreSQL
ALTER TABLE テーブル名 DROP [COLUMN] 列名
SQL Server ALTER TABLE テーブル名 DROP COLUMN 列名


列名の変更
DBMS DDL
Oracle
PostgreSQL
ALTER TABLE テーブル名 RENAME COLUMN 列名 TO 新列名
MySQL ALTER TABLE テーブル名 CHANGE [COLUMN] 列名 TO 新列名 列定義


列名のデータ種類の変更
DBMS DDL
Oracle ALTER TABLE テーブル名 MODIFY ( 列名 列定義 )
MySQL ALTER TABLE テーブル名 MODIFY COLUMN 列名 列定義
SQL Server ALTER TABLE テーブル名 ALTER COLUMN 列名 列定義


タグ:SQL

SQLの基本-演算子 [SQL]


SQLの演算子

SELECT及びWHERE句に指定することが可能な演算子

思いのほか便利な演算子が揃っています。

・数値及び日付の加減乗除演算

 優先順位

* 同一の優先順位にある演算子は左から順に計算

* カッコ内にある演算子は最初に計算

* 乗算、除算、剰余演算子の次に加算、減算を計算

結果
数値 + 数値 数値
日付 + 数値 日付
日付 + 期間 日付
数値 - 数値 数値
日付 - 数値 日付
日付 - 期間 日付
数値 - 日付 数値
数値 * 数値 数値
数値 / 数値 数値
数値 % 数値 余り
(SQL Server,MySQL,PostgreSQL)



・文字列の結合

文字列式1 || 文字列式2 = 文字列式1文字列式2 (Oracle,PostgreSQL)

文字列式1 + 文字列式2 = 文字列式1文字列式2 (SQL Server)

・単項演算子

+式

 式の符号を正する。

-式

 式の符号を負する。-(-2)は+2と評価されます。

~式

 式のビットを全て反転する。
 2進数の1の補数を得ます。

・ビット演算子

&,|,^,~<<,>>

ビット演算 意味
式1 & 式2 式1 ビット演算AND 式2
式1 | 式2 式1 ビット演算OR 式2
式1 ^ 式2 式1 ビット演算XOR 式2
式1 << 式2 式1を式2分左にビットシフト
式1 << 式2 式1を式2分左にビットシフト
式1 >> 式2 式1を式2分右にビットシフト
~式1 ビット演算NOT 式1



・比較演算子

式1 比較演算子 式2

比較演算子

条件式 意味
= 等しい a='10'
列aの値が10と等しい
< 小さい a<'10'
列aの値が10より小さい
<= 以下 a<='10'
列aの値が10以下
> 大きい a>'10'
列aの値が10より大きい
>= 以上 a>='10'
列aの値が10以上
LIKE ワイルドカード a LIKE 'a%'
先頭の文字がa
a LIKE '%a'
最後文字がa
a LIKE 'a%b'
先頭の文字がaで最後の文字がb
ワイルドカードには「_」と「%」があり、 「_」は任意の1文字で、「%」は0文字以上の任意の文字列
BETWEEN 範囲指定 a BETWEEN '10' AND '20'
列aの値が10~20までの値
IS NULL NULLの検索 a IS NULL
列aに値がセットされていない(NULL)
IS NOT NULL 非NULLの検索 a IS NOT NULL
列aに値がセットされている
IN 複数の値を検索 a IN ('2','4','6')
列aの値が2,4,6のデータを検索
=とORで表現できるがINを使用した方がコストがかからないといわれている。
ANY、SOME 複数の値を検索 a = ANY ('2','4','6')
指定した値と比較演算子で一致したレコードを表示
INとは違い比較演算子が使用可能
・場合わけ
CASE WHEN 条件式1 THEN リターン式1
[WHEN 条件式2 THEN リターン式2 ...]
[ELSE リターン式3 ] END

CASE 条件式1 WHEN 値式1 THEN リターン式1
[WHEN 値式2 THEN リターン式2 ...]
[ELSE リターン式3 ] END

・複数の検索結果全てを取得
SELECT XXXXXXXXXXXXX FROM XXX UNION [ALL]
SELECT YYYYYYYYYYYYY FROM YYY

・複数の検索結果の共通部分を取得
(Oracle,PostgreSQL)
SELECT XXXXXXXXXXXXX FROM XXX INTERSECT
SELECT YYYYYYYYYYYYY FROM YYY

・複数の検索結果の差分を取得
(Oracle)
SELECT XXXXXXXXXXXXX FROM XXX MINUS
SELECT YYYYYYYYYYYYY FROM YYY

(PostgreSQL)
SELECT XXXXXXXXXXXXX FROM XXX EXCEPT [ALL]
SELECT YYYYYYYYYYYYY FROM YYY

・小計を集計する
(Oracle)
SELECT *列関数を含む* GROUP BY ROLLUP(列名1,[, 列名2 ...])
(SQL Server)
SELECT *列関数を含む* GROUP BY 列名1,[, 列名2 ...] WITH ROLLUP
・クロス集計する
(Oracle)
SELECT *列関数を含む* GROUP BY CUBE(列名1,[, 列名2 ...])
(SQL Server)
SELECT *列関数を含む* GROUP BY 列名1,[, 列名2 ...] WITH CUBE

・優先順位
演算の実行順序は演算子毎に優先度が有るので式の評価結果に大きな影響します。
同じ優先度の演算子は左から評価されますが、括弧が有る場合は括弧が優先的に評価されます。
演算子
+,- ,~ (単項演算し)
* (乗算)、/ (除算)、% (剰余)
+ (加算)、(+, || 連結)、- (減算)
>=,>,<,>=,<=,<>,!=, !>, !< (比較演算子)
^,&,| (ビット演算子)
NOT
AND
ALL、ANY、BETWEEN、IN、LIKE、OR
=

SQLの基本-数値関数編 [SQL]

SQLの基本-数値関数編

数値演算関数をまとめてみました。 何処まで、SQLで処理すれば良いのか迷いますが、知っていて損はないと思います。

・絶対値

Oracle MySQL PostgreSQL SQL Server
ABS(数値式) ABS(数値式) ABS(数値式) ABS(数値式)

・ビットAND演算

Oracle MySQL PostgreSQL SQL Server
BITAND(数値式,数値式)      

・数値式の符号を返す(-1,0,1)

Oracle MySQL PostgreSQL SQL Server
SIGN(数値式) SIGN(数値式) SIGN(数値式) SIGN(数値式)

・平方根を返す

Oracle MySQL PostgreSQL SQL Server
SQRT(数値式) SQRT(数値式) SQRT(数値式) SQRT(数値式)

・整数の除算

Oracle MySQL PostgreSQL SQL Server
数値1 DIV 数値2 数値1 DIV 数値2 数値1 DIV 数値2 数値1 DIV 数値2

・余りを返す

Oracle MySQL PostgreSQL SQL Server
MOD(割られる数,割る数) MOD(割られる数,割る数) MOD(割られる数,割る数) 割られる数 % 割る数

・CRC32(巡回冗長検査)値を返す

Oracle MySQL PostgreSQL SQL Server
  CRC32(文字列式)    

・0~1の範囲の乱数を返す。

Oracle MySQL PostgreSQL SQL Server
DBMS_RANDOMパッケージを使用する。
RAND()
RAND(種数)
RANDOM() RAND()
RAND(種数)

Oracleの乱数
基本書式では、0~1未満の小数点以下38桁を返します。
拡張書式は指定した範囲を返します。
基本書式:DBMS_RANDOM.VALUE
拡張書式:DBMS_RANDOM.VALUE(範囲の最小値, 範囲の最大値)
戻り値:数値

・πを返す

Oracle MySQL PostgreSQL SQL Server
PI() PI() PI() PI()

ラジアンと角度
・角度からラジアンに変換

Oracle MySQL PostgreSQL SQL Server
数値式 * PI()/180 RADIANS(数値式) RADIANS(数値式) RADIANS(数値式)

・ラジアンから角度に変換

Oracle MySQL PostgreSQL SQL Server
ラジアン値 * 180 / PI() DEGRES(ラジアン値) DEGRES(ラジアン値) DEGRES(ラジアン値)

三角関数、逆三角関数
・サイン(正弦)を返す

Oracle MySQL PostgreSQL SQL Server
SIN(ラジアン値) SIN(ラジアン値) SIN(ラジアン値) SIN(ラジアン値)

・コサイン(余弦)を返す

Oracle MySQL PostgreSQL SQL Server
COS(ラジアン値) COS(ラジアン値) COS(ラジアン値) COS(ラジアン値)

・タンジェント(正接)を返す

Oracle MySQL PostgreSQL SQL Server
TAN(ラジアン値) TAN(ラジアン値) TAN(ラジアン値) TAN(ラジアン値)

・コタンジェント(余接)を返す

Oracle MySQL PostgreSQL SQL Server
1/TAN(ラジアン値) COT(ラジアン値) COT(ラジアン値) COT(ラジアン値)

・アークサイン(逆正弦)

Oracle MySQL PostgreSQL SQL Server
ASIN(数値式) ASIN(数値式) ASIN(数値式) ASIN(数値式)

・アークコサイン(逆余弦)

Oracle MySQL PostgreSQL SQL Server
ACOS(数値式) ACOS(数値式) ACOS(数値式) ACOS(数値式)

・アークタンジェント(逆正接)

Oracle MySQL PostgreSQL SQL Server
ATAN(数値式)
ATAN2(Y数値式,X数値式)
ATAN(数値式)
ATAN2(Y数値式,X数値式)
ATAN(数値式)
ATAN2(Y数値式,X数値式)
ATAN(数値式)
ATN2(Y数値式,X数値式)

・ハイパボリックサイン(双曲線正弦)

Oracle MySQL PostgreSQL SQL Server
SINH(数値式) (EXP(数値)-EXP(-数値))/2 (EXP(数値)-EXP(-数値))/2 (EXP(数値)-EXP(-数値))/2

・ハイパボリックコサイン(双曲線余弦)

Oracle MySQL PostgreSQL SQL Server
COSH(数値式) (EXP(数値)+EXP(-数値))/2 (EXP(数値)+EXP(-数値))/2 (EXP(数値)+EXP(-数値))/2

・ハイパボリックタンジェント(双曲線正接)

Oracle MySQL PostgreSQL SQL Server
TANH(数値式) (EXP(数値)-EXP(-数値))/(EXP(数値)+EXP(-数値)) (EXP(数値)-EXP(-数値))/(EXP(数値)+EXP(-数値)) (EXP(数値)-EXP(-数値))/(EXP(数値)+EXP(-数値))

* Oracle以外は、指数関数により取得可能
tanh(θ)=sinh(θ) / cosh(θ)
tanh(θ)=(exp(θ) - exp(-θ))/(exp(θ) + exp(-θ))

丸め、切捨て切り上げ
・四捨五入を返す
桁数:正の数の場合は小数点以下指定数で四捨五入
   負の数の場合は整数部を指定桁数+1桁目までを四捨五入
操作内容:0で四捨五入、0以外は切り捨て

Oracle MySQL PostgreSQL SQL Server
ROUND(数値式 [, 桁数]) ROUND(数値式 [, 桁数]) ROUND(数値式 [, 桁数]) ROUND(数値式 , 桁数[, 操作内容])

・指定された桁数に切り捨てた値を返す

Oracle MySQL PostgreSQL SQL Server
TRUNC(数値式 [, 桁数]) TRUNC(数値式 [, 桁数]) TRUNCATE(数値式 [, 桁数])

・指定した数値以上で最小の整数を返す

Oracle MySQL PostgreSQL SQL Server
CEILING(数値式)
CEIL(数値式)
CEILING(数値式)
CEIL(数値式)
CEILING(数値式)
CEIL(数値式)
CEILING(数値式)
CEIL(数値式)

・指定数値以下の最大の整数を返す

Oracle MySQL PostgreSQL SQL Server
FLOOR(数値式) FLOOR(数値式) FLOOR(数値式) FLOOR(数値式)

・最も大きい値を返す

Oracle MySQL PostgreSQL SQL Server
GREATEST(数値式1,数値式2[, 数値式 ・・・]) GREATEST(数値式1,数値式2[, 数値式 ・・・])    

・最も小さい値を返す

Oracle MySQL PostgreSQL SQL Server
LEAST(数値式1,数値式2[, 数値式 ・・・]) LEAST(数値式1,数値式2[, 数値式 ・・・])    

対数とべき乗
・自然対数の底のべき乗

Oracle MySQL PostgreSQL SQL Server
EXP(数値式) EXP(数値式) EXP(数値式) EXP(数値式)

・自然対数を返す

Oracle MySQL PostgreSQL SQL Server
LN(数値式) LN(数値式)
LOG(数値式)
LN(数値式)
LOG(数値式)
LOG(数値式)

・2を底とする対数を返す

Oracle MySQL PostgreSQL SQL Server
LOG2(数値式) LOG2(数値式) LOG2(数値式) LOG2(数値式)

・常用対数を返す

Oracle MySQL PostgreSQL SQL Server
  LOG10(数値式)   LOG10(数値式)

・底を指定する対数を返す

Oracle MySQL PostgreSQL SQL Server
LOG(底の数値,数値式1) LOG(底の数値,数値式1)  

・べき乗を返す

Oracle MySQL PostgreSQL SQL Server
POW(基数,指数)
POWER(基数,指数)
POW(基数,指数)
POWER(基数,指数)
POW(基数,指数)
POWER(基数,指数)
SQUARE(数値式)

集合関数
・標準偏差を返す

Oracle MySQL PostgreSQL SQL Server
STDDEV(数値式)   STDDEV(数値式) STDEV(数値式)

・母集団(標本)標準偏差を返す

Oracle MySQL PostgreSQL SQL Server
STDDEV_POP(数値式) STDDEV(数値式)   STDEVP(数値式)

・分散を返す

Oracle MySQL PostgreSQL SQL Server
VARIANCE(数値式)   VARIANCE(数値式) VAR(数値式)

・母集団(標本)分散を返す

Oracle MySQL PostgreSQL SQL Server
VAR_POP(数値式) VARIANCE(数値式)   VARP(数値式)

・相関係数を返す
-1~1の範囲の値を返します。
-1≒は負の関連性が強い
0≒は関連性が薄い
1≒は正の関連性が強い
*年齢と給与の係数取得等に使用します。

Oracle MySQL PostgreSQL SQL Server
CORP(数値式1,数値式2)      


SQLの基本-文字列操作関数編 [SQL]

SQLの基本-文字列操作関数編

文字列の値の調査や操作のための関数をまとめてみました。 使用法は、特に難しくもなく引数から連想できるものばかりなのでサンプルは不要としました。

・文字を文字コードに変換(最初の1バイト)

Oracle MySQL PostgreSQL SQL Server
ASCII(文字列) ASCII(文字列)
HEX(文字列式) 16進の文字コードを返す
ASCII(文字列) ASCII(文字列)
UNICODE(文字列)



・数値を文字に変換

Oracle MySQL PostgreSQL SQL Server
  HEX(16進数値)   STR(数値, 桁数, 小数点以下の桁数)



・文字列のエスケープ表現
文字列内の引用符やバックスラッシュがSQLで処理されるように文字列を作成する。

Oracle MySQL PostgreSQL SQL Server
QUOTE(文字列式)



・文字列の結合

Oracle MySQL PostgreSQL SQL Server
CONCAT(文字列式,文字列式) CONCAT(文字列式,文字列式 [, 文字列式・・・])
CONCAT_WS(区切り文字, 文字列式 [, 文字列式・・・]))
文字列式 || 文字列式 文字列式 + 文字列式



・文字数の取得

Oracle MySQL PostgreSQL SQL Server
LENGTH(文字列式) CHARACTER_LENGTH(文字列式)
CHAR_LENGTH(文字列式)
LENGTH(文字列式)
CHARACTER_LENGTH(文字列式)
CHAR_LENGTH(文字列式)
LEN(文字列式)



・文字列のバイト数

Oracle MySQL PostgreSQL SQL Server
LENGTHB(文字列式) OCTET_LENGTH(文字列式)
>LENGTH(文字列式)
OCTET_LENGTH(文字列式)  



・英字の小文字変換

Oracle MySQL PostgreSQL SQL Server
LOWER(文字列式)
全角アルファベットも対象
LOWER(文字列式)
LCASE(文字列式)
LOWER(文字列式)
LCASE(文字列式)
LOWER(文字列式)
全角アルファベットも対象



・英字の大文字変換

Oracle MySQL PostgreSQL SQL Server
UPPER(文字列式)
全角アルファベットも対象
UPPER(文字列式)
UCASE(文字列式)
UPPER(文字列式)
UCASE(文字列式)
UPPER(文字列式)
全角アルファベットも対象



・単語のキャピタライズ
単語の先頭文字を大文字に変換します。

Oracle MySQL PostgreSQL SQL Server
INITCAP(文字列式)
全角アルファベットも対象
UPPER(文字列式)
UCASE(文字列式)
   



・文字列の反転

Oracle MySQL PostgreSQL SQL Server
  REVERSE(文字列式)   REVERSE(文字列式)



・文字列の長さ分指定文字で埋める

Oracle MySQL PostgreSQL SQL Server
LPAD(文字列式, 数値式, 埋め文字列)
RPAD(文字列式, 数値式, 埋め文字列)
LPAD(文字列式, 数値式, 埋め文字列)
RPAD(文字列式, 数値式, 埋め文字列)
LPAD(文字列式, 数値式[, 埋め文字列])
RPAD(文字列式, 数値式[, 埋め文字列])
 



・文字列の空白の除去

Oracle MySQL PostgreSQL SQL Server
LTRIM(文字列式)
RTRIM(文字列式)
LTRIM(文字列式)
RTRIM(文字列式)
LTRIM(文字列式)
RTRIM(文字列式)
LTRIM(文字列式)
RTRIM(文字列式)



・文字列から指定文字列の除去

Oracle MySQL PostgreSQL SQL Server
TRIM([{LEADING | TRAILING | BOTH}] 文字列式 [ FROM 削除文字列式]) TRIM([{LEADING | TRAILING | BOTH}] 文字列式 [ FROM 削除文字列式]) TRIM([{LEADING | TRAILING | BOTH}] 文字列式 [ FROM 削除文字列式])  



・空白文字列の作成

Oracle MySQL PostgreSQL SQL Server
  SPACE(数値式)   SPACE(数値式)



・連続文字列の作成

Oracle MySQL PostgreSQL SQL Server
  REPEAT(文字列式, 数値式) REPEAT(文字列式, 数値式) REPLICATE(文字列式, 数値式)



・文字列の検索(文字位置)

Oracle MySQL PostgreSQL SQL Server
INSTR(文字列式,検索文字列式[, 開始位置[,発見場所数指定]])
INSTRB(文字列式,検索文字列式[, 開始位置[,発見場所数指定]])
LOCATE(文字列式,検索文字列式[, 開始位置])
POSSTR(文字列式,検索文字列式)
INSTR(文字列式,検索文字列式[, 開始位置])
POSITION(検索文字列式 IN 文字列式)
POSITION(検索文字列式 IN 文字列式) CHARINDEX(文字列式,検索文字列式[, 開始位置])



・文字の置換え
TRANSLATEは、検索文字列式と置換文字列式を一文字づつ対応させて置き換えます。
REPLACEは、単語単位の置換えです。
例:
 文字列式=abcdefdcba
 検索文字列式=abcd
 置換文字式=置換文字
 
TRANSLATE(文字列式, 検索文字列式, 置換文字列式) → 置換文字ef字文換置
REPLACE(文字列式, 検索文字列式, 置換文字列式)  → 置換文字efdcba

Oracle MySQL PostgreSQL SQL Server
TRANSLATE(文字列式, 検索文字列式, 置換文字列式)
REPLACE(文字列式, 検索文字列式, 置換文字列式)
REPLACE(文字列式, 検索文字列式, 置換文字列式) TRANSLATE(文字列式, 検索文字列式, 置換文字列式)
REPLACE(文字列式, 検索文字列式, 置換文字列式)
REPLACE(文字列式, 検索文字列式, 置換文字列式)



・文字列の一部置換と挿入

Oracle MySQL PostgreSQL SQL Server
  INSERT(文字列式, 入れ替え位置, 置換文字数, 置換文字列式)   STUFF(文字列式, 入れ替え位置, 置換文字数, 置換文字列式)



・文字列の左右の取得

Oracle MySQL PostgreSQL SQL Server
LEFT(文字列式,数値式)
RIGHT(文字列式,数値式)
  LEFT(文字列式,数値式)
RIGHT(文字列式,数値式)



・文字列の一部の取得

Oracle MySQL PostgreSQL SQL Server
SUBSTR(文字列式, 数値式1, 数値式2) SUBSTR(文字列式, 数値式1, 数値式2)
SUBSTRING(文字列式, 数値式1[, 数値式2])
SUBSTRING(文字列式 FROOM 数値式1[ FOR 数値式2])
MID(文字列式, 数値式1, 数値式2)
SUBSTR(文字列式, 数値式1, 数値式2)
SUBSTRING(文字列式, 数値式1[, 数値式2])
SUBSTRING(文字列式 FROOM 数値式1[ FOR 数値式2])
SUBSTRING(文字列式, 数値式1[, 数値式2])


SQLの基本-日付関数編 [SQL]

SQLの基本-日付関数編

日付関数は作成、更新の日時登録などでよく使います。 日付を扱う関数は多いのですが、よく使いそうなものをまとめました。

現在の日時を取得
・CURRENT_TIMESTAMP
・CURRENT_DATE
・CURRENT_TIME
・SYSDATE :Oracle
・SYSDATE() :MySQL
・GETDATE() :SQLServer
・NOW() :MySQL,PostgreSQL
CURRENT_****は括弧を使用しないで定数みたいに扱います。



日付要素の取得
書式:EXTRACT(日付要素 FROM 値式 )
戻値:要素に対する値
日付要素

Oracle MySQL PostgreSQL 内容
    'millennium' 千年紀
    'century' 世紀
    'decade' 10年
YEAR YEAR 'year'
    'quarter' 四半期
MONTH MONTH 'month'
    'week'
DAY DAY 'day'
HOUR HOUR 'hour'
MINUTE MINUTE 'minute'
SECOND SECOND 'second'
    'millisecond' ミリ秒
    'microseconds' マイクロ秒
    'dow' 曜日
    'doy' 年間通算日
    'epoch' 1970/1/1 00:00:00からの秒数
TIMEZONE_HOUR     タイムゾーン付き時
TIMEZONE_MINUTE     タイムゾーン付き分
TIMEZONE_REGION     タイムゾーン
TIMEZONE_ABBR     タイムゾーン略称
  YEAR_MONTH   年月
  DAY_HOUR   日時
  DAY_MINUTE   日時分
  DAY_SECOND   日時分秒
  HOUR_MINUTE   時分
  HOUR_SECOND   時分秒
  MINUTE_SECOND   分秒



日付要素の取得(SQLServer)
書式:DATEPART(日付要素, 値式 )
戻値:数値

書式:DATENAME(日付要素, 値式 )
戻値:文字列

日付要素

SQLServer 内容
year
quarter 四半期
week
day
hour
minute
second
millisecond ミリ秒
dayofyear 年間通算日
weekday 曜日



日付の書式指定
MySQL
書式:DATE_FORMAT(日付式, 書式文字 )
書式:TIME_FORMAT(日付式, 書式文字 )
戻値:文字列
Oracle
書式:TO_CHAR(日付式、書式文字)
戻値:文字列

書式文字

MySQL Oracle 内容
  E M:明治,T:大正,S:昭和,H:平成
  EE 明治,大正,昭和,平成
%Y YYYY 年(4桁)
%y YY 年(2桁)
%m MM 月(01~12)
%c FMMM 月(1~12)
%M   月(January,February,・・・)
  MONTH 月(1月、2月・・・12月)
%b   月(Jan,Feb,・・・)
  MON 月(1月、2月・・・12月)
%d dd 日(01~31)
%e FMdd 日(1~31)
%D   日(1st,2nd,3rd,,,31st)
%w   曜日(0:日曜~6:土曜)
%W   曜日(Sunday,Monday、・・・)
  DAY 曜日(日曜日,月曜日、・・・)
%a   曜日(Sun,Mon、・・・)
  DY 曜日(日,月、・・・)
%H HH24 時(00~23)
%k   時(0~23)
%h HH 時(01~12)
%l FMHH 時(1~12)
%p   AM,PM
  AM(A.M.) 午前,午後
%i MI 分(01~59)
%s FMMI 秒(0~59)
%S SS 秒(00~59)
%f   マイクロ秒(000000~999999)
%j   年間通算日(001~365)
%u   年間通算週:月曜から(00~53)
%U   年間通算週:日曜から(00~53)
%v   年間通算週:月曜から(01~53)
%V   年間通算週:日曜から(01~53)
%r HH:MI:SS AM 12 時間形式の時刻(hh:mm:ss に続けて AM または PM)
%T HH24:MI:SS 24 時間形式の時刻(hh:mm:ss)



日付の加減算
MySQL
書式:ADDDATE(日付式, 書式文字 )
書式:ADDTIME(日付式, 書式文字 )
戻値:文字列
Oracle(月単位の加減算)
書式:ADD_MONTHS(日付式、月数)
戻値:日付(DATE型)



日付の差を求める
MySQL
書式:DATEDIFF(日付式,日付式)
戻値:日数
Oracle
書式:MONTHS_BETWEEN(日付式,日付式)
戻値:月数



次の曜日を求める
Oracle
書式:NEXT_DAY(日付式,曜日)
書式:NEXT_DAY(日付式,週初めからの日数)
戻値:日付(DATE型)



月末を求める
Oracle,MySQL
書式:LAST_DAY (日付式)
戻値:日付(DATE型)


SQL言語(表データの更新:INSERT,UPDATE,DELETE,MERGE) [SQL]

SQL言語(表データの更新:INSERT,UPDATE,DELETE,MERGE)

データベースの表を更新する基本的なSQLのメモです。


・INSERT

表にデータを追加します。

基本構文1


INTOに表名のみを指定した場合の値の並びは、表作成時の列順とみなされます。
列が指定された場合の値の並びは、列名の並び順に値が並んでいるとみなされます。

基本構文2

値を副問い合わせ(SELECT文)により指定します。複数のデータを追加することが出来ます。

基本構文3

複数のデータを一度に追加することが出来ます。INSERT文を複数発行するより効率がよく複数の表に 一度に追加することが可能です。

基本構文4

基本構文3と同様ですが、条件によりデータを追加する表を指定することが出来ます。 このとき使用する条件は、SELECT文で指定した表の列の条件式です。
ALL:条件に一致したINTOを全て実行
FIRST:最初に条件に一致したINTOのみを実行

・UPDATE
行データの値を変更します。

基本構文1

検索条件で指定した行データの値を変更します。

基本構文2

指定した列名の値を副問い合わせ(SELECT文)から取得しセットします。 WHERE EXISTSのFROM以降は、副問い合わせのFROM以降と同じ記述にします。

・DELETE
行データを削除します。

基本構文1

検索条件で指定した行データを削除します。

基本構文1

複数の行データを副問い合わせにより検索し削除します。

・MERGE
条件により行の挿入または更新を行う。

基本構文

プログラム中のIF文が減るので嬉しいSQL文です。 条判定は表または副問い合わせの形ですが、OracleのDUALで仮想の表を 作成しSELECT文で値を返すようにすれば値による条件でUPDATEかINSERTが 選択できます。 MERGE文はOracle9iから使用可能です。


SQL言語(SELECT 表結合) [SQL]

SQL言語(SELECT 表結合)

データベースで表結合を使用しないケースは少ないと思いますので、
表結合の一般的な方法を理解しておく必要があります。

・内部結合

特定のキーで表を結合します。どちらかの表にデータが存在しない行は結果に
表示されません。私は、双方のキーデータにNULLが存在しないことが確実な場合のみに使用しています。




例1:JOINを使用する場合





例1:JOINを使用しない場合(最近あまり使用しない)


・外部結合
外部結合では、どちらかのテーブルのレコードはすべて表示されます。 外部結合には、LEFT OUTER JOINとRIGHT OUTER JOINがあり、左右いずれかの表を優先して表示を行う指定です。

LEFT OUTER JOIIN


RIGHT OUTER JOIIN


このほかに、結合を使用して一つのテーブルを別のテーブルに見立てて結合することも出来ます。この方法は プログラムではなるべく使わないようにしていますが、保守でデータが欲しいときには割りと多用しています。


SELECT文の基礎 [SQL]

SQL言語(SELECT)

プログラムでは勿論ですが保守でもSQLを使用することは多々あります。
そこで、SQLの基礎の備忘録

メモ:

・予約語及び英字の列名は全て大文字で記述する。(気持ち早くなるかな)

・括弧及びスペースの扱いの統一を行う。

・開発の場合は複雑な処理はプログラムで行う。

・保守及び個人的な興味の為、複雑なSQLに挑戦することは必要

・SELECT文

SELECT文は、DBからデータを取得する場合に使用します。

SELECT文は、SELECT句、FROM句、WHERE句、GROUP BY句、HAVING句及びORDER BY句により
生成されます。


基本書式:


SELECT句
検索の結果データを抜き出すテーブルのカラムを指定します。
・ALL:重複行も全てを表示する。(デフォルト)
・DISTINCT:重複行の削除。
・*:FROM句で指定されたテーブルの列名を全て取得する。
・列名,...:取得したい列名を指定
・列名 AS 列別名,...:列名の別名をつける時に使用。


基本例:

別名を使用:
出力結果の列名を別の名前に変更したい場合はASを使用し列名に別の名前を割当てることが出来ます。
値を使用1:
検索結果の列に値を結合したい場合は、&を指定し結果と値を結合します。

値を使用2:
検索に全て同じ値の列を追加したい場合は、SELECT句に値を指定します。

関数の使用:
処理系により様々な関数が用意されています。マニュアル等を参考にしてください。


FROM句
検索するテーブルを指定します。また、テーブルとみなすことが出来るものを指定することができます。 何をテーブルとみなすかはDBMSに依存します。DBMSのマニュアルを確認してください。
・表名 [ 表別名 ] , ...:取得する表名を指定する。
・表別名:表別名を指定することにより長い表名も扱いやすい名前に変更できます。
表別名の使用:
複数の表を使用した場合、表の表記がわずらわしいときがあります。このような場合に 表別名を使用して表名を単純化します。

↓別名使用

↓別名使用(JOINを使わない場合)


WHERE句
・検索条件:取得データを検索条件により検索対象データを選択する。
・複数の条件指定:ANDまたはORにより指定。
・条件式の否定:条件式にNOTを指定することで条件式の否定が使用できる。
条件式

条件式 意味
= 等しい a='10'
列aの値が10と等しい
< 小さい a<'10'
列aの値が10より小さい
<= 以下 a<='10'
列aの値が10以下
> 大きい a>'10'
列aの値が10より大きい
>= 以上 a>='10'
列aの値が10以上
LIKE ワイルドカード a LIKE 'a%'
先頭の文字がa
a LIKE '%a'
最後文字がa
a LIKE 'a%b'
先頭の文字がaで最後の文字がb
ワイルドカードには「_」と「%」があり、 「_」は任意の1文字で、「%」は0文字以上の任意の文字列
BETWEEN 範囲指定 a BETWEEN '10' AND '10'
列aの値が10~20までの値
IS NULL NULLの検索 a IS NULL
列aに値がセットされていない(NULL)
IS NOT NULL 非NULLの検索 a IS NOT NULL
列aに値がセットされている
IN 複数の値を検索 a IN ('2','4','6')
列aの値が2,4,6のデータを検索
=とORで表現できるがINを使用した方がコストがかからないといわれている。

GROUP BY句
表内の行をグループ化します。
* SELECT句に指定したグループ関数を使用していない列は、GROUP BY句に指定しなければなりません。
グループ関数
関数名 意味
AVG 平均 SELECT AVG(年齢) FROM 社員マスタ
社員の平均年齢
COUNT NULLでない行数 SELECT COUNT(社員) FROM 社員マスタ
社員数
MAX 最大値 SELECT MAX(給与) FROM 社員マスタ
最高額給与
MIN 最小値 SELECT MIN(給与) FROM 社員マスタ
最低額給与
SUM 合計 SELECT SUM(給与) FROM 社員マスタ
社員給与の合計



HAVING句
グループ分けしたデータにWHERE句と同様に検索条件を追加します。 HAVING句を使用する場合は、GROUP BY句によりグループ化されている必要があります。



ORDER BY句
SELECTで指定した列の検索結果をソートします。
・ASC:昇順
・DESC:降順
* ソートキーは複数指定可能


メッセージを送る

この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。

×

この広告は1年以上新しい記事の更新がないブログに表示されております。