MySQL 전공 정리-(1)

 

SQL 기본 명령 1

사용자의 모든 테이블 보기 SELECT * FROM tab;

-scott 사용자 대표 테이블 : DEPT, EMP, SALGRADE, BONUS

 

테이블 구조 보기 DESC 테이블명

 

NULL 데이터

• 0(zero)도 아니고, 빈 공백도 아니

• 미확정(해당 사항 없음), 알 수 없는(unknown) 값을 의미

• 연산, 할당, 비교가 불가능 (연산을 할 경우 결과는 null값)

 

별칭(alias)

-쿼리문의 결과가 출력될 때, 컬럼의 이름이 지정한 컬럼명의 대문자로 출력됨

 (연산 후의 결과 컬럼명이 보기에 좋지는 않음)

 ① 컬럼명 as 별칭

 ② 컬럼명 별칭

 ③ 컬럼명 “별칭” //별칭에 공백이나 특수문자, 대소문자 구별을 사용

 

연산식 별칭 처리 시

SELECT empno “사 번”, ename “사 원 명” , sal*12 “연봉” FROM emp;

 

|| 연결 연산자(concatenate)

• 기존 컬럼값에 문자열을 추가하여 연결하기 위한 연산자

 

중복되는 컬럼을 한 번씩만 보여주기 위한 DISTINCT 키워드

• 중복된 값은 제외하고 한 개 값만 출력

SELECT DISTINCT deptno FROM emp;

SQL 기본 명령 2 - (1)

where 조건절 : 조건을 이용하여 특정 레코드를 조회하기 위한 절

• 실무에서 테이블의 모든 테이터를 검색하는 일은 거의 없음.

• 많은 데이터 중에서 필요한 데이터만 검색하는 경우가 대부분임.

 

비교연산자

-where 절에는 하나 또는 그 이상의 조건을 설정할 수 있음

-조건에 사용하는 비교연산자

=(같다), >(크다), <(작다), >=크거나 같다, <=작거나 같다, <>(같지않다)

 

특정 데이터 추출 : 숫자 비교

-부서번호가 10번인 사원만 출력 SLECT *FROM emp WHERE deptno = 10;

 

-급여가 2000이상인 사원의 사번, 이름, 급여를 출력

 SELECT empno, ename, sal FROM emp WHERE sal >= 2000;

특정 데이터 추출 : 문자열 비교

-문자열은 단일 따옴표 안에 표시함 : ‘문자열’

 

-FORD 사원의 사번, 이름, 급여 출력

 SELECT empno, ename, sal FROM emp WHERE ename = ‘FORD’;

 SELECT empno, ename, sal FROM emp WHERE ename = ‘Ford’; 

 

፠비교값은 대소문자를 구별한다는 것에 주의!

 

특정 데이터 추출 : 날짜 데이터 비교

-날짜 데이터는 단일 따옴표 안에 표시함 : ‘yyyy/mm/dd’

 

-1982년 1월 1일 이후에 입사한 사원의 이름, 입사일자 출력

SELECT ename, hiredate FROM emp WHERE hiredate >= ‘1982/01/01’;

 

LIKE 연산자

-WHERE ename = ‘홍길동’

 : 정확히 사원이름이 홍길동인 사원만 검색

 

(Q) 성이 홍씨인 사원만 출력

• WHERE ename = ‘홍’

  : 정확히 사원이름이 홍인 사원을 검색

 

-와일드 문자

% : 0개 이상의 문자열 대체

밑줄문자(_) : 단 1개 문자 대체

 

예시) 사원이름이 J로 시작 : ‘J%’

         사원이름에 A를 포함 : ‘%A%’

         사원이름 두 번째 문제가 A : ‘_A%’

 

-와일드 문자 비교 : LIKE 연산자

컬럼명 LIKE 와일드문자열(pattern)

 

-이름이 J로 시작하는 사원의 사번, 이름 출력

