데이터베이스 개론 & SQL - 3

MySQL 기반으로 강의가 진행되고 있다. 여러 엔진 중 InnoDB를 기준으로 하고 있다고 소개했다.

InnoDB란?

DB 엔진 중 하나로, DBMS가 데이터베이스에 대해 데이터를 삽입, 추출, 업데이트, 삭제하는 데 사용되는 기본 소프트웨어 컴포넌트이다. MySQL 5.5 버전 이후 사용되는 엔진이며 바이너리에 내장되어 있다. 해당 엔진은 트랜잭션을 지원하므로 트랜잭션 세이프 스토리지 엔진이라 한다.

대용량 데이터 처리나 다수의 사용자의 동시 접속을 가능하게 하며 파일과 파티션으로 구성되어 있는 데이터 테이블과 인덱스 테이블 공간에 저장하므로 성능이 우수하다고 한다. 그밖에 데이터 무결성에 대해 중점을 두어 보장하고 논리적인 장애 복구를 수행할 수 있다는 장점이 있으나, 데드락 발생 가능성이 있으며 대용량 처리 시 많은 자원을 순간적으로 소모하며 파일 백업이 아닌 복구 방법이 별도로 존재하는 단점도 존재한다.

 

 

데이터 추가 

1. 컬럼 전체의 값 추가하기

Insert into 테이블명
	values (컬럼에 해당하는 값들);

-- ex
Insert into employee
	values (1, 'Messi', '1987-02-01', 'M', 'DEV_BACK', 100000000, null);

Insert into ~ values ~ 명령어를 사용하여 테이블의 튜플을 추가해줄 수 있다. 다만 주의할 점은 values 안에 컬럼 순서에 맞게끔 실제 데이터를 넣어줘야 한다는 점이다.

여기서 null 은 dept_id 로 Foreign key 이므로 DEPARTMENT 테이블에 값이 들어가기 전까진 넣어줄 수 없기에 임시 처리한 것이라 생각하면 된다.

 

데이터를 추가하면서 생기는 에러를 살펴보자.

Insert into employee
	values (1, 'Jane', '1996-05-05', 'F', 'DSGN', 90000000, null);
-- PK에 해당하는 id에 이미 1번을 앞서 넣어줬으므로 에러가 발생한다. 2로 수정해줘야한다.


Insert into employee
	values (2, 'Jane', '1996-05-05', 'F', 'DSGN', 40000000, null);
-- 이 경우는 일전에 만들어준 테이블의 check 조건의 salary >= 50000000 를 위반하므로 에러가 발생한다.


Insert into employee
	values (2, 'Jane', '1996-05-05', 'F', 'DSGN', 90000000, 111);
-- dept_id 에 임의의 값을 넣어줘서 에러가 발생한다. DEPARTMENT 테이블 안에 있는 값을 넣어줘야 한다.

만약 check 에 관련된 에러를 기억하지 못한다면(대부분 그럴 것이다) show create table 테이블명 명령으로 확인이 가능하다. 이 명령어는 MySQL에서 사용 가능하고, PostgreSQL 에선 pg_dump DB명 -U 접속계정 --schema-only -t 테이블명 명령를 사용해줘야 한다고 한다.

이미지를 확인해보면 employee_chk_2 가 위반됐음을 알 수 있고 에러창에서도 해당 값으로 확인이 가능하다. 그래서 저번 강의 때 check 제약의 경우따로 이름을 짓는 것이 좋다고 한 것이다.

 

2. 특정 컬럼 값만 추가하기

-- 1
Insert into 테이블명(값을 넣어줄 컬럼들)
	values (위 컬럼 순서에 맞는 값들);
    
-- 2
Insert into 테이블명
	values (컬럼명, 값), (컬럼명, 값), ...;

-- ex
Insert into employee( name, birth_date, sex, position, id )
	values ('Jenny', '2000-10-12', 'F', 'DEV_BACK', 3);

 

 

데이터 수정

임의의 값을 테이블마다 넣어서 처리해준다. 위에 기재했듯 employee 테이블을 작성할 땐 dept_id 를 채울 수 없었으므로 DEPARTMENT 테이블의 내용을 채운 뒤 해당 null 값들을 변경해줄 필요가 있다.

update 테이블명 set 컬럼명 = 값 where 조건;

-- ex id가 1번인 사원의 부서번호를 1003으로 변경
update employee set dept_id = 1003 where id = 1;

 

수정에서 주의할 점은 where 을 사용하여 조건을 기재해줘야 한다는 점이다. where 이 없으면 전체 컬럼의 값이 변경된다.

그렇다면 예시로 만들었던 프로젝트상의 참여한 사람들의 연봉을 올려주는 쿼리문을 만들고자 한다면, update 테이블명에서 참조해줄 테이블명을 연달아 써준다.

update employee e, works_on wo 
	set e.salary = e.salary * 2 
    where e.id = wo.empl_id and wo.proj_id = 2003;

