[Crash Course] SQL(2)

물리삭제와 논리삭제
    • 물리삭제: SQL의 DELETE명령을 사용해 직접 데이터를 삭제하자는 사고방식
    • 논리삭제: 실제 테이블안에 데이터는 남아 있지만, 삭제로 설정된 행을 제외하는 SELECT명령을 실행, 해당 행은 삭제된것 처럼 보임
    • 논리삭제의 장점은 삭제되기전의 상태로 돌아갈수 있지만,
    • 논리삭제의 단점은 삭제해도 데이터베이스의 크기의 저장공간이 늘어나지 않는점, 검색속도가 떨어지는 점을 들수 있다.  뿐만 아니라 애플리케이션 측 프로그램에서는 삭제임에도 불구하고 UPDATE명령을 실행하므로 혼란을 야기할수 있다.   

(예) 사용자가 탈퇴한 경우는 데이터를 삭제할때 물리삭제를 하는 편이 안전하나 반면 쇼핑사이트의 경우는 사용자의 주문을 취소할 경우에는  취소되었다고 하더라두 발주는 된것으로. 해당정보가 완전히 불필요 한것이라고는 말할수 없기때문에 논리삭제 방법을 많이 사용한다.

COUNT - 행 개수 구하기
SELECT  COUNT(*) FROM sample11; // 테이블 전체의 행의 개수
SELECT COUNT * FROM sample11 WHERE name = 'A'; // sample11테이블에서 name열이 A인 행의 개수
NULL값

COUNT(*)의 경우 모든 열을 카운트 하기 때문에 NULL값이 있어서 무시 되지 않지만,
예를 들어 Name의 열에 NULL값이 있는데 아래와 같이 검색을 하면 NULL값이 제외된 행의 수가 나온다.

SELECT COUNT(name) FROM sample 11 // NULL값을 가진 행을 제외한 행수
DISTINCT- 중복제거하기
Sample12

Result

SELECT  DISTINCT name FROM sample12; 
NULL값을 제외하고 중복을 제거한 뒤 개수 구하기
SELECT COUNT(ALL, name) COUNT(DISTINCT name) FROM sample12;
// COUNT(ALL, name) : 4
// COUNT(DISTINCT name) : 3
COUNT외의 집계함수 : SUM, AVG, MIN, MAX
Sample13

SUM으로 quantity열의 합계구하기

SELECT SUM(quantity) FROM sample13; // 16

AVG로 평균값 구하기

SELECT AVG(quantity), SUM(quantity)/COUNT(quantity) FROM sample13; 
// AVG(quantity) : 4.0000
// SUM(quantity)/COUNT(quantity) : 4.0000
// NULL값은 무시된다.

AVG로 평균값 구하기(NULL값은 0으로 변환)

