テーブル変更操作
カラム追加(先頭、後ろ)
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
/* 方法1 おすすめ */
INSERT INTO personal_info(
pno
, name
, mail
, create_datetime
, telephone
)
SELECT
(MAX(pno)+1)
, :name
, :mail
, :create_datetime
, :telephone
FROM personal_info
/* 方法2
「パラメータについて推定された型が不整合です」という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|
________________________________________________________________________________
**/