[데이터베이스] DML - SELECT 😂

Narcoker·2024년 5월 19일

데이터베이스

목록 보기

15/17

그 날의 내 무지함을 항상 상기하고 성장하자..ㅎ
그 때의 내 쿼리문.. 학습하고 다시 생각해보니 웃음 밖에 안 나오네 😂😂

그땐 그랬지 하는 순간이 오길 바라며

DML

데이터베이스 내의 데이터를 조작하는 데 사용되는 SQL(Structured Query Language) 명령어의 집합
DML 명령어는 데이터의 삽입, 수정, 삭제, 조회와 같은 작업을 수행

SELECT

데이터베이스 테이블에 저장된 데이터를 조회

기본적인 SELECT 문 구조

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

DISTINCT : 중복 레코드 제거

column [alias] : 속성, 공백 이후 [alias]를 작성하면 이 속성명을 다른 이름으로 출력

WHERE절: 조건

ASC | DESC : 오름차순 | 내림차순, 미작성 시 기본값은 ASC

명령어, 테이블명, 속성명은 대소문자를 구분하지 않는다.

특정 속성만 출력

학생 테이블에서 학번과 이름만 출력

SELECT sid, sname
FROM student

테이블의 모든 열 출력

학생 테이블에서 모든 열 출력

SELECT * 
FROM student;

DISTNCT를 이용하여 중복 제거

학생 테이블에서 소속 학과를 출력

SELECT deptno
FROM stduent;

SELECT DISTNCT deptno
FROM stduent;

특정 필드 이름을 변경하여 출력

학생 테이블에서 deptno를 department로 바꿔 출력
공백을 사용해서 이름을 지정한다.

SELECT sid, sname, deptno department
FROM student;

열을 결합하여 출력

학번과 이름을 합치고(|| 연산자 이용)
열 이름을 stduent로 출력

SELECT sid||sname stduent
FROM student;

정렬 출력

이름, 주소, 생년월일을 출력하되 생년월일의 내림차순으로 정렬

SELECT sname, addr, birthdate
FROM student
ORDER BY birthdate DESC;

학과, 이름, 학점을 출력하되
학과 순으로 우선 정렬 후, 학점이 높은 순서로 다음 정렬

SELECT deptno, sname, grade
FROM student
ORDER BY deptno, grade DESC;

검색 조건 부여

= : Equal

<> : Not Equal (!= 도 가능)

> : Greater than

< : Less than

>= : Greater than or equal to

<= : Less than or equal to

BETWEEN ... AND : 구간 검색
IN : 집합의 원소인지를 검사
IS (NOT) NULL : NULL 검사
LIKE : 문자열의 패턴 검사

-- 학생 테이블에서 `여학생들`의 학번, 이름, 학과, 주소를 출력

SELECT sid, sname, deptno, addr
FROM student
WHERE gen = 'F'; -- 문자 데이터인 경우 인용부호(' ')를 사용

비교 연산자

학점이 3.5 이상의 학생의 이름, 학과, 학점 출력

SELECT sname, deptno, grade
FROM student
WHERE grade >= 3.5;

BETWEEN ... AND 연산자

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

SELECT sname, grade
FROM student
WHERE grade BETWEEN 3.0 AND 3.5;

-- 또는

SELECT sname, grade
FROM student
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);

-- 또는

SELECT sid, sname, grade, advisor
FROM student
WHERE advisor = 101 or advisor = 201 or advisor = 401;

LIKE 연산자

문자 데이터에 대하여 조건 건색
% : 0개 이상의 문자열이 존재
_ : 1개 이상의 문자열이 존재

전체 문자열 혹은 여러 문자를 구별 - %

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

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

문자 개수 구별 - _

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

SELECT sname
FROM student
WHERE sname LIKE '____';

NOT 연산자

NOT BETWEEN ... AND, NOT IN, NOT LIKE 등에 사용하여 부정을 의미

학점이 3.0과 3.5 사이가 아닌 학생들의 이름과 학점 출력

SELECT sname, grade
FROM stduent
WHERE grade NOT BETWEEN 3.0 AND 3.5;

