6주차. SQL 1

1. 개요

SQL(Structed Query Language)은 DBMS에서 자료의 검색과 관리, 데이터베이스 스키마 생성 및 수정, 데이터베이스 객체 접근 조정을 위해 사용된다. 쉽게 말해 데이터베이스 언어인 셈이다. SQL은 아래와 같이 세가지로 분류된다.

• 데이터 정의 언어(Data Definition Language)
  → CREATE, DROP, ALTER 등
• 데이터 조작 언어(Data Manipulation Language)
  → SELECT, INSERT, DELETE, UPDATE
• 데이터 제어 언어(Data Control Language)
  → GRANT, REVOKE, COMMIT, ROLLBACK 등

2. CREATE TABLE

1) 사용법

① relation의 이름을 명시한다.

Create Table Employee

② attribute의 이름, Domain Constraints, Null Constraints를 명시한다.

Create Table Employee
	(FName	    VARCHAR(15)      Not NUll,
     MInit      CHAR,
     LName      VARCHAR(15)      Not NUll,
     Ssn 	    CHAR(9)          Not Null,
     BDate      Date,
     Address    VARCHAR(30),
     Gender     Char,
     Salary     Decimal(10, 2),
     SupperSsn  CHAR(9),
     DNo        Int              Not Null

③ Key Constraints, Entity Integrity Constraints, Referential Integrity Constraints를 명시한다.

Create Table Employee
	(FName	     VARCHAR(15)       Not NUll,
     MInit       CHAR,
     LName       VARCHAR(15)       Not NUll,
     Ssn 	     CHAR(9)           Not Null,
     BDate       DATE,
     Address     VARCHAR(30),
     Gender      CHAR,
     Salary      DECIMAL(10, 2),
     SupperSsn   CHAR(9),
     DNo         INT               Not Null,
    Primary Key(Ssn),
    Foreign Key(SuperSsn) References Employee(Ssn),
    Foreign Key(DNo) References Department(DNumber));

아직 Department 테이블이 만들어지지 않았기 때문에 위의 Create Table 명령은 실행되지 않는다. 하지만, Department도 Employee의 pk를 필요로 하기 때문에(순환 참조) Department를 먼저 만드는 것도 불가능하다. 이럴 때에는 둘 중 아무 테이블이나 일단 외래키 부분을 뺀 상태로 Create하고, 나중에 Alter Table 명령으로 fk를 연결해야 한다.

2) 활용

위에서 배운 사용법대로, 아래의 스키마를 참조하여 Create Table 문을 작성해보자.

① Department

• 부서명은 대략 15글자 정도이며, 중복을 허용하지 않는다.
• Ssn은 항상 9글자이다.

Create Table Department
	(DName          VARCHAR(15)   Not Null,
     DNumber        INT           Not Null,
     MgrSsn         CHAR(9)       Not Null,
     MgrStartDate   DATE,
    Primary Key(DNumber),
    Unique(DName),
    Foreign Key(MgrSsn) References Employee(Ssn));

② Dept_Locations

• 부서의 위치는 대략 15글자 정도로 표현된다.
• 한 부서가 여러 곳에 위치할 수 있다.

Create Table Dept_Location
	(DNumber     INT           Not Null,
     DLocation   VARCHAR(15)   Not Null,
    Primary Key(DNumber, DLocation),
    Foreign Key(DNumber) References Department(DNumber));

③ Project

• 프로젝트 명은 대략 15글자이며, 중복을 허용하지 않는다.
• 프로젝트의 위치는 대략 15글자이며, 필수적인 값이 아니다.

Create Table Project
	(Pname       VARCHAR(15)    Not NUll,
     PNumber     INT            Not Null,
     PLocation   VARCHAR(15),
     DNum        INT            Not Null,
    Primary Key(PNumber),
    Unique(PName),
    Foreign Key(DNum) References Department(DNumber));

④ Works_On

• 근무시간은 십의 자리까지만 나타내며, 소수점 이하 한자리까지만 표기한다.

