SQL Master データベースエンジニアとLinuxエンジニアのための情報まとめ

OracleやMS SQL serverの資格対策、SQLコマンド辞典、Linuxコマンド辞典、セキュリティに関する情報

SQL基礎講座

1. ユーザとアクセス権限

1-1. ユーザの作成

1-2. ユーザアカウントをロックする

1-3. パスワードを期限切れにする

1-4. パスワードを変更する

1-5. ユーザ名の変更

1-6. ユーザの削除

2. テーブルの作成・変更・削除

2-1. テーブルの作成

2-2. NOT NULL制約

2-3. PRIMARY KEY

2-4. 列のデフォルト値

2-5. CHECK制約

2-6. テーブルの変更

2-7. テーブルの削除

3. テーブル操作(DML)

3-1. 行の挿入[INSERT]

3-2. 列の更新[UPDATE]

3-3. 行の削除[DELETE]

4. テーブル参照(SELECT)

4-1. SELECTの基礎

4-2. ORDER BY句

4-3. GROUP BY句

5. テーブル結合

6. サブクエリ

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

投稿日:2009年8月23日 更新日:


comment

広告

転職