[Database] 데이터 타입(숫자, 문자)와 연산자, 검색 패턴

타입(data type)

✅ 숫자

테이블의 타입 확인 : describe 테이블명;

1. 정수

(1) TINTINT : -128 ~ 127범위. 1바이트. java의 byte와 매핑

(2) INT : 4바이트. java의 int타입과 매핑

(3) BIGINT : 8바이트. java의 long타입과 매핑

(4) UNSIGNED 타입을 사용하여 양수만 표현 가능. 표현 값 2배로 증가.

ex) TINTINT UNSIGNED 사용 시 숫자 범위 : 0 ~ 255

2. 실수

(1) 부동 소수점 타입

FLOAT, DOUBLE -> 오차가 발생할 여지가 있음

(2) 고정 소수점 타입

DECIMAL(M, D)

java의 Bigdecimal

M은 총자릿수(정수부+소수부)를 의미하고, 65자리까지 표현가능, D는 소수부 의미

정확한 숫자 표현을 위해 사용

ex) create table post(price DECIMAL(10, 3);

정수+실수 총 10자리수 사용, 실수는 3자리수

⭐FLOAT, DOUBLE은 이진수로 바꿔 변환하는 과정에서 오차가 생긴다

⭐ FLOAT, DOUBLE 쓰던 습관에서 DECIMAL 쓰는 습관으로 바꾸자!

<타입 실습과제>

1. author 테이블에 age 데이터 tinyint unsigned로 추가

255이하로 insert 후 255초과로 insert 테스트

ALTER TABLE author ADD COLUMN age TINYINT UNSIGNED;

INSERT INTO author(id, name, email, password, role, address, age) VALUES(1, '유정', '유정@naver.com', '111', 'manager', '1동 1호', 100);

INSERT INTO author(id, name, email, password, role, address, age) VALUES(2, '윶엉', '윶엉@naver.com', '222', 'user', '2동 2호', 933);

-> Error Code: 1264. Out of range value for column 'age' at row 1 0.000 sec -> 값 삽입 안됨

2. post에 price라는 원고료컬럼을 추가. 원고료 컬럼은 총자릿수 10자리 및 소수점 단위는 3자리까지 가능

컬럼 추가 후 비어있는 컬럼에 숫자 update test(소숫점 3이하, 소수점3초과 test)

ALTER TABLE post ADD COLUMN price DECIMAL(10, 3);

UPDATE post SET price=10.110 WHERE contents='건강';

UPDATE post SET price=10.110444 WHERE contents='운동';

-> 2 row(s) affected, 2 warning(s): 1265 Data truncated for column 'price' at row 3 1265 Data truncated for column 'price' at row 4 Rows matched: 2 Changed: 2 Warnings: 2 0.016 sec -> 소수점 4번째 자리에서 반올림해서 삽입됨

✅ 문자

1. CHAR(255)

고정 길이의 문자열(0~255까지 설정가능)

정해진 자릿수 문자에 제한을 두기 위해서는 사용

ex) sex CHAR(1) 지정해두고 '남', '여'같이 정해진 값을 넣을 때 사용됨

2. VARCHAR(255)

가변 길이의 문자열(0~65.535까지 설정가능)

길이를 지정하더라도 실제 입력된 문자열의 길이만큼만 저장하고 사용

ex) name VARCHAR(255) 지정해두고 'yj'만 입력하면 2byte의 크기만 차지함

3. TEXT

가변 길이의 문자열

65,535바이트 저장 가능한 일반적인 문자열을 위한 타입

VARCHAR보다 더 큰 범위를 표현하고 싶으면 LONGTEXT 사용 (LONGTEXT는 4GB)

DISK에 저장해서 조회속도가 VARCHAR(메모리저장)에 비해 느림

⭐ INDEX 사용이 어려움(B-Tree 인덱싱 불가, Full-Text 인덱스 가능)

🎈 VARCHAR vs TEXT

VARCHAR는 B-TREE 인덱싱이 가능하지만,

B-TREE 인덱싱이 안되고 모두 조회하는 Full-TEXT 인덱싱을 하기 때문에 느리다

4. BLOB

다양한 크기의 바이너리 데이터를 저장할 수 있는 타입

일반적으로 PNG와 같은 이미지파일을 저장할때 지정하는 타입

ex)

create table table_blob(id int, myimg blob);

