DB [혼공SQL] 정리 + 쿼리문

 

 

 

 

정리

 

취업 준비 및 면접 질문에 대비하며 SQL의 중요성을 다시 한번 느꼈다. 혼자공부하는 SQL을 구매하여 빠르게 내용을 한번 정리하였다.

 

 

조작어

 

SELECT 문 순서,

 

GROUP BY는 WHERE 대신 HAVING과 같이 사용
주로 집계 함수(SUM, MIN, AVG등)과 주로 사용

 

 

조작어

 

SELECT 문 순서,

 

GROUP BY는 WHERE 대신 HAVING과 같이 사용
주로 집계 함수(SUM, MIN, AVG등)과 주로 사용

'

INSERT, UPDATE, DELETE 문 

 

 

데이터형식

 

변수사용 

 

PREPARE, EXECUTE

 

LIMIT에는 변수사용이 불가능하여 PREPARE, EXECUTE 로 대체

 

형변환 - CAST, CONVERT

 

실수를 정수(SINGED)로 변환

 

 

조인

 

내부 조인

 

두 테이블에 모두 있는 내용만 출력

 

별칭(밑의 경우 M, B) 사용 권장 - 컬럼명이 겹칠 수 있기 때문

 

외부 조인 : 한쪽 테이블에만 있는 내용도 출력

 

상호 조인 : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인 - 두 테이블의 각 행의 개수를 곱한 개수

 

자체 조인 : 1개의 테이블로 조인 - 2개 이상의 열로 존재할 때 가능

 

 

SQL 프로그래밍

 

 

프로그래밍 언어와 비슷하게 IF/ELSE, CASE,WHILE 존재 

 

 

 

제약조건

 

기본키(PRIMARY KEY)와 외래키(FORIGN KEY)

 

두 테이블이 연결된 상태에서는 기준 테이블의 열 이름이 변경/삭제 되지 않음. 이때 ON UPDATE/DELETE CASCADE를 사용가능

UNIQUE : 중복 허용X, 비어있는 값(NULL) 허용

 

CHECK : 입력되는 데이터 점검

 

DEFALUT : 입력하지 않았을 때 기본값

 

 

 

뷰

 

SELECT문으로 만들어져 있음. 테이블로 취급

 

보안

 

SQL을 단순하게 만들 수 있음

 

CREATE OR REPLACE VIEW : 기존 뷰 덮어쓰기 

 

뷰에서 데이터 입력을 하려면, 뷰에서 보이지 않는 테이블의 열에 NOT NULL이 없어야함

 

WITH CHECK OPTION : 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않게 함

 

 

 

인덱스

 

SELECT문으로 검색하는 속도가 빨라지지만 추가적인 공간이 필요함.

 

책에 있는 표지 Or 찾아 가기

 

 

 

스토어드프로시져

 

함수 만들기 

 

SQL 안에서 코딩 

 

 

 

DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE market_db;

USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);
CREATE TABLE buy -- 구매 테이블
(  num 		INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
   mem_id  	CHAR(8) NOT NULL, -- 아이디(FK)
   prod_name 	CHAR(6) NOT NULL, --  제품이름
   group_name 	CHAR(4), -- 분류
   price     	INT  NOT NULL, -- 가격
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');

SELECT * FROM member;

INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);

SELECT * FROM member;
SELECT * FROM buy;


-- 실습 1. WHERE 단순 조회
SELECT mem_name FROM member;

SELECT addr "주소", debut_date "데뷔 일자", mem_name FROM member;

-- 2. 관계 연산자, 논리 연산자의 사용 (평균키 162 이하, 165 이상이면서 인원 6명 초과, 평균키 165이상이거나 인원 6명 초과)
SELECT mem_id, mem_name 
	FROM member
    WHERE height <= 162;
    
    
SELECT mem_name, height, mem_number
	FROM member
	WHERE height >= 165 AND mem_number > 6;
    
    
SELECT mem_name, height, mem_number
	FROM member
	WHERE height >= 165 OR mem_number > 6;
    
-- 3. BETWEEN AND (키 163~165)

SELECT mem_name, height
	FROM member
    WHERE height >= 163 AND height <= 165;
    
SELECT mem_name, height
	FROM member
    WHERE height BETWEEN 163 AND 165; 
    
    
-- IN (경기, 전남, 경남에 사는 사람 OR과 같음)
SELECT mem_name, addr
	FROM member
    WHERE addr = '경기' OR addr = '전남' OR addr = '경남';
    
SELECT mem_name, addr
	FROM member
    WHERE addr IN('경기', '전남', '경남'); 
    

    