NULL

존재하지 않거나 모르는 데이터의 상태를 표시
해당 열에 데이터가 저장되지 않았음을 의미

공간 효율성을 위하여 압축이 가능

NULL 에 대한 연산들

산술 연산
10 + NULL -> NULL
10 * NULL -> NULL

비교 연산
10 = NULL -> FALSE
10 > NULL -> FALSE
10 <> NULL -> FALSE

지도 교수가 아직 정해지지 않은 학생의 학번과 이름을 출력

SELECT sid, sname
FROM student
WHERE advisor IS null;

문자 함수 - LOWER, UPPER, INITCAP

LOWER : 소문자로 출력

UPPER : 대문자로 출력

INITCAP : 첫글자만 대문자로 출력

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

SELECT LOWER(dname), UPPER(college)
FROM department;

문자 함수 - LPAD

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

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

문자 함수 - SUBSTR

SUBSTR('속성명', 출발위치, 길이)에 따라
출발 위치 부터 길이 만큼 출력

길이는 옵션 값으로 없으면 끝까지 출력한다.

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

속성이나 입력한 문자열의 문자 개수를 출력

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

문자 함수 - TRANSLATE

TRANSLATE(필드, 'A', 'B') 형태로 필드에서 문자 A를 찾아 B로 변환 후 출력

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

숫자 함수 - ROUND

ROUND(필드 혹은 입력값, 숫자) 형태로
입력값을 숫자 만큼 반올림

숫자가 양수면 소수점 아래 숫자까지 출력
숫자가 음수면 10의 숫자 제곱까지 출력

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

숫자 함수 - TRUNC

ROUND(필드 혹은 입력값, 숫자) 형태로
입력값을 숫자 만큼 버림

20번 학과에 소속된 교수들의 근무 연수를 출력

SELECT pid, pname, hiredate, TRUNC((SYSDATE - hiredate) / 365) -- 근무 연수
FROM professor
WHERE deptno = 20;

숫자 함수 - 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;

날짜 함수 - 산술 연산

날짜 필드는 산술 연산이 가능하다
SYSDATE 는 현재 날짜를 출력한다.

SELECT hiredate hiredate - 7, hiredate + 7, SYSDATE - hiredate
FROM professor
WHERE hiredate LIKE '%01';

SYSDATE - hiredate : 임용된 이후 며칠이 지났는지

날짜 함수 - MONTHS_BETWEEN

MONTHS_BETWEEN(날짜1, 날짜2) 형태로
날짜1과 날짜2 사이의 개월 수를 출력

