20211019 데이터베이스 02

1. 데이터 모델링

1) 데이터 모델링 : "복잡한 현실 세계에 존재하는 데이터를 단순화 시켜 표현해 컴퓨터 세계의 데이터베이스로 옮기는 변환 과정”

2) 데이터 모델링의 특징

추상화(Abstraction)

현실세계를 간략하게 표현한다.

단순화 ( Simple )

누구나 쉽게 이해할 수 있도록 표현한다.

명확성(Clarity)

명확하게 의미가 해석되어야 하고 한 가지 의미를 가져야 한다.

 

 

데이터모델링

 

 

 

개념적 데이터 모델

사람의 머리로 이해할 수 있도록 현실 세계를 개념적인 형태로 모델링하여 데이터베이스의 개념적 구조로 표현하는 도구

 

 

 

논리적 데이터 모델

개념적 구조를 논리적 형태로 모델링하여 데이터베이스의 논리적 구조로 표현하는 도구

 

 

---

데이터모델링(개체-관계 모델)

관계(relationship)

- 개체와 개체가 맺고 있는 의미 있는 연관성

- 개체 집합들 사이의 대응 관계, 즉 매핑(mapping)을 의미

예) 고객 개체와 상품 개체 간의 구매 관계 “고객은 상품을 구매한다”

 

관계의 유형(사상원소수 (관계의 분류 기준))

- 일대일 (1:1) 관계

- 일대다 (1:N) 관계

- 다대다 (N:M) 관계

 

 

정보공학표기법

관계형 데이터 모델 

카디널리티 : 튜플의 수

디그리 : 속성의 수

도메인 : 릴레이션에 포함된 각각에 속성들이 가질 수 있는 값들의 집합

 

데이터베이스 스키마 : 데이터베이스 전체구조

- 데이터베이스를 구성하는 릴레이션 스키마(테이블 구조)의 모음

- 데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 형식 언어 (null 허용, varchar...)

 

데이터베이스 인스턴스 : 데이터베이스를 구성하는 릴레이션 인스턴스의 모음

 

릴레이션의 특성

튜플의 유일성 : 하나의 릴레이션에는 동일한 튜플이 존재할 수 없다.

튜플의 무순서 : 하나의 릴레이션에서 튜플 사이의 순서는 무의미하다.

속성의 무순서 : 하나의 릴레이션에서 속성 사이의 순서는 무의미하다.

속성의 원자성 : 속성 값으로 원자 값(더 이상 분리되지 않는 값)으로 사용할 수 있다.

 

 

---

키 : 릴레이션에서 튜플들을 유일하게 구별하는 속성 또는 속성들

키의 특성

유일성 :

- 하나의 키 값으로 하나의 튜플만을 유일하게 식별

- 한 릴레이션에서 모든 튜플은 서로 다른 키 값을 가져야 함

 

최소성 : 

- 꼭 필요한 최소한의 속성들로만 키를 구성

( id 만으로 구분 가능한데 id+password+email로 구분 하지 않음)

 

 

키의 종류

기본키 : 후보키 중에서 기본적으로 사용하기 위해 선택한 키
‒예) 고객 릴레이션의 기본키: 고객아이디

 

후보키 : 유일성과 최소성을 만족하는 속성 또는 속성들의 집합 / 기본키+대체키
‒예) 고객 릴레이션의 후보키: 고객아이디, (고객이름, 주소) 등

 

대체키 :기본키로 선택되지 못한 후보키
- 예) 고객 릴레이션의 대체키: (고객이름, 주소)

 

슈퍼키 :유일성을 만족하는 속성 또는 속성들의 집합
‒ 예) 고객 릴레이션의 슈퍼키: 고객아이디, (고객아이디, 고객이름), (고객이름, 주소) 등 (식별가능)

 

 

외래키(FK,  ️Foreign Key) :

다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합

릴레이션들 간의 관계를 표현

‒ 참조되는 릴레이션: 외래키가 참조하는 기본키를 가진 릴레이션
‒ 참조하는 릴레이션: 외래키를 가진 릴레이션

회원 : 참조되어진다 (PK - 기본키)        /             상품 : 참조한다.(FK - 외래키)

 

특성	• 유일성: 한 릴레이션에서 모든 튜플은 서로 다른 키 값을 가져야 함 • 최소성: 꼭 필요한 최소한의 속성들로만 키를 구성
종류	• 기본키: 후보키 중에서 기본적으로 사용하기 위해 선택한 키 • 후보키: 유일성과 최소성을 만족하는 속성 또는 속성들의 집합 • 외래키: 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합 • 대체키: 기본키로 선택되지 못한 후보키 • 슈퍼키: 유일성을 만족하는 속성 또는 속성들의 집합

 

 

