1. 테이블을 통째로 20100702101051 시간을 기준으로 복사
CREATE TABLEe table_070210 AS
(
SELECT * FROM table_name
AS OF TIMESTAMP TO_TIMESTAMP('20100702101051', 'YYYYMMDDHH24MISS')
);
2. 하나의 ROW만 복구 하는거
INSERT INTO table_name VALUE
(
SELECT * FROM table_name
AS OF TIMESTAMP TO_TIMESTAMP('2010-12-03 10:10:51', 'YYYY-MM-DD HH24:MI:SS')
WHERE idx=06002995
);
Oracle
** 윈도우 서버 HOST명은 제어판 -> 시스템 -> 컴퓨터 이름의 전체 컴퓨터 이름이다
SQL*PLUS
** 사용자의 생성 및 변경, 권한 부여는 SYS와 SYSTEM 사용자만이 할 수 있다
사용자
** 사용자가 사용할 테이블스페이스를 만든 다음 사용자 생성시 해당 테이블스페이스를 지정한다
SYS/CHANGE_ON_INSTALL AS SYSDBA /* 오라클 슈퍼 사용자 ID */
SYSTEM/MANAGER /* 오라클 데이타베이스를 유지보수 관리할때 사용하는 사용자 ID */
SCOTT/TIGER /* SAMPLE 사용자 ID */
HR/HR /* SAMPLE 사용자 ID */
CREATE USER 사용자명 IDENTIFIED BY 패스워드; /* 사용자 생성과 패스워드 설정 */
CREATE USER 사용자명 IDENTIFIED BY 패스워드 /* 사용자 생성시 테이블스페이스 지정 */
DEFAULT TABLESPACE 테이블스페이스명
TEMPORARY TABLESPACE 테이블스페이스명;
ALTER USER 사용자명 DEFAULT TABLESPACE 테이블스페이스명; /* 사용자 디폴트 테이블스페이스 변경 */
ALTER USER 사용자명 IDENTIFIED BY 패스워드; /* 사용자 패스워드 변경 */
ALTER USER 사용자명 PASSWORD EXPIRRE; /* 사용자 패스워드 무효 */
ALTER USER 사용자명 ACCOUNT LOCK; /* 사용자 접속 금지 */
ALTER USER 사용자명 ACCOUNT UNLOCK; /* 사용자 접속 허용 */
DROP USER 사용자명 /* 사용자 삭제 */
DROP USER 사용자명 INCLUDING CONTENTS; /* 사용자와 관련된 모든 정보 삭제 */
권한
** 사용자를 만든후 해당 사용자에게 권한을 부여 해야 한다
** 오브젝트 권한 ALL(ALTER, DELETE, INDEX, INSERT, REFERENCE, SELECT, UPDATE)을 사용하여
테이블에 한번에 모든 권한을 부여할 수 있거나 원하는 권한만 부여할 수 있다
뷰는 DELETE, INSERT, SELECT, UPDATE, 시퀀스는 ALTER, SELECT, 프로시저는 EXECUTE 권한을 줄 수 있다
** 120여개나 되는 권한을 일일이 부여하는 단점을 해결하기 위해 ROLE을 사용한다
GRANT 권한1, 권한2, ... TO 사용자명 /* 사용자에 권한 부여 */
GRANT CREATE SESSION TO 사용자명; /* 데이타베이스에 접속할 수 있는 권한 */
GRANT CREATE TABLE TO 사용자명; /* 테이블을 만들수 있는 권한 */
GRANT SELECT ON 테이블명 TO 사용자명; /* 테이블의 SELECT 권한 */
GRANT ALL ON 테이블명 TO 사용자명; /* 테이블에서 처리할 수 있는 모든 권한 */
GRANT EXECUTE 프로시저명 TO 사용자명; /* 프로시저를 실행할 수 있는 권한 */
GRANT SELECT ON A사용자명.테이블명 to B사용자명 /* A사용자 테이블의 SELECT 권한을 B사용자에게 부여 */
REVOKE 권한1, 권한2, ... FROM 사용자명; /* 사용자의 권한 취소 */
REVOKE CREATE SESSION FROM 사용자명; /* 데이타베이스에 접속할 수 있는 권한 취소 */
REVOKE CREATE VIEW FROM 사용자명; /* 뷰를 만들수 있는 권한 취소 */
CONNECT /* 데이타베이스에 접속할 수 있는 권한을 가진 롤 */
RESOURCE /* 테이블, 인덱스, 뷰등을 생성, 변경, 삭제할 수 있는 권한을 가진 롤 */
DBA /* 데이타베이스를 관리하기 위해 필요한 권한을 가진 롤 */
RXP_FULL_DATABASE /* EXPORT 툴을 사용할 수 있는 권한을 가진 롤 */
IMP_FULL_DATABASE /* IMPORT 툴을 사용할 수 있는 권한을 가진 롤 */
SYSDBA /* 데이타베이스 시작과 종료및 모든 관리를 하기 위한 필요한 권한을 가진 롤 */
SYSOPER /* SYSDBA 권한을 포함 데이타베이스를 생성할 수 있는 권한을 가진 롤 */
GRANT 롤1, 롤2, ... TO 사용자명; /* 사용자에게 롤 권한 부여 */
REVOKE 롤1, 롤2, ... FROM 사용자명; /* 사용자의 롤 권한 취소 */
로그온
CONNECT 또는 CONNECT 사용자ID/패스워드
자료 사전
** 특정 테이블에 관련된 자료사전이 어떤 것인지 알고 싶을때는 DICTIONARY 테이블의
COMMENTS 칼럼에 특정 테이블과 관련된 문자열을 찾아보면 된다
DBA_xxxx /* DB 전체의 관련된 정보로 SYS, SYSTEM 사용자만 볼 수 있다 */
ALL_xxxx /* 자신이나 다른 사용자가 생성한 오브젝트중에 자신이 볼 수 있는 정보 */
USER_xxxx /* 자신이 생성한 오브젝트 정보 */
V$_xxxx /* DB의 성능 분석, 통계 정보를 제공하면 X$의 뷰 */
X$_xxxx /* DB의 성능 분석, 통계 정보 */
SELECT * FROM DICTIONATY; /* 모든 상태 정보 */
SELECT * FROM ALL_USERS; /* 사용자 ID 리스트 */
SELECT * FROM USER_USERS; /* 사용자 정보 */
SELECT * FROM USER_CONSTRAINTS; /* 제약 정보 */
SELECT * FROM USER_TABLESPACES /* 테이블스페이스 정보 */
SELECT * FROM USER_TABLES; /* 테이블 정보 */
SELECT * FROM DBA_ROLES; /* 전체 롤, SYS, SYSTEM에서 사용 */
SELECT * FROM USER_ROLE_PRIVS; /* 현재 사용자에게 부여된 롤 확인 */
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE=롤명; /* 해당 롤이 어떤 권한을 가지고 있는지 확인 기/
SELECT * FROM USER_TAB_PRIVS_RECD; /* 오브젝트에서 부여 받은 권한 */
TableSpace(테이블스페이스)
- SYSTEM 테이블스페이스
** 사용자 생성시 별도의 테이블스페이스를 지정하지 않으면 시스템으로 지정된다
시스템이 사용자 데이타 포함을 허용하더라도 사용하지 말아야 한다
데이타베이스와 함께 생성되고 모든 데이타베이스에서 필요하다
데이타 딕셔너리 정보를 포함한다
시스템 실행 취소 세그먼트를 포함한다
- UNDO 테이블스페이스
SQL문을 실행한 후 트랜잭션을 ROLLBACK 하기 위해 변경 이전의 데이타를 저장한다
- TEMP 테이블스페이스
SQL문을 실행할 때 내부 분류작업(GROUP BY, ORDER BY, ...)을 위해 사용한다
USERS 테이블스페이스
사용자가 테스트할 수 있는 저장공간이다
- CREATE TABLESPACE 테이블스페이스명 /* 자동 확장 테이블스페이스를 만들기 */
DATAFILE 디렉토리와파일명 SIZE 크기
AUTOEXTEND ON NEXT 크기 MAXSIZE 크기; /* 자동으로 NEXT 만큼 확장 MAXSIZE는 지정하지 않아도 된다 */
ALTER TABLESPACE RENAME 테이블스페이스명 TO 새테이블스페이스명; /* 테이블스페이스명 변경 */
ALTER TABLESPACE 테이블스페이스명 RENAME /* 물리적인 데이타 파일 변경 */
DATAFILE 'c:\oracle\oradata\jerry01.dbf' TO 'c:\oracle\oradata\aaa\jerry01.dbf';
ALTER USER 사용자명 TEMPORARY TABLESPACE 템프테이블스페이스명; /* 사용자의 기본 템프데이블스페이스 변경 */
DROP TABLESPACE 테이블스페이스명 INCLUDING CONTENTS;
** 테이블스페이스를 삭제하더라도 관련 데이타 파일은 삭제되지 않기 때문에
탐색기에서 직접 삭제해야 한다
테이블 스페이스 및 사용자 생성
** 테이블 스페이스는 저장 영역 및 임시 테이블 스페이스 이렇게 두게가 필요하지만
1. CREATE TABLESPACE ts_name
DATAFILE '/home/oracle/data/ts_name.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M
2. CREATE TEMPORARY TABLESPACE ts_name_temp TEMPFILE '/home/oracle/data/ts_name_temp.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
** 임시 테이블 스페이스는 기본으로 정해져 있는 TEMP TABLESPACE 영역을 사용해도 된다
3. CREATE USER 사용자명 IDENTIFIED BY 패스워드
DEFAULT TABLESPACE ts_name
TEMPORARY TABLESPACE ts_name_temp
4. GRANT CONNECT, RESOURCE TO 사용자명
Table(테이블)
** 제약 조건에는 PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, NOT NULL이 있다
CREATE TABLE 테이블명 (칼럼 데이타형 제약, ...); /* 테이블 만들기 */
CREATE TABLE 테이블명 (칼럼1 데이타형, ... /* 기본 키 설정 */
CONSTRAINT 이름 RPIMARY KEY (칼럼1));
ALTER TABLE 테이블명 DROP PRIMARY KEY; /* 기본 키 삭제 */
ALTER TABLE 테이블명 DROP CONSTRAINT 제약명; /* 제약 조건 삭제 */
ALTER TABLE 테이블명 MODIFY (칼럼 데이타형, ...); /* 칼럼의 타입 변경 */
ALTER TABLE 테이블명 ADD (칼럼 데이타형, ...); /* 칼럼 추가 */
ALTER TABLE 테이블명 DROP COLUMN 칼럼; /* 칼럼 삭제 */
ALTER TABLE 테이블명 RENAME COLUMN 칼럼명 TO 새칼럼명 /* 칼럼명 변경 */
ALTER TABLE 테이블명 MOVE TABLESPACE 테이블스페이스명; /* 테이블의 테이블스페이스 변경 */
DROP TABLE 테이블명; /* 테이블 삭제 */
RENAME 테이블명 TO 새테이블명; /* 테이블명 변경 */
TRUNCATE TABLE 테이블명; /* 테이블내의경 데이타 삭제 */
SELECT * FROM 테이블명; /* 현재 사용자인 경우 사용자명은 생략 */
SELECT * FROM 사용자명.테이블명; /* 다른 사용자인 경우 해당 사용자명을 지정 */
SELECT * FROM DBA_CONSTRAINTS /* 테이블별 CONSTRAINT(제약, PRIMARY KEY, FOREIGN KEY..) */
WHERE OWNER=사용자명 AND TABLE_NAME=테이블명;
SELECT USER FROM DUAL; /* 현재 사용자명 확인 */
SELECT SYSDATE FROM DUAL; /* 오늘 날짜 확인 */
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;
** 오라클에서는 테이블없이 SELECT문의 결과를 보고자 할때 DUAL을 사용한다
DESC 테이블명; /* 테이블 구조 */
Sequence(시퀀스)
** MSSQL, MySql의 자동 증가와 같은 역할을 한다
SEQUENCE가 한번 증가되면 데이타가 삭제 되어도 다시 사용할 수 없다
CREATE SEQUENCE 시퀀스명 INCREMENT BY n /* 증가값 */
START WITH n /* 시작값 */
MAXVALUE n | NOMAXVALUE /* 최대값/최대값 지정하지 않음 */
MINVALUE n | NOMINVALUE /* 최소값/최소값 지정하지 않음 */
CYCLE | NOCYCLE /* CYCLE은 최대값까지 증가가 완료되면 다시 START로 돌아간다
NOCYCLE은 증가가 완료되면 에러를 발생시킨다 */
CACHE n | NOCACHE /* 시퀀스를 메모리에서 관리하며 기본값은 20 */
CREATE TABLE Temp (id number, name varchar(20));
CREATE SEQUENCE sqe (INCREMENT BY 1, START WITH 1, NOCYCLE, NOCACHE);
INSERT INTO Temp (id, name) VALUES (seq.NEXTVAL, '홍길동');
Synonym(시노늄)
** 이름이 긴 다른 사용자의 객체를(사용자명.테이블명) 요약 해서 사용한다
PUBLIC 키워드는 데이타베이스내 모든 사용자들이 시노늄을 사용할 수 있게 한다
CREATE PUBLIC SYNONYM 시노늄명 FOR 사용자명.테이블명;
CREATE PUBLIC SYNONYM Emp FOR hr.employee;
SELECT * FROM Emp;
View(뷰)
** REPLACE 명령을 사용하면 만들거나 수정할때 마다 다시 컴파일을 한다
CREATE OR REPLACE VIEW 뷰명 AS SELECT 칼럼1, 칼럼2, ... FROM 테이블명 WHERE 조건;
DROP VIEW 뷰명;
Procedure(프로시저)
CREATE OR REPLACE PROCEDURE 프로시저명 /* 프로시저 생성 */
(파라미터1 데이타타입, 파라미터2 OUT 데이타타입, 파라미터3 IN OUT 데이타타입)
IS
변수 선언;
BEGIN
프로그램;
END;
DROP PROCEDURE 프로시저명; /* 프로시저 삭제 */
MSSQL
** SQL문은 대소문자를 구분하지 않지만 데이타는 대문자와 소문자를 구분한다
주석을 다는 방법은 /* 주석 */ 이거나 한줄만 주석 처리를 할 경우는 문장 맨앞에 --를 붙인다
** 각각의 데이타베이스의 SYSOBJECTS 테이블에 해당 데이타베이스의 모든 정보가 보관되어 있다
SYSOBJECTS의 TYPE 칼럼으로 'U'=사용자 테이블, 'P'=저장 프로시저, 'K'=프라이머리 키, 'F'=포린 키,
'V'=뷰, 'C'=체크 제약등 오브젝트 이름과 정보를 알 수 있다
데이타 검색
USE 데이타베이스명 /* USE 문을 사용한 데이타베이스 선택 */
SELECT * FROM 데이블명 /* 모든 칼럼 불러오기 */
SELECT TOP n * FROM 테이블명 /* 상위 n개의 데이타만 가져오기 */
SELECT 칼럼1, 칼럼2, 칼럼3 FROM 테이블명 /* 특정 칼럼 가져오기 */
SELECT 칼럼1 별명1, 칼럼2 AS 별명2 FROM 테이블명 /* 칼럼에 별명 붙이기 */
SELECT 칼럼3 '별 명3' FROM 테이블명 /* 칼럼 별명에 스페이스가 들어갈 경우는 작은따옴표 사용 */
SELECT DISTINCT 칼럼 FROM 테이블명 /* 중복되지 않는 데이타만 가져오기 */
** 데이타는 오름차순으로 재배열된다
DISTINCT를 사용하면 재배열이 될때까지 데이타가 리턴되지 않으므로 수행 속도에 영향을 미친다 */
SELECT * FROM 테이블명 WHERE 조건절 /* 조건에 해당하는 데이타 가져오기 */
** 조건식에 사용하는 비교는 칼럼=값, 칼럼!=값, 칼럼>값, 칼럼>=값, 칼럼<값, 칼럼<=값이 있다
문자열은 ''(작은따옴표)를 사용한다
날짜 비교를 할때는 'yy-mm-dd' 형식의 문자열로 한다(날짜='1992-02-02', 날짜>'1992-02-02')
SELECT * FROM 테이블명 WHERE 칼럼 BETWEEN x AND y /* 칼럼이 x>=와 y<=사이의 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 IN (a, b...) /* 칼럼이 a이거나 b인 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 LIKE '패턴' /* 칼럼이 패턴과 같은 데이타 가져오기 */
** 패턴에 사용되는 기호는 %, _가 있다
'k%'(k로 시작되는), '%k%'(중간에 k가 있는), '%k'(k로 끝나는)
'p_'(p로 시작하는 2자리), 'p___'(p로 시작하는 4자리), '__p'(3자리 데이타중 p로 끝나는)
SELECT * FROM 테이블명 WHERE 칼럼 IS NULL /* 칼럼이 NULL인 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 NOT BETWEEN x AND y /* 칼럼이 x와 y 사이가 아닌 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 NOT IN (a, b...) /* 칼럼이 a나 b가 아닌 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 NOT LIKE '패턴' /* 칼럼이 패턴과 같지 않은 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 IS NOT NULL /* 칼럼이 NULL이 아닌 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼>=x AND 칼럼<=y
SELECT * FROM 테이블명 WHERE 칼럼=a or 칼럼=b
SELECT * FROM 데이블명 WHERE 칼럼1>=x AND (칼럼2=a OR 칼럼2=b)
** 복수 조건을 연결하는 연산자는 AND와 OR가 있다
AND와 OR의 우선순위는 AND가 OR보다 높은데 우선 순위를 바꾸고 싶다면 ()을 사용한다
SELECT * FROM 테이블명 ORDER BY 칼럼 /* 칼럼을 오름차순으로 재배열하기 */
SELECT * FROM 테이블명 ORDER BY 칼럼 ASC
SELECT * FROM 테이블명 ORDER BY 칼럼 DESC /* 칼럼을 내림차순으로 재배열하기 */
SELECT * FROM 테이블명 ORDER BY 칼럼1 ASC, 칼럼2 DESC /* 복수 칼럼 재배열하기 */
SELECT * FROM 테이블명 ORDER BY 1 ASC, DESC 3 /* 칼럼 순서로 재배열하기 */
** 기본적으로 SELECT 문에서는 출력순서가 보증되지 않기 때문에
데이타의 등록 상태나 서버의 부하 상태에 따라 출력되는 순서가 달라질 수 있다
따라서 출력하는 경우 되도록이면 ORDER BY를 지정한다
** 칼럼 번호는 전체 칼럼에서의 번호가 아니라 SELECT문에서 선택한 칼럼의 번호이고 1부터 시작한다
연산자
** 1순위는 수치 앞에 기술되는 + - 같은 단항 연산자
2순위는 사칙 연산의 산술 연산자인 * / + -
3순위는 = > 비교 연산자
4순위는 AND OR 같은 논리 연산자
()을 붙이면 우선 순위를 바꿀수 있다
1. SELECT 문의 연산
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명
2. ORDER BY 구의 연산
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 ORDER BY 칼럼3+칼럼4 DESC
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 ORDER BY 3 DESC
3. WHERE 구의 연산
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 WHERE 칼럼2>=(칼럼3+칼럼4)
4. NULL 연산
SELECT 칼럼1, 칼럼2, ISNULL(칼럼3, 0) + ISNULL(칼럼4, 0) AS '별명' FROM 테이블명
** 수치형 데이타와 NULL값과의 연산 결과는 항상 NULL이다
만약 NULL 값을 원치 않으면 ISNULL(칼럼, 기준값) 함수를 사용해서 기준값을 변환시킨다
5. 날짜 연산
SELECT GETDATE() /* 서버의 현재 날짜를 구한다 */
SELECT 날짜칼럼, 날짜칼럼-7 FROM 테이블명
SELECT 날짜칼럼, 날짜칼럼+30 FROM 테이블명
SELECT 날짜칼럼, DATEDIFF(day, 날짜칼럼, GETDATE()) FROM 테이블명
** 날짜의 가산과 감산은 + -로 할 수 있다
날짜와 날짜 사이의 계산은 DATEDIFF(돌려주는값, 시작날짜, 끝날짜) 함수를 사용한다
6. 문자 연산
SELECT 칼럼1 + 칼럼2 FROM 테이블명
SELECT 칼럼 + '문자열' FROM 테이블명
SELECT 칼럼1 + '문자열' + 칼럼2 FROM 테이블명
** 기본 연결은 문자와 문자이고 문자와 숫자의 연결은 CONVERT 함수를 사용해야 한다
함수
1. 수치 함수
ROUND(수치값, 반올림위치) /* 반올림 및 자르기 */
ABS(수치 데이타) /* 절대값 */
SIGN(수치 데이타) /* 부호 */
SQRT(수치값) /* 제곱근 */
POWER(수치값, n) /* n승 */
2. 문자열 함수
SUBSTRING(문자열, 위치, 문자수) /* 특정 위치 문자열 추출 */
LEFT(문자열, 문자수) /* 왼쪽부터 지정한 갯수 문자열 추출 */
RIGHT(문자열, 문자수) /* 오른쪽부터 지정한 갯수 문자열 추출 */
LEN(문자열) /* 문자열의 문자수 */
LTRIM(문자열) /* 문자열 왼쪽의 스페이스를 삭제 */
RTRIM(문자열) /* 문자열 오른쪽의 스페이스를 삭제 */
UPPER(문자열) /* 대문자로 변환 */
LOWER(문자열) /* 소문자로 변환 */
** 위치나 문자수는 한글일 경우 2바이트가 한글자로 취급된다
SELECT LEN('월abcd화수목')은 8을 돌려준다
3. 날짜 함수
GETDATE() /* 현재 날짜와 시각 */
DATEADD(돌려주는값, 기간, 날짜) /* 날짜 및 시간의 더하기와 빼기 */
DATEDIFF(돌려주는값, 시작날짜, 끝날짜) /* 두 날짜와 시간의 차이 */
DATEPART(돌려주는값, 날짜) /* 날짜와 시간의 특정값을 돌려준다 */
** 돌려주는값(약어)
Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk,
Hour-hh, Minute-mi, Second-ss, Milisecond-ms
SELECT DATEADD(dd, 7, 날짜칼럼)
4. 형변환 함수
CONVERT(데이타 타입, 칼럼) /* 칼럼을 원하는 데이타 타입으로 변환 */
CONVERT(데이타 타입, 칼럼, 날짜형 스타일) /* 원하는 날짜 스타일로 변환 */
CAST(칼럼 AS 데이타 타입) /* 칼럼을 원하는 데이타 타입으로 변환 */
** 스타일
1->mm/dd/yy, 2->yy.mm.dd, 3->dd/mm/yy, 4->dd.mm.yy, 5->dd-mm-yy,
8->hh:mm:ss, 10->mm-dd-yy, 11->yy/mm/dd, 12->yymmdd
SELECT CONVERT(varchar(10), 날짜칼럼, 2)
그룹화
함수
SELECT COUNT(*) FROM 테이블명 /* 전체 데이타의 갯수 가져오기 */
SEELECT COUNT(칼럼) FROM 테이블명 /* NULL은 제외한 칼럼의 데이타 갯수 가져오기 */
SELECT SUM(칼럼) FROM 테이블명 /* 칼럼의 합계 구하기 */
SELECT MAX(칼럼) FROM 테이블명 /* 칼럼의 최대값 구하기 */
SELECT MIN(칼럼) FROM 테이블명 /* 칼럼의 최소값 구하기 */
SELECT AVG(칼럼) FROM 테이블명 /* 칼럼의 평균값 구하기 */
GROUP BY문
SELECT 칼럼 FROM 테이블명 GROUP BY 칼럼
SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 GROUP BY 칼럼1
SELECT 칼럼1, COUNT(*) FROM 테이블명 GROUP BY 칼럼1
SELECT 칼럼1, 칼럼2, MAX(칼럼3) FROM 테이블명 GROUP BY 칼럼1, 칼럼2
** GROUP BY를 지정한 경우 SELECT 다음에는 반드시 GROUP BY에서 지정한 칼럼 또는
그룹 함수만이 올 수 있다
조건
SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 GROUP BY 칼럼1 HAVING SUM(칼럼2) < a
SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 ORDER BY 칼럼1 COMPUTE SUM(칼럼2)
** HAVING: 그룹 함수를 사용할 경우의 조건을 지정한다
HAVING의 위치: GROUP BY의 뒤 ORDER BY의 앞에 지정한다
COMPUTE: 각 그룹의 소계를 요약해서 보여준다
ORDER BY가 항상 선행해서 나와야 한다
조건절의 서브 쿼리
** SELECT 또는 INSERTY, UPDATE, DELETE 같은 문의 조건절에서 SELECT문을 또 사용하는 것이다
SELECT문 안에 또 다른 SELECT문이 포함되어 있다고 중첩 SELECT문(NESTED SELECT)이라고 한다
** 데이타베이스에는 여러명이 엑세스하고 있기 때문에 쿼리를 여러개 나누어서 사용하면 데이타의 값이
달라질수 있기때문에 트랜잭션 처리를 하지 않는다면 복수의 쿼리를 하나의 쿼리로 만들어 사용해야 한다
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼2 = (SELECT 칼럼2 FROM 테이블명 WHERE 조건)
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼1 IN (SELECT 칼럼1 FROM 테이블명 WHERE 조건)
** 서브 쿼리에서는 다른 테이블을 포함할 수 있다
두개의 테이블에서 읽어오는 서브쿼리의 경우 서브 쿼리쪽에 데이타가 적은 테이블을 주 쿼리쪽에 데이타가
많은 테이블을 지정해야 처리 속도가 빨라진다
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼1 IN (SELECT 칼럼2-1 FROM 테이블명2 WHERE 조건)
** FROM구에서 서브 쿼리를 사용할 수 있다
사용시 반드시 별칭을 붙여야 하고 처리 속도가 빨라진다
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건1 AND 조건2
SEELCT 칼럼1, 칼럼2 FROM (SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건1) 별칭 WHERE 조건2
데이타 편집
추가
** NULL 값을 허용하지도 않고 디폴트 값도 지정되어 있지 않은 칼럼에 값을 지정하지 않은채
INSERT를 수행하면 에러가 발생한다
** 수치값은 그대로 문자값은 ''(작은따옴표)로 마무리 한다
** SELECT INTO는 칼럼과 데이타는 복사하지만 칼럼에 설정된 프라이머리, 포린 키등등의 제약 조건은
복사되지 않기 때문에 복사가 끝난후 새로 설정해 주어야 한다
INSERT INTO 테이블명 VALUES (값1, 값2, ...) /* 모든 필드에 데이타를 넣을 때 */
INSERT INTO 테이블명 (칼럼1, 칼럼2, ...) VALUES (값1, 값2, ...) /* 특정 칼럼에만 데이타를 넣을 때 */
INSERT INTO 테이블명 SELECT * FROM 테이블명2 /* 이미 존재하는 테이블에 데이타 추가 */
INSERT INTO 테이블명(칼럼1, 칼럼2, ...) SELECT 칼럼1, 칼럼2, ...) FROM 테이블명2
SELECT * INTO 테이블명 FROM 테이블명2 /* 새로 만든 테이블에 데이타 추가 */
SELECT 칼럼1, 칼럼2, ... 테이블명 FROM 테이블명2
갱신
UPDATE 테이블명 SET 칼럼1=값1, 칼럼2=값2 /* 전체 데이타 갱신 */
UPDATE 테이블명 SET 칼럼1=값1, 칼럼2=값2 WHERE 조건 /* 조건에 해당되는 데이타 갱신 */
삭제
DELETE FROM 테이블명 /* 전체 데이타 삭제 */
DELETE FROM 테이블명 WHERE 조건 /* 조건에 해당되는 데이타 삭제 */
오브젝트
** 데이타베이스는 아래 오브젝트들을 각각의 유저별로 관리를 하는데 Schema(스키마)는 각 유저별 소유 리스트이다
1. Table(테이블)
** CREATE일때 프라이머리 키를 설정하지 않는다면 (칼럼 int IDENTITY(1, 1) NOT NULL) 자동 칼럼을 만든다
데이타들의 입력 순서와 중복된 데이타를 구별하기 위해서 반드시 필요하다
** 테이블 정보 SP_HELP 테이블명, 제약 조건은 SP_HELPCONSTRAINT 테이블명 을 사용한다
CREATE TABLE 데이타베이스이름.소유자이름.테이블이름 (칼럼 데이타형 제약, ...) /* 테이블 만들기 */
DROP TABLE 테이블명 /* 테이블 삭제 */
ALTER TABLE 테이블명 ADD 칼럼 데이타형 제약, ... /* 칼럼 추가 */
ALTER TABLE 테이블명 DROP COLUMN 칼럼 /* 칼럼 삭제 */
** DROP COLUMN으로 다음 칼럼은 삭제를 할 수 없다
- 복제된 칼럼
- 인덱스로 사용하는 칼럼
- PRIMARY KEY, FOREGIN KEY, UNIQUE, CHECK등의 제약 조건이 지정된 칼럼
- DEFAULT 키워드로 정의된 기본값과 연결되거나 기본 개체에 바인딩된 칼럼
- 규칙에 바인딩된 칼럼
CREATE TABLE 테이블명 (칼럼 데이타형 DEFAULT 디폴트값, ...) /* 디폴트 지정 */
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 UNIQUE, ...) /* 유니크 설정 */
** UNIQUE란 지정한 칼럼에 같은 값이 들어가는것을 금지하는 제약으로 기본 키와 비슷하지만
NULL 값을 하용하는것이 다르다
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 NOT NULL, ...) /* NOT NULL 설정 */
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 PRIMARY KEY, ...) /* 기본 키 설정 */
** 기본 키는 유니크와 NOT NULL이 조합된 제약으로 색인이 자동적으로 지정되고 데이타를
유일하게 만들어 준다
** 기본 키는 한 테이블에 한개의 칼럼만 가능하다
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 FOREIGN KEY REFERENCES 부모테이블이름(부모칼럼), ...)
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 CHECK(조건), ...) /* CHECK 설정 */
** CHECK는 조건을 임의로 정의할 수 있는 제약으로 설정되면 조건을 충족시키는 데이타만
등록할 수 있고 SELECT의 WHERE구와 같은 조건을 지정한다
** CONSTRAINT와 제약 이름을 쓰지 않으면 데이타베이스가 알아서 이름을 붙이지만
복잡한 이름이 되기 때문에 되도록이면 사용자가 지정하도록 한다
** CONSTRAINT는 칼럼과 데이타형을 모두 정의한 뒤에 맨 마지막에 설정할 수 있다
CREATE TABLE 테이블명 (칼럼1 데이타형,
칼럼2 데이타형, ...
CONSTRAINT 이름 PRIMARY KEY(칼럼1)
CONSTRAINT 이름 CHECK(칼럼2 < a) ...)
ALTER TABLE 테이블명 ADD CONSTRAINT 이름 제약문 /* 제약 추가 */
ALTER TABLE 테이블명 DROP CONSTRAINT 제약명 /* 제약 삭제 */
ALTER TABLE 테이블명 NOCHECK CONSTRAINT 제약명 /* 제약 효력 정지 */
ALTER TABLE 테이블명 CHECK CONSTRAINT 제약명 /* 제약 효력 유효 */
** 제약명은 테이블을 만들때 사용자가 지정한 파일 이름을 말한다
2. View(뷰)
** 자주 사용하는 SELECT문이 있을때 사용한다
테이블에 존재하는 칼럼들중 특정 칼럼을 보이고 싶지 않을때 사용한다
테이블간의 결합등으로 인해 복잡해진 SELECT문을 간단히 다루고 싶을때 사용한다
** 뷰를 만들때 COMPUTE, COMPUTE BY, SELECT INTO, ORDER BY는 사용할 수 없고
#, ##으로 시작되는 임시 테이블도 뷰의 대상으로 사용할 수 없다
** 뷰의 내용을 보고 싶으면 SP_HELPTEXT 뷰명 을 사용한다
CREATE VIEW 뷰명 AS SELECT문 /* 뷰 만들기 */
CREATE VIEW 뷰명 (별칭1, 별칭2, ...) AS SELECT문 /* 칼럼의 별칭 붙이기 */
CREATE VIEW 뷰명 AS (SELECT 칼럼1 AS 별칭1, 칼럼2 AS 별칭2, ...)
ALTER VIEW 뷰명 AS SELECT문 /* 뷰 수정 */
DROP VIEW 뷰명 /* 뷰 삭제 */
CREATE VIEW 뷰명 WITH ENCRYPTION AS SELECT문 /* 뷰 암호 */
** 한번 암호화된 뷰는 소스 코드를 볼 수 없으므로 뷰를 암호화하기전에
뷰의 내용을 스크립트 파일로 저장하여 보관한다
INSERT INTO 뷰명 (칼럼1, 칼럼2, ...) VALUES (값1, 값2, ...)
UPDATE 뷰명 SET 칼럼=값 WHERE 조건
** 원래 테이블에 있는 반드시 값을 입력해야 하는 칼럼이 포함되어 있지 않거나
원래 칼럼을 사용하지 않고 변형된 칼럼을 사용하는 뷰는 데이타를 추가하거나
갱신할 수 없다
** WHERE 조건을 지정한 뷰는 뷰를 만들었을때 WITH CHECK OPTION을 지정하지 않았다면
조건에 맞지 않는 데이타를 추가할 수 있지만 뷰에서는 보이지 않는다
또한 뷰를 통해서 가져온 조건을 만족하는 값도 뷰의 조건에 만족하지 않는 값으로도
갱신할 수 있다
CREATE VIEW 뷰명 AS SELECT문 WITH CHECK OPTION
** 뷰의 조건에 맞지 않는 INSERT나 UPDATE를 막을려면 WITH CHECK OPTION을 설정한다
3. Stored Procedure(저장 프로시저)
** 데이타베이스내에서 SQL 명령을 컴파일할때 캐시를 이용할 수 있으므로 처리가 매우 빠르다
반복적으로 SQL 명령을 실행할 경우 매회 명령마다 네트워크를 경유할 필요가 없다
어플리케이션마다 새로 만들 필요없이 이미 만들어진 프로시저를 반복 사용한다
데이타베이스 로직을 수정시 프로시저는 서버측에 있으므로 어플리케이션을 다시 컴파일할 필요가 없다
** 저장 프로시저의 소스 코드를 보고 싶으면 SP_HELPTEXT 프로시저명 을 사용한다
CREATE PROC 프로시저명 AS SQL문 /* 저장 프로시저 */
CREATE PROC 프로시저명 변수선언 AS SQL문 /* 인수를 가지는 저장 프로시저 */
CREATE PROC 프로시저명 WITH ENCRYPTION AS SQL문 /* 저장 프로시저 보안 설정 */
CREATE PROC 프로시저명 /* RETURN 값을 가지는 저장 프로시저 */
인수1 데이타형, ... 인수2 데이타형 OUTPUT
AS
SQL문
RETURN 리턴값
DROP PROCEDURE 프로시저명1, 프로시저명2, ... /* 저장 프로시저 삭제 */
명령어
BEGIN ... END /* 문장의 블록 지정 */
DECLARE @변수명 데이타형 /* 변수 선언 */
SET @변수명=값 /* 변수에 값 지정 */
PRINT @변수명 /* 한개의 변수 출력 */
SELECT @변수1, @변수2 /* 여러개의 변수 출력 */
IF 조건 /* 조건 수행 */
수행1
ELSE
수행2
WHILE 조건1 /* 반복 수행 */
BEGIN
IF 조건2
BREAK - WHILE 루프를 빠져 나간다
CONTINUE - 수행을 처리하지 않고 조건1로 되돌아간다
수행
END
EXEC 저장프로시저 /* SQL문을 실행 */
EXEC @(변수로 지정된 SQL문)
GO /* BATCH를 구분 지정 */
에제
1. 기본 저장 프로시저
CREATE PROC pUpdateSalary AS UPDATE Employee SET salary=salary*2
2. 인수를 가지는 저장 프로시저
CREATE PROC pUpdateSalary
@mul float=2, @mul2 int
AS
UPDATE Employee SET salary=salary* @Mul* @mul2
EXEC pUpdateSalary 0.5, 2 /* 모든 변수에 값을 대입 */
EXEC pUpdateSalary @mul2=2 /* 원하는 변수에만 값을 대입 */
3. 리턴값을 가지는 저장 프로시저
CREATE PROC pToday
@Today varchar(4) OUTPUT
AS
SELECT @Today=CONVERT(varchar(2), DATEPART(dd, GETDATE()))
RETURN @Today
DECLARE @answer varchar(4)
EXEC pToday @answer OUTPUT
SELECT @answer AS 오늘날짜
4. 변수 선언과 대입, 출력
** @는 사용자 변수이고 @@는 시스템에서 사용하는 변수이다
DECLARE @EmpNum int
SET @EmpNum=10
SELECT @EmpNum
4. Trigger(트리거)
** 한 테이블의 데이타가 편집(INSERT/UPDATE/DELETE)된 경우에 자동으로 다른 테이블의
데이타를 삽입, 수정, 삭제한다
** 트리거 내용을 보고 싶으면 SP_HELPTRIGGER 트리거명 을 사용한다
CREATE TRIGGER 트리거명 ON 테이블명 FOR INSERT AS SQL문 /* INSERT 작업이 수행될때 */
CREATE TRIGGER 트리거명 ON 테이블명 AFTER UPDATE AS SQL문 /* UPDATE 작업이 수행되고 난 후 */
CREATE TRIGGER 트리거명 ON 테이블명 INSTEAD OF DELETE AS SQL문
DROP TRIGGER 트리거명
5. Cursor(커서)
** SELECT로 가져온 결과들을 하나씩 읽어들여 처리하고 싶을때 사용한다
** 커서의 사용방법은 OPEN, FETCH, CLOSE, DEALLOCATE등 4단계를 거친다
** FETCH에는 NEXT, PRIOR, FIRST, LAST, ABSOLUTE {n / @nvar}, RELATIVE {n / @nvar}가 있다
SET NOCOUNT ON /* SQL문의 영향을 받은 행수를 나타내는 메시지를 숨긴다 */
DECLARE cStatus SCROLL CURSOR /* 앞뒤로 움직이는 커서 선언 */
FOR
SELECT ID, Year, City FROM aPlane
FOR READ ONLY
OPEN cStatus /* 커서를 연다 */
DECLARE @ID varchar(50), @Year int, @City varchar(50), @Status char(1)
FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */
WHILE @@FETCH_STATUS=0 /* 커서가 가르키는 결과의 끝까지 */
BEGIN
IF @Year <= 5 SET @Status='A'
ELSE IF @Year> 6 AND @Year <= 9 SET @Status='B'
ELSE SET @Status='C'
INSERT INTO aPlane(ID, City, Status) VALUES(@ID, @Year, @Status)
FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */
END
CLOSE cStaus /* 커서를 닫는다 */
DEALLOCATE cStatus /* 커서를 해제한다 */
보안과 사용자 권한
** 보안의 설정 방법은 크게 WINDOWS 보안과 SQL 보안으로 나뉘어 진다
** 사용자에게 역할을 부여하는데는 서버롤 데이타베이스롤이 있다
1. SA(System Administrator)
** 가장 상위의 권한으로 SQL 서버에 관한 전체 권한을 가지고 모든 오브젝트를 만들거나
수정, 삭제할 수 있다
2. DBO(Database Owner)
** 해당 데이타베이스에 관한 모든 권한을 가지며 SA로 로그인해서 데이타베이스에서 테이블을
만들어도 사용자는 DBO로 매핑된다
** 테이블이름의 구조는 서버이름.데이타베이스이름.DBO.테이블이름이다
3. DBOO(Database Object Owner)
** 테이블, 인덱스, 뷰, 트리거, 함수, 스토어드 프로시저등의 오브젝트를 만드는 권한을 가지며
SA나 DBO가 권한을 부여한다
4. USER(일반 사용자)
** DBO나 DBOO가 해당 오브젝트에 대한 사용 권한을 부여한다
MySql
** COMMAND LINE에서의 실행을 기준으로 한다
시작하기, 끝내기
MYSQL -u 사용자명 -p /* 관리자 계정은 디폴트로 ROOT */
MYSQL -u 사용자명 -p 데이타베이스명 /* 시작할때 데이타베이스를 선택한다 */
MYSQL -u 사용자명 데이타베이스명 /* 암호가 지정되어 있지 않을 경우 */
USE 데이타베이스명 /* 데이타베이스 선택 */
SELECT VERSION(), CURRENT_DATE; /* 버전과 오늘 날짜 */
SELECT USER(); /* 사용자 */
STATUS /* 상태 */
QUIT /* 끝내기 */
패스워드 설정 및 변경
USE MYSQL /* MYSQL 데이타베이스를 선택 */
UPDATE USER SET PASSSWORD=PASSWORD('패스워드') WHERE USER='사용자명';
FLUSH PRIVILEGES; /* 변경 패스워드를 적용 */
데이타베이스 생성 및 선택
SHOW DATABASES; /* 데이타베이스 리스트 */
CREATE DATABASE 데이타베이스명; /* 새 데이타베이스 만들기 */
ALTER DATABASE 데이타베이스명 DEFAULT CHARACTER SET 문자셋; /* 문자셋 지정. 4.1버전 이상 */
DROP DATABASE 데이타베이스명; /* 데이타베이스 삭제 */
USE 데이타베이스명; /* 데이타베이스 선택 */
사용자 생성 및 권한
** ROOT 관리자 계정으로 접속한다
1.
USE MYSQL /* MYSQL 데이타베이스 선택 */
5.0 버전 이전
INSERT INTO USER (HOST, USER, PASSWORD) VALUES('localhost', '사용자명', PASSWORD('패스워드'));
5.0 버전 이후
INSERT INTO USER /* USER 테이블에 사용자 등록 */
(HOST, USER, PASSWORD, ...
VALUES('localhost', '사용자명', PASSWORD('패스워드'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
'', '', '', '', 0, 0, 0, 0);
** USER 테이블의 HOST, USER, PASSWORD 이외의 나머지 칼럼 갯수만큼 처리
위의 조건은 관리자에 해당하므로 권한에 제약을 두고 싶다면 각 칼럼별 'Y', 'N'를
구분해 주어야 한다
FLUSH PRIVILEGES; /* 사용자 등록 적용 */
2.
GRANT ALL ON *.* /* 모든 데이타베이스에 접근 허용 */
TO 사용자명@LOCALHOST IDENTIFIED BY '패스워드'
GRANT ALL ON 데이타베이스명.* /* 패스워드없이 지정한 데이타베이스에 접근 허용 */
TO 사용자명@LOCALHOST
GRANT ALL ON 데이타베이스명.* /* 지정한 데이타베이스에 접근 허용 */
TO 사용자명@LOCALHOST IDENTIFIED BY '패스워드'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON 테이타베이스명.* /* LOCALHOST의 데이타베이스에 접근 허용 */
TO 사용자명@LOCALHOST IDENTIFIED BY '패스워드';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON 테이타베이스명.* /* LOCALHOST가 아닌 다른 곳에서의 접속 허용 */
TO 사용자명@'%' IDENTIFIED BY '패스워드';
SELECT HOST, USER, PASSWORD FROM USER; /* 사용자 리스트 */
DELETE FROM USER WHERE USER='사용자명' AND HOST= '호스트명'; /* 사용자 삭제 */
SHOW GRANTS FOR 사용자명@localhost; /* 사용자의 권한 상태 */
SHOW GRANTS FOR CURRENT_USER; /* 현재 사용자의 권한 상태 */
테이블
SHOW TABLES; /* 테이블 리스트 */
CREATE TABLE 테이블명 (칼럼 데이타형 NOT NULL AUTO_INCREMENT); /* 자동 증가값 만들기 */
CREATE TABLE 테이블명 (칼럼 데이타형 제약, ... /* 기본 키 설정 */
PRIMARY KEY (칼럼));
CREATE TABLE 테이블명 (칼럼 데이타형 제약, ... /* 외래 키 설정 */
FOREIGN KEY(칼럼) REFERENCES 부모테이블명 (부모칼럼));
ALTER TABLE 테이블명 RENAME 새테이블명; /* 테이블명 변경 */
ALTER TABLE 테이블명 MODIFY 칼럼 데이타형 제약; /* 칼럼의 형식 변경 */
ALTER TABLE 테이블명 CHANGE 칼럼 새칼럼 데이타형; /* 칼럼명과 형식 변경 */
ALTER TABLE 테이블명 ADD 칼럼 데이타형 제약; /* 칼럼 추가 */
ALTER TABLE 테이블명 ADD INDEX (칼럼); /* 인덱스 칼럼 만들기 */
ALTER TABLE 테이블명 DROP COLUMN 칼럼; /* 칼럼 삭제 */
LOAD DATA INFILE 'TXT파일' INTO TABLE EXPORT FIELDS TERMINATED BY ' '; /* TXT파일을 DB에 읽어오기 */
SELECT * FROM EXPORT INTO OUTFILE 'txt파일' FILEDS TERMINATED BY ' '; /* DB를 TXT파일로 내보내기 */
** FIELDS TERMINATED BY는 '\t', ',', ';'등 원하는 구분자를 지정하면 된다
DROP TABLE 테이블명; /* 테이블 삭제 */
DESC 테이블명; /* 테이블 구조 */
인덱스
** 적절한 인덱스 사용은 SELECT문의 성능을 크게 향상 시킨다
최대 16개까지 지정할 수 있다
최대 인덱스는 256바이트까지 가능하다
CAHR, VARCHART에 대해서 앞 문자열로 인덱스 할 수 있다
인덱스는 테이블에서 MUL 로 표시된다.
CREATE TABLE 테이블명 (칼럼 데이타형 제약, ... /* 인덱스 만들기 */
INDEX 인덱스명(칼럼));
** INDEX (칼럼), KEY 인덱스명 (칼럼)으로 해도 된다
SHOW KEYS FROM 테이블명; /* 인덱스 리스트 */
Create Or Replace Procedure p_logiN(
inUserid In member.id%type,
inPwd In member.puw%type)
IS
v_pwd member.pwd%type;
Begin
Select pwd Into v_pwd
From member
Where id=inUserid;
ifv_pwd<>inPwd then
DBMS_Out.put_line('패스위드 오류');
else
DBMS_Out.put_line('정상적으로 로그인 됨');
end if
Exception
When No_Date_Found Then
DBMS_Out.put_line('유저아이디'||inUserid||'는 존재하지 않습니다.');
DBMS_Out.put_line(v_last_name||' '||v_sal);
SQL
SQL문
SELECT
DML(데이터 조작어)
INSERT, UPDATE, DELETE
DDL(데이터 정의어) IMPLICIT COMMIT
CREATE, ALTER, DROP, RENAME, TRUNCATE
TCL(트랜잭션 제어)
COMMIT, ROLLBACK, SAVEPOINT
DCL(데이터 제어어)IMPLICIT COMMIT
GRANT, REVOKE
[1] Writing Basic SQL Statements
1. SELECT 기본 문장( 선택, 프로잭션, 조인)
SELECT [DISTINCT] { *, column [alias], ... }
FROM table ;
2. SELECT 예제
SELECT * FROM dept ;
SELECT deptno, loc FROM dept ;
SELECT ename, sal, 12 * (sal + 100) FROM emp ;
* Null 값과 연산을 하면 Null이 나온다.
3. Column Alias 예제
SELECT ename AS nme, sal salary
FROM emp ;
SELECT ename "Name", sal*12 "Annual Salary"
FROM emp ;
* 대소문자를 구분하고 공백있는 컬럼 Alias를 만들고 싶을땐 " "로 막는다.
* AS는 안 써도 된다.
* WHERE, GROUP BY절에는 안된다. ORDER BY 절에는 사용 가능.
4. Concatenation 연산자 (|| : pipeline 2개)
SELECT ename||job "Employees" FROM emp ;
--> ename 데이터와 job 데이터가 붙어서 출력된다.
5. 문자열을 데이터로 출력할때
SELECT ename||' '||'is a'||' '||job "Employee Details"
FROM emp ;
* 문자열은 ' '로 막고, 컬럼 Alias는 " "로 막는다.
6. DISTINCT keyword : 중복된 Row를 하나로 만들어 준다. 자동 SORTING[ASC]
SELECT DISTINCT deptno
FROM emp ;
* DISTINCT 대신 UNIQUE를 써도 된다.
7. SQL*Plus Log On 방법
* UserName, PassWord, HostString에 일일이 입력해도 되지만,
UserName에 username/password@HostString이라고 입력하면 된다.
* UNIX상에서 command로 들어 갈 때는 sqlplus username/password 만 입력하면된다.
8. 테이블 구조보는 SQL Command (DESC)
SQL> DESC dept : Column Name, Null?, Data Type display
9. SQL*Plus Editing Commands( 다음 행까지 계속하려면 -(하이픈)으로 연결한다.)
① A[PPEND] text : 현재 line의 마지막 문장 뒤에 text를 붙인다.
② C[HANGE]/old/new : 현재 line의 old text를 new text로 바꾼다.
③ C[HANGE]/text/ : 현재 line을 text를 삭제한다.
④ CL[EAR] BUFF[ER] : buffer의 내용을 모두 지운다.
⑤ DEL : 현재 line을 지운다.
⑥ DEL n : n번째 line을 지운다.
⑦ DEL m n : m ~ n번째 line을 지운다.
⑧ I[NPUT] : 현재 line 다음에 line이 제한없이 추가된다.
⑨ I[NPUT] text : 현재 line 다음에 line이 추가되면서 text가 들어간다.
⑩ L[IST] : buffer전체를 보여준다.
⑪ L[IST] n : n번째 line을 보여준다.
⑫ R[UN] or / : SQL, PL/SQL문장을 실행하라!
⑬ n : n번째 line을 display하면서 Editing 상태로 해준다.
⑭ n text : n번째 line이 text로 바뀐다.
⑮ 0 text : 1번째 line이 추가되면서 text가 1번째 line으로 들어간다.
* Bald로 표시된 명령어는 line번호를 먼저 수행한 후 실행해야 한다.
10. SQL*Plus File Commands
① SAV[E] filename [REP[LACE]|APP[END]] : buffer의 내용을 filename.sql로 저장한다.
② GET filename : filename.sql을 buffer로 불러온다.
③ START filename : filename.sql을 실행하라.
④ @filename : START filename과 같다.
⑤ ED[IT] : buffer의 내용을 edit program으로 실행한다.
⑥ ED[IT] filename : filename.sql을 edit program으로 실행한다.
⑦ SPO[OL] filename : retrieve data를 filename.lst로 저장한다.
⑧ SPOOL OFF : SPOOL을 끝내라.
⑨ SPOOL OUT : retrieve data를 system printer로 출력하라.
⑩ EXIT : SQL*Plus를 종료한다.
* SPOOL 사용법
SQL> spool filename
SQL> select ...
SQL> spool off
11. Special Tip
* 잠시 host상태로 나가고 싶을 때.
SQL> ! ( $)
-- host 상에서 다시 SQL로 들어가려면 exit(lo)
-- unix 상에서 env를 치면 오라클 환경을 볼 수 있다.
* SQL> define -editor
--> Editor가 vi인지..다른 edit프로그램인지를 보여준다.
* line size 바꾸기
SQL> SET PAGESIZE 20 -- 한 page를 20line으로 보여준다.
-- log off하면 사라진다.
* NLS값 보기
SQL> select * from V$NLS_PARAMETERS
* NLS값 바꾸기
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'
-- SESSION : 현 session동안만 YYYY-MM-DD 포맷으로 사용한다는 뜻.
sqlplus가 종료되면 원상태로 복구된다.
[2]Restricting and Sorting Data
1. 비교연산자
= : Equal to
> : Grater than
>= : Greater than or equal to
< : Less than
<= : Less than or equal to
<> : Not equal to
예) SELECT ename, sal, comm
FROM emp
WHERE sal <= comm ;
2. 비교연산자 2
BETWEEN A AND B , IN(list), LIKE, IS NULL
3. BETWEEN 연산자( NOT BETWEEN )
SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1000 AND 1500 ; --> sal >= 1000 and sal <= 1500
4. IN 연산자( NOT IN )
SELECT emp, ename, sal, mgr
FROM emp
WHERE mgr IN (7902, 7566, 7788) ; --> mgr = 7902 or mgr = 7566 or mgr = 7788
5. LIKE 연산자( NOT LIKE )
예1) SELECT ename
FROM emp
WHERE ename LIKE 'S%' ; --> ename이 S로 시작하는 모든 데이터를 찾는다.
예2) SELECT ename
FROM emp
WHERE ename LIKE '_A%' ; --> 두 번째 글자가 A인 모든 데이터를 찾는다.
예3) SELECT ename
FROM emp
WHERE ename LIKE '%A/_%B' ESCAPE '/' ;
--> '/'는 Escape문자로 정의되었기 때문에 '_'도 문자로 인식한다.
즉, ename이 A_로 포함하는 모든 데이터를 찾는다.
* ESCAPE는 모든 문자가 가능하다.
6. IS NULL 연산자
SELECT ename, mgr
FROM emp
WHERE mgr IS NULL ; --> mgr이 null인 데이터를 찾는다.
7. Logical 연산자
AND : 두 개의 조건이 모두 만족해야 OK
OR : 한 개의 조건만 만족하면 OK
NOT
8. AND 연산자
SELECT empno, ename, job, sal
FROM emp
WHERE sal >= 1100
AND job = 'CLERK' ;
9. OR 연산자
SELECT empno, ename, job, sal
FROM emp
WHERE (sal >= 1100 OR job = 'CLERK') ;
10. NOT 연산자
SELECT ename, job
FROM emp
WHERE job NOT IN ('CLERK', 'MANAGER') ;
--> NOT (job = 'CLERK OR job = 'MANAGER')
11. 연산자 우선순위( 산술 > 문자 > 비교 > 논리 )
1 : 모든 비교 연산자
2 : NOT
3 : AND --> False > Null > True
4 : OR --> True > Null > False
예) A AND B에서...
- A가 False이고 B가 Null이면... False 이다.
- A가 True이고 B가 Null이면.... Null이다.
12. SORT (ORDER BY)
* ASC는 default값이다.(작은 값부터..)
* 값이 Null일 때 가장 큰 값이 된다. (ASC일 때...맨 뒤에 붙는다.)
* column alias도 sorting이 된다.
예1) SELECT ename, job, deptno, hiredate "Date"
FROM emp
ORDER BY hiredate["Date" or 4 ] ;
예2) SELECT ename, job, deptno, hiredate
FROM emp
ORDER BY hiredate DESC ;
예3) SELECT empno, ename, sal*12 annsal
FROM emp
ORDER BY empno, annsal ; * select 절에 없는 열을 기준으로 정렬 가능.
예4) SELECT ename, deptno, sal
FROM emp
ORDER BY deptno, sal DESC
[3] Single-Row Functions
1. Character Functions
①LOWER( column|expression )
LOWER('String') --> string : 소문자로 변환
②UPPER( column|expression )
UPPER('String') --> STRING : 대문자로 변환
③INITCAP( column|expression )
INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환
④CONCAT( column1|expression1 ,column2|expression2 )
CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.
⑤SUBSTR(column|expression, m [,n]) : m값이 음수면 문자값의 끝부터..)
SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.
⑥LENGTH( column|expression )
LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.
⑦INSTR( column|expression, )
INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.
⑧LPAD( column|expression,n,'string' ) : n 은 전체 길이
LPAD('String',10,'*') --> ****String
: 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)
⑨ RPAD('String',10,'*') --> String****
: 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)
⑩ LTRIM(' String') --> 'String' : 문자열의 왼쪽 공백을 버린다.
⑪ RTRIM('String ') --> 'String' : 문자령의 오른쪽 공백을 버린다.
* TRIM(leading/tailing/both, trim_character FROM trim_source )
TRIM( 'S' FROM 'SSMITH') --> MITH
2. Number Functions
① ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.
② TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.
③ MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.
* ROUND예제(WHOLE NUMBER:정수)
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL
==> 45.92 46 50
* TRUNC예제
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL
==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40
* SYSTEM 날짜를 갖고 오는 방법.
SELECT sysdate FROM dual
3. Date 계산( 날짜를 숫자로 저장)
date + number : date에 number만큼 후의 날자를 보여준다.
date - number : date에 number만큼 전의 날자를 보여준다.
date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )
date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.
4. Date Functions
MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194
; 두날짜 사이의 달수를 보여준다.
ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94
; 날짜에 6개월을 더한 날자를 보여준다.
NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'
; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.
('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)
LAST_DAY('01-SEP-95') --> '30-SEP-95'
; 해당월의 마지막날자를 보여준다.
ROUND('25-JUL-95','MONTH')--> 01-AUG-95 ROUND('25-JUL-95','YEAR')--> 01-JAN-96
TRUNC('25-JUL-95','MONTH') --> 01-JUL-95 TRUNC('25-JUL-95','YEAR') --> 01-JAN-95
5. Conversion Functions
nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호
TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.
- Date Format Elements
YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)
MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC
D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)
DD --> 07 (달의 일출력)
DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.
DAY --> MONDAY (요일출력) DY-->MON
CC --> 20 (몇 세기인지를 보여준다.)
WW --> 그 해의 몇 번째 주인가를 리턴한다.
W --> 그 달의 몇 번째 주인가를 리턴한다.
* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.
HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)
* 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER
*숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)
ddspth : 14-> fothteenth
* / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.
TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.
- Number Format Elements
9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234
0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로
L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)
TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.
TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.
6. NVL Funcion : 값이 null일 때 설정값을 보여준다.
NVL(number_column, 0) : null일 때 0을 보여준다.
NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.
NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.
* column Type과 표현식의 type이 반드시 일치해야 한다.
7. DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.
*DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])
F1 (F2 (F3 (col,arg1),arg2),arg3)
[4] Displaying Data from Multiple Tables (JOIN)
1. EquiJoin : column1과 column2는 Primary Key와 Foreign Key관계인 경우
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno
2. Non-EquiJoin : Join하는 Table 사이에 서로 대응하는 Key가 없는 경우
where절의 Join조건에 '='을 제외한 비교연산자를 사용한다.
SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
3. Outer Join : 서로 = 되지 않는 row 까지도 모두 보여준다.
정보가 없는쪽 컬럼 뒤에 (+)를 붙인다.( =, and 만 사용가능)
SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno (+) = d.deptno
ORDER BY e.deptno
4. Self Join : 같은 Table을 그것이 마치 2개의 Table인 것처럼 Join해서 사용한다.
SELECT worker.ename, manager.ename
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno
5. SET OPERATORS
UNION: 중복된 row는 제외하고 보여준다. UNION ALL : 중복된 row까지 모두 보여준다. INTERSECT :A,B의 중복된 row만 보여준다.MINUS : A,B의 중복된 row를 제외한 A row를 보여준다.
[5] Aggregating Data Using Group Functions(그룹함수를 사용한 데이터집계)
1. Group Function : 행집합에 적용하여 그룹당 하나의 결과를 생성한다.
AVG([DISTINCT|ALL] n) : 평균값
COUNT({*|[DISTINCT|ALL] expr}) : row수
MAX([DISTINCT|ALL] expr) : 최대값
MIN([DISTINCT|ALL] expr) : 최소값
SUM([DISTINCT|ALL] n) : 합
STDDEV([DISTINCT|ALL] x) : 표준편차
VARIANCE([DISTINCT|ALL] x) : 분산
* count(*)를 제외한 모든 Group Function은 Null을 배제하고 수행한다.
Null을 포함하고 싶다면 NVL함수를 사용한다.
* DISTINCT나 ALL을 쓰지 않으면 Default가 ALL이다.
* AVG,SUM, STDDEV, VARIANCE는 반드시 숫자형이다.
2. 어떤 컬럼에 해당하는 데이터별 그룹함수를 사용할 때
SELECT [deptno,] COUNT(ename)
FROM emp --> 이문장은 성립되지 않는다. GROUP BY가 없다.
<추가>
GROUP BY deptno
*일반칼럼과 그룹함수를 같이 쓰면 group by절에 일반칼럼 명시(열 별칭 사용못함)
*GROUP BY 열을 SELECT 절에 포함시키지 않아도 된다
3. 그룹함수는 WHERE절에 올수가 없다.
SELECT deptno, AVG(sal)
FROM emp
WHERE AVG(sal) > 2000
GROUP BY deptno
--> 이문장은 성립되지 않는다. WHERE절에 그룹함수가 올 수 없다.
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
4. HAVING : 그룹함수를 조건절에 적용시키기 위해서 사용한다.
SELECT job, SUM(sal)
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal)
*절 평가 순서 : ① WHERE -> ② GROUP BY절 -> ③ HAVING절
*그룹함수는 두번까지 중첩될수 있습니다. MAX( AVG(SAL))
[6] Subqueries( WHERE 절, HAVING 절, FROM 절 )
1. Subquery 규칙
- 반드시 ()로 묶어야 한다.
- 반드시 비교연산자 오른쪽에 위치해야 한다.
- ORDER BY 절에는 사용할 수 없다.
- 서브쿼리 결과가 한개일때는 Single-Row 비교연산자를 사용해야 한다.
( =, >, >=, <, <=, <> )
- 서브쿼리 결과가 여러개일때는 Multi-Row 연산자를 사용해햐 한다.
2. Subquery 사용예1
* 반드시 비교연산자 오른쪽에 써야한다.
SELECT ename FROM emp
WHERE sal > (SELECT sal FROM emp
WHERE empno = 7566)
3. Subquery 사용예2
SELECT ename, job
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE empno = 7369)
AND
sal > (SELECT sal
FROM emp
WHERE empno = 7876)
4. Subquery 사용예3
SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) > (SELECT MIN(AVG(sal))
FROM emp
GROUP BY job)
5. Subquery 사용예4 (Subquery 결과가 여러개가 나올때 비교연산자 사용법)
SELECT empno, ename
FROM emp
WHERE sal IN (SELECT MIN(sal) ( =ANY 와 같음 )
FROM emp
GROUP BY deptno)
6. ANY(동의어:SOME)연산자를 사용한 Subquery : 조건중에 한개만 만족하면 OK
SELECT empno, ename, job
FROM emp
WHERE sal < ANY (SELECT sal --> OR
FROM emp
WHERE job = 'CLERK')
AND job <> 'CLERK'
7. ALL연산자를 사용한 Subquery : 모든 조건을 만족해야 OK
SELECT empno, ename, job
FROM emp
WHERE sal > ALL (SELECT avg(sal) --> AND
FROM emp
GROUP BY deptno
[7] Multiple-Column Subqueries
1. Multiple-Column Subquery (Pairwise Subquery)
: 서브쿼리의 결과가 두개 이상의 컬럼형식으로 나올 때 비교하는 컬럼도 서브쿼리 컬럼
갯수와 형식이 같아야한다.
SELECT ename, deptno, sal, comm
FROM emp
WHERE (sal, NVL(comm,-1)) IN
(SELECT sal, NVL(comm,-1)
FROM emp
WHERE deptno = 30)
2. NonPairwise Subquery
SELECT ename, deptno, sal, comm
FROM emp
WHERE sal IN (SELECT sal FROM emp
WHERE deptno = 30)
AND
NVL(comm,-1) IN (SELECT NVL(comm,-1) FROM emp
WHERE deptno = 30)
3. Subquery 안에 Null값이 있을 때...
* 매니저가 아닌 사원을 보여주기?
SELECT e.ename
FROM emp e
WHERE e.empno NOT IN
(SELECT m.mgr FROM emp m)
IN은 Null value가 나와도 한개의 조건만 만족하면 OK이지만,
NOT IN은 !=ALL과 같아서 모든조건이 TRUE여만 TRUE인 것이다.
* NOT IN 을 !=ANY 로 바꿔주는게 정답에 가깝다.
4. FROM절에 사용되는 Subquery
SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a,
(SELECT deptno, avg(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg
5. EXISTS : EXISTS 뒤에 나오는 서브쿼리의 결과가 한건이 row라도 있다면 O.K
SELECT dname, deptno
FROM dept
WHERE EXISTS (SELECT * FROM emp
WHERE dept.deptno = emp.deptno)
: 행의 존재유무만 확인
[8] Producing Readable Output with SQL*Plus
1. Substitution Variable (치환변수)
& : 변수가 한 번 사용되고 메모리에서 사라진다.
&& : 한 번 입력한 변수는 CLEAR하기 전까지 계속 메모리에 남아있다.
DEFINE variable=value : CHAR 데이터 유형의 사용자 변수를 생성하여 값을 변수에 할당한다.
DEFINE : 현재 DEFINE 된 모든 것들을 보여준다.
DEFINE 변수명 : 지정한 변수명의 DEFINE 사항을 보여준다.
UNDEFINE 변수명 : 지정한 변수명의 DEFINE 상태를 CLEAR한다.
ACCEPT : 변수를 입력하라고 물어보는 Prompt의 Text를 변경할수 있다. USER가
입력하는 값을 HIDE시킬수가 있다. DataType의 Format을 변경할수가 있다.
* SET VERIFY 는 SQL*PLUS 가 치환변수를 값으로 바꾸기 전후의 명령 텍스트 표시를 토글한다.
2. & 치환변수 사용예
SELECT empno, ename, sal, deptno
FROM emp
WHERE empno = &employee_num
3. && 치환변수 사용 예 --> 한번만 물어보게 된다.
SELECT empno, ename, job, &&column_name
FROM emp
ORDER BY &column_name
4. 변수 값이 character or date value일때 꼭 single quotation mark로 묶어 줘야 한다.
SELECT ename, deptno, sal*12
FROM emp
WHERE job = '&job_title'
5. 종합적인 예제(실행중에 열이름, 표현식, 텍스트를 지정)
--> 어떤 절에나 사용해도 된다.
SELECT empno, ename, job, &column_name
FROM emp
WHERE &condition
ORDER BY &order_column
6. ACCEPT 사용예 (file로 만들어서 START시켜야 한다.)
*ACCEPT variable [datatype] [FORMAT format] [PROMPT text] [HIDE]
ACCEPT dept PROMPT 'Provide the department name: '
SELECT *
FROM dept
WHERE dname = UPPER('&dept')
/
Provide the department name: Sales
처리된 결과값...
7. 파일로 만들어서 치환변수를 여러개 처리할때 예제
test.sql
SELECT &1 FROM &2
SQL> @test empno emp
==> &1에 empno, &2에 emp가 들어간다.(define)
SELECT &2 FROM &4
SQL> @test e empno b emp
==> &1에 e, &2에 empno,&3에 b, &4에 emp가 들어간다.(define)
8. SET command 변수
① ARRAY[SIZE] {20 | n} : 데이터 fatch size
② COLSEP {_ | text} : column seperator
③ FEED[BACK] {6 | n | OFF | ON} : n 개이상의 레코드 반환시 레코드 수를 표시
④ HEA[DING] {OFF | ON} : column heading 출력
⑤ LIN[ESIZE] {80 | n} : 가로 80 으로 출력
⑥ PAGES[IZE] {50 | n} : 세로 50 으로 출력
⑦ LONG {80 | n} : long date type의 데이터를 출력할때 80byte까지만 보여주겠다.
⑧ PAU[SE] {OFF | ON | text} : text에 문자열을 넣으면 pause될 때마다 메시지를 보여준다.
⑨ TERM[OUT] {OFF | ON} : 결과를 화면에 보여주겠는가?
⑩ VERIFY {OFF | ON} : 치환변수의 old값과 new값을 보여주면서 결과가 처리된다.
* SHOW {SET command 명} : SET command명의 현재 상태를 보여준다. SQL> SHOW ECHO)
* SHOW ALL : 모든 SET command의 현재 상태를 보여준다.
* SET 상태를 바꿔 주려면... SQL> SET PAUSE ON <-- 이런식으로 하면 된다.
* DEFINE command나 SET command는 자신의 환경파일(login.sql)에 일괄적으로 처리 할수 있다.
* login.sql은 oracle superuser용 환경파일이다.
* SQL Plus command가 다음 줄로 이어질 때는 (-)으로 연결한다.
9. REPORT 출력 예
① SET PAGESIZE 37 --> 세로 37로 출력
② SET LINESIZE 60 --> 가로 60으로 출력
③ SET FEEDBACK OFF
④ TTITLE 'Employee|Report' --> Top Title을 Employee 다음 줄에 Report를 쓰겠다.
⑤ BTITLE 'Confidential' --> Bottom Title을 Confidential로 쓰겠다.
⑥ COLUMN job HEADING 'Job|Category' FORMAT A15 : | 은 text를 두줄로 찍는다.
--> job column Heading을 Job 다음줄에 Category로쓰고 15byte의 자리수로
만들겠다.
형식 : COL[UMN] [{column | alias } [option]]
COL[UMN] [column], COL[UMN] column CLE[AR], CLE[AR] COL[UMN]
옵션 : CLE[AR], FOR[MAT] format, HEA[DING] text, JUS[TIFY] {align}, NUL[L] text,
NOPRI[NT]:열을 숨김니다 <> PRI[NT],
TRU[NCATED] : 첫 행 끝에 표시되는 문자열을 잘라버린다.
WRA[PPED] : 문자열의 끝을 다음 행으로 줄바꿈합니다.
⑦ REM ** Insert SELECT statement --> 주석문
*BREAKE 명령?
[9] Multipulating Data (DML)
*트랜젝션 : 논리 작업 단위를 형성하는 DML 문 모음으로 구성된다., DDL문(한개),DCL문(한개)
1. INSERT 예제1
: 모든 컬럼에 INSERT할 때는 컬럼명을 쓰지 않아도 되지만 테이블 CREATE시
만들어진 순서대로 입력해야한다.
INSERT INTO dept
VALUES (50,'AAA','BBB')
* NULL 값을 갖는 행 삽입시 열목록에서 열을 생략(암시적)하는 방법과
NULL 키워드를 지정(명시적)하는 방법이 있다.
2. INSERT 예제2
INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
VALUES ( 7196, 'GREEN', 'SALESMAN', 7782, SYSDATE, 2000, NULL, 10 )
3. sql 파일로 만들어서 Argument를 받으면서 INSERT 실행하기.
* test.sql 파일
ACCEPT department_id PROMPT 'Please enter the department number: '
ACCEPT department_name PROMPT 'Please enter the department name: '
ACCEPT location PROMPT 'Please enter the location: '
INSERT INTO dept ( deptno, dname, loc )
VALUES (&department_id, '&department_name', '&location' )
SQL> @test = START test
Please enter the department number: 90
Please enter the department name: PAYROLL
Please enter the location: HOUSTON
1 row created
4. 다른 테이블의 row를 Copy하기(VALUES 절을 사용하지 않는다.)
INSERT INTO managers (id, name, salary, hiredate)
SELECT empno, ename, sal, hiredate
FROM emp
WHERE job = 'MANAGER'
5. UPDATE 예제1
UPDATE emp
SET deptno = 20,
sal = 2500,
comm = null
WHERE empno = 7782
6. UPDATE 예제2 (Multiple-Column Subquery를 사용한 예)
UPDATE emp
SET (job, deptno) = (SELECT job, deptno
FROM emp
WHERE empno = 7499)
WHERE empno = 7689
7. UPDATE 예제3 (다른 테이블에 있는 데이터를 SELECT해서 UPDATE하기)
UPDATE employee
SET deptno = (SELECT deptno FROM emp WHERE empno = 7788)
WHERE job = (SELECT job FROM emp WHERE empno = 7788)
8. DELETE 예제1 (조건에 맞는 데이터 지우기)
DELETE FROM dapartment
WHERE dname = 'DEVELOPMENT'
9. DELETE 예제2 (한 테이블의 전체 데이터 삭제)
DELETE FROM department
10. DELETE 규칙
* FROM은 옵션이므로 사용하지 않아도 된다.(예: DELETE department)
* Primary Key, Forien Key 관계가 설정되어 있는 데이터를 DELETE할때는 문제가 발생 할 수 있다.
11. COMMIT
* 변경된 데이터를 Fix시킨다.
* 이전상태의 데이터는 모두 잃게 된다.
* 모든 User가 결과를 볼수 있다.
* LOCK이 풀린다.
* 모든 SavePoint들이 clear된다.
* 자동 커밋 : DDL, DCL, 정상 종료시
12. ROLLBACK
* 변경된 데이터를 undo 시킨다.
* Transaction 전단계의 데이터로 돌아간다.
* Lock이 풀린다.
* 자동 롤백 : 비정상 종료, 시스템 장애
13. SAVEPOINT 예제
* SAVEPOINT : Transaction이 일어난 곳에 Marking을 할 수가 있다.
* 오라클은 자동적으로 눈에 안보이는 savepoint를 찍어 놓는다.
SQL> UPDATE.....
SQL> SAVEPOINT update_done ;
Savepoint created.
SQL> INSERT.....
SQL> ROLLBACK TO update_done ;
Rollback complete.
14. TABLE LOCK 예제 (DBA가 임으로 TABLE에 LOCK을 걸 수가 있다.)
[10] Creating and Managing Tables (DDL)
1. Database Objects
TABLE, VIEW, SEQUENCE, INDEX, SYNONYM
2. Object Naming Rule
- 반드시 첫글자는 문자이어야 한다.
- 길이는 1 ~ 30 글자 이다.
- A-Z, a-z, 0-9, _, $, # 만을 사용할수 있다.
- 동일한 Object명이 존재해서는 안된다.(다른 user가 소유한 table과는 중복가능)
- 오라클 서버의 예약어는 사용할 수 없다.
3. CREATING TABLES( create 권한, 저장영역이 필요 )
CREATE [GLOBAL TEMPORARY] TABLE [schema.]table
( column datatype [DEFAULT expr] [,...] );
*GLOBAL TEMPORARY : 임시 table로 지정, 정의는 모든 세션에서 볼 수 있지만,
데이터는 데이터를 삽입하는 세션에서만 볼 수 있다.
* DEFAULT 값: 다른 열의 이름이나, 의사열은 잘못된 값이다.
* CREATE TABLE dept
( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13),
dymd DATE DEFAULT SYSDATE )
4. Data Dictionary
USER_ : 사용자가 소유하는 객체에 대한 정보
ALL_ : 사용자가 ACCESS 할 수 있는 모든 TABLE 에 대한 정보
DBA_ : DBA 롤을 할당 받은 사용자만 ACCESS 할 수 있다.
V$_ : 동적 성능(Performance) 뷰, DB SERVER 성능 및 잠금에 관한 정보.
* DICT 테이블 안에 모든 data dictionary 정보가 있다.
>> 유저가 소유한 개별 객체 유형을 봄.
SELECT * FROM USER_TABLES
>> 유저가 소유한 TABLE, VIEW, 동의어, 시퀀스를 봄.
SELECT DISTINCT object_type
FROM USER_OBJECTS ( USER가 ACCESS 할 수 있는 ALL_OBJECTS )
>> User가 소유한 TABLE을 설명.
SELECT * FROM USER_CATALOG
--> SELECT * FROM cat 이라고 써도 된다.
>> 모든 테이블 이름보기 SELECT * FROM TAB
>> 컬럼 정보 보기 USER_TAB_COLUMNS
5. Data Types
VARCHAR2(size) : Variable-length character data (1 ~ 4000 bytes)
CHAR(size) : Fixed-length character data (1 ~ 2000 bytes)
NUMBER(p,s) : p - precision, s - scale
DATE : B.C.4712-01-01 ~ A.D.9999.12.31
LONG : Variable-length character data (2GB)
- 한 table 당 하나 - constraints 사용 못함
- order by, group by에서 사용못함 - 서브쿼리에서 사용할 때 not copy
LONG RAW : Raw binary data of variable length up to 2 gigabytes
RAW(size) : 2000 byte
CLOB : Single-byte character data up to 4 gigabytes
BLOB : Binary data up to 4 gigabytes
BFILE : binary data stored in an external file; up to 4 gigabytes
6. Subquery를 사용해서 테이블 복사하기(새로운 table 의 datatype 정의는 필요없다)
CREATE TABLE dept30
AS
SELECT empno, ename, sal*12 ANNSAL, hiredate
FROM emp
WHERE deptno = 30
* computed field일때 반드시 컬럼 alias를 줘야 한다. (ANNSAL)
* computed 컬럼으로 만들어진 컬럼 타입 NUMBER일 때 기존의 자릿수는 사라지고
최대자리수로 잡힌다.
* column, type, data 모든것이 복사된다.(NOT NULL constraints 만 상속된다.)
* key constraint는 정의되지 않는다.
* WHERE조건을 false가 되게 만들면 데이터는 복사되지 않는다.
7. 테이블에 컬럼 추가 하기
ALTER TABLE dept30
ADD ( job VARCHAR2(9) )
-> 열을 추가할 때 모든 행은 초기에 null 값을 가진다.( not null 제약조건 X )
8. 테이블에 있는 기존 컬럼 수정하기
ALTER TABLE dept30
MODIFY ( ename VARCHAR2(15) )
* 제약사항.
- 숫자타입에서 자릿수를 늘리는건 가능하다.
- 숫자타입에서 자릿수를 줄일때는 모든 data가 null이거나 데이터가 한건도 없을 때만 가능하다.
- char <--> varchar2 로 바꿀때는 모든 data가 null이거나 데이터가 한건도 없을 때만 가능하다.
- default값도 바꿀 수 있다.(기존 default값은 유지된다.)
- column name 은 modify 가 안된다.
=> 열을 추가(ADD), 수정(MODIFY)할 수 있지만 table에서 삭제할 수 없다.
<oracle 8i 가능>
1.> ALTER TABLE table SET UNUSED (column);
OR
ALTER TABLE table SET UNUSED COLUMN column;
2.>ALTER TABLE table DROP UNUSED COLUMNS;
9. DROP TABLE
DROP TABLE dept30
10. OBJECT 이름 바꾸기
RENAME dept TO department
11. TRUNCATE TABLE
TRUNCATE TABLE department
* ROLLBACK을 절대로 할 수 없다.
* 로그없이 테이블의 전체 데이터가 삭제되고 COMMIT이 된다.
* 저장공간을 해제( DELETE 문은 해제할 수 없다.)
12. 테이블에 주석문 추가하기
COMMENT ON TABLE emp
IS 'Employee Information'
13. 컬럼에 주석문 추가하기
COMMENT ON TABLE emp COLUMN emp.ename
IS 'Employee Name'
14. 주석문 삭제하기
COMMENT ON TABLE emp IS ' '
15. 주석문을 볼수있는 data dictionary
* Column
ALL_COL_COMMENTS
USER_COL_COMMENTS
* Table
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
[11] Including Constraints
1. 오라클의 Constraint Type
NOT NULL : null value를 허용하지 않음.
UNIQUE : 유일한 값을 가지면서 null도 허용한다.
PRIMARY KEY : NOT NULL, UNIQUE, TABLE당 1개 설정, 참조 당함
FOREIGN KEY : Primary에 연결되어 있어서 Primary에 있는 값들만 갖고 있다.(PK,UK 참조)
CHECK : 정해진 값 이외의 것이 들어오면 Error
2. Constraint 규칙
- Constraint을 생성할 때 이름을 주지 않으면 오라클서버는 SYS_Cn 이라고 이름을 자동 생성한다.
- Column level 과 Table level로 Constraint를 정의한다.
- constraint를 볼 수 있는 Data Dictionary는
USER_CONSTRAINTS,USER_CONS_COLUMNS 이다.
3. Constraint 정의
* Column Level
column [CONSTRAINT constraint_name] constraint_type,
* Table Level
column,...
[CONSTRAINT constraint_name] constraint_type (column, ...),
(column, ...),
* not null은 반드시 column level로 정의를 내려야 한다.
4. NOT NULL Constraint : column level
5. UNIQUE Constraint : index 자동 생성
CREATE TABLE dept (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
CONSTRAINT dept_dname_uk UNIQUE (dname) )
6. PRIMARY KEY Constraint
CREATE TABLE dept (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
CONSTRAINT dept_dname_uk UNIQUE (dname) ,
CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno) )
7. FOREIGN KEY Constraint
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10) [CONSTRAINT epm_ename_nn] NOT NULL,
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno) )
--> dept.deptno를 emp.deptno가 Foreign key로 사용하겠다는 뜻.
* column level로 정의할 때는...(FOREIGN KEY 키워드는 생략)
deptno NUMBER(7,2) NOT NULL
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno),
* ON DELETE CASCADE
Foreign key로 연결된 parent data가 삭제될 때 child data도 모두 삭제 되게 만든다.
8. CHECK Constraint
..., deptno NUMBER(2),
CONSTRAINT emp_deptno_ck
CHECK (deptno BETWEEN 10 AND 99), ...
--> deptno의 값은 10에서 99 사이에 있는 값들만 입력이 가능하다는 뜻.
* CURRVAL, NEXTVAL, LEVEL, ROWNUM등 Pseudocolumn엔 허용되지 않는다.
* SYSDATE, UID, USER, USERENV 함수들을 호출할수 없다.
* 제약조건을 추가(ADD), 삭제(DROP)할 수 있지만 수정할 수는 없다.
* 제약조건을 설정(ENABLE) 또는 해제(DISABLE)합니다.
* MODIFT 절을 사용하여 NOT NULL 제약조건을 추가합니다.
9. ADD Constraint
*ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (column),
ALTER TABLE emp
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno)
* Constraint 수정은 할 수 없다.
* not null constraint일 경우 ADD로 하지 않고 MODIFY로 한다.
(기존 행에 null data 값이 없어야 한다.)
10. DROP Constraint
*ALTER TABLE table_name
DROP (PRIMARY KEY| UNIQUE (column) |
CONSTRAINT constraint) [CASCADE];
ALTER TABLE emp
DROP CONSTRAINT emp_mgr_fk
* Primary를 삭제할 때 Foreign Key관계(종속된 제약조건)의 Constraint까지 DROP 하고 싶으면...
ALTER TABLE dept
DROP PRIMARY KEY CASCADE
* CASCADE CONSTRAINTS 절은 DROP COLUMN 절과 함께 사용됩니다.
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;
11. DISABLE CONSTRAINT(CREATE TABLE, ALTER TABLE 문에서 사용)
: ENABLE CONSTRAINT 하기 전까지 실행을 멈춘다.
ALTER TABLE emp
DISABLE CONSTRAINT emp_empno_pk CASCADE
12. ENABLE CONSTRAINT(CREATE TABLE, ALTER TABLE 문에서 사용)
ALTER TABLE emp
ENABLE CONSTRAINT emp_empno_pk
* ENABLE 할 때는... 모든 data를 체크하기 때문에 시간이 오래 걸린다.
*
13. USER_CONSTRAINTS Data Dictionary
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMP'
--------------------------------------------------------
CONSTRAINT_NAME C SEARCH_CONDITION
------------------------- - --------------------------
SYS_C00674 C EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P
--------------------------------------------------------
14. USER_CONS_COLUMNS Data Dictionary
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMP'
--------------------------------------------------------
CONSTRAINT_NAME COLUMN_NAME
EMP_DEPTNO_FK DEPTNO
EMP_EMPNO_PK EMPNO
EMP_MGR_FK MGR
SYS_C00674 EMPNO
SYS_C00675 DEPTNO
--------------------------------------------------------
[12] Creating Views
* Oracle8부터는 View에도 Data를 갖고 있을 수 있다.
* View를 사용하는 이유?
- 데이터베이스 access를 제한한다.
- 복잡한 쿼리를 쉽게 만든다.
- 데이터의 독립을 허용하기 위해
- 동일한 데이터로부터 다양한 결과를 얻기 위해
* view를 만들 때 ORDER BY절을 사용할 수 없다.
* Simple View에는 DML 문장을 수행 할 수 있지만, 함수 또는 데이터 그룹을 포함하지 못한다.
(복합뷰<Complex VIEW> 는 함수 또는 데이터 그룹을 포함할 수 있다.)
* GROUP함수, GROUP BY절, DISTINCT keyword등을 사용한 view에는 행을 delete 할 수 없다.
* 뷰를 사용한 데이터 엑세스
- USER_VIEWS에서 뷰 정의를 검색한다.(select 문의 텍스트는 LONG 열에 저장된다.)
- 뷰의 기본 테이블에 대한 액세스 권한을 확인한다.
- 데이터를 기본 테이블에서 검색 또는 갱신한다.
1. CREATE VIEW 문장
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[ (alias[, alias]...) ]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
OR REPLACE : 만들려고 하는 view가 이미 존재할 때 recreate한다.
FORCE : base table이 존재하지 않아도 view를 강제로 만든다.
NOFORCE : base table이 반드시 존재해야만 view를 만들 수 있다.
WITH CHECK OPTION : view를 만들 때 where조건에 있는 value를
View를 통해서 DML 문장으로 수정하는 것을 막는다.
WITH READ ONLY : view를 통해서 DML문장을 수행하는 것을 막는다.
2. Creating View 예제1
CREATE VIEW empvu10
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10 ;
--> 이렇게 만들어진 view의 column이름은 empno, ename, job이 된다.
3. Creating View 예제2
CREATE VIEW salvu30
AS SELECT empno employee_number, ename name, sal salary
FROM emp
WHERE deptno = 30 ;
--> column alias를 준 employee_number, name, salary가 view의 column이름이 된다.
4. VIEW 수정하기 (OR REPLACE)
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10 ;
5. Complex VIEW
CREATE VIEW detp_sum_vu
(naem, minsal, maxsa, avgsal)
AS SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname ;
6. WITH CHECK OPTION 사용예
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM emp
WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;(deptno 가 20만 작업 가능)
( constraint name을 주지 않으면 SYS_Cn으로 만들어 진다. )
* 예를들어 다음과 같은 update문장을 수행하면 error가 난다.
UPDATE empvu20
SET deptno = 10
WHERE empno = 7788 ;
--> ORA-01402: view WITH CHECK OPTION where-clause violation
7. WITH READ ONLY 사용예
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10
WITH READ ONLY ;
* 예를들어 다음과 같은 DELETE문장을 수행하면 error가 난다.
DELETE FROM empvu10
WHERE employee_number = 7782 ;
--> ORA-01752: Cannot delete from view without exactly one key-preserved table
8. View 없애기
DROP VIEW empvu10 ;
9. 만들어진 View를 볼때
SELECT * FROM salvu30 ;
* 인라인 view : FROM 절에 사용, 객체가 아님.
10. View를 통한 DML 작업 수행에 관한 규칙
*뷰가 다음을 포함한 경우 행을 제거할 수 없슴.
- 그룹함수 - GROUP BY절 - DISTINCT 키워드 - 의사열 ROWNUM 키워드
*뷰가 다음을 포함한 경우 데이터를 수정할 수 없슴.
- 위에서 언급된 모든 조건(제거할 수 없슴)
- ROWNUM 의사 열 : 하위 질의에서 반환되는 가 행에 1에서 시작하는 순차값을 할당.
*뷰가 다음과 같은 경우 데이터를 추가할 수 없슴.
- 뷰가 위에서 언급한 포함하는 경우.(제거, 수정)
- 기본 테이블에서 뷰에 의해 선택되지 않은 열에 NOT NULL 제약 조건이 있는 경우.
11. "TOP-N" 분석 수행
SELECT [column_list], ROWNUM
FROM (SELECT [column_list] FROM table
ORDER BY TOP-N_column)
WHERE ROWNUM <= N ( where 절은 < 또는 <= )
[13] Other Database Objects (Sequence, Index, Synonym)
1. Sequence?
- unique한 숫자를 자동으로 부여한다.
- 공유 가능하다.
- primary key 성격을 갖는다.
- cache 설정을 할 수 있기 때문에 빠른 속도를 낼 수 있다.
2. CREATE SEQUENCE 문장
CREATE SEQUENCE sequence
[INCREMENT BY n] : default 1
[START WITH n] : default 1
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALEU}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE{] ; : default 20개의 cache
INCREMENT BY n : n만큼 증가 한다.
START WITH n : 처음 시작하는 숫자는 n이다.
MAXVALUE n : MAX값은 n이다.
NOMAXVALUE : MAX값은 무한대다. (10^27 OR -1) --> default
MINVALUE n : MIN값은 n이다.
NOMINVALUE : MIN값은 무한대다. (-(10^26) OR 1 ) --> default
CYCLE | NOCYCLE : MAX나 MIN값으로 갔을때 다시 처음부터 새로
시작할 건지를 설정한다. default는 NOCYCLE
CACHE n | NOCACHE : n만큼의 CACHE를 줄 것인지를 설정한다.
--> default는 cache 20으로 잡는다.
3. CREATE SEQUENCE 예제
CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 91
MAXVALUE 100
NOCACHE
NOCYCLE ;
4. USER_SEQUENCES data dictionary
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM USER_SEQUENCES ;
* last_number는 다음에 sequence 가능 한 숫자를 보여준다.
5. NEXTVAL 과 CURRVAL
NEXTVAL : Sequence가 1개 증가된다.
CURRVAL : 현재 sequence 값을 보여준다.
* 사용가능
- SELECT문장에서 SELECT list
- INSERT문장에서 SELECT list, values절 list
- UPDATE문장에서 SET절
* 사용불가
- View의 SELECT list
- SELECT문장에서 DISTINCT keyword
- SELECT문장에서 GROUP BY, HAVING, ORDER BY절
- SELECT, DELETE, UPDATE문장 안에 있는 subquery
- CREATE TABLE, ALTER TABLE문장에서 DEFAULT expression
* CACHE가 20으로 정의 했을 때...
① INSERT INTO dept
VALUES (dept_deptno.NEXTVAL, ...) --> 1
② SELECT last_number FROM user_squences --> 21
③ SELECT dept_deptno.CURRVAL FROM dual --> 1
④ SELECT dept_deptno.NEXTVAL FROM dual --> 2
6. SEQUENCE 사용예
INSERT INTO dept( deptno, dname, loc )
VALUES (dept_deptno.NEXTVAL, 'MARKETING', 'SAN DIEGO')
--> 증가된 sequence값이 deptno에 저장된다.
* 현재의 sequence값을 보려면...
SELECT dept_deptno.CURRVAL FROM dual ;
* SELECT에서 sequence값 증가시키기
SELECT dept_deptno.NEXTVAL FROM dual ;
7. SEQUENCE 수정하기
ALTER SEQUENCE dept_deptno
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE ;
* sequence에 대한 ALTER privilege를 갖고 있어야 한다.
* maxvalue가 current value보다 적을 땐 반드시 drop하고 re-create해야 한다.
* START WITH 문을 변경할 수 없습니다.( 삭제 후 다시 생성 )
8. SEQUENCE 제거하기
DROP SEQUENCE dept_deptno ;
9. INDEX ?
- Retrieve를 빨리 하기 위해서
- DISK I/O를 줄일수 있다.
- 테이블과는 독립적이다.
* PRIMARY KEY or UNIQUE constraint 생성시 오라클 서버에서 자동으로 생성되는것과, CREATE INDEX를 사용해서 만들어지는 index가 있다.
* INDEX는 많이 만든다고 좋은게 아니다.
* INDEX는 이렇때 만드는게 좋다.
- WHERE절이나 JOIN조건에 자주나오는 column
- column의 범위가 클때
- null value를 많이 갖고 있는 column
- 전체 row에서 2~4% 정도의 row를 찾을때
10. CREATE INDEX
CREATE INDEX index
ON table (column[, column]...) ;
CREATE INDEX emp_ename_idx
ON emp (ename) ;
11. USER_INDEXES and USER_IND_COLUMNS data dictionary
SELECT ic.index_name, ic.column_name, ic.column_position, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMP' ;
12. INDEX 제거하기
DROP INDEX emp_ename_idx ;
13. SYNONYM ?
user가 synonym을 사용하면 스키마를 별도로 명시할 필요가 없어진다.
14. CREATE SYNONYM
CREATE [PUBLIC] SYNONYM synonym
FOR object ;
* PUBLIC은 모든 user에게 synonym권한을 주는 것이고, DBA권한이 있는 사람만
사용이 가능하다.
* PUBLIC을 안쓰면 만든 사람만 사용한다.
CREATE SYNONYM d_sum
FOR dept_sum_vu ;
--> dept_sum_vu라는 view테이블을 d_sum으로 사용 하겠다.
15. PUBLIC SYNONYM
CREATE PUBLIC SYNONYM dept
FOR alice.dept
--> alice가 만든 dept테이블을 dept로 모든 유저에게 명시 하겠다.
16. SYNONYM 제거
DROP SYNONYM d_sum ;
* public으로 정의된 SYNONYM은 DBA만 제거가 가능하다.
[14] Controlling User Access (GRANT, REVOKE)
* 시스템 권한(System Privilege): 데이터 베이스를 액세스할 수 있다.
* 객체 권한(Object Privilege) : 데이터 베이스 객체 내용을 조작할 수 있다.
1. System Privilege? --> DBA권한
- Create new users ( CREATE USER )
- Remove users ( DROP USER )
- Remove tables ( DROP ANY TABLE )
- Backup tables ( BACKUP ANY TABLE )
2. Creating User
CREATE USER scott
IDENTIFIED BY tiger ;
3. system privilege에서의 GRANT
GRANT privilege [, privilege...]
TO user [, user...]
[WITH ADMIN OPTION] ;
* WITH ADMIN OPTION : dba가 권한을 주는 user에게도 admin 권한을 줄 수 있다.
* user system privilege(DBA 가 USER에게 할당 할 수 있는 권한)
CREATE SESSION : 테이터베이스에 connect하는 권한
CREATE TABLE : 테이블 만드는 권한
CREATE SEQUENCE : sequence 만드는 권한
CREATE VIEW : view 만드는 권한
CREATE PROCEDURE : stored prcedure, function 만드는 권한
GRANT create table, create sequence, create view
TO scott
--> scott에게 table, sequence, view만드는 권한을 준다.
4. ROLE : 사용자에게 부여할 수 있는 관련 권한을 하나로 묶어 명명한 그룹
Grant를 줄 role user를 만든후 그 role user에 grant를 주고,
role user의 권한을 각각의 user에게 넘겨준다.
① SQL> CREATE ROLE manager ;
Role created.
② SQL> GRANT create table, create view TO manager ;
Grant succeeded.
③ SQL> GRANT mananger TO brake, clock ;
Grant succeeded.
5. User Password 변경하기
ALTER USER user IDENITIFIED BY password ;
예) ALTER USER scott IDENTIFIED BY lion ;
6. Object Privileges?
* 객체마다 다르다.
* 소유자는 객체에 대한 모든 권한을 갖는다.
* 소유자는 자신의 객체에 대한 특정 권한을 부여할 수 있다.
7. Object Privilege에서의 GRANT
GRANT object_priv [(columns)| ALL]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION] ;
* sue, rich 라는 user에게 emp 테이블을 select 권한을 준다.
GRANT select
ON emp
TO sue, rich ;
* scott, manager라는 user에게 dept 테이블의 dname,loc 컬럼을 update할 수 있게 권한을 준다.
GRANT update (dname, loc)
ON dept
TO scott, manager ;
* WITH GRANT OPTION
GRANT select, insert
ON dept
TO scott
WITH GRANT OPTION ;
--> scott에게 dept 테이블의 select, insert권한을 주면서 scott가 다른 유저에게도
이 권한을 줄 수 있게 한다.
* PUBLIC : 모든 유저에게 권한을 부여한다.
GRANT select
ON alice.dept
TO PUBLIC ;
--> 모든유저에게 alice가 만든 dept 테이블의 select권한을 준다.
* 모든 유저에게 모든 object권한을 주기
GRANT ALL
ON emp
TO PUBLIC ;
8. 권한 없애기 (REVOKE)
REVOKE {privilege [, privilege...] | ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS]
REVOKE select, insert
ON dept
FROM scott ; --> scott에게서 dept테이블의 select, insert권한을 없앤다.
* CASCADE CONSTRAINTS : 이 옵션을 않쓰면 revoke할 때 forien key
관계의 table을 revoke할 수 없다.
9. Privilege Grant를 볼 수 있는 Data Dictionary
ROLE_SYS_PRIVS : System privilege 권한에 대한 정보
ROLE_TAB_PRIVS : table(object) privilege 권한에 대한 정보
USER_ROLE_PRIVS : role정보
USER_TAB_PRIVS_MADE : 내가 다른 사람에게 준 TABLE 권한에 대한 정보
USER_TAB_PRIVS_RECD : 내가 다른 사람에게 받은 TABLE 권한에 대한 정보
USER_COL_PRIVS_MADE : 내가 다른 사람에게 준 COLUMN 권한에 대한 정보
USER_COL_PRIVS_RECD : 내가 다른 사람에게 준 COLUMN 권한에 대한 정보
*****************************************************************
TABLE <--> PROCEDURE
VIEW : DML 수행
SEQUENCE : ALTER, SELECT
|
