🐷전동준(Jeon DongJun)

Go with the flow

[Oracle]오라클 강의내용 복습 1일차

Oracle Day1️⃣

  • Oracle은 여러 사람이 공유하여 사용할 목적으로 체계화 해 통합, 관리하는 데이터의 집합인 데이터 베이스다.
  • 정형 데이터들의 관계를 정의하는 관계형 데이터베이스(RDBMS)
  • 다른 DBMS보다 대량의 정보를 관리할 때 좋은 성능을 보임(대규모 데이터베이스 지원)
  • SQL문을 실행하는 가장 효율적인 방법을 선택
  • 고성능 트랜잭션 처리를 제공


다운로드 및 환경 세팅

  • 데이터베이스 다운로드 공식 홈페이지 : https://www.oracle.com/downloads/
  • 돈내고 쓰는 ORCL과 무료인 XE로 나뉘어짐
  • Database Express Edition 다운로드
    • image
  • Prior Release Archive 선택
    • image
  • 윈도우 버전 선택
    • image
  • Oracle 회원 가입 후 로그인 후 다운로드
  • 압축 해제 후 설치 파일 실행
    • image
  • (주의) 비밀번호 설정
    • image
  • Oracle 홈의 위치를 잘 기억해야 함
    • image
    • XE는 eXpress Edition

  • 설치가 꽤 오랜 시간이 걸린다😓

  • 다운이 완료되면 커맨드 창에서 sqlplus입력 후 비밀번호 입력
    • sqlplus는 내 컴퓨터에 설치되어 있는 Server(Oracle 18C XE)에 자동으로 찾아 접속해주는 클라이언트
    • image
    • 성공적으로 접속이 되는 것을 확인할 수 있다.
  • 추가로 DBeaver Community 설치
  • DBeaver 실행
    • image
  • 오라클과 연동
    • 새 데이터베이스 연결
      • image
    • 오라클 선택
      • image
    • 내 컴퓨터에 설치한 것이 맞기 때문에 localhost가 맞음, xe버전이기때문에 ORCL을 xe로 변경
      • image
    • [Edit Driver Setting] 클릭
      • image
    • [Libraries] 탭에들어가 기존의 것들 다 삭제
      • image
    • Java와 Oracle DB를 연결해주는 것(JDBC)을 찾아서 다운받아줘야 함
    • 오라클 홈페이지로 가서 JDBC Driver로 가서 자신에게 맞는 버전을 찾아서 다운(나는 18C를 다운받았기 때문에 18C)
      • image
    • ojdbc8.jar 파일을 라이브러리에서 추가해줘야함
    • 여기서 jar란 Java ARchive 파일을 뜻함
    • 파일 추가하고 [Find Class] 클릭 후 oracle.jdbc.OracaleDriver 선택하고 확인
      • image-20220304102957849
    • 비밀번호 적고 완료하면 DB가 잘 연결된 것을 확인할 수 있음
      • image
    • 위의 [SQL]을 누르면 스크립트가 생김
      • image


테스트

  • 강사님이 주신 .txt 파일을 받아 여러 명령어를 실행해봤음
    • image
    • CREATE, INSERT INTO, DROP, SELECT 등 다 잘 작동한다.


숙제(10문제 만들어서 제출)

  • 쪽지시험으로 쓰기 위해 우리들에게 문제 10개와 답을 만들어서 제출하라고 하셨다.
  • 그것을 그대로 적어봤다.
--1. 사원 테이블에서 커미션이 없는 사원의 이름과 월급을 구하고, 월급이 큰 순서대로 정렬하시오.
SELECT ENAME, SAL FROM EMP WHERE COMM IS NULL ORDER BY SAL DESC;

--2. 사원 테이블에서 위에 매니저가 존재하지 않는 사원의 이름과 부서번호를 출력하시오.
SELECT ENAME, DEPTNO FROM EMP WHERE MGR IS NULL;

