SQL을 활용한 데이터 정의와 조작

SQL(Structured Query Language)

 

- 관계형 데이터베이스를 위한 데이터 언어

- 관계형 데이터베이스의 데이터 정의, 조작, 제어를 위해 사용하는 언어

- 관계형 데이터베이스와 사용자와의 매개

 

MySQL 서버와 클라이언트 프로그램을 별도로 설치해줘야 한다.

(난 맥을 사용하기 때문에 클라이언트 프로그램 설치하지 않고 terminal에 명령어 입력해주면 된다.)

 

- MySQL 설치 => MySQL 서버 설치(관계형 데이터베이스 + DBMS)

- 클라이언트 프로그램 따로 설치

 

 

SQL 명령어 분류

 

DDL은 데이터 구조, 스키마 정의

-> DML은 준비된 스키마 기반으로 데이터 삽입/삭제/변경/조회

SQL 명령어 분류	명령어	설명
데이터 정의어(DDL)	CREATE ALTER DROP RENAME	- 테이블 구조 및 제약조건(즉, 스키마)를 정의 - 테이블 또는 데이터 구조를 생성, 변경, 삭제 등
데이터 조작어(DML)	SELECT	데이터베이스로부터 조건에 맞는 데이터를 조회 또는 검색 (Retrieve 또는 Query)
	INSERT UPDATE DELETE	데이터베이스에 저장된 데이터 변경 - 테이블에 새로운 투플 삽입 - 데이터 삭제 - 데이터 갱신(수정)
데이터 제어어(DCL)	GRANT REVOKE	- 데이터베이스 접근에 대한 권한 부여/회수 - 테이블 또는 객체들에 대한 사용 권한 부여/회수
트랜잭션 제어어(TCL)	COMMIT ROLLBACK	DML에 의한 데이터 조작 연산 묶음을 하나의 논리적인 작업 단위(트랜잭션)로 관리 및 제어

 

 

SQL 표준과 다양한 관계형 데이터베이스

 

데이터베이스	SQL 표준 준수 정도	용도	특징
Oracle	중간	기업 내부 정보 관리	- 2023 3월 기준 점유율 1위 - 기업 정보 분석 보고서를 위한 분석 기능 충실
MySQL (MariaDB)	낮음	연구 및 개발 웹과의 연동	- 2023 3월 기준 점유율 2위 - 연구 및 개발에 주로 집중 - 특히 웹과의 연동에서 강점 -> API stack (Linux 또는 Windows+Apache+PHP+MySQL) - 개발을 주된 목적으로 설계된 DB -> Oracle 대비 부족한 세부 분석 기능은 프로그래밍 단에서 처리한다는 접근법 - 필수적인 SQL 표준은 문제 없이 지원하며, 성능 튜닝 용이
PostgreSQL	높음	연구 및 개발	- SQL 표준을 최대한 준수 - 개발자 선호도 높음 - 아직은 MySQL보다 낮은 점유율 - 특정 쿼리에서는 MySQL 대비 성능이 낮음

 

=> MySQL은 SQL 표준 준수 정도는 낮지만, 좋은 특징을 갖고 있어 우리가 사용할 것임.

 

SQL 문법 기본 사항

 

- 각 구문 마지막에 세미콜론 필수

 

- SQL 문법에서의 기본자

* .sql 파일을 읽어들일 때: space 또는 tab 사용(섞어서 사용도 가능)

but, CMD에서 해당 파일의 코드 일부를 복사/붙여넣기: tab 문자 붙여 넣을 때 에러 발생 가능

=> .sql 파일을 작성하든 CMD를 사용하든 구분자는 space로 통일 권장

 

- 대소문자 구분 관련

* 문자열 데이터의 대소문자는 반드시 구분

* SQL 명령어는 대소문자 구분 중요하지 않음(해도 그만, 안 해도 그만)

* 테이블명/컬럼명은 특별한 이유 없으면 대문자/소문자로 통일

 

- 예약어를 테이블명/컬럼명으로 사용할 수 없음

 

릴레이션 스키마 및 관계 대수: 데이터베이스로의 구현

 

 

"CREATE database" => 사용할 데이터베이스 생성

"USE company" => company 라는 데이터베이스 사용할 것이라고 선언 -> 이제 여기에 저장됨

"CREATE table" => 릴레이션(테이블) 스키마 -> 구조와 제약조건 저장

"DML 변경 구문" => insert, update, delete

"DML 조회 구문" => select, 관계대수의 기본 연산들을 구현해놓음

 

데이터 타입

 

- 도메인 타입: SQL에서 지원하는 속성값의 범주(개념적, 추상적)

- 데이터 타입: 각 DBMS에서 도메인 타입을 실제로 구현한 것

=> 도메인 타입은 그냥 이런 타입을 지원한다~를 얘기하는 것이지만,

우리가 실제로 써줘야 하는 것은 데이터 타입이기 때문에 여기에 주목할 것 !!

도메인 타입

우리는 빨간색을 써줘야 한다는 것이다.

대문자로 써도 되고, 소문자로 써도 되지만 통일하자 !

 

문자열 타입

문자열 데이터 타입

 

CHAR(4) = 길이가 4인 고정길이 문자열

=> 길이가 4 미만이면 패딩하여 채우고, 4 초과되면 4만큼만 잘라 쓴다.

CHAR과 VARCHAR은 다르다고 인식될 수도 있어서 특별한 케이스가 아니라면 VARCHAR 타입 사용하자.

 

정수형 타입

 

정수형 데이터 타입

실수형 타입

 

