유데미 스타터스 취업 부트캠프 4기

by

데이터 정의(DDL)

  • CREATE(스키마, 테이블 구조 생성)
CREATE TABLE 테이블명{
	ID INTEGER PRIMARY KEY,
	NAME VARCHAR(20) NOT NULL,
	PRICE INTEGER
};
  • AGE(테이블 구조 변경)
# 컬럼 추가
ALTER TABLE 테이블명
ADD 컬럼명 VACHAR(30);

# 컬럼 타입 변경
ALTER TABLE 테이블명
MODIFY 컬럼명 INTEGER;

# 컬럼 삭제
ALTER TABLE 테이블명
DROP COLUMN 컬럼명;
  • DROP(드롭 테이블 구조)
DROP TABLE 테이블명;
  • RENAME(테이블 이름 변경)
  • TRUNCATE(모든 데이터 삭제)

데이터 조작(DML)

  • 선택(쿼리)
SELECT 컬럼명
FROM 테이블명
WHERE 조건
GROUP BY 컬럼명
HAVING 조건
ORDER BY 컬럼명
  • 삽입(삽입)
INSERT INTO 테이블명(컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...)
  • 업데이트하려면
UPDATE 테이블명
SET 속성1 = 값1
WHERE 조건
  • 삭제 삭제)
DELETE FROM 테이블명;

데이터 제어(DCL)

  • 승인하다
  • REVOKE(트랜잭션 제어)

조건문

  • 어디
  • 패턴매칭(LIKE)
운영자 기능 예 설명
% 여러 문자와 일치하는 패턴 %두루미% 과학을 포함하는 문자열, 예. B. 철학 또는 학과
_ 문자와 일치하는 패턴 _두루미 앞에 임의의 문자가 있는 문자열입니다.
() 1자에 해당 (0-9)% 0에서 9 사이의 숫자로 시작하는 문자열
(^) 1개의 문자와 일치 (^0-9)% 0에서 9 사이의 숫자로 시작하지 않는 문자열

영

  • NULL+숫자 연산의 결과는 NULL입니다.

  • 집계 함수를 계산할 때 NULL을 포함하는 행은 집계에서 제외됩니다.

  • 해당 행이 없으면 SUM, AVG 함수의 결과는 NULL이고 COUNT 함수의 결과는 0입니다.

제한

  • 특정 테이블 수준 규칙
  • 예상치 못한 데이터 손실 또는 일관성 없는 데이터 추가, 변경 등을 방지합니다.

여러 조건(CASE)

  • 검색 결과 및 출력에서 ​​중복 데이터 제외
  • CASE는 내장 함수가 아니지만 연산자로 분류됩니다.

  • WHERE와의 차이점: 결과를 가져오는 WHERE / 결과를 필터링하는 CASE
SELECT 
	CUSTID, 
	SUM(SALEPRICE) AS '총구매액',
	CASE 
		WHEN (SALEPRICE >= 15000) THEN '최우수고객'
		WHEN (SALEPRICE >= 10000) THEN '우수고객'
		WHEN (SALEPRICE >= 5000) THEN '일반고객'
		ELSE
	END AS '고객등급'
FROM ORDERS
GROUP BY CUSTID;

상태 확인(IF)

  • 조건별 분기
  • IF(조건, 공식 1, 공식 2) : 조건이 참이면 공식 1, 거짓이면 공식 2
  • IFNULL(수식1, 수식2) : 수식1이 null이 아니면 수식1, null이면 수식2
SELECT IF (100 > 200, '참', '거짓');

산술 연산자

  • +, -, *, /, %

비교 연산자

  • =, >, <, <>, !
    =
  • 참(1), 거짓(0) 값 반환

논리 연산자

  • 그리고, 또는, 아닙니다
  • 참(1), 거짓(0) 값 반환

문자열 처리

  • CONCAT: 열과 문자열 또는 문자열과 문자열을 결합합니다.

SELECT
CONCAT ('출판사: ', PUBLISHER)
FROM BOOK;

SELECT
CONCAT ('홍길동', '모험')


  • CONCAT_WS: 열과 문자열, 문자열과 문자열을 구분 기호로 결합
SELECT CONCAT_WS(':', username, phone) AS '전화' FROM customer;

SELECT GROUP_CONCAT(username, ":", phone) AS "전화" FROM customer;


  • LENGTH, CHAR_LENGTH: 문자열 길이 함수
# 문자열 BYTE 길이
SELECT
LENGTH (문자열)

# 문자의 갯수
SELECT
CHAR_LENGTH (문자열)

>> 6
>> 2

'추천 관련글,
  • TRIM, LTRIM, RTRIM: 공백 제거
-- TRIM() - 문자열 좌우 공백 제거
SELECT TRIM(' 안녕하세요 ');

-- LTRIM() – 좌측 공백 제거
SELECT LTRIM(' 안녕하세요 ');

-- RLTRIM 우측 공백 제거
SELECT RTRIM(' 안녕하세요 ');

-- 문자열 좌측 문자 제거 (LEADING)
SELECT TRIM(LEADING '안' FROM '안녕하세요안');

