Goals:

• SQL의 기본 개념을 이해하고, 그 종류를 설명할 수 있다. (Understanding the basic concepts of SQL and being able to explain its types)

• 데이터베이스의 물리적 구조를 설명할 수 있다. (Explaining the physical structure of a database)

• SQL을 사용하여 데이터베이스와 테이블을 생성할 수 있다. (Being able to create databases and tables using SQL)

Contents:

• SQL 개요(SQL Overview)

  • 정의 (Definition)

  • 특징 (Characteristics)

  • 명령어 종류(Command)

• 데이터베이스 생성하기(Creating a Database)

• 테이블 생성하기(Creating a Table)

• 데이터베이스 구현 #1 (Implementing a Database #1 ) - 제약조건(Constrains)

• 데이터베이스 구현 #2 (Implementing a Database #2 )

• 학습 정리 (Summary)

  ---

  💡 SQL 개요(SQL Overview)

  📍SQL(Structured Query Language) 정의(definition)

  • 데이터베이스에 접근할 수 있는 데이터베이스 언어이다.

• 비절차적언어(Non procedure language)로 최종 사용자까지 손쉽게 활용함으로써 시스템이 개발되었다.

• 1974년 IBM 연구소에서 발표한 SEQUEL(Structured English Query Language)에서 유래하였다.

• MySQL, DB2, SQL Server, ORACLE, INFORMIX, SYBASE 등 관계형데이터베이스에서 모두 사용하는 언어이다.

📍SQL의 특징 (Characteristics of SQL)

• 자연어와 유사한 언어로 프로그래밍 경험이 별로 없는 사용자도 쉽게 학습(Easy to learn)

• 비절차적언어로 최종 사용자까지 손쉽게 활용함으로써 시스템 개발 및 유지 보수에 소요되는 시간을 줄일 수 있음(Faster query processing)

• 단순히 검색만을 위한 질의어가 아니라 데이터 정의, 조작, 제어기능을 모두 제공(Wide variety of commands)

• ANSI, ISO에서 국제 표준으로 채택

📍SQL의 명령어 종류(Command of SQL)

• 

  DDL (데이터정의어, Data Definition Language)

• • 구조를 생성하거나, 구조 변경, 삭제 등 데이터 구조에 대한 명령어

• DML (데이터조작어, Data Manipulation Language)

  • 가장 활용도가 높은 명령어로서 원하는 데이터를 검색하기 위해 사용하는 명령어

  • 새로운 행을 입력하거나 기존의 행을 수정하고자 할 때, 또는 원치 않는 데이터를 삭제하는 등 데이터를 조작하는 것에 대한 명령어이다.

• DCL (데이터제어어, Data Control Language)

  • 논리적인 작업의 단위로 DML에 의해 조작된 결과를 다루는 명령어이다.

  • 데이터베이스에 접근하는 권한을 부여하고 객체들을 사용하도록 주고 받는 명령어이다.

---

💡데이터베이스 생성하기(Creating a Database)

📍관계형 DBMS 유형

1. DBMS 서버(server): Oracle, MySQL

2. 데이터베이스 A, B, Z = 학교 데이터베이스, 병원 데이터베이스 같은 대집합이다. DBMS서버에는 여러개의 데이터베이스가 존재한다.

3. 스키마(Schema) = '데이터베이스 테이블'(Database Table) 이라는 용어로 쓰이기도 한다. 각각의 데이터베이스에는 여러개의 '테이블'이 존재한다.

📍ORACLE 데이터 저장구조 (Data storage structure)

1. 데이터 파일(Data File)

📍SQL server 데이터 저장구조 (Data storage structure)

• DBMS는 여러 개의 데이터베이스를 동시에 운영한다.

• 하나의 데이터베이스는 데이터 파일(.mdf)과 로그파일(.ldf)로 구성된다.

• 데이터 파일에서 데이터는 페이지(8k)라는 최소의 저장단위에 저장된다.

• 8개의 연속된 페이지 → 익스텐트(Extent) 로그 파일에는 데이터의 변경에 대한 내용이 기록된다.

