SELECT COUNT(NVL(PROD_MILEAGE, 0)) AS ALL_CNT,
COUNT(*) AS ALL_CNT2,
AVG(NVL(PROD_MILEAGE, 0)) AS ALL_AVG,
SUM(NVL(PROD_MILEAGE, 0)) AS ALL_SUM,
MAX(NVL(PROD_MILEAGE, 0)) AS MILEAGE_MAX,
MIN(NVL(PROD_MILEAGE, 0)) AS MILEAGE_MIN
FROM prod;
🍁그룹 함수
일반 조건 : WHERE 절 사용
그룹 함수에 대한 조건 : GROUP BY ~ HAVING 절 사용
GROUP BY절 : 그룹으로 묶고자 하는 컬럼명 OR 일반함수를 처리한 컬럼 지정
HAVING 절 - 그룹함수를 이용한 조건(비교 연산자)사용 - 그룹 조건이 없으면 HAVING 절은 사용 안해도 됨 - GROUP BY절만 사용 가능
그룹에 대한 문제로 인식하는 방법 : '~별로 조회' 라는 개념적 용어가 사용되면 GROUP BY를 사용
GROUP BY에 사용한 컬럼 or 일반함수는 select절에 넣어서 어떤 걸로 그룹화 해줬는지 알려주는 것이 좋다.
GROUP BY를 사용한 경우 규칙 - GROUP BY절에 제시된 컬럼들은 원형 그대로, 조회 할 컬럼에 제시 - SELECT 절 뒤에 컬럼 또는 일반함수를 이용해서 조회하고자 하는 컬럼들은 GROUP BY절에 원형 그대로 제시가 되어 있어야 오류가 안남 - 컬럼 뒤에 별칭(AS) GROUP BY절에는 사용하면 오류남 - 집계함수는 group by 절에 제시하지 않는다
SELECT mem_like, COUNT(NVL(mem_like, 0)) AS cnt_like
FROM member
-- 그룹화할 컬럼 지정
GROUP BY mem_like;
그룹 함수 문제
문제 1
회원의 성별 count 값을 조회하기
조회 컬럼 : 회원 성별, count 값
SELECT if(SUBSTRING(mem_regno2,1,1)=1,'남','여') AS mf, COUNT(mem_regno2) AS cnt
FROM member
GROUP BY SUBSTRING(mem_regno2,1,1);
문제 2
cart 테이블을 이용해서 회원별로 count 하기
SELECT cart_member, COUNT(*)AS cnt, MAX(cart_qty) AS MAX_qty, SUM(cart_qty) AS sum_qty
FROM cart
GROUP BY cart_member;
SELECT REPLACE('컬럼1에 찾을값이 있다', '찾을값', '바꿀값') AS msg;
REPLACE 문제
문제1
회원의 성씨 중에 이씨를 리씨로 바꾸기
SELECT mem_name,
REPLACE(mem_name , '이', '리') as rep1,
REPLACE(SUBSTRING(mem_name,1,1),'이','리') AS rep2,
SUBSTRING(mem_name,2,2) AS nm,
CONCAT(REPLACE(SUBSTRING(mem_name,1,1),'이','리'),SUBSTRING(mem_name,2,2)) AS rep_name
FROM member
WHERE SUBSTRING(mem_name,1,1) = '이';
🍁 ROUND
Round() : 반올림 함수
SELECT ROUND(123.567, 0) AS r1,
ROUND(123.567, 1) AS r2,
ROUND(123.567, 2) AS r3,
ROUND(123.567, 3) AS r4,
ROUND(123.567, 4) AS r5,
ROUND(123.567, -1) AS r6,
ROUND(123.567, -2) AS r7,
ROUND(123.567, -3) AS r8,
ROUND(123.567, -4) AS r9;
ROUND 문제
문제 1
상품정보에서 상품명, 원가율 조회하기
원가율 = 매입가/판매가의 백분율 값
원가율을 소숫점 2자리까지 표현
SELECT prod_name, ROUND((prod_cost/prod_sale)*100, 2) AS 원가율
FROM prod;
문제 2
회원 중에 취미가 수영인 회원이 구매한 상품들을 조회하기
회원 거주 지역이 서울, 대전, 광주인 회원
상품분류중에 피혁이라는 분류에 속해있는 상품을 구매한 회원
조회컬럼 : 상품명, 상품분류명, 원가(소숫점 2자리)
SELECT prod_name,
(SELECT lprod_nm
FROM lprod
WHERE lprod_gu = prod_lgu) AS lprod_nm,
ROUND((prod_cost/prod_sale)*100, 2) AS 원가율
FROM prod
WHERE prod_id IN(
SELECT cart_prod
FROM cart
WHERE cart_member IN(
SELECT mem_id
FROM member
where mem_like = '수영'
AND substring(mem_add1,1,2) IN ('서울', '대전', '광주')))
And prod_lgu IN(select lprod_gu
from lprod
WHERE lprod_nm LIKE '%피혁%');
🍁 Case 조건문
조회 컬럼 대신 [조건문]으로 조회하기
작성 방법 1
Case 기준값 When 비교값 Then 처리할 값 Else 처리할 값(없어도 됨) End
SELECT Case 1
When 1 Then '1입니다'
When 0 Then '0입니다'
ELSE '음수'
END AS case_data;
작성 방법 2
Case When 비교연산자 Then 처리할 값 When 비교연산자 Then 처리할 값 Else 처리할 값 End
SELECT Case
When 1<0 Then '1입니다'
When 1>0 Then '0입니다'
ELSE '음수'
END AS case_data;
if 조건문 : mysql, mariadb에만 있음 - true, false 결과 값을 무조건 넣어야 함
SELECT mem_id, mem_name,
case SUBSTRING(mem_regno2,1,1)
when 1 then '남'
when 3 then '남'
ELSE '여'
END AS gender
FROM member;
문제 2
회원 이름, 회원성별, 마일리지, 고객구분
고객구분은 마일리지의 값이 100이상이면 '우수고객' 100미만이면 '일반고객'
📍 코드 1 - Case 조건문 사용
SELECT mem_name,
case SUBSTRING(mem_regno2,1,1)
when 1 then '남'
when 3 then '남'
ELSE '여'
END AS gender,
mem_mileage,
case
when mem_mileage >=100 then '우수고객'
when mem_mileage < 100 then '일반고객'
END AS grade
FROM member;
📍 코드 2 - If 조건문 사용
SELECT mem_name,
if(MOD(SUBSTRING(mem_regno2, 1, 1), 2)=1, '남자', '여자') AS MF,
mem_mileage,
if(mem_mileage >= 5000, '우수고객', '일반고객') AS mem_best
FROM member;
mod() : 나머지의 값을 가져오는 함수 작성 방법 : SELECT MOD(10, 2) AS mod_test
문제 3
회원이 구매한 상품명, 판매가격을 조회
여성인 회원들이 구매한 상품이고, 우수고객이 구매한 상품에 대해서만 조회
우수고객은 마일리지값이 5000점 이상인 회원
SELECT prod_name, prod_sale
FROM prod
WHERE prod_id IN(
SELECT cart_prod
FROM cart
WHERE cart_member in(
select mem_id FROM member where SUBSTRING(mem_regno2,1,1) = 2 AND mem_mileage >= 5000));
🍁 NULL
NULL, NAN, NONE
NULL 체크 → NVL(컬럼명, NULL인 경우 대체할 값)
NULL인 경우 : 메모리가 존재하지 않는 값
' '인 경우 : 메모리는 존재하지만 값은 없는 것(비어있음), white space
SELECT NVL(NULL, 'NULL이다'),
NVL(1, 'NULL이다');
NULL값 찾기 : IS NULL (WHERE절 내에서만 사용가능)
조건에서 NULL을 비교할 때는 비교연산자를 쓸 수 없고 ISNULL이나 IS NOT NULL을 사용해야 한다.
SELECT PROD_NAME, NVL(PROD_MILEAGE,0) AS PROD_MILEAGE
FROM prod
WHERE prod_mileage IS NULL;
IFNULL vs NULLIF - IFNULL(A, B) : A가 NULL이면 B를 리턴하고, NULL이 아니면 A 리턴한다. - NULLIF(A, B) : A = B가 True이면 NULL을 리턴하고, 그렇지 않으면 A을 리턴한다.
🍁 날짜 함수
NOW() : 데이터 INSERT시에 주로 사용
CURDATE() : 데이터 INSERT시에 년월일만 넣고자 할 때 주로 사용
시간은 자주 사용되지는 않음
SELECT NOW() AS '오늘날짜', CURDATE() AS '오늘 년월일', CURTIME() AS '오늘 시분초';
날짜 포맷 이용하기 - DATE_FORMAT(컬럼명, 날짜형식)
SELECT DATE_FORMAT('20231122144059', '%Y-%M-%D %H:%I:%S') AS dt1,
DATE_FORMAT(NOW(), '%Y-%M-%D %H:%I:%S') AS dt2;
날짜 함수 문제
문제 1
회원아이디, 회원이름, 회원생일 조회
생일은 '년-월-일' 형태로 조회
조회 되는 날짜의 가장 최솟값은 1970년 1월 1일이다. 그렇기 때문에 성윤미의 경우 mem_regno1에 460409로 데이터 값이 저장되어 있지만 %Y 형태로 연도를 가져오면 1946년으로 조회되지 못하고 2046년으로 나타나는 것이다.
SELECT mem_id, mem_name,
DATE_FORMAT(mem_bir,'%Y년-%m월-%d일') AS birthday,
DATE_FORMAT(mem_regno1,'%Y-%m-%d') AS mem_regno1
FROM member;
🍁 형(타입) 변환 함수
Cast() or Convert()
형 변환을 주로 사용하는 경우 : 문자타입의 날짜값을 날짜타입으로 바꾸고자 할 때 주로 사용된다.
SELECT CAST('20231122' AS DATE), CONVERT('20231122', DATE);
예시 - 아이디 a001인 회원이 가지고 있는 마일리지보다 큰(이상) 회원들 조회 - 조회 컬럼 : 회원아이디, 회원 마일리지
SELECT mem_id, mem_mileage
FROM member
WHERE mem_mileage >= (SELECT mem_mileage FROM member WHERE mem_id = 'a001');
📍 단일 컬럼의 단일 행 2
SELECT 뒤에 조회할 컬럼명 대신해서 서브쿼리 특징
하나의 컬럼에 하나의 값만 조회가능
단일 컬럼에 단일 행
예시 - 주문번호, 주문자 아이디, 주문자이름, 주문상품코드, 주문수량 조회
SELECT cart_no, cart_member, (SELECT mem_name FROM member WHERE mem_id = cart_member) AS mem_name, cart_prod, cart_qty
FROM cart;
📍 단일 컬럼에 다중 행
IN 함수를 이용해서 서브쿼리를 사용할 경우 규칙
하나의 컬럼을 여러개의 값을 조회 할 수 있음
단일 컬럼에 다중 행이라고 칭함
예시 - 한번도 주문한적이 없는 회원을 조회하기 - 조회 컬럼 : 회원아이디, 회원이름
SubQuery 문제
문제 1
- 상품 정보에 존재하지 않는 상품분류정보를 조회 - 조회컬럼 : 상품분류코드, 상품분류명
SELECT lprod_gu, lprod_nm
FROM lprod
WHERE lprod_gu NOT IN (SELECT prod_lgu FROM prod);
문제 2
- 주문번호, 주문상품코드, 주문 수량, 주문상품명, 상품분류명 조회하기
- 단, 회원의 거주지역이 서울 또는 대전인 경우 - 정렬은 주문번호를 기준으로 오름차순, 주문수량을 기준으로 내림차순
select cart_no, cart_prod, cart_qty,
(SELECT prod_name
FROM prod
WHERE prod_id = cart_prod) AS prod_name,
(SELECT lprod_nm
FROM lprod
WHERE lprod_gu = SUBSTRING(cart_prod, 1, 4)) AS lprod_nm
FROM cart
WHERE cart_member IN (SELECT mem_id FROM member WHERE SUBSTRING(mem_add1, 1, 2) IN('서울','대전'))
ORDER BY cart_no, cart_qty desc;
문제 3
- 조회 컬럼 : 상품코드, 상품명, 상품판매가격, 거래처명 - 조건 : 회원의 마일리지 값 100이상, 주문수량 5이상, 상품분류명에 컴퓨터 포함 - 거래처 주소지 지역이 서울 또는 대전 또는 광주 인 경우
SELECT prod_id, prod_name, prod_sale, (SELECT buyer_name FROM buyer WHERE prod_buyer = buyer_id) AS buyer_name
FROM prod
WHERE prod_id IN (SELECT cart_prod FROM cart
WHERE cart_member in (SELECT mem_id FROM member where mem_mileage >= 100))
AND prod_id IN (SELECT cart_prod from cart
where cart_qty >=5)
AND prod_lgu IN (SELECT lprod_gu FROM lprod
where lprod_nm LIKE '%컴퓨터%')
AND prod_buyer IN (
SELECT buyer_id from buyer
where SUBSTRING(buyer_add1,1,2) IN('서울','대전','광주'));
SQL에서 별칭을 사용할 수 있는 곳 - 컬럼명 대신 별칭 사용 : 예시) mem_id as id - 테이블명 대신 별칭 사용 : 예시) From member ME
예시 - 별칭 사용하여 회원의 마일리지 값이 10 이상인 데이터만 조회하기 - 조회컬럼은 회원아이디, 회원이름, 마일리지 - 별칭 : id, name, point - 사용할 테이블명 : member - 사용할 컬럼명 : mem_mileage, mem_id, mem_name - 조건 : mem_mileage >= 10 - 5번 : 별칭에 띄어쓰기 사용 불가로 오류 발생 - 따옴표 사용도 지양
-- 1
SELECT mem_id as id, mem_name as name, mem_mileage as point
FROM member
WHERE mem_mileage >=10;
-- 2
SELECT mem_id id, mem_name name, mem_mileage point
FROM member
WHERE mem_mileage >=10;
-- 3
SELECT mem_id 'id', mem_name 'name', mem_mileage 'point'
FROM member
WHERE mem_mileage >=10;
-- 4
SELECT mem_id 'mem id', mem_name 'mem name', mem_mileage 'mem point'
FROM member
WHERE mem_mileage >=10;
-- 5(오류)
SELECT mem_id mem id, mem_name mem name, mem_mileage mem point
FROM member
WHERE mem_mileage >=10;
📍테이블에 별칭 사용하기
테이블에 별칭을 사용하는 시점으로 부터 이름은 바뀜
대문자를 써서 테이블 별칭이라는 걸 나타냄
테이블이 객체(엔테티)이고 안에 있는 컬럼들은 변수 라고 생각할 수 있음 → 자바의 맵핑 기능
SELECT MEM.mem_id as id, mem_name as name, mem_mileage as point
FROM member MEM
WHERE mem_mileage >=10
🐧 ORDER BY 정렬
예시 - 회원의 이름이 김은대인 회원을 조회하기 - 조회컬럼 : 회원아이디, 회원이름, 마일리지 - 정렬 : 회원아이디를 기준으로 오름차순 - Order By 컬럼명 ASC (오름차순) - Order By 컬럼명 DESC (내림차순)
SELECT mem_id as id, mem_name as name, mem_mileage as point
FROM member
WHERE mem_mileage >=10
-- AND mem_name ='김은대'
ORDER BY mem_mileage DESC, mem_id asc;
명령문 처리순서 1. select 2. from 3. 테이블 메모리 올리기 4. where 조건 5. And 조건 또는 OR 조건 6. 조회할 컬럼 7. 정렬
정렬에 사용할 수 있는 이름 - 컬럼명, 별칭, 위치 ※ 조건절에는 별칭을 쓸 수 없다. 컴파일 순서에 따르면 별칭은 아직 메모리 상에 올라와 있지 않기 때문이다.
-- 별칭
SELECT mem_id as id, mem_name as name, mem_mileage as point
FROM member
WHERE mem_mileage >=10
ORDER BY name DESC;
-- 위치
SELECT mem_id as id, mem_name as name, mem_mileage as point
FROM member
WHERE mem_mileage >=10
ORDER BY 3 DESC;
정렬 문제
문제 1
- 상품분류코드가 P201이고 상품판매가격이 17만원인 상품코드, 상품명, 상품분류코드, 상품판매가격 조회 - 정렬은 상품명을 기준으로 오름차순 - 사용할 테이블 : prod - 사용할 컬럼 : prod_id, prod_name, prod_lgu, prod_sale - 일반 조건 : prod_lgu='P201' AND prod_sale = 170000 - 정렬 조건 : prod_name asc
SELECT * FROM prod;
SELECT prod_id, prod_name, prod_lgu, prod_sale
FROM prod
WHERE prod_lgu = 'P201'
AND prod_sale = 170000
ORDER BY prod_name asc;
🐧 SQL 연산자
비교연산자: >, <, >=, <=, =, <>, !=
논리연산자: And, Or
산술연산자: +, -, *, /
SQL 연산자 문제
문제 1
- 회원 중에 76년 1월 1일 이후에 태어난 회원을 조회하기 - 단, 주민등록번호 앞자리 6자리를 이용하기 - 조회컬럼: 회원아이디, 이름, 주민번호 앞자리 6자리
SELECT mem_id, mem_name, mem_regno1
FROM member
WHERE mem_regno1 >= 760101;
문제 2 - 상품 판매가격이 15만원 또는 17만원 또는 33만원인 상품을 조회 - 조회컬럼은 상품명, 판매가격
-- 1 OR 비교연산자 사용
SELECT prod_name, prod_sale
FROM prod
WHERE prod_sale = 150000
OR prod_sale = 170000
OR prod_sale = 330000;
-- 2 IN 사용
SELECT prod_name, prod_sale
FROM prod
WHERE prod_sale IN (150000, 170000, 330000);
🐧 SQL 함수
📍LIKE
LIKE : 문자내 특정 단어 포함 여부 검색
상품명에 삼성이라는 단어가 있는 데이터 조회하기 - 조회컬럼 : 상품명, 상품코드
SELECT prod_name, prod_id
FROM prod
WHERE prod_name LIKE '%삼성%';
상품명 중에 첫글자가 '삼'으로 시작하는 모든 것 찾기 → WHERE prod_name LIKE '삼%';
상품명 중에 두 번째 단어가 '성'으로 시작하는 모든 것 찾기 → WHERE prod_name LIKE '_성%';
상품명 중에 마지막 단어가 '치'로 끝나는 모든 것 찾기 → WHERE prod_name LIKE '%치';
상품명 중에 '여름'이라는 단어가 포함되어 있으면 찾기 → WHERE prod_name LIKE '%여름%';
SELECT mem_name, mem_bir
FROM member
-- 0000-00-00
WHERE mem_bir Between '1975-01-01' AND '1975-12-31';
-- 00000000
WHERE mem_bir Between '19750101' AND '19751231';
-- 0000.00.00
WHERE mem_bir Between '1975.01.01' AND '1975.12.31';
-- 0000/00/00
WHERE mem_bir Between '1975/01/01' AND '1975/12/31';
📍CONCAT
컬럼 데이터 합치기(병합) : CONCAT(값1, 값2, 값3, ...) 함수사용
회원이름, 회원주민번호(앞-뒤), 주소(앞-뒤)
SELECT mem_name,
CONCAT(mem_regno1, '-', mem_regno2) AS mem_regno,
CONCAT(mem_add1, ' ', mem_add2) AS mem_add
FROM member;
📍LOWER / UPPER
대소문자 변환
SELECT LOWER(mem_id) AS '소문자로', UPPER(mem_id) AS '대문자로'
FROM member;
SELECT prod_id, prod_name
FROM prod
WHERE substring(prod_name, 4, 2) = '칼라';
문제 7
- 회원의 성씨가 김씨 - 거주지역이 서울 또는 대전 - 기념일에 결혼이 포함되어있는 회원 정보 조회하기 - 조회컬럼 : 회원이름, 지역(지역만), 기념일명
-- 1
SELECT mem_name, left(mem_add1, 2), mem_memorial
FROM member
WHERE LEFT(mem_name,1) = '김'
AND left(mem_add1, 2) IN ('서울', '대전')
AND mem_memorial = '결혼기념일';
-- 2
SELECT mem_name, left(mem_add1, 2), mem_memorial
FROM member
WHERE mem_name like '김%'
AND substring(mem_add1, 1, 2) IN ('서울', '대전')
AND mem_memorial LIKE '%결혼%'
- 기본 IP : 127.0.0.1 (프라이빗 ip로 자신의 노트북에서만 접속할 수 있음)
- 기본 Host : localhost
- 데이터 베이스를 관리하는 영역 mysql
🔍DB 사용 방법
📍절대권한(root)으로 접속해서 생성해야 함
📍사용자 생성 : 계정 및 패스워드 생성
📍 Database 생성 : 사용자가 사용할 공간 만들기
(Oracle은 제외한다. Oracle은 사용자 생성이 곧 DB 생성과 같기 때문이다.)
create database 후 root 새로고침
📍 권한 부여 : 사용자가 접속하고,DB를 사용할 수 있는 권한 부여
📍 사용자, DB, 권한부여 후 할일
사용자로 접속
Table 설계(DB 설계) → 테이블을 어떻게 만들지 모델링 하는 것 → 데이터를 저장할 수 있는 공간 : 테이블 → 테이블 내에 데이터를 저장하는 원소값 : 컬럼(열) → 테이블 하나를 만들기 위해서는 컬럼(열)이 하나 이상 존재해야 한다. → 컬럼은 타입(또는 길이와 함께)으로 정의된다. → CHAR(10)는 고정형 데이터 타입 → VARCHAR(10)는 가변형 데이터 타입 → 성격에 맞는 데이터끼리 테이블로 모아야 함
Table을 이용해서 입력, 수정, 삭제, 조회 → (CRUD)를 수정함
📌관계형 데이터 베이스
📍 1 : m 형태
📍 1개인 쪽이 부모 테이블, m개인 쪽이 자식 테이블
📍 부모 쪽 테이블에는 PK(기본키)가 존재하야 하고 자식 테이블에는 PK를 참조하는 FK(외래키)가 존재해야 함
📍 PK - 고유한 값, 중복 불가
좌 logical / 우 physical
✏️데이터 베이스 입력
📍 규칙 타입 이름
📍 제약조건 : constraint 제약조건 이름 primary key (컬럼명)
📍commit / rollback
데이터에 대한 입력/수정/삭제 시에는 commit 수행
만약 입력/수정/삭제를 되돌리려면 rollback 수행
commit 이후에는 rollback이 안된다
mysql or mariadb는 자동 commi된다
oracle은 항상 commit 해야 한다
📍 데이터 입력 / 수정 / 삭제 / 조회
데이터 입력
Insert Into 테이블명 (컬럼명1, ...컬럼명N) Values (값1, ...값N)
데이터 수정
Update 테이블명 Set 수정할컬럼명1 = 수정할값, 수정할컬럼명2 = 수정할값 Where 수정 조건 제시 (필수)
데이터 삭제
Delete From 테이블명 Where 삭제 조건 제시 (필수)
데이터 조회
Select 조회할컬럼1, 조회할컬럼2, ... From 테이블명
📍 데이터 조회 / 수정/ 삭제 해보기
회원정보 전체 조회하기 - 회원정보 테이블 : member - 모든 컬럼의 데이터를 조회하라는 의미
SELECT * FROM member;
조건 걸어서 조회하기 - 회원중에 아이디가 a001인 회원의 이름 조회하기 - 컴파일(해석) 순서 : select > from member > where 조건들 > 조회할 컬럼
SELECT mem_id, mem_name FROM member WHERE mem_id = 'a001';
조건 걸어서 수정하기 - 상품분류코드가 P201에 대해서 상품분류명을 "향수"로 수정하기
UPDATE lprod SET lprod_nm = '향수' WHERE lprod_gu = 'P201';
조건 걸어서 삭제하기 - 상품분류코드가 P201인 데이터를 삭제하기 - 삭제는 조건에 맞는 행이 삭제됨 - 자식 테이블에서 참조를 하고 있기 때문에 삭제 불가