e.id = wo.empl_id 부분이 두 테이블을 연결지어주기 때문에 특히나 중요하다.

 

데이터 삭제

delete from 테이블명 where 조건;

-- ex
delete from employee where id = 8;

위의 예시를 보면 employee 테이블의 id가 8인 사원을 삭제하는 것이다. 그럼 employee 테이블에서 삭제되는 건 물론 참조관계로 묶여있던 works_on 테이블의 id 8도 사라져있음을 알 수 있다.

show create table works_on 으로 테이블 정의를 확인해보면, empl_id 속성은 employee 테이블의 id와 foreign key로 연결되어 있어, employee 테이블에서 삭제되거나 수정되면 그 내용을 따르는 cascade로 제약되어 있다. 이 때문에 해당 테이블의 튜플도 사라진 것이다.

 

delete from 테이블명;

위의 경우는 테이블 내의 모든 튜플을 삭제하는 명령어가 된다. 즉 스키마 테이블 자체는 남아있어서 drop table 과는 다르다.

 

데이터 조회

select 컬럼명, ... from 테이블명 where 조건;

-- ex
select `name`, `position`  from employee where id = 9;

얼핏 보면 조회가 쉽다고 생각하겠지만 나는 아직도 조회가 너무 어렵다. 실무에서도 제일 많이 쓰일 것이고... 요구사항에 맞춰 조회를 해줘야 하므로 이보다 더 복잡한 방법도 배울 것이다.

강의에서 나온 프로젝트 2002 의 임직원 정보를 조회하는 쿼리문을 작성해보자.

Project 테이블에서 id가 2002 를 찾고, leader_id 는 employee 테이블의 id를 참조하므로 employee 테이블에 접근이 가능해진다. 참조되는 id 의 관계를 보여주려면 e.id = p.leader_id 로 써줘야 한다.

select e.id, e.name, e.`position` from employee e, project p where p.id = 2002 and e.id = p.leader_id;

= 을 사용하여 서로 다른 테이블 사이의 참조관계를 활용하는 것을 Join condition 이라고 부른다. Join 은 매우 중요하므로 나중에 자세히 배울 것이다. 이런 조건들을 어떻게 사용해주냐에 따라 DB 성능이 좌우되기 때문에 많은 연습이 필요하다. 특히나 조회의 경우 삭제, 추가, 수정에 비해 많이 요청되므로 조회의 성능이 곧 그 DB의 성능이라고 불릴 정도라고 한다.

 

AS

테이블이나 attribute에 별칭을 지어줄 때 사용하는 명령어, 생략도 가능하다.

위의 조회문은 테이블 별칭을 지을 때 as 가 생략된 것이다. 생략된 as 를 다시 써주면 아래와 같다.

select e.id, e.name, e.`position` from employee as e, project as p where p.id = 2002 and e.id = p.leader_id;

 

아래의 경우는 컬럼명에 as 를 사용한 것이다.

select e.id as leader_id, e.name as leader_name, e.`position` as leader_position from employee e, project p where p.id = 2002 and e.id = p.leader_id;

 

Distinct

조회할 때 중복되는 튜플을 제거하고 조회하고자 할 때 사용

select distinct 컬럼명, ...
from 테이블명
where 조건;

-- ex
select distinct p.id, p.name
from project p, employee e, works_on wo 
where e.dept_id  = 1004 and e.id = wo.empl_id and wo.proj_id = p.id;

 

Like

select 컬럼명 from 테이블명
where 컬럼명 like '조건';

-- ex
select name from employee where name like 'S%' or name like '%s';

특정 컬럼의 값에서 조건에 해당하는 값을 조회할 때 사용

컬럼명 Like 'S%';

위의 경우는 컬럼명에 해당하는 값을 읽어와 Like 다음의 S%, S로 시작하는 문자열을 읽어와서 조회할 수 있게 된다.

 

% 기호는 문자열 길이에 무관하게 무슨 문자든 들어갈 수 있음을 의미한다. 그럼 Like '%am%' 이라면? 값의 문자열에 am이 들어가면 해당되는 것이다.

문자 1개로 한정 지어주려면 _ 기호를 사용해준다. 즉 Like 'J___' 이라면 J로 시작하는 4글자 문자가 리턴된다.

 

위와 같은 문자(%, _ 등)를 정해진 기능이 아닌 그저 기호로써 사용하고 싶다면 \ (백슬래시)를 사용하여 함께 사용해주면 된다. -> \%, \_

 

*

튜플의 모든 attribute 정보를 조회할 때 사용하는 예약어이다.

select * from 테이블명;

만약 위의 예제에서 테이블에 여러 테이블이 나열된다면 해당 테이블의 모든 속성이 이어진 것마냥 보일 것이다.

 

DB 조회 시 주의할 것

Select 로 조회할 때 조건들을 포함하여 조회한다면, 이 조건들과 관련된 attribute에 index가 걸려 있어야 한다.

-> 인덱스가 없다면 데이터가 많아질수록 조회속도가 느려진다.