0613 수업 내용

1) SQL CMD 

 

(DB튜닝 (물리적인튜닝 / SQL튜닝) / SQL 튜닝 공부하기! (개발자에게 좋음) )

(프로젝트 - 시간이 지날수록 데이터 늘어남 - 데이터 SQL 최적화하기 - 조회/수정 등 좋기때문에 공부하기!)

 

 

2) 스키마 

employee - 핵심테이블 (관계선을 가장 많이 가지고 있기 때문에 알 수 있음) 

 

employee - 컬럼보기 

사번 

성

이름

이메일

핸드폰 번호

입사일

직무

급여 

수당 

매니저(관리자) 

부서 

 

NUMBER(6,0) = NUMBER (6) 

NUMBER(6,0) NOT NULL (DB에서 NULL:  아무값도 아니다, 값이 아니다 ) (자바에서 NULL = 참조하지 않는다) 

     (NOT NULL = 값이 아니면 안된다 = 값이 반드시 있어야 한다) 

      (LASTNAME/ EMAIL/HIREDATE/JOBID - 는 '반드시 값이 있어야 한다'는 것을 알 수 있다) 

VARCHAR2 가변길이 문자형 

PHONE NUMBER 가 문자형인 이유는? 앞에 0으로 시작하기 때문에 (숫자형은 010 을 10로 인식하기 때문에)  

- 문자형의 2가지형태

     고정길이 갖는CHAR(3)  (A하나 입력시에도 자리3자리 무조건차지(2자리 비어두고 마지막자리에 A입력) ) 

           = 무조건 고정길이를 가지고 있다. (메모리 효율성은 낮지만, 처리속도가 더 빠르다)

           // 항상 고정길이를 가지고 있는 경우 사용 (예시) 우편번호, 주민등록번호 

     가변길이를 갖는 VARCHAR2 (3) (최대 3, A하나 입력시 한자리 차지 ) (메모리의 효율성이 좋다) 

DATE NOT NULL (날짜자료형) 

NUMBER(8,2) 최대전체자리 8자리의 소수점2자리까지 = 999999.99

NUMBER(2,2) 최대자리수2자리에 해당하는 소수점2자리 = .99  

 

\

employees테이블과  jobs 테이블  

(fk) EMPLOYEES 가 JOBS 테이블을 참조한다 

부모엔터티: 먼저 자료가 저장되어 있어야 할 곳으로 즉, 참조 될 곳 ( JOBS 테이블 )

자식엔터티: 참조하는 쪽 (EMPLOYEES 테이블)

 

점선 관계선(비식별자 관계) : 부모엔터티의 PK를 자식엔터티의 일반컬럼으로 참조 

                                                                     (PK? 엔터티를 구분하는 주식별자) (아래그림 참고) 

3) 테이블관계, 참여자수, 필수선택참여여부 

부모엔터티 

: 미리 자료가 저장되는 곳 

자식엔터티 

: 부모엔터티를 참조하는 곳 

 

PRIMARY KEY 컬럼

: 엔터티(행)을 구분하는 주식별자 

FOREIGN KEY 컬럼 

: 자식엔터티에서 부모엔터티PK를 참조하는 컬럼 

 

식별자 employee_id / ssn 

주식별자  employee_id

보조식별자  ssn

 

 

X(오라클) 이 참조되지 않도록 PK 설정한다. 

 

테이블 관계 종류

- 비식별자관계

: 부모엔터티PK를 자식엔터티의 일반컬럼으로 참조하는 관계 - 점선표기

- 주식별자관계 

: 부모엔터티PK를 자식엔터티의 PK컬럼으로 참조하는 관계 - 실선표기 

 

비식별자관계

주식별자관계

참여자수(관계차수, Cardinality )

1:1관계

1:M관계

M:N관계 

 

1:1관계

1:M관계

M:N관계 

(논리적으로만 표기가능, 실제 테이블설계 불가)

 

필수선택참여여부 (필수참여/ 선택참여)

NULL 

선택참여 

: 한 고객은 하나의 포인트를 가질수도 있고, 갖지 않을 수도 있다. 

한 직무 담당사원은 O이거나MANY명이다. (선택참여) 

 

 

 

필수참여 (동그라미 표시 X)  |

: 하나의 포인트는 고객 한 명이 가지고 있어야 한다. 

 

한 사원은 반드시 하나의 직무담당를 담당한다.  

 

4) SQL 

(SQL: Structured query language) 구조화된 질의어 

 

SQL 종류 

- SELECT 

 : 조회 

- DML

 : (데이터조작어), 추가(INSERT), 수정(UPDATE), 삭제(DELETE) 

