3.1 Database Languages
DBMS는 유저와의 의사소통을 위해 데이터베이스 언어를 제공해야하고, 사용자는 언어를 이용하여 요구사항을 표현
데이터베이스 언어 분류 - 기능적 관점
- DDL : Data Definition Language
- DML : Data Manipulation Language
- DCL : Data Control Language
데이터 언어 분류 - 표현 방식 관점
- Procedural : 절차적
- Non Procedural : 비절차적
Data Definition Language : DDL
데이터베이스 스키마에 대한 조작 담당하는 영역
스키마 생성, 삭제, 변경 등 담당
스키마에 관련되는 도메인, 데이터 제약 조건(ic)등을 표현할 수 있는 기능 제공
DDL 컴파일러는 데이터 사전에 저장되는 테이블을 생성함
ex)
Create table professor (
pID char(5),
name varchar(20),
deptName varchar(20),
salary numeric(8,2));Data Manipulation Language : DML
데이터의 인스턴스를 조작하는 언어
데이터 갬색, 생성, 조회, 삭제, 변경 등 기능 제공
사용자는 DML을 이용하여 질의를 생성하여 DBMS에 전달처리하게 하는데, 이런 측면에서 query language라고도 함
Data Control Language : DCL
스키마와 인스턴스 제외한 다른 객체 조작
- 트랜잭션 시작/종료
- 세션 시작/종료
- 백업/복구
- 데이터 권한 부여 및 취소
- 사용자 계정 관리
Procedural vs Declarative : 절차적 vs 비절차적
Procedural 절차적 언어
무슨 데이터를 요구하고 어떻게 데이터를 얻을 것인지 명시함 → 처리 방법 및 절차 명시
대부분의 언어는 prodecural → ex) 관계대수, C, C++, java…
Non Procedural(Decalrative) 비절차적 언어
어떻게 얻을 건지에 대한 내용없이 무슨 데이터가 필요한지만 명시 → 방법 및 절차 X 원하는 데이터만 명시
ex) SQL, PRolog, Lisp
비절차적 언어가 절차적 언어보다 진보된 언어. 하지만 컴퓨터 관점에서는 어렵고 복잡하다.
Relational Database Languages : 관계형 데이터베이스 언어
순수 관계형 데이터베이스 언어
이론적으로 개발되어있으나 상용 시스템에 구현되지 않은 언어
- 관계 대수
- 튜플 관계 해석
- 도메인 관계 해석
→ 세개 모두 질의어 표현력이 동일하고 언어간 상호변환 가능
실제 시스템에서 구현되어 있는 언어
- SQL
- QUEL
- Query by Example
- LDL
SQL Overview
= Structed Query Language의 약어
관계형 데이터베이스 언어
DDL + DML + DCL을 모두 포함하는 언어
관계형 데이터 베이스에서 사실상의 공식적인 표준 언어
3.2 DDL SQL
SQL언어의 DDL부분은 다음과 같은 것들을 정의함
- 관계 스키마
- 속성의 도메인
- 무결성 제약(ic)
- 관계에 연관되는 인덱스
- 관계 저장을 위한 물리적 저장 구조
⇒ 테이블과 컬럼 정의하는 명령어. 생성, 수정, 삭제 등의 데이터 전체 골격 결정
⇒ 데이터베이스의 데이터를 정의하는 언어로, DB관리자가 주로 사용. 스키마, 도메인, 테이블, 뷰, 인덱스 등을 정의하거나 변경, 삭제
SQL 명칭은 대소문자 구분이 없다 → Name = NAME = name = nAmE
인용부호 내에서는 대소문자 구분
세미콜론은 문장의 끝을 표시
Domain Types in SQL
char(n) : 길이가 n인 고정 길이 문자 스트링
varchar(n) : 최대 길이가 n인 가변 길이 문자 스트링
int : integer
smallint : Small integer
numeric(p, d) : 소수점 이하 자릿수 지정. p는 유효숫자 개수이고 d는 소수점 뒤에 나오는 숫자개수
→ ex) numeric(5,2) : xxx.xx 형태
real, double precision
float(n) : 소수점이 n자리 이상 나타내는듯?
Create Table
create table 문장은 새로운 테이블 정의하여 생성함.
Create table R
(A1 D1,
...,
An Dn,
(integrity-constraint1),
...,
(integrity-constraintk));R : relation 이름
각각의 A : relation에 있는 속성의 이름
D : 속성 A의 데이터타입 → 즉, 도메인
마지막에는 관련 데이터 무결성 제약이 나옴
Example)
Create table professor (
pID char(5),
name varchar(20) not null,
deptName varchar(20),
salary numeric(8,2));
- Insert into professor values (‘10’, ’Lee’, ’CS’, 7500);
- Insert into professor values (‘11’, ‘Choi’, ’CS’, 7000);professor 테이블을 정의하며, 4개의 속성 가짐
각각 이름과 데이터 타입 정의함
두번째 name 속성은 널값을 허용하지 않는 not null 무결성 제약을 갖고 있음
→ Insert : professor 테이블에 튜플을 추가하는 명령어
위의 두개 insert 명령을 수행하면 두개의 튜플이 생성됨.
인용부호는 데이터타입이 문자형일때 사용. 기본적으로 single quote
Integrity Constraints : 무결성 제약
주로 세가지를 많이 사용
- not null : 널 값 허용 X
- primary key(A1, …, An) : 테이블의 주 키 선언
- foreign key(A1, …, An) references R : 외래 키 선언
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 테이블을 참조하는 외래키
→ 속성을 명시적으로 언급하지 않아도됨. 외래키는 참조되는 테이블의 주키만 참조하므로
→ 언급한다면 foreign key (deptName) references department(department의 주키); 이런식으로
무결성 제약은 선언시 이름 명기 가능 : 후에 제약 삭제/변경 용이
→ ex) constraint myFirstForeignKey foreign key (deptName) references department;
University Database Creation
Create table department (
deptName varchar(20) primary key, //주키가 한개일땐 이런식으로 가능
chairman char(5),
building varchar(30),
budget numeric(10,0)
foreign key (chairman) references professor);
-- foreign key (chairman) references professor(pID);로 참조 속성 명시할 수도 있음
Create table professor (
pID char(5) primary key,
name varchar(20) not null,
deptName varchar(20),
salary numeric(10,2),
foreign key (deptName) references department);
Create table student (
sID char(5)
primary key, name varchar(20) not null,
gender char(1),
deptName varchar(20),
GPA numeric(3,2),
totalCredit integer,
foreign key (deptName) references department),
check (gender in (‘F’, ‘M’)));
-- gender 속성은 'F' or 'M'만 가져야하는 제약
Create table course (
cID char(5) primary key,
title varchar(20),
deptName varchar(20),
credit integer,
foreign key (deptName) references department);
Create table teaches (
pID char(5),
cID char(5),
semester char(10),
year numeric(4,0),
classroom char(5),
primary key (pID, cID, semester, year), // 주키가 4개 속성의 조합
foreign key (pID) references professor,
foreign key (cID) references course,
foreign key (classroom) references room);
Create table takes (
sID char(5),
cID char(5),
semester char(10),
year numeric(4,0),
grade varchar(2),
primary key (sID, cID, semester, year),
foreign key (sID) references student,
foreign key (cID) references course);
Create table room (
roomID char(5) primary key,
building varchar(30),
capacity numeric(6,0));Drop/Alter Table
Drop : 스키마 삭제
→ ex) Drop table student;
Alter : 스키마 변경
Alter table r add A D;
Alter table r drop A;
Alter table student add constraint myConst foreign key (deptName) references DEPARTMENT on delete cascade;
-- 이런 예시도 있다- r 이라는 테이블에 A라는 속성을 추가. D는 A의 도메인
- A는 r이라는 테이블의 속성을 제거
Drop vs Delete
Drop : 스키마 전체를 삭제하는 DDL 기능. 테이블전체를 날려버림. 그니까 당연히 내용도 날아감
→ ex) Drop table student; // student 테이블 전체 삭제
Delete : 테이블의 내용(즉, 튜플)을 삭제하는 DML 문장. 테이블 스키마는 남아있음
→ ex) Delete from student; // student 테이블의 내용 삭제
DDL에서 중요한 키워드 3개 : CREATE / ALTER / DROP
3.3 DML SQL
대표적인 키워드 4개
- Selecet : 검색
- Insert : 입력
- Delete : 삭제
- Update : 갱신
Insertion : 입력
insert 데이터베이스 튜플을 입력하는 연산
Insert into course values (’437’, ’Advanced Databases’, ’CS’, 4);
Insert into course (cID, title, deptName, credit)values (’437’, ’Advanced Databases’, ’CS’, 4);위의 두가지 질의문 동일하다
Insert into course values (’777’, ’undecided’, ’CS’, null);null이라고 명시하면 해당 속성에는 값이 들어가지 않음
Insert into professor select * from professor;insert문장에 select from where 표현 사용가능
이렇게 할 시 select from where이 평가되기 전까지 튜플이 테이블에 입력되지 않음
해당 문장의 효과는 튜플개수가 2배되는 것
Deletion
Delete from professor;professor 테이블에 있는 모든 튜플을 삭제함
튜플 삭제되어도 스키마는 존재
→ 스키마 삭제는 drop
Delete from professor where deptName=’EE’;professor 테이블에서 과가 EE인 튜플 삭제
Delete from professor
where deptName in (select deptName
from department
where building = ’Vision Hall’);중첩질의
Vision Hall에 있는 학과의 교수 튜플 제거
Delete from professor
where salary < (select avg(salary) from professor);봉급이 교수 평균 봉급보다 작은 교수 삭제
-
집계함수는 where절에 직접 나올 수 없으므로 중첩질의 사용
-
교수가 삭제됨에 따라 평균 값도 변할 수 있는데, SQL에서는 처음에 평균값 계산하고 이를 근거로 연산 수행
Updates
Update ~ set ~ where
Increase salaries of professors whose salary is over 7000 by 3%, and all others receive a 5% raise을 SQL으로 표현
Update professor
set salary = salary*1.03
where salary > 7000;
Update professor
set salary = salary*1.05
where salary <= 7000;교수 봉급을 인상하는 갱신 문장 : 봉급이 7000 이상이면 3% 나머지는 5% 인상한다.
update를 여러번 할 때는 순서가 중요함
→ 3%인상 후에 5% 인상하게되면 3%인상으로 7000 이상이 된 교수는 인상이 중복 적용됨
Update professorset salary = case
when salary <= 7000 then salary*1.05
else salary*1.03
end;case 사용하여 만든 동일한 갱신 문장
Update student S
set S.totalCredit =
(select sum(credit)
from takes natural join course
where S.sID=takes.sID and grade <> ’F’ and grade is not null);student 테이블의 totalCredit 속성 값 갱신함.
grade 속성이 F가 아니고 grade가 널값이 아닌 과목의 credit 속성 값의 합을 구하여 totalCredit 값으로 갱신
set 절에서 scalar subquery가 사용됨
Update student
set totalCredit = 0
where totalCredit is null;만약 어떤 학생이 과목을 하나도 이수하지 않았다면 sum(credit)은 널값이 나옴
→ 이를 0으로 갱신하고자 하면 위의 갱신문 사용하면됨
3.4 Select SQL Statements
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은 생략불가. 나머지는 생략가능하나 순서 지켜야함
having 절은 group by절이 나와야만 나올 수 있음
select 문의 결과는 relation
Select name
from professor;
-- professor 테이블에서 교수 이름이 있는 name 속성 열만 출력select 절은 질의 결과에서 사용자가 보고 싶은 속성 리스트를 가짐
→ 관계 대수의 project 연산과 대응됨
Select *
from professor;
-- professor 테이블 출력*는 ‘모든 속성’을 의미한다
Select pID, name, deptName, salary/12
from professor;
-- professor 테이블에서 해당속성들을 차례로 보여줌
-- salary/2도 자동연산해서 보여줌 -> 당연하지만 값이 아예 바뀌는 것은 아님select 절에 나오는 속성은 수식 표현도 가능하다
Select distinct deptName
from professor;
-- 중복제거하고 학과 이름 보여줌SQL은 결과 테이블의 튜플의 중복 허용
→ 허용하지 않으려면 select 절에 위치하는 속성에 distinct 키워드
위의 예제처럼 하면 중복되는 학과의 이름은 제거
Select all deptName
from professor;
-- 학과 이름만 보여줌
-- Select deptName from professor;과 동일all 키워드는 중복 허용
디폴트이기때문에 생략해도 똑같음
where 절
where 절은 결과 튜플이 만족해야하는 조건을 명시함
→ 관계대수의 select 연산에 대응
from professor
where deptName = ‘CS' and salary > 8000;CS과이면서 연봉이 8000 이상인 교수를 검색하는 질의
and, or, not 같은 논리연산자 사용 가능. 적용순위는 산술연산자보다 후 순위
from 절
from절에는 질의에 관련있는 테이블을 나열해야함
→ 관계 대수의 카티시안 곱 연산에 대응
Select *
from professor, teaches;
가능한 모든 professor, teacher의 쌍을 생성함. 카티시안곱 연산 결과를 리턴하는 질의
SQL Execution Model : 실행 과정
from절에 명시된 각 테이블에서 하나의 튜플을 가져온다
→ where절에 명시된 조건에 적용
→ 참이라면 튜플을 group by절로 보냄 : from절에 있는 모든 조합의 튜플에 대해 위의 과정 실행
→ group by절에 명시된 속성을 이용하여 서브그룹을 생성
→ 각 그룹에 대해 having 절의 조건을 적용하여, 조건을 참으로 만드는 서브그룹을 구함
→ 서브그룹에 대해 order by 절을 적용하여 디스플레이
Joins
조인은 where절에서 명시할 수 있음
Select name, cID
from professor, teaches
where professor.pID=teaches.pID;where절의 =는 professor테이블의 pid속성과 teaches테이블의 pid 속성간의 equi조인 표현
→ equi조인 : 두 테이블의 중복되는 속성(여기선 Pid)가 같은것만 남김
⇒ 즉 pID를 기준으로 두 테이블을 합치는것
→ 여기서 pID 속성이 두번나오므로 하나를 제거하게되면 : natural join
pID속성이 두 테이블 모두에 존재하므로 속성이름만 사용하면 혼돈 발생 가능 → 테이블 이름도 명시
Select title, name
from teaches, course, professor
where teaches.cID=course.cID and teaches.pID=professor.pID
and course.deptName=‘CS‘;
teaches, course, professor간에 equi조인이 필요한 질의
deptName은 course와 professor 모두에 존재하므로 course.deptName으로 표현
Natural Joins
natural join
자연조인은 두 테이블에서 동일한 이름을 가지는 속성간에 조인연산을 적용하며 결과 테이블에는 조인속성에 대한 중복이 제거하여 한번만 나옴
Select * -- 8 attributes
from professor natural join teaches;professor 테이블과 teaches 테이블에는 pID 속성이 공통존재
→ 둘 중 하나는 제거됨 : 총 속성 8개 테이블 리턴
Select * -- 9 attributes but semantically equivalent
from professor, teaches
where professor.pID=teaches.pID;이렇게하면 professor.pID와 teaches.pID 두 속성 모두 테이블에 나오게되므로 총 9개의 속성 테이블을 리턴하게됨
동일한 이름을 가진 속성으로 인해 원하지 않은 조인 연산이 일어나지 않도록 주의해야함
교수 이름과 그 교수가 강의하는 과목명을 검색하는 질의어를 만들어보자
Select name, title
from professor natural join teaches natural join course;위의 질의는 틀린것
deptName이 professor, course테이블에 공통 속성으로 존재 : 이 속성에 대해 Natural join
→ 교수가 소속학과에서 한 수업만 나오게됨
→ 교수가 다른학과에서 개설한 교과목 정보 포함안됨 : Wrong!
Select name, title
from professor natural join teaches, course
where teaches.cID= course.cID;
Select name, title
from (professor natural join teaches) join course using(cID);
-- using : 동일한 이름을 가진 모든 속성 X, 명시된 속성만을 조인 속성으로 하는 것
Select name, title
from teaches, course, professor
where teaches.cID=course.cID and teaches.pID=professor.pID;Rename Operations
as 키워드를 통해 테이블과 속성 재명명 가능
→ old-name as new-name
Select pID, name, salary/12 as monthlySalary
from professor;salary/12를 monthlySalary로 재명명 → 영구적으로 바뀌는건 아님. Select문은 다 그럼
Select sID, name myName, deptName
from student; -- “name” is renamed
Select sID, name, myName, deptName
from student; -- wrong attribute name, syntax error키워드 as는 생략가능 : 생략하려면 as만 그냥 빼면됨
- as를 생략하고 name을 myName으로 재명명한 것
- 4개의 속성 테이블을 리턴하는 문장 → wrong!
Select distinct T.name -- T.name이라고 있어도 이렇게 출력되는것 아님. name이라고 나옴
from professor as T, professor as S
where T.salary > S.salary and S.deptName = ‘CS’;
-- 소프트웨어 학부의 최소 아무 1명보다 돈을 많이 받는 사람들의 목록
-- 최저가 6000이니 그 이상인 사람들(중복 없이)두번 재명명도 가능
원래 from절에 명기된 모든 테이블에서 한개의 튜플을 가져와서 where절 조건 적용
→ from절에 동일 테이블 두번 재명명
→ professor 테이블에서 한번에 두개 튜플 가져오는 효과
→ 비교 연산할 때 사용
String Operations
like 연산자는 패턴을 활용하여 스트링에 대한 비교연산 제공
% : 길이에 무관한 임의 스트링 → 몇개의 문자가와도 상관없다는 의미
_ : 하나의 character
Select name
from professor
where name like '%da%‘;da가 이름중에 아무곳이나 들어가있는 교수이름 검색
→ ex) dda(O) da(O) dan(O)
select name
from professor
where name like '%n_'; 1개의 스트링이 와야함을 의미
→ ex) yang(O), han(X)
Select cID
from course
where title like ‘100\%' escape '\';100%를 가지는 스트링을 검색하는 질의
%를 임의의 스트링을 의미하게하지말고 퍼센트 기호로 인식하게 하기위해 탈출 기호 사용
Tuples Ordering
order by : 결과 테이블의 튜플 정렬에 사용
Select distinct name
from professor
order by name;기본값 오름차순 → a,b,…,c 순서로
내림차순으로 하려면 desc 키워드 추가 → ex) order by name desc
Order by deptName desc, name한개 이상의 속성기준으로 정렬 가능
deptName값 기준 내림차순으로 1차정렬 후, 동일한 값 사이에서는 name 속성기준 오름차순 정렬
“Where” clause predicates : where 절 연산자
Select name
from professor
where salary between 5000 and 6000; -- 5000<= 이고 <=6000between : 값 구간 의미함. 경계값 포함
Select name, cID
from professor, teaches
where (professor.pID, deptName) = (teaches.pID, ’CS’);
-- where professor.pID=teaches.pID and deptName='CS';과 동일튜플 사이의 비교가능
괄호로 임시튜플 생성하여 비교하는 개념
Set Operations
union, intersect, except:차집합
자동으로 중복제거 → 안하려면 union all 이런식으로
// 튜플이 r 테이블에서 m번 나오고, s 테이블에서 n번 나올때
m+n times in “r union all s”
min(m, n) times in “r intersect all s”
max(0, m–n) times in “r except all s”union : 두 입력 멀티셋을 중복 상관없이 더함
intersect : 튜플이 입력멀티셋 테이블에서 적게나오는 횟수만큼 결과 멀티셋 테이블에 나옴
Find course IDs that ran in Fall 2009 or in Fall 2010
(select cID from teaches where semester = ‘Fall’ and year = 2009)
union
(select cID from teaches where semester = ‘Fall’ and year = 2010);
Find course IDs that ran in Fall 2009 and in Fall 2010
(select cID from teaches where semester = ‘Fall’ and year = 2009)
intersect
(select cID from teaches where semester = ‘Fall’ and year = 2010);
Find course IDs that ran in Fall 2009 but not in Fall 2010
(select cID from teaches where semester = ‘Fall’ and year = 2009)
except
(select cID from teaches where semester = ‘Fall’ and year = 2010);2009 가을 or/and/but not 2010가을에 강의한 course IDs를 리턴
3.5 Null Values
튜플의 속성 값으로 널 값 가질 수 있음
Unknown : 값이 존재하는데 잘 모름
Not exist : 값 자체가 없음
널 값이 포함되는 산술연산의 결과는 널 값
→ ex) 5 + null = null
Select name
from professor
where salary is null;
-- where deptName=""; 이런건 틀린거널값의 존재여부는 is null 사용
Three-valued Logic
널 값이 속한 비교연산자의 결과는 unknown
→ ex) 5 < null, null <> null 이런거 다 결과 unknown
따라서 비교연산자의 결과는 참, 거짓, unknown 3가지
OR:
(unknown or true) = true
(unknown or false) = unknown
(unknown or unknown) = unknown
AND:
(true and unknown) = unknown
(false and unknown) = false
(unknown and unknown) = unknown
NOT:
(not unknown) = unknownwhere절에서 사용시 결과가 참인 경우만 사용자에게 반환
false, unknown은 둘다 반환X
true AND false -> take minimum of 1 and 0 -> 0 -> false
True OR unknown -> take maximum of 1 and 0.5 -> 1 -> true
Not false -> (1 – 0) -> 1 -> true
Not unknown -> (1 – 0.5) -> 0.5 -> unknowntrue : 1, false : 0, unknown : 0.5
AND : 최소를 취함
OR : 최대를 취함
NOT : (1-value)를 취함
📖 데이터베이스 1: 이론과 실제, 이상호, 진샘미디어
