SQLの各種関数

####################
#        
####################
CREATE TABLE SampleMath 
(m  NUMERIC (10,3), n  INTEGER, p  INTEGER);

INSERT INTO SampleMath(m, n, p) VALUES (500,  0,    NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0,    NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7,    3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5,    2); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4,    NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (8,    NULL, 3); 
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1,    NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2,    NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1,    NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);

select * from SampleMath;

-- 1.       
/*
1.abs_col:ABS(m)    （   ）
2.mod_col:MOD(n,p)    （n÷p   ）
3.round_col：ROUND(m,n)    （       

*/


CREATE TABLE SampleStr 
(str1  VARCHAR(40), str2  VARCHAR(40), str3  VARCHAR(40));
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,'rt',NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('  ' ,'  ' ,'  ');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' , NULL ,NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,'xyz',NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,NULL ,NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,NULL ,NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc  ' ,'abc' ,'ABC'); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' , 'abc' ,'ABC'); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,'i' ,'I');

-- 2.         
#1.  concat()
SELECT str1, str2, str3, CONCAT(str1, str2, str3) 
AS str_concat  FROM SampleStr; 
#2.length()
#3.lower() upper()
#4.REPLACE(     ，       ，       )
   insert(     ，    ，    ，        ）
#5.substring（      FROM         FOR       ）


-- 3.       
#1.select current_date        
#2.select current_time        
#3.select current_timestamp;          
#4.extract(     from   ）
SELECT CURRENT_TIMESTAMP, 
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, 
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, 
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, 
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, 
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, 
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

-- 4.      
--     &     
--      
#1.CAST（      AS          ）
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
SELECT CAST('2009-12-14' AS DATE) AS date_col;

--      
#1.  null      
-- COALESCE(  1，   2，   3……)
--            A        1    NULL  
SELECT COALESCE(NULL, 1) AS col_1,      
COALESCE(NULL, 'test', NULL) AS col_2,       
COALESCE(NULL, NULL, '2009-11-01') AS col_3;


SELECT COALESCE(str2, 'NULL')  FROM SampleStr;--  null   'null'
select str2 from samplestr;