■ 주석
1줄 주석 : -- (블록 잡고 ctrl + /) --○ 새로운 개념
여러줄 주석 : /* ~ */ --==>> 결과값
-->> 설명
--○ 현재 접속한 자신의 계정 확인
SELECT USER
FROM DUAL;
--==>> HR
--※ sys로 접속한 상태에서! 시작
--○ 사용자 계정 생성 및 패스워드 설정
create user scott
identified by tiger;
--==>>User SCOTT이(가) 생성되었습니다.
--○ 권한 부여
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT;
--==>>Grant을(를) 성공했습니다.
--테이블스페이스는 테이블들이 논리적으로 저장되는 공간
--○ 구조적인 변경
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
--==>>User SCOTT이(가) 변경되었습니다.
--○ 구조적인 변경
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
--==>>User SCOTT이(가) 변경되었습니다.
--지금부터 SCOTT으로 접속~~!
--CONNECT SCOTT/TIGER
-- 사용자 확인
SELECT USER
FROM DUAL;
--==>> SCOTT
--○ 테이블 생성(DEPT)
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY
, DNAME VARCHAR2(14)
, LOC VARCHAR2(13)
);
--하나의 항목속성을 컬럼이라 부름
--==>>Table DEPT이(가) 생성되었습니다.
--○ 확인
SELECT *
FROM DEPT;
--○ 테이블 생성(EMP)
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY
, ENAME VARCHAR2(10)
, JOB VARCHAR2(9)
, MGR NUMBER(4)
, HIREDATE DATE
, SAL NUMBER(7,2)
, COMM NUMBER(7,2)
, DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
--==>>Table EMP이(가) 생성되었습니다.
--○ 확인
SELECT *
FROM EMP;
--○ 데이터 입력(DEPT TABLE)
--약식 구문으로 입력하고 있으나 권장하지는 않음
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
--==>> 4행이 삽입되었습니다.
--○ 데이터 입력(EMP TABLE)
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
--==>> 14행이 삽입되었습니다.
--○ 테이블 생성(BONUS)
CREATE TABLE BONUS
( ENAME VARCHAR2(10)
, JOB VARCHAR2(9)
, SAL NUMBER
, COMM NUMBER
) ;
--==>> Table BONUS이(가) 생성되었습니다.
--○ 테이블 생성(SALGRADE)
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
--==>> Table SALGRADE이(가) 생성되었습니다.
--○ 데이터 입력(SALGRADE TABLE)
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
--==>>5행이 삽입되었습니다.
COMMIT;
--COMMIT명령어 = 메모리상에 올라가진 내용을 HDD에 기록하라!
--==>> 커밋 완료
--ROLLBACK;
--ROLLBACK명령어 = COMMIT과 반대
/*
DB운영에서 가장 중요한 것은 메모리이다
메모리를 얼만큼 아끼는 지가 시간과 비용을 얼만큼 아끼는가 이기 때문이다.
*/
--○ 현재 SCOTT 계정이 소유하고 있는 테이블 조회
SELECT *
FROM TAB;
--==>> TAB는 TABLE의 약자
/*
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SCOTT이 총 4개의 테이블을 가지고 있다!
*/
중요!!!
--------------------------------------------------
--■■■ SELECT 문의 처리(PARSING) 순서 ■■■--
--------------------------------------------------
--필수 암기--
/*
SELECT 컬럼명 -- ⑤ 세로방향으로 추려냄
FROM 테이블명 -- ①
WHERE 조건절 -- ② 가로방향으로 추려냄
GROUP BY 절 -- ③ 그룹화
HAVING 절 -- ④ 그룹의 조건
ORDER BY 절 -- ⑥ 오름차순 / 내림차순
※ 쿼리문 구성할 때 ORDER BY를 사용하지 않는 경우가 있다.
ORDER BY정렬은 리소스 소모가 굉장히 심해서
시스템 부하가 많이 발생하기 때문!
WHERE 조건절에 만족하는 데이터들만 메모리 상에 올라간다.
그러므로 WHERE 조건절이 없으면 모든 데이터를 다 스캔하기 때문에 메모리 사용량이 늘어난다.
SELECT ENAME
FROM EMP;
조건이 하나도 없으므로 TABLE 전체를 스캔하여 메모리 상에 올라간다.
SELECT ENAME, JOB, EMPNO
FROM EMP
WHERE ENAME = 'JSW';
WHERE 조건에 맞는 데이터만 메모리 상에 올라감. 메모리 절약
*/
--○ BONUS 테이블 조회
SELECT *
FROM BONUS;
--○ 부서정보(DEPT) 테이블 조회
SELECT *
FROM DEPT;
--○ 사원정보(EMP) 테이블 조회
SELECT *
FROM EMP;
--○ 급여 등급(SALGRADE) 테이블 조회
SELECT *
FROM SALGRADE;
--○ 테이블의 구조 확인
DESCRIBE EMP;
/* 실행 결과
이름 널 유형
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
//NOT NULL을 필수 입력사항정도로 이해하자!(비어있으면 안되기 때문!)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
*/
/*
---------------------------------
--■■■ 주요 자료형 ■■■--
---------------------------------
1. 숫자형 NUMBER
NUMBER(3) = -999~999까지 입력 가능
NUMBER(4,1) = -999.X ~ 999.X 소숫점 첫번째까지 입력가능
전체가 4자리이고 소숫점이 1자리
2. 문자형 고정형 크기
CHAR(10) = 10바이트 크기인 문자
5바이트 만큼의 데이터를 입력해도 10바이트를 차지한다.
가변형 크기
VARCHAR2(10) = 가변형이라고 해서 명시되어 있는 바이트를 넘어갈 수 없다.
오버플로우로 짤리는 것이아니라 처음부터 에러가 난다.
헤더에 관련 자료의 크기나 정보의 크기가 더 크기때문에 참조할 때 리소스가 더 많이 소모된다.
따라서 고정된 길이의 데이터는 CHAR형을 사용한다.
숫자로 구성된 데이터지만 숫자로 처리하면 안되는 것
1. 전화번호
2. 학번
3. 주민번호
첫시작이 0으로 시작하게 된다면 숫자형이면 앞에 0이 삭제되기 때문!!
NCHAR(N) - 유니코드기반 글자수 (N글자 입력가능)
NVARCHAR2(N) - 유니코드기반 글자수 (N글자 입력가능)
3. 날짜형
DATE -
*/
SELECT SYSDATE
FROM DUAL;
--==>>18/01/05
--SESSION - 연결과 관련되어 있는 정보를 담아두는 공간
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT SYSDATE
FROM DUAL;
--==>>2018-01-05 14:49:06
SELECT 0123
FROM DUAL;
--==>> 123
SELECT '0123'
FROM DUAL;
--==>>0123
--------------------------------------------------------------------------------
--○ EMP 테이블에서 부서번호가 20번인 사원들의 정보 중
-- 사원번호, 사원명, 급여, 직종명, 부서번호 조회
SELECT EMPNO
,ENAME
, SAL
, JOB
, DEPTNO
FROM EMP
WHERE DEPTNO=20;
-- ※ 각 컬럼에 별칭을 부여할 수 있다.
SELECT EMPNO AS "사원번호", ENAME "사원명", SAL 급여, JOB "직종 명", DEPTNO
FROM EMP
WHERE DEPTNO=20;
--○ EMP 테이블에서 부서번호가 20번과 30번 직원들의 정보 중
-- 사원번호, 사원명, 직종명, 급여, 부서번호 항목을 조회한다.
SELECT EMPNO 사원번호, ENAME 사원이름, JOB 직종명, SAL 급여, DEPTNO 부서번호
FROM EMP
WHERE DEPTNO=20 OR DEPTNO=30
ORDER BY SAL ASC;
--위와 아래는 같은 구문
SELECT EMPNO 사원번호, ENAME 사원이름, JOB 직종명, SAL 급여, DEPTNO 부서번호
FROM EMP
WHERE DEPTNO IN(20,30);
--○ || 문자열 결합 연산자
SELECT 'A'||'B'
FROM DUAL;
--○ EMP 테이블에서 직종이 CLERK인 사원들의 정보를 모두 조회한다
SELECT *
FROM EMP
WHERE JOB='CLERK';
-- 오라클에서 입력된 데이터(값) 만큼은 반드시 대소문자 구분을 해야한다.
--○ 테이블 복사
--> 내부적으로 대상 테이블 안에 들어있는 데이터 내용만 복사하는 과정
CREATE TABLE TBL_EMP
AS -- 저거저거~~ 저거로 만들어달라
SELECT *
FROM EMP;
SELECT *
FROM TBL_EMP;
--○ 이미 만들어진 테이블에 컬럼 추가
-- TBL_EMP 테이블에 주민번호 정보를 담을 수 있는 컬럼(SSN) 추가
ALTER TABLE TBL_EMP
ADD SSN CHAR(13);
--==>> Table TBL_EMP이(가) 변경되었습니다.
/*
INSERT / UPDATE / DELETE 와 같은 DML구문을 작성한 이후에는
선택적으로 COMMIT이나 ROLLBACK을 해야한다.
다른 구문들은 자동적으로 COMMIT을 실행해준다!
한쪽은 오토 COMMIT을 해주는 사이에 잘못 적힌 데이터를 수정하지 않았을 경우
잘못된 데이터가 메모리에 올라 갈 수 있다 주의 주의
*/
-- 컬럼이 추가된 테이블 구조 확인
DESC TBL_EMP;
/*
이름 널 유형
-------- - ------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SSN CHAR(13)
*/
SELECT *
FROM TBL_EMP;
/*
SSN이 NULL로 되어있음
1. 컬럼의 구성되어 있는 순서는 중요하지 않다 아니 의미가 없다.
2. 데이터를 채우려면 UPDATE해야함
레코드 한개가 하나의 데이터인데 SSN에 데이터를 삽입하려면
UPDATE를 사용해야함
*/
ALTER TABLE TBL_EMP DROP COLUMN SSN;
SELECT *
FROM TBL_EMP;
SELECT *
FROM EMP;
ALTER TABLE TBL_EMP
ADD SSN CHAR(13);
/*
DELETE 실수를 하지 않는 방법
SELECT *
FROM TBL_EMP
WHERE EMPNO=7369;
처음에 삭제해야 하는 것을 확인!!
DELETE
FROM TBL_EMP
WHERE EMPNO=7369;
COMMIT;
SELECT *을 DELETE로 바꾼후 실행!
*/
-- TBL_EMP 테이블 삭제
DROP TABLE TBL_EMP;
--오라클 휴지통에 들어가 있음
SELECT *
FROM TAB;
--이상한 글자가 있음 DROP 한 것
-- BIN$Aa411Q6JRXexdxVM7/zvrw==$0
------------------------------------------------------------------------------
--○ NULL의 처리
SELECT 2, 1+2, 3-1
FROM DUAL;
--==>>2 3 2
SELECT 2+NULL, 2-NULL, 2*NULL, 2/NULL
FROM DUAL;
--==>>NULL NULL NULL NULL
--※ NULL은 현실에 존재하는 특정한 값을 의미하는 것이 아니라
-- 아무것도 들어있지 않은 상태를 의미하는 값이므로
-- NULL을 대상으로 연산을 수행할 경우 또는 특정 연산 과정에 NULL이 포함될 경우
-- 결과는 무조건 NULL이다.
--○ 다시 EMP 테이블을 복사하여 TBL_EMP테이블을 생성
CREATE TABLE TBL_EMP
AS
SELECT *
FROM EMP;
--==>>Table TBL_EMP이(가) 생성되었습니다.
--○ TBL_EMP 테이블에서 커미션(COMM) → 수당이 NULL인 사원들의
-- 사원명, 직종명, 급여, 커미션을 조회한다.
SELECT ENAME 사원명, JOB 직종명, SAL 급여, COMM 커미션
FROM TBL_EMP
WHERE COMM IS NULL;
--※ NULL은 값으로 존재하는 것이 아니기 때문에 값으로 비교할 수 없다.
-- =, >=, <=, !=, <>, >, < 와 같은 연산자를 사용할 수 없다. !=와 <> ^= 모두 같지 않다는 의미
--○ TBL_EMP 테이블에서 20번 부서에 근무하지 않는 사원들의 정보를
-- 사원번호, 사원명, 부서번호로 조회한다.
SELECT EMPNO 사원번호, ENAME 사원명, DEPTNO 부서번호
FROM TBL_EMP
WHERE DEPTNO!=20;
SELECT EMPNO 사원번호, ENAME 사원명, DEPTNO 부서번호
FROM TBL_EMP
WHERE NOT DEPTNO=20;
--TBL_EMP 테이블에서 COMM(커미션)이 NULL이 아닌 것을 조회
SELECT *
FROM TBL_EMP
WHERE COMM IS NOT NULL;
SELECT *
FROM TBL_EMP
WHERE NOT COMM IS NULL;
--○ TBL_EMP 테이블에서 모든 사원들의
-- 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다.
-- 단, 급여(SAL)는 매월 지급한다.
-- 수당(COMM)은 1회/년 지급한다.
ALTER TABLE TBL_EMP
ADD MONEY NUMBER(10);
ALTER TABLE TBL_EMP DROP COLUMN MONEY;
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, COMM 커미션, (SAL*12)+(NVL(COMM,0)) 연봉
FROM TBL_EMP;
'Database' 카테고리의 다른 글
| 오라클 다중 WHERE조건 (0) | 2018.06.25 |
|---|---|
| 무결성 (Integrity) (0) | 2018.01.12 |
| JOIN (조인) (0) | 2018.01.12 |
| 오라클 SQL Developer 2018.01.09 (0) | 2018.01.09 |
| 데이터베이스와 DBMS 개요 (0) | 2018.01.09 |