[SQL] 데이터 정의어 / 무결성 제약조건 / SELECT문 / 실습

SQL

비절차적 언어(선언적 언어)로 사용자는 자신이 원하는 바만 명시하며, 원하는 것을 처리하는 방법은 명시할 수 없다. 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정을 담당한다. 자연어에 가까운 구문을 사용하여 질의를 표현한다는 장점이 있다

 

인터페이스

• 대화식 Interface SQL
• 내포된 Embedded SQL

 

구성요소

• 데이터 검색: select
• 데이터 조작어 DML: insert, update, delete
• 데이터 정의어 DDL: create, alter, drop
• 트랜잭션 제어 TCL: commit, rollback
• 데이터 제어어 DCL: grant, revoke

 

 

 

데이터 정의어와 무결성 제약 조건

데이터 정의어의 종류

create	domatin	도메인 생성
	table	테이블 생성
	view	뷰 생성 (*뷰: 가상의 테이블)
	index	인덱스 생성 (SQL2 표준X)
alter	table	테이블 구조 변경
drop	domain	도메인 제거
	table	테이블 제거
	view	뷰 제거
	index	인덱스 제거 (SQL2 표준X)

-- 관계 제거
drop table DEPARTMENT;

-- 테이블 변경
alter table EMPLOYEE add PHONE char(13);

-- 인덱스 생성
create unique index EMPINDEX on EMPLOYEE(EMPNO);

-- 도메인 생성
create domain DEPTNAME char(10) default '개발';

 

관계 정의에 사용되는 데이터 타입

interget 또는 int	정수형
smallint	작은 정수형
number(n, s) 또는 decimal(n, s)	n개의 숫자에서 소수 아래 숫자가 s개인 십진수
real	실수형
float (n)	적어도 n개의 숫자가 표현되는 실수형
character (n) 또는 char (n)	n바이트 문자열 (default=1)
varchar (n) 또는 character varying (n)	최대 n바이트까지의 가변 길이 문자열
bit (n) 또는 bit varying (n)	n개의 비트열 또는 최대 n개까지의 가변 비트열
date	날짜형
blob	binary large object, 멀티미디어 데이터 등 저장

create table employee(
    EMPNO number not null,
    EMPNAME char(10),
    TITLE char(10),
    MANAGER number,
    SALARY number, 
    DNO number,
    primary key (EMPNO),
    foreign key (MANAGER) references EMPLOYEE (EMPNO),
    foreign key (DNO) references DEPARTMENT (DEPTNO));

 

무결성 제약조건

• not null: NULL값이 아님
• unique: 유일값만을 가짐
• default 'A': 기본값은 A
• check: 이하 조건을 만족하는 값
• primary key
• foreign key

create table employee(
    EMPNO number not null,
    EMPNAME char(10) unique,
    TITLE char(10) default '사원',
    MANAGER number,
    SALARY number check (SALARU < 6000000), 
    DNO number check (DNO in (1,2,3,4)) default 1,
    primary key (EMPNO),
    foreign key (MANAGER) references EMPLOYEE (EMPNO),
    foreign key (DNO) references DEPARTMENT (DEPTNO)
    		on delete set default on update cascade);

 

참조 무결성 제약조건 유지

• on delete no action
• on delete cascade 
• on delete set null
• on delete set default
• on update no action
• on update cascade
• on update set null
• on update set dafault

update DEPARTMENT
set DEPTNO = 6
where DEPTNO = 3;

 

실습 데이터

-- DEPARTMENT 테이블 생성
CREATE TABLE DEPARTMENT (
	DEPTNO		NUMBER	NOT NULL,
	DEPTNAME	CHAR(10),
	FLOOR		NUMBER,
	PRIMARY KEY(DEPTNO)
);

INSERT INTO DEPARTMENT VALUES(1, '영업', 8);
INSERT INTO DEPARTMENT VALUES(2, '기획', 10);
INSERT INTO DEPARTMENT VALUES(3, '개발', 9);
INSERT INTO DEPARTMENT VALUES(4, '총무', 7);


