3-0. 준비과정
3-1. 조인
3-1-1. inner 조인
3-1-2. left/right outer 조인
3-2. 데이터 정규화
3-2-1. 1NF(제 1정규화)
3-2-2. 2NF(제 2정규화)
3-2-3. 3NF(제 3정규화)
3-2-4. 비정규화
3-3. 유니온(union)
3-3-1. 유니온(union)
3-3-2. 유니온 올(union all)
4. 서브쿼리(Sub Query)
4-1. where절에 대한 SubQuery
4-2. select절에 대한 SubQuery
5. 문자열 함수
5-1. concat
5-2. left, right
5-3. substring
5-4. char_length
5-5. lpad, rpad
5-6. ltrim, rtrim, trim
5-7. replace
TIP
3-0. 준비과정
use kdt; # 예제로 사용될 kdt 데이터베이스에 접근. select * from member; # 예제로 사용될 member 테이블에 접근. desc member; # 참고할 member 테이블을 확인 select * from orders; # 예제로 사용될 orders 테이블에 접근. |
table이 너무 비대해지면 불러오는 속도가 느려질 가능성이 있음
→ 이를 방지하기 위해 연관성을 주는 table을 새로 생성하여 자주 사용하지 않는 data를 옮겨둔 다음,
필요할 때마다 합쳐서 보여주는 작업이 필요할 때가 있음.
profile 테이블을 생성.
'userid' field는 primary key로 설정. profile table에 foreign key를 넣어 생성할 예정.
CREATE table profile ( userid varchar(20) not null, # userid필드값은 member 테이블에서의 userid 필드값과 같은 조건으로 작성. height double, weight double, blood varchar(10), MBTI varchar(10), foreign key(userid) references member(userid) # 프로필 table의 userid 필드값에 member table의 userid필드값을 레퍼런스로 하여 foreign key를 생성. ) ; |
foreign key의 필드명 userid는 다르게 작성해도 무방함. (member 필드명 userid만 정확히 기재하면 됨.)
profile table에 data를 입력.
INSERT INTO profile VALUES ('cho', 160, 50.5, 'A', 'ISTP'); INSERT INTO profile VALUES ('choi', 170, 50.5, 'B', 'ENTJ'); INSERT INTO profile VALUES ('jeong', 180, 50.5, 'O', 'ISFJ'); INSERT INTO profile VALUES ('kim', 190, 50.5, 'AB', 'ISTP'); INSERT INTO profile VALUES ('park', 165, 50.5, 'A', 'ESTP'); # member table에 없는 값인 'chang'을 입력했을 시, foreign key의 제약 조건에 위배했다는 오류가 발생. |
3-1. 조인
SELECT 필드명1, 필드명2 ... FROM 테이블1 [INNER, LEFT, RIGHT] JOIN 테이블2 ON 테이블1.연결할필드 = 테이블2.연결할필드 ; |
3-1-1. inner 조인
조인하는 테이블의 on 절의 조건이 일치하는 결과만 출력. (data의 교집합 부분만 표현)
join, inner join, cross join 모두 같은 의미로 사용됨.
일치하는 값이 없는 경우, 결과에 나타나지 않음.
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; 'userid'필드값이 어디의 table 소속인지 모호하다는 오류가 발생. as를 이용하여 '별명'을 지어준 후(member as m, profile as p) 필드값의 앞에 붙여주면 오류가 해결.(m.userid, p.weight) |
3-1-2. left/right outer 조인
두 테이블이 조인할 때, 왼쪽/오른쪽을 기준으로 했는지에 따라 기준 테이블의 것을 모두 출력.
outer join은 조인하는 테이블의 on 절의 조건 중 한쪽의 데이터를 모두 가져옴.
left outer 조인 : 왼쪽의 A 테이블 + A와 B 테이블의 교집합.
right outer 조인 : 오른쪽의 B 테이블 + A와 B 테이블의 교집합.
outer join은 left outer join, right outer join, full outer join 3가지가 존재.
full outer join은 거의 사용하지 않음. (두 개의 table을 한꺼번에 join해서 출력 → 출력의 복잡함 및 비효율성)
SELECT m.userid, m.username, m.hp, p.height, p.weight, p.MBTI FROM member as m LEFT JOIN profile as p ON m.userid = p.userid; m.userid의 'lee' 값은 height, weight, MBTI(profile테이블의 값)가 존재하지 않으므로 'null' 값으로 출력이 됨. SELECT m.userid, m.username, m.hp, p.height, p.weight, p.MBTI FROM member as m RIGHT JOIN profile as p ON m.userid = p.userid; 현재 profile 테이블에 있는 userid값이 member 테이블에 모두 존재하기 때문에 해당 예시의 right join의 값은 inner join의 값과 동일하게 출력됨. (만약, profile 테이블에 있는 userid값이 member 테이블에 없는 값이라면, 그 없는 userid의 field값은 'null'값으로 출력이 됨.) |
3-2. 데이터 정규화
데이터베이스를 설계할 때 중복 데이터를 제거하고 데이터를 구조화하는 과정.
(크고 조직화되어있지 않은 테이블과 관계들을 조직화된 테이블과 관계들로 나누는 것)
'데이터 정규화' 가 필요한 경우:
데이터를 갱신, 삽입, 삭제하는 등 테이블을 수정할 때 원하지 않게 데이터가 삭제되거나 가공되는 일이
발생하는데 이를 '이상 현상'이라고 함.
이러한 이상 현상의 발생을 해결,방지하기 위한 목적으로 사용되며, 추가적으로 데이터베이스의 성능 최적화,
데이터의 일관성과 무결성을 유지하기 위한 목적으로도 사용된다.
3-2-1. 1NF(제 1정규화)
테이블 안의 모든 값들은 '단일 값'이어야 한다. (중복되는 데이터가 없도록 분해하는 과정.)
이를 위해 테이블 내의 모든 data값은 원자값(Atomic value)만을 갖도록 분해되어야 함.
(field값에 data를 '중복 값'으로 넣으면 안된다. ex. hobby : '등산','게임','운동' ... X)
만약 '중복 값'을 넣고 싶다면, '취미'라는 table을 새롭게 생성해야 함.
(예: userid의 'kim'의 hobby가 2개 이상인 경우에는 kim='운동', kim='등산'처럼 작성.)
SELECT userid, hobby FROM 취미 WHERE userid='kim'; |
만약 kim='운동', kim='등산'에서 임의의 data 한개만의 삭제가 필요한 경우를 대비하여
identity값을 미리 입력해두는 것이 필요.
(예. auto-increment를 사용하여 번호를 매겨서 구분이 가능하도록 함.)
3-2-2. 2NF(제 2정규화)
1NF를 만족하면서, 부분적 종속을 제거하여 완전 함수 종속성을 가진 관계들로만 테이블을 생성하는 것.
또한, 종속성들 중 종속 관계에 있는 열들끼리 테이블을 구분. ('탑승객' table과 '비행기 정보' table로 구분)
함수 종속성 함수에서 x값에 따라 y값이 결정되는 것. |
3-2-3. 3NF(제 3정규화)
2NF를 만족하면서, 기본 키에 대해 이행적 함수 종속을 제거하는 것.
이를 위해 테이블에서 기본 키가 아닌 field값 간의 종속 관계를 제거하여 테이블을 잘 구조화된 형태로 만듦.
('탑승객' table에서 '존칭'이라는 field값을 제거하여 '존칭 정보' table을 새로 생성.
이를 통해 더욱 작고 구조화된 형태가 만들어짐.)
3-2-4. 비정규화
정규형인 테이블을 정규형을 지키지 않는 테이블로 만드는 것.(데이터 일부의 '중복'을 허용한다는 의미)
테이블의 조회속도를 올리는데 사용하거나, 복잡한 JOIN으로 성능이 저하되는 경우 효율성을 제고하는 목적.
그러나, 데이터의 중복이 허용되기 때문에 '일관성'을 유지하는 것이 중요하다.
어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적절한 정규화를 사용하는 것이 중요함.
→ 무조건적으로 정규화를 사용하는 것이 좋은 것은 아님.
auto_increment MySQL에서 제공되는 데이터 타입 중 하나. 테이블의 특정 열에 값을 삽입할 때 자동으로 1씩 부여하는 기능을 제공. 이렇게 할당받은 identity한 값을 통해 레코드를 고유하게 식별하는 것이 목적. auto_increment 속성이 부여된 열은 NULL값을 가질 수 없음. Unique key와 함께 사용될 때 테이블 내에서 유일한 값을 가지게 됨. tel이라는 테이블을 생성. (idx필드값을 생성해줄 예정) create table tel( idx int auto_increment primary key, name varchar(20) not null, hp varchar(20) not null, job varchar(20), regdate datetime default now() ); mySQL의 특징으로, auto_increment에서는 primary key를 반드시 작성해주어야 함. 작성해주지 않는다면 오류가 발생. 'kim'이라는 새로운 data의 삽입. idx필드값에 1이 자동으로 생성이 된 것을 확인할 수 있음. insert into tel(name, hp, job) values ('kim', '010-000-0000', '학생'); insert into tel(idx, name, hp, job) values (2, 'park', '010-000-0000', '학생'); # 정상. insert into tel(idx, name, hp, job) values (10, 'jeong', '010-000-0000', '군인'); # 정상. 2번이라는 primary key의 중복으로 인한 에러 발생. insert into tel(name, hp, job) values ('lee', '010-000-0000', '선생'); 마지막으로 번호를 부여했던 10번 다음 번호인 11번으로 부여되어 출력됨. |
3-3. 유니온(union)
3-3-1. 유니온(union)
'합집합'을 나타내는 연산자로, 중복된 값을 제거함.
서로 같은 종류의 테이블(컬럼(필드값)이 같아야 함)에서만 적용이 가능하다.
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블1 UNION SELECT 컬럼명1, 컬럼명2, ... FROM 테이블2 |
'product'라는 테이블1을 생성.
CREATE table product ( code varchar(6) not null, name varchar(50) not null, detail varchar(1000), price int default 0, regedate datetime default now() ); |
'product'에 data를 입력.
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공기청정기', '성능좋아요', 1500000, now()); |
'product_new'라는 테이블2를 생성.
CREATE table product_new( code varchar(6) not null, name varchar(50) not null, detail varchar(1000), price int default 0, regedate datetime default now() ); |
'product_new'에 data를 입력.
INSERT INTO product_new VALUES ('200000', '엘지그램', '가벼워요', 1500000, now()); INSERT INTO product_new VALUES ('200001', '삼성모니터', '잘보여요', 1300000, now()); INSERT INTO product_new VALUES ('100001', '갤럭시23', '좋아요', 1300000, now()); |
만약 product의 갤럭시23 data를 동일하게 product_new에 입력해준 후 union을 실행한다면
중복된 값이므로 중복하여 출력되지 않는다.
'product'와 'product_new'의 code, name, price을 컬럼으로 하여 data를 출력.
SELECT code, name, price FROM product UNION SELECT code, name, price FROM product_new; |
위과 같이 '세 줄'로 나누어서 작성하면 더욱 보기 편함.
3-3-2. 유니온 올(union all)
합집합을 나타내는 연산자로, 중복된 값을 제거하지 않음.
('union'은 중복된 값을 제거함.)
SELECT code, name, price FROM product UNION ALL SELECT code, name, price FROM product_new; |
4. 서브쿼리(Sub Query)
다른 쿼리 내부에 포함되어 있는 select문.
서브쿼리를 포함하고 있는 쿼리를 외부쿼리, 서브쿼리는 내부쿼리라고 부름.
서브쿼리는 소괄호()로 감싸서 표현.
서브쿼리는 메인쿼리 컬럼 사용이 가능하지만, 메인쿼리는 서브쿼리 컬럼 사용이 불가능.
select, where, from, having절 등에서 사용이 가능.
4-1. where절에 대한 SubQuery
100001의 가격보다 크거나 같은 price를 가지고 있는 상품의 모든 정보
SELECT price FROM product WHERE code='100001'; # select절에 대한 SubQuery가 됨. SELECT * FROM product WHERE price >= ( SELECT price FROM product WHERE code='100001' ) |
4-2. select절에 대한 SubQuery
코드, 이름, 가격, 전체데이터의 가격 중 가장 큰 값을 보고 싶은 경우 출력하는 Query
(SubQuery를 이용하여 작성).
오류 발생. group이 없으면 집합함수의 발생이 안됨. 가장 큰 값(max price)의 출력을 SubQuery로 설정. SELECT code, name, price, ( SELECT max(price) FROM product ) as max_price FROM product; |
member 테이블과의 연결 목적으로 orders 테이블의 생성.
CREATE table orders( 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) # userid를 foreign key로 지정 후 member테이블의 userid를 레퍼런스함. ); |
orders 테이블 내 data 생성.
INSERT INTO orders (no, userid, product, cnt) VAULES (1, 'kim', '사과', 3); INSERT INTO orders (no, userid, product, cnt) VAULES (2, 'park', '꿀사과', 2); INSERT INTO orders (no, userid, product, cnt) VAULES (3, 'lee', '바나나', 5); INSERT INTO orders (no, userid, product, cnt) VAULES (4, 'cho', '딸바', 1); INSERT INTO orders (no, userid, product, cnt) VAULES (5, 'choi', '오렌지', 2); INSERT INTO orders (no, userid, product, cnt) VAULES (6, 'jeong', '블루베리', 3); |
상품을 최소 2개 이상 구입한 회원의 id와 이름, 성별을 보고싶은 경우 출력하는 Query
(SubQuery를 이용하여 작성).
SELECT userid, username, gender FROM member WHERE userid IN ( SELECT userid FROM orders GROUP BY userid HAVING count(no)>=2 ) ; 소괄호() : SubQuery userid를 통해 총 구입한 사람들 및 no의 갯수를 알려주는 list를 확인 + having절을 이용한 cnt>=2를 조건으로 함. |
5. 문자열 함수
5-1. concat
복수의 문자열을 연결해주는 함수.
SELECTCONCAT('안녕', '하세요') as concat1; ---------------------------------- 안녕하세요 |
예) member테이블의 userid가 'jeong'인 필드명 ssn1과 ssn2의 data값을 합쳐서 출력하고 싶은 경우.
SELECT CONCAT(ssn1,' ',ssn2) FROM member WHERE userid='jeong'; |
중간에 공백을 넣고 싶은 경우, concat에 ' '를 추가.
5-2. left, right
왼쪽 또는 오른쪽에서 길이만큼 문자열을 가져옴.
SELECT LEFT/RIGHT('ABCDEFGHIJKL',5) as leftright5; ---------------------------------- ABCDE or HIJKL |
예) member테이블의 userid가 choi인 email을 왼쪽부터 5글자까지만 출력하고 싶은 경우.
SELECT userid, LEFT(email,5) as email FROM member WHERE userid='choi'; |
5-3. substring
문자열의 일부를 가져옴.
substring()의 형태 : substring(문자열, 시작위치, 길이)
예) 문자열 'ABCDEFGHIJKL' 중 3번째 위치에서 2개의 문자열을 가져오고 싶은 경우.
SELECT SUBSTRING('ABCDEFGHIJKL', 3, 2) as substring; ---------------------------------- CD |
5-4. char_length
문자열의 길이를 반환
예) 문자열의 길이가 12개라는 의미.
SELECT CHAR_LENGTH ('ABCDEFGHIJKL') as char_length1; ---------------------------------- 12 |
5-5. lpad, rpad
문자의 왼쪽 또는 오른쪽을 해당 길이만큼 늘리고 빈 공간을 채울 문자열을 반환.
예) 문자열의 길이를 10으로 하면서 나머지 공간의 왼쪽 부분을 0으로 채워넣고 싶은 경우.
SELECT LPAD('ABCD', 10, '0') as lpad1; ---------------------------------- 000000ABCD |
5-6. ltrim, rtrim, trim
문자의 왼쪽, 오른쪽, 양쪽 공백을 제거.
예) 문자열의 양쪽 공백을 모두 제거하고 싶은 경우.
SELECT TRIM(' ABCD ') as trim1; ---------------------------------- ABCD |
5-7. replace
문자열에서 특정 문자열을 치환.
replace()의 형태 : replace(문자열, 치환할대상, 치환할문자)
예) 문자열 ABCDEFG에서 CD를 XX로 치환하고 싶은 경우.
SELECT REPLACE ('ABCDEFG', 'CD', 'XX') as replace1; ---------------------------------- ABXXEFG |
예) 상품을 2개 이상 구입한 사용자의 userid, 구입횟수, address1를 출력하고 싶은 경우.
select * from member; select * from orders; select userid, substring(address1, 1, 2) as address from member; select count(no) as cnt from orders group by userid having cnt>=2; # join을 이용했을 때 SELECT m.userid, count(o.no) as cnt, substring(m.address1, 1, 2) 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 m.userid, t.ocnt, substring(m.address1, 1, 2) 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; # 소괄호() 가 t가 됨. |
예) orders와 동일한 조건과 형태의 orders2라는 테이블을 생성하고 싶은 경우.
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) ); select * from orders2; # 현재는 아무 data도 없는 상태. # orders와 동일한 형태의 테이블인 orders2를 생성 후 orders에 존재하는 데이터를 모두 복사하여 저장하고 싶은 경우. INSERT INTO orders2 ( SELECT * FROM orders ) ; |
TIP
* 카멜 표기법(Camel Case) :
첫 번째 단어는 소문자로 쓰고, 그 이후 단어의 첫 글자는 대문자로 쓰는 방식. (ex. numCount)
* 스네이크 표기법(Snake Case) :
모든 단어를 소문자로 쓰고, 각 단어를 밑줄(_)로 구분하는 방식. (ex. product_new)
두 표기법은 각각의 사용 분야와 개발자의 취향에 따라 선택됨.
* 추후 mySQL에 익숙해진다면, tb_member, mem_userid 등과 같이 언더바(_)를 사용하여 앞에 구분을
해두면 복잡한 상황에서도 작성하기가 용이해짐.
* '정규화'를 사용함으로써 join이 빈번하게 사용된다면 실무적으로 속도가 느려질 수도 있기 때문에,
정규화가 계속 진행될수록 실무자간에 사용의 협의가 필요하다.
