SQL : DML

Vorhandenheit ·2021년 12월 17일
Database
0

Database

목록 보기
4/28

DML : Data Manipulation Language

테이블에 데이터를 검색, 삽입 수정, 삭제하는 데 사용
SELECT, INSERT, UPDATE, DELETE 가 있습니다.

1. SELECT

select 구문의 기능은 절차 지향형 언어의 '함수'와 같습니다.

(1) SELECT * FROM

SELECT * FROM 테이블명 ;

  • ; : 명령문의 마지막에는 꼭 ';' 을 입력하기, 붙이지않고 ENTER키를 누르면 입력중인 것으로 간주되어 명령문이 실행되지않음

    • : "모든 열" 을 의미

  • FROM : 처리 대상을 지정하는 키워드, 테이블 명을 지정합니다.

대소문자 구별
예약어와 데이터베이스 객체명은 대소문자를 구별하지 않습니다.

(2) SELECT 결과

  • SELECT 명령을 실행하면 표 형식의 데이터가 출력이 됩니다. 이 데이터를 테이플이라고 합니다.
  • 이 표 형식의 데이터는 '행(레코드)' 와 '열(컬럼/필드)'로 구성이 됩니다.
  • 행과 열이 만나는 부분은 '셀'이라고 부릅니다.
  • 이 셀 안에는 '수치형'데이터, '문자열형'데이터, '날짜시간형'데이터 가 들어갑니다.
  • NULL은 아무것도 저장되어있지 않은 상태를 의미합니다.

2. DESC

테이블 구조를 참조합니다.

(1) DESC

DESC 테이블명

자료형

  • INTERGER 형 : 수치형의 하나로 정수값을 저장할 수 있는 자료형(소수점X)
  • CHAR 형 : 문자열형의 하나로 문자열을 저장할 수 있는 자료형
    CHAR(10)이라고 되어있을 경우 최대 10문자로된 문자열을 저장할 수 있습니다.
    VARCHAR 와 비교해서 '고정 길이 문자열' 자료형이라고도 합니다.
  • VARCHAR 형 : CHAR형과 다르게 데이터 크기에 맞춰 저장공간의 크기도 변경이 됩니다. '가변 길이 문자열'자료형이라고 합니다.
  • DATE 형 : 날짜값을 저장할 수 있는 자료형
  • TIME 형 : 시간을 저장할 수 있는 자료형

3. 검색 조건 지정

데이터 검색에는 열을 지정하는 방법과 행을 지정하는 방법이 있습니다.

(1) SELECT 구에서 열 지정하기

SELECT 열1, 열2 FROM 테이블명

지정한 열의 순서대로 표시됩니다.

(2) WHERE 구에서 행 지정하기

SELECT 열 FROM 테이블명 WHERE 조건식

  • 조건지정
    = : ~와 같음
    <> : ~와 같지않음
    ' >= : ~이상
    ' > : ~보다 큼
    <= : ~이하
    < : ~보다 작음

A. 구의 순서와 생략

SELECT FROM WHERE 순서로 적어야됩니다. WHERE이 FROM 앞으로 오면 에러가 발생합니다.

B. WHERE 구

조건식을 작성할 때, '열과 연산자, 상수로 구성되어있는 식'입니다.
열의 이름이 no이고 두번째 행을 가지고 오고싶다면 밑에처럼 작성하면 됩니다

SELECT * FROM sample WHERE no=2;

문자열 형일 경우는 싱글 쿼트('')로 둘러싸 표기해야합니다.

(3) NULL 값 검색

SELECT * FROM sample WHERE 열=NULL;

이렇게 검색하면 NULL 값은 되지않습니다. NULL 값을 검색할 때에는 'IS NULL' 을 사용해야합니다.

SELECT * FROM sample WHERE IS NULL;

4. LIKE

'LIKE' 술어를 사용하면 문자열의 일부분을 비교하는 '부분 검색'을 할 수 있습니다.

