--- title: "오라클 DBMS 정리 02" date: "2018-11-06" tags: ["오라클DBMS"] status: archived verified: false --- ## 오라클 DBMS 정리 02 - DDL, DCL --- ### 테이블 생성 ```sql -- 테이블명은 어떤 컬럼을 갖고 있는지 유추 가능한 연관있는 이름이어야 함 CREATE TABLE 테이블명( 컬럼명 데이터형(크기), ... ); ``` * 가장 간단한 형태의 테이블 생성 ```sql -- 계정이 가지고 있는 테이블의 목록보기 -- tab을 dictionary라고 함 SELECT tname FROM tab; -- 테이블 구조 조회 (sqlplus문) DESC 테이블명 ``` * **대소문자 섞어 사용해도 오라클이 모두 대문자화** - 입력되는 값들은 예외 ![02-01](/md/legacy-assets/oracle/02-01.webp) * BIN으로 시작하는 테이블은 휴지통에 있는 테이블들 **PURGE RECYCLEBIN**으로 삭제 가능 ```sql -- class4_info 테이블 생성 -- 번호, 이름, 나이, 시력, 주민번호, 이메일, 입력일 저장 -- 번호는 숫자 0~9999 -- 이름은 문자열 -- 나이는 숫자 0~999 -- 시력은 실수 -- 주민번호는 고정길이 문자열 -- 이메일은 가변길이 문자열 -- 단, 한글 5자까지 CREATE TABLE class4_info ( num NUMBER(4), name VARCHAR2(15) ssn CHAR(14), age NUMBER(3), eye NUMBER(3,2), email VARCHAR2(50), input_date DATE ); -- 생성된 테이블의 구조 보기 : sqlplus문 DESC Class4_info -- 생성된 테이블 명 조회 : SQL문 SELECT tname FROM tab; ``` --- ### INSERT ```sql INSERT INTO 테이블명(컬럼명, ... ) VALUES (값, ...); ``` * 테이블에 레코드를 삽입할 때 사용 * **데이터는 순차적으로 입력되지 않음(append나 add가 아님)** * SUBQUERY로 한번에 여러행을 INSERT 가능 * 일반적으로 한번에 하나의 레코드를 추가 * 컬럼명은 생략 가능 - 생략을 하게되면 테이블의 컬럼의 순서대로 값을 모두 넣어야 함(권장하지 않음) * **컬럼명은 테이블의 순서와 상관없이 정의가능** - 정수, 실수 : 그냥 값을 넣으면 됨(홑따옴표 없이) - 문자열 : 고정이든 가변이든 홑따옴표을 이용해서 값을 넣음 + '값', '오늘은 화요일' - 날짜 : 현재 날짜 입력시 SYSDATE, 다른 날짜 입력시 날짜형식의 문자열을 입력 + SYSDATE의 경우 시분초의 정보도 들어감 - '2018-10-24' 시분초 없이 단순히 날짜만 바꿔서 넣고싶으면 날짜형식을 맞춰서 * 컬럼명은 대소문자 구분안함, 문자 입력값들은 대소문자 구분함 ```sql -- 컬럼명을 생략하는 경우 : 테이블의 컬럼 순서대로 값을 넣어야 함 INSERT INTO class4_info VALUES (1, 'YOUNG', '900000-0000000', 29, 1.5, 'young@young.com', SYSDATE); -- 컬럼명을 명시 안하면 생길 수 있는 오류 -- 문자열일 경우 이름과 이메일이 바뀌어서 입력돼도 오류인지 모를 수 있음 INSERT INTO class4_info VALUES (2, 'sol@sol.com', '930815-0000000', 26, 0.8, 'sol', SYSDATE); -- 컬럼명을 명시하는 경우 : 테이블의 컬럼 순서와 상관 없이 원하는 컬럼만 값을 넣을 수 있다(권장) INSERT INTO class4_info (name, age, input_date) VALUES ('Sol', 26, SYSDATE); -- INSERT할 때 컬럼을 생략하면 NULL이 들어감 -- NULL의 뜻은 숫자도 문자열도 아닌 사용할 수 없는 값 -- NULL은 조회되지 않는다 -- 문자열은 컬럼을 생략하거나 홑따옴표 두개를 공백없이 붙여넣으면 NULL이 들어감 -- 숫자는 컬럼을 생략하는 경우에만 NULL이 들어감(0은 NULL이 아니다!) INSERT INTO class4_info (age, name, email) VALUES (30, 'Dong', 'dong@dong.com'); ``` --- ### SELECT ```sql SELECT 컬럼명, ... FROM 테이블명 ``` * 테이블에 원하는 컬럼의 모든 레코드를 조회할 때 사용 * 컬럼명은 테이블안에 존재하는 컬럼, 작성 시 테이블명 먼저 명시하고 컬럼명 작성하는게 편함 * 컬럼명을 모르고 *(asterisk)를 사용하면 모든 컬럼을 조회 - '*'하고 컬럼명을 같이 사용할 순 없음 * **컬럼명은 테이블의 컬럼의 순서와는 관계가 없음** ```sql -- 모든 오라클 속성확인 show all -- autocommit OFF 되어있음 -- autocommit이 보통 OFF, DML작업 후 COMMIT이 필요함 ``` --- ### 트랜잭션(Transaction) * **DB 업무 처리의 단위** * 대상쿼리 : INSERT, UPDATE, DELETE - **대상쿼리는 메모리에만 기록됨(File영역으로 바로 안씀)** - 때문에 비정상종료 시 insert 같은 기록이 레코드로 안남게됨 - 대상쿼리 작업을 다음에도 사용하고 싶다면 COMMIT - **COMMIT**하면 Session 메모리영역에 있던 쿼리작업내용이 File영역에 쓰여지고, 모든 접속자에게 알려짐 - create는 대상쿼리 아니기 때문에 File영역에 바로 써버림 - 대상쿼리가 하나로 완료되는 경우와 대상쿼리가 여러개 묶여서 완료되는 경우가 있음 * **COMMIT과 ROLLBACK은 DCL(Data Control Language)** * Transaction이 완료되면 COMMIT으로 완료명령을 내려야 함 - COMMIT이란 접속자 세션의 내용(메모리)을 File영역(HDD, SDD)에 기록하고 변경된 내용을 모든 접속자에게 통지하는 일 * **ROLLBACK**은 접속자 세션의 내용을 취소하는 것 - COMMIT된 데이터는 ROLLBACK되지 않는다 (메모리에 남은게 없기 때문) * autocommit을 설정하면 퀴리문 하나로 트랜잭션이 완료됨 - exit으로 나가게 되면 정상종료되면서 commit됨 ```sql -- autocommit 사용 명령 SET autocommit ON INSERT INTO Class4_info(num, name, ssn, age, eye, email, input_date) VALUES(3, 'sol', '930815-1234567', 26, 0.7, 'sol@sol.com', SYSDATE); INSERT INTO Class4_info (name, email, num, ssn, age, input_date, eye) VALUES ('영근', 'oh@test.com', 4, '900000-0000000', 28, SYSDATE, 0.8); COMMIT; -- 위와 같은 경우 INSERT 2개가 하나의 트랜잭션을 이룬다 라고 말할 수 있다. ``` * COMMIT을 하면 임시적인 메모리 데이터가 영속적인 파일 데이터로 저장됨 * 오라클에서 null은 아무 값이 없는 상태를 뜻함 ![02-02](/md/legacy-assets/oracle/02-02.webp) --- ### 연산자(오라클) * 연산에 사용되는 예약된 부호들 * 산술연산자 - +, -, *, /, mod() - mod() : 나눈 나머지를 구하는 함수 - SELECT의 조회컬럼에서 사용가능 * 붙임 연산자 - || - SELECT 조회컬럼에서 사용가능 * 관계 연산자 - \>, <, >=, <=, =, !=(<>) * 논리 연산자 - AND, OR, NOT - AND나 OR은 관계연산자를 붙일 때 사용 - AND는 전항과 후항이 모두 참일 때만 참 반환 - OR은 전항 또는 후항이 참이면 참 반환 + 전항 후항 모두 거짓일 때만 거짓 반환 * 문자열 연산자 - LIKE - '%'와 '_'와 같이 씀 * NULL 비교 연산자 - IS NULL, IS NOT NULL * 포함 연산자 - IN(), NOT IN() --- ### UPDATE ```sql UPDATE 테이블명 SET 컬럼명 = 변경할 값, 컬럼명 = 변경할 값, ... WHERE 컬럼명 = 기준값; ``` * 컬럼의 값을 변경할 때 사용 - **Transaction 대상쿼리, 작업 후 COMMIT/ROLLBACK 필수** * SUBQUERY 사용가능 * 기준값에 해당하는 모든 레코드의 컬럼값이 변경됨 - 0행 ~ n행이 변경이 됨 * WHERE는 조건 설정 - 컬럼명 연산자 기준값 논리연산자 컬럼명 연산자 기준값 + 연산자로 관계연산자가 들어옴(=, !=(<>), >, <, >=, <=) + 논리연산자로 여러 조건을 이을 수 있음 --- ### DELETE ```sql -- 모든 레코드 삭제 DELETE FROM 테이블명; -- 특정 레코드 삭제 DELETE FROM 테이블명 WHERE 컬럼명=기준값; ``` * 레코드를 삭제할 때 사용 - 쿼리로 컬럼의 값 하나를 없앨 순 없음(UPDATE를 해야 함) * **Transaction 대상쿼리, 작업 후 COMMIT/ROLLBACK 필수** * SUBQUERY 사용가능 --- ### TRUNCATE ```sql TRUNCATE TABLE 테이블명; ``` * DELETE보다 삭제 속도가 빠름 * truncate는 테이블의 레코드를 절삭할 때 사용, **rollback 불가능** - delete는 rollback이 가능 * WHERE절 없음, 모든 레코드를 자를 때 사용 * **DELETE + COMMIT = TRUNCATE** ![02-03](/md/legacy-assets/oracle/02-03.webp) --- ### ROLLBACK ```sql ROLLBACK; ``` * Transaction 취소 작업 - **메모리에서 작업한 내용을 다시 되돌리는 것** - 직전 COMMIT 이후까지 되돌림 * COMMIT된 데이터는 취소할 수 없다 * **SAVEPOINT와 함께 사용하면 특정 지점까지 취소시킬 수 있음** --- ### SAVEPOINT ```sql SAVEPOINT 저장점명; ... ROLLBACK TO 저장점명; ``` * **트랜잭션이 여러개의 쿼리로 이루어진 경우 SAVEPOINT를 사용** - **저장점들은 COMMIT되면 사라짐** - 같은 이름의 저장점을 생성하면 이전 저장점이 사라진다. * 쿼리를 수행하기 전 설정 * ROLLBACK에서 저장점을 지정하여 원하는 위치까지 되돌릴 수 있음 --- ![02-04](/md/legacy-assets/oracle/02-04.webp) --- ### DROP ```sql -- 테이블 삭제 DROP TABLE 테이블명; -- Oracle Version 10g 부터는 DROP된 테이블은 휴지통으로 들어간다 -- 휴지통 내용보기(sqlplus문) SHOW RECYCLEBIN -- 휴지통을 비우기 전까지 .DBF(table space)의 크기는 변하지 않는다 -- 테이블 명을 조회하면 삭제된 테이블명(BIN~~~)이 조회된다, 다른 작업은 아무것도 못 함 -- 휴지통 비우기(sqlplus문) PURGE RECYCLEBIN -- 휴지통 비워야 DBF 크기가 줄어든다. -- 휴지통에 버려진 테이블 복구(sqlplus문) -- 복구할 땐 원본 테이블명이 없어야 복구됨 FLASHBACK TABLE 테이블명 TO BEFORE DROP; ``` * **데이터베이스의 모든 객체를 삭제할 때 사용** * DDL(Data Definition Language) * SHOW, PURGE, FLASHBACK은 sqlplus문(오라클 한정일 수 있음) --- ### SELECT ```sql SELECT 컬럼명, 컬럼명 alias, 컬럼명 연산자, 함수(컬럼명) FROM 테이블명 WHERE 조건 - 모든 연산자가 올 수 있음 GROUP BY 그룹으로 묶을 컬럼 ORDER BY 정렬 ``` * 테이블에서 필요한 값 조회 - 원하는 값을 잘 뽑아내기위해 가장 잘 써야 함 * SELECT/FROM 필수, WHERE절, GROUP BY절, ORDER BY절 (절은 생략가능) --- ### ALIAS ```sql 컬럼명 alias 컬럼명 AS alias -- 테이블 alias는 조인하는 경우 아니면 잘 안씀 FROM 테이블명 alias -- 부여된 alias는 WHERE절에서 바로 아래에 있는 WHERE절에서 사용할 수 없음 -- 같은 컬럼을 다른 컬럼명으로 여러개 쓸 수 있다 SELECT num, name, input_date AS i_date, input_date AS i_date2 FROM CLASS4_INFO; ``` * 컬럼명 또는 테이블명에 사용하는 **별칭** * 쿼리 내에 긴 컬럼/테이블명을 짧게 줄일 수 있음 * **alias는 그 쿼리내에서만 사용** - 다음 쿼리까지 유지돼 반영되어 사용되진 않음 --- [숙제풀이](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181023/homework.sql)