형 변환

CAST / CONVERT 함수를 사용하여 데이터 형 변환이 가능하다.

SELECT로 검색 또는 INSERT, UPDATE로 필드에 값을 넣을 때 설정한 데이터타입에 맞춰 데이터를 넣어야 하는 경우 형변환 함수를 사용한다.

사용법

CAST(지정값 AS type)
CONVERT(지정값, type)

사용하기

-- 숫자를 문자로
SELECT CAST(972 AS CHAR);
SELECT CONVERT(972, CHAR);
결과 : 972

-- DECIMAL 활용
SELECT CAST('-100' AS DECIMAL(5,3));
SELECT CONVERT('-100', DECIMAL(5,3));
결과 : -99.999

-- 날짜형 데이터타입을 SIGNED 타입으로
SELECT CAST(NOW() AS SIGNED);
SELECT CONVERT(NOW(), SIGNED);
결과 : 20230108115033

-- 숫자형을 날짜 타입으로
SELECT CAST(20230108 AS DATE);
SELECT CONVERT(20230108, DATE);
결과 : 2023-01-08

날짜, 시간관련 함수

-- 현재의 시간 정보들
SELECT CURDATE(), CURTIME(), NOW();
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

결과 : 2023-01-08 / 11:02:09 / 2023-01-08 11:02:09

SELECT DAYNAME(CURDATE());

결과 : Sunday

SELECT MONTHNAME(CURDATE());

결과 : January

SELECT DAY(CURDATE());
SELECT MONTH(CURDATE());
SELECT YEAR(CURDATE());

결과 : 8 / 1 / 2023

SELECT DAYOFWEEK(CURDATE());
SELECT DAYOFMONTH('2022-02-22');
SELECT DAYOFYEAR('2023-03-03');
SELECT WEEKOFYEAR('2024-04-04');

결과 : 1 / 22 / 62 / 14

SELECT YEARWEEK('2023-05-05');

결과 : 202318

SELECT LAST_DAY('2023-06-06');
SELECT QUARTER('2023-07-07');

결과 : 2023-06-30 / 3

SELECT PERIOD_ADD('202308', 13);
SELECT PERIOD_DIFF('202308', '202408');

결과 : 202409 / -12

-- ADDDATE는 INTERVAL을 붙여 사용도 가능하다
SELECT DATE_ADD(CURDATE(), INTERVAL 111 DAY);
SELECT DATE_ADD(CURDATE(), INTERVAL 111 MONTH);
SELECT DATE_ADD(CURDATE(), INTERVAL 111 YEAR);
결과 : 2023-04-29 / 2032-04-08 / 2134-01-08


SELECT ADDDATE(CURDATE(), 5);
SELECT ADDDATE(CURDATE(), INTERVAL 5 YEAR);
결과 : 2023-01-13 / 2028-01-08


-- SUB : 기존 ADD에 -만 붙여서 사용도 가능하다
SELECT DATE_SUB(CURDATE(), INTERVAL 111 DAY);
SELECT DATE_ADD(CURDATE(), INTERVAL -111 DAY);
결과 : 2022-09-19로 동일

-- EXTRACT의 날짜 요소 값
-- YEAR, MONTH, QUARTER, WEEK, DAY, HOUR, MINUTE, SECOND, YEAR_MONTH, DAY_HOUR
-- DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND 등

SELECT EXTRACT(YEAR_MONTH FROM CURDATE());
SELECT EXTRACT(QUARTER FROM CURDATE());
SELECT EXTRACT(MINUTE_SECOND FROM NOW());

결과 : 202301 / 1 / 2215

SELECT DATE_FORMAT('2222-02-22', '%u %W %j');
결과 : 08 Friday 053

DATE_FORMAT 구분 기호

-- SYSDATE
-- 아래의 SELECT절의 SLEEP(3)우측의 SYSDATE만 3초 뒤의 시간 값을 가져오고 있다.
SELECT NOW(), SYSDATE(), SLEEP(3), SYSDATE(), NOW();

-- NOW는 쿼리가 시작되는 시간을 항상 사용하며
-- SYSDATE는 실행 될 때의 시간이 기준이다.

문자열 함수

SELECT LENGTH('오늘의날씨는맑음');

결과 : 24

SELECT CHAR_LENGTH('오늘의날씨는맑음');

결과 : 8

-- CONCAT(str1, str2, str3, .....)
SELECT CONCAT('오늘의', ' 날씨는', ' 맑아유') AS 오늘의날씨;