(1) LIKE로 패턴 매칭하기

= 연산자로 검색할 경우에는 열 값이 완전히 일치할 때 참이 됩니다. 하지만 LIKE 술어를 사용하면 열 값이 부분적으로 일치하는 경우에도 참이 됩니다.

열명 LIKE '패턴'

  • 패턴을 정의할 때 사용할 수 있는 메타문자로는 % 와 _이 있습니다.
WHERE text LIKE 'SQL%' // text 열 안에 SQL 들어간 행을 찾습니다.

문자열 도중에 있는 'SQL'을 검색하고자 한다면 '%SQL%'바꾸면 됩니다.

전방일치 : SQL%
중간일치: %SQL%
후방일치: %SQL

주의

%를 LIKE로 검색할 경우에는 \%로
_를 검색할 경우에는 _로 합니다

5.ORDER BY

SELECT 명령의 ORDER BY 구를 사용하여 검색결과의 행 순서를 바꿀 수 있습니다.

(1) ORDER BY로 검색 결과 정렬

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명

(2) ORDER BY 정렬 방향

  • 내림차순
SELECT 열명 FROM 테이블명 ORDER BY 열명 DESC

-오름차순

SELECT 열명 FROM 테이블명 ORDER BY 열명 ASC

ORDER BY 는 테이블에 영향을 주지않습니다. 저장장치에 저장된 데이터의 행 순서를 변경하는 것은 아닙니다!

(3) 복수 열 지정해 정렬

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명1, 열명2

NULL 값 같은 경우 MySQL 같은 경우에는 가장 작은 값으로 취급해 ASC에는 가장 먼저, DESC에는 가장 나중에 표시됩니다.

6.LIMIT

(1) 행수 제한

LIMIT 구는 SELECT 명령 마지막에 지정하는 것으로, WHERE 구나 ORDER BY 구의 뒤에 저장합니다.

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 LIMIT행수

WHERE no <=3 같은 조건을 붙인다면 LIMIT 3과 똑같은 결과를 얻을 수 있습니다. 하지만 내부처리 순서가 전혀 다릅니다. LIMIT는 반환할 행수를 제한하는 기능으로, WHERE 구로 검색한 후 ORDER BY로 정렬된 뒤 최종적으로 처리됩니다.

(2) OFFSET

페이지 나누기 기능은 LIMIT을 사용해 가단히 구현할 수 있습니다.

SELECT 열명 FROM 테이블명 LIMIT 행수 OFFSET 위치

7. INSERT

데이터베이스 테이블에 행을 추가하기 위해서는 INSERT 명령을 사용합니다.

(1) INSERT로 행 추가하기

INSERT INTO 테이블명 VALUES (값1, 값2...)

값을 지정할 떄는 해당 열의 데이터 형식에 맞도록 지정해야합니다.

(2) 값을 지정할 열 지정하기

INSERT INTO 테이블명 (열1, 열2..) VALUES (값1, 값2...)

열을 지정하지않으면 디폴트값으로 행이 추가가 됩니다.

8. DELETE

(1) DELETE로 행 삭제하기

DELETE 명령은 WHERE 조건에 일치하는 '모든 행'을 삭제하니다

DELETE FROM 테이블명 WHERE 조건식;

9. UPDATE

(1) UPDATE로 데이터 갱신하기

테이블의 셀값을 갱신하는 명령입니다.

UPDATE 테이블명 SET 열명=값 WHERE 조건식
  • WHERE 값을 생략하면 테이블의 모든 행이 갱신 대상이 됩니다.

(2) 복수열 갱신

UPDATE 명령의 SET 구에서는 필요에 따라 콤마로 구분하여 갱신할 열을 여러개 지정할 수 있습니다.

UPDATE 테이블명 SET 열명1=값1, 열명2=값2... WHERE 조건식

NULL로 값을 갱신할 경우에는 'NULL 초기화'라고 부릅니다!

10. 집계함수

