[30일차]SQL

유태형·2022년 6월 9일

sql 코드스테이츠

코드스테이츠

목록 보기

30/77

오늘의 목표

SQL이란?
SELECT문
연산자
함수
subQuery
JOIN
INSERT
UPDATE
DELETE
DDL
DCL

내용

SQL이란?

데이터를 메모리에 저장하거나 파일 시스템으로 사용중인 pc의 파일로 저장할 경우 각각 큰 단점이 존재합니다.

첫번째로 메모리 상에 데이터를 저장할 경우, 프로그램 종료시 해당 데이터도 같이 사라지므로 데이터의 수명이 프로그램의 수명에 의존할 수 밖에 없고, 프로그램이 정전이나, 고장등으로 꺼지게 된다면 모든 데이터가 소멸되어 버립니다.

두번재로 파일시스템 상에 데이터를 저장할 경우, 프로그램이 종료되어도 데이터는 하드디스크에 저장이 되어 있으나 만약 여러대의 PC에서 같은 데이터를 각각의 파일시스템에서 관리해야하는 경우 동기화와 같은 무결성 원칙이 위배되기 매우 쉽습니다. 파일시스템은 파일의 전부를 읽어와야 하기 때문에 파일의 용량이 커지면 작업이 버겁고 비효율적이게 됩니다.

따라서 데이터베이스라는 독립적인 구역을 만들어 여러대의 PC에서 접근할 수 있도록 하는것이 훨씬 안전합니다.

데이터 베이스도 여러 종류가 있지만 그 중 관계형 데이터베이스에서 SQL(Structed Query Language)이란 언어를 사용합니다.

SQL은 구조화된 쿼리 언어입니다.

SQL은 구조화된(스키마가 정의된) 데이터베이스에서 원하는 데이터를 불러오고 입력하고 수정하고 삭제하기 위해 사용되는 프로그래밍 언어입니다.

SQL은 크게 3가지로 나뉘어집니다.
1. DDL
2. DML
3. DCL

SQL = DDL + DML + DCL이라고 하여도 무방합니다.

SELECT문

SELECT문은 기본적으로 데이터베이스 테이블에 있는 데이터들을 출력하기 위해 사용되는 DML문입니다.

SELECT [DISTINCT] {*,column[alias],...}
FROM table
WHERE conditions(s)
ORDER BY {column [ASC|DESC},...}

SELECT : 테이블에서 해당 열을 가져옵니다.
DISTINCT : 열의 중복을 제거합니다.
column : 출력하고 싶은 속성을 나열합니다. ( *와이드카드 사용시 모든 속성을 출력합니다)
속성1||속성2 로 속성2개를 합치기가 가능합니다.
alias : 출력 시, SQL이용시 속성을 다른 이름으로 표현합니다.
WHERE : 출력되는 레코들의 조건을 지정합니다.
ORDER BY : 정렬기준을 정합니다, ASC:오름차순(기본), DESC:내림차순

연산자

비교 연산자

연산자	설명
=	Equal
<>	Not Equal
>	Grater than
<	Less than
>=	Grather than or equal
<=	Less than OR equal

Between 연산자

학점이 3.0에서 3.5 사이인 학생의 이름과 학점 검색

SELECT sname, grade
FROM student
WHERE grade between 3.0 and 3.5;

WHERE grade >= 3.0 and grade <= 3.5

IN 연산자

지도 교수 id가 101, 201, 401인 학생의 학번, 이름, 학점, 지도교수 id를 출력

SELECT sid, sname, grade, advisor
FROM student
WHERE advisor in (101, 201, 401);

LIKE 연산자

문자열 패턴을 비교합니다.

% = 0또는 n개의 문자을 대신합니다.
_ = 1개의 문자를 대신합니다.

이름이 S로 시작하는 경우에만 출력

SELECT sname
FROM student
WHERE sname like 'S%';

이름이 4글자인 경우에만 출력

SELECT sname
FROM student
WHERE sname like '____';

논리 연산자

다른 프로그래밍과 마찬가지로 논리 비교연산자 사이에 조건의 관계를 정의합니다.

연산자	설명
AND	모든 조건이 만족
OR	하나 이상의 조건이 만족
NOT	주어진 조건을 부정

NULL

NULL은 존재하지 않거나, 모르는 상태를 표시합니다. 데이터가 저장되지 않았음을 의미하기도 합니다.

NULL 에 대한 산술 연산 -> NULL
NULL 에 대한 논리 연산 -> False

함수

문자 함수

LOWER, UPPER
INITCAP
LPAD, RPAD
SUBSTR
INSTR
LTRIM, RTRIM
LENGTH
TRANSLATE
REPLACE

