3월 30일 (1) DB - DML(추가), WHERE절의 연산자

어제 이어서

 

SELECT절 : SELECT * 

FROM절 : FROM table

WHERE절 : WHERE condition 

 

ORDER BY 활용

ASC는 생략 가능(기본값), DESC는 반드시 작성

두 개 이상의 칼럼을 활용하여 다중 정렬을 처리할 수 있다.

(각 컬럼별 오름차순, 내림차순을 정할 수 있다.)

 

 

+ 별칭에 공백을 넣고 싶을 때(두 단어 이상으로 별칭을 만들 경우)  `(백틱)`으로 감싸준다.

DESC employees;

SELECT *
FROM employees
ORDER BY first_name, last_name DESC;

-- 중복 데이터 제거
SELECT DISTINCT gender
FROM employees;

-- 별칭 만들기
SELECT last_name 성, first_name `이  름`
FROM employees;

---

WHERE절의 연산자

1. 비교연산자 

=, !=, >=, =<, >, <

==은 없음!

 

2. 논리연산자

1) AND, OR, NOT

2) BETWEEN a AND b

3) IN(list)

4)  NOT을 조합하여 반대 경우의 조건을 작성

 

AND : 단순히 두 조건을 결합하는 경우에 사용, 다른 컬럼도 상관없이 사용가능

BETWEEN a AND b: a와 b 사이의 범위 지정, 같은 컬럼을 조건의 대상으로 사용하는 경우

-- 2005 ~ 2020년 사이에 고용된 사람들 중 2010 ~ 2015년의 정보
SELECT DISTINCT hire_date
FROM employees
ORDER BY hire_date DESC;

SELECT *
FROM employees
WHERE hire_date BETWEEN '2010-01-01' AND '2015-12-31'
ORDER BY hire_date DESC;

-- 위 문장과 같은 의미
SELECT *
FROM employees
WHERE hire_date >= '2010-01-01' AND hire_date <= '2015-12-31'
ORDER BY hire_date DESC;

 

IN(list) 연산자 : OR연산자를 확장시켜서 사용하는 형태

-- 이름이 'Mark' 이거나 'Elvis' 이거나 'Ohad'인 사원의 사번과 생일을 조회하시오.
SELECT emp_no, birth_date, first_name, last_name
FROM employees
WHERE first_name = 'Mark'
OR first_name = 'Elvis'
OR first_name = 'Ohad'
ORDER BY first_name, last_name;

-- IN 연산자로 바꾸기
SELECT emp_no, birth_date, first_name, last_name
FROM employees
WHERE first_name IN ( 'Mark', 'Elvis', 'Ohad')
ORDER BY first_name, last_name;

+ 데이터가 많으면 많을수록 느리다.

 

OR,

NOT : 제외, NOT을 조합하여 반대 경우의 조건을 작성

NOT IN = AND

 

+ 일시정보에서 개별 데이터 추출

1) 연도추출 : YEAR(datetime 컬럼 또는 date 컬럼)

2) 월 추출 : MONTH(datetime 컬럼 또는 date 컬럼)

3) 일 주출 : DAY(datetime 컬럼 또는 date 컬럼)

4) 시간 추출 : HOUR(datetime 컬럼 또는 time컬럼)

5) 분 추출 : MINUTE(datetime 컬럼 또는 time컬럼)

6) 초 추출 : SECOND(datetime 컬럼 또는 time컬럼)

-- 생일이 4월, 6월, 8월인 직원의 조회하세요.
SELECT count(*)
FROM employees
WHERE MONTH(birth_date) IN (4, 6, 8);

SELECT count(*)
FROM employees
WHERE MONTH(birth_date) NOT IN (4, 6, 8);

 

3. 문자열연산자

LIKE : 대소문자 구분하지 않고 반영

모르는 부분에 대한 처리를 위해서 특수문자를 사용하여 검색

1) % : 여러 글자를 대체하여 표현(대체하는 글자의 수는 0개부터)

    LIKE 'M%' -> M으로 시작하는 모든 값(M부터 글자 개수 제한 없이 찾아준다.)

    LIKE '%M' -> M으로 끝나는 모든 값

    LIKE '%M%' -> M으로 시작하거나 M이 중간에 들어가는 M으로 끝나는 모든 값. 가장 많이 사용한다.

 

2) _  : 한 글자를 대체하는 표현, 단어의 글자개수를 지정할 수 있다.

    LIKE 'M_' -> M으로 시작하는 두 글자 단어

    LIKE '_M' -> M으로 끝나는 두 글자 단어

    LIKE 'M__' -> M으로 시작하는 세 글자 단어

    LIKE '_M___' -> 두 번째 글자가 M인 5글자의 단어

 

