[DB]오라클(Oracle)과 SQL

데이터베이스(DB)는 방대한 정보들을 모아놓는 저장소이며 그런 DB를 관리하는 게 데이터베이스 관리 시스템(DBMS;Database Management System)다. 여러 가지 DBMS 중 관계형 DBMS가 제일 보편적이고 많이 쓰이는데, 오라클(Oracle)과 MySQL, MS-SQL 등이 포함되어 있다.

 

그럼 먼저 버추얼 박스에 설치해 둔 가상 PC부터 켠다. 여기서 OS User가 root 유저, oracle 유저 두 가지가 있는데, root 유저는 Microsoft의 administer처럼 관리자 권한을 소유한 유저라 서버 관리자만 사용한다. oracle 유저는 그 권한 중 아주 기본적인 부분만 허용받은 일반 유저다. 그래도 오라클을 다룰 수 있으며, 나도 oracle 유저로 들어갔다.

또한 OS 유저 말고 DB 유저가 따로 분류되는데, sys 유저와 system 유저, hr 유저가 있다. sys 유저가 최상위로, sysdba(dba + DB생성 + DB시작과 종료) 권한을 가지고 있다. system 유저는 dba 권한만 가지고 있고, hr 유저는 임의로 만들어지며 dba 권한까지만 부여받을 수 있다.

 

터미널 열기

리눅스 바탕화면에 우클릭 후 터미널 열기(Open Terminal)를 하면 아래와 같은 창이 뜬다. 리눅스는 GUI가 아니라 CUI(TUI) 환경이라, 동작이나 실행을 이런 터미널 창에서 조작한다. GUI 환경보다 더 빠른 것도 있지만 좀 더 안정적이기도 해서 서버 컴퓨터에 리눅스를 많이 사용한다고 한다.

 

이제 DB를 구동시키기 위해 OS 프로세스인 리스너 프로세스를 활성화해야 한다. 터미널 창에 lsnrctl start 를 치면 뭐가 길게 나오는데, 마지막에 The command completed successfully 가 뜨면 완료된 것이다.

그리고 SQL을 시작시켜야 한다. SQL(Structured Query Language)은 관계형 DBMS에서 서버와 통신하기 위해 사용되는 언어로, SQL을 모르면 오라클이나 MySQL을 다룰 수 없다. 이 SQL을 구동시키는 툴은 두 가지가 있는데, SQL Plus와 SQL Developer가 있다. SQL Developer는 GUI에서 사용하는 프로그램이고 나는 SQL Plus를 켠다. 이 프로그램도 원래는 설치를 따로 해야 한다.

터미널 창에 sqlplus /nolog 입력(띄어쓰기 필수)

위의 화면처럼 SQL 입력문이 활성화되고 나면 DB에 접속할 유저를 선택한다. 나는 sys 유저로 로그인을 할 것이기 때문에 터미널 창에 conn sys/패스워드 as sysdba 를 치면 되지만, system이나 hr 유저로 로그인할 때는 conn system(또는 hr)/패스워드 만 치면 된다.

처음 sys로 접속하면 Connected to an idle instance. 라고 나오는데, DB가 꺼져있다는 뜻이다. sys 유저는 DB를 시작할 수 있으므로 startup 을 쳐서 DB를 켜 준다. 마지막에 Database opened 가 뜨면 완료된 것이다. 그럼 DB를 켰으니 hr 유저로 다시 로그인을 한다(conn hr/패스워드). 현재 유저를 확인하고 싶다면 터미널 창에 show user를 치면 된다.

혹시 로그인을 했을 때 위의 화면처럼 ERROR(ORA-28002)가 뜨는 것은 hr 유저 계정의 사용 기간이 며칠 남았는지 알려주는 것이다. 만료 7일 전부터 뜨기 때문에, 날짜가 끝나기 전에 패스워드를 바꿔주면 된다. 터미널 창(SQL)에 alter user 유저명 을 치고 엔터, identified by 새로운패스워드; 를 치면 바뀐다.

 

Select 구문

