220513 sql 기타연산자(IN,LIKE,BETWEEN), 함수

▶DATE 자료형
1975-01-01 : 날짜형 문자 -> 날짜형문자가 연산시 날짜형으로 자동형변환
750101은 1975일지 2075일지 모르니 명확하게 써주는 것이 좋다!

 

 

▶기타연산자

▷IN 연산자
질의 탐색을 위해 사용될 둘 이상의 표현식을 지정
(NOT 연산자와 함께 사용가능)

▷BETWEEN 
범위내의 모든 값을 탐색
두 범위의 한계값을 포함

▷LIKE연산자
컬럼값을 지정된 패턴과 비교하여 문자형태가 같은 Row를 검색
- LIKE와 함께 쓰이는 %,_ : 와일드카드=>문자의 형태지정
- % : 여러글자, _ : 한 글자
- 삼% : 삼으로 시작하고 뒤에 여러글자가 나옴
- '%'나 '_'를 검색하기 위해서는 ESCAPE사용
     =>   \% ESCAPE\

---

▶함수

미리 만들어 놓은 작은 프로그램으로

혼자서 실행되지 않고 다른 함수에 의해서 호출을 받아야만 실행되는 프로그램
- 컬럼의 값이나 데이터 타입을 변경할 경우
- 숫자 또는 날짜 데이터의 출력형식을 변경할 경우
- 하나 이상의 행에 대한 집계를 수행하는 경우 사용가능

▷문자함수
결합연산자
- || :둘 이상의 문자열을 연결하는 결합연산자
- CONCAT함수 : 두 문자열을 연결하여 반환//딱 두개만!! || 과 차이는 연결해주는 갯수

글자변환
- LOWER : 소문자로 반환
- UPPER : 대문자로 반환
- INITCAP : 첫글자만 대문자로 반환

------------------------------------0513수업
SELECT PROD_ID  AS 상품코드
    , PROD_NAME AS 상품명
    , PROD_SALE  AS 판매가 
    FROM PROD
WHERE (PROD_SALE >= 300000 AND PROD_SALE <=500000)
ORDER BY PROD_SALE;

SELECT MEM_ID 회원ID, MEM_NAME 회원명, MEM_JOB 직업, MEM_MILEAGE 마일리지
FROM MEMBER
WHERE (MEM_JOB ='공무원' AND MEM_MILEAGE >= 1500);

--**IN 연산자
--상품 중 판매가 150000,170000,330000상품조회 

SELECT PROD_NAME 상품명
    ,PROD_SALE 판매가
FROM PROD
WHERE PROD_SALE =150000 OR PROD_SALE =170000 OR PROD_SALE =330000
ORDER BY PROD_SALE;

-- 위 식을 좀더 간단히 IN연산식으로 바꿈

SELECT PROD_NAME 상품명
    ,PROD_SALE 판매가
FROM PROD
WHERE PROD_SALE IN (150000,170000,330000);
-------------------------------------------------실습
SELECT MEM_ID 회원ID, MEM_NAME 회원명
FROM MEMBER
WHERE MEM_ID IN ('c001', 'f001', 'w001');


--상품분류테이블에서 현재 상품테이블에 존재하는 분류만검색
SELECT LPROD_GU
    ,LPROD_NM
FROM LPROD
WHERE LPROD_GU IN (SELECT DISTINCT PROD_LGU FROM PROD);

--문제 거래처테이블에서 현재 상품테이블에 존재하는 거래처만 검색
SELECT BUYER_ID 거래처, BUYER_NAME 거래처명
FROM BUYER
WHERE bUYER_ID IN(SELECT DISTINCT PROD_BUYER FROM PROD);--서브쿼리문
-- WHERE 20 IN(10,20,30)                        

--거래처테이블에서 현재 상품테이블에 존재하지 않는 거래처만 검색
SELECT BUYER_ID 거래처, BUYER_NAME 거래처명
FROM BUYER
WHERE bUYER_ID NOT IN(SELECT DISTINCT PROD_BUYER FROM PROD);

------------상품의 판매가가 100000~300000원 사이의 상품 조회
SELECT PROD_NAME 상품명
    , PROD_SALE 판매가
FROM PROD
WHERE PROD_SALE BETWEEN 100000 AND 3000000;
--==PROD_SALE>=100000 AND PROD_SALE<=300000
--DATE자료형
--'1975-01-01' : 날짜형문자-> 날짜형문자가 연산시 날짜형으로 자동형변환
--회원 중 생일이 19750101 761231사이에 태어난 회원 검색

