๐Ÿ“–
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
  • ์ˆซ์ž ํ•จ์ˆ˜
  • ๋ฌธ์ž ํ•จ์ˆ˜
  • ๋‚ ์งœ ๋ฐ ์‹œ๊ฐ„ ํ•จ์ˆ˜
  • NULL ๊ฐ’ ์ฒ˜๋ฆฌ
  • ํ–‰ ๋ฒˆํ˜ธ ์ถœ๋ ฅ

Was this helpful?

  1. Database

Built in function

PreviousBasic SQLNextSub Query

Last updated 4 years ago

Was this helpful?

  • Built in function : DBMS๊ฐ€ ์ œ๊ณตํ•˜๋Š” ๋‚ด์žฅํ•จ์ˆ˜

  • ์ž์ฃผ ์‚ฌ์šฉํ–ˆ๋˜ ๊ฒƒ๋“ค mysql ๊ณต์‹๋ฌธ์„œ์—์„œ ์š”์•ฝ ์ •๋ฆฌ

ํ•จ์ˆ˜๋ช…

๊ธฐ๋Šฅ

CEIL()

์ธ์ˆ˜๋ณด๋‹ค ์ž‘์ง€ ์•Š์€ ๊ฐ€์žฅ ์ž‘์€ ์ •์ˆ˜ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. (์˜ฌ๋ฆผ)

FLOOR()

์ธ์ˆ˜๋ณด๋‹ค ํฌ์ง€ ์•Š์€ ๊ฐ€์žฅ ํฐ ์ •์ˆ˜ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. (๋‚ด๋ฆผ)

MOD()

๋‚˜๋จธ์ง€ ๋ฐ˜ํ™˜

POW()

์ง€์ •๋œ ๊ฑฐ๋“ญ ์ œ๊ณฑ์œผ๋กœ ์ œ๊ธฐ ๋œ ์ธ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

POWER()

์ง€์ •๋œ ๊ฑฐ๋“ญ ์ œ๊ณฑ์œผ๋กœ ์ œ๊ธฐ ๋œ ์ธ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

ROUND()

๋ฐ˜์˜ฌ๋ฆผ

SIGN()

์ธ์ˆ˜์˜ ๋ถ€ํ˜ธ๋ฅผ ๋ฐ˜ํ™˜

SQRT()

์ธ์ˆ˜์˜ ์ œ๊ณฑ๊ทผ์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

TRUNCATE()

์ง€์ •๋œ ์†Œ์ˆ˜ ์ž๋ฆฟ์ˆ˜๋กœ ์ž๋ฆ…๋‹ˆ๋‹ค.

ABS()

์ ˆ๋Œ“๊ฐ’

ํ•จ์ˆ˜๋ช…

๊ธฐ๋Šฅ

์—ฐ๊ฒฐ๋œ ๋ฌธ์ž์—ด ๋ฐ˜ํ™˜

์ธ์ˆ˜๋ฅผ ์†Œ๋ฌธ์ž๋กœ ๋ฐ˜ํ™˜

์ง€์ •๋œ ๋ฌธ์ž์—ด๋กœ ์™ผ์ชฝ์ด ์ฑ„์›Œ์ง„ ๋ฌธ์ž์—ด ์ธ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

๋ฌธ์ž์—ด์ด ์ •๊ทœ์‹๊ณผ ์ผ์น˜ํ•˜๋Š”์ง€ ์—ฌ๋ถ€

์ง€์ •๋œ ํšŸ์ˆ˜๋งŒํผ ๋ฌธ์ž์—ด ๋ฐ˜๋ณต

์ง€์ •๋œ ๋ฌธ์ž์—ด์˜ ํ•ญ๋ชฉ ๋ฐ”๊พธ๊ธฐ

๋ฌธ์ž์—ด์˜ ๋ฌธ์ž ๋ฐ˜์ „

์ง€์ •๋œ ๋ถ€๋ถ„ ๋ฌธ์ž์—ด์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

