🤔 My 돌고래

• 오늘부터 ! (사실 어제부터) SQL을 본격적으로 들어가게 되었습니다. 실습 환경은 MySQL이고, 오늘은 간단하게 기본적인 문법 몇 가지와 관련 SQL 문제를 풀게 되었습니다.

 

---

🌱 SQL의 기본적인 문법

• 어제 드디어 기나긴 학부 생활이 종료되고, 졸업식이 있었던 날이었습니다! 😀 그래서 어제 하루는 수업을 듣지 못해 GROUP BY부터 진행하게 되는 점 양해 부탁드립니다. 🙇🏻‍♂️ 시간이 되면 앞부분도 정리해서 추가로 올리도록 하겠습니다.

 

✨ GROUP BY

SELECT [열] FROM [테이블] WHERE [열] = [조건값] GROUP BY [열] HAVING [열] = [조건값]

• GROUP BY는 하나의 열, 혹은 2개 이상의 열을 기준으로 그룹화를 가능하게 해주는 명령어입니다.
• 데이터를 그룹화할 때에는 기준이 되는 열이 필요하기 때문에 반드시 SELECT 문에 사용한 열을 GROUP BY 절에도 사용할 수 있어야 합니다.
• 그룹화한 데이터에서 데이터를 필터링하려면 HAVING 절을 사용하면 됩니닷!
• DISTINCT는 GROUP BY와 유사하게 지정한 열의 중복 데이터를 제거하는 명령어이지만, 중복을 제거할 뿐, 그룹화를 하는 것이 아니기 때문에 별도의 계산 작업을 하기는 어렵습니다.

 

✨ 테이블 생성 및 조작

💡 AUTO_INCREMENT

CREATE TABLE (sample_column INT AUTO_INCREMENT PRIMARY KEY);

• AUTO_INCREMENT를 적용하여 데이터를 입력하게 되면, 해당 적용 열에 자동으로 1씩 증가한 데이터가 입력되게 됩니다.
• 이걸 적용한다고 해서 숫자가 연속적으로 입력되는 것은 아니기 때문에, 적용된 데이터가 어디까지 입력되었는지 궁금할 때에는 SQL에 있는 LAST_INSERT_ID() 함수로 확인이 가능합니다.
• 또한, 이 AUTO_INCREMENT를 다시 적용했을 때, 다른 값으로 시작하도록 설정하려면 ALTER를 이용해서 가능하게 할 수 있습니다.

ALTER TABLE increment AUTO_INCREMENT = 100;

• 증갓값을 변경하려면 'SET @@AUTO_INCREMENT = [증갓값]' 과 같이 입력하면 첫 데이터 입력 이후의 데이터부터 설정된 [증갓값]씩 증가하도록 수정할 수 있습니다.

 

💡 조회 결과를 테이블에 입력

• 조회 결과를 다른 테이블에 입력하기 위해서는 INSERT INFO ~ SELECT 문이 필요합니다.
• 이때는 반드시 입력 테이블과 조회한 열의 데이터 유형이 동일해야 합니다.
• 또다른 방법으로는, 테이블을 생성할 때 AS를 이용해서 원본 테이블에 대한 SELECT 결과값을 바로 입력해줄 수도 있습니다.

CREATE TABLE select_new AS (SELECT * FROM insert_select_from);

 

💡 외래키로 연결되어 있는 테이블 조작

• REFERENCES는 참조 관계를 생성하는 명령어로 자식 테이블(child)이 부모 테이블(parent)을 참조할 수 있게 합니다.
• 자식 테이블에 데이터를 입력할 때, 부모 테이블에 해당 데이터가 없으면 입력에 실패하고 오류가 발생하게 됩니다.
  • 그래서 이럴 때는 부모 테이블에 데이터를 먼저 입력한 뒤, 같은 데이터를 자식 테이블에 입력하면 오류없이 결과가 출력될 수 있습니다.
• 또한, 삭제할 때에도 부모 테이블에서만 데이터를 삭제하게 되면 오류가 발생합니다!
  • 따라서, 자식 테이블의 데이터를 먼저 삭제하고 부모 테이블의 데이터를 삭제해야 정상적으로 삭제가 이뤄집니다.
  • 제약 조건을 제거 후(DROP CONSTRAINT [외래키])에 부모 테이블을 삭제해도 가능하긴 합니다.
• 외래키를 잠시 OFF하고 데이터를 입력하거나 수정하려면 SET FOREIGN_KEY_CHECKS=0 을 설정해서 잠시 외래키를 비활성화할 수도 있지만, 이때는 잠시 데이터의 무결성이 깨지게 됩니다.

 

✨ 데이터의 유형

• 정수형은 기본적으로 부호를 가지는 SIGNED 형식이고, TINYINT, SMALLINT, BIGINT 등의 유형들이 많이 있지만 기본적으로는 INT형을 사용한다고 합니다.
• 부동 소수점을 사용하려면 FLOAT, DOUBLE 타입을 쓰고, 고정 소수점을 사용하려면 DECIMAL, NUMBER 타입을 사용합니다.
  • 특히, MySQL이 아니라 다른 SQL 문과 함께 사용하려면 FLOAT와 NUMBER형을 사용하는 것이 좋습니다.
• 부동 소수점 숫자인 FLOAT형과 DOUBLE형은 정확한 값이 아닌 근사치를 저장하게 됩니다.
  • 따라서, WHERE 절로 이 저장된 값을 찾으려고 하면 검색이 불가합니다.
  • 금융 데이터에서는 소수점이 확실히 고정되어야하는 것이 일반적이므로 DECIMAL, NUMBER와 같은 고정 소수점을 사용해 데이터를 관리한다고 합니다.

 

