データベース関連

SQL Server関連

SQL一般

SQL関連メモ(Oracle)

関連Web

selectしながらロック

selectの条件に正規表現

  • http://homepage1.nifty.com/kojama/works/rdbms/mssql/sql.html#oterm01
  • 全角半角区別などはコレーション設定の影響があるので注意
    select col from hoge
    --where col like '%[A-Z][0-9]%' --英大文字に数字が連結している
    --where col like '%[^A-Z][a-z]%' --1文字目が英大文字でなく、2文字目以降が英小文字
    --where col like '%[ぁ-ん]%' --ひらがなを含む
    --where col like '%[ァ-ン]%' --全角カタカナを含む
    --where col like '%[亜-龠]%' --漢字を含む

日付型を文字列に変換

現在日付

  • CURRENT_TIMESTAMPを使う
  • あるいは Select GETDATE()

文字列の中に改行コードがあるかの条件

列名 like '%'+char(10)+'%'

文字列の中に数字以外の文字があるかを判定

  • SQL Serverにはtranslate()がないので↓のようにする
    len(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(kekka,'0', '') , '1', ''), '2', ''),'3',''), '4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'.','')
    ) > 0
  • もっといい方法ないのか?

ストアドプロシージャでファイル出力する

  • http://msdn2.microsoft.com/ja-jp/library/ms175046.aspxより
  • 次の例では、@var 変数の内容を、現在のサーバー ディレクトリにある var_out.txt というファイルに書き込みます。
    DECLARE @cmd sysname, @var sysname
    SET @var = 'Hello world'
    SET @cmd = 'echo ' + @var + ' > var_out.txt'
    EXEC master..xp_cmdshell @cmd
  • oracleのような組み込みコマンドはないらしい。よってシェルを使うらしい。

先頭のxx件だけ取得したいとき

  • 以下のようにする
    SELECT TOP 100 * FROM <table>

外部結合とLEFT JOINは微妙に動作が違う!?

  • 外部結合
    select * from
    (
    	select a.ptsid, isnull(cnt,0) cont 
    	from
    		pt_basic a,
    		(select ptsid, Count(*) cnt from exam_basic 
    			WHERE EXDATE>='20060101' and EXDATE<='20060630' group by ptsid
    		) b
    	where a.ptsid *= b.ptsid  
    ) a
    where cont=0
  • LEFT JOIN
    select * from
    (
    	select a.ptsid, isnull(cnt,0) cont 
    	from
    		pt_basic a LEFT JOIN
    		(select ptsid, Count(*) cnt from exam_basic 
    			WHERE EXDATE>='20060101' and EXDATE<='20060630' group by ptsid
    		) b	
    	ON a.ptsid = b.ptsid  
    ) a
    where cont=0
  • この場合、cont=0という条件で絞った結果が欲しいのだが、外部結合だと全行が返ってくる。バグ?
  • (SQL Server2000の場合)
  • ヘルプの「*=」の項目に以下のような記述があるため、できるだけ LEFT JOINを使った方が良さそう
    Microsoft潤・SQL Server潤・の以前のバージョンでは、左外部結合と右外部結合の条件は
    WHERE 句で *= 演算子および=* 演算子を使用して指定していました。この構文を使用すると、
    何とおりにも解釈できるあいまいなクエリに
    なることがあります。FROM 句で SQL-92 に準拠した外部結合を指定すれば、解釈があいまいに
    なることはありません。
    SQL-92 構文の方が厳密なので、WHERE 句で従来の Transact-SQL 外部結合構文を使用する方法に
    ついてはこのリリースでは説明していません。SQL Server の今後のバージョンではこの構文は
    サポートしなくなる予定です。
    Transact-SQL 外部結合を使用しているステートメントは SQL-92 構文を使用するように
    変更する必要があります。
    
    SQL-92 規格では、FROM 句または WHERE 句のどちらでも内部結合を指定できます。WHERE 句で
    内部結合を指定すると、Transact-SQL 外部結合構文で生じたようなあいまいさは生じません。

