[데이터베이스] #3 SQL I (데이터베이스 언어, DDL SQL)

목차

1. 데이터베이스 언어
   1. DDL
   2. DML
   3. DCL
   4. 절차적 언어 vs. 비절차적 언어
   5. SQL
2. DDL SQL
   1. SQL 특징
   2. 테이블 생성
   3. 무결성 제약
   4. 스키마 삭제 및 변경
   5. Drop vs. Delete
3. DML SQL
   1. Insert: 입력
   2. Delete: 삭제
   3. Update: 갱신
   4. Select 검색

---

1. 데이터베이스 언어

데이터 베이스 언어는 기능적 관점에서 DDL, DML, DCL로 구분

DDL

스키마에 대한 조작을 담당

(생성, 삭제, 변경 등)

데이터베이스 시스템은 스키마에 대한 정보를 데이터 사전에 저장/관리하므로, DDL 실행 효과는 데이터 사전에 반영

DML

인스턴스에 대한 조작을 담당

(생성, 조회, 삭제, 변경 등)

사용자는 DML을 이용하여 질의를 생성

DML을 질의어라고도 함

DCL

스키마와 인스턴스를 제외한 다른 객체에 대한 조작을 담당

(트랜잭션 시작/종료, 트랜잭션 분리도 지정, 세션 시작/종료, 회복 및 복구 기능, 데이터 권한 부여 및 취소, 사용자 계좌 관리 등)

절차적 언어 vs. 비절차적 언어

절차적 언어는 사용자가 처리 방법 및 절차를 명시

비절차적 언어는 방법 및 절차에 대한 언급 없이 원하는 데이터만을 명시

SQL 언어는 비절차적 언어

SQL

SQL (structured query language)

관계형 데이터베이스 모델을 위한 언어

DDL, DML, DCL 모두 포함

 

---

2. DDL SQL

SQL 언어의 DDL 부분은 관계 및 이와 관련된 것에 대한 사양을 정의

• 관계 스키마
• 속성의 도메인
• 무결성 제약
• 관계에 연관되는 인덱스
• 관계 저장을 위함 디스크 상의 물리적 구조
• 관계에 연관되는 보안 및 권한 부여/취소

 

SQL 특징

• 대소문자 구분 없음 (인용부호 내에서만 대소문자 구문)
• ; (세미콜론)은 문장의 끝을 표기
• 도메인 타입
  • char(n): 고정 길이(n) 문자열
  • varchar(n): 최대 길이(n)인 가변 길이 문자열
  • int: 정수
  • smallint: 작은 정수
  • numeric(p, d): 넘버 numeric(5,2)는 xxx.xx형태
  • real, double precision: 실수
  • float(n): 실수

 

테이블 생성

create table 문장: 새로운 테이블을 정의하여 생성

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 테이블 정의
• professor 테이블은 4개의 속성을 가짐
• 속성의 각 타입은 char(S), varchar(20), varchar(20), numeric(8,2)
• 두 번째 속성인 name은 null 값을 허용하지 않는 not null 무결성 제약을 가짐
• insert 문장은 professor 테이블에 터플을 추가하는 명령어
• 하나의 insert 명령은 하나의 터플을 추가
• insert 문장의 values 부문에서 문자 타입은 ‘’ 부호 있음 숫자 타입은 ‘’ 부호 없음

 

무결성 제약

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,
);

• not null - 널 값 허용하지 않음
• primary key - 주 키 선언
• foreign key - 외래 키 선언

 

constraint myFirstForeignKey foreign key (deptName) references department

무결성 제약은 선언 시에 이름을 명기할 수 있음

무결성 제약이 이름을 가지면, 후에 해당 제약 삭제/변경 시에 용이

gender	char(1),
check (gender in ‘F', 'M'))

gender 속성은 반드시 “F” 또는 “M”

 

foreign key (chairman) references professor(pID)

chairman 왜래 키를 professor 테이블의 pID로 명시 가능

명시하지 않아도 주 키로 지정됨

 

primary key (pID, cID, semester, year)

주 키가 2개 속성 이상으로 구성이 된다면 다음과 같이 선언

 

스키마 삭제 및 변경

• 스키마 삭제: DROP
• 스키마 변경: ALTER
  • 새로운 속성 추가 또는 기존 속성 제거
  • 테이블 제약사항 추가 및 제거 가능
    • (not null, unique, primary key, foreign key, cheak 등)

 

Drop vs. Delete

Drop 문장은 스키마를 삭제하는 DDL 기능

Delete 문장은 터플을 삭제하는 DML 기능

 

---

3. DML SQL

터플 검색, 입력, 삭제, 갱신 연산 등을 수행

• select
• insert
• delete
• update

 

Insert: 입력

Insert into course values ('437', 'Advanced Databases', 'CS', 4);
Insert into course(cID, title, deptName, credit) values ('437', 'Advanced Databases', 'CS', 4);

course에 터플을 입력하는 연산

위 두 질의문은 동일

 

Insert into course values ('437', 'undecided', 'CS', null);

null을 명시하면 해당 속성에는 값이 들어가지 않음

null은 SQL 언어에서 예약어

 

Insert into professor
select * from professor;

insert 문장에 select-from-where 표현을 사용할 수 있음

이 경우 select-from-where 표현이 평가되지 전까지는 터플이 테이블에 입력되지 않음

professor 테이블은 터플 개수가 2배가 되는 효과를 가짐

 

Delete: 삭제

터플을 삭제

Delete from professor;

professor 테이블의 모든 터플 삭제

모든 터플이 삭제되어도 테이블 스키마는 존재함

스키마를 삭제하는 기능은 drop

 

Delete from professor where deptName='EE';

deptName='EE'인 professor 테이블의 모든 터플 삭제

 

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

'Vision Hall' 건물에 위치한 학과 교수의 교수관계에서 튜플을 모두 삭제

중첩질의(SQL 문장 내에 다른 SQL 문장이 존재)

속성만 삭제할 수는 없음

속성을 변경(삭제)하는 것은 update 문장

 

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

1. 집계함수는 where 절에 직접 나올 수 없으므로 중첩 질의 사용
2. 질의어 초기에 평균값을 계산 → 연산 도중 터플이 삭제됨에 따라 평균값이 바뀌지 않음

 

update: 갱신

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

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

salary 7000 초과 교수 연봉 3% 인상, 나머지는 모두 5% 인상

• 첫 번째 문장에서 update 문장의 순서가 바뀌게 되면 급여 3%가 올라 급여가 7000 이상이 된 교수가 급여 5% 인상 조건에 또 해당될 수 있으므로 순서를 주의해야 함
• 두 번째 문장에서는 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);

sudent 테이블의 totalCredit 속성 값을 갱신하는 질의

• from 절에서 course와 takes 간에 자연 조인
• where 절에서 sID를 사용하여 지연조인 결과 테이블과 student 테이블을 조인
• 과목을 하나도 이수하지 못한 학생은 sum(credit)=null이므로 totalCredit=null이 됨

  Update staudent
  set totalCredit=0
  where totalCredit is null;

• 다음으로 수정

 

Select: 검색

주어진 조건에 적합한 데이터를 검색하여 반환

6개의 절을 가질 수 있음

select 절과 from 절은 생략 불가능

나머지 절들은 반드시 위의 순서를 지켜야 함

• select A1, A2, …, An
• from R1, R2, …, Rn
• where P
• group by <grouping attributes>
• having <conditions>
• order by <ordering attributes>

select 문장의 결과는 관계이며 select 문장은 절의 구성요소로 나올 수 있음 (중첩 가능)