Section 2

[echo-youn]

Section 2-1

관계형 데이터베이스 다루기

• DBeaver 설치 (Database Client)
• 데이터베이스와 연결하기
• server host: tmdwo07278.cafe24.com
• Database: tmdwo07278
• Username: tmdwo07278
• Password: q1w2e3r4

Mysql 데이터 타입

• varchar
• int
• bigint
• char
• tinyint
• datetime

DDL

• 테이블 만들기 - https://dev.mysql.com/doc/refman/8.3/en/create-table.html

CREATE TABLE <tbl_name>
(
  <col_name> <column_definition>,
  ...
);

-- 학생 테이블
CREATE TABLE tmdwo07278.edu_{이름}_student (
	id BIGINT auto_increment NOT NULL,
	name varchar(100) NOT NULL,
	gender varchar(20) NOT NULL,
	age INT NOT NULL,
	isGraduated TINYINT DEFAULT 0 NOT NULL,
	createtime datetime DEFAULT now() NOT NULL,
	lastModifiedTime datetime NULL,
	CONSTRAINT edu_echo_student_pk PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

• 테이블 수정하기 - https://dev.mysql.com/doc/refman/8.3/en/alter-table.html

ALTER TABLE <테이블명>
ADD COLUMN <col_name> <column_definition>;

ALTER TABLE tmdwo07278.edu_{이름}_student ADD mobile varchar(20) NULL;

DML

• 데이터 조회하기 - https://dev.mysql.com/doc/refman/8.3/en/select.html

SELECT [col_name]
FROM [tbl_name]
WHERE [col_name] = '데이터' AND [col_name] > '데이터';

• 데이터 입력하기 - https://dev.mysql.com/doc/refman/8.3/en/insert.html

INSERT [tbl_name] ( [col_name], [col_name]...)
VALUES('1', '2', '3'....);

INSERT INTO edu_{이름}_student(id, name, gender, age, isGraduated, createtime, lastModifiedTime) 
SELECT id, name, gender, age, isGraduated, createtime, lastModifiedTime
FROM edu_echo_student;

• 데이터 수정하기 - https://dev.mysql.com/doc/refman/8.3/en/update.html

UPDATE [tbl_name]
SET [col_name] = '수정할 값'
WHERE [col_name] = '수정할 데이터 조회'

• 데이터 삭제하기 - https://dev.mysql.com/doc/refman/8.3/en/delete.html

DELETE FROM [tbl_name]
WHERE [col_name] = '';

조회 연습하기

1. id가 3인 학생의 이름, 성별, 나이를 찾으세요.
2. id가 30이상 50미만의 학생을 찾으시오. (전체)
3. 이름이 "Nelle Harrington"인 학생을 찾으시오.
4. 여성 중 나이가 23살이며 졸업한 학생을 찾으세요.

데이터 집계하기

GroupBy

예시)

-- 학생 몇명인지 전체 조회
SELECT COUNT(1) as cnt
FROM edu_{이름}_student;

-- 재학중인 학생 몇명인지 조회
SELECT COUNT(1) as cnt
FROM edu_{이름}_student
WHERE isGraduated = 0;

-- 여학생 수
SELECT COUNT(1) as cnt
FROM edu_{이름}_student
WHERE gender = 'FEMALE';

-- 나이별 학생수
SELECT age, COUNT(1) as cnt
FROM edu_{이름}_student
GROUP BY age;

-- 나이별 성별별 학생수
SELECT age, gender, COUNT(1) as cnt
FROM edu_{이름}_student
GROUP BY age, gender;

-- 문제1 나이가 24살 이상인 남학생 중 졸업하지 않은 학생수를 구하세요

-- 문제2 나이가 24살 이상인 학생 중 졸업하지 않은 학생 수, 나이의 합, 나이의 평균을 구하세요. (COUNT 함수, SUM 함수, AVG함수 사용)

Section 2-2

관계형 데이터베이스

키에 대한 설명

기본키 (Primary Key)

테이블에서 행(row)의 식별자로 이용되는 키를 기본키라 합니다.

외래키 (Foreign key)

테이블에서 다른 테이블의 식별자로 이용되는 키를 외래키라 합니다.

예시)

erDiagram
    STUDENT ||--|| MAJOR : ""
    STUDENT {
        studentId bigint PK "고유 번호(학번)"
        name varchar "이름"
        gender varchar "성별"
        majorId bigint FK "주 전공"
        majorName varchar "전공 이름"
    }

    MAJOR {
        majorId bigint PK "전공"
        majorName varchar "전공 이름"
    }

Loading

조인이란?

데이터를 조회 시 외래 키 등을 활용해 다른 테이블을 조회하는 것.

사전 데이터 준비

-- majorId 컬럼 추가
ALTER TABLE edu_{이름}_student ADD majorId BIGINT NULL;

-- edu_{이름}_major 테이블 생성
CREATE TABLE edu_{이름}_major(
    majorId BIGINT auto_increment primary key,
    majorName VARCHAR(50) NOT NULL,
    professor VARCHAR(50) NOT NULL
);

-- 데이터 생성
-- 1. 물리학과, 아인슈타인
-- 2. 화학공학과, 프리츠 하버
-- 3. 컴퓨터공학과, 앨런 튜링
-- 4. 체육학과, 김연아
-- 5. 수학과, 피타고라스
-- 6. 물리학과, 오펜하이머
-- 7. 화학공학과, 오펜하이머
-- 8. 수학과, 레온하르트 오일러
-- 9. 컴퓨터공학과, 빌 조이
-- 10. 체육학과, 박지성

-- 막간에 코딩테스트(?) 골고루 학생한테 입력해주기 (id가 10, 20, 30... 인 학생에게 체육학과, 박지성의 전공 id를 입력하시오)

```sql
UPDATE edu_{이름}_student
SET majorId = ?
WHERE <where clause>;

조인 해보기

-- 전공 테이블 기준으로 조회

SELECT (4)
FROM (1)
WHERE (2)
GROUP BY (3);

SELECT *
FROM <테이블 A> <별칭 A> JOIN <테이블 B> <별칭 B> ON <A 테이블의 fk> = <B 테이블의 PK>;

-- 화학공학과 학생들의 학번들 조회
SELECT s.id
FROM edu_{이름}_major m INNER JOIN edu_{이름}_student s ON m.id = s.majorId
WHERE m.name = '화학공학과';

-- 문제 오펜하이머의 수업을 듣는 재학생들 이름, 성별, 나이, 전화번호

-- 문제 앨런 튜링의 컴퓨터공학수업을 들은 졸업생의 전공id, 학번, 전공명, 이름, 성별, 나이, 전화번호

LEFT, RIGHT, INNER, OUTER

-- 신입생 입학
INSERT INTO edu_{이름}_student(name, gender, age, isGraduated)
SELECT name, gender, age, 0
FROM edu_echo2_student;

-- 학번 짝수인 신입생들은 피타고라스의 수학 강의가 필수라 할당해주어함 (createtime 활용)

나머지 학생들 조회 해보자-

일대일, 일대다, 다대다

일상생활 속 예시

1 대 1 : `계정: 비밀번호`, `사람 : 주민등록번호` 등...
1 대 다: `부모님(아버지, 어머니) : 자식(1~n명)`, `학생 : 성적`
다 대 다: `학생 : 강의`, `학생 : 동아리`

ERD

잼보드?!

-- 동아리 테이블

-- 학점 테이블