SQLサーバー入門

(2006.11 更新)

以下に進む前に、SQLサーバ用のパスワードを受取ってください

演習課題１：

SQLサーバに接続し、以下を行う。

SQLサーバーの利用

演習室PCとは別の場所にあるSQLサーバにネットワークで接続し、サーバー上のデータベースで共同作業を行う。

ＳＱＬサーバー接続用のアカウントとパスワードの用紙を受け取る。
なお、この用紙についてを紛失・落とす・忘れてくる（次回も使用する）などの管理不行き届きがある場合は、データベース演習の単位は「不可」とする。
ログイン用パスワードで、ＳＱＬサーバーに接続する。
1. スタート→プログラム→コマンド・プロンプト
2. SQLサーバー（cyteen）に接続します。(-S -U は大文字)
  
  osql -S www.cyteen.nagoya-bunri.ac.jp -U アカウント　（アカウントは学籍番号です）
3. パスワードを入力する（８桁の数字）
  ※パスワードはエコーバックされません（入力内容は表示されず、カーソルも移動しない）。
Transact-SQL:
以下で実行するＳＱＬは、Ｔｒａｎｓａｃｔ－ＳＱＬというＳＱＬを拡張したものである。
Transact-SQLでは、複数のコマンド（selectなど）を一度に実行したり、変数（@変数名）やＦＯＲなどのプログラム制御構造を利用可能である。
コマンドを実行するにはgoコマンドの実行が必要である。

例）
コマンド1
コマンド2
コマンド3
go
データベースの操作では、ログインアカウントに適切なロール（権限）が設定されている必要がある。
諸君のアカウントにはデータベース作成ロール（サーバロール）が設定済みである。
以下の様にデータベースを作成し、テーブルにデータを登録してみる。
???????の部分は自分の学籍番号を利用すること。
1. create database db???????　(データベース作成)
2. go
3. use db???????　(データベース利用宣言)
4. go
5. create table t1 ( ID INTEGER, NAME char(12) )　(表作成)
6. go
7. insert into t1 values ( 番号, '名前' )　(レコード記録)番号は、自分の学籍番号・名前は自分の氏名とする。ただし氏名は漢字で6文字まで。
  名前には漢字を利用できる（Alt+半角全角キーで漢字入力ONとOFF切り替え）
8. insert into t1 values ( 123, 'aaa' )
9. go
10. select * from t1　(レコード表示)
11. go
12. update t1 set ID = 456 where ID = 123
13. go
ＳＱＬサーバの利用の為には、アカウント（ＩＤとパスワード）が必要である。さらに、ＳＱＬサーバ内の個別のデータベースを利用するには、それぞれ別々に権限を設定する必要がある。勿論、自分で作成したデータベースは自分自身で利用可能だが、他人が作成したデータベースでは利用するための権利を設定しなければ利用不能である。
他人が作成したデータベースを利用するには、そのデータベースの利用権限（データベースロール）を設定してもらう必要がある。
諸君のアカウントは、管理者によりデータベース「testdb」についてアクセス許可設定済みである（use testdbとして、データベースtestdbを使用可能）。
その他、アカウントには、”テーブルの作成”等のDDL権限などデータベースロールを設定することが出来る。（諸君のアカウントにはtestdb内ではＤＤＬは許可していないのでcreate tableなどのＤＤＬはtestdbでは利用不能）
さらに、データベースtestdbではテーブルCOLLECTIONに全ユーザに対してSELECT・INSERTのみの操作権限を許可している。この様に個別のステートメントごとに利用の許可・不許可を設定できる。
ユーザアカウントに対して、ＳＱＬサーバの利用権限、データベースの利用権限、ＳＱＬコマンドの利用権限という３段階で状況に応じて柔軟に権限を設定可能である。
以下の様に、共同作業用のテーブルCOLLECTIONにデータを登録せよ。
1. use testdb　（testdbはこちらであらかじめ用意した共同作業用のデータベース）
2. go
3. insert into COLLECTION (NAME, COST, REMARKS) values ('品名', 価値, '学籍番号#説明')
  
  ※注意
  品名：コレクション品の名前を10文字以内で記入「'」で囲むこと価値：コレクション品の価値を数値で記入。半角文字で記入すること説明：学籍番号#につづけてコレクション品の説明を10文字以内で記入
  
  ※品名には主キー設定がしてあるので、重複データは登録できない。
