SQL道場

SQLとは

SQLとは「Structured Query Language」の略で「構造化問い合わせ言語」という意味です。SQLは、米国の米国の米国規格協会(ANSI)と国際標準化機構(ISO)で、リレーショナルデータベースの標準言語であることが認められています。

SQLにはデータ定義機能、データ操作機能、データ制御の3つの機能があります。

DDL
Data Definition Language
データ定義命令
データベース、表、ユーザー等の作成、変更、削除を行うCREATE、ALTER、DROPなど
DML
Data Manupilation Language
データ操作命令
データの検索、挿入、更新、削除を行うSELECT、INSERT、UPDATE、DELETEなど
DCL
Data Control Language
データ制御文
データの変更を確定、取り消すためのコマンド COMMIT、ROLLBACKなど

SQLの記述について

予約後の扱いについて

DMLの「SELECT」、「FROM」、「WHERE」、「INSERT」、「UPDATE」、「DELETE」など、DDLの「CREATE TABLE」、「DROP TABLE」命令、DCLの「GRANT」、「REVOKE」命令などの単語は予約語です。

予約語は表名(テーブル名)、列名(カラム名)などとして使用できません。しかし、どうしても予約語を表名や列名として使用した場合は、予約語を[]で囲んで指定します。

SELECT 
    [INSERT],
    [UPDATE]
FROM [UPDATE]
;

システム開発の現場では、予約語を表名、列名などとして使用することは紛らわしく、システムトラブルの温床となるため使用することはありません。

大文字と小文字の区別

SQL文では大文字と小文字は区別していません。以下のSQLはいずれも同じ結果が返ってきます。

SELECT * FROM COMMON;
SELECT * FROM common;
SELECT * FROM Common;

ただし、データベース製品によってはオブジェクト名(テーブルなど)は大文字と小文字を区別されることがあります。また、データベース内の文字列データは大文字と小文字を区別されます。ただし、これもデータベース製品や設定などによって区別されないこともあります。

SELECT * FROM COMMON WHERE NAME = 'parts';
SELECT * FROM COMMON WHERE NAME = 'Parts';

空白文字

空白文字とは半角スペース、タブ、改行などです。

SQL文内では空白文字が連続した場合でも1つの空白文字として扱われます。長いSQL文を記述する場合は、改行や空白を入れて整形すると読みやすくなります。※全角スペースは空白文字ではありません。

以下のSQLはいずれも同じ結果が返ってきます。

SELECT * FROM COMMON WHERE NUMBER = 1;
SELECT *     FROM    COMMON    WHERE    NUMBER = 1;
SELECT 
      *     
FROM COMMON    
WHERE NUMBER = 1;

コメントの記述

1行のコメントを記述する場合は「–」(マイナス記号を2つ連続)に続けて記述します。

複数行のコメントを記述する場合は、コメント文を「/*」(開始)と「*/」(終了)で囲みます。SQL文中であっても「/*」「*/」で囲みます。

1行コメント行

--SELECT * FROM COMMON WHERE NUMBER = 1;
SELECT *     FROM    COMMON    WHERE    NUMBER = 1;
SELECT 
      *     
FROM COMMON    
WHERE NUMBER = 1;

複数行コメント行

/*
SELECT 
      NUMBER,
      MAKER,
      ID,
      NAME
FROM COMMON    
WHERE NUMBER = 1
;
*/

文中コメント行

SELECT 
      NUMBER,
      /* MAKER,
      ID,
      NAME */
FROM COMMON    
WHERE NUMBER = 1
;

SQL文末のカンマ要否

MySQL、Oracle、PostgreSQL、SQL ServerともSQL文の入力の最後に「;」つけます。DB2は「ステートメント終了文字」の指定として「;」(セミコロン)または「@」などを指定しますがデフォルトでは「;」でプロシージャの終わりが「@」。

AccessはSQL文末に「;」をつけません。

SQLServerではコマンド入力の場合は改行して「GO」があればSQL文のおわりとみなされます。また、SQL文をスクリプトとして記述しない場合も「;」は不要です。SQLLiteをSQLLiteManagerから使う場合は、SQL文の入力の最後には「;」をつけてもつけなくてもどうちらもで構いません。

関数

算術関数