-- LIKE (앞글자 우~ 회원 첮기, __핑크 회원 찾기 ) 
SELECT *
	FROM member
    WHERE mem_name LIKE '우%';

    
SELECT *
	FROM member
    WHERE mem_name LIKE '__핑크'; -- 언더바 두개 
    

-- 서브쿼리 (이름이 '에이핑크' 회원의 평균 키 보다 큰 회원을 검색) 
SELECT mem_name, height 
	FROM member
    WHERE height > (SELECT height FROM member WHERE mem_name = '에이핑크');



SELECT * FROM member;

-- ORDER BY 절 (오름차순, 내림차순 쓸때) (키는 내림차순, 날짜는 오름차순) 
SELECT mem_id, mem_name, debut_date, height 
	FROM member
    WHERE height >= 164
    ORDER BY height DESC; 
    

SELECT mem_id, mem_name, debut_date, height 
	FROM member
    WHERE height >= 164
    ORDER BY height DESC, debut_date ASC; 
    
    
-- LIMIT (출력의 개수 제한) (member에서 3개만 출력)
SELECT * 
	FROM member
    LIMIT 3; 
    
    
-- DISTINCT (중복된 결과를 제거) 
SELECT addr FROM member; -- 경남 중복 
SELECT DISTINCT addr FROM member; 


-- GROUP BY + HAVING (더하고, 평균, 최소, 최대, 행의 수) 
SELECT mem_id "회원 아이디", SUM(amount) "총 구매 개수"
	FROM buy
	GROUP BY mem_id; -- 합 구할때 (이름이 같은거)
    
SELECT mem_id "회원 아이디", SUM(price*amount) "총 구매 금액"
	FROM buy
    GROUP BY mem_id; -- 총 구매 금액 구할때 
    
    
SELECT AVG(amount) "평균 구매 개수" FROM buy; -- 테이블의 평균 구매 개수 


SELECT mem_id, AVG(amount) "평균 구매 개수"
	FROM buy
    GROUP BY mem_id; -- 아이디별 평균 구매 개수 

SELECT COUNT(phone1) "연락처가 있는 회원" FROM member; -- phone1 열에 null 값 제외 

-- HAVING 절 (ORDER BY 쓸 떄 where 역할) (총 구매 금액이 1000이상)
SELECT mem_id "회원 아이디", SUM(price*amount) "총 구매 금액"
	FROM buy
    GROUP BY mem_id
    HAVING SUM(price*amount) > 1000;
    -- ORDER BY SUM(price*amount) DESC;

-- --------------------
-- 3
-- --------------------

-- INSERT 데이터 입력 + 자동증가 AUTO_INCREMENT (INSERT는 null로) 

USE market_db;
CREATE TABLE hongong2 (
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT
);

INSERT INTO hongong2 VALUES (NULL, '보핍', 25); 

