SAK 図書館
SAK 図書館
SQL 基礎実地編 (その二) テーブル削除、クリア、定義変更、名前変更、権限
■テーブル削除
・テーブルを削除すると、付随する index も自動的に削除される。
テーブル中のデータだけでなく、項目定義など、全て削除されるので注意す
ること。
drop table sak.テストm;
■テーブルクリア
・テーブル中のデータだけを全て削除するには、delete 文を使用する。
delete from sak.テストm;
・但し、単純クリアが目的なら、この方法はレスポンス的に問題がある。
トランザクション機能が働くので、全レコードのトランザクションバッファ
への書き込みとコミットによる更新が行われる。
何万件にも及ぶデータのクリアを delete 文でやると実用に耐えない。
・単純なテーブルクリアが目的の場合は、表切り捨て(truncate) を使用する
と良い。この方が一瞬でクリアできる。
truncate table sak.受注m;
・思いきって、テーブル削除と再作成を利用する手もある。
drop table sak.テストm;
create table sak.テストm (
キー char (008) primary key,
データ1 number (009, 0),
データ2 number (009, 0),
データ3 number (009, 0)
) STORAGE(INITIAL 100M NEXT 20M MAXEXTENTS 99);
■テーブル定義変更(テーブル属性変更、列定義変更)
・データ項目の桁数が足りなくなったり、新たな項目を追加するのは alter
文で簡単に行える。但し、項目の削除はできない。
(最近の Oracle では、列削除が行える。)
項目の削除を行うには、
データ退避
テーブル削除
テーブル再作成
新形式にデータ編集
データセットアップ
を行う必要がある。
・新たにテーブルに項目を追加(列の追加、フィールド追加、カラム追加)
するには、次のようにする。
alter table sak.テストm add (
新項目1 number (009, 0),
新項目2 varchar2(005)
);
・最近の Oracle は列の削除が行える。
alter table testm drop (
削除項目1,
削除項目2
);
・既存のテーブル項目を変更するには(列変更)、次のようにする。
但し、項目タイプの変更や桁数を少なくする場合は、該当項目にデータが
セットされているとエラーになる場合がある。
update 文で、該当項目を NULL にしたり、桁調整するか、
データ退避
テーブル削除
テーブル再作成
新形式にデータ編集
データセットアップ
を行う必要がある。
alter table sak.テストm modify (
データ1 varchar2(020)
);
■整合性制約定義、リレーション、リファレンス、外部キー
・データ項目に他のテーブルとのリレーションをとって、制約条件などを設定
することができる。(constraints)
制約追加は、create table 時、または、alter table で行う。
create table sak.テストm (
キー char (008) primary key,
データ1 number (009, 0)
constraint テストm_データ1_ct1 not null
constraint テストm_データ1_ct2 check(受注数 >= 0),
データ2 number (009, 0),
データ3 number (009, 0)
);
・列制約には、
primary key 主キー制約(unique & not null)
unique 一意キー制約(ユニーク項目、一意制約、ユニーク値、ユニークキー、ユニーク制約)
not null NOT ヌル制約
check チェック制約
references 参照整合性制約(参照制約、外部整合性制約、外部制約キー)
がある。
alter table sak.テストm
add constraint テストm_受注数 not null 受注数
;
alter table sak.テストm
add constraint テストm_受注数 check(受注数 >= 0)
;
alter table sak.テストm
add constraint テストm_キー_fk
foreign key (キー) references マスタm (キー)
;
・制約の削除は、次のようにする。
alter table sak.テストm
drop constraint テストm_受注数
;
・制約の参照は、次のようにする。(列制約表示、列制約確認)
select substr(a.table_name, 1, 16), substr(b.column_name, 1, 16),
a.constraint_name, a.constraint_type
from user_constraints a, user_cons_columns b
where a.table_name = b.table_name (+)
and a.constraint_name = b.constraint_name (+)
and a.table_name = '受注M'
;
■名前変更(テーブル名変更)
・テーブル、ビュー、その他オブジェクトの名前を変更するには、rename 文
を使用する、
rename sak.テストm to sak.あいうえおm;
■スキーマ作成
・スキーマ(schema) は、ユーザを作成すると自動的に作られる。
(ユーザ追加、スキーマ生成)
create user testscm
identified by testpw
default tablespace USER_DATA
temporary tablespace TEMPORARY_DATA
quota unlimited on USER_DATA
;
・スキーマ(schema) の容量を限定することもできる。
create user testscm
identified by testpw
default tablespace USER_DATA
temporary tablespace TEMPORARY_DATA
quota 10M on USER_DATA
;
■スキーマ削除
・スキーマ(schema) は、ユーザを削除することで行う。
drop user testscm;
・cascade を指定すると、user が所有していたオブジェクトも削除される。
drop user testscm cascade;
■システム権限付与
・スキーマ(schema) やオブジェクトに個別に権限を付与することができるが、
一般的にはロールを使用する。
権限関係はシビアな問題なので、マニュアルをよく見て、システムに適切な
セキュリティが保てるように設計すること。
(ユーザ権限、テーブルアクセス制限)
grant CREATE SESSION to testscm;
grant CREATE SESSION to testscm with admin option;
revoke CREATE SESSION from testscm;
・ロール作成、権限付与、権限取消、権限確認
create role alluser;
grant CREATE SESSION to alluser;
revoke CREATE SESSION from alluser;
select * from role_sys_privs where role = 'ALLUSER';
・スキーマへのロール付与、ロール取消、ロール確認
grant alluser to testscm;
revoke alluser from testscm;
select * from user_role_privs;
■レプリケーション(レプリカ)、パラレル、分散データベース、クラスタ
・パフォーマンス向上や複数データベース分散など、色々な機能がある。
レプリケーションは他のデータベースと同期をとる。更新を含むレプリケー
ションはけっこうやっかいである。
パラレルは複数のインスタンスで同一データベースにアクセスする。
複数のサーバで更新できるので、パフォーマンスは高くなる。
分散データベースは、複数のデータベース中のオブジェクトを結合して
論理的な 1 データベースのように扱う。
うまく設計すれば、非常に高いパフォーマンスを得ることができる。
・Oracle8 から、クラスタ構成がとれる。複数のサーバデータベースをひとつ
のデータベースのように扱い、障害時に停止しない運用が可能である。
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK