[Day 16 | MySQL] Table, Variable, 데이터 모델링,

p238 239

나이에 int를 주면 낭비다. int는 ~21억에서 21억이니까.

데이터 사이즈에 따라 맞춤형으로 데이터타입을 주는 것이 좋다. 

디스크와 메모리 공간이 낭비가 된다.

 

실제로 디스크에 4바이트가 들어가있다면, 

메모리에도 숫자 10이라도 4바이트가 올라간다.  --> 낭비

 

모델링이나 데이터베이스 할 때는 int 많이 하지만, 

성능까지 고민하는 데이터 설계는 데이터타입은 적절한 것을 고려해서 해야.

 

PK와 not null unique의 차이는? 

백단의 인덱스 구조가 조금 다름

PK는 테이블 당 보통 하나 만드는 키 제약조건이라고 생각하면 됨 

 

 

 

-- --------------------
-- 테이블 생성 -- 

create table userTbl(
	uNo int auto_increment primary key, -- 제약 조건이 들어가면 	
	uID varchar(10) not null unique,
	uName varchar(10) not null,
	uAge int not null check(uAge between 0 and 120), 
	uAddr char(4) default '서',
	mob1 char(3) check(mob1 in ('010','011', '019')),
	mob2 char(8),
	uDate datetime not null default now()
);

그럼 테이블이 생기잖아

여기서 제약 조건 잘 들어갔는지 확인! 

그런데 Name이 뭔지 알 수 없다. 

 

 

그래서 

이렇게 제한 조건들을 다 따로 빼주자 

-- 이름 만들 수 있는 테이블 만들기 -- 

create table userTbl(
	uNo int auto_increment , -- 제약 조건이 들어가면 	
	uID varchar(10) not null,
	uName varchar(10) not null,
	uAge int not null , 
	uAddr char(4) default '서울',
	mob1 char(3) check(mob1 in ('010','011', '019')),
	mob2 char(8),
	uDate datetime not null default now(),

	
	CONSTRAINT userTb1_uNo_pk primary key(uNo),  -- 제약 조건을 만들어라 
	CONSTRAINT userTb1_uID_uk UNIQUE KEY (uID),	
	constraint userTb1_uAge_ck check (uAge between 0 and 120),
	CONSTRAINT userTb1_uMob1_ck CHECK (mob1 in ('010', '011', '019'))
	
	
	);

 

 

 

 

 

엔티티 관계도

 

테이블 간 연결고리를 확인할 수 있다. 

 

 

auto increment 를 쓰면 (자동으로 숫자 증가) 여기에 쳌 표시 되어있음

auto increment  쓰고 데이터 산입했는데 에러가 난다면, 

에러도 카운팅되어서, 

그 다음 데이터 insert 시에 다음 넘버로 넘어간다. 이 때는 다른 조치 방법을 써줘야 함. 

 

-- > 한 수강생님의 답변 : mysql 이 내부에서 inno DB 엔진일때 auto increment가 정상적으로 집계되지 않을 때가 꽤 많아요 그래서 보통 auto_increment 를 기존 값으로 치환하거나, 기존 행을 delete cascade로 삭제합니다. (5v. 기준) 

 

 

on delete cascade는 거의 쓰지 X

회원 탈퇴해도 아이디 삭제하지 말고,내역이 남아야하니까, 

ON UPDATE cascade

 

 

Table01.sql

-- Table01.sql -- 
-- DDL : 정의 --> create, alter, drop -- 


CREATE TABLE  t30(
	id int, 
	age int not null check(age>0)  -- 컬럼 데이터타입 제약조건 
);

INSERT into t30 values(10, 10);
INSERT into t30 values(null, null);

SELECT * from t30;

create table t31(
	id int,
	age int not null, 
	check(age>0)
);

INSERT into t31 values(10, 10);
INSERT into t31 values(null, null);

SELECT * from t31;

show table status;


-- --------------------
-- 테이블 생성 -- 

create table userTbl(
	uNo int auto_increment primary key, -- 제약 조건이 들어가면 	
	uID varchar(10) not null unique,
	uName varchar(10) not null,
	uAge int not null check(uAge between 0 and 120), 
	uAddr char(4) default '서',
	mob1 char(3) check(mob1 in ('010','011', '019')),
	mob2 char(8),
	uDate datetime not null default now()
);

drop table userTbl ; 

-- --------------------------

-- 이름 만들 수 있는 테이블 만들기 -- 

create table userTbl(
	uNo int auto_increment , -- 제약 조건이 들어가면 	
	uID varchar(10) not null,
	uName varchar(10) not null,
	uAge int not null , 
	uAddr char(4) default '서울',
	mob1 char(3) check(mob1 in ('010','011', '019')),
	mob2 char(8),
	uDate datetime not null default now(),

	
	CONSTRAINT userTb1_uNo_pk primary key(uNo),  -- 제약 조건을 만들어라 
	CONSTRAINT userTb1_uID_uk UNIQUE KEY (uID),	
	constraint userTb1_uAge_ck check (uAge between 0 and 120),
	CONSTRAINT userTb1_uMob1_ck CHECK (mob1 in ('010', '011', '019'))
	
	
	);

