๐Ÿ“–
Mini's Today I Learned
  • Today I Learned
  • ๐Ÿ’ปComputer Science
    • ๐ŸงฑComputer Science
      • Computer Architecture
    • ๐Ÿฆ•OperatingSystem
      • System Structure
      • 01. ์šด์˜์ฒด์ œ ์—ญํ• 
      • 02. History๋กœ ์ดํ•ดํ•˜๋Š” ์šด์˜์ฒด์ œ
      • 03. ์šด์˜์ฒด์ œ ๊ตฌ์กฐ
      • 04. ํ”„๋กœ์„ธ์Šค ์Šค์ผ€์ฅด๋ง
      • 05. ์Šค์ผ€์ฅด๋ง ์•Œ๊ณ ๋ฆฌ์ฆ˜
      • 06. ํ”„๋กœ์„ธ์Šค ์ƒํƒœ์™€ ์Šค์ผ€์ฅด๋Ÿฌ
      • 07. ์ธํ„ฐ๋ŸฝํŠธ
      • 08. ํ”„๋กœ์„ธ์Šค์™€ ์ปจํ…์ŠคํŠธ ์Šค์œ„์นญ
      • 09. ํ”„๋กœ์„ธ์Šค๊ฐ„ ์ปค๋ฎค๋‹ˆ์ผ€์ด์…˜(IPC ๊ธฐ๋ฒ•)
      • 10. Thread(์Šค๋ ˆ๋“œ)
      • 11. ๊ฐ€์ƒ๋ฉ”๋ชจ๋ฆฌ (Virtual Memory System)
      • 12. ํŒŒ์ผ์‹œ์Šคํ…œ ์ดํ•ด
      • 13. ๋ถ€ํŒ…์˜ ์ดํ•ด
      • 14. ๊ฐ€์ƒ๋จธ์‹ (Virtual Machine)
  • ๐Ÿ“ˆDatabase
    • MySQL CLI (Frequently used)
    • 1-Tier, 2Tier, 3Tier
    • Basic SQL
    • Built in function
    • Sub Query
    • View
    • Stored Program
    • index
    • Normalization(์ •๊ทœํ™”)
    • Transaction
    • Transaction
  • ๐ŸŒ Network
    • ๐Ÿ”Œ TCP&IP Basic
      • 01 TCP/IP ๋ง›๋ณด๊ธฐ
      • 02 TCP/IP ๊ฐœ์š”
      • 03 ํ†ต์‹  ์„œ๋น„์Šค์™€ ํ”„๋กœํ† ์ฝœ
      • 04 ์• ํ”Œ๋ฆฌ์ผ€์ด์…˜ ๊ณ„์ธต
      • 05 ํŠธ๋ Œ์ŠคํฌํŠธ ๊ณ„์ธต
      • 06 ๋„คํŠธ์›Œํฌ๊ณ„์ธต
      • 07 ๋ฐ์ดํ„ฐ ๊ณ„์ธต๊ณผ ๋ฌผ๋ฆฌ๊ณ„์ธต
      • 08 ๋ผ์šฐํŒ…
      • 09 ๋ณด์•ˆ
      • 10 HTTP ํ”„๋กœํ† ์ฝœ
    • ๐Ÿฟ๏ธHTTP ์™„๋ฒฝ ๊ฐ€์ด๋“œ ์š”์•ฝ
      • 1. HTTP: ์›น์˜ ๊ธฐ์ดˆ
      • 2. URL ๊ณผ ๋ฆฌ์†Œ์Šค
      • 3. HTTP Message
      • 4. Connection Management
      • 5. Overview of web server architectures
      • 6. Proxies
      • 7.Caching
      • 8. Integration Points: Gateways, Tunnels, and Relays
      • 9. Web Robots
      • 10. HTTP/2.0
      • 11. Client Identification and Cookies
      • 12. Basic Authentication
      • 13. Digest Authentication
      • 14. Secure HTTP
      • 15. Entities and Encodings
      • 16. Internationalization
      • 17. Content Negotiation and Transcoding
      • 18. Web Hosting
  • ๐ŸดLanguage
    • โ˜•JAVA
      • Comparable vs Comparator
  • ๐Ÿ› ๏ธ Framework
  • ๐ŸงฉDesign Pattern
    • ๊ฐ์ฒด ์ง€ํ–ฅ ์„ค๊ณ„ ์›์น™ (SOLID)
    • Design Pattern ๊ฐœ์š”
    • Template method
    • Singleton
  • ๐ŸฅFrontEnd
    • HTML&CSS
      • 01 Intro
      • 02 HTML Basic
      • 03 Content Section
      • 04 Block and Inline Elements
      • 05 Text Contents
      • 06 Inline Elements
      • 07 Multi Media
      • 08 Table&Form&etc
      • 09 Global Attribute & etc
      • 10 css basic
      • 11 css config
      • 12 css unit
      • 13 Box Model
      • 14 Font & Text
      • 15 Float(๋„์›€) & Position
      • 16 Background
      • 17 Transitions & Transforms
      • 18 Animation & Multi Columns(๋‹ค๋‹จ)
      • 19 Flex
      • 20 Grid
    • Sass(SCSS)
    • TypeScript
    • Webpack Introduction
      • ์›นํŒฉ์˜ ๊ธฐ๋ณธ ๊ตฌ์กฐ
      • CommonJS
      • ESM
      • WebpackConfig
        • 1. Handlebars
        • 2. Caching
        • 3. Minification & Mangling
        • 4. Mode(Development mode & Production Mode)
        • 5. ์ด๋ฏธ์ง€ ํŒŒ์ผ ๋ชจ๋“ˆ๋กœ ๋‹ค๋ฃจ์–ด๋ณด๊ธฐ(file-loader)
        • 6. ์ด๋ฏธ์ง€ ํŒŒ์ผ ๋ชจ๋“ˆ๋กœ ๋‹ค๋ฃจ์–ด๋ณด๊ธฐ(url-loader)
        • 7. SASS Loader
        • 8. Post CSS
        • 9. BLOWSERS LIST
        • 10. Stylelint
        • 11. Babel
      • Webpack-Practice
    • ETC
      • GIT
      • MarkDown