💡 타입의 변환

• 암시적 형 변환은 자료형을 직접 변경하지 않아도 실행 환경에서 자동으로 자료형을 변경하는 방법이고,
• 명시적 형 변환은 CAST, CONVERT 등의 함수를 사용해 자료형을 직접 변경해주는 방법입니다. 보통 명시적으로 타입을 변환시켜주는 것이 훨씬 좋습니다.
• 타입에는 여러 종류가 있지만, 그 중에서도 datetime(날짜와 시간), int(정수), decimal(소수), text(글), timestamp(시간), varchar(문자열), char(고정 크기 문자열), blob(이미지 데이터) 등을 주로 사용하기 때문에 이 타입들은 꼭 기억해두기!
• 이 중에서 DATETIME은 문자형으로 저장되고, TIMESTAMP는 숫자형으로 저장된다는 차이점이 존재하니 이것도 기억하기!

 

💡 문자형

• MySQL에서는 유니코드 UTF-8은 3바이트, UTF-16이면 2바이트를 사용한다고 하네요!
• 최근에는 utf8mb4라는 형식으로 다양한 이모티콘을 표현한다고도 합니다.

 

💡 콜레이션

• 콜레이션은 문자열 데이터가 담긴 열의 비교나 정렬 순서를 위한 규칙을 의미합니다.
• 콜레이션에 따라서 우선순위를 한글로 할지, 영어로 할지, 대소문자를 구분할지 등이 결정됩니다.
• DB 또는 테이블을 생성할 때 특별한 경우를 제외하고는 MySQL 기본 설정값을 사용하기 때문에 동일한 DB 내에서 정렬 순서로 인한 큰 문제는 없을 것입니다.
• 그렇기 때문에 ! 왠만해서는 이 콜레이션은 건드리지 않는 것이 베스트입니다. 👍🏻

 

---

🌿 DB 직접 만들어보기

• 강사님께서 주신 데이터들을 바탕으로 DB를 직접 구현해보게 되었습니다. 아직 심화 과정까진 나가지 않아서 지금까지 배운 내용들로도 충분히 쉽게 만들 수 있었습니다.
• 우선 PK, FK 설정만 진행해뒀고, 다른 사항들은 나중에 차차 배워가며 추가해보기로 했습니다.

학사 관리 ERD

• 결과적으로 위와 같은 형태를 가진 DB를 만들어낼 수 있었고, 다음은 관련 코드입니다.

CREATE DATABASE IF NOT EXISTS academic_manage;
USE academic_manage;

DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS professor;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS class;
DROP TABLE IF EXISTS takes;

CREATE TABLE department (
    dept_id VARCHAR(10) NOT NULL PRIMARY KEY,
    dept_name VARCHAR(30) NOT NULL,
    office VARCHAR(10)
);

CREATE TABLE student (
    stu_id VARCHAR(10) NOT NULL PRIMARY KEY,
    resident_id CHAR(15) NOT NULL,
    `name` VARCHAR(10) NOT NULL,
    `year` INT NOT NULL,
    address VARCHAR(10),
    dept_id VARCHAR(10) NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE professor (
    prof_id VARCHAR(10) NOT NULL PRIMARY KEY,
    resident_id CHAR(14) NOT NULL,
    `name` VARCHAR(10) NOT NULL,
    dept_id VARCHAR(10) NOT NULL,
    position VARCHAR(10) NOT NULL,
    year_emp INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE course (
    course_id VARCHAR(10) NOT NULL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    credit INT
);

CREATE TABLE class (
    class_id VARCHAR(20) NOT NULL PRIMARY KEY,
    course_id VARCHAR(10) NOT NULL,
    `year` INT,
    semester INT,
    division CHAR(1),
    prof_id VARCHAR(10) NOT NULL,
    classroom VARCHAR(20),
    enroll INT,
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    FOREIGN KEY (prof_id) REFERENCES professor(prof_id)
);

CREATE TABLE takes (
    stu_id VARCHAR(10) NOT NULL,
    class_id VARCHAR(20) NOT NULL,
    grade VARCHAR(5),
    PRIMARY KEY (stu_id, class_id),
    FOREIGN KEY (stu_id) REFERENCES student(stu_id),
    FOREIGN KEY (class_id) REFERENCES class(class_id)
);

• 그리고 해당 DB에서 특정 데이터들을 추출해내는 문제들을 여럿 풀게 되었습니다.

 

---

🤔 21일차 회고

• 오늘은 간단한 sql 문법들을 정리하고, 해당 내용들을 가지고서 DB를 한 번 만들어보게 되었습니다.
• 그 중에서도 콜레이션이나 외래키 설정하는 부분은 학부 데이터베이스 수업 시간에나 다루고 그 이후로는 다뤄본 적이 없어서 감회가 새로웠던 부분 중 하나입니다.
• 오늘은 쉽고 다루기 편한 부분이라 내용이 짧게 느껴졌는데, 내일부터는 SQL의 꽃인 조인, 그리고 서브 쿼리 등을 배우게 될 예정이라 포스팅이 더욱 풍성해질 것 같습니다!
• 그리고 주말 간 ADsP 자격증 시험을 보고 왔는데, 난이도는 어려웠으나 생각보다 너무 잘 보게 되어서 감사했고, 4월에 있을 '빅데이터 분석 기사'도 슬슬 준비해볼까 합니다!