2016/7/30 エラー一覧 表示 ⇒ エラー一覧 からリスト表示可能 バックアップ DBを右クリック ⇒ タスク ⇒ バックアップ ⇒ OK リストア DBを右クリック ⇒ タスク ⇒ 復元 ⇒ データベース ⇒ ⇒ オプション ⇒ チェック="既存データベースを上書きする ⇒ ⇒ チェック無し="復元の前にログ末尾のバックアップを実行する ⇒ OK (操作前に既存クエリは全て閉じる) (もしエラーが出る場合、一度ログアウトしてから再度ログインして実行) コマンドプロンプトから sqlcmd /E /S Takehiro\SQLExpress2014 /E Windows認証を利用して接続 /S 接続先となる SQL Server の名前 sqlcmd では "go" がステートメント実行の合図 コマンドプロンプトからのバックグランド実行 sqlcmd /E /S Takehiro\SQLExpress2014 /i "D:\temp.sql" D:\temp.sql には任意のSQLを保存 updateも可能 認証 第1 SQL Server への接続 ログイン アカウントの作成 第2 データベースへの接続 データベース ユーザーの登録 第3 オブジェクトの操作 オブジェクト権限の付与 ユーザー追加(第一認証) セキュリティー ⇒ ログイン ⇒ 新しいログイン ⇒(ユーザーは次回ログイン時にパスワードを変更) データベース ユーザーの登録(ログインとのマッピング) (第二認証) セキュリティー ⇒ ログイン ⇒ ユーザー ⇒ プロパティ ⇒ ユーザーマッピング ⇒ ⇒ DB選択チェック ⇒ OK オブジェクト権限の付与
2016/7/30 宣言と同時代入は2008から Declare @z int = 888
単純表記 select 10 f1, 20 f2, 'aaa' f3
1行代入 Select @x = 88, @y = 99, @z = 'AAA' Set からの代入は複数不可
バッチ(go)と変数の範囲 T-SQL のローカル変数はバッチの範囲のみ有効。 バッチの区切りは "go" クエリエディタの行番号 ツール ⇒ オプション ⇒ テキストエディタ ⇒ Transact-SQL ⇒ 全般 ⇒ 表示 ⇒ 行番号 IF文 Case文
If (Datepart(hour, Getdate() ) < 12) Declare @x int = Datepart(hour, Getdate() )
Begin Select
Print 'おはよう' Case
End When @x <12 Then 'おはよう' Else When @x < 17 Then 'こんにちは'
Begin Else 'こんばんは' Print 'こんにちは' End End (Select 直後に "@msg =" を付与し変数代入も可) インクリメント演算子 Set @i += 1 While文 Declare @i int = 1 While @i <= 5 Begin Print @i Select @i += 1 End Goto によるジャンプ Goto test
test: IF文や While文中も可能
Sleep
Waitfor Delay '00:00:03' Select * From emp
2016/7/30 照合順序
カナ, 全半角, 大小文字, アクセント ⇒ kana, byte, case, accent CS_AS_KS_WS (デフォルト = Japanese_CI_AS)
C Case S Sensitive
A Accent I Insensitive
K Kana
W Wide
Japanese_BIN (バイナリ順: Binary sourt, すべてを区分する)
漢字コード(char, varchar ⇒ Shift-jis, vchar, nvarchar ⇒ Unicode)
テーブル作成(主キー, 照合順序) Create Table emp2
( empno int Primary Key
, empName varchar(50) Collate Japanese_CI_AS Not Null
変更時 Alter Table empTest Alter Column empName varchar(10) Collate Japanese_CI_AS SQL単位 Select * From emp Where empName = 'a' Collate Japanese_CI_AS_KS
char型はディスク使用効率が×でパフォーマンスが○。性別や商品コードに使用。 text型は下記互換のため、char(max) or varchar(max) or n以下略 ユニコードとしたい場合はシングルクォートの前にNプレフィックス int型系は小数点以下はエラーにならず切捨てられる getdate() は分まで、sysdatetime() は100ナノ秒(10-7まで) current_timestamp パフォーマンス WHERE CONVERT(varchar, a, 111) = '2012/04/01' 上記は重い。インデックスが利用されないインデックススキャン(全件走査)になるため。 グレーターザン、レスザンを使用。 フォーマット関数
Print Format(Current_TimeStamp, 'yyyy/MM/dd aaaa dddd HH:mm:ss') └ 2016/07/29 aaaa 金曜日 22:37:49
Print Format(Current_TimeStamp, 'yy/M/d aaaa ddd hh:mm:ss') └ 2016/7/29 aaaa 金 10:37:49
2016/7/30 データ型 char 固定長文字列(8000バイトまで) varchar 可変長文字列(8000バイトまで) varchar(max) メモ型(2Gまで), nvarchar(max)も可 nchar Unicode対応の固定長文字列(4000文字まで) nvarchar Unicode対応の可変長文字列(4000文字まで) tinyint 0 ～ 255 smallint -31,769 ～ 32,767 int -231 ～ 231-1 231 = 21億 bigint -263 ～ 263-1 263 = decimal(p, s) 新数型, p: 全体桁, s: 小数点以下桁 numeric(p, s)でも可能 float(n) 概数型, 精度=8-15桁, ac単精度浮動小数型 real 概数型, 精度=1-7桁, ac倍精度浮動小数型 概数とは切上・切捨・四捨五入などによって表せられるおおよその数 smalldatetime 分単位 datetime 3.33ミリ秒単位 date 日単位 time 時間のみを格納、100ナノ秒単位 datetime2 100ナノ秒単位 datetimeoffset 100ナノ秒単位かつタイムゾーンも可能 smallmoney 範囲小 money 範囲大 binary 固定長バイナリ(8000バイトまで) varbinary 可変長バイナリ(8000バイトまで) varbinary(max) 2Gまで bit 1 or 0, yes/no型 table, XML, geometory, geography
DatePart, DateDiff 引数
year yy, yyyy weekday dw ×datediff
quarter qq, q hour hh
month mm, m minute mi, n
dayofyear dy, y second ss, s
day dd, d millisecond ms
week wk, ww (weekdayは日曜開始で1-7)
DATEADD (datepart , number, date )
DATEDIFF ( datepart , startdate , enddate ) EOMonth ⇒ 2012から
DateFromParts ⇒ 2012から、DateSerialと同様、複数形注意
2,147,483,648
2016/7/30 Format ⇒ 2012から、 FORMAT ( value, format [, culture ] )
CONVERT( 変換後のデータ型, 変換したいデータ [,日付書式のオプション] ) CAST( 変換したいデータ AS 変換後のデータ型 )
Convert(varchar, DateFromParts(2012, 12, 13), 111) ⇒ "111"は日本
intデータ型に対しAVGなど集計関数を利用すると結果もintになる。そのため ○ AVG( Convert( float, sal) )
× Convert(flost, AVG(sal) ) AVGの段階でintの少数無しに
GetDate よりも Oracleで仕様可能な "Current_TimeStamp" を使用
Print Substring('abcde', 3, 2) 上記は "cd"
Print CharIndex('cd', 'abcde') 上記返り値は 3
T-SQLの IsNull は Oracle のNVL、accのNz Select IsNull(empno, 'aa') as empno2 From emp IIF は Oracleの Decode
Choose関数で曜日を取得することも1案, (DatePart(weekday, から)
関数 役割
GETDATE 現在の日付と時刻を取得（datetime データ型対応）CURRENT_TIMESTAMPと記述しても可 SYSDATETIME GETDATE の datetime2 データ型対応版
SYSDATETIMEOFFSET GETDATE の datetimeoffset データ型対応版 YEAR 日付から年を取得（結果は int 型） MONTH 日付から月を取得（結果は int 型） DATEPART 日付／時刻から指定した部分を取得（結果は int 型） DATEADD 日付／時刻の加算を行う DATEDIFF 日付／時刻の差を取得 EOMONTH 月末の取得 DATEFROMPARTS 文字列からの日付データの作成 FORMAT 日付データの書式の変更 関数 役割 UPPER、LOWER 大文字、小文字変換 RTRIM／LTRIM 右／左から半角スペースの削除 REPLACE STUFF 文字列の置換 RIGHT／LEFT 右／左から部分抽出 SUBSTRING 部分抽出 LEN 文字列の長さ DATALENGTH 文字列のバイト数 CHARINDEX 文字列内の検索 ASCII 文字の ASCII コード取得 CHAR ASCII コードを文字変換 SQL Server 役割 ROUND 四捨五入 POWER べき乗 RAND 乱数 CEILING(n) n に対してそれ以上の最小の整数 FLOOR(n) n に対してそれ以下の最大の整数 SQRT 平方根
2016/7/30 ユーザー定義関数
CREATE FUNCTION TRIM ( @param1 varchar(100) ) RETURNS varchar(100)
AS BEGIN
RETURN RTRIM( LTRIM(@param1) ) END
CREATE FUNCTION 関数名 ( [@パラメーター名 データ型], ･･･ ) 使用する時は "dbo."を付与、 select dbo.Trim(empname) from emp ユーザー定義関数は プログラミング ⇒ スカラー関数 に
削除は Drom Function dbo.Trim
◆◆ここから応用編◆◆ ローカル変数へ select代入
Declare @shimei varchar(50), @hiredate datetime
Select @shimei = empname, @hiredate = hiredate from emp where empno = 1 select @shimei, @hiredate;
ローカル変数への代入は1レコードのみ、whereなどで絞る
パラメータ化, exec
Declare @x varchar(20) = 'emp' Exec ('select * from ' + @x);
sp_executesqlによる動的SQL (システム ストアド プロシージャー) Declare @sql nvarchar(100), @x varchar(10)
Select @x = 'emp'
Select @sql = N'Select * From ' + @x Exec sp_executesql @sql
sp_executesql は文字列連結が完了していることが前提。そのため下記は不可 Exec sp_executesql N'Select * From ' + @x
sp_executesql のパラメーター化
exec sp_executesql N'Select * from emp Where empname Like @p1 And sal > @p2' , N'@p1 varchar(50), @p2 int'
, @p1 = '%田%', @p2 = 290000
左記でも可能 ⇒ Exec sp_executesql @sql, @xx, @p1=@x, @p2=10
Exec と sp_executesql は後者がパフォーマンスが良い
2016/7/30 トップ句にはパレンを付与する
Declare @x int = 3
○ Select Top 3 * From emp
○ Select Top (@x) * From emp ただし accはパレン不可 × Select Top @x * From emp (MySQL は語尾に Limit 5)
Merge, 2008から
データが存在する場合は Update, データ不在の場合は Insert Update と Insert をつなげて、造語 Upsert
Merge Into t1 Using t2
On t1.a = t2.a When Matched Then
Update Set t1.b = t2.b When Not Matched Then
Insert Values(t2.a, t2.b); (ステートメント末尾のセミコロン必須)
Offset - Fetch ページング(2012から)
Select * From emp Order By hiredate Desc
Offset 0 Rows ○ゼロスタート
Fetch Next 3 Rows Only
一時テーブル
テーブル名にシャープ # を付与。
あらかじめ Create Table でも可能。 Intoでのテーブル作成クエリも可能。
一時テーブルとインラインビュー サブクエリが5階層以上になった場合は一時テーブル。 インラインビューも内部的には一時的な作業テーブルを作成している。 クエリ オプティマイザーの限界が5階層ほど。 一時テーブルを作成し、その一時テーブルへインデックスを作成するとパフォーマンス向上。 接続解除で自動に一時テーブルは消失するが、Drop Table から明示削除も可能。
2016/7/30 ソート
Select
Row_Number() Over(Order By hiredate Desc) rowNum , Rank() Over(Order By hiredate Desc) rnk1
, Dense_Rank() Over(Order By hiredate Desc) rnk2 , NTile(3) Over(Order By hiredate Desc) ntile1 , *
From emp
これらは関数
Partition By 句によるグループ化 Select
Row_Number() Over(Partition By deptno Order By hiredate Desc) rowNum , Rank() Over(Partition By deptno Order By hiredate Desc) rnk1
, Dense_Rank() Over(Partition By deptno Order By hiredate Desc) rnk2 , NTile(3) Over(Partition By deptno Order By hiredate Desc) ntile1 , *
From emp
テーブル変数
Declare @t Table ◆テーブル変数は一時テーブルに比して
( rownum int 1. 配列代替として利用
, empno int 2. Select * Into でのテーブル作成不可
, empname char(50) 3. テーブル変数へインデックス作成不可
, sal int 4. 複数CPUコアがあってもパラレル処理にならない
, hiredate datetime 5. 有効範囲がバッチ内のみ(接続中のみ有効)
, deptno int )
Insert Into @t
Select Row_Number() Over(Order By hiredate Desc) rowNum, * From emp
2016/7/30 ユーザー定義テーブル型 (User-Defined Table Type) (2008から)
Create Type type1 As Table
( rowNum int DB内へ永続化される
, empno int ) 削除は Drop Type から
go ストアドパラメーターに利用
Declare @t type1 Insert Into @t
Select Row_Number() Over(Order By hiredate Desc) rowNum, empno From emp Select * From @t Where rowNum Between 1 And 3
Drop Type type1
CTE(Common Table Expression) 共通テーブル式 go
With cteTest1
As ( Select Row_Number() Over(Order By hiredate Desc) rowNum, empname From emp) Select * From cteTest1 Where rowNum Between 1 And 3
内部的にはインラインビュートほぼ同じ ⇒ インラインビューの置換え、可読性向上に CTEにのみの特徴として再帰クエリ、コード実行と共におそらく無くなる 再帰クエリ(CTE) WITH cte1 (社員番号, 社員名, 上司社員番号, 階層) AS ( -- 上司 SELECT 社員番号, 社員名, 上司社員番号, 0 FROM 社員 WHERE 社員番号 = 1001 UNION ALL -- 部下（再帰）
SELECT e.社員番号, e.社員名, e.上司社員番号, cte1.階層 + 1 FROM 社員 AS e
INNER JOIN cte1
ON e.上司社員番号 = cte1.社員番号 )
2016/7/30 ストアド プロシージャー(最基本)
go
CREATE PROCEDURE proc1 AS
SELECT * FROM emp WHERE deptno = 20
ストアド変更, パラメータ付き ALTER PROCEDURE proc1
@param1 int 初期値指定 ⇒ @param1 int = 10
AS
SELECT * FROM emp WHERE deptno = @param1 EXEC proc1 @param1=20 または
EXEC proc1 20
パラメータのnull判定
ALTER PROCEDURE proc1 @param1 int = NULL
AS ◆Return で強制終了
IF @param1 IS NULL BEGIN
BEGIN PRINT 'パラメーター未入力！'
PRINT 'パラメーター未入力！' RETURN
END END
ELSE Return(0), Return(1) も可
BEGIN
SELECT * FROM emp WHERE deptno = @param1 END
@@RowCount は1つ前のSQL実行時に影響のあった行数を返すシステム関数 出力パラメーター (Output)
CREATE PROCEDURE proc4 ↓
@param1 int, 事前設定
@param2 int OUTPUT AS
SELECT * FROM emp WHERE deptno = @param1 事前設定
SELECT @param2 = @@ROWCOUNT ↑ @@RowCoutn = システム関数
DECLARE @out1 int ↓
EXEC proc4 10, @out1 OUTPUT 実行時
2016/7/30 テーブル値パラメーター (Table-Valued Parameters), In句のパラメーター化
-- ユーザー定義テーブル型 go
CREATE TYPE valuelist AS TABLE ( val int ) 事前 - タイプ作成 go
-- テーブル値パラメーターを利用するストアド プロシージャ
CREATE PROCEDURE proc3 事前 - プロシージャー作成
@v valuelist READONLY AS
SELECT * FROM emp WHERE empno IN ( SELECT val FROM @v )
-- ユーザー定義テーブル型を利用した変数@v の宣言
DECLARE @v AS valuelist 実行時
タイプ宣言
-- 変数 @v へ値の格納（IN 演算子に与える値） タイプにパラメータ値代入
INSERT INTO @v (val) タイプパラ入りストアドの実行
VALUES ( 1 ) ,( 5 ) -- ストアド プロシージャの実行 EXEC proc3 @v vba で複数のレコードセットを含む場合のrs移動 Set rs = rs.NextRecordset()
Identity (oracleのSequence, accのCounter)
Create Table t1(idt int Identity(1, 1), f2 varchar(10) )
(ロールバックしても連番が付与されるため、完全な連番にならない)
直近Identity 取得
Select Scope_Identity() (同一モジュール内であれば一貫して同じ値 = 自分が追加した Identity値) (Set Nocount On)
出力パラメーター (ADOの場合のみ, 通常でもあって問題にならない)
CREATE PROCEDURE proc5 ("～行処理されました" をレコードセットとしない)
@p1 int DECLARE @out1 int
,@p2 int OUTPUT EXEC proc5 333, @out1 OUTPUT AS
SET NOCOUNT ON SELECT @out1 INSERT INTO idTest VALUES(@p1)
2016/7/30 Return コード
EXEC @ret = proc6 Return のデフォルト値はゼロ
ストアド取得
Select definition From sys.sql_modules where definition Like '%proc%' データベース毎に異なるので、use からDB指定
データベース中の ⇒ プログラミング ⇒ ストアドプロシージャー ⇒ からも
Set Xact_Abort On, 全体ロールバック 規定では OFF
ステートメントエラーが発生した場合もロールバックを行う ALTER PROCEDURE procTranTest
@param1 int AS
SET XACT_ABORT ON BEGIN TRAN
INSERT INTO tranTest VALUES ( @param1, 999 )
INSERT INTO tranTest VALUES ( 1, 999 ) ※ここだけエラーの場合、
COMMIT TRAN 1行上コードもロールバック
SET XACT_ABORT OFF
2つのレコードセット from ADO
strSQL = "Set NoCount On Select * From emp; Select @@RowCount;"
