기본 sql문 정리

u·2022년 4월 26일

Database

목록 보기

6/7

기본 데이터 타입

DDL (Data Definition Language)

use

USE db_name

create

CREATE [OR REPLACE] {DATABASE | TABLE} [IF NOT EXISTS] db_name;

[OR REPLACE]와 [IF NOT EXISTS]를 동시에 쓰는 것은 불가능

drop

DROP {DATABASE | TABLE} IF EXISTS db_name

describe

DESCRIBE table_name

alter

column

# add
ALTER TABLE table_name ADD COLUMN col_name column_definition;

# drop
ALTER TABLE table_name DROP COLUMN col_name;

# rename
ALTER TABLE table_name RENAME COLUMN col_name1 to col_name2;

#modify - 데이터 타입 바꾸기
ALTER TABLE table_name MODIFY [COLUMN][IF EXISTS] col_name column_definition;

Integrity constraints : guarantee to keep data consistency

check(<predicate>)
not null
unique - can be null
primary key - unique & null
foreign key

DML (Data Manipulation Language)

insert

INSERT INTO TABLE table_name VALUES(),(),(),....;

delete

DELETE FROM table_name 
[WHERE where_condition] 
[ORDER BY ...]
[LIMIT row_count]

update

UPDATE table_name SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}]...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

BIF (Built-In Function)

String Functions

CHAR_LENGTH()
CHARACTER_LENGTH()

LENGTH()

CHAR()
CHR()

CONCAT(str1, str2, ...)
CONCAT_WS(separator, str1, str2, ...)

문자에만 적용됨

LCASE()
LOWER()
UCASE()
UPPER()

조건에 따라 양끝 문자를 자른다.

TRIM([ { BOTH | LEADING | TRAILING} [remstr] FROM ] str)

SELECT TRIM(LEADING FROM stringValue) FROM bif;

SUBSTRING()

# 문자열의 인덱스는 1부터 시작이다 - 아래 명령어는 1번째 문자로부터 2개의 문자를 출력하라는 뜻)
SELECT SUBSTRING(stringValue, 1, 2) FROM bif;

특정 문자열을 정해진 문자열로 전부 바꾼다

REPLACE()

SELECT REPLACE(col, from, to) FROM bif;

LOCATE()

Returns the position of a string within a string - 없으면 0, Null 일땐 Null

INSTR()

SELECT INSTR(stringValue, 'a') FROM bif;

POSITION

Compares two strings in sort order

STRCMP(expr1, expr2)
1 : expr1 > expr2 (ex. b > a, a > 1)
0 : 같음
-1 : expr1 < expr2 (ex. a < b)

타입변환

CAST(expr AS type)

정수를 N진법으로 변환

HEX()
OCT()
BIN()

Date Functions

CURDATE() - DATE 자료형 - 날짜 출력
CURTIME() - TIME 자료형 - 현재 시간 출력

INTERVAL time_quantity time_unit - 시간,날짜 조작

#- INSERT ONE ROW  where its dateValue is Yesterday
INSERT INTO bif VALUES(null,null,CURDATE() + INTERVAL 1 DAY);
# 날짜의 하루 전 조회
SELECT dateValue - INTERVAL 1 DAY FROM bif;

CONVERT_TZ(dt, from_tz, to_tz) - 시간대 변경
SELECT CONVERT_TZ(dateValue, "+9:00", "+00:00") FROM bif;

UNIX_TIMESTAMP() - 1970년부터 지나간 시간을 1초 단위로 반환
FROM_UNIXTIME() - FROM_UNIXTIME(0) 입력 시 1970-01-01 반환

YEAR() (4자리 반환)
MONTH() (1~12)
DAYOFMONTH() (SUNDAY = 1)
DAYOFWEEK()

HOUR(), -> 시간반환
MINUTE(),
SECOND()

STR_TO_DATE("str", "str_format") -> string 형태를 date 타입으로 변환

