데이터베이스 Day5 - SQL에서의 각종 함수

SQL에서의 각종 함수

수학 함수

ROUND(값, 자리수)

• 반올림한 값을 구하는 함수
• 자리수 아래에서 반올림하여 자리수까지 출력
• 양수값: 소수점 오른쪽 자릿수(소수점 이하)
• 음수 값: 소수점 왼쪽 자릿수 (1의 자리부터)
  • ex) ROUND(3.456, 1) : 3.500

SELECT clientNo, ROUND(AVG(bookPrice * bsQty)) AS "평균 주문액",
                 ROUND(AVG(bookPrice * bsQty),0) AS "1의 자리까지 출력",
                 ROUND(AVG(bookPrice * bsQty),-1) AS "10의 자리까지 출력",
                 ROUND(AVG(bookPrice * bsQty),-2) AS "100의 자리까지 출력",
                 ROUND(AVG(bookPrice * bsQty),-3) AS "1000의 자리까지 출력"
FROM book, bookSale
WHERE book.bookNo = bookSale.bookNo
GROUP BY clientNo;

순위 출력 함수

RANK( ), DENSE_RANK( ), ROW_NUMBER( )

• RANK( ): 값의 순위 반환 (동일 순위 개수만큼 증가) 1 1 3
• DENSE_RANK( ): 값의 순위 반환 (동일 순위 상관없이 1 증가) 1 1 2
• ROW_NUMBER( ): 행위 순위

SELECT bookPrice,
    RANK() OVER (ORDER BY bookPrice DESC) "RANK",
    DENSE_RANK() OVER (ORDER BY bookPrice DESC) "DENSE_RANK",
    ROW_NUMBER() OVER (ORDER BY bookPrice DESC) "ROW_NUMBER"
FROM book;

문자 함수

REPLACE( ), CHAR_LENGTH( ), LENGTH( ), SUBSTR( )

• REPLACE( ) : 문자열을 치환(대체)하는 함수
• CHAR_LENGTH( ): 문자열 길이(글자 수)를 반환하는 함수
  • 스페이스(공백)도 1글자로 인식
  • '자바 프로그래밍' : 8자
• LENGTH( ): 바이트 수
  • utf8 : 한글 3바이트
  • 유니코드: 한글 2바이트
  • '자바 프로그래밍' : 22바이트
  • 'HTML/CSS' : 10바이트(영문자 -> 1바이트)
  • 'MFC 입문' : 10바이트
• SUBSTR( ): 지정한 길이만큼의 문자열을 반환하는 함수

-- '서울 출판사'에서 출간한 도서의 도서명과 바이트 수, 문자열 길이, 출판사명 출력
SELECT B.bookName AS "도서명",
       LENGTH(B.bookName) AS "바이트 수",
       CHAR_LENGTH(B.bookName) AS "길이",
       P.pubName AS "출판사"
FROM book B
    INNER JOIN publisher P ON B.pubNo = P.pubNo
WHERE P.pubName = '서울 출판사';

-- 도서 테이블의 '저자' 열에서 성만 출력
SELECT SUBSTR(bookAuthor, 1, 1) AS "성"
FROM book;

-- 도서 테이블의 '저자' 열에서 이름만 출력
SELECT SUBSTR(bookAuthor, 2, 2) AS "이름"
FROM book;

날짜 함수

DATE( ), TIME( ), YEAR( ), MONTH( ), DAYOFMONTH( ), ...

• DATE(NOW( )): 현재 날짜 출력
• TIME(NOW( )): 현재 시간 출력
• YEAR(CURDATE( )): 현재 날짜 연도 출력
• MONTH(CURDATE( )): 현재 날짜 월 출력
• DAYOFMONTH(CURDATE( )): 현재 날짜 일 출력
• DATEDIFF( ): 날짜 차이 계산
• TIMEDIFF( ): 시간 차이 계산

-- 현재 날짜와 시간 출력
SELECT DATE(NOW()), TIME(NOW());

