연산자

• 연산자 사용 : 우선순위 () > 산술 > 관계 > 비교 > is null, like, in > between, not > and > or
• 비교 연산 : =, !=, >, <, >=, <=, <>
• 논리 연산 : and, or, not, between

SELECT FROM jikwon WHERE jikwon_jik = '대리'; -- 레코드 제한
SELECT FROM jikwon WHERE jikwon_ibsail = '2014-3-2';
SELECT FROM jikwon WHERE jikwon_ibsail = '14-3-2'; -- 위와 같음
SELECT FROM jikwon WHERE jikwon_no = 1;
SELECT FROM jikwon WHERE jikwon_no = '1'; -- ''써도 빼도 됨
SELECT FROM jikwon WHERE jikwon_no = '2' OR jikwon_no = 5;
SELECT FROM jikwon WHERE jikwon_no <= '2' OR jikwon_no >= 28;
SELECT FROM jikwon WHERE jikwon_jik = '사원' AND jikwon_gen = '남' AND jikwon_pay <= 3500;
SELECT FROM jikwon WHERE jikwon_jik = '사원' AND jikwon_gen = '여' OR jikwon_ibsail >= '2017-1-1';
SELECT FROM jikwon WHERE jikwon_jik != '대리'; -- 같지않다
SELECT * FROM jikwon WHERE jikwon_jik <> '대리'; -- 위와 같은 뜻

SELECT FROM jikwon WHERE jikwon_no >= 5 AND jikwon_no <= 10;
SELECT FROM jikwon where jikwon_no BETWEEN 5 AND 10;
SELECT FROM jikwon where jikwon_ibseil BETWEEN '2015-1-1' AND 2016-12-31;
SELECT FROM jikwon WHERE jikwon_no < 5 OR jikwon_no > 25; -- 긍정적 형태의 조건 - 속도빠름
SELECT FROM jikwon where jikwon_no not BETWEEN 5 AND 25; -- 부정적 형태의 조건
SELECT FROM jikwon where jikwon_no >= 3 + 20; -- 숫자 연산 가능
SELECT FROM jikwon where jikwon_name = '홍길동';
SELECT FROM jikwon where jikwon_name >= '박'; -- 문자도 대소비교 가능
SELECT ASCII('a'),ASCII('b'),ASCII('가'),ASCII('나') FROM DUAL;
SELECT * FROM jikwon where jikwon_name BETWEEN '이' AND '최'; -- 최씨는 안나옴 / 이씨 이후부터 최시 이전까지 나옴

• in 조건 연산

SELECT FROM jikwon WHERE jikwon_jik = '대리' OR jikwon_jik = '과장' OR jikwon_jik = '부장'; -- 너무 김
SELECT FROM jikwon WHERE jikwon_jik IN('대리','과장','부장'); -- 위와 같음
SELECT FROM jikwon WHERE jikwon_no IN(1,3,5,7);
SELECT FROM jikwon WHERE buser_num IN(10,30) ORDER BY buser_num;

• like 조건 연산 : %(0개 이상의 문자열), _(한 문자)

SELECT FROM jikwon WHERE jikwon_name LIKE '이%';
SELECT FROM jikwon WHERE jikwonname LIKE '%라';
SELECT FROM jikwon WHERE jikwon_name LIKE '%순%'; -- 순이라는 글자 포함만 되어 있으면
SELECT FROM jikwon WHERE jikwon_name LIKE '이%라'; -- 3,4,5 글자 상관없음
SELECT * FROM jikwon WHERE jikwon_name LIKE '이라'; -- 3글자로 한정
SELECT FROM gogek WHERE gogek_name LIKE '이%';
SELECT FROM gogek WHERE gogekname LIKE '이'; -- 2글자
SELECT FROM gogek WHERE gogek_name LIKE '__'; -- 2글자 다 검색
SELECT FROM gogek WHERE gogek_jumin LIKE '___1%';
SELECT FROM gogek WHERE gogek_jumin LIKE '%-2%';
SELECT FROM jikwon WHERE jikwon_pay LIKE '5%';

• null

UPDATE jikwon SET jikwon_jik = NULL WHERE jikwon_no = 5;
SELECT FROM jikwon;
SELECT FROM jikwon WHERE jikwon_jik = NULL; -- 안나옴 주의
SELECT * FROM jikwon WHERE jikwon_jik IS NULL;

• limit

SELECT FROM jikwon LIMIT 5;
SELECT FROM jikwon LIMIT 5, 3; -- 5번행 이후로 3개
SELECT * FROM jikwon WHERE jikwon_jik = '사원' LIMIT 5; -- 5명으로 제한 / oracle에 없음

• 다양한 연산자 조합

SELECT jikwon_no AS 사번, jikwon_name AS 직원명, jikwon_jik AS 직급, jikwon_pay AS 연봉,
jikwon_pay / 12 AS 보너스, jikwon_ibsail AS 입사일 FROM jikwon
WHERE jikwon_jik IN ('과장', '사원') AND ((jikwon_pay >= 4000 AND jikwon_ibsail BETWEEN '2015-1-1' AND '2019-12-31')
OR (jikwon_name LIKE '이%' AND jikwon_ibsail BETWEEN'2015-1-1' AND '2019-12-31'))
ORDER BY jikwon_jik ASC, jikwon_pay DESC LIMIT 3;

• JSON data 형태로 출력 (key,value형식)

  SELECT JSON_OBJECT('jikwon_no',jikwon_no,'jikwon_name',jikwon_name) AS 'jsondata' FROM jikwon WHERE jikwon_jik = '대리';

GUI로도 형태를 바꾸어 출력할 수 있다.