宣言と同時代入は 2008 から int = 888 mysql 単純表記 select 10 f1, 20 f2, 'aaa' f3 1 行代入 = = = 'AAA' Set からの代入は複数不可 バッチ (go) と変数の範

Transcription

1 エラー一覧 表示 エラー一覧からリスト表示可能 mysql バックアップ リストア 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 オブジェクト権限の付与 - 1 -

2 宣言と同時代入は 2008 から int = 888 mysql 単純表記 select 10 f1, 20 f2, 'aaa' f3 1 行代入 = = = 'AAA' Set からの代入は複数不可 バッチ (go) と変数の範囲 T-SQL のローカル変数はバッチの範囲のみ有効 バッチの区切りは "go" クエリエディタの行番号 ツール オプション テキストエディタ Transact-SQL 全般 表示 行番号 IF 文 Case 文 If (Datepart(hour, Getdate() ) < 12) int = Datepart(hour, Getdate() ) Begin Select Print ' おはよう ' Case End <12 Then ' おはよう ' Else < 17 Then ' こんにちは ' Begin Else ' こんばんは ' Print ' こんにちは ' End End (Select 直後に "@msg =" を付与し変数代入も可 ) インクリメント演算子 += 1 While 文 int = 1 <= 5 Begin += 1 End Goto によるジャンプ Goto test test: IF 文や While 文中も可能 Sleep Waitfor Delay '00:00:03' Select * From emp - 2 -

3 照合順序 カナ, 全半角, 大小文字, アクセント kana, byte, case, accent CS_AS_KS_WS ( デフォルト = Japanese_CI_AS) C Case S Sensitive A Accent I Insensitive K Kana W Wide mysql 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-3 -

4 データ型 char 固定長文字列 (8000 バイトまで ) varchar 可変長文字列 (8000 バイトまで ) varchar(max) メモ型 (2G まで ), nvarchar(max) も可 nchar Unicode 対応の固定長文字列 (4000 文字まで ) nvarchar Unicode 対応の可変長文字列 (4000 文字まで ) tinyint 0 ~ 255 smallint -31,769 ~ 32,767 int ~ = 2,147,483, 億 bigint ~ = decimal(p, s) 新数型, p: 全体桁, s: 小数点以下桁 numeric(p, s) でも可能 float(n) real smalldatetime datetime date time datetime2 datetimeoffset smallmoney money 概数型, 精度 =8-15 桁, ac 単精度浮動小数型 概数型, 精度 =1-7 桁, ac 倍精度浮動小数型 概数とは切上 切捨 四捨五入などによって表せられるおおよその数 分単位 3.33 ミリ秒単位 日単位 時間のみを格納 100 ナノ秒単位 100 ナノ秒単位 100 ナノ秒単位かつタイムゾーンも可能 範囲小 範囲大 binary 固定長バイナリ (8000 バイトまで ) varbinary 可変長バイナリ (8000 バイトまで ) varbinary(max) bit 2G まで 1 or 0, yes/no 型 table, XML, geometory, geography 9,223,372,036,854,780,000 mysql 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と同様 複数形注意 - 4 -

5 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 の少数無しに mysql GetDate よりも Oracle で仕様可能な "Current_TimeStamp" を使用 関数 GETDATE SYSDATETIME SYSDATETIMEOFFSET 役割 現在の日付と時刻を取得 (datetime データ型対応 ) CURRENT_TIMESTAMP と記述しても可 GETDATE の datetime2 データ型対応版 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 CHAR 役割大文字 小文字変換右 / 左から半角スペースの削除文字列の置換右 / 左から部分抽出部分抽出文字列の長さ文字列のバイト数文字列内の検索文字の ASCII コード取得 ASCII コードを文字変換 Print Substring('abcde', 3, 2) 上記は "cd" Print CharIndex('cd', 'abcde') 上記返り値は 3 SQL Server ROUND POWER RAND CEILING(n) FLOOR(n) SQRT 役割四捨五入べき乗乱数 n に対してそれ以上の最小の整数 n に対してそれ以下の最大の整数平方根 T-SQLの IsNull は Oracle のNVL accのnz Select IsNull(empno, 'aa') as empno2 From emp IIF は Oracleの Decode Choose 関数で曜日を取得することも1 案, (DatePart(weekday, から ) - 5 -

6 ユーザー定義関数 CREATE FUNCTION TRIM varchar(100) ) RETURNS varchar(100) AS BEGIN END RETURN RTRIM( LTRIM(@param1) ) CREATE FUNCTION 関数名 ( [@ パラメーター名データ型 ], ) 使用する時は "dbo." を付与 select dbo.trim(empname) from emp ユーザー定義関数はプログラミング スカラー関数に 削除は Drom Function dbo.trim mysql ここから応用編 ローカル変数へ select 代入 datetime = = hiredate from emp where empno = ローカル変数への代入は1レコードのみ whereなどで絞る パラメータ化, exec varchar(20) = 'emp' Exec ('select * from ' sp_executesqlによる動的 SQL ( システムストアドプロシージャー ) varchar(10) = 'emp' = N'Select * From ' Exec sp_executesql は文字列連結が完了していることが前提 そのため下記は不可 Exec sp_executesql N'Select * From ' sp_executesql のパラメーター化 exec sp_executesql N'Select * from emp Where empname And sal N'@p1 = '% 田 = 左記でも可能 Exec と sp_executesql は後者がパフォーマンスが良い sp_executesql のパラメーターはあくまで Where テーブル名や列名は文字列として - 6 -

7 トップ句にはパレンを付与する int = 3 Select Top 3 * From emp Select Top (@x) * From emp ただし accはパレン不可 Select * From emp (MySQL は語尾に Limit 5) mysql 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 から明示削除も可能 - 7 -

8 ソート Select From emp 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, * mysql これらは関数 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 テーブル変数 Table テーブル変数は一時テーブルに比して ( rownum int 1. 配列代替として利用, empno int 2. Select * Into でのテーブル作成不可, empname char(50) 3. テーブル変数へインデックス作成不可, sal int 4. 複数 CPUコアがあってもパラレル処理にならない, hiredate datetime 5. 有効範囲がバッチ内のみ ( 接続中のみ有効 ), deptno int ) Insert Select Row_Number() Over(Order By hiredate Desc) rownum, * From emp Select * Where rownum Between 1 And 3-8 -

9 ユーザー定義テーブル型 (User-Defined Table Type) (2008 から ) Create Type type1 As Table go type1 Insert ( rownum int DB 内へ永続化される, empno int ) 削除は Drop Type から mysql ストアドパラメーターに利用 Select Row_Number() Over(Order By hiredate Desc) rownum, empno From emp Select * 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. 社員番号 ) SELECT * FROM cte1-9 -