-- 날짜에서 연, 월, 일 추출
SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE());

-- 시간에서 시, 분, 초, microsecond 출력
-- CURTIME -> CURRENT_TIME으로 변경 가능
SELECT HOUR(CURTIME()), MINUTE(CURTIME()), SECOND(CURTIME()), MICROSECOND(CURTIME());

LOAD_FILE() 함수

• 대용량 데이터 저장

• 대본 : text 타입

• 동영상 : LONGBLOB 타입

• LOAD_FILE("파일 경로")

• 파일 용량이 현재 설정된 크기보다 큰 경우 데이터 저장 안됨

  • my.ini 파일에서

  • 파일 최대 크기 변수 변경

  • 파일 업로드/다운로드 하는 폴더 경로를 별도로 지정하는 내용 추가(동영상 파일이 저장된 경로로 지정)

  • 저장할 수 있는 파일의 최대 크기 변수 확인

    • SHOW variables LIKE 'max_allowed_packet';

    • 4,194,304 -> 1024M(1G)로 변경 (my.ini 가서)

  • 파일 업로드/다운로드 경로 변수 확인

    • SHOW variables LIKE 'secure_file_priv';

    • C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\

    • ProgramData : 숨김 폴더

    • [보기] / [숨긴 항목] 체크

  • my.ini 파일

    • C:\ProgramData\MySQL\MySQL Server 8.0 안에 위치
    • max_allowed_packet=1024M 로 변경
    • secure-file-priv="C:/dbWorkspace/movies" 라인 추가 (개인의 경로에 맞추어 처리!!!)

  • MySQL 서버 재시작

예제

• movie 테이블 생성

CREATE TABLE movie (
    movieId VARCHAR(10) NOT NULL PRIMARY KEY,
    movieTitle VARCHAR(30),
    movieDirector VARCHAR(20),
    movieStar VARCHAR(20),
    movieScript LONGTEXT, -- text 데이터
    movieFilm LONGBLOB -- 바이너리 데이터
);

• Schindler 파일 Insert

INSERT INTO movie 
       VALUES ('1', '쉰드러 리스트', '스필버그', '리암 니슨',
               LOAD_FILE('D:/Full_Stackc_Study/dbWorkspace/database/movies/Schindler.txt'),
               LOAD_FILE('D:/Full_Stackc_Study/dbWorkspace/database/movies/Schindler.mp4'));

데이터 파일로 내보내기

• 테이블에 저장된 Text 타입과 Blob 타입의 데이터를 파일로 내보내기

SELECT 열이름 FROM 테이블 WHERE 조건
INTO OUTFILE '경로/파일명.txt'
LINES TERMINATED BY '\\n'; -- 줄바꿈. 문자도 저장

SELECT 열이름 FROM 테이블 WHERE 조건
INTO OUTFILE '경로/파일명.mp4';

• LONGTEXT 타입의 영화 대본 데이터를 텍스트 파일로 내보내기

SELECT movieScript 
FROM movie 
WHERE movieId = '1'
INTO OUTFILE 'D:/Full_Stackc_Study/dbWorkspace/database/movies/Schindler_out.txt'
LINES TERMINATED BY '\\n';

• 동영상 파일(바이너리 파일)로 내보내기

SELECT movieFilm 
FROM movie 
WHERE movieId = '1'
INTO OUTFILE 'D:/Full_Stackc_Study/dbWorkspace/database/movies/Schindler_out.mp4';

• 도서 테이블의 모든 데이터를 텍스트 파일로 내보내기

SELECT * FROM book
INTO OUTFILE 'D:/Full_Stackc_Study/dbWorkspace/database/movies/book_out.txt';

데이터베이스 관리 기능

• 계정 관리

  • 사용자 계정 생성 / 조회 / 수정

  • 비밀번호 변경

  • 계정 잠금 / 잠금 해제

• 권한 관리

  • 계정에 권한 부여 / 제거

• 테이블 복사

• 백업 및 복구(Export / Import)