Create Table Works_On
	(ESsn    CHAR(9)        Not Null,
     PNo     INT            Not Null,
     Hours   DECIMAL(3,1)   Not Null,
    Primary Key(ESsn, PNo),
    Foreign Key(ESsn) References Employee(Ssn),
    Foreign Key(PNo) References Project(PNumber));

⑤ Dependent

• 부양 가족의 이름은 대략 15글자 정도이다.
• 사원과의 관계는 대략 8글자로 표현되며 필수적인 값이 아니다.

Create Table Depedent
	(ESsn            CHAR(9)       Not Null,
     DependentName   VARCHAR(15)   Not Null,
     Gender          CHAR, 
     BDate           DATE,
     Relationship    VARCHAR(8),   
    Primary Key(ESsn, DependentName),
    Foreign Key(ESsn) References Employee(Ssn));

3. SELECT 문

1) 기본 사용법

Select {attribute 이름} From {테이블 명} Where {조건}의 형태로 쓰이며, 이 때 각 중괄호에는 여러 개의 값이 들어갈 수 있다. 이 내용을 바탕으로 이름이 John B. Smith인 사원의 BDate와 Address를 검색하는 쿼리를 작성해보자.

Select Bdate, Address 
From Employee 
Where FName='John' And MInit='B' And LName='Smith';  

실행 결과는 아래와 같다.

2) 동등 Join 문과 함께 사용하기

Employee의 Dno와 Department의 DNumber가 같은 모든 튜플을 검색해보자. 이 쿼리의 결과는 사원의 소속 부서에 대한 정보를 함께 보고 싶을 때 용이할 수 있다.

① Implicit Notation(암시적 표현)

Select * 
From Employee e, Department d
where e.Dno = d.DNumber;

② Explicit Notation(명시적 표현)

Select * 
From Employee e
Inner Join Department d
On e.Dno = d.DNumber;

보다시피 조금 더 간단한 암시적인 표현이 더 일반적으로 사용된다. 참고로, From 뒤에 나오는 테이블의 순서를 바꾸면 결과 테이블의 순서도 뒤바뀐다.

3) Join 연산의 종류

① 교차 조인(Cross Join)

• 다른 말로, Cartesian Product 또는 데카르트 곱이라고도 한다.
• 가능한 모든 경우의 수를 테이블로 만드는 연산으로, 데이터 중복이 빈번하다.
• 모든 조인 문을, Cartesian Product의 결과 테이블에서 조건식을 만족하는 부분만 선택하는 연산으로 볼 수 있다.
• Employee와 Department를 교차 조인하면, Employee의 tuple이 총 8개, Department의 tuple이 총 3개이므로, 24개의 tuple을 가지는 테이블이 나타날 것이다.

② 내부 조인(Inner Join)

• 동등 조인(Equivalent Join), 자연 조인(Natural Join), 비등가 조인(Non-Equi Join)으로 구분된다.
• 동등 조인이 가장 자주 쓰이며, 비등가 조인은 거의 사용되지 않는다.
• 자연 조인은 동일한 타입과 이름을 가진 attribute를 조인 조건으로 인식하는 조인문으로, 조인 조건을 직접 작성하지 않아도 된다.
• 아래의 결과는 각각 동등 조인과 자연 조인의 결과를 나타낸 것이다.
  
  

③ 외부 조인(Outer Join)

• 완전 외부 조인(Full Outer Join), 왼쪽 외부 조인(Left Join), 오른쪽 외부 조인(Right Join)으로 구분된다.
• Left Join이 가장 일반적으로 사용된다. Full Outer Join은 MySQL에서 지원하지 않기 때문에 Union 연산을 이용한다.
• Left Join
  • 왼쪽 테이블의 모든 튜플이 나오고, 오른쪽 테이블에서 조건을 만족하는 튜플이 옆에 붙는다.
  • 매칭되는 튜플이 없으면, Null로 채워진다.
• Right Join
  • 오른쪽 테이블의 모든 튜플이 나오고, 왼쪽 테이블에서 조건을 만족하는 튜플이 옆에 붙는다.
  • 매칭되는 튜플이 없으면, Null로 채워진다.
    

④ 안티 조인(Anti Join)