4. go
5. 要領が分かったら、同様に幾つか他の品物を登録する
登録データを確認する
自分のデータが登録できていることを確認せよ

注)likeの検索パターン文字は規格どおりの%や_を使用する
また他の学生はどのようなデータを登録しているかも確認する。

例1）
1. select * from COLLECTION where REMARKS like '学籍番号%'
2. go
例2）
1. select * from COLLECTION where COST=自分が記録した値段
2. go
order by や sum 、avg などこれまで学んできたＳＱＬの概念で、演習者がinsertでデータベースに追加したデータについて集計などの処理をしてみること。また、許可されていない操作についてそのコマンドが実行不能であることを確認する。
例）
1. select avg(cost) from COLLECTION
2. go
3. select name,cost from COLLECTION order by cost
4. go
5. select name + ltrim(cost) from collection 注)フィールド名などでは大文字小文字の区別はない。文字列結合演算子は　+　を使用する。異なる型どうしのフィールドの演算では型を変換して一致させる必要がある。例） cast(フィールド as 型)　型変換関数
6. go
7. drop table COLLECTION
  権限のないコマンドなので実行を拒否される
8. go
ＳＱＬサーバとの接続を切る
※時間に余裕があれば、下記の追加課題にも挑戦してください。
1. exit

上記作業でSQLをgoで実行してエラーが起きなければ、goでＳＱＬが実行されるたびに、サーバ側のデータベースに記録が完了される。（よって特にファイルを「保存」などの処置はしなくてよい）

追加課題

・　データベース　testdb　の collection テーブルで、name と cost を結合して表示する。
　　※osql の画面上では、日本語文字列の文字の表示の幅は、全角＝半角２文字に
　　　　換算されない。
　　　　よって、 select name,cost で表示しようとすると、桁ぞろえが正しく行われない。
　　cost の表示を桁数を揃えて表示する様に工夫する。
　　ヒント：
　　　name + cast(cost as varchar)
　　で、文字列型に変換して name と結合
　　cost の左側に　+ 演算子で、適当な長さの空白文字列"　　　　　　　"を結合
　　　 "##########"　+ cast(cost as varchar)
　　right 関数で right(文字列,　長さ)　を指定し、右側から、固定長で文字列を切り取る
　　最終的には、
　　　select name + right( ?????? , ??)
　　の様な感じになる。
・ use db学籍番号
　とし、再び、自分用のデータベースの使用を開始する。
　create table mylist ( name varchar(20) , cost int)
　で、テーブルを作成し、
　insert into mylist (name , cost) select name, cost from testdb.dbo.collection　where 適当な条件
　で、自分のデータベースのテーブルにデータを取り込む
※　条件に関して：　mylistで指定した cost フィールドのデータ型は int で32bitの整数値が格納できます。
　しかし、collectionテーブルのcostはこの範囲を超える値が格納されている為、cost に関して、適当な値以下のものだけinsertするようにしてください。

　上記のSelect文で、 testdb.dbo.collection の部分は、　データベース名.所有者.テーブル名
　の書式になっており、 testdbデータベース中の、dbo の、collectionテーブルを意味する。

・　mylist に update や　delete を使用して、レコードの更新・削除の練習をする
　　testdbのcolllection表では、 update と deleteが禁止されていましたが、こちらのテーブルでは操作可能です。
　試しに、レコードを修正・削除してみてください。