📍MySQL/Maria DB 저장구조 (storage structure)

2. SHOW DATABASES: "만들어져있는 DB를 보여주어라" 라는 뜻

3. 기본적으로 생성되있는 시스템 데이터베이스

4. 프로그래머가 데이터베이스를 추가하게 되면 그 밑으로 만들어지게 된다. 여러개 만들 수 있다.

📍CREATE DATABASE 명령어 - SQL Server, MySQL/Maria DB

• 하나의 새로운 데이터베이스를 생성하는 것

• 데이터베이스 생성을 위한 기본파일(데이터파일, 로그파일 등)과 함께 관리를 위한 시스템테이블 등을 생성한다. ORACLE만 기본파일이 아닌 "Table space"구조를 사용한다.

• 데이터베이스 삭제하려면 → DROP DATABASE

📍CREATE DATABASE 명령어 실행 - MySQL #1

1. 데이터베이스 객체(object), 혹은 스키마 네비게이터(schema navigator)라고 한다.

2. 명령어 실행 예제

3. 실행 버튼

📍CREATE DATABASE 명령어 실행 - MySQL #2

1. 사진처럼 드래그하고 실행버튼을 누르면 해당 명령어만 실행할 수 있다.

2. 결과

📍CREATE DATABASE 명령어 - ORACLE

• CREATE TABLESPACE 명령어 (위에 언급된 것처럼 오라클은 테이블스페이스로 매핑이 된다.)

• 데이터가 저장될 파일경로와 이름도 함께 지정할 수 있다. (번외로 사이즈가 100 M보다 넘어간다면 자동으로 늘어나도록 설정도 가능하다.)

💡테이블 생성하기(Creating a Table)

📍완성된 테이블 예제

• 데이터베이스는 여러 개의 테이블로 구성된다.

• 애트리뷰트 → 테이블의 컬럼(열), 튜플 → 테이블의 로우(행, 레코드)

📍CREATE TABLE 명령어(command)

제약조건(Constraints) 디테일은 아래의 추가설명참조

Student 테이블의 컬럼은 7개지만 아래의 예제에선 No 에서 길이까지만 다룰예정

실제 MySQL CREATE TABLE 실습

실제 MySQL DROP TABLE 실습

💡데이터베이스 구현 #1 - 제약조건(Implementing a Database - Constrains)

제약 조건은 데이터베이스 스키마에 적용되어 데이터의 무결성을 보장하고 데이터의 정확성과 일관성을 유지하는 역할을 하기 때문에 구현할 때 제약 조건을 명확하게 정의하고 적용하는 것이 중요하다.

📍PRIMARY KEY 제약조건

• PRIMARY KEY = UNIQUE + NOT NULL = 해당 열에 NULL 값을 허용하지 않도록 강제하고 중복되서는 안된다.

• 테이블 내의 각 행을 고유하게 식별하는 열(또는 열의 조합)이고 윗문장 처럼 두가지 제약조건을 동시에 가진다.

• 테이블당 하나만 가질 수 있으며, 자동으로 인덱스가 생성된다.

📍NOT NULL 제약조건

• NULL 값을 허용하지 않겠다는 것을 의미한다.

• 기본키를 제약조건 했다는 뜻은 유일해야 하고, 널값을 허용하지 않으므로 따로 NOT NULL을 쓰진 않았다.

📍DEFAULT 제약조건

• 스칼라 값(문자, 숫자)과 스칼라 함수를 이용할 수 있다. 예: getdate() 함수

• 특정 열에 삽입되는 값이 명시되지 않을 때 사용되는 기본적인 값이다. 이는 해당 열에 값이 입력되지 않을 경우 자동으로 할당된다.

📍CHECK 제약조건

• 도메인 무결성을 강조하기 위한 제약조건이다.

• 삽입되는 데이터를 검사해서 해당되는 영역의 데이터이면 진행하고, 그렇지 않으면 삽입작업을 취소하는 역할을 수행

• True 혹은 False 값을 만족하는 어떤 조건도 포함될 수 있음( True면 허용, False면 비허용)

