[ SQL ] 서브쿼리( 단일행, 복수행 ) / Join(inner, outer) / DDL(create, alter, drop) / insert / update / delete ...

 

---

 

DML

 

​

---

​

서브쿼리( subquery )

 

 

▶ 최고급여를 받는 사원에 대한 사원정보 출력

select max(sal) from emp;

 

▶ 최고 급여와 같은 사원 정보

select empno, ename from emp where sal = (select max(sal) from emp);

 

▶ 둘의 쿼리를 결합해본다.

SCOTT의 급여보다 이상인 사원번호, 사원명, 급여 출력

 

20번 부서의 최고 급여를 받은 사원에 대한 사원번호, 사원명, 부서번호, 급여를 출력

​

▶ 서브쿼리 특징

​

· 단일행 서브쿼리 ( 1행 , 1열 )

· 복수행 서브쿼리 ( 여러행, 1열 ) - 아래 4개 밖에 못씀

<all (서브쿼리) - 서브쿼리에 나온것 중 최소값 보다 작은

>all (서브쿼리) - 서브쿼리에 나온것 중 최대값 보다 큰

<any (서브쿼리) - 서브쿼리에 나온것 중 최대값 보다 작은

<any (서브쿼리) - 서브쿼리에 나온것중 최소값 보다 큰

· 연산자

· 서브쿼리의 결과 => 반드시 1개

​

 

예시)

직책이 MANAGER인 사원의 최소 급여보다 큰 사원에 대한 사원정보 출력

 

 

부서별 최대급여를 받는 사원들의 사원번호, 이름, 부서번호, 급여를 출력

​

---

 

 

조인( join )

 

​

▶ equi join (=) 예제

테이블 emp와 dept이 가지고 있는 deptno 컬럼으로 둘을 합치기

​

select *
from emp cross join dept
where emp.deptno = dept.deptno;

 

 

select *
from emp inner join dept
where emp.deptno = dept.deptno;

 

 

select *
from emp inner join dept
using(deptno);

​

 

예제) 직책이 clerk인 사원의 사원이름, 사원번호, 부서번호, 부서이름, 위치 출력

​

select ename, empno, deptno, dname, loc
from emp inner join dept
using(deptno)
where(emp.job='clerk');

 

※ deptno가 어느테이블의 deptno인지 헷갈려 하므로 어느 테이블의 deptno인지 명시해줘야한다.

※ 같은 컬럼명이 있으면 USING을 사용해도 된다.

※ INNER JOIN 과 JOIN 은 똑같은 결과를 얻을수 있다.

※ 에러 메세지

Column 'deptno' in field list is ambiguous 뜻 : 어디 칼럼에 속했는지 불확실하다

※ 테이블 별칭을 써서 약자로 쓰기 가능

 

​

▶ NON EQUIL JOIN

조인을 수행하는 기준컬럼이 =이 아닌 범위로 수행될 때 의미

 

예시)  급여를 기준으로 호봉수 알고 싶을때

 

보통 조인 할때에는 WHERE보다 ON을 많이 쓴다.

예시)  20번 부서 사원들의 급여에 대해 grade 출력

 

예시)  NON-EQUIL JOIN과 EQUIL JOIN을 동시에 사용해보자.

​

​

▶ OUTER JOIN

( inner join보다 많이 쓰인다. )

두개의 테이블을 결함할 떄 어느 하나의 테이블의 모든행을 다 출력하는것을 의미

왼쪽 테이블 행 모두 불러올 때 : LEFT OUTER JOIN

오른쪽 테이블 행 모두 불러올 때 : RIGHT OUTER JOIN

​

EMP 의 부서번호를 출력

 

보통 OUTER JOIN은 상품과 주문의 관계에서 많이 사용된다.

상품 / 주문 ( 주문이 된 상품과 주문이 되지 않은 상품 구분 가능 )

- 재고

회원 / 주문 ( 주문을 한 회원과 주문을 하지 않은 회원 구별 가능 )

- 구입

​

​

▶ SELF JOIN

자기자신의 테이블과 조인하는 것( 반드시 테이블 별칭 써줘야 한다.)

 

이때 매니저가 없는 사원들을 보고싶으면 OUTER JOIN을 사용

 

Smith 의 매니저는 Ford

Allen의 매니저는 Blake임을 알수 있다.

매니저가없는 King은 NULL 값이 나옴을 알수 있다. ( = KING은 최상위 사원 )

이번에는 매니저가 없으면 NULL대신 '관리자 없음'이 나오게 출력

​

 

---

 

DDL

​

​

▶ 데이터를 담을 데이터베이스 / 테이블 만들기

​

· 데이터베이스

--- 데이터베이스 만들기
CREATE DATABASE TEST1;

--- 데이터베이스 삭제하기
DROP DATABASE TEST1;

 

· 테이블

CREATE TABLE 테이블명 (
컬럼명 데이터타입(크기) 옵션,
컬럼명 데이터타입(크기) 옵션,
컬럼명 데이터타입(크기) 옵션,
);

 

※ 대소문자의 구분이 X

​

▶ 테이블 만들기

CREATE TABLE DEPT2 (
DEPTNO INT(2),
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

Query OK, 0 rows affected (0.022 sec)

CREATE TABLE EMP2 (
	EMPNO 	INT(4),
	ENAME	VARCHAR(10),
	JOB	VARCHAR(9),
	MGR	INT(4),
	HIREDATE DATE,
	SAL	DECIMAL(7,2),
	COMM	DECIMAL(7,2),
	DEPTNO 	INT(2)
);

​

▶ 다른테이블 참조해서 만들기

 

CREATE TABLE DEPT2
AS SELECT * FROM DEPT;

//
Query OK, 4 rows affected (0.028 sec)
Records: 4  Duplicates: 0  Warnings: 0

//
SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.001 sec)