LOWER, UPPER

학과 테이블에서 학과 이름은 소문자로, 단과 대학은 대문자로 출력

SELECT lower(dname), upper(college)
FROM department;

LPAD, RPAD

LPAD(필드, 문자 범위, ‘형식’), RPAD(필드, 문자 범위, '형식')에 따라 결과 출력

SELECT lpad(deptno, 10, ' '), lpad(dname, 20, '*'),
lpad(budget, 20, '.')
FROM department;

SUBSTR

Substr(‘문자열’ 혹은 필드, pos, n) 함수는 문자열의 pos 위치부터 n개의 문자를 출력. (n이 없으면 마지막까지)

SELECT dname, substr(dname, 2),
substr(dname, 3, 3)
FROM department;

INSTR

Instr(필드, '문자') – 필드에서 문자가 있는 위치를 출력
Instr(필드, '문자', 수1, 수2) – 수1부터 문자가 있는 위치를검색하되, 수2번째의 위치 출력

SELECT dname, instr(dname, 'e'), instr(dname, 'ic'),
instr(dname, 'e', 2, 2)
FROM department;

LENGTH

LENGTH 함수는 필드나 입력한 문자열의 문자 개수를 출력

SELECT deptno, length(deptno), dname, length(dname)
FROM department;

TRANSLATE

TRANSLATE(필드, ‘A’, ‘B’) 함수는 필드에서 문자 A를 찾아 B로 변환한 후 출력(문자 단위 변경)

SELECT dname, translate(dname, 'e', 'E'),
translate(dname, 'ie', 'IE')
FROM department;

숫자 함수

ROUND, CEIL, FLOOR
TRUNC
POWER
SQRT
SIGN
ABS
MOD

ROUND

ROUND(필드 혹은 입력값, 숫자) 함수는 입력값을 숫자만큼반올림(숫자가 양수이면 소수점 아래 숫자까지 출력, 음수이면 10의 숫자 제곱까지 나타냄)

SELECT round(45.923, 1), round(45.923),
round(45.323, -1), round(grade, 1)
FROM student
WHERE deptno=30;

POWER

POWER(필드 혹은 입력값, 숫자) 함수는 입력값의 숫자만큼제곱한 값을 출력

SELECT grade, power(grade,2), power(50,5)
FROM student
WHERE deptno = 30;

SQRT

SQRT(필드 혹은 입력값) 함수는 입력값의 제곱근을 출력

SELECT grade, sqrt(grade), sqrt(40)
FROM student
WHERE deptno = 30;

SIGN

SIGN(필드 혹은 입력값) 함수는 입력값 < 0이면 -1 출력, 입력값 = 0이면 0 출력, 입력값 > 0이면 1 출력

SELECT grade, grade – 3.0, sign(grade – 3.0)
FROM student
WHERE deptno = 30;

날짜 함수

날짜 + 숫자 -> 날짜
날짜 - 숫자 -> 날짜
날짜 - 날짜 -> 날짜 차이

날짜함수는 DBMS마다 다릅니다. 이것은 사용할 DBMS마다 찾아보길 바랍니다.

DATEDIFF

DATEDIFF(날짜1,날짜2) 함수는 날짜1과 날짜2 사이의 개월수 출력

SELECT DATEDIFF(sysdate(), hiredate),
DATEDIFF('1984-01-01', '1988-11-05')
FROM professor
WHERE DATEDIFF(sysdate(), hiredate) > 200;

DATEADD

DATEADD(날짜,숫자) 함수는 날짜의 달에 숫자만큼 더한 값 출력

SELECT hiredate, DATEADD(hiredate,3),
DATEADD(hiredate,-3)
FROM professor
WHERE deptno = 10 or deptno = 20;

NETX_DAY

NEXT_DAY(날짜, 문자) 함수는 입력 받은 날짜를 기준으로문자만큼 지난 날짜를 출력
(일요일=1, 월요일=2, 화요일=3, 수요일=4, 목요일=5, 금요일=6, 토요일=7)

SELECT hiredate, next_day(hiredate,'금요일'),
next_day(hiredate,6)
FROM professor
WHERE deptno=10 or deptno = 40;

LAST_DAY

LAST_DAY(날짜) 함수는 입력 받은 날짜가 포함된 달의 마지막 날짜 출력

SELECT sysdate, last_day(sysdate), hiredate,
last_day(hiredate), last_day('88/02/15')
FROM professor
WHERE deptno = 10 or deptno = 20;

날자 -> 문자열

Oracle

TO_CHAR(날짜, '문자') 함수는 입력 받은 날짜를 입력한 문자형으로 변환하여 출력

