๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

SQL3

[TIL] 221028 - ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค MySQL ๊ณ ๊ธ‰๊ธฐ๋Šฅ ์‹ค๋ฆฌ์ฝ˜ ๋ฐธ๋ฆฌ์—์„œ ๋‚ ์•„์˜จ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค - 5. MySQL ๊ณ ๊ธ‰๊ธฐ๋Šฅ ์‚ดํŽด๋ณด๊ธฐ 1. ํŠธ๋žœ์žญ์…˜ ์†Œ๊ฐœ ํŠธ๋žœ์žญ์…˜ - Atomic ํ•˜๊ฒŒ ์‹คํ–‰๋˜์–ด์•ผ ํ•˜๋Š” SQL๋“ค์„ ๋ฌถ์–ด์„œ ํ•˜๋‚˜์˜ ์ž‘์—…์ฒ˜๋Ÿผ ์ฒ˜๋ฆฌํ•˜๋Š” ๋ฐฉ๋ฒ• : ํ…Œ์ด๋ธ” ๋‚ด์šฉ์„ ๋ณ€๊ฒฝํ•˜๋Š” SQL์ด ์—ฐ๋‹ฌ์•„ ์‹คํ–‰๋˜๋ฉฐ ๋งˆ์น˜ ํ•˜๋‚˜์˜ SQL์ฒ˜๋Ÿผ ์ „๋ถ€ ์„ฑ๊ณตํ•˜๊ฑฐ๋‚˜ ์ „๋ถ€ ์‹คํŒจ ๋˜์–ด์•ผํ•œ๋‹ค. (COMMIT์„ ์•ˆํ•˜๊ณ  ํŠธ๋žœ์žญ์…˜์„ closeํ•˜๋ฉด ๋ชจ๋‘ ๋ฌดํšจํ™” → rollback์ด๋ž‘ ๋™์ผํ•œ ํšจ๊ณผ) : SELECT์—์„œ๋Š” ์‚ฌ์šฉํ•  ์ด์œ ๊ฐ€ ์—†์Œ : BEGIN/END ํ˜น์€ BEGIN/COMMIT ์‚ฌ์ด์— ํ•ด๋‹น SQL๋“ค์„ ์‚ฌ์šฉํ•˜๋ฉฐ ROLLBACK atomicํ•˜๊ธฐ๋•Œ๋ฌธ์— ๋™๊ธฐํ™” ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•œ๋‹ค. ํŠธ๋žœ์žญ์…˜ ์ปค๋ฐ‹ ๋ชจ๋“œ : autocommit autocommit = True : ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์ž‘์—…์ด ๊ธฐ๋ณธ์ ์œผ๋กœ ๋ฐ”๋กœ ์ปค๋ฐ‹๋œ๋‹ค. .. 2022. 10. 28.
[TIL] 221027 - ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค SQL : JOIN ์‹ค๋ฆฌ์ฝ˜๋ฐธ๋ฆฌ์—์„œ ๋‚ ์•„์˜จ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค - 4. SQL ๊ณ ๊ธ‰ ๋‹ค๊ธฐ์ง€(JOIN) 1. INSERT / UPDATE / DELET MySQL ์ปฌ๋Ÿผ ํƒ€์ž… Numeric Type INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT DECIMAL, NUMERIC FLOAT, DOUBLE, BIT Date and Time DATE, DATETIME, TIMESTAMP, TIME, YEAR String Type CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET JSON Type : ๋‹ค์–‘ํ•œ JSON ์กฐ์ž‘ํ•จ์ˆ˜ ์ œ๊ณต Spatial Type : ์œ„๋„ ๊ฒฝ๋„ ์ค‘์‹ฌ ์œ„์น˜ ๊ด€๋ จ ํƒ€์ž… INSERT INTO _ VALUES(_) : primary ke.. 2022. 10. 28.
[TIL] 221026 - ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค SQL : SELECT, GROUP BY ์‹ค๋ฆฌ์ฝ˜๋ฐธ๋ฆฌ์—์„œ ๋‚ ์•„์˜จ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค - 3. SQL ๊ธฐ๋ณธ๋‹ค์ง€๊ธฐ(SELECT์™€ GROUP BY) 1. ์‹ค์Šตํ™˜๊ฒฝ SQL Workbench๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ SQL ์‹ค์Šต SQL Workbench : MySQL ์‚ฌ์ดํŠธ์—์„œ ์ œ๊ณตํ•ด์ฃผ๋Š” ๋ฌด๋ฃŒ ํด๋ผ์ด์–ธํŠธ SQL ์—๋””ํ„ฐ ์„ค์น˜ connection ๋งŒ๋“ค๊ธฐ (์ž‘์—…์ด ์ง„ํ–‰๋  ์„œ๋ฒ„ - rds) ๋“ค์–ด๊ฐ€๊ธฐ์ „ SHOW DATABASES; USE prod; SHOW TABLES; 2. SELECT ํ…Œ์ด๋ธ”์—์„œ ๋ ˆ์ฝ”๋“œ๋ฅผ ์ฝ์–ด์˜ค๋Š”๋ฐ ์‚ฌ์šฉ CASE WHEN : ํ•„๋“œ ๊ฐ’์˜ ๋ณ€ํ™˜์„ ์œ„ํ•ด ์‚ฌ์šฉ ๊ฐ€๋Šฅ CASE WHEN ์กฐ๊ฑด THEN ์ฐธ์ผ ๋•Œ ๊ฐ’ ELSE ๊ฑฐ์ง“์ผ๋•Œ ๊ฐ’ END ํ•„๋“œ์ด๋ฆ„ NULL : null์— ์‚ฌ์น™์—ฐ์‚ฐ์„ ํ•˜๋ฉด null IN / LIKE : ๋ฌธ์ž์—ด ๋งค์นญ / BETWEEN STRING function.. 2022. 10. 26.