mysql関数とプロセス制御の詳細

関数とは
関数、?ストレージ・プロシージャを定義できる以上、なぜ関数を作らなければならないのでしょうか.実は、関数はsql文で使用され、ストレージ・プロシージャはsql文を実行します.両者を区別する
組み込み関数

数学関数

関数＃カンスウ＃
説明
ROUND(x,y)
戻りパラメータxの四捨五入yビット小数の値
RAND()
0～1のランダム値を返します.RAND()乱数ジェネレータは、パラメータ(シード)を指定して指定した値を生成します.

集約関数

関数＃カンスウ＃
説明
AVG(col)
指定した列の平均値を返します.
COUNT(col)
指定したカラムのNull以外の値の数を返します.
MIN(col)
指定した列の最小値を返します
MAX(col)
指定した列の最大値を返します.
SUM(col)
指定した列のすべての値の和を返します.
GROUP_CONCAT(col)
グループに属するカラム値を組み合わせた結果を返します.

文字列関数

関数＃カンスウ＃
説明
CHAR_LENGTH(str)
戻り値は文字列strの長さであり、長さの単位は文字である.1つのマルチバイト文字を1つの単一文字として計算します.
CONCAT(str1,str2,…)
文字列の結合パラメータがNULLの場合、NULLが返されます.
CONCAT_WS(separator,str1,str2,…)
文字列結合(カスタムコネクタ)CONCAT_WS()は空の文字列を無視しません.(ただしNULLはすべて無視されます).
CONV(N,from_base,to_base)
進数変換例:SELECT CONV(‘a’,16,2);aを16進数から2進数文字列に変換して表す
FORMAT(X,D)
数字Xの書式を'#,##,###,###.#',小数点以下のDビットを四捨五入して保持し、結果を文字列として返します.Dが0の場合、返される結果に小数点は含まれないか、小数点は含まれません.例えば:SELECT FORMAT(12332.1,4);結果は「12332.100」
INSERT(str,pos,len,newstr)
strの指定された位置に文字列posを挿入する:位置を置換する実際の位置len:置換の長さnewstr:新しい文字列、特にposが元の文字列の長さを超えると、元の文字列が返されます.lenが元の文字列の長さを超える場合は、新しい文字列で完全に置き換えられます.
INSTR(str,substr)
文字列strにおけるサブ文字列の最初の出現位置を返します.
LEFT(str,len)
文字列strの最初のlen位置からのサブシーケンス文字を返します.
LOWER(str)
小文字にする
UPPER(str)
大文字に変更
REVERSE(str)
文字列strを返します.順序と文字順序が逆です.
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
lenパラメータを持たないフォーマットは、位置posから始まる文字列strからサブ文字列を返します.lenパラメータを持つフォーマットは、文字列strからlen文字と同じ長さのサブ文字列を返し、位置posから開始します.FROMのフォーマットを標準SQL構文として使用します.posに負の値を使用することもできます.もしそうならば、サブ文字列の位置は、文字列の先頭位置ではなく、文字列の末尾のpos文字から始まる.次のフォーマットの関数でposに負の値を使用できます.

日付時間関数

関数＃カンスウ＃
説明
CURDATE()
またはCURRENT_DATE()は現在の日付を返します
CURTIME()
またはCURRENT_TIME()が現在の時刻を返す
DAYOFWEEK(date)
dateが表す週の数日目(1~7)を返します.
DAYOFMONTH(date)
戻りdateは1ヶ月目の何日目(1~31)
DAYOFYEAR(date)
戻りdateは1年目の何日目(1~366)
DAYNAME(date)
SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt)
指定したfmtフォーマットに従ってUNIXタイムスタンプtsをフォーマットする
HOUR(time)
timeの時間値を返す(0~23)
MINUTE(time)
timeの分値を返します(0~59)
MONTH(date)
dateの月値(1~12)を返します.
MONTHNAME(date)
SELECT MONTHNAME(CURRENT_DATE);
NOW()
現在の日付と時刻を返します.
QUARTER(date)
SELECT QUARTER(CURRENT_DATE);
WEEK(date)
戻り日dateは1年のうち何週目(0~53)
YEAR(date)
日付dateを返した年(1000~9999)
DATE_FORMAT(date,format)
format文字列に基づいてdate値をフォーマットする
重点学習:DATE_FORMAT

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
->                 '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
->                 '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'

暗号化関数

関数＃カンスウ＃
説明
MD5()
計算文字列strのMD 5チェックサム
PASSWORD(str)
文字列strの暗号化バージョンを返します.この暗号化プロセスは不可逆的であり、UNIX暗号化プロセスとは異なるアルゴリズムを使用します.

制御フロー関数

関数＃カンスウ＃
説明
CASE WHEN[test1] THEN [result1]…ELSE [default] END
testNが真の場合はresultNを返し、そうでなければdefaultを返します.
CASE [test] WHEN[val1] THEN [result]…ELSE [default]END
testとvalNが等しい場合はresultNを返し、そうでない場合はdefaultを返します.
IF(test,t,f)
testが本当なら、tを返します.そうでなければfを返す
IFNULL(arg1,arg2)
arg 1が空でない場合はarg 1を返し、そうでない場合はarg 2を返します.
NULLIF(arg1,arg2)
arg 1=arg 2がNULLを返す場合.そうでなければarg 1に戻る
カスタム関数
関数の作成

delimiter //
create function sum2(
    a int,
    b int)
returns int
begin
    declare num int;
    set num = a + b;
    return(num)
end //
delimiter ;

delimiter //
create function fun(i int)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res = 10;
    elseif i = 20 then
        set res = 200;
    elseif i = 30 then
        set res = 300;
    else
        set res = 400;
    end if;
    return res;
end //
delimiter ;

関数の削除

drop function fun_name;

じっこうかんすう

#      
select UPPER('egon') into @res;
select @res;
#       
select fun(11),name from tb2;

に注意
関数の数はsql文を書くことはできません.そうしないと、エラーを報告します.関数はただの機能です.sqlで使用する機能はsqlを書く場合は、ストレージ・プロシージャを使用します.
プロセス制御
じょうけんステートメント

-- if   
delimiter //
create procedure pro_if()
begin
  declare i int default 0;
  if i = 1 then
    select 1;
  elseif i = 2 then
    select 2;
  else
    select 7;
  end if;
end //
delimiter ;

実行結果

mysql> call pro_if();
+---+
| 7 |
+---+
| 7 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

ループステートメント

whileサイクル

delimiter //
create procedure pro_while()
begin
  declare num int;
  set num = 0;
  while num < 3 do
    select
      num;
    set num = num + 1;
  end while ;
end //
delimiter ;

実行結果

mysql> call pro_while();
+------+
| num  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
+------+
| num  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

repeatサイクル

delimiter //
create procedure pro_repeat()
begin
  declare i int;
  set i = 0;
  repeat
    select i;
    set i = i + 1;
    until i >= 5
  end repeat;
end //
delimiter ;

実行結果

mysql> call pro_repeat();
+------+
| i    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
+------+
| i    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

loopサイクル

-- 100      
delimiter //
create procedure sum2(a int)
begin
  declare sum int default 0;
  declare i int default 1;
  loop_name: loop #     
    if i > a then
      leave loop_name; #          leave   break
    end if;
    set sum = sum + i;
    set i = i + 1;
  end loop; #     
  select sum; #     
end //
delimiter ;

実行結果

mysql> call sum2(100);
+------+
| sum  |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)