---

제약조건

무결성 제약조건 :

- 데이터의 무결성을 보장하고 일관된 상태로 유지하기 위한 규칙

- 무결성 : 데이터를 결함이 없는 상태, 즉 정확하고 유효하게 유지하는 것

 

 

도메인 무결성 제약조건 :

- 특정 속성의 값이 그 속성이 정의된 도메인에 속한 값이어야 한다는 규칙

- null 값은 허용됨 ( not null이 아닌 경우)

- ex) 성별은 남, 여의 값만 가질 수 있다.

개체 무결성 제약조건 : 

- 기본키를 구성하는 모든 속성은 NULL값을 가질 수 없는 규칙

- 아래 테이블에서 학번이 기본키면 NULL값을 가질 수 없다.

* 참조 무결성 제약조건 :

- 외래키는 참조할 수 없는 값을 가질 수 없는 규칙

하지만, 외래키의 속성이 널 값을 가진다고 해서 참조 무결성을 위반한 것은 아니다.
참조할 수 없는 데이터를 넣었을 때가 문제이다.

 

• RESTRICTED - 레코드를 변경 또는 삭제하고자 할 때 해당 레코드를 참조하고 있는 개체가 있다면,

변경 또는 삭제 연산을 취소

• CASECADE - 레코드를 변경 또는 삭제하면, 해당 레코드를 참조하고 있는 개체도 변경 또는 삭제

• SETNULL - 레코드를 변경 또는 삭제하면, 해당 레코드를 참조하고 있는 개체의 값을 NULL로 설정

 

 

---

MySQL 실습

데이터 타입 (숫자)

데이터 타입(문자)

char, varchar 차이

char : 문자열의 길이를 설정한만큼 메모리의 수를 그대로 가져간다.

varchar : 가변길이 문자형. 메모리의 수가 저절로 변경된다. 메모리의 효율이 좋다.

 

 

데이터타입(날짜)

데이터 추적이 가능하도록 날짜값을 필수로 입력해준다.

timestamp : mysql이 설치되어 있는 컴퓨터의 국제표준시간 기준.

 

---

테이블생성 - DDL

회원 레벨 테이블은 레벨번호, 레벨이름, 등록날짜 속성으로 구성되고,

레벨번호는 기본 키이며 튜플이 추가될 때마다 자동 증가하도록 테이블을 생성하시오. 

CREATE TABLE tb_member_level (
	level_num INT(11) NOT NULL AUTO_INCREMENT,
	level_name VARCHAR(200) NULL DEFAULT NULL,
	level_reg_date DATE NULL DEFAULT NULL,
	PRIMARY KEY (level_num)
);

AUTO_INCREMENT : 숫자 자동증가. 1,2,3...

중간에 튜플 2번을 삭제하고 INSERT 하면 2번이 아닌, 3번부터 추가된다.

(코드를 관리할 필요 없는 정보의 경우 사용. 내부적으로 식별이 필요. EX) 주문이력 등)

 

 값을 넣었다 삭제하면 삭제한 값 다음으로 숫자가 증가하게 되는데, 해당 속성의 데이터 유형을 바꿨다 다시 INT로 설정하고 기본값을 AUTO_INCREMENT로 설정하면 순서대로 된다

 

 

level_num을 등록하지 않고 쿼리 실행 했을 때 : 1이 자동으로 등록된다.

INSERT INTO TB_MEMBER_LEVEL
(LEVEL_NAME, LEVEL_REG_date) VALUES
('관리자',
CURDATE());

 

 

테이블생성2.

회원 테이블은 아이디, 비밀번호, 이름, 레벨, 이메일, 주소, 등록날짜 속성으로 구성되고, 아이디는 기본 키이고, 레벨번호는 레벨테이블의 레벨번호를 참조하는 테이블을 생성하시오.

CREATE TABLE tb_member(
m_id VARCHAR(200) NOT NULL COMMENT '회원 아이디',
m_pw VARCHAR(200) NOT NULL COMMENT '회원 비밀번호',
m_name VARCHAR(200) NOT NULL COMMENT '회원 이름',
m_level INT(11) NULL DEFAULT NULL COMMENT '회원등급',
m_email VARCHAR(200) NOT NULL comment '회원 이메일',
m_addr VARCHAR(200) NOT NULL COMMENT '회원 주소',
m_reg_date DATE NOT NULL COMMENT'회원등록날짜',
PRIMARY KEY(m_id),
CONSTRAINT FK_m_level FOREIGN KEY (m_level) REFERENCES tb_member_level
(LEVEl_num)
);

 