두 특수 문자의 조합 : LIKE '_M%' -> 두 번째 글자가 M인 모든 값, 뒤에 몇 글자가 오는지 모르지만 M이 두 번째인 값

-- LIKE
SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name = 'Mark';

-- 이름에 'a'가 들어가는 사원의 사번과 이름을 조회하시오.
SELECT emp_no, first_name
FROM employees
WHERE BINARY first_name LIKE 'a%'
ORDER BY first_name;

+ MySQL 검색(SELECT)은 대소문자 구분 없이 결과를 가져온다.

데이터의 대소문자를 구분하여 검색할 경우, 컬럼명 앞에 BINARY를 붙이고 검색한다.

문자열에서 대소문자 구문해서 사용할 경우 사용

 

컬럼 자체가 대소문자를 구분하도록 만들 경우 테이블 생성 시 

TYPE 뒤에 BINARY를 붙인다.

예) CREATE TABLE tb1_name(

      col_name VARCHER(50) BINARY NOT NULL,

      .....);

 

날짜 검색에 LIKE 활용

-- 날짜 검색에 LIEK활용
-- 4월에 태어난 사원의 사번, 이름, 생일을 조회하시오. 
SELECT emp_no, first_name, last_name, birth_date
FROM employees
WHERE birth_date LIKE '____-04%';

-- 2010년에 입사한 사원의 사번, 이름, 입사일을 조회하시오. 
SELECT emp_no, first_name, last_name, hire_date
FROM employees
WHERE hire_date LIKE '2010%';

---

+ 해당 컬럼의 날짜 더하기/빼기 UPDATE문으로 변경

더하기 date_add('값을 가져올 컬럼', INTERVAL '더할 값' KEYWORD)

KEYWORD : 연도(YEAR), 개월(MONTH). 일자(DAY)

빼기 date_sub() 사용(또는 date_add의 '더할 값'에 음수를 입력)

-- 현재 데이터에서 생일과 고용일에 40년 추가
UPDATE employees
SET birth_date = date_add(birth_date, interval 40 year),
	hire_date = date_add(hire_date, interval 40 year)
; 

-- 현재 데이터에서 생일과 고용일에 20년 빼기
UPDATE employees
SET birth_date = date_sub(birth_date, interval 20 year),
	hire_date = date_sub(hire_date, interval 20 year)
;

 

+ 결과 값 1000개 이상 출력 설정

Edit - Preferences - 설정 Limit Rows 체크박스 해제

 

+ COUNT(컬럼명) : 행의 개수를 구하는 함수

COUNST(*) : 모든 행의 개수를 구할 때

 

-- count 사용
SELECT  count(*) FROM employees;

SELECT count(*)
FROM employees
WHERE hire_date >= '2010-01-01' AND hire_date <= '2015-12-31';

-- 2010년 전에 입사한 직원의 수를 조회하시오.
SELECT count(*)
FROM employees
WHERE hire_date < '2010-01-01';

SELECT *
FROM employees
WHERE hire_date < '2010-01-01'
ORDER BY hire_date DESC;

 

연습문제

-- DB 사용하기
-- ues DB 이름
USE dev_db;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

-- 1. 사원명과 입사일을 조회하시오.
SELECT ENAME, HIREDATE
FROM emp;

-- 2. 사원번호와 이름을 조회하시오.
SELECT EMPNO, ENAME
FROM emp;

-- 3. 사원테이블에 있는 직책의 목록을 조회하시오.
SELECT JOB
FROM emp;

-- 4. 총 사원수를 구하시오.
SELECT count(*)
FROM emp;

-- 5. 부서번호가 10인 사원을 조회하시오.
SELECT EMPNO, ENAME
FROM emp
WHERE DEPTNO = 10;

-- 6. 이름이 'KING'인 사원을 조회하시오.
SELECT ENAME
FROM emp
WHERE ENAME ='KING';

-- 7. 사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름을 조회하시오.
SELECT EMPNO, ENAME
FROM emp
WHERE BINARY ENAME LIKE 'S%'; 

-- 8. 사원의 이름에 T가 포함된 사원의 사원번호와 이름을 조회하시오.
SELECT EMPNO, ENAME
FROM emp
WHERE ENAME LIKE '%T%'; 

-- 9. 커미션이 300, 500, 1400인 사원의 사번, 이름, 커미션을 조회하시오.
SELECT EMPNO, ENAME
FROM emp
WHERE COMM IN (300, 500, 1400);