MySql共通関数およびカスタム関数の詳細

-- ----------------------
--######内蔵関数の使用###
-- ----------------------
--AVG()関数は、数値列の平均値を返します.

--  count     
SELECT AVG(count) FROM access_log;

--クエリ・アクセス数が平均アクセス数より多いsite_idおよびcount:

SELECT site_id, count FROM access_log WHERE count > (SELECT AVG(count) FROM access_log);

--COUNT()関数は、指定した条件に一致する行数を返します.
--指定した列の値の数を返します.

SELECT COUNT(site_id) FROM access_log;

--テーブルのレコード数を返します.

SELECT COUNT(*) FROM access_log;

--指定されたカラムの異なる値の数を返します.

SELECT COUNT(DISTINCT count) FROM access_log;

--FIRST()関数は、指定されたカラムの最初のレコードの値を返します.MS AccessのみがFIRST()関数をサポートします.

SELECT count FROM access_log ORDER BY count ASC --    
LIMIT 1; --     

--LAST()関数は、指定したカラムの最後のレコードの値を返します.LAST()関数をサポートするのはMS Accessのみです.

SELECT count FROM access_log ORDER BY count DESC LIMIT 1; --     

--MAX()関数は、指定したカラムの最大値を返します.

SELECT MAX(count) FROM access_log;

--MIN()関数は、指定したカラムの最小値を返します.

SELECT MIN(count) FROM access_log;

--SUM()関数は、数値列の合計数を返します.

SELECT SUM(count) FROM access_log;

--UCASE()関数は、フィールドの値を大文字に変換します.
--Websites表から「name」列と「url」列を選択し、「name」列の値を大文字に変換

SELECT UCASE(name) AS site_title, url FROM Websites;

--LCASE()関数は、フィールドの値を小文字に変換します.
--「Websites」表から「name」と「url」列を選択し、「name」列の値を小文字に変換

SELECT LCASE(name) AS site_title, url FROM Websites;

--テキストフィールドから文字を抽出するMID()関数.

--   ：SELECT MID(column_name,start[,length]) FROM table_name;
SELECT MID(name,1,5) AS ShortTitle FROM Websites;

--LEN()関数は、テキストフィールドの値の長さを返します.
--「Websites」表から「name」および「url」列の値の長さを選択します.

SELECT name, LENGTH(url) as LengthOfURL FROM Websites;

--ROUND()関数は、数値フィールドを指定した小数点以下の桁数に丸めます.

SELECT ROUND(alexa,2) FROM Websites;

--NOW()関数は、現在のシステムの日付と時刻を返します.
--「Websites」表からname、url、およびその日の日付を選択します.

SELECT `name`, url, Now() AS date FROM Websites;

--FORMAT()関数は、フィールドの表示をフォーマットするために使用されます.
--Websites表から名前、url、およびYYYY-MM-DDとしてフォーマットされた日付を選択します.

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date FROM Websites;

--GROUP BY文は、集約関数と組み合わせて、結果セットを1つ以上の列に基づいてグループ化するために使用されます.
--マルチテーブルクエリ+関数

--    access_log    site_id     ：
SELECT site_id, SUM(access_log.count) AS nums FROM access_log GROUP BY site_id;

--              ：
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

--HAVING句では、グループ化された各グループのデータをフィルタできます.

--    HAVING      ，WHERE               。

--合計アクセス数が200を超えるWebサイトを検索します.

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM 
	(access_log INNER JOIN Websites ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

--合計アクセス数が200を超えるWebサイトを検索し、alexaランキングが200を下回っています.

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

-- -----------------------------
--関数をカスタマイズする際には、次の点に注意してください.
--関数には戻り値が必要です.
--関数の内部にselect*from tb 1というsql文を書くことはできません.
-- -----------------------------

--            
CREATE FUNCTION jia(i INT , j INT) RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 0;
	SET num=i+j;
	RETURN(num);
END

--カスタム関数の実行

SELECT jia(1,2) AS num;

--関数の削除

DROP FUNCTION jia;