[데이터베이스] Ch3 SQL I

3-1 Database Languages

데이터베이스 시스템은 사용자와의 의사소통을 위하여 데이터베이스 언어를 제공해야 하며, 사용자는  데이터베이스 언어를 이용하여 사용자의 요구사항을 데이터베이스 시스템에 표현한다.

 

• 기능적 관점
  1. DDL
     
     • 데이터베이스 스키마에 대한 조작을 담당하는 영역 (스키마 생성, 삭제, 변경)
     • 스키마에 대한 정보를 사전에서 관리하기 때문에 DDL 실행 효과는 데이터 사전에 반영된다.
  2. DML
     • 데이터베이스 인스턴스를 조작하는 언어를 의미한다. (인스턴스 생성, 조회, 삭제, 변경)
     • 사용자는 DML을 이용해 질의를 생성하여 데이터베이스 시스템에 전달한다.
  3. DCL
     • 데이터베이스 시스템의 스키마와 인스턴스를 제외한 다른 객체를 조작하는 데이터베이스 언어이다.
     • ex) 트랜잭션 시작, 종료
• 언어가 표현되는 방식 관점
  1. 절차적 언어
     • 처리 방법 및 절차(how to do)를 명시
  2. 비절차적 언어
     • 방법 및 절차에 대한 언급 없이 원하는 데이터(what to do)만을 명시

 

참고로 SQL언어는 비절차적 언어이며, 사용자는 원하는 데이터를 선언적으로 표현할 뿐 어떻게 데이터를 구하는 지를 명시하지 않는다.

 

3-2 DDL SQL

SQL 명칭은 대소문자 구분이 없으며, 인용부호 내에서만 대소문자를 구분한다.

일반적으로 SQL 문장에세 세미콜론(;) 기호는 문장의 끝을 표기한다.

 

Create table professor(
    pID Char(5),
    name varchar(20) not null,
    deptName varchar(20),
    salary numeric(8,2));

 

테이블을 생성하려면 create table 뒤에 생성하고자 하는 테이블 명이 나오고, 속성명과 도메인명이 쌍을 이루어 나오고, 마지막으로 관련 데이터 무결성 제약이 나온다.

 

위의 코드는 professor 테이블을 정의하며, 4개의 속성을 가지고, 각 데이터 타입은 char(5), varchar(20), varchar(20), numeric(8.2)이다.

두 번째 속성인 name은 널 값을 허용하지 않는 not null 무결성 제약을 가지고 있으며, 상기 무결성 제약에 대한 이름은 없다.

 

Insert into professor values ('10', 'Lee', 'CS', 7500);
Insert into professor values ('11', 'Choi', 'CS', 7000);

 

insert 문장은 professor 테이블에 터플을 추가하는 명령어이며, 하나의 insert 명령은 하나의 터플을 추가하므로 

위의 두 개의 insert 명령을 수행하면 두 개의 터플이 professor 테이블에 추가적으로 생성된다.

 

무결성 제약에는 3가지가 있다.

널 값을 허용하지 않는 not null, 테이블의 주 키를 선언하는 primary key, 외래 키를 선언하는 foregin key이다.

Create table professor(
    pID Char(5),
    name varchar(20) not null,
    deptName varchar(20),
    salary numeric(8,2),
    primary key (pID), 
    foreign key (deptName) references department);

 

위의 예제에서 pID는 주 키이고, deptName은 department 테이블을 참조하는 외래 키이다.

외래 키인 경우 참조하는 속성을 명시적으로 언급하지 않아도 되며, 이는 외래 키는 참조되는 테이블의 주 키만을 참조하기 때문이다.

 

Drop table student;
Alter table student;

 

스키마 삭제는 DROP 문장으로 하며, 스키마 변경은 ALTER 문장을 사용한다.

ALTER 명령을 이용하여 새로운 속성 추가 또는 기존 속성 제거를 할 수 있으며, 새로운 속성을 추가하는 경우에는 새로운 속성명 및 속성 타입이 명시되어야 한다.

 

Delete from student;

 

그렇다면 DROP과 DELETE의 차이점은 무엇일까?

DROP 문장은 스키마를 삭제하는 DDL 기능을 하며, DELETE 문장은 관계의 내용물 즉, 터플을 삭제하는 DML 문장이다.

 

3-3 DML SQL

✅Insertion

입력 연산은 데이터베이스 터플을 입력하는 연산이다.

Insert into course values ('777', 'Undecided', 'CS', null);

 

위처럼 null이라고 명시하면 해당 속성에는 값이 들어가지 않는다.

 

Insert into professor select * from professor;

 

insert 문장에 select-from-where 표현을 사용할 수 있으며, 이 경우 select-from-where 표현이 평가되기 전까지는 터플이 테이블에 입력되지 않는다.

 

위 코드의 의미는 professor 테이블의 터플 개수가 2배가 된다는 의미이다. (무한 루프는 발생하지 않는다.)

 

