SQL기초

WooBuntu·2021년 1월 6일
데이터베이스
0

데이터베이스

목록 보기
8/16

SQL은 데이터베이스의 데이터와 메타 데이터(데이터 구조에 관한 데이터)를 생성하고 처리하는 문법만 가지고 있는 데이터 부속어이다.

SQL문은 실행 순서가 없는 비절차적인 언어이다.
즉, 찾는 데이터만 기술하고 어떻게 찾는지 그 절차(실행 순서)는 기술하지 않는다.

mac 터미널에서 mysql서버에 접속하려면 루트 계정 접속이 필요

mysql -u root -p
# 엔터 후 비밀번호 입력

데이터 조작어 - 검색

-- 5번
SELECT 
	[ALL | DISTINCT]  -- DISTINCT는 중복을 제거해주는 예약어이다
    	[테이블이름.]{*|속성이름[[AS] 속성이름별칭]}
        
-- 1번
[FROM 
	{테이블 이름 [AS 테이블이름별칭]}
    	[INNER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN
        {테이블이름 [ON 검색조건]} 
        | FULL [OUTER] JOIN {테이블이름}]]
        
-- 2번
[WHERE 검색조건(들)]

-- 3번
[GROUP BY {속성이름, [..., n]}]

-- 4번
[HAVING 검색조건(들)]
[질의 UNION 질의 | 질의 UNION ALL 질의]

-- 6번
[ORDER BY {속성이름 [ASC | DESC], [..., n]}]
-- 대괄호 안의 예약어들은 옵션
-- 중괄호 안의 예약어들은 필수

SELECT문

WHERE 조건

(문자열의 경우 대소문자 구분하지 않음)

  • 비교

    • = or <>(같지 않음)
    • < or >
    • <= or >=

  • 범위(BETWEEN)

  • 집합

    • IN
    • NOT IN

  • 문자열 패턴(LIKE)

    • 대소문자를 구분하지 않음

    • 찾는 속성이 텍스트 혹은 날짜 데이터를 포함하면 비교 값에는 반드시 영문 작은 따옴표로 둘러싸야 한다.

    • %는 임의의 문자열(0개 이상의 문자열)을 대신하는 기호이다

    • _는 특정 위치의, 임의의 한 문자를 대신하는 기호이다

      숫자에도 적용할 수 있는데 예를 들어 4자리의 숫자를 찾고 싶다면, LIKE '____'과 같이 작성할 수 있다

    • []는 한 개의 문자를 대신하는 기호이다.

      ex) '[0-5]%' : 0-5사이 숫자로 시작하는 문자열

    • [^]는 1개의 문자와 불일치하는 기호이다.

      ex) '[^0-5]%' : 0-5사이 숫자로 시작하지 않는 문자열

  • NULL

    • IS NULL

    • IS NOT NULL

  • 복합조건

    • AND

    • OR

    • NOT

ORDER BY

  • 문자열의 경우 정렬 순서는 숫자, 영문자, 한글 순이다.
SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;

가격의 내림차순으로 정렬한 후, 출판사의 오름차순으로 정렬

집계 함수와 GROUP BY

집계함수

  • SUM([ALL | DISTINCT] 속성이름)

    ex) SELECT SUM(saleprice) AS 총매출 FROM Orders;

  • AVG([ALL | DISTINCT] 속성이름)

  • MIN([ALL | DISTINCT] 속성이름)

  • MAX([ALL | DISTINCT] 속성이름)

  • COUNT({[[ALL | DISTINCT] 속성이름] | *})
    NULL값을 제외한 행의 개수를 세는 함수

SELECT COUNT(DISTINCT publisher) FROM Book;

집계 함수는 여러 개를 혼합하여 쓸 수 있다.

SELECT
	SUM(saleprice) AS Total,
    	AVG(saleprice) AS Average,
        MIN(saleprice) AS Minimum,
        MAX(saleprice) AS Maximum
FROM Orders;

GROUP BY

속성 값이 같은 값끼리 그룹을 만든다

SELECT custid, COUNT(*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;

HAVING절은 GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할을 한다

주의사항

  • GROUP BY로 투플을 묶은 경우 SELECT절에는 GROUP BY에 사용한 속성만 올 수 있다.

  • 다른 속성은 오직 집계 함수에 인자로 밖에 올 수 없다.

  • HAVING절의 검색 조건에는 집계 함수만이 올 수 있다.

2개 이상의 테이블에서 SQL 질의

JOIN

SELECT * FROM Customer, Orders;

위와 같이 아무런 조건 없이 두 테이블을 SELECT시키면 카티전 프로덕트 연산이 된다
(쓸모 없는 정보)

(동등)조인

다음의 두 가지 문법 모두 사용할 수 있다.

SELECT 속성들
FROM 테이블1, 테이블2
WHERE 조인조건 AND 검색조건
SELECT 속성들
FROM 테이블1 INNER JOIN 테이블2 ON 조인조건
WHERE 검색조건

셀프 조인(170pg)

하나의 테이블(자신)을 대상으로 조인하는 것

외부조인

SELECT 속성들
FROM 테이블1 {LEFT | RIGHT | FULL [OUTER]} JOIN 테이블2 ON 조인조건
WHERE 검색조건

  • LEFT 조인 : '조인조건을 만족하지 못하는 테이블1의 행'과 대응되는 테이블2부분을 NULL처리

  • RIGHT 조인 : '조인조건을 만족하지 못하는 테이블2의 행'과 대응되는 테이블1부분을 NULL처리

  • FULL 조인: '조인조건을 만족하지 못하는 테이블1의 행과 테이블2의 행'에 대응되는 각 부분을 NULL처리

SUBQUERY(부속질의/중첩질의)

SQL문 내부에 또 다른 SQL문을 작성

SELECT name
FROM Customer
WHERE custid IN(SELECT custid FROM Orders);

부속질의 간에는 상하 관계가 있기 때문에, 하위 부속질의를 먼저 실행하고 그 결과를 이용하여 상위 부속질의를 실행한다.

반면 상관 부속질의는 상위 부속질의의 튜플을 이용하여 하위 부속질의를 계산한다\

SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT avg(b2.price)
                  FROM Book b2
                  WHERE b2.publisher = b1.publisher)

똑같은 Book테이블을 상위 부속질의에서는 b1으로, 하위 부속질의에서는 b2로 별칭한 것이다.
(b1, b2를 튜플 변수라 한다)

먼저, b1에서 튜플을 하나 읽는다.

해당 튜플의 publisher와 같은 publisher를 가진 튜플들의 price 평균을 낸다.

다시 b1의 튜플로 돌아와 b1 튜플의 price가 price평균 값보다 높은 지 판별한다.

이걸 b1의 모든 튜플에 대해 반복한다

부속질의와 조인의 차이점

부속질의는 SELECT문에 나오는 결과 속성을 FROM 절의 테이블에서만 얻을 수 있고,

조인은 조인한 모든 테이블에서 결과 속성을 얻을 수 있다.

부속질의로 할 수 있는 모든 작업은 조인으로도 할 수 있지만,
한 개의 테이블에서만 결과를 얻는 것은 부속질의로 작성하는 편이 권장된다.

집합 연산

합집합 : UNION

차집합 : NOT IN(다른 DBMS에서는 MINUS)

교집합 : IN(다른 DMBS에서는 INTERSECT)

SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION -- 만약 중복까지 포함하려면 UNION ALL
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

EXISTS

  • EXISTS

    부속질의문의 어떤 행이 조건에 만족하면 참이다

  • NOT EXISTS

    부속질의문이 모든 행이 조건에 만족하지 않을 때만 참이다

SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT * 
              FROM Orders od 
              WHERE cs.custid=od.custid);

데이터 정의어

CREATE

