Chap4. SQL

질의어와 SQL

• 질의어
  : 사용자가 DB에 존재하는 정보를 요구하기 위해 사용하는 언어.
  (ex. 관계대수 - 절차적 언어)

• SQL(Structured Query Language)
  : 자연어에 가까우며 질의의 수행 절차를 자세히 명시할 필요가 없도록 비절차적 요소가 많이 포함된 언어.

• SQL이 제공하는 기능

  • 데이터 정의 언어 (Data Definition Language)
    : DB에 의해 사용되는 데이터 저장 구조를 명시하는 언어.
    테이블을 생성, 수정, 삭제하는 명령문을 제공.
    
    
  • 데이터 조작 언어 (Data Manipulation Language)
    : 사용자가 데이터에 접근하고 조작할 수 있게 하는 언어.
    DB에 저장된 정보의 검색 이외에 레코드의 삽입, 삭제, 수정과 같은 명령문 제공.

데이터 정의 언어

: DB에 의해 사용되는 데이터 저장 구조를 명시하는 언어. 테이블을 생성, 수정, 삭제하는 명령문.

• 많이 사용되는 SQL 데이터 타입
  

테이블 생성

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

ex. department 테이블 생성

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

// not null은 해당 필드에 null을 허용하지 않는다는 의미.
// 필드리스트에는 <필드명>과 <데이터 타입>이 들어가야 한다.

기본키, 외래키 설정

기본키

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

방법1.

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

방법2.

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

단, 이름부여가 생략되어 제약조건 이름이 임의로 부여된다.

+)기본키로 정의된 필드는 자동으로 널을 허용하지 않는다.

++) primary key(stu_id, class_id)
이처럼 기본키가 2개 이상일 때는 방법2가 불가하다.

외래키

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

방법1.

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

방법2.

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) foreign key(dept_id) references department(dept_id),
	constraint pk_student primary key(stu_id),
);

테이블 삭제

drop table <테이블 이름>

※주의
다른 테이블에서 외래키로 참조되는 필드를 포함하는 경우에는 삭제 불가능

테이블 수정

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

• 추가

  alter table <테이블 이름> add <추가할 필드>

ex.

SQL> alter table student add age int;

• 삭제

  alter table <테이블 이름> drop column <삭제할 필드이름>

  ex.

SQL> alter table student drop column age;

기본키, 외래키 관련 주의사항

error : 테이블 또는 뷰가 존재하지 않습니다.

; 외래키를 필드로 갖는 테이블을 생성하고자 할 때는 외래키가 참조하는 테이블이 먼저 생성되어 있어야 한다.

+) 삭제할 때도 마찬가지로 외래키가 참조하는 테이블을 먼저 삭제해야 한다.

데이터 조작 언어

: 생성된 테이블에 레코드를 삽입하거나 삽입된 데이터를 수정, 삭제, 검색하는 기능을 수행하는 SQL문.

레코드 삽입

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

ex.

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

• <값리스트>에는 <필드리스트>의 순서에 맞춰 삽입될 값을 나열한다.

• <필드리스트>에 나열되지 않은 필드에 대해서는 널이 들어간다. 그러므로 널이 허용되지 않는 필드는 꼭 필드리스트에 나와야 한다.

• <필드리스트>를 생략할 경우, <값리스트>는 테이블 생성 시 필드를 나열한 순서대로 값을 나열해야 한다.

• 필드 이름을 나열할 경우 그 순서는 테이블을 생성할 때 지정한 순서와 반드시 일치할 필요는 없다.

//Run SQL Command Line을 이용하다가 sqldeveloper를 이용해보았다.
메모장에서 코드를 긁어오거나 파일을 그대로 불러올 수 있는 점이 편하다.

레코드 수정

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

ex. professor테이블에서 '고희석'이란 교수의 직위를 '교수'로, 학과번호를 '923'으로 수정

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

• 대상 테이블의 모든 레코드에 대해 수정을 적용하려면 where절을 생략

TIP) 문자열 타입을 Date타입으로 변환
: TO_DATE('2020-10-17', 'YYYY-MM-DD')

레코드 삭제

delete from <테이블이름>
where <조건>

ex. professor테이블에서 이름이 '김태석'인 교수 삭제

delete	from professor
where	name='김태석'

※테이블의 모든 레코드를 삭제하더라도 테이블은 삭제되지 않고 남아있다. (테이블 삭제는 drop table)

기본키, 외래키 관련 주의사항

error : 무결성 제약조건이 위배되었습니다 - 부모키가 없습니다

1. 어떤 테이블에서 외래키로 사용되는 필드에 대해 데이터를 삽입할 경우 외래키가 참조하는 테이블의 해당 필드에 그 값이 먼저 삽입되어 있어야 한다. (수정도 마찬가지)

2. 다른 테이블에서 외래키로 참조되는 필드를 가지고 있는 테이블에서 레코드를 삭제할 경우에도 오류가 발생한다.

레코드 검색

(내용이 많아서 따로 글 씀)
https://velog.io/@continue_deve/Chap-4.-SQL-3.5-%EB%A0%88%EC%BD%94%EB%93%9C-%EA%B2%80%EC%83%89

-----------------------------여기까지가 중간고사-------------------------------------

뷰

: 기존 테이블들로부터 생성되는 가상의 테이블.

• 물리적 생성 X
  : 기능과 역할을 테이블과 비슷하지만 테이블처럼 물리적으로 생성되진 않음.

• 기존 테이블의 조합
  ; 기존의 테이블들을 조합하여 실제로 존재하는 테이블인 것처럼 보임.

• 보안효과
  : 사용시 특정 사용자에게 테이블의 내용 중 일부를 숨길 수 있음.

• 질의를 단순하게
  : 복잡한 질의의 결과를 뷰로 만들어 사용하면 질의가 훨씬 단순해짐.

뷰 생성

create or replace view <뷰이름> as
<select문>

• or replace
  : <뷰이름>과 같은 뷰가 이미 존재하는 경우 기존의 뷰를 지우고 새로 생성.

권한 부여

grant create view to <사용자 계정>

• 사용자 계정에는 일반적으로 뷰 생성 권한이 부여되지 않기 때문에 필요하다.

실행 예

takes테이블에서 grade필드를 제외한 뷰

create or replace view v_takes as
select stu_id, class_id
from takes

student테이블에서 컴퓨터공학과 학생들 레코드만 추출한 뷰

create or replace view cs_student as
select s.stu_id, s.resident_id, s.name, s.year, s.address, s.dept_id
from student s, department d
where s.dept_id = d.dept_id and d.dept_name = '컴퓨터공학과'

뷰 사용

: 테이블과 비슷하게 사용된다.

• 생성된 뷰에 대해서 SQL문을 실행시킬 수 있다.

select	*
from	v_takes
where	stu_id = '1292502'

• 뷰에 insert, update, delete문(DML)을 실행시키면 뷰 생성에 사용된 테이블에 대해 결과가 적용된다.

insert into	v_takes
values	('1292502', 'C101-01')

• 뷰를 생성할 때 맨 마지막에 with read only 키워드를 넣어주면 DML의 사용이 불가하다.

• 조인된 뷰는 대부분 DML의 사용이 어렵다.

뷰 삭제

drop view <뷰이름>