• Not In 또는 Not Exsist 연산자를 사용하여 한쪽 테이블에만 있는 데이터를 추출한다.
• 다음 포스팅에서 자세히 다루기로 한다.

⑤ 세미 조인(Semi Join)

• 안티 조인에 반대되는 개념이다.

⑥ 셀프 조인(Self Join)

4) Nested Join 문

Join으로 생성되는 임시 테이블에 Join 연산을 수행하는 것을 중첩 조인이라 한다. 문법은 별로 어렵지 않지만, 주어진 요구 사항을 이해하는 게 매우 어렵고, 쿼리를 해석하는 것도 간단하지가 않다.

5) 활용

선택 조건과 조인 조건을 잘 구분해내는 것이 무엇보다 중요하다. 선택 조건은 테이블에서 특정 튜플을 골라내는 것이고, 조인 조건은 두 테이블 간의 연결 관계를 동등 조인으로 나타내는 것이다. 쉽게 말해 하나의 테이블에 적용되는 조건은 선택 조건, 두 테이블 사이에 적용되는 조건은 조인 조건이다.

① Research 부서에서 일하는 사원의 FName, LName과 Address를 검색하는 쿼리

• Department 테이블의 필터링을 위한 선택 조건과 Employee와 Department를 조인하기 위한 조인 조건이 where 절에 들어가야 한다.

select e.FName, e.LName, e.Address 
from Employee e, Department d 
where d.DName='Research' // Department에서 DName이 Research인 행을 선택
and 
d.DNumber = e.Dno; // 선택한 Department 행의 DNumber를 Employee의 Dno와 비교한다.

② Stafford에 위치한 프로젝트에 대해 PNumber, DNumber, 부서 관리자의 LName과 Address, BDate를 검색하는 쿼리

• Project 테이블을 필터링하기 위한 선택 조건과 Employee와 Department를 조인하기 위한 조인 조건, Project와 Department를 조인하기 위한 조인 조건이 where 절에 들어가야 한다.

select p.PNumber, d.DNumber, e.LName, e.Address, e.BDate
from Project p, Department d, Employee e
where p.PLocation='Stafford' // 선택 조건
and d.Mgr_Ssn=e.Ssn // 조인 조건 1
and p.DNum=d.DNumber; // 조인 조건 2

③ 사원에 대해 FName, LName을, 직속 감독자의 FName, LName을 검색하는 쿼리

• 이전의 예제에서는 alias를 사용하지 않아도 무방했지만, 이번 예제에서는 반드시 aliasing을 사용해야 한다.

select e.FName, e.LName, s.FName, s.LName
from Employee e, Employee s
where e.Super_Ssn=s.Ssn;

④ Research 부서에서 일하는 모든 사원들에 대해 Employee의 모든 attribute와 Department의 모든 attribute를 검색하는 쿼리

select * from Employee e, Department d
where d.DName='Research' // Department에서 DName이 Research인 행을 선택
and 
e.DNo = d.DNumber; // 선택한 Department 행의 DNumber를 Employee의 Dno와 비교한다.

⑤ 모든 사원의 구별되는 Salary를 검색하는 쿼리

select distinct salary from employee;

6) 기타 연산자

① 집합 연산자

• Union(합집합), Except(차집합), Intersect(교집합)이 존재한다.
• 연산의 결과는 중복을 제거하지 않은 집합으로 표현된다.

② Like + 와일드카드

• % 또는 *은 임의의 개수의 문자를, _ 또는 ?는 임의의 한 문자를 의미한다.
• 연산 속도가 매우 느리기 때문에 사용하지 않는 것을 권장한다.
• 주소가 Houston TX인 모든 사원의 이름을 검색하는 쿼리

select FName, LName
from Employee
where Address like '%Houston TX%';
// 또는 where Address like '*Huston TX*';

• 1960년 대에 태어난 모든 사원의 이름을 검색하는 쿼리

select FName, LName
from Employee
where BDate like '__6_______';
// 또는 where BDate like '??6???????';

③ 산술 연산

• Product X 프로젝트에 참여하는 모든 사원의 FName과 LName, 10% 인상된 급여를 검색하는 쿼리

