AP データベース SQL基礎2
AP データベースへ戻る。
AP データベース SQL基礎へ戻る。
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
| 4 | 田中美咲 | 総務部 | 280000 |
ALTER TABLE
表に対する様々な変更をする句です。ALTER TABLEに続けて使うキーワードで、
- １．列の追加 (ADD COLUMN):
- 既存のテーブルに新しい列を追加します。
- ２．列の削除 (DROP COLUMN):
- 既存のテーブルから列を削除します。
- ３．列の変更 (ALTER COLUMN / MODIFY COLUMN):
- 既存の列のデータ型、NULL許容性、デフォルト値などを変更します。構文はデータベースシステムによって異なる場合があります(例: SQL Server, PostgreSQLはALTER COLUMN、MySQLはMODIFY COLUMN)。
- ４．列名の変更 (RENAME COLUMN):
- 既存の列の名前を変更します。これもデータベースシステムによって構文が異なります (例: RENAME COLUMN old_name TO new_nameやCHANGE old_name new_name data_type)。
- ５．制約の追加・削除 (ADD CONSTRAINT / DROP CONSTRAINT):
- 主キー、外部キー、UNIQUE、CHECKなどの制約を追加または削除します。
- ６．テーブル名の変更 (RENAME TO):
- テーブル自身の名前を変更します。
- １．列の追加
ALTER TABLE 社員
ADD COLUMN 入社日 DATE;
| 社員ID | 氏名 | 所属部署 | 給与 | 入社日 |
|---|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 | NULL |
| 2 | 佐藤花子 | 開発部 | 400000 | NULL |
| 3 | 鈴木一郎 | 営業部 | 350000 | NULL |
| 4 | 田中美咲 | 総務部 | 280000 | NULL |
このように列が追加されます。次は列を削除してもとに戻します。
２．列の変更
ALTER TABLE 社員
MODIFY COLUMN 給与 BIGINT NOT NULL;
上記で、社員テーブルの給与列にBIGINT型を設定して、NOT NULL制約を指定できます。
- ３．列の削除
ALTER TABLE 社員
DROP COLUMN 入社日;
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
| 4 | 田中美咲 | 総務部 | 280000 |
４．制約の追加
ALTER TABLE 社員
ADD CONSTRAINT PK_社員ID PRIMARY KEY (社員ID);
上記のようにすると社員テーブルの社員ID列を主キー設定にし、一意な値を保持し、NOT NULL制約を適用できます。
外部キー制約の追加
外部キー制約を追加する場合は以下のようになります。
ALTER TABLE 社員
ADD CONSTRAINT FK_社員_部署
FOREIGN KEY (所属部署)
REFERENCES 部署テーブル (部署名);
外部キーを設定する列をFOREIGN KEY (所属部署)のように列指定し、外部キーとして参照するテーブルと列を、REFERENCES 部署テーブル (部署名)で指定するという記述方法になります。
５．制約の削除
ALTER TABLE 社員
DROP CONSTRAINT PK_社員ID;
上記のようにすると社員テーブルの社員IDの制約を削除することができます。
６．テーブル名の変更
ALTER TABLE 社員
RENAME TO 従業員;
上記のようにすると社員テーブルを従業員テーブルという名前に変更できます。
ON DELETE CASCADE・ON UPDATE CASCADE
外部キーの削除変更があれば連動して、更新されたり、削除されたりする仕組みを形成します。
ALTER TABLE 社員
ADD CONSTRAINT FK_社員_部署 FOREIGN KEY (所属部署)
REFERENCES 部署テーブル (部署名)
ON DELETE CASCADE
ON UPDATE CASCADE;
このように連動定義をしていると
DELETE FROM 部署テーブル
WHERE 部署名 = '総務部';
のようにして総務部を削除すると、部署テーブルは
| 部署名 | 拠点 |
|---|---|
| 営業部 | 東京 |
| 開発部 | 大阪 |
| 試作部 | 三重 |
となり
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業部 | 350000 |
社員テーブルはこのように自動的に総務部の情報がなくなります。
また営業部の名称を営業部から営業戦略部のように変更する以下のSQLを実行すると
UPDATE 部署テーブル
SET 部署名 = '営業戦略部'
WHERE 部署名 = '営業部';
部署テーブルは以下のようになり
| 部署名 | 拠点 |
|---|---|
| 営業戦略部 | 東京 |
| 開発部 | 大阪 |
| 試作部 | 三重 |
となり、社員テーブルは
| 社員ID | 氏名 | 所属部署 | 給与 |
|---|---|---|---|
| 1 | 山田太郎 | 営業戦略部 | 300000 |
| 2 | 佐藤花子 | 開発部 | 400000 |
| 3 | 鈴木一郎 | 営業戦略部 | 350000 |
のように連動して値が変更されます。便利ですが、本当に置き換えてしまうことで解決する関係であるかどうかまで熟知して利用する必要があります。
CREATE USER
SQLシステムにログインして表を扱うユーザを追加する操作です。
CREATE USER report_user WITH PASSWORD 'password123';
CREATE USER app_user WITH PASSWORD 'password456';
これでレポートを出力するためのユーザreport_user(任意のユーザ名)とアプリを動かすときにアプリがSQL問合せをするときの権限のユーザapp_userをそれぞれパスワードを指定して作成したことになります。
REVOKE
権限をなくすための句です。
REVOKE ALL PRIVILEGES ON TABLE 社員 FROM report_user;
REVOKE ALL PRIVILEGES ON TABLE 部署テーブル FROM report_user;
REVOKE ALL PRIVILEGES ON TABLE 社員 FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE 部署テーブル FROM app_user;
すべての権限を各テーブルからはく奪するには上記のようなSQLを使います。
GRANT
ユーザの権限と表の権限の関係を設定するものです。
GRANT SELECT ON TABLE 社員 TO report_user;
GRANT SELECT ON TABLE 部署テーブル TO report_user;
部署テーブルと社員テーブルにselectのような射影操作だけをreport_userに許可する権限設定となりました。
これでreport_userは以下のようなSQLが実行できます。
SELECT * FROM 社員;
SELECT 部署名, 拠点 FROM 部署テーブル WHERE 部署名 = '営業部';
ただし、以下のようなSQLは実行できません。
INSERT INTO 社員 (社員ID, 氏名, 所属部署, 給与) VALUES (5, '新入社員', '開発部', 300000);
DELETE FROM 社員 WHERE 社員ID = 1;
UPDATE 社員 SET 給与 = 500000 WHERE 社員ID = 1;
app_userには、より様々なことができる権限を設定するとしたら、以下のようなSQLを実行します。
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 社員 TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE 部署テーブル TO app_user;
上記によりapp_userはSELECT、INSERT、UPDATE、DELETEを実行できる。
社員テーブルの氏名、所属部署列のみ更新できる権限をapp_userに設定する場合は以下のとおりのSQLとなる。
GRANT UPDATE (氏名, 所属部署) ON TABLE 社員 TO app_user;
あらゆる権限を付与する場合には以下のようなSQLを実行します。
GRANT ALL PRIVILEGES ON TABLE 社員 TO app_user;
GRANT ALL PRIVILEGES ON TABLE 部署テーブル TO app_user;
このようなGRANTによる権限設定のみをできるようにするには
GRANT ALL PRIVILEGES ON TABLE 社員 TO report_user WITH GRANT OPTION;
のようにWITH GRANT OPTIONという句をつけます。
AP データベース SQL基礎へ戻る。
AP データベースへ戻る。