[MySQL] 쿼리

데이터베이스

데이터베이스 조회

SHOW DATABASES;

데이터베이스 선택

USE example_db;

지원하는 인코딩 확인

SHOW VARIABLES LIKE 'c%';

데이터베이스 생성

CREATE DATABASE example_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

데이터베이스 수정

ALTER DATABASE example_db DEFAULT CHARACTER SET utf8;

데이터베이스 삭제

DROP DATABASE example_db;

테이블

테이블 조회

SHOW TABLES;

테이블 정보 확인

DESC zip_info;

생성

CREATE TABLE vote
(
    voteSeq          BIGINT UNSIGNED AUTO_INCREMENT,
    voteType         VARCHAR(10)     NOT NULL, # ONCE, DAILY
    voteTitle        VARCHAR(500)    NOT NULL,
    voteCount        BIGINT UNSIGNED NOT NULL,
    voteStartDate    DATETIME        NOT NULL,
    voteEndDate      DATETIME        NOT NULL,
    registrationDate DATETIME        NOT NULL,
    modificationDate DATETIME        NOT NULL,
    PRIMARY KEY (voteSeq)
);

CREATE TABLE voteItem
(
    voteSeq       BIGINT UNSIGNED  NOT NULL,
    voteItemSeq   BIGINT UNSIGNED AUTO_INCREMENT,
    voteItemTitle VARCHAR(500)     NOT NULL,
    voteItemCount BIGINT UNSIGNED  NOT NULL,
    voteItemOrder TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (voteItemSeq),
    FOREIGN KEY (voteSeq) REFERENCES vote (voteSeq)
);

삭제

DROP TABLE vote;

인덱스

테이블 생성시 인덱스 추가

CREATE TABLE user
(
    userSeq  BIGINT UNSIGNED AUTO_INCREMENT,
    id       VARCHAR(100) NOT NULL,
    password VARCHAR(200) NOT NULL,
    role     VARCHAR(20)  NOT NULL,
    PRIMARY KEY (userSeq),
    UNIQUE INDEX (id)
);

인덱스 검색

SHOW INDEX FROM [테이블명];

인덱스 추가

ALTER TABLE [테이블명] ADD INDEX [인덱스명] ([칼럼리스트]);

유니크 인덱스 추가

ALTER TABLE [테이블명] ADD UNIQUE INDEX [인덱스명] ([칼럼리스트]);

인덱스 삭제

ALTER TABLE [테이블명] DROP INDEX [인덱스명];

컬럼

칼럼 추가

ALTER TABLE [테이블명] ADD [컬럼명] [타입명] AFTER [위치할컬럼명];

칼럼 변경

ALTER TABLE [테이블명] CHANGE [기존컬럼명] [새컬럼명] [새자료형];

칼럼 삭제

ALTER TABLE [테이블명] DROP [칼럼명];

기타

날짜 포맷 변환 후 조회

SELECT DATE_FORMAT(datetime, '%Y-%m-%d') FROM user

조건별 데이터 처리 후 조회

SELECT age, IF(age > 0 AND age < 20, 'young', 'old') AS status FROM user

SELECT name, IF(name REGEXP 'Tom|John', 'O', 'X') AS status FROM user

SELECT IFNULL(age, 0) FROM user

특정 시간에 저장된 데이터 조회

• 날짜 상관 없이 9시에 등록된 사용자 조회
• register_datetime은 DATETIME 타입

SELECT * FROM user WHERE HOUR(register_datetime) = 9

두 정수값 범위 사이의 값을 갖는 가상 테이블 조회

• 0시부터 23시까지의 데이터를 가상으로 만들어 조회

WITH RECURSIVE HOURS AS
(
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM HOURS WHERE HOUR < 23
)

SELECT * FROM HOURS

문자열 split

SELECT SUBSTRING_INDEX('2018-04-02', '-', 1);   #2018
SELECT SUBSTRING_INDEX('2018-04-02', '-', 2);   #2018-04
SELECT SUBSTRING_INDEX('2018-04-02', '-', -1);  #02

문자열 substring

SELECT SUBSTRING('20180401', 1, 4);     #2018
SELECT SUBSTRING('20180401', 5, 2);     #04
SELECT SUBSTRING('20180401', 7, 2);     #01

문자열 concat

# MySQL
SELECT CONCAT('A', 'B', 'C'); #ABC

DATETIME

-- add date
SELECT DATE_ADD(NOW(), INTERVAL -7 DAY);
SELECT DATE_ADD(NOW(), INTERVAL +7 HOUR);

-- datetime to string
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- string to datetime
SELECT STR_TO_DATE('2020-01-01', '%Y-%m-%d');

외부 접속 가능한 계정 추가

• 8버전 미만

  GRANT ALL PRIVILEGES ON *.* TO 'ubuntu'@'%' IDENTIFIED BY '123456';
  
  FLUSH PRIVILEGES;
  
  SELECT * FROM mysql.user;
  

• 8버전 이상

  CREATE USER 'ubuntu'@'%' IDENTIFIED BY '123456';
  GRANT ALL PRIVILEGES ON *.* TO 'ubuntu'@'%';
  -- GRANT ALL PRIVILEGES ON my_test_db.* TO 'ubuntu'@'%';
  FLUSH PRIVILEGES;
  
  SELECT * FROM mysql.user;

쿼리 실행 이력 확인

• 참고 : https://extbrain.tistory.com/98

# 로그 입력 방식 확인 (default: FILE)
SELECT @@GLOBAL.log_output;
# 로그 활성화 여부 확인 (default: 0)
SELECT @@GLOBAL.general_log;

# 테이블에 쌓아 쿼리로 이력을 조회할 수 있도록 설정 변경
SET GLOBAL log_output = 'table';
SET GLOBAL general_log = 1;

# 이력 확인
SELECT * FROM mysql.general_log;

# 원복
SET GLOBAL log_output = 'file';
SET GLOBAL general_log = 0;
TRUNCATE mysql.general_log;

로그 파일 위치 확인

SHOW VARIABLES LIKE 'general_log%';