SQL - 데이터베이스 구축

03-01 데이터베이스 필수 용어

 

각 SQL 관련 용어에 대해 간단한 설명을 제공하겠습니다:

• 데이터 (Data):
  데이터는 정보의 단위로, 숫자, 텍스트, 이미지, 비디오 또는 기타 형식으로 저장될 수 있는 정보의 집합입니다.
• 테이블 (Table):
  테이블은 데이터베이스 내에서 구조화된 형태로 데이터를 저장하는 방법입니다. 테이블은 행과 열의 그리드로 구성되며, 각 행은 레코드를 나타내고, 각 열은 필드 또는 속성을 나타냅니다.
• 데이터베이스 (Database):
  데이터베이스는 관련된 데이터의 모음을 구성하며, 이러한 데이터는 테이블, 뷰, 저장 프로시저 및 다른 객체를 포함하는 시스템입니다. 데이터베이스는 데이터를 구조화하고 효과적으로 관리하는 데 사용됩니다.
• DBMS (Database Management System):
  DBMS는 데이터베이스를 관리하고 조작하는 소프트웨어 또는 시스템입니다. DBMS는 데이터베이스 생성, 수정, 검색, 삭제 및 보안 관리를 담당하며 사용자와 데이터베이스 간의 상호 작용을 지원합니다.
• 컬럼 (Column):
  컬럼은 데이터베이스 테이블에서 수직으로 정렬된 데이터 요소의 열을 나타냅니다. 각 컬럼은 데이터베이스 내의 특정 속성이나 정보 조각을 저장합니다.
• 열이름 (Column Name):
  열이름은 테이블의 컬럼에 대한 고유 식별자이며, 각 컬럼을 식별하고 해당 데이터를 참조하는 데 사용됩니다. 열이름은 대개 테이블 정의를 포함하는 SQL 문에서 정의됩니다.
• 데이터형식 (Data Type):
  데이터형식은 각 컬럼에 저장될 수 있는 데이터의 유형 또는 형식을 나타냅니다. 예를 들어, 정수, 문자열, 날짜, 시간 등과 같은 다양한 데이터 형식이 있으며, 이러한 형식은 데이터의 유효성을 유지하기 위해 사용됩니다.
• 기본 키열 (Primary Key Column):
  기본 키열은 테이블 내에서 각 레코드를 고유하게 식별하는 열입니다. 기본 키열은 중복 값을 허용하지 않고 검색 및 관리 목적으로 사용됩니다. 주로 기본 키로는 숫자, 고유 ID 또는 문자열 등이 사용됩니다.

 

SQL에서 사용되는 주요 데이터 형식과 각 데이터 형식에 대한 간단한 설명은 다음과 같습니다:

INTEGER (정수):
데이터 형식: INTEGER 또는 INT
설명: 정수 값을 나타내는 데이터 형식으로, 소수점 이하를 포함하지 않는 숫자를 저장합니다. 예를 들어, 1, -10, 100 등의 정수 값을 저장할 때 사용됩니다.

VARCHAR (가변 길이 문자열):
데이터 형식: VARCHAR(n)
설명: 가변 길이 문자열을 나타내는 데이터 형식으로, 최대 길이가 n인 문자열을 저장합니다. 이 데이터 형식은 문자열의 길이가 다를 수 있는 경우에 사용됩니다.

CHAR (고정 길이 문자열):
데이터 형식: CHAR(n)
설명: 고정 길이 문자열을 나타내는 데이터 형식으로, 항상 길이가 n인 문자열을 저장합니다. 빈 공간은 패딩 문자로 채워질 수 있습니다.

DATE (날짜):
데이터 형식: DATE
설명: 날짜를 나타내는 데이터 형식으로, 연도, 월, 일 정보를 저장합니다. 주로 날짜와 시간을 관리하기 위해 사용됩니다.

TIME (시간):
데이터 형식: TIME
설명: 시간을 나타내는 데이터 형식으로, 시간 정보를 저장합니다. 시, 분, 초 등의 정보를 포함할 수 있습니다.

DATETIME (날짜 및 시간):
데이터 형식: DATETIME
설명: 날짜와 시간을 모두 포함하는 데이터 형식으로, 연도, 월, 일, 시, 분, 초를 저장합니다. 주로 날짜와 시간을 동시에 저장하기 위해 사용됩니다.