DB情報取得系

  • 現在日付取得
    • getdate() もしくは current_timestampを使う
  • サーバ情報取得
    select * from sysservers
  • データベース名取得
    select name from sysdatabases
  • ユーザテーブル名取得
    select * from sysobjects where type='U' order by name
  • テーブルの列情報取得
    select  * from syscolumns where id = object_id('table_name')
  • ある名前の列を持つテーブルを列挙する
    select a.name tname, b.name cname from 
    syscolumns a, sysobjects b
    where a.name='col_name' and a.id=b.id
  • テーブルに従属するオブジェクト類を得る
    select * from sysobjects where parent_obj=object_id('table_name')
    • プライマリキー xtype='PK'
    • 外部参照 xtype='F'
  • 制約ビュー sysconstraints
    • 以下のようなビューになっている
      SELECT constid = CONVERT(int, id), 
             id = CONVERT(int, parent_obj), 
             colid = CONVERT(smallint, info), 
             spare1 = CONVERT(tinyint, 0), 
             status = CONVERT(int, 
                       CASE xtype WHEN 'PK' THEN 1 
                                  WHEN 'UQ' THEN 2 
                                  WHEN 'F' THEN 3 
                                  WHEN 'C' THEN 4 
                                  WHEN 'D' THEN 5 
                                  ELSE 0 
                       END + 
                       CASE WHEN info != 0 THEN (16)    ELSE (32) 
                       END + 
                       CASE WHEN (status & 16) != 0 THEN (512)  ELSE 0 
                       END + 
                       CASE WHEN (status & 32) != 0 THEN (1024)  ELSE 0 
                       END + (2048) + 
                       CASE WHEN (status & 256) != 0 THEN (16384) ELSE 0 
                       END + 
                       CASE WHEN (status & 512) != 0 THEN (32767) ELSE 0 
                       END + 
                       CASE WHEN (status & 4) != 0 THEN (131072) ELSE 0 
                       END + 
                       CASE WHEN (status & 1) != 0 THEN (1048576) ELSE 0 
                       END + 
                       CASE WHEN (status & 1024) != 0 THEN (2097152) ELSE 0 END), 
             actions = CONVERT(int, 4096), 
             error = CONVERT(int, 0)
      FROM   sysobjects
      WHERE  xtype IN ('C', 'F', 'PK', 'UQ', 'D') AND (status & 64) = 0

情報検索操作系

  • 日付型のフォーマットを指定する
    convert(varchar,date,112) --YYYYMMDDの形式
  • データ型の変換
    • CONVERT()関数を使う。 CONVERT(float, <exp>)みたいな感じ
  • 2つの日付の差分を取る
    • datediff()関数を使う
      select manid,datediff(year,birth,getdate()) age from <table_name>
  • NULLだったらゼロを返す
    select *,((f1+f2+f3+f4+f5)*ryo) fuk
    from(
    	select ptsid,exdate,a.ykcd,b.ryo,
    	isnull(ksufuk,0) f1,
    	isnull(asafuk,0) f2,
    	isnull(hirufuk,0) f3,
    	isnull(yorufuk,0) f4,
    	isnull(ssnfuk,0) f5,
    	yoho,yoryo 
    	from ykzi_info a, tmykzi b
    	) c
    ) d
    みたいにする
  • 文字列結合
    select (JNM_SEI + ' ' + JNM_MEI) as simei from hoge_tbl;

DB定義操作系

  • ある列を後からプライマリキーに指定する
    ALTER TABLE <table_name> ADD constraint <pk_name> PRIMARY KEY (<col_name>)
    最後の列名の指定には()が必ず必要な模様なので注意
  • 指定した制約を無効化する
    ALTER TABLE <table_name> NOCHECK CONSTRAINT <const_name>
  • VIEWがあったときだけドロップする
    if exists (select * from dbo.sysobjects where id = object_id(N'<ビューの名前>')
     and OBJECTPROPERTY(id, N'IsView') = 1) 
         drop view <ビューの名前>
  • 列の属性変更
    ALTER TABLE <tablename> ALTER column <col_name> char(15) Not null
    みたいな感じ。ただし制約があると失敗するのであらかじめ制約をなんとかしないといけない
  • テーブルの列追加
    alter table <table_name> ADD <column_name> INTEGER DEFAULT 0  not null
    みたいな感じ。
    • 列の追加位置はSQL Serverでは指定できないようで、tableを作り直すとかしないとダメらしい(本当か?)
    • ちなみにMySQLであれば before <col_name>, after <colname> という指定ができる
  • テーブルの列削除
    alter table <table_name> drop column <column_name>
    ただしこれだと制約をあらかじめ削除しておかないと失敗する
  • テーブルの列名変更
    exec sp_rename '<table_name>.<col_name>', '<new_col_name>';
  • index作成
    create unique NONCLUSTERED index Index名 on テーブル名(列1,列2)
    • クラスターIndexはIndexの為のテーブルを作成せずに実際のテーブルを指定された列でソートして管理する。
    • そのため各テーブルにクラスターIndexは1つだけ
  • index削除
    drop index テーブル名.Index名