์„ ํ–‰ ๋ฐ ํ›„ํ–‰ ๊ณต๋ฐฑ ์ œ๊ฑฐ

๋ฌธ์ž์—ด์˜ ๋ฌธ์ž ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜

๋ฐ”์ดํŠธ ๋‹จ์œ„๋กœ ์ธก์ •๋œ ๋ฌธ์ž์—ด์˜ ๊ธธ์ด ๋ฐ˜ํ™˜

  • ๋‹จ์ˆœ ๋ฌธ์ž์—ด๋กœ ์ €์žฅ ๊ด€๋ฆฌ ํ• ์ˆ˜๋„ ์žˆ๊ฒ ์ง€๋งŒ, ๋‚ ์งœํ˜• ๋ฐ์ดํ„ฐ๋กœ ์ €์žฅํ•˜์—ฌ ๊ด€๋ฆฌํ•˜๋ฉด, ๋‚ ์งœ ๋”ํ•˜๊ฑฐ๋‚˜ ์ฐจ์ด๋ฅผ ๊ตฌํ•˜๋Š” ๋“ฑ์˜ ๋‚ ์งœ๋งŒ์˜ ์—ฐ์‚ฐ ์†์‰ฝ๊ฒŒ ์ฒ˜๋ฆฌ ๊ฐ€๋Šฅ

  • format: ๋‚ ์งœ, ์‹œ๊ฐ„ ํ•จ์ˆ˜์—์„œ ๋‚ ์งœ์™€ ์‹œ๊ฐ„ ๋ถ€๋ถ„์„ ๋‚˜ํƒ€๋‚ด๋Š” ์ธ์ˆ˜ ํ‘œ๊ธฐ (%Y, %m, %d,..)

  • ๋‚ ์งœํ˜• ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ง„ ์—ด์„ ๋Œ€์ƒ์œผ๋กœ ์—ฐ์‚ฐ ์ˆ˜ํ–‰

  • ๋ฌธ์žํ˜• (char, varchar) ๋ฐ์ดํ„ฐ์™€ ๋‚ ์งœํ˜• ๋ฐ์ดํ„ฐ๊ฐ„ ์—ฐ์‚ฐ ์ˆ˜ํ–‰์‹œ์—๋Š” date_format, str_to_dateํ•จ์ˆ˜๋กœ ๋ฐ์ดํ„ฐํ˜• ์ƒํ˜ธ ๋ณ€ํ™˜ํ•˜์—ฌ ์ˆ˜ํ–‰

ํ•จ์ˆ˜๋ช…

๊ธฐ๋Šฅ

๋‚ ์งœ ๊ฐ’์— ์‹œ๊ฐ„ ๊ฐ’ (๊ฐ„๊ฒฉ, interval) ์ถ”๊ฐ€

๋ฌธ์ž์—ด ๋ฐ์ดํ„ฐ๋ฅผ ๋‚ ์งœ๋กœ ๋ณ€ํ™˜

์ง€์ •๋œ ๋‚ ์งœ ํ˜•์‹ (๋‚ ์งœํ˜• ๋ฐ์ดํ„ฐ๋ฅผ ๋ฌธ์ž์—ด๋กœ)

๋‚ ์งœ ๋˜๋Š” datetime ์‹์˜ ๋‚ ์งœ ๋ถ€๋ถ„ ์ถ”์ถœ ('2020-02-09')

๋‚ ์งœ์—์„œ ์‹œ๊ฐ„ ๊ฐ’ (๊ฐ„๊ฒฉ) ๋นผ๊ธฐ

๋‘ ๋‚ ์งœ ๋นผ๊ธฐ