SELECT MEM_ID 회원아이디
    ,MEM_NAME 회원명
    ,MEM_BIR 생일
 FROM MEMBER
WHERE MEM_BIR BETWEEN '750101' AND '761231'; --이 아이가 1975가 될지 1875가 될지 몰라서 '1975-01-01' 식으로 명확하게 작성해줘야함

--상품중 매입가 300,000~1,500,000/판매가 800,000~2,000,000인상품

SELECT PROD_COST 매입가 
    ,  PROD_SALE 판매가
    ,  PROD_NAME 상품명
FROM PROD
WHERE PROD_COST BETWEEN 300000 AND 15000000 
AND PROD_SALE BETWEEN 800000 AND 2000000;

----회원중 75년생이 아닌사람
SELECT MEM_ID 회원아이디
    ,MEM_NAME 회원명
    ,MEM_BIR 생일
 FROM MEMBER
WHERE MEM_BIR NOT BETWEEN'1975-01-01' AND '1975-12-31';--= WHERE NOT(MEM_BIR BETWEEN'1975-01-01' AND '1975-12-31')

--LIKE연산자
--LIKE와 함께 쓰이는 %,_ : 와일드카드
--% : 여러글자, _ : 한 글자
--삼% :삼으러 시작하고 뒤에 여러글자가 나옴
SELECT PROD_ID 상품코드
    , PROD_NAME 상품명
FROM PROD
WHERE PROD_NAME LIKE '삼%';

SELECT PROD_ID 상품코드
    , PROD_NAME 상품명
FROM PROD
WHERE PROD_NAME LIKE '_성%';

SELECT PROD_ID 상품코드
    , PROD_NAME 상품명
FROM PROD
WHERE PROD_NAME LIKE '%치';

SELECT PROD_ID 상품코드
    , PROD_NAME 상품명
FROM PROD
WHERE PROD_NAME NOT LIKE '%치';

SELECT PROD_ID 상품코드
    , PROD_NAME 상품명
FROM PROD
WHERE PROD_NAME LIKE '%여름%';

--회원테이블에서 김씨성을 가진회원
SELECT MEM_ID 회원ID
    ,  MEM_NAME 성명
FROM MEMBER 
WHERE MEM_NAME LIKE '김%';

--주민번호 앞자리 75제외검색
--||'-'||결합연산자
SELECT MEM_ID 회원ID
    ,  MEM_NAME 성명
    ,  MEM_REGNO1||'-'||MEM_REGNO2 주민등록번호 
FROM MEMBER 
WHERE MEM_REGNO1 NOT LIKE '75%'
ORDER BY MEM_REGNO1;

--개똥이는 삼성에서 100만원미만 가격 내림차순 정렬리스트

SELECT PROD_ID 상품ID
    ,  PROD_NAME 상품명
    ,  PROD_SALE 판매가
    ,  PROD_DETAIL 제품설명글
FROM PROD
WHERE PROD_NAME LIKE '%삼성%' AND PROD_SALE<1000000
ORDER BY PROD_SALE DESC;

--P193 
--|| :둘 이상의 문자열을 연결하는 결합연산자
SELECT 'a' || 'bcde' FROM DUAL;

--CONCAT함수 : 두 문자열을 연결하여 반환//딱 두개만!! ||과 차이는 연결해주는 갯수
SELECT CONCAT ('My Name is', MEM_NAME)FROM MEMBER;

--CHR : ASCII -> 문자/ ASCII : 문자를 ASCII//알파넷에서 쓰던것이 상용화
SELECT CHR (65) "CHR", ASCII('ABC')"ASCII" FROM DUAL;--뒤에있는 BC는 의미가 없음
SELECT ASCII (CHR (65))RESULT FROM DUAL;--SYS계정을 가지고있는, 제공해주는 DUAL 가상의 테이블
SELECT CHR(75)"CHR", ASCII('K') "ASCII" FROM DUAL;
--------------------실습

--회원테이블 COLUME
SELECT ASCII(MEM_ID) AS 회원ASCII
    ,CHR(ASCII(MEM_ID)) AS 회원CHR
FROM MEMBER;


----------194
--LOWER : 소문자로 반환
--UPPER : 대문자로 반환
--INITCAP : 첫글자만 대문자로 반환
SELECT LOWER('DATA manipulation Language') as "LOWER"
, UPPER('DATA manipulation Language') as "UPPER"
 ,INITCAP ('DATA manipulation Language') as "INITCAP"
from dual;

--회원테이블의 회원ID를 대문자로 변환하여 검색
SELECT MEM_ID AS 변환전ID
      ,UPPER(MEM_ID) AS변환후ID
FROM MEMBER;