--3. 사원 테이블에서 부서번호가 30인 사람들 중 1982년 1월 1일 전에 입사한 사원의 이름과 직업을 구하시오.
SELECT ENAME, JOB FROM EMP WHERE DEPTNO=30 AND HIREDATE < '1982-01-01';


--4. 사원 테이블에서 직업명과 해당 직업을 갖고 있는 사람 수를 구하시오.
SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB;

--5. 전체 사원의 수를 구하시오.
SELECT COUNT(*) FROM EMP;

--6. 커밋이 있는 사원들의 이름과 직업 연봉을 구하시오.(연봉 컬럼은 YEAR_SAL로 변경)
SELECT ENAME, JOB, SAL*12+COMM AS YEAR_SAL FROM EMP WHERE COMM IS NOT NULL;

--7. 관리자(MANGER)의 사원번호가 7839인 사원의 이름과 부서번호, 입사일을 구하시고 입사일이 빠른 순서로 정렬해주시오.
SELECT ENAME, DEPTNO, HIREDATE FROM EMP WHERE MGR = 7839 ORDER BY HIREDATE ASC;

--8. 직업이 관리자(MANAGER)인 사람들의 평균 월급을 구하시오.
SELECT AVG(SAL) FROM EMP WHERE JOB='MANAGER';

--9. 월급 테이블에서 가장 높은 등급의 월급의 중간값을 구하시오.
SELECT (LOSAL+HISAL)/2 FROM SALGRADE WHERE GRADE=5;

--10. 부서 테이블에서 지역(LOC)만 가져와서 새 테이블을 만들고(NEWDEPT) 조회해주세요.
CREATE TABLE NEWDEPT AS SELECT LOC FROM DEPT;
SELECT * FROM NEWDEPT;


DATABASE

  • 관계형 데이터베이스는 다음 3가지가 중요
    • Entity : 데이터베이스에 저장하려고 하는 현실 상의 개념이자 객체, TABLE
    • Attribute : Entity의 속성, COLUMN
    • Tuple : Entity의 값, ROW
  • KEY
    • key는 식별자로 튜플(행)을 검색이나 정렬 등을 할 때 사용한다.
    • 슈퍼키(Super/Composite) - 복합키, 유일성을 만족
    • 후보키(Candidate) - 유일성과 최소성 만족
    • 기본키(Primary) - 후보키 중 선택된 키
    • 대체키(Alternate) - 후보키 중 선택X 키
    • 외래키(Foreign) - 다른 테이블의 컬럼이나 기본키 참조
  • 데이터들의 관계를 구조화시켜서 테이블을 분리하는 정규화(Normalization)도 중요하다.
    • 1 정규화 - 하나의 컬럼엔 하나의 값
    • 2 정규화 - 기본키에 종속적이지 않은 컬럼 분리
    • 3 정규화 - 기본키를 제외한 나머지 컬럼들 간의 종속 불가
    • BCNF - 기본키가 여러개일 경우, 복합키로
    • 4 정규화 - 다치 종속을 제거
    • 5 정규화 - 조인이 후보키를 통해서만 성립되도록
    • 역(반)정규화 - 필요할 경우 정규화를 거꾸로 진행
  • 보통 기업에서는 3정규화 까지만 진행한다고 한다.
  • TRANSACTION : 최소한의 작업 단위
    • 원자성(Atomicity) : 모두 반영하거나 아니거나
    • 일관성(Consistancy) : 실행 이전 이후 결과가 일관
    • 고립성(Isolation) : 실행 도중 다른 트랜잭션의 영향을 받으면 안된다.
    • 지속성(Durability) : 성공하면 결과가 영구적으로 반영된다.


Oracle DataType

  • 오라클의 데이터 타입은 공식홈페이지 Documentation에서 확인할 수 있다.
  • 나는 18C 버전이기에 해당 버전의 데이터타입은 여기서 확인 - Data Types (oracle.com)