select e.FName, e.LName, 1.1*e.salary as Increased_Salary 
from Employee e, Project p, Works_on w 
where p.PName='ProductX' // 선택 조건
and p.PNumber=w.Pno // 조인 조건 1
and e.ssn=w.Essn; // 조인 조건 2

④ between-and 연산자

• salary가 30000 이상 40000 이하인 5번 부서의 모든 사원을 검색하는 쿼리

select *
from Employee
where (salary between 30000 and 40000) and Dno=5;

⑤ order by

• 기본 값은 오름차순이다. 참고로, 우리에게 익숙한 순서는 전부 오름차순이다.
• 프로젝트에 참여하는 사원을 부서의 알파벳 순서대로, 또 각 부서 내에서는 LName과 FName의 알파벳 순서대로 DName, LName, FName, PName을 출력하는 쿼리

select DName, LName, FName, PName
from Employee e, Department d, Project p, Works_on w
where w.Essn=e.Ssn // 프로젝트에 참여하는 사원
and d.DNumber=e.Dno // 그 사원의 부서
and p.PNumber=w.Pno // 사원이 진행하는 프로젝트
order by DName, LName, FName;

4. INSERT

1) 튜플 추가하기

테이블의 attribute 순서대로 값을 삽입하거나, 임의의 순서대로 또는 원하는 값만 골라 삽입하는 두 가지 방법이 존재한다.

① 동일한 순서로 삽입

insert into Employee
values ('Richard', 'K', 'Marini', '653298653', '1980-12-24',
		'98 Oak Forest Katy TX', 'M', 37000, '987654321', 4); 

② 임의 순서로 삽입(원하는 값만 골라 삽입)

• 명시하지 않은 attribute는 디폴트 값 또는 null 값을 가지게 된다.
• values의 attribute에는 이름을 명시해도 되고, 안해도 된다.

insert into Employee(FName, LName, DNo, Ssn)
values (FName='Richard', LName='Marini', DNo=4, Ssn='653298653'; 
// 또는 values ('Richard', 'Marini', 4, '653298653');

2) Entity Integrity Constraints

DDL에서 정의한 무결성 제약 조건을 만족해야 한다. 아래와 같은 경우에는 삽입 연산이 진행될 수 없다.

① 2번 부서가 없는 경우

insert into Employee(FName, LName, Ssn, DNo)
values ('Robert', 'Hatcher', '980760540', 2);

② 기본키(Ssn)에 Null이 들어가는 경우

insert into Employee(FName, LName, DNo)
values ('Robert', 'Hatcher', 5);

3) Select와 함께 사용하기

쿼리의 결과로 생성된 임시 테이블을 다른 테이블에 삽입하려는 경우에 Select와 Insert를 결합하여 사용할 수 있다. (이 때에는 values 키워드는 사용하지 않는다.) 아래와 같이 가공된 결과를 새로운 테이블로 저장하는 경우에 유용할 수 있다.

CREATE TABLE WORKS_ON_INFO
( EMP_NAME VARCHAR(15),
  PROJ_NAME VARCHAR(15),
  HOURS_PER_WEEK DECIMAL(3,1) );
  
INSERT INTO WORKS_ON_INFO
SELECT E.LNAME, P.PNAME, W.HOURS
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.PNUMBER=W.PNO AND W.ESSN=E.SSN;

5. DELETE / UPDATE 문

1) DeLete 문

① 기본 사용법

Delete From {테이블 명} Where {조건};

② 활용

• LName이 Brown인 사원을 삭제하는 쿼리

delete from Employee where LName='Brown';

• 5번 부서에 소속된 사원을 삭제하는 쿼리

delete from Employee where DNo=5;

2) Update 문

① 기본 사용법

Update {테이블 명} Set {attribute 이름 = 값} Where {조건};

② 활용

• Project 테이블에서 PNumber가 10인 튜플의 PLocation을 Bellaire로 변경하고 담당 부서인 DNum을 5로 변경하는 쿼리

update Prject 
set PLocation='Bellaire', Dnum=5
where PNumber=10;

• DNo가 5인 부서 소속 모든 사원의 salary를 10% 인상하는 쿼리

update Employee
set salary=salary*1.1
where Dno=5;