-- EMPLOYEE 테이블 생성
CREATE TABLE EMPLOYEE (
	EMPNO	NUMBER	NOT NULL,
	EMPNAME	CHAR(10)	UNIQUE,
	TITLE	CHAR(10)	DEFAULT '사원',
	MANAGER	NUMBER,
	SALARY	NUMBER	CHECK (SALARY < 6000000),
	DNO		NUMBER	DEFAULT 1 CHECK (DNO IN (1,2,3,4)) ,
	PRIMARY KEY(EMPNO),
	FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO) ON DELETE CASCADE
);


INSERT INTO EMPLOYEE VALUES(2106, '김창섭', '대리', 1003, 2500000, 2);
INSERT INTO EMPLOYEE VALUES(3426, '박영권', '과장', 4377, 3000000, 1);
INSERT INTO EMPLOYEE VALUES(3011, '이수민', '부장', 4377, 4000000, 3);
INSERT INTO EMPLOYEE VALUES(1003, '조민희', '과장', 4377, 3000000, 2);
INSERT INTO EMPLOYEE VALUES(3427, '최종철', '사원', 3011, 1500000, 3);
INSERT INTO EMPLOYEE VALUES(1365, '김상원', '사원', 3426, 1500000, 1);
INSERT INTO EMPLOYEE VALUES(4377, '이성래', '이사',  NULL, 5000000, 2);


-- EMP_PLANNING 뷰 생성
CREATE VIEW EMP_PLANNING
AS
SELECT E.EMPNAME, E.TITLE, E.SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO=D.DEPTNO AND D.DEPTNAME='기획';

 

테이블 생성 확인

select * from tab;

 

테이블 구조 확인

describe department;
desc department;

 

Department 테이블 내용 확인

select * from department;

 

 

 

SELECT문

관계 데이터베이스에서 정보를 검색하는 SQL문으로 가장 자주 사용된다. 관계 대수의 실렉션과 의미가 완전히 다르며, 관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것이다. 

 

-- * 를 사용하여 모든 속성 검색
select *
from DEPARTMENT;

-- 원하는 속성 열거: 모든 부서의 부서번호와 부서이름
select DEPTNO, DEPTNAME
from DEPARTMENT;

-- 상이한 값 검색: 모든 사원의 직급
select distinct TITLE
from EMPLOYEE;

-- 특정 튜플들의 검색: 2번 부서에 근무하는 사원에 관한 모든 정보
select *
from EMPLOYEE
where DNO=2;

-- 문자열 비교: 이씨 성을 가진 사원들의 이름, 직급, 소속 부서번호
select EMPNAME, TITLE, DNO
from EMPLOYEE
where EMPNAME like '이%'; -- 첫 번째 글자는 이, 그 이후는 자유

-- 불 연산자를 사용한 프레드키트: 직급이 과장이면서 1번 부서에 근무하는 사원들의 이름과 급여
select EMPNAME, SALARY
from EMPLOYEE
where TITLE='과장' and DNO=1;

-- 부정 검색: 직급이 과장이면서 1번 부서에 속하지 않은 사원들의 이름과 급여
select EMPNAME, SALARY
from EMPLOYEE
where TITLE='과장' and DNO<>1;

-- 범위 연산자: 급여가 300만원 이상이고 450만원 이하인 사원들의 이름, 직급, 급여
select EMPNAME, TITLE, SALARY
from EMPLOYEE
where SALARY between 3000000 and 4500000; -- where SALARY>=3000000 and SALARY<=4500000;

-- 리스트를 사용한 검색: 1번 부서나 3번 부서에 소속된 사원들의 모든 정보
select *
from EMPLOYEE
where DNO in (1,3);

-- 산술연산자: 직급이 과장인 사원들의 이름, 현재 급여, 급여 10% 인상 값
select EMPNAME, SALARY, SALARY*1.1 as NEWSALARY
from EMPLOYEE
where TITLE='과장';

 

NULL