DDL

뷰(VIEW) 생성

  • 뷰를 생성하고 확인해볼 수 있다.

    • CREATE OR REPLACE VIEW V_EMP AS SELECT * FROM EMP;
SELECT * FROM V_EMP;

    • image

    • 데이터를 확인만 할 수 있는 뷰를 생성했다.

시퀀스(SEQUENCE) 생성

  • 시퀀스 생성 후 확인

    • CREATE SEQUENCE sequence_test;
SELECT sequence_test.nextval FROM dual;

    • dual 은 오라클에서 사용하는 가상 테이블

    • image

    • 두 번 눌러서 2가되었다;;😅 select 하면 자동으로 1씩 증가하는 것을 확인

이미 만들어져있는 테이블(TABLE) 복사

  • 테이블 전체 복제

    • 기존에 있는 emp 테이블 전체를 복제해 myemp테이블을 만듦. 그리고 확인

    • CREATE TABLE myemp AS SELECT * FROM emp;
SELECT * FROM myemp;
    • image

  • 특정 컬럼만 복제

    • emp 테이블의 ename, empno 컬럼만 가져와서 emps라는 테이블을 만들고 확인해보자.

    • CREATE TABLE emps AS SELECT ename, empno FROM emp;
SELECT * FROM emps;
    • image

  • 테이블의 데이터 값 말고 구조(컬럼)만 복제해보기

    • WHERE절의 조건이 FALSE라면 구조만 복사된다고 한다.(예) WEHRE 1=2)

    • emp테이블의 구조만 복사해서 empall 테이블을 만들고 확인해보자.

    • CREATE TABLE empall AS SELECT * FROM emp WHERE 1 = 2;
SELECT * FROM empall;
    • image

NOT NULL 특성 주기

  • 테이블을 만들거나 수정해서 속성을 변경할 수 있다.

  • NOT NULL 속성을 부여할 수 있다.(NOT NULL은 값이 비어있으면 안되고 무조건 값이 들어가있어야 함)

  • 예제 1

    • CREATE TABLE table_notnull01(
  id char(3) NOT NULL,
  name varchar2(20)
);
    
INSERT INTO table_notnull01 
values(1, 'dongjun');
    
SELECT * FROM table_notnull01 ;

    • image

    • 값을 비우지 않고 잘 넣어주면 잘 들어가는 것을 볼 수 있다. 하지만

    • INSERT INTO table_notnull01 
values('','dongjun2');

    • image

    • ID가 NOT NULL로 설정이 되어있기 때문에 값이 없는 상태로 데이터를 삽입하려니깐 되지 않는다.

    • 대신 ' ' 처럼 공백은 들어갈 수 있음.

  • 예제2

    • CONSTRAINT로는 NOT NULL 적용시킬 수 없다.

    • NOT NULL은 COLUMN 레벨에서만 사용이 가능하다.

    • CREATE TABLE table_notnull02(
  id char(3),
  name varchar2(20),
  CONSTRAINT tn02_id_nn NOT NULL (id)
);

    • image

    • CONSTRAINT로 ID라는 컬럼에 NOT NULL 제약을 해줄려다보니깐 에러가 난다.

UNIQUE 속성

  • 컬럼들이 갖는 값들이 유일(1개만)해야 한다는 조건을 주어주는 것

  • 예제1

    • ID 컬럼에 UNIQUE를 직접 주기

    • CREATE TABLE table_unique01(
  id char(3) UNIQUE,
  name varchar2(20)
);
    
INSERT INTO table_unique01
values('100', 'oracle');
    
INSERT INTO table_unique01
values('200', 'python');
    
INSERT INTO table_unique01
values('100', 'hadoop');

    • 이 경우 ID 컬럼에 유일한 특성을 쥐어줬기 때문에 세번째 insert가 무결성을 위배해 들어갈 수 없게 된다. 똑같은 id값 100이 이미 존재하기 때문에

    • image

    • INSERT INTO table_unique01(name)