システムストアドプロシージャ

  • コネクションとそのステータスの一覧を得る
    sp_who
  • 特定のテーブルのサイズを得る  sp_spaceused <table_name>
    • やや高度な応用技
      CREATE TABLE #temp (
      TABLE_NAME sysname,
      rows sysname,
      reserved sysname,
      data varchar(32),
      index_size varchar(254),
      unused varchar(100)
      )
      
      INSERT INTO #temp EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'"
      SELECT * FROM #temp order by table_name
  • テーブル一覧取得
    sp_tables
  • テーブルの列情報取得
    sp_columns <table_name>
  • テーブルのプライマリキー情報取得
    sp_pkeys <table_name>

あるDBのバックアップからの別のDBへのリストア

RESTORE DATABASE <TO_DB>
FROM DISK='FROM_DBバックアップファイル' WITH REPLACE,
MOVE 'FROM_DBデータ論理名' TO 'TO_DBデータ物理ファイルフルパス.mdf',
MOVE 'FROM_DBログ論理名' TO 'TO_DBログの物理ファイルフルパス.ldf'

みたいな感じ。リストア先のDBはREPLACEをつけておけばあってもなくてもOK

  -- 読み込みのDISKのファイル名
  SELECT  @bkfname = 'D:\backup7\MySampleTest.DAT'
  --データベースの名前
  SELECT  @dbname2  = 'MySampleCopy'

  RESTORE  DATABASE  @dbname2   --復元したDBの名前
        FROM  DISK = @bkfname   --保存先ファイル名
        WITH
             REPLACE             --同じ名前のDBがあれば、既存を削除
           , MOVE 'MySampleTest_Data' TO 'd:\test\MySampleCopy.mdf'
           , MOVE 'MySampleTest_Log'  TO 'd:\test\MySampleCopy.ldf'
  • 論理ファイル名はRESTORE FILELISTONLYで返されるレコードセットで取得できる
    RESTORE FILELISTONLY FROM DISK='<backup_file_name>'

データベースファイルを圧縮する

DBCC SHRINKDATABASE
   ( database_name [ , target_percent ]
       [ , { NOTRUNCATE | TRUNCATEONLY } ]
   )

肥大したLogファイルを縮小する

BACKUP LOG [DatabaseName] WITH NO_LOG
DBCC SHRINKDATABASE ([DatabaseName], TRUNCATEONLY)
DBCC SHRINKFILE ([DatabaseName]_log, 2, TRUNCATEONLY)

コマンドラインからのSQL Serverへの接続

sqlcmd -S サーバー名 -U ログインユーザー名 -P パスワード
  • :QUITで終了
  • ※−i でファイル指定
  • ※サーバー名はコンピュータ名\SQLEXRESSなどだがコンピューター名の代わりにlocalhostでもOK

データファイル、ログファイルからデータベースを新規作成

create database データベース名
on primary
(
filename = 'データファイルのフルパス'
)
log on
(
filename = 'ログファイルのフルパス'
)
for attach

データファイルが複数ある時は

on primary
(
filename = 'データファイルのフルパス'
)
, 
(
filename = 'データファイルのフルパス'
)

となり、必ずひとつはprimaryを指定する。

デタッチ→アタッチしたDBのユーザをログインと結びつける

  • 参考:http://support.microsoft.com/kb/274188/
  • アタッチしたDBが hogeDB ユーザ名が hoge とする
  • 結びついてないユーザの確認
    Use hogeDB
    go
    sp_change_users_login 'report'
    ユーザhogeが表示されるはず
  • ユーザとログインの結びつけ
    • まずmanagement Studio でセキュリティの下に hoge ログインを作成する
    • 以下のコマンドを実行する
      Use hogeDB
      go
      sp_change_users_login 'update_one', 'hoge', 'hoge'

ログインの作成

create login
db_user with password = 'db_user',
default_database = デフォルトのデータベース,
check_policy = OFF
  • ※デフォルトのデータベースを指定しない時はmasterがデフォルトのデータベースとなる。
  • ※パスワードを簡単にしたりするにはcheck_policy = OFFが必要。

ユーザーを作成

  • 以下ではユーザーhoge_userをログインユーザーdb_userで作成
    create user hoge_user for login db_user with default_schema = dbo 

ロールにユーザーを追加

  • 以下ではデータベースロールdb_ownerにユーザー
    exec sp_addrolemember 'db_owner', 'hoge_user'

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2009-06-23 (火) 21:00:45 (4542d)