【DB/SQL Tips】SQLチートシート・Tips集🌟

未来日のイベントだけを取得する

• from が開始日時
• to が終了日時
• CURRENT_DATE()で現在日時を取得する

-- 未来日のイベントだけを取得する SQL
SELECT *
FROM events
WHERE `from` > CURRENT_DATE()
ORDER BY `from` ASC;

• JSで扱うときも同様に、バッククォートで囲む

// fromは予約語なのでバッククォートで囲む
const query = `
  SELECT * 
  FROM events 
  WHERE ${`from`} > CURRENT_DATE() 
  ORDER BY ${`from`} ASC;
`;

重複行のまとめ方 GROUP BY, DISTINCT, EXISTS

https://qiita.com/yamadayamada_jp/items/ef7ac4301f9c20491bae

【SQL】ON DELETE CASCADEについて

• onDelete: "CASCADE" は、データベースにおいて外部キー制約の一部として使用されるオプションで、親テーブルの行が削除された際に、関連する子テーブルの行も自動的に削除することを指定します。
• このオプションを使用することで、手動で子テーブルの行を削除する手間を省き、データベースの一貫性と整合性を簡単に維持することができます

具体的には、以下のように動作します：

1. 親テーブルの行削除: 親テーブルの特定の行を削除する際、その行に関連付けられている子テーブルの行も自動的に削除されます。
2. 子テーブルの連動削除: 子テーブルに外部キー制約を設定し、その外部キー制約に onDelete: "CASCADE" を適用すると、親テーブルの行削除時に子テーブルの関連行も削除されるようになります。

子テーブルに設定するオプションである

onDelete: "CASCADE" オプションは、親テーブルと子テーブルの関係を定義する際、子テーブルに設定するものです。
このオプションにより、親テーブルの行が削除された場合に、子テーブルの関連する行も自動的に削除されます。

設定方法のポイント：

1. 子テーブルの外部キー制約: onDelete: "CASCADE" は子テーブルの外部キー制約の一部として設定します。
2. 親テーブルの指定は不要: 親テーブル自体にこのオプションを設定するのではなく、親テーブルの行が削除されたときに子テーブルの行も削除するという動作を子テーブルの外部キーに指定します。

https://qiita.com/tasty_uni/items/abdd691bbc8efd3362c7

LIKE検索 (前方一致, 後方一致, 部分一致)

• 前方一致（hoge%）
• 後方一致（%hoge）
• 部分一致（%hoge%）

select timezone, name_ja
from events
where name_ja like "%ぴゅぴゅまる%";

https://qiita.com/ika_katsuo/items/40e7784284344c7d8697

SQLで日時をフォーマットする方法📝

SQL で日時を「yyyy/mm/dd hh:mm」の形式にフォーマットする方法は、使っているデータベースによって違います。代表的なものを以下にまとめました。

MySQL

SELECT
  DATE_FORMAT(your_datetime_column, '%Y/%m/%d %H:%i') AS formatted_datetime
FROM your_table;

• %Y：4 桁の西暦
• %m：2 桁の月（01–12）
• %d：2 桁の日（01–31）
• %H：2 桁の時（00–23）
• %i：2 桁の分（00–59）

PostgreSQL

SELECT
  to_char(your_timestamp_column, 'YYYY/MM/DD HH24:MI') AS formatted_datetime
FROM your_table;

• YYYY：4 桁の西暦
• MM：2 桁の月
• DD：2 桁の日
• HH24：24 時間表記の時（00–23）
• MI：分

SQL Server

FORMAT 関数を使う方法（SQL Server 2012 以降）

SELECT
  FORMAT(your_datetime_column, 'yyyy/MM/dd HH:mm') AS formatted_datetime
FROM your_table;

CONVERT 関数を使う方法

SELECT
  CONVERT(varchar(16), your_datetime_column, 120) AS formatted_datetime
FROM your_table;

• スタイル 120：yyyy-mm-dd hh:mi:ss（24 時間表記）
• varchar(16) として切り詰めることで秒以下を削除しています。

Oracle

SELECT
  TO_CHAR(your_date_column, 'YYYY/MM/DD HH24:MI') AS formatted_datetime
FROM your_table;

SQLite

SELECT
  strftime('%Y/%m/%d %H:%M', your_datetime_column) AS formatted_datetime
FROM your_table;

ポイントまとめ

• 24時間表記 にしたい場合は、SQL Server では HH、PostgreSQL/Oracle では HH24 を使う
• 分 のフォーマット指定子は、MySQL なら %i、PostgreSQL/Oracle なら MI、SQLite なら %M
• 型変換 が絡む場合（文字列として扱いたいなど）は、CAST や CONVERT、FORMAT、TO_CHAR を組み合わせてください。