[cms database]
회원 테이블 - member
회원포인트 지급 현황 - points
상품 테이블 - mall_product
결제 테이블 - pays
[center table] 회원 정보
센터테이블을 잘짜야함 => 잘못짜면 다 갈아엎어야함
스트레스많이 받지만 다음 프로젝트에서 유용했다
cms의 경우
사람기준 member
상품기준 product
테이블 모음집 ERD
코드짤때 테이블을 잘못짜서 중첩시켜 불러올경우 => 속도가 떨어짐
[테이블 종류와 예시]
center table : 사원번호, 이름, 연락처, 이메일, 나이, 사진, 직책
side table - 보조테이블 : 주소, 가족관계, 급여, 호봉
external table : 외부테이블
view table - 가상테이블 : 다른 부서에 전달할 때
[join]
테이블 조인
table과 table을 연결하여 해당 데이터를 추가적으로 비교하여 출력하는 역할
> select 출력할거 from 기준테이블 as 별명 join 연결할테이블 as 별명 where 조건함수;
> select * from member as a join pays as b where a.mid = b.mall_id;
> select a.mname,b.mall_id,b.pcode,b.pay_part,b.pay_money,b.pay_date from member as a join pays as b where a.mid = b.mall_id;
테이블 두개를 조인하는거 뿐만아니라 다수의 테이블 조인 가능 join 여러개 주루루룩
실무에서는 아래 조인들 안씀! => join 하나로 모두 가능
- outer join, left join, right join, inner join, full join, cross join
=====
👾응용 문제) 아래 리스트가 필요해요!
고객 아이디, 상품명, 상품코드, 결제금액, 결제일자
1. 요소 확인하기
[mall_product : 상품 테이블] - a
상품명 : a.pname
[pays : 결제 테이블] - b
고객아이디 : b.mall_id
상품코드 : b.pcode
결제금액 : b.pay_money
결제일자 : b.pay_date
[참조 요소]
상품코드 : a.pcode b.pcode
2. 쿼리문 짜기
> select b.mall_id,a.pname,b.pcode,b.pay_money,b.pay_date from mall_product as a join pays as b where a.pcode = b.pcode;
👾응용 문제) 아맞다 3월 22일에 결제된거로 뽑아주세요!
> select b.mall_id,a.pname,b.pcode,b.pay_money,b.pay_date from mall_product as a join pays as b where a.pcode = b.pcode and b.pay_date >= '2025-03-22 00:00:00' and b.pay_date <= '2025-03-22 23:59:59';
👾응용 문제) 포인트가 저장된 테이블을 이용하여 다음과같이 출력되도록 sql query문을 작성
[출력예시]
고객명, 고객아이디, 연락처, 포인트값, 포인트내역, 포인트 지급일
[member : 고객 테이블] - a
고객명 : a.mname
*고객아이디 : a.mid
연락처 : a.mtel
[points : 포인트 테이블] - b
포인트값 : b.mall_point
포인트내역 : b.point_contents
포인트 지급일 : b.point_date
[참조 요소]
고객아이디 : a.mid b.mall_id
> select a.mname,a.mid,a.mtel,b.mall_point,b.point_contents,b.point_date from member as a join points as b where a.mid = b.mall_id;
=====
[뷰 테이블]
뷰테이블 : 가상의 테이블 (join한 결과를 저장하는 테이블)
조인이 된 상태에서만 가능
1. 생성
> create view 뷰테이블명 as select [join 문법]
> create view point_list as select a.mname,a.mid,a.mtel,b.mall_point,b.point_contents,b.point_date from member as a join points as b where a.mid = b.mall_id;
2. 갱신
뷰테이블에 갱신 되는지 테스트 쿼리
> insert into points values('0','luck0707','50000','관리자 지급','2025-03-31 12:00:00');
=> 갱신이 된다!
⭐️뷰테이블의 데이터는 실제 테이블에 값이 변화가 되었을 경우 자동으로 갱신
뷰테이블에 직접 데이터를 입력하는것은 불가능
insert, delete (X)
유일하게 가능 update (O) 단, 동일한 데이터가 있을 경우 모두 수정됨
> update point_list set mall_point='10000' where mall_id='apink';
> select * from points;
=> update로 뷰테이블을 수정하면 실제 테이블에도 반영이 된다!
3. 삭제
실제 테이블의 데이터는 삭제안됨
> drop view 가상테이블명;
=====
[자료형 선언시 int 관련 사항]
signed : 기본 default 자료형(-1234~1234)
unsigned : 정수기준으로 음수를 사용하지 않고 양수만 사용하는 형태이며, 양수값이 범위가 증가 (1~5622...)
[외래키]
부모테이블과 자식테이블을 연결하는 key
1. 부모 테이블을 제작 후 자식 테이블을 생성해야함
단, 부모 테이블에 primary key 또는 unique 중에서 하나라도 있어야만 연결이 가능
2. 자식 테이블 제작시 부모테이블에서 사용하는 컬럼명과 자료형범위가 동일하게 사용하는것을 권장
3. 데이터는 부모 테이블에 먼저 입력 후 자식테이블에 foreign key로 생성된 컬럼명에 데이터를 입력하는 순으로 세팅
예시 사용법 - 자식 테이블
5.1버전
foreign key ('자신의테이블컬럼명') reference '부모테이블명'('연결할컬럼명')
8버전
foreign key (자신의테이블컬럼명) references 부모테이블명(연결할컬럼명)
> foreign key는 desc로 봤을때 MUL 멀티키로 보임!
create table subject(
sidx int not null auto_increment,
subject_nm char(100) not null,
subject_code char(5) not null,
teacher char(30) not null,
class_time tinyint(3) not null,
class_use enum('Y','N') not null default 'N',
primary key(sidx),
unique (subject_code)
);
//smallint 범위 : -32000 ~ 32000
unsigned 사용시 : 1 ~ 65000
기본은 signed로 설정되어있음
//tinyint : 음수값이 없어서 unsigned 필요없음
create table info(
subject_code char(5) not null,
money smallint unsigned,
person tinyint,
foreign key (subject_code) references subject(subject_code)
);
create table student(
subject_code char(5) not null,
std_name char(30) not null,
std_tel char(11) not null,
foreign key (subject_code) references subject(subject_code)
);
5.1버전
foreign key ('subject_code') reference 'subject'('subject_code')
8버전
foreign key ('subject_code') references subject(subject_code)
insert into subject values('0','Java인강','12345','에이핑크','90','Y');
insert into subject values('0','Front-end인강','06000','에이핑크','30','Y');
insert into info values('12345','150000','30');
insert into info values('06000','50000','30');
insert into student values('06000','홍길동','01055667788');
insert into student values('12345','유관순','01010040301');
alter table info modify money int unsigned not null;
select * from subject;
delete from subject where subject_code='06000';
> 삭제안됨 메인테이블에 두개의 자식테이블이 붙어있어서 둘다 지워야 이거 가능
update subject set subject_code='07000' where sidx='3';
> 못바꿈 자식테이블땜에
⭐️회사에서도 외래키때문에 생각 많이하고한다
수정 삭제가 곤란해지기 때문에
하나의 부모테이블에 많은 자식이 있으면 안됨
너무 많아도 적어도안된다
보통 회사에서 메인하나에 5개까지 만든다
더많이 만들면 개발자가 괴로워한다
하나지울때 다지워야함
적당히쓰기!
외래키는 부모테이블 기준으로 맞는값만 자식테이블에서 넣을 수 있음
delete from subject where sidx='1';
> 안지워짐!
[foreign key로 셋팅된 테이블의 데이터를 삭제하는 방법]
1. 자식 테이블의 데이터를 먼저 삭제 후 부모 테이블의 데이터를 삭제할 수 있음
2. 부모 테이블의 데이터를 여러개의 자식테이블에서 사용할 경우
모든 자식테이블의 각각데이터를 삭제하지 않을 경우 부모 테이블에서 데이터를 삭제할 수 없음
delete from info where subject='12345';
delete from subject where sidx='1';
> 삭제는 입력의 반대 ! 자식먼저 죽이고 부모 죽이기
👾응용 문제) subject, info, student 세개의 테이블을 이용하여 view 제작
view Table : e_learning
과목코드 | 과목명 | 강사 | 수업시간 | 학생명
[subject] - a
* 과목코드 : a.subject_code
과목명 : a.subject_nm
강사 : a.teacher
수업시간 : a.class_time
[info]
?
[student] - c
학생명 : c.std_name
[참조 요소]
과목 코드 : a.subject_code = c.subject_code
select a.subject_code,a.subject_nm,a.teacher,a.class_time,c.std_name from subject as a join student as c where a.subject_code = c.subject_code;
create view e_learning as select a.subject_code,a.subject_nm,a.teacher,a.class_time,c.std_name from subject as a join student as c where a.subject_code = c.subject_code;