210824_ORACLE_필기

1. DML(Data Manipulation Language)

- RDBMS 내 테이블의 데이터를 저장,수정,삭제하는 명령어 

>>INSERT,UPDATE,DELETE[WHERE]

 

2.DDL(Data Definition Language)

- RDBMS 내 데이터 관리를 위해 테이블을 포함한 여러 객체를 생성,수정,삭제하는 명령어

>>CREATE,ALTER,RENAME,TRUNCATE,DROP

 

3.TCL(Tracsaction Control Language)

- 트랜잭션 데이터의 영구 저장,취소 등과 관련된 명령어

>>COMMIT,ROLLBACK

 

4.DCL(Data Control Language)

- 데이터 사용 권한과 관련된 명령어 

>>GRANT TO, ALTER USER , DROP USER , DROP USER CASCADE[종속]

 

5.DQL(Data Query Language)

- RDBMS에 저장한 데이터를 원하는 방식으로 조회하는 명령어

>> SELECT ,WHERE , GROUP BY [HAVING],JOIN,서브쿼리 

 

 

6. DML _ 

 6-1. INSERT?

--INSERT INTO T/N(C/N)    *INSERT 구조*

--VALUES(VALUES)  

--ENROL 테이블 조회

SELECT * FROM ENROL; 

-구조확인

DESC ENROL;

-ENROL 테이블을 조회하여 20150000 보다 작은 수를 가져와서 A_ENROL 테이블을 생성

CREATE TABLE A_ENROL

AS

SELECT *

FROM ENROL

WHERE STU_NO <20150000;

 

DESC A_ENROL;

SELECT * FROM A_ENROL;

 

--데이터 생성

INSERT INTO A_ENROL(SUB_NO,STU_NO,ENR_GRADE)

VALUES(108,20151062,92);

 

INSERT INTO A_ENROL

VALUES(109,20152088,85);

 

SELECT * FROM A_ENROL;

 

INSERT INTO A_ENROL(SUB_NO,STU_NO)

VALUES(110,20152088);

 

SELECT * FROM A_ENROL;

 

INSERT INTO A_ENROL

VALUES(111,20153075,NULL);

 

SELECT * FROM A_ENROL;

 

6-2. 복수행 삽입?

--ENROL 테이블 조회

SELECT * FROM ENROL;

-- 학번이 2015로 시작하는 ENROL 테이블 조회

SELECT * FROM ENROL

WHERE STU_NO LIKE '2015%';

-- 학번이 2015로 시작하는 ENROL 테이블 조회하여 A_ENROL에 INSERT 함

INSERT INTO A_ENROL

SELECT * FROM ENROL

WHERE STU_NO LIKE '2015%';

 

SELECT * FROM A_ENROL;

 

6-3. UPDATE?

--UPDATE <T/N>

--SET CO--NAME=??

--WHERE ??

SELECT * FROM A_ENROL;

 

UPDATE A_ENROL

SET ENR_GRADE=ENR_GRADE+5;

 

UPDATE A_ENROL

SET ENR_GRADE=ENR_GRADE+5

WHERE SUB_NO=101;

 

--과목이름이 '시스템분석설계'인 그 과목만 점수를 10점 업데이트하라

UPDATE A_ENROL

SET ENR_GRADE=ENR_GRADE+10

WHERE SUB_NO=(SELECT SUB_NO

FROM SUBJECT

WHERE SUB_NAME='시스템분석설계');

 

SELECT SUB_NO

FROM SUBJECT

WHERE SUB_NAME='시스템분석설계';

SELECT * FROM A_ENROL;

 

6-4. DELETE?

--DELETE FROM <T/N>

--WHERE ???

DELETE FROM A_ENROL

WHERE STU_NO=20131001;

 

SELECT * FROM A_ENROL;

 

--A_ENROL테이블에서 과목이름이 기계요소설계인 과목번호를 가진 내용을 삭제하라

DELETE FROM A_ENROL

WHERE SUB_NO=(SELECT SUB_NO

FROM SUBJECT

WHERE SUB_NAME='기계요소설계');

 

SELECT SUB_NO

FROM SUBJECT

WHERE SUB_NAME='기계요소설계';

 

6-5.다중튜플삭제?

DELETE FROM A_ENROL;

 

   7. TCL?

 

SELECT * FROM B_STUDENT;

DELETE FROM B_STUDENT;

SELECT * FROM B_STUDENT;

 

ROLLBACK;

 

SELECT * FROM B_STUDENT;

DELETE FROM B_STUDENT;

