JDBC를 이용한 DB작업_기초 SQL 문법 [1/3]

[ DBMS ]

• DataBase Management System
• DB를 사용하기 위한 시스템
• Oracle DB, MSSQL, MySQL 등이 있음

Oracle DB를 기반으로 정리했습니다. MSSQL이나 MySQL 등도 거의 오라클DB에서 시작됐으므로 쿼리문이나 구조는 거의 비슷하다고 보면 됩니다. 약간씩 다르긴 하지만 오라클DB를 이해하면 나중에 익히는데 크게 문제는 없을 것 같습니다. 사실 실무에서는 엄청 중요한 시스템이 아니면 웬만큼 오라클DB를 사용하지는 않는 편입니다. 일단 가격이나 유지보수요율이 다른 DB들에 비해 워낙 비쌉니다.. 안정성도 원래 OracleDB가 좋았다고 하는데 요즘은 MSSQL이 많이 좋아졌고 다른 무료 DB들도 쓸만하다고 합니다. (심지어 SAP에서 사용하던 오라클 DB들도 HANA DB로 바뀌는 추세라 오라클DB의 입지가 많이 줄어드는 느낌입니다.)

 

 

---

 

 

DB의 기본 구조는 테이블(Table)입니다. 엑셀 파일 하나 같이 네모네모로 생긴 표에 데이터가 정형화되어 저장돼 있습니다. 테이블의 데이터는 데이터의 성격별로 최대한 잘게 쪼개서 관리합니다. 예를 들어 ID와 PW를 저장한 테이블이 있고 그 계정에 대한 주소, 전화번호, 이메일 등의 부가적인 정보가 있다면 두 개의 테이블을 생성해 각각 저장합니다. 한 테이블에 너무 많은 정보가 들어 있으면 쿼리의 속도도 느려지고 데이터를 다루는 유연성이 떨어지기 때문입니다. 

 

쪼개진 테이블들의 내용은 나중에 WAS에서 쉽게 가져갈 수 있도록 하기 위해 뷰(View)라는 것을 따로 작성해두는데 뷰는 여러 테이블의 내용을 입맛대로 엮어서(join) 하나의 가상 조회용 테이블을 만드는 방식입니다. 뷰는 호출될 때마다 새로 만들어지기 때문에 테이블의 내용이 수정되면 뷰의 내용도 같이 바뀝니다. 여러 테이블에서 값을 엮으려면 유니크한 값이 필요한데 이를 'Primary Key'라고 합니다. 이 키값을 기반으로 두 테이블의 값을 조합할 수 있습니다. 뷰를 사용해서 데이터를 유연하게 다룰 수 있고 또 조회 시 프로그램에서 테이블에 직접 접속하지 않도록 하므로써 보안이나 안정성을 높일 수 있습니다.

 

따라서 소스코드에서 직접 DB에 쿼리를 날려 데이터를 조합해 가져올 수도 있지만, 데이터를 뽑아오는 조건이 복잡하고 긴 경우 이 로직을 미리 DB의 뷰(view)에 짜두고 해당 뷰만 호출해서 가져가게 할 수 있다는 것입니다. 실무에서는 워낙 많은 양의 데이터와 테이블들이 있고, 또 서로 다른 DB에 저장돼있는 정보를 엮어서 한번에 봐야할 경우도 많습니다. 이런 경우 뷰를 사용해서 로직을 미리 짜둔 뒤 프로그램에서는 해당 뷰가 제공하는 내용을 가져가기만 합니다.

 

그밖에도 데이터를 다루기 위한 수많은 기능들이 존재하기 때문에 DBMS를 다루는 것도 다른 개발 언어를 다루는 것과 같은 급의 난이도가 존재합니다. DB는 따로 공부를 해나가야하니 아주 기본적인 부분만 정리하도록 하겠습니다.

 

 

 

 

---

 

 

[ 테이블(Table) 생성 ]

 

