3. 데이터 베이스(Data Base) Day 3

데이터 정규화란?

- 데이터를 효율적으로 저장하고, 중복을 최소화하고, 데이터 무결성을 유지하기 위한 과정

- 일반적으로 정규화는 여러 단계로 나뉘며, 각 단계는 데이터의 중복성을 불이고 논리적 구조를 개선

 

1. 제 1 정규형(1NF, First Normal Form)

- 테이블 내 모든 열의 값만 있어야 하며, 중복된 데이터는 허용 되지 않음

- 각 셀에 하나의 값만 있어야 하며, 중복된 데이터는 허용되지 않음

 

제 1 정규형의 예시)

 

 

2.제 2 정규형(2NF)

- 2NF를 만족하면서, 기본키에 대해 부분 함수 종속이 없어야함

- 테이블의 기본키 일부에만 종속되는 속성은 제거  

 

제 2 정규형의 예시)

 

3. 제 3 정규형(3NF)

- 2NF를 만족하면서, 기본키가 아닌 속성들이 서로 종속 관계에 있지 않아야 함

- 이행적 함수 종속을 제거해야함 

4. 비정규화

- 정규형에 일치하게 되어 있는 테이블을 정규형을 지키지 않는 테이블로 변경

- 테이블을 조회하는 용도로 사용하거나, 너무 데이터가 많이 나뉘어 성능이 저하한다면, 비정규화를 하여 테이블을 다루는 것이 효과적 일 수 있음

- 어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적벌한 정규화를 하는것이 좋음

 

# profile 테이블 생성

create table profile (userid varchar(20) not null,       height double,       weight double,      mbti varchar(10),      foreign key(userid) references member(userid));

 

# 데이터 삽입

1) insert into profile values('apple', 160, 50, 'ISTJ'); 2) insert into profile values('orange', 170, 70, 'ESTP'); 3) insert into profile values('avocado', 180, 80, 'INTP');

 

##*JOIN 테이블 사용시 참고 사항*##

## MEMBER 테이블 데이터 

 

## PROFILE 테이블 데이터

 

 

2. 조인(Join)

SELECT 필드1, 필드2, ... FROM 테이블 [INNER, LEFT, RIGHT] JOIN 테이블2 ON 테이블1.필드 = 테이블2.필드

 

1) INNER JOIN

- 조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력

- 두 테이블 간의 데이터 교집합

- JOIN,CROSS JOIN 모두 같은 의미로 사용됨

 

# JOIN 사용법

1) select member.userid, name, gender, mbti                                                   from member join profile on member.userid = profile.userid; 2) select member.userid, member.name, member.gender, profile.mbti from member inner join profile on member.userid = profile.userid;

 

# LEFT/RIGHT JOIN

- 두테이블이 조인될떄 왼쪽(기본키 테이블) 또는 오른쪽(외래키 테이블) 기준으로, 기존 테이블의 데이터를 모두 출력

 

 

# LEFT JOIN

SELECT MEMBER.USERID, MEMBER.NAME, MEMBER.GENDER, PROFILE.MBTI FROM MEMBER LEFT JOIN PROFILE ON MEMBER.USERID = PROFILE.USERID;

 

# RIGHT JOIN

SELECT MEMBER.USERID, MEMBER.NAME, MEMBER.GENDER, PROFILE.MBTI FROM MEMBER RIGHT JOIN PROFILE ON MEMBER.USERID = PROFILE.USERID;

 

# AS 사용

SELECT M.USERID, M.NAME, M.GENDER, P.MBTI FROM MEMBER AS M LEFT JOIN PROFILE AS P ON M.USERID = P.USERID;

 

3. MYSQL 문자열 함수

 

1) CONCAT : 문자열을 연결해주는 함수

SELECT CONCAT('안녕하세요', 'MYSQL') AS CONCAT;

 

 

# CONCAT(ADDRESS1과 ADDRESS2를 연결해주는 함수)

SELECT CONCAT(ADDRESS1, ' ', ADDRESS2) AS ADDRESS FROM MEMBER WHERE USERID = 'APPLE';

 

 

