N'Che는 초보개발자

데이터베이스 설계 

사용자의 다양한 요구사항을 고려하여 데이터베이스를 생성하는 과정

과정 내에서 피드백이 가능하다.

요구사항 분석 목적

• 사용자의 요구 사항을 수집 분석하여 개발할 데이터베이스의 용도 파악
• 업무에 필요한 데이터, 데이터를 이용한 처리 방향 등을 고려
• 결과물 : 요구사항 명세서

주요작업

• 데이터베이스를 실제로 사용할 사용자의 범위를 결정
• 사용자가 조직에서 수행하는 업무를 분석
• 면담, 설문조사, 업무 관련 문서 분석 등의 방법을 이용해 요구사항 수집
• 수집된 요구사항에 대한 분석 결과를 요구사항 명세서로 작성

요구사항 분석 예시 ((p.nn))

개념적 설계

개념적 설계 목적

• DBMS에 독립적인 개념적 스키마 설계
• 요구사항 명세서를 개념적 구조로 표현
• 결과물 :  E-R 다이어그램

주요 작업

• 요구사항 분석 결과를 기반으로 중요한 개체를 추출하고 개체간의 관계를 결정하여 E-R 다이어그램으로 표현

개체와 속성 추출

• 저장할만한 가치가 있는 중요 데이터를 가진 사람이나 사물
• 예) 학사행정 데이터베이스 개발에 필요한 개체
  • 학사행정 운영에 필요한 사람 :  학생, 교수, 행정담당자 등
  • 학사행정 운영에 필요한 사물 :  강의실, 행정실, 교자재 등

개체 추출 방법

• 요구 사항 문장에서 업무와 관련이 깊은 의미 있는 명사
  • 업무와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외
  • 의미가 같은 명사가 여러 개일 경우는 대표 명사 하나만 선택
  • 추출된 명사를 개체와 속성으로 분류

개체와 속성 추출 예 ((p.nn))

JOIN

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것 (사용자가 필요한 집합 결과 도출)

→범위산정

inner join

SELECT
	*
FROM
	tb_member_level AS l
	INNER join
	tb_member AS m
	on
	l.level_num = m.m_level
WHERE
	l.level_num > 1;

SELECT
	*
FROM
	tb_member_level AS l
	LEFT JOIN
	tb_member AS m
	on
	l.level_num = m.m_level;

null 비교는 is 로!

SELECT
	*
FROM
	tb_member_level AS l
	LEFT JOIN
	tb_member AS m
	on
	l.level_num = m.m_level
	WHERE
	m_id IS NULL;

SELECT
	*
FROM
	tb_member_level AS l
	LEFT JOIN
	tb_member AS m
	on
	l.level_num = m.m_level
	WHERE
	m_id IS not null;

	

>> inner join과 결과값이 같다.

self join

SELECT
	m1.m_id AS member1
	,m2.m_id AS memeber2
	,m1.m_addr AS address
FROM
	tb_member AS m1
	join
	tb_member AS m2
	on
	m1.m_id <> m2.m_id
	and
	m1.m_addr = m2.m_addr
	ORDER BY  m1.m_id;

cross join

SELECT
	*
FROM
	tb_member_level AS l
	CROSS JOIN 
	tb_member AS m
ORDER BY l.level_num,m.m_id; 

A행xB행 (곱집합)

SQL 실습

테이블 생성 - DDL

속성이 레벨번호, 레벨이름, 레벨등록날짜로 구성되고 기본키가 권한인 회원 권한테이블을 생성하시오.

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

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

CREATE TABLE tb_member (
	m_id VARCHAR(200) NOT NULL
	,m_pw VARCHAR(200) NOT NULL
	,m_name VARCHAR(200) NOT NULL
	,m_level INT(11) NULL DEFAULT NULL
	,m_email VARCHAR(200) NOT NULL
	,m_addr VARCHAR(200) NOT NULL
	,m_reg_date DATE NOT NULL
	,PRIMARY KEY (m_id)
	,CONSTRAINT FK_m_level FOREIGN KEY (m_level) REFERENCES tb_member_level(level_num)
);