SELECT to_char(sysdate, 'DAY, DDTH MONTH YYYY')
FROM sys.dual;

MySQL

DATE_FORMAT(날짜, '문자') 함수는 입력 받은 날짜를 입력한 문자형으로 변환하여 출력

SELECT DATE_FORMAT(sysdate, 'DAY, DDTH MONTH YYYY')
FROM dual;

문자열 -> 날자

Oracle

TO_DATE(문자열, 포맷) 함수는 문자열을 포맷에 따라 해석하여 날짜를 반환

SELECT pid, pname, hiredate
FROM professor
WHERE hiredate = to_date('2002-06-11', 'yyyy-mm-dd');

MySQL

TO_DATE(문자열, 포맷) 함수는 문자열을 포맷에 따라 해석하여 날짜를 반환

SELECT pid, pname, hiredate
FROM professor
WHERE hiredate = to_date('2002-06-11', '%Y-%m-%d');

집계 함수

COUNT
SUM
AVG
MAX
MIN

AVG

AVG 함수는 필드의 평균값 출력

SELECT avg(grade)
FROM student;

COUNT

COUNT 함수는 필드의 레코드수 출력

SELECT count(*)
FROM student;

SUM

AVG 함수는 필드의 총합 출력

SELECT sum(grade)
FROM student;

MIN

MIN 함수는 필드의 최소값 출력

SELECT min(grade)
FROM student
WHERE deptno = 20;

MAX

MAX 함수는 필드의 최소값 출력

SELECT max(grade)
FROM student
WHERE deptno = 20;

GROUP BY 필드' 절은 동일한 필드 값을 갖는 레코드를 그룹으로 묶은다음, 각 그룹에 대해 집계 함수를 적용

WHERE절을 사용할 경우, GROUP BY 절 앞에 선언
SELECT 절에 나올 수 있는 필드는 GROUP BY의 필드와 집계 함수가 적용되는 필드로 국한됨

SELECT deptno, avg(grade)
FROM student
GROUP BY deptno;

Group By에서 특정 조건을 만족하는 그룹만을 검색하고자 할 경우, HAVING 사용 (WHERE은 단순 필드의 조건 시, HAVING은 그룹 함수에 대한 조건 시)

SELECT deptno, avg(grade)
FROM student
GROUP BY deptno
HAVING count(*)>3;

SubQuery

Subquery: 두 개의 query를 하나로 표현 (Subquery의 결과의 수가 하나일 경우, 'in' 대신에 '=' 연산 사용 가능)

학점이 가장 높은 학생의 이름과 학과, 학점을 출력

SELECT sname, deptno, grade
FROM student
WHERE grade = (select max(grade) from student);

40번 학과에서 학점이 가장 낮은 학생보다 학점이 더 높은 학생들의 이름과 학점, 그리고 학과를 출력

SELECT sname, grade, deptno
FROM student
WHERE grade>any (select grade from student where deptno = 40)
ORDER BY grade DESC;

40번 학과에 재학중인 모든 학생보다 학점이 더 높은 학생들의 이름과 학점, 그리고 학과를 출력

SELECT sname, grade, deptno
FROM student
WHERE grade>all (select grade from student where deptno = 40)
ORDER BY grade DESC;

학과별로 학점이 가장 높은 학생의 이름과 학점, 그리고 학과번호를 출력(두개의 필드를 비교)

SELECT sname, grade, deptno
FROM student
WHERE (grade, deptno) in
(select max(grade), deptno from student group by deptno);

30번 학과보다 평균 학점이 높은 학과의 학과 번호와 평균 학점을 출력(Having 응용)

SELECT deptno, avg(grade)
FROM student
GROUP BY deptno
HAVING avg(grade) > (select avg(grade)
from student
where deptno = 30);

Join

Join은 하나이상의 테이블에 포함된 속성들을 검색 결과로 출력할때 사용합니다.
Join을 하기 위해서는 공통된 속성을 하나이상 가져야 하며 두 테이블은 공통된 테이블로 연결합니다.

Join의 종류 : Inner Join, LEFT Outer Join, RIGHT Outer Join, FULL Outer Join

INNER JOIN

학생 테이블과 학과 테이블에서 학번, 학과 번호, 단과대학 필드 선택

SELECT s.sid, d.deptno, d.college
FROM student s, department d
WHERE s.deptno = d.deptno;

학점이 3.5 이상인 학생들의 이름, 학과 이름, 지도교수 이름, 그리고 학점을 출력(3개의 테이블 조인)

SELECT sname, dname, pname, grade
FROM student s, department d, professor p
WHERE s.deptno = d.deptno
AND s.advisor = p.pid
AND grade >= 3.5;

