Seoyun Lee

Seoyun Lee

매일 공부하고 매일 정리하기

[MySQL] MySQL 정리 2

5 분 소요

MySQL 내장함수

  • 사람들의 편의를 위해 다양한 기능의 내장 함수를 미리 정의하여 제공
  • 대표적인 내장함수 종류
    • 문자열 함수
    • 수학 함수
    • 날짜와 시간 함수

LENGTH()

  • 전달받은 문자열의 길이를 반환

    SELECT LENGTH('abcdefg');
>>> 7

CONCAT()

  • 전달받은 문자열을 모두 결합하여 하나의 문자열로 반환

  • 전달받은 문자열 중 하나라도 NULL이 존재하면 NULL을 반환

    SELECT CONCAT('My', 'SQL Op', 'en Source'), CONCAT('MySQL', NULL, 'OpenSource');
>>> 'MySQL Open Source', NULL

LOCATE()

  • 문자열 내에서 찾는 문자열이 처음으로 나타나는 위치를 찾아서 해당 위치를 반환

  • 찾는 문자열이 문자열 내에 존재하지 않으면 0을 반환

  • MySQL에서는 문자열의 시작 인덱스를 1부터 계산

    SELECT LOCATE('abc', 'abababababcabacbac');
>>> 9

LEFT(), RIGHT()

  • LEFT() : 문자열의 왼쪽부터 지정한 개수만큼의 문자를 반환

  • RIGHT() : 문자열의 오른쪽부터 지정한 개수만큼의 문자를 반환

    SELECT 
    LEFT('MySQL is an open source relational database management system', 5),
    RIGHT('MySQL is an open source relational database management system', 6);
>>> 'MySQL', 'system'

LOWER(), UPPER()

  • LOWER() : 문자열의 문자를 모두 소문자로 변경

  • UPPER() : 문자열의 문자를 모두 대문자로 변경

    SELECT 
  LOWER('MySQL is an open source relational database management system'),
  UPPER('MySQL is an open source relational database management system');
>>> 'mysql is an open source relational database management system', 'MYSQL IS AN OPEN SOURCE RELATIONAL DATABASE MANAGEMENT SYSTEM'

REPLACE()

  • 문자열에서 특정 문자열을 대체 문자열로 교체

    SELECT REPLACE('MSSQL', 'MS', 'My');
>>> MySQL

TRIM()

  • 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거

  • TRIM() : 함수에서 사용할 수 있는 지정자

    • BOTH() : 전달받은 문자열의 양 끝에 존재하는 특정 문자를 제거 (기본 설정)
    • LEADING() : 전달받은 문자열 앞에 존재하는 특정 문자를 제거
    • TRAILING() : 전달받은 문자열 뒤에 존재하는 특정 문자를 제거

  • 만약 지정자를 명시하지 않으면, 자동으로 BOTH로 설정

  • 제거할 문자를 명시하지 않으면, 자동으로 공백을 제거

    SELECT TRIM('    ##MySQL##    '),
       TRIM(LEADING '#' FROM '##MySQL##'),
       TRIM(TRAILING '#' FROM '##MySQL##')
>>> '##MySQL##', 'MySQL##', '##MySQL'

FORMAT()

  • 숫자 타입의 데이터를 세 자리마다 쉼표를 사용하는 형식으로 변환

  • 반환되는 데이터의 형식은 문자열 타입

  • 두 번째 인수는 반올림할 소수 부분의 자릿수

    SELECT FORMAT(123123123123.123123123, 6);
>>> 123,123,123.123123

FLOOR(), CEIL(), ROUNT()

  • FLOOR() : 내림

  • CEIL() : 올림

  • ROUND() : 반올림

    SELECT FLOOR(10.95), CEIL(10.95), ROUND(10.95);
>>> '10', '11', '11'

SORT(), POW(), EXP(), LOG()

  • SORT() : 양의 제곱근

  • POW() : 첫 번째 인수로는 밑수, 두 번째 인수로는 지수를 전달하여 거듭제곱 계산

  • EXP() : 인수로 지수를 전달받아, e의 거듭제곱을 계산

  • LOG() : 자연로그 값을 계산

    SELECT SQRT(4), POW(2, 3), EXP(3), LOG(3);
>>> '2', '8', '20.085536923187668', '1.0986122886681098'

