데이터베이스 이론(Oracle)

● 데이터베이스

- 데이터를 모아 놓은 것으로 이런 데이터베이스를 관리하는 소프트웨어가 DBMS이다
- 데이터베이스를 관리한다는 것은 데이터베이스 안에 데이터를 저장하고, 필요한 데이터를 꺼내고, 저장된 데이터 수정, 삭제하는 행위임

▶ RDMBS
- 관계형 데이터베이스
- R은 관계를 의미
- 종류 : 마리아디비, 오라클, H2 등

* 관계형 모델
- 테이블(데이터) 간의 연결
- 2차원 구조의 테이블에 데이터를 성격에 맞게 분리하여 중복되지 않게 저장하고, 필요한 데이터는 테이블 간에 관계를 맺어 추출하는 것

* 테이블
- 컬럼(열)과 로우(행)로 구성
- 컬럼(=필드) : 테이블을 구성하는 데이터의 속성
- 로우 : 지정된 컬럼 데이터로 이루어진 하나의 묶음

* RDBMS의 단점
- 컬럼이 변경되면 기존에 테이블을 사용했던 프로그램을 모두 수정해야함

▶ SQL(Structured Query Language)
- 해석 : '구조화된 혹은 구조적 질의 언어'
-> 틀이나 형식이 정해져있다, 문법을 지켜야한다
- RDBMS와 소통하는 프로그래밍 언어
- 질의(query)언어이다
- 데이터베이스를 상대로 데이터를 조회, 입력, 수정, 삭제하기 위해 사용
- 조건에 맞는 데이터 전체를 한번에 처리하는 집합적 언어
- RDBMS 제품의 종류에 구애 받지 않고 SQL을 사용할 수 있음

* SQL 문법의 종류

- DDL / DML / DCL(TCL)

○ DDL : 테이블 구조
- Data Definition Language
- CREATE(객체 생성) / DROP(객체 삭제) / ALTER(객체 변경) / TRUNCATE TABLE(테이블의 모든 데이터 삭제, 테이블 구조는 유지) / RENAME(객체 이름만 변경)

○ DML : 데이터 변경
- Data Manipulation Language
- INSERT(데이터 추가) / SELECT(데이터 가져오기) / UPDATE(데이터 검색후 수정) / DELETE(데이터 검색후 삭제)
#TRUNCATE TABLE | DELETE 차이
- 데이터 삭제는 공통 개념
- 전자는 모든 데이터 삭제 후 복구 불가
- 후자는 선별적 삭제 가능, 복구 가능

○ DCL : 사용자 권한
- Data Control Language
- GRANT(권한 부여) / REVOKE(권한 회수)
- 이 기능을 사용하려면 권한을 주거나 회수 할 대상(사용객체)가 있어야 한다
- CREATE USER 다음에 바로 실행하는 경우가 많음
- 부여된 권한을 사용하려면 사용자가 로그인(DBMS에 접속)해야 사용할 수 있다
- 특정 객체를 사용하려면 권한이 부여되거나 회수 되어야 한다.

○ TCL : 데이터 처리 흐름을 관리 실패시 복구, 성공시 저장
- Transaction Control Language
- COMMIT : 변경된 데이터를 DB에 적용
- ROLLBACK : 변경된 데이터를 변경시키기 이전으로 되돌림
- 큰 작업을 할때는 TCL을 이용하는 것이 안전하다
- 최종 확인이 된 경우에만 COMMIT, 조금이라도 불완전한 경우 ROLLBACK

## 테이블 만들기

* sts의 Database Navigator(webuser)에서 작업하기

CREATE TABLE 테이블명 (
컬럼1 데이터타입 [NOT] NULL [제약조건],
컬럼2 데이터타입 [NOT] NULL [제약조건],
PRIMARY KEY(기본키로 삼을 컬럼명)
);

- 테이블명 : 테이블 생성시 사용할 이름
- 컬럼n : 실제 데이터가 들어가는 컬럼의 이름
- 데이터타입 : 컬럼의 데이터 유형
- [NOT] NULL : 데이터 값이 비어도 되거나 (NULL) 반드시 들어가야한다
- [제약조건] : 필요하면 컬럼의 데이터를 제한할 수 있다
- PRIMARY KEY : 데이터를 유일하게 식별할 수 있는 키값 컬럼을 설정, 복합 컬럼으로 기본키를 만들면 편리함