values('hadoop');
    
INSERT INTO table_unique01(name)
values('spark');
    
    
SELECT * FROM table_unique01;

    • image

    • 이 경우에는 들어가진다. NULL값은 유일하지 않다.

  • 예제2

    • CONSTRAINT 사용

    • CREATE TABLE table_unique02(
  id char(3),
  name varchar2(20),
  CONSTRAINT tuo02_id_unq UNIQUE (id)
);
    
INSERT INTO table_unique02 
values('100', 'oracle');
    
INSERT INTO table_unique02
values('200', 'python');
    
INSERT INTO table_unique02 
values('100', 'oracle');

    • 통제 조건을 넣어주고 똑같은 값을 넣어주면 CONSTRAINT 명이 명시된 에러가 나온다.

    • image

  • 예제3

    • 제약조건을 id, name 두개를 감싸서 걸어줘보자.

    • CREATE TABLE table_unique03(
  id char(3),
  name varchar2(20),
  CONSTRAINT tou03_id_unq UNIQUE (id, name)
);
    
INSERT INTO table_unique03	
VALUES('100', 'oracle');
INSERT INTO table_unique03
VALUES('100', 'python');
    
SELECT * FROM table_unique03;

    • image

    • ID가 같은 100이여도 잘 들어가 진다. id와 name을 같이 엮어서 제약을 줬기 때문에 똑같은 100,oracle이나 100,python을 넣지 않는 이상 잘 들어갈 것이다.

    • 따로 주려면 이렇게 해야한다.

    • CREATE TABLE table_unique04(
  id char(4),
  name varchar2(20),
  CONSTRAINT tu04_id_unq unique(id),
  CONSTRAINT tu04_id_unq2 unique(name)
);
    
    
INSERT INTO table_unique04
values('100','oracle');
INSERT INTO table_unique04 
values('100','python');

    • 이렇게 따로따로 제약조건을 준 것이기 때문에 두번째 100,python이 들어가지지 않는다.

    • image

주요키(Primary Key) 속성

  • 이번에는 주요키를 주는 실습을 해보자.

  • 주요키 속성은 unique 이면서 not null 속성을 갖고 있다.

  • 예제1

    • CREATE TABLE table_pk01(
  id char(3) PRIMARY KEY,
  name varchar2(20)
);
    
INSERT INTO table_pk01
values('100','oracle');
INSERT INTO table_pk01 
values('200','mysql');
INSERT INTO table_pk01 
values('200','mssql');

    • 세번째 200,mssql이 들어가지 않는다.

    • image

  • 예제2

    • 제약조건으로 제약 생성 예제

    • CREATE TABLE table_pk02(
  id char(3),
  name varchar2(20),
  CONSTRAINT tp02_id_pk PRIMARY KEY (id)
);
    
INSERT INTO table_pk02 
values('100','oracle');
    
INSERT INTO table_pk02 
values('200','mysql');
INSERT INTO table_pk02 
values('200','mssql');

    • 역시 200,mssql이 들어가지 않는다.

    • image

  • 예제3

    • 이번엔 id, name 두개를 묶어서 제약 조건을 주자.

    • CREATE TABLE table_pk03(
  id char(3),
  name varchar2(20),
  CONSTRAINT tp03_id_pk PRIMARY KEY (id, name)
);
    
INSERT INTO table_pk03
values('100','oracle');
INSERT INTO table_pk03 
values('200','mysql');
INSERT INTO table_pk03 
values('200','mssql');
INSERT INTO table_pk03(name)
values('sqlite3');

    • image

    • 묶여서 하나의 키로 잡아주기 때문에, id에 NULL값이 들어갈 수 없어서, name에 sqlite3만 들어갈 수 없다.