SELECT empno, ename, FROM emp WHERE ename LIKE ‘J%’;

 

-이름에 A를 포함하는 사원의 사번, 이름 출력

SELECT empno, ename FROM emp WHERE ename LIKE ‘%A%’;

 

-이름이 N으로 끝나는 사원의 사번, 이름 출력

SELECT empno, ename FROM emp WHERE ename LIKE ‘%N’;

 

-이름의 두 번째 문자가 A인 사원의 사번, 이름 출력

SELECT empno, ename FROM emp WHERE ename LIKE ‘_A%’;

SQL 기본 명령 2 - (2), (3)

 

 IN 연산자

• 여러 개의 값 중에서 일치하는 것이 있으면 조건에 맞는 것으로 함

• 논리연산자 OR 대신 사용 가능, 날짜와 문자열 비교도 가능

 

부서번호가 10, 20인 사원의 사번, 이름, 부서번호 출력

SELECT empno, ename, deptno FROM emp WHERE deptno IN (10, 20);

 

BETWEEN A AND B 연산자

• A에서 B까지의 특정 범위 값을 조회하는 연산

• 논리연산자 AND 대신 사용 가능

 

급여가 2000에서 4000 사이인 사원의 사번, 이름, 급여 출력

SELECT empno, ename, sal FROM emp WHERE sal BETWEEN 2000 AND 4000;

 

논리 연산자 : AND, OR, NOT

• WHERE 절에 조건이 두 개 이상일 경우 : AND, OR

  AND : 두 조건을 모두 만족해야 검색 (조건1 AND 조건2)

  OR : 두 조건 중에서 한 가지만 만족하면 검색 (조건1 OR 조건2)

 

• WHERE 절의 조건에 해당하지 않을 경우 검색 : NOT

  NOT 조건

 

AND

급여가 2000에서 4000 사이인 사원의 사번, 이름, 급여 출력

SELECT empno, ename, sal FROM emp WHERE sal >=2000 AND sal <= 4000;

 

OR

직급이 CLERK(사무원)이거나, 부서번호가 10번인 사원의 사번, 이름, 직급, 부서번호 출력

SELECT empno, ename, job, deptno FROM emp WHERE job = ‘CLERK’ OR deptno = 10;

 

፠OR과 IN은 경우에 따라 대체하여 사용 가능.

 

NOT

부서번호가 20번이 아닌 사원의 사번, 이름, 부서번호 출력

SELECT empno, ename, deptno FROM emp WHERE NOT depno = 20;

 

NOT IN 연산

부서번호가 10, 20번이 아닌 사원의 사번, 이름, 부서번호 출력

SELECT empno, ename, deptno FROM emp WHERE deptno NOT IN (10,20);

NOT LIKE 연산

이름에 A를 포함하지 않는 사원의 사번, 이름, 부서번호 출력

SELECT empno, ename, deptno FROM emp WHERE ename NOT LIKE ‘%A%’;

 

NOT BETWEEN A AND B 연산

급여가 1000에서 3500 사이에 포함되지 않는 사원의 사번, 이름, 급여 출력

SELECT empno, ename, sal FROM emp WHERE sal NOT BETWEEN 1000 AND 3500;

 

IS NULL, IS NOT NULL

• 컬럼값이 NULL인지, 아닌지를 비교하는 연산자

• 관리자(mgr)가 없는(NULL) 사원의 사번, 이름, 관리자 출력

SELECT empno, ename, mgr FROM emp WHERE mgr IS NULL;

 

፠NULL 값은 = 연산자가 아니라 IS NULL 연산자를 사용해야만 비교가 가능.

 

수당을 받는 모든 사원 출력

SELECT empno, ename, comm FROM emp WHERE comm IS NOT NULL;

ORDER BY 절

• 쿼리 결과의 순서를 다시 정렬하기 위해 사용하는 절

 

