개발/MySQL

23.03.20MySQL 조인, 정규화, 문자열 함수

상달군 2023. 3. 20. 16:44
728x90

목차

1. MySQL조인

  • join
  • inner join
  • left / right outer join

2. MySQL정규화

  • 데이터 정규화
  • 1NF( 제1 정규화)
  • 2NF( 제2 정규화)
  • 3NF( 제3 정규화)
  • 비정규화
  • auto_incrment
  • union
  • ✔Sub Qusery (서브 쿼리)

3.MySQL 문자열 함수 

  • concat (복수의 문자열을 연결)
  • left , right (왼쪽 또는 오른쪽 길이만큼 문자열 가져오기)
  • substring(문자의 일부분 가져오기)
  • char_length (문자열길이)
  • lpad, rpad (빈공간 채우기)
  • ltrim, rtrim, trim (공백제거)
  • replace (치환)

파일명 : day3.sql

1. MySQL조인

예제를 위한 기본 준비

# member테이블과 연관성 있는 테이블 만들기

create table profile(
	userid varchar(20) not null,
    height double, 
    weight double,
    blood varchar(10),
    mbti varchar(10),
    foreign key(userid) references member(userid) 
    # foreign key를 선언 해주는 방법
	# foreign key(생성되는 테이블 칼럼) references member(연결시켜줄 테이블 칼럼) 
);

select * from profile;

insert into profile values('skckdeo', 174, 89, 'B', 'ISFJ');# skckdeo ID는 없기 때문에 에러발생 userid가 조건이 primary key이기 때문에 !!
insert into profile values('apple', 160, 50, 'B', 'ISPT');
insert into profile values('banana', 170, 80.5, 'o', 'ESFJ');
insert into profile values('grapes', 158.4, 59.1, 'B', 'ISPT');
insert into profile values('melon', 165, 74, 'A', 'ISFJ');  
insert into profile values('orange', 165, 70, 'A', 'ISFJ');

 

1-1. join

  • select 필드명1, 필드명2,..... from 테이블1 [inner, left, right] join 테이블2 on 테이블1.연결할필드 = 테이블2.연결할필드;

1 -2. inner join

  • inner 조인 (교집합)
    • 조인하는 테이블의 on절의 조건이 일치하는 결과만 출력
    • 일반join,  inner join,  cross join  모두 같은 의미로 사용됨 
# Error Code: 1052. Column 'userid' in field list is ambiguous
select userid, username, hp, height, weight, mbti from member inner join profile on member.userid = profile.userid; #필드명이 겹치는경우!!!
# as 를 이용하여 별명을 사용해서 member = m 으로 profile = p를 정해주고 사용 !
select m.userid, m.username, m.hp, p.height, p.weight, p.mbti from member as m inner join profile as p on m.userid = p.userid;

 

1-3. left / right outer join

 

  • 두 테이블이 조인될때 (왼쪽/오른쪽)을 기준으로 했느냐에 따라 기준 테이블의 것을 모두 출력 해주는것
  • outer join은 조인하는 테이블의 on절의 조건중 한쪽의 데이터를 모두가져옴
  • left outer join, right outer join, full outer join 이렇게 3가지가 존재함 
    그러나 full outer join은 거의 사용하지 않음 성능상의 문제와 모든 데이터가 나와 헷갈리게 되어 잘 사용하지 않음
  • left join시 member에 값이 모자르기 때문에 모자라는 값은 null값으로 출력 된다. 
select m.userid, m.username, m.hp, p.height, p.weight, p.mbti from member as m left outer join profile as p on m.userid = p.userid; #left
select m.userid, m.username, m.hp, p.height, p.weight, p.mbti from member as m right outer join profile as p on m.userid = p.userid; #right
  • 위에서 사용한 as를 이용해서 별명을 사용하면 편하다. 필드명이 겹쳐도 쉽게 해결할 수 있다. 

2. MySQL정규화

 

 

