1. 테이블 생성 시에 주의해야 할 몇 가지 규칙
- 테이블 이름을 지정하고 각 컬럼들은 괄호 “( )”로 묶어 지정한다.
- 컬럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
- 테이블명과 컬럼명은 반드시 문자로 시작해야되고, 벤더별로 길이에 대한 한계가 있다.
- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
2. 테이블 생성 예제에서 추가적인 주의 사항
- 테이블 생성 시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 컬럼명은 대문자로 만들어짐
- DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
- 컬럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.
ALTER TABLE
1. ADD COLUMN: 컬럼 추가 EX) ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));
2. MODIFY COLUMN: 컬럼의 데이터 유형, 디폴트값, NOTNULL 제약조건에 대한 변경
EX) ALTER TABLE PLAYER MODIFY (ADDRESS VARCHAR2(80) DEFAULT ‘기타’ NOT NULL)
- 크기를 늘릴 수는 있지만 줄이지는 못한다.
- NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 컬럼의 폭을 줄일 수 있다.
- NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.
- DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.
3. RENAME COLUMN: 컬럼명 변경할 때
EX) ALTER TABLE PLAYER RENAME COLUMN 변경해야 할 컬럼명 TO 새로운 컬럼명;
- RENAME TABLE은 테이블 명을 변경.. EX) RENAME 변경전 테이블명 TO 변경후 테이블명;
4. DROP CONSTRAINT: 제약조건 삭제 명령어
EX) ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
5. ADD CONSTRAINT: 제약조건을 추가하는 명령어
EX) ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명)
EX) ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);
DROP TABLE: 테이블 삭제 EX) DROP TABLE 테이블명 CASCADE CONSTRAINT;
- 실행결과에서 좌측 정렬되어있는 것은 문자 및 날짜 데이터이고.. 우측 정렬되어있는 것은 숫자 데이터임.
(1)과 (2)는 같은 결과 값이 나온다.
(1) SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE (JOB, DEPTNO) IN (('MANAGER' , '20'), ('CLERK', '30'));
(2) SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE (JOB = 'MANAGER' AND DEPTNO = '20') OR (JOB = 'CLERK' AND DEPTNO = '30');
서브 쿼리
- 메인쿼리의 컬럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.
- 항상 메인쿼리 레벨로 결과 집합이 생성된다. 예를들어.. 메인쿼리로 조직(1), 서브쿼리로 사원(M)테이블을 사용하면 결과 집합은 조직(1) 레벨이 된다.
- 단일행 또는 복수행 비교 연산자와 함께 사용 가능하다. 단일행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 복수행 비교연산자는 서브쿼리의 결과 건수와 상관 없다.
- 서브쿼리에서는 order by를 사용하지 못한다. Order by 절은 select절에서 오직 한 개만 올 수 있기 때문에 order by절은 메인쿼리의 마지막 문장에 위치해야 한다.
- 1) 비연관 서브쿼리: 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않는 형태의 서브쿼리이다. 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용된다.
- 2) 연관 서브쿼리: 서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태의 서브쿼리이다. 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용된다. Ex) exists 서브쿼리는 항상 연관 서브쿼리로 사용된다.
- FROM절에서 사용되는 서브쿼리를 인라인 뷰(동적 뷰)라고 한다. 서브쿼리의 컬럼은 메인쿼리에서 사용할 수 없으나.. 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같기 때문에 인라인 뷰의 컬럼은 자유롭게 참조될 수 있다.
뷰(View)사용의 장점
- 독립성: 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
- 편리성: 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 sql문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.
- 보안성: 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 컬럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.
그룹(GROUP) 함수(GROUP BY 절에 위치함)
- ROLLUP함수: 병렬로 수행하기 가능하기 때문에 효과적이고, 계층적 분류를 포함하고 있는 데이터의 집계에 적합하도록 되어 있다.
EX) 만약 GROUP BY ROLLUP(DNAME, JOB) 이면, DNAME에 대한 집계, ALL Departments + ALL Jobs 대한 집계 총 두 개에 대해 집계가 구해짐.
- CUBE함수: 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성함.
EX) GROUP BY CUBE(DNAME, JOB)이면, DNAME에 대한 집계, ALL Departments + JOB, ALL Departments + ALL Jobs 총 3개의 집계가 구해짐
- GROUPING SETS 함수: 원하는 부분의 소계만 손쉽게 추출함
EX) GROUP BY GROUPING SETS(DNAME, JOB)이면, ALL Departments + JOB, ALL Jobs + DNAME에 대한 집계
- GROUPING함수: 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시되고, 그 외는 0이 표시된다.
WINDOW함수
- LAG함수: 총 3개의 ARGUMENTS가 있는데.. 두번째 인자는 몇번째 앞의 행을 가져올지 결정하는 거고, 세번째 인자(숫자만 가능)는 예를 들어 파티션의 첫번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 바꿔줄 수 있다.
EX) 본인보다 입사일자가 두 명 앞선 사원의 급여를 본인의 급여와 함께 출력하고 싶을 때
SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER(ORDER BY HIREDATE) AS PREV_SAL
FROM EMP;
- LEAD함수: 몇번째 이후 행의 값을 가져올 수 있다.
- RATIO_TO_REPORT 함수: 파티션 내 전체 SUM(컬럼)값에 대한 행 별 칼럼 값의 백분율을 소수점으로 구할 수 있음.
EX) 만약 MGR=’234’인 행이 두 건 출력되고, 각각 SAL이 400, 600이라면, RATIO_TO_REPORT(SAL) OVER()는 0.4, 0.6이 된다.
- PERCENT_RANK 함수: 파티션별 윈도우에서 제일먼저 나오는 것을 0으로 제일 늦게 나오는 것을 1로 함.
EX) 3 건이 출력됐을 때, 첫번째 PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R 값은 0, 두번째, 0.5, 세번째 값은 1이 된다.
- CUME_DIST함수: 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율
EX) 3건이 출력됐을 때, 0.3333, 0.6667, 1.0000이 출력됨.
- NTILE함수: 파티션 별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있다. NTILE(4)의 의미는 만약 14명의 팀원이 조회가 됐다면.. 14의 팀원을 4개의 조로 나눈다는 의미이다. 전체 14명을 4개의 집합으로 나누면 (3*4 +2=14) 3명이 4개의 집합으로 이루어지고 두명이 남는데.. 나머지 두명은 앞의 조부터 할당된다. 즉, 4명+4명+3명+3명으로 조를 나누게 됨.
EX) SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;
유저와 권한
- GRANT CREATE USER TO SCOTT;
- GRANT CREATE TABLE TO SCOTT;
- 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.
EX) MENU 테이블을 조회할 수 있도록 SCOTT한테 권한을 부여함 GRANT SELECT ON MENU TO SCOTT;
- OBJECT 권한은 SELECT, INSERT, UPDATE, DELETE 등의 권한을 따로따로 관리한다.
ROLE: 유저들과 권한들 사이에서 중개 역할을 함.
- ROLE에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, ROLE은 유저에게 직접 부여될 수도 있고 다른 ROLE에 포함하여 유저에게 부여될 수도 있다.
- REVOKE CREATE SESSION, CREATE TABLE FROM PJS; -- 우선 권한을 뺏고
- CREATE ROLE LOGIN_TABLE;
- GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
- GRANT LOGIN_TABLE TO PJS; -- 권한을 다시 줌.
- ORCLE에서는 기본적으로 몇가지 ROLE을 제공하고 있다. 그 중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE이다.
- DROP USER PJS CASCADE; -- PJS 유저가 만든 MENU 테이블도 같이 삭제됨
- GRANT CONNECT, RESOURCE TO PJS;
절차형 SQL
- PL/SQL 특징 : 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다. 이런 PL/SQL을 이용하여 다양한 저장 모듈을(Stored module)을 개발할 수 있다. 저장 모듈이란 pl/sql 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 sql 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행도리 수 있는 완전한 실행 프로그램이다. Oracle의 저장 모듈에는 procedure, user defined function, trigger 가 있다.
- 특징으로는
1) Block 구조로 되어있어 각 기능별로 모듈화가 가능하다
2) 변수, 상수 등을 선언하여 sql 문장 간 값을 교환한다.
3) DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
4) 여러 SQL 문장을 Block으로 묶고 한번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
- PL/SQL 에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 하며, 그 결과 역시 반드시 하나여야 한다. 조회 결과가 없거나 하나 이상인 경우에는 에러를 발생시킨다.
- USER Defined Function은 프로시저처럼 절차형 sql 을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다. Function이 프로시저와 다른점은 return을 사용해서 하나의 갑을 반드시 되돌려 줘야 한다는 것이다.
- Trigger란: 특정한 테이블에 INSERT, UPDATE, DELETE 와 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다 즉 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다.
- Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 trigger와 각 행에 대해서 발생되는 trigger가 있다.
- ROLLBACK을 하면 하나의 트랜잭션이 취소가 되어 Trigger로 입력되는 정보까지 하나의 트랜잭션으로 인식하여 두 테이블 모두 입력 취소가 된다. Trigger는 데이터베이스에 의해 자동 호출되지만 결국 insert, updae, delete문과 하나의 트랜잭션 안에서 일어나는 일련의 작업들이라 할 수 있다.
- Trigger는 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용될 수 있다.
- COMMIT, ROLLBACK으로 실행 안 된다.