기술노트 블로그

안녕하세요, 이것이 오라클이다를 공부하고 강의 자료를 남깁니다.

SELECT문

원하는 데이터를 가져와 주는 기본적인 (SELECT ... FROM)

SELECT의 구문 형식

실습

HR. 이렇게 스키마 명을 붙여서 스키마에 있는 테이블을 조회가 가능하다.

SYS. 시스템 스키마이다.

DBA_USERS를 통해서 모든 사용자(스키마) 조회가 가능하다.

DBA_TAB_COLUMNS를 통해서 스키마의 테이블 조회가 가능하다.

sqlDB 스키마를 만들고 userTBL, buyTBL 등을 만들었다. 

CREATE SEQUENCE idSEQ;

시퀀스를 만들면 idSEQ.NEXTVAL 정도가 가능하다.

COMMIT;

을 통해서 완료한다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=m60T2eFbCYA&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=11

Linux 컴퓨터에 설치된 Oracle을 사용하고 싶다면 다음 그림과 같은 구성을 해야 한다.

먼저 리눅스 서버에 접속을 해보았다.

리눅스 서버의 ip주소를 확인해보았다.

두번째의 inet을 확인하면 된다.

그리고 윈도우 컴퓨터에서 리눅스 서버에 ping을 보내본다.

잘 응답하는 것을 확인할 수 있다.

그리고

윈도우 컴퓨터의 Oracle SQL Developer를 통해서

리눅스 서버의 오라클로 접속을 한다.

윈도우 컴퓨터에서 리눅스 컴퓨터로 접속을 한 것이다.

그리고 dba_users 를 확인해서

스키마들을 총 확인한다.

이번에는 리눅스 서버에 새로운 스키마를 생성해보았다.

yourDB이다.

그리고 새로운 접속을 생성한다.

또한, 새로운 테이블을 생성하고

조회를 해보았다.

정상적으로 잘 실행된다.

데이터베이스 초록색 체크 버튼을 클릭을 하면

커밋이 된다.

이제 리눅스 서버 sqlplus에서 테스트해볼 차례다.

sqlplus yourDB/1234 명령어를 통해서 yourDB로 sqlplus에 접속한다.

테스트 데이터를 INSERT하고

조회를 해본다.

커밋을 한다.

조회가 정상적으로 잘 된다.

한글이 깨지는 것은 특별한 설정이 없기 때문이다.

사용자 관리하기

사장님, 팀장님, 일반직원 스키마가 있다.

이 사장님 스키마, 팀장님 스키마 그리고 일반직원 스키마는 그대로 두고

Shop 스키마 그리고 HR 스키마를 다루어볼 예정이다.

사장님은 사용하지는 않을 것이므로 단순히 읽기 권한만 갖는다.

팀장님은 데이터베이스 관리자 역할을 한다.

사용자는 Shop 스키마에 대해서 읽기/쓰기 권한을 갖는다. 하지만 HR 스키마는 중요하므로 읽기 권한만 갖는다.

이를 총괄하는 것이 바로 팀장님이다.

1. director 생성

팀장님에게 가장 높은 권한인 DBA가 들어간다.

권한이 부여됨, 관리자 그리고 기본값에 체크해준다.

이 데이터베이스 전체를 총괄하는 시스템과 같은 역할을 하게 된 것이다.

팀장님을 만들었다.

2. ceo 생성

모든 테이블에 대한 SELECT 권한이 부여된다.

사장님도 만들었다.

3. staff 생성

테이블에 접속할 수 있는 CONNECT 권한

일반직원에게는

Shop 스키마에 대한 memberTBL, productTBL 에 대한 모든 권한이 부여된다.

HR 스키마에 대한 COUNTRIES, DEPARTMENTS, EMPLOYEES 등의 테이블에 대한 SELECT 권한이 부여된다.

director 실습

사용자 CREATE 및 DROP이 가능하다.

ceo 실습

사장님은 모든 테이블에 대한 SELECT가 가능하다.

하지만 INSERT, UPDATE, DELETE 등은 당연히 불가능하다.

사용자는 Shop 테이블에 대한 모든 권한을 주었다.

