SQL기초

---

SQL

SQL은 Structured Query Language의 줄임말로 관계형 데이터베이스 관리 시스템에서 자료의 검색과 관리, 데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리할때 이용하는 언어이다. 데이터베이스 언어 SQL 문법의 종류를 살펴보면 크게 3종류이다.

 

데이터 정의 언어 (DDL : Data Definition Language)

ex) CREATE, DROP, ALTER

 

데이터 조작 언어 (DML : Data Manipulation Language)

ex) SELECT, INSERT, DELETE, UPDATE

 

데이터 제어 언어 (DCL : Data Control Language)

ex) GRANT, REVOKE, SET TRANSACTION, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, LOCK

---

CREATE TABLE

ㅇ새로운 릴레이션을 생성하는 데 사용

ㅇ릴레이션의 이름 명시

ㅇ애트리뷰트 이름, 값집합, NOT NULL 제약조건 명시

ㅇ키, 엔티티 무결성, 참조 무결성

CREATE TABLE을 이용하여 위의 EMPLOYEE를 구현해보면 아래와 같다.

위와 같이 EMPLOYEE와 DEPARTMENT를 연결하려면 Mgr_ssn과 Ssn을 연결을 해야하는데 이는 아래와 같이 구현할 수 있다.

MGRSSN이 FK가 되고 SSN이 PK가 되는 것이다. 추가로 MGRSTARTDATE 즉 매니저가 일을 시작한 날짜의 NOT NULL을 풀어서 NULL값이 가능하도록 하였다. DB에서 NULL이 가능하다는 것은 특징적인 것이기 때문에 잘 확인해야한다.

이러한 방식으로 하나씩 연결해 나가면 아래와 같이 된다.

 

---

SELECT

SELECT 문의 구조자료

SELECT <애트리뷰트 목록>: 질의 결과에 나타나는 애트리뷰트 이름 목록

FROM <테이블 목록>: 질의 대상이 되는 릴레이션 목록

WHERE <조건>: 질의 결과의 투플들이 만족해야 하는 조건(bool)식

 

SQL 질의와 관계 대수

• SQL은 관계 대수의 SELECT, PROJECT, JOIN 명령으로 표현 가능함

• SELECT 절: 프로젝션 애트리뷰트에 해당

• WHERE 절: 선택조건, 조인조건에 해당

 

Q0. 이름이 ‘John B. Smith’인 사원의 생일(BDATE)과 주소(ADDRESS)를 검색하시오.

SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’;

 

Q1. ‘Research’ 부서에서 일하는 모든 사원의 이름(FNAME, LNAME)과 주소를 검색하시오.

SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=‘Research’AND DNUMBER=DNO;

DNUMBER = DNO를 통해 equi join이 이용되었다.

 

Q2. 5번 DEPARTMENT에서 일하는 EMPLOYEE 투플들의 모든 애트리뷰트 값들을 검색하라.

SELECT *
FROM EMPLOYEE
WHERE DNO=5;

---

JOIN

두 릴레이션으로부터 “조인 조건”에 맞는 투플을 결합하여 하나의 투플로 생성하는 연산이다.

그 종류는 다양하다.

- 교차 조인 (CROSS JOIN - CARTESIAN JOIN)

 

- 내부 조인 (INNER JOIN) : 조인이 되는 키값을 기준으로 교집합(NULL은 제외)

• 등가/동등/동일 조인(EQUI JOIN)

• 자연 조인 (NATURAL JOIN)

• 비등가 조인(NON-EQUI JOIN)

 

- 외부 조인 (OUTER JOIN) :조인이 되는 키값을 기준으로 기준테이블 Key 집합 (기준테이블은 NULL값 포함)

• 완전 외부 조인 (FULL OUTER JOIN)

• 왼쪽 외부 조인 (LEFT OUTER)

• 오른쪽 외부 조인 (RIGHT OUTER)

 

- 안티 조인 (ANTI JOIN)

- 세미 조인 (SEMI JOIN)

- 셀프 조인

---

Cartesian product

카티션 프로덕트는 두 개의 테이블에서 각각의 레코드들을 서로 결합하여 하나의 레코드로 구성하면서 가능한 모든 조합의 레코드들로 테이블을 만드는 연산이다. 즉, 두 테이블의 카티션 프로덕트는 모든 가능한 조합에 의해 레코드들을 생성한다. 이때 조인은 특정 조건을 만족하는 레코드만 선택한다. 즉, 조인은 두 테이블을 카티션 프로덕트를 한 후 그 결과에서 조건식을 만족하는 레코드만을 선택하는 연산이다.

---

INNER JOIN

내부조인(inner join)중의 동등조인(equi join)

 

ex) dnumber=dno인 모든 투플을 선택하라 (implicit notation)

SELECT *

FROM EMPLOYEE, DEPARTMENT

WHERE DNUMBER=DNO;

 

내부조인(inner join)의 동등조인(equi join) : 가장 많이 쓰이는 조인 연산

 

자연조인(natural join)

