๐Ÿ“šSQL ๋ฌธ๋ฒ• ์ •๋ฆฌ

์œค๊ฑดํฌ(Guny)ยท2020๋…„ 10์›” 7์ผ
39

Database

๋ชฉ๋ก ๋ณด๊ธฐ
1/7
post-thumbnail
post-custom-banner
*MySql ๋ฌธ๋ฒ•์„ ๊ธฐ์ค€์œผ๋กœ ์ž‘์„ฑํ–ˆ์Šต๋‹ˆ๋‹ค.

DML(Data Manipulation Language)

  • insert
  • update
  • delete
  • select

DDL(Data Definition Language)

  • create
  • alter
  • drop
  • truncate
  • rename

DCL(Data Control Language)

  • grant
  • revoke

TCL(Transaction Control Language)

  • commit
  • rollback
  • savepoint

Create

  • ํ…Œ์ด๋ธ” ์ƒ์„ฑ
    create table student (
    ์นผ๋Ÿผ๋ช… ํƒ€์ž… ์กฐ๊ฑด(not null ๋“ฑ),
    ์นผ๋Ÿผ๋ช… ํƒ€์ž… ์กฐ๊ฑด(not null ๋“ฑ),
    ์นผ๋Ÿผ๋ช… ํƒ€์ž… ์กฐ๊ฑด(not null ๋“ฑ),
    PRIMARY KEY ~~ );

Insert

  • ํ…Œ์ด๋ธ”์— ๋ฐ์ดํ„ฐ ์‚ฝ์ž…
    insert into student values(ํ…Œ์ด๋ธ”์— ๋งž๋Š” ๋ฐ์ดํ„ฐ ์–‘์‹);

Update

  • ๋ฐ์ดํ„ฐ ๋‚ด์šฉ ์ˆ˜์ •
    update ํ…Œ์ด๋ธ” set ์นผ๋Ÿผ = '๊ฐ’' where ์กฐ๊ฑด;

Delete

  • ๋ฐ์ดํ„ฐ ์‚ญ์ œ
    delete from ํ…Œ์ด๋ธ” where ์กฐ๊ฑด;

select

  • ๋ชจ๋“  ์ปฌ๋Ÿผ ์กฐํšŒ
    select * from student;

  • ํ•„์š”ํ•œ ์ปฌ๋Ÿผ ์กฐํšŒ
    select age, name from student;

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

  1. select
  2. from
  3. where
  4. group by
  5. having
  6. order by

*์ฟผ๋ฆฌ์˜ ์‹คํ–‰ ์ˆœ์„œ๋Š” from โ–ถ where โ–ถ group by โ–ถhaving โ–ถ select โ–ถ order by


Where

  • ์ปฌ๋Ÿผ ์กฐํšŒ์˜ ์กฐ๊ฑด
    select * from student where id = 1;

  • and, or ์กฐ๊ฑด
    select * from student where sex='๋‚จ์ž' and(or) address='์„œ์šธ';

  • like ์กฐ๊ฑด
    % ๋กœ ํ‘œํ˜„
    select * from student where name = '๊น€%';

Limit

  • ์กฐํšŒ ๊ฒฐ๊ณผ ํ–‰์˜ ์ˆ˜ ์ œํ•œ
    select * from student limit 1;

Group By

  • ํŠน์ • ์นผ๋Ÿผ ๊ธฐ์ค€์˜ ๋ฐ์ดํ„ฐ ๊ทธ๋ฃนํ•‘
    select age from student group by age;

Order by

  • ํŠน์ • ์นผ๋Ÿผ ๊ธฐ์ค€์œผ๋กœ ์ •๋ ฌ
  • ASC : ์˜ค๋ฆ„์ฐจ์ˆœ 1 2 3 4 5
  • DESC : ๋‚ด๋ฆผ์ฐจ์ˆœ 5 4 3 2 1
    select * from student order by age desc;

Distinct

  • ์ค‘๋ณต ํ–‰์„ ์ œ๊ฑฐ
    select distinct name from student;

Alias

  • ์นผ๋Ÿผ์— ๋ณ„์นญ์„ ์ฃผ๊ณ  ์กฐํšŒ
    select age '๋‚˜์ด' from student where id = 3;

์—ฐ์‚ฐ์ž

  • ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ž : + - / *
  • ๋น„๊ต ์—ฐ์‚ฐ์ž : > < >= <= = != ^= <>
  • ๋…ผ๋ฆฌ ์—ฐ์‚ฐ์ž : and, or, not
  • ๋น„๊ต ์—ฐ์‚ฐ์ž2 : between and, in, is null, like

ํ•จ์ˆ˜