• IN 조건문 사용 - IN 앞에 나오는 값들 중 있는가 ('M','F')

📍UNIQUE 제약조건

• 중복되서는 안된다는 뜻. 만약 UNIQUE 만 있다면 중복만 안되면된다. (NULL 값 허용)

• 테이블에 여러 개가 존재할 수 있음

• 예: 휴대전화번호는 없을수도 있지만 중복되면 안된다

📍FOREIGN KEY 제약조건 #1 - 테이블 정의 시

• 부모 테이블의 기본키 값에 대한 참조 무결성을 유지하고 자식 테이블 간의 관계를 유지한다.

• 💡참조하는 테이블(컬럼)에 데이터가 삽입될 때에는 항상 참조되는 테이블(컬럼)에 있는 값으로 들어가야 함

📍FOREIGN KEY 제약조건 # - 제약조건 추가(ALTER TABLE)

• ALTER TABLE 용도

  1. 테이블에 이미 데이터가 존재하는 경우: 테이블에 이미 데이터가 존재하는 경우에는 FOREIGN KEY 제약 조건을 추가할 수 없다. 이 경우에는 ALTER TABLE 명령어를 사용하여 FOREIGN KEY를 추가한다.

  2. 기존 테이블에 외래 키를 추가해야 하는 경우: 기존의 테이블에 외래 키 제약 조건을 추가해야 할 때에도 ALTER TABLE 명령어를 사용하여 FOREIGN KEY를 추가할 수 있다.

  3. 외래 키 제약 조건을 재정의해야 하는 경우: 기존의 외래 키 제약 조건을 수정하거나 새로운 외래 키 제약 조건을 추가해야 하는 경우에도 ALTER TABLE 명령어를 사용하여 FOREIGN KEY를 추가할 수 있습니다.

  4. 테이블 간의 관계를 설정하는 경우: 다른 테이블과의 관계를 설정하기 위해 외래 키 제약 조건을 추가해야 하는 경우에도 ALTER TABLE 명령어를 사용하여 FOREIGN KEY를 추가할 수 있습니다.

• ALTER TABLE 주의사항

  1. ALTER TABLE 명령어를 사용하여 FOREIGN KEY 제약 조건을 추가하는 경우, 데이터의 무결성이 깨지지 않도록 주의해야 한다.

  2. 무결성 검사: ALTER TABLE 명령어를 실행하기 전에 데이터베이스 시스템은 새로운 FOREIGN KEY 제약 조건이 추가될 때 이를 만족시키는지 여부를 확인합니다. 즉, 새로운 외래 키가 참조하는 테이블에 해당하는 값이 존재하는지, 또는 NULL 값을 허용하는지 등을 검사한다.

  3. 동작 방식 지정: ALTER TABLE 명령어를 사용하여 FOREIGN KEY 제약 조건을 추가할 때, 데이터베이스 시스템은 이를 어떻게 처리할지 결정한다. 기존의 데이터에 영향을 미치는지, 추가되는 제약 조건에만 영향을 미치는지 등을 고려하여 적절한 동작 방식을 선택한다.

  4. 일반적으로 데이터베이스 시스템은 무결성 검사를 통과하지 못하는 경우에 에러를 발생시킨다.

• ON DELETE SET NULL

  • 외래 키 제약 조건에 ON DELETE SET NULL 옵션을 설정하면, 참조되는 테이블의 값이 삭제될 때 해당하는 외래 키 값을 널(Null)로 설정한다. 이 방법을 선택하면 참조되는 값이 삭제될 때 관련된 행의 외래 키 값이 자동으로 널로 설정되어 참조 무결성을 유지할 수 있다.

• ON DELETE CASCADE

  • 외래 키 제약 조건에 ON DELETE CASCADE 옵션을 설정하면, 참조되는 테이블의 값이 삭제될 때 해당하는 외래 키를 가지고 있는 모든 행이 자동으로 삭제된다. 이 방법을 선택하면 참조되는 값이 삭제될 때 관련된 행이 함께 삭제되어 데이터의 일관성을 유지할 수 있다.

