데이터베이스 SQL <데이터 정의 언어(DDL), 데이터 조작 언어(DML)>

SQL

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

- 데이터 저장 구조를 명시하는 언어

- 테이블 스키마의 정의, 수정, 삭제를 위한 명령문 제공

​

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

- 사용자가 데이터를 접근하고 조작할 수 있게 하는 언어

- 레코드의 검색(search), 삽입(insert), 삭제(delete), 수정(update)을 위한 명령문 제공

​

데이터 정의 언어(DDL)

◽ 테이블 생성(create table) - 기본키, 외래키 설정

◽ 테이블 삭제(drop table)

◽ 테이블 수정(alter table)

​

<<주요 데이터 타입>>

 

테이블 생성

형식 : create table <테이블 이름> (<필드리스트>)

◽<필드리스트>는 '<필드명> <데이터타입>'의 나열

​

예) department 테이블을 생성하는 SQL문

create table department(
   dept_id     varchar2(10)     not null,
   dept_name   varchar2(14)     not null,
   office      varchar2(10) 
)

→ not null은 해당 필드에 널을 허용하지 않는다는 것을 의미

​

기본키 설정

- 테이블에서 기본키 역할을 할 필드를 지정

- constraint <제약조건 이름> primary key(<기본키 필드명>)

create table department(
   dept_id     varchar2(10)     not null,
   dept_name   varchar2(14)     not null,
   office      varchar2(10) 
   constraint pk_department primary key(dept_id)
)

create table department(
   dept_id     varchar2(10)     primary key
   dept_name   varchar2(14)     not null,
   office      varchar2(10) 
)

위의 질의 둘다 가능, dept_id 필드를 기본키로 지정

<제약조건 이름>은 다 다르게 지정

​

외래키 설정

- 테이블 생성 시 외래키 역할을 할 필드를 지정

- constraint <제약조건 이름> foreign key (<외래키 필드명>) references <참조될 테이블명>(<기본키 필드명>)

create table student(
   stu_id       varchar2(10),
   resident_id  varchar2(14)    not null, 
   name         varchar2(10)    not null,
   year         int,
   address      varchar2(10),
   dept_id      varchar2(10),

   constraint pk_student primary key(stu_id)
   constraint fk_student foreign key(dept_id) references department(dept_id)   
)

stu_id 필드를 기본키로 지정, dept_id를 외래키로 지정함

​

테이블 삭제

형식: drop table <테이블 이름>

✔ 다른 테이블에서 외래키로 참조되는 경우에는 삭제할 수 없음

​

테이블 수정

- 기존 테이블에 새로운 필드를 추가하거나 기존 필드를 삭제

필드 추가 형식: alter table <테이블 이름> add <추가할 필드명 데이터 타입>

예) student 테이블에 age 필드를 추가

alter table student add age int

필드 삭제 형식: alter table <테이블 이름> drop column <삭제할 필드명>

예) student 테이블에 age 필드를 추가

alter table student drop column age

 

기본키, 외래키 주의사항

✔ 외래키를 갖는 테이블을 생성할 때 외래키가 참조하는 테이블을 먼저 생성해야함

✔ 테이블을 삭제할 때도 외래키로 참조되는 테이블은 삭제할 수 없음

✔ 외래키를 해제하거나 외래키를 참조하는 테이블을 먼저 삭제해야함

​

데이터 조작 언어(DML)

​

레코드 삽입

형식 : insert into <테이블이름>(<필드 리스트>) values (<값 리스트>)

<필드 리스트>

◽ 새로운 레코드가 삽입될 테이블의 필드들을 나열

<값 리스트>

◽ <필드 리스트>의 순서에 맞춰 삽입될 값들을 나열

​

✔ <필드 리스트>에 나열되지 않은 필드에 대해서는 NULL이 입력

❕ NULL이 혀용되지 않도록 정의된 필드에는 반드시 값을 지정해야함

✔ <필드 리스트>를 생략할 경우 <값 리스트>에는 테이블을 정의할 때 사용한 필드들의 순서에 맞춰서 모든 값을 나열해야함

 

​예)

insert into department(dept_id, dept_name, office)
values ('920', '컴퓨터공학과', '210호')

<필드리스트>를 생략하고 데이터를 삽입하는 예

insert into department
values ('923', '산업공학과', '207호')

레코드 삽입 시 주의사항

외래키 필드를 포함하는 레코드를 삽입할 때 참조하는 테이블의 해당 필드에 그 값을 갖는 레코드가 존재해야함

❕ 참조 테이블에 그런 레코드를 먼저 삽입해야함

​

레코드 수정

형식 : update <테이블 이름> set <수정 내역> where <조건>

<수정 내역>

◽ 대상 테이블의 필드에 저장되는 값을 수정하기 위한 산술식

◽ ',' 을 이용하여 여러 필드에 대한 수정 내역을 지정 가능

 

<조건>

◽ 수정 대상이 될 레코드를 선택하기 위한 조건을 기술

◽ 관계대수에서 선택 연산(σ)의 조건식과 같은 의미

◽ where절을 생략할 경우, 테이블의 모든 레코드를 수정

