MySQL의 함수

use kdt;

MySQL의 문자열 함수

이 함수는 MySQL전용 함수이고 다른 SQL에는 없을수도 있다.
CONCAT: 복수의 문자열을 연결해주는 함수

SELECT CONCAT('안녕하세요! ', 'MySQL') AS CONCAT;
SELECT * FROM MEMBER;
SELECT CONCAT(ADDRESS1, ' ', ADDRESS2, ' ', ADDRESS3) AS ADDRESS
FROM MEMBER WHERE USERID = 'APPLE';

# LEFT, RIGHT: 왼쪽 또는 오른쪽에서 길이만큼 문자열을 가져옴
SELECT LEFT('ABCDEFGHIJKLMNOP', 5);
SELECT USERID, LEFT(USERPW, 2) AS PASSWORD FROM  MEMBER;

5글자만 출력

패스워드 앞 두글자만 풀력

# SUBSTRING: 문자열의 일부를 가져옴
# SUBSTRING(문자열, 시작위치): 시작위치부터 끝까지
# SUBSTRING(문자열, 시작위치, 길이): 시작위치부터 길이만큼
SELECT substring('ABCDEFGHIJKLMN', 5) AS SUB;
SELECT SUBSTRING('ABCDEFGHIJKLMN', 5, 3) AS SUB;
SELECT USERID, substring(USERPW,1, 3) AS PASSWORD FROM  MEMBER;

5글자부터 끝까지 출력

5번째 글자부터 3글자 출력

패스워드 첫번째부터 세글자 출력

# CHAR_LENGTH: 문자열의 길이를 반환alter
SELECT char_length('ABCDEFGHIJKLMN') AS CNT;
SELECT email, char_length(email) AS LEN FROM MEMBER;

LPAD, RPAD: 왼쪽 또는 오른쪽의 해당 길이만큼 늘리고, 빈 공간을 채울 문자열을 반환
LPAD(문자열, 총길이, 채울문자열)

SELECT LPAD('ABCDEFG', 10, '0') AS LAPD;
SELECT RPAD('ABCDEFG', 10, '0') AS LAPD;

RPAD

SELECT USERID, RPAD(USERID, 20, '*') AS PAD FROM MEMBER;

LTRIM, RTRIM, TRIM: 왼쪽, 오른쪽, 모든 공백을 제거

SELECT LTRIM('     ABCDEF     ') AS LTRIM;
SELECT TRIM('     ABCDEF     ') AS TRIM;

REPLACE: 문자열에서 특정 문자열을 변경
REPLACE(문자열, 대상, 바꿀 문자열)

SELECT REPLACE('ABCDEFG', 'CD', '') AS REPL;

문자열 CD 를 공백으로 변경

유니온(UNION)
합집합을 나타내는 연산자로, 중복된 값을 제거함
서로 같은 종류의 테이블(컬럼이 같아야 함)에서만 적용이 가능
SELECT 컬럼명1, 컬럼명2, ... FROM 테이블1 UNION SELECT 컬럼명1, 컬럼명2, ... FROM 테이블2;

CREATE TABLE PRODUCT(
    CODE VARCHAR(5) NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    DETAIL VARCHAR(1000),
    PRICE INT DEFAULT 0,
    REGDATE DATETIME DEFAULT NOW()
);
DESC PRODUCT;

INSERT INTO PRODUCT VALUES('00001', '그래픽카드','엄청 빨라요', 400000, NOW());
INSERT INTO PRODUCT VALUES('00002', '맥북', '너무 예뻐요', 1400000, NOW());
INSERT INTO PRODUCT VALUES('00003', '밀키트', '맛있어요', 10000, NOW());
INSERT INTO PRODUCT VALUES('00004', '서버', '잘돌아요', 10000000, NOW());
INSERT INTO PRODUCT VALUES('00005', '스포츠카', '빨라요', 100000000, NOW());