테이블은 아래와 같은 코드 형태로 생성할 수 있습니다. SQL Developer와 같은 GUI 툴을 사용하면 좀 더 간단하게 만들 수도 있지만 쿼리를 사용하는게 더 좋을 것 같습니다. 

 

'varchar(20)'은 최대 20byte까지 저장할 수 있는 가변형 데이터 타입 입니다. 만약 1byte만 저장하면 1byte 공간만 차지합니다. 반대로 'char(20)' 타입이라고 하면 고정된 저장 공간이라 1byte를 쓰면 나머지는 모두 공백으로 채워집니다. 'date'는 날짜형 타입입니다.

 

'default'는 만약 데이터가 입력되지 않았을 때 디폴트로 채워둘 값을 의미합니다. 'sysdate' 값을 디폴트로 지정해 ID와 PW가 저장될 때 시스템이 가지고 있는 시간 정보를 넣어준다는 의미입니다. 'not null'은 비어있는 값은 허용하지 않겠다는 의미입니다. 'primary key'는 이 테이블에서 중복되지 않는 유일한 값이어야 한다는 뜻이고 비어있는 것(NULL)도 허용하지 않습니다.

 

이외에도 많은 제약 조건들과 데이터 타입이 있기 때문에 필요할 때마다 찾아서 익혀두는 것이 좋습니다.

 

-- 사용자 계정을 저장할 테이블
create table ORG_USER (
    USER_ID varchar(20) primary key,
    USER_PW varchar(20) not null,
    JOIN_DATE date default sysdate
);

-- 사용자 정보를 저장할 테이블
create table ORG_USER_INFO (
    USER_ID varchar(20) primary key,
    PHONE varchar(15),
    EMAIL varchar(30),   
    ADDRESS varchar(50)
);

 

 

 

※ insufficient privileges 메세지가 뜨며 테이블 및 뷰 생성에 실패할 경우

 

접속한 계정에 권한이 없을 때 발생하는 메세지입니다. SQLPLUS를 통하거나 Developer에서 처음 설치할 때 패스워드를 설정했던 system 계정으로 접속한 뒤 'GRANT'  명령을 통해 계정에 권한을 부여해주면 됩니다.

 

• GRANT CRETAE  ANY TABLE TO '계정명'
• GRANT CREATE ANY VIEW TO '계정명'

 

권한을 주는 조건은 매우 세분화되어 있기 때문에 검색해서 적절하게 권한을 부여하면 됩니다. 

 

 

 

---

 

 

[ 테이블(Table) 내 데이터 검색 ]

 

SQL에서 검색 조건문은 'where'절로 작성합니다. 정보를 가져오는 'select'와 적절히 섞어서 사용하면 됩니다. 

 

-- 테이블 내 모든 데이터 검색
select * from ORG_USER;

-- 테이블 내 특정 조건으로 전체 행 검색
select * from ORG_USER where user_id = 'codevang2';

-- 테이블 내 특정 컬럼만 검색
select USER_ID from ORG_USER;

-- 테이블 내 특정 컬럼을 특정 조건으로 검색
select USER_ID from ORG_USER where user_pw = '12345';

-- 특정 조건에서 포함되는 문자열이 있는지 찾고 싶을 때
select * from ORG_USER where user_id like '%2'; -- 2로 끝나는 값
select * from ORG_USER where user_id like 'c%'; -- c로 시작하는 값
select * from ORG_USER where user_id like '%devan%'; -- devan이 포함되는 값

 

 

---

 

 

[ 테이블(Table) 내 데이터 추가/수정/삭제 ]

 

DB를 다루는 실무자들이 가장 싫어하는 트랜젝션 작업들입니다. 까딱 잘못하면 데이터가 왕창 꼬이거나 손실될 수 있기 때문에 실제 운영DB에서 작업할 때는 항상 테이블 백업부터 해놔야 합니다. 어플리케이션 개발자가 DB에서 실제로 트랜젝션 작업을 다룰 일은 별로 없겠지만 코드에서 쿼리를 보내 데이터를 조작하는 일은 많기 때문에 기본적인 구조와 필요한 쿼리문은 작성할 수 있어야 합니다.

 