✅Deletion

delete 문장은 터플을 삭제하는 기능이다.

Delete from professor;
Delete from professor where deptName = 'EE';
Delete from professor where deptName in (select deptName 
					from department
                                        where building = 'Vision Hall');

 

첫 번째 코드는 professor에 있는 모든 터플을 삭제한다는 것이다.

(모든 터플이 삭제되어도 테이블 스키마는 존재하며, 후에 새로운 터플을 입력할 수도 있다.)

 

두 번째 코드는 professor 테이블에 있는 deptName의 속성 중, EE인 터플을 모두 삭제하겠다는 것이다.

 

Delete from professor where salary < (select avg(salary) from professor);

 

위의 예시는 봉급이 교수 평균 봉급보다 작은 교수를 professor 테이블에서 삭제하는 것이다.

 

여기서 알 수 있는 사실은 집계함수는 where절과 함께 사용할 수 없다는 것이다.

집계함수는 avg(salary)처럼 여러 행을 한번에 계산한 뒤, 결과값을 반환한다.

반면, where 절은 각 행을 기준으로 조건을 평가하여 필터링하기 때문이다.

 

그래서 (select avg(salary) from professor)를 통해 먼저 교수들의 평균 봉급을 계산하고 where salary <를 조건으로 사용한 것이다.

 

✅Updates

Update professor 
set salary = salary*1.03
where salary > 7000;

Update professor
set salary = salary*1.05
where salary <= 7000;

 

위의 코드는 교수의 봉급을 인상하는 것이다.

교수 봉급이 7000 이상이면 3% 인상을 하는데

7000보다 많은 봉급을 가진 교수 봉급을 먼저 인상하고, 7000이하 봉급을 후에 갱신한다.

 

만약에 두 순서가 바뀌면 5% 봉급 인상을 하여 7000보다 커지게 된 교수들도 의도치 않게 3%인상이 한 번 더 적용된다.

즉, update의 순서는 중요하다.

 

Update professor
set salary = case
		when salary <= 7000 then salary*1.05
                else salary*1.03
            end;

 

아니면 SQL은 case 문장을 지원하기 때문에 case를 사용하여 위와 동일한 갱신 문장을 만들 수도 있다.

 

3-4 Select SQL Statement

✅Select

Select 문장은 주어진 조건에 적합한 데이터를 검색하여 반환한다.

select A1, A2, ..., An		// attribute list
from R1, R2, ..., Rm		// relation list
where P				// selection predicate
group by <grouping attributes>
having <conditions>
order by <ordering attributes>;

 

select 문장은 위처럼 모두 6개의 절을 가질 수 있다.

이 중에서 select절과 from 절은 반드시 필요하며 나머지 절들은 생략 가능하지만 select 문장에 나타날 때는 반드시 위의 순서를 지켜야 한다.

다만, having절은 group by절이 나오지 않으면 나올 수 없다.

 

Select distinct deptName
from professor;

 

SQL 언어는 입력 테이블과 결과 테이블에 터플의 중복을 허용한다.

그래서 SQL select 문장의 결과 테이블에 터플 중복을 허용하지 않으려면, select절에 위차하는 속성 앞에 distinct를 넣으면 된다.

 

✅Where

where절은 결과 터플이 만족해야 하는 조건을 명시하며, 이는 관계대수의 선택 조건(select predicate)에 대응된다.

 

논리 연산자(and, or, not) 사용이 가능하며, 논리 연산자 적용순위는 산술 연산자보다 후순위이다.

 

✅From 

from절에는 질의에 관련이 있는 테이블을 나열해야 하며, 이는 관계대수의 카티시안곱 연산에 대응이 된다.

Select *
from professor, teaches;

 

위의 예제는 professor 테이블과 teaches 테이블 간의 카티시안곱 연산 결과를 검색하는 질의이다.

 

✅Joins

SQL 문장에서 join은 where절에서 명시할 수 있다.

Select name, cID
from professor, teaches
where professor.pID=teaches.pID;

 

위의 예제에서 where절에 나오는 코드는 professor 테이블의 pID 속성과 teaches 테이블의 pID 속성간의 동등 조인(equal-join)을 표현한다.

Select * 	// 8 attributes 
from professor natural join teaches;

 

자연 조인(natural join)은 두 테이블에서 동일한 이름을 가지는 속성 간에 조인 연산을 적용하며 결과 테이블에는 조인 속성에 대한 중복이 제거되어 한 번만 나온다.

 

✅Rename

테이블과 속성에 대하여 재명명 연산을 적용할 수 있으며, as 키워드를 사용하여 재명명할 수 있다.

 

Select sID, name myName, deptName
from student;

 

주의해야 할 점은, 키워드 as가 생략이 가능하며, 이 경우 콤마 위치에 따라 표현의 의미가 변경된다. 

위의 예제에서 name은 myName으로 재명명되었다.