CONSTRAINT : 제약조건을 추가

FK_m_level FOREIGN KEY (m_level)

REFERENCES tb_member_level (level_nu  m) : 참조되는 테이블 

 

참조되는 테이블을 먼저 생성해야 한다.

 

 

외래키 추가 방법

 

 

 

테이블 생성 - 상품

상품 테이블은 상품코드, 상품이름, 상품가격, 판매자 아이디, 상품등록날짜 속성으로 구성되고,

상품코드는 기본 키이고, 판매자 아이디는 회원테이블의 아이디를 참조하는 테이블을 생성하시오.

CREATE table TB_GOODS(
g_code VARCHAR(50) NOT NULL,
g_name VARCHAR(50) NOT NULL,
g_price INT(11) NOT NULL,
g_seller_id VARCHAR(50) NOT NULL,
g_reg_date DATE NOT NULL,
PRIMARY KEY (g_code),
CONSTRAINT FK_G_SELLER_ID FOREIGN KEY (g_seller_id) REFERENCES
tb_member (m_id)
);

 

테이블 생성 - 주문

CREATE TABLE tb_order(
o_num INT(11) NOT NULL AUTO_INCREMENT,
o_id varchar(200) NULL DEFAULT NULL,
o_g_code VARCHAR(200) NULL DEFAULT NULL,
o_amount INT(11) NULL DEFAULT NULL,
o_reg_date DATETIME NULL DEFAULT NULL,
PRIMARY KEY (o_num),
CONSTRAINT fk_o_g_code FOREIGN KEY (o_g_code) REFERENCES tb_goods (g_code),
CONSTRAINT fk_o_id FOREIGN KEY (o_id) REFERENCES tb_member (m_id)
);

 

테이블 생성 - 로그인

CREATE TABLE tb_login(
login_num INT(11) NOT NULL AUTO_INCREMENT,
login_id varchar(200) NOT NULL,
login_date DATE NOT NULL,
logout_date DATE NOT NULL,
PRIMARY KEY(login_num),
CONSTRAINT fk_login_id FOREIGN KEY (login_id) REFERENCES tb_member (m_id)
);

 

---

다음과 같이 로그인 테이블에 삽입하는 SQL문장을 작성하시오.

INSERT INTO tb_member_level(
level_name,level_reg_date)
VALUES
,('판매자',curdate())
,('구매자',curdate());

SELECT * FROM tb_member_level
WHERE level_reg_date = '2020-03-17' ;

 

- 특정테이블 복사시, 키(기본키..)에 관한 내용은 복제가 되지 않는다.

- 테이블의 삭제 -> drop table 테이블명;

 

---

집계함수 - 모든 RDBMS 공통, 숫자에만 적용

round  - 소수점 자리 n 번째에서 반올림

truncate - 소수점 절삭

 

 

---

데이터 형식 변환 함수 - cast, convert

1) cast

SELECT CAST('2021-10-19 15:23:35' AS DATE) AS '날짜';

<- date 타입 : YYYY-MM-DD

 

 

 

SELECT CAST('2021-10-19 15:23:35' AS DATEtime) AS '날짜1';

<- DATATIME : YYYY-MM-DD HH:MM:SS

 

SELECT CAST('2021-10-19 15:23:35' AS time) AS '날짜2';

<- TIME : HH:MM:SS

 

 

 

2) convert

SELECT 
	CONVERT (AVG(o_amount), SIGNED INTEGER) AS'평균주문개수'
	FROM tb_order;

16.9 -> 17 로 반올림된다.

 

 

3) CONCAT - 문자열을 합친다.

SELECT
	CONCAT (m_id, ':',m_name) AS '아이디:이름'
FROM
	tb_member;

<- 하나의 행을 기준으로 합쳐진다.

 

 

 

 

---

암시적인 형변환 - 비교x

1) 숫자로 자동변환된다. 

1-1) concat 사용시

2) 숫자와 문자 비교시 자동으로 변환 ( 1 - true / 0 - false )

 

제어흐름함수

1) IF

10>20 이 true 이면 'True'를 반환하고, false 면 'False'를 반환한다.

SELECT IF (10>20, 'True', 'False');

2) IFNULL

NULL 이면 '널'로 반환하고, 값이 있으면 값을 그대로 보여준다.

SELECT IFNULL(NULL, '널'), IFNULL(100, '널');

3) NULLIF

비교하는 두 값이 같으면 NULL.

같지 않으면 앞의 값을 반환한다.