意味命令対象
絶対値ABSSQL Server
MySQL
PostgreSQL
Oracle
DB2 SQLite
Access
最小の整数CEIL/CEILINGSQL Server
MySQL
PostgreSQL
Oracle DB2
べき乗POW/POWERSQL Server
MySQL
PostgreSQL
Oracle DB2
3乗根CBRT
サインSINSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
タンジェントTANSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
逆タンジェントATAN/ATNSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
ラジアンを度に変換DEGREESSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
円周率PISQL Server
MySQL
PostgreSQL
AND演算BITAND
意味命令対象
符号を得るSIGN/SGNSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
値をまるめるROUNDSQL Server
MySQL
PostgreSQL
Oracle DB2
SQLite Access
平方根SQRT/SQRSQL Server
MySQL
PostgreSQL
Oracle DB2
指数値EXPSQL Server
MySQL
PostgreSQL
Oracle DB2
コサインCOSSQL Server
MySQL
PostgreSQL
Oracle DB2
SQLite Access
逆サインASINSQL Server
MySQL
PostgreSQL
Oracle DB2
逆タンジェントATAN2/ATN2SQL Server
MySQL
PostgreSQL
Oracle DB2
引数の中の最大値GREATESTMySQL
Oracle
乱数RAND/RANDOM/RNDSQL Server
MySQL
PostgreSQL
SQLite Access
意味命令対象
最大の整数FLOORSQL Server
MySQL
PostgreSQL
Oracle DB2
切り捨てTRUNC/TRUNCATEMySQL
PostgreSQL
Oracle DB2
2乗SQUARESQL Server
自然対数LN/LOG/LOG10SQL Server
MySQL
PostgreSQL
Oracle DB2
Access
双曲線コサインCOSH
逆コサインACOSSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
コタンジェントCOTSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
引数の中の最小値LEASTMySQL Oracle
余剰MODMySQL
PostgreSQL
Oracle DB2

文字列関数

変換

意味命令対象
文字をASCIIコードに変換ASC/ASCIISQL Server
MySQL
PostgreSQL
Oracle DB2
Access
ユニコードを文字に変換NCHAR/NCHRSQL Server
Oracle
数値を8進数の文字列に変換OCTMySQL
文字列を区切り文字を使って連結CONCAT_WSMySQL
文字列が何番目に見つかったかを返すFIELDMySQL
マルチバイト文字をコードに変換ORDMySQL
先頭文字を大文字に変換INITCAP
数値を2進数の文字列に変換BINMySQL
N番目の文字列を返すELTMySQL
英文字を大文字に変換UPPER/UCASESQL Server
MySQL
Oracle
PostgreSQL
DB2
SQLite Access
ANSI
意味命令対象
ASCIIコードを文字に変換CHR/CHARSQL Server
MySQL
PostgreSQL
Oracle DB2
Access
数値を文字列に変換STRSQL Server
Access
数値を16進数の文字列に変換HEXMySQL
数値を指定した進数の文字列に変換CONVMySQL
カンマで区切られた文字の中から文字を探すFIND_IN_SETMySQL
英文字を小文字に変換LOWER/LCASESQL Server
MySQL
Oracle
PostgreSQL
DB2
SQLite Access
ANSI
文字をユニコードに変換UNICODESQL Server
文字列のビット長を取得BIT_LENGTHMySQL
指定されたビットに対する文字列のセットを返すMAKE_SETMySQL

編集

意味命令対象
文字列から指定された文字を削除TRIMSQL Server
MySQL
Oracle
PostgreSQL
DB2
SQLite Access
ANSI
文字列の右側の空白を削除RTRIMSQL Server
MySQL
Oracle
PostgreSQL
DB2
SQLite Access
左から文字列を充填LPAD
文字列の左部分を取り出すLEFT
文字列を指定された回数を繰り返すREPEAT/REPLICATE
文字列を反転REVERSE
空白文字列を作成SPACE
文字列を置き換えTRANSLATE
文字列の部分切り出しSUBSTR
SOUNDEXキーを求めるSOUNDEX
意味命令対象
文字列の左側の空白を削除LTRIMSQL Server
MySQL
Oracle
PostgreSQL
DB2
Access
文字列の両端の空白を削除BTRIM
右から文字列を充填RPAD
文字列の右部分を取り出すRIGHT
文字列を置換REPLACE
文字列を結合CONCAT
文字列を置換または挿入するINSERT
文字列の部分的入れ替えTRANSLATE
文字列の部分切り出しSUBSTRING

文字長

意味命令対象
文字列の長さを得るLEN/LENGTH
意味命令対象
文字列の文字長を得るCHAR_LENGTH/CHARACTER_LENGTH
意味命令対象
文字列のバイト数を得るOCTET_LENGTH

検索

意味命令対象
文字列内に含まれる文字列を検索CHARINDEX
文字列中の文字を検索POSITION
文字列内の文字位置を検索LOCATE
意味命令対象
文字列内に含まれる文字列を検索INSTR
文字列中の文字を検索POSSTR

日付刻関数

日付・時刻

意味命令対象DB
現在の日付を得るCURRENT_DATE
現在の日付を得るCURDATE
現在の時刻を得るCURRENT TIME
現在の日付と時刻を得るCURRENT_TIMESTAMP
現在の日付と時刻を得るSYSDATE
現在の日付と時刻を得るNOW
現在のUTCの日付と時刻を得るGETUTCDATE
年と週を返すYEARWEEK
意味命令対象DB
現在の日付を得るCURRENT DATE
現在の時刻を得るCURRENT_TIME
現在の時刻を得るCURTIME
現在の日付と時刻を得るCURRENT TIMESTAMP
現在の日付と時刻を得るGETDATE
現在の日付と時刻を得るTIMEOFDAY
UNIXタイムスタンプを得るUNIX_TIMESTAMP

