|
|
MySQLの演算子とSQL関数
インデックスへ戻る
演算子
MySQLで利用できる演算子について一覧を以下に示す。
| 種類 |
演算子 |
| 論理演算子 |
AND, OR, NOT, XOR |
| 比較演算子 |
=, >, <, <=, >=, <>, != |
| IS (NOT) NULL |
| (NOT) IN, (NOT) EXISTS |
| (NOT) BETWEEN |
| (NOT) LIKE |
| 正規表現 |
REGEXP |
| 算術演算子 |
+, -, *, / |
| 文字列(連結)演算子 |
|| |
| 日付/時刻演算子 |
+, - |
| 集合演算子 |
UNION (ALL) |
文字列関数
文字列関数の一覧を以下に示す。
| 関数 |
機能 |
サンプル |
| ASCII(x) |
文字列xの左端の文字コードを10進数で返す。 |
ASCII('2') ⇒ 50 |
| BIN(N) |
整数を２進数で返す。 |
BIN(12) ⇒ '1100' |
| BIT_LENGTH(x) |
文字列xの長さをビットで返す。 |
BIT_LENGTH('text') ⇒ 32
BIT_LENGTH('あ') ⇒ 16 |
| CHAR(N,...) |
10進数NのASCIIコード値から文字を返す。 |
CHAR(77,121,83,81,'76') ⇒ 'MySQL' |
| CHAR_LENGTH(x) |
文字列xの文字数を返す。
マルチバイト文字は１文字とみなす。 |
CHAR_LENGTH('text') ⇒ 4
CHAR_LENGTH('あ') ⇒ 1 |
| CONCAT(x,y,...) |
文字列x,yを順番に連結する。
いずれかの引数がNULLのときはNULLを返す。 |
CONCAT('My','S','QL') ⇒ 'MySQL'
CONCAT('My',null,'QL') ⇒ null |
CONCAT_WS
('区切り文字',x,y,...) |
文字列を区切り文字で連結してその結果を返却する。 |
CONCAT_WS(',','My',S','QL')
⇒ 'My,S,QL' |
| CONV(N,from_base,to_base) |
数値を、進数の異なる数値に変換する。 |
CONV("a",16,2) ⇒ '1010'
CONV(20,16,10) ⇒ '32' |
| ELT(N,x1,x2,x3...) |
N番目の引数で指定した文字列を返却する。 |
ELT(1,'a','b','c','d') ⇒ 'a'
ELT(4,'a','b','c','d') ⇒ 'd' |
| FIELD(str,x1,x2,x3...) |
x1,2,3,...リスト内のstrのインデックスを返却する。
strが検出されない場合は、0を返す。(ELT()の逆) |
FIELD('a','a','b','c') ⇒ 1
FIELD('c','a','b','c') ⇒ 3 |
| HEX(x) |
数値または文字列を16進数で返却する。 |
HEX(255) ⇒ 'FF'
HEX('あ') ⇒ 'A4A2'(EUC-JPの場合) |
| INSERT(str,pos,len,newstr) |
str内の位置posから長さlenまでの文字列を
newstrに置き換えた文字列を返す。 |
INSERT('あいうえお',3,2,'か')
⇒ 'あいかお' |
| INSTR(str,substr) |
str内で最初にsubstrが出現する位置を返却する。 |
INSTR('foobarbar','bar') ⇒ 4
INSTR('あいうえお', 'う') ⇒ 3 |
| LEFT(str,len) |
strの左端にある長さlenの文字を返却する。
RIGHTの逆。 |
LEFT('foobar',4) ⇒ 'foob'
LEFT('abcd',6) ⇒ 'abcd' |
| LENGTH(str) |
strの長さ(バイト)を返却する。 |
LENGTH('foobar') ⇒ 6
LENGTH('あいうえお') ⇒ 10 |
LOCATE(x,str)
LOCATE(x,str,pos) |
str内の指定位置(pos)より後ろで、
最初に検索文字列xが出現する位置を返却する。
posを指定しなければ、先頭から検索する。 |
LOCATE('bar','foobarbar') ⇒ 4
LOCATE('え','あいうえおうえ',5) ⇒ 7 |
| LOWER(str) |
文字列を小文字に変換する。UPPERの逆。 |
LOWER('abcDEFG') ⇒ 'abcdefg'
LOWER('あいう') ⇒ 'あいう' |
| LPAD(str,len,padstr) |
文字列strがlenの長さになるまで
strの左側にpadstrを埋める。RPADの逆。 |
LPAD('123',5,'0') ⇒ '00123'
LPAD('あいう',5,'x') ⇒ 'xxあいう'
LPAD('abcdef',3,'0') ⇒ 'abc' |
| LTRIM(str) |
文字列の左側にある空白文字を削除。RTRIMの逆。 |
LTRIM(' 123') ⇒ '123' |
| MID(str,pos,len) |
SUBSTRING(str,pos,len)のシノニム。 |
|
| OCT(N) |
Nの８進数の文字列を返却する。 |
OCT(12) ⇒ '14' |
| QUOTE(str) |
strをSQLで正しいエスケープデータ値として返却する。
引数がNULLの場合、単一引用符で囲まれずに返される。 |
QUOTE("Don't") ⇒ 'Don\'t'
QUOTE(NULL) ⇒ NULL |
| REPEAT(str,count) |
文字列を指定した回数繰り返す。 |
REPEAT('SQL',3) ⇒ 'SQLSQLSQL'
REPEAT('げ',3) ⇒ 'げげげ' |
| REPLACE(str,x,y) |
文字列str内のxをyに置換する。 |
REPLACE('<br','<','<') ⇒ '<br'
REPLACE('あいうえお','う','woo')
⇒ 'あいwooえお' |
| REVERSE(str) |
文字列strの順序を逆にする。 |
REVERSE('abc') ⇒ 'cba'
REVERSE('あいうえお') ⇒ 'おえういあ' |
| RIGHT(str,len) |
文字列strの右側にある長さlenの文字を返す。
LEFTの逆。 |
RIGHT('foobarbar',4) ⇒ 'rbar'
RIGHT('あいうえお',4) ⇒ 'いうえお' |
| RPAD(str,len,padstr) |
文字列strがlenの長さになるまで
strの右側にpadstrを埋める。LPADの逆。 |
RPAD('123',5,'0') ⇒ '12300'
RPAD('あいう',5,'x') ⇒ 'あいうxx'
RPAD('abcdef',3,'0') ⇒ 'abc' |
| RTRIM(str) |
文字列の右側にある空白文字を削除。LTRIMの逆。 |
RTRIM('123 ') ⇒ '123' |
| SPACE(N) |
N文字のスペースを返却する。 |
SPACE(6) ⇒ ' ' |
SUBSTRING(str,pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos)
SUBSTRING
(str FROM pos FOR len) |
部分文字列を返却する。lenが省略された場合は、
pos文字以降の文字列を返却する。
FROM を使用する形式は SQL-92 の構文。 |
SUBSTRING('Quadratically',5,6)
⇒ 'ratica'
SUBSTRING('あいうえお',3,2)
⇒ 'うえ'
SUBSTRING('foobarbar' FROM 4)
⇒ 'barbar' |
| SUBSTRING_INDEX(str,x,count) |
str文字列中の区切り文字xが
count番目の直前の部分文字列を返却する。
countが整数なら左から数える。
countが負数の場合は、右から数える。 |
SUBSTRING_INDEX('a;b;c;d;e;f',';',2)
⇒ 'a;b'
SUBSTRING_INDEX('a;b;c;d;e;f',';',-2)
⇒ 'e;f'
|
TRIM(str)
TRIM([BOTH | LEADING |
TRAILING] [remstr] FROM str) |
引数がstrのみの場合は、
LTRIM,RTRIMを両方適用した場合と同じ。
LEADINGは、LTRIMと同じ。
TRALINGは、RTRIMと同じ。
remstrは、削除対象文字で省略時はスペース。 |
TRIM(' bar ') ⇒ 'bar'
TRIM(BOTH 'x' FROM 'xxxbarxxx')
⇒ 'bar'
TRIM(LEADING 'x' FROM 'xxxbarxxx')
⇒ 'barxxx'
TRIM(TRAILING 'x' FROM 'xxxbarxxx')
⇒ 'xxxbar'
|
| UPPER(str) |
文字列を大文字に変換する。LOWERの逆。 |
UPPER('abcDEFG') ⇒ 'ABCDEFG'
UPPER('あいう') ⇒ 'あいう' |
数学関数
数学関数の一覧を以下に示す。すべての数学関数はエラーの場合NULLを返す。
| 関数 |
機能 |
サンプル |
| ABS(X) |
Xの絶対値を返す。 |
ABS(2) ⇒ 2
ABS(-32) ⇒ 32 |
| CEILING(X),CEIL(X) |
X以上の最も小さい整数値を返す。 |
CEILING(1.23) ⇒ 2
CEIL(-1.23) ⇒ -1 |
| CRC32(expr) |
巡回冗長検査値を計算し、32 ビットの符号なしの値を返す。 |
CRC32('MySQL') ⇒ 3259397556 |
| DIV |
整数の除算。 FLOOR() と同様。 |
SELECT 5 DIV 2 ⇒ 2 |
| EXP(X) |
e（自然対数の底）をX乗した値を返す。 |
EXP(2) ⇒ 7.389056
EXP(-2) ⇒ 0.135335 |
| FLOOR(X) |
X 以下の最も大きい整数値を返す。 |
FLOOR(1.23) ⇒ 1
FLOOR(-1.23) ⇒ -2 |
| GREATEST(X,Y,...) |
最も大きい引数を返す。各引数の比較方法は、LEASTと同じ。 |
GREATEST(34.0,5.0,767.0) ⇒ 767.0
GREATEST("B","A","C") ⇒ "C" |
| LEAST(X,Y,...) |
最も小さい引数を返す。各引数の比較方法は以下のとおり。
・戻り値がINTEGERの場合または引数が全て整数の場合
⇒ 各引数は整数として比較される。
・戻り値がREALの場合または引数が全て実数の場合
⇒ 各引数は実数として比較される。
・
|
LEAST(34.0,5.0,767.0) ⇒ 5.0
GREATEST("B","A","C") ⇒ "A" |
| LOG2(X) |
2 を底とする X の自然対数を返す。
特定の数値の格納に何ビット必要か調べるのに役立つ。 |
LOG2(65536) ⇒ 16.000000 |
| MOD(N,M) , % |
N を M で割ったときの余りを返す。 |
MOD(234, 10) ⇒ 4
SELECT 253 % 7 ⇒ 1 |
| PI() |
π の値を返す。 |
SELECT PI() ⇒ 3.141593 |
| POW(X,Y), POWER(X,Y) |
X を Y 乗した値を返す。 |
POW(2,2) ⇒ 4
POW(2,-2) ⇒ 0.250000 |
| RAND(), RAND(N) |
0〜1.0の範囲のランダムな浮動小数点数を返す。
引数にシード値(繰り返し)を指定できる。 |
RAND() ⇒ 0.9233482386203
RAND(20) ⇒ 0.15888261251047
RAND(20) ⇒ 0.15888261251047 |
| ROUND(X),ROUND(X,D) |
引数 X を最も近い整数に丸めた値を返す。
小数部の桁数を D に指定された桁数に丸める。 |
ROUND(1.58) ⇒ 2
ROUND(1.298, 1) ⇒ 1.3
ROUND(23.298, -1) ⇒ 20 |
| SIGN(X) |
X が負数/ゼロ/正数かに応じて-1/0/1 を返す。 |
ROUND(1.58) ⇒ 2
SIGN(-32) ⇒ -1
SIGN(0) ⇒ 0
SIGN(234) ⇒ 1 |
| TRUNCATE(X,D) |
数値 X の小数部を D に指定された桁数に切り捨てた値を返す。 |
TRUNCATE(1.223,1) ⇒ 1.2
TRUNCATE(-1.999,1) ⇒ -1.9 |
日付関数
不正な日付や時刻を入力した場合、戻り値はNULLを返す。
| 関数 |
機能 |
サンプル |
ADDDATE
(date,INTERVAL expr unit) |
INTERVALを指定する形式の場合、DATE_ADD()のシノニム。 |
ADDDATE('1998-01-02', INTERVAL 31 DAY)
⇒ '1998-02-02' |
| ADDDATE(expr,days) |
exprには、日付式か日付時刻式(※)を指定する。
days には expr に加える日数を指定する。
※日付/時刻型の「値の格納方法」を参照。 |
ADDDATE('1998-01-02', 31)
⇒ '1998-02-02' |
| ADDTIME(expr,expr2) |
expr に expr2 を加えた結果が返される。
expr には日付式か日付時刻式を指定し、
expr2 には時刻式を指定する。 |
ADDTIME('2006-11-10 23:59:59.99'
,'01:00:00.01')
⇒ '2006-11-11 01:00:00.000000' |
CURDATE()
CURRENT_DATE
CURRENT_DATE() |
現在の日付を文字列もしくは数値で返す。 |
CURDATE() ⇒ '2006-11-10'
CURDATE() + 0 ⇒ 20061110 |
CURTIME()
CURRENT_TIME
CURRENT_TIME() |
現在の時刻を文字列もしくは数値で返す。 |
CURTIME() ⇒ '23:50:26'
CURTIME() + 0 ⇒ 235026.000000 |
NOW()
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
LOCALTIME
LOCALTIME()
SYSDATE() |
現在の日付と時刻を文字列もしくは数値で返す。 |
NOW() ⇒ '2006-11-10 20:41:55'
NOW() + 0
⇒ 20061110204223.000000 |
| DATE(expr) |
日付式または日付時刻式 expr の日付部分を取り出す。 |
DATE('2006-11-10 20:41:55')
⇒ '2006-11-10' |
| DATEDIFF(expr,expr2) |
2つの日付の差を日数単位で返す。 |
DATEDIFF('1997-12-31 23:59:59'
,'1997-12-30') ⇒ 1 |
DATE_ADD
(date,INTERVAL expr unit)
DATE_SUB
(date,INTERVAL expr unit) |
日付を加算(減算)した結果を返す。
unitに指定できる値は「unit引数に指定できる値」を参照。
date には、開始日を指定する。
expr には、加算(減算)する値を指定する。 |
DATE_ADD('1999-01-01', INTERVAL
1 HOUR) ⇒ '1999-01-01 01:00:00'
DATE_ADD('1998-01-02', INTERVAL 31 DAY)
⇒ '1998-02-02'
DATE_SUB('2006-02-28', INTERVAL 1 MONTH)
⇒ '2006-01-28' |
| DATE_FORMAT(date,format) |
format文字列に合わせて、date値を形式設定する。
format文字列には表の指示子を指定する。 |
DATE_FORMAT(NOW()
,'%Y年%m月%d日 %H時%i分%s秒')
⇒ '2006年11月11日 10時54分13秒' |
DAY(expr)
DAYOFMONTH(expr) |
日付式または日付時刻式 expr の1〜31の日にちを返す。 |
DAY('2006-11-10 20:41:55')
⇒ 10 |
| DAYOFWEEK(expr) |
日付式または日付時刻式 expr の
曜日インデックス(1=日曜、2=月曜、... 7=土曜)を返す。 |
DAYOFWEEK('2006-11-10 20:41:55')
⇒ 6 |
| DAYOFYEAR(expr) |
日付式または日付時刻式 expr の
1 〜 366 の範囲の年間を通した日にちを返す。 |
DAYOFYEAR('2006-11-10 20:41:55')
⇒ 314 |
| EXTRACT(unit FROM date) |
unitで指定した日付の一部を抽出する。
unitに指定できる値は「unit引数に指定できる値」を参照。 |
EXTRACT(YEAR_MONTH FROM NOW())
⇒ 200611 |
| FROM_DAYS(N) |
指定された日数 N に対して DATE 型の値を返す。 |
FROM_DAYS(729669) ⇒ '1997-10-07' |
| HOUR(time) |
time に指定された時刻の時間部分の値を返す。 |
HOUR('10:05:03') ⇒ 10
HOUR('272:59:59') ⇒ 272 |
| LAST_DAY(date) |
指定した日付の月の最後の日にちを返す。 |
LAST_DAY('2003-02-05') ⇒ '2003-02-28'
LAST_DAY('2004-01-01 01:01:01')
⇒ '2004-01-31' |
| MAKEDATE(year,dayofyear) |
指定された年と年間を通した日にちに対応する日付を返す。 |
MAKEDATE(2006,31) ⇒ '2006-01-31'
MAKEDATE(2006,365) ⇒ '2006-12-31'
MAKEDATE(2004,365) ⇒ '2006-12-30' |
| MAKETIME(hour,minute,second) |
引数に指定された値から、計算した時刻値を返す。 |
MAKETIME(12,15,30) ⇒ '12:15:30' |
| MICROSECOND(expr) |
マイクロ秒を、0〜999999 の範囲内の数値として返す。 |
MICROSECOND('12:00:00.123456')
⇒ 123456 |
| MINUTE(time) |
指定された時刻の分を0〜59の範囲の値として返す。 |
MINUTE('98-02-03 10:05:03')
⇒ 5 |
| MONTH(date) |
指定された日付の月を1〜12の範囲の値として返す。 |
MONTH('1998-02-03') ⇒ 2 |
| MONTHNAME(date) |
date に指定された月の名前を返す。 |
MONTHNAME('1998-02-05')
⇒ 'February' |
| PERIOD_ADD(P,N) |
N に指定された月数を期間 P(YYMM または YYYYMM の形式)
に加える。戻り値は YYYYMM の形式で返る。 |
PERIOD_ADD(9801,2) ⇒ 199803 |
| PERIOD_DIFF(P1,P2) |
期間 P1 と P2 の間の月数を返す。
P1 と P2 の指定は YYMM または YYYYMM 形式で行う。 |
PERIOD_DIFF(9802,199703) ⇒ 11 |
| QUARTER(date) |
date に指定された日付に対応する四半期を1〜4の範囲
の値として返す。 |
QUARTER('98-04-01') ⇒ 2 |
| SECOND(time) |
time に指定された時刻の秒を0〜59の範囲の値として
返す。 |
SECOND('10:05:03') ⇒ 3 |
| SEC_TO_TIME(seconds) |
引数に指定された秒数を'HH:MM:SS' または HHMMSS
形式の値として返す。 |
SEC_TO_TIME(2378) ⇒ '00:39:38'
SEC_TO_TIME(2378) + 0 ⇒ 3938 |
| STR_TO_DATE(str,format) |
DATE_FORMAT() 関数と逆の働きをする。
文字列strをformat形式で受け取り、DATETIME型で
返す。
format文字列には表の指示子を指定する。 |
STR_TO_DATE('2006/11/16','%Y/%m/%d')
⇒ 2006-11-16 |
SUBDATE(date,INTERVAL expr type)
SUBDATE(expr,days) |
INTERVAL を指定する形式は、DATE_SUB()のシノニム。
２番目の形式のdays には expr から差し引く日数を指定する。 |
SUBDATE('1998-01-02', INTERVAL 31 DAY)
⇒ '1997-12-02'
SUBDATE('1998-01-02 12:00:00', 31)
⇒ '1997-12-02 12:00:00' |
| SUBTIME(expr,expr2) |
expr から expr2 を差し引いた結果が返される。
expr には日付(時刻)式を指定し、expr2 には時刻式を
指定する。 |
SUBTIME('2006-11-16 20:00','1:1:1')
⇒ '2006-11-16 18:58:59' |
| TIME(expr) |
時刻式または日付時刻式 expr の時刻部分を取り出す。 |
TIME('2003-12-31 01:02:03')
⇒ '01:02:03' |
| TIMEDIFF(expr,expr2) |
開始時刻exprと終了時刻expr2の間の時間が返される。 |
TIMEDIFF('1997-12-31 23:59:59.000001',
'1997-12-30 01:01:01.000002')
⇒ '46:58:57.999999' |
TIMESTAMP(expr)
TIMESTAMP(expr,expr2) |
日付(時刻)式 expr を日付時刻値として返す。
引数が 2 つの場合は、日付式または日付時刻式 expr
に時刻式 expr2 を加えた結果を日付時刻値として返す。 |
TIMESTAMP('2003-12-31')
⇒ '2003-12-31 00:00:00'
TIMESTAMP('2003-12-31 12:00:00','12:00:00')
⇒ '2004-01-01 00:00:00' |
| TIME_FORMAT(time,format) |
日付(時刻)式 expr を日付時刻値として返す。
DATE_FORMAT()関数に似ているが、format に指定できる
文字列は、時分秒を処理する形式指定子に限られる。 |
TIME_FORMAT('100:00:00',
'%H %k %h %I %l')
⇒ '100 100 04 04 4' |
| TIME_TO_SEC(time) |
time 引数に指定された時刻を秒数に変換して返す。 |
TIME_TO_SEC('22:23:00')
⇒ 80580 |
| TO_DAYS(date) |
date に指定された日付を日数(年0からの通し日数)
に変換して返す。 |
TO_DAYS('1997-10-07')
⇒ 729669 |
| WEEK(date [,mode]) |
date に指定された日付に対応する週数を返す。 |
WEEK('1998-12-31',1)
⇒ 53 |
| WEEKDAY(date) |
date に指定された日付の曜日インデックス
（0 = 月曜、1 = 火曜、... 6 = 日曜）を返す。 |
WEEKDAY('1998-02-03 22:23:00')
⇒ 1 |
| WEEKOFYEAR(date) |
指定された日付のカレンダ上の週を、1〜53の
数値として返す。 |
WEEKOFYEAR('1998-02-20')
⇒ 8 |
| YEAR(date) |
date に指定された日付の年を数値として返す。 |
YEAR('98-02-03') ⇒ 1998 |
YEARWEEK(date)
YEARWEEK(date,start) |
指定された日付の年と週を返す。 |
YEARWEEK('1987-01-01') ⇒ 198653 |
unit引数に指定できる値
DATE_ADD、DATE_SUB関数のunit引数とexpr引数がどのように関連するか以下に示す。
| unit値 |
前提となるexprの形式 |
| SECOND |
秒 |
| MINUTE |
分 |
| HOUR |
時 |
| DAY |
日 |
| MONTH |
月 |
| YEAR |
年 |
| MINUTE_SECOND |
'分:秒' |
| HOUR_MINUTE |
'時:分' |
| DAY_HOUR |
'日 時' |
| YEAR_MONTH |
'年-月' |
| HOUR_SECOND |
'時:分:秒' |
| DAY_MINUTE |
'日 時:分' |
| DAY_SECOND |
'日 時:分:秒' |
| DAY_MICROSECOND |
'日.ミリ秒' |
| HOUR_MICROSECOND |
'時.ミリ秒' |
| MINUTE_MICROSECOND |
'分.ミリ秒' |
| SECOND_MICROSECOND |
'秒.ミリ秒' |
| MICROSECOND |
'ミリ秒' |
DATE_FORMAT、STR_TO_DATE関数などのformat文字列には以下を指定する。
| 指示子 |
説明 |
| %M |
月の名前（January..December） |
| %W |
曜日名（Sunday..Saturday） |
| %D |
英語のサフィックス付きの日付（0th、1st、2nd、3rd など） |
| %Y |
4 桁の数値で表した年 |
| %y |
2 桁の数値で表した年 |
| %a |
略式の曜日名（Sun..Sat） |
| %d |
数値で表した日付（00..31） |
| %e |
数値で表した日付（0..31） |
| %m |
数値で表した月（00..12） |
| %c |
数値で表した月（0..12） |
| %b |
略式の月名（Jan..Dec） |
| %j |
年間を通した日にち（001..366） |
| %H |
時（00..23） |
| %k |
時（0..23） |
| %h |
時（01..12） |
| %I |
時（1..12） |
| %i |
数値で表した分（00..59） |
| %r |
12 時間形式の時刻（hh:mm:ss に続けて AM または PM） |
| %T |
24 時間形式の時刻（hh:mm:ss） |
| %S |
秒（00..59） |
| %s |
秒（00..59） |
| %f |
マイクロ秒（000000..999999） |
| %p |
AM または PM |
| %w |
曜日（0=Sunday..6=Saturday） |
| %% |
リテラルの ‘%’ |
インデックスへ戻る
おすすめ書籍
|