INSERT INTO table_blob (id, myimg) VALUES(1, LOAD_FILE('C:\\test_picture.jpeg'));

SELECT HEX(myimg) FROM table_blob WHERE id=1;

5. ENUM

미리 들어갈 수 있는 특정 데이터의 값을 지정

컬럼명 ENUM('데이터값1', '데이터값2', ...)

⭐ NOT NULL DEFAULT 'user'등의 옵션도 추가 가능

role타입을 enum타입으로 변경, 'user', 'admin'으로 enum 타입 지정.

NOT NULL로 설정하되, 입력이 없을 시에는 'user'로 세팅되도록 defualt 설정

UPDATE author SET role='user' WHERE role='manager';

ALTER TABLE author MODIFY COLUMN role ENUM('user', 'admin') NOT NULL DEFAULT 'user';

admin으로 데이터 세팅 후 INSERT TEST

super-user 데이터로 INSERT TEST

role 데이터 없이 INSERT TEST

INSERT INTO author(id, name, email, password, role, address, age) VALUES(2, 'bbb', 'bbb@naver.com', 'bbb', 'admin', 'b동 b호', 22);

INSERT INTO author(id, name, email, password, role, address, age) VALUES(3, 'ccc', 'ccc@naver.com', 'ccc', 'super-user', 'c동 c호', 33);

-> Error Code: 1265. Data truncated for column 'role' at row 1 0.000 sec

INSERT INTO author(id, name, email, password, address, age) VALUES(4, 'ddd', 'ddd@naver.com', 'ddd', 'd동 d호', 33);

-> NOT NULL DEFAULT 옵션으로 'user'로 자동으로 채워짐

6. DATE ⭐ ⭐

- DATE

날짜를 저장할 수 있는 타입

YYYY-MM-DD

- DATETIME(m)

날짜와 함께 시간까지 저장, m 지정시 소수점 microsecnods

YYYY-MM-DD HH:MM:SS => %Y %m %d %H %i %s로 쓰임

가장 많이 사용

java의 localdatetime과 sync가 맞다

- DATETIME DEFAULT CURRENT_TIMESTAMP

현재 시간을 default로 삽입하는 형식.

해당 컬럼이 언제 INSERT, UPDATE 됐는지 기록할 때 자주 사용됨

1. post 테이블에 DATETIME으로 created_time 컬럼 추가 및 default로 현재 시간 들어가도록 설정

ALTER TABLE post ADD COLUMN created_time datetime(6) default current_timestamp(6);

컬럼 추가 후 INSERT TEST

INSERT INTO post(id, created_time) VALUES(6, '2023-11-17');

연산자

✅ 비교 연산자

1. =

2. !=, <>

3. <. <=, >, >=

4. IS NULL, IS NOT NULL

5. BETWEEN min AND max : 피연산자의 값이 min값 이상 max값 이하인 값들을 반환함.

6. IN(), NOT IN()

✅ 논리 연산자

1. AND (&&)

2. OR (||)

3. NOT (!)

<논리 연산자 실습>

1. author 테이블의 id가 1, 2, 4는 아닌 데이터 조회 (NOT IN 사용)

SELECT * FROM author WHERE id NOT IN(1, 2, 4);

2. post 테이블의 id가 2~4까지 데이터 조회 (BETWEEN, AND, OR 조건 활용)

(1) BETWEEN AND

SELECT * FROM post WHERE id BETWEEN 2 AND 4;

(2) AND

SELECT * FROM post WHERE id>=2 AND id<=4;

(3) OR

SELECT * FROM post WHERE id=2 OR id=3 OR id=4;

SELECT * FROM post WHERE NOT (id<2 OR id>4);

✅ 검색 패턴

1. LIKE

특정 패턴을 포함하는 데이터만을 검색하기 위한 와일드카드(wildcard) 문자

일반적으로 %와 함께 사용됨

ex) SELECT * FROM author WHERE name LIKE '신%';

2. NOT LIKE

3. REGEXP (Regular Expression의 약자)

정규표현식을 토대로 패턴 연산 수행

ex) SELECT * FROM author WHERE name REGEXP '[a-z]';

SELECT * FROM author WHERE name REGEXP '[가-힣]';

4. NOT REGEXP