(1) COUNT

A. COUNT로 행 개수 구하기

SQL은 집합을 다루는 집계함수를 제공합니다. 집계함수는 인수로 집합을 지정합니다 그렇기 떄문에 집합함수라고도 불립니다.

COUNT (집합)

SELECT COUNT(*) FROM sample
  • WHERE 구 지정
SELECT COUNT(*) FROM sample WHERE no=1

SELECT 구는 WHERE 구보다 나중에 내부적으로 처리됩니다. 따라서 WHERE구로 조건을 지정하면 테이블 전체가아닌, 검색된 행이 COUNT로 넘겨집니다.

B. 집계함수와 NULL 값

집계함수는 집합 안에 NULL 값이 있는 경우는 무시합니다!

DISTINCT

집합을 다룰 때, 집합 안에 중복된 값이 있을 수 있습니다. 그럴때 DISTINCT는 중복된 값을 제거합니다.

SELECT DISTINCT 열명 FROM 테이블명

(2) SUM

집합의 합계를 구할 수 있습니다.

SELECT SUM(열명) FROM sample

마찬가지로 null 값은 무시합니다.

(3) AVG

평균값을 구할 수 있습니다

SELECT AVG(열명) FROM sample

(4) MIN, MAX

MIN 집계함수, MAX 집계함수를 사용해 집합에서 최솟값과 최댓값을 구할 수 있습니다.

SELECT MIN(열명), MAX(열명) FROM sample

11. GROUP BY

(1) GROUP BY 로 그룹화

GROUP BY 구에 열을 지정하여 그룹화하면 지정된 열의 값이 같은 행이 하나의 그룹으로 묶입니다.

SELET 열명 FROM sample GROUP BY 열명
  • GROUP BY를 지정해 그룹화하면 DISTINCT와 같이 중복을 제거하는 효과가 있습니다.
  • GROUP BY 구로 그룹화하면 그룹히 하나의 집합으로 집계함수의 인수로 넘겨집니다

(2) HAVING 조건 지정

집계함수에서는 WHERE을 사용해서 조건 지정을 할 수 없습니다. 왜냐하면 WHERE 구로 행을 검색하는 처리가 GROUP BY로 그룹화하는 처리보다 순서상 앞서기 떄문입니다.

HAVING 구는 GROUP BY구 뒤에 기술합니다.

SELECT 열명, COUNT(열명) FROM sample GROUP BY 열명 HAVING COUNT(열명)=1;

내부 처리 순서
WHERE 구 => GROUP BY 구 => HAVING 구 => SELECT 구 => ORDER BY 구

  • 내부 처리순서 떄문에 별명 역시 사용할 수 없습니다.

(3) 복수열의 그룹화

먼저 주의할 점은 GROUP BY에서 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 지정할 수 없습니다.

SELECT no, name(그룹화된), quantity FROM sample GROUP BY name; //이렇게 사용할수 없습니다

no, quantitiy 역시 그룹화한다면 사용할 수 있습니다.

12. 서브쿼리

서브쿼리는 SELECT 명령에 의한 데이터 질의로, 하부의 부수적 질의를 의미합니다.

(1) DELETE의 WHERE 구에서 서브쿼리 사용

최솟값을 가지는 행 삭제

DELETE FROM sample WHERE a=(SELECT MIN(a) FROM sample)

서브 쿼리를 사용하면 delete와 select를 결합할 수 있습니다.

(2) 스칼라 값

SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'라고 합니다.
스칼라 값을 반환하는 서브 쿼리를 특별히 '스칼라 서브쿼리'라고 부르기도합니다.

서브쿼리 패턴

SELECT MIN(a) FROM sample // 하나의 값을 반환하는 패턴
SELECT 열명 FROM sample// 복수의 행이 반환되지만 열은 하나인 패턴
SELECT MIN(열명1), MAX(열명2) FROM sample // 하나의 행이 반환되지만 열은 하나인 패턴
SELECT 열명1, 열명2, FROM sample // 복수의 행, 복수의 열이 반환되는 패턴

