実際の実行 Set Rs = Cn.Execute() = "Insert Into T3(F1,F3) Select F1,F3 From T1 " 更新クエリ UPDATE T1 SET T1.F3 = "2", T1.F4 = "1" IIF 条件 UPDATE T1 SET F3 = "2"

2015/11/1

重複値抽出

SQL = "Select Str From [T1$] " & _ "Group By Str " & _

"Having Count(*)>1 "

↓サブクエリ↓

SQL = "Select * From [T1$] Where Str1 In " & _

"(Select Str1 From [T1$] Group By Str1,Str2 Having Count(*)>1)"

Select フィールドが1つなのは In条件に代入するため（他は不要）

不一致クエリ

左に存在、右に不存在 ⇒ 抽出

SQL = "Select * From [Sheet1$] As a " & _ "Left Join [Sheet2$] As b " & _

"On a.F1=b.F1 " & _ "Where b.F1 Is Null "

トップ10%

SQL = "Select Top 10 Percent * From [Sheet1$] " & _

"Order By F1 Asc " 降順時は Asc ⇒ Desc

累計

累計: DSum("Num","T2","主キー>=" & [T2]![主キー]) トップテンなど他の条件が無い場合の処理 アンパサンド失念注意

テーブル作成クエリ

SQL = "SELECT * INTO [Ex Sheet] FROM Sheet1" CurrentDb.CreateQueryDef("", SQL).Execute

DoCmd.OpenTable "Ex Sheet" Recordset は不可

DoCmd.RunSQL "Select KStr,KDate Into tmp From T1"

追加クエリ

INSERT INTO [Ex Sheet] SELECT F2,F11 FROM Sheet1;

Select 後に追加フィールド設定、*可、単純Append 追加 【エクセル時】

2015/11/1 実際の実行 ⇒ Set Rs = Cn.Execute(SQL)

SQL = "Insert Into T3(F1,F3) Select F1,F3 From T1 "

更新クエリ

UPDATE T1 SET T1.F3 = "２", T1.F4 = "１"

↓IIF 条件↓

UPDATE T1 SET F3 = "２", F4 = IIF(F1 Like '3',True,False) 【エクセル時】

SQL =

"Update [Sheet1$] Set F3='京都', F4=IIF(F1 In ('2','3'),True,False)"

順位づけ

SELECT Count(T1(2).社員番号) AS 実績順位, T1.社員番号, T1.社員名, T1.実績 FROM T1, T1 AS T1(2) WHERE (((T1(2).実績)>[T1]![実績])) OR (((T1(2).社員番号)=[T1]![社員番号])) GROUP BY T1.社員番号, T1.社員名, T1.実績 ORDER BY Count(T1(2).社員番号); ↓エクセル時（全く同様）↓ SQL = _

"Select Count(b.社員番号) As 実績順位, a.社員番号, a.社員名, a.実績 " & _ "From [Sh$] As a,[Sh2$] As b " & _

"Where (b.実績 > a.実績) Or (b.社員番号 = a.社員番号) " & _ "Group By a.社員番号, a.社員名, a.実績 " & _

"Order By Count(b.社員番号) "

社員番号 = 主キー重複無し

シートをVBAでコピーして使用

合計レコードを1行 Union から追加

SQL =

"Table [Sh$] Union All Select Null,'合計',Sum(実績) From [Sh$] "

順位フィールド追加_Dcount利用

順位: DCount('T価格順!商品id','T価格順','価格>' & [価格])+1 順位: Dcount('主キーF','テーブル名','数字F>' & [数字F])+1

エクセル同様

2015/11/1 Dim Cn As New ADODB.Connection

Dim Cmd As New ADODB.Command Dim Cat As New ADOX.Catalog Dim SQL As String

Cn.ConnectionString = _

"Provider=Microsoft.Ace.OLEDB.12.0;" & _ "Data Source=" & Path & "参照.accdb" Cn.Open

Cat.ActiveConnection = Cn

SQL = "SELECT * FROM T1 WHERE KStr LIKE '*Str*'" Cmd.ActiveConnection = Cn

Cmd.CommandText = SQL On Error Resume Next

Cat.Tables.Delete "newT" テーブル削除 Cat.Views.Delete "新規クエリ" クエリ削除 On Error GoTo 0 Cat.Views.Append "新規クエリ", Cmd クエリ追加 Cn.Close

アクセスからADOでカレント設定

Set Cn = CurrentProject.Connection

エクセルからのテーブル作成クエリまとめ

Dim DB As DAO.Database Set DB = CreateObject("DAO.DBEngine.120").Workspaces(0) _ .OpenDatabase(Path & "参照先.accdb")

SQL = "Select * Into newT From newQ" On Error Resume Next

DB.TableDefs.Delete "newT" On Error GoTo 0

DB.CreateQueryDef("", SQL).Execute newQ クエリは既存条件

DoCmd.RunSQL "SELECT * INTO tmp FROM T1"

ADOX まとめ、アクセスから

Dim Cmd As New ADODB.Command Dim Cat As New ADOX.Catalog

Cn.ConnectionString = CnStr & "参照.accdb" Cn.Open

2015/11/1 Cat.ActiveConnection = Cn Cmd.ActiveConnection = Cn Cmd.CommandText = SQL Cat.Views.Append "newQ", Cmd

選択クエリカウント

Dim Cat As New ADOX.Catalog Cn.Open

Set Cat.ActiveConnection = Cn MsgBox Cat.Views.Count

Dim Cat As New ADOX.Catalog 名称取得

Set Cn = CurrentProject.Connection Set Cat.ActiveConnection = Cn For Each c In Cat.Views

MsgBox c.Name Next c

拡張子

mdf データファイル ldf トランザクション ログファイル 　└ データ復元

テーブル手動新規作成

データベース名 ⇒ テーブル ⇒ 右クリック新しい～ ⇒ 定義して保存

行数指定のレコードセット取得

SQL = "Select PrmKey, " & _

"(Select Count(PrmKey) From T3 Where PrmKey < b.PrmKey)+1 As PriKey " & _ "From T3 As b " & _

"Where (Select Count(PrmKey) From T3 Where PrmKey < b.PrmKey)+1 = 3" ◆ 【テーブル名 As b】はサブクエリ内From 不可

演算子 < ⇒ Less than

Graphical User Interface ⇒ GUI

復元

2015/11/1

右クリックからスクリプト化可能

データベースのスクリプト化 テーブルのスクリプト化

コマンドプロンプトからの SQLcmd

sqlcmd /E /S サーバー名 E = Window認証接続 S = 接続先 SQL Server Name >1 で接続有効 実行は go, 終了は exit

/i バックグランド実行

sqlcmd /E /S Takehiro-pc\SQL /i "C:\Test.sql"

エクセル外部データ

データ ⇒ その他のソース ⇒ SQL Server から

2015/11/1

アクセス外部データ

外部データ ⇒ ODBC ⇒ DSN名新規作成 ⇒ SQL Server ⇒

参照でDesktop\Test など ⇒ 完了 ⇒ サーバー名 = takehiro-pc\sql ⇒ 次⇒ 既存のデータベースを以下から Initial Catalog ⇒ sampleDB2 ⇒ 完了 ⇒ OK ⇒ 選択OK ⇒ テーブル選択を 社員2（複数可） ⇒ 終

ワイルドカートエスケープ

LIKE '果汁100\%%' ESCAPE '\' ～～\%～' Escape '\' ⇒ \以下をエスケープ 『エスケープ文字』、エン\ 以外も可能だが通常エン\ 使用

テーブルデータ全体の高速削除

TRUNCATE TABLE Tbl1 削除はテーブル自体ではなくデータ

Identityリセット ⇒ Delete From ではリセットされない

SQL Server での GUI