BOOLEAN (부울):
데이터 형식: BOOLEAN
설명: 논리 값을 나타내는 데이터 형식으로, 참(True) 또는 거짓(False) 두 가지 값을 저장합니다.

DECIMAL 또는 NUMERIC (고정 소수점 숫자):
데이터 형식: DECIMAL(p, s) 또는 NUMERIC(p, s)
설명: 고정 소수점 숫자를 나타내는 데이터 형식으로, 총 자릿수 p와 소수점 이하 자릿수 s를 지정하여 소수를 저장합니다.

BLOB (이진 대형 객체):
데이터 형식: BLOB
설명: 이진 데이터, 예를 들어 이미지, 오디오, 비디오 등을 저장하는 데 사용되는 데이터 형식입니다.

CLOB (문자 대형 객체):
데이터 형식: CLOB
설명: 대용량 텍스트 데이터를 저장하는 데 사용되는 데이터 형식으로, 긴 문자열이나 문서를 저장할 때 유용합니다.
이것은 일반적으로 사용되는 SQL 데이터 형식의 몇 가지 예시이며, 데이터베이스 관리 시스템(DMS)에 따라 지원하는 데이터 형식이 다를 수 있습니다. 데이터 형식을 올바르게 선택하는 것은 데이터 저장 및 검색의 정확성과 효율성을 보장하는 중요한 부분입니다.

 

 

요구사항 분석, 시스템 설계, 모델링

DBMS(MySQL) -> Table -> Data(row)

03-02 데이터베이스 구축 절차 실습

구축 절차

구축

schema의 구축

 

MySQL에서 새로운 스키마(데이터베이스)를 만드는 것은 매우 간단한 쿼리문을 사용하여 수행할 수 있습니다. shopdb라는 이름의 스키마를 생성하기 위해서는 다음과 같은 SQL 문을 사용할 수 있습니다:

CREATE DATABASE shopdb;

이 쿼리를 실행하기 전에 몇 가지 사항을 고려해야 합니다:

권한: 이 쿼리를 실행하기 위해서는 데이터베이스를 생성할 수 있는 충분한 권한이 있어야 합니다. MySQL에서는 일반적으로 root 사용자 또는 데이터베이스 생성 권한을 가진 사용자가 이 작업을 수행합니다.

캐릭터 세트 및 정렬 순서: 데이터베이스의 기본 문자 집합과 정렬 순서를 지정하지 않으면 MySQL은 서버의 기본값을 사용합니다. 특정 문자 집합 및 정렬 순서를 사용하려면 쿼리를 다음과 같이 수정할 수 있습니다:

CREATE DATABASE shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

이 예에서는 utf8mb4 문자 집합과 utf8mb4_unicode_ci 정렬 순서를 사용합니다. 이는 다양한 언어와 기호를 포함하는 데이터에 적합합니다.

서버 설정: 서버의 설정이나 제약 사항에 따라 데이터베이스 생성에 영향을 줄 수 있으므로 이를 고려하는 것이 좋습니다.

이 쿼리를 실행한 후에는 해당 데이터베이스에 테이블을 생성하고 데이터를 관리할 수 있습니다. 데이터베이스 생성이 성공적으로 완료되면, USE shopdb; 쿼리를 사용하여 이 데이터베이스를 선택하고 해당 스키마에서 작업을 시작할 수 있습니다.

 

 

 

 

 

테이블 생성: memberTbl, ProductTbl

-- 멤버 테이블(membertbl) 생성
CREATE TABLE `shopdb`.`membertbl` (
  `memberID` CHAR(8) NOT NULL,     -- 회원 ID, 비어 있을 수 없는 값 (NOT NULL)으로 설정
  `memberName` CHAR(5) NOT NULL,   -- 회원 이름, 비어 있을 수 없는 값 (NOT NULL)으로 설정
  `memberAddress` CHAR(20) NULL,   -- 회원 주소, 비어 있어도 됨 (NULL) 

  PRIMARY KEY (`memberID`)        -- 기본 키로 회원 ID를 설정
);

-- 제품 테이블(producttbl) 생성
CREATE TABLE `shopdb`.`producttbl` (
    `productName` CHAR(4) NOT NULL,  -- 제품명, 비어 있을 수 없는 값 (NOT NULL)으로 설정
    `cost` INT NOT NULL,             -- 가격, 비어 있을 수 없는 값 (NOT NULL)으로 설정
    `makeDate` DATE NULL,            -- 제조일, 비어 있어도 됨 (NULL)
    `company` CHAR(5) NULL,          -- 제조사, 비어 있어도 됨 (NULL)
    `amount` INT NOT NULL,           -- 수량, 비어 있을 수 없는 값 (NOT NULL)으로 설정

    PRIMARY KEY (`productName`)      -- 기본 키로 제품명을 설정
);

 

 