Powered by GitBook
On this page
  • SQL ๊ธฐ๋ณธ
  • ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์„ ์ด์šฉํ•œ SQL ์งˆ์˜
  • 2๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ” SQL ์งˆ์˜
  • ์ง‘ํ•ฉ์—ฐ์‚ฐ์ž
  • EXISTS

Was this helpful?

  1. Database

Basic SQL

SQL ๊ธฐ๋ณธ

  • ๋ฌธ๋ฒ• ์ˆœ์„œ

    SELECT
    FROM
    WHERE
    GROUP BY
    HAVING
    ORDER BY
  • ์‹คํ–‰์ˆœ์„œ

    FROM        -- ํ•ด๋‹น ๋ฐ์ดํ„ฐ๊ฐ€ ์žˆ๋Š” ๊ณณ์„ ์ฐพ์•„์„œ
    WHERE        -- ์กฐ๊ฑด์— ๋งž๋Š” ๋ฐ์ดํ„ฐ๋งŒ ๊ฐ€์ ธ์™€์„œ
    GROUP BY    -- ์„ ํƒํ•œ column์œผ๋กœ ๊ทธ๋ฃนํ™”
    HAVING        -- group by ๊ฒฐ๊ณผ๋ฅผ ๋‚˜ํƒ€๋‚ด๋Š” ๊ทธ๋ฃน์„ ์ œํ•œ
    SELECT        -- ์†์„ฑ(์—ด) ์ถœ๋ ฅ ์„ ํƒ
    ORDER BY    -- ์ •๋ ฌ
  • ์ฃผ์˜์‚ฌํ•ญ - ๋ณ„์นญ(Alias)

    • ์‹คํ–‰ ์ˆœ์„œ์— ๋งž๊ฒŒ ์‚ฌ์šฉํ•  ๊ฒƒ!

      • ์˜ˆ: select ์ ˆ์— alias๋ฅผ ์ •์˜ํ–ˆ๋‹ค๋ฉด order by์ ˆ์—์„œ๋Š” ์‚ฌ์šฉ์ด ๊ฐ€๋Šฅํ•˜์ง€๋งŒ, where์ ˆ์—์„œ๋Š” ์‚ฌ์šฉ ๋ถˆ๊ฐ€๋Šฅํ•˜๋‹ค.

      • ์ด์œ : select ์ ˆ๋ณด๋‹ค where์ ˆ์ด ๋จผ์ € ์‹คํ–‰์ด ๋˜๊ธฐ ๋•Œ๋ฌธ!

    • ๋งŒ์•ฝ ์‹คํ–‰ ์ˆœ์„œ์— ๋งž์ง€ ์•Š๊ฒŒ ์‚ฌ์šฉํ•˜๊ฒŒ ๋˜๋ฉด Invalid Identifier๋ผ๋Š” Error๋ฐœ์ƒ

ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์„ ์ด์šฉํ•œ SQL ์งˆ์˜

  • where ์ ˆ์— ์กฐ๊ฑด์œผ๋กœ ์‚ฌ์šฉ๊ฐ€๋Šฅํ•œ ์ˆ ์–ด

    ์ˆ ์–ด

    ์—ฐ์‚ฐ์ž

    ์‚ฌ์šฉ ์˜ˆ

    ๋น„๊ต

    <, >, <=, >=,=, <>,

    price < 2000

    ๋ฒ”์œ„

    between

    price between 1000 and 2000

    ์ง‘ํ•ฉ

    in, not in

    price in (1000, 2000, 3000)

    ํŒจํ„ด

    LIKE(๋” ๋ณต์žกํ•œ ํŒจํ„ด์€ REGEXP ์—ฐ์‚ฐ์ž๋กœ ์ •๊ทœ ํ‘œํ˜„์‹ ํ™œ์šฉ)

    bookname like '์ถ•๊ตฌ์˜ ์—ญ์‚ฌ'

    NULL

    IS NULL, IS NOT NULL

    price IS NULL

    ๋ณตํ•ฉ์กฐ๊ฑด

    and, or , not

    (price<2000) and (bookname like '์ถ•๊ตฌ์˜ ์—ญ์‚ฌ')

  • LIKE์™€ ๊ฐ™์ด ์‚ฌ์šฉํ•˜๋Š” ์™€์ผ๋“œ ๋ฌธ์ž

    ์™€์ผ๋“œ ๋ฌธ์ž

    ์˜๋ฏธ

    ์‚ฌ์šฉ ์˜ˆ

    %

    0 ๊ฐœ ์ด์ƒ์˜ ๋ฌธ์ž์—ด๊ณผ ์ผ์น˜

    '%์ถ•๊ตฌ%': ์ถ•๊ตฌ๋ฅผ ํฌํ•จํ•˜๋Š” ๋ฌธ์ž์—ด

    +

    ๋ฌธ์ž์—ด ์—ฐ๊ฒฐ

    '๊ณจํ”„' + '๋ฐ”์ด๋ธ”': '๊ณจํ”„ ๋ฐ”์ด๋ธ”'

    []

    1๊ฐœ์˜ ๋ฌธ์ž์™€ ์ผ์น˜

    '[0-5]%': 0-5 ์‚ฌ์ด์˜ ์ˆซ์ž๋กœ ์‹œ์ž‘ํ•˜๋Š” ๋ฌธ์ž์—ด

    1๊ฐœ์˜ ๋ฌธ์ž์™€ ๋ถˆ์ผ์น˜

    '' : 0-5์‚ฌ์ด์˜ ์ˆซ์ž๋กœ ์‹œ์ž‘ํ•˜์ง€ ์•Š๋Š” ๋ฌธ์ž์—ด

    _

    ํŠน์ • ์œ„์น˜์˜ 1๊ฐœ์˜ ๋ฌธ์ž์™€ ์ผ์น˜

    '_๊ตฌ%': ๋‘๋ฒˆ์งธ ์œ„์น˜์— '๊ตฌ'๊ฐ€ ๋“ค์–ด๊ฐ€๋Š” ๋ฌธ์ž์—ด

  • GROUP BY

    • ์†์„ฑ ๊ฐ’์ด ๊ฐ™์€ ๊ฐ’ ๋ผ๋ฆฌ (ํŠœํ”Œ๋“ค์˜)๊ทธ๋ฃน์„ ๋งŒ๋“ค ์ˆ˜ ์žˆ๋‹ค.

    • select ์ ˆ์—๋Š” group by ์ ˆ์— ์‚ฌ์šฉํ•œ ์†์„ฑ ํ˜น์€ ์ง‘๊ณ„ํ•จ์ˆ˜๋งŒ ์‚ฌ์šฉ ๊ฐ€๋Šฅ.

  • Having

    • ๋ฐ˜๋“œ์‹œ group by์ ˆ๊ณผ ๊ฐ™์ด ์ž‘์„ฑํ•ด์•ผํ•จ.

    • where ์ ˆ ๋ณด๋‹ค ๋’ค์— ์™€์•ผํ•จ

    • ๊ฒ€์ƒ‰์กฐ๊ฑด(select)์—๋Š” ์ง‘๊ณ„ํ•จ์ˆ˜๊ฐ€ ์™€์•ผํ•จ.

2๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ” SQL ์งˆ์˜

  • join (์˜ˆ์‹œ)

    • ์—ฌ๋Ÿฌ๊ฐœ์˜ ํ…Œ์ด๋ธ”์„ ์—ฐ๊ฒฐํ•˜์—ฌ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์„ ๋งŒ๋“œ๋Š” ๊ณผ์ •

    • ๋‘ ๋ฆด๋ ˆ์ด์…˜(ํ…Œ์ด๋ธ”)์˜ ๊ณตํ†ต ์†์„ฑ์„ ๊ธฐ์ค€์œผ๋กœ, ์†์„ฑ(์—ด)์ด ๊ฐ™์€ ํŠœํ”Œ(ํ–‰)์„ ์ˆ˜ํ‰์œผ๋กœ ๊ฒฐํ•ฉ

    • ๋‘ ํ…Œ์ด๋ธ”์— ์กฐ๊ฑด์ด ์—†๋Š” ๊ฒฝ์šฐ ์นด๋””์ „ ํ”„๋กœ๋•ํŠธ ์—ฐ์‚ฐ(customer ํ–‰ * orders ํ–‰)

      select * 
      from customer, orders;
    • ๋™๋“ฑ ์กฐ์ธ(equal join) : ๋™๋“ฑ ์กฐ๊ฑด์— ์˜ํ•ด join, ์ผ๋ฐ˜์ 

      select * 
      from customer c, orders o
      where c.custid = o.custid;
      
      select *
      from customerc inner join orders o on c.custid = o.custid;
    • ์„ธํƒ€ ์กฐ์ธ: ๋น„๊ต์กฐ๊ฑด(>, >=, <. <=, =,!=)์— ์˜ํ•ด join ์ด์ค‘ = ๋กœ join ํ•œ๊ฑธ ๋™๋“ฑ ์กฐ์ธ์ด๋ผ๊ณ  ํ•  ์ˆ˜ ์žˆ์Œ.

    • ์ž์—ฐ ์กฐ์ธ: ์ค‘๋ณต ์†์„ฑ ์ œ๊ฑฐ

    • ์…€ํ”„ ์กฐ์ธ(self join) : ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ” (์ž์‹ ) ์„ ๋Œ€์ƒ์œผ๋กœ ์กฐ์ธ

      select e.ename, e.job
      from emp m, emp e
      where m.empno = e.mgr and m.ename = "BLAKE";
    • ์™ธ๋ถ€ ์กฐ์ธ(outer join) : ์ž์—ฐ ์กฐ์ธ ํ›„, ๋ชจ๋“  ๊ฐ’ ์ถ”์ถœ (left, right, full)ํ•˜๋˜, ์กฐ์ธ์‹คํŒจ(๋Œ€์‘ ๋˜์ง€ ์•Š๋Š” ์†์„ฑ)์‹œ NULL๋กœ ํ‘œ์‹œ

      • MySQL์—์„œ full outer join ์€ left, right join์„ union allํ•ด์„œ ํ‘œํ˜„

        select c.name, o.saleprice
        from customer c left outer join orders o
            on c.custid = o.custid
        union all
        select c.name, o.saleprice
        from customer c right outer join orders o
            on c.custid = o.custid;
  • sub query (๋ถ€์† ์งˆ์˜)

    • ๋ถ€์†์งˆ์˜ ๋จผ์ € ์ฒ˜๋ฆฌํ•˜๊ณ , ์ „์ฒด ์งˆ์˜๋ฅผ ์ฒ˜๋ฆฌ

      select bookname
      from book
      where book.price = (
          select MAX(price)
          from book
      );
    • ๋ถ€์†์งˆ์˜ ๊ฒฐ๊ณผ 4๊ฐ€์ง€

      • 11 (๋‹จ์ผํ–‰ ๋‹จ์ผ์—ด)

      • N1 (๋‹ค์ค‘ํ–‰ ๋‹จ์ผ์—ด) - IN ํ‚ค์›Œ๋“œ ํ™œ์šฉ

      • 1N (๋‹จ์ผํ–‰ ๋‹ค์ค‘์—ด)

      • NN (๋‹ค์ค‘ํ–‰ ๋‹ค์ค‘์—ด)

    • ๋ถ€์†์งˆ์˜ ๊ฐ„์—๋Š” ์ƒํ•˜๊ด€๊ณ„๊ฐ€ ์žˆ๊ณ , ์‹คํ–‰ ์ˆœ์„œ๋Š” ํ•˜์œ„ ๋ถ€์†์งˆ์˜๋ฅผ ๋จผ์ € ์‹คํ–‰ํ•˜๊ณ , ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์ด์šฉํ•ด์„œ ์ƒ์œ„ ๋ถ€์†์งˆ์˜๋ฅผ ์‹คํ–‰ํ•œ๋‹ค.

    • ์ƒ๊ด€ ์งˆ์˜ : ์ƒ์œ„ ๋ถ€์†์งˆ์˜์™€ ํ•˜์œ„ ๋ถ€์†์งˆ์˜๊ฐ€ ๋…๋ฆฝ์ ์ด์ง€ ์•Š๊ณ , ์„œ๋กœ ๊ด€๋ จ์„ ๋งบ๊ณ  ์žˆ๋‹ค.

      • select b1.bookname
        from book b1
        where b1.price > (
            select avg(b2.price)
            from book b2
            where b1.publisher = b2.publisher
        )

