Database

SQL 개요 및 DDL

정코딩딩 2024. 4. 4. 19:35

이번에 SQL 개요 및 DDL 에 대해서 알아보자. 자세한 설명은 아래에서 하겠다!

SQL(Structed Query Language)

의미에서도 볼 수 있듯이 구조화된 질의어이다.

자연어로 “학년이 3학년인 학생들의 학번과 이름을 알려줘” 라고 할 수 없으니까 구조화된 질의어를 만들었고

예를들면, "SELECT sno, sname FROM student WEHRE syear=3; "이런식으로 작성할 수 있다.

종합 데이터베이스 언어 역할을 한다. 즉, 데이터 베이스와 인터렉트 할 수 있는 언어라는 것을 의미한다.

단순히 검색만을 위한 데이터 질의어가 아니다. 테이블의 구조를 정하거나, 삽입, 삭제, 갱신과 같은 조작, 혹은 제어 와 같이 다양한 작업을 처리할 수 있는 그런 언어로 설계되어있다.

데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL) 등의 기능을 모두 제공한다.

테이블을 생성할 때에는 CREATE, 테이블을 변경하고 싶을 때에는 ALTER, 삭제하고 싶을 때에는 DROP, 테이블의 구조만 남기고 투플을 다 삭제하고 싶을 때에는 TRUNCATE, 테이블의 구조를 보고 싶을 때 DESCRIBE, 테이블의 이름을 변경하고 싶을 때 RENAME 을 사용한다. 사용자의 권한을 부여하는 GRANT, 권한을 다시 뺏어오는 REVOKE 연산도 있다.

SQL 의 특징

  1. 이전 포스트에 있는 관계 대수에 기초하고 있다. 관계 대수는 high level 은 아니었는데, 왜냐면 시퀀스가 있어야했고 알고리즘적인 요소가 있어야했다.
  2. 그런데 SQL 은 비절차적 데이터 언어이기 때문에 연산의 순서에는 전혀 관계가 없다. 그래서 non-procedural 한 언어라고 한다.
  3. 그리고 선언적 데이터 언어이다. 단순히 한문장만 작성하더라도 그 명령어를 돌릴 수 있다. SQL 명령문에는 데이터 처리를 위한 접근 경로에 대한 명세가 불필요하다.
  4. 또한, 사용자 친화적인 인터페이스 제공한다.
  5. 표준화가 되어있어서 상용 RDBMS 간의 전환이 용이하고, 여러 관계 데이터베이스를 접근하는 데이터베이스 응용 프로그램의 작성을 지원한다. 표준이 되어있다고는 하지만..mysql 방언.. 오라클에서만 지원하는 익스텐션이 있기도하다..!
  6. 터미널을 통해 대화식 질의어로 사용 가능하다. workbench 라는 어플을 사용해서 확인 가능하다!
  7. 매번 대화식 질의어로 하기에는 어려움이 있으니까 응용 프로그램에 삽입된 형태로도 사용가능하다. Java, python, PHP, C, C++ 등과 같은 범용 프로그래밍 언어로 개발된 응용 프로그램에 삽입해서 사용한다.
  8. 개개의 투플 단위가 아닌 “집합(테이블) 단위”로 처리한다.
  9. 관계 대수와 유사하게 입력이 테이블이고 출력도 테이블인 연산에 기초하고 있다.

우리가 사용하는 범용 프로그래밍 언어는 기본적으로 변수 단위로 처리하지, SQL 처럼 집합 단위로 처리하지 않는다.

그래서 SQL 연산을 하고 나서는 차례식 루프를 돌면서 값들을 가져와야하는 방법론이 생기게 된다.

SQL 데이터 정의문

한 기관이 관리하는 모든 데이터는 database 라는 가장 큰 논리적 단위에 저장한다.

그래서 a database == a set of tables 이고,

a table == a set of tuples 이라고 할 수 있다.

 

데이터 베이스 생성문 : CREATE DATABASE dbname

데이터 베이스 삭제문 : DROP DATABASE dbname

이렇게 작성할 수 있다.

테이블의 생성

테이블의 종류에는 기본 테이블과 가상 테이블이 있는데, 대부분은 기본테이블로 만들게 된다.

기본 테이블은 CREATE TABLE 문으로 만들어지는 테이블이다. DBMS 의 화일로 물리적으로 생성되고 저장된다.

가상 테이블은 CREATE VIEW 문으로 만들어지는 테이블이다. 어떤 기본 테이블로부터 유도되어 만들어지는 테이블이고 물리적으로는 존재하지 않는다.

가상 테이블은 뒤에서 다시 말해보자.

 

