MySQL 명령어 : SELECT, ORDER BY, WHERE, DISTINCT

MySQL 의 명령어 중 SELECT, ORDER BY, WHERE, DISTINCT 에 대해 알아보고 간단한 실습까지 진행해보려고 한다.

실습 진행을 위해 아래의 menu db를 사용할 것이다. ERD로 나타내면 아래와 같고 실습 전에 그림 밑에 MySQL에서 사용할 db의 script를 넣고 진행하면 된다. 

 

MENU DB

• ERD (Entity Relationship Diagram)
  • 논리모델

• 물리모델

 

 

use menudb;

-- 테이블 삭제
DROP TABLE IF EXISTS tbl_payment_order CASCADE;
DROP TABLE IF EXISTS tbl_payment CASCADE;
DROP TABLE IF EXISTS tbl_order_menu CASCADE;
DROP TABLE IF EXISTS tbl_order CASCADE;
DROP TABLE IF EXISTS tbl_menu CASCADE;
DROP TABLE IF EXISTS tbl_category CASCADE;

-- 테이블 생성
-- category 테이블 생성
CREATE TABLE IF NOT EXISTS tbl_category
(
    category_code    INT AUTO_INCREMENT COMMENT '카테고리코드',
    category_name    VARCHAR(30) NOT NULL COMMENT '카테고리명',
    ref_category_code    INT COMMENT '상위카테고리코드',
    CONSTRAINT pk_category_code PRIMARY KEY (category_code),
    CONSTRAINT fk_ref_category_code FOREIGN KEY (ref_category_code) REFERENCES tbl_category (category_code)
) ENGINE=INNODB COMMENT '카테고리';

CREATE TABLE IF NOT EXISTS tbl_menu
(
    menu_code    INT AUTO_INCREMENT COMMENT '메뉴코드',
    menu_name    VARCHAR(30) NOT NULL COMMENT '메뉴명',
    menu_price    INT NOT NULL COMMENT '메뉴가격',
    category_code    INT NOT NULL COMMENT '카테고리코드',
    orderable_status    CHAR(1) NOT NULL COMMENT '주문가능상태',
    CONSTRAINT pk_menu_code PRIMARY KEY (menu_code),
    CONSTRAINT fk_category_code FOREIGN KEY (category_code) REFERENCES tbl_category (category_code)
) ENGINE=INNODB COMMENT '메뉴';


CREATE TABLE IF NOT EXISTS tbl_order
(
    order_code    INT AUTO_INCREMENT COMMENT '주문코드',
    order_date    VARCHAR(8) NOT NULL COMMENT '주문일자',
    order_time    VARCHAR(8) NOT NULL COMMENT '주문시간',
    total_order_price    INT NOT NULL COMMENT '총주문금액',
    CONSTRAINT pk_order_code PRIMARY KEY (order_code)
) ENGINE=INNODB COMMENT '주문';


CREATE TABLE IF NOT EXISTS tbl_order_menu
(
    order_code INT NOT NULL COMMENT '주문코드',
    menu_code    INT NOT NULL COMMENT '메뉴코드',
    order_amount    INT NOT NULL COMMENT '주문수량',
    CONSTRAINT pk_comp_order_menu_code PRIMARY KEY (order_code, menu_code),
    CONSTRAINT fk_order_menu_order_code FOREIGN KEY (order_code) REFERENCES tbl_order (order_code),
    CONSTRAINT fk_order_menu_menu_code FOREIGN KEY (menu_code) REFERENCES tbl_menu (menu_code)
) ENGINE=INNODB COMMENT '주문별메뉴';


CREATE TABLE IF NOT EXISTS tbl_payment
(
    payment_code    INT AUTO_INCREMENT COMMENT '결제코드',
    payment_date    VARCHAR(8) NOT NULL COMMENT '결제일',
    payment_time    VARCHAR(8) NOT NULL COMMENT '결제시간',
    payment_price    INT NOT NULL COMMENT '결제금액',
    payment_type    VARCHAR(6) NOT NULL COMMENT '결제구분',
    CONSTRAINT pk_payment_code PRIMARY KEY (payment_code)
) ENGINE=INNODB COMMENT '결제';