SELECT MONTHS_BETWEEN(SYSDATE, hiredate), MONTH_BETWEEN('84/01/01, '88/11/05')
FROM professor
WHERE MONTHS_BETWEEN(SYSDATE, hiredate) > 200;

날짜 함수 - ADD_MONTHS

ADD_MONTH(날짜, 숫자) 형태로
날짜의 달에 숫자만큼 더한 값 출력

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

날짜 함수 - NEXT_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;

기타 함수 - TO_CHAR

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

SELECT TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY')
FROM sys.dual;

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM sys.dual;

기타 함수 - TO_DATE

TO_DATE(문자열, 포맷) 형태로
문자열을 포맷에 따라 해석하여 날짜를 반환

2002년 6월 11에 임용된 교수를 출력

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

기타 함수 - DECODE

DECODE(필드, 조건1, 조건1의 참, 조건2, 조건2의 참, ..., 거짓) 형태로
필드에서 조건의 참, 거짓에 따라 출력

SELECT sname, deptno, decode(deptno, 10, 'CE', 20, 'ME', 'NOT Eng') DECODED_DEPT
FROM student;

집계 함수 - AVG

특정 필드의 평균 값을 출력한다.

SELECT AVG(grade)
FROM student;

집계 함수 - MIN

특정 필드의 최소값을 출력한다.

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

집계 함수 - MAX

특정 필드의 최대값을 출력한다.

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

집계 함수 - SUM

집계 함수 - COUNT

결과 행의 수를 출력한다.

SELECT count(*)
FROM student
WHERE deptno = 10;

GROUP BY

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

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

SELECt deptno, AVG(grade)
FROM student
GROUP BY deptno;

HAVING

GROUP BY 에서 특정 조건을 만족하는 그룹만을 검색하고자 할 때 사용

WHERE은 단순 필드 조건 시, HAVING은 그룹 함수에 대한 조건 시 사용

학생 테이블에서 학과별로 평균 점수를 구하여 출력하되,
학과에 학생이 3명 보다 많은 학과만 출력

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

Subquery

두 개 이상의 쿼리를 하나로 표현
Subquery의 결과의 수가 하나일 경우 IN 대신에 = 연산 사용 가능

공대에 소속된 학과에 재학 중인 학생들의 이름은?

-- 공대에 소속된 학과들 출력

SELECT deptno
FROM department
WHERE collage = 'Engineering';

-- 공대 학과: 10, 20

위 쿼리문을 아래 (10, 20) 에 대입

SELECT sname
FROM student
WHERE deptno in (10, 20);

결과

SELECT sname
FROM student
WHERE deptno in 
(
SELECT deptno
FROM department
WHERE collage = 'Engineering';
);

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

SELECT sname, deptno, grade
FROM student
WHERE grade = 
(
SELECT MAX(grade)
FROM student;
);

> any

40번 학과에서 학점이 가장 낮은 학생보다 학점이 더 높은 학생들의
이름과 학점, 그리고 학과를 학점을 기준으로 내림차순을 출력

SELECT sname, grade, deptno
FROM stduent
WHERE grade > any
(
SELECT MIN(grade)
FROM student
WHERE deptno = 40
)
ORDER BY grade DESC;

> all

40번 학과에 재학 중인 모든 학생보다 학점이 더 높은 학생들의
이름과 학점 그리고 학과를 내림차순으로 출력

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

GROUP BY 응용 🔴

학과별로 학점이 가장 높은 학생의 이름과 학점, 그리고 학과번호를 학점을 기준으로 내림차순 출력

SELECT sname, grade, deptno
FROM student
WHERE (deptno, grade) IN (
    SELECT deptno, MAX(grade)
    FROM student
    GROUP BY deptno;
)
ORDER BY grade DESC;

WHERE (deptno, grade) IN (...) 구문은 SQL에서 여러 열을 기준으로 비교하는 방식
IN 앞에 필드가 하나 이상일 경우 Subquery의 select 절에서도 동일한 수의 필드가 필요

HAVING 응용

30번 학과보다 평균 학점이 높은 학과의 학과 번호와 평균 학점을 출력

SELECT deptno, AVG(grade)
FROM student
GROUP BY deptno
HAVING AVG(grade) > 
(
SELECT AVG(grade)
FROM student
WHERE deptno = 30;
);

많은 중첩 쿼리

공과대학('Engineering')에서 학점이 가장 높은 학생의 학번과 이름, 그리고 학과와 학점을 출력
(공과대학 번호 모름, 혹은 계속 변경될 우려가 있음)

-- 틀린 쿼리문 : 이 학생이 공대생이 아닐 수도 있다!

SELECT sid, sname, deptno, grade
FROM student
WHERE grade = 
-- 3. 공과대학의 최고점과 같은 학점을 가진 학생의 정보를 불러온다 
-- 						=> 다른 대학의 학생이 최고점과 같은 학점인 가능성이 있다!!
	-- 2. 공과대학에 속해있는 학과의 학생들 중 최고점을 뽑아온다.
	(
	SELECT MAX(grade) 
	FROM student
	WHERE deptno in 
		-- 1. 공과대학인 학과의 번호들을 가져 온다.
		(
			SELECT deptno from department
			FROM department
            WHERE colleage = 'Engineering';
		);
	)

-- 맞는 쿼리문
-- 공과대학인지와 최고점인지를 모두 확인해야 한다.

SELECT sid, sname, deptno, grade
FROM student
WHERE deptno IN (
    SELECT deptno
    FROM department
    WHERE colleage = 'Engineering'
)
AND grade = (
    SELECT MAX(grade)
    FROM student
    WHERE deptno IN (
        SELECT deptno
        FROM department
        WHERE colleage = 'Engineering'
    )
);

-- 위 쿼리문을 최적화 한 것
-- 공과 대학인 학과에 있는 학생 정보를 모두 뽑아온 뒤 내림차순을 정렬한다.
-- 최대 개수를 1로 제한하여 최고점을 가진 학생만 뽑아올 수 있도록 한다.

SELECT sid, sname, deptno, grade
FROM student
WHERE deptno IN (
    SELECT deptno
    FROM department
    WHERE colleage = 'Engineering'
)
ORDER BY grade DESC
LIMIT 1;

중첩문의 연걸

자신이 속한 학과의 평균 학점 보다 학점이 더 낮은 학생의 이름과 학과, 그리고 학점을 출력

SELECT sname, deptno, grade
FROM student s
WHERE grade < 
(
SELECT AVG(grade)
FROM student
WHERE deptno = s.deptno; --자신의 학과 번호
)
ORDER BY deptno;

EXISTS

EXIST 연산자는 서브퀴리의 결과값이 한개 이상 존재할 경우 true 반환

지도학생이 있는 교수의 이름과 학과, 그리고 전공을 출력

SELECT pname, deptno, major
FROM professor p
WHERE EXISTS 
(
SELECT sid
FROM student
WHERE advisor = p.pid
)
ORDER BY deptno;

집합 함수 - UNION

두 개의 질이 결과를 합집합으로 출력
중복된 데이터는 제거한다.

SELECT addr
FROM student
WHERE deptno = 30

UNION

SELECT addr
FROM student
WHERE deptno = 40;

집합 함수 - INTERSECT

두 개의 질이 결과를 교집합으로 출력

SELECT addr
FROM student
WHERE deptno = 30

INTERSECT

SELECT addr
FROM student
WHERE deptno = 40;

집합 함수 - MINUS

앞의 질의 결과에서 뒤의 질의 결과를 차집합으로 출력

SELECT addr
FROM student
WHERE deptno = 30

MINUS

SELECT addr
FROM student
WHERE deptno = 40;

Narcoker

열정, 끈기, 집념의 Frontend Developer

이전 포스트

[데이터베이스] DDL

다음 포스트

[데이터베이스] DML - SELECT 😂

데이터베이스

DML

SELECT

기본적인 SELECT 문 구조

특정 속성만 출력

테이블의 모든 열 출력

DISTNCT를 이용하여 중복 제거

특정 필드 이름을 변경하여 출력

열을 결합하여 출력

정렬 출력

검색 조건 부여

비교 연산자

BETWEEN ... AND 연산자

IN 연산자

LIKE 연산자

전체 문자열 혹은 여러 문자를 구별 - %

문자 개수 구별 - _

NOT 연산자

NULL

NULL 에 대한 연산들

문자 함수 - LOWER, UPPER, INITCAP

문자 함수 - LPAD

문자 함수 - SUBSTR

문자 함수 - INSTR

문자 함수 - LENGTH

문자 함수 - TRANSLATE

숫자 함수 - ROUND

숫자 함수 - TRUNC

숫자 함수 - POWER

숫자 함수 - SQRT

숫자 함수 - SIGN

날짜 함수 - 산술 연산

날짜 함수 - MONTHS_BETWEEN

날짜 함수 - ADD_MONTHS

날짜 함수 - NEXT_DAY

날짜 함수 - LAST_DAY

기타 함수 - TO_CHAR

기타 함수 - TO_DATE

기타 함수 - DECODE

집계 함수 - AVG

집계 함수 - MIN

집계 함수 - MAX

집계 함수 - SUM

집계 함수 - COUNT

GROUP BY

HAVING

Subquery

> any

> all

GROUP BY 응용 🔴

HAVING 응용

많은 중첩 쿼리

중첩문의 연걸

EXISTS

집합 함수 - UNION

집합 함수 - INTERSECT

집합 함수 - MINUS

[데이터베이스] DDL

[데이터베이스] DML - INSERT, UPDATE, DELETE

0개의 댓글