SELECT AVG(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END AS avgnull10 FROM sample13; 
//avgnull10 : 3.2000

MIN, MAX로 최솟값, 최대값 구하기

SELECT MIN(quantity), MAX(quantity), MIN(name), MAX(name) FROM sample13;
// MIN(quantity) : 1
// Max(quantity) : 10
// MIN(name) : A
// Max(name) : C

GROUP BY 

name으로 열로 그룹화하기 : 지정된 열의 값이 같은 행은 하나의 그룹으로 묶입니다.  DISTINCT와 같은 결과값을 얻는다.  GROUP BY구는 집계함수와 사용하지 않으면 별 의미가 없다.  그룹화된 각각의 그룹이 집계함수 인수로 넘겨지기 때문이다.

SELECT name FROM sample13 GROUP BY NAME;
// name: A, B,C,NULL

⇒GROUP BY로 그룹화되면 클라이언트로 반환되는 결과는 그룹당 하나의 행이 되므로 A의 그룹의 quantity값이 1,2와같이 여러개의 경우는 어느값을 반환해야 할지 몰라서 에러를 발생합니다.

따라서 GROUP BY에서 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT구를 지정할수없다.

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

 

name열로 그룹화 해 합계를 구하고 내림차순으로 정렬

SELECT name, COUNT(name), SUM(quantity) FROM sample13 GROUP BY name ORDER BY SUM(quantity) DESC;
// name          : B, C, A, NULL
// COUNT(name)   : 1, 1, 2, 0
// SUM(quantity) : 10, 3, 3, NULL

최솟값을 가지는 행 삭제하기 :괄호로 서브쿼리를 지정해 삭제

DELETE FROM sample13 WHRER quantity=(SELECT MIN(quantity) FROM sample13)

네스티드 구조 (중첩구조) : SELECT 명령안에 SELECT명령이 들어있는 구조

SELECT * FROM (SELECT * FROM sample) sq;
UPDATE sample14 SET a="있음" WHERE EXISTS(SELECT * FROM sample15 WHERE no2 =no;
UPDATE sample14 SET a="없음" WHERE NOT EXISTS(SELECT * FROM sample15 WHERE no2 =no) 
UPDATE sample14 SET a="없음" WHERE NOT EXISTS(SELECT * FROM sample15 WHERE no2 =no) 

IN

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

    •  IN은 집합 안에  NULL값이 있어도 무시하지 않는다
    • 집계함수(COUNT, AVG, MIN, MAX, SUM)에서는 집합 안의 NULL값은 무시하고 처리
    •  IN을 사용해도 NULL = NULL값은 비교할수 없다. NULL값을 비교할 경우에는 IS NULL을 사용해야한다.

스칼라 값이란  단일 값으로 통용되지만,  SELECT명령이 하나의 값만 반환하는 것을 ‘스칼라 값을 반환한다’고 한다.

SELECT * FROM sample14 WHERE no IN(3, 5);

 

테이블 작성

CREATE TABLE sample17( no INTERGER NOT NULL,  a VARCHAR(30),  b DATE);

 

테이블 삭제

DROP TABLE sample17; //신중하게 삭제

 

테이블 변경

//ALTER TABLE로 테이블에 열 추가하기
ALTER TABLE sample17 ADD newcol INTEGER;
DESC sample17;

열 속성 변경

ALTER TABLE sample17 MODIFY newcol VARCHAR(20);
DESC sample17;

 

열 이름 변경

ALTER TABLE sample17 CHANGE newcol c VARCHAR(20);
DESC sample17;

 

열 삭제

ALTER TABLE sample17 DROP c;
DESC sample17;

제약

테이블 열에 제약 정의하기

CREATE TABLE sample18( a INTEGER NOT NULL, b INTEGER NOT NULL UNIQUE, c VARCHAR(30) );
 //c열에 NOT NULL제약추가하기
 

 

테이블 테이블 제약 정의하기

CREATE TABLE sample19( no INTEGER NOT NULL, sub_no INTEGER NOT NULL, PRIMARY KEY(no, sub_no) );

 

테이블 제약에 이름 붙이기

CREATE TABLE sample19( no INTEGER NOT NULL, sub_no INTEGER NOT NULL, name VARCHAR(30),  CONSTAINT pkey_sample PRIMARY KEY(no, sub_no) );

      • 뷰는 SELECT명령을 기록하는 데이터베이스 객체다.
      • 복잡한 SELECT명령을 데이터베이스에 등록해 두었다가 편리하게 사용할수 있는것이다.
      • 가상테이블이다.
      • SELECT명령에서만 사용하는 것을 권장
      • 뷰는 데이터베이스 객체이기 때문에 DDL로 작성(CREATE VIEW)하거나 삭제(DROP VIEW)
      • 머티리얼라이즈드 뷰(Materialized View) : 뷰가 참조되었을때 데이터를 저장해둡니다. 이후 다시 참조할때 이전에 저장해 두었던 데이터를 그대로 사용. 뷰에 지정된 테이블의 데이터가 변경된 경우에는 SELECT명령을 재실행하여 데이터를 다시 저장한다.(RDBMS)
// 열을 지정해 뷰 작성하기
CREATE VIEW sample_view1 (n, v, v2) AS SELECT no, a,  a*2 FROM sample;
SELECT * FROM sample_view1 WHERE n =1;

테이블결합

FROM 구에 테이블 2개를 지정해 곱집합 구하기

SELECT * FROM sample20(테이블 명1), sample21(테이블 명2);
    • 내부결합
    • 외부결합

관계형모델

관계형모델과 SQL

    • 합집합 : SQL에서는 UNION 덧셈
    • 차집합 : SQL에서는 EXCEPT 뺄샘
    • 교집합 : SQL에서는 INTERSECT 공통부분
    • 곱집합 : SQL에서는 FROM 구에 CROSS JOIN
    • 선택 : SQL에서 속성은 행을 말하기 때문에
    • WHERE구에 조건을 지정해 데이터를 검색
    • 투영: 속성을 추출 SQL에서 속성은 열을 말하기 때문에 SELECT 구의 결과를 반환할 열을 지정하는 것
    • 결합 : SQL에서는 내부결합에 해당