(2013.05 更新)
レポートフォルダから、第７回の資料をコピーしておく
SQLには、テーブルのデータの選択・表示を行うSelect文の他、テーブルのデータの追加修正、テーブルの設計・修正、テーブルの利用権限の設定などを行う命令がある。
SQLのステートメントの分類：
以下の手順に従ってクエリーを実行し、最後にデータベースとエクスポートしたEXCELファイルを提出する。
ファイル名はデータベース・EXCELのどちらも、「第7回#学籍番号」として、レポートフォルダに提出する。
※ファイル名で、#、数字、番号は全て半角文字を使用すること。
まず、第７回のデータベースファイルを開く。
・表のインポート
以下の手順に従い、Excelで作成したワークシートの表を、Accessのテーブルとして取り込みます。このように他の形式のデータを取り込むことをデータのインポートと呼びます。
以下のように、インポートを選ぶ。
検索先に、ユーザホーム（H:）にコピーしたフォルダを指定し、
ファイル形式には「Excelファイル」を選び、
ファイル名「COLLECTION」を選び、
インポートボタンを押す。
インポートに関する指示内容：
以下に指示されたように、インポートするテーブルの設定を行う。
次へ→先頭行をフィールド名として使うをチェック→次へ→次へ→次のフィールドに主キーを設定する（ITEM）→次へ→完了
 |
 |
 |
 |
 |
 |
| COLLECTION テーブルの内容を確認する |
 |