​

예) student 테이블에서 모든 학생들의 학년을 하나씩 증가

update student
set    year = year + 1

 

예) professor 테이블에서 '고희석' 교수의 직위를 '교수'로 수정하고 학과번호를 '923'으로 수정

update professor
set    position = '교수', dept_id = '923'
where  name = '고희석'

update professor set position = '교수', dept_id = '923' where name = '고희석'

레코드 수정 시 주의사항

❕ 외래키로 지정된 필드의 값을 수정할 때 외래키가 참조하는 테이블에 존재하는 값으로만 수정 가능

​

레코드 삭제

형식 : delete from <테이블 이름> where <조건>

◽ where 절에 지정된 조건을 만족하는 레코드들을 모두 삭제

◽ where 절이 생략되면 테이블에 있는 모든 레코드를 삭제

​

예) professor 테이블에서 이름이 '김태석'인 교수를 삭제

delete from professor
where       name = '김태석'

✔ delete문을 이용하여 테이블의 모든 레코드를 삭제하더라도 테이블은 삭제되지 않음

​

레코드 삭제 시 주의사항

❕ 다른 테이블의 외래키에 의해 참조되는 필드를 가지고 있는 테이블에서 레코드를 삭제할 경우 오류 발생 가능

​

레코드 검색

select 문

- 데이터베이스에서 원하는 레코드를 찾기 위한 질의 문장을 표현

- SQL에서 가장 많이 사용됨

형식 : select <필드 리스트> from <테이블 리스트> where <조건>

 

select

◽ 질의 결과로 출력할 필드들을 지정

◽ 관계대수의 추출(project)연산(π)에 해당

from

◽ 질의 실행과정에 필요한 테이블들을 지정

◽ 여러 테이블들이 나열될 경우, 관계대수의 카티션 프로덕트를 의미

where

◽ 검색되어야 하는 레코드에 대한 조건 기술

◽ 관계대수의 선택 연산(σ)에서 사용되는 조건식과 같은 의미

◽ 특별한 조건이 없을 경우 생략 가능

select name, dept_name
from   deparment, student
where  department.dept_id = student.dept_id

예) student 테이블에서 모든 학생들의 주소를 검색

select address from student

검색 결과에서 중복된 레코드를 제거하려면 select 절에서 distinct 키워드를 사용해야함

​

예) 모든 학생들의 주소를 검색하되 중복을 제거하고 출력

select distinct address
from   student

from 절에 나타난 테이블에서 모든 필드의 값을 추출할 경우, select 절에 모든 필드를 명시할 필요 없이 '*' 사용

 

예) 학생들에 대한 모든 정보를 검색

select * from student

✔ 필드들의 출력 순서는 테이블에 정의된 순서대로 출력

​

레코드의 순서 지정(order by)

- 검색 결과를 정렬하여 출력하는 기능

형식 : select문 마지막에 다음과 같은 order by절을 추가

order by <필드리스트>

- 오름차순을 기본으로 하며 <필드리스트>에 여러 개의 필드를 나열할 경우 나열된 순서대로 정렬

​

예) 3, 4학년 학생들의 이름과 학번을 검색하되, 학생 이름의 오름차순으로 정렬하고 같은 이름에 대해서는 학번의 오름차순으로

select   name, stu_id
from     student
where    year = 3 or year = 4
order by name, stu_id

내림차순 정렬은 해당 필드 이름 뒤에 desc 키워드를 삽입

​

예) 앞의 질의에서 검색 결과를 학생 이름의 내림차순으로 정렬

select   name, stu_id
from     student
where    year = 3 or year = 4
order by name desc, stu_id

재명명 연산

- 테이블이나 필드에 대한 재명명

- 긴 이름을 줄이거나 동일 이름이 여러 개 존재할 경우 다른 이름 사용

- 하나의 질의를 처리하는 동안만 일시적으로 사용

​

예) 학생들의 이름과 소속 학과 이름을 검색

select student.name, department.dept_name
from   student, department
where  student.dept_id = department.dept_id

   ⬇⬇⬇⬇⬇

 

select  s.name, d.dept_name
from    student s, department d
where   s.dept_id = d.dept_id

동일 테이블이 두 번 사용되는 경우(self-join)

​

예) '김광식' 학생과 주소가 같은 학생들의 이름과 주소를 검색

select  s2.name
from    student s1, student s2
where   s1.address = s2.address and s1.name = '김광식'

 

LIKE 연산자

문자열에 대해서 일부분만 일치하는 경우를 찾아야 할 때

- '=' 연산자 대신 'like' 연산자를 이용

- '=' 는 문자열이 정확히 일치할 경우에만 참(true)

 

형식 : where <필드 이름> like <문자열 패턴>

◽ <필드 이름>이 <문자열 패턴>을 따르는지 여부를 검사

문자열 패턴 구성요소

◽ _ : 임의의 한 개 문자에 대응

◽ % : 임의의 여러 개의 문자들에 대응

​

예) 김씨 성을 가진 학생들의 정보를 찾는 질의

select *
from   student
where  name like '김%'