- 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인을 간단히 표현하는 방법으로 조인 조건이 필요 없으나 반드시 두 테이블 간의 동일한 이름, 타입을 가진 컬럼이 필요하다. 조인에 이용되는 컬럼은 명시하지 않아도 자동으로 조인에 사용된다. 동일한 이름을 갖는 컬럼이 있지만 데이터 타입이 다르면 에러가 발생한다.

---

OUTER JOIN

- LEFT OUTER JOIN

• 결과에 왼쪽 테이블의 모든 투플이 나온다. 만약, 오른쪽 테이블에 매칭되는 투플이 없으면 NULL로 채운다.

- RIGHT OUTER JOIN

• 결과에 오른쪽 테이블의 모든 투플이 나온다. 만약, 왼쪽 테이블에 매칭되는 투플이 없으면 NULL로 채운다.

 

SELECT E.Lname AS Employee_Name, S.Lname AS Supervisor_Name
FROM Employee AS E LEFT OUTER JOIN EMPLOYEE AS S ON E.Super_ssn

---

Multiway JOIN

Join연산도 중첩(nested)될 수 있다.

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber)
JOIN EMPLOYEE ON Mgr_ssn=Ssn)
WHERE Plocation=‘Stafford’;

 

sql문을 분석해보면 아래와 같다.

SELECT 절: `Pnumber`, `Dnum`, `Lname`, `Address`, `Bdate` 라는 컬럼들을 선택하여 결과로 가져온다.
  
**FROM 절**: `PROJECT` 테이블과 `DEPARTMENT` 테이블을 JOIN 연산한다. 이때 조인의 기준은 `Dnum`이 `Dnumber`와 같은 경우이다. 즉, `PROJECT` 테이블의 `Dnum` 컬럼 값과 `DEPARTMENT` 테이블의 `Dnumber` 컬럼 값이 일치하는 레코드끼리 결합된다.
    그 후, 이렇게 결합된 결과와 `EMPLOYEE` 테이블을 다시 JOIN 연산한다. 이때의 조인 기준은 `Mgr_ssn`이 `Ssn`과 같은 경우이다. 즉, 앞서 언급된 조인 결과의 `Mgr_ssn` 컬럼 값과 `EMPLOYEE` 테이블의 `Ssn` 컬럼 값이 일치하는 레코드끼리 결합된다.
    
**WHERE 절**: 조인된 결과 중에서 `Plocation`이 'Stafford'인 레코드만을 필터링하여 선택한다.
결과적으로 이 쿼리는 `PROJECT` 테이블의 위치(`Plocation`)가 'Stafford'인 프로젝트들과 관련된 부서 및 그 부서의 관리자의 일부 정보(`Lname`, `Address`, `Bdate`)를 반환합니다.

---

Anti JOIN

세미 조인과 반대 되는 개념으로 서브 쿼리 결과에는 없는 메인 쿼리 결과 데이터만 추출하는 조인이다. 한쪽 테이블에만 있는 데이터를 추출하고 NOT IN이나 NOT EXISTS 연산자를 사용한다.

SELECT *
FROM employee a, department b
WHERE a.dno = b.dnumber AND a.dno
NOT IN
	( SELECT dnumber
	FROM department
	WHERE mgrssn IS NULL) ;

---

Semi JOIN

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

SELECT *
FROM employee a, department b
WHERE a.dno = b.dnumber AND a.dno
IN
	( SELECT dnumber
	FROM department
	WHERE mgrssn IS NOT NULL) ;

---

DELETE

DELETE 명령은 릴레이션에서 투플(들)을 제거하는 명령이다. 삭제할 투플에 대한 조건은 WHERE 절에서 명시한다. WHERE 절을 생략한 경우에는 테이블내의 모든 투플을 삭제하며, 테이블은 데이터베이스내에서 빈 테이블로 남게 된다. 따라서 항상 조건을 넣어주고 조심스럽게 DELETE를 수행해야한다.

 

DELETE FROM EMPLOYEE;

위의 명령어는 한번의 DELETE 명령으로 WHERE 절의 조건을 만족하는 투플을 모두 삭제한다. 따라서 아래와 같이 명령을 실행해야한다.

DELETE FROM EMPLOYEE WHERE LNAME=‘Brown’;

DELETE FROM EMPLOYEE WHERE SSN=‘123456789’;

DELETE FROM EMPLOYEE WHERE DNO=5;

---

UPDATE

UPDATE 명령은 투플의 애트리뷰트 값을 수정하기 위해 사용한다. WHERE 절은 한 릴레이션에서 수정할 투플을 선택하는데 사용된다. SET절은 변경할 애트리뷰트와 그들의 새로운 값을 명시한다.

//PROJECT 테이블에서 PNUMBER가 10인 투플에 대하여 PLOCATION을 ‘Bellaire’로 변경하고, 담당 부서인 DNUM을 5로 변경하라.
UPDATE PROJECT
SET PLOCATION=‘Bellaire’, DNUM=5
WHERE PNUMBER=10 ;

---