[Database][SQL] 별칭 / 연산자 / 함수

 🐧 사용된 데이터 ERD

🐧 조회하기 

• 회원 테이블 전체 조회하기
  - 컬럼정보 : [mem_id, mem_pass, ....]
• 리스트 안에 딕셔너리 형태로 데이터 전달 받는다 → 하나의 딕셔너리가 하나의 행
•  [{'a001','asdfghjk',...}, {'b001','1004'...},{},{},{},{}]

SELECT * FROM member;

 

🐧 별칭 사용하기 

📍컬럼에 별칭 사용하기

• 별칭을 사용하는 이유 : 긴 단어를 줄여서 사용하는데 좋다.
• 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 '%여름%';

📍Between A and B   

• Between A and B : A이상 B이하의 범위 조건
• 날짜 타입 또는 숫자 타입에 대한 범위 연산 조건에 사용됨
• 날짜 형식(포맷 형식)
  → 0000-00-00, 0000.00.00, 0000/00/00, 00000000

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;

 

📍 TRIM / LTRIM / RTRIM 

• 공백 제거하기
• TRIM :  좌우 공백제거
• LTRIM :  왼쪽 공백제거
• RTRIM : 오른쪽 공백제거

SELECT '              왼쪽공백', LTRIM('    왼쪽공백제거'),
'오른쪽공백     ', RTRIM('오른쪽공백제거     '),
'    좌우공백    ', TRIM('   좌우공백제거   ');

 

📍 LEFT / RIGHT 

 

• 특정 자릿수까지의 문자 추출하기
• mysql or mariaDB용

SELECT LEFt(prod_id, 4), RIGHT(prod_id, 6)
FROM prod;

 

📍 SUBSTRING 

• 문자열 내 특정 위치값 추출하기
• 표준 : IN, LIKE, BETWEEN
• 회사에서 만든 것 : CONCAT, SUBSTRING

SELECT SUBSTRING('Java Program', 7, 3),
 SUBSTRING('Java Program', 1, 3);

SQL 함수 문제

문제 1

- 회원의 거주 지역이 서울이고 마일리지가 1000이상인 회원의아이디, 이름, 주소(앞), 마일리지 조회하기

SELECT * FROM member;
SELECT mem_id, mem_name, mem_add1, mem_mileage
FROM member
WHERE mem_add1 LIKE '%서울%'
	AND mem_mileage >= 1000;

---

문제 2
- 회원 중에 1975년도에 태어난 회원조회
- 조회컬럼 : 회원이름, 회원생일

SELECT mem_name, mem_bir
FROM member
WHERE mem_bir LIKE '1975%';

---

문제 3
- 회원 마일리지의 값이 10이상 100000이하이며 회원의 성씨가 김씨인 회원들 조회
- 조회 컬럼 : 회원이름, 마일리지

SELECT * FROM member;
SELECT mem_name, mem_mileage
FROM member
WHERE mem_mileage between 10 AND 100000
	AND mem_name LIKE '김%';

---

문제 4
- 회원의 출생년도가 1975년생이 아닌 회원 조회
- 조회컬럼 : 회원이름, 회원생일
- NOT IN, NOT LIKE, NOT BETWEEN

SELECT mem_name, mem_bir
FROM member

--NOT LIKE
WHERE mem_bir NOT like '1975%';

-- NOT BETWEEN 
WHERE mem_bir NOT BETWEEN'1975-01-01' AND '1975-12-31';

---

문제 5
- P101 상품분류에 대해 새로운 상품을 등록한다.
- P101000006이 현재 마지막 번호이고 마지막 번호에 1 증가시키고자 한다.
- 결과 값 : P101000007

SELECT prod_id
FROM prod;
SELECT CONCAT(LEFT('P101000006', 9),
RIGHT('P101000006', 1) + 1)

 

---

문제 6

- 상품명의 4번째 자리부터 2개의 문자가 '칼라'인 것 조회

- 조회컬럼 : 상품코드, 상품명

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 '%결혼%'