【MySQL必知必会】学習ノートDay 5

【MySQL必知必会】学習ノートDay 5&1.25&D 11-12章&P 68-82ページ
9、データ処理関数の使用
(1)文書処理関数

eg:Upper()テキストを大文字

に変換

SELECT  vend_name,Upper(vend_name)  AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

一般的なテキスト処理関数:

              
Left()               
Right()              
LTrim()               
RTrim()               
Lower()              
Upper()             
Length()            
Locate()              
Soundex()        SOUNDEX  
SubString()          

注意:SOUNDEXは、任意のテキスト列を音声表示を記述するアルファベット数値モードに変換するアルゴリズムです.SOUNDEXは似たような発音文字と音節を考慮し,文字比較ではなく列を発音比較できるようにした.

eg:customersテーブルには、Y.Leeという名前の顧客Coyote Inc.があります.しかし、これが入力エラーの場合、この連絡名は実際にはY.Lieであるべきです.正しい連絡名で検索すると、次のようにデータは返されません.

SELECT cust_name,cust_contact
FROM customers
WHERE cust_contact = 'Y.Lie';

Soundex()関数を使用して検索します.これは、Y.LeeとY.Lieの発音が似ているため、すべての発音がY.Lieに似ている連絡名に一致します.SOUNDEX値は

と一致します.

SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact )= Soundex('Y.Lie');

(2)日付と時刻処理関数

でよく使用される日付および時間処理関数

                     
AddDate()                 ( 、  )
AddTime()                 ( 、  )
CurDate()                 
CurTime()                 
DateDiff()                  
Date_Add()                     
Date_Format()                     
DayOfWeek()               ，        
Now()                        
Date()                         
Time()                           
Second()                      
Minute()                       
Hour()                         
Day()                          
Month()                        
Year()                         

注意:MySQLで使用する日付フォーマットは、yyyy-mm-ddの形式でなければなりません.

eg:受注レコードを取得し、order_dateは2005-09-01:

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date)= '2020-01-25';

注意:order_が格納されているためdate値は2020-01-25 11:30:05の場合があるので、日付部分のみを取るのが信頼できる方法です

eg:2005年9月のすべての受注

を取得

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2020-09-01' AND '2020-09-30';

または、

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) =9;

(3)数値処理関数

常用数値処理関数

         
Abs()             
Cos()             
Exp()             
Mod()            
Pi()         
Rand()          
Sin()             
Sqrt()            
Tan()            

10、要約データ
(1)集計関数

集計関数:行グループ上で実行され、単一の値を計算および返す関数

SQL集計関数:

            
AVG()               
COUNT()            
MAX()               
MIN()               
SUM()             

(2)AVG()関数

eg:AVG()を使用してproductsテーブルのすべての製品の平均価格

を返します.

SELECT  AVG（prod_price）AS  avg_price
FROM  products;

eg:AVG()で特定の仕入先が提供する製品の平均価格

を返す.

SELECT  AVG（prod_price）AS  avg_price
FROM  products
WHERE vend_id= 1003;

注意:

AVG()は、特定の数値列の平均値を決定するためにのみ使用でき、列名は関数パラメータとして与えられる必要があります.

複数の列の平均値を得るためには、複数のAVG()関数

を用いる必要がある.

AVG()関数カラム値NULLを無視する行

(3)COUNT()関数

COUNT()関数には2つの使い方があります

COUNT(*)を使用して、テーブル列にNULL(NULL)が含まれているか非NULL(

)が含まれているかにかかわらず、テーブル内のローの数をカウントします.

COUNT(column)を使用して、NULL値

を無視して、特定のカラムの値を持つローをカウントします.

eg:customersテーブルの顧客の合計

を返します.

SELECT COUNT(*) AS num_cust
FROM customers;

eg:Eメールアドレスを持つ顧客のみをカウントする

SELECT COUNT(cust_email) AS num_cust
FROM customers;

注意:

列名を指定すると、指定した列の値が空の行はCOUNT()関数によって無視されます.

ただし、COUNT()関数でアスタリスク(*)が使用されている場合は、

は無視されません.
(4)MAX()関数

eg:productsテーブルで最も高いアイテムの価格

を返します.

SELECT MAX(prod_price) AS max_price
FROM products;

注意:

非数値データに対してMAX()を使用する:MAX()は一般的に最大の数値または日付値を見つけるために使用されますが、MySQLは、テキスト列の最大値を返すなど、任意の列の最大値を返すために使用できます.テキストデータの場合、対応する列でデータがソートされると、MAX()は最後の行を返します.

NULL値:MAX()関数NULLの列値を無視する行

(5)MIN()関数

eg:productsテーブルで最も安いものの価格

を返します.

SELECT MIN(prod_price) AS min_price
FROM products;

非数値データのMAX():MIN()関数はMAX()関数と同様に使用されます.MySQLでは、テキスト列の最小値を返すなど、任意の列の最小値を返すために使用できます.テキストデータに使用する場合、データが対応する列でソートされると、MIN()は先頭の行を返します.

NULL値:MIN()関数NULLの列値を無視する行

(6)SUM()関数

eg:検索注文品の総数:

SELECT SUM(quantity)  AS  items_ordered
FROM  orderitems
WHERE  order_num = 20005;

eg:受注内のすべての品目の価格の合計を返します:

SELECT SUM(item_price*quantity)  AS  total_price
FROM  orderitems
WHERE  order_num = 20005;

注意:

標準演算オペレータを使用して、すべての集計関数を使用して、複数の列の計算

を実行できます.

SUM()関数NULLの列値を無視する行

(7)異なる値を集約

以上の5つの集計関数は、次のように使用できます.

すべての行に対して計算を実行し、ALLパラメータを指定するか、パラメータを与えない(ALLがデフォルトの動作であるため)

を指定する.

は異なる値のみを含み、DISTINCTパラメータ

を指定する

DISTINCTを指定しない場合はALL

とする.

eg:AVG()関数を使用して、特定のベンダーが提供する製品の平均価格を返します.上記のSELECT文と同じですが、DISTINCTパラメータが使用されているため、平均値はそれぞれ異なる価格のみを考慮します:

SELECT  AVG（DISTINCT prod_price）AS  avg_price
FROM  products
WHERE vend_id= 1003;

注意:

カラム名を指定した場合、DISTINCTはCOUNT()にのみ使用できます.DISTINCTはCOUNT(*)には使用できませんので、COUNT(DISTINCT)は使用できません.そうしないとエラーが発生します.同様に、DISTINCTではカラム名を使用する必要があります.計算や式では使用できません.

DISTINCTをMIN()およびMAX()に使用することは可能であるが、実際の価値はない

.
(8)集合関数の組合せ

eg:productsテーブルのアイテムの数、製品価格の最高、最低、および平均値

を返します.

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM  products;
     

【MySQL必知必会】シリーズノート:【MySQL必知必会1-4章】学習ノートDay 1【MySQL必知必会5-7章】学習ノートDay 2【MySQL必知必会8-9章】学習ノートDay 3【MySQL必知必会10章】学習ノートDay 4