- 이름 : 길병찬
- 학번 : 20171580
- 이메일 : [email protected]
select *
from employee
where Fname = 'John' and Minit = 'B' and Lname = 'Smith';또는 아래와 같은 방법도 가능하다.
select *
from employee
where (Fname, Minit, Lname) = ('John', 'B', 'Smith');
조금 응용하면,
select *
from employee
where (Fname = 'John' and Minit = 'B' and Lname = 'Smith')
or (Fname = 'Joyce' and Minit = 'A' and Lname = 'English');대신에
select *
from employee
where (Fname, Minit, Lname) in (('John', 'B', 'Smith'),
('Joyce', 'A', 'English'));을 써도 같다.
select *
from employee
where Dno = 5;
select
emp.Lname,
emp.Fname,
emp.Address
from employee emp
INNER JOIN department d on emp.Dno = d.Dnumber
where d.Dname = 'Research';INNER JOIN 대신 JOIN 만을 써도 같은 효과가 난다. 실무에서도 간편하기 때문에 많이 생략한다.
select
p.Pnumber,
p.Dnum,
e.Lname,
e.Address,
e.Bdate
from project p
INNER JOIN department d on p.Dnum = d.Dnumber
INNER JOIN employee e on d.Mgr_ssn = e.Ssn
where p.Plocation = 'Stafford';
select
emp.Fname '사원 이름',
emp.Lname '사원 성',
super.Fname '상사 이름',
super.Lname '상사 성'
from employee emp
LEFT JOIN employee super on super.Ssn = emp.Super_ssn;왜 LEFT JOIN 을 사용했을까?
사장은 직속 상사가 없다. 만일 사장의 정보(사장 튜플)를 같이 조회하고 싶지 않다면 INNER JOIN 을 사용해도 된다. 그렇지만, 사장도 포함하고 싶다면 LEFT JOIN 을 써야한다.
- 방법 1 : SELF JOIN 을 사용한다.
select
emp.Fname '사원 이름',
emp.Lname '사원 성'
from employee emp
INNER JOIN employee super on super.Ssn = emp.Super_ssn
where super.Fname = 'Franklin' and super.Lname = 'Wong';- 방법 2: 서브 쿼리를 사용한다.
select
emp.Fname '사원 이름',
emp.Lname '사원 성'
from employee emp
where emp.Super_ssn = (select e.Ssn
from employee e
where e.Fname = 'Franklin' and e.Lname = 'Wong');
select
e.ssn,
d.dname
from employee e
CROSS JOIN department d- CROSS JOIN 을 명시하지 않고 콤마로 같은 효과를 낼 수 있다.
select
e.ssn,
d.dname
from employee e, department d
기본 접근 : 당연한 얘기지만, 성이 Smith 라는 정보는 employee 테이블에 있고, 직원과 관련된 프로젝트 번호는 works_on 테이블에 있으므로 그 둘을 활용한다. 아래와 같이 2가지 서로 다른 방법을 활용할 수 있다.
방법 1 : JOIN 만을 이용한 방법
select w.Pno
from works_on w
JOIN employee e on w.Essn = e.Ssn
where e.Lname = 'Smith';방법 2 : 서브쿼리를 이용한 방법
- ‘Smith’의 pk를 구해본다. 123456789 하나의 값이 조회된다.
select e.Ssn from employee e where Lname = 'Smith'- 123456789 pk의 직원이 일하는 project 의 pk 를 구해본다. 비록 위에서는 123456789 하나의 값만 조회되었지만, 동명이인이 있을 경우, 두개 이상의 값이 조회될 수 있으므로,
IN연산자를 쓰는 것이 좋겠다.
select w.Pno from works_on w where w.Essn IN (123456789);- 결과적으로 위의 두 퀴리를 조합한다.
select w.Pno
from works_on w
where w.Essn in (select e.Ssn
from employee e
where Lname = 'Smith');
select e.Fname, e.Lname from employee e
where e.Address LIKE '%Houston, TX%';
방법 1 : JOIN만을 사용
select e.Salary * 1.1 '10% 향상된 급여'
from employee e
INNER JOIN works_on wo on e.Ssn = wo.Essn
INNER JOIN project p on wo.Pno = p.Pnumber
where p.Pname = 'ProductX';방법 2 : 서브쿼리를 활용 서브쿼리를 사용할 때에는 아래처럼 차근차근 '분할 정복'을 사용하면 좋다.
- 먼저, '‘ProductX’' 프로젝트의 pk 를 구한다. 1 하나의 값이 조회된다.
select Pnumber from project p where p.Pname = 'ProductX'- 1번 project 에서 일하는 직원들의 pk를 구한다. 그러면 결과로는 [123456789, 453453453] 이 조회된다.
select w.Essn from works_on w where w.Pno = 1;- pk 가 [123456789, 453453453] 중 하나인 직원의 급여의 1.1 배를 구한다.
select e.Salary * 1.1 '10% 상승된 급여'
from employee e
where e.Ssn IN (123456789, 453453453);- 최종적으로, 아래와 같이 쿼리를 조합한다.
select e.Salary * 1.1 '10% 상승된 급여'
from employee e
where e.Ssn in (
select w.Essn
from works_on w
where w.Pno = (select Pnumber
from project p
where p.Pname = 'ProductX')
);
11. 모든 부서 이름, 부서에 소속한 사원의 성과 이름, 그리고 각 사원이 진행하는 프로젝트 이름의 리스트를 검색하라. 단, 부서 이름은 내림차순 순서대로, 그리고 각 부서 내에서 사원의 성과 이름은 오름차순대로 구하라.
select
d.Dname,
e.Lname,
e.Fname,
p.Pname
from employee e
INNER JOIN department d on e.Dno = d.Dnumber
INNER JOIN works_on wo on e.Ssn = wo.Essn
INNER JOIN project p on wo.Pno = p.Pnumber
order by d.Dname desc, e.Lname, e.Fname; 따라서팁
ASC 는 생략가능하다
방법1 : JOIN 만으로 구하는 방법
select e.Lname, e.Fname
from employee e
INNER JOIN works_on wo on e.Ssn = wo.Essn
INNER JOIN project p on wo.Pno = p.Pnumber
where e.Dno = 5 and p.Pname = 'ProductX' and wo.Hours >= 10;방법2 : 서브퀴리를 사용하는 방법 (역시 논리적으로는 이렇게도 가능하다는 것을 보여주기 위함)
select
e.Lname,
e.Fname
from employee e
where e.Dno = 5
and e.Ssn in (
select wo.Essn
from works_on wo
INNER JOIN project p on wo.Pno = p.Pnumber
where p.Pname = 'ProductX' and wo.Hours >= 10
);
질문에서는 '배우자가 있는 사원들의 수'를 검색하라고 했다. 먼저 해당 검색을 완료하고, '자식이 있는 사원들의 수' 를 검색할 때에는 어떻게 상황이 달라지는지 다루어 보겠다.
각 사원들은 배우자가 없거나, 배우자를 단 1명만 가진다. 이러한 논리적 수준에서의 전제가 있으므로, 아래의 쿼리에서는 Essn 이 중복없이 검색된다.
select dd.Essn from dependent dd where dd.Relationship = 'Spouse'그러므로 아래와 같이 그룹함수 count 를 적용하는 것 만으로 원하는 결과를 얻을 수 있다
select count(dd.Essn) '배우자가 있는 사원의 수'
from dependent dd
where dd.Relationship = 'Spouse';
그렇다면, 배우자가 아닌, 자식이 있는 사원의 수를 구하려면 어떻게 할까? 일단, 수를 구하기 전에, 그들의 SSN 부터 구해보자.
select dd.Essn
from dependent dd
where dd.Relationship = 'Son' or dd.Relationship = 'Daughter';아들이 여러명이거나, 딸이 여러명이거나, 아들과 딸 모두 있는 사원이 있을 수 있다. 따라서 위의 결과에서 dd.Essn 이 중복될 여지가 있다. 아래와 같이 중복을 막기 위해 distinct 키워드를 사용하는 것이 가능하다.
select distinct dd.Essn
from dependent dd
where dd.Relationship = 'Son' or dd.Relationship = 'Daughter';그렇지만, 이 경우, distinct 때문에 그룹함수를 사용한 결과를 select 할 수 없다. 따라서 아래와 같이 인라인 뷰를 사용한다. 인라인 뷰에 alias(아래 예시에서는 'temp')가 꼭 붙어야 함에 유의한다.
select count(*) '자식이 있는 사원의 수'
from (
select distinct dd.Essn
from dependent dd
where dd.Relationship = 'Son' or dd.Relationship = 'Daughter'
) temp;이 상황에서 유의해햐 할 점은, 하나의 부서는 여러 위치를 가질 수 있다는 점이다. 따라서, 어떤 부서에 할당된 프로젝트의 위치가 해당 부서가 위치한 어느 위치와도 다를 경우만 조회해야 한다. 즉, 일반화하면, 어떤 튜플(비유 : 프로젝트)이 조회되어야 하는지의 여부가 다른 집합(집합의 개별 튜플이 아닌 (부분)집합 전체)(비유 : 부서 위치들)에 의해 결정된다.
그러므로, 아래와 같은 쿼리는 질문이 원하는 내용을 조회하지 않는다.
select
p.Pname,
d.Dname
from project p
inner join department d on p.Dnum = d.Dnumber
inner join dept_locations dl on d.Dnumber = dl.Dnumber
and p.Plocation != dl.Dlocation;이 질문을 풀기 위해서는 조인을 사용해서는 곤란하고, 피상적인 조건들을 제거하면 아주 패턴화되어 있는 경우이다.
예를 들어, 다음과 같은 두개의 테이블 Foo 와 Bar 가 있다고 하자.
Foo 테이블
| foo_column |
|---|
| a |
| b |
| c |
| d |
Bar 테이블
| bar_column |
|---|
| x |
| a |
| c |
| e |
이때, Foo 테이블에서 Bar 테이블에 들어있지 않은(또는 들어있는) 튜플만 구하고 싶다면 어떻게 할까? 즉, 결과로는 ('b', 'd') 가 조회되어야 한다. 차집합 등 집합연산을 쓸 수도 있지만, 쓰지 않는다면 어떻게 할지 고민해보자.
우선 'a'가 Bar에 존재하는지의 여부를 알기 위해 다음 쿼리를 조회해보면 1 이 검색된다.
즉, 결과가 "존재한다".
여기서 1 은 'a'가 Bar에 존재하는지 여부를 알기위해 집어넣은 임의의 값이다. 아무런 값이나 상관없다.
select 1 from Bar b
where b.bar_column = 'a'또, 'b'가 Bar에 존재하는지의 여부를 알기 위해 다음을 조회해보면 아무것도 검색되지 않는다.
즉, 결과가 "존재하지 않는다".
select 1 from Bar b
where b.bar_column = 'b'그렇다면, 프로그래밍적으로 생각해보자. 앞서 상수였던 'a'나 'b' 를 변수화하고, Foo의 모든 튜플들에 대해서 반복문을 취하면서 결과가 존재하는지 존재하지 않는지 를 검사해 통과한 튜플만 보여주면 되지 않을까? 그런 표현이 다음과 같다.
select
f.foo_column
from Foo f
where not EXISTS(
select 1
from Bar b
where b.bar_column = f.foo_column
);다시 문제로 돌아오면, Foo는 project로, Bar는 dept_locations 으로 비유할 수 있다. 다만 바뀐 것은 실무에서는 단순한 값 일치를 검사하는 조건이 아니라, 요구사항에 따라 좀더 복잡한 조건을 사용하는 경우가 많다는 것이다. project 의 모든 튜플에 대해 반복문을 실행하면서 dept_locations 에 대한 쿼리 수행 결과가 "존재하는지 존재하지 않는지" 검사한다는 기본 개념은 같다.
방법 1 : EXISTS 사용
select
p.Pname,
d2.Dname
from project p
inner JOIN department d2 on p.Dnum = d2.Dnumber
where not exists(
select 1
from dept_locations dl
where dl.Dnumber = p.Dnum and dl.Dlocation = p.Plocation
);방법 2 : IN 연산자 사용하기
project 의 attribute 를 서브쿼리 안에서 사용해서 논리적으로 방법 1과 같다. 그렇지만, EXISTS 연산자를 사용하면, dept_locations 에서 모두 검색하는 것이 아니라 조건을 충족하는 경우가 1건이라도 있으면, 검색을 멈추고 다음 project 에 대한 검색을 수행하기 때문에 일반적으로 EXISTS 연산자의 효율이 IN 연산자를 쓰는 것과 같거나 더 효율이 좋을 수 있다 (하지만 DBMS들이 서로 다르지만 갈수록 모두가 똑똑해지고 있어서 정확한 비교는 어렵다.). 여기서 IN 연산자를 사용해 본 것은 물론 논리적으로 이렇게도 할 수 있다는 것을 보여주기 위함이다.
select
p.Pname,
d.Dname
from project p
inner join department d on p.Dnum = d.Dnumber
where p.Plocation not in (select dl.Dlocation
from dept_locations dl
where dl.Dnumber = p.Dnum);
로컬 컴퓨터에서 db를 관리하는 경우는 특별한 경우들이다. 많은 경우, 데이터베이스를 서버에 올려 사용한다. 일반적으로 서버에 원하는 DBMS를 설치하여 사용할 수 있다. 그렇지만, 클라우드에서는 database layer 를 인터페이스화 하고, 설치나 분산 처리, 백업 등의 관심사를 뒤로 감추어서 실수를 줄이고 편리하게 하는 방법을 제공한다. 이러한 것을 PaaS (Platform as a Service : 플랫폼 자체를 추상화시키고 인터페이스만 노출해 서비스로 제공하는 것) 라고 한다.
어쨌든, AWS 에서 MySQL 을 사용하면, 로컬 컴퓨터에 있는 데이터 관리(데이터 유실 걱정, 백업 등)의 번거로움 없이 접속할 수 있고, 네트워크에 연결에 네트워크 기반 서비스를 개발하는 데에 사용할 수 있다.
물론 AWS를 이용하지 않아도 Azure나 GCP와 같은 퍼블릭 클라우드 업체들이 있고, 퍼블릭 클라우드를 이용하지 않아도 집에서 데이터베이스 서버를 운영하거나, cafe24 같은 호스팅 서비스를 통해 서버를 호스팅한 후, 설치해 사용할 수 있다.
하지만, 클라우드 사용을 익혀두면 실제 안정적이고도 빠른 서비스 개발에 사용할 여지가 많으므로, 가장 광범위하게 사용되는 AWS 를 기준으로 소개하고자 한다.
AWS 에서는 관계지향형 데이터베이스를 RDS 라는 서비스로 제공하고, NoSQL은 종류에 따라 ElastiCache, DynamoDB 등으로 제공한다. 이 글에서는 사용자가 AWS 계정이 있고, 비용 문제가 없다(처음 가입하면 너무 비싼 사양의 서비스를 일부러 고르지 않는 이상 프리티어가 적용되어 1년 무료이며, 이와 별개로 우리 국민대학교의 지원도 받을 수 있다.)고 가정한다.
AWS 에 접속하여 Management Console 로 이동한다.
RDS 서비스를 검색하고 들어간다.
왼쪽 메뉴에서 파라미터 그룹을 클릭하여 들어간다.
database 에 대한 설정인 파라미터 그룹을 만들어준다. 이것은 설정 내용에 대한 명세이고, 나중에 데이터베이스 인스턴스에 적용할 수 있다. MySQL 의 default 인코딩은 다국어를 지원하지 않고, 따라서 한글도 지원하지 않는다. 이 문제를 해결하기 위해, 인코딩을 utf8 로 하는 파라미터 그룹을 생성할 것이고, 이후에 이 파라미터 그룹을 mysql 인스턴스에 적용할 것이다.
우선 파라미터 그룹을 생성한다. 파라미터 그룹 패밀리는 mysql5.6 을 선택한다. (일부러 프리티어에서 무료로 사용할 수 있는 5.6 버전을 선택했다. 비용이 상관없거나 원한다면 그 이상을 선택해도 된다. 그 대신 mysql 인스턴스를 만들때에도 버전을 맞추고 파라미터 그룹을 적용해야 한다.) 당연히 그룹 이름은 자유롭게 지어준다.
생성 후 위와 같이 방금 생성한 파라미터 그룹 (예시에서는 jjangga0214-config) 가 보이면 클릭해준다.
chacter_set 을 검색하여, '파라미터 편집'을 클릭하여 chacter_set 으로 시작하는 모든 설정 옵션 값을 utf8 로 바꾸어준다.
위와 같이 utf8 로 변경하고, 변경사항을 저장해준다.
이번에는 collation 으로 시작하는 파라미터 옵션들의 값을 모두 utf8_general_ci 으로 변경 후 저장한다. 그러면 한글을 포함한 다국어를 지원하는 파라미터 그룹이 생성 완료된 것이다.
위에서 파라미터 그룹 생성을 완료했다. 이번에는 왼쪽 메뉴에서 '인스턴스' 메뉴를 클릭하고, 인스턴스 생성을 눌러 인스턴스를 생성하는 단계이다.
AWS 가 PaaS 레벨 서비스인 RDS 에서 지원하는 DBMS 들이 보인다. (이 외의 DBMS 를 AWS 에서 사용하고 싶다면 IaaS(Infrastructure as a Service) 서비스인 EC2 에 직접 DBMS 를 설치해 사용한다.) 우리는 여기서 MySQL 을 예시로 사용할 것이므로 MySQL 을 선택한다.
실제 서비스 운용이 아니라 개발, 테스트 용도라는 가정하에 '개발/테스트 - MySQL' 을 선택한다. 만약 실 서비스 운용이 목적이라면 '프로덕션'을 선택할 수 있다. 물론 작은 규모의 서비스라면 샤딩과 같이 부가적인 설정과 기술이 필요 없을 것이므로 가격이 저렴한(조건에 따라 무료) '개발/테스트' 용도의 가성비가 더 좋을 수 있다. (참고로 Aurora는 AWS 에서 개발한, MySQL 과 호환되는(내부 구현이 다르지만 인터페이스가 같다. 이 점만 놓고 보면 MariaDB와 비슷하다.) DBMS이다.)
DB 엔진 버전에 따라 적용할 수 있는 파라미터 그룹이 달라진다. 이전에 파라미터 그룹을 생성할 때 입력했던 파라미터 그룹 패밀리인 Mysql 5.6 과 호환되도록 5.6 버전 대를 선택해준다. (물론 앞서 파라미터 그룹 패밀리를 다르게 설정했다면, 여기서도 알맞게 맞추어 주어야 해당 파라미터 그룹의 적용이 가능해진다.)
database 의 인스턴스 이름과 사용자이름(user), 암호를 설정해준다.
앞서 만든 DB 파라미터 그룹을 적용함으로서 앞서 설정했던 다국어 인코딩이 지금 만드는 DB 인스턴스에 적용될 수 있게 한다.
몇 분 기다리고 새로고침하면 인스턴스가 생성되어 가동된다.
인스턴스 생성 후 몇분이 지나 인스턴스가 완전히 가동되면 인스턴스 메뉴에서 위와 같이 인스턴스의 현재 상태를 살펴볼 수 있다.
스크롤을 조금 내리면 위와 같이 '연결' 탭이 보인다. 여기서 엔드포인트는 해당 database 인스턴스에 접속할 수 있는 url 이다. 일반적으로 절대 외부에 노출하지 않는 정보이다 (물론 엔드포인트를 알아도 접속하려면 username 과 password 를 알아야 하지만, 모르더라도 DDOS 와 같은 공격을 받거나 다른 이유로 위험할 수 있으므로 일반적으로 공개하지 않는다. 나는 이 글을 쓰는 용도로 인스턴스를 생성했으므로 스크린샷을 찍고 인스턴스를 삭제할 예정이라 공개한다.).
또한, 보안그룹을 볼 수 있는데, 간단히 말해 인바운드(외부에서 접속할 시 허가할 프로토콜, port, ip 등의 규칙)와 아웃바운드(외부에 패킷을 내보낼 때 프로토콜, port, ip 등의 규칙) 규칙을 적용하는 것이다. 위의 예시에서는 인바운드가 122.43.183.79/32 로 되어있으므로 해당 ip 에서만 엔드포인트를 통해 database 로 접속할 수 있다. 이외의 ip 에서 접속시 방화벽에서 차단한다. 아웃바운드는 0.0.0.0/0 으로 설정되어 있는데, 이는 외부의 모든 ip로 패킷을 내보내는 것을 허용한다는 의미이다.
인바운드는 기본적으로 내 컴퓨터의 ip(인스턴스 생성 시점에 aws 에 브라우저에 접속하면서 aws 서버가 파악하는 나의 ip)로 지정되어 있다. 만일 다른 외부에서 접속을 허용해야 한다면 보안그룹을 클릭해 설정을 바꾸어준다.
위 예시에서는 위치무관(0.0.0.0/0) 으로 인바운드를 느슨하게 설정했다. 실 서비스라면 서비스에서 사용하는 ip 대역과 db에 직접 접속하고자 하는 컴퓨터의 ip 만으로 설정하는 것이 좋다. 이렇게 엔드포인트도 노출하지 않고, 인바운드 규칙도 적절히 설정하는 것이 기본적인 보안에 좋다.
이제 우리가 원하는 설정대로 인스턴스가 가동중이라면, 접속하면 된다. database 접속 tool 은 여러가지가 있는데, 나는 jetbrains 사의 datagrip 을 이용한다. 각 프로그램에 알맞은 방법으로 AWS의 인스턴스와 연결을 맺어보자.
위와 같이 Host 로 엔드포인트를, User와 Password 에는 인스턴스 생성시 입력했던 정보를 입력하면 연결할 수 있다.
연결이 잘 되는지 테스트를 해보기 위해 위와 같이 show databases; 를 실행해보았고, mysql 의 기본 데이터베이스들 5개가 잘 검색되었다.
MySQL 은 그 자체로 데이터베이스이다. 그렇지만 이와 별개의 의미로, 내부적으로 database 라고 부르는 또 다른 분류가 존재한다. 이것의 예시가 위에서 show databases; 명령으로 조회한 5개의 database 들이고(그 중 이름이 mysql인 database도 있다.), 일반적으로 여기에서 추가적으로 다른 database 를 생성하여 사용한다. 이 database 들을 쉽게 설명하면, 여러개의 테이블들을 가지고 있는 독립적인 묶음이라고 보면 된다. 즉, MySQL 안에 여러개의 database 가 존재하고, 하나의 database 에는 여러개의 table, view 와 같이 실제 schema와 data가 존재한다. 데이터베이스 수업 예제에서는 CompanyDB 라는 database 를 새로 만들고, 그 안에 여러 table 을 만들었던 것이다.
user 들은 database 에 묶이지 않고 독립적으로 존재하며, 특정 database 들에 대해 특정한 권한을 할당받을 수 있다. 당연히 root 유저는 모든 database 에 대한 모든 권한을 가지고 있다.
AWS 에서는 RDS의 파라미터 그룹에서 설정을 했지만, 직접 MySQL 을 설치한 상황에서 설정을 하고 싶으면, 설정파일인 my.cnf 등에 설정하는 것이 필요하다. 관련 내용은 인터넷에 많으므로, 자세한 내용은 생략한다.
개인적으로 생각하는, 데이터베이스를 PaaS 로 사용할 때의 장점은 샤딩, 분산처리, 자동화 등을 직접 손대지 않고 설정만으로 조절할 수 있다는 점이다.