OUTER JOIN

소속 교수가 없는 학과의 경우, 학과 번호와 이름만 출력(LEFT OUTER JOIN)

SELECT d.deptno, dname, pname
FROM department d LEFT OUTER JOIN professor p
ON d.deptno = p.deptno
WHERE d.deptno <> 20;

공통된 속성의 이름이 같을 경우, using 절 사용 가능

SELECT d.deptno, dname, pname
FROM department d LEFT OUTER JOIN professor p
USING(deptno)
WHERE d.deptno <> 20;

INSERT

INTEREST INTO 테이블(속성,,,,) VALUES (값,,,)는 데이터베이스에 레코드를 삽입하는 DML문입니다. 모든속성, 임의의 속성, SELECT문의 결과로 레코드 삽입이 가능합니다.

INSERT INTO department
VALUES (60, 'Statistics', 'Sciences', 30000000);

INSERT INTO department(deptno, dname)
VALUES (70, 'Philosophy');

INSERT INTO d10student(stno, name, address, grade)
SELECT sid, sname, addr, grade
FROM student
WHERE deptno = 10;

UPDATE

UPDATE 테이블 SET 속성=값 WHERE 조건은 이미 존재하는 레코드의 값을 수정할 수 있습니다.

학생 테이블에서 Taehee의 데이터 갱신

UPDATE student
SET deptno=10, addr = 'Daegu', grade = 3.89
WHERE sname = 'Taehee';

Database를 전공한 교수가 소속된 학과의 예산을 최고 수준으로 인상

UPDATE department
SET budget = (SELECT max(budget) FROM department)
WHERE deptno in (SELECT deptno FROM professor
WHERE major = 'Database');

DELETE

DELETE FROM 테이블 WHERE 조건은 테이블에 존재하는 레코드를 삭제하는 DML입니다.

학생 테이블에서 학과번호가 20인 행 값 삭제 (이때 WHERE 절이 없으면, 모든 데이터가 삭제됨)

DELETE FROM student
WHERE deptno = 20;

학생이 없는 학과를 삭제

DELETE FROM department
WHERE deptno NOT IN (SELECT DISTINCT deptno
FROM student);

DDL

DDL은 데이터 정의어로써 테이블의 스키마를 정의하는 명령입니다. 레코드의 입력과 삭제를 하기 위한 구조를 정의하는 명령입니다.

DDL = CREATE + DROP + ALTER

CREATE

테이블, 인덱스, 데이터베이스등 스키마를 생성합니다.

CREATE TABLE table_name
(column_name type (size) [NULL/NOT NULL],
column_name type (size) [NULL/NOT NULL],
…)

CREATE TABLE table_name(column,column,...)
AS
SELECT column,column,...
FROM table
WHERE 조건

DROP

테이블,인덱스,데이터베이스등 스키마를 삭제합니다.

DROP TABLE 테이블, DROP DATABASE 데이터베이스, DROP INDEX 인덱스,,,,,

ALTER

스키마에 구성인 속성을 추가하거나 삭제합니다.

Alter Table 테이블 Add (new속성 타입(사이즈))

Alter Table 테이블 Modify (속성 타입(사이즈))

후기

백엔드 개발자라면 관계형 데이터베이스의 SQL은 알고 있어야 유연하고 자연스럽게 데이터베이스로부터 데이터를 불러올 수 있습니다. 데이터베이스는 형태는 각각 다르더라도 서버운영에 필수적인 것은 부정할 수 없는 사실입니다. 꼭 알아두어야 해서 집중하였습니다.

GitHub

없음!

유태형

오늘도 내일도 화이팅!

이전 포스트

[29일차] RestAPI, OpenAPI, Postman

다음 포스트

[30일차]SQL

코드스테이츠

오늘의 목표

내용

SQL이란?

SELECT문

연산자

비교 연산자

Between 연산자

IN 연산자

LIKE 연산자

논리 연산자

NULL

함수

문자 함수

LOWER, UPPER

LPAD, RPAD

SUBSTR

INSTR

LENGTH

TRANSLATE

숫자 함수

ROUND

POWER

SQRT

SIGN

날짜 함수

DATEDIFF

DATEADD

NETX_DAY

LAST_DAY

날자 -> 문자열

Oracle

MySQL

문자열 -> 날자

Oracle

MySQL

집계 함수

AVG

COUNT

SUM

MIN

MAX

SubQuery

Join

INNER JOIN

OUTER JOIN

INSERT

UPDATE

DELETE

DDL

CREATE

DROP

ALTER

후기

GitHub

[29일차] RestAPI, OpenAPI, Postman

[31일차]데이터베이스 설계

0개의 댓글