SELECT *|컬럼명 FROM 테이블명 WHERE 조건절 ORDER BY 기준컬럼명 [ASC | DESC];

 

기준 컬럼명 : 정렬하기 위한 기준 컬러명

(동일 값을 처리하기 위하여 여러 컬럼을 작성할 수 있음)
ASC : 오름차순(생략가능, default값)

DESC : 내림차순

 

ORDER BY 절 : 숫자 정렬

• 사원의 사번, 이름, 급여를 출력(급여기준 오름차순)

SELECT empno, ename, sal FROM emp ORDER BY sal; #default 값으로 오름차순

            SELECT empno, ename, sal FROM emp ORDER BY sal DESC; #내림차수

 

ORDER BY 절 : 문자열 정렬

• 사원의 사번, 이름, 급여를 출력(이름기준 오름차순)

SELECT empno, ename, sal FROM emp ORDER BY ename;

 

ORDER BY 절 : 날짜 정렬

• 사원의 사번, 이름, 입사일을 출력(입사일이 가장 최근인 사원 순)

SELECT empno, ename, hiredate FROM emp ORDER BY hiredate DESC;

 

ORDER BY 절 : 여러 컬럼값 정렬

• 사원의 사번, 급여, 이름 출력

(급여기준 내림차순, 급여가 같으면 이름기준 오름차순)

SELECT empno, sla, ename FROM emp ORDER BY sal DESC, ename ASC;

SQL 주요함수 1 - 숫자함수

DUAL 테이블

: SELECT 절에 기술할 테이블이 없을 경우 사용하는 Dummy Table

 

언제 사용할까?

-(Q) 간혹, 상황에 따라 쿼리 작업에서 연산식만 사용하는 경우가 있다.

예를 들어, 10+30 결과를 구하기 위하여 쿼리를 작성한다면?

-SQL > SELECT 10+30 FROM ??????;

 

(?) 10 + 30 연산은 테이블 없다.

-> 실행되려면 테이블이 있어야 한다.

 

ROUND(반올림) 함수

: 지정한 자리 수 이하에서 반올림한 결과를 구해주는 함수

형식 : ROUND(숫자, n) n : 반올림 자릿수

반올림자리

반올림 방법

• 반올림자리(n)가 0 또는 양수이면, n+1 위치에서 반올림

• 반올림자리(n)가 음수이면, n위치에서 반올림

 

소수점 이하 두번째 자리에서 반올림

SELECT ROUND(46.593, 2) FROM DUAL; 결과 46.59

 

-1은 일의 자리에서 반올림

SELECT ROUND(46.593, -1) FROM DUAL; 결과 50

 

TRUNC(버림) 함수

: 지정한 자리 수 이하에서 버린 결과를 구해주는 함수

형식 : TRUNC(숫자, n) n : 버림 자릿수

버림자리

버림 방법

• 버림자리(n)가 0 또는 양수이면, n+1 위치에서 버림

• 버림자리(n)가 음수이면, n 위치에서 버림

 

소수점 이하 두 번째 자리에서 버림

SELECT TRUNC(46.593, 2) FROM DUAL; 결과 46.59

 

-1은 일의 자리에서 버림

SELECT TRUNC(46.593, -1) FROM DUAL; 결과 40

MOD(나머지) 함수

: 나누기 연산을 한 후에 구한 나머지를 결과로 돌려주는 함수

형식 : MOD(컬럼| 숫자, 나누기값)

 

10번 부서 사원의 급여를 100으로 나눈 나머지 출력

SELECT MOD(sal, 100) FROM emp WHERE deptno = 10;

SQL 주요함수 2 - 문자함수

UPPER(대문자), LOWER(소문자) 함수

: 소문자는 대문자로, 대문자는 소문자로 변환하여 출력하는 함수

 

20번 부서 사원의 모든 이름을 모두 소문자로 변경하여 출력하는 쿼리를 작성하시오.