-- p331

drop table userTbl ;  

drop table buyTb1 ;  

-- buy table 만들기 
create table buyTb1(
	bNo int  auto_increment , 
	bName varchar(20) not null, 
	price int not null, 
	amount int not null, 
	uNo int not null, 
	
	constraint buyTbl_bNo_pk primary key(bNo),
	constraint buyTbl_uNo_userTb1_fk foreign key userTbl(uNo)
			   references userTbl(uNo)
);


-- ---------------------


INSERT into userTbl values ( null, 'uTom', 'Tom', 30, default , '010', '11111111', default);

INSERT into buyTb1 values (null, 'Pen', 100, 3, 1);

SELECT * from buyTb1 bt ;

-- userTbl의 데이터 삽입시 에러 확인 
INSERT into userTbl values ('uJane', 'Jane', 30, default , '010', '11111111', default); -- 데이터 개수 안맞아서 안됨
INSERT into userTbl values (null, 'uTom', 'Jane', 30, default , '010', '11111111', default); -- 제약조건에 어긋나서 X
INSERT into userTbl values (null, 'uJane', null , 30, default , '010', '11111111', default); -- 제약조건에 어긋나서 X
INSERT into userTbl values (null, 'uJane', null , 2000, default , '010', '11111111', default); -- 나이 제약조건에 어긋나서 X



-- buyTbl의 데이터 삽입시 에러 확인
INSERT into buyTb1 values ( 'Pen', 100, 3, 1); --  Column count doesn't match value count at row 1
INSERT into buyTb1 values (null, 'Pen', 100, 3, 2); -- CONSTRAINT `buyTbl_uNo_userTb1_fk` FOREIGN KEY (`uNo`) REFERENCES `usertbl` (`uNo`))
INSERT into buyTb1 values (null, NULL , 100, 3, 1); -- Column 'bName' cannot be null
INSERT into buyTb1 values (1 , 'Pen', 100, 3, 1); -- Duplicate entry '1' for key 'buytb1.PRIMARY'



------------------------------------------

-- p328

-- 테이블 생성 이후에 제약 조건을 추후 추가할 수 있다. 

create table uTbl(
	uNo int not null,
	uID varchar(10) not null,
	uName varchar(10) not null,
	uAge int not null ,
	uAddr char(4) ,
	mob1 char(3) ,
	mob2 char(8),
	uDate datetime not null 
);


-- 제약조건 없던걸 만드는 
ALTER table uTbl 
 ADD CONSTRAINT uTbl_uNo_pk primary key(uNo);


-- 디폴트값 제약조건 수정  
ALTER table uTbl 
 alter column uAddr set DEFAULT 'SEOUL'; -- 컬럼 변경 
 

 
 ALTER table uTbl 
  add column height smallint; -- 컬럼 추가 p336 
 
  
ALTER table uTbl 
 add column weight smallint; -- 컬럼 추가 
 
  
ALTER table uTbl 
 add column height; -- 컬럼 제거

\

 

 

 

 

SQL도 컴파일 언어이기 때문에, 변수 사용 가능

목적: 결과 처리 

--------------- J a v a --------------

변수

연산자(산술, 연결, 논리, 비교,,,)

조건문(if else) 

반복문 (for ,,)

예외처리 (try catch)

함수(블록) --> function

 

------------------------------------

 

 

--> 이런 것들을 할 수 있는 것이 Stored Procedure 라는 DB 객체 

p439

데이터베이스 잘하기 위해서 넘어야 할 요소: Stored Procedure (맛보기만 한다) 

^성능이 좋음 

 

 

Variable01.sql

-- Variable01 -- 

-- JAVA에서는 
int i = 10; 
SYSTEM. OUT.println(i);


-- p244
SET @i = 10; 
SELECT @i;

SET @j = 20; 
SELECT @i + @j;

set @iSal = 3000; 

SELECT * 
from emp
where sal > @isal; 

set @iSal = 2500;
prepare eQuery  -- 구문을 미리 준비 
	from 'select * from emp where sal > ?'; -- 이 쿼리에 넣어라 
execute eQuery using @iSal; -- 실행시키는데, ?에 대한 값을 @iSal변수에서 받아가라

delimiter $$ 
create procedure usp_emp1()
begin
 select * from emp;
end $$;

delimeter; 


call usp_emp1();

-----------
-- --------------------------
delimiter $$ 
create procedure usp_emp2(in iSal int)
begin
 select * from emp where sal > iSal;
end $$

-- call usp_emp2();
call usp_emp2(2500);
call usp_emp2(2999);

-- 한 번 듣고 흘려도 됩니다. 나중에 공부할때, 알아두면 좋음

 