NULL ๊ฐ’ ์ฒ˜๋ฆฌ

  • ์ง€์ •๋˜์ง€ ์•Š์€ ๊ฐ’

  • ๊ฐ’์„ ์•Œ ์ˆ˜๋„ ์—†๊ณ , ์ ์šฉํ•  ์ˆ˜๋„ ์—†๋‹ค. ('', ' ', '0'๊ณผ๋Š” ๋‹ค๋ฅธ ํŠน๋ณ„ํ•œ ๊ฐ’)

  • ๋น„๊ต ์—ฐ์‚ฐ์ž๋กœ ๋น„๊ต ๋ถˆ๊ฐ€๋Šฅ

  • NULL ๊ฐ’์— ๋Œ€ํ•œ ์—ฐ์‚ฐ๊ณผ ์ง‘๊ณ„ํ•จ์ˆ˜

    • NULL+์ˆซ์ž ์—ฐ์‚ฐ์˜ ๊ฒฐ๊ณผ๋Š” NULL

    • ์ง‘๊ณ„ํ•จ์ˆ˜ ๊ณ„์‚ฐ์‹œ NULL์ด ํฌํ•จ๋œ ํ–‰์€ ์ง‘๊ณ„์—์„œ ๋น ์ง„๋‹ค.

    • ํ•ด๋‹น ๋˜๋Š” ํ–‰์ด ํ•˜๋‚˜๋„ ์—†์„ ๊ฒฝ์šฐ, sum, avgํ•จ์ˆ˜ ๊ฒฐ๊ณผ๋Š” null์ด ๋˜๊ณ , countํ•จ์ˆ˜์˜ ๊ฒฐ๊ณผ๋Š” 0์ด๋‹ค.

  • NULL ๊ฐ’์„ ํ™•์ธํ•˜๋Š” ๋ฐฉ๋ฒ• (IS NULL, IS NOT NULL)

    • NULL๊ฐ’ ์ฐพ์„ ๋•Œ๋Š” '=' ๊ฐ€ ์•„๋‹ˆ๋ผ IS NULL

    • NULL์ด ์•„๋‹Œ ๊ฐ’์„ ์ฐพ์„ ๋•Œ๋Š” '<>'๊ฐ€ ์•„๋‹ˆ๋ผ IS NOT NULL

  • IFNULL : NULL ๊ฐ’์„ ๋‹ค๋ฅธ ๊ฐ’์œผ๋กœ ๋Œ€์น˜ํ•˜์—ฌ ์—ฐ์‚ฐํ•˜๊ฑฐ๋‚˜, ๋‹ค๋ฅธ ๊ฐ’์œผ๋กœ ์ถœ๋ ฅํ•˜๋Š” ํ•จ์ˆ˜.

ํ–‰ ๋ฒˆํ˜ธ ์ถœ๋ ฅ

  • ๋ณ€์ˆ˜๋Š” ์ด๋ฆ„ ์•ž์— @ ๊ธฐํ˜ธ๋ฅผ ๋ถ™์ด๋ฉฐ, ์น˜ํ™˜๋ฌธ์—๋Š” SET๊ณผ := ๊ธฐํ˜ธ๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค.

    set @seq:=0;
    
    select (@seq:=@seq+1)'์ˆœ๋ฒˆ', custid, name, phone
    from customer
    where @seq<2;

์ฐธ์กฐ: mysql ๊ณต์‹๋ฌธ์„œ

MySQL์—์„œ ๋ณ€์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ฒ˜๋ฆฌํ•˜๋Š” ๋ฐฉ๋ฒ• ( )

๐Ÿ“ˆ
์ˆซ์ž ํ•จ์ˆ˜
๋ฌธ์ž ํ•จ์ˆ˜
๋‚ ์งœ ๋ฐ ์‹œ๊ฐ„ ํ•จ์ˆ˜
Programmers ์ฐธ์กฐ
CONCAT()
LOWER()
LPAD()
REGEXP
REPEAT()
REPLACE()
REVERSE()
SUBSTR()
TRIM()
CHAR_LENGTH(str)
LENGTH(str)
ADDDATE(date, interval)
STR_TO_DATE()
DATE_FORMAT()
DATE()
DATE_SUB()
DATEDIFF()