오라클 테이블 생성/삭제/수정/순서

* 테이블 만들기
- primary key(PK)
- 테이블 삭제
- 자동 증가 번호 제공 객체 : 
- insert 문
- 데이터 삭제
- 데이터 수정
- 컬럼의 순서
* group by
* having ****
* 서브 쿼리
--테이블 생성
 
-- 메모 테이블
 
-- 1. 엔티티(속석) 정의
--  메모(번호, 내용, 작성자, 작성시간)
 
-- 2. 설계
--  a. 번호 : 숫자, 식별자(***), NN, ND      1, 2.. m01, m02.. AB0011
--  b. 내용 : 문자열(2000), NN
--  c. 작성자 : 문자열(5), NN
--  d. 작성시간 : 날짜, NN
 
-- 3. 구현
--  1,2번의 결과를 실제 데이터베이스에 구현(구축)하는 단계 -> SQL 사용
 
 
-- SQL(시퀄), Query
 
create table 테이블명
(
  --테이블 스키마 선언(컬럼구성, 선언)
  컬럼정의,
  컬럼정의,
  
  컬럼명 자료형 제약사항,
  컬럼명 자료형(길이) 제약사항
);
 
 
create table memo
(
  -- 기본키(기본컬럼, 테이블의 식별자 역할 : 행과 행을 유일하게 구분할 수 있는 값을 가지는 컬럼)   **가장역할
  --  a. 테이블내에서 레코드 구분 식별자
  --  b. 중복값을 가질 수 없다. ND
  --  c. Null을 가질 수 없다. NN
  seq number primary key, --(NN,ND)
  
  memo varchar2(2000) not null, --필수값(NN)
  
  name nvarchar2(5) not null,
  
  regDate date not null
);
 
 
desc table memo;
 
 
 
 
-- 테이블 삭제(데이터 포함)
--create로 생성한 테이블은 drop으로 삭제한다.
drop table memo; 
 
 
 
 
-- ex)메모2
create table memo
(
  seq number primary key, --기본키
  memo varchar2(2000),
  
  --name nvarchar2(5) null, --null 허용 컬럼(비워둘 수 있다.) //익명허용
  -- default '익명' : 기본값**
  -- 컬럼의 기본값
  --  : 사용자가 값을 넣으면 그 값이 대입되고
  --    사용자가 값을 넣지 않으면 기본값이 대신 대입된다.
  name nvarchar2(5) default '익명' null,
  
  regDate date default sysdate not null --데이터가 추가되는 시간이 글쓴 시간
);
 
 
 
 
-- ex)메모3
create table memo
(
  seq number primary key,
  memo varchar2(2000) not null,
  name nvarchar2(5) null,
  regDate date default sysdate not null
);
 
-- 메모 추가
-- 자동 증가 번호 제공 객체(넘버링)
-- 시퀀스 객체 : 테이블에 종속적이진 않다.
create sequence memoSeq;
drop sequence memoSeq;
 
select memoSeq.nextVal from dual;
select 'm' || memoSeq.nextVal from dual;
 
-- 상품번호 ABFC1220.,
 
 
 
 
--실제로 만들어보기
create table memo
(
  seq number primary key,
  memo varchar2(2000) not null,
  name nvarchar2(5) not null,
  etc varchar2(1000) default '비고없음' null,
  regDate date default sysdate not null
);
 
 
-- 테이블에 데이터 추가하기
-- insert 문
--  : 레코드를 추가하기(행단위 입력)
--- insert into 테이블명 (컬럼리스트) values (값리스트);
 
insert into memo(seq, memo, name, etc, regDate) 
  values (1, '메모입니다', '홍길동', '테스트', '2015-01-09');
 
select * from memo; --확인
 
 
--primary key 컬럼
--에러]unique constraint (%s.%s) violated
insert into memo(seq, memo, name, etc, regDate) 
  values (1, '메모입니다', '홍길동', '테스트', '2015-01-09');
 
 
