【SQL】SQLメモ #MySQL

テーブル変更操作

カラム追加（先頭、後ろ）

SQL


/* テーブル新規作成 */
DROP TABLE IF EXISTS personal; 
CREATE TABLE personal( 
  mail CHARACTER VARYING (250) NOT NULL
  , create_datetime TIMESTAMP WITH TIME ZONE
  , PRIMARY KEY (mail)
); 

/* 先頭のカラムとして追加 */
ALTER TABLE personal ADD pno SMALLINT FIRST; 

/* カラムの後ろに追加 */
ALTER TABLE personal ADD name AFTER pno; 

/* カラムを追加 */
ALTER TABLE personal ADD tel VARCHAR (12);

データタイプ変更

SQL


/* データタイプを変更 */
ALTER TABLE personal ALTER tel type VARCHAR (13);

列名変更

SQL


/* 列名を変更 */
ALTER TABLE personal RENAME COLUMN tel TO telephone;

テーブル名変更

SQL


/* テーブル名を変更 */
ALTER TABLE personal RENAME TO personal_info;

テーブル操作

キーより最大値を求めて新規レコードを挿入

SQL

/* 方法１ おすすめ */
INSERT INTO personal_info(
  pno
  , name
  , mail 
  , create_datetime
  , telephone
)
SELECT 
  (MAX(pno)+1)
  , :name
  , :mail 
  , :create_datetime
  , :telephone
FROM personal_info

/* 方法２ 
「パラメータについて推定された型が不整合です」というSQLエラーが出る場合があります。
原因は調査中。一応、おすすめられません。*/
INSERT INTO personal_info( 
  pno
  , name
  , mail 
  , create_datetime
  , telephone
) 
VALUES ( 
  (SELECT (MAX(pno)+1) FROM personal_info WHERE mail = :mail)
  , :name
  , :mail 
  , :create_datetime
  , :telephone
)

テーブル検索操作

キーより複数項目を集約

postgreSQL


/* キー(pno)より複数項目を集約 */
/* Table - mails
__________________________________
pno |seq |mail_address
1   |1   |sample1_1@sampleSQL.co.jp
1   |2   |sample1_2@sampleSQL.co.jp
2   |1   |sample2@sampleSQL.co.jp
__________________________________
*/
SELECT
  pno
  , string_agg(mail_address, ',') AS other_mails 
FROM
  mails 
ORDER BY
  pno

/* result
_________________________________________________________
pno|mail_address
1  |sample1_1@sampleSQL.co.jp,sample1_2@sampleSQL.co.jp
2  |sample2@sampleSQL.co.jp
_________________________________________________________
*/

特定時間間隔の計算

postgreSQL


/* 特定時間間隔の計算 （時間間隔パラメータ[:time] 例え:2h）*/
/** Table - mails
____________________________________________________________
pno |seq |mail_address               |create_time
1   |1   |sample1_1@sampleSQL.co.jp  |2018/04/10 9:10:41.565
1   |2   |sample1_2@sampleSQL.co.jp  |2018/04/10 10:10:41.565
2   |1   |sample2@sampleSQL.co.jp    |2018/04/10 11:10:41.565
____________________________________________________________
**/
SELECT
  pno
  , mail_address
  , create_time 
  , now() - (:time)::interval  AS limit_time
FROM
  mails 
WHERE
  create_time <= now() - (:time)::interval 

/**
result
    now() ='2018/04/10 12:30:41.565'
    :time ='120 minutes'
________________________________________________________________________________
pno|mail_address              |create_time             |limit_time
1  |sample1_1@sampleSQL.co.jp |2018/04/10 9:10:41.565  |2018/04/10 10:30:41.565
1  |sample1_2@sampleSQL.co.jp |2018/04/10 10:10:41.565 |2018/04/10 10:30:41.565
________________________________________________________________________________
**/

IS NULL／IS NOT NULL／空('')／空ではない(!='')

SQL ServerでよくNULLと空のチェックがあり、間違いやすいため、これらの「IS NULL／IS NOT NULL／空('')／空ではない(!='')」チェックの筋を通していきます。
例として一つずつ紹介します。

SQLServer


/* IS NULL ／ IS NOT NULL：
カラムのNULLチェックが、値が空（''）の時は、チェック不可 */
/** Table - persons
____________________________________________________________
pno  |kanji_sei|kanji_mei|nick_name
90007|NULL     |         |静香ちゃん
____________________________________________________________
**/
SELECT 
    pno,
    kanji_sei,
    kanji_mei,
    nick_name,
    CASE WHEN kanji_sei IS NULL THEN NULL
         WHEN kanji_sei IS NOT NULL THEN 1
         ELSE 2 END AS kanji_sei_new,
    CASE WHEN kanji_mei IS NULL THEN NULL
         WHEN kanji_mei IS NOT NULL THEN 1
         ELSE 2 END AS kanji_mei_new
FROM persons WHERE pno= '90007';

/**  Result
_________________________________________________________________
pno  |kanji_sei|kanji_mei|nick_name|kanji_sei_new|kanji_mei_new
90007|NULL     |         |静香ちゃん|NULL         |1
_________________________________________________________________
**/