출처 : https://inpa.tistory.com/entry/JAVA-%E2%98%95-%EC%A0%95%EA%B7%9C%EC%8B%9DRegular-Expression-%EC%82%AC%EC%9A%A9%EB%B2%95-%EC%A0%95%EB%A6%AC#string_%ED%81%B4%EB%9E%98%EC%8A%A4%EC%9D%98_%EC%A0%95%EA%B7%9C%EC%8B%9D_%EB%AC%B8%EB%B2%95

자바 String에서 숫자, 특수문자, 문자 등을 빼거나 다른 것으로 치환할 때 자주 쓰인다.

String txt = "hi^@*-";
String result = txt.replaceAll("[^a-z0-9]",""); //숫자, 문자가 아닌 것은 제외

5. CAST

CAST(a AS type)

보통 정수 값을 DATE 타입으로 변환하는데 사용

ex) SELECT CAST(20200101 AS DATE); => 2020-01-01

6. CONVERT

- CONVERT

문자열을 날짜/시간으로 변환하는 데 사용

SELECT CONVERT('2020-01-01', DATE) => 2020-01-01 DATE 타입으로 변환

SELECT CONVERT('2020-01-01 12:00:00', DATETIME) => 2020-01-01 12:00:00 DATETIME 타입으로 변환

- DATE_FORMAT ⭐ ⭐

DATE_FORMAT 함수는 날짜/시간 타입의 데이터를 지정된 형식의 문자열로 변환

가장 많이 사용

ex) SELECT DATE_FORMAT('2020-01-01 17:12:00', '%Y-%m-%d'); => 2020-01-01

SELECT * FROM post WHERE DATE_FORMAT(created_time, '%Y-%m-%d') = '2023-11-17';

(+) Date Format 구분 기호

출처 : https://jjunn93.com/entry/QueryDSL-DATEFORMAT-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0

CAST, CONVERT 사용 시 유의사항

- 최신버전

CAST('123' as INT) 방식으로 ing 사용 가능

CAST('123' as signed) 방식으로 signed(또는 unsigned) 사용 가능

- 구버전

CAST('123' as signed) 방식으로 signed(또는 unsigned) 사용 가능

여기서 signed는 부호있는 정수, 즉 음수/양수 모두 포함

unsigned는 부호 없는 정수로서 0이상 양수를 의미

모든 업데이트 버전은 구버전 위에 추가되기 때문에 구버전을 알고 있으면 버전 관계없이 돌아갈 수 있음

특정 기간, 날짜 조회

-날짜 데이터 조회하는 방식 중 많이 사용하는 방식

1. DATE_FORMAT(date, format)을 활용한 조회

Y, mm, dd, H, i, s

2. LIKE를 사용하여 문자열 형식으로 조회

SELECT * FROM post WHERE created_time LIKE '2023%';

3. BETWEEN 연산자

특정 날짜 범위를 지정하여 데이터를 검색

4. 날짜 비교 연산자

WHERE created_time >= '2021-01-01' AND created_time <= '2021-11-17'

오늘 날짜 관련 함수

now()

<실습>

1. date_format, like, between, 비교연산자를 각각 사용하여 2023년에 생성된 데이터 출력

SELECT * FROM post WHERE DATE_FORMAT(created_time, '%Y')='2023';

SELECT * FROM post WHERE created_time LIKE '2023%';

SELECT * FROM post WHERE created_time BETWEEN '2023-01-01' AND '2023-12-31';

SELECT * FROM post WHERE created_time >= '2023-01-01' <= '2023-12-31';

2. now()를 활용해서 오늘날짜에 생성된 데이터 출력하기

SELECT * FROM post WHERE DATE_FORMAT(created_time, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d');

저작자표시

'Back-End 공부 > Database' 카테고리의 다른 글

[Database] INDEX와 VIEW 생성, 조회, 삭제 방법, 사용자 관리 방법 (0)	2023.11.22
[Database] 테이블 JOIN 종류와 사용 방법 (1)	2023.11.21
[Database] 트랜잭션 정의와 특징, TCL(COMMIT, ROLLBACK) (0)	2023.11.21
[Database] 제약조건 및 흐름제어 (1)	2023.11.20
[Database] DBMS 정의와 종류, SQL 문법(DDL, DML, TCL) (0)	2023.11.16

어찌저찌 얼렁뚱땅 개발일지

[Database] 데이터 타입(숫자, 문자)와 연산자, 검색 패턴

✅ 숫자