--컬럼 중 null을 허용, not null
insert into memo(seq, memo, name, regDate) 
  values (3, '메모입니다', '홍길동', '2015-01-09'); --암시적으로 null 입력
 
insert into memo(seq, memo, name, etc, regDate) 
  values (4, '메모입니다', '홍길동', 'null', '2015-01-09'); --명시적으로 null 입력
 
 
--에러]not null, cannot insert NULL into (%s)
insert into memo(seq, memo, etc, regDate) 
  values (5, '메모입니다', 'null', '2015-01-09'); --암시적
  
insert into memo(seq, memo, name, etc, regDate) 
  values (6, '메모입니다', null, 'null', '2015-01-09'); --명시적
 
 
select seq, to_char(regDate, 'YYYY-MM-DD-HH24-MI-SS') from memo;
--where ibsadate >과<의 경우 '2015-01-01' 특성 고려하기
 
 
-- 컬럼의 순서
-- 컬럼의 순서와 값의 순서는 반드시 같아야 한다.
 
 
 
 
 
-- default
insert into memo(seq, memo, name, etc) 
  values (9, '메모입니다', '홍길동', '테스트'); --암시적(생략해되는, 안정적),,, date를 지워도 뜬다
  
insert into memo(seq, memo, name, etc, regDate) 
  values (10, '메모입니다', '홍길동', '테스트', sysdate); --직접 대입
 
insert into memo(seq, memo, name, etc, regDate) 
  values (11, '메모입니다', '홍길동', '테스트', default); --명시적*** null과 비슷한 상황
 
 
 
 
--시퀀스 객체
select memoSeq.nextVal from dual;
select memoSeq.currVal from dual; --**
 
insert into memo(seq, memo, name, etc, regDate) 
  values (memoSeq.nexVal, '메모입니다', '홍길동', '테스트', default);
 
 
 
 
 
--insert 생략 표현
--  : 컬럼 리스트를 생략할 수 있다. (테이블에 가서 직접 확인)
--  : 가독성 낮음
--  : 값리스트의 순서는 테이블 원본 컬럼의 순서와 동일(***********)
--  : null값이나 default값을 생략할 수 없다.(***********)
insert into memo values (memoSeq.nextVal, '메모', '홍길동', '비고', sysdate); --원본
insert into memo values ('메모', memoSeq.nextVal, '홍길동', '비고', sysdate); --순서변경x
insert into memo values (memoSeq.nextVal, '메모', '홍길동', '비고'); --생략x
 
 
 
 
 
-- 데이터 수정, update
--  : 컬럽값(데이터)를 수정한다.
-- update 테이블명 set 컬럼명=값, 컬럼명=값, 컬럼명=값;
-- update 테이블명 set 컬럼명=값, 컬럼명=값, 컬럼명=값 where 절; ******조건을 찾아 바꿔야.,
 
update memo set name = '하하하'; --9개 행 이(가) 업데이트되었습니다. --문제?.. 모두 바꿔버린다.
update memo set memo = '메모입니다!';
update memo set memo = '메모입니다!!!!' where seq=1;--'1개' 행 이(가) 업데이트되었습니다.
 
--여러 컬럼값 수정
update memo set name= '호호호', etc = '임시' where seq=1;
 
--null값, default값 수정
update memo set etc=null, regDate=default where seq=1;
 
 
select * from tblCountry;
 
--기존 면적에서 +10씩..
update tblCountry set area += 10; --x
update tblCountry set arear = area+10; --o
 
 
 
 
 
-- 데이터 삭제, delete
--  : 레코드 삭제(행단위 삭제)
--  : 셀 삭제 -> 업데이트(null)
 
-- delete [from] 테이블명 [where절];
delete from memo where seq=6;
delete from memo where etc is null;
 
delete from memo; --모든 데이터 삭제
drop table memo; --데이터+테이블 삭제
 
select * from memo; --확인
rollback; --실행x
 
 
 
 
 