SELECT NULLIF(10,10), NULLIF(30,10);

4-1) CASE - 옆에 값이 있는 경우 when 과 비교 한다. 

else 반환값 -> 맞는 조건이 없으면 반환한다.

SELECT
CASE 3
WHEN 1  THEN  '일'
WHEN 3  THEN  '삼'
WHEN 5  THEN  '오'
ELSE '조건에 맞지 않는다'
END AS 'CASE 구문';

4-2) case 사용방법

SELECT
CASE 
WHEN  (m.m_level = 1) THEN  '관리자'
WHEN  (m.m_level = 2) THEN  '판매자'
WHEN  (m.m_level = 3) THEN  '구매자'
ELSE '등급이 없음'
END AS '등급'
FROM 
	tb_member AS m
WHERE
	m.m_id = 'id001';

 

---

문자열 함수

1) * trim - 양쪽의 공백을 제거

SELECT TRIM('   KSMART41   ') AS result;

2) repeat - 반복

SELECT REPEAT('KSMART', 3) AS result;

3) * replace - 'KMSART41기' 중 KSMART를 '한국스마트정보교육원'으로 변환한다.

SELECT REPLACE ('KSMART41기', 'KSMART' , '한국스마트정보교육원') AS result;

4) REVERSE

SELECT REVERSE ('KSMART41') AS result;

5) * CONCAT - 문자열 합치기.

SPACE - 공백을 넣어준다.

SELECT CONCAT('KSMART', SPACE(15), '41기') AS result;

6) ASCII - 아스키 코드 변환

SELECT ASCII('A'), CHAR(65);

---

날짜함수

1) ADDDATE - 날짜더하기   

기준날짜 , INTERVAL N DAY (N일 후)

SELECT 
   ADDDATE('2020-12-18', INTERVAL 31 DAY)
  ,ADDDATE('2020-12-18', INTERVAL 1 MONTH);

SELECT 
   ADDDATE(CURDATE(), INTERVAL 27 DAY)
  ,ADDDATE(CURDATE(), INTERVAL 1 MONTH);

 

2) SUBDATE - 날짜빼기    기준날짜, INTERVAL N DAY (N일 전)

SELECT 
   SUBDATE(CURDATE(), INTERVAL 27 DAY)
  ,SUBDATE(CURDATE(), INTERVAL 1 MONTH);

3) YEAR, MONTH, DAYOFMONTH

SELECT 
   YEAR(CURDATE())
  ,MONTH(CURDATE())
  ,DAYOFMONTH(CURDATE());

4) DAYOFYEAR : 날짜의 해당 연도 시작에서부터의 일 수를 반환

SELECT 
  DAYOFYEAR(CURDATE());

5) DATEDIFF : (날짜1 - 날짜2) 의 차이

TIMEDIFF : (시간1 - 시간2) 의 차이

SELECT 
   DATEDIFF('2020-12-06', NOW())
  ,TIMEDIFF('17:30:01', CURRENT_TIME());

6) DAYOFWEEK, MONTHNAME, DAYOFYEAR

SELECT 
   DAYOFWEEK(CURDATE())
  ,MONTHNAME(CURDATE())
  ,DAYOFYEAR(CURDATE());

DAYOFWEEK  - 일 월 화...순으로 열거한다. 화요일이면 3이 반환. 

 

7) LAST_DAY : 입력한 날짜가 속한 달의 마지막 날짜 반환

SELECT LAST_DAY('2020-12-18');

---

시스템 함수 

1)SLEEP(초) : 해당 초 동안 쿼리문 지연

SELECT SLEEP(3);

SELECT 
    a.*
FROM 
    tb_member a,(SELECT SLEEP(3)) b

2) SET @변수선언 = '값';

- 세션이 연결되어 있는 동안 해당 값이 저장되어 있다.

세션이 해제하고 다시 연결하면 저장된 값은 없어지므로 세션변수라고 한다.

SET @myNumber1 = 1;
SELECT @myNUMBER1

= 을 저장할 때 비교연산자와 혼동 될 수 있으므로, := 으로 저장할 수 있다.

 

---

SELECT문 조건 연산자

JAVA에서 조건1&&조건2 는 조건1이 FALSE 면 조건2를 처리하지않고 조건비교를 중지한다.

& 는 모든 조건을 비교한다.

 

SELECT문 LIKE 키워드

EX) _ _ 한 % : 우아한 테크(O), 대한민국(X)

 

 

 

 

---

쿼리문 조회시 > 

FROM->WEHRE-GROUB>HAVING>SELECT 순으로 조회한다.