Post

오토에버 클라우드 2기 6일차

Posted Updated
Preview Image
By minnnning
10 min read
오토에버 클라우드 2기 6일차

현재 접속자 확인 select user();

현재 사용중인 데이터 베이스 확인 select database();

Function

입력 데이터를 이용해서 연산을 수행한 후 출력 값을 만들어 내는 개체 입력 데이터는 argument(parameter,매개변수,인수,인자등)라 하고 출력값을 return value 라고 한다

프로그래밍 언어의 함수와 차이점은 항상 리턴값이 존재하고 원본을 변경하지 않는다

 

함수의 종류

  • Scala Function: 데이터 단위로 연산을 수행하는 함수로 데이터 1개를 주면 1개가 리턴되고 하나의 열을 대입하면 열에 존재하는 데이터만큼 리턴(절댓값 계산)
  • Grouping Function: 여러 개의 데이터를 묶어서 연산을 수행하는 함수(평균,최대,최소 계산)
  • System Function: null관련 처리나 타입 변환등을 수행하는 함수

 

Scala Function

수치함수

  1. round

    반올림 select SAL, round(SAL,-2) from emp;

  1. floor

    소수점 버림 select SAL, floor(SAL) from emp;

  2. ceil

    소수점 올림 select SAL, ceil(SAL/100) from emp;

  3. mod

    mod(컬럼이름이나 표현식, 나눌 수) 뒤의 수로 나누어서 나머지를 리턴

    select * from emp where mod(empno,2) = 1 ; 사원번호가 홀수인 사원 출력

 

문자열 함수

select name,BIT_LENGTH(name), CHAR_LENGTH(name), LENGTH(name) from tstaff t ;

한글을 사용할때 한글이 몇 바이트를 차지하는지 알아야한다

  1. char

    숫자를 문자로 변환

  2. left,right

    길이 만큼 왼쪽이나 오른쪽에서 추출 select name, left(name,2), right(name,2) from tstaff t ;

  1. upper,lower

    대소문자 변경 select * from emp where ename=upper('ward'); 오라클은 기본적으로 대소문자 구분하지만 mariadb는 대소문자 구분을 안함(기본설정)

  2. ltrim,rtrim,trim

    공백 제거

  3. substring

    substring(컬럼이나 연산식, 시작위치, 가져올 개수)를 이용해서 부분 문자열 리턴select ename,hiredate from emp where SUBSTRING(hiredate,6,2) = 02; 2월 입사자 출력

 

날짜 함수

데이터베이스는 날짜를 숫자와 문자 모두로 표현이 가능한데 산술 연산도 가능

  1. 현재날짜 및 시간

    current_date(), curdate(), current_time(), curtime(), now(), localtime(), localtimestamp(), current_timestamp()등

    year(), month(), day()

  2. str_to_date(날짜 문자열, 서식 문자열)

    mariadb와 mysql은 일반적인 날짜 형식의 문자열도 자동으로 날짜로 인식

  3. 날짜 + interval

    날짜 + interval 숫자 기간(day, year)을 이용해서 기간형 데이터와 연산 가능

  4. datediff()

    날짜간의 뺄셈

 

시스템 정보 함수

user(), database(), found_rows(), row_count()

 

타입 변환 연산자
  1. datetime: 년원일 시분초, date: 년월일, time: 시분초
  2. char: 문자열
  3. signed: 정수로 부호 사용 가능
  4. unsigned: 정수로 부호 사용 불가
  5. binary: 특정문자 대소문자 구분
  6. cast: 숫자를 문자로 변환

 

NULL관련 함수
  1. ifnull(데이터1,데이터2): 데이터1이 null이면 데이터2
  2. nullif(데이터1,데이터2): 두개의 데이터가 같으면 null, 다르면 첫번째 인자값
  3. coalsce(데이터 나열): 데이터중 null이 아닌 첫번째 데이터 리턴

 

case ~ when ~ else ~ end

case 데이터 when 값 then 데이터가 값일때 내용 … else 일치하는 값이 없는 경우 end

 

Grouping Function

집계함수 데이터를 그룹화해서 통계를 계산 group by 이후에 그룹화가 이루어지므로 having 절이나 select절에서만 사용가능하다

sum, avg → null을 제외

count → null인 데이터 제외

max, min, stddev(표준편차), variance(분산)