NULL 및 NOT NULL 설명:
NULL: NULL은 데이터베이스 필드에 값이 없음을 나타냅니다. 특히, NULL 키워드가 사용되면 해당 열에는 데이터가 필수로 들어가지 않아도 된다는 것을 의미합니다. 예를 들어, memberAddress와 makeDate, company 열은 NULL로 설정되어 있으므로 값이 없거나 알 수 없는 경우 NULL로 표시될 수 있습니다.

NOT NULL: NOT NULL은 데이터베이스 필드에 값이 반드시 있어야 함을 나타냅니다. 예를 들어, memberID, memberName, cost, amount 열은 NOT NULL로 설정되어 있으므로 해당 열에는 항상 값이 있어야 합니다. 데이터 무결성을 유지하기 위해 사용됩니다.

 

 

값 넣기

INSERT INTO `shopdb`.`membertbl` (`memberID`, `memberName`, `memberAddress`) VALUES('Dang', '당탕이', '경기도 부천시 중동');
INSERT INTO `shopdb`.`membertbl` (`memberID`, `memberName`, `memberAddress`) VALUES('Jae', '지운이', '서울 은평구 증산동');
INSERT INTO `shopdb`.`membertbl` (`memberID`, `memberName`, `memberAddress`) VALUES('Han', '한주연', '인천 남구 주안동');
INSERT INTO `shopdb`.`membertbl` (`memberID`, `memberName`, `memberAddress`) VALUES('Sang', '상달이', '경기도 성남시 분당구');
INSERT INTO `shopdb`.`membertbl` (`memberID`, `memberName`, `memberAddress`) VALUES('Ezreal', '완상이', '경기도 김포시 걸포동');

INSERT INTO `shopdb`.`producttbl` (`productName`, `cost`, `makeDate`, `company`, `amount`) VALUES ('컴퓨터', '200', '2020-1-1', '삼성', '17');
INSERT INTO `shopdb`.`producttbl` (`productName`, `cost`, `makeDate`, `company`, `amount`) VALUES ('세탁기', '120', '2020-12-25', 'LG', '3');
INSERT INTO `shopdb`.`producttbl` (`productName`, `cost`, `makeDate`, `company`, `amount`) VALUES ('냉장고', '145', '2020-3-5', '대우', '22');

 

 

괄호 ()는 SQL 문에서 열 (column) 목록을 지정할 때 사용됩니다. SQL에서 괄호는 여러 가지 목적으로 사용됩니다.

열 목록 지정: 괄호 내에서 열을 명시적으로 지정할 때 사용됩니다. 예를 들어, INSERT INTO 문에서는 어떤 열에 어떤 값을 삽입할 것인지를 괄호 내에서 명시합니다.

INSERT INTO `shopdb`.`membertbl` (`memberID`, `memberName`, `memberAddress`) VALUES('Dang', '당탕이', '경기도 부천시 중동');

이 예제에서는 membertbl 테이블의 memberID, memberName, memberAddress 열에 값을 삽입하고 있습니다.

 

 

함수 호출: SQL에서 함수를 호출할 때 함수 이름 뒤에 괄호를 사용하여 함수에 전달되는 인수(argument)를 나열합니다. 함수 호출에도 괄호가 사용됩니다.

SELECT AVG(`score`) FROM `student_scores`;

위의 SQL 문에서 AVG 함수를 호출하고 있으며, 괄호 안에 score 열을 전달하여 평균 값을 계산합니다.

괄호의 사용은 SQL 문의 구문 규칙에 따라 이루어지며, 괄호 내에 필요한 열이나 값들을 정의하거나 함수를 호출하기 위해 사용됩니다.

 

 

03-03 데이터베이스 개체 - 인덱스

인덱스란

책 뒤에 찾아보기같은 개념, 빠르게 찾을 수 있음

index를 만들면 굉장히 빠르게 Data를 불러옴

-- 테이블 생성
CREATE TABLE shopdb.indexTBL (
    first_name VARCHAR(14),
    last_name VARCHAR(16),
    hire_date DATE
);