์ง‘ํ•ฉ์—ฐ์‚ฐ์ž

  • MySQL์€ ๋‹ค๋ฅธ DBMS์™€ ๋‹ฌ๋ฆฌ, MINUS, INTERSECT์—ฐ์‚ฐ์ด ๋ณ„๋„๋กœ ์—†๋‹ค. ๋Œ€์‹  NOT IN, INNER JOIN์„ ์‚ฌ์šฉ.

  • ํ•ฉ์ง‘ํ•ฉ

    • UNION : ์ค‘๋ณต ์ œ๊ฑฐ ๋ชจ๋“  ๊ฒฐ๊ณผ

    • UNION ALL :์ค‘๋ณต์„ ํฌํ•จํ•˜์—ฌ ๋ชจ๋“  ๊ฒฐ๊ณผ

  • ์ฐจ์ง‘ํ•ฉ(MINUS) -NOT IN ์—ฐ์‚ฐ์ž

  • ๊ต์ง‘ํ•ฉ(INTERSECT) - innter join

EXISTS

  • ์กฐ๊ฑด์— ๋งž๋Š” ํŠœํ”Œ์ด ์กด์žฌํ•˜๋ฉด ๊ฒฐ๊ณผ์— ํฌํ•จ

  • ์ƒ๊ด€ ๋ถ€์† ์งˆ์˜๋ฌธ ํ˜•์‹.

    select name, address
    from customer cs
    where exists(
        select * 
        from orders od
        wherer cs.custid = od.custid
    )
    • cs ํ…Œ์ด๋ธ”์˜ custid๊ฐ€ odํ…Œ์ด๋ธ”์˜ custid์™€ ์ผ์น˜ํ•˜๋ฉด ์กด์žฌํ•˜๋Š” ๊ฒƒ (true) => name, address ์ถœ

Previous1-Tier, 2Tier, 3TierNextBuilt in function

Last updated 4 years ago

Was this helpful?

๐Ÿ“ˆ