Ⅰ. 제약조건 삭제
1. 기본 제약조건 삭제
-- 테이블 TBL_BUYCOMMENT04의 제약조건 조회
HR
select *
from user_constraints
where table_name = 'TBL_BUYCOMMENT04';
-- 데이터 값('TBL_BUYCOMMENT04')는 꼭 대문자로 적어 주어야 한다.
-- TBL_BUYCOMMENT04의 foriforeign key 제약조건 제거
HR
alter table tbl_buycomment04
drop constraint fk_tbl_buycomment04_fk_userid;
select *
from user_constraints
where table_name = 'TBL_BUYCOMMENT04';
2. NOT NULL 제약 제거
▷ 첫 번째 방법
-- TBL_HOIWON04의 제약조건 조회
HR
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
-- TBL_HOIWON04의 not null 제약조건 조회
HR
alter table tbl_hoiwon04
drop constraint SYS_C007067;
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
-- 제약조건의 이름(SYS_C007067)을 알기 어렵다.
▷ 두 번째 방법
HR
alter table tbl_hoiwon04
modify name null;
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
-- name은 컬럼명
HR
desc tbl_hoiwon04;
-- passwd와 name이 null 값이 허용되도록 바뀌었다.
tbl_hoiwon04에서 primary key를 삭제해 보자.
HR
alter table tbl_hoiwon04
drop constraint pk_tbl_hoiwon04_userid;
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
HR
desc tbl_hoiwon04;
-- primary key가 삭제되면서 userid가 null 값이 허용되도록 바뀌었다.
Ⅱ. 제약조건 추가
1. 기본 제약조건 추가
-- foreign key를 추가하려면 먼저 not null 부터 제거해 주어야 한다.
HR
alter table tbl_buycomment04
modify fk_userid null;
select *
from user_constraints
where table_name = 'TBL_BUYCOMMENT04';
-- tbl_hoiwon04 테이블에서 userid를 참조하는 foreign key 제약조건 추가하기
alter table TBL_BUYCOMMENT04
add constraint FK_tbl_buyComment04_fk_userid foreign key(fk_userid)
references tbl_hoiwon04(userid) on delete set null;
-- 참조받는 tbl_hoiwon04 userid 컬럼에 unique 또는 primary key를 앞서 삭제해서 없기 때문에 오류가 발생한다.
HR
alter table tbl_hoiwon04
add constraint pk_tbl_hoiwon04_userid primary key(userid);
alter table TBL_BUYCOMMENT04
add constraint FK_tbl_buyComment04_fk_userid foreign key(fk_userid)
references tbl_hoiwon04(userid) on delete set null;
select *
from user_constraints
where table_name = 'TBL_BUYCOMMENT04';
-- primary key를 추가하고 foreign key를 다시 추가한다.
2. NOT NULL 제약 추가
-- NOT NULL 제약을 추가할 때는 add constraint 를 쓰면 오류가 발생하기 때문에, NOT NULL 제약을 추가할 때는 modify를 사용해야 한다.
▷ add constraint를 사용할 경우
HR
alter table tbl_hoiwon04
add constraint NN_TBL_HOIWON_PASSWD passwd not null;
▷ modify를 사용할 경우
HR
alter table tbl_hoiwon04
modify passwd not null;
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
desc tbl_hoiwon04;
HR
alter table tbl_hoiwon04
modify name not null;
desc tbl_hoiwon04;
-- NOT NULL 제약은 modify를 사용하는 것이 좋다.
Ⅲ. 제약조건 수정
-- 제약조건을 수정하는 명령문은 없다.
-- 제약조건을 수정하려면 생성된 제약조건을 삭제하고 새로이 수정된 내용을 제약조건을 추가하는 것밖에 없다.
문제 ▷▷ tbl_sawontest02 에서 jik 컬럼에 Check제약에 '주임'을 추가한다.
HR
select *
from user_constraints
where table_name = 'TBL_SAWONTEST02';
HR
alter table tbl_sawontest02
drop constraint ck_tbl_sawontest02_jik;
alter table tbl_sawontest02
add constraint ck_tbl_sawontest02_jik check (jik in('사장','부장','과장','대리','주임','사원'));
select *
from user_constraints
where table_name = 'TBL_SAWONTEST02';
Ⅳ. 제약조건 활성화/비활성화
1. 제약조건을 비활성화 시키기
-- 삭제하는 것이 아니다.
-- TBL_SAWONTEST02의 제약조건 조회
select *
from user_constraints
where table_name = 'TBL_SAWONTEST02';
-- ck_tbl_sawontest02_jik 제약조건 비활성화
alter table tbl_sawontest02
disable constraint ck_tbl_sawontest02_jik;
select *
from user_constraints
where table_name = 'TBL_SAWONTEST02';
1-2. foreign key에 의해 참조를 받는 primary key 제약조건을 비활성화
-- foreign key에 의해 참조를 받는 primary key 제약조건을 비활성화 하고자 할 때는 한 번에 비활성화가 안 된다. 먼저 참조를 하고 있는 foreign key를 비활성화 시킨 다음에 참조를 받는 primary key를 비활성화 해야 한다.
-- 그런데 cascade 옵션을 사용하면 이 두가지작업(foreign key를 비활성화, primary key를 비활성화) 을 한꺼번에 할 수 있다.
-- TBL_HOIWON04 테이블의 제약조건 조회
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
-- TBL_BUYCOMMENT04 테이블의 제약조건 조회
select *
from user_constraints
where table_name = 'TBL_BUYCOMMENT04';
-- TBL_BUYCOMMENT04의 foreign key(FK_TBL_BUYCOMMENT04_FK_USERID)는 TBL_HOIWON04의 primary key(PK_TBL_HOIWON04_USERID)를 참조하고 있다.
-- TBL_HOIWON04 테이블의 pk_tbl_hoiwon04_userid 비활성화를 시켜 보면 오류가 발생한다.
alter table tbl_hoiwon04
disable constraint pk_tbl_hoiwon04_userid;
-- cascade를 사용하여 TBL_HOIWON04의 primary key(PK_TBL_HOIWON04_USERID)와 TBL_BUYCOMMENT04의 foreign key(FK_TBL_BUYCOMMENT04_FK_USERID)를 한 번에 비활성화시킨다.
alter table tbl_hoiwon04
disable constraint pk_tbl_hoiwon04_userid cascade;
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
select *
from user_constraints
where table_name = 'TBL_BUYCOMMENT04';
-- foreign key와 primary key 모두 비활성화 되었다.
문제 ▷▷ TBL_BUYCOMMENT04 테이블에 있는 FK_TBL_BUYCOMMENT04_FK_USERID 제약조건의 상태가 DISABLED 인데 상태를 ENABLED로 변경하시오.
alter table tbl_hoiwon04
enable constraint pk_tbl_hoiwon04_userid;
alter table tbl_buycomment04
enable constraint fk_tbl_buycomment04_fk_userid;
select *
from user_constraints
where table_name = 'TBL_HOIWON04';
select *
from user_constraints
where table_name = 'TBL_BUYCOMMENT04';
-- 활성화 시킬 때는 cascade를 사용할 수 없기 때문에 각각 primary key를 활성화 시킨 다음에 foreign key를 활성화 시켜야 한다.
2. 비활성화 되어진 제약조건을 활성화 시키기
alter table tbl_sawontest02
enable constraint ck_tbl_sawontest02_jik;
select *
from user_constraints
where table_name = 'TBL_SAWONTEST02';
Ⅴ. default 값 조회 및 변경
create table tbl_Newmember
(userid varchar2(20)
,passwd varchar2(20)
,name Nvarchar2(20)
,jobname Nvarchar2(20) default '없음'
,registerday date default sysdate
);
insert into tbl_Newmember(userid, passwd, name)
values('leess','1234','이순신');
commit;
select *
from tbl_Newmember;
-- TBL_NEWMEMBER의 컬럼에 대한 정보 조회
select *
from user_tab_columns
where table_name = 'TBL_NEWMEMBER';
jobname의 default 값을 '없음'에서 '학생'으로 바꾸어 보자.
alter table tbl_newmember
modify jobname default '학생';
select *
from user_tab_columns
where table_name = 'TBL_NEWMEMBER';
jobname의 default 값을 삭제해 보자.
alter table tbl_newmember
modify jobname default null;
select *
from user_tab_columns
where table_name = 'TBL_NEWMEMBER';
-- (null) 과 null 은 같은 것을 말한다.
-- 만약 null 이라는 문자를 넣고 싶다면 'null'이라고 입력해야 한다.
alter table tbl_newmember
modify jobname default 'null';
select *
from user_tab_columns
where table_name = 'TBL_NEWMEMBER';
▷ 테이블명 변경하기
select *
from tbl_Newmember;
rename tbl_Newmember to tbl_Oldmember;
▷ 결과
테이블 이름이 변경되었습니다.
-- 이름을 변경하고 예전 이름으로 다시 조회해 보면 오류가 발생한다.
select *
from tbl_Newmember;
▷ 컬럼명 변경하기
alter table tbl_Newmember
rename column passwd to password;
select *
from tbl_Newmember;
▷ 컬럼의 데이터타입 크기 변경하기
desc tbl_Newmember;
passwd varchar2(20)을 varchar(40)으로 변경해 보자.
alter table tbl_Newmember
modify passwd varchar2(40);
desc tbl_Newmember;
passwd varchar2(40)을 varchar(10)으로 변경해 보자.
alter table tbl_Newmember
modify passwd varchar2(10);
desc tbl_Newmember;
passwd varchar2(10)을 varchar(2)으로 변경해 보자.
alter table tbl_Newmember
modify passwd varchar2(2);
-- 이미 insert 되어진 data 값이 있기 때문에 최소 4byte('1234')보다 커야 변경할 수 있다.
-- insert 되어진 data 값이 없을 경우 마음대로 변경할 수 있다.
▷ 새로운 컬럼 추가하기
alter table tbl_Newmember
add adress Nvarchar2(100);
desc tbl_Newmember;
새로 추가한 컬럼을 Not Null로 지정하고자 한다.
alter table tbl_Newmember
add adress Nvarchar2(100) not null;
-- 이미 insert 되어있는 값이 존재하기 때문에 오류가 발생한다.
데이터가 존재하는 테이블에 not null로 컬럼을 새로 추가하고자 한다.
alter table tbl_Newmember
add adress Nvarchar2(100) default ' ' not null;
desc tbl_Newmember;
▷ 컬럼 삭제하기
alter table tbl_Newmember
drop column adress;
desc tbl_Newmember;
Ⅵ. SQL
▷ SQL(Strctured Qurery Language) 이란?
-- RDBMS(Relation DataBase Management System)에서 구조화(정형화) 되어진 질의(검색, 요구)에 대해서 응답하도록 해 주는 언어이다.
▷ RDBMS(Relation DataBase Management System)란?
-- 데이터를 column과 row로 이루어진 2차원 형태의 테이블에 저장한 후 테이블과 테이블 사이에 참조관계를 이용하여 필요한 정보를 추출해 오며 CRUD 업무가 가능한 데이터베이스를 관리해 주는 소프트웨어를 말한다.
▷ CRUD란?
-- Create[생성], Read[읽기, select], Update[변경], Delete[삭제]
▷ RDBMS의 종류
-- ORACLE(ORACLE사), MySQL(ORACLE사)
-- MSSQL Server(Microsoft사)
-- DB2(IBM사), Informix(IBM사)
-- Sybase(Sybase사)
▷ Relation
-- 관계(테이블과 테이블을 참조하는 것. 1:1 관계, 1:多 관계, 多:多 관계)
▷ 테이블(table)이란?
-- 2차원 형태의 column(field, 열)과 row(record, 행)로 이루어진 것으로 데이터가 저장되어지는 곳이다.
show user;▷ 결과
USER이(가) "HR"입니다.
select * from tab;-- 현재 오라클 서버에 접속한 사용자(지금은 HR)가 만든 (사용자소유의) 테이블과 뷰의 이름을 조회해 주는 것이다.
★★★★ 굉장히 중요 ★★★★
▷ || (vertical line, pipe)
select *
from user_tables
where table_name = 'EMPLOYEES';
-- EMPLOYEES 테이블에서 department_id가 50인 사원들의 employee_id, first_name, last_name, salary, department_id 나타내기
select employee_id, first_name, last_name, salary, department_id
from employees
where department_id = 50;
first_name과 last_name을 묶어서 나타내 보자.
select employee_id, first_name || ' ' || last_name, salary, department_id
from employees
where department_id = 50;
-- 컬럼과 컬럼을 이어주는 것이 || 이다.
-- first_name과 last_name이 너무 붙지 않게 하기 위해 공백을 넣어 주었다.
▷ 별칭(별명, alias)
-- select 되어진 컬럼에 대해 별칭(별명, alias)을 부여한다.
select employee_id
, first_name || ' ' || last_name AS "사원명"
, salary
, department_id
from employees
where department_id = 50;
-- AS는 소문자(as)로도 가능하다.
select employee_id AS "사원번호"
, first_name || ' ' || last_name "사원명"
, salary
, department_id
from employees
where department_id = 50;
-- AS는 생략이 가능하다.
select employee_id AS "사원번호"
, first_name || ' ' || last_name "사원명"
, salary 기본급여
, department_id
from employees
where department_id = 50;
-- 쌍따옴표도 생략이 가능하다.
-- 그러나 별칭(alias)에 공백을 포함하고 싶다면 쌍따옴표를 꼭 써 주어야 한다.
▷ select 정렬(Order by)
-- SQL 문에서 by 의 뜻은 ...에 의해서, ...에 따라, 즉, 어떤 기준이냐라는 말이다.
-- select 되어진 결과물을 정렬(오름차순, 내림차순)해서 보기
▷ salary의 오름차순 정렬
select employee_id AS "사원번호"
, first_name || ' ' || last_name "사원명"
, salary 기본급여
, department_id "부서 번호"
from employees
where department_id = 50
order by salary asc;
-- asc은 생략이 가능하고, 생략이 되어있다면 asc를 말한다.
select employee_id AS "사원번호"
, first_name || ' ' || last_name "사원명"
, salary 기본급여
, department_id "부서 번호"
from employees
where department_id = 50
order by salary desc;
▷ salary의 내림차순 정렬
select employee_id AS "사원번호"
, first_name || ' ' || last_name "사원명"
, salary 기본급여
, department_id "부서 번호"
from employees
where department_id = 50
order by 3;
▷ salary의 오름차순 정렬
-- 위치값으로도 지정이 가능하다.
▷ NULL을 처리해 주는 함수
-- NULL은 존재하지는 않는 것이다.
-- 비교연산을 할 수 없다.
-- NULL 에 사칙연산( +, -, *, / )을 해 주면 그 결과는 무조건 NULL이 되어진다.
select 5+2, 5-2, 5*2, 5/2
from dual; -- 저장해 주기 위한 가상 테이블
select 20, null, 20+10, 20+null, 20-null, 0*null, 2/null
from dual;
▷ NVL
select nvl(20,-5), nvl(null, -5), nvl(20+10, 0), nvl(20+null, 0)
from dual;
-- 결과물이 null이라면 오른쪽 값을 나타내고 null이 아니라면 왼쪽 값을 나타낸다.
select nvl('이순신', '거북선'), nvl(null, '거북선')
from dual;
select employee_id
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
, commission_pct AS 수당퍼센티지
, salary * commission_pct AS 수당금액
, salary + (salary * commission_pct) AS 월급1
, nvl(salary + (salary * commission_pct), salary) AS 월급2
, nvl(salary + (salary * commission_pct), salary) * 12 AS 연봉
, department_id AS 부서번호
from employees;
-- 월급 1 은 commission_pct가 null인 경우가 있기 때문에 월급도 null로 변한다.
▷ NVL2
select nvl2(20, 100, 200), nvl2(null, 100, 200)
from dual;
-- 첫 번째 값이 null이 아니라면 두 번째 값을 나타내고, 첫 번째 값이 null이라면 세 번째 값을 나타낸다.
문제 ▷▷ nvl2를 이용하여 월급2를 월급1과 같이 구하라.
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
, commission_pct AS 수당퍼센티지
, nvl(salary + (salary * commission_pct), salary) AS 월급1
, AS 월급2
, department_id AS 부서번호
from employees;
nvl2(commission_pct, salary + (salary * commission_pct), salary) AS 월급2
employees 테이블에서 department_id가 null인 사원만 모든 정보를 나타내 보자.
※ employees 테이블에서 Kimberely Grant는 유일하게 부서 번호가 없다.
select employee_id, department_id, nvl(department_id, -9999)
from employees;
select employee_id, department_id
from employees
where department_id = null;
-- null을 사용할 때는 = 을 사용하는 것이 아니라 is를 사용한다.
select employee_id, department_id
from employees
where department_id is null;
employees 테이블에서 coission_pct가 null 인 사원들만 사원번호, 사원명, 기본급여, 커미션퍼센티지, 부서번호를 나타내세요.
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
, commission_pct AS 커미션퍼센티지
, department_id AS 부서번호
from employees
where commission_pct is null;
employess 테이블에서 department_id가 50번이 아닌 부서에 근무하는 사원들에 대해서 사원번호, 사원명, 기본급여, 커미션퍼센티지, 부서번호를 나타내세요.
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
, commission_pct AS 커미션퍼센티지
, department_id AS 부서번호
from employees
where department_id != 50
order by 부서번호;
-- 50번이 아닌 부서에 근무하는 사원들을 나타내었지만 null은 비교 연산자를 할 수 없으므로 Kimberely Grant가 제외되었다.
문제 ▷▷ 부서번호가 null인 Kimberely Grant를 나타내세요.
▷ 첫 번째 방법(나)
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
, commission_pct AS 커미션퍼센티지
, department_id AS 부서번호
from employees
where department_id <> 50 or department_id is null
order by 부서번호;
▷ 두 번째 방법
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
, commission_pct AS 커미션퍼센티지
, department_id AS 부서번호
from employees
where nvl(department_id, -9999) != 50
order by 부서번호;
문제 ▷▷ employees 테이블에서 월급(기본급+수당금액)이 10000보다 큰 사원들만 사원번호, 사원명, 월급, 부서번호로 나타내되 월급의 내림차순으로 보이게 하세요.
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, nvl2(commission_pct, salary+(salary*commission_pct), salary) AS 월급
, department_id AS 부서번호
from employees
where nvl2(commission_pct, salary+(salary*commission_pct), salary) > 10000
order by 월급 desc;
employees 테이블에서 월급(기본급+수당금액)이 10000보다 큰 사원들만 사원번호, 사원명, 월급, 부서번호로 나타내되 부서번호의 오름차순으로 정렬한 후 동일한 부서번호 내에서는 월급의 내림차순으로 보이게 하세요.
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, nvl2(commission_pct, salary+(salary*commission_pct), salary) AS 월급
, department_id AS 부서번호
from employees
where nvl2(commission_pct, salary+(salary*commission_pct), salary) > 10000
order by 부서번호 asc, 월급 desc;
▷ OR, AND, NOT, IN 연산자
employees 테이블에서 부서번호가 30, 60, 90번 부서에 근무하는 사원들에 대해서 부서번호, 사원번호, 사원명, 연봉(월급*12)을 나타내되 부서번호의 오름차순으로 정렬한 후 동일한 부서번호 내에서는 연봉의 내림차순으로 보이게 하세요.
-- OR
select department_id as 부서번호
, employee_id as 사원번호
, first_name || ' ' || last_name as 사원명
, nvl( salary + (salart * commission_pct), salary)*12 as 연봉
from employees
where department_id = 30 or
department_id = 60 or
department_id = 90 or
order by 부서번호, dusqhd desc;
-- IN
select department_id as 부서번호
, employee_id as 사원번호
, first_name || ' ' || last_name as 사원명
, nvl( salary + (salart * commission_pct), salary)*12 as 연봉
from employees
where department_id in(30, 60, 90)
order by 부서번호, dusqhd desc;
employees 테이블에서 부서번호가 30, 60, 90번 부서에 근무하지 않는 사원들에 대해서 부서번호, 사원번호, 사원명, 연봉(월급*12)을 나타내되 부서번호의 오름차순으로 정렬한 후 동일한 부서번호 내에서는 연봉의 내림차순으로 보이게 하세요
select department_id as 부서번호
, employee_id as 사원번호
, first_name || ' ' || last_name as 사원명
, nvl( salary + (salary * commission_pct), salary)*12 as 연봉
from employees
where department_id != 30 and
department_id != 60 and
department_id != 90
order by 부서번호, 연봉 desc;
-- Kimberely Grant가 제외되었다.
select department_id as 부서번호
, employee_id as 사원번호
, first_name || ' ' || last_name as 사원명
, nvl( salary + (salary * commission_pct), salary)*12 as 연봉
from employees
where (nvl(department_id, -9999) != 30 and
nvl(department_id, -9999) != 60 and
nvl(department_id, -9999) != 90)
order by 부서번호, 연봉 desc;
▷ 범위 연산자
-- between A and B : A부터 B까지
employees 테이블에서 기본급여가 3000부터6000 까지인 사원들만 사원번호, 사원명, 기본급여를 나타내세요.
select employee_id as 사원번호
, first_name || ' ' || last_name as 사원명
, salary as 기본급여
from employees
where salary between 3000 and 6000
order by 기본급여;
-- 범위를 할 때, 문자와 날짜도 가능하다.
employees 테이블에서 first_name이 'E'부터 'T'까지인 사원들의 사원 번호, 사원이름, 기본급여를 나타내세요.
select employee_id
, first_name
, salary
from employees
where 'E' <= first_name and first_name <= 'T'
order by first_name;
또는
select employee_id
, first_name
, salary
from employees
where first_name between 'E' and 'T'
order by first_name;
▷ 내일 수업 미리 준비
-- employees 테이블에 주민번호(jubun) 컬럼 추가하기
alter table employees
add jubun varchar2(13);
update employees set jubun = '5710151234567'
where employee_id = 100;
-- employee_id = 100부터 employee_id = 206까지 주민번호를 입력한다.