2-1. 데이터 정규화

  • 데이터베이스를 설계할 때 중복을 최소화 하는것을 의미한다. 
  • 데이터가 크고 조직화되어 있지 않은 테이블과 관계들을 조직화된 테이블과 관계들로 나눈것 !! 

데이터 정규화 필요한 경우 

  • 데이터를 갱신, 삽입, 삭제하는 등 테이블을 수정할 때 원하지 않게 삭제되거나 가공되는 일이 발생하는데 이를 '이상현상'이라고 함
  • 이상현상이 발생하는 경우 데이터 정규화가 반드시 필요할 수 있다.

2-2. 정규화의 종류

  • 1NF( 제1 정규화)
    • 테이블 안의 모든 값들은 단일 값이어야 함 
    • ✔한개의 필드에 여러 데이터를 넣지 말아라 !
  • 2NF( 제2 정규화)
    • 1NF를 만족하면서, 완전 함수 종속성을 가진 관계들로만 테이블을 생성하는것을 말합니다.
    • 종속성들 중 종속 관계에 있는 열들끼리 테이블을 구분해 주는 것 
    • ✔ 함수 종속성
    •  x값에 따라서 y값이 결정되는 경우 
  • 3NF( 제3 정규화)
    • 2NF를 만족하면서, 기본키에 대해 이행적 함수 종속이 되지 않는 것을 의미한다. 
  •  비정규화
    • 정규형에 일치하게 되어 있는 테이블을 정규형을 지키지 않는 테이블로 만드는 것을 의미합니다.
    • 테이블을 조회하는 용도로 사용하거나, 너무 데이터가 많이 나뉘어 성능이 저하된다면 비정규화를 하여 테이블을 다루는것이 더 효율적일 수 있다!
    • 어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적절한 정규화를 하는것이 좋음 
  • auto_incrment (필드의 identity한 숫자를 자동으로 부여함)
  • auto_incrment사용을 위한 테이블 생성과 필드값 삽입 
create table tel(
	idx int auto_increment primary key,  #idx에 auto_increment를 적용 할것이다.  # 에러 MySQL만의 특징 때문에 발생 primary key를 입력시 해결 가능 
    name varchar(20) not null,
    hp varchar(20) not null,
    job varchar(20),
    regdate datetime default now()
);

idx 값에 auto_incrment를 사용 하였기 때문에 값을 넣어 주지 않는이상 1씩 증가 하게 되어있다!! 

이때, idx의 값은 primary key 값이 된다 !

#idx 값을 넣지 않았다! 
insert into tel (name, hp, job) values ('김사과', '010-1234-5748', '학생');
#idx값을 2의 값을 넣어주었다. 
insert into tel (idx, name, hp, job) values (2,'반하나', '010-0000-2000', '학생'); 

#idx값을 10으로 넣어주는것도 가능하다. 
insert into tel (idx, name, hp, job) values (10,'오렌지', '010-8888-8888', '학생'); 

#Error Code: 1062. Duplicate entry '2' for key 'tel.PRIMARY' => 프라이머리 키 값이 중복 되어있다라는 에러메세지
insert into tel (idx, name, hp, job) values (2,'이메론', '010-7788-9999', '공무원');

# 위와 같이 2다음에 10 넣었을땐 idx값은 11로 저장되고 2~10사이는 뛰어넘게 된다.
insert into tel (name, hp, job) values ('이메론', '010-7788-9999', '공무원');

결과값 !

  • union(유니온)
    • 합집합을 나타내는 연산자로, 중복된 값을 제거함을 의미합니다.
    • 서로 같은 종류의 테이블(칼럼이 같아야 함)에서만 적용이 가능하다.
    • select 컬럼명1,컬럼명2.... 테이블1 union select from 컬럼명1,컬럼명2.... 테이블2

유니온 예제를 위한 테이블 두개 생성 

create table product(
	code varchar(6) not null,
    name varchar(50) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);