SIN(), COS(), TAN()

  • SIN() : 사인값 반환

  • COS() : 코사인값 반환

  • TAN() : 탄젠트값 반환

    SELECT SIN(PI()/2), COS(PI()), TAN(PI()/4);
>>> '1', '-1', '0.9999999999999999'

ABS(), RAND()

  • ABS() : 절대값을 반환

  • RAND() : 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성

    SELECT ABS(-2), RAND(), ROUND(RAND()*100, 0);
>>> '2', '0.4142058337239603', '35'

NOW(), CURDATE(), CURTIME()

  • NOW() : 현재 날짜와 시간을 반환, 반환되는 값은 ‘YYYY-MM-DD HH:MM:SS’ 또는 YYYYMMDDHHMMSS 형태로 반환

  • CURDATE() : 현재 날짜를 반환, 반환되는 값은 ‘YYYY-MM-DD’ 또는 YYYYMMDD 형태로 반환

  • CURTIME() : 현재 시간을 반환, 반환되는 값은 ‘HH:MM:SS’ 또는 HHMMSS 형태로 반환

    SELECT NOW(), CURDATE(), CURTIME();
>>> '2022-05-23 22:28:34', '2022-05-23', '22:28:34'

DATE(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

  • 전달받은 값에 해당하는 날짜, 월, 일, 시, 분, 초에 해당하는 값을 반환

    SELECT 	NOW(),
        DATE(NOW()),
        MONTH(NOW()),
        DAY(NOW()),
        HOUR(NOW()),
        MINUTE(NOW()),
        SECOND(NOW());
>>> '2022-05-23 22:31:58', '2022-05-23', '5', '23', '22', '31', '58'

MONTHNAME(), DAYNAME()

  • MONTHNAME() : 월에 해당하는 이름을 반환

  • DAYNAME() : 요일에 해당하는 이름을 반환

    SELECT 	NOW(),
        MONTHNAME(NOW()),
        DAYNAME(NOW());
>>> '2022-05-23 22:34:03', 'May', 'Monday'

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

  • DAYOFWEEK() : 일자가 해당 주에서 몇번째 날인지를 반환 (일요일 = 1, 토요일 = 7)

  • DAYOFMONTH() : 일자가 해당 월에서 몇 번째 날인지 반환 (0 ~ 31)

  • DAYOFYEAR() : 일자가 해당 연도에서 몇번째 날인지를 반환 (1 ~ 366)

    SELECT 	NOW(),
        DAYOFWEEK(NOW()),
        DAYOFMONTH(NOW()),
        DAYOFYEAR(NOW());
>>> '2022-05-23 22:37:03', '2', '23', '143'

DATE_FORMAT()

  • 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환

  • MySQL Date and Time Function : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

    SELECT DATE_FORMAT(NOW(), '%D %y %a %d %m %n %j');
>>> '23rd 22 Mon 23 05 n 143'

CREATE TABLE [new_table_name] AS SELECT [table_name]

  • TABLE 복제

    CREATE TABLE city2 AS SELECT * FROM city;

CREATE DATABASE [new_table_name]

  • 새로운 데이터베이스를 생성 → USE문으로 새 데이터베이스를 사용

    CREATE DATABASE seoyun;
USE seoyun;

  • [seoyun] → [Tables] 우클릭 → [Create Table]

    create_table

    CREATE TABLE test (
  id INT NOT NULL PRIMARY KEY,
  col1 INT NOT NULL,
  col2 FLOAT NOT NULL,
  col3 VARCHAR(45) NULL		);
      
SELECT * FROM test;

ALTER TABLE()

  • ADD() : 테이블에 칼럼 추가

  • MODIFY() : 테이블의 칼럼 타입 변경

  • DROP() : 테이블에 칼럼 제거

    ALTER TABLE test
	ADD col4 INT NULL;
      
ALTER TABLE test
	MODIFY col4 VARCHAR(20) NULL;
      
ALTER TABLE test
	DROP col4;

INDEX

  • 테이블에서 원하는 데이터를 빠르게 찾기 위해 사용
  • 일반적으로 데이터를 검색할 때 순서대로 테이블 전체를 검색하므로 데이터가 많으면 많을수록 탐색하는 시간이 늘어남
  • 검색과 질의를 할 때 테이블 전체를 읽지 않기 때문에 빠름
  • 설정된 컬럼 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 함
  • 인덱스가 있는 테이블은 처리 속도가 느려질 수 있으므로 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 좋음
  • CREATE INDEX : 인덱스 생성
  • CREATE UNIQUE INDEX : 중복을 허용하지 않는 인덱스
  • FULLTEXT INDEX : 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색
  • SHOW INDEX : 인덱스 정보 보기

CREATE INDEX

CREATE INDEX Col1Idx ON test(col1);

CREATE UNIQUE INDEX Col2Idx ON test(col2);

ALTER TABLE test ADD FULLTEXT Col3Idx(col3);

SHOW INDEX FROM test;

show_index_test2

  • Test라는 테이블을 생성할 때 ID 칼럼은 PRIMARY KEY로 설정해 두었으므로 PRIMARY라는 인덱스를 부여받음

INDEX 삭제

  1. ALTER문을 사용하여 테이블에 추가된 인덱스 삭제

    ALTER TABLE test
 DROP INDEX Col3Idx;

  2. DROP문은 내부적으로 ALTER문으로 자동 변환되어 명시된 이름의 인덱스를 삭제

    DROP INDEX Col2Idx ON test;

VIEW

  • VIEW는 데이터베이스에 존재하는 일종의 가상 테이블

  • 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하진 않음

  • MySQL에서 뷰는 다른 테이블이나 다른 VIEW에 저장되어 있는 데이터를 보여주는 역할만 수해

  • VIEW를 사용하면 여러 테이블이나 VIEW를 하나의 테이블처럼 볼 수 있음

  • VIEW의 장점

    • 특정 사용자에게 테이블 전체가 아닌 필요한 컬럼만 보여줄 수 있음
    • 복잡한 쿼리를 단순화해서 사용
    • 쿼리 재사용 가능

  • VIEW의 단점

    • 한 번 정의된 뷰는 변경할 수 없음
    • 삽입, 삭제, 갱신 작업에 많은 제한 사항을 가짐
    • 자신만의 인덱스를 가질 수 없음

  • CREATE VIEW : 뷰 생성

  • ALTER VIEW : 뷰 수정

  • DROP VIEW : 뷰 삭제

    CREATE VIEW testView AS
  SELECT Col1, Col2
  FROM test;
  	
ALTER VIEW testView AS
  SELECT Col1, Col2, Col3
  FROM test;
  	
DROP VIEW testView;

INSERT

  • 데이터를 직접 테이블에 삽입할 때 사용

  • 테이블 이름 다음에 나오는 열 생략 가능

  • 생략할 경우에 VALUE 다음에 나오는 값들의 순서 및 개수가 테이블이 정의도니 열 순서 및 개수와 동일해야 함

  • INSERT INTO VALUE : 직접 table에 값을 넣을 수 있음

    INSERT INTO test
  VALUE(1, 123, 1.1, "test");
    • SELECT * FROM test; → 직접 RESULT GRID에서 값을 입력 → [Apply] 로도 값을 삽입할 수 있음

  • INSERT INTO SELECT : 테이블 안에 다른 테이블의 내용을 복사

    # Test2는 Test와 같은 테이블이 됨
INSERT INTO test2 SELECT * FROM test;

  • UPDATE : 기존에 입력되어 있는 값을 변경 (WHERE절 생략 가능하나 테이블의 전체행의 내용 변경)

    UPDATE test
  SET col1 = 1, col2 = 1.0, col3 = 'test'
  where id = 1;

  • DELETE : 행 단위로 데이터를 삭제하는 구문(데이터는 지워지지만 테이블 용량은 줄어들지 않음) → 원하는 데이터만 지울 수 있고 삭제 후 잘못 삭제한 것을 되돌릴 수 있음

    DELETE FROM test 
	WHERE id = 1;

  • TRUNCATE : 용량이 줄어 들고, 인덱스 등도 모두 삭제 → 한번에 다 지워야 하고 삭제후 되돌릴 수 없음

    TRUNCATE TABLE test;

  • DROP TABLE : 테이블 전체, 공간, 객체를 삭제 → 삭제후 되돌릴 수 없음

    DROP TABLE test;
DROP DATABASE seoyun;

태그:

카테고리:

업데이트:

댓글남기기

참고