SQL 2

4장 데이터의 추가, 삭제, 갱신

데이터 : 클라이언트 => 서버

[행 추가하기]

mysql> INSERT INTO 테이블명 VALUES( ....);  

-열 지정해서 행 추가하기

mysql> INSERT INTO 테이블명(열1이름,열2이름) VALUES( , );  

-Defalut를 명시적으로 지정하기

mysql> INSERT INTO 테이블명(a,b) VALUES(1,DEFAULT);  

-암묵적으로 저장: 지정하지 않은 열은 Default 값으로 저장됨

+ NULL이 NO로 제약(NOT NULL)이 걸려있다면 VALUES에 NULL을 저장할 수 없음

[행 삭제하기]

DELETE 열 삭제는 불가함

mysql> DELETE FROM 테이블명 WHERE 조건식;
mysql> DELETE FROM 테이블명;

WHERE생략시 모든 행 삭제

[데이터 갱신]

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

WHERE구 생략시 모든 행의 지정한 열이 갱신됨
WHERE 조건에 일치하는 모든 행 갱신

SET 열명 = 값 => 여기서 =는 대입연산자

mysql> UPDATE sample41 SET a='update' WHERE a IS NULL;

-SET구 실행순서
실행순서는 제품마다 달라지는 것에 주의

MySQL> UPDATE sample41 SET no = no+1, a = no; 
순서대로 no에는 no+1 대입, a에는 새로 갱신된 no가 대입

Oracle> UPDATE sample41 SET no = no+1, a = no;
순서에 상관없이 a에는 갱신 이전의 no가 대입

-NULL로 갱신
UPDATE sample41 SET a=NULL;
다만 NOT NULL제약시 불가

*UPDATE는 셀을 갱신하는 명령어

[물리삭제와 논리삭제]

-물리삭제
DELETE 명령을 통해 데이터 삭제

-논리삭제
UPDATE 명령을 통해 삭제플래그 값을 갱신, 실제 데이터는 삭제되지 않고 테이블에 남아있으며, 참조시 삭제플래그가 삭제로 설정된 행을 제외하는 SELECT명령 사용

5장 집계와 서브쿼리

[집계함수]

인수로 집합을 지정
집계 : 복수의 값(집합)에서 하나의 값을 계산하는 것
-COUNT
-SUM
-AVG
-MIN
-MAX

mysql> SELECT * FROM sample51;
+------+------+----------+
| no   | name | quantity |
+------+------+----------+
|    1 | A    |        1 |
|    2 | A    |        2 |
|    3 | B    |       10 |
|    4 | C    |        3 |
| NULL | NULL |     NULL |
+------+------+----------+

[COUNT]

인수로 주어진 집합의 ‘개수’를 반환

mysql> SELECT COUNT(*) FROM sample51;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
sample51의 행의 개수 5

-열을 지정하는 경우

mysql> SELECT COUNT(name) FROM sample51;  

name이 NULL인 행은 제외되고 계산됨

반면 COUNT(*)은 모든 열의 행수를 카운트하기에 NULL값이 무시되지 않음

-DISTINCT : 중복된 값을 제거(다른 집계함수에서도 사용가능)

mysql> SELECT ALL name FROM sample51;
mysql> SELECT DISTINCT name FROM sample51;
생략시 ALL로 간주

-DISTINCT를 이용해 중복되지 않는 데이터 개수 구하기 집계함수의 인수로 DISTINCT사용

mysql> SELECT COUNT(DISTINCT name) FROM sample51;

[SUM]

수치형 집합만 지정가능, NULL값 무시

mysql> SELECT SUM(quantity) FROM sample51;
+---------------+
| SUM(quantity) |
+---------------+
|            16 |
+---------------+

[AVG]

mysql> SELECT AVG(quantity) FROM sample51;
+---------------+
| AVG(quantity) |
+---------------+
|        4.0000 |
+---------------+

-NULL을 0으로 계산하기

mysql> SELECT AVG(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) FROM sample51;

[MIN MAX]

문자열형,날짜시간형에도 사용가능 NULL값 무시

mysql> SELECT MAX(quantity) FROM sample51;
+---------------+
| MAX(quantity) |
+---------------+
|            10 |
+---------------+
1 row in set (0.01 sec)