결과 : 오늘의 날씨는 맑아유

-- CONCAT(구분자, str1, str2, str3, ....)
-- 해당 예시에서 010만 문자로 취급한 이유는 010을 숫자형으로 넣을 때 '10'이 리턴되기 때문
SELECT CONCAT_WS('-', '010', 1234, 5678);

결과 : 010-1234-5678

SELECT FORMAT(1234567890, 1);
SELECT FORMAT(1234567890.123, 1);

결과 : 1,234,567,890.0 / 1,234,567,890.1

-- INSTR(str, 찾을문자열)
SELECT INSTR('오늘의 날씨는 맑음', '날');

결과 : 5

-- LOCATE(찾을문자열, str)
SELECT LOCATE('맑', '오늘의 날씨는 맑음');

-- LOCATE(찾을문자열, str, 찾기 시작할 위치)
-- 10번째 문자부터 '맑'이 있는지 찾으라는 의미. 문자열의 시작지점부터 길이를 계산해 리턴
SELECT LOCATE('맑', '오늘의 날씨는 맑음', 10);

-- POSITION(찾을문자열 IN str)
SELECT POSITION('맑' IN '오늘의 날씨는 맑음');

결과 : 9 / 0 / 9

SELECT LOWER('TODAY IS GooD');
SELECT UPPER('today Is GoOd');

-- SELECT LCASE('TODAY IS GooD');
-- SELECT UCASE('today Is GoOd');

결과 : today is good / TODAY IS GOOD

SELECT LPAD('WEATHER', 5, 'GOOD');
SELECT LPAD('WEATHER', 15, 'GOOD');

결과 : WEATH / GOODGOODWEATHER

SELECT RPAD('WEATHER', 5, 'GOOD');
SELECT RPAD('WEATHER', 15, 'GOOD');

결과 : WEATH / WEATHERGOODGOOD

-- 활용방법
SELECT LPAD('WEATHER', CHAR_LENGTH('WEATHER')+8, 'GOOD');

-- TRIM (LEADING/TRAILING/BOTH A FROM str) : str에서 사용한 속성에 따라 A를 제거
-- 아무 값도 주지않으면 양쪽의 공백만 제거
SELECT TRIM(LEADING '$' FROM '$$$MONEY$$$');
SELECT TRIM(TRAILING '$' FROM '$$$MONEY$$$');
SELECT TRIM(BOTH '$' FROM '$$$MONEY$$$');
SELECT TRIM('      M O N E Y        ');

결과 : MONEY$$$ / $$$MONEY / MONEY / M O N E Y

SELECT LTRIM('     D D D D D     ');
SELECT RTRIM('     D D D D D     ');

결과 : D D D D D     /     D D D D D

SELECT LEFT('MONEY', 3);
SELECT RIGHT('MONEY', 3);

결과 : MON / NEY

SELECT REPEAT('MONEY', 3);

결과 : MONEYMONEYMONEY

-- REPLACE(str, 바꿀문자열, 바뀔문자열)
SELECT REPLACE('MONEY' , 'EY' , 'DAY');

결과 : MONDAY

SELECT REVERSE('MONEY');

결과 : YENOM

-- SPACE(N) : N칸만큼 공백 추가
SELECT CONCAT_WS('-', '010', SPACE(10), '5678');

결과 : 010-          -5678

-- SUBSTR(str, 시작위치, 길이) : 문자열을 시작위치서부터 길이만큼 잘라낸다
SELECT SUBSTR('MONEY', 3, 2);

결과 : NE

-- STRCMP(str1, str2) : 비교 후 str1이 크면 1, 같으면 0, 작으면 -1 반환
SELECT STRCMP('DEF', 'AEG');
SELECT STRCMP('DEF', 'DEF');
SELECT STRCMP('DEF', 'DEG');

결과 : 1, 0, -1

숫자형 함수

SELECT ABS(-3);

결과 : 3

SELECT CEIL(10);
SELECT CEIL(10.1);
SELECT CEIL(-10.1);

결과 : 10 /  10.1 / -10

SELECT FLOOR(10);
SELECT FLOOR(10.9);
SELECT FLOOR(-10.9);

결과 : 10 / 10 / -11

-- ROUND(A, B) : A를 소수점 B자리까지 반올림한다
SELECT ROUND(23.5);
SELECT ROUND(123.453836935476, 2);

결과 : 24 / 123.45