비어있는 값. Null값을 포함한 다른 값과 Null값을 +, - 등을 사용해 연산하면 결과는 Null이 나오며, Count(*)를 제외한 집단 함수들은 Null값을 무시한다. 어떤 속성에 들어 있는 값이 Null인지 비교하기 위해 'DNO=NULL'처럼 나타내면 안 된다.

 

ORDER BY절

사용자가 select문에서 질의 결과의 순서를 명시하지 않으면, 기본 키의 값이 증가하는 순서대로 사용자에게 제시된다. order by절은 select절에 명시된 하나 이상의 속성을 사용해 검색 결과를 정렬하며 디폴트는 오름차순이다. null값은 오름차순에서는 가장 마지막에, 내림차순에서는 가장 앞에 나타난다

-- 2번 부서에 근무하는 사원들의 급여, 직급, 이름 검색. 급여 오름차순으로 정렬
select SALARY, TITLE, EMPNAME
from EMPLOYEE
where DNO=2
orderby SALARY;

 

집단함수

 데이터베이스에서 검색된 여러 튜플들의 집단에 적용되는 함수로, 각 집단함수는 한 관계의 한 개의 속성에 적용되어 단일 값을 반환한다. select절과 having절에만 나타날 수 있다. 키워드 distinct가 집단함수 앞에 사용되면 집단함수가 적용되기 전에 먼저 중복값을 제거한다

 count(*)를 제외하고 모든 집단 함수들이 null값을 제거한 후 남아있는 값들에 대해 집단함수 결과를 구한다. count(*)는 겨로가 관계의 모든 행들의 총 개수를 구하고, count(속성)은 해당 속성에서 null값이 아닌 값들의 개수를 구한다

count	튜플이나 값의 개수
sum	합
avg	평균값
max	최대값
min	최소값

-- 모든 사원들의 평균 급여와 최대급여
select avg(SALARY) as AVGSAL, max(SALARY) as MAXSAL
from EMPLOYEE;

 

그룹화

group by절에 사용된 속성에 동일한 값을 갖는 튜플들이 각각 하나의 그룹으로 묶는다. 이때 사용된 속성을 그룹화 속성이라고 한다. 각 그룹에 대하여 결과 관계에 하나의 튜플이 생성된다. select 절에는 각 그룹마다 하나의 값을 갖는 속성, 집단함수, 그룹화에 사용된 속성들만 나타날 수 있다

-- 모든 사원들에 대해, 사원들이 속한 부서번호별로 그룹화하고 각 부서마다 부서번호, 평균급여, 최대급여 검색
select DNO, avg(SALARY) as AVGSAL, max(SALARY) as MAXSAL
from EMPLOYEE
group by DNO;

 

HAVING

어떤 조건을 만족하는 그룹에 대해서만 집단함수 적용. 각 그룹마다 하나의 값을 갖는 속성을 사용해 각 그룹이 만족해야하는 조건을 명시한다. 그룹화 속성에 같은 값을 갖는 튜플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타난다. having절에 나타나는 속성은 반드시 group by절에 나타나거나 집단함수에 포함되어야한다

*where 속성에 대한 정보, having 집단함수에 대한 정보

-- 모든 사원들에 대해 사원들이 속한 부서번호별로 그룹화하고
-- 평균급여가 250만원 이상이 부서에 대해 부서번호, 평균급여, 최대급여 검색
select DNO, avg(SALARY) as AVGSAL, max(SALARY) as MAXSAL
from EMPLOYEE
group by DNO
having avg(SALARY)>=2500000;

 

집합연산

적용 시 두 관계가 합집합 호환성을 가져야한다

• union
• except
• intersect
• union all
• except all
• intersect all

-- 김창섭이 속한 부서이거나 개발부서의 부서번호 검색
(select DNO
from EMPLOYEE
where ENPNAME='김창섭')
union
(select DEPTNO
from DEPARTMENT
where DEPTNAME='개발');

 