CREATE TABLE IF NOT EXISTS tbl_payment_order
(
    order_code    INT NOT NULL COMMENT '주문코드',
    payment_code    INT NOT NULL COMMENT '결제코드',
    CONSTRAINT pk_comp_payment_order_code PRIMARY KEY (payment_code, order_code),
    CONSTRAINT fk_payment_order_order_code FOREIGN KEY (order_code) REFERENCES tbl_order (order_code),
    CONSTRAINT fk_payment_order_payment_code FOREIGN KEY (order_code) REFERENCES tbl_payment (payment_code)
) ENGINE=INNODB COMMENT '결제별주문';

-- 데이터 삽입
INSERT INTO tbl_category VALUES (null, '식사', null);
INSERT INTO tbl_category VALUES (null, '음료', null);
INSERT INTO tbl_category VALUES (null, '디저트', null);
INSERT INTO tbl_category VALUES (null, '한식', 1);
INSERT INTO tbl_category VALUES (null, '중식', 1);

INSERT INTO tbl_category VALUES (null, '일식', 1);
INSERT INTO tbl_category VALUES (null, '퓨전', 1);
INSERT INTO tbl_category VALUES (null, '커피', 2);
INSERT INTO tbl_category VALUES (null, '쥬스', 2);
INSERT INTO tbl_category VALUES (null, '기타', 2);

INSERT INTO tbl_category VALUES (null, '동양', 3);
INSERT INTO tbl_category VALUES (null, '서양', 3);

INSERT INTO tbl_menu VALUES (null, '열무김치라떼', 4500, 8, 'Y');
INSERT INTO tbl_menu VALUES (null, '우럭스무디', 5000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '생갈치쉐이크', 6000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '갈릭미역파르페', 7000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '앙버터김치찜', 13000, 4, 'N');

INSERT INTO tbl_menu VALUES (null, '생마늘샐러드', 12000, 4, 'Y');
INSERT INTO tbl_menu VALUES (null, '민트미역국', 15000, 4, 'Y');
INSERT INTO tbl_menu VALUES (null, '한우딸기국밥', 20000, 4, 'Y');
INSERT INTO tbl_menu VALUES (null, '홍어마카롱', 9000, 12, 'Y');
INSERT INTO tbl_menu VALUES (null, '코다리마늘빵', 7000, 12, 'N');

INSERT INTO tbl_menu VALUES (null, '정어리빙수', 10000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '날치알스크류바', 2000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '직화구이젤라또', 8000, 12, 'Y');
INSERT INTO tbl_menu VALUES (null, '과메기커틀릿', 13000, 6, 'Y');
INSERT INTO tbl_menu VALUES (null, '죽방멸치튀김우동', 11000, 6, 'N');

INSERT INTO tbl_menu VALUES (null, '흑마늘아메리카노', 9000, 8, 'Y');
INSERT INTO tbl_menu VALUES (null, '아이스가리비관자육수', 6000, 10, 'Y');
INSERT INTO tbl_menu VALUES (null, '붕어빵초밥', 35000, 6, 'Y');
INSERT INTO tbl_menu VALUES (null, '까나리코코넛쥬스', 9000, 9, 'Y');
INSERT INTO tbl_menu VALUES (null, '마라깐쇼한라봉', 22000, 5, 'N');

INSERT INTO tbl_menu VALUES (null, '돌미나리백설기', 5000, 11, 'Y');

COMMIT;

 

 

 

 

 

# SELECT FROM 

-- 단일 컬럼 조회
SELECT menu_name
  FROM tbl_menu;

-- 다중 컬럼 조회
SELECT menu_code, menu_name, menu_price, category_code, orderable_status
  FROM tbl_menu;
  