-- group by
--  : 특정 컬럽값을 대상으로.. 중복값이 발생하면.. 그 중복값을 기준으로 그룹을 형성
--  : 그룹 짓는 이유? -> 그룹별로 집계 함수를 적용하기 위해서(********)
--  : 그룹별로 통계값을 구하는 작업을 할 때 사용한다.(********)
--  : group by + 집계함수
--  : where절 group by절 order by절
--  : select 대상(집계함수 결과, group by 대상컬럼) - 그 이외의 단일컬럼들은 사용 불가능!!
 
 
select * from insa;
--부서별 평균 월급?
select avg(basicpay) from insa; --전직원 평균 월급(155만원)
select distinct buseo from insa; --부서찾기
select avg(basicpay) from insa where buseo='총무부'; --171
select avg(basicpay) from insa where buseo='개발부'; --138
select avg(basicpay) from insa where buseo='영업부'; --160
select avg(basicpay) from insa where buseo='기획부'; --185
select avg(basicpay) from insa where buseo='인사부'; --153
select avg(basicpay) from insa where buseo='자재부'; --141
select avg(basicpay) from insa where buseo='홍보부'; --145 번거로운 작업..
 
select buseo, trunc(avg(basicpay)) from insa group by buseo; --한방에!***
select buseo, trunc(avg(basicpay)), name from insa group by buseo; --집계함수와단일함수는 표현x
 
 
--지역별로 사는 직원수?
select city, count(*), trunc(avg(basicpay)), max(ibsadate), min(ibsadate) from insa
      group by city;
      
--대륙별 국가 몇개?
select cont, count(*) from tblCountry group by cont;
 
 
select * from insa;
 
 
 
-- having
--  ***********
-- select -> 조건 -> where절     : 단일 레코드 조건
-- group by -> 조건 -> having절  : 집합 함수의 결과에 대한 조건
 
-- 부서별 평균 월급
select buseo, avg(basicpay) from insa group by buseo;
 
-- 부서별 평균 월급이 150만원이 넘는 결과만 출력
select buseo, avg(basicpay) from insa group by buseo having avg(basicpay) >= 1500000; --**
-- 직원들 중 월급이 150만원이 넘는 직원들만을 대상으로 부서별 평균월급?
select buseo, avg(basicpay) from insa where basicpay >= 1500000 group by buseo;
 
-- 부서의 직원이 10명이 넘는 부서만 가져오기
select buseo, count(*) from insa group by buseo having count(*) >= 10;
 
select buseo, count(*) from insa group by buseo having city = '서울';
select buseo, count(*) from insa group by buseo having buseo > '다'; --해빙 응용 바꿔보기
 
 
 
 
 
-- 서브 쿼리, Sub Query, 하위 쿼리
--  : 쿼리 안에 또 다른 쿼리가 있는 형태
--  : select문 안에 selcet문
 
-- 인구수가 가장 많은 나라의 이름?
select * from tblCountry order by popu desc;
 
select name, max(popu) from tblCountry; --120660 x
select * from tblCountry where popu = 120660;
 
select name from tblCountry where popu = max(popu);
 
select name from tblCountry where popu = (select max(popu) from tblCountry); --***
 
 
-- 직원 중 월급이 가장 많은 직원명?
select max(basicpay) from insa; --2650000
select name, basicpay from insa where basicpay = 2650000;
 
select name, basicpay from insa where basicpay = (select max(basicpay) from insa); --***
select name, basicpay from insa where basicpay = (select min(basicpay) from insa);
select name, basicpay from insa where basicpay >= (select avg(basicpay) from insa);
 
 
 
-- 서브쿼리 사용시.,
-- 1. 서브쿼리의 결과값이 1개일때.,
-- 2. 서브쿼리의 결과값이 여러개일때.,
 
-- 수당을 20만원 이상받는 직원들이 소속된 부서의 직원명단을 출력?
select * from insa where sudang >= 200000;
select distinct buseo from insa where sudang >= 200000;
 
select * from insa where buseo in ('총무부', '개발부', '영업부', '기획부', '인사부'); --48
select * from insa where buseo in (select distinct buseo from insa where sudang >= 200000); --48**