SELECT * FROM PRODUCT;
DELETE FROM PRODUCT;

첫번째 테이블

CREATE TABLE PRODUCT_NEW(
    CODE VARCHAR(5) NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    DETAIL VARCHAR(1000),
    PRICE INT DEFAULT 0,
    REGDATE DATETIME DEFAULT NOW()
);

INSERT INTO PRODUCT_NEW VALUES('10001', '게임','재밌어요', 91400, NOW());
INSERT INTO PRODUCT_NEW VALUES('10002', '집', '넓어요', 50000000, NOW());
INSERT INTO PRODUCT_NEW VALUES('10003', '고양이', '야옹', 500000, NOW());
INSERT INTO PRODUCT_NEW VALUES('10004', '강아지', '멍멍', 400000, NOW());


SELECT * FROM PRODUCT_NEW;

INSERT INTO PRODUCT VALUES('00003', '밀키트', '맛있어요', 10000, NOW());

두번째 테이블

SELECT CODE, NAME, PRICE, REGDATE FROM PRODUCT
UNION
SELECT CODE, NAME, PRICE, REGDATE FROM PRODUCT_NEW;
# 날짜/시간이 다르기 때문에 중복 데이터가 모두 출력되었다.

SELECT CODE, NAME, PRICE FROM PRODUCT
UNION
SELECT CODE, NAME, PRICE FROM PRODUCT_NEW;
# UNION은 중복된 데이터를 하나만 출력한다.

SELECT CODE, NAME, PRICE FROM PRODUCT
UNION ALL
SELECT CODE, NAME, PRICE FROM PRODUCT_NEW;
# UNION ALL은 중복된 데이터를 모두 출력한다.

서브쿼리(Sub Query)

• 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미
• 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고도 부름
• 서브쿼리는 괄호()를 사용해서 표현
• SELECT, WHERE, FROM, HAVING 등에서 사용할 수 있음

SELECT * FROM PRODUCT;
# 상품코드가 '00001' 의 가격보다 크거나 같은 가격을 가지고 있는 상품의 모든 정보를 출력
SELECT * FROM PRODUCT WHERE PRICE >= (SELECT PRICE FROM PRODUCT WHERE CODE='00001');

# PRODUCT 테이블에서 모든 상품의 CODE, NAME, PRICE, 가장비싼가격
SELECT CODE,NAME,PRICE, (SELECT MAX(PRICE) FROM PRODUCT) AS 'MAX PRICE' FROM PRODUCT ;

# AUTO_INCREMENT: 필드에 IDENTITY한 숫자를 자동으로 부여
CREATE TABLE ORDERS(
    No INT auto_increment primary KEY, # AUTO_INCREMENT 를 사용하려면 해당컬럼에 PRIMARY KEY를 걸어주어야 한다
    USERID VARCHAR(20) NOT NULL,
    PRODUCT_CODE VARCHAR(5) NOT NULL,
    CNT INT DEFAULT 0,
    REGDATE DATETIME DEFAULT NOW(),
    FOREIGN KEY(USERID) REFERENCES MEMBER(USERID)
);

DESC ORDERS;

테이블 생성

INSERT INTO ORDERS VALUES (1, 'APPLE', '00005', 1, NULL);
SELECT * FROM ORDERS;
INSERT INTO ORDERS VALUES (10, 'BANANA', '00004', 1, NULL);
SELECT * FROM ORDERS;
-- INSERT INTO ORDERS VALUES (10, 'APPLE', '00003', 2, NULL); # 중복된 No
INSERT INTO ORDERS (USERID, PRODUCT_CODE, CNT) VALUES ('APPLE', '00003', 2);
SELECT * FROM ORDERS;