SELECT/INSERT/UPDATE/DELETE는 가능하다.

하지만 테이블을 삭제하는 작업은 안된다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=SywgEhFczHs&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=10

SQL Developer

테이블을 우클릭하고 빠른 DDL을 통해서

워크 시트에 저장을 하면

다음과 같이 DDL문이 나오는 것을 볼 수 있다.

생각보다 CREATE문이 복잡하다.

기본값 세팅을 위해서다.

우리가 입력할 때는 기본값 세팅을 직접 입력하지 않아도

기본값이 자동으로 입력된다.

실제 CREATE문은 생각보다 많이 복잡하다.

carTable을 생성하고

테스트 데이터를 입력한 다음

SELECT * FROM carTable; 명령어를 실행해서

테이블을 조회해봤다.

HELP INDEX를 통해서

SQLPLUS 전용 명령어를 확인할 수 있다.

각 조회된 명령어들을 

HELP RUN 등과 같이 입력을 하면

RUN이 어떤 용도인지 확인할 수 있다.

SQLPLUS 전용 명령어의 특징은 세미콜론을 안 붙여도 되요.

권장사항은 세미콜론을 붙여주세요.

DESCRIBE 명령어를 통해서 carTable 구조를 파악할 수 있다.

LIST 명령어를 통해서 우리가 사용했던 SQL문을 확인할 수 있다.

L도 똑같다.

LIST는 버퍼의 내용을 확인하는 것이다.

LIST에 있는 버퍼의 내용을 실행한다.

LIST에 있는 내용을 편집도 가능하다.

APPEND 명령어를 통해서 ORDER BY data를 통해서 버퍼의 내용에 추가할 수도 있다.

DEL 명령어를 통해서 버퍼의 내용을 지울 수도 있다.

그리고 다시 LIST를 해보면

SP2-0223: No lines in SQL buffer.

에러 구문을 확인할 수 있다.

RUN 명령어를 실행해봐도 

SP2-0103: Nothing in SQL buffer to run.

에러 구문을 확인할 수 있다.

SHOW USER 명령어를 통해서

현재 사용자를 확인할 수 있다.

CONNECT HR/1234@XE 명령어를 통해서

HR 사용자에 연결할 수 있다.

그리고 다시 USER USER 명령어를 실행하면

현재 사용자가 HR로 변경된 것을 확인할 수 있다.

그리고 HR 스키마에 있는 employees 테이블을 확인해보면

이렇게 조회가 잘 되는 것을 볼 수 있다.

column first_name heading "이름" format a15 명령어를 통해서

간격을 조정할 수 있다.

그리고 다시 RUN을 해보면

깔끔하게 바뀐 것을 볼 수 있다.

현재 내용을 저장할 수 있다.

HOST 명령어를 통해서 SQLPLUS를 잠깐 빠져나와서 프롬프트 창으로 이동할 수 있다.

DIR 명령어를 통해서 방금 생성한 sqlfile.sql 파일을 확인할 수 있다.

TYPE 명령어를 통해서 내용을 확인할 수 있다.

EXIT 명령어를 통해서 명령 프롬프트 창을 빠지고 다시 SQLPLUS 창으로 나올 수 있다.

START C:\TEMP_SQLPLUS\sqlfile.sql 명령어를 통해서

저장된 파일을 실행할 수 있다.

앞으로 쓸 것을 저장하겠다.

SPOOL 명령어를 통해 가능하다.

NOTEPAD 명령어를 통해서 확인해봤다.

텍스트 파일로 저장이 되었다는 의미이다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=dI_tFgM0WH0&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=9

데이터베이스 모델링이란

데이터베이스 모델링이란 건축물의 설계도와 같다.

프로젝트란

현실세계의 업무를 컴퓨터 시스템으로 옮겨놓는 일련의 과정

폭포수 모델

폭포가 떨어지듯이 각 단계가 끝나면 다음 단계로 진행하는 것

데이터베이스 모델링

폭포수 모델의 업무 분석과 시스템 설계 부분에 들어간다.

현실에서 쓰이는 것을 테이블로 만든다.

현실세계 - 진열된 물건, 직원, 고객이 쇼핑몰을 운영한다.