クエリエディタ上で右クリック ⇒ エディタでクエリをデザイン

From にサブクエリ

SQL = "Select Num As Fld, KDate, Str From Tbl1 Order By Fld Asc;" ↓

SQL = "Select * From " & _

"(Select Num As Fld, KDate, Str From Tbl1) " & _ "Order By Fld Desc"

Max関数で文字列取得、冗長回避

2015/11/1 GROUP BY 部門.部門番号, 部門名

↓

SELECT 部門.部門番号, Max(部門名), COUNT(*) GROUP BY 部門.部門番号

自己結合

Select emp.社員番号, emp.氏名, emp.上司社員番号, manager.氏名 As 上司の氏名

From emp Inner Join emp manager

On emp.上司社員番号 = manager.社員番号

インライン ビュー

SELECT ステートメントの結果をテーブルのように利用する方法 Select * From 社員2

Inner Join

(Select 部門番号, MAX(給与) m_sal From 社員2 Group By 部門番号) As b

On 社員2.部門番号 = b.部門番号 And 社員2.給与 = b.m_sal

テーブル作成 要求値、主キー

SQL = "Create Table tmp (F1 Int Not Null, F2 Int Primary Key);"

Order By には数値指定可能

SQL = "Select * From T3 Order By 3 Desc" 数値は１スタート、1カラム目

2015/11/1

文字列はダブル不可、シングルのみ

文字列結合は + 結合演算子、& or || 不可

Null 有集計関数

Select Avg(IsNull(給与, 0)) From 社員2 IsNull(給与, 0) == Nz(給与, 0)

Inner Join の Inner は省略されることも

順位まとめ

SQL = _

Select * From & _ "(Select " & _

"(Select Count(KNum) From T3 " & _

" Where T3.KNum < b.KNum)+1 As 順位 " & _ ", PKey, KStr, KDate " & _

"From T3 As b )" & _ Order By 順位 Asc;

Case When Then Else End、IIF 関数代替

Select Case 部門番号 When 10 Then '総務部' When 20 Then '営業部' Else 'その他' End As 部門, * From 社員2

Join時に記述順結合を強制、Option(Force Order)

Select 仕入先名, 区分名, 商品.* From 商品 Inner Join 商品区分

2015/11/1 On 商品.区分コード = 商品区分.区分コード Inner Join 仕入先 On 商品.仕入先コード = 仕入先.仕入先コード Option(Force Order) Select の最後に付記

With [ Rollup | Cube ]

Group By Year(jt.受注日), sk.区分名 Group By の直後に記述 Group By sk.区分名, Year(jt.受注日)

With Rollup 空白はSQL Server では Null表示

全体の集計と GROUP BY 句の “1つ目の列” でグループ化した集計

Access SQL の Join ネスト

SQL = _

Select Year(jt.受注日) As 受注年, sk.区分名, Sum(jm.単価 * jm.数量) As 受注額 & _ "From (((受注明細 jm " & _

"Inner Join 受注 jt " & _

"On jm.受注コード = jt.受注コード) " & _ "Inner Join 商品 sh " & _

"On jm.商品コード = sh.商品コード) " & _ "Inner Join 商品区分 sk " & _

"On sh.区分コード = sk.区分コード) " & _ "Group By Year(jt.受注日), sk.区分名 " ◆結合ごとにパーレン付与

ビュー作成 （仮想表）

Go バッチ区切りとして Go 付記 Create View 受注商品一覧 As Select jt.受注コード, jt.受注日, jm.単価, jm.数量, sh.商品名, sk.区分名 From (((受注明細 jm

2015/11/1 Inner Join 受注 jt On jm.受注コード = jt.受注コード) Inner Join 商品 sh On jm.商品コード = sh.商品コード) Inner Join 商品区分 sk On sh.区分コード = sk.区分コード) 作成時、オブジェが閉じている旨メッセージがあるがOkay ↓↓ 実行

Select 区分名, Sum(単価 * 数量) As 受注額 From 受注商品一覧 Group By 区分名 Order By 区分名 └ テーブル指定が不要になる └ 上記で各 View 指定フィールドの取得、演算が可能 └ パラメーター以外は Access Query と同じ（ストアド） ◆View 削除 Select 商品名 From 受注商品一覧

クロス集計

◆SQL Server Ver Select sk.区分名

, Sum( Case Year(jt.受注日)

When '2005' Then jm.単価 * jm.数量 Else 0 End) As CY2005 , Sum( Case Year(jt.受注日)

When '2006' Then jm.単価 * jm.数量 Else 0 End) As CY2006 , Sum( Case Year(jt.受注日)

When '2007' Then jm.単価 * jm.数量 Else 0 End) As CY2007 , Sum( jm.単価 * jm.数量) As 総計