외래키(Foreign Key) 속성

  • 외래키는 다른 테이블을 참조할 때 비교하는 키(컬럼)이다.

  • 예제1

    • 외래키 속성을 컬럼에 직접 지정해주고,

    • 위에서 만든 table_pk01 테이블의 id 컬럼을 주요키로 참조하는 경우

    • CREATE TABLE table_fk01(
  id char(3),
  name varchar2(20),
  pkid char(3) REFERENCES table_pk01(id)
);
    
INSERT INTO table_fk01
values('500','oracle','500');

    • image

    • image

    • 해당 테이블(table_pk01)에 500이라는 값이 없어서 안들어가진다.

    • INSERT INTO table_fk01
values('500','oracle','100');
    
SELECT * FROM table_fk01;

    • image

    • table_pk01 테이블의 id 컬럼에는 100값이 존재하기 때문에 pkid가 100으로 잘 들어가진 것을 확인할 수 있다.

  • 예제2

    • 제약조건으로 위에서 만든 table_pk02 테이블의 id 컬럼을 주요키로 참조하는 경우

    • CREATE TABLE table_fk02(
  id char(3),
  name varchar2(20),
  pkid char(3),
  CONSTRAINT tf02_id_fk FOREIGN KEY (id) REFERENCES table_pk02(id)
);
    
INSERT INTO table_fk02 
VALUES('300','hadoop','300');

    • image

    • 역시 되지 않는다. table_pk02 테이블의 id 컬럼에는 300이라는 값이 없기 때문이다.

    • INSERT INTO table_fk02 
VALUES('300','hadoop','200');

    • 이렇게 넣어도 되지 않는다. 왜?

    • CONSTRAINT tf02_id_fk FOREIGN KEY (id) REFERENCES table_pk02(id)

    • 이 제약 조건에서 FOREIGN KEY를 ID로 지정해놓았기 때문에 안되는 거다 당연히.(똑같이 300인 것이 table_pk02테이블의 id컬럼에 없기 때문에)

    • SQL문을 적으면서 오타를 주의하자!

    • 그러면 id값을 200으로 변경해보면?

    • INSERT INTO table_fk02 
VALUES('300','hadoop','200');

    • image

    • 잘 들어간다😋

    • (id)를 (pkid)로 바꿔도 될 것이다!

체크(CHECK) 속성

  • 체크 속성을 이용하면, 체크 속성에 정해준 값이 아니라면 입력이 되지 않는다.

  • 예제

    • MARRIAGE 컬럼에 직접 속성을 준 경우

    • CREATE TABLE table_check01(
  id char(3),
  name varchar2(20),
  marriage char(1) CHECK(marriage IN ('y','n'))
);
    
INSERT INTO table_check01
values('100','oracle','y');
    
INSERT INTO table_check01
values('200','python','n');
    
INSERT INTO table_check01
values('300','html','t');

    • 마지막 300,html,t는 들어가지 않는다. y나 n이 아니기 때문

    • image

번외

  • 오라클에는 특별하게 SYSDATE 라는게 있는데, 이것은 지금 현재 날짜와 시간을 반환한다.
  • SELECT SYSDATE FROM DUAL;
    • 내 컴퓨터의 설정마다 다르게 나올 수 있는 점을 유의
    • image

DML

