1. ユーザとアクセス権限
1-1. ユーザの作成
ユーザの作成はGUIのツールでも作成可能ですが、ここではSQLを使用してユーザを作成してみましょう。
GUIのツールの方がユーザを作成するのは簡単なのに、なぜわざわざSQLでユーザを作成するのでしょうか？それはSQL文をプログラムと組み合わせたり、タスクに組んで実行したりする事でユーザ作成の自動化を行えるからです。
ユーザを作成するには、OracleではCREATE USERを使用しますが、SQL Serverではストアードプロシージャ?、sp_addloginを使用します。
■Oracle
CREATE USER Jenny IDENTIFIED BY password
■SQL Server
sp_addlogin Jenny, password, default_db_name
または、
sp_addlogin @loginame=Jenny, @defdb=default_db_name
1-2. ユーザアカウントをロックする
ユーザアカウントを一時的に使用停止・データベースへのアクセス停止するには、ロックをおこないます。
■Oracle
ALTER USER Jenny ACCOUNT LOCK
■SQL Server
ALTER LOGIN Jenny DISABLE
ロックを解除する事もできます。
■Oracle
ALTER USER Jenny ACCOUNT UNLOCK
■SQL Server
ALTER LOGIN Jenny ENABLE
1-3. パスワードを期限切れにする
ユーザのパスワードを意図的に期限切れにする事で、ユーザの次回ログイン時にパスワードの変更を行わせる事ができます。
■Oracle
ALTER USER Jenny PASSWORD EXPIRE
■SQL Server
ALTER LOGIN Jenny WITH CHECK_EXPIRATION = ON
1-4. パスワードを変更する
管理者であれば、ユーザのパスワードの変更ができます。
■Oracle
ALTER USER Jenny IDENTIFIED BY ‘new_password’
■SQL Server
ALTER LOGIN Jenny WITH PASSWORD = ‘new_password’
1-5. ユーザ名の変更
セキュリティの観点から定期的にユーザ名を変更する場合があります。ただし、サービス用のアカウントはプログラム等で使用されているので、ユーザ名の変更は滅多に行いません。ユーザ名の変更を行う場合は、影響範囲を考えて実行しましょう。
■Oracle
Oracleではユーザ名の変更はできない。
■SQL Server
ALTER LOGIN Jenny WITH NAME = Jennifer
1-6. ユーザの削除
ユーザを削除する場合は、以下のSQL文を実行します。
■Oracle
DROP USER Jenny
■SQL Server
sp_droplogin Jenny
2. テーブルの作成・変更・削除
2-1. テーブルの作成
テーブルを作成するには、CREATE TABLEでテーブルの定義を行います。
CREATE TABLE table_name
(
column_name column_definition
)
table_nameにはテーブル名を指定します。column_nameには列名、column_definitionには列の型を指定します。
それでは、社員情報テーブル、tbEmpTableを作成してみましょう。必要な列は以下の通りです。
社員番号：empID
姓：surname
名：givenname
部署コード：deptCD
社員情報テーブル、tbEmpTableを作成するSQL文は以下の通りです。
CREATE TABLE tbEmpTable
(
empID varchar(20),
surname varchar(20),
givenname varchar(20),
deptCD varchar(5)
)
2-2. NOT NULL制約
社員番号(empID)には必ず値を入力するようにします。その場合、NOT NULL制約を指定します。NOT NULL制約を指定しない列はNULL値が許可されます。
CREATE TABLE tbEmpTable
(
empID varchar(20) NOT NULL,
surname varchar(20),
givenname varchar(20),
deptCD varchar(5)
)
2-3. PRIMARY KEY
社員番号(empID)は社員1人1人に対してユニークな番号なので、社員番号(empID)は重複しないようにする必要があります。その場合、プライマリキー制約を指定します。
CREATE TABLE tbEmpTable
(
empID varchar(20) NOT NULL PRIMARY KEY,
surname varchar(20),
givenname varchar(20),
deptCD varchar(5)
)
プライマリキー制約の指定により、empIDには重複する社員番号をINSERT・UPDATEできなくなります。
2-4. 列のデフォルト値
デフォルト値を設定していれば、INSERT時に値を指定されなかった列は、そのデフォルト値でINSERTされます。例えば、部署コード(deptCD)の指定がない場合は人事付け(001)でINSERTしたいと思います。その場合は次のようにテーブルを作成します。
CREATE TABLE tbEmpTable
(
empID varchar(20) NOT NULL PRIMARY KEY,
surname varchar(20),
givenname varchar(20),
deptCD varchar(5) DEFAULT ‘001’
)
2-5. CHECK制約
有効な値のみINSERTもしくはUPDATEできるように、列に条件を指定する事ができます。それがCHECK制約です。指定した条件に合わない値をINSERTもしくはUPDATEすると、エラーとなり、INSERT/UPDATEできません。例えば、社員番号(empID)は4桁のみ有効とCKECH制約で設定すると、
CREATE TABLE tbEmpTable
(
empID varchar(20) NOT NULL PRIMARY KEY CHECK(len(empID)=4),
surname varchar(20),
givenname varchar(20),
deptCD varchar(5) DEFAULT ‘001’
)
len関数は文字列の長さを返す関数です。Oracleの場合はlength関数ですが、使い方は同じです。
2-6. テーブルの変更
テーブル属性の変更はALTER TABLEを使用します。ALTER TABLEは列の追加・削除、プライマリキーなどの制約の設定を行う事ができます。
それでは、まず社員情報テーブル(tbEmpTable)にメールアドレス用の列(email)を追加してみましょう。
■Oracle
ALTER TABLE tbEmpTable ADD (email varchar(20))
■SQL Server
ALTER TABLE tbEmpTable ADD email varchar(20)
列の削除はALTER TABLEの後にDROPと記述して使用します。
■Oracle
ALTER TABLE tbEmpTable DROP (givenname)
■SQL Server
ALTER TABLE tbEmpTable DROP COLUMN givenname
2-7. テーブルの削除
テーブルを削除するには、DROP TABLEを使用します。DROP TABLEでテーブルを削除すると、インデックスなどの情報も全て削除されます。
DROP TABLE tbTableName
3．テーブル操作（DML)
DML(Data Manipulation Language)にはINSERT、UPDATE、DELETEなどがあります。DMLの広い意味ではSELECTもDMLととらえられますが、SELECTに関しては別のセクションで説明します。
3?1．行の挿入（INSERT)
テーブルに行を挿入するにはINSERTを使用します。
使用例：
INSERT INTO tbEmpTable VALUES(1001, ‘Tanaka’, ‘Taro’, 100);
上の例では、tbEmpTableには「社員番号」「姓」「名」「部署コード」の列に対応する値をINSERTしています。列を限定してINSERTする場合は次のように行います。
使用例：
INSERT INTO tbEmpTable(empID, surname) VALUES(1001, ‘Tanaka’);
SELECTの結果をINSERTする事もできます。
使用例：
INSERT INTO tbEmpTable(empID, surname, deptCD) SELECT empID, name, deptCode FROM tbApplicantTable;
この場合、SELECTで抽出する列数とINSERTで指定する列数が同じでなければなりません。
3?2．行の更新（UPDATE)
既存の行に対して、値を更新するにはUPDATEを使用します。一度に複数の列、複数の行を更新する事が可能です。WHERE句で更新する条件を指定しない場合は、全行に対して更新してしまいますので、注意が必要です。
使用例：社員テーブルの部署コード列の値を全て100で更新する
UPDATE tbEmpTable SET deptCD = ‘100’;
使用例：指定した列の部署コードを200で更新する
UPDATE tbEmpTable SET deptCD = ‘200’ WHERE empID = ‘1001’;
使用例：複数の列を更新する
UPDATE tbEmpTable SET surname = ‘Yamada’, deptCD = ‘300’ WHERE empID = ‘1001’;
3?3．行の削除
行を削除する場合はDELETEもしくはTRUNCATE TABLEを使用します。DELETEとTRUNCATE TABLEの違いはDELETEは指定した行を削除する事ができますが、TRUNCATE TABLEはテーブルの行を全て削除します。TRUNCATE TABLEはログやインデックスの処理は行われないので、DELETEよりも高速に削除処理が行えます。しかしTRUNCATE TABLEはDDL文ですので、一度削除したらROLLBACKで処理を取り消す事ができません。
使用例：テーブルの行を全て削除
DELETE FROM tbEmpTable
使用例：高速にテーブルの行を全て削除
TRUNCATE TABLE tbEmpTable
使用例：行を指定して削除
DELETE FROM tbEmpTable WHERE empID = ‘1001’
4．テーブル参照(SELECT)
SELECTはデータベースのテーブルからデータを選択して表示する時に使用するコマンドです。SQLには様々なコマンドがありますが、SELECTが一番良く利用されるコマンドでしょう。データベースの運用を担当する事になった場合も、SELECTの使い方から学んでいきますよね。SELECTコマンドは簡単ですが、使い方によっては複雑なデータ処理も行う事ができますので、基礎から応用まで勉強していきましょう。
4?1．SELECTの基礎
例題を見ながらSELECT文の使い方を学んでいきましょう。SELECT文は習うより慣れろですので、文法を覚えるよりもとにかくコンソールにSELECT文をたくさん書いてみましょう。
使用例：社員テーブルの全ての列・行を表示する。
SELECT * FROM tbEmpTable;
*（アスタリスク）を使うと、そのテーブルの列全部を表示します。表示する列を指定したい場合は次のようにSELECT文を記入します。
使用例：社員テーブルから社員番号と社員名を表示する。
SELECT empID, user_name FROM tbEmpTable;
行全てを表示するのではなく、指定した行のみを表示する場合は、SELECT文の後ろにWHERE句を追記して、条件を記入します。
使用例：社員テーブルから社員番号が1003の社員名を表示する。
SELECT empID, user_name FROM tbEmpTable WHERE empID = 1003;
WHERE句には複数の条件を記入することができます。次の例で社員番号が2000以上で部署コードが300の社員を表示してみましょう。
使用例：社員テーブルから、社員番号が2000以上で部署コードが300の社員情報を表示する。
SELECT * FROM tbEmpTable
WHERE empID >= 2000 AND deptCD = 300;
4?2．ORDER BY
SELECT文の結果を、指定した列の値を基にソートしたい場合はORDER BYを使います。ORDER BYはSELECT文の最後に記述する必要があります。
使用例：社員情報を社員番号の昇順で表示する。
SELECT * FROM tbEmpTable ORDER BY empID ASC;
昇順の場合はASCを指定しますが、省略してもかまいません。逆に降順を指定する場合はDESCを指定する必要があります。
使用例：社員情報を社員番号の降順で表示する。
SELECT * FROM tbEmpTable ORDER BY empID DESC;
複数の列を基にソートする事ができます。例えば、部署コードを基にソートする場合、部署には複数の社員が存在しますので、第2のソートキーとして社員の名前を基にソートしてみましょう。
使用例：社員情報を部署コードと社員名を基に昇順で表示する。
SELECT * FROM tbEmpTable ORDER BY deptCD, user_name;
使用例：社員情報を部署コードと社員名を基に降順で表示する。
SELECT * FROM tbEmpTable ORDER BY deptCD DESC, user_name DESC;
4?3．GROUP BY
特定の列を基にグループ化して集計を行う場合はGROUP BY句を使います。GROUP BY句を使う場合は集計関数を利用します。
使用例：部署ごとに社員数を求める
SELECT deptCD, COUNT(*) FROM tbEmpTable GROUP BY deptCD;
deptCD COUNT(*)
———————————–
100 5
200 14
300 7
400 9