- DECIMAL/NUMERIC: d 자리의 소수점을 갖는 총 p자리의 유효숫자의 실수

- FLOAT: 소수점 아래 약 7자리(32bit)

- DOUBLE: 소수점 아래 약 15-16자리(64bit)

- FLOAT(n): n자리의 유효숫자

* n<=24 => FLOAT

* n>=25 => DOUBLE

실수형 데이터 타입

날짜형 타입

 

- DATE: 날짜만

- TIME: 시간만

- DATETIME: 사용자의 시간대로 저장

- TIMESTAMP: 서버의 시간대로 저장

날짜형 데이터 타입

Large-Object 타입

 

이미지 저장할 때 주로 쓰임

쿼리 결과 large object가 반환될 경우, large objet 자체가 아니라 포인터가 반환됨

 

- BLOB

: 도메인 타입 binary large object

: 바이너리 파일(이미지, 동영상, 오디오, 각종 파일 등) 저장

: 저장만 가능(DB에서 직접 컨텐츠를 보거나 다룰 수 없음)

-> 저장과 관련된 전/후 처리는 연동된 애플리케이션에서 수행

 

- TEXT

: 도메인 타입 character large object

: VARCHAR보다 큰 텍스트 저장(XML, JSON, 문서 등)

: 저장 기능(그 이상의 해석 또는 분석을 DB가 하지 않음)

: LIKE 연산자 등의 기본적인 문자열 검색 기능 사용 가능

=> 지금 당장은 하지 않을 것임 !!

 

Large-Object 데이터 타입

사용자 정의 데이터 타입

 

MySQL에서는 지원하지 않음

 

 

DDL(Data Definition Language)

 

릴레이션에 대하여 다음을 명세 또는 정의: 

- 각 릴레이션의 스키마

- 각 애트리뷰트가 가질 수 있는 값이 범위

- 키

- 제약조건

- 인덱스

- 물리적 저장 구조

 

테이블 생성: CREATE TABLE 구문

 

<구조>

create table r (
	ATTRIBUTE_1 DATATYPE_1, //데이터 타입 정의
    ATTRIBUTE_2 DATATYPE_2,
    ...,
    ATTRIBUTE_n DATATYPE_n,
    (CONSTRAINT_1), //제약조건
    (CONSTRAINT_2),
    ...
    (CONSTRAINT_k)
); //세미콜론 붙여줘야 함

 

<예시>

create table instructor (
	ID char(5),
    name varchar(20),
    dept_name varchar(20),
    salary numeric(8, 2)
);

 

 

<제약 조건>

- 기본키: primary key 명시

* tip) 밑줄친 것 모두 기본키 -> 복합 기본키이면 모두 적어주면 됨

- 외래키: foreign key 명시(참조 관계 있는 것 다 명시해주면 됨)

* tip) 나가는 화살표가 모두 외래키

- 기초적인 무결성(값의 존재) 확보: 꼭 있어야 하는 것은 "not null"

create table instructor
	(ID	varchar(5),
    name 	varchar(20) not null,
    dept_name	varchar(20),
    salary	 numeric(8, 2),
    primary key (ID),
    foreign key (dept_name) references department (dept_name)
);

create table student
	(ID	varchar(5),
    name	varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3, 0),
    primary key (ID),
    foreign key (dept_name) references department (dept_name)
);

 

 

create table takes
	(ID	varchar(5),
    course_id	varchar(8),
    sec_id	varchar(8),
    semester varchar(6),
    year	numeric(4, 0)
    grade	varchar(2),
    primary key (ID, course_id, sec_id, semester, year),
    foreign key (course_id, sec_id, semester, year) references section(couse_id, sec_id, semester, year),
    foreign key (ID) references student (ID)
);

create table course
	(course_id	varchar(8),
    title	varchar(50),
    dept_name	varchar(20),
    credits	numeric(2, 0),
    primary key (course_id),
    foreign key (dept_name) references department (dept_name)
    );

 

 

*** 테이블 생성 순서: 참조 당하는 테이블이 참조하는 테이블보다 먼저 생성되어야 함 ***

ex. student 테이블보다 department 테이블이 먼저 생성되어 있어야 함..

 

따라서 테이블 생성 순서를 번호로 매기면 다음과 같다.

tip)

나가는 화살표가 없으면 1번

-> 1번을 참조하는 것 2번(단, 다른 참조관계도 참고해서 번호 잘 매기면 된다.)

-> 최대한 빠른 번호 지정할 것.

 

 

<편의 제공 조건들>

: 제약조건은 아니고, 편의를 제공하는 조건일 뿐

 

- DEFAULT

: 속성의 디폴트값 지정(초기값이 null로 세팅되면 나중에 문제가 생길 수 있기 때문)

create table t1 (
	i int DEFAULT -1,
    c varchar(10) DEFAULT 'c',
    price numeric(16, 2) DEFAULT 0.00
); //-1 c 0.00

insert into t1 (i) values (0); //0 c 0.00
insert into t1 (c) values ('a'); //-1 a 0.00
insert into t1 (price) values (30.00); //-1 c 30.00

 

- AUTO_INCREMENT

: 자동으로 번호 매겨주기

: 유일한 정수 값을 갖는 단일 속성에 대해서 지원

: 주로 단일 기본키와 함께 사용

create table animals (
	id MEDIUMINT NOT NULL AUTO_INCREMENT,
	name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

insert into animals (name) values ('dog');
insert into animals (name) values ('cat');
insert into animals (name) values ('penguin');
insert into animals (name) values ('lax');
insert into animals (name) values ('whale');
insert into animals (name) values ('ostrich');

select * from animals;