変換

意味命令対象DB
秒を時分秒に変換SEC_TO_TIME
意味命令対象DB
時分秒を秒に変換TIME_TO_SEC

編集

意味命令対象DB
年月日を取り出すDATE
月を取り出すMONTH
時を取り出すHOUR
秒を取り出すSECOND
曜日を英文字列で得るDAYNAME
日付を整形するDATE_FORMAT
日付値から任意の日付要素の数値を得るEXTRACT
日付値から任意の日付要素の数値を得るDATEPART
書式によって整形されたUNIXタイムスタンプを得るFROM_UNIXTIME
意味命令対象DB
年を取り出すYEAR
日を取り出すDAY
分を取り出すMINUTE
月を英文字列で得るMONTHNAME
日付要素を文字列で得るDATENAME
時刻を整形するTIME_FORMAT
日付値から任意の日付要素の数値を得るDATE_PART
日付値から任意の日付要素の数値を切り捨てるDATE_TRUNC

計算

意味命令対象DB
年間の通算日を得るDAYOFYEAR
曜日を数値で得るDAYOFWEEK
曜日を数値で得るWEEKDAY
次の指定した曜日の日付値を得るNEXT_DAY
年齢を計算するAGE
日付の足し算DATE_ADD
日付の引き算DATE_SUB
日付の差を月単位で計算MONTHS_BETWEEN
紀元0年1月1日からの日数を日付にするFROM_DAYS
期間PにN月を加算PERIOD_ADD
意味命令対象DB
日を得るDAYOFMONTH
四半期を数値で得るQUARTER
月の最終日の日付値を得るLAST_DAY
年間の通算週を取得WEEK
日付の足し算DATEADD
日付の差DATEDIFF
日付の引き算SUBDATE
月を加算ADD_MONTHS
紀元0年1月1日からの日数を得るTO_DAYS
期間p1と期間p2の間の月数を計算PERIOD_DIFF

集計関数

集計

意味命令対象DB
平均値を算出するAVG
最大値を得るMAX
相間係数を算出するCORR/CORRELATION
集計行かどうか判定GROUPING
意味命令対象DB
合計値を算出するSUM
最小値を得るMIN
行数を数えるCOUNT

集計・偏差

意味命令対象DB
標準偏差を算出するSTDDEV/STDEV
意味命令対象DB
母集団標準偏差を算出するSTDDEV_POP/STDEVP

集計・分散

意味命令対象DB
分散を算出するVAR/VARIANCE
意味命令対象DB
母集団分散を算出するVAR_POP/VARP

分析関数

分析

意味命令対象DB
同順位を飛ばさずに順位をつけるDENSE_RANK
行番号をつけるROW_NUMBER
回帰直線のy切片を求めるREGR_INTERCEPT
ウィンドウを使った合計を求めるSUM~OVER~
意味命令対象DB
同順位を飛ばして順位をつけるRANK
回帰直線の傾きを求めるREGR_SLOPE
回帰直線の確定係数を求めるREGR_R2

変換関数

変換

意味命令対象DB
データ型を変換CAST
データ型を変換CONVERT
NULL値を変換ISNULL
NULL値を変換NVL
日付型に変換TO_DATE
意味命令対象DB
NULL値でない最初の値を返すCOALESCE
値を変換DECODE
2つの引数が等しい場合にNULLを返すNULLIF
文字列型に変換TO_CHAR
数値型に変換TO_NUMBER

システム情報関数

システム

意味命令対象DB
権限チェックの際に使用されるユーザ名を戻すCURRENT_USER
現在データベースを実行しているユーザ名USER
意味命令対象DB
データベース接続を開始させたユーザ名を戻すSESSION_USER

SQL基本操作

テーブルからデータ(レコード)を抽出する基本的なやり方

基本的なレコード抽出方法

条件にマッチするレコードの検索方法

条件にマッチ(合致)するレコードの検索方法

テーブルとテーブルを結合する方法

RDBは複数のテーブルを作成しそれぞれのキー同士を結合してデータを抽出するのが基本となります。テーブル同士を結合して抽出する方法を解説しています。

テーブル結合の詳細

行を並び替え(ソート)して抽出する方法

行を並び替えする方法

データを登録する

テーブルに行を挿入する方法(INSERT)

データ更新する

データ更新する方法(UPDATE)

データを削除する

テーブルの行を削除する(DELETE)

お手軽テーブルバックアップ方法

SELECT結果から表(テーブル)を作成する方法

テーブルを参照する必要がない関数やシステム日付を取得する場合(仮想表)

対応:OracleMySQLSQLServer

関数テストやDB日付の取得等、実テーブル参照をする必要がない場合は仮想表を使います。

仮想表(DUAL)を使う方法