SELECT

  • * (ASTERISK)를 사용하면 해당 테이블의 모든 컬럼을 조회한다.

  • 그게 아니라 특정 컬럼을 가져오고 싶으면 컬럼명을 적어주고, 다수의 컬럼을 가져오고 싶다면 컬럼명 사이에 ,를 적어준다.

  • || 를 사용하면 각 값을 합칠 수 있음

    • EMPNO   ENAME 하면 10JORDAN 이런식으로 합쳐짐

    • 예제

      SELECT ENAME||'님이 '||HIREDATE||'에 입사를 하고 '||SAL||'의 월급을 받습니다.' AS GOOD FROM EMP;

      image

  • +를 사용하면 각 컬럼의 값을 더할 수 있는데 문자형과 숫자형이 더해지면 NULL로 값이 조회된다.

  • WHERE 절에서 특정 범위 값을 조건으로 잡고 싶다면, BETWEEN A AND B나 >= <= 등을 사용하면 된다.

  • 날짜를 다룰때는 1980-01-01 이렇게 해도되고 80-01-01 이렇게해도 되며, 1980/01/01도 되고 80/01/01도 된다.

  • ORDER BY문을 이용해 데이터를 정렬할 수 있으며 디폴트는 ASC(오름차순)이다.

  • 집계함수 SUM, AVG, MAX 등을 이용하고 싶으면 그룹화 해줘야하는데 그 때 GROUP BY를 사용하면 되고 그룹화할 컬럼명을 적어주면 된다.

INSERT

  • INSERT도 위에서 많이 사용해서 패스
  • INSERT INTO 테이블명 VALUES('값1','값2') 이런 식으로 사용
  • 값1과 값2는 해당 테이블의 컬럼 순서를 잘 파악하고 속성도 잘 파악해서 넣어야 한다.

UPDATE

  • 아까 만든 TEST01 테이블에서 이름이 WARD인 사원의 월급을 2000으로 바꾸자.

    • UPDATE TEST01 
SET SAL = 2000
WHERE ENAME ='WARD';
    
SELECT * FROM TEST01;
    • image

  • 이름이 WARD인 사원의 직업을 MANAGER로 변경 후 부서를 20으로 바꾸자.

    • UPDATE TEST01
SET JOB = 'MANAGER', DEPTNO = 20
WHERE ENAME = 'WARD';
    
SELECT * FROM TEST01;
    • image

DELETE

  • DELETE는 삭제하는 DML이다.
  • 조건을 적지 않으면 다 날라갈 수 있기 때문에, 조심해서 꼭 원하는 조건을 적어줘야 함


DCL

  • 데이터 제어언어(Data Contorl Language)
  • 데이터를 트랜잭션할 때 트랜잭션을 저장하고 취소하는 TCL이 있다,
    • COMMIT : 데이터와 트랜잭션 저장
    • ROLLBACK : 데이터와 트랜잭션 취소, 가장 최근의 COMMIT 상태로 돌아간다.
  • 그리고 데이터베이스에 접근,관리할 수 있는 권한을 주는 GRANT와 그것을 삭제하는 REVOKE가 있다.
  • 그렇게 각각의 사용자에게 권한과 역할을 부여해 데이터에 대한 보안을 유지한다.
    • 시스템 권한
      • 객체 생성, 변경, 소멸 등에 관한 권한
      • SYSTEM이 부여하나, 기능이 매우매우 강력하기 때문에 일반적으로 관리자만 사용한다.
    • 객체 권한
      • 사용자가 특정 객체에 대한 작업을 수행할 수 있도록 권한을 부여한다.
      • 객체의 소유자가 부여할 수 있다.
    • 역할(Role)
      • 권한의 집합으로 많은 권한을 한꺼번에 부여하기 위해서다.
  • 설치와 동시에 기본적으로 생성되어 있는 Role
    • CONNECT - 접속 권한 등
    • RESOURCE - 객체와 관련된 기본 시스템 권한(생성, 수정, 삭제 등)
    • DBA - DB 관리 권한 등
    • SYSDBA - DBA + DB 시작, 종료 권한 등
    • SYSOPER - SYSDBA + DB 생성 권한 등

2022

[web]jQuery 복습 3

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 2

13 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]jQuery 복습 1

14 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리4

5 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리3

10 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리2

7 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]JavaScript 정리1

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]CSS 기초 정리

11 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[web]HTML 기초 정리

8 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

[Pandas]pandas 연습

3 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑

2021

[Python기초]module

1 분 소요

[Noitce] 고쳐야하거나 틀린 것이 있으면 말씀해주세요!

맨 위로 이동 ↑