[Day19 - DB] 서브쿼리, Structure Query Language(DDL, DML, DCL, TCL) 기본 이론

서브쿼리

 

: 메인 쿼리 내에 또 다른 쿼리문을 작성하는 것

: 서브쿼리는 괄호 안에 작성

: 주로 WHERE 절에 쓰임

: SELECT문에 작성하는 서브쿼리는 조인과 동일하며, 조인이 더 효율적

: FROM 절에서도 사용 가능

SELECT 칼럼명1, 칼럼명2 ...
FROM 테이블명
WHERE 조건문 (SELECT 칼럼명3, 칼럼명4
                           FROM 테이블명
                           WHERE 조건문);

---

[단일행 서브쿼리]

: 서브 쿼리의 결과가 단일 행

: 연산자 - =, <> (!=), >, >=, <, <= 

 

[예시1]

-- comm 테이블에서 ename이 WARD인 사람의 comm이 작은 ename과 comm 조회
--  서브쿼리로 작성
SELECT ename, comm
FROM emp
WHERE comm < (SELECT comm FROM emp WHERE ename='WARD');

-- join으로 나타낸 것
SELECT e1.ename, e1.comm
FROM emp e1 JOIN emp e2
WHERE e2.ename='WARD' AND e1.comm < e2.comm;

[예시2]

-- student, department 테이블을 이용하여 서진수 학생과 주전공이 동일한 학생들의 이름과 전공 조회
SELECT s.name, d.dname
FROM student s JOIN department d
ON s.deptno1=d.deptno
WHERE s.deptno1 = (SELECT deptno1 FROM student WHERE name='서진수');

[예시3]

-- professor, department 테이블을 이용하여 박원범 교수보다 나중에 입사한 사람의 이름, 입사일, 학과명 조회
SELECT p.name, p.hiredate, d.dname
FROM professor p 
JOIN department d ON p.deptno=d.deptno
WHERE p.hiredate > (SELECT hiredate FROM professor WHERE name='박원범');

[예시4]

-- 위 예시보다 아래 예시처럼 명시적으로 주는 것이 더 많이 사용됨
-- student 테이블에서 주전공이 201 학과(전자공학과)의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게 조회
SELECT s.name, s.weight
FROM student s
WHERE s.weight > (SELECT AVG(weight) FROM student WHERE deptno1='201');

-- student 테이블에서 주전공이 전자공학과인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게 조회
SELECT s.name, s.weight
FROM student s
WHERE s.weight > (SELECT AVG(st.weight) 
					FROM student st 
					JOIN department d ON d.deptno = st.deptno1 
					WHERE d.dname = '전자공학과')
ORDER BY 2;

[예시5]

-- gogak, gift 테이블을 이용하여 노트북을 받을 수 있는 고객의 이름, 포인트 조회
SELECT go.gname, go.point
FROM gogak go
WHERE go.point > (SELECT g_start FROM gift WHERE gname='노트북');

[예시6]

-- gogak, gift 테이블을 이용하여 노트북을 받을 수 있는 고객의 이름, 포인트 조회
SELECT go.gname, go.point
FROM gogak go
WHERE go.point > (SELECT g_start FROM gift WHERE gname='노트북');

[예시7]

-- emp, dept 테이블을 이용하여 sales 부서를 제외한 나머지 부서에 속한 직원의 사번, 이름, 부서명 조회
SELECT e.empno, e.ename, d.dname
FROM emp e JOIN dept d USING(deptno)
WHERE deptno <> (SELECT deptno FROM dept WHERE dname='SALES');

[예시8] 서브쿼리 2개 이용

-- student, exam_01, hakjum 테이블을 이용하여 학점이 A0인 학생의 학번, 이름, 점수 조회
-- USING도 사용 가능
-- between 사용 가능
SELECT s.studno, s.name, e.total
FROM student s JOIN exam_01 e ON s.studno=e.studno
WHERE e.total > (SELECT min_point FROM hakjum WHERE grade='A0')
				AND e.total < (SELECT min_point FROM hakjum WHERE grade='A+')
ORDER BY 3;

[예시9] 메인 쿼리의 테이블을 서브쿼리 내에서 사용

-- emp2 테이블에서 본인이 속한 부서의 평균 연봉보다 적게 받는 직원의 이름, 연봉, 부서명 조회
-- 단일행
SELECT name, pay, d.dname
FROM emp2 e JOIN dept2 d ON e.deptno = d.dcode
WHERE pay < (SELECT AVG(pay) FROM emp2 WHERE deptno=e.deptno);

