[MariaDB] SQL Query 기본

함수

• 사칙 연산, 함수 사용 가능

SELECT SIN(PI()/4), 1 + 2 * 3 - 4 / 5 FROM DUAL;

• 대소문자 구분 없음 (※ 테이블명은 대소문자 구분)

SeLeCt VERSION(), CURRENT_DATE, NOW() FROM DUAL;

-- CREATE TABLE (DDL)
CREATE TABLE PET(
	NAME VARCHAR(100),
    OWNER VARCHAR(50),
    SPECIES VARCHAR(20),
    GENDER CHAR(1),
    BIRTH DATE,
    DEATH DATE
);

-- CHECK SCHEMA
DESCRIBE PET;
DESC PET;

-- DELETE TABLE
DROP TABLE PET;
SHOW TABLES;

-- INSERT: DML(CREATE)
INSERT INTO PET VALUES('구미호', '나루토', 'fox', 'm', '2007-12-25', NULL);

-- SELECT: DML(READ)
SELECT * FROM PET;

-- UPDATE: DML(UPDATE)
UPDATE PET SET SPECIES='dog' WHERE SPECIES='fox';

-- DELETE: DML(DELETE)
DELETE FROM PET WHERE NAME='성타니';

• LOAD DATA: MySQL (CLI) 전용

LOAD DATA LOCAL INFILE '/root/pet.txt' INTO TABLE PET;

SELECT 방법

SELECT NAME, SPECIES
  FROM PET
 WHERE NAME = 'BOWSER';

SELECT NAME, SPECIES, BIRTH
  FROM PET
 WHERE BIRTH > '1997-12-31';

SELECT NAME, SPECIES, GENDER
       FROM PET
      WHERE SPECIES = 'dog'
      AND GENDER = 'F';

SELECT NAME, SPECIES
  FROM PET
 WHERE SPECIES = 'bird'
    OR SPECIES = 'snake';

  SELECT NAME, BIRTH
    FROM PET
ORDER BY BIRTH ASC;

  SELECT NAME, BIRTH
    FROM PET
ORDER BY BIRTH DESC;

  SELECT NAME, BIRTH, DEATH
    FROM PET
   WHERE DEATH IS NULL;

  SELECT NAME
    FROM PET
   WHERE NAME LIKE 'b%';

  SELECT NAME
    FROM PET
   WHERE NAME LIKE '%fy';

  SELECT NAME
    FROM PET
   WHERE NAME LIKE '%w%';

  SELECT NAME
    FROM PET
   WHERE NAME LIKE 'b____'; -- b로 시작, 다섯글자 이름

SELECT NAME, COUNT(*), MAX(BIRTH)
  FROM PET;

기본 함수

시간

- `NOW()` : 쿼리가 실행된 시간
- `SYSDATE()` : 함수가 실행된 시간

SELECT NOW(), SLEEP(2), SYSDATE() FROM DUAL;

날짜

SELECT DATE_FORMAT(NOW(), '%d %b') FROM DUAL;

날짜 비교(1) : PERIOD_DIFF

두 인자의 개월 수 차이를 구하는 함수

SELECT FIRST_NAME, HIRE_DATE,
       PERIOD_DIFF(
       DATE_FORMAT(CURDATE(), '%y%m'),
       DATE_FORMAT(HIRE_DATE, '%y%m'))
FROM employees;

날짜 비교(2) : DATE_ADD(=ADDDATE), DATE_SUB(=SUBDATE)

• 날짜를 date 타입의 컬럼이나 값에 type(year, month, day)의 표현식으로 더하거나 뺄 수 있다.
• 예제) 각 사원의 근속 연수가 5년이 되는 날에 휴가를 보내준다면, 각 사원의 5년 근속 휴가 날짜는?

SELECT FIRST_NAME, HIRE_DATE,
       DATE_ADD(HIRE_DATE, INTERVAL 5 YEAR)
FROM employees;

형변환 : CAST

SELECT DATE_FORMAT('2013-01-09', '%Y년 %m월 %d일') FROM dual;
-- Error

SELECT DATE_FORMAT(CAST('2013-01-09' AS DATE), '%Y년 %m월 %d일') FROM dual;

SELECT '12345' + 10, CAST('12345' AS INT) + 10 FROM dual;
-- 12355, 12355

SELECT CAST(CAST(1-2 as unsigned) as signed) FROM dual;

SELECT CAST(CAST(1-2 AS UNSIGNED) AS INT) FROM dual;
-- Error

SELECT CAST(CAST(1-2 AS UNSIGNED) AS INTEGER) FROM dual;
-- Error

타입

-- 문자: varchar < char < text < CLOB(Character Large Object)
-- 정수: medium int, int(signed, integer 다 동의어), unsigned, bit int
-- 실수: float, double
-- 시간: date, datetime
-- LOB: CLOB, BLOB