조인

 두 개의 관계로부터 연관된 튜플들의 결합. 두 관계 사이에 속하는 속성 값들을 비교연산자로 연결한 것으로, 가장 흔히 사용되는 비교연산자는 = 이다. 일반적인 형식은 from절에 두 개 이상의 관계가 열거되고, where절에 두 관계에 속하는 속성들을 비교하는 조건이 포함된다. 먼저 조인 조건을 만족하는 튜플들을 찾고, 이 튜플들로부터 select절에 명시된 속성들만 프로젝트하고, 필요하다면 중복을 배제하는 순서로 진행된다. 

 조인 조건을 생략했을 때와 조인 조건을 틀리게 표현했을 때는 카티션 곱이 생성된다. 조인 조건이 명확해지도록 속성 이름 앞에 관계 이름이나 튜플 변수를 사용하는 것이 바람직하다. 두 관계의 조인 속성 이름이 동일하다면 반드시 속성 이름 앞에 관계 이름이나 튜플 변수를 사용해야한다.

-- 모든 사원의 이름과 사원이 속한 부서 이름 검색
select EMPNAME, DEPTNAME
from EMPLOYEE as E, DEPARTNAME ad D
where E.DON=D.DEPTNO;

 

자체 조인

한 관계에 속하는 튜플을 동일한 관계에 속하는 다른 튜플들과 조인. 실제로는 한 관계에만 접근하지만 from절에 두 관계가 참조되는 것처럼 나타내기 위해 해당 관계에 대한 별칭을 두 개 지정한다

-- 모든 사원에 대해 사원이름과 직속상사이름 검색
select E.EMPNAME, M.EMPNAME
from EMPLOYEE E, EMPLOYEE M
where E.MANAGER=M.EMPNO;

-- 모든 사원에 대해 소속부서이름, 사원이름, 직급, 급여 검색
-- 부서이름에 대해 오름차순, 부서이름이 같은 경우 salary에 대해 내림차순
select DEPTNAME, EMPNAME, TITLE, SALARY
from EMPLOYEE E, DEPARTNAME D
where E.DNO = D.DEPTNO
order by DEPTNAME, SALARY desc;

 

부질의

외부 질의의 where절에 다시 select, from, where 등의 형태로 포함된 select문으로 중첩질의 nested query라고 한다. insert, delete, update문에도 사용할 수 있다. 중첩질의의 결과로 한 개의 스칼라값(단일 값), 한 개의 속성으로 이루어진 관계, 여러 속성으로 이루어진 관계가 반환될 수 있다

-- 한 개의 스칼라값 반환
-- 박영권과 같은 직급을 갖는 모든 사원들의 이름, 직급
select EMPNAME, TITLE
from EMPLOYEE
where TITLE = (select TITLE
	       from EMPLOYEE
	       where EMPNAME='박영권');
           
-- 영업부나 개발부에 근무하는 사원들의 이름
select EMPNAME
from EMPLOYEE
where DNO in (select DEPTNO
	       from DEPARTMENT
	       where DEPTNAME='영업' or DEPTNAME='개발');

select EMPNAME
from EMPLOYEE E, DEPARTMENT D
where E.DNO=D.DEPTNO and (D.DEPTNAME='영업'- or D.DEPTNAME='개발');


-- 여러 속성들로 이루어진 관계 반환
-- 영업부나 개발부에 근무하는 사원 이름
select EMPNAME
from EMPLOYEE E
where exists (select *
	       from DEPARTMENT D
	       where E.DNO=D.DEPTNO and (D.DEPTNAME='영업'- or D.DEPTNAME='개발');

 

 

 

실습

관리자로 접속 - 접속이름: 관리자, 사용자이름: system

alter user hr identified by hr account unlock;

HR로 접속 - 접속이름: HR, 사용자이름: hr, 비밀번호: hr

--20번 및 50번 부서에서 근무하는 모든 사원들의 last_name 및 부서번호 알파벳순 조회

--매니저가 없는 사람들의 last_name과 job_id 조회

--last_name의 네번째 글자가 a인 사원들의 last_name 조회

--시애틀에 사는 모든 사람들의 last_name, 부서명, 지역ID, 도시명 조회

--자신의 매니저보다 먼저 고용된 사원들의 last_name 및 고용일 조회