# LEFT, RIGHT : 왼쪽 또는 오른쪽에 길이만큼 문자열을 반환

SELECT LEFT('ABCDEFGHIJKLMN', 5) AS STR;

 

 

# member 테이블에서 왼쪽 userid의 id로 왼쪽에서 3글자 추출

SELECT USERID, LEFT(USERID, 3) AS ID FROM MEMBER;

 

 

# SUBSTRING : 문자열의 일부를 추출하여 반환

 

# 시작위치부터 끝까지 추출

SELECT SUBSTRING('ABCDEFGHIJKLMN', 5) AS SUB;

 

 

# 시작위치부터 길이만큼 추출(5번째 E부터 3글자 추출)

SELECT SUBSTRING('ABCDEFGHIJKLMN', 5, 3 ) AS SUB;

 

 

# # 시작위치부터 길이만큼 추출(1번째부터 3글자 추출)

SELECT USERID, SUBSTRING(USERID, 1, 3) AS ID FROM MEMBER;

 

# CHAR_LENGHT : 문자열의 길이를 반환

SELECT CHAR_LENGHT('ABCDEFGHIJKLMN') AS CNT;

 

SELECT EMAIL, CHAR_LENGHT(EMAIL) AS LEN FROM MEMBER;

 

 

# LPAD, RPAD : 왼쪽 또는 오른쪽의 문자열을 해당 길이만큼 늘리고, 빈 공간을 채운 문자열을 반환

SELECT LPAD('ABCDEFG', '10', ' * ') AS LPAD;

 

# RPAD

SELECT USERID, RPAD(USERID, 20 ' ') AS ROAD FROM MEMBER;

 

# LTRIM, RTRIM, TRIM : 왼쪽, 오른쪽, 양쪽 모두 공백을 제거

 

# LTRIM (왼쪽의 공백을 제거 후 추출)

SELECT LTRIM ('      ABCDEF      ') AS LTRIM;

 

# RTRIM(오른쪽의 공백을 제거 후 추출)

SELECT RTRIM('      ABCDEF      ') AS RTRIM;

 

# TRIM(앞, 뒤 공백만 제거)

SELECT TRIM('      ABCDEF      ') AS TRIM;

 

# REPLACE : 문자열에서 특정 문자열을 변경하여 변환

 

# CD를 공백으로 변경하여 추출

SELECT REPLACE('ABCDEFG', 'CD', ' ') AS RP;

 

# 성별의 남자,여자를 '자' 빼고 추출 

SELECT USERID, REPLACE(GENDER, '자', ' ') AS GENDER FROM MEMBER;

 

3. 유니온(UNION)

SELECT 컬럼1, 컬럼2, ... FROM 테이블 1 UNION SELECT 컬럼1, 컬럼2,... FROM 테이블2

- 합집합을 나타내는 연산자로 중복된 값을 제거함

- 서로 같은 종류의 테이블(컬림이 같아야 함)에서만 적용이 가능

 

 

# 테이블 생성

CREATE TABLE PRODUCT(NAME VARCHAR(5) NOT NULL,   DETAIL VARCHAR(1000),  PRICE INT DEFAULT 0,  REGDATE DATETIME DEFAULT NOW());

 

 

# 데이터 삽입

1) INSERT INTO PRODUCT VALUES('00001', '맥북에어', '가벼워요', '1500000', NOW()); 2) INSERT INTO PRODUCT VALUES('00002', '모니터', '잘보여요', '400000', NOW()); 3) INSERT INTO PRODUCT VALUES('00003', '지갑', '잘담겨요', '100000', NOW()); 4) INSERT INTO PRODUCT VALUES('00004', '아이패드', '글쎄요', '700000', NOW()); 5) INSERT INTO PRODUCT VALUES('00005', '그래픽카드', '가벼워요', '500000', NOW()); 3

 

# 테이블 생성

CREATE TABLE PRODUCT_NEW( CODE VARCHAR(5) NOT NULL, NAME VARCHAR(20) NOT NULL, DETAIL VARCHAR(1000), PRICE INT DEFAULT 0, REGDATE DATETIME DEFAULT NOW());

 

 