-- TRUNCATE(A, B) : A를 소수점 B째 자리까지 남기고 버림
SELECT TRUNCATE(123.4535136, 2);

결과 : 123.45

SELECT POW(2, 16);

결과 : 65536

SELECT SQRT(8);

결과 : 2.8284271247461903

-- MOD(A, B) : A를 B로 나눈 나머지
SELECT MOD(21, 5);

결과 : 1

-- RAND()*100 : 0~100까지의 난수 랜덤 리턴
SELECT FLOOR(RAND()*100);

결과 : 0~100사이의 값 중 정수값을 랜덤 리턴 (FLOOR로 소수점 버림)

SELECT SIGN(2311);
SELECT SIGN(0);
SELECT SIGN(-3168316);

결과 : 1 / 0 / -1

SELECT GREATEST(1, 7, 103, 5);
SELECT LEAST(1, 7, 103, 5);

결과 : 103 / 1

뷰(View)란?

데이터베이스에 존재하는 가상테이블을 말한다.

실제의 테이블처럼 행과 열을 가지고 있지만 실제로 데이터를 저장하고 있지는 않다.

다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만 수행한다.

뷰를 사용하면 여러 테이블, 뷰를 하나의 테이블처럼 볼 수 있다.

장점

특정 사용자에게 테이블 전체가 아닌 필요한 필드만 보여준다. (보안 측면)
복잡한 쿼리를 단순화해서 사용할 수 있다.
재사용성이 증가한다.

단점

한번 정의된 뷰는 변경이 불가능하다.

삽입, 삭제, 갱신에 많은 제약사항이 있다.
자신만의 인덱스를 가질 수 없다.

일반 테이블보다 검색 속도가 느리다.

사용하기

-- CREATE VIEW 뷰 이름 AS
CREATE VIEW 매니저들 AS
SELECT E.ENAME, E.EMPNO, E.JOB, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.JOB='MANAGER' AND D.DEPTNO=30;

SELECT * FROM 매니저들;

대체하기 (REPLACE)

기존에 생성했던 뷰를 새로운 뷰로 대체한다.

-- CREATE OR REPLACE VIEW 뷰 이름 AS
CREATE OR REPLACE VIEW 30번부서 AS
SELECT E.ENAME, E.EMPNO, E.JOB, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.JOB='MANAGER' AND D.DEPTNO=30;

SELECT * FROM 30번부서;

조회가 잘 된다.

컬럼, 테이블, 서브 쿼리, WHERE 절 등에 원하는 별칭을 붙여주는 것이 ALIAS이다.

• 테이블의 명칭이 너무 길 때 줄여서 사용하면 편리하다.
• 현재의 SELECT 문장에서만 유효하다.
• FROM에서 테이블에 설정하면 해당 테이블 ALIAS는 SELECT에서 테이블명 대신 사용할 수 있다.
• 별칭에 공백이 있다면 ' ', " " 처리해야 한다
• 예약어 AS가 없어도 별칭을 지정할 수 있다.
• WHERE 절에서는 사용 불가능하다. ( 쿼리문을 실행할 때 WHERE절이 SELECT절 보다 먼저 실행되기 때문 )

SELECT 컬럼명 AS 별칭
SELECT 함수 AS "공 백 별 칭"
SELECT (서브쿼리) AS '공 백 별 칭'

-- AS가 없이도 별칭을 지정할 수 있다.
SELECT BOY_GROUP AS 보이그룹
  FROM SINGER;
SELECT GIRL_GROUP 걸그룹
  FROM SINGER;

-- 예시
-- 직업이 매니저이고, 부서번호가 30인 사람들의 사원명, 사원번호, 직업, 부서번호 조회
SELECT E.ENAME, E.EMPNO, E.JOB, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.JOB='MANAGER' AND D.DEPTNO=30;

[  DML(Data Manipulation Language) - 데이터 조작어  ]

데이터를 조회하거나 삽입, 갱신, 삭제할 수 있다.

데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위를 트랜잭션(Transaction)이라고 하며, DML은 트랜잭션을 다루는 명령어라고 할 수 있다. 데이터의 CRUD를 DML을 통해 수행한다고 할 수 있다.

다음의 테이블로 DML 명령어들을 사용해보자

CREATE TABLE EXAM1(
	ID INT(5) NOT NULL AUTO_INCREMENT,
	SUBJECT VARCHAR(20) NOT NULL,
    CONTENT VARCHAR(20) NOT NULL,
    CREATE_DATE DATETIME,
    PRIMARY KEY(ID)
);