📍테이블 변경 명령어(ALTER TABLE command)

• ALTER TABLE 명령어를 이용해서 테이블 구조 변경할 수 있다.

• 컬럼 추가 혹은 삭제, 컬럼 타입 변경, 컬럼명이나 테이블명을 변경 등

📍테이블 삭제 명령어(DROP TABLE command)

• DROP TABLE 명령어를 이용해서 테이블을 삭제할 수 있다.

• 테이블의 모든 데이터와 함께 테이블이 삭제된다.

• (참고) TRUNCATE TABLE 명령어는 테이블은 남겨놓고 내용만 삭제하는 명령어

📍컬럼의 추가(ADD column)

• 항상 기존의 열 끝에 추가된다 (물리적인 개념)

• NOT NULL 이면서 기본 값도 없는 열은 추가할 수 없다.

• NULL로 설정하거나 기본값을 정의해야 한다.

📍컬럼의 수정(ALTER column)

• 기본값, 체크 등의 제약조건이나 개체 기본값, 규칙 또는 인덱스가 설정된 열은 수정불가

• 데이터형의 수정은 호환이 있는 것으로 해야 함

• 숫자형의 경우 작은 크기로 수정할 수 없으며, 기존의 열에 들어있던 가장 긴 문자열보다 길이가 짧은 문자 데이터형으로 수정불가

• 처음이므로 삭제, 추가위주로 연습하는 것을 추천

📍컬럼의 삭제

• 기본값, 체크 등의 제약조건이나 개체 기본값, 규칙 또는 인덱스가 설정된 열은 삭제불가함

• 제약조건을 없애고 삭제해야 함

• 열 삭제는 데이터를 영구적으로 없애므로, 반드시 백업한 후에 수행해야 함(사용시 조심해야함)

---

💡데이터베이스 구현 #2 (Implementing a Database)

1. student 테이블 정의서

2. SQL문 작성 및 실행

---

1. course 테이블 정의서

2. SQL문 작성 및 실행 - NULL에 Y가 없으면 NULL 값 허용 안한다는 뜻(NOT NULL)

---

1. 

   주어진 enroll 테이블 정의서

2. 

   SQL문 작성 및 실행

---

위의 작업을 마친뒤 SHOW TABLES 명령어로 완성된 테이블을 확인할 수 있다.

---

학습 정리 (Summary)

---

SQL 특징(SQL) 추가 설명(further information)

절차적언어(procedure language) C언어, 자바, 파스칼, 파이썬은 절차적인언어로써 컴퓨터가 해야할 일들을 구체적으로 절차적으로 적어줘야 한다.

데이터베이스 구현 #1(database implantation) 추가설명(further information)

💡외래키 제약조건 (Foreign Key constraints)테이블에 이미 데이터가 존재하는 경우에 FOREIGN KEY 제약 조건을 추가하는 것이 어려운 이유

• 데이터 무결성 문제: 이미 존재하는 데이터와 외래 키 제약 조건을 추가하려는 데이터 간에 무결성이 깨질 수 있다. 외래 키 제약 조건은 해당 열의 값이 다른 테이블의 기본 키나 유일한 키 값에 해당해야 한다는 제약을 가지고 있기 때문에, 이미 존재하는 데이터와 이러한 제약을 만족시키는 것은 어려울 수 있다.

• 참조 무결성 위반: 이미 존재하는 데이터와 외래 키 제약 조건을 추가하려는 경우, 참조하는 테이블에 해당하는 값이 없거나 잘못된 경우가 발생할 수 있다. 이는 참조 무결성을 위반하는 상황이 된다.

• 성능 문제: 이미 존재하는 대량의 데이터에 대해 외래 키 제약 조건을 추가하는 경우, 이 작업이 수행될 때까지 시간이 오래 걸리고 성능 문제가 발생할 수 있다.

• 해결법(Solution) 데이터를 추가하고 난 후에 외래 키 제약 조건을 추가하는 것이 일반적으로 더 안전하고 효율적인 접근 방법이다.