[クレイジーJava]SQLライブラリ関数:行関数、グループ関数、selectグループ、フィルタグループ

1.SQLライブラリ関数:
1)C言語標準ライブラリ関数と同様に、SQLはよく使われる機能を標準ライブラリ関数にカプセル化してユーザーに使用させる.これらのライブラリ関数の下位層の実現効率は非常に高く、ユーザーが自分で同じ関数を書くよりも効率が高いので、これらの常用機能に出会ったらできるだけライブラリ関数を使う.
2)SQLはプログラミング言語ではなく、オブジェクト向けという言い方もないので、SQLの関数はC言語と同じように、独立した実行ユニットであり、クラスやオブジェクトを呼び出す必要はなく、最高級の実行体が単独で実行する.
3)SQL関数は大きく分けて2種類あります.
i.行関数:
a.そのパラメータは変数、定数であってもよく、変数はselectから出た行の列であってもよい.
b.行関数は単行関数とも呼ばれ、selectから出た単独の行(一度に1行)にしか機能しない(選択した1行の列や列の算術式などをパラメータとして利用できる)が、同時に複数行を統計計算することはできない.
c.行関数は1つの値しか返さず、ネスト呼び出し(1つの行関数の戻り値を別の行関数のパラメータとする)をサポートする.
        ii. グループ関数:
a.selectから出た複数行は1つのグループを構成することができる.
b.グループ関数は、複数行からなるグループを統計することができ、すなわち、複数行に同時に作用する.
c.一般的にグループ関数を使用する場合、クエリー結果をグループ化し、グループ関数を利用して各グループを統計し、グループはある列の値が等しいように分けることができる.
2.一般的な行関数:
1)文字列長の計算:char_length(文字列);
2)sin:sin(数値);
3)現在の日付/時刻を返す:curdate();curtime();
4)文字列のMD 5暗号化:md 5(文字列);//暗号化された文字列を返します
5)nullを判断する3つの関数:よく使われる
         a. ifnull(expr1, expr2);//expr 1 nullはexpr 2を返し、そうでなければexpr 1を返す
         b. nullif(expr1, expr2);//expr 1=expr 2はnullを返し、そうでなければepxr 1を返す
         c. isnull(expr1);//expr 1 nullはtrueを返します.そうしないとfalseを返しますが、演算子is nullのほうが使いやすいです.
6)3つの演算子関数(C言語の?:):if(expr 1,expr 2,expr 3);//栄誉感expr 1をtrueとし、0またはnullでない場合はexpr 2を返し、そうでない場合はexpr 3を返す
!!すなわち、この方法では0とnullがfalseを表す.
7)分岐選択関数——case:MySQLは単独で提供するが、非常に使いやすいので、MySQL管理者はできるだけ把握しなければならない.
a.C言語の使い方——一致して返す:

case      
when    1 then    1
when    2 then    2
...
else          
end

!したがってcase関数は最終的に1つの値を返し、比較する値に基づいて所望の値を返すにすぎない.
b.条件復帰:

case
when     1 then    1
when     2 then    2
...
else          
end

!!caseは、そのゼラチン条件に果たして合致するかによって所望の値を返す.
!例:

select teacher_name,
	case teacher_id
		when 1 then 'Java Teacher'
		when 2 then 'C++ Teacher'
		else 'Other Teacher'
	end
from teacher_table;

select student_name,
	case
		when student_age <= 15 then 'primary'
		when student_age <= 20 then 'upgrade'
		else 'super'
	end
from student_table;