SELECT STR_TO_DATE("2021-03-21", "%Y-%c-%e);
SELECT STR_TO_DATE("April 7, 2022", "%M %e, %Y");

# April 07, 2022의 요일은?
# 5 -> 목요일
SELECT DAYOFWEEK(str_to_date("April 07, 2022", "%M %d, %Y"));

DATE_FORMAT(DATE, "str_format") -> DATE 타입을 원하는 string 형태로 표현

SELECT DATE_FORMAT(CURDATE(), "%W %M %Y");

AVG, MAX, MIN
STD, SUM, VARIANCE

BIT_AND, BIT_OR, BIT_XOR

# bitValue 타입은 int, tinyint, ... 가능
insert into bif set bitvalue = b'1001'; 
insert into bif set bitvalue = b'1011';
insert into bif set bitvalue = b'0010';
# 비트연산 -> 입력 : (정수 | 비트), 출력 : (정수)
SELECT BIT_AND(bitValue), BIT_OR(bitValue), BIT_XOR(bitValue) FROM bif;

COUNT, COUNT_DISCTINCT

JSON_OBJECTAGG(key,value)

SELECT JSON_OBJECTAGG(stringValue, intValue);

+,-,/,*,%
POW(a, r), SQRT(a, r)

FLOOR(), CEILING()
RAND() -> 0<=x<1

#generate 5 to 15
SELECT FLOOR(5+RAND()*11)

CASE OPERATOR - CASE value WHEN [compare_value] THEN result [ELSE result] END;

SELECT CASE WHEN integerValue >= 90 THEN 'A' WHEN integerValue >= 80 THEN 'B' ELSE 'C' END FROM bif;

IF(expr1, expr2, expr3)

SELECT intValue, IF(intValue>=80, "PASS", "FAIL") FROM bif WHERE intValue >= 0;

SHA2(str, hash_len) - hash_len : 224, 256, 384, 512
데이터베이스에 저장될 때 해싱돼서 저장된다
효과 : 관리자로부터 사용자의 정보를 보호하고 해킹 시 데이터 보호 가능

SELECT SHA2(stringValue, 256) FROM bif;
INSERT INTO bif set stringValue = SHA2("str", 256);

PASSWORD(str)

INSERT INTO bif SET passwordValue = PASSWORD('12345');
SELECT passwordValue,IF(passwordValue = PASSWORD('12345'),1,0) FROM bif;

ENCODE(str, pw_str)
DECODE(encoded, pw_str)

COMPRESS(str) - returns a binary, compressed string
UNCOMPRESS(str) - reuncompress binary to string

select uncompress(compress("123"));

POINT
-PointFromText()
-AsText()
-X
-Y
-ST_DISTANCE()

INSERT INTO bif SET geoValue = PointFromText("Point(10 10)");
SELECT AsText(geoValue), X(geoValue), Y(geoValue) FROM bif;
SELECT ST_DISTANCE(geoValue, PointFromText("Point(0 0)");
SELECT ST_DISTANCE(PointFromText("Point(10 10)"), PointFromText("Point(0 0)")) as dist;

JSON


CREATE TABLE j1 (j JSON);

INSERT INTO j1 VALUES({"name":"jack","ID":"615458","temperature":"36.5"});
INSERT INTO j1 VALUES({"name":"james","ID":"123456","temperature":"36.7"});
INSERT INTO j1 VALUES({"name":"james","ID":"432156","temperature":"37.0"});

# 데이터 값 반환
SELECT JSON_VALUE(j,"$.temperature") FROM j1;
# 데이터 타입을 보여준다
SELECT JSON_TYPE(JSON_VALUE(j, "$.temperature")) FROM j1;
# 해당 컬럼이 존재하는 지 확인
SELECT JSON_EXISTS(j, '$.temperature') FROM j1; 
# 파싱해서 보여준다
SELECT JSON_DETAILED(j) FROM j1; 
# 키 목록 반환
SELECT JSON_KEYS(j) FROM j1;
# 키 개수 반환
SELECT JSON_LENGTH(j) FROM j1;