속성이 상품코드, 상품이름, 상품가격, 판매자 아이디, 상품등록날짜 구성되고 기본키가 상품코드, 판매자 아이디는 회원테이블의 아이디를 참조하는 상품테이블을 생성하시오.

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_coed 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_num, level_name, level_reg_date)
VALUES
(1, '관리자', '2020-03-17')
,(2, '판매자', '2020-03-17')
,(3, '구매자', '2020-03-17');

각 데이터를 각 테이블에 삽입하는 SQL 문장 작성.
외래키가 참조된 순서에 맞추어 insert문을 작성해주어야 한다.

SQL 집계함수

SELCET 구문이나 HAVING절에서 사용한다.

SELECT
	o.o_id AS '구매자 아이디'
	,sum(o.o_amount) AS '총 주문수량'
	,AVG(o.o_amount) AS '평균 주문수량'
	,ROUND(AVG(o.o_amount),1) AS '평균 주문수량'
	,COUNT(o.o_amount) AS '총 주문횟수'
	,MAX(o.o_amount) AS '최대 주문수량'
	,MIN(o.o_amount) AS '최소 주문수량'
FROM
	tb_order AS o
GROUP BY o.o_id
;

round( n , 반올림 할 소수점 자리수 )

실행 순서 인지하기 (tb_order AS o 선언 시점이 사용 시점 이전에 있어야 한다!)

SQL 내장함수

데이터 형식 변환 함수

-- 형변환

SELECT
		NOW() AS 'DATETIME'
		,CAST(NOW() AS DATE) AS 'DATE'
		,CAST(NOW() AS TIME) AS 'TIME';
        
        
SELECT
    CONVERT (AVG(o.o_amount), SIGNED INTEGER) AS '평균 주문 개수'
FROM
    tb_order o;
    
 --   정수 타입: 음수까지 표현할 수 있는 SIGNED 타입
 --    		  양수만을 표현할 수 있는 UNSIGNED 타입

문자열 연결함수

-- 문자열 연결함수

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

암시적인 형변환

-- 문자와 문자를 더함 (정수로 변환되서 연산됨)
SELECT '100' + '200’ ;
-- 문자와 문자를 연결 (문자로 처리)
SELECT CONCAT('100', '200’); 
-- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)
SELECT CONCAT(100, '200’);

제어 흐름 함수

SELECT IF (조건식, true시 반환하는 값, false시 반환하는 값);

-- 조건이 null이 아닐 경우 조건을 반환.
SELECT IFNULL(조건, null일경우 반환하는 값);

-- 조건1과 조건2가 같을 경우 null값을 반환, 다를경우 조건1을 반환
SELECT NULLIF(조건1,조건2);

문자열 함수

-- 문자를 ascii 코드로 변환 / ascii코드를 문자로 변환
SELECT ASCII('A'), CHAR(65);

-- 공백을 제거
SELECT TRIM('   KSMART35   ') AS result;

-- 반복
SELECT REPEAT('KSMART', 3) AS result;

-- 문자열 대체
SELECT REPLACE ('KSMART35기', 'KSMART' , '한국스마트정보교육원') AS result;

-- 문자열을 거꾸로 출력
SELECT REVERSE ('KSMART35') AS result;

-- 공백을 추가, 문자열 연결
SELECT CONCAT('KSMART', SPACE(15), '35기') AS result;

날짜 함수

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

  ,SUBDATE('2020-03-18', INTERVAL 31 DAY)
  ,SUBDATE('2020-03-18', INTERVAL 1 MONTH)

  ,ADDTIME('2020-03-18 23:59:59', '1:1:1')
  ,ADDTIME('15:00:00', '2:10:10')

  ,SUBTIME('2020-03-18 23:59:59', '1:1:1')
  ,SUBTIME('15:00:00', '2:10:10');

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

SELECT 
   HOUR(CURTIME())
  ,MINUTE(CURRENT_TIME())
  ,SECOND(CURRENT_TIME())
  ,MICROSECOND(CURRENT_TIME());

SELECT DATE(NOW()), TIME(NOW());

curdate() <현재날짜

year >int data type 으로 반환

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

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

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

시스템 함수

SELECT SLEEP(3);

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

n초 뒤에 결과 반환

MySQL 변수

SET @myNumber1 = 1;
SET @myNumber2 = 2.3;
SET @myStr1 = '합계:';

SELECT
    @myNumber1 AS '변수1'
   ,@myNumber2 AS '변수2';
 
SELECT
    @myStr1
   ,@myNumber1 + @myNumber2) AS '합계';
 