-- 전체 컬럼 조회
SELECT *   # 실무에서는 * 을 쓰면 어떤 정보를 원하는 지 직관적으로 이해할 수 없기 때문에 '*'사용을 지양한다.
  FROM tbl_menu;
  
-- 연산자 사용 가능 
SELECT 12 + 17; -- FROM DUAL(Oracle)
SELECT 12 - 17;
SELECT 12 * 17;
SELECT 12 / 17;
SELECT 12 % 17;

SELECT now()

 

 

# ORDER BY 

-- 오름차순 정렬
SELECT menu_code, menu_name, menu_price
  FROM tbl_menu 
    ORDER BY menu_name;

-- 내림차순 정렬 (DESC를 명시적으로 작성해야 내림차순 정렬됨)
SELECT menu_code, menu_name, menu_price
  FROM tbl_menu 
    ORDER BY menu_name DESC;

-- 다중 조건 정렬 가능 
SELECT menu_code, menu_name, menu_price
  FROM tbl_menu 
    ORDER BY menu_price, menu_name;  # 두개 이상 사용시 앞의 순서 정렬이 우선.

-- 컬럼의 연산 결과로 정렬 가능 -- 별칭을 사용한 정렬 가능
SELECT menu_code, menu_name, menu_price, menu_code * menu_price as `연산결과`
  FROM tbl_menu 
    ORDER BY `연산결과`;

-- 오름차순(ASC) 정렬 시 NULL이 맨처음 (default)
-- IS NULL을 붙이면 오름차순(ASC) 정렬 시 NULL을 맨끝으로 (IS NULL ASC) : ASC 생략 가능
SELECT category_code, category_name, ref_category_code
  FROM tbl_category
	ORDER BY ref_category_code IS NULL; 
    
-- 내림차순(DESC) 정렬 시 NULL이 맨끝 (default)
-- IS NULL을 붙이면 내림차순(DESC) 정렬 시 NULL을 맨처음으로 (IS NULL DESC): DESC 생략 불가 
SELECT category_code, category_name, ref_category_code
  FROM tbl_category
	ORDER BY ref_category_code IS NULL DESC, ref_category_code DESC;

 

 

# WHERE 

-- 1) 비교 연산자
SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE orderable_status = 'Y';
    
SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE orderable_status != 'Y';

SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE menu_price <= 10000;

-- SELECT menu_name, menu_price, orderable_status
--   FROM tbl_menu
--     WHERE 10000 < menu_price <= 20000;


-- 2) AND
SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE 10000 < menu_price
    AND menu_price <= 20000;

-- 3) OR
SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE menu_price > 30000
    OR menu_name = '열무김치라떼';
    

-- 4) BETWEEN
SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE menu_price BETWEEN 10000 AND 20000;       # 경계 포함
    
SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE menu_price NOT BETWEEN 10000 AND 20000; 
    
-- 5) LIKE

SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE menu_name LIKE '%김치%';
    
SELECT menu_name, menu_price, orderable_status
  FROM tbl_menu
    WHERE menu_name NOT LIKE '%김치%';
    
-- 6) IN	

SELECT menu_name, menu_price, orderable_status, category_code
  FROM tbl_menu
    WHERE category_code IN (4,5,6);
    
SELECT menu_name, menu_price, orderable_status, category_code
  FROM tbl_menu
    WHERE category_code NOT IN (4,5,6);
    

-- 7) IS NULL 
SELECT category_code, category_name, ref_category_code
  FROM tbl_category
    WHERE ref_category_code IS NULL;

SELECT category_code, category_name, ref_category_code
  FROM tbl_category
    WHERE ref_category_code IS NOT NULL;

#  DISTINCT

SELECT DISTINCT category_code
  FROM tbl_menu
	ORDER BY category_code;
    
SELECT DISTINCT ref_category_code
  FROM tbl_category;

SELECT DISTINCT category_code, orderable_status
  FROM tbl_menu
	ORDER BY category_code, orderable_status;