-- 문자열 우측 문자 제거 (TRAILING)
SELECT TRIM(TRAILING '요' FROM '요안녕하세요');

>>안녕하세요
>> 안녕하세요 여러분

  • SUBSTRING, MID, LEFT, RIGHT: 문자열 추출
  • 색인
~ 아니다 안녕 아래에 계산하기 요 그
하나 2 삼 4 5 
# 2번 인덱스부터 3개
SELECT SUBSTRING('안녕하세요', 2, 3);
# /로 구분하고 3번 인덱스까지
SELECT SUBSTRING_INDEX('안/녕/하/세/요', '/', 3);

SELECT LEFT('안녕하세요', 3);
SELECT RIGHT('안녕하세요', 3);

>> 안녕
>> 헤이/녕/하
>> 안녕하세요
>> 해

날짜 데이터 유형 처리

  • DATE_ADD, DATE_SUB : 기준일에 더하기, 빼기
SELECT DATE_ADD('2021-8-31', INTERVAL 5 DAY),
SELECT DATE_SUB('2021-8-31', INTERVAL 1 MONTH);
  • DATEDIFF: 날짜1 – 날짜2
SELECT DATEDIFF('2021-8-31', '2022-8-31')
  • DATE_FORMAT: 날짜 형식
SELECT DATE_FORMAT('2021-8-31', '%Y-%m-%d')

하위 쿼리

  • 상관 하위 쿼리: 상위 하위 쿼리의 튜플을 사용하여 하위 하위 쿼리를 계산합니다.

  • 즉, 상위 서브쿼리와 하위 서브쿼리는 독립적이지 않고 서로 연관되어 있다.

SELECT b1.BOOKNAME
FROM BOOK AS b1
WHERE b1.PRICE > (SELECT AVG(b2.PRICE)
		     FROM BOOK AS b2 
       		     WHERE b2.PUBLISHER = b1.PUBLISHER)
  • 인라인 뷰: FROM 절의 하위 쿼리는 뷰처럼 취급됩니다.

그룹화 기준

  • 계산하기
SELECT COUNT(*), COUNT(price)
FROM Orders;

COUNT(*) // NULL 포함
COUNT(PRICE) // NULL 미포함
  • HAVING : HAVING절에 Group by로 선택한 컬럼의 조건을 지정
  • 집계 함수는 HAVING 절 검색 조건에서 사용됩니다.

SELECT 
 code, 
 NAME,
 min(price)
FROM product
GROUP BY name
HAVING min(price) < (select avg(price) 
			from product);

연결하다

  • 내부 조인: 단순
  • (왼쪽, 오른쪽, 전체) OUTER JOIN
  • 교차 연결

집합 연산자

  • 집합 연산자: 두 SELECT 문 결과의 합계, 교집합 또는 차를 계산하는 연산자입니다.

  • UNION(유니온)
# 거주지가 대한민국인 고객과
# 도서를 주문한 고객 가져오기
SELECT NAME
FROM CUSTOMER
WHERE ADDRESS LIKE '대한민국%'

UNION

SELECT NAME
FROM CUSTOEMR
WHERE CUSTID IN (SELECT CUSTID 
		FROM ORDERS)
  • NOT IN(마이너스 요율)
# 거주지가 대한민국인 고객 중
# 도서를 주문한 고객 제외하기
SELECT NANME
FROM CUSTOMER
WHERE ADDRESS LIKE '대한민국%' AND
NAME NOT IN (SELECT NAME 
		FROM CUSTOEMR 
             	WHERE CUSTID IN (SELECT CUSTID 
            			FROM ORDERS))
  • IN(교차점)
# 거주지가 대한민국이면서
# 도서를 주문한 고객 가져오기
SELECT NAME
FROM CUSTOMER
WHERE ADDRESS LIKE '대한민국%' AND
NAME IN (SELECT NAME
		FROM CUSTOEMR
		WHERE CUSTID IN (SELECT CUSTID FROM ORDERS))

보다

  • 하나 이상의 테이블을 결합하여 생성된 가상 테이블입니다.

  • 기존 테이블을 변경하지 않고 새로운 데이터 구조를 사용할 수 있습니다.

  • 독립적인 인덱스를 생성하기 어렵습니다.

  • 보기 속성을 변경할 수 없음(ALTER VIEW (x))
CREATE VIEW V_ORDERS
AS SELECT 
	O.ORDERID,
	C.CUSTID,
	C.USERNAME,
	B.BOOKID,
	O.SALEPRICE,
	O.ORDERDATE
FROM CUSTOMER AS C 
JOIN ORDERS AS O 
JOIN BOOK AS B
ON C.CUSTID = O.CUSTID AND B.BOOKID = O.BOOKID

SELECT * FROM V_ORDERS;

집계 함수

  • WEEKOFYEAR, YEARWEEK, WEEK: 주 범위(0 ~ 53)를 반환합니다.

  • DAYOFFYEAR: 1년의 일수
/* 날짜 집계함수 */