SELECT LOWER(ename) FROM emp WHERE deptno = 20;

 

INITCAP 함수

: 단어의 첫글자를 대문자로 변환하여 출력하는 함수

 

사원 이름의 첫 글자를 대문자로 출력하는 쿼리를 작성하시오

SELECT INITCAP(ename) FROM emp;

 

LENGTH 함수

: 문자열의 길이를 구하여 출력하는 함수

 

사원 이름이 몇 글자인지 출력하는 쿼리를 작성하시오.

SELECT ename, LENGTH(ename) FROM emp;

 

INSTR 함수

: 특정 컬럼 또는 문자열에서 지정 문자의 위치를 반환하는 함수

형식 : INSTR(컬럼|문자열, [검색할 시작위치], [검색문자 순서])

 

-SQL>SELECT INSTR(‘welcome to oracle’, ‘o’) FROM DUAL; 결과 5

           SELECT INSTR(‘welcome to oracle’, ‘o’, 6) FROM DUAL; 결과 10

           SELECT INSTR(‘welcome to oracle’, ‘o’, 3, 3) FROM DUAL; 결과 12

 

SUBSTR 함수

: 특정 컬럼 또는 문자열에서 지정한 일부분을 추출하여 반환하는 함수

형식 : SUBSTR(컬럼|문자열,m,[n]) //m : 시작워치, n : 추출문자개수

*n이 생략되면 m 위치에서 문자열 끝까지 추출

 

SQL>SELECT SUBSTR(‘welcome to oracle’, 4, 3) FROM DUAL; 결과 com

          SELECT SUBSTR(‘welcome to oracle’, 10) FROM DUAL; 결과  oracle

LPAD, RPAD 함수

: 오른쪽(왼쪽) 정렬 후, 지정 문자를 왼쪽(오른쪽)에 채우는 함수

형식 : LPAD(컬럼|문자열, m, ‘c’), RPAD(컬럼|문자열,m, ‘c’)

       m: 자리수, c: 채울문자

 

SELECT LPAD(‘oracle’,10,’*’), RPAD(‘oracle’,10,’*’) FROM DUAL:

                         ****oracle                 oracle****

 

LTRIM, RTRIM 함수

: 왼쪽(오른쪽) 지정 문자가 연속이면 지정 문자를 삭제하는 함수

형식 : LTRIM(컬럼|문자열, ‘c’), RTRIM(컬럼|문자열, ‘c’) c: 삭제문자

 

SELECT LTRIM(‘****oracle’, ‘*’), RTRIM(‘oracle****’,’*’) FROM DUAL;

                            oracle                             oracle

 

TRIM 함수

: 양쪽에 지정 문자가 연속이면 지정 문자를 삭제하는 함수

형식 : TRIM(‘c’ from 컬럼|문자열)

 

SELECT TRIM(‘*’ from ‘***oracle****’) FROM DUAL;

결과 : oracle

SQL 주요함수 3 - 날짜함수

날짜 함수
: 날짜 데이터형에 사용, 대부분 일 단위로 계산(기본표현 : 년/월/일)

 

SYSDATE 함수

: 시스템에 저장된 현재 날짜와 시간을 반환하는 함수

 

SELECT SYSDATE FROM DUAL; 기본적으로 날짜만 출력하고 시간은 표시되지 않음.

 

날짜 연산

: 날짜 형 데이터에도 더하기나 빼기와 같은 연산이 가능함

 

SYSDATE+1(내일 날짜), SYSDATE-1(어제 날짜)

SELECT SYSDATE+1, SYSDATE -1 FROM DUAL;

 

날짜 연산

• 20번 부서 사원들의 현재까지 근무 일수를 출력하는 쿼리를 작성하시오.

SELECT ename, hiredate, SYSDATE-hiredate FROM emp WHERE deptno = 20;

 