# 데이터 삽입

1) INSERT INTO PRODUCT_NEW VALUES('10001', '맥북프로', '무거워요', '2500000', NOW()); 2) INSERT INTO PRODUCT_NEW VALUES('20002', '동전지갑', '이것도 잘 담겨요', '10000', NOW()); 3) INSERT INTO PRODUCT_NEW VALUES('30003', 'LG그램', '이것도 가벼워요', '120000', NOW());

 

 

# UNION 사용(두개의 테이블의 데이터를 모두 보여줌)

SELECT CODE, CODE, NAME, PRICE FROM PRODUCT UNION SELECT CODE, NAME, PRICE FROM PRODUCT_NEW;

 

 

# PRODUCT에 있는 데이터를 PRODUCT_NEW에 동일하게 삽입

INSERT INTO PRODUCT_NEW VALUES('0003', '지갑', '잘담겨요', 100000, NOW());

 

 

# UNION은 중복 데이터를 제거

SELECT CODE, NAME, PRICE FROM PRODUCT UNION SELECT CODE, NAME, PRICE FROM PRODUCT_NEW;

 

 

# 날짜 / 시간이 다르기 떄문에 중복데이터가 아님!!

SELECT CODE, NAME, PRICE, REGDATE FROM PRODUCT UNION SELECT CODE, NAME, PRICE, REGDATE FROM PRODUCT_NEW;

 

 

# UNION ALL은 중복 데이터를 포함

SELECT CODE, NAME, PRICE, REGDATE FROM PRODUCT UNION ALL SELECT CODE, NAME, PRICE, REGDATE FROM PRODUCT_NEW;

 

 

# 서브 쿼리(SubQuery)

- 다른 쿼리 내부에 포함되어 있는 SELECT문을 의미 -

- 서브 쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고도 부름

- 서브 쿼리는 괄호()를 사용해서 표현

- SELECT, WHERE, FROM, HAVING 절 등에서 사용 할 수 있음

 

 

# 상품 코드가 '00005'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 정보를 모두 출력

1) SELECT PRICE FROM PRODUCT WHERE CODE = '00005'; 2) SELECT * FROM PRODUCT WHERE PRICE >= '00005'; -> SELECT * FROM PRODUCT WHERE PRICE >= (SELECT PRICE FROM PRODUCT WHERE CODE = '00005');

#PRODUCT 테이블에서 모든 상품의 CODE, NAME, PRICE '가장 비싼 상품의 가격' 을 출력

예) 00001 맥북에어 1500000 1500000

1) SELECT CODE, NAME, PRICE, ('가장 비싼 상품의 가격') FROM PRODUCT; --> SELECT CODE, NAME, PRICE, (SELECT MAX(PRICE) FROM PRODUCT) AS '가장 비싼 상품의 가격' FROM PRODUCT;

 

# 테이블 생성

CREATE TABLE ORDERS( NO INT AUTO_INCREMENT PRIMARY KEY, USERID VARCHAR(20) NOT NULL, PRODUCT_CODE VARCHAR(5) NOT NULL, PRODUCT_CNT INT DEFAULT 1, REGDATE DATETIME DEFAULT NOW(), FOREIGN KEY(USERID) REFERENCES MEMBER(USERID));

 

# 데이터 삽입

INSERT INTO ORDERS VALUES(1, 'APPLE', '00005', 1, NOW());

INSERT INTO ORDERS VALUES(5, 'BANANA', '00003', 1, NOW());

INSERT INTO ORDERS (USERID, PRODUCT_CODE, PRODUCT_CNT) VALUES ('APPLE', '00002', 1);

 

# 중복된 키(Duplicate entry '5' for key 'orders.PRIMARY)

#INSERT INTO ORDERS VALUES(5, 'ORANGE', '00002', 1, NOW());

#INSERT INTO ORDERS (USERID, PRODUCT_CNT) VALUES ('APPLE', 1);

 

# 데이터 삽입

INSERT INTO ORDERS (USERID, PRODUCT_CODE, PRODUCT_CNT) VALUES ('APPLE', '00004', 1);

 