-- 테이블에 다른 D(employees)B의 다른 테이블(employees)의 성, 이름, 고용일 500개만 추가
INSERT INTO shopdb.indexTBL
  SELECT first_name, last_name, hire_date
  FROM employees.employees
  LIMIT 500;

SELECT * FROM indexTBL;
SELECT * FROM shopdb.indexTBL WHERE first_name = 'Mary';

-- INDEX 를 생성하면 빠르게 불러올 수 있다!
CREATE INDEX idx_indexTBL_firstname ON shopdb.indexTBL(first_name);
SELECT * FROM shopdb.indexTBL WHERE first_name = 'Mary';

 

뷰(View)

뷰 특징

 

가상 테이블, 진짜 테이블에 링크된 개념

뷰를 사용하면 데이터 보안과 안정성이 좋음(읽기 전용의 특징을 잘 살림)

뷰 생성

 

USE shopdb;

-- View 생성

CREATE VIEW uv_memberTBL

AS SELECT memberName, memberAddress FROM memberTBL;

-- View 조회

SELECT * FROM uv_memberTBL;

저장 프로시저(Stored Procedure)

개념: MySQL에서 제공해주는 프로그래밍 기능

 

-- 저장 프로시저 정의

DELIMITER //

CREATE PROCEDURE myProc()

BEGIN

SELECT * FROM memberTBL WHERE memberName = '당탕이';

SELECT * FROM productTBL WHERE productName = '냉장고';

END //

 

-- 실행

CALL myProc();

트리거(Trigger)

개념: 테이블에 부착되어 테이블에 특정 작업이 발생되면 실행되는 코드

예시: 회원Table의 회원 삭제 -> 트리거 발동 -> 삭제Table에 저장

-- 삭제 테이블 생성

CREATE TABLE deletedMemberTBL(

memberID char(8),

memberName char(5),

memberAddress char(20),

deleteDate date -- 삭제 날짜

);

 

-- TRIGGER 생성

DELIMITER //

CREATE TRIGGER trg_deletedMemberTBL -- 트리거 이름

AFTER DELETE -- 삭제 후에 작동

ON memberTBL -- 트리거를 부착할 테이블

FOR EACH ROW -- 각 행마다 실행

BEGIN

-- OLD 테이블의 내용을 백업테이블에 삽입

INSERT INTO deletedMemberTBL

VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());

END //

 

USE shopdb;

SELECT * FROM memberTBL;

DELETE FROM memberTBL WHERE memberName = '당탕이';

-- ERROR: 1175 Safe Update 가 뜬다면 안전하게(킷값으로만) 삭제 업데이트 설정이 켜져있어서임

-- Edit -> Preferences -> SQL Editor -> Safe Updates(가장 아래) 체크 해제

 

 

 

-- TRIGGER 삭제
DROP TRIGGER trg_deletedMemberTBL

-- 오타 등으로 트리거 재설정 시 사용

DELIMITER //
DROP TRIGGER IF EXISTS trg_deletedMemberTBL;
CREATE TRIGGER trg_deletedMemberTBL
    AFTER DELETE
    ON memberTBL
    FOR EACH ROW
BEGIN
    -- OLD 테이블의 내용을 백업테이블에 삽입
    INSERT INTO deletedMemberTBL
    VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
END //
DELIMITER ;

 

 

아래는 추가 예제 코드입니다.

 

-- MySQL 서버에 연결
-- (MySQL 서버에 연결하는 방법은 MySQL 클라이언트를 사용하여 실행합니다)

-- 데이터베이스 생성 (만약 이미 존재한다면 건너뛰기)
CREATE DATABASE IF NOT EXISTS mydatabase;

-- 해당 데이터베이스를 사용
USE mydatabase;

-- 테이블 생성
CREATE TABLE IF NOT EXISTS sample_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

-- 데이터 삽입 (10행의 예제 데이터)
INSERT INTO sample_data (name, age, city) VALUES
    ('John Doe', 30, 'New York'),
    ('Jane Smith', 28, 'Los Angeles'),
    ('Bob Johnson', 35, 'Chicago'),
    ('Alice Williams', 26, 'San Francisco'),
    ('Michael Brown', 40, 'Boston'),
    ('Emily Davis', 32, 'Seattle'),
    ('David Lee', 29, 'Dallas'),
    ('Linda Wilson', 31, 'Miami'),
    ('James Miller', 34, 'Houston'),
    ('Sarah White', 27, 'Phoenix');