10 ストアドプロシージャー ( 最基本 ) go CREATE PROCEDURE proc1 AS SELECT * FROM emp WHERE deptno = 20 mysql ストアド変更, パラメータ付き ALTER PROCEDURE int int = 10 AS SELECT * FROM emp WHERE deptno EXEC または EXEC proc1 20 パラメータのnull 判定 ALTER PROCEDURE int = NULL AS Return で強制終了 IS NULL BEGIN BEGIN PRINT ' パラメーター未入力!' PRINT ' パラメーター未入力!' RETURN END END ELSE Return(0), Return(1) も可 BEGIN SELECT * FROM emp WHERE deptno は1つ前のSQL 実行時に影響のあった行数を返すシステム関数 出力パラメーター (Output) CREATE PROCEDURE int, int OUTPUT AS SELECT * FROM emp WHERE deptno 事前設定 = = システム関数 int EXEC proc4 OUTPUT 実行時

11 テーブル値パラメーター (Table-Valued Parameters), In 句のパラメーター化 -- ユーザー定義テーブル型 go CREATE TYPE valuelist AS TABLE ( val int ) go -- テーブル値パラメーターを利用するストアドプロシージャ CREATE PROCEDURE valuelist READONLY AS SELECT * FROM emp WHERE empno IN ( SELECT val ) 事前 - タイプ作成 mysql 事前 - プロシージャー作成 -- の宣言 AS valuelist 実行時タイプ宣言 -- へ値の格納 (IN 演算子に与える値 ) タイプにパラメータ値代入 INSERT (val) タイプパラ入りストアドの実行 VALUES ( 1 ),( 5 ) -- ストアドプロシージャの実行 EXEC 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 ("~ 行処理されました " をレコードセットとしない int int,@p2 int OUTPUT EXEC proc5 OUTPUT AS SET NOCOUNT ON INSERT INTO idtest VALUES(@p1) = SCOPE_IDENTITY() SELECT * FROM idtest

12 Return コード = proc6 Return のデフォルト値はゼロ mysql ストアド取得 Select definition From sys.sql_modules where definition Like '%proc%' データベース毎に異なるので use からDB 指定データベース中の プログラミング ストアドプロシージャー からも Set Xact_Abort On, 全体ロールバック規定では OFF ステートメントエラーが発生した場合もロールバックを行う ALTER PROCEDURE int AS SET XACT_ABORT ON BEGIN TRAN INSERT INTO trantest VALUES 999 ) INSERT INTO trantest VALUES ( 1, 999 ) COMMIT TRAN SET XACT_ABORT OFF ここだけエラーの場合 1 行上コードもロールバック 2 つのレコードセット from ADO strsql = "Set NoCount On Select * From emp;

13 - 13 -

14 - 14 -

15 - 15 -

16 - 16 -

17 - 17 -

18 - 18 -

19 - 19 -

20 - 20 -

21 - 21 -

22 - 22 -

23 - 23 -

24 - 24 -

25 - 25 -

26 - 26 -

27 - 27 -

28 - 28 -

29 - 29 -

30 - 30 -

31 - 31 -

32 - 32 -

33 - 33 -

34 - 34 -

35 - 35 -

36 - 36 -

37 - 37 -

38 - 38 -

39 - 39 -

40 - 40 -

41 - 41 -

42 - 42 -