오라클 DB의 경우 트랜젝션 작업 후에는 항상 쿼리를 실행한 뒤 커밋(commit)을 해줘야 합니다. 커밋은 실행한 쿼리가 실제 DB에 반영되도록 해줍니다. 커밋을 하기 전이라면 롤백(rollback)을 통해 쿼리 실행(트랜젝션)을 무효화할 수 있습니다. MSSQL 같은 경우는 실행 즉시 자동 커밋되기  때문에 롤백을 고려한다면 수동으로 트랙잭션을 걸어주고 작업해야 합니다. 

 

* 트랜젝션 (Transaction)

• 사전적 의미 : 모든 과정이 수행돼야 작업이 정상적으로 완료될 수 있는 과정의 모음집
• DB에서의 트랜젝션 : 실제 커밋(DB에 반영) 되기 전까지의 모든 DB 데이터 수정 작업의 기록

* 커밋(Commit)

• 트랜잭션 처리 과정에서 변경된 데이터를 실제 DB에 영구 저장하는 행위

* 롤백(Rollback)

• 커밋되기 전의 트랜젝션이라면 롤백 명령을 통해 무효화 시킬 수 있음
• 커밋된 이전의 트랙젝션 작업은 롤백 불가
• 쉽게 말해 마지막 커밋 시점까지 되돌리기(Ctrl + z)를 수행하는 것과 같음

 

update와 delete의 경우 조건(where)을 주지 않으면 테이블 내 컬럼의 데이터를 모두 바꾸거나 지워버리기 때문에 주의해야 합니다. 실제로 실행 블록 지정을 잘못 하는 등 한번의 실수로 데이터를 날려먹는 경우를 많이 봤습니다.. DB는 항상 행(가로줄)이 한묶음입니다. 100개 컬럼 중 1개 컬럼에만 갑을 넣어도 나머지 99개 컬럼에 디폴트값 또는 NULL값이 들어가서 생성됩니다.

 

-- 데이터 삽입 (insert)
insert into ORG_USER (USER_ID, USER_PW) values ('codevang1', '12345');
insert into ORG_USER (USER_ID, USER_PW) values ('codevang2', '12345');
commit;

-- ORG_USER의 모든 값 조회
select * from ORG_USER

 

-- 데이터 수정 (update)
-- where 조건 없으면 user_id의 모든 값을 바꿔버림
update ORG_USER set USER_ID = 'codevang12345' where user_id = 'codevang1';
commit;
select * from ORG_USER;

 

-- 데이터 삭제  (delete)
-- where 조건 없으면 모든 데이터가 삭제됨
delete ORG_USER where USER_ID = 'codevang12345';
commit;
select * from ORG_USER;

 

 

 

---

 

[ 뷰(View) 작성 ]

 

뷰는 쿼리문을 통해 여러 테이블의 데이터를 이리 저리 조합하고 편집해서 필요한 조건의 가상 테이블을 완성시켜 줍니다. 가장 기본 개념인 JOIN만 써서 위에서 만든 두 테이블을 엮어 두겠습니다. 

 

CREATE VIEW VIEW_USER_INFO AS

-- 기준테이블(a)와 조인시킬 테이블(b)에서 가져올 컬럼 지정
select a.user_id, a.user_pw, b.phone, b.email, b.address from org_user a
    left outer join org_user_info b  -- 조인시킬 테이블
        on a.user_id = b.user_id;     -- 조인 조건
  
    -- 조인된 완성본에서 다시 조회 조건을 부여할 때 조인 후 조건문 사용
    -- where b.phone is not null;       

 

 

---

 

 

이클립스에서 DB와 연동해서 데이터를 입출력하는 과정은 다음 글에서 정리하겠습니다.