이게 정석

1. UI?

2. CLIENT?

3. DB?

4. DB 모델링?

5. stored procedure 

 

근데 인력이 딸리니까 5번 뺌 

 

 

 

 

 

java sql을 html로 감싼다 --> 이게 JSP

java 

 

스프링이라는 프레임워크

조금 더 나아가서 

5. stored procedure 등등 db컴포넌트들을 개발하고 싶다? 

그럼 sp , view를 가지고 하면 된다. 

1,2,3,4,5 다 할거냐의 선택은 인력에 따라 선택 \

 

 

 

테이블 구성하는 것 

테이블 구성도를 만든다. 

erd

모델링 도구들은 유럽 도구가 많음 

 

 

 

 

웹:

 

1. 프로젝트 설계 기본 흐름(프로젝트 구축) 

2. html 구성정보로 어떤 데이터를 뽑을 수 있는지 데이터 근원지 알 수 있음. (하둡에 저장, 저장 시스템에 대한 이해) 

3. 파이썬 분석. (실제 저장된 내용 분석) 

 

파이썬 분석을 잘 하더라도, 프로젝트 기승전결 알기가 힘들어요. 

1. 목적이 분명하지 않은 경우가 있음.

 

방향 잡기 데이터 분석 관련 

1. 파이썬 + 분석 얹기(Code mind, 뭘 해야할지를 모르게 됨) 

2. 통계 분석 책인데 파이썬 언어 없음(Business mind, 나 파이썬을 배웠는데 왜 못다루지?)

3. 융합하려다보면, 모든걸 다 담으려해서 어려움. 

 

한국에서는 DB --> big data로 흘러감. 

처음 빅데이터 과정이 그래서 하둡으로 만들어짐. 

의문: 저장기술에 무슨 빅데이터 분석이 들어가? 

 

빅데이터는 웹, 회사내db, 일반 파일, 시스템 로그 등 에서 가져올 수 있다. 

그러나 일반적으로 웹으로 생각함. Big Data (volume, variety, velocity) --> 그래서 웹일 수 밖에 없음. 

 

데이터의 목적이 어디있느냐? 

시나리오로 기승전결을 만들어가야하는데, 어? 통계, 수학 --> 시각화 필요하네? 

빅데이터 처리 어떻게 하나 ? --> 엑셀, 타블로 (유연하지 않아)

-- > 언어적인 처리 해보자 (파이썬)

 

데이터 양이 많네? 저장기술 필요하네 (하둡) 

웹을 이해해보자 --> 그럼 구축해보자 

웹서버 구축하기 위해서는? --> 게시판 만들어보래. --> 3. db 테이블 필요하네, 2. 처리하려면 자바 필요하고, 1. 이걸 UI로 만들어줘야지 

웹 크롤링의 기본 기술을 html, css, javascript 어디서 읽어올래? --> UI를 해야겠다. 

 

 

데이터 모델링

- 관계형 DB< 

 

데이터 모델링 과정: 

1.  현실(업무 프로세스) 이해 <-- 고객 니즈를 기반으로 범위 제한 --> 명세 (문서로 남김) 

현실(업무 프로세스) 이해

2.  개념적 모델링 --> 개념적 구조

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ERD(Entity Relationship Diagram)&nbsp;

          --> ERD(Entity Relationship Diagram) 

 

3.  논리적 모델링 --> 논리적 구조(Data 모델) 

 

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 정규화&nbsp;

     - 학번 / 과목 코드 : 키값.

          --> 정규화 

 

4.  물리적 모델링 --> 물리적 구조 

          --> 정규화 RDBMS 선정, 테이블 화 할수있는 데이터 타입, 사이즈 정의 

 

 

---------------------------------------------------------------------------------------------------

 

 

 

--------

 

도서 대여점의 주 데이터: 도서 

 

 

아래와 같은 방식으로 테이블을 만듬 

읽어보기: https://travislife.tistory.com/13

https://hyonee.tistory.com/117

데이터베이스 모델링

[데이터모델링] 데이터모델링 이해 (단계, ERD, 정규화)

데이터 모델링은 현실세계 업무에서 발생하는 데이터를 데이터베이스화 하기 위한 과정

 

 

 

 

테이블을 create할 수도 있지만 워크벤치에서 바로 넣을 수 있음.

ERD 다 만든 상태에서 그리는 것 . 

 

 

이렇게 FK도 바로 설정할 수 있음! 

 

 

 

 

 

 

---

과제:

1. 어제 작성한 테이블 설계도를 

회원 테이블, 게시판 테이블

 

- 개념적 모델링 : ERD 작성을 하고, 

- 논리적 / 물리적 모델링 Table 화 

- DDL완성 

 

2. Shop DB 만들어보기 

- Entitiy: [ 회원 ] -  < 구매 > -  [ 상품 ] 

- 논리적 / 물리적 모델링 Table 화

- DDL완성