count는 모든 데이터가 null인경우 0을 리턴하지만 나머지 함수는 null을 리턴

 

  • Group by는 컴럼의 값이나 연산의 결과가 동일한 데이터끼리 모아서 처리하는 절, 이 절에서 사용한 컬럼과 집계함수를 같이 출력

  • Having은 group by 이후의 조건을 기술하는 절이다

 

SubQuery와 JOIN

set연산 2개 이상의 테이블로부터 데이터를 추출하는 방법 중 하나 이 연산자를 이용해서 여러개의 select문장을 연결해서 사용이 가능하다

주의 첫번째 select와 두번째 select 구문의 조회된 열의 개수와 자료형이 순서대로 일치해야 한다 order by는 한번만 사용가능

테이블이 존재 할 때 위 아래로 짜른 구조일때 set을 사용한다

연산자설명중복 제거
UNION두 SELECT 결과의 합집합O
UNION ALL중복 포함한 합집합X
INTERSECT교집합O
EXCEPT (MINUS for Oracle)차집합O
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- EMP 테이블과 MANAGER 테이블의 이름 목록 합집합
SELECT name FROM emp
UNION
SELECT name FROM manager;

-- 중복을 제거하지 않고 합집합
SELECT name FROM emp
UNION ALL
SELECT name FROM manager;

-- EMP와 MANAGER 테이블에 모두 있는 이름 (교집합)
SELECT name FROM emp
INTERSECT
SELECT name FROM manager;

-- EMP에만 있고 MANAGER에는 없는 이름 (차집합)
SELECT name FROM emp
EXCEPT
SELECT name FROM manager;

 

SubQuery

하나의 SQL 문 내부에 포함된 또 다른 SELECT 문. 괄호 ()로 감싸야 하며, 사용하는 위치에 따라 인라인 뷰, 스칼라 서브쿼리, 다중행 서브쿼리로 나뉜다

사용 위치명칭설명
WHERE절일반 서브쿼리조건 필터링
FROM절인라인 뷰가상의 테이블처럼 사용
SELECT절스칼라 서브쿼리단일 값 반환

  • WHERE절에서 사용하는 서브쿼리 예시

    1
2
3
4
5
6
7
8

    -- 부서 번호가 10번인 사원의 이름 조회
SELECT name
FROM emp
WHERE dept_id = (
    SELECT dept_id
    FROM dept
    WHERE dept_name = 'ACCOUNTING'
);

  • IN / ANY / ALL 등을 사용하는 다중행 서브쿼리 → 서브쿼리 반환이 여러행

    연산자의미동작 방식
    IN포함 여부 검사서브쿼리 결과 집합에 값이 하나라도 있으면 TRUE
    ANY비교 연산자와 함께 사용
    (= SOME)    		서브쿼리 결과 중 하나라도 비교 조건을 만족하면 TRUE
    ALL비교 연산자와 함께 사용서브쿼리 결과 모두 비교 조건을 만족해야 TRUE
    EXISTS데이터 존재 여부하나라도 참이면 이전 select문 실행
    1
2
3
4
5
6
7
8

    -- 급여가 모든 부서 평균 급여보다 높은 사원
SELECT name
FROM emp
WHERE salary > ALL (
    SELECT AVG(salary)
    FROM emp
    GROUP BY dept_id
);

 

JOIN

테이블을 수평 결합(side‑by‑side)하여 관련 데이터를 한 번에 조회할 때 사용한다 2개의 테이블으 가로방향으로 합치는 연산

JOIN 유형설명반환할 행 조건
INNER JOIN양쪽 테이블에서 매칭되는 행만A⋈B 의 교집합
LEFT JOIN왼쪽 테이블의 모든 행 + 오른쪽 매칭 행A ∪ (A⋈B)
RIGHT JOIN오른쪽 테이블의 모든 행 + 왼쪽 매칭 행B ∪ (A⋈B)
FULL OUTER JOIN양쪽 테이블의 모든 행, 매칭 없으면 NULLA ∪ B
CROSS JOIN모든 조합(Cartesian product)행은 행X행 개수, 열은 열+ 열 개수 만큼 나온다
SELF JOIN같은 테이블을 두 번 참조테이블을 자기 자신과 조인
SEMI JOIN서브쿼리를 이용해서 조인 

 

오토에버 클라우드 2기
DB mariaDB sql
This post is licensed under CC BY 4.0 by the author.