이를 데이터베이스의 테이블로 만든다.

또한 행위 자체도 테이블로 만들 수 있다.

모델링도 별도의 책이 따로 있다.

이를 공부하면 좋다.

이렇게 L자형 테이블은 필요가 없어서 공간의 낭비가 된다.

그래서 분리하려고 한다.

그래서 테이블을 나누었다.

그리고 구매 테이블에 PK를 추가해준다.

주키와 기본키라 부른다.

외래키라 부른다.

김범수가 여러번 구매를 할 수 있다.

이러한 경우 1:N 관게라고 합니다.

그리고 테이블을 설계한다.

SQL Developer의 Data Modeler 툴을 사용하겠다.

보기 > Data Modeler > 브라우저를 선택한다.

보기 브라우저를 확대하여 사용한다.

제목 없음을 우클릭 해서 먼저 디자인을 저장한다.

모델 저장

우리가 주로 사용하는 것은 관계형 모델이다.

우클릭하여 표시를 선택하면 볼 수 있다.

테이블 버튼을 누르면 테이블을 만들 수 있다.

이름을 userTBL로 입력하고 적용을 클릭한다.

열에서 이름, 데이터 유형, 소스 유형, 크기 그리고 PK 또는 FK 여부를 선택하여

적용 버튼을 선택하여 열을 추가한다.

그리고 확인을 선택한다.

표 4-2에 나온 것 그대로 표가 나오는 것을 볼 수 있다.

이렇게 테이블을 두개 만들었다.

그리고 열쇠키와 초록색 선을 선택을 하고

PK 테이블을 선택하고

FK 테이블을 선택하고

판별자열에 userName을 선택을 하고

연관된 열에서 확인을 하고

확인을 선택을 하면

다음과 같이 표 간의 PK 관계가 형성된 것을 볼 수 있다.

표를 우클릭하여 DDL 미리보기를 선택을 하면

다음과 같이 DDL문을 미리 볼 수 있다.

DDL 미리보기 창을 닫기를 선택을 한다.

그리고 DDL 생성을 클릭을 하면

생성을 선택을 하면

그리고 전제를 선택하고

확인을 선택을 하면

생성했던 표들 그 화면 전체를

DDL문으로 만들 수 있다.

저장을 선택하여 저장한다.

C:\모델링\modelSchema.ddl로 저장한다.

SYSTEM에 접속 후

SYSTEM에서 다른 사용자를 우클릭하고

사용자 생성을 선택한다.

사용자 이름: Model

새 비밀번호: 1234

비밀번호 확인: 1234

기본 테이블 스페이스: USERS

임시 테이블 스페이스: TEMP

부여된 롤 탭에서

기본 롤인

CONNECT, RESOURCE를 선택한다.

할당량 탭에서

할당량: 10

단위: m

을 입력하고

적용을 선택하여

Model 사용자를 생성한다.

다음과 같이 Model 사용자가 만들어졌다.

접속을 해야 할 차례다.

로컬-Model 접속을 새로 생성한다.

저장을 선택하고 접속을 선택하여 접속한다.

파일 > 열기 를 선택한다.

그리고 C:\모델링\modelSchema.ddl 을 선택한다.

열기를 선택한다.

modelSchema.ddl 파일을 열고

우측에서 db 버튼을 클릭하고

적용할 db를 선택한다.

우리는 로컬-Model을 선택할 것이다.

그리고 스크립트 전체를 실행할 때는

스크립트 실행 버튼을 선택한다.

로컬-Model 스키마에 테이블들이 생성된 것을 확인할 수 있다.

이번에는 스키마에 있는 테이블들을 다른 스키마에서 가져오려고 한다.

파일 > Data Modeler > 임포트 > 데이터 딕셔너리를 선택한다.

데이터 딕셔너리를 가져오는 것이다.

우리가 만든 접속을 선택하고 다음

스키마 데이터베이스 SHOP을 선택하고 다음

임포트할 객체를 선택한다.

테이블, 뷰, 내장 프로시저 등

SHOP에 있는 객체들을 선택해서 가져온다.

데이터베이스 이름 그리고 데이터베이스 버전을 확인하고

임포트할 DB 객체를 확인한 다음