SELECT
    @myStr1
   ,ROUND((@myNumber1 + @myNumber2), 3) AS '합계';

SELECT 문 조건 연산자

SELECT 문 조건 LIKE 키워드

SQL JOINS

INNER JOIN

SELECT
	m.m_id			AS '회원 아이디'
	,m.m_name		AS '회원 이름'
	,l.level_name	AS '회원 권한'
FROM
	tb_member m
	INNER join
	tb_member_level l
	on
	m.m_level = l.level_num
WHERE
	l.level_name LIKE '%판매%';

데이터모델링

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

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

관계 (relationship)

• 개체와 개체가 맺고 있는 의미 있는 연관성
• 개체 집합들 사이의 대응관계, 즉 매핑(mapping)을 의미
• 예) 고객 개체와 상품 개체 간의 구매 관계 > "고객은 상품을 구매한다."

관계의 유형

• 일대일 (1 : 1) 관계
• 일대다 (1 : N) 관계
• 다대다 (N : M) 관계

E-R 다이어그램

E-R 다이어그램 도형 기호

관계형 데이터 모델

데이터 베이스의 구성

데이터베이스 스키마 (database schema)

• 데이터베이스의 전체 구조
• 데이터베이스를 구성하는 릴레이션 스키마의 모음

데이터베이스 인스턴스 (database instance)

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

키

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

키의 특성

키의 종류

외래키 ( foreign key)

• 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합
• 릴레이션들 간의 관계를 표현
  • 참조하는 릴레이션 : 외래키를 가진 릴레이션
  • 참조되는 릴레이션 : 외래키가 참조하는 기본키를 가진 릴레이션

제약조건

MySQL 데이터 타입

숫자 데이터 형식

문자 데이터 형식

한글 데이터를 BINARY로 변환하여 ORDER BY 할 수 있음

날짜 데이터 형식

랜덤한 data를 얻을 수 있는 website

https://www.mockaroo.com/

db 생성

CREATE DATABASE db01 DEFAULT CHARACTER SET UTF8;

CREATE DATABASE db01 DEFAULT CHARACTER SET UTF8;
/* 영향 받은 행: 1  찾은 행: 0  경고: 0  지속 시간 1 쿼리: 0.016 sec. */

table 생성

CREATE TABLE costomer (
	c_id			VARCHAR(100) NOT NULL PRIMARY KEY 
	,c_pw 		VARCHAR(100) NOT NULL 
	,c_name		VARCHAR(100) NOT NULL 
	,c_birth		DATE			
	,c_add 		VARCHAR(100) NOT NULL 
	,c_phone		VARCHAR(100) NOT NULL 
	,c_balance		INT			NOT NULL 	
	,c_update		DATE		NOT NULL 
);

mokaroo에서 field name, type, table name을 알맞게 설정하고 랜덤한 데이터를 생성

연습용으로 500개의 데이터를 생성했다.

다운로드한 costomer.sql 파일을 MySQL의 쿼리 창에 드래그앤 드랍 한다.

MySQL의 쿼리창에 costomer.sql 이 불러오기 된다.

f9 로 실행시 데이터들이 추가된다!