INSERT

테이블에 새로운 컬럼을 삽입할 때 사용한다.

INSERT INTO EXAM1 VALUES(1, '저녁메뉴추천좀', '냉무', CURDATE());
-- 가능하면 아래의 방법처럼 컬럼의 이름을 항상 적는것이 좋다.
INSERT INTO EXAM1(CONTENT, SUBJECT, CREATE_DATE) VALUES('배고프다', '뭐먹지', CURDATE());

UPDATE

테이블의 데이터를 수정(변경)할 때 사용한다.

UPDATE EXAM1
SET SUBJECT = '저녁사실먹었음'
WHERE ID = 1;

DELETE

WHERE절을 사용하지 않으면 모든 데이터가 삭제된다.

DELETE FROM EXAM1
WHERE ID=1;

SELECT

생성되어 있는 테이블에서 원하는 값을 조회할 때 사용한다.

-- Asterisk(*) 기호를 사용하여 해당 테이블의 모든 필드를 선택할 수 있다.
 SELECT *
   FROM 테이블명

-- WHERE절의 조건을 사용하면, 선택할 튜플의 조건을 상세히 설정 가능하다.
SELECT 필드이름
  FROM 테이블이름
 WHERE 조건

-- BETWEEN a AND b : a 이상 b 이하의 값을 출력
SELECT * 
  FROM EMP
 WHERE SAL BETWEEN 800 AND 2450;

-- IN : IN 안의 값과 일치하는 데이터 출력
SELECT *
  FROM EMP
 WHERE EMPNO IN(7900, 7934);
 
-- LIKE : 문자 형태로 일치하는 데이터 출력 - 종류가 많아 따로 정리하겠다.
-- EMP 테이블에서 'K'라는 문자가 들어있는 사원 정보 조회
SELECT *
  FROM EMP
 WHERE ENAME LIKE "%K%";
 

-- IS NULL : NULL값을 가진 데이터를 출력
-- NOT between a and b : a와 b 사이에 없는 데이터를 출력
-- NOT IN(LIST) : LIST값과 일치하지 않는 데이터를 출력
-- NOT LIKE : 문자형태와 일치하지 않는 데이터를 출력
-- IS NOT NULL : NULL값을 갖지 않는 데이터를 출력