(3) SELECT 구에서 서브쿼리 사용하기

SELECT
	(SELECT COUNT(*) FROM sample1 AS sql1,
  	SELECT COUNT(*) FROM sample3 AS sql2);

(4) SET 구에서 서브쿼리 사용하기

UPDATE sample SET a=(SELECT MAX(a) FROM sample);

SET 구에서 서브쿼리를 사용할 경우에도 스칼라 값을 반환하도록 스칼라 서브 쿼리를 지정할 필요가 있습니다.

(5) FROM 구에서 서브쿼리 사용하기

'네스티드 구조' 또는 '중첩구조' '내포구조'라고 부릅니다.

SELECT * FROM (SELECT * FROM sample) sq;

(6) INSERT 명령과 서브쿼리

INSERT 명령에는 VALUES 구의 일부로 서브쿼리를 사용하는 경우와
VALUES 구 대신 SELECT 명령을 사용하는 두가지 방법이 있습니다.

  • VALUES 구에서 서브 쿼리 사용
INSERT INTO sample VALUES(
	(SELECT COUNT(*) FROM sample1),
  	(SELECT COUNT(*) FROM sample2)
);
SELECT * FROM sample
  • SELECT 결과를 INSERT
INSERT INTO sample SELECT 1, 2;
SELECT * FROM sample;

13. 상관 서브쿼리

(1) EXISTS

EXISTS 술어를 사용해, 서브쿼리를 사용해 검색할 때 '데이터가 존재하는지 아닌지' 판별하기 위한 조건을 지정할 수 있습니다.

UPDATE sample1 SET a="있음" WHERE EXISTS (SELECT * FROM sample2 WHERE no2 =no);

EXISTS 술어에 서브쿼리를 지정하면 서브쿼리가 행을 반환할 경우에 참을 돌려줍니다.

(2) NOT EXISTS

'없음'의 경우, 행이 존재하지 않는 상태가 참이 되므로 이때는 NOT EXISTS를 사용합니다. NOT을 붙이는 것으로 값을 부정할 수 있습니다.

UPDATE sample1 SET a='없음' WHERE NOT EXISTS (SELECT * FROM sample2 WHERE no2=no);

(3) 상관 서브쿼리

부모 명령과 지식인 서브쿼리가 특정관계를 맺는 것을 '상관 서브쿼리'라고 부릅니다

UPDATE sample1 SET a ="있음" WHERE EXISTS (SELECT * FROM sample2 WHERE no2=no);

(4) IN

스칼라 값끼리 비교할 때는 = 연산자를 사용해야합니다. 다만 집합을 비교할 때는 사용할 수 없습니다. 이때 IN을 사용하면 집합 안의 값이 존재하는지 조사할 수 있습니다.

열명 IN (집합)
SELECT * FROM sample1 WHERE no IN (SELECT no2 FROM sample2);

IN에서는 집합안에 NULL 값이 있어도 무시하지않습니다.

14. 수치 연산

(1) 사칙 연산

SQL에서도 연산자를 이용해 여러가지 연산을 할 수 있습니다.

연산자의 우선순위
1.곱셈, 나눗셈
2.덧셈, 뺼셈

(2) SELECT 구로 연산하기

SELECT 식1, 식2...FROM 테이블명

SELECT *, price * quantitiy FROM sample

(3) 열의 별명

SELECT *, price * quantity AS amount FROM sample

이름을 붙일 떄는 숫자로 시작할 수 없습니다. 이름이 숫자로 시작한다면 수치형 상수를 의미하는 건지 데이터베이스 객체명을 의미하는 것인지 구별할 수 없습니다. 그래서 데이터베이스 객체명은 '숫자로 시작해서는 안 된다'라는 제약이 생겼습니다.

(4) WHERE 구에서 연산하기