완료를 선택하여 디자인을 생성한다.

임포트할 모델과 기존의 모델을 비교한다.

병합을 선택을 하면

두 모델이 합쳐진다.

데이터 모델링을 연습해봤다.

상당히 할 것이 많고 경험도 많이 필요하다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=lbZl33HdgGc&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=8

데이터의 백업 및 복원

실습

명령 프롬프트에서 exp userid=SYSTEM/0000@xe OWNER=Shop FILE=C:\DB백업\Shop01.dmp 명령어를 통해서

사용자의 아이디와 비밀번호와 SID가 SYSTEM/0000@xe

스키마는 Shop

C:\DB백업 디렉토리에

Shop01.dmp 파일이라는

Shop 스키마에 대한 백업 파일을 생성하여 백업한다.

그리고 Oracle Developer에서 확인한다.

데이터가 잘 있다.

그리고 PRODUCTTBL이라는 테이블을 삭제하고

다시 조회를 했다.

이를 사고라고 치겠다.

이번에는 방금 백업한 파일을 복원해보겠다.

imp userid=SYSTEM/0000@xe FROMUSER=Shop TOUSER=Shop FILE=C:\DB백업\Shop01.dmp TABLES=(productTBL)

명령어를 통해서 해당 사용자의 스키마에서 백업 파일에서

Shop 사용로부터 Shop 사용자로

PRODUCTTBL 테이블을 백업한다.

복원이 잘 된 것을 볼 수 있다.

오라클 데이터 백업 및 복원은 알아야할 것이 굉장히 많다.

별도의 책으로 공부하시길 바랍니다.

비주얼 스튜디오를 설치한다.

.net 버전을 설치한다.

그리고

Oracle Developer Tool을 설치한다.

C:\app\ant19\product\11.2.0\client_1\Network\Admin 경로에

tnsnames.ora 파일을 다음과 같이 설정한다.

ASP.NET 빈 웹 사이트를 만든다.

Web Form 파일을 만든다.

SqlDataSource를 만든다.

데이터 소스 구성을 클릭한다.

새 연결 > Oracle 데이터 베이스를 선택한다.

서버 이름, 사용자 이름, 암호를 입력하고 연결 테스트를 한다.

연결 테스트에 성공했다.

XE_SHOP 상태에서 다음

ConnectionString인데 그냥 두세요. 다음

사용자 지정을 선택하고 다음

적절한 SQL문을 입력하고 다음

쿼리 테스트를 클릭해서 미리보기로 쿼리를 조회할 수 있다.

오라클에 있었던 memberTBL의 데이터를 가져오는 것이다.

그리고 마침을 클릭한다.

연결을 완성하였다.

이 연결을 보일 리스트뷰가 필요하다.

ListView를 클릭하고

우리가 만든 SqlDataSource를 선택한다.

그리고 ListView 구성을 클릭한다.

레이아웃 선택 -> 표

스타일 선택 -> 전문가

옵션 -> 페이징 사용

등을 예로 들어서 임의로 선택한다.

그리고 확인을 클릭한다.

그리고 지금까지 한 것을 저장하기 위해서 저장 버튼을 클릭한다. 모드 저장

웹 서비스를 추가로 설치를 해야 하는데

비주얼 스튜디오에서는 테스트 웹 서비스가 깔려있다.

메뉴 > 파일 > 브라우저에서 보기를 클릭한다.

그러면

웹 브라우저에서 웹 서버에 접속을 하고 오라클의 데이터를 가져와서 보여주는 역할을 한다.

DBMS 설치하고

스키마 생성

테이블 생성

데이터 입력

데이터 조회 및 활용

테이블 외의 데이터베이스 개체에 대해 알아보고 (인덱스, 뷰, 저장 프로시저, 트리거)

그리고 exp, imp 등의 명령어를 사용해서 데이터베이스 백업 및 복원 작업을 했고

응용프로그램에서, 웹 브라우저에서 직접 접근할 수 있는

작업까지 해보았습니다.

이 책의 전체를 요약한 내용이다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=ldbslx2F4oY&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=7

테이블 외의 데이터베이스 개체의 활용

인덱스 생성 및 조회