፠ 날짜타입에는 연월일뿐만 아니라, 시분초까지도 저장되어 있음. 소수점 이하의 숫자는 하루가 되지 못한 시간을 의미.

MONTHS_BETWEEN 함수

: 날짜와 날짜 사이의 개월 수를 구하는 함수

형식 : MONTHS_BETWEEN(최근날짜, 이전 날짜)

 

• 20번 부서 사원들의 현재까지 근무 개월수를 출력하는 쿼리를 작성하시오.

• SELECT ename, hiredate, MONTHS_BETWEEN(SYSDATE, hiredate) FROM emp WHERE deptno = 20;

 

ADD_MONTHS 함수

: 지정한 개월 수를 더한 날짜를 구하는 함수

형식 : ADD_MONTHS(날짜, 더할 개월수)

 

• 20번 부원 사원들의 입사일자에서 6개월 후 일자를 구하는 쿼리를 작성하시오.

• SELECT hiredate, ADD_MONTHS(hiredate, 6) FROM emp WHERE deptno = 20;

 

LAST_DAY 함수

: 해달 날짜가 속한 달의 마지막 날짜를 반환하는 함수

형식 : LAST_DAY(날짜)

 

• 20번 부서 사원들의 입사한 달의 마지막 날을 구하는 쿼리를 작성하시오

SELECT hiredate, LAST_DAY(hiredate) FROM emp WHERE deptno = 20;

 

NEXT_DAY 함수

: 해당 날짜를 기준으로 명시된 요일에 해당되는 날짜를 반환하는 함수

형식 : NEXT_DAY(날짜, 요일)

• 현재 날짜를 기준으로 최초로 돌아오는 월요일 날짜를 구하는 쿼리를 작성하시오.

SELECT NEXT_DAY(SYSDATE, ‘월’) FROM DUAL;

 

SQL 주요함수 4 - 형변환함수

형 변환 함수

: 숫자, 문자, 날짜의 데이터 형을 변환해야 하는 경우에 사용하는 함수

 

TO_NUMBER : 문자 데이터 -> 숫자 데이터

TO_CHAR : 숫자 데이터 -> 문자 데이터

TO_DATE : 문자 데이터 -> 날짜 데이터

 

TO_DATE 함수 : 문자 -> 날짜

• 날짜형은 날짜(년월일)와 시간(시분초)에 대한 정보를 저장

• 오라클 기본 날짜 형식 : YY/MM/DD

형식 : TO_DATE(‘날짜표현문자열’, 날짜_시간포맷)

 

 

TO_DATE 함수 : 문자 -> 날짜

• ‘2020-01-20’ 날짜형식으로 표현된 문자 데이터를, 날짜 데이터(20/01/20)로 출력하는 쿼리를 작성하시오

SELECT TO_DATE(‘2020-01-20’, ‘YYYY/MM/DD’) FROM DUAL;

 

TO_CHAR 함수

형식 : TO_CHAR(숫자|날짜, 형별포맷)

 

 

• 현재 날짜와 시간을 출력하는 쿼리를 작성하시오.

SELECT TO_CHAR(SYSDATE, ‘YYYY/MM/DD, HH24:MI:SS’), FROM DUAL;

 

TO_CHAR 함수 : 숫자 -> 문자

• 20번 부서의 사원 급여를 6자리로 바꾸고, 앞에 $ 또는 지역통화를 붙여 출력하느 쿼리를 작성하시오.(예 : $2,200, \2,200)

SELECT ename, TO_CHAR(sal,’$999,999’), TO_CHAR(sal,’L999,999’) FROM emp WHERE deptno = 20;

TO_NUMBER 함수 : 문자 -> 숫자

• 숫자형식으로 표현된 문자열을 숫자로 변환

형식 : TO_NUMBER(‘문자숫자’)

SELECT TO_NUMBER(‘123’), TO_NUMBER(‘12.3’) FROM DUAL;