insert into product values('100000', '아이폰14', '예뻐요', 1500000, now());
insert into product values('100001', '갤럭시23', '좋아요', 1300000, now());
insert into product values('100002', '맥북에어', '가벼워요', 1400000, now());
insert into product values('100003', 'z플립4', '잘접혀요', 1800000, now());
insert into product values('100004', 'LG공기청정기', '성능이좋아요', 600000, now());
select * from product;
create table product_new(
	code varchar(6) not null,
    name varchar(50) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()    
);
insert into product_new values('200000', '엘지그램', '가벼워요', 200000, now());
insert into product_new values('200001', '삼성모니터', '잘보여요', 500000, now());
insert into product_new values('100001', '갤럭시23', '좋아요', 1300000, now()); # 유니온 테스트를 위해 위에 같은값을 삽입해줌! 
select * from product_new;

유니온(union) 을 사용한 쿼리

select code, name, price from product 
union
select code, name, price from product_new;

#한줄로 써도 되지만 union을 기준으로 짜르고 세미클론(;)까지 실행 됩니다.

중복되어 있는 100001코드 갤럭시23 내용이 출력 되지 않는 결과값을 확인 할수 있습니다. 

 

# 중복값이 아닌 regdate 칼럼을 추가 했을경우 !

select code, name, price, regdate from product 
union
select code, name, price, regdate from product_new; 

#전체가 다 중복 되어야 출력이 되지 않는다.! 중복되는 값 '갤럭시 23'에 regdate가 중복 되지 않아 갤럭시23이 출력 된다.

만약, 중복 되지 않는 필드값이 하나라도 존재 하는경우 그 값은 중복 값이 아닙니다. 

regdate의 값이 중복 되지 않기 때문에 출력 된 모습을 볼수 있습니다. 

  • union all 
    • 합집합을 나타내는 연산자(union과 같다)로, 중복된 값을 제거하지 않음.
select code, name, price from product 
union all 
select code, name, price from product_new;


# all을 사용하게 되면 모든 데이터가 나온다!

Sub Qusery (서브 쿼리)

  • 다른 쿼리 내부에 포함되어 있는 select 문을 의미한다. 
  • 서브 쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고 부릅니다.
  • 서브쿼리는 괄호()로 감싸져서 표현한다.
  • 특징
    • 서브쿼리는 메인 컬럼 사용이 가능하며, 메인쿼리는 서브쿼리 컬럼을 사용하지 못함.
    • select, where, from, having 절 등에서 사용할 수 있다. 

아래 where절 서브쿼리 해보기 

  • select price from product where code = '100001';
  • 예문) code 값이 100001인 녀석의  price보다 큰거나 같은 값을 가지고 있는 상품의 모든정보 보고 싶다 !
select * from product where price >= (select price from product where code = '100001');

아래 select절 서리쿼리 해보기 

  • 예문) 코드, 이름, 가격 전체 데이터의 가격중 가장 큰 값을 출력하고 싶다.
  • 단, 서브쿼리를 사용해보기  헷갈릴수 있기 때문에 별명 max_price를 넣어준다. 
select code, name, price, (select max(price) from product) as max_price from product;

문제 ) 

  • 문제를 풀기 위한 order 테이블 생성 및 데이터 삽입
