SELECTCOUNT(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 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
FROMmemberWHERESUBSTRING(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
FROMmemberwhere mem_like ='수영'ANDsubstring(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 cart_no, cart_member, (SELECT mem_name FROMmemberWHERE 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 NOTIN (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 FROMmemberWHERESUBSTRING(mem_add1, 1, 2) IN('서울','대전'))
ORDERBY 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 FROMmemberwhere 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
whereSUBSTRING(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번 : 별칭에 띄어쓰기 사용 불가로 오류 발생 - 따옴표 사용도 지양
-- 1SELECT mem_id as id, mem_name as name, mem_mileage as point
FROMmemberWHERE mem_mileage >=10;
-- 2SELECT mem_id id, mem_name name, mem_mileage point
FROMmemberWHERE mem_mileage >=10;
-- 3SELECT mem_id 'id', mem_name 'name', mem_mileage 'point'FROMmemberWHERE mem_mileage >=10;
-- 4SELECT mem_id 'mem id', mem_name 'mem name', mem_mileage 'mem point'FROMmemberWHERE mem_mileage >=10;
-- 5(오류)SELECT mem_id mem id, mem_name mem name, mem_mileage mem point
FROMmemberWHERE mem_mileage >=10;
📍테이블에 별칭 사용하기
테이블에 별칭을 사용하는 시점으로 부터 이름은 바뀜
대문자를 써서 테이블 별칭이라는 걸 나타냄
테이블이 객체(엔테티)이고 안에 있는 컬럼들은 변수 라고 생각할 수 있음 → 자바의 맵핑 기능
SELECT MEM.mem_id as id, mem_name as name, mem_mileage as point
FROMmember 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
FROMmemberWHERE mem_mileage >=10-- AND mem_name ='김은대'ORDERBY 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
FROMmemberWHERE mem_mileage >=10ORDERBY name DESC;
-- 위치SELECT mem_id as id, mem_name as name, mem_mileage as point
FROMmemberWHERE mem_mileage >=10ORDERBY3DESC;
정렬 문제
문제 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 =170000ORDERBY prod_name asc;
🐧 SQL 연산자
비교연산자: >, <, >=, <=, =, <>, !=
논리연산자: And, Or
산술연산자: +, -, *, /
SQL 연산자 문제
문제 1
- 회원 중에 76년 1월 1일 이후에 태어난 회원을 조회하기 - 단, 주민등록번호 앞자리 6자리를 이용하기 - 조회컬럼: 회원아이디, 이름, 주민번호 앞자리 6자리
문제 2 - 상품 판매가격이 15만원 또는 17만원 또는 33만원인 상품을 조회 - 조회컬럼은 상품명, 판매가격
-- 1 OR 비교연산자 사용SELECT prod_name, prod_sale
FROM prod
WHERE prod_sale =150000OR prod_sale =170000OR 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 '%여름%';
- 기본 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인 데이터를 삭제하기 - 삭제는 조건에 맞는 행이 삭제됨 - 자식 테이블에서 참조를 하고 있기 때문에 삭제 불가