-- UPDATE 데이터 수정 (city_name 열의 'Seoul" -> '서울') (city_name열의 'New York' -> 뉴옥, population을 0으로 설정)

UPDATE city_popul 
	SET city_name = '서울'
    WHERE city_name = 'Seoul';
    
    
SELECT * FROM city_popul WHERE city_name = '서울'; 

UPDATE city_popul
	SET city_name = '뉴욕', population = 0
    WHERE city_name = 'New York';
    
    
SELECT * FROM city_popul WHERE city_name = '뉴욕'; 

-- DELETE 데이터 삭제 
DELETE FROM city_popul
	WHERE city_name LIKE 'New%'; 
    
    
-- --------------------
-- 4 조인 (존나 중요) 
-- --------------------

-- 내부 조인 (두 테이블 모두 지정한 열의 데이터가 있을때)  
	-- (구매 테이블의 GRL아이디를 가진 사람이 구매한 물건 발송), (아이디/이름/구매 물품/주소/연락처)
SELECT * 
	FROM buy
    INNER JOIN member
    ON buy.mem_id = member.mem_id
    WHERE buy.mem_id = 'GRL';
    
    

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr,
	CONCAT(M.phone1, M.phone2) '연락처'
    FROM buy B
		INNER JOIN member M 
        ON B.mem_id = M.mem_id;
        

        
	-- (우리 사이트에서 한 번이라도 구매한 기록이 있는 회원들에게 안내문 발송) 
SELECT DISTINCT M.mem_id, M.mem_name, M.addr -- DISTINCT 중복제거 
	FROM buy B
		INNER JOIN member M 
        ON B.mem_id = M.mem_id
	ORDER BY M.mem_id;
    

-- 외부 조인 (두 테이블 중, 1개의 테이블에만 데이터가 있을때)
	-- (전체 회원의 구매 기록(구매 기록이 없는 회원의 정보도 출력) null출력 할때) (회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원)
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM member M -- 왼쪽 테이블 기준 
		LEFT OUTER JOIN buy B
        ON M.mem_id = B.mem_id 
	ORDER BY M.mem_id;
    

SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
	FROM member M
		LEFT OUTER JOIN buy B
        ON M.mem_id = B.mem_id
	WHERE B.prod_name IS NULL
    ORDER BY M.mem_id;
    

-- --------------------
-- 5 복습 
-- --------------------

-- GUI랑 SQL로 데이터 만들기 차이 
DROP DATABASE IF EXISTS naver_db;
CREATE DATABASE naver_db;

USE naver_db;
DROP TABLE IF EXISTS member;

CREATE TABLE member(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    mem_number TINYINT NOT NULL,
    addr CHAR(2) NOT NULL,
    phone1 CHAR(3) NULL,
    phone2 CHAR(8) NULL,
    height TINYINT UNSIGNED NULL,
    debut_date DATE NULL
);

DROP TABLE IF EXISTS buy;

CREATE TABLE buy(
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id CHAR(8) NOT NULL,
    prod_name CHAR(6) NOT NULL,
    group_name CHAR(4) NULL,
    price INT UNSIGNED NOT NULL,
    amount SMALLINT UNSIGNED NOT NULL, -- UNSIGNED는 양의 정수만 
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15');

INSERT INTO buy VALUES( NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES( NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES( NULL, 'APN', '아이폰', '디지털', 200, 1);

-- --------------------
-- 제약 조건
-- --------------------


-- 기본키 제약조건 PK : 데이터를 구분할 수 있는 식별자 

-- ALTER TABLE (제약조건 수정)
ALTER TABLE member
	ADD CONSTRAINT
    PRIMARY KEY (mem_id);
    
    
-- 외래키 제약조건 FK : 구매 테이블의 아이디(FK)는 반드시 회원 테이블의 아이디(PK)로 존재 
-- ex) FOREIGN KEY(mem_id) REFERENCES member(mem_id);

-- ALTER TABLE ( 외래키 제약조건 수정)
ALTER TABLE buy
	ADD CONSTRAINT
    FOREIGN KEY(mem_id)
    REFERENCES member(mem_id);
    
    
-- ON UPDATE CASE (기준 테이블의(buy) 열 이름이 변경될 때 참조 테이블(member)의 열 이름도 자동으로 변경)
ALTER TABLE buy
	ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id) -- FK 넣고 
    ON UPDATE CASE -- 참조 테이블 변경 허용
    ON DELETE CASE; -- 참조 테이블 삭제 허용  
    
											
-- UNIQEU (고유키) ex.이메일 
-- ex) email CHAR(20) NULL UNIQUE 

-- 체크 제약 조건 (전화번호 92, 031, 051 만 입력)
ALTER TABLE member
	ADD CONSTRAINT
    CHECK (phone1 IN('02', '031', '051')); 
    

-- --------------------
-- SQL FUNCTION
-- --------------------

-- IF 
DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE myNum INT;
    SET myNum = 200;
    IF myNum = 100 THEN
		SELECT '100입니다.';
	ELSE
		SELECT '100이 아닙니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc2(); 


-- CASE 문 (CASE ~ END) (총 구매액에 따라 회원 등급 분류)_구매 안한 사람 null 도 있어서 조인 필요
SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액",
		CASE
			WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
			WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
			WHEN (SUM(price*amount) >= 1) THEN '일반고객'
			ELSE '유령고객'
		END "회원등급"
	FROM buy B
		RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
	GROUP BY M.mem_id
    ORDER BY SUM(price*amount) DESC; 
    
    
-- WHILE 문 = 반복문 (1~100 까지 합) 
DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
	DECLARE i INT; -- 1에서 100 까지 증가할 변수 
    DECLARE hap INT; -- 더한 값을 누적할 변수 
    SET i = 1;
    SET hap = 0;
    
    WHILE ( i <= 100) DO
		SET hap = hap + i; -- hap의 원래 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET i = i + 1; -- i의 원래 값에 1을 더해서 다시 i에 넣으라는 의미
	END WHILE;
		SELECT '1부터 100까지의 합 ==>', hap;
	END$$
DELIMITER ;
CALL whileProc(); 


-- 동적 SQL 
DROP PROCEDURE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간 

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_tale;