* 테이블

- 테이블은 데이터를 보관하는 저장소. 테이블의 구조는 로우가 아닌 컬럼이 구성
- 테이블 이름과 컬럼이름은 최고 30자를 넘지 않도록 하는 것이 좋음
- _(언더스코어)문자 숫자를 사용 가능하지만 첫 글자는 반드시 문자
- 한글을 컬럼등의 객체이름으로 사용은 가능하나 권장하지 않음
- 단어와 단어 사이는 '_'를 사용하는 것이 좋음
- 기본적으로 대소문자 구분하지 않는 DBMS가 많다
ex) employee, product, emp_id, employee_id 등

## 컬럼의 데이터 유형
* 문자형
- CHAR(n) : 고정길이 n자 까지, 최대 2000자
- VARCHAR2(n) : 가변길이 최대 n자 까지, 최대 4000자
* 숫자형
- NUMBER[p,s] : p(전체 유효 자리수),s(소수점 이하 유효 자리수)는 생략 가능
* 날짜형
- BC 4712년 1월 1일 ~ 9999년 12월 시 분 초 입력하면 자동변환

## NULL
- 데이터베이스 : 데이터가 없음
- 자바 : 가르키는 주소 없음
- NOT NULL : 반드시 데이터가 들어가야 한다

● 실습

* 오라클 webuser에서 작업

# {연습} 테이블 만들기(DDL)

▷ 사원정보테이블을 아래의 조건을 참조하여 만들기

[요구조건]

컬럼명 컬럼설명 데이터형 NULL여부 기본키
-----------------------------------------------------------------------------------------------
emp_id 사원번호 NUMBER NOT NULL O
emp_name 사원명 VARCHAR2(100) NOT NULL
gender 성별 VARCHAR2(10) NULL
age 나이 NUMBER NULL
hire_date 입사일자 DATE NULL
etc 기타 VARCHAR2(500) NULL

[답변]

CREATE TABLE employee (
    emp_id NUMBER NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,
    gender VARCHAR2(10) NULL,
    age NUMBER NULL,
    hire_date DATE NULL,
    etc VARCHAR2(500) NULL,
    PRIMARY KEY(emp_id)
);

▷ 테이블이 잘 만들어졌는지 DML의 SELECT로 확인

SELECT * FROM employee;

# {연습} 테이블에 데이터 추가(DML)
-구문 :
INSERT INTO 테이블명(컬럼리스트, ...)
VALUSE(값리스트, ...);

-테이블에 데이터를 추가한다
-컬럼리스트와 값리스트는 서로 일치하는 데이터형이어야 한다
-값은 절대로 명시적으로 적용하기
-각 컬럼과 값의 구분기호는 ',' 사용
-각 컬럼과 값에 명시한 순서와 데이터형이 같아야한다

▷ 이전에 작성한 employee테이블에 다음의 데이터를 추가하시오

EMP_ID EMP_NAME GENDER AGE HIRE_DATE ETC
1 홍길동 남성 33      2023-03-03
2   김유신 남성    44      2023-04-04
3    강감찬 남성    55      2023-05-05
4    신사임당 여성     45      2023-06-06

[답변]

INSERT INTO employee(emp_id, emp_name, gender, age, hire_date)
	VALUES(1, '홍길동', '남성', 33, '2023-03-03');
INSERT INTO employee(emp_id, emp_name, gender, age, hire_date)
	VALUES(2, '김유신', '남성', 44, '2023-04-04');
INSERT INTO employee(emp_id, emp_name, gender, age, hire_date)
	VALUES(3, '강감찬', '남성', 55, '2023-05-05');
INSERT INTO employee(emp_id, emp_name, gender, age, hire_date)
	VALUES(4, '신사임당', '여성', 45, '2023-06-06');
INSERT INTO EMPLOYEE (emp_id, emp_name, gender, age, hire_date)
	VALUES (5,'세종대왕', '남성', 46, '2022/07/07');

(주의)
- 문자열에서 여백은 글자로 포함됨
- TRIM함수를 통해 여백 지우기
- 날짜의 경우 날짜 형식을 지키고 있으면 앞뒤 공백은 무시함(공백이 있어도 오류가 아님)
- 사이의 공백은 유효함(오라클에서 판단 불가)
- 오라클 insert 오류 (NOT NULL, PRIMARY KEY에 의해 발생)