# 7로 삭제하면 다음 데이터 생성시 8 로 생성된다.

삭제 전

INSERT INTO ORDERS (USERID, PRODUCT_CODE) VALUES ('APPLE', '00004'); 삭제 후

 

# PRODUCT 테이블에 기본키 추가하기

PRIMARY KEY 적용 전

 

 

ALTER TABLE PRODUCT ADD PRIMARY KEY(CODE); PRIMARY KEY 적용 후

 

# ORDER 테이블에 외래키 추가하기

ALTER TABLE ORDERS ADD FOREIGN KEY(PRODUCT_CODE) REFERENCES PRODUCT(CODE) ON UPDATE CASCADE ON DELETE CASCADE;

 

 

# 데이터 삽입

INSERT INTO VALUES (USERID, PRODUCT_CODE, PRODUCT_CNT) VALUES ('ORANGE', '00001', 1);

 

 

# 특정 상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력

1) SELECT USERID, COUNT(NO) FROM ORDERS GROUP BY USERID;

2) SELECT USERID, COUNT(NO) FROM ORDERS GROUP BY USERID HAVING COUNT(NO) >= 2;

3) SELECT USERID, NAME, GENDER FROM MEMBER WHERE USERID IN('APPLE', 'BANANA','ORANGE');

4) SELECT USERID, NAME, GENDER FROM MEMBER WHERE USERID IN (SELECT USERID FROM ORDERS GROUP BY USERID HAVING COUNT(NO) >= 2); 

 

# 테이블 생성

CREATE TABLE ORDERS_NEW(NO INT AUTO_INCREMENT PRIMARY KEY, USERID VARCHAR(20) NOT NULL,  PRODUCT_CODE VARCHAR(5) NOT NULL, PRODUCT_CNT INT DEFAULT 1,  REGDATE DATETIME DEFAULT NOW(),  FOREIGN KEY(USERID) REFERENCES MEMBER(USERID), FOREIGN KEY(PRODUCT_CODE) REFERENCES PRODUCT(CODE));

CREATE TABLE ORDERS_NEW(
       NO INT AUTO_INCREMENT PRIMARY KEY,
       USERID VARCHAR(20) NOT NULL,
       PRODUCT_CODE VARCHAR(5) NOT NULL, 
       PRODUCT_CNT INT DEFAULT 1,
       REGDATE DATETIME DEFAULT NOW(),
       FOREIGN KEY(USERID) REFERENCES MEMBER(USERID),
       FOREIGN KEY(PRODUCT_CODE) REFERENCES PRODUCT(CODE));

 

 

# ORDERS_NEW 테이블에 ORDERS 데이터 삽입

INSERT INTO ORDERS_NEW(SELECT * FROM ORDERS);

 

# ORDERS_NEW_NEW 테이블에 ORDERS 데이터 삽입

CREATE TABLE ORDERS_NEW_NEW(SELECT * FROM ORDERS);

 

뷰(VIEW)

- 하나 이상의 테이블의 쿼리 결과를 가상의 테이블 형태로 보여주는 것

- 실제 데이터는 저장하지 않고, 쿼리를 미리 저장해두어 테이블처럼 사용 할 수 있게 함

- 복잡한 쿼리나 자주 사용하는 쿼리를 간단하게 사용 할 수있도록 도와주는 가상의 테이블

- 테이블의 일부 열이나 행만 사용자에게 보여줄 수 있음(민감한 데이터를 보호)

- 성능이 떨어질 수 있음. 뷰를 사용할 때 마다 원본 테이블에서 데이터를 조회하므로, 큰 데이터셋이나 쿼리의 경우 성능이 저하될 수 있음

- ORDER BY는 뷰를 조회할때 사용하는것을 추천

 - INSERT / UPDATE / DELETE 할때 GROUP BY, JOIN 복잡하게 적용된 뷰는 데이터를 추가하거나 수정하기 힘듦

 

# 뷰 생성하기

# CREATE VIEW 뷰 이름 AS 쿼리

