[SQLServer] チートシート

2023-08-252024-04-16

私の作業用として、よく使うSQLやTIPS、トラブルシューティング的なものをまとめました。

SQL

データベース操作

データベース一覧

select * from sys.databases;

データベース作成

CREATE DATABASE database_name;

ユーザー操作

ユーザ作成（権限設定）

CREATE login user_name WITH password = 'usernamepass';
CREATE USER user_name FOR login user_name ;
EXEC sp_addrolemember 'db_owner', user_name ;

SQLServerではログインとユーザが別になっています
- ログインを作り、そのログインにユーザを紐づけて利用する
ロールの種類はここにあります
- db_owner
- db_securityadmin
- db_accessadmin
- db_backupoperator
- db_ddladmin
- db_datawriter
- db_datareader
- db_denydatawriter
- db_denydatareader

テーブル操作

テーブル一覧確認

SELECT * FROM sys.objects;

ユーザーテーブルの一覧確認

SELECT * FROM sys.objects WHERE TYPE = 'U' ORDER BY name;

テーブルの行数確認

SELECT
  o.name
  , si.rows
FROM
  sys.objects AS o
  LEFT OUTER JOIN sys.sysindexes AS si
    ON o.object_id = si.id
    AND si.indid < 2
WHERE
  o.type = 'U'
ORDER BY
  o.name

テーブル名変更

EXEC sp_rename 'table_name','new_table_name','OBJECT'

select結果でテーブル作成

SELECT * INTO dbo.T_Work_Tmp FROM T_Work;

インデックス操作

そのうち書きます

カラム操作

カラム一覧の確認

SELECT
  t.object_id, t.name AS TABLE_NAME, t.type_desc
  , c.column_id, c.name AS column_name
  , tp.name, c.max_length, c.precision, c.scale, c.collation_name, c.is_nullable
FROM
  sys.objects t
  INNER JOIN sys.columns c
    ON c.object_id = t.object_id
  LEFT OUTER JOIN sys.types tp
    ON tp.system_type_id = c.system_type_id
    AND tp.name <> 'sysname'
WHERE
  t.type = 'U'
  AND t.name = 'table_name'
ORDER BY
  t.name
  , c.column_id
;

カラム追加

ALTER TABLE table_name ADD
    column1  VARCHAR (10) DEFAULT '' NOT NULL
  , column2  VARCHAR (10) DEFAULT '' NOT NULL

カラム名変更

EXEC sp_rename 'table_name.column_name','new_column_name','COLUMN'

カラム変更

ALTER TABLE table_name ALTER COLUMN column1 INT

カラム削除

ALTER TABLE table_name DROP COLUMN column1, column2

ストアドプロシージャ操作

そのうち書きます

SELECT

件数制限

SELECT top 100 * FROM table_name;

サブクエリ中のSELECTをソートしたい

SELECT
  'xxx' AS column_1
  , 'yyy' AS column_2
UNION ALL
SELECT
  *
FROM
  (
    SELECT
      top 999999999999999999
      column_1
      , column_2
    FROM
      table_name
    ORDER BY
      column_1
  )a

サブクエリのSELECTにTOPをつけます
- 百京だからほとんどいけるでしょう

INSERT

INSERT INTO table_name (
  column_3
  , column_4
)
SELECT
  column_1
  , column_2
FROM
  table_name_1
;

UPDATE

そのうち書きます

DELETE

DELETE
FROM
  table_name
WHERE
  KEY = 1

コマンド

bcp

フォーマットファイル作成

bcp.exe table_name format nul -f table_name.fmt -c -S server_name -U sa -P password -d database_name

エクスポート

-- UTF8ファイルのエクスポート
bcp.exe table_name out table_name.txt -c -C 65001 -S server_name -U sa -P password -d database_name

インポート

取込先のテーブルは以下を想定してたものです。

CREATE TABLE test (
  column1   nvarchar(10)
  , column2 nvarchar(10)
  , column3 INTEGER
  , PRIMARY KEY(column1)
);

取り込むファイルの文字コードはUTF8でCRLFの改行コードが入っているものとします。

あ	A	1
い	I	2
う	U	3

フォーマットファイルは以下のような指定になります。

13.0
3
1  SQLCHAR   0  128  "\t"    1   column1  ""
2  SQLCHAR   0  128  "\t"    2   column2  ""
3  SQLCHAR   0  0    "\r\n"  3   column3  ""

bcpを実行します。

bcp.exe test in test.txt -c -C 65001 -S server_name -U sa -P password -d database_name
 
コピーを開始しています...
 
3 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 187    平均 : (16.04 行/秒)

結果は以下のようになります。

column1	column2	column3
あ	A	1
い	I	2
う	U	3

パフォーマンスチェック／チューニング

SQLの確認

そのうち書きます

チューニング

そのうち書きます

TIPS

インポート・エクスポートコマンド生成SQL

SELECT
  t.name
  -- エクスポート側
  , 'bcp.exe ' + name + ' out ' + name + '.txt -c -C 65001 -S from_database -U sa -P password -d from_database' AS export_cmd
  -- テーブルデータ削除
  , 'truncate table ' + name + ';' AS trancate_sql
  -- インポート側
  , 'bcp.exe ' + name + ' in ' + name + '.txt -c -C 65001 -S to_database -U sa -P password -d to_database' AS import_1_cmd
FROM
  sys.objects t
WHERE
  t.type = 'U'
ORDER BY
  t.name

UTF8でのエクスポートはbcpコマンドのバージョンが古いと動かないようです
- ここでMicrosoft Command Line Utilities 15 for SQL Serverをダウンロードしインストールしたbcpコマンドを使う必要があります
- bcpコマンドのありかは「C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn」でした（私の場合）

text型のクセ

text/ntext型は以下のようなクセがあるのでご注意ください。

replace関数が使えません
- いったんvarchar等にcastして置換します
- REPLACE(CAST(text_column AS VARCHAR), ‘aaa’, ‘bbb’)
text_column <> ”とかが使えません
- これもvarcharにcastします

PHP + SQLServer

SQLServer 用の PDO ドライバーを PHP に設定します
- PDO ドライバーはこちら
  - PHP のバージョンごとにドライバがあるようです
- php.ini ファイルに設定
  - extension=pdo_sqlsrv_73_ts
  - dllファイルがphp_pdo_sqlsrv_73_ts.dllの場合

（いまさら）SQLServer Express 2008 インストール

インストーラをダウンロードし実行します
- https://www.microsoft.com/ja-jp/download/details.aspx?id=30438
- saアカウントを有効にします
リモート接続を許可します
- Management Studioから設定もできますが、コマンドプロンプトからも設定可能です

cd "C:\Program Files\Microsoft SQL Server\100\Tools\Binn"
SQLCMD.EXE -S localhost\\instance_name -U sa -P xxxxxxx
 
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;
GO
ALTER LOGIN sa ENABLE;
GO
-- ALTER LOGIN sa WITH PASSWORD = 'password';
-- GO
EXEC sp_configure 'remote access', 1;
GO
RECONFIGURE;
GO

SQLServer Configuration managerを起動します
- Win+R: sqlservermanager10.msc
- SQLServer ネットワークの構成 → プロトコル
  - TCP/IPを有効化
    - TCP/IPのプロパティ → IPアドレス → IP Allに1433を設定します
サービス
- SQL Server Browserを自動起動に設定し開始します
Windowsファイアウォール
- 受信規則で以下を許可します
  - UDP 1434
  - TCP 1433