はじめに
SQL Serverで開発やデータ抽出をやっていますが、いつも使うクエリの定型文が思い出せず毎回調べているのでこのタイミングでまとめて見ようかなと(アドベントカレンダーに登録したけど書く内容に行き詰まったから)
作成・更新・削除系
テーブル作成
create table テーブル名A(
カラム1 INT IDENTITY(1,1) NOT NULL, --自動採番(始まりの値,インクリメントする値)
カラム2 INT DEFAULT 5, --デフォルトを指定したいとき
カラム3 INT NULL, --NULL許容
カラム4 INT NOT NULL, --NOT NULL制約
カラム5 DATETIME
)
テーブル作成でよく使う設定を記述しました。
デフォルトを指定したいときはそんなに使わないけど、忘れた頃にやってくる。。。
制約削除
ALTER TABLE テーブル名A DROP CONSTRAINT 制約名B
カラム削除しようとしたら、制約に依存しているから~というエラーが出る。制約付きカラムの削除のときによく使った。(上記のテーブルでいうと「カラム2」がそれに価する)
カラム削除
ALTER TABLE テーブル名A DROP COLUMN カラム2
自動採番設定済みカラムに対して任意の値をインサートする
set identity_insert テーブル名A on;
insert into テーブル名A(カラム1,カラム2,カラム3,カラム4) values (2,1,1,00000000000)
set identity_insert テーブル名A off;
identity_insert
を上記のように指定してあげればインサートできます。
このときに気をつけなければならないのが、インサート文で全カラムを明示しないといけません。
(いつもそのことを忘れてしまう)
その他系
テーブルを全取得
SELECT
*
FROM
sys.objects
WHERE
type = 'U';
シノニムを全取得
SELECT
NAME
,BASE_OBJECT_NAME
FROM
SYS.SYNONYMS
ORDER BY
BASE_OBJECT_NAME
テーブルの情報を取得
SELECT
TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
,COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME='テーブル名A'
アンチパターン
NULLの扱い
NULLを許可しているカラムではスカラー式が任意の値にならないことに注意する。
SQLは3値論理であることを忘れない。
最近、これを忘れていて痛い目にあった。。。