From (((受注明細 As jm

◆Access Ver Select sk.区分名 , Sum(

IIF( Year(jt.受注日) = '2005', jm.単価 * jm.数量, 0)) As CY2005 , Sum(

IIF( Year(jt.受注日) = '2006', jm.単価 * jm.数量, 0)) As CY2006 , Sum(

IIF( Year(jt.受注日) = '2007', jm.単価 * jm.数量, 0)) As CY2007 , Sum( jm.単価 * jm.数量) As 総計

From (((受注明細 As jm

◆SQL Server Pivot For 列 In ( カンマDelim列名 )

2015/11/1 (Select sk.区分名, Year(jt.受注日) As 受注年 , jm.単価 * jm.数量 As 受注金額 From (((受注明細 jm Inner Join 受注 jt On jm.受注コード = jt.受注コード) Inner Join 商品 sh On jm.商品コード = sh.商品コード) Inner Join 商品区分 sk On sh.区分コード = sk.区分コード) ) p

Pivot ( Sum(受注金額) For 受注年 In ( [2005], [2006], [2007] )) As pvt Order By sk.区分名

Insert Into からは PrimaryKey 非作成

テーブル定義のみ作成

存在しない条件を Where句で

Select * Into 社員3 From 社員 Where 社員番号=-1

(Ex) Rs.RecordCount

adOpenKeyset で開く必要あり、Rs.MoveLast は不要

Cn.Execute(SQL) or adOpenForwardOnly では Rs.RecordCount = -1

(Ex) Rs.GetRows

行列逆になるため Transpose v = Rs.GetRows(30, adBookmarkCurrent) v = Application.Transpose(v) 以下は同じ、インデックス含まず Range("E2").Resize(UBound(v, 1), UBound(v, 2)) = v Range("E2").Resize(Rs.RecordCount, Rs.Fields.Count) = v

テーブル作成、構造のみコピー

DoCmd.TransferDatabase _ acExport, _ "Microsoft Access", _ CurrentDb.Name, _ acTable, _ "元テーブル名", _ ●変更点 "コピー後名称", _ ●変更点 True 'Structure Only

主キーのSQL追加

2015/11/1

クロス集計、ピボット

Transform Sum(単価 * 数量) Select Year(日) & '年' As 年, Month(日) & '月' As 月, 商品, 性別, Sum(単価 * 数量) As 金額合計 From DB3

Group By Year(日), Month(日), 商品, 性別 Order By Year(日) Asc, Month(日) Asc, Pivot 地域 ; 処理4分40秒 ◆同じ処理に対するSQL Select 処理2分25秒 Year(日) & '年' As 年, Month(日) & '月' As 月, 商品, 性別, Sum(単価 * 数量) As 合計, Sum(IIF(地域 = '京都', 単価 * 数量, 0)) As 京都, Sum(IIF(地域 In ('千葉'), 単価 * 数量, 0)) As 神奈川, Sum(IIF(地域 = '京都', 単価 * 数量, 0)) As 千葉, Sum(IIF(地域 = '京都', 単価 * 数量, 0)) As 大阪, Sum(IIF(地域 = '京都', 単価 * 数量, 0)) As 東京 From DB3

Group By Year(日), Month(日), 商品, 性別 Order By Year(日) Asc, Month(日) Asc,

クロス集計まとめ

Select Year(日) & '年' As 年, Month(日) & '月' As 月,

2015/11/1 商品, 性別, IIF(IIF(Format(誕生日,'mm/dd') > Format(Date(),'mm/dd'), Datediff('yyyy', 誕生日, Date())-1, Datediff('yyyy', 誕生日, Date())) < 10, '10未満', IIF(IIF(Format(誕生日,'mm/dd') > Format(Date(),'mm/dd'), Datediff('yyyy', 誕生日, Date())-1, Datediff('yyyy', 誕生日, Date())) < 20, '10代', '年齢不明')) As 年齢, Sum(単価 * 数量) As 合計, Sum(IIF(地域 = '京都' , 単価 * 数量, 0)) As 京都, Sum(IIF(地域 = '千葉' , 単価 * 数量, 0)) As 千葉 From DB3

Group By Year(日), Month(日), 商品, 性別,

IIF(IIF(Format(誕生日,'mm/dd') > Format(Date(),'mm/dd'), Datediff('yyyy', 誕生日, Date())-1, Datediff('yyyy', 誕生日, Date())) < 10, '10未満', IIF(IIF(Format(誕生日,'mm/dd') > Format(Date(),'mm/dd'), Datediff('yyyy', 誕生日, Date())-1, Datediff('yyyy', 誕生日, Date())) < 20, '10代', '年齢不明')) Order By Year(日) Asc, Month(日) Asc, 商品 Asc, 性別 Asc ;

オートナンバーフィールド作成

DoCmd.RunSQL "Create Table TB(F1 Long, F2 Counter(1))"

併売マトリックス

TransForm Count(a.ID) As Cnt Select a.Item From T10 As a

2015/11/1 Inner Join T10 As b On a.ID = b.ID Group By b.Item Pivot a.Item;

前年比ランク当年実績

F1 = ID F2 = 前年Rank F3 = 当年Rank Pivot Row = 前年ランク Pivot Col = 当年ランク Pivot Data = Count(ID)

データ型

TinyInt 0～255 SmallInt 2^15 -32,768 ～ 32,767 Int 2^31 -2,147,483,648 ～ 2,147,483,647 BigInt 2^63 -9,223,372,036,854,775,808 ～ 整数 -1

Mid関数

Substring（Fld, 2, 1) Mid(Fld, 2,1)

SQL Server のインラインビューで別名省略不可

Select * From (Select * From Tbl) As x

2015/11/1

クエリ結果での行表示

Select

Row_Number ( ) Over(Order By nin) As RowNo, * From Test1

Join

Inner Join Left Outer Join Full Outer Join Right Outer Join

Mod演算子

% 利用

Select nin From Test1

Where Left(nin, 1) % 10 = 0 And Substring(nin, 5, 1) % 10 = 0

５７

外部データソース（Access）

USE master

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

EXEC sp_configure 'show advanced options', 1 RECONFIGURE

EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE Select * From OpenRowSet('Microsoft.Ace.OLEDB.12.0' , 'G:\大容量ファイル\電通\131005_work\450.accdb';'admin';'' , 'Select * From Data1')

SELECT * FROM

OPENROWSET('Microsoft.ACE.OLEDB.12.0' , 'Excel 12.0;Database=C:\temp\Shohin.

2015/11/1

フォーマットファイル

11.0 = バージョン番号 2 = データファイルのフィールド数 1 SQLCHAR 0 12 "," 1 a "" 2 SQLCHAR 0 10 "\r\n" 2 b Japanese_CI_AS 1 データファイルのフィールド番号 2 データ型 3 プレフィックス長 4 フィールドの長さ 5 フィールドの終端文字 6 SQL Server データベース内でのフィールド番号 7 SQL Server テーブルのフィールド名 └実際の名前でなくても良いが、空白は× 8 行の照合順序

自動番号列追加 or 新規作成

Alter Table Tbl1 Add AutoNum Int Identity(1, 1) Alter Table Tbl1 Add AutoNum Int Identity(10, 2)

2015/11/1 Set Identity_Insert Tbl1 On 　└ オートナンバー列への明示的値追加宣言 Select Ident_Current('社員コピー') 　└現在のオートナンバー値を確認 DBCC CheckIdent('社員コピー', Reseed, 20) 　└オートナンバー現値を変更

クエリ結果に行数

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row

フィールドの削除・変更

Alter Table 社員コピー Drop Column AutoNum

Alter Table 社員コピー Alter Column 部門番号 Char(10)

フォーマット

d 日（1日） M 1月 dd 日（01日） MM 01月 ddd Sun MMM Jan dddd Sunday MMMM January h 12時間制 H 24時間制 hh HH

デフォルト設定

Alter Table idTest Add

KDate Char(50) Default Format(GetDate(), 'MMM d, yyyy (ddd)')

既存値にも適用

Alter Table idTest Add KDate Date Default GetDate() With Values

宣言（定数）

Declare @x As Int Declare @x As Int = 10 Select @x = 10 , @y As Int = 30

, @z As VarChar(50)

初期値 Select @y = 2, @z = 'AAA'

2015/11/1

メッセージ表示

Ｐｒｉｎｔ 'Hello World." 　└ダブルクォート不可、シングルのみ

可変と固定（Char, VarChar）

変数の使い方

Declare @x As BigInt , @y As VarChar(30) = 'H12_01'

Select @x = Count(nin) From ( ◆別処理で変数代入

Select nin From Data100 サブクエリ可能

Where Date = @y Group By nin ) As a

Print @x

Select kin2 / @x As 客単価 From ( ◆本処理開始 Select Sum(kin) As kin2 From Data100 サブクエリ可能 Where Date = @y ) As a

IF分サンプル

Declare @x Int

Set @x = Datepart(Hour, GetDate() )

If @x < 12 Begin

ＳＱＬ自習書５の勉強 と Ｔａｙｌｏｒ Ｓｗｉｆｔ ・Ｓａｆｅ

2015/11/1 Print 'Morning!' End Else Begin Print 'Evening!' End

データベース作成

Create Database aaa

出力結果の存在Exists確認IF

If Exists (Select * From Data100 Where nin = 1) If Not Exists (Select * From Data100 Where nin = 1)

Case When Then (When Then) Else End は主に変数格納

Declare @Str VarChar(30) Select @Str = Case When 10 > 20 Then '10-20' When 10 < 20 Then '異' Else 'AAA' End Print @Str

While文、繰返し処理サンプル

Declare @x Int =1 While @x <= 10 Begin Print @x Select @x += 1 End @x += 1 【==】 @x = @x +1 【+=】演算子 = インクリメント演算子 【-=】演算子 = デクリメント演算子

2015/11/1

Goto ⇒ ：

Goto Er While @x <= 10 Begin Print @x Select @x += 1 End Er:

Print 'Hello World.'

WaitFor Delayによる待ち

WaitFor Delay '00:00:05' Print '京都'

文字列を数値に

Convert(Int, Right(nin, 1))

Japanese_ CI_AS　照合順序

ひらがな・カタカナ、半角・全角、大文字・小文字を区分せず アクセントを区分 AS = Accent Sensitive = アクセントを区分する AI = Accent Insensitive

CI = Case Insensitive (Upper Case, Lower Case) CI_AS_KS = Kana Sensitive

CI_AS_WS = Wide Sensitive

大文字 = C (Case), アクセント = A (Accent), ひらがなカナ = K (Kana) 半角全角 = W (Wide)

I = Insensitive, S = Sensitive

SQL自体の照合順序の変更は要再セットアップ

●データベース単位の照合順序

2015/11/1

●テーブル単位の照合順序 Create Table Tmp

( F1 Int Primary Key

, F2 VarChar(10) Collate Japanese_CI_AS Not Null)

規定テーブルの列単位変更（デザインからも可能） Alter Table Temp

Alter Column empname VarChar(15) Collate Japanese_CI_AS

●ステートメント単位の照合順序 Select * From emp

Where empname = '浅田 ユカリ' Collate Japanese_CI_AS_KS

Create Table 中の Primary Key, Aceess同様

Create Table tmp (

F1 Date Not Null, F2 Int Primary Key )

Tablestructure Copy

Select * Into Tmp From T1 Where 1 = 2 ありえない Where 条件

結果の表示

クエリ中で右クリック ⇒ 結果の出力 ⇒ テキスト or グリッド クエリ中で右クリック ⇒ クエリオプション ⇒ テキスト ⇒

カンマ区切り or 固定列など

Select 社員番号, RTrim(氏名), 給与, 入社日, 部門番号 From 社員

テーブル出力

コマンドプロンプトから、／後以外は小文字可能（takehiro\sqlexpress）

bcp SampleDB.dbo.社員 out D:\aaa.txt /S Takehiro\SQLEXPRESS /T /c /t ","

/S = SQLパス /T = Windows認証

/c = テキスト out / in = Export / Inport /t = 区切り（未指定でタブ区切り）

2015/11/1

テキスト出力、クエリ出力カンマ区切り設定

クエリ ⇒ 右クリック ⇒ クエリオプション

SSIS

SQL Server Integration Services

エクスポート

データベース名（SampleDB）右クリック ⇒ タスク ⇒ エクスポート ⇒ 【SQL記述】 Select 社員番号, Datediff(Year, 入社日, GetDate() ) As 勤続年数, 給与,

2015/11/1 RTrim(氏名) As 氏名,

Case 部門番号 When '10' Then '部門10' Else '部門20' End As 部門 From 社員

Order By 社員番号 Desc;

インポート

データベース名右クリック ⇒ タスク ⇒ データのインポート ⇒

データ型

2015/11/1 Decimal(p, s) p = 全体桁数、s = 少数桁数 Decimal(5, 3) で ⇒ 10.12345 ⇒ 10.123 Decimal ⇒ Numeric でも全く同じ Float 少数、Ac倍精度、有効桁数 = 15, 規定ではn=53 Real 少数、Ac単精度、有効桁数 = 7 SmallDateTime 分単位 （2014/12/13 01:10:10.000） DateTime 3.33ミリ秒単位、秒単位まで （2014/12/13 01:10:10.123） DateTime2 100ナノ秒単位 Date 日単位 （2014/12/13） Time 時間のみ格納、100ナノ秒単位 （01:10:10.1230000） Timeは少数以下7桁 Money, SmallMoney Bit 1 or 0 Table Char固定長の右側空白は Where では無視される パフォーマンスはChar, ディスク使用効率はVarChar が良い 8千バイトを超える場合は VarChar(Max) を使用、Textは下位互換用 NCはｒ, NVarChar, NVarChar(Max) ユニコード格納用、バイト数ではなく文字数指定 通常文字格納も可能 ●ユニコード文字

Create Table T1(F1 NVarChar(Max), F2 VarChar(Max)) Insert Into T1(F1, F2) Values(N'凮AAA', '凮AAA')

　└ユニコード時は先頭に【N プレフィックス】付与（大文字） 金額 SmallMoney -214,747.2468 ～ 214,747.3647 Money -922,337,203,685,477.5808 ～ 整数 -1 　└少数4桁が扱える分、Int や BigInt より4桁少ない 　└Moneyは少数4桁含めAccessの通貨型 　└日本では～ドル.～セント計算がないためBigInt代替可

2015/11/1

Date取得関数

GetDate()

SysDateTime() ナノ秒（ 10-7 ）格納

SysDateTimeOffset() タイムゾーン オフセットを含む

Print GetDate() 01 3 2014 4:07AM

Print SysDateTime() 2014-01-03 04:07:44.2671777 Print SysDateTimeOffset() 2014-01-03 04:07:44.2671777 +09:00 ◆日付WhereでのFormatやConvertは重い、冗長でも下記に × Convert(VarChar, a, 111) = '2014/12/13' ○ a >= '2014/12/13 And a < '2014/12/14'

Word

Ctrl + Alt + [R | T | C] = ®©™ Ctrl + Shift + [T | D] = 時間や日付 　└ トレードマーク（商品の出処商標・登録商標） = ™ 　└ 著作権、コピーライトマーク = ©

　└ 登録商標マーク = Registerd trademark symbol = ®

DatePart関数

Print DatePart(Quarter, SysDateTime() )

year quarter month day week weekday

dayofyear 該当年の1月1日からの日付（Int）

hour minute second

Second, Minute

●日付関数 ⇒ Day, Week, Year, Quarter, Hour, DateAdd DateAdd(Quarter, -5, GetDate() ) EOMonth Eomonth(GetDate(), -1) DateFromPart Print DateFromParts('2014', '12', '13') DateSerial の文字列Ver DateTimeFromParts, SmallDateTimeFromParts DateTime2FromParts, DateTimeOffsetFromParts

2015/11/1 Format Print Format(SysDateTime(), 'yyyy年MM月dd日 (ddd)')

CONVERT( 変換後のデータ型, 変換したいデータ [,日付書式のオプション] ) Select Convert(Char(30), hiredate) + 'AAA'

, * From emp

CAST( 変換したいデータ AS 変換後のデータ型 ) Select Cast(hiredate As Char(30)) + 'aaa' , * From emp

●ConvertとCast関数は同じだがCastはANSI SQL92規格 　 のため、他データベース製品利用を考慮してCast使用 ●Convert(VarChar, ⇒ 最大バイト数未定義 ⇒

　　最大サイズに合わせて自動調整

Select Convert(VarChar, hiredate, 111) From emp ●Convert第三引数 111 = 日 = (yyyy/mm/dd) 11 = yy/mm/dd 101 = 米式 = mm/dd/yyyy 103 = 英・仏 = dd/mm/yyyy 104 = 独 = dd.mm.yyyy 105 = 伊 = dd-mm-yyyy

AVGからの小数点以下取得

× Select Convert( Float, AVG(sal) ) ～ ○ Select AVG( Convert( Float, sal) ) ～

文字列関数その他

Datalength 文字列の長さバイト数

CharaIndex Select CharIndex('ｳ', 'あいうえお', 1) 　└ Findと同じ、第三引数で検索開始位置 検索地不存在の戻り値 = 0

Select CharIndex('ｳ', 'あいうえお' Collate Japanese_CI_AS, 1) 　└ 3

Select CharIndex('ｳ', 'あいうえお' Collate Japanese_CI_AS_KS, 1) 　└ 0

Char, AscII Select AscII('A') Select Char(65)

Upper, Lower, RTrim, LTrim, Replace, SubString, Len

2015/11/1

Power べき乗 Select Power(10, 3) ⇒ 1000

Sqrt 平方根 Select Sqrt(2) ⇒ 1.4142135623

Round, Rand, Ceiling, Floor

Ceiling, Floor は異、引数が1つで少数含む指定値での 切り上げ・切り下げ整数

Select Floor(123.456) ⇒ 123

ユーザー定義関数

UDF：User Defined Function

Create Function Trim(@param1 VarChar(100) ) Returns VarChar(100)

As Begin

Return Rtrim( LTrim(@param1) ) End

↓ 実行

プログラミング ⇒ 関数 ⇒ スカラー値関数 ⇒ dbo.Trim ↓

Select dbo.Trim(' 京都 ') ⇒ 京都

その他関数

Choose

Concat 文字列結合

Select Concat('A', 'B', 'C') ⇒ ABC

剰余 10 Mod 3 ⇒ 10 % 3

Execute は Exec 省略可能

2015/11/1 Declare @x VarChar(20)

Select @x = 'emp'

Exec('Select * From ' + @x)

列名変数化

Declare @ColName VarChar(100) Select @ColName = 'empname'

Exec('Select ' + @ColName + ' From emp')

sq_executesql による動的SQL

Declare @sql NVarChar(100), @x VarChar(10) Select @x = 'emp'

Select @sql = N'Select * From ' + @x Execute sp_executesql @sql

パラメータ

sp_executesql N'Select * From emp Where empname Like @p1 And sal > @p2' , N'@p1 VarChar(50), @p2 Int' , @p1 = '%田%', @p2 = 290000 ●注●1つ目のシングル終わりの後にスペースを入れてはいけない 第一引数 = @付きパラメータ記述 第二引数 = パラメータに対するデータ型定義 第三引数 = パラメータへ値代入

テーブル名・列名のパラメータ化

Declare @sql NVarChar(100), @x VarChar(10) Select @x = 'emp'

Select @sql =

N'Select * From ' + @x +

' Where empname Like @p1 And sal > @p2' Exec sp_executesql @sql

, N'@p1 VarChar(50), @p2 Int' , @p1 = '%田%', @p2 = 200000

Top句での変数

Declare @n Int Top句はDelete や Updateなど

2015/11/1 Select Top(@n) * From emp DECLARE @N = 値

Order By hiredate Desc DELETE TOP(@N) FROM t

Merge

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 );

文末セミコロン必須

順位など（Row_Number, Rank, Dense_Rank, Ntile

Select

Row_Number() Over(Order By hiredate Desc) As RowNum ,Rank() Over(Order By hiredate Desc) As Rank

,Dense_Rank() Over(Order By hiredate Desc) As DeRank ,Ntile(3) Over(Order By hiredate Desc) As Ntile

, * From emp

◆Partition By 句を利用したグループ化 Select

Row_Number() Over(Partition By deptno Order By hiredate Desc) As RowNum ,Rank() Over(Partition By deptno Order By hiredate Desc) As Rank

2015/11/1 ,Dense_Rank() Over(Partition By deptno Order By hiredate Desc) As DeRank

,Ntile(3) Over(Partition By deptno Order By hiredate Desc) As Ntile , * From emp

ページング

Select * From(

Select Row_Number() Over(Order By hiredate Desc) As RowNum , * From emp) t

Where RowNum Between 1 And 3 ～件目から～件目を表示

ページング2, Offset .. Only 　

Select * From emp Order By hiredate Desc

Offset 0 Rows Fetch Next 3 Rows Only

--表示件数--Offset Next

0 3 1～3件表示

2 4 3～7件表示

一時テーブルの作成

Select Row_Number() Over(Order By hiredate Desc) As RowNum , * Into #t From emp

オブジェクトエクスプローラーには表示されない 接続を切ると無くなるが Drop から削除も可能 重複名テーブルは一時テーブルでも作成不可 Create Table #t からの作成も可能

テーブル変数

Declare @t table ①テーブル名・列名・列型宣言 ( rownum int ②値代入 ,empno int ③データ利用 ,empname char(50) ,sal int 明示削除はできなく、バッチ終了 ,hiredate datetime と同時に削除される ,deptno int ) Insert Into 作成不可

インデックス作成不可 insert into @t

2015/11/1 select row_number() over(order by hiredate desc) as rownum

, * from emp

テーブル変数よりも一時テーブル select * from @t

where rownum between 1 and 3

ユーザー定義 テーブル型

Create Type type1 As Table ( Rownum Int , empno Int , empname Char(50) , sal Int , hiredate DateTime , deptno Int ) Go Declare @t type1 Insert Into @t

Select Row_Number() Over(Order By hiredate Desc) As Rownum , * From emp

Select * From @t Where Rownum Between 1 And 3

◆削除はDrop で Drop Type type1

◆Type作成は Go でバッチ区切り

繰返し例

Declare @x Int = 1 While @x <= 3 Begin

Select * From emp Where empname Like '%' + Choose(@x, 'geof', '忍', 'ゆかり') + '%'

Set @x += 1 End

CTE（Common Table Expression）：共通テーブル式

Go

With CTE1 As (

Select Row_Number() Over(Order By hiredate Desc) As RowNum , * From emp

2015/11/1 )

Select * From cteTest1 Where RowNum Between 1 And 3

一時テーブルやテーブル変数と同じくSelect結果を名付け インラインビューの置き換え（可読性向上）

IF使用繰り返し例

Declare @x Int = 1, @y1 VarChar(100) ,@y2 VarChar(100) ,@y3 VarChar(100) While @x <= 3 Begin If @x = 1 Begin

Select @y1 = empname From emp Where empno = @x End

Else

If @x = 2 Begin

Select @y2= empname From emp Where empno = @x End

Else

If @x = 3 Begin

Select @y3= empname From emp Where empno = @x End

2015/11/1 Set @x += 1

End

Select @y1, @y2, @y3

再帰

Go With cte1(社員番号, 社員名, 上司社員番号, 階層) As ( -- 上司 Select 社員番号, 社員名, 上司社員番号, 0 From 社員

2015/11/1 Where 社員番号 = 1001

Union ALL

-- 部下（再帰）

Select e.社員番号, e.社員名, e.上司社員番号, cte1.階層 + 1 From 社員 As e

Inner Join cte1

On e.上司社員番号 = cte1.社員番号 )

Select * From cte1

Note： CTE と一時テーブル、テーブル変数、インライン ビューとの使い分け CTE は、一時テーブルやテーブル変数と同じように SELECT ステートメントの結果 に対して、名前を付けて保存できる機能です。内部的な動作は、インライン ビュー とほぼ同じなので、単純なクエリの場合は、一時テーブルよりもパフォーマンスが良 く処理される場合が多くあります。 しかし、インライン ビューのときと同様、複雑なクエリになった場合には、逆の結果 になることが多々ありますので、気を付けてください。前述したように、サブクエリの 入れ子が何段階にもなっている複雑なクエリで、かつ結果を何度も利用するような 場合には、（インデックスを付与した）一時テーブルを利用することをお勧めします。 したがって、CTE は、インライン ビューの置き換え（インライン ビューを読みやすく するためなど）として利用したい場合にお勧めの機能です。

なお、CTE では、CTE にしかない特徴として、次の STEP 2.13 で説明する「再帰ク エリ」という利用方法が可能です。再帰クエリを利用する場合には、CTE は大変便 利ですので、ぜひ活用してみてください。

SQL 2015/11/1 Note： テーブル変数と一時テーブルの使い分け テーブル変数は、一時テーブルと比べて、次の制限事項があります。 このようにテーブル変数は、一時テーブルと比べて、利用が面倒なのと、パフォー マンス関連（インデックスが作成できない点とパラレル処理の対象とならない点）で 一時テーブルよりも劣ります。単純な処理であれば、速度はほとんど変わりません が、クエリ結果に対してインデックスを作成してチューニングしたい場合には、テー ブル変数を利用することができません。 したがって、テーブル変数を一時テーブルの置き換えとして利用しようと考えている 場合は、置き換えることはせず、一時テーブルを利用することをお勧めし

ます。

もちろん、単純な処理を記述する場合には、テーブル変数は便利ですし、次の STEP で説明するユーザー定義テーブル型として利用する場合には、大変便利な 機能です。 Tips： インライン ビューと一時テーブルの使い分け インライン ビューと一時テーブルでは、単純なクエリの場合は、インライン ビューの ほうがパフォーマンスが良い場合が多いのですが、複雑なクエリ（サブクエリが 5階 層、6階層と、何段階もの入れ子になって利用されるようなケース）の場合には、一 時テーブルを利用したほうが（筆者の経験的には）パフォーマンスが良い場合が多 くなります。 インライン ビューも、内部的には、一時的な作業テーブルを作成しているので、一 時テーブルの場合とほとんど同じ内部処理になるのですが、サブクエリが何段階も の入れ子になっている場合は、クエリを解析して実行プラン（内部的な実行方法）を 選択する「クエリ オプティマイザー」が、最適ではない遅い実行プランを選択してし まうことがあります。 これは、SQL Server に限った話ではなく、Oracle や DB2 でも同様で、複雑なクエ リになった場合は、クエリ オプティマイザーの動作に限界があるためです。 弊社の案件では、クエリの長さが 20KB（2万文字）以上にもなるサブクエリで、印刷 すると10ページにもなるようなクエリを扱ったことがありますが、このクエリは、オプ ティマイザーが実行プランを選択するフェーズ（初回のコンパイル フェーズ）だけ で、20秒以上もの時間がかかり、かつ選択された実行プランも最適なものではな い、遅い実行プランでした。 このクエリに対しては、一時テーブルを利用してシンプルに記述し、その一時テーブ ルへインデックスを作成するなどして、パフォーマンス向上を実現しました（同じ結 果を何度か再利用する場合には、一時テーブルへインデックスを作成することでパ フォーマンスを向上させることができます）。 このようなクエリは、メンテナンス性も非常に低く、実際のクエリ作成者以外が見た ときに、誰も理解できないクエリ（誰も改修できないクエリ）となってしまいますので、 こういった状況にならないよう、一時テーブルなどを利用して、シンプルなクエリを記 述することをお勧めします。 CTE は、一時テーブルやテーブル変数と同じように SELECT ステートメントの結果 に対して、名前を付けて保存できる機能です。内部的な動作は、インライン ビュー とほぼ同じなので、単純なクエリの場合は、一時テーブルよりもパフォーマンスが良 く処理される場合が多くあります。 しかし、インライン ビューのときと同様、複雑なクエリになった場合には、逆の結果 になることが多々ありますので、気を付けてください。前述したように、サブクエリの 入れ子が何段階にもなっている複雑なクエリで、かつ結果を何度も利用するような 場合には、（インデックスを付与した）一時テーブルを利用することをお勧めします。 したがって、CTE は、インライン ビューの置き換え（インライン ビューを読みやすく するためなど）として利用したい場合にお勧めの機能です。

なお、CTE では、CTE にしかない特徴として、次の STEP 2.13 で説明する「再帰ク エリ」という利用方法が可能です。再帰クエリを利用する場合には、CTE は大変便 利ですので、ぜひ活用してみてください。

SQL 2015/11/1

ストアドプロシージャ

まとめて処理したいデータベース操作を1つのオブジェクトとして保存 テーブルに対するユーザからの操作を拒否可能 任意のアプリから呼び出し可能 コンパイル済みの実行プラン（クエリ オプティマイザ選択）をキャッシュ格納

ストアド パラメータ

以下2つの方法

①イコール Exec Proc1 @Prm1 = 10, @Prm2 = 'aaa' ②カンマ区切り Exec Proc1 10, 'aaa'

ストアド作成

Go

Create Procedure Proc1 As

Select * From emp Where deptno = 20

パラメータ付きに変更

Go

Alter Procedure Proc1

@Param1 Int '@Param1 Int = 10 ⇒ 初期値設定

As

Select * From emp Where deptno = @Param1

インライン ビューと一時テーブルでは、単純なクエリの場合は、インライン ビューの ほうがパフォーマンスが良い場合が多いのですが、複雑なクエリ（サブクエリが 5階 層、6階層と、何段階もの入れ子になって利用されるようなケース）の場合には、一 時テーブルを利用したほうが（筆者の経験的には）パフォーマンスが良い場合が多 くなります。 インライン ビューも、内部的には、一時的な作業テーブルを作成しているので、一 時テーブルの場合とほとんど同じ内部処理になるのですが、サブクエリが何段階も の入れ子になっている場合は、クエリを解析して実行プラン（内部的な実行方法）を 選択する「クエリ オプティマイザー」が、最適ではない遅い実行プランを選択してし まうことがあります。 これは、SQL Server に限った話ではなく、Oracle や DB2 でも同様で、複雑なクエ リになった場合は、クエリ オプティマイザーの動作に限界があるためです。 弊社の案件では、クエリの長さが 20KB（2万文字）以上にもなるサブクエリで、印刷 すると10ページにもなるようなクエリを扱ったことがありますが、このクエリは、オプ ティマイザーが実行プランを選択するフェーズ（初回のコンパイル フェーズ）だけ で、20秒以上もの時間がかかり、かつ選択された実行プランも最適なものではな い、遅い実行プランでした。 このクエリに対しては、一時テーブルを利用してシンプルに記述し、その一時テーブ ルへインデックスを作成するなどして、パフォーマンス向上を実現しました（同じ結 果を何度か再利用する場合には、一時テーブルへインデックスを作成することでパ フォーマンスを向上させることができます）。 このようなクエリは、メンテナンス性も非常に低く、実際のクエリ作成者以外が見た ときに、誰も理解できないクエリ（誰も改修できないクエリ）となってしまいますので、 こういった状況にならないよう、一時テーブルなどを利用して、シンプルなクエリを記 述することをお勧めします。

2015/11/1

パラメータ入力チェックにNull使用

Go

Alter Procedure Proc1 @Param1 Int = Null As If @Param1 Is Null Begin Print 'パラメーター未入力' End Else Begin

Select * From emp Where deptno = @Param1 End

Exec Proc1 10

上記はReturn強制終了でも可能

Go

Alter Procedure Proc1 @Param1 Int = Null As If @Param1 Is Null Begin Print 'パラメーター未入力' Return End

Select * From emp Where deptno = @Param1 この場合、Elseif は不要に

ADOからSQL Server コマンド実行

Docmd.Runsql ⇒

Cn.Execute ("Alter Table sampleDB.dbo.t1 Add F3 VarChar(100)")

ADOからパラメータ付きストアドレコードセット

Rs.Open "sampleDB.dbo.Proc1 10", Cn, adOpenKeyset, adLockOptimistic or

Rs.Open "sampleDB.dbo.Proc1 @Param1=10"

2015/11/1

ストアドでIn演算子を利用するのに動的SQL使用の場合

Go

Create Procedure Proc2 @Param1 Varchar(100) As

Exec('Select * From emp Where empno In (' + @Param1 + ')')

Exec Proc2 @Param1 = '1, 10'

ストアド In 条件, ユーザー定義テーブル型使用

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)

Declare @v Valuelist Insert Into @v (Val) Values(1), (5)

Exec Proc3 @v

Drop Procedure Proc3 Drop Type Valuelist

2015/11/1

ストアド中にCTEを入れた例

Go

Alter Procedure Proc2 @Param1 Int = 4 As

With Cte1(empno, empname) As (Select empno, empname From emp)

Select empno, empname From Cte1

Exec Proc2 10

パラメータ付きストアド例

Go

Alter Procedure Proc3

@F1 VarChar(100) = 'empno'

, @F2 VarChar(Max) = ',empname, deptno' , @no Int =10

As

Exec('Select ' + @F1 + @F2 +

' From emp Where deptno = ' + @no)

Exec Proc3 'empno', ',empname,sal,deptno', 10

Output

Go

Create Procedure Proc4 @Param1 Int,

@Param2 Int Output As

Select * From emp Where deptno = @Param1

Select @Param2 = @@RowCount Declare @Out1 Int

Exec Proc4 10, @Out1 Output

2015/11/1

Exists

Select * From emp x Where Not Exists (

Select * From 社員 y Where x.empno = y.社員番号 )

Exists と Not Exists はWhereで

アイデンティティ, シーケンス（Sequence オラクル）, オートナンバー

Select Scope_Identity()

最後にインサートしたオートナンバー

Go

Alter Procedure Proc4 @Param1 Int

, @Param2 Int Output As

Set NoCount On ← ADO用 Insert Into idTest(b) Values(@Param1) Select @Param2 = Scope_Identity()

Declare @Out Int

Exec Proc4 11, @Out Output Select @Out

Select * From idTest

ADOから実行する場合のサンプル SQL = _

" Declare @Out Int " & Chr(13) & _

" Exec Proc4 " & n & ", @Out Output " & Chr(13) & _ " Select @Out;"

Set Rs = Cn.Execute(SQL) Range("A1") = Rs(0)

2015/11/1

Returnによる数値指定取得

Go

Alter Procedure Proc6 @Param1 Int = Null As If @Param1 Is Null Begin Print '未入力' Return(99) End

Select * From emp

Where deptno = @Param1 Return(0)

Declare @Ret Int Exec @Ret = Proc6

Select @Ret ←強制終了で99, 正常終了で0を返す

プロシージャー削除

Drop Proc Proc1

Drop Procedure Proc1 Procedure は Proc 省略可能

ストアド変更

ストアド右クリック ⇒ 変更

ロールバック

Go

Alter Procedure Proc1 @Param1 Int

As Xact = Transact

Set Xact_Abort On ← 制約違反ロールバック開始

Begin Tran ← トラン明示開始

Insert Into tranTest Values (@Param1, 999) Insert Into tranTest Values (1, 999) Insert Into tranTest Values (11, 999)

Commit Tran ← トラン終了

2015/11/1

例外処理

Go

Alter Procedure Proc1 @Param1 Int

As

Begin Try Begin Tran

Insert Into tranTest Values (@Param1, 999) Insert Into tranTest Values (11, 999) Commit Tran End Try Begin Catch RollBack Tran End Catch

エラー

ERROR_NUMBER エラー番号 ERROR_MESSAGE エラーメッセージ ERROR_SEVERITY エラーの重大度レベル ERROR_STATE エラーの状態番号 ERROR_LINE エラーが発生した行番号 ERROR_PROCEDURE エラーが発生したストアドプロシージャ またはトリガーの名前 Go

Alter Procedure Proc1 @Param1 Int

As

Begin Try Begin Tran

Insert Into tranTest Values (@Param1, 999) Insert Into tranTest Values (11, 999) Commit Tran End Try Begin Catch RollBack Tran Select Error_Number() , Error_Message() , Error_Severity() エラーの重要度レベル End Catch

2015/11/1

エラー処理

Go

Alter Procedure Proc1 @Param1 Int

As

Begin Tran

Insert Into tranTest Values (@Param1, 999) If @@Error <>0 Goto Er

Insert Into tranTest Values (11, 999) If @@Error <>0 Goto Er Commit Tran Return @Error ⇒ 成功で【0】 Er: Rollback Tran Return

Throw による同エラー再処理

Go

Alter Procedure Proc1 @Param1 Int

As

Set Xact_Abort On Begin Try

Begin Tran

Insert Into tranTest Values (@Param1, 999) Insert Into tranTest Values (1, 999)

Commit Tran End Try Begin Catch Throw Rollback Tran End Catch Set Xact_Abort Off

複数セレクトに対するレコードセット（ADOから）

Set Rs = Rs.NextRecordset

2015/11/1

エラーの登録

sp_addmessage 50001 , 16 ←規定の空き重要度レベル , 'エラーテスト3' ←エラーメッセージ , 'us_english' ←言語 , 'False' ←Windowsログへ記録 , 'Replace' ←新規作成時は不要 RaisError(50001, 16, 1)

Select * From sys.messages sp_dropmessage 50001, 'us_english'

オブジェクト依存関係

Select Object_Name(referencing_id) , referenced_entity_name As 依存元 , * From sys.sql_expression_dependencies

オブジェクト列名まで

Select referenced_entity_name As 依存元 , referenced_minor_name As 依存元の列名 , * From sys.dm_sql_referenced_entities ('dbo.empView', 'Object')

オブジェクト依存関係GUI表示

2015/11/1

空間データ型 （平面と球体）

データアクセス

第一チェック = SQL Server への接続（ログイン認証） 第二チェック = データベースへの接続 第三チェック = オブジェクト操作

GUIデザイン

エディタ右クリックから ⇒

混合モード

データベースで右クリック ⇒ プロパティ

SQL Server認証設定（第一）

セキュリティ中のログインで右クリック ⇒ 新しいログイン

2015/11/1

データベース接続（第二チェック）権限付与

セキュリティ ⇒ ログイン ⇒ ユーザ名をWクリック 　└ 設定はアドインで行う必要有

オブジェクト権限付与（第三）

ストアドやテーブルを右クリック ⇒ プロパティ ⇒ 全般 ⇒ 検索 選択 = Select

Windowsボタン + Xキー = コンピュータの管理（管理ツール）等表示

2015/11/1

グループの追加

Windowsボタン + X ⇒ コンピュータの管理 自習書4_39page sa = System Administrator

アカウントの有効化

SQL Server認証ログインの初期設定ではsaはログイン無効設定

チャーム

Windows8の画面右等にマウスポイントを合わせた時の表示

パスワード設定 （Windows）

管理ツール ⇒ ローカル セキュリティ ポリシー 自習書4_50 page 何回パスワードを間違えるとアカウントロック等の設定

2015/11/1

ロックアウトからの復元

パスワードポリシーで、3回以上ログインミスをした場合など 全般タブから再度新パスワードを設定

固定サーバーロール

セキュリティ ↓ ログイン ↓ 特定ユーザ名をWクリック 登録 = マッピング

SQL でのログインアカウント

Create Login LoginName1 With Password = 'pass1' Drop Login aaa Create Login LoginName2 From Windows

2015/11/1

固定サーバーロールへのメンバー追加

ALTER SERVER ROLE ロール名 ADD MEMBER ログイン名 　└ 自習書4_63 page

GRANT 権限 TO ログイン名 REVOKE 権限 FROM ログイン名

権限一覧

Select * From sys.server_principals

ログイン アカウント = サーバー プリンシパル in SQL Server

階層

特定データベース ⇒ セキュリティ ⇒ ユーザー ⇒ 特定ユーザー └ 特定ユーザー設定 セキュリティ ⇒ ログイン ⇒ [sa, ユーザー名] └ 新しいログイン ⇒ 全般 ⇒ Windows認証 or SQL Server認証 ⇒ 追加（ログイン名, パス設定） 　　　　　　　└ 次回ログインでパス変更 　　　　　　　 ⇒ サーバーロール ⇒ サーバ全体特権 　　　　　　　 ⇒ ユーザーマッピング ⇒ 許可DB設定 　　　　　　　 ⇒ 状態 ⇒ ログイン有効無効, ログインロックアウト 　　　　　　　 ⇒ セキュリティ保護可能なリソース 　　　　　　　 ⇒ サーバーロール ⇒　[sysadmin, diskadmin] 状態 セキュリティ保護可能なリソース サーバロール 全般 ユーザーマッピング

2015/11/1

権限付与（接続）

Use Test1

Grant Connect To Guest Revoke Connect From Guest

データベースユーザーの作成

USE データベース名

CREATE USER ユーザー名 FOR LOGIN ログイン名 [ WITH オプション ]

データベースユーザー一覧の取得

Use Test1

Select * From sys.database_principals

ログイン アカウント = サーバー プリンシパル

データベース ユーザー = データベース プリンシパル

プリンシパル = SQL Server へアクセスするユーザー（アカウント）の総称

データ更新

Update T1 Set F2 = 'BBC' Where F2 = 'aaa'

固定データベースロール

db_owner, public の2つが重要

この場合、Update 更新不可 許可と拒否 同時は拒否優先

2015/11/1

オブジェクト権限

Grant（許可）, Deny（拒否）, Revoke（取り消し）

Grant(許可) と Deny(拒否) は Deny が優先される

テーブルへの権限付与 GUI

オブジェクト権限設定

Grant Select On T1 To Test Grant Insert On T1 To Test Deny Insert On T1 To Test Revoke Insert On T1 To Test

スキーマの確認

データベース内個人プロパティの全般から データベース ⇒ セキュリティ ⇒

　　ユーザー ⇒ 特定ユーザー ⇒ 　　プロパティ ⇒ 全般

2015/11/1

スキーマ概要

Create Table AAA.A (F1 Int)

権限用途

Test ユーザーへ T1 テーブルの Select を Deny さらに、ビュー V1 Select 権限を

複数行の Insert, 2008から

Insert Into T1(F2, F3) Values('A', '1/1/2014') , ('B', '2/2/2014') , ('C', '3/3/2014')

IntelliSense ⇒ 2008から

ANSI SQL-92

DML = Data Manipulation Language Select, Insert, Update, Delete DDL = Data Definition Language

Create, Alter, Drop DCL = Data Contorol Language

Grant, Revoke, Commit, Rollback

2015/11/1

Null の大きさ

SQL Server では Null = 最少値, Oracleでは 最大値

Order By 数値

Select * From T1 Order By 1 Asc

数値はセレクト抽出時の列順番

IsNull = NVL(Oracle) = Nz(Acess)

Having は集計を行った結果に対する絞込み

Select 部門番号, Count(*) From emp Having Count(*) >= 5

Option(Force Order)

Select * From T1 Option(Force Order)

With Rollup

SELECT YEAR(受注日), 区分名, SUM(受注明細.単価 * 数量) FROM 受注明細 INNER JOIN 受注 ON 受注明細.受注コード = 受注.受注コード INNER JOIN 商品 ON 受注明細.商品コード = 商品.商品コード INNER JOIN 商品区分 ON 商品.区分コード = 商品区分.区分コード GROUP BY YEAR(受注日), 区分名 WITH ROLLUP ORDER BY YEAR(受注日), 区分名

With Rollup ⇒ With Cube

Select A, B, C Sum(n) From t

Group By Grouping Sets ( (A, B, C)

, (A, B) , (A, C) , (B, C)

2015/11/1

Pivot

2005から、 Pivot は演算子

データ移動

bcp Test1.dbo.Tbl In "C:\Users\Takehiro Yamada\Desktop\t.txt" /S TAKEHIRO\SQLEXPRESS /T /c /t "," └ スペース含むパスはパス全体をWクォートでくくる └ 文字列はWクォートでくくらない 。くくるとWクォートごとデータ移動

既存列 デフォルト設定

Alter Table Tbl Add Default (100) For [F2int]

Go

バッチ区切りの終了

Declare変数はバッチの間のみ有効 Declare @x Int = 20 , @y Int = 30

Select Cast(@x As VarChar(100)) + '①' Go

Select Cast(@x As VarChar(100)) + '②' ←無効、エラー

Where中 Collate

Select * From emp

Where empname Like '%ﾕｶﾘ%' Collate Japanese_CI_AS

Select * From emp

Where empname Like '%ユカリ%' Collate Japanese_CS_AS_KS_WS

ビューに対して、UPDATE や INSERT、DELETE など、更新系のステートメントを実行することも 可能です。ただし、ビュー内で GROUP BY や集計関数、DISTINCT 処理を行っていたり、演算 結果を行っている列に対しては、更新系のステートメントを実行することはできません。

2015/11/1

Exists

If Exists(Select * From emp)

Print 'データ有り' ← Begin, End 省略タイプ

【Where中】 If Exists(

Select * From emp Where empno = 1 ) Begin Print 'データ有り' End Else Begin Print 'データ無し' End Print '処理終了'

照合順序一覧表示

SELECT * FROM fn_helpcollations() WHERE name LIKE '%japan%'

Access倍精度 = Float

サポート開始年度

2012 EOMonth IIF ⇒Oracle での　DECODE

DateFromParts Choose Format 2005 CTE 　⇒ 関数Oracle比較 = TransactSQL入門100page

グラフィカル実行時プラン表示

Table Scan = 全件検索 クエリデザイナ右クリック ↓ 実際の実行プランを含める

2015/11/1

インデックス作成（非クラスター）

Create Index index_姓 On 社員(姓)

RID = Row ID = 行識別子

インデックス削除

Drop Index 社員.index_姓 or

Drop Index index_姓 On 社員

インデックスの無効化と復元

Alter Index index_姓 On 社員 DisAble Alter Index index_姓 On 社員 ReBuild

2015/11/1

ページとヒープ

データファイルは内部的に8kbのページに区切り ページ = ディスク入出力の単位 800バイトテーブル ⇒ 1ページに10行分格納可能 ページ前提 = ヒープ インデックス自体もデータファイルへ内部的には格納 ルートノード = ルートページ 中間ノード = 中間ページ リーフノード = リーフページ データファイル内の連続した8ページ = エクステント ⇒ テーブルやインデックス 　　に割り当てられる領域

クエリが読み取ったページ数確認

Set Statistics IO On

Set Statistics IO OFF

└ OFF にするか接続を切るまで有効

2015/11/1

ゲージ名称

テーブル・クエリのテキスト出力（SQL Server以外）

SQL = "Select * Into [Test.txt] In '" & Str & "' 'Text;' From [Sheet1$];"

空間クエリ

平面 geom geometry geometry::STGeomFromText('POINT(3 4)', 0) geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0) 2点間距離 DECLARE @g1 geometry; DECLARE @g2 geometry; SET @g1 = geometry::STGeomFromText('POINT(0 0)', 0); SET @g2 = geometry::STGeomFromText('POINT(3 4)', 0); SELECT @g1.STDistance(@g2); 面積 SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0); SELECT @g.STArea() 【立体】Geog DECLARE @g1 geography

SELECT @g1 = geog FROM 郵便局 WHERE id = 1

SELECT * FROM 郵便局 WHERE @g1.STDistance(geog) < 2000 AND id <> 1

空間クエリ, GeoG Insert Intoの中

大文字小文字区分有り！ ⇒ STGeogFrom～ ('東京都中野区', Geography::STGeomFromText('POINT(139.663835 35.707398)', 4612),

2015/11/1

不一致クエリ

Select * From A Where Not Exists(

Select * From B Where A.企業ID = B.企業ID)

Select A.* From A Left Join B On A.企業ID = B.企業ID Where B.企業ID Is Null

Select * From A Except Select * From B

トリガー

Create Trigger Test_main_insert On Test_main_table For Iinsert

As

Insert Into Test_sub_table

( No, Name, Trigger_kind, Trigger_date )

Select No, Name, 1, Getdate() From Inserted Go

Insert Into Test_main_table Values(100, 'Name1') Insert Inserted

Update Inserted Delete Deleted

FOR INSERT, UPDATE, DELETE の書き方も可能

権限まとめ

①接続認証 Create Login aaa With Password = 'aaa' ②データベース接続

（テーブルでない方の大元）セキュリティ ⇒ ログイン ⇒ aaa ⇒ Ddouble Click ⇒ ユーザーマッピング ⇒ 接続先データベース名をチェック選択

↓ 上記手順終了後、下記が可能

Grant Connect To aaa Revoke Connect From aaa ③オブジェクト操作

2015/11/1 表挿入 書式選択（通貨） 行高・列幅調整 Tablixコピー Excelエクスポート サーバへのアップ page76 グラフ一覧 86 グラフ列系追加 セカンダリ設定 92 セカンダリ凡例設定 横・縦軸タイトルの表示と非表示 8