- 오라클은 자동 숫자증가 기능이 미흡
- RDBMS(오라클 등) 내부에서 식별자는 대소문자 구분하지 않음

* 무결성 제약 조건
데이터베이스의 정책을 위반하지 않는 유일한 값
PRIMARY KEY에 의해 발생

▷ INSERT구문의 확장(DML)
INSERT INTO 테이블명
values(값리스트, ...);
- 컬럼리스트가 빠진 경우는 모든 컬럼을 다 사용하겠다는 뜻
- 값리스트는 모든 컬럼리스트와  일치
- 컬럼리스트는 테이블을 만들때 사용한 컬럼순서를 따라야 합니다.
- 값 리스트도 이 순서와 일치햐야만한다
- INSERT INTO 테이블명 (컬럼리스트) VALUES(값리스트);
구문이 가독성,유지 / 보수 측면에서 더 도움이 됨

* 오라클의 UPDATE Query 기본설정
- 트랜잭션이 기본으로 걸려있다
- UPDATE Query를 실행하면 반드시 COMMIT명령을 내리기

▷DELETE 데이터 삭제 (DML)
- 구문
DELETE FROM 테이블명 WHERE 조건
- DELETE FROM에서 FROM 생략 가능(가독성을 위해 작성 권장)
- WHERE 조건절은 생략하면 안됨
ex)  DELETE FROM employee WHERE 1 = 1;
    DELETE FROM employee;
    TRUNCATE TABLE employee;
-> 같은 기능

#{연습}
5번 자료 삭제
- 원하는 구문 블록 잡아 스크립트 실행

DELETE FROM EMPLOYEE WHERE EMP_ID = 5;

▶ 테이블 새로 만들기

[요구조건]

컬럼명 컬럼설명   데이터형     NULL여부 기본키
-------------------------------------------------------------------------------------------------------------------
seq_id   일련번호   NUMBER   NOT NULL   Y
station_name 지하철역명 VARCHAR2(100) NULL
board_date 탑승일자 DATE    NULL
gubun 승차/하차 구분 VARCHAR2(10) NULL
board_time 탑승시간 NUMBER    NULL
passenger_number 승객수    NUMBER    NULL

[답변]

CREATE TABLE station (
	seq_id			NUMBER		NOT NULL,
	station_name		VARCHAR2(100)	NULL,
	board_date		DATE		NULL,
	gubun			VARCHAR2(10)	NULL,
	board_time		NUMBER		NULL,
	passenger_number	NUMBER		NULL
);

▷ 데이터 추가
* board_time(탑승시간)은 탑승한 시간대를 적는 것으로 제한
9시 ~ 9시 59분까지라면 9
10시 ~ 10시 59분까지라면 10

[요구조건]

seq_id station_name board_date gubun board_time passenger_number
1 '서울역(150)' '2023-01-01' '승차' 9 1200
2 '서울역(150)'   '2023-01-01' '하차' 9      1500
3      '서울역(150)'   '2023-03-03' '승차' 10      500
4       '서울역(150)'   '2023-03-03' '하차' 10   1000
5       '서울역(150)'    '2023-04-04' '승차' 10      2000

INSERT INTO station(seq_id, station_name, board_date, gubun, board_time, passenger_number)
	VALUES(1, '서울역(150)', '2023-01-01', '승차', 9, 1200);
INSERT INTO station(seq_id, station_name, board_date, gubun, board_time, passenger_number)
	VALUES(2, '서울역(150)', '2023-01-01', '하차', 9, 1500);
INSERT INTO station(seq_id, station_name, board_date, gubun, board_time, passenger_number)
	VALUES(3, '서울역(150)', '2023-03-03', '승차', 10, 500);
INSERT INTO station(seq_id, station_name, board_date, gubun, board_time, passenger_number)
	VALUES(4, '서울역(150)', '2023-03-03', '하차', 10, 1000);
INSERT INTO station(seq_id, station_name, board_date, gubun, board_time, passenger_number)
	VALUES(5, '서울역(150)', '2023-04-04', '승차', 10, 2000);

'백엔드 > 데이터베이스' 카테고리의 다른 글

SQL 함수(숫자형 함수, 문자형 함수) (0)	2023.07.10
데이터베이스(SELECT) (0)	2023.07.05
오라클 - 기본 문법 (0)	2023.06.27
오라클 설치 (0)	2023.06.27
데이터베이스 - MySQL Workbench (0)	2023.05.23