#읽어보기 힘들다면 끊어서 보면 알아보기가 쉽다(팁!)

SELECT M.USERID, M.NAME, M.GENDER, P.MBTI

                                 FROM MEMBER AS INNER JOIN PROFILE AS P

                                 ON M.USERID = P.USERID;

 

# 뷰 생성하기

CREATE VIEW VW_USER_INFO AS SELECT M.USERID, M.NAME, M.GENDER, P.MBTI    FROM MEMBER AS M INNER JOIN PROFILE AS P ON M.USERID = P.USERID;

 

#Can not insert into join view 'gom.vw_user_info' without fields list

# insert into vw_user_info values('grapes', '포도', '남자', 'INFP');

 

# 뷰 변경하기

# CREATE VIEW 뷰 이름 AS 쿼리

- ALTER VIEW VW_USER_INFO AS SELECT M.USERID, M.NAME, M.GENDER, P.HEIGHT, P.MBTI FROM MEMBER

    AS SELECT M.USERID, M.NAME, M.GENDER, P.HEIGHT, P.MBTI

    FROM MEMBER AS M INNER JOIN PROFILE AS P

     ON M.USERID = P.USERID;

 

# 뷰 삭제 하기

DROP VIEW 뷰 이름

-DROP VIEW VW_USER_INFO;

 

MYSQL의 사용자

 

1. 사용자 추가하기

- MYSQL 8.0 COMMAND LINE CLIENT에서 ROOT 계정으로 로그인

- 접속 가능한 사용자를 추가

 

# CREATE USER '사용자명'@'LOCALHOST' IDENTIFIED BY '비밀번호';

예) CREATE USER 'APPLE'@'LOCALHOST' IDENTIFIED BY '1111';

 

# 사용자 목록 조회

- USE MYSQL;

SELECT USER, HOST FROM USER;

 

# 사용자 권한 할당 상세 옵션

CREATE, DROP, ALTER : 테이블에 대한 생성, 삭제, 변경권한

SELECT, INSERT, UPDATE, DELETE : 테이블의 데이터 조회, 삽입, 변경, 삭제에 대한 권한

ALL : 모든 권한

USAGE : 권한을 부여하지 않고 계정만 생성

GRANT 권한 ON 데이터베이스.테이블 TO '사용자'@'LOCALHOST';

 

# 현재 PC에서만 접근

GRANT ALL ON *.* TO '계정명'@'LOCALHOST';   

예시)GRANT ALL ON *.* TO 'APPLE'@'LOCALHOST';     

 

# 모든 IP에서 접근 가능

GRANT ALL ON *.* TO '계정명' @ '%';
예시)GRANT ALL ON *.* TO 'APPLE' @ '%';

# 변경사항이 즉시 적용됨

FLUSH Privileges

# 사용자 삭제하기

DROP USER '계정명'@'LOCALHOST';

예시)DROP USER 'APPLE'@'LOCALHOST';

 

# 사용자 권한 조회하기

SHOW GRANTS FOR '계정명'@'LOCALHOST';

예시)SHOW GRANTS FOR 'APPLE'@'LOCALHOST';

 

# 사용자 권한 제거하기

REVOKE 권한명 Privileges on *.* from '계정명'@'LOCALHOST';

예시)REVOKE ALL Privileges on *.* FROM 'APPLE'@'LOCALHOST';

예시)REVOKE ALL Privileges on *.* FROM 'APPLE'@'%';                   # 모든 권한 삭제

 

# 실습

1. TEMP 데이터베이스 생성

2. TEMP 데이터베이스에 모든 권한을 가진 사용자 'SUPERMAN'을 생성하고, MEMBER 테이블(기본 쿼리를 사용)을 생성하고 데이터 1개를 추가

 

CREATE DATABASE TEMP;

USE TEMP;

CREATE TABLE TEMP.MEMBER(SELECT * FROM KDT.MEMBER);

SELECT * FROM MEMBER;

CREATE USER 'SUPERMAN'@'%' Identified BY '1004';

GRANT ALL ON TEMP.* TO 'SUPERMAN' @ '%';

 

 

 

영단어를 데이터베이스