서식스토리

동의어 설정
CREATE SYNONYM MYTEST FOR SYSTEM.TEST;

동의어 삭제
DROP SYNONYM MYTEST;

시퀀스 만들기
CREATE SEQUENCE DEPT_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 100000;

시퀀스 목록
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG
FROM USER_SEQUENCES;

시퀀스 최대값 변경
ALTER SEQUENCE DEPT_SEQ MAXVALUE 100000;

시퀀스 삭제
DROP SEQUENCE DEPT_SEQ;

시퀀스 활용
INSERT INTO DEPT01 VALUES(DEPT_SEQ.NEXTVAL,'SALES','CHICAGO');

** 오라클의 시퀀스는 MySQL의 auto_increment 속성에 해당한다.

관리자 계정으로 접속
CONN SYSTEM/비밀번호;

계정 생성
CREATE USER 사용자계정 IDENTIFIED BY 비밀번호
DEFAULT TABLESPACLE USERS //  users 그룹에 사용자 스키마를 위한 기본 테이블 스페이스를 지정
TEMPORARY TABLESPACE TEMP //  temp 에 사용자의 임시 테이블 스페이스를 지정
QUOTA 10M ON USERS;        // Quota 설정

계정권한부여
GRANT CONNECT, RESOURCE TO TEST;

계정비밀번호변경
ALTER USER 사용자계정 IDENTIFIED BY 비밀번호;

계정삭제
DROP USER 사용자계정;

뷰 목록보기
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;

JOIN으로 VIEW 생성하기 (존재하면 변경하여 생성한다)
CREATE OR REPLACE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
ORDER BY EMPNO DESC;

뷰를 생성할 권한 부여하기 (DBA)
GRANT CREATE VIEW TO SCOTT;

뷰삭제
DROP VIEW 뷰이름;

필드 크기 변경
alter table dept01 modify(dname varchar2(30));

필드 이름 변경
alter table 테이블명 rename column 필드명 to 새로운필드명;

테이블명 변경
rename dept01 to dept02;

테이블 모든 내용 제거
truncate table dept02;

권한부여 및 제거
grant create session to scott;
revoke create session from scott;

테이블 목록 ( show tables )
select * from tab;

같지 않다. ( deptno != 10 )
not deptno=10; 또는 deptno <> 10;

범위 조건
where sal between 1000 and 3000;
또는 sal >=1000 and sal <=3000;

IN 연산자
where empno in (7844,7654,7521);
where empno=7844 or empno=7654 or empno=7521;

NOT IN 연산자
where empno not in (7844,7654,7521);
where empno <> 7844 AND empno <> 7654 AND empno <> 7521;

like 에서 _ 의 사용
where ename like '_K%'; // 두번째 글짜가 K인 조건

SQL*Plus 명령어

LIST (L) 버퍼에 저장된 단 한문장의 sql 문을 나타냄.
/ SQL문을 보여주지 않고 직전 sql 실행
R SQL문을 보여주고 직전 sql 실행

SQL*Plus 파일 명령어

EDIT [파일명] :
SAVE 파일명 : ( 파일명.sql 로 저장 된다. )
SAVE 파일명 APPEND : 파일에 추가
SAVE 파일명 REPLACE : 파일에 덮어 쓰기
GET 파일명 : 파일의 내용을 SQL 버퍼로 읽어 들인다. ( / 를 누르면 실행 )
@ 파일명 : 파일에 저장된 내용을 실행시킨다.
SPOOL : 화면 내용 갈무리
   SPOOL 파일명
   SPOOL OFF ( 파일명.lst 로 저장 된다. )

환경 변수 조작
set heading off/on
set linesize 80   (가로문자수)
set pagesize 14   (세로출력수)

계산기처럼 사용하기
select 24*60*60 from dual;

대/소문자변환 함수 LOWER(), UPPER()
첫글자만 대문자,그리고 나머진 소문자 INITCAP()
문자열 연결함수 CONCAT(필드또는 문자열, 필드또는 문자열)
문자 길이를 구하는 LENGTH()
문자 바이트수를 구하는 LENGTHB()
문자열 일부만 구하는 SUBSTR(필드명,위치,갯수)
문자열 일부만 구하는 SUBSTRB - BYTE단위
특중 문자의 위치를 구하는 INSTR(필드명,문자), INSERTB()
INSTR(필드명,문자,검색위치,몇번째인지)
특정 기호로 채우는 LPAD/RPAD 함수
LPAD(필드명,자릿수,'채울문자')
특정 문자를 잘라내는 TRIM 함수
SELECT TRIM('a' FROM 'aaaaDataBase Programmingaaaa') FROM dual;

절대값구하는 ABS(-15)
소수점 아래를 버리는 FLOOR(34.11823)
반올림함수 ROUND(컬럼, -3) : 1653 --> 2000
특정 자릿수에서 잘라내는 TRUNC함수 TRUNC(12.345,2) --> 12.34
나머지 값을 반환하는 MOD 함수 MOD(34,5) --> 4

현재 날짜를 반환하는 SYSDATE : 괄호가 없이 사용됨.
두 날짜 사이 간격을 개월수를 계산하는 MONTHS_BETWEEN 함수
개월 수를 더하는 ADD_MONTHS(HIREDATE,3)
지정된 날짜에서 가장 가까운 특정요일 찾는 함수 NEXT_DAY(date,'수요일');
환경설정에 저장된 언어를 변경하려면 ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
해당 달의 마지막 날짜를 반환 LAST_DAY(날짜)
ROUND 함수는 날짜에도 적용시킬 수 있다.
TRUNC(DATE,'MONTH') 그 날짜가 속한 달의 1일을 구한다.
TO_CHAR(SYSDATE,'YYYY-MM-DD')
TO_CHAR(SAL,'999,999') : 3자리에 콤마찍기
TO_DATE('2008/01/01') : 숫자형이나 문자형을 날짜형으로 변환
TO_NUMBER('10,000','99,999') : 문자형을 숫자형으로 변환
NVL(COMM,0) : NULL을 다른 값으로 변환하는 함수, +/- 등의 연산을 하려면 반드시 변환필요!!
DECODE(표현식,조건1,결과1,조건2,결과2,DEFAULT) : SWITCH 문에 해당 (조건 == 일치시 사용)
CASE함수도 DECODE와 비슷하나 IF문과 같은 형태로 사용할 수 있다.
CASE WHEN 조건1 THEN 결과1
     WHEN 조건2 THEN 결과2
     WHEN 조건3 THEN 결과3
     ELSE 결과n
END AS 별칭

테이블 복사 생성 CREATE TABLE EMP02 AS SELECT * FROM EMP;
필드 추가 ALTER TABLE EMP01 ADD(JOB VARCHAR2(9));
필드 크기 변경 ALTER TABLE EMP02 MODIFY(JOB VARCHAR2(20));
필드 삭제 ALTER TABLE 테이블 DROP COLUMN 필드;
테이블 삭제 DROP TABLE 테이블;
테이블명 변경 RENAME 테이블명 TO 새테이블명;
테이블 모든 데이터 삭제 TRUNCATE TABLE 테이블;
자동 COMMIT : CREATE, ALTER, DROP, RENAME, TRUNCATE
ROLLBACK
COMMIT
SAVEPOINT C1
ROLLBACK TO C1

제약조건 조회 -- P primary key, R foreign key, U unique, C check, not null
SELECT A.CONSTRAINT_TYPE,B.COLUMN_NAME
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.TABLE_NAME='EMP06';