07월 18일 목 | SQL활용 01 - SQL Developer 기본사용

기본 SQL 작성하기

SQL 문

- Stored Query Language

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

- SQL 명령어는 대소문자를 구분하지 않는다.

- 주석은 --

 

 

SQL명령어

1. DDL명령어 Data Definition Language 정의어

DDL(Data Definition Language)은 `데이터를 정의하는 언어'로서, 보다 엄밀하게 말하면 `데이터를 담는 그릇을 정의하는 언어'이며, 이러한 그릇을 DBMS에서는 오브젝트라고 한다. DDL을 통해 정의할 수 있는 대상, 오브젝트 유형은 다음과 같다.

   - DB및 TABLE에 대한 정의
   - Create 생성
   - Drop 삭제
   - Alter 수정

 

2. DML명령어 Data Manipulation Language 조작어

   - 레코드작업
   - select 조회 및 검색
   - insert 삽입
   - update 수정
   - delete 삭제

 

3. DCL명령어 Data Control Language 제어어

   - 사용자가 별로 쓸일 없음. 호스팅업체가 주로 사용
   - grant 사용자 접근 권한부여
   - revoke 사용자 접근 권한 취소
   - deny 특정사용자만 접근차단

 

 

SQL Developer 사용하기

 

1. 오라클 홈페이지에서 SQL Developer 다운 받기

https://www.oracle.com/index.html

https://www.oracle.com/database/technologies/appdev/sql-developer.html

 

2. 경로설정

설치시 경로설정을 아래와 같이 해준다.

C:\Program Files\Java\jdk1.8.0_211

 

3. Oracle 접속하기

플러스 버튼을 눌러서 새로 설정해준다.

 

작업이름, 

사용자이름: java0514

비밀번호: 1234 (비밀번호 저장 체크)

등을 설정하고 테스트 후 접속.

 

 

4. 새로만들기

 

새로만들기 -> 데이터베이스 파일 -> SQL 파일

 

자동커밋하는 법

도구 -> 환경설정 -> 데이터베이트 -> 객체뷰어

 

실행단축키

ctrl + enter

스크립트실행

F5

 

5. 테이블 생성하고 데이터 insert하기

1) 테이블생성

 

2) 데이터 insert

 

3) 생성된 테이블 조회

 

6. 테이블 구조확인 및 삭제

1) 테이블 구조확인은

desc table t_emp;

 

2) 테이블 삭제

drop table t_emp;

 

7. 테이블 재생성

1) 테이블 재생성

이런식으로 드래그한 후 ctrl + enter 해주어도 된다.

 

 

2) 테이블명 변경

rename t_emp to s_emp;  --변경 select * from tab;

변경되었다.

 

 

 

8. 테이블에 칼럼추가

 

1) 테이블 칼럼추가하기

hire_date라는 date형 칼럼을 추가할 것이다.

alter table s_emp add(hire_date date);

hire_date라는 칼럼이 추가되었다.

 

 

 

2) null값인 칼럼에 데이터 추가하기.

date형에 날짜 데이터를 넣을 때는 String형으로 넣으면 된다.

'2019-07-18'

 

 

2) 에러메시지 확인하기

일부러 전화번호의 값을 범위 이상으로 늘린 값을 넣어보려했다.

그랬더니 콘솔에

오류 보고 -
ORA-12899: value too large for column "JAVA0514"."S_EMP"."PHONE" (actual: 25, maximum: 15)

이런 메시지가 떴음.

앞으로 오류가 생기면 이런 메시지를 참고하여 오류를 수정하면 된다.

 

 

 

3) 컬럼의 자료형의 범위 변경하기

alter table s_emp modify (phone varchar2(20));

이렇게 phone필드의 varchar2를 (15)에서 (20)으로 변경하였다.

 

 

 

4) 컬럼 아이디 변경하기

alter table s_emp rename column id to t_id;

 

5) 컬럼 삭제하기

dept_name이란 컬럼을 삭제해보았다.

alter table s_emp drop column dept_name;

 

 

 

 

SQL Update 연습문제

 

연습문제 1) t_id가 100인 레코드의 입사일을 오늘 날짜로 수정

update s_emp set hire_date=sysdate where t_id=100;

연습문제 2) t_id가 101인 레코드를 아래 데이터로 수정
      입사일 2019-01-01, 이름 박인비

update s_emp set hire_date='2019-01-01' where t_id=101; update s_emp set name='박인비' where t_id=101;

 

이렇게 해도 된다.

update s_emp set hire_date='2019-01-01',name='박인비' where t_id=101;

 

연습문제 3) t_id가 102인 레코드를 아래 데이터로 수정
      입사일 -> 2019-03-03, 급여 급여*1.1

update s_emp set hire_date='2019-03-03' where t_id=102; update s_emp set salary=salary*1.1 where t_id=102;

역시 이렇게 하나로 묶어도 된다. 

update s_emp set hire_date='2019-03-03',salary=salary*1.1  where t_id=102;

 

결과값:

 

연습문제 4) t_id가 400인 레코드를 삭제하시오.

이렇게 5번째에 새로운 레코드가 생성되었다. 이것을 삭제하시오.

delete from s_emp where t_id=400;

 

결과값:

 

 

SQL Alter 연습문제

 

s_emp 테이블에 대하여

1) email을 관리하기 위한 mailid컬럼을 10byte로 추가하시오.

alter table s_emp add(mailid varchar2(10));

맨 오른쪽에 mailid라는 컬럼이 생성되었다.

 

 

2) mailid컬럼을 20byte로 수정하시오.

alter table s_emp modify (mailid varchar2(20));

20byte로 수정되었다.

 

 

3) mailid컬럼명을 e_mail로 수정하시오.

alter table s_emp rename column mailid to e_mail;

 

수정된 것을 확인할 수 있다.

 

 

 

4) s_emp 테이블명을 t_emp로 변경하시오.

rename s_emp to t_emp;

테이블명이 변경되었다.

 

 

 

제약조건이란?

1. 정의

테이블의 해당 컬럼에 원치않는 데이터가 입력,변경,삭제되는 것을 방지하기 위해

테이블 생성 또는 변경시 설정하는 조건

 

2. 종류

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

예) 주민번호 -> pk

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

 

 

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

 

1) 제약조건 설정하기

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 --데이터 입력하지 않았을 때 0     ,phone       VARCHAR2(15) constraint  c_emp_phone_ck check(phone like '1234-%')     ,dept_id     NUMBER(7)    constraint  c_emp_dept_id_fk references dept(deptno) );

 

2) 제약조건 이름검색하기

제약조건 목록확인 (데이터사전)

select * from user_constraints;

from user_constraints; 

from 뒤의 영역 테이블이라고 할 수 있다. 

from user_constraints where table_name='DEPT'; 

from user_constraints where table_name='C_EMP'; 

 

3) 제약조건은 수정할 수 없고 삭제만 가능하다.

alter table c_emp drop constraint c_emp_name_nn;

 

4) 제약조건 추가하기

alter table c_emp add constraint c_emp_name_un unique(name)

 

-- not null 제약조건은 add로 할 수 없고 modify로 가능

alter table c_emp modify (name varchar(25) constraint c_emp_name_nn not null);

 

새 테이블 생성 후 

1) id 컬럼에 pk 제약조건 추가

alter table 테이블명 add constraint 제약조건이름 제약조건종류(필드명)

alter table c_emp add constraint c_emp_id_pk primary key(id);

이 조건을 붙인 다음 데이터가 하나만 들어가진다.