SELECT * FROM B_STUDENT;

CREATE TABLE C_STUDENT(STU_NO NUMBER,

STU_NAME CHAR(10));

 

ROLLBACK;

 

SELECT * FROM B_STUDENT;

 

SELECT * FROM A_STUDENT;

DELETE FROM A_STUDENT;

SELECT * FROM A_STUDENT;

 

ROLLBACK;

 

7-1. 병행처리?

SELECT *

FROM A_STUDENT;

 

INSERT INTO A_STUDENT(STU_NO,STU_NAME)

VALUES(10,'홍');

SELECT * FROM A_STUDENT;

 

COMMIT;

 

8. DDL?

--CREATE/DROP/TRUNCATE/ALTER

8-1. CREATE

CREATE TABLE TEST1

(U_ID VARCHAR2(20),

U_DATE DATE);

 

DESC TEST1;

 

SELECT * FROM TEST1;

 

8-2.기존의테이블을 이용하여 새로운 테이블을 만드는 방법

CREATE TABLE T_STUDENT

AS

SELECT * FROM STUDENT

WHERE STU_DEPT='기계';

 

DESC T_STUDENT;

 

SELECT * FROM T_STUDENT;

 

8-3.열내용을 추가하는 방법

ALTER TABLE T_STUDENT

ADD (ARMY CHAR(1));

DESC T_STUDENT;

SELECT * FROM T_STUDENT;

 

8-4.열의 데이터타입을 변경하는 방법

ALTER TABLE T_STUDENT

MODIFY(ARMY NUMBER);

DESC T_STUDENT;

 

8-5.열의 내용을 삭제하는 방법

ALTER TABLE T_STUDENT

DROP(ARMY);

 

DESC T_STUDENT;

SELECT * FROM T_STUDENT;

 

8-6.열의 이름을 바꾸는 방법

ALTER TABLE T_STUDENT

RENAME COLUMN STU_NAME TO NAME;

 

8-7.테이블 이름을 변경하는 방법

RENAME T_STUDENT TO TEST_STUDENT;

 

DESC T_STUDENT;

DESC TEST_STUDENT;

 

8-8.테이블의 데이터삭제하는방법(완전삭제)

TRUNCATE TABLE TEST_STUDENT;

 

DESC TEST_STUDENT;

SELECT * FROM TEST_STUDENT;

ROLLBACK;

 

8-9.테이블삭제?

DROP TABLE TEST_STUDENT;

DESC TEST_STUDENT;

 

8-10. CONSTRAINT(제약조건)

--1.NOT NULL (C)

--2.UNIQUE KEY (UK)

--3.PRIMARY KEY (PK)

--4.FOREIGN KEY (FK)

--5.CHECK (C)

 

8-10-1.NOT NULL CONSTRAINT CASE?

--T_STUDENT 테이블 생성 제약조건 STU_DEPT 에 걸기

CREATE TABLE T_STUDENT(

STU_NO CHAR(9),

STU_NAME VARCHAR2(12),

STU_DEPT VARCHAR2(20)

CONSTRAINT N_STU_DEPT NOT NULL,

STU_GRADE NUMBER(1),

STU_CLASS CHAR(1),

STU_GENDER CHAR(1),

STU_HEIGHT NUMBER(5,2),

STU_WEIGHT NUMBER(5,2));

 

8-10-2. 제약조건을 확인하는 방법

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='T_STUDENT';

 

DROP TABLE T_STUDENT;

--T_STUDENT 테이블을 UNIQUE ,NOT NULL 제약조건을 걸어준다. 

--NOT NULL : 특정 열에 데이터의 중복 여부와는 상관없이 NULL의 저장을 허용하지 않는 제약 조건

--(반드시 열에 값이 존재해야만 하는 경우)

--UNIQUE : 열에 저장할 데이터의 중복을 허용하지 않고자 할 때 사용

CREATE TABLE T_STUDENT(

STU_NO CHAR(9),

STU_NAME VARCHAR2(12)

CONSTRAINT U_STU_NAME UNIQUE,

STU_DEPT VARCHAR2(20)

CONSTRAINT N_STU_DEPT NOT NULL,

STU_GRADE NUMBER(1),

STU_CLASS CHAR(1),

STU_GENDER CHAR(1),

STU_HEIGHT NUMBER(5,2),

STU_WEIGHT NUMBER(5,2));

 

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='T_STUDENT';

 

DROP TABLE T_STUDENT;

 

