DDL
DDL
테이블 정의, 변경, 삭제
계정 생성
생성자 생성 권한 - system 계정
conn system/비번(oracle);
create user 계정(jdbc) identified by 암호(jdbc)
grant resource, connect to jdbc;
connect jdbc/jdbc;
CREATE TABLE
: 테이블 정의
CREATE TABLE 테이블 이름 ( 컬럼명1 타입(길이) 제약조건,
컬럼명2 타입(길이) 제약조건,
컬럼명n 타입(길이) 제약조건);
- 이름 규칙 : 1) 숫자 시작 불가능 2) 오라클 키워드 불가능 3) 길이제한 있음 4) _ 가능
- 주석: –
예제) emp 테이블 생성하기
id 정수 5자리 | 사번 |
---|---|
name 문자열 20자리 | 이름 |
title 문자열 20자리 | 직급 |
dept_id 정수 5자리 | 부서코드 |
salary 실수 소수점 2자리 | 급여 |
create table emp (
id number(5),
name varchar2(20),
title varchar2(20),
dept_id number(5),
salary number(12,2)
);
number(12,2) : 전체 12자리, 따라서 정수 10자리 소수점 2자리
-
SUBQUERY
employees 테이블처럼 11개 컬럼, 이름, 타입, 자리 그대로 복사해서 테이블을 생성 + 데이터 복사
CREATE TABLE 테이블명 AS SELECT__FROM__;
- 다른 계정의 테이블 및 데이터를 복사해 올 경우, 해당 계정에서 접근 허가를 선언해야 함.
example) jdbc계정에서 hr계정의 정보를 가져온다면 hr 계정에서 grant select on employees to jdbc;
create table emp_copy as select*from hr.employees; : 전체 복사 create table emp_copy as select salary, first_name from hr.employees; : salar, first_name만 조회
-
SEQUENCE
자동으로 증가하는 숫자값이 시퀀스
-
시퀀스 생성
- create sequnece 시퀀스 이름
-
1부터 시작, 최대값까지 자동으로 증가
- 조건 - create sequnece 시퀀스 이름(start with 10, increment by 5 maxvalue 100);
-
10부터 시작, 5씩 증가, 최대 100까지 증가
-
시퀀스 활용
- 시퀀스명.currval
-
현재 값을 보여달라는 것
- 시퀀스명.nextval
-
증가한 값(다음 값)을 보여주는 것
확인 : dual 테이블
insert into emp values(emp_seq.nextval, 'DEF', 'Employee', 30, 455050);
-
시퀀스 수정 삭제
- alter sequence 시퀀스이름 start with 10
-
10부터 시작하는 것으로 수정
- drop sequence 시퀀스이름
-
삭제
-
시퀀스는 다른 테이블에 사용하면 안 된다.
시퀀스는 특정한 하나의 테이블에 중복되지 않으면서 하나씩 증가하는 값을 갖겠다는 뜻인데, 다른 테이블에 함께 사용하면 의도한 증가값을 만들어내지 못 함.
ALTER TABLE
: 테이블 변경(구조 변경)
ALTER TABLE 테이블이름 (ADD 컬럼명 타입(길이) 제약조건); : 테이블 추가
ALTER TABLE 테이블이름 (MODIFY 컬럼명 타입(길이) 제약조건); : 테이블 변경
ALTER TABLE 테이블이름 (DROP COLUMN 컬럼명); : 테이블 삭제
emp테이블에 입사일 저장 컬럼 추가
alter table emp add indate date;
emp테이블에 title 컬럼 길이 20>10자리 변경
alter table emp modify title varchar2(10);
emp테이블에서 입사일 컬럼 삭제
alter table emp drop column indate;
컬럼명 변경
alter table c_emp rename column 이전컬럼명 to 새컬럼명;
테이블명 변경
rename 이전테이블명 to 새테이블명;
- 변경하려는 데이터 사이즈에 이미 그 사이즈를 넘은 데이터가 저장되어 있다면 축소 불가
DROP TABLE
: 테이블 삭제
DROP TABEL 테이블이름;
drop table emp;
- 삭제 이후 복구 불가
DML
데이터 저장, 수정, 삭제
INSERT INTO 테이블명[(컬럼명)] VALUES( 데이터 )
emp에 데이터 저장
INSERT INTO emp VALUES(100, 'Michale', 'Employee', 100, 10.99000.5);
INSERT INTO emp VALUES(200, 'JENNY', 'Employee', 40.99000.5);
INSERT INTO emp VALUES(300, 'David', 'Employee', 30.99000.5);
INSERT INTO emp(id, name) VALUES(300, 'JENNY');
: 부분에 넣는 경우. 변수 수와 데이터타입을 반드시 일치시킨다.
-
INSERT 수행 후(임시적 insert상태) DB 영구 저장하겠다는 SQL 취소 SQL - 저장: COMMIT; (인서트 결과 반영)
- 취소: ROLLBACK; (인서트 결과 미반영)
INSERT도 subquery가 가능하다.
INSERT INTO 테이블명( ) SELECT___ FROM 조회 테이블
: 조회테이블로부터 select 한 것을 테이블명이 넣어라
INSERT INTO emp SELECT*FROM employees; 서로 다른 계정에 있는 것을 insert 할 때 (복사 할 때) 1) hr계정에서 허가: grant select on employees to jdbc; 2) 복사하기 insert into emp select employee_id, first_name, job_id, department_id, salary from hr.employees;
** 허가받고 가져올 때, 해당 계정.테이블명으로 명시 ex. hr.employees
TCL
Transaction Control Lang
Insert - > Commit or Insert -> RollBack 해야 끝
Update -> Commit or Update -> Commit 해야 끝
DDL - 자동 commit
DROP TABLE EMP;CREAT
ALTER
DROPDML - commit/rollback 결정
TRANSACTION 처리 언어<br />1. commit하지 않은 상태|rollback이면 다른 세션이 처리 결과 미반영
2. commit하면 다른 세션이 처리 결과 반영INSERT
UPDATE
DELETE
-
UPDATE(~WHERE)
update 테이블명 set 변경컬럼명 = 변경값 where 변경조건식;
update 테이블명 set 변경컬럼명 = 변경값 where 변경조건식; : 모든 행을 변경
update emp set title = 'Manager' where name = 'Egon'; : name이 'Egon'인 것의 title을 'Manager'로 변경
-
UPDATE에도 SUBQUERY가 가능하다
UPDATE 테이블명 SET 컬럼이름 = (subquery) WHERE 컬럼이름 연산자 (subquery);
update emp set dept_id = (select dept_id from emp where name = 'David' and rownum = 1)where name = 'Alley';
-
-
DELETE(~WHERE)
DELETE 테이블명 WHERE 삭제조건식 ; : 조건에 맞는 데이터행 삭제(테이블구조 ㅇ)
DELETE 테이블명; : 테이블 모든 데이터행 삭제 (테이블구조 ㅇ)
DROP TABLE 테이블명; : 테이블 모든 데이터행 삭제 + 테이블구조 삭제
예제) emp테이블에서 급여를 못 받는 사원의 급여를 1000으로 수정
update emp set salary = 1000 where name = 'DZAMA';
DCL
Data Control Language
사용자에게 어떤 권한을 부여하거나 빼앗을 때 사용하는 구문
- SYSTEM: data control lang
- REVOKE: 권한 뺏기 (revoke resource, connect from jdbc;)
- GRANT: 권한 부여 (grant resource, connect from jdbc;)
- DENY
제약조건(constraint)
테이블은 현실세계를 토대로 모델링을 해야하고, 따라서 데이터의 모순이 없어야 한다. 따라서 경우에 따라 제약조건을 활용할 수 있다.
- Unique: 중복되면 안 된다.
- Not null: null 값은 허용할 수 없다.
- Primary Key: 중복 x + null x
- Foreign Key: 다른 테이블을 포함한 값을 사용
- Check: 사용자 조건
예제)
테이블 1) c_dept
create table c_dept(dept_id number(5) constraint c_dept_id_pk primary key, dept_name varchar2(20) constraint c_dept_name_uk unique, city varchar2(20) constraint c_dept_city_nn not null);
테이블 2) c_emp
create table c_emp(emp_id number(5) constraint c_dept_id_pk primary key, emp_name varchar2(20) constraint c_emp_name_nn not null, title varchar2(20) constraint c_emp_title_ck check(title in('Employee', 'Exaggeration', 'Executives', 'Director', 'Exaggeration')), salary number(12,2) constraint c_emp_salary_ck check(salary>=1000), dept_id number(5) constraint c_emp_dept_id_fk references c_dept(dept_id));