----특정 컬럼만을 참조해서 만들기
CREATE TABLE DEPT3
AS SELECT LOC, DEPTNO 
FROM DEPT
WHERE DEPTNO = 10;

//
Query OK, 1 row affected (0.029 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM DEPT3;
+----------+--------+
| LOC      | DEPTNO |
+----------+--------+
| NEW YORK |     10 |
+----------+--------+
1 row in set (0.000 sec)


----컬럼명을 바꿔서 저장하기
CREATE TABLE DEPT4
AS SELECT LOC LOCATION, DEPTNO NO
FROM DEPT;

Query OK, 4 rows affected (0.021 sec)
Records: 4  Duplicates: 0  Warnings: 0

SELECT * FROM DEPT4;
+----------+----+
| LOCATION | NO |
+----------+----+
| NEW YORK | 10 |
| DALLAS   | 20 |
| CHICAGO  | 30 |
| BOSTON   | 40 |
+----------+----+
4 rows in set (0.000 sec)

 

예제)

EMP 테이블의 사원번호, 사원이름, 급여, 연봉, 입사일자가 포함된 EMP_YEAR 테이블을 만들어보자

 

​

예제) 빈 테이블로 복사하기

※ 부정조건 ( 데이터 복사가 안되게 부정조건 걸어줌 )

 

방법1

 

방법2

​

▶ 테이블 수정하기

​

Alter 구문 사용

* 테이블 수정 - alter
1) 컬럼
	- 추가
	- 내용변경
	- 삭제
2) 제약조건

 

1) 추가하기

add

​

2) 내용변경

• modify

 

• rename

​

3) 삭제

drop

 

---

 

DML( Insert , Update, Delete )

​

1) Insert문

데이터를 집어넣는 구문

데이터를 집어넣는 값은 컬럼의 데이터 타입과 크기에 맞게 넣어야 한다.

(컬럼의 데이터타입이 문자이고 크기가 10이면 10자릿수를 의미한다., 숫자의 경우는 바이트수 의미)

--1 : 전체 컬럼에 데이터를 집어넣을 때
INSERT INTO 테이블명 VALUES(컬럼1의값, 컬럼2의값, 컬럼3의값);

--2 : 특정 컬럼에만 데이터를 집어넣을 때
INSERT INTO 테이블명(컬럼명1, 컬럼명2) VALUES (컬럼1의값, 컬럼2의값);

 

예제)

​

2) Update문

특정 컬럼의 데이터값을 바꿀때 사용한다.

 

예제)

전체 부서위치를 서울로 변경

10번부서만 지역을 부산으로 변경

​

3) Delete문

특정값을 가진 컬럼의 데이터 지우기

Delete from 테이블명 [where 컬럼명 = 값];

 

예제)

10번부서만 지워보기

 

 

---

 

데이터무결성

​

테이블내에서 데이터들이 제대로 유지되게 하는 조건

​

▶ NULL 조건

- NOT NULL이면 무조건 데이터를 집어넣어야 한다.

- NULL이면 데이터를 집어넣지 않아도 된다.

- ' ' 를 insert하면 NULL이 아닌 공백이 들어간다.

​

※ NOT NULL로 설정된 컬럼에 NULL을 insert 하면 에러 발생

 

---- 연습을 위해 테이블을 하나 생성해보자

CREATE TABLE DEPT2 (
DEPTNO INT(2) NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(15)
);

MariaDB [sample]> desc dept2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(2)      | NO   |     | NULL    |       |
| dname  | varchar(14) | YES  |     | NULL    |       |
| loc    | varchar(15) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.016 sec)

 

 

Insert문으로 데이터를 집어넣었을때,

데이터를 ' ' 로 하면 공백인 문자가 들어간것이고, 데이터를 주지않으려면 Null 이라고 써야한다.

( 공백과 NULL은 다른것이다. )

INSERT INTO DEPT2 VALUES(10, '영업', '서울');

INSERT INTO DEPT2 VALUES(20, '', '서울');

INSERT INTO DEPT2 VALUES(30, NULL, '서울');

INSERT INTO DEPT2 VALUES(40, '연구', NULL); -- 에러

INSERT INTO DEPT2 VALUES(40, '연구', '');

MariaDB [sample]> select * from dept2;
+--------+-------+------+
| deptno | dname | loc  |
+--------+-------+------+
|     10 | 영업  | 서울 |
|     20 |       | 서울 |
|     30 | NULL  | 서울 |
|     40 | 연구  |      |
+--------+-------+------+
4 rows in set (0.001 sec)

 

 

▶ Update문에서 NOT NULL인 컬럼을 NULL로 바꾸려 할때 에러가 난다.

UPDATE DEPT2 SET LOC=NULL
WHERE DEPTNO = 20;

ERROR 1048 (23000): Column 'LOC' cannot be null

 

▶ 여러개의 Insert문 사용

​

▶ 서브쿼리 사용하기

​

▶ Default

- 조건 : 컬럼의 기본값을 설정한다.

- DEFAULT를 쓸때는 DEFAULT라고 써줘야 한다.

--- 테이블 생성

CREATE TABLE DEPT2 (
DEPTNO INT(2) DEFAULT 10,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

​