!!caseは短絡特性を有し、すなわち上から次の条件検査を行い、最初に一致したものを検査するとすぐに戻って次の検査を継続しない!!
3.グループ関数:
1)前述のように、複数行のレコード統計値を計算する関数で、複数行が1つのグループを構成し、各グループは1つの値を返します.
2)どのようにグループ化するかは人為的に指定できますが、グループを指定しない場合は、クエリーされたすべてのレコード(行)を1つのグループとして処理します.そのため、計算された結果は常に1つの行しかありません.1つのグループしかないからです.
3)最もよく使われる5大グループ関数(統計関数):distinctは重複を除くexprを表し、allは重さを除去しないことを表し、デフォルトでは重さを除去しないので、重さを除去しない場合はallを書かなくてもよい
a.avg([distinct|all]expr);//グループ内のexprの平均値の計算
b.count(*);//グループ内のすべてのレコードの数を計算する
c.count(distinct expr);//グループ内のすべてのレコードのストライプ数を計算しますが、exprは繰り返し計算されず、exprはnullに等しいものも計算されません!!一般的にはexpr列の合計数(nullを除く)を計算します
d.max(expr);//グループ内最大のexprを返す
e.min(expr);//グループ内の最小のexprを返し、最大最小は重くする必要はなく、重くするかどうか結果は同じで、重くするかどうかはかえって一定の余分なオーバーヘッドがある.
f.sum([distinct|all]expr);//グループ内のexprの和を返すには、重み付けするかどうかを考慮する必要がある
4)avg、sumを計算する際にnull値を0とする必要がある場合がありますが、SQLのデフォルトdistinctとdistinctを付けない場合はnullは無視されます.そのため、ifnull関数である必要がある場合は、次のようにします.

select avg(ifnull(num, 0)) from tableX;

4.selectグループ:
1)selectはクエリー結果のレコードをグループ化し、キーワードgroupbyを使用して属性としてselectの最後に置くことができる.
2)フォーマット:select...from...groupby expr 1[,expr 2[,expr 3...]];
3)クエリされたレコードはexpr 1の値でグループ化され、expr 1の等しいレコードはグループ化され、expr 1の等しいグループでグループ化され続け、expr 2の値でグループ化された後、expr 2の値が等しいグループでグループ化され続け...
4)例:select count(*)from student_table group by teacher,class_date;
!ここでは、まず記録を授業先生別にグループ化し、各授業先生の下で授業日別にグループ化し、count(*)の計算結果が最も細分化されたグループ、すなわちclass_dateグループの人数を示す!
!覚えておいて!グループ化後に計算された統計値は、最も細分化されたグループに作用します!!
ここでもし2人の先生AとB、Aが1日に3人の学生が授业を受けて、2日に4人の学生が授业を受けて、B先生は2日に7人の学生が授业を受けて、3日に10人の学生が授业を受けて、それでは上のselect文は4行出力して、それぞれ3、4、7、10です;
5)標準SQLのグループ統計に対する規定:
i.グループ化の目的は統計のためです!!
ii.したがってgroupbyがグループ化されると、select後にグループ関数(統計量の選択)が表示されなければなりません.そうしないと、エラーが表示されます.
iii.グループ化の目的は1つのグループを1つのレコードとして扱うため、1つのレコードは1つの結果をクエリーしますが、selectの後ろに普通の列(1つのフィールドを選択する)がある場合、あなたは1つのグループの中のどのレコードでこのグループを代表しますか??何の根拠もありませんので、矛盾して、直接クエリーを拒否します;
6)グループ統計の説明フィールド:
i.上記のクエリ文「select count(*)from student_table group by teacher,class_date;「4つの結果が得られるが、4つの値にすぎず、どの値がどのグループを表しているのか分からず、読みやすさが悪い.
ii.これらの値がそれぞれどのグループを表すかを示すために、selectの後にグループの根拠となる列を加えることができる.例えば、select count(*)、teacher、class_date from student_table group by teacher、class_date;
iii.これは5)の規定と矛盾しない.selectに1つのグループ関数が現れさえすれば、1つのグループ関数(統計値)が1つのグループを代表することができるからだ.
iv.実は定数文字列で説明するのが一番いいです:select count(*)as“count”,concat(teacher,class_date)as“group”from student_table group by teacher,class_date;
5.フィルタ・グループ:
1)where文は行をフィルタするために使用され、標準SQLはフィルタ行と区別するためにキーワードhavingでグループをフィルタする.
2)グループ統計後、グループ全体が1行として扱われる(グループ全体を1つのレコードとして扱うことに相当)ため、行をフィルタリングする際には必ずグループ全体を表す値を条件判断としなければならないため、havingの条件判断にはグループ関数数(統計値)が含まれなければならない.
3)例えば、select count(*)from student_table group by student_name having count(*)>10;//人数が10を超えるグループを選別する
4)whereとhavingを混用しないで、1つのフィルタ行、1つのフィルタグループ、間違いで直接間違いを報告します!