๋ฌธ์ž ์กฐ์ž‘ ํ•จ์ˆ˜

  • UPPER(str) : ๋Œ€๋ฌธ์ž ๋ณ€ํ™˜
  • LOWER(str) : ์†Œ๋ฌธ์ž ๋ณ€ํ™˜
  • INITCAP(str) : ์ฒซ ๊ธ€์ž๋งŒ ๋Œ€๋ฌธ์ž๋กœ ๋ณ€ํ™˜
  • CONCAT(str, tmp) : ๋‘ ๋ฌธ์ž๊ฐ’ ๊ฒฐํ•ฉ
  • SUBSTR(str,a,b) : ๋ฌธ์ž ์ถ”์ถœ a = ์‹œ์ž‘์œ„์น˜, b = ๊ฐœ์ˆ˜
  • LENGTH(str) : ๋ฌธ์ž์—ด ๊ธธ์ด ๋ฐ˜ํ™˜
  • L/RPAD(๋Œ€์ƒ,์ด๊ธธ์ด,์ฑ„์šธ๋ฌธ์ž์—ด) : ๋Œ€์ƒ ๋ฌธ์ž์—ด์— ์ฑ„์šธ๋ฌธ์ž์—ด์„ ์ด๊ธธ์ด๋งŒํผ ์ฑ„์›Œ์„œ ๋ฐ˜ํ™˜
    LPAD('001',7,'0') โ–ถ 0000001
  • LTRIM, RTRIM(๋Œ€์ƒ, ์ œ๊ฑฐํ•  ๋ฌธ์ž์—ด) : ๋Œ€์ƒ ๋ฌธ์ž์—ด์—์„œ ์ œ๊ฑฐํ•  ๋ฌธ์ž์—ด์„ ์—†์•ค ๋’ค ๋ฐ˜ํ™˜
    select LTRIM('00010' , '00') โ–ถ 010
  • REPLACE(๋Œ€์ƒ๋ฌธ์ž์—ด,๋ฐ”๊พธ๊ณ ์‹ถ์€๋Œ€์ƒ,๋ฐ”๊พธ๊ณ ์‹ถ์€๋‚ด์šฉ) : ๋Œ€์ƒ๋ฌธ์ž์—ด์—์„œ ๋ฐ”๊พธ๊ณ  ์‹ถ์€ ๋Œ€์ƒ์„ ๋ฐ”๊พธ๊ณ  ์‹ถ์€ ๋‚ด์šฉ์œผ๋กœ ์ˆ˜์ •ํ•œ ๋’ค ๋ฐ˜ํ™˜
    REPLACE('ABCDEFG','DEF','XXX') โ–ถ ABCXXXG

๊ณ„์‚ฐ ํ•จ์ˆ˜

  • max
  • min
  • count
  • avg
  • sum

select ๊ณ„์‚ฐํ•จ์ˆ˜(์นผ๋Ÿผ) from ํ…Œ์ด๋ธ”; ๋กœ ์‚ฌ์šฉ


JOIN

INNER JOIN

์ผ๋ฐ˜์ ์ธ ์กฐ์ธ, A์™€ B ํ…Œ์ด๋ธ”์˜ ์กฐ์ธ์—์„œ A์™€ B ํ…Œ์ด๋ธ” ๋ชจ๋‘์— ์žˆ๋Š” ๋ ˆ์ฝ”๋“œ๋งŒ ์กฐํšŒ๋œ๋‹ค.

SELECT A.a, A.b, B.c 
FROM A
JOIN B
ON B.a = A.c;

ON ๋Œ€์‹  WHERE๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.

LEFT OUTER JOIN

A์™€ B ํ…Œ์ด๋ธ”์˜ ์กฐ์ธ ์ƒํ™ฉ์—์„œ Aํ…Œ์ด๋ธ” ๊ธฐ์ค€์œผ๋กœ Bํ…Œ์ด๋ธ”์— Aํ…Œ์ด๋ธ”์˜ ์นผ๋Ÿผ๊ณผ ์ผ์น˜ํ•˜๋Š” ์กฐ๊ฑด์ด ์—†์œผ๋ฉด Bํ…Œ์ด๋ธ”์˜ ์นผ๋Ÿผ์€ NULL๋กœ ์กฐํšŒ๋œ๋‹ค.

SELECT A.a, A.b, B.c 
FROM A
LEFT OUTER JOIN B
ON B.a = A.c;

outer๋ฅผ ์ƒ๋žตํ•  ์ˆ˜ ์žˆ๋‹ค.

RIGHT OUTER JOIN

A์™€ B ํ…Œ์ด๋ธ”์˜ ์กฐ์ธ ์ƒํ™ฉ์—์„œ Bํ…Œ์ด๋ธ” ๊ธฐ์ค€์œผ๋กœ Aํ…Œ์ด๋ธ”์— Bํ…Œ์ด๋ธ”์˜ ์นผ๋Ÿผ๊ณผ ์ผ์น˜ํ•˜๋Š” ์กฐ๊ฑด์ด ์—†์œผ๋ฉด Aํ…Œ์ด๋ธ”์˜ ์นผ๋Ÿผ์€ NULL๋กœ ์กฐํšŒ๋œ๋‹ค.

SELECT A.a, A.b, B.c 
FROM A
RIGHT OUTER JOIN B
ON B.a = A.c;

์—ญ์‹œ outer๋ฅผ ์ƒ๋žตํ•  ์ˆ˜ ์žˆ๋‹ค.

CROSS JOIN(Cartesian JOIN)

์ง‘ํ•ฉ์˜ ๊ณฑ ๊ฐœ๋…์œผ๋กœ
A= {a, b, c, d} , B = {1, 2, 3} ์ผ ๋•Œ
A CROSS JOIN B ๋Š”
(a,1), (a, 2), (a,3), (b,1), (b,2), (b,3), (c, 1), (c,2), (c,3), (d, 1), (d, 2), (d,3)์˜ ๊ฒฐ๊ณผ๊ฐ€ ๋œ๋‹ค.
๊ฒฐ๊ณผ์˜ ๊ฐœ์ˆ˜๋Š” n(A) * n(B) ์ด๋‹ค.

SELECT A.a A.b B.c
FROM A
CROSS JOIN B;
// ์œ„ ์•„๋ž˜ ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋Š” ๊ฐ™๋‹ค.
SELECT A.a, A.b, B.c
FROM A, B;
profile
๊ฐœ๋ฐœ์ž
post-custom-banner

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

comment-user-thumbnail
2022๋…„ 9์›” 30์ผ

๋•๋ถ„์— ์ข‹์€ ๋‚ด์šฉ ์ž˜ ๋ณด๊ณ  ๊ฐ‘๋‹ˆ๋‹ค
๊ฐ์‚ฌํ•ฉ๋‹ˆ๋‹ค.

๋‹ต๊ธ€ ๋‹ฌ๊ธฐ