[SQL] ์ค‘๊ธ‰ ๋ฌธ๋ฒ•๐Ÿƒ๐Ÿปโ€โ™€๏ธ

Arielยท2023๋…„ 2์›” 19์ผ
2

SQL

๋ชฉ๋ก ๋ณด๊ธฐ
2/2

4. SQL ์ค‘๊ธ‰ ๋ฌธ๋ฒ•


  • SELECT (ํ…Œ์ด๋ธ” ์กฐํšŒ)
    1. ๋ฐ์ดํ„ฐ ์ง‘๊ณ„
    ๐Ÿ’กas๋ฅผ ์ด์šฉํ•˜๋ฉด ์ปฌ๋Ÿผ๋ช… ๋ณ€๊ฒฝ ๋˜๋Š” ์ง€์ • ๊ฐ€๋Šฅ
    ๐Ÿ’กํŠน์ • ์ปฌ๋Ÿผ์— ๋Œ€ํ•œ ์ง‘๊ณ„ ๋ฐ์ดํ„ฐ ์กฐํšŒ์‹œ group by ์‚ฌ์šฉ ํ•„์ˆ˜
    ๐Ÿ’กdistinct ์‚ฌ์šฉ ์‹œ ๊ฐ’ ์ค‘๋ณต ์ œ๊ฑฐ ๊ฐ€๋Šฅ

    -- ์ „์ฒด ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ
    SELECT sum([column 1]) FROM [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…];
     
    --[example 1] test.score ํ…Œ์ด๋ธ”์—์„œ ์ „์ฒด ํ•™์ƒ์˜ ์ ์ˆ˜ ํ•ฉ ๊ตฌํ•˜๊ธฐ
    SELECT sum(score) FROM test.socre;
    
    --[example 2] test.score ํ…Œ์ด๋ธ”์—์„œ ์ปฌ๋Ÿผ๋ช…์ด total์ธ ์ „์ฒด ํ•™์ƒ์˜ ์ ์ˆ˜ ํ•ฉ ๊ตฌํ•˜๊ธฐ
    SELECT sum(score) as total FROM test.score;
    
    
    -- ํŠน์ • ์ปฌ๋Ÿผ์˜ ๊ฐ’๋ณ„ ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ
    SELECT [column 1], sum([column 2]) FROM [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…] GROUP BY [column 1];
    
    -- [example 1] test.score ํ…Œ์ด๋ธ”์—์„œ ํ•™์ƒ ์ด๋ฆ„๋ณ„ ์ ์ˆ˜ ํ•ฉ ๊ตฌํ•˜๊ธฐ
    SELECT name, sum(score) as total FROM test.score GROUP BY name;
    
    
    -- ์ „์ฒด ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ
    SELECT count([column 1]) FROM [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…];
    
    -- [example 1] test.score ํ…Œ์ด๋ธ”์˜ ์ „์ฒด ํ–‰์˜ ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ
    SELECT count(*) FROM test.score;
    
    -- [example 2] test.score ํ…Œ์ด๋ธ”์—์„œ ์ค‘๋ณต ์ œ๊ฑฐํ•œ name์˜ ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ
    SELECT count(distinct name) FROM test.score;
    
    
    -- ํŠน์ • ์ปฌ๋Ÿผ์˜ ๊ฐ’๋ณ„ ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ
    SELECT [column 1], count([column 2]) FROM [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…] GROUP BY [column 1];
    
    -- [example 1] test.score ํ…Œ์ด๋ธ”์—์„œ ํ•™์ƒ ์ด๋ฆ„๋ณ„ ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ
    SELECT name, count(*) FROM test.score GROUP BY name;
    

    2. ๋ฐ์ดํ„ฐ์˜ ํ˜•์‹ ๋ณ€๊ฒฝ
    ๐Ÿ’ก๋ฐ์ดํ„ฐ ํ˜•์‹ ๋ณ€๊ฒฝ์˜ ๊ฒฝ์šฐ RDMS์— ๋”ฐ๋ผ ์•ฝ๊ฐ„์˜ ์ฐจ์ด๊ฐ€ ์žˆ์Œ.

    -- ์ˆซ์ž๋ฅผ ๋ฌธ์ž๋กœ ๋ณ€๊ฒฝ
    select cast([column 1] as varchar) from [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…];
    
    -- [example 1] test.score ํ…Œ์ด๋ธ”์—์„œ score์„ ๋ฌธ์žํ˜•์œผ๋กœ ๋ณ€๊ฒฝ
    select cast(score as varchar) from test.score;
    
    
    -- ๋ฌธ์ž๋ฅผ ์ˆซ์ž๋กœ ๋ณ€๊ฒฝ
    select cast([column 1] as int) from [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…];
    
    -- [example 1] test.score ํ…Œ์ด๋ธ”์—์„œ ๋ฌธ์ž๋กœ ๋˜์–ด์žˆ๋Š” score๋ฅผ ์ˆซ์žํ˜•์œผ๋กœ ๋ณ€๊ฒฝ
    select cast(score as int) from test.score;
    
     
    -- timestamp ์—์„œ date๋งŒ ์ถ”์ถœ
    select date([column 1]) from [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…];
    
    -- [example 1] test.score ํ…Œ์ด๋ธ”์—์„œ ์‹œํ—˜ ์‹œ๊ฐ„(yyyy-mm-dd hh:mm:ss)์„ ๋‚ ์งœ(yyyy-mm-dd)๋งŒ ์ถ”์ถœ
    select date(test_time) from test.score;
    

  • DELETE (๋ฐ์ดํ„ฐ ์‚ญ์ œ)

    DELETE FROM [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…] WHERE [์กฐ๊ฑด];
    
    -- [example] test.score ํ…Œ์ด๋ธ”์—์„œ ์ ์ˆ˜๊ฐ€ 40์  ๋ฏธ๋งŒ์ธ ํ–‰ ์‚ญ์ œ
    DELETE FROM test.score where score < 40;
    

  • DROP (ํ…Œ์ด๋ธ” ์‚ญ์ œ)

    DROP TABLE [์Šคํ‚ค๋งˆ๋ช…].[ํ…Œ์ด๋ธ”๋ช…];
    
    -- [example] test.score ํ…Œ์ด๋ธ” ์‚ญ์ œ
    DROP TABLE test.score;
    
profile
Data Analyst

0๊ฐœ์˜ ๋Œ“๊ธ€