create table orders(
	no int not null,
    userid varchar(20) not null,	#member과 연결 할 수 있는 코드 
    product varchar(100) not null,
    cnt int default 1, 
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

insert into orders (no, userid, product, cnt) values(1,'apple','사과',3);
insert into orders (no, userid, product, cnt) values(2,'apple','꿀사과',2);
insert into orders (no, userid, product, cnt) values(3,'banana','바나나',5);
insert into orders (no, userid, product, cnt) values(4,'banana','딸바',1);
insert into orders (no, userid, product, cnt) values(5,'orange','오렌지',2);
insert into orders (no, userid, product, cnt) values(6,'berry','블루베리',3);

# 문제 1) 

  • 상품을 최소 2번이상 구입한 회원의 id, name, gender을 출력해보기 
  •  단, 서브쿼리를 사용하시오
select userid, username, gender from member where userid in (select userid from orders group by userid having count(no) >= 2);

 

# 문제 2) 

  • 상품을 2번이상 구입한 사용자의 아이디, 상품 구입횟수, 시도이름을 출력
# join 만 이용한거 너무길어서 as address기준으로 짤랐습니다. 
select m.userid,  count(o.no) as cnt, substring(m.address1, 1, 3)as address 
from member as m right outer join orders as o on m.userid = o.userid group by userid having cnt >= 2;

 

# 서브 쿼리 from을 이용하기
select userid, count(no) as ocnt from orders group by userid having count(no) >= 2 ; # 주소를 가져 올수 없다 ! member테이블과 join 하면 끝난다 !


#너무 길어서 member as m 기준으로 짤랐음
select m.userid, t.ocnt, substring(m.address1, 1, 3) as address from member as m 
right outer join
(select userid, count(no) as ocnt from orders group by userid having count(no) >= 2) as t on m.userid = t.userid; # 괄호()부분이 테이블 자체가 됨
  • 동일한 테이블을 만들때 쉽게 하는 생성 하는방법 
# orders와 동일한 테이블을 생성해보기 
create table orders2(
	no int not null,
    userid varchar(20) not null,	
    product varchar(100) not null,
    cnt int default 1, 
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

# orders와 동일한 테이블 orders2를 만들고 orders에 존재하는 데이터를 모두 복사 하여 orders2에 저장해보기 
insert into orders2(select * from orders);

# orders3테이블을 만들면서 바로 orders내용을 붙혀 넣기
create table orders3(select * from orders);

1번째 방법 - 하나씩 orders 테이블과 값을 비교해가며 테이블의 정보를 넣어 테이블을 생성후, insert를 이용하여 orders내용을 넣어준다. 

2번째 방법 - 생성할때 아에 select * from orders를 사용하여 한번에 붙혀 넣는다 !

 

3.MySQL 문자열 함수 

예전에 만들어 두었던 member 테이블을 그대로 이용한다 !!

member 테이블이 궁금하신분들은 https://sangdal91.tistory.com/26 필드값은 조금 바껴있을수도 있습니다. 

 

  • concat 
    •  복수의 문자열을 연결해주는 함수 
select concat('안녕','하세요') as conat ;
select concat(address1, address2, address3) as address from member where userid = 'orange';

  • left , right
    •  왼쪽 또는 오른쪽에서 길이만큼 문자열을 가져온다.
  • 예제는 left만 진행 하였습니다. 
select left('ABCDEFGHIJKLMN', 5);
# -member테이블에 apple 사용자의 email 왼쪽에서 5자만 뽑아 오기 
select userid, left(email,5) as email from member where userid = 'apple';

  • substring
    • 문자열의 일부를 가져옵니다. 
select substring('ABCDEFGHIJKLMN', 3, 2); # 인덱스를 사용하는것이 아닌 시작 위치를 부터 가져온다 !!!
select substring(address1, 1, 3) as 주소 from member where userid = 'orange';

  • char_length 
    • 문자열의 길이를 반환 해주는 함수
select char_length('ABCDEFGHIJKLMN'); 
select char_length(email) as len from member;

  • lpad, rpad 
    • 왼쪽 또는 오른쪽의 해당 길이만큼 늘리고 그 빈공간을 채울 문자열로 채워 반환 해준다.
select lpad('ABCDEFG', 10, '0'); # 10자리를 보여주고 나머지를 왼쪽을 '0'으로 채워주세요 
select lpad(point, 5 , 0) as lpad from member;

  • ltrim, rtrim, trim
    • 왼쪽 또는 오른쪽 공백을 제거 해주는 함수 
    • trim은 양쪽 모두를 제거 해줍니다. 
select ltrim('      ABCDEFG     ') as ltrim;
select trim('      ABCDEFG     ') as trim; #양쪽 모두 공백을 제거 해주는 방법 trim!!

 

  • replace
    • 문자열에서 특정 문자열을 찾아 변경 해줍니다. 
    • replace(문자열, 대상, 바꿀 문자열)
select replace('ABCDEFG', 'CD', '✔') as repl;

 

728x90