CREATE TABLE T_STUDENT(

STU_NO CHAR(9),

STU_NAME VARCHAR2(12)

CONSTRAINT U_STU_NAME UNIQUE,

STU_DEPT VARCHAR2(20)

CONSTRAINT N_STU_DEPT NOT NULL,

STU_GRADE NUMBER(1),

STU_CLASS CHAR(1),

STU_GENDER CHAR(1),

STU_HEIGHT NUMBER(5,2),

STU_WEIGHT NUMBER(5,2),

CONSTRAINT P_STU_NO PRIMARY KEY(STU_NO) );

 

8-10-3.PRIMARY KEY를 동시에 두개를 할당하는 경우의 CASE임.

CREATE TABLE T_ENROL(

SUB_NO CHAR(3),

STU_NO CHAR(9),

ENR_GRADE NUMBER(3),

CONSTRAINT P_ENROL PRIMARY KEY(SUB_NO,STU_NO));

 

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='T_ENROL';

 

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='SUBJECT';

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='STUDENT';

 

DROP TABLE T_ENROL;

DESC STUDENT;

DESC SUBJECT;

 

CREATE TABLE T_SUBJECT

AS

SELECT *

FROM SUBJECT;

 

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='SUBJECT';

 

SELECT * FROM T_SUBJECT;

SELECT * FROM SUBJECT;

DROP TABLE T_SUBJECT;

 

CREATE TABLE T_SUBJECT(

SUB_NO NUMBER(5),

SUB_NAME VARCHAR2(20),

SUB_PROF CHAR(10),

SUB_GRADE CHAR(5),

SUB_DEPT VARCHAR2(10),

CONSTRAINT P_SUB_NO PRIMARY KEY(SUB_NO));

 

CREATE TABLE T_ENROL(

SUB_NO NUMBER(5),

STU_NO VARCHAR2(9),

ENR_GRADE NUMBER(3),

CONSTRAINT ENR_SUB_NO_FK1 FOREIGN KEY(SUB_NO) REFERENCES T_SUBJECT(SUB_NO),

--CONSTRAINT ENR_STU_NO_FK2 FOREIGN KEY(STU_NO) REFERENCES STUDENT(STU_NO),

CONSTRAINT ENR_PK1 PRIMARY KEY(SUB_NO,STU_NO));

DROP TABLE T_ENROL;

 

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME='T_ENROL';

 

--CHECK?

DROP TABLE T_STUDENT;

--CHECK: 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용

CREATE TABLE T_STUDENT(

STU_NO CHAR(9),

STU_NAME VARCHAR2(12)

CONSTRAINT U_STU_NAME UNIQUE,

STU_DEPT VARCHAR2(20)

CONSTRAINT N_STU_DEPT NOT NULL,

STU_GRADE NUMBER(1),

STU_GENDER CHAR(1)

CONSTRAINT C_STU_GENDER CHECK (STU_GENDER IN('M','F')),

STU_HEIGHT NUMBER(5,2),

STU_WEIGHT NUMBER(5,2),

CONSTRAINT P_STU_NO PRIMARY KEY(STU_NO));

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME='T_STUDENT';

 

--제약조건의 비활성화/활성화?

ALTER TABLE T_STUDENT

DISABLE CONSTRAINT N_STU_DEPT;

ALTER TABLE T_STUDENT

ENABLE CONSTRAINT N_STU_DEPT;

 

--제약조건의 삭제방법?

ALTER TABLE T_ENROL

DROP CONSTRAINT ENR_SUB_NO_FK1 CASCADE;

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='T_ENROL';

 

--VIEW?

--단순뷰

CREATE OR REPLACE VIEW V_STUDENT1

AS

SELECT * FROM STUDENT

WHERE STU_DEPT='컴퓨터정보';

SELECT * FROM V_STUDENT1;

--조인뷰?

CREATE OR REPLACE VIEW V_ENROL1

AS

SELECT SUB_NAME,SUB_NO,STU_NO,ENR_GRADE

FROM ENROL NATURAL JOIN SUBJECT;

SELECT * FROM V_ENROL1;

\

--학과별 평균신장보다 큰 학생들의 학번, 이름, 신장을 검색하라

--IN LINE VIEW

SELECT STU_NO,STU_NAME,A.STU_DEPT,STU_HEIGHT

FROM STUDENT A,(SELECT STU_DEPT,AVG(STU_HEIGHT) AS AVG_HEIGHT

FROM STUDENT GROUP BY STU_DEPT) B

WHERE A.STU_DEPT=B.STU_DEPT

AND A.STU_HEIGHT>B.AVG_HEIGHT;