CREATE TABLE 테이블이름 -- 테이블이름은 _나 카멜 케이스를 쓰고 공백을 두지 말 것
({속성이름 데이터타입
  [NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 조건]
 }
  [PRIMARY KEY 속성이름(들)]
  [FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
          [ON DELETE {CASCADE | SET NULL}]
          -- ON DELETE의 옵션을 명시하지 않으면 RESTRICT(NO ACTION)으로 간주한다
)

  • CHAR

    n바이트를 가진 문자형 타입으로, 저장되는 문자의 길이가 n보다 작으면 나머지는 공백으로 채워서 n바이트를 만들어 저장한다.

  • VARCHAR

    n바이트를 가진 문자형 타입이지만 저장되는 문자의 길이만큼만 기억장소를 차지하는 가변형

CHAR와 VARCHAR의 저장된 문자열이 같은 값일지라도 CHAR는 공백을 채운 문자열이기 때문에 동등 비교에 실패하는 경우가 있다는 것에 유의해야 한다

CREATE TABLE NewBook(
bookid INTEGER PRIMARY KEY,
bookname VARCHAR(20),
publisher VARCHAR(20),
price INTEGER);

PRIMARY KEY는 위와 같이 설정할 수도 있지만, 복수의 속성을 PRIMARY KEY로 삼기 위해선 아래와 같이 해야 한다.

CREATE TABLE NewBook(
bookname VARCHAR(20),
publisher VARCHAR(20),
price INTEGER,
PRIMARY KEY (bookname, publisher));
-- 복수의 속성이 아니더라도 반드시 ()로 감싸주어야 한다

데이터 타입

  • INTEGER OR INT

    4바이트 정수형

  • NUMERIC(m,d) OR DECIMAL(m,d)

    전체 자릿수 m, 소수점이하 자릿수 d를 가진 숫자형

  • CHAR(n)

    문자형 고정길이 n, 문자를 저장하고 남은 공간은 공백으로 채움

  • VARCHAR(n)

    문자형 가변길이 n

  • DATE

    날짜형, 연도, 월, 날, 시간

ALTER문

생성된 테이블의 속성과 속성에 관한 제약, 기본키 및 외래키의 변경

ALTER TABLE 테이블이름
      [ADD 속성이름 데이터타입]
      [DROP COLUMN 속성이름]
      [MODIFY 속성이름 데이터타입]
      [MODIFY 속성이름 데이터타입 [NULL | NOT NULL]]
      [ADD PRIMARY KEY(속성이름)]
      [[ADD | DROP] 제약이름]

DROP문

DROP TABLE 테이블이름;

DROP문은 테이블의 구조와 데이터를 모두 삭제한다

데이터만 삭제하려면 DELETE문을 사용한다.

데이터 조작어 - 삽입, 수정, 삭제

INSERT문

INSERT INTO 테이블이름 [(속성리스트)] VALUES (값리스트);
-- 속성리스트는 생략 가능하다
-- 다만 속성리스트를 생략한 경우 데이터의 입력 순서는 미리 정의한 속성의 순서와 일치해야 한다

대량 삽입(bulk insert)

한 테이블에 저장된 데이터를 읽어서 다른 테이블에 삽입하는 방법
(한꺼번에 여러 개의 튜플 삽입)

INSERT INTO 테이블이름 (속성리스트)
SELECT (속성리스트)
FROM 다른 테이블 이름;

당연히 읽어오는 테이블의 속성 리스트들의 도메인과 삽입할 테이블의 속성 리스트들의 도메인이 일치해야 한다.

UPDATE문

UPDATE 테이블이름
SET 속성이름 = 값, [반복]
[WHERE 검색조건];

SAFE UPDATES 모드

UPDATE나 DELETE시 실수를 방지하기 위해 WHERE문에서 기본키로만 검색하도록 하는 모드

DELETE 문

DELETE FROM 테이블이름
[WHERE 검색조건];

DROP문이 테이블의 구조와 데이터를 함께 삭제하는 반면, DELETE문은 테이블의 구조는 남기고 데이터만 삭제한다

profile
WooBuntu
이전 포스트

[번외] 업무관점의 데이터베이스

다음 포스트

내장 함수

0개의 댓글

관련 채용 정보