---

[다중행 서브쿼리]

: 서브 쿼리 결과가 여러 행

: 연산자 - in, exists, > any, > any, <all, >all

: any - 하나라도 만족하면 true >> 데이터들 중 하나라도 만족하면 출력, all - 모두 만족해야 true >> 데이터 중 모든 것이 만족해야 함

 

[예시1]

-- emp2 테이블을 이용하여 '과장' 직급의 최소 연봉자보다 연봉이 높은 직원의 사번, 이름, 연봉 조회
-- 단일행
SELECT empno, name, pay
FROM emp2
WHERE pay > (SELECT MIN(pay) FROM emp2 WHERE position='과장');

-- 다중행
SELECT empno, name, pay
FROM emp2
WHERE pay >any (SELECT pay FROM emp2 WHERE position='과장')
ORDER BY 3;

[예시2]

-- student 테이블에서 학년별로 키가 가장 큰 학생의 이름과 학년 조회
SELECT name, grade, height
FROM student
WHERE (grade, height) IN (SELECT grade, max(height) FROM student GROUP BY grade)
ORDER BY 2;

[예시3]

-- student 테이블에서 학년이 1학년인 학생중에 몸무게가 가장 적은 학생보다 몸무게가 적은 학생의 이름, 학년, 몸무게 조회
SELECT name, grade, weight
FROM student
WHERE weight <all (SELECT weight FROM student WHERE grade=1);

[예시4]

-- emp2, dept2 테이블을 이용해 각 부서별 평균 연봉을 구하고 평균 연봉이 가장 적은 부서의 평균 연봉보다 많이 받는 직원들의 직원명, 부서명, 연봉 조회
SELECT e.name, d.dname, e.pay
FROM emp2 e JOIN dept2 d ON e.deptno=d.dcode
WHERE e.pay >any (SELECT AVG(pay) FROM emp2 GROUP BY deptno);

[예시5]

-- student, exam_01, department 테이블에서 같은 학과 같은 학년 학생의 평균 점수보다 점수가 높은 학생의 학번, 이름, 학과, 학년, 점수 조회
SELECT s.studno, s.name, d.dname, s.grade, e.total
FROM student s JOIN department d ON s.deptno1=d.deptno
JOIN exam_01 e USING(studno)
WHERE e.total >any (SELECT AVG(e1.total) 
				FROM student s1 JOIN exam_01 e1 USING(studno) 
				GROUP BY grade, deptno1)
ORDER BY s.deptno1, s.grade;

[예시6] Exists

-- student, professor 테이블에서 담당 학생이 있는 교수들의 교수번호, 교수명 조회
SELECT DISTINCT s.profno, p.name
FROM student s JOIN professor p USING(profno)
WHERE profno IS NOT NULL;

SELECT p.profno, p.name
FROM professor p
WHERE EXISTS (SELECT * FROM student WHERE profno=p.profno);

SELECT e.profno, p.name
FROM (SELECT DISTINCT profno FROM student) e JOIN professor p USING(profno);


-- student, professor 테이블에서 담당 학생이 없는 교수들의 교수번호 교수명 조회
SELECT p.profno, p.name
FROM professor p
WHERE NOT EXISTS(SELECT * FROM student s WHERE p.profno=s.profno);

 

---

[Structure Query Language]

DDL(Data Definition Language)

 

: 데이터 정의어(데이터베이스를 정의하는 언어)

: 데이터가 아닌 CREATE에 의해 생성된 것을 기준으로 함(즉, 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 대상으로 함)

: CREATE(생성), ALTER(변경), TRUNCATE(잘라내기), DROP(삭제)

 

[데이터베이스 생성 및 삭제]

-- 데이터베이스 생성
CREATE DATABASE 데이터베이스명;

-- 데이터베이스 삭제
DROP DATABASE 데이터베이스명;

---

CREATE

[테이블 생성]

CREATE TABLE 테이블명(
    칼럼명1  데이터 타입 PRIMARY KEY,
    칼럼명2 데이터 타입 NOT NULL,
    칼럼명3 데이터 타입 DEFAULT 값,
    ....,
    PRIMARY KEY(칼럼명1)
);

: PRIMARY KEY

- NOT NULL(default)이면서 UNIQUE하고 유일한 것으로 칼럼과 함께 지정도 가능하고, 마지막에 작성도 가능하며, 추후 추가도 가능

: NOT NULL - NULL을 허용하지 않음