기본 테이블을 생성하기 위한 일반적인 형식은 다음과 같다.

  • PRIMARY KEY : 기본 키와 개체 무결성 제약 조건을 명세한다.
  • UNIQUE : 대체키를 명세한다. 후보키이다.
  • NOT NULL : 애트리뷰트 값 제약조건
  • FOREIGN KEY : 외래 키와 참조 무결성 제약조건을 명세한다. SQL에서는 참조된 테이블의 후보 키도 외래 키로 될 수 있다. 
    옵션에는 SET NULL, SET DEFAULT, CASCADE 가 있다. 예를들면, "ON DELETE SET NULL" : 참조 투플이 삭제되면 NULL 로 설정, "ON UPDATE CASCADE" : 참조 투플이 갱신되면 갱신된 값이 파급적으로 갱신된다!
  • CHECK : 제약조건을 명세해준다.

데이터 타입

대표적인 데이터 타입에는 숫자, 문자 스트링, 날짜, 시간 등이 있다.

  • 숫자
    • INT, INTEGER, SAMLLINT, BIGINT : 정수
    • FLOAT(n), REAL, DOUBLE : 부동소숫점 숫자
    • DECIAML(i,j), NUMERIC(i,j) : 고정소숫점 숫자 / i는 총 자리수, j는 i-j 를 소숫점 아래에 적어둔다.
      네이버와 같은 곳에서 지도를 표시할 때 위도, 경도로 표현한다고 하는데 위도를 보통 decimal(10,7), 경도도 deciaml(10,7) 라고 표현한다고 한다.
  • 문자 스트링
    • CHAR(n) : 고정 길이 문자
    • VARCHAR(n) : 가변 길이 문자
  • 날짜
    • DATE : YY-MM-DD ex) ‘2024-04-01’
    • DATETIME ex) ‘2024-04-19 19:32:50’
  • 시간
    • TIME : hh:mm:ss ex)’14:30:00’TIMESTAMP ex)’2024-04-04 15:45:30’
    • INTERVAL : day, hour, minute, second ex)’1 day’

자 이제 위에서 말한 것들에 대한 예시를 봅시다.

이렇게 생긴 테이블을 데이터베이스에 생성하고 싶다면 다음과 같이 작성 가능하다.

CREATE TABLE STUDENT(
	SNO integer primary key,
	SNAME varchar(10) not null,
	YEAR int not null default 1,
	DEPT varchar(5),
	// 여기부터는 위의 테이블에 대한 내용이 아니라 db 에 저장하기 위한 설정 사항들
	ENTER_DATE datetime default now() on update now(),
	constraint YEAR_CONST check(year >= 1 and year <= 4)
)
CREATE TABLE COURSE(
	CNO varchar(4) primary key,
	CNAME varchar(10) not null,
	CREDIT int not null default 3,
	DEPT varchar(10) not null,
	PRname varchar(10) not null,
	// 여기부터는 위의 테이블에 대한 내용이 아니라 db 에 저장하기 위한 설정 사항들
	ENTER_DATE datetime default now() on update now()
)

등록 테이블은 위의 두 테이블과 조금은 다른 것이 학생과 과목을 이어주는 테이블이라는 점! 

CREATE TABLE ENROL (
	SNO int,
	CNO char(4),
	GRADE char(1),
	MIDTERM int,
	FINAL int,
	ENTER_DATE datetime default now()
	ON UPDATE now(),
	
	PRIMARY KEY (SNO, CNO),
	FOREIGN KEY(SNO) references STUDENT(SNO)
		ON UPDATE CASCADE
		ON DELETE NULL,
	FOREIGN KEY(CNO) references COURSE(CNO)
		ON UPDATE CASCADE
		ON DELETE NULL,
	CHECK (GRADE >='A' AND GRADE <= 'F')
)

기본 테이블의 제거

테이블을 삭제하는 것은 간단하다. 일반적인 형식은 다음과 같다.

DROP TABLE 기본_테이블_이름 {RESTRICT|CASCADE};

RESTRICT 는 참조하는 뷰나 외래키 제약조건이 있으면 실행 실패하라는 조건이고,

CASCADE 는 참조하는 뷰나 외래키 제약조건도 모두 삭제하라는 조건이다.

기본 테이블의 변경

기본 테이블의 애트리뷰트를 변경하기 위해 사용한다.

ALTER TABLE 기본_테이블_이름
([ADD 열_이름 데이타_타입] [DEFAULT 기정의 값] | 
	[DROP 열_이름] [CASCADE|RESTRICT] |
	[MODIFY 열_이름 데이타_타입 [DEFAULT 값]);
alter table student add column email varchar(100);
alter table student modify column email 
	varchar(100) default 'abc@konkuk.ac.kr’;
alter table student drop column email;

일반적인 형식을 가지고 위의 세개의 예시처럼 사용할 수 있다.