뷰

저장 프로시저

트리거를 실행하면 INSERT/UPDATE/DELETE가 자동으로 실행

탈퇴한 회원을 다른 테이블에 모아야 한다면

트리거를 통해서 이를 수행할 수 있다.

삭제한 데이터가 트리거로 저장하기로 한 테이블에서 조회가 된다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=2sL0g_jwMzc&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=6

테이블 생성

테이블 생성 실습

스키마, 이름 그리고 열들을 만들었다.

memberid, membername, memberaddress는 대문자로 자동으로 입력된다.

PK열을 클릭하면 PK 등록이 가능하다.

또한 데이터 타입을 지정할 때 한글이라면 데이터 타입을 nchar 또는 nvarchar2로 지정한다.

동일하게 제품 테이블도 만들었다.

만들어진 테이블을 확인해봤다.

SQL문을 보면 생각보다 복잡하다.

8장에서 다룰 예정이다.

데이터를 보면 아무것도 입력되지 않았다.

문서 초록색 플러스 버튼을 클릭을 해서 다음과 같이 데이터를 직접 입력할 수 있다.

그리고 데이터 베이스 초록색 체크 버튼을 클릭을 하면

입력된 데이터들이 데이터베이스에 확정이 되는 것이다. 이를 커밋이라고 한다.

메시지 로그를 보면 INSERT문을 볼 수 있다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=XpWrNgY5yew&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=5

요구사항 분석과 시스템 설계 그리고 모델링

데이터베이스 구축을 하기 위해서는 요구사항 분석, 시스템 설계 그리고 모델링이 필요하다.

결과적으로 이렇게 그림이 나온다.

이 그림을 가지고 데이터베이스가 이렇게 돌아가는구나를 파악한다.

스키마는 데이터베이스의 묶음으로 본다.

mysql이나 sqlserver는 스키마 = 데이터베이스이다.

오라클은 스키마들 자체를 관리하는 것을 데이터베이스라고 부른다.

단위가 살짝 다르다.

한 명의 사용자는 하나의 스키마를 갖고 있다.

이 그림을 보면 사용자가 3명이라고 보면 된다.

쇼핑몰 사용자, HR 사용자 그리고 SYSTEM 사용자이다.

데이터를 표 형태로 모아놓은 것이 테이블이다.

단순한 표 형태로 생각하면 된다.

오라클 스키마 구축 절차

데이터가 지워지거나 깨지면 큰일나죠

그래서 데이터 백업 및 관리를 해야 한다.

일반 사용자가 회원가입을 할 때

회원은 가입할 때 행이 하나가 쏙 들어간다.

DBMS 전문가처럼 INSERT문을 날리고 해서 할 수는 없다.

그래서 응용 프로그램을 구축을 하는 것이다.

실습

사용자 이름, 새 비밀번호, 비밀번호 확인, 기본 테이블 스페이스, 임시 테이블 스페이스를 정한다.

기본 테이블 스페이스와 임시 테이블 스페이는 물리 저장 공간 (HDD)를 이야기한다.

그리고 만들어진 사용자에게 CONNECT, RESOURCE 권한을 부여한다.

자기 자신 스키마에 접속할 수 있는 권한

스키마의 테이블에 데이터를 사용할 수 있는 권한

이렇게 두 개의 권한을 주로 부여한다.

USERS 테이블 스페이스에 할당량 10 단위는 M

이렇게 10M를 저장할 수 있도록 공간을 할당한다.

SHOP 계정에 10M를 USERS 테이블 스페이스에 할당한다.

그리고 SQL을 보면

이렇게 SQL이 만들어진 것을 볼 수 있다.

실제 이 SQL을 실행한 것과 같다.

나중에는 직접 이렇게도 SQL문 작성이 가능해야 한다.

접속을 만들어준다.

로컬-SHOP

사용자 이름: SHOP

비밀번호: 1234

그리고 호스트 이름에는 현재 자기 자신의 컴퓨터 이름이 적혀있지만

다른 컴퓨터에 접속을 하려면 ip를 입력하면 된다.

포트번호도 동일하게 입력해주면 된다.

5장에서 다룰 예정이다.

감사합니다.