7월18일 - SQL활용 1

[참고교재]

학습모듈 - SQL활용 

컴원미디어 -CHAP4 데이터타입과 데이터 무결성

                CHAP5 테이블 설계

SQL문

 

 - Stored Query Language

 - 어떻게 작업을 수행할 지가 아니라 무엇을 해야할 지에 관해 정의하는 결과중심적 언어. 절차적은 아님.

    cf)자바는 절차적언어
 - SQL 명령어는 대소문자를 구분하지 않는다
 - 주석 --

1. DCL명령어 Data Control Language 제어어
   - 사용자가 별로 쓸일 없음. 호스팅업체가 주로 사용
   - grant 사용자 접근 권한부여
   - revoke 사용자 접근 권한 취소
   - deny 특정사용자만 접근차단

2. DDL명령어 Data Definition Language 정의어
   - DB및 TABLE에 대한 정의
   - Create 생성
   - Drop 삭제
   - Alter 수정

3. DML명령어 Data Manipulation Language 조작어
   - 레코드작업
   - Select 조회 및 검색
   - insert 삽입
   - update 수정
   - delete 삭제

 

 

Oracle SQL Developer 툴 설치

 

1. 다운로드 - Windows 64-bit with JDK 8 included

https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

 

2. 압축풀고 database 폴더 만들어주기

 

3. sql developer 설치하기

  - 파일 .exe 설치 후, oracle접속

  - name        : myOracle11g

    사용자이름 : java0514

    비밀번호    : 1234    ->비밀번호 저장 체크

    호스트이름 : localhost

    포트          : 1521

    SID           : xe 

  - 테스트 후, 접속하기

 

SQL DCL 제어어

 - grant, revoke, deny

 

 

SQL DDL 정의어

 - create, drop, alter

 

 

SQL DML 조작어

 - select, insert, update, delete

 

 

1. 테이블 생성

 

--테이블 생성
create table t_emp(
    id         number(5) not null --사번
    ,name      varchar2(25)       --이름
    ,salary    number(7,2)        --급여 (,2) 소숫점 두자리
    ,phone     varchar2(15)       --전화번호
    ,dept_name varchar2(25)       --부서명
);

 

2. 테이블명 변경

 

--테이블명 변경
rename t_emp to s_emp;
select * from tab;

 

3. 행 추가

 

--행추가
insert into s_emp values(100,'손흥민',2000,'010-222-2222','개발부');
insert into s_emp values(101,'김연아',3000,'010-333-3333','총무부');
insert into s_emp values(102,'이승우',4000,'010-444-4444','영업부');

 

4. 테이블에 칼럼추가

  - 양식 : alter table 변경할 테이블 add(필드명 자료형);

 

alter table s_emp add(hire_date date);

 

5. 행 추가

 

insert into s_emp
values(103,'무궁화',5000,'010-555-5555','영업부','2019-07-18');

 

SQL문의 제약조건

해당칼럼에 쓰레기값이 들어오는 것을 방지하기위해 강제로 주는 조건

primary key - 본인 notnull 절대허용안함(주민번호 )
foreign key - 부모자식관계

unique - notnull 한번만 허용(핸드폰, 이메일주소 등)

 

1) 정의

-> 테이블의 해당 칼럼에 원치않는 데이터가 입력/ 변결/ 삭제되는 것을 방지하기 위해 테이블 생성 또는 변경시 설정하는 조건

 

2) 종류

primary key	기본키, 유일성. null값을 인정하지 않음   -> 테이블당 1개만 가능
foreign key	외래키, 자식테이블이 부모테이블 칼럼을 참조   -> references 부모테이블(칼럼명)
unique	중복을 허용하지 않음. null값을 1번만 허용
check	특정 데이터만 입력가능
not null	빈값을 허용하지 않음

 

예) 주민번호 -> pk

     이메일, 핸드폰번호 -> unique

 

3) 제약조건을 반영한 테이블 생성하기

 

부서테이블 생성
drop table dept;
create table dept(
  dept number(7) primary key
  ,dname varchar(20)
  ,loc varchar(10)
);

 

insert into dept values(10,'경리팀','부산');
insert into dept values(20,'연구팀','대전');
insert into dept values(30,'총무팀','대구');
insert into dept values(40,'전산팀','인천');

 

제약조건을 반영한 테이블 생성하기

create table c_emp(
  id number(5) constraint c_emp_id_pk primary key
  ,name varchar2(25) constraint c_emp_name_nn not null
  ,salary number(7,2) default 0
  ,phone varchar(15) constraint c_emp_phone check(phone like '1234-%')
  ,dept_id number(7) constraint c_emp_dept_id_fk
);

 

4) 제약조건 이름 검색하기
- 제약조건 목록 확인(데이터사전)
select * from user_constraints;
select * from user_constraints where table_name='DEPT;
select * from user_constraints where table_name='C_EMP';

 

5) 제약조건은 수정할 수 없고 삭제만 가능함
alter table c_emp drop constraint c_emp_name_nn;

6) 제약조건 추가하기 
alter table c_emp add constraint c_emp_name_un unique(name);

--not null 제약조건은 add로 할 수 없고 modify로 가능 
alter table c_emp modify(name varchar2(25) constraint c_emp_name_nn not null);

 

 

---------------------------------------------------------------------------------
# 제약조건이 설정되지 않은 경우 #
---------------------------------------------------------------------------------

1) 제약조건이 설정되지 않은 테이블 생성하기 
drop table c_emp;
create table c_emp(
 id number(5)
 ,name varchar2(25)
 ,salary number(7,2)
 ,phone varchar2(15)
 ,dept_id number(7)
);


insert into c_emp(id,name) values(1,'코코넛');
insert into c_emp(id,name) values(1,'바나나');
insert into c_emp(id,name) values(1,'복숭아');

delete from c_emp;

 

---------------------------------------------------------------------------------
# 제약조건이 설정 된 경우 #
---------------------------------------------------------------------------------

1)id칼럼에 pk제약조건 추가
--alter table 테이블이름 add constraint 제약조건이름 제약조건종류(필드명)
alter table c_emp add constraint c_emp_id_pk primary key(id);

 

pk제약조건으로 insert해줄경우 하나만 입력가능하므로 , 오류발생한다.