2020. 10. 23. 10:23ㆍ교육과정/KOSMO
키워드 : SQL 복습 / oracle 계정 생성 / 무결성 제약 조건 /
****
1. [복습] SQL
(1) DDL (구조적인 면에서 작업이 필요할 때)
- CREATE
- DROP
- ALTER
(2) DML (레코드 수정이 필요할 때) <일반적인 이론에서는 CRUD처리라고 한다>
- INSERT (c. create)
- DELETE (d. delete)
- UPDATE (u.update)
- SELECT (r. read)
(3) DCL (권한에 관한 작업)
- GRANT (권한 부여)
- REVOKE (권한 회수)
2. Oracle 계정 생성하여 SQL Developer 접속하기
3. EMP 테이블에 있는 정보 확인하기
※ SELECT * FROM 테이블명 ;
: 테이블에 들어있는 데이터들을 보여줌
※ DESC 테이블명 ;
: 테이블의 자료형을 포함한 구조를 보여줌
※ COMMIT ;
: 작업한 내용을 반영
4. 무결성 제약 조건
※ 무결성 제약 조건의 종류
| NOT NULL | null 값을 허용하지 않음 (컬럼 레벨 방식으로만 적용) |
| UNIQUE | 유일하게 식별하는 값만 허용 → 중복값 허용 안 함 (PRIMARY KEY와 유사하나 null값 허용됨) |
| PRIMARY KEY | 유일하게 식별하는 값만 허용하며 null값도 허용 안 함 (UNIQUE + NOT NULL) UNIQUE INDEX 자동생성 |
| FOREIGN KEY | 다른 테이블의 PK를 참조 |
| CHECK | 제한적인 입력 처리 |
| DEFAULT | 제약 조건은 아니지만, 입력값이 없을 대 디폴트 설정값으로 자동 입력됨 |
(+) 모든 제약 조건은 DATA DICTIONARY에 저장되며, 제약 조건 이름을 의미있게 부여하면
참조하기 쉽기 때문에 규칙적인 제약 조건 이름이 권장된다.
(1) NOT NULL - null값 입력 배제
| CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 NOT NULL ) ; |
| ALTER TABLE 테이블명 MODIFY (컬럼이름 데이터타입 NOT NULL) ; |
(2) UNIQUE - 중복값 배제, 단 null값은 입력 가능
| CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 CONSTRAINT 제약조건명 UNIQUE (컬럼이름) ) ; |
| ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 UNIQUE (컬럼이름) ; |
(3) PRIMARY KEY - 필수입력사항을 만들고자 할 때, 중복값과 null값 모두 배제함
- 컬럼 레벨 제약 조건 방식
|
CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 CONSTRAINT 제약조건명 PRIMARY KEY ) ; |
- 테이블 레벨 제약 조건 방식
|
CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 CONSTRAINT 제약조건명 PRIMARY KEY (컬럼이름) ) ; |
(4) FOREIGN
|
CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 CONSTRAINT 제약조건명 FOREIGN KEY ) ; |
|
CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 ) ; |
(5) CHECK
|
CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 CONSTRAINT 제약조건명 CHECK (컬럼이름 IN ('조건1', '조건2')) ) ; |
|
ALTER TABLE 테이블명 |
(6) DEFAULT
|
CREATE TABLE 테이블명 ( 컬럼이름 데이터타입 DEFAULT '기본값' ) ; |
| default는 NOT NULL과 함께 기술시 앞쪽에 작성한다. gender varchar2(10) DEFAULT '남자' NOT NULL |
※ 제약조건 추가 : ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건종류 (컬럼이름) ;
※ 제약조건 삭제 : ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명 ;
※ 제약조건 (비)활성화 : ALTER TABLE 테이블명 DISABLE/ENABLE CONSTRAINT 제약조건명 ;
※ 제약조건 확인 : SELECCT * FROM USER_CONSTRAINT WHERE table_name='대문자로 테이블명' ;
* CONSTRAINT_TYPE은 P, R, U, C 4가지 값 중 하나를 갖는다.
P : PRIMARY KEY
R : FOREIGN KEY
U : UNIQUE
C : CHECK, NOT NULL
5. 연습문제
6. 테이블 생성시 제약조건을 적절히 추가했을 경우
(+)
7. SQL 기본문법 연습문제 (제약조건)
8. 오라클에서 함수 활용하기
-- 함수 활용
SELECT ename, sal, sal+comm FROM emp;
-- NULL값을 0으로 치환한 연산결과 출력하기
-- (단, 컬럼명이 복잡해져서 JAVA에서 인지하지 못할 수 있다.)
SELECT ename, sal, sal+NVL(comm,0) FROM emp;
-- AS를 사용하여 컬럼명에 별칭 붙이기
-- 별칭을 나타내는 AS는 생략 가능하다.
SELECT ename, sal, sal+NVL(comm,0) AS "총급여" FROM emp;
SELECT ename, sal, sal+NVL(comm,0) "총급여" FROM emp;
-- 연결 연산자 ||
SELECT ename|| ' ' ||job AS staff FROM emp;
-- 사번, 사원명, 업무, 월급 출력
SELECT empno, ename, job, sal FROM emp;
-- 업무가 SALESMAN인 사람들만 출력 (대소문자 구분 해야 함)
SELECT empno, ename, job, sal FROM emp WHERE job='SALESMAN';
-- 월급이 1000 이상인 사람들만 출력
SELECT empno, ename, job, sal FROM emp WHERE sal>1000;
9. SELECT 검색하기 연습문제
-- 연습문제 1. 20번 부서에서 근무하는 사원의 사원번호, 이름, 부서번호 출력
SELECT empno, ename, deptno FROM emp WHERE deptno=20;
-- 연습문제 2. 입사일이 81/01/01에서 81/06/09인 사원의 사원번호, 이름, 입사일을 출력
SELECT empno, ename, hiredate FROM emp WHERE hiredate between '81/01/01' and '81/06/09';
SELECT empno, ename, hiredate FROM emp WHERE (hiredate<='81/06/09' AND hiredate>='81/01/01');
-- (+) 해당 날짜가 포함되지 않는 경우
SELECT empno, ename, hiredate FROM emp WHERE (hiredate<'81/06/09' AND hiredate>'81/01/01');
-- 연습문제 3. 담당업무가 salesman, clerk인 사원들의 이름과 업무를 출력
SELECT ename, job FROM emp WHERE job IN ('SALESMAN','CLERK');
SELECT ename, job FROM emp WHERE (job='SALESMAN' or job='CLERK');
-- 연습문제 4. 업무가 president이고 급여가 1500이상이거나 업무가 salesman인 사원의 정보를 출력
SELECT * FROM emp WHERE ((job='PRESIDENT' AND sal>1500) OR (job='SALESMAN'));
-- 연습문제 5. 업무가 president 또는 salesman이고 급여가 1500이상인 사원의 정보를 출력
SELECT * FROM emp WHERE (job IN ('PRESIDENT','SALESMAN') AND (sal>1500));
SELECT * FROM emp WHERE ((job='PRESIDENT' OR job='SALESMAN') AND (sal>1500));
-- 연습문제 6. 커미션(comm)이 없는 사원의 이름, 급여, 커미션을 출력
SELECT ename, sal, comm FROM emp WHERE NVL(comm,0)=0;
-- 연습문제 7. 사원명, 급여, 커미션, 총급여( 급여 + 커미션)을 출력
SELECT ename, sal, comm, sal+NVL(comm,0) AS "총급여" FROM emp;
-- 연습문제 8. 이름이 A로 시작하는 사원명 출력
SELECT ename FROM emp WHERE ename like 'A%';
-- 연습문제 9. 이름이 두번째 문자가 L인 사원명 출력
SELECT ename FROM emp WHERE ename LIKE '_L%';
-- 연습문제 10. 이름에 L이 두 번 이상 포함된 사원명 출력
SELECT ename FROM emp WHERE ename like '%L%L%';
-- 연습문제 11. 커미션(COMM)이 NULL이 아닌 사원의 모든 정보를 출력
SELECT * FROM emp WHERE comm IS NOT NULL;
-- 연습문제 12. 보너스가 급여보다 10%이상 많은 모든 사원에 대해 이름, 급여, 보너스를 출력
SELECT ename, sal, comm FROM emp WHERE comm>(sal*1.1);
-- 연습문제 13. 업무가 clerk이거나 analyst이고 급여가 1000, 3000, 5000이 아닌 모든 사원의 정보를 출력
SELECT * FROM emp WHERE job IN('CLERK','ANALYST')AND sal NOT IN(1000,3000,5000);
SELECT * FROM emp WHERE ((job='CLERK' OR job='ANALYST')
AND(sal!=1000 AND sal!=3000 AND sal!=5000));
-- 연습문제 14. 부서가 30이거나 또는 관리자가 7782인 사원의 모든 정보를 출력
SELECT * FROM emp WHERE (deptno=30 OR mgr=7782);