・INSERT INTO 表名 （フィールド,フィールド,...） Ｖａｌｕeｓ （値,値,...）
レコードの追加を行う。
| 8-1-1a | INSERT INTO COLLECTION ( ITEM, WORTH, REMARKS ) VALUES ('宇宙服', 250.00, '火星探検用'); |
| 8-1-1b | INSERT INTO COLLECTION VALUES ('十円玉', 1000, 'やがて貴重になるかもしれない'); |
8-1-1aの実行
8-1-1aを繰り返して実行できないことの確認。
理由は？ ヒント： 主キーは重複できない。よって同じ値を追加できない。
8-1-1bの実行
・INSERT INTO 表名 （フィールド,フィールド,...） SELECT文
1上の例のＶａｌｕｅｓの代わりにＳｅｌｅｃｔ文を書いて、他のテーブルからテーブルへデータを追加できる。
| 8-1-2b | INSERT INTO INVENTORY ( ITEM, COST, REMARKS ) SELECT ITEM, WORTH, REMARKS FROM COLLECTION; |
8-1-2aの実行。テーブルINVENTORYの作成。（テーブルの作成についてはもう少し下で解説する。）
8-1-2bの実行。テーブルにデータを追加。
・UPDATE 表名 ＳＥＴ フィールド１＝値１， フィールド２＝値２，… WHERE 条件
レコードの修正
update 文と select 文の機能の差に注意すること。
select文は、テーブル中のレコードに対して、計算や文字列操作を施し、その結果を「表示」するが、
元のテーブルのレコードを修正したり、テーブルを新規に作成することはない。
それに対して、UPDATEでは、実際にテーブルのレコードが修正される。
| 8-2a | UPDATE COLLECTION SET WORTH = 900 WHERE ITEM = '十円玉'; |
| 8-2b | UPDATE COLLECTION SET WORTH = 900, ITEM = '五円玉' WHERE ITEM = '十円玉'; |
| 8-2c | UPDATE COLLECTION SET WORTH = 555; |
| 8-2d | UPDATE COLLECTION SET WORTH = WORTH * 0.005; |
8-2a から8-2dの実行。
重要：「UPDATE では、WHERE で条件を指定しない場合、全てのレコードに付いて変更が実行される」ことをしっかりと覚えておくこと。
・DELETE FROM 表 WHERE 条件
レコードの削除。
| 8-3 | DELETE * FROM INVENTORY WHERE COST < 275; |
8-3まで実行。
MS-ACCESSでは、*を指定する
DELETE * FROM 表 WHERE 条件
重要：「DELETE は、WHERE で条件を指定しない場合、全てのレコードを削除する」ことをしっかりと覚えておくこと。
最終結果：
・CREATE DATABASE
略
Accessでは、１つのデータベース単位でデータベースを作成する。複数のデータベースを管理する機能は無い。
それに対して、データベースサーバでは、複数のデータベースを扱うことが出来るので、データベースを作成する命令が必要となる。
・CREATE TABLE 表名 （フィールド 型 キーワード，…）
テーブルの作成。
データ型、特に
- 文字型 CHAR(文字数)
例） CHAR(30) 30文字分のデータを記録可能なフィールド
- 数値型 INTEGER 整数
- 数値型 NUMERIC 小数
- 日時型 DATETIME
などは覚えておくこと。
| CREATE TABLE BILLS (BILL_NO NUMERIC CONSTRAINT mc UNIQUE, NAME CHAR(30) NOT NULL, AMOUNT NUMERIC, ACOUNT_ID NUMERIC NOT NULL); |
9-2-5を実行し、BILLSテーブルのデザインを確認（テーブルのデザインを表示）する。
キーワード UNIQUE 重複なし の設定
キーワード PRIMARY KEY 主キーの設定
キーワード NOT NULL NULL値を不許可の設定
例） CHAR(30) NOT NULL
注意：Ａｃｃｅｓｓでは、ＵＮＩＱＵＥの設定は、CONSTRAINT 制約名 UNIQUE と指定する
・ALTER TABLE 表名 ADD フィールド 型 または DROP フィールド または ALTER COLUMN フィールド 型
テーブルの修正。
| 9-3a | ALTER TABLE BILLS ADD COMMENTS CHAR(80); |
| 9-3b | ALTER TABLE BILLS DROP NAME; |
9-3aを実行
9-3bを実行
SQLサーバでは、Drop column またはDrop constraint の指定が必要（次回、紹介）
・DROP TABLE 表名
テーブルの削除
| 9-4 | DROP TABLE BILLS; |
9-4を実行
・DROP DATABASE
略
・表のエクスポート
データベース中のテーブルをExcelのファイルとして取り出します。
テーブルからINVENTORYを選ぶ。
エクスポートを選ぶ。
OKボタン
ファイルの種類を（Excel）、ファイル名を「第7回#学籍番号」としてエクスポートする。
「第7回PASSWD#」に、データをインポートする。
インポートするデータはデータベース演習の履修者のものを用いる、
・データベース演習の出席データを元に、Excelのファイルを準備する。
以下のURL
http://kaz.cyteen.nagoya-bunri.ac.jp/cgi-bin/DB/iattend22.cgi
を開き、マウスで範囲選択し、Excelのワークシートに貼り付ける。
・Excelのワークシートの1行目に、データベースのフィールド名となる、
| p | id | n |
を追加する
・貼り付けたデータを、
1列目がパスワード
２列目が学籍番号
3列目が名前
となるように整える。
※余分な列は削除しておくこと
・1列目のパスワードを設定する
例） 以下のいずれかで設定せよ
・全て同じものを指定
例） password
・学籍番号と同一のものを設定 OR 学籍番号にPなどの文字列を付け加えたものを指定
文字列の結合記号 & を使用して =”P”＆セル番号 など
例） p3112987
・RANDBETWEEN（）関数を使用し、ランダムな数値を指定（桁数を統一するとよい）
=RANDBETWEEN(10000000, 99999999) など
※乱数列は、Ｅｘｃｅｌのファイルを開くたびに変化するので、別の列で乱数パスワードを作製し、パスワード記録列に値をコピーして貼り付ける。
例） 39198829
・Excelのワークシートを、データベースにインポートする。
インポート先のテーブルで テーブル Passwd を選択
間違って、新規テーブルにデータをインポートしてしまった場合や、
インポート元のワークシートに余分なデータなどがあり、
テーブル Passwdに
インポートできない場合は、
適当な名前の新規テーブルにインポートし、
インポート用のクエリ
insert into passwd (p, id, n) select p,id,n from 新規テーブル名;
で、Passwdテーブルにインポートする。
・レポートやフォームが正常に機能することを確認する。
ファイル名はデータベース・EXCELのどちらも、「第7回PASSWD#学籍番号」として、レポートフォルダに提出する。
※ファイル名で、#、数字、番号は全て半角文字を使用すること。