-- WEEKOFYEAR
SELECT WEEKOFYEAR('2021-01-01'); -- 53 (2020년의 53주에 해당)
SELECT WEEKOFYEAR('2021-01-05');  -- 1 (2021년의 1주에 해당)
SELECT WEEKOFYEAR('2021-02-01');  -- 5 (2021년의 5주에 해당)
SELECT WEEKOFYEAR('2021-12-31');  -- 52 (2021년의 52주에 해당)

-- DAYOFFYEAR
SELECT DAYOFYEAR("2021-01-01"); -- 1
SELECT DAYOFYEAR("2021-06-15"); -- 166
SELECT DAYOFYEAR("2021-12-31"); -- 365

기본통계

기술통계

  • 개수, 최대값, 최소값, 중앙값
  • 합계, 평균
  • 랭크 대표값

고객당 총 주문, 합계, 평균, 최소/최대 구매 금액

SELECT 
   C.USERNAME 이름, 
    COUNT(*) 주문량, 
    FORMAT(SUM(SALEPRICE),0) 합계, 
    FORMAT(AVG(SALEPRICE), 1) 평균, 
    MAX(SALEPRICE) 최대, 
    MIN(SALEPRICE) 최소 
FROM ORDERS O LEFT JOIN CUSTOMER C ON O.CUSTID = C.CUSTID GROUP BY 이름;

주문 수량의 합계, 평균, 최소, 최대, 분산, 표준편차

SELECT
   SUM(SALEPRICE) 합계, 
    FORMAT(AVG(SALEPRICE), 1) 평균, 
    MAX(SALEPRICE) 최대, 
    MIN(SALEPRICE) 최소, 
    FORMAT(VARIANCE(SALEPRICE),1) 분산, 
    FORMAT(STD(SALEPRICE),1) 표준편차 FROM ORDERS;

사분위수

  • GROUP_CONCAT 함수를 이용하여 데이터를 합치고 사분위수와 같은 수를 구한 후 SUBSTRING_INDEX 함수를 이용하여 데이터를 추출하는 방식이다.

  • 병합할 데이터가 많은 경우 SET GROUP_CONCAT_MAX_LEN = 10485760; 동일한 설정이 필요합니다

가격 카운트가 있는 사분위수

-- 1단계: 컬럼 결과를 한 줄로 결합
SELECT 
    publisher, 
    GROUP_CONCAT(bookname SEPARATOR ':')
FROM book
GROUP BY publisher;


-- 2단계: 모든 가격을 결합한다.


SELECT 
   GROUP_CONCAT(saleprice ORDER BY saleprice SEPARATOR ',')
FROM Orders;


-- 3단계: 전체 레코드 수를 25%~100% 까지 계산해 본다.


SELECT 
	25/100 * COUNT(saleprice) + 1 AS '25%',
	50/100 * COUNT(saleprice) + 1 AS '50%',
	70/100 * COUNT(saleprice) + 1 AS '75%',
	MAX(saleprice) AS 'MAX'
FROM Orders;



-- 4단계: 
-- substring_index(str, delim, count)
-- 문자열 str 을 delim 로 구분해서 배열로 만든 후 count 만큼만 보여준다.


-- count 가 양수이면 왼쪽에서 count 수만큼 보여주고 음수이면 오른쪽에서 count 수 만큼 보여준다.


select substring_index('www.mysql.com', '.', 1); -- 'www'
select substring_index('www.mysql.com', '.', -1); -- 'com'


-- 5단계: FINAL
-- 합쳐지는 데이터가 많을 경우 꼭 SET GROUP_CONCAT_MAX_LEN = 10485760; 와 같은 설정이 필요함
-- SET GROUP_CONCAT_MAX_LEN = 10485760; 
SELECT 	MIN(saleprice) AS 'MIN',
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(saleprice ORDER BY saleprice SEPARATOR ','),',', 25/100 * COUNT(*) + 1), ',', -1) AS `25%`,
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(saleprice ORDER BY saleprice SEPARATOR ','),',', 50/100 * COUNT(*) + 1), ',', -1) AS `50%`,
        AVG(saleprice) AS 'MEAN', 
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(saleprice ORDER BY saleprice SEPARATOR ','),',', 75/100 * COUNT(*) + 1), ',', -1) AS `75%`, 
        MAX(saleprice) AS 'MAX'
FROM Orders;


  • 총 가격 범위는 가격의 합으로 판매 가격을 계산한 사분위수 범위입니다.

SELECT MIN(saleprice) AS 'MIN',
 FORMAT(25/100 * **SUM(saleprice)**, 1) AS '25%',
 FORMAT(50/100 * SUM(saleprice), 1) AS '50%',
 FORMAT(AVG(saleprice),1 ) AS 'MEAN', 
 FORMAT(70/100 * SUM(saleprice), 1) AS '75%',
 MAX(saleprice) AS 'MAX'
FROM Orders;

순위

  • 순위: 1,2,2,4,5
  • Denes_랭크: 1,2,2,3,4
  • 라인 번호: 1,2,3,4,5

소계

  • 롤업
SELECT
	DEPTNO,
	JOB,
	SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);