INSERT INTO ORDERS (USERID, PRODUCT_CODE, CNT) VALUES ('BANANA', '00002', 1);
INSERT INTO ORDERS (USERID, PRODUCT_CODE, CNT) VALUES ('ORANGE', '00004', 1);
INSERT INTO ORDERS (USERID, PRODUCT_CODE, CNT) VALUES ('AVOCADO', '00003', 1);
INSERT INTO ORDERS (USERID, PRODUCT_CODE, CNT) VALUES ('CHERRY', '00001', 1);
SELECT * FROM ORDERS;

auto_increment 는 내가 입력한 값부터 순차적으로 증가한다.

# 상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력
SELECT USERID,NAME,GENDER FROM MEMBER WHERE USERID 
IN (SELECT USERID FROM ORDERS group by USERID HAVING COUNT(NO)>=2);

# 상품을 최소 2번이상 구입한 아이디의 앞2 글자와 이름, 상품 구입횟수를 출력
# 조인
SELECT LEFT(M.USERID, 2),NAME, COUNT(O.NO) AS CNT FROM MEMBER AS M INNER JOIN 
ORDERS AS O ON M.USERID = O.USERID GROUP BY M.USERID HAVING CNT >=2;

# 서브쿼리
SELECT LEFT(M.USERID, 2), M.NAME, SUB.OCNT FROM MEMBER AS M INNER JOIN
(SELECT USERID, COUNT(NO) AS OCNT FROM ORDERS GROUP BY USERID HAVING COUNT(NO) >= 2) AS SUB
ON M.USERID = SUB.USERID;

서브쿼리 문을 사용하니 코드가 오히려 더 복잡해 진걸 볼수있다.

상황에따라 적절한 코드를 사용하자.

CREATE TABLE ORDERS_NEW(
    No INT auto_increment primary KEY,
    USERID VARCHAR(20) NOT NULL,
    PRODUCT_CODE VARCHAR(5) NOT NULL,
    CNT INT DEFAULT 0,
    REGDATE DATETIME DEFAULT NOW(),
    FOREIGN KEY(USERID) REFERENCES MEMBER(USERID)
);

테이블 생성

SELECT * FROM ORDERS_NEW;
INSERT INTO ORDERS_NEW(SELECT * FROM ORDERS);

CREATE TABLE ORDERS_NEW_NEW(SELECT * FROM ORDERS);

 

 

서브쿼리를 사용하면 이런식으로 손쉽게 테이블을 복사할수 있다.

 

뷰(VIEW)

• 가상의 테이블을 생성
• 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 직접 저장하고 있지 않음

뷰를 사용하는 이유

• SQL 코드를 간결하게 만들어 줌
• 삽입, 삭제, 수정 작업에 대한 제한 사항을 가지게 만들수 있음
• 내부 데이터를 전체 공개하고 싶지 않을 때

CREATE VIEW VW_MEMBER AS SELECT USERID, NAME, HP, GENDER FROM MEMBER; SELECT * FROM VW_MEMBER;
CREATE * FROM MEMBER;

 

member테이블중 userid, name, hp, gender 의 데이터만 가져와 view 로 만들었다.

 

 

# MEMBER 테이블의 USERID, NAME, HP와 PROFILE 테이블의 MBTI를 가지는 가상 테이블(뷰)를 만들어보자
# VW_MEMBER_PROFILE
CREATE VIEW VW_MEMBER_PROFILE AS SELECT M.USERID, NAME, HP, P.MBTI FROM MEMBER AS M INNER JOIN PROFILE AS P
ON M.USERID = P.USERID;

SELECT * FROM VW_MEMBER_PROFILE;

 

 

 

# 뷰 수정하기
# ALTER VIEW 뷰이름 AS 쿼리문
ALTER VIEW VW_MEMBER_PROFILE AS SELECT M.USERID, NAME, HP, P.MBTI FROM MEMBER AS M LEFT JOIN PROFILE AS P
ON M.USERID = P.USERID;

 

 

 

이너조인 부분을 레프트 조인 으로 수정하였다.

 

# 뷰 삭제하기
DROP VIEW VW_MEMBER_PROFILE;