데이터베이스 서버 내부에서 WHERE 구 => SELECT 구 순서로 처리됩니다.
그렇기 때문에 SELECT 구 에서 지정한 별명은 WHERE에서 사용할 수 없습니다.

SELECT *, price * quantity AS amount FROM sample WHERE amount >= 2000;

이렇게 사용할 수없습니다. where 뒤에 amount를 price * quantitiy로 바꿔야합니다.

(5) ORDER BY 구에서 연산

SELECT *, price * quantity AS amount FROM sample ORDER BY amount DESC

ORDER BY 에서는 별명을 사용할 수 있습ㄴ.다

(6) 함수

연산자 외에 함수를 사용해서도 연산할 수 있습니다.

A. ROUND

ROUND 함수는 기본적으로 소수점 첫째 자리를 기준으로 반올림한 값을 반환합니다.

SELET amount, ROUND(amount, 0) FROM sample

15. 문자열 연산

(1) 문자열 결합

문자열 데이터를 결합하는 연산입니다.

  • '+' : SQL Server
  • '||' : Oracle, DB2, PostgreSQL
  • CONCAT : MySQL
SELECT CONCAT(quantity, unit) FROM sample;

(2) SUBSTRING 함수

문자열의 일부분을 계산해서 반환해주는 함수입니다.

(3) TRIM 함수

문자열의 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해주는 함수로 문자열 도중에 존재하는 스페이스는 제거되지 않습니다.

(4) CHARACTER_LENGTH 함수

문자열의 길이를 계산해 돌려주는 함수입니다.

16. CASE

CASE문을 이용해 데이터를 변환 할 수 있습니다.

(1) CASE 문

기존의 연산자나 함수만으로 처리할 수 없는 예로, NULL 값을 0으로 간주하여 계산하고 싶은 경우가 잇습니다. NULL 값으로 연산한 결과는 모두 NULL 이 됩니다.

CASE WHEN 조건식 1 THEN 식1
[WHEN 조건식2 THEN 식2...]
[ELSE 식3]
END

WHEN 절에는 참과 거짓을 반환하는 조건식을 기술합니다. 해당 조건을 만족하여 참이 되면 THEN 절에 기술한 식이 처리됩니다.
WHEN절과 대응하는 THEN 절 식의 처리결과를 CASE 문의 결괏값으로 반환합니다.

  • CASE로 NULL 값을 0으로 변환하기
SELECT a, CASE WHEN a IS NULL THEN 0
ELSE a END "a(null=)" FROM sample

COALESCE

NULL 값을 변환하는 경우라면 COALESCE 함수를 사용하는 편이 더 쉽습니다.

SELECT a, COALESCE(a, 0) FROM sample

(2) 또 하나의 CASE 문

숫자로 이루어진 코드를 알아보기 더 쉽게 문자열로 변환하는 경우 CASE문을 많이 사용합니다.

  • 디코드 : 문자화하는 것
  • 인코드 : 수치화하는 것

(3) CASE를 사용할 경우 주의사항

CASE 문은 SELECT 뿐만 아니라 WHERE, ORDER BY 에서도 사용할 수 있습니다.

  • ELSE 생략
    ELSE를 생략하면 ELSE NULL이 됩니다. 상정외에 데이터가 들어올 수 있으니 ELSE를 생략하지않는 편이 좋습니다.

  • WHEN에 NULL 지정하기
    단순 CASE 문으로는 NULL 값을 비교할 수 없습니다. 그렇기 떄문에 IS NULL을 사용해야합니다.

CASE
	WHEN a = 1 THEN 조건1
    WHEN a = 2 THEN 조건2
    WHEN a IS NULL THEN '데이터 없음'
	ELSE "미지정"
END

출처

SQL 첫걸음

profile
Vorhandenheit
읽고 기록하고 고민하고 사용하고 개발하자!
이전 포스트

SQL : DDL

다음 포스트

SQL : 복수 테이블

0개의 댓글