또한 SELECT 문의 마지막에 ORDER BY 절을 통해 정렬이 필요한 데이터를 정렬할 수 있다.-- ASC(오름차순), DESC(내

-- ASC : 오름차순, DESC : 내림차순
SELECT *
  FROM EMP
 ORDER BY EMPNO(ASC, DESC)
 
-- ALIAS 사용
SELECT SAL+COMM AS TOTAL
  FROM EMP
 ORDER BY TOTAL;
 
-- 컬럼의 숫자 사용
-- ( 2번째 열을 기준으로 정렬하기 )
SEELCT COMM
  FROM EMP
 ORDER BY 2;
 
-- 여러 컬럼을 기준으로 사용
-- ( 3번째 열을 기준으로 오름차순한 후, 1번째 열을 기준으로 내림차순 정렬 )
SELECT *
  FROM EMP
 ORDER BY 3,1 DESC

[  CREATE (생성)  ]

DB 생성

MySQL에서는 CREATE문을 사용하여 데이터베이스와 테이블을 만들 수 있다.

CREATE DATABASE 데이터베이스이름

해당 데이터베이스를 사용하기 위해서는 USE문을 사용하여 사용할 데이터베이스를 선택해야 한다.

USE 데이터베이스이름

테이블을 만들기 위해서 CREATE TABLE 키워드를 사용한다.

CREATE TABLE 테이블이름
(	컬럼명 데이터타입 조건,
	컬럼명 데이터타입 조건,
    ...
)

테이블 생성 시 주의사항

- 객체를 의미할 수 있는 적절한 이름을 사용해야 한다.
- 다른 테이블의 이름과 중복되지 않도록 지정해야 한다.
- 한 테이블 내에서는 컬럼명이 중복되게 지정할 수 없다.
- 테이블 이름을 지정하고 각 컬럼들은 괄호로 묶어서 지정한다.
- 각 컬럼들은 , 로 구분되고, 테이블 생성문의 끝은 ; 기호로 맺는다.
- 컬럼뒤에 데이터 유형은 꼭 지정되어야 한다.
- 테이블 명과 컬럼 명은 반드시 문자로 시작한다.
- 벤더사마다 사전에 정의한 예약어는 사용 불가능하다.
- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

컬럼에 대한 규칙

- DATETIME 유형에는 별도로 크기를 지정하지 않는다.
- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
- 컬럼의 구분은 , 로 하되 마지막 컬럼은 콤마를 찍지 않는다.
- 컬럼에 대해서 CONSTRAINT 를 이용하여 제약조건을 추가 가능하다.

데이터타입

컬럼에 지정할 수 있는 데이터타입의 종류는 숫자형, 문자, 시간형 등이 있고 대표적인 것만 정리했다.

제약 조건

데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사 규칙이다.

CREATE, ALTER에 사용 가능하다.

테이블 생성

CREATE DATABASE EXAM;

USE EXAM;

CREATE TABLE EXAM1(
	ID INT(5) NOT NULL AUTO_INCREMENT,
    CONTENT VARCHAR(30) NOT NULL,
    CREATE_DATE DATETIME,
    PRIMARY KEY(ID)
);

-- 만약 동일한 테이블을 추가로 생성하고 싶다면 아래와 같이 작성하면 된다.
CREATE TABLE EXAM2 AS
SELECT * FROM EXAM1 WHERE 1=2;

기본 키(PK)값이 ID인 EXAM1 테이블을 생성했다.

[  ALTER (수정)  ]

ALTER문을 사용하여 데이터베이스와 테이블의 컬럼을 추가, 삭제 및 변경할 수 있다.

사용되는 키워드는 다음과 같다

CHANGE, MODIFY에 의해 데이터 타입이 변경될 때 데이터의 손실이 발생할 수 있다.

추가하기

ALTER TABLE EXAM1 ADD COLUMN SUBJECT VARCHAR(20) NOT NULL;
-- FIRST, SECOND, THIRD, BEFORE, LAST등을 사용하여 순서 지정 가능.
ALTER TABLE EXAM1 ADD COLUMN SUBJECT VARCHAR(20) NOT NULL AFTER `ID`;

삭제하기

ALTER TABLE CUSTOMER DROP COLUMN SUBJECT;

변경하기

-- ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입
ALTER TABLE EXAM1 MODIFY COLUMN CONTENT INTEGER;
ALTER TABLE EXAM1 MODIFY COLUMN CONTENT INTEGER AFTER CREATE_DATE;

-- ALTER TABLE 테이블명 CHANGE 기존컬럼명 변경할컬럼명 데이터타입
ALTER TABLE EXAM1 CHANGE COLUMN CONTENT CONTINENT VARCHAR(20);

-- ALTER TABLE 테이블명 RENAMTE TO 변경할테이블명;
ALTER TABLE EXAM1 RENAME TO EXAM3;

삭제하기

-- 컬럼 제거
ALTER TABLE EXAM1 DROP COLUMN CONTINENT;

[  DROP, TRUNCATE (삭제)  ]

DROP : 데이터베이스와 테이블을 삭제할 수 있다.

TRUNCATE : 테이블의 데이터만을 지우고 싶을 때 사용한다.

-- DROP
DROP DATABASE DB이름
DROP TABLE 테이블이름

-- TRUNCATE
TRUNCATE TABLE 테이블이름

-- IF EXISTS 절을 사용해 삭제하려는 DB나 테이블이 존재하지 않을 때
-- 발생하는 에러를 미리 방지할 수 있다.
DROP DATABASE IF EXISTS DB명
DROP TABLE IF EXISTS 테이블명

[  기본 문법  ]

SELECT * FROM EXAM;
select * from EXAM;
SElecT * FrOM EXAM;

세미콜론을 기준으로 구문을 구분한다

키워드와 구문에서 대문자, 소문자를 구분하지 않는다.

대소문자를 일관되게 사용하는 것이 가독성 측면에서 유리할 것이다.
또한 테이블 명과 필드의 이름은 대소문자를 구분하기 때문에 주의해서 사용하자.

# 주석
-- 주석
/* 주석
   주석 */

위의 세 가지 방법으로 주석처리를 할 수 있으며, " -- " 뒤에는 반드시 공백이 한 칸 존재해야 한다.

두 줄 이상의 주석을 사용할 경우 세 번째 방법으로 사용하면 되지만 주석을 원하는 곳을 모두 드래그 한 다음 "Ctrl+/" 을 하는 방법도 있다.