mysql> SELECT MIN(quantity) FROM sample51;
+---------------+
| MIN(quantity) |
+---------------+
|             1 |
+---------------+
mysql> SELECT MIN(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) FROM sample51;

[그룹화]

집계함수의 활용범위를 넓힐 수 있음

-GROUP BY구 : 지정된 열의 값이 같은 행이 하나의 그룹으로 묶임
지정하지 않은 열은 SELECT에서 사용X (집계함수와 사용은 가능)
GROUP BY는 그룹화된 각각의 그룹이 하나의 집합으로 집계함수의 인수로 넘겨짐

+------+----------+
| name | quantity |
+------+----------+
| A    |        1 |
| A    |        2 |
| B    |       10 |
| C    |        3 |
| NULL |     NULL |
+------+----------+
mysql> SELECT name FROM sample51 GROUP BY name;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| NULL |
+------+

집계함수와 사용
mysql> SELECT name,COUNT(name),SUM(quantity) FROM sample51 GROUP BY name;
+------+-------------+---------------+
| name | COUNT(name) | SUM(quantity) |
+------+-------------+---------------+
| A    |           2 |             3 |
| B    |           1 |            10 |
| C    |           1 |             3 |
| NULL |           0 |          NULL |
+------+-------------+---------------+

-내부처리순서
WHERE -> GROUP BY -> SELECT -> ORDER BY

때문에 다음과 같이 사용할 수 없다. => WHERE구에서 집계함수 사용 X

mysql> SELECT name, COUNT(name), SUM(quantity) FROM sample51 WHERE COUNT(name)=1 GROUP BY name;

-HAVING구
WHERE구와 동일하게 조건식을 지정
조건식에는 집계된 열의 값이나 집계함수의 계산결과가 전달됨

mysql> SELECT name FROM sample51 GROUP BY name HAVING COUNT(name)>1;

[서브쿼리]

SELECT명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미

mysql에서는 데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없음

[상관서브쿼리]

-EXISTS(SELECT) : 반환된 행이 있다면 참, 없다면 거짓을 반환 => SELECT가 스칼라 값을 반환하지 않아도 됨

mysql> SELECT * FROM SAMPLE551 WHERE EXISTS(SELECT * FROM SAMPLE552 WHERE no2 = no);
552.no2 = 551.no인 행을 반환

mysql> SELECT * FROM SAMPLE551 WHERE NOT EXISTS(SELECT * FROM SAMPLE552 WHERE no2 = no);
552.no2 = 551.no가 아닌 행을 반환

다음과 같이 SET구에서도 EXISTS와 CASE를 사용해 업데이트를 할 수 있음
mysql> UPDATE SAMPLE551 SET a = (CASE WHEN EXISTS(SELECT * FROM SAMPLE552 WHERE no2=no) THEN '있음1' ELSE '없음1' END);

UPDATE명령(부모)과 서브쿼리(자식)처럼, 부모명령과 자식인 서브쿼리가 특정 관계를 맺는 것을 ‘상관 서브쿼리’라 부름

두 테이블의 열이 같은 이름이라면(EX. board_id, board_id) 에러 혹은 같은 테이블의 열을 가리켜 항상 참이 됨.
이를 해결하기위해 열명 앞에 ‘테이블명.’ 을 붙여주면 됨
table1.board_id = table2.board_id

만약 열명이 같고 명시하지 않는다면, 가장 가까운 스코프에 있는 열로 인식하고 처리함
그렇기에 해당 쿼리의 열은 명시를 하지 않아도 상관은 없으나 가독성을 위해 명시하자!

-IN
열명 IN(집합)
스칼라 값끼리 비교시 =를 사용했다면, 집합을 비교할 때는, IN으로 집합 안에 값이 존재하는 지 조사할 수 있다.

테이블이 다음과 같을 때
551
+------+-------+
| no   | a     |
+------+-------+
|    1 | 없음1 |
|    2 | 없음1 |
|    3 | 있음1 |
|    4 | 없음1 |
|    5 | 있음1 |
+------+-------+
552
+------+
| no2   | 
+------+
|    3 | 
|    5 | 
+------+
mysql> SELECT * FROM551 WHERE no IN(3,5);
mysql> SELECT * FROM551 WHERE no IN(SELECT no2 FROM 552);

집합부분을 서브쿼리로 지정할 수도 있음

-NOT IN

-IN과 NULL