: DEFAULT 값 - 작성한 값으로 디폴트 값 지정

 

[기존 테이블에서 데이터 가져와 테이블 생성]

CREATE TABLE 생성할 테이블명 AS (복사할 테이블의 SELECT문 작성)

CREATE TABLE emp_sub 
	AS SELECT empno, ename, job, hiredate, sal FROM emp WHERE deptno=10;

 

[빈 테이블 생성]

CREATE TABLE 생성할 테이블명 AS (SELECT * FROM 테이블명 WHERE 거짓조건문);

CREATE TABLE emp_t AS SELECT * FROM emp WHERE 1=2;

---

ALTER

[이미 생성된 테이블에 칼럼 추가]

ALTER TABLE 테이블명 ADD 칼럼명 데이터 형식 및 권한 설정;

[이미 있는 칼럼 데이터 형식 변경]

ALTER TABLE 테이블명  MODIFY COLUMN 변경할 칼럼명 변경할 데이터형식;

[칼럼 삭제]

ALTER TABLE 테이블명 DROP 칼럼명;

[칼럼명 변경]

ALTER TABLE 테이블명 RENAME COLUMN 기존 칼럼명 TO 변경할 칼럼명;

[테이블명 변경]

RENAME TABLE 기존 테이블명 TO 변경할 테이블명;

---

[테이블 삭제]

DROP TABLE 테이블명;

---

[데이터 삭제]

TRUNCATE TABLE 테이블명;
DELETE FROM 테이블명 (WHERE 조건절);

- TRUNCATE는 테이블을 삭제하지는 않지만 테이블의 내용은 모두 삭제하며, 메모리까지 삭제

- DELETE는 테이블을 삭제하지 않고 모든 데이터가 삭제되지만, 메모리는 유지

---

[constraint(제약조건)]

- PRIMARY KEY : 동일한 데이터 및 NULL값을 허용하지 않음 (unique & not null)

- NOT NULL : NULL값이 허용되지 않음

CREATE TABLE temp(
	id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL
);

INSERT INTO temp VALUES(null, 'jung'); -- 에러 : id에 null 값이 들어갈 수 없음
INSERT INTO temp VALUES(1, 'jang');
INSERT INTO temp VALUES(1, 'gong'); -- 에러 : 동일한 id 생성 불가
INSERT INTO temp VALUES(2, NULL); -- 에러 : 이름에 null이 들어갈 수 없음

- UNIQUE : 동일한 데이터를 허용하지 않지만 null은 허용되며, null은 중복도 허용

CREATE TABLE temp2(
	email VARCHAR(50) UNIQUE
);

INSERT INTO temp2 VALUES(null);
INSERT INTO temp2 VALUES('kosta@kosta.com');
INSERT INTO temp2 VALUES(null);
INSERT INTO temp2 VALUES('kosta@kosta.com'); -- 에러 : email 중복

- CHECK : 특정 값의 타당성을 보장하기 위한 것으로, 값의 범위를 제한. check(값의 조건 설정)

CREATE TABLE temp3(
	name VARCHAR(20) NOT NULL,
	age INT DEFAULT 1 CHECK(age>0)
);

-- 칼럼값을 전부 넣을 것이 아닌 경우 칼럼명 명시가 필요
INSERT INTO temp3 (name) VALUES('hong');
INSERT INTO temp3 VALUES('song', -2); -- 에러 : age>0이라는 check 조건을 만족하지 않음
INSERT INTO temp3 VALUES('song', 1);

 

- FOREIGN KEY : 2개 이상의 테이블의 관계를 맺는 것

더 자세히는 내일 블로깅 참고

 

 

---

DML(Data Manipulation Language)

: 데이터 조작어(데이터베이스 내 레코드를 조작하는 언어)

: 데이터를 기준으로 함

: INSERT(입력), UPDATE(수정), DELETE(삭제), MERGE(병합)

 

---

DCL(Data Controller Language)

: 데이터 제어어

: 데이터베이스 접근에 대한 권한을 결정하는 역할을 하는 언어

: GRANT(권한 추가), REVOKE(권한 삭제)

- 권한은 계정마다 특정 테이블에 권한을 주거나 테이블 사이의 관계에 따른 권한을 주는 등에 대한 제어

---

TCL(Transaction Control Language)

: COMMIT(확정-모든 작업을 정상으로 처리), ROLLBACK(취소-이전 커밋 상태로 되돌림)

: 트랜젝션 - 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위

---

DQL(Data Query Language)

: SELECT(선택)