.
.
.
.
insert into costomer (c_id, c_pw, c_name, c_birth, c_add, c_phone, c_balance, c_update) values ('com.sun.Overhold', 'DvUBBNrUW', 'Degoey', '1989-12-01', '77668 Arrowood Plaza', '985-746-8085', 69223481, '2019-12-26');
insert into costomer (c_id, c_pw, c_name, c_birth, c_add, c_phone, c_balance, c_update) values ('ca.cbc.Stringtough', 'bjCAP2a', 'Toulamain', '1976-10-17', '504 Londonderry Drive', '786-305-4802', 12367696, '2019-11-29');
insert into costomer (c_id, c_pw, c_name, c_birth, c_add, c_phone, c_balance, c_update) values ('com.discovery.Bigtax', 'mR9EyMKSn', 'Dring', '1969-11-25', '75165 Randy Hill', '683-451-3427', 80079873, '2019-08-11');
insert into costomer (c_id, c_pw, c_name, c_birth, c_add, c_phone, c_balance, c_update) values ('edu.uiuc.Cardify', 'sx9368zeS', 'Carruthers', '1997-12-08', '4 Grim Drive', '534-197-3281', 79275848, '2020-01-30');
/* SQL 오류 (1062): Duplicate entry 'edu.uiuc.Cardify' for key 'PRIMARY' */
/* 영향 받은 행: 354  찾은 행: 0  경고: 0  지속 시간 354 of 500 쿼리: 0.672 sec. */

Primary key 값으로 edu.uiuc.Cardify값이 중복되어 354번째 행에서 실행이 멈추었다.

(랜덤데이터에서 중복이 나오다니!)

Heidi SQL : SQL을 편리하게 작업할 수 있도록 GUI와 기능을 제공

MySQL : Database 서버를 만들고 접근할 수 있다

root 최상위관리자

DB생성

F9 > 실행

쿼리 2초 이상 : error로 간주

파일> 세션관리자 >> 데이터베이스 > 사용할 세션만 체크 > 저장

세션이 연결되어 시작됨

단축키 설정

db  > 보통 snake 표기법 사용 (abc_abc를 이용해 연결) 절대적이진 않음.

java > camel (abcAbc)

DDL - CREATE 구문

쿼리에서 코드를 작성하여 생성하기 

 gui를 활용하여 db>새로생성>테이블로 생성하기

<CREATE 코드> 탭에서 생성된 코드를 확인할 수 있다.

DML - INSERT 구문

데이터 삽입하기.

NOW()  > 현재 연월일시분초.

같은 코드를 두번 실행했을 경우 오류.

primary key 값은 중복되어 삽입되지 않음.

여러 데이터를 한번에 insert

DML - SELECT 구문

as > 별칭부여

* > 전체

DML - UPDATE 구문

**u_name 속성에 홍05 값을 가진 데이터가 있는지 확인**

u_name 속성이 홍05인 데이터의 u_add 속성의 값을 금암동으로 변경.

AS를 이용해 호출을 짧게!

DML - DELETE 구문

**usertb 테이블에 u_id이 값이 id011인 데이터가 있는지 확인**

**delete 구문을 사용할 때 where 조건이 있는지 꼭 확인!**

f9:실행

DML DELETE vs. DDL TRUNCATE

delete : 행들을 하나씩 전부 삭제

truncate : 테이블을 전체 드랍한 뒤 새로 생성함

복제

전체 복제

CREATE TABLE usertb_backup_all 
(SELECT 
      * 
 FROM 
    usertb); 

부분 복제

CREATE TABLE usertb_backup
(SELECT
    b.u_id
   ,b.u_pw
   ,b.u_name
 FROM
   usertb AS b);

ON DUPLICATE KEY UPDATE

중복 키 오류 발생 시 사용자가 원하는 값을 직접 설정

INSERT INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES
('id001', 'pw001', '홍01', '2020-01-10', '덕진동', '010', '00010001', '2020-03-10')
ON DUPLICATE KEY UPDATE
u_birth = '2019-01-10’;

IGNORE

중복되면 오류 무시/ 중복되지 않으면 실행

INSERT IGNORE INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES('id010','pw010','홍10','2020-10-10','덕진동','010','00010001','2020-03-10');
INSERT IGNORE INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES('id011','pw011','홍11','2020-11-10','덕진동','010','00010001','2020-03-10');

ON DUPLICATE KEY UPDATE vs. INSERT IGNORE vs. REPLACE INTO

http://jason-heo.github.io/mysql/2014/03/05/manage-dup-key2.html

ORDER BY

오름차순 DESC

내림차순 ASC

LIMIT

limit의 인자값 5에 맞추어 5개 행만 보이게 된다.

행은 0번부터 시작된다.

출력되는 행을 잘 확인하자

DISTINCT

중복 데이터 하나만 조회

GROUP BY

HAVING