SQL에서 제일 기본이 되는 것이 select 구문이다. DB에 테이블 형식으로 저장된 데이터들을 select 구문으로 찾아낼 수 있고, select 절과 from 절로 이루어져 있다. 테이블 구조를 조회하려면 desc 테이블명 을 치면 된다.

그리고 유저가 소유하고 있는 테이블 이름들을 보고 싶다면 아래처럼 select table_name 엔터, from user_tables; 라고 치면 된다.

 

select * 엔터, from 테이블명; 을 치면 그 테이블 데이터가 모두 나오는데, 정리가 안돼서 제대로 알아보기 어려울 수 있다. 그럴 때 테이블 열 사이즈를 조절하기 위해 쓰는 것이 col 열제목 format 조절양 형식인 아래의 구문이다.

그 후에 다시 select * 엔터, from 테이블명; 을 쳐보면 아래처럼 알아보기 쉬운 형태로 바뀌어 나온다. *은 테이블의 모든 데이터를 나타내는 것이고, * 자리에 열 제목을 치면 그 열의 데이터가 나온다.

 

더불어 터미널 창을 깔끔하게 정리하고 싶을 때는 !clear 라고 치면 된다. 단순히 화면을 내리는 것이기 때문에 위의 작업들이 지워지지는 않는다.

 

그리고 만약 이미 작성한 구문에 오타가 났을 경우, 작성했던 마지막 구문에 한해서 수정이 가능하다. SQL 구문으로 ed를 치고 오타 난 구문으로 들어가 i를 누르면 수정할 수 있다. 수정이 끝나고 esc 키로 완료한 뒤 :wq를 치면 구문 밖으로 나와지는데, 여기서 / 를 치면 고친 구문으로 재실행할 수 있다.

위의 모든 과정에서 끝에 ;(세미콜론)이 찍히는 것은 SQL 구문이다. conn(로그인)이나 startup, show user, col(열 사이즈 조절), desc 등 세미콜론이 찍히지 않는 것은 SQL Plus의 환경설정 구문이다.

참고로 마지막 구문이 뭐였는지 확인하고 싶을 땐 SQL구문으로 l(소문자 L)을 치면 된다.

 

다음으로 select 구문에 쓰이는 것들에는 null 값, Column Alias, 연결 연산자, DISTINCT 키워드, 리터럴이 있다.

먼저 null 값은 사용할 수 없거나, 알려지지 않은, 할당받지 못한 값, 또는 아직 정의되지 않은 값 등을 뜻한다. 0이나 공백과는 다른 값이며 모든 데이터 타입에 사용이 가능하다. null 값이 포함된 연산을 수행했을 때는 무조건 null 값이 결과로 도출된다. 공백과 null 값은 똑같이 표기되는데, 만약 데이터가 number 타입이면 null이지만 문자 타입이면 구분이 되지 않는다.

두 번째로 Column Alias는 열 제목을 원하는 대로 바꿔서 표기해주는 방법이다. 예를 들어 어떤 연산을 했을 경우, 열 제목에 연산이 그대로 적히기 때문에 Column Alias로 보기 좋게 바꾼다.

형식은 select 열제목 as alias 엔터, from 테이블명; 으로 쓴다. as는 생략 가능하고 alias에 새로운 제목을 적으면 된다. alias는 기본적으로 대문자이며, " " 안에 작성하면 소문자, 공백, 특수기호 모두 가능하다.

 

그리고 연결 연산자는 Java에서 or을 뜻하는 || 기호를 사용하는데, 두 열을 하나의 열로 합쳐주는 역할을 한다. 공백이나 구분 기호가 따로 안 들어가기 때문에, 열제목 || ', ' || 열제목 과 같이 ' ' 안에 구분 기호를 써 준다.

 

마지막으로 DISTINCT는 중복 값을 제거해 주는 키워드이며, 리터럴은 쿼리 구문에서 명령어나 열 제목이 아닌 일반 문자, 숫자, 날짜 데이터를 말한다. 문자와 날짜 리터럴은 꼭 ' ' 안에 적어야 한다.