/* 空（=''） ／ 空ではない（!=''）：
DATALENGTH(カラム)で値の長さにより空チェックができる。値がNULLの時は、チェック不可 */
/** Table - persons
____________________________________________________________
pno  |kanji_sei|kanji_mei|nick_name
90007|NULL     |         |静香ちゃん
____________________________________________________________
**/
SELECT 
    pno,
    kanji_sei,
    kanji_mei,
    nick_name,
    CASE WHEN DATALENGTH(kanji_sei)=0 THEN 0
         WHEN DATALENGTH(kanji_sei)>0 THEN 1
         ELSE 2 END AS kanji_sei_new,
    CASE WHEN DATALENGTH(kanji_mei)=0 THEN 0
         WHEN DATALENGTH(kanji_mei)>0 THEN 1
         ELSE 2 END AS kanji_mei_new
FROM persons WHERE pno= '90007';

/**  Result
_________________________________________________________________
pno  |kanji_sei|kanji_mei|nick_name|kanji_sei_new|kanji_mei_new
90007|NULL     |         |静香ちゃん|2            |0
_________________________________________________________________
**/


/* 【練習】IS NULL／IS NOT NULL／空（''）／空（''）ではない ：
*    以下のテーブルから氏名を取得してください。
*    　姓が登録無しの時、且つ名が登録無しの時、氏名は「ニックネーム」を設定
*    　姓が登録有り時、氏名は「姓」を設定
*    　名が登録有り時、氏名は「名」を設定
*    　姓が登録有り時、且つ名が登録有り時、氏名は「姓 名」を設定
*
* Table - persons
____________________________________________________________
pno  |kanji_sei|kanji_mei|nick_name
90007|NULL     |         |静香ちゃん
____________________________________________________________
**/
SELECT 
    pno,
    kanji_sei,
    kanji_mei,
    nick_name,
    CASE
        WHEN (kanji_sei IS NOT NULL AND DATALENGTH(kanji_sei)>0) AND (kanji_mei IS NOT NULL AND DATALENGTH(kanji_mei)>0) 
            THEN CONCAT(kanji_sei, ' ', kanji_mei)
        WHEN (kanji_sei IS NOT NULL AND DATALENGTH(kanji_sei)>0) AND (kanji_mei IS NULL OR DATALENGTH(kanji_mei)=0) 
            THEN kanji_sei
        WHEN (kanji_sei IS NULL OR DATALENGTH(kanji_sei)=0)      AND (kanji_mei IS NOT NULL AND DATALENGTH(kanji_mei)>0) 
            THEN kanji_mei
        WHEN (nick_name IS NOT NULL AND DATALENGTH(nick_name)>0) 
            THEN nick_name
        ELSE NULL
    END AS full_name
FROM persons WHERE pno= '90007';

/**  Result
_________________________________________________________________
pno  |kanji_sei|kanji_mei|nick_name|full_name|
90007|NULL     |         |静香ちゃん|静香ちゃん|
_________________________________________________________________
**/

誕生日（生年月日）から年齢、年代取得

SQLServer


/*  誕生日（生年月日）から年齢、年代取得 ：
*    CONVERT(VARCHAR, SYSDATETIME(), 112)--------------現在日付(YYYYMMDD)の文字列
*    CONVERT(VARCHAR, birth_date, 112)-----------------誕生日(YYYYMMDD)の文字列
*    RIGHT(CONVERT(VARCHAR, SYSDATETIME(), 112), 4)----現在日付(MMDD)の文字列
*    RIGHT(CONVERT(VARCHAR, birth_date, 112), 4)-------誕生日(MMDD)の文字列
*
* Table - persons
____________________________________________________________
pno  |birth_date                 |
90007|1970-07-22 00:00:00.0000000|
____________________________________________________________
**/

SELECT 
    pno,
    birth_date,
    CONVERT(VARCHAR, SYSDATETIME(), 112) AS today,
    CONVERT(VARCHAR, birth_date, 112) AS birthday,
    RIGHT(CONVERT(VARCHAR, SYSDATETIME(), 112), 4) AS today_day,
    RIGHT(CONVERT(VARCHAR, birth_date, 112), 4) AS birthday_day,
    [person_age].age AS age, 
        [person_age_period].age_period AS age_period 
FROM persons
OUTER APPLY(
        SELECT
            YEAR(SYSDATETIME()) - YEAR(birth_date) - CASE WHEN RIGHT(CONVERT(VARCHAR, SYSDATETIME(), 112), 4) < RIGHT(CONVERT(VARCHAR, birth_date, 112), 4) THEN 1 ELSE 0 END AS age
    ) AS [person_age] 
OUTER APPLY(
    SELECT
        CASE
            WHEN  '0' <= [person_age].age AND '19' >= [person_age].age THEN '10'
            WHEN '20' <= [person_age].age AND '29' >= [person_age].age THEN '20'
            WHEN '30' <= [person_age].age AND '39' >= [person_age].age THEN '30'
            WHEN '40' <= [person_age].age AND '49' >= [person_age].age THEN '40'
            WHEN '50' <= [person_age].age AND '59' >= [person_age].age THEN '50'
            WHEN '60' <= [person_age].age THEN '60'
            ELSE null
        END AS age_period
) AS [person_age_period]
WHERE pno = '90007';

/**  Result
________________________________________________________________________________
pno  |birth_date                 |today   |birthday|today_day|birthday_day|age|age_period|
90007|1970-07-22 00:00:00.0000000|20181221|19700722|1221     |0722        |48 |40|
________________________________________________________________________________
**/