ITスクール DAY28 Oracle(SQL Developer)

SQL Developer

DBMSを使用するため、cmdからアクセスする方法がありますが、Queryをリアルタイムで検証するためにはより便利なUIが必要になります。
Oracleは、SQL Developerを利用することができます。

Table

Entity -> Attributesの集まり -> Relation

Entity（エンティティ）：様々な属性も持っている名詞を意味します。人間、場所、もの、事件、概念はエンティティになれます。

Relation(リレーション）　： DB上に情報を区別して保存する基本単位です。
エンティティに関したデーターをDBは一つのリレーションに入力し、管理します。

Oracle, mySQLのようなRelational Database（関係データベース)では、Relationを
table　だと表現し、NoSQLでは、Collection　だと表現します。

Record -> Recordの集まり -> Table

Record : それぞれのエンティティの属性情報です。
横にあり、row,tupleだとも表現します。
recordはパンケーキのように、募ってテーブルになっていきます。

属性(attribute） : 様々な属性の中で一つの属性名です。
縦軸になり、column,fieldだとも表現んします。

DBは、各々のエンティティの属性情報を集めたテーブルの集まりです。

CREATE TABLE PERSON(
    p_name varchar2(10 char),
    p_age number(3),
    p_gender varchar2(2 char)
);

INSERT INTO person VALUES('홍길동',10,'남');
INSERT INTO person VALUES('김길동',30,'여');
INSERT INTO person VALUES('최길동',10,'남');

name	age	gender
홍길동	10	M
김길동	30	W
최길동	10	M

SQLの種類

DDL(Data Definition Language)

テーブルの構造、関係を設定します。
全体的な仕組み、枠をコントロールします。

例）CREATE, ALTER, DROP

DML(Data Manupulaton Language)

テーブルの中(record)のデーターを扱います。
CRUD(Insert, read , update, deleteができます。

例）INSERT, SELECT, UPDATE, SELECT
SELECTは特別に問い合わせだと表現します。

DCL(Data Control Language)

データーの管理権限などを割り当てる言語です。
主に、DBAの業務になります。

例）GRANT, REVOKE

TCL(Transaction Control Language)

プロセスのように、データーを構造化したり、CRUDで扱う作業単位をTranscationと言います。
Transcationの最後は、DBに永久的に保存する過程が必要になり、それをcommitだといいます。
しかし、commitしてからデータに間違ったり、修正する必要がある場合はどうしましょうか？
その場合は、Transaction前に戻す必要があります。
このようにデーターの整合性のため、Transcationごとの結果を扱う言語がTCLです。

例）COMMIT,ROLLBACK,SAVEPOINT

DBMSが異なっても、SQLの場合、プロトコールのように標準規則がありますので、
とても似ています。
また、各DBMSはそのDBMSの長所を生かすための拡張SQLも使っており、OracleはPL/SQLというSQLがあります。

SQLの場合、全てを大文字として認識するため、カメル表記ではなく、スネークスタイルで表記します。

データーの仕組み

CREATE TABLE table_name(
    column_name DATATYPE(DOMAIN),  
    column_name DATATYPE2,
    ...
);

データータイプ (文字型)

CHAR (容量)

データーの容量が静的です。
char[] arrayだと考えば、分かりやすいです。
空いてるスペースは空白に埋めますが、処理速度は速いです。

VARCHAR2(容量)

データーの容量が可変的です。
Arraylistだと考えば、分かりやすいです。
空いてるスペースがあれば、容量が合わせて減りますが、
CHARよりは処理速度が遅いです。

VARCHAR2(5)   -- 5BYTE
              --韓国語は一文字3BYTE 
VARCHAR2(5 CHAR)  -- 5CHAR  => 最大 15BYTES

javaと異なり、文字列もシングルクォーテーションで表現します。

データータイプ (数字)

NUMBERS(P,S) : 10進法基準

P : 精密度 (Precision), S : 範囲(Scale)

pは全ての桁数を意味します。
pより大きな桁を入力した場合、エラーが発生します。

sが自然数なら小数点後からの桁数, 負数なら、小数点以上(小数点基準左)の数字の桁数を意味　　　　　 します。
sに指定された数以上の数値を入力すると、sに指定された数値によって四捨五入されます。
sが負の数の場合、小数点を基準に左の桁数だけ四捨五入されます。
pがsよりも大きい場合、sは小数点以下の有効数字の桁数を示します。

   --123.54
  
   numcheck1 NUMBER(3),    => 124
   numcheck2 NUMBER(3,2),  => error(pは3ですが、123.54は5桁だからです。)
   numcheck3 NUMBER(5,2),  => 123.54 
   numcheck4 NUMBER(7,1),  => 123.5
   numcheck5 number(7,-1)  => 120 
 

FLOAT(P) : NUMBERの下位タイプで二進法基準。

CREATE TABLE testNum(
   t_num NUMBER(5),  --12345 
   t_flo FLOAT(5)    --12000
 );
 
 INSERT INTO testNum VALUES(12345,12345);
 SELECT * FROM testNum;

float(5)"は実際には"float(2), 1.5"を四捨五入して2になりますし、
12345のうち先頭の2桁「12」は正しく表示され、残りの桁は0で埋められます。
まだ、こちらのメカニズムは100％理解していないため、もっと勉強してみます。

データータイプ （日付け）

DATE

: 年, 月, 日, 時間,　分 ,秒まで入力可能です。

TIMESTAMP

: DATEでさらにms(ミリ秒、0.001秒)まで表現可能です。

dual

直接、テーブルを作れず、dualという仮想のテーブでデーターの演算などができます。

SELECT 10+1 from dual;             --11
SELECT 10-5 from dual;             --5
SELECT 10/4 AS devide FROM dual;   --2.5
SELECT 10*5 AS multiply FROM dual; --50
SELECT SYSDATE FROM DUAL;          --2023-08-18 18:11:56

Constraint

制約条件と呼ばれるものは、データの整合性を保証するために使用されます。
データの整合性とは、データベースに格納されたデータの一貫性と正確性を維持することを指します。

ドメイン制約（Domain Constraint）：

「テーブル内の属性の値は、指定された値のみを持つ必要がある」という条件です。
データ型（type）、ヌル（null/not null）、デフォルト値（default）、チェック（check）などを使用して指定できます。

CREATE TABLE test1(
    t_1 VARCHAR2(10 CHAR),
    t_2 VARCHAR2(10 CHAR) NOT NULL
);	
	

CREATE TABLE example2(
    col1 VARCHAR2(10 CHAR),
    col2 VARCHAR2(10 CHAR)
    CONSTRAINT col2_notnull NOT NULL
); 

CONSTRAINT ~ NOT NULLの場合、最後の属性名にコンマを付けず制約するSQLを作成します。
パラメーターも入れないです。

プライマリーキー(主キー)（Primary Key Constraint）

エンティティは、独立して存在し、一意に識別可能な単位です。
たくさんのエンティティが存在しても、求めているエンティティを見つけることができる必要があります。
そのため、テーブルは主キーを指定し、ヌル値を持つことはできず、唯一の値のみ存在する条件です！

例）社会保障番号

外部キー制約（Foreign Key Constraint）

テーブル間の参照関係を宣言する制約条件です。
参照されるテーブルの外部キーは、参照するテーブルの主キーとドメインが同じである必要があり、参照されるテーブルの値が変更されると、参照するテーブルの制約を受けます。

Constraintの例

Unique(スーパキー)

UNIQUE : 単語の意味通り、該当するカラムに入る値がテーブル全体で一意である必要があります。NOT NULLとも組み合わせて使用できますが、UNIQUEはあまり使用されません。

CREATE TABLE example3( 
  col1 varchar2(10 char) UNIQUE,
  col2 varchar2(10 char) UNIQUE NOT NULL,
  col3 varchar2(10 char),
  CONSTRAINT col3_unique UNIQUE(col3)
);

Primary Key（プライマリ キー）

NOT NULL + UNIQUE
ID、住民番号、会員番号、記事番号などがプライマリキーに適しています。
自然キー（エンティティとの関係性が高い）と一連番号のような人工キー（エンティティとの関係性が低い）があります。

CREATE TABLE example4(
   col1 VARCHAR2(10 CHAR) PRIMARY KEY,
   col2 VARCHAR2(10 CHAR)
);

CREATE TABLE example5(
   col1 VARCHAR2(10 CHAR),
   col2 VARCHAR2(10 CHAR),
   col3 VARCHAR2(10 CHAR),
   CONSTRAINT PK_example5 PRIMARY KEY(col1,col2)
);

テーブルには1つのプライマリキーがあることをオススメします。

Foreign Key（外部キー）

別のテーブルの特定のカラムを参照し、同じデータがある場合にのみ入力が許可されます。
参照されるテーブルはPKやUNIQUEで指定されたカラムのみをFKとして指定できます。

外部キー設定のためには、参照されるカラムが最初に作成されている必要があり、
外部キーを配置するテーブルはその後に作成される必要があります。

CREATE TABLE テーブル名(
  カラム名 データ型,
  CONSTRAINT fk名 FOREIGN KEY(カラム名)
     REFERENCES 参照元テーブル名(参照元テーブルのプライマリキー(またはユニークキー) カラム名)
     ON DELETE SET NULL || ON DELETE CASCADE
  );   

  
CREATE TABLE EXAMPLE6(
    ex6_id VARCHAR2(10 CHAR) PRIMARY KEY
);

INSERT INTO example6 VALUES('10');
INSERT INTO example6 VALUES('20');
INSERT INTO example6 VALUES('30');

CREATE TABLE EXAMPLE7(
    ex7_id VARCHAR2(10 CHAR),
    CONSTRAINT fk_ex7 FOREIGN KEY(ex7_id)
        REFERENCES EXAMPLE6(ex6_id)
        ON DELETE CASCADE
);

INSERT INTO example7 VALUES('10');
INSERT INTO example7 VALUES('20');
INSERT INTO example7 VALUES('30');
INSERT INTO example7 VALUES('40'); --fail: ex6 -> '10','20','30' '40'(x)

SELECT * FROM example6;
SELECT * FROM example7;

DELETE FROM example6 WHERE ex6_id = '10';
SELECT * FROM example6;
SELECT * FROM example7;


CREATE TABLE school(
    department_code NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(10 CHAR) NOT NULL
);

CREATE TABLE student(
    student_number NUMBER(3) PRIMARY KEY, 
    student_name VARCHAR2(30 CHAR) NOT NULL,
    department_code NUMBER(4) NOT NULL,
    CONSTRAINT fk_department_code FOREIGN KEY(department_code)
    REFERENCES school(department_code) 
    ON DELETE CASCADE
);

INSERT INTO school VALUES(1001,'컴퓨터공학과');
INSERT INTO school VALUES(1002,'체육학과');

INSERT INTO student VALUES(101,'김수철',1002);
INSERT INTO student VALUES(102,'니콜라스',1001);
INSERT INTO student VALUES(103,'정찬성',1002);
INSERT INTO student VALUES(104,'남궁성',1001);

SELECT * FROM school;
SELECT * FROM student;

DELETE FROM SCHOOL WHERE department_code=1001;

SELECT * FROM school;
SELECT * FROM student;

DROP TABLE school; --error

DROP TABLE school CASCADE CONSTRAINTS PURGE; --referenceを無視(CASCADE)
DROP TABLE student CASCADE CONSTRAINTS PURGE;

Check & Default

CHECK（チェック）:
データの値の範囲や条件を設定して、条件に合致するデータのみを許可します。

CREATE TABLE example8(
   coll number(10),
   CONSTRAINT ex8_check CHECK(coll BETWEEN 1 and 9)
);

INSERT INTO example8 VALUES(10); //error

DEFAULT（デフォルト）:
何のデータも入力しなかった場合、指定したデータが自動的に入力されます。

CREATE TABLE example9(
coll NUMBER DEFAULT 999
);

INSERT INTO example9 VALUES(DEFAULT);
INSERT INTO example9 VALUES(NULL);
INSERT INTO example9 VALUES(0);
INSERT INTO example9 VALUES(11);

999
null
0
11

ALTER

CREATE TABLE coffeemenu(
    c_name VARCHAR2(17 char) PRIMARY KEY,
    c_price number(7) NOT NULL,
    c_calories number(3) NOT NULL,
    c_startsell date NOT NULL
);

カラムのデータータイプ変更

--ALTER table tablename MODIFY columnname datatype(容量)
ALTER table coffeemenu MODIFY c_name number(3);
ALTER table coffeemenu MODIFY c_name varchar2(2 char);

データ型を変更する際は、対応するカラムの値をすべて削除する必要があります。
それを怠るとエラーが発生します。
カラムの容量を縮小する場合、カラムの容量を調べて、変更後の容量よりも大きな値が存在するかどうかを確認し、値を変更します。

容量の確認(description)

--DESC tablename
DESC coffeemenu

이름          널?       유형                
----------- -------- ----------------- 
C_NAME      NOT NULL VARCHAR2(17 CHAR) 
C_PRICE     NOT NULL NUMBER(7)         
C_CALORIES  NOT NULL NUMBER(3)         
C_STARTSELL NOT NULL DATE         

カラム名変更

--ALTER TABLE tablename RENAME COLUMN beforename TO newname;

ALTER TABLE coffeemenu RENAME COLUMN c_name TO c_name2;
DESC coffeemenu;

Table COFFEEMENU이(가) 변경되었습니다.

이름          널?       유형                
----------- -------- ----------------- 
C_NAME2     NOT NULL VARCHAR2(17 CHAR) 
C_PRICE     NOT NULL NUMBER(7)         
C_CALORIES  NOT NULL NUMBER(3)         
C_STARTSELL NOT NULL DATE    

カラム追加

--ALTER TABLE tablename ADD columnname datatype [(option)];
ALTER TABLE coffeemenu ADD c_name1 datatype varchar2(20 char);

addの後にcolumnを記載すれば、エラーがでますので、カラム名のみつけてください

Table COFFEEMENU이(가) 변경되었습니다.

이름          널?       유형                
----------- -------- ----------------- 
C_NAME2     NOT NULL VARCHAR2(17 CHAR) 
C_PRICE     NOT NULL NUMBER(7)         
C_CALORIES  NOT NULL NUMBER(3)         
C_STARTSELL NOT NULL DATE              
C_NAME1              VARCHAR2(20 CHAR) 

カラム削除

--ALTER TABLE tablename DROP COLUMN columnname

UPDATE coffeemenu SET c_name1 = c_name2;
ALTER coffemenu DROP COLUMN c_name2;

이름          널?       유형                
----------- -------- ----------------- 
C_PRICE     NOT NULL NUMBER(7)         
C_CALORIES  NOT NULL NUMBER(3)         
C_STARTSELL NOT NULL DATE              
C_NAME1              VARCHAR2(20 CHAR) 

カラムにconstraint追加

ALTER TABLE coffeemenu MODIFY c_name1 NOT NULL;
DESC coffeemenu;

이름          널?       유형                
----------- -------- ----------------- 
C_PRICE     NOT NULL NUMBER(7)         
C_CALORIES  NOT NULL NUMBER(3)         
C_STARTSELL NOT NULL DATE              
C_NAME1     NOT NULL VARCHAR2(20 CHAR) 

テーブルのすべての値を削除

TRUNCATE TABLE tablename;

テーブルの構造は残っています。

テーブル削除

DROP TABLE tablename;
DROP TABLE tablename CASCADE CONSTRAINT PURGE;
DROP TABLE tablename PURGE;

cascadeは削除したいテーブルが参照されている場合も削除できるようにするSQLであり、
purgeはごみばこに行かずに一機に削除するSQLです。

テーブル復元（コミ場後にある場合）

FLASHBACK TABLE tablename TO BEFORE DROP;
SELECT * FROM tablename

FLASHBACK TABLE coffeemenu TO BEFORE DROP;
SELECT * FROMcoffeemenu;

7500	250	2023-08-18 17:02:03	감자맛커피

ゴミ箱のごみ削除

PURGE recyclebin;