- DDL

 : (데이터정의어), 객체생성(CREATE), 객체구조변경(ALTER), 객제체거(DROP) 

- DTL

 : (데이터트랜잭션), 트랜잭션완료(COMMIT) /복구(ROLLBACK)  

- DCL 

 : (데이터제어), 계정생성(CREATE USER), 계정변경(ALTER USER), 계정제거(DROP USER),계정권한분여(GRANT), 권한부여취소(REVOKE)  

 

SELECT

     SELECT 컬럼명1, 컬럼명2... (또는) 

     FROM 테이블

     (아래는 생략가능) 

      [WHERE

      GROUP NY

      HAVING

     ORDER BY]

 

     ex) SELECT * FROM employees; 

            SELECT employee_id, first_name FROM employees; 

 

SELECT 구문의 처리순서 

<-------SELECTION:조건에 맞는 행검색---------><--PROJECTION:컬럼선택출력-->

FROM -> WHERE -> GROUP BY -> HAVING ->                          SELECT           ->                 ORDER BY 

 

자료형: 숫자NUMBER, 문자CHAR/VARCHAR2, 날짜DATE 

문자형리터럴은 ' ' 표기한다. ex) 'HELLO'

 

SELECT절에 사용가능한 연산자 

산술연산자  +   -   *   / 

결합연산자 || (문자열을 이어주는 연산자) 

SELECT employee_id,

               salary , salary+100, salary+salary*commission_pct,

               first_name, last_name, first_name||last_name  

FROM employees; 

 

헤딩 

1. 대문자로 보임 

2. 헤딩변경가능 

 

HEADING 변경 

: 별칭주기 

SELECT employee_id 사번,     (연산식 한칸 띄우고 별칭주기(예:사번, 실급여, 성명)) 

               salary , salary+100, salary+salary*commission_pct 실급여,

               first_name, last_name, first_name||last_name 성명  

FROM employees; 

 

 

SELECT employee_id 사번,   

               salary "Sal" , --""사용하여 Heading 대소문자구분, 공백허용가능 

                 salary+100 "salary더하기100", salary+salary*commission_pct 실급여,

               first_name, last_name, first_name||last_name 성명  

FROM employees; 

 

SELECT employee_id 사번, 

               salary "Sal" ,

                 salary+100 "salary더하기100", salary+salary*commission_pct 실급여,

               first_name, last_name,

               first_name||'~'||last_name  AS 성명  

FROM employees; 

 

WHERE절: 조건식

ex) SELECT * FROM employees WHERE salary >=15000; 

WHERE절에 사용가능한 연산자

산술연산자 ex)  WHERE salary+salary*commission_pct >=15000;

비교연산자 >      >=      <      <=     =      <>   

                      주의점: NULL값 비교불가 

논리연산자 AND      OR     NOT     ex) WHERE salary>=15000 AND hire_date< '20/01/01' 

NULL연산자 IS NULL, IS NOT NULL 

BETWEEN연산자 : 같은컬럼명 >=AND<=같은컬럼명 대신

                                ex) WHERE salary >= 10000 AND salary <= 20000; 

                                      WHERE salary BETWEEN 10000 AND 20000;

IN연산자 : 같은컬렴명 = OR 같은컬럼명 = 대신 

                  ex) WHERE department_id=50 OR department_id=90; 

                        WHERE department_id IN (50, 90); 

                        

                         WHERE NOT(department_id=50 OR department_id=90);

                         WHERE department NOT IN (50, 90);

 

 

LIKE 연산자 : % 패턴 - 0개이상 

                    :  _ 패턴 - 1개

 

--이름에 E또는  를 포함한 사원의 사번, 이름을 출력하시오 

SELECT employee_id, first_name 

FROM employees 

WHERE LOWER(first_name) LIKE '%e%';  //first_name값이 ABC > FALSE  

                                                                                               //ABe  > TRUE

                                                                                               //e       > TRUE

                                                                                              //eAB   > TRUE 

 

--직무명이 SALES로 시작하는 모든 직무번호와 직무명을 출력하시오 

SELECT job_id, job_title 

FROM jobs 

WHERE job_title LIKE 'SALES%'; 

 

--직무명이 Manager로 끝나는 모든 직무번호와 직무명을 출력하시오. 

SELECT job_id, job_title

FROM jobs 

WHERE job_title LIKE '%Manager';  

 

-- 사원이름에 an을 포함한 사원의 사번과 이름을 출력하시오 
SELECT employee_id, first_name
FROM employees
WHERE first_name LIKE '%an%';

 

