DB SQL 테이블
1. 데이터베이스 준비 단계
DROP DATABASE IF EXISTS a5;DROP DATABASE: 데이터베이스를 삭제하라는 명령어다.IF EXISTS a5: 만약 'a5'라는 이름의 데이터베이스가 이미 존재한다면(IF EXISTS) 삭제하라는 조건이다. 기존에 연습하던 데이터가 남아있어 충돌하는 것을 방지하기 위해 가장 먼저 실행하는 초기화 작업이다.
CREATE DATABASE a5;CREATE DATABASE a5: 'a5'라는 이름의 빈 데이터베이스(데이터를 담을 큰 창고)를 새로 만든다.
USE a5;USE a5: 방금 만든 'a5' 데이터베이스를 지금부터 사용하겠다고 선택하는 명령어다. 이후 작성되는 모든 테이블과 데이터는 이 'a5' 안에 저장된다.
2. 부서(dept) 테이블 생성 및 데이터 추가
CREATE TABLE dept(CREATE TABLE dept: 'dept'라는 이름의 테이블(표)을 만든다. 이 괄호 안에는 표의 각 칸(컬럼)을 어떻게 구성할지 정의한다.
id INT UNSIGNED NOT NULL AUTO_INCREMENT,id: 부서의 고유 번호를 저장할 칸이다.INT: 정수(숫자)만 입력받는다.UNSIGNED: 음수는 제외하고 0과 양수만 허용한다.NOT NULL: 이 칸은 절대 비워둘 수 없다.AUTO_INCREMENT: 데이터를 추가할 때마다 1부터 자동으로 번호가 1씩 늘어나며 부여된다.
PRIMARY KEY(id),PRIMARY KEY(id): 방금 만든id칸을 이 테이블의 '기본키(가장 중요한 고유 식별자)'로 지정한다. 데이터베이스는 이 기본키를 기준으로 데이터를 빠르게 찾고 정렬한다.
regDate DATETIME NOT NULL,regDate DATETIME NOT NULL: 등록 날짜와 시간을 저장하는 칸이며, 빈칸을 허용하지 않는다.
`name` CHAR(100) NOT NULL UNIQUE
);`name` CHAR(100): 부서 이름을 저장하며, 최대 100글자까지 들어간다.UNIQUE: 중복을 허용하지 않는다. 즉, 똑같은 이름의 부서를 두 개 만들 수 없도록 막는다.
INSERT INTO dept SET regDate = NOW(), `name` = '홍보부서';
INSERT INTO dept SET regDate = NOW(), `name` = '기획부서';INSERT INTO dept:dept테이블에 새로운 데이터를 넣는다.SET: 입력할 값을 지정한다.regDate = NOW(): 등록일에 현재 시스템의 날짜와 시간을 자동으로 입력한다.`name` = '홍보부서': 부서 이름에 '홍보부서'라는 글자를 넣는다. (기획부서도 동일한 원리로 2번 데이터로 추가된다.)
3. 사원(emp) 테이블 생성 및 데이터 추가
CREATE TABLE emp(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL UNIQUE,
deptName CHAR(100) NOT NULL
);- 부서 테이블을 만들 때와 구조가 같다. 차이점은
`name`칸이 '사원의 이름'을 의미한다는 점과, 사원이 속한 부서의 이름을 문자로 직접 적기 위해deptName이라는 칸을 만들었다는 점이다.
INSERT INTO emp SET regDate = NOW(), `name` = '홍길동', deptName = '홍보부서';
INSERT INTO emp SET regDate = NOW(), `name` = '홍길순', deptName = '홍보부서';
INSERT INTO emp SET regDate = NOW(), `name` = '임꺽정', deptName = '기획부서';- 3명의 사원 데이터를 순서대로 추가한다. 각 사원의 등록일, 이름, 그리고 소속 부서의 이름(문자열)을 지정해 주었다.
4. 부서명 변경 (데이터 갱신 시뮬레이션)
UPDATE dept SET `name` = '마케팅부서' WHERE `name` = '홍보부서';
UPDATE emp SET deptName = '마케팅부서' WHERE deptName = '홍보부서';UPDATE 테이블명 SET 변경할값 WHERE 조건: 기존 데이터를 수정하는 명령이다.- 첫 번째 줄은 부서 테이블에서 '홍보부서'를 찾아 '마케팅부서'로 이름을 바꾼다.
- 두 번째 줄은 사원 테이블에서 소속이 '홍보부서'로 적혀있던 사람들을 모두 찾아 '마케팅부서'로 바꾼다. (이후 코드는 이름이 다시 홍보부서로 바뀌었다가, 또다시 마케팅부서로 바뀌는 상황을 반복 연습하는 과정이다.)
5. 데이터베이스 구조 변경 (정규화 과정)
ALTER TABLE emp ADD COLUMN deptId INT UNSIGNED NOT NULL;ALTER TABLE emp: 이미 만들어진 사원 테이블의 구조를 뜯어고친다.ADD COLUMN deptId:deptId라는 새로운 칸을 추가한다. 여기에 부서 이름(글자) 대신 부서 번호(숫자)를 저장할 목적이다.
UPDATE emp SET deptId = 1 WHERE deptName = '마케팅부서';
UPDATE emp SET deptId = 2 WHERE deptName = '기획부서';- 사원 테이블을 검색하여, 현재
deptName칸에 '마케팅부서'라고 적힌 사람들의 빈deptId칸에 1번을 채워 넣는다. '기획부서'인 사람에게는 2번을 채워 넣는다.
ALTER TABLE emp DROP COLUMN deptName;DROP COLUMN deptName: 번호(deptId)를 모두 부여했으므로, 더 이상 필요 없어진 글자 저장 칸(deptName)을 테이블에서 아예 삭제해 버린다. 이를 통해 데이터의 중복을 없애고 구조를 최적화했다.
6. 데이터 조회 및 테이블 결합(JOIN)
SELECT * FROM emp;SELECT *: 모든 칸의 데이터를 보여달라는 뜻이다. (사원 테이블을 조회하면 소속 부서가 1, 2 같은 숫자로만 보여서 식별이 어렵다.)
SELECT * FROM dept;- 부서 테이블을 조회하여 1번이 무슨 부서인지, 2번이 무슨 부서인지 확인한다. (사용자가 두 표를 번갈아 봐야 하므로 불편하다.)
SELECT emp.*, dept.name AS `부서명`
FROM emp
INNER JOIN dept;emp.*, dept.name: 사원 테이블의 모든 정보와 부서 테이블의 이름 정보를 가져온다.AS 부서명:dept.name칸의 제목을 화면에 띄울 때만 '부서명'이라는 별명으로 바꿔서 보여준다.INNER JOIN dept: 두 테이블을 무작정 합친다. (연결 조건이 없어서 사원 3명과 부서 2개가 모두 곱해진 6줄의 잘못된 데이터가 나온다.)
SELECT emp.*, dept.name AS `부서명`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id;ON emp.deptId = dept.id: 두 테이블을 합치는 정확한 기준(조건)을 제시한다. 사원 테이블의deptId(부서번호)와 부서 테이블의id(고유번호)가 똑같은 것끼리만 짝을 지어 출력한다. 정상적인 명부가 만들어진다.
SELECT
e.id AS `사원번호`,
e.name AS `사원명`,
DATE(e.regDate) AS `입사일`,
d.name AS `부서명`
FROM emp e
INNER JOIN dept d
ON e.deptId = d.id
ORDER BY `부서명`, `사원명`;FROM emp e INNER JOIN dept d: 테이블 이름 뒤에 띄어쓰기하고e,d를 적어 테이블의 별명을 지어준다. 코드가 훨씬 간결해진다.SELECT e.id AS 사원번호:emp테이블의id칸을 '사원번호'라는 이름으로 가져온다.DATE(e.regDate):DATETIME형식의 날짜/시간 데이터에서 시간은 잘라내고 연-월-일만 깔끔하게 가져오는 함수다.ORDER BY 부서명, 사원명: 최종 출력될 데이터를 정렬한다. 1차로 부서명을 가나다순으로 정렬하고, 부서가 같으면 2차로 사원명을 가나다순으로 정렬하여 보기 좋은 최종 보고서를 완성한다.
성심당마스코트