--이름이 'J'로 시작하고 n문자를 포함한 사원의 사번과 이름을 출력하시오 

SELECT employee_id, first_name
FROM employees

WHERE first_name LIKE 'J%' AND  first_name LIKE '%n%';

 

--입사월이 11월인 사원 사번, 입사일자를 출력하시오. 
SELECT employee_id 사번 , hire_date 입사일 
FROM employees
WHERE hire_date LIKE '%/11/%';
--WHERE hire_date LIKE '__/11/__'; 

 

사원의 사번, 급여를 출력하시오 

 

SELECT employee_id 사번, salary 급여 
FROM employees;

 

부서번호가 90번인 부서의 사원들 사번, 급여를 출력하시오 (부서번호: department_id) 

 

SELECT employee_id, salary 
FROM employees 
WHERE department_id = '90';

 

90번 부서를 제외한 사원들 사번, 급여, 부서번호를 출력하시오
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE department_id <> '90';

 

부서배치받은 사원들의 사번, 급여, 부서번호를 출력하시오 
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE department_id IS NOT NULL;

 

부서배치를 받지 못한 사원의 사번, 급여, 부서번호를 출력하시오 
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE department_id IS NULL;

 

90번 부서를 제외한 사원들 사번, 급여, 부서번호를 출력하시오. 단, 부서배치 못한 사원도 출력하시오. 
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE department_id <> '90' OR department_id IS NULL;

 

90번 부서이고 급여가 20000이상인 사원들의 사번, 급여, 부서번호를 출력하시오.
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE department_id = '90' AND salary >=20000;

 

급여가 10000이상 20000이하인 사원들의 사번, 급여, 부서번호를 출력하시오.
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE salary >=10000 AND salary <=20000;

 

부서번호가 10, 50, 90인 사원들의 사번, 급여, 부서번호를 출력하시오.
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE department_id = '10' or department_id = '50' or department_id = '90';

 

부서번호가 10, 50, 90아닌 사원들의 사번, 급여, 부서번호를 출력하시오
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees
WHERE department_id <> '10' AND department_id <> '50' AND department_id <> '90';

 

이것도 가능

WHERE NOT (department_id = '10' or department_id = '50' or department_id = '90');

 

급여가 10000이상 20000이하인 사원들을 대상으로 부서번호가 10, 50, 90인 사원들의 사번, 급여, 부서번호를 출력하시오.
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE (salary>=10000 AND salary <=20000) AND (department_id = '10' or department_id = '50' or department_id = '90');

 

BETWEEN IN 사용 

--급여가 10000이상 20000이하인 사원들의 사번, 급여, 부서번호를 출력하시오.
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE salary BETWEEN 10000 AND 20000; 

--부서번호가 10, 50, 90인 사원들의 사번, 급여, 부서번호를 출력하시오.
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE department_id IN(10, 50, 90); 

 

--급여가 10000이상 20000이하인 사원들을 대상으로 부서번호가 10, 50, 90인 사원들의 사번, 급여, 부서번호를 출력하시오.
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
WHERE salary BETWEEN 10000 AND 20000 AND department_id IN(10, 50, 90); 

 

- WHERE BY 절: 정렬 , 컬럼/별칭/인덱스/ SELECT안하는 컬럼 사용 가능 

--모든사원을 급여가 적은 순으로 사번, 급여, 부서번호를 출력한다. 

SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 

ORDER BY salary;  

 

ORDER BY salary ASC;  

이거랑 같음(오름차순, 생략가능) 

 

모든사원의 사번, 급여, 부서번호를 많은 급여를 받는 사원부터 출력한다. 
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
ORDER BY salary DESC;

(내림차순) 

 

모든사원의 사번, 급여, 부서번호를 많은 급여를 받는 사원부터 출력한다. 
단, 급여가 같은경우 작은사번부터 출력하시오. 
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees 
ORDER BY salary DESC, employee_id;

 

 

SELECT employee_id, salary 급여

FROM employees

WHERE 급여>=15000; (X)

//처리가 안되는 이유: FROM > WHERE > SELECT 순서로 처리가 되기 때문에. 

 

SELECT employee_id, salary 급여

FROM employees

ORDER BY 급여; (O) 

//처리가 되는 이유:SELECT 절 이후 ORDER BY 사용가능 

 

SELECT employee_id, salary 급여

FROM employees

ORDER BY 2; (O) //인덱스 2번 (급여정렬됨) 

 

SELECT employee_id 사번, salary 급여

FROM employees 

ORDER BY hire_date; (O)  //출력하지 않은 대상도 정렬 가능, 일사일자순으로 정렬해서 사번과 급여를 출력한다.