SQL SQL 불펌하지 마세요!!!!!!!! 불펌 금지.

Presentation on theme: "SQL SQL 불펌하지 마세요!!!!!!!! 불펌 금지."— Presentation transcript:

1 SQL SQL 불펌하지 마세요!!!!!!!! 불펌 금지

2 /*DB의 계정 생성*/ create user scott identified by tiger default tablespace users temporary tablespace temp; grant connect, resource to scott; create user scott 생성 사용자 사용자ID identified by tiger 비밀번호 = tiger default tablespace users 데이터 저장 영역 할당 temporary tablespace temp; 작업 영역 할당 grant connect, resource to scott; 권한부여 DB들에 접속 권한 SQL명령 권한 을 scott에게 준다

3 sys system scott user temp DataBase(저장소) - XE 저장 한 데이터 작업을 마친 데이터
취소(Rollback)불가 작업중인 데이터 언제든지 취소 가능 DataBase(저장소) - XE commit DB의 모든테이블 사용가능 사용자 생성, 수정, 삭제 DB의 종료 시작 가능

4 테이블 - 데이터를 저장하는 그릇 Create table dept( 생성 테이블 테이블명 Deptno number(10), 컬럼명 저장할 데이터타입(최대자릿수) Dname varchar2(50), 컬럼명 저장할 데이터타입(최대바이트의 문자) Loc varchar2(50)); 컬럼명 저장할 데이터타입 (최대바이트의 문자) 테이블 생성 create table dept( deptno number(10), dname varchar2(50), loc varchar2(50)); 테이블에 데이터 삽입 insert into dept(deptno, dname, loc)values(10, ‘영업’ , ‘서울’); insert into dept(deptno, dname, loc)values(20, ‘기획’ , ’부산’); ** 문자열을 삽입할때는 ‘ ‘<<를 꼭 사용해야 한다.

5 Dept Table deptno dname loc varchar(50) 50바이트의 글자 ‘서울’ number(10) 10자리수의 숫자 10 varchar(50) 50바이트의 글자 ‘영업’

6 /* EMP테이블 생성하기 */ create table emp( empno number(10), empname varchar2(30), duty varchar2(30), mgr number(10), hire_date date, sal number(20), bonus number(20), deptno number(10)); insert into emp(empno, empname,duty, mgr,hire_date ,sal, bonus, deptno) values(280101,'홍길동','과장',990101,to_date('2008/01/01','yyyy/mm/dd'), , ,10);

7 EMPNO 사번 EMP NAME 이름 Duty 직책 Mgr 직속상사 Sal 월급 Bonus 보너스 280101 홍길동 과장
테이블의 제약 조건 - 테이블에 부적절한 데이터가 저장되는 것을 방지 하는것 PRIMARY KEY(기본키) - 레코드들을 구별하기 위한 값 - 유일한 값(UNIQUE) - 생략 불가(NOT NULL) EMPNO 사번 EMP NAME 이름 Duty 직책 Mgr 직속상사 hire_date 입사일 Sal 월급 Bonus 보너스 280101 홍길동 과장 990101 500 만원 100 EMP table

8 /* PRIMARY KEY 추가 */ alter table emp add constraint pk_emp primary key(empno); - PRIMARY KEY 제약 조건 추가 ALTER TABLE EMP 테이블구조 수정 테이블 이름 Add constraint pk_emp 추가 제약조건 제약조건 이름(임의) PRIMARY KEY (EMPNO) 제약조건종류 제약 설정되는 컬럼 이름 /* 레코드 추가 */ insert into emp(empno,empname) values(280102,’홍길순’); /* 사번이 홍길순과 같으므로 삽입 불가 */ values(280102,’장동건’); /* 사번이 생략되었으므로 삽입 불가 */ insert into emp(empname,duty) values(‘조인성’,’대리’);

9 /. 테이블을 만들면서 PRIMARY KEY 설정을 함
/* 테이블을 만들면서 PRIMARY KEY 설정을 함 */ create table emp( empno number(10) constraint pk_emp primary key, empname varchar2(50), duty varchar2(50), mgr number(10), hire_date date, sal number(20), bonus number(20), deptno number(10)); /* 테이블에 값넣기*/ insert into emp(empno,empname,duty,mgr,hire_date,sal,bonus,deptno) values(280303,‘장동건’,’과장’, ,to_date('2008/03/03','yyyy/mm/dd'), , ,10); /*사번은 중복 불가*/ insert into emp(empno,empname) values(280303,‘김태희’); /*사번은 생략 불가*/ insert into emp(empname,duty) values(‘조인성,’대리’); /*레코드를 데이터 베이스에 저장*/ commit;

10 Dept테이블에 존재하지 않는 부서번호는 삽입 불가
280101 홍길동 과장 990101 500 만원 200 10 empno Emp name duty mgr Hire_date sal bonus Dept no EMP table deptno dname 영업 loc 서울 dept table Dept 테이블의 부서 번호 Dept테이블에 존재하지 않는 부서번호는 삽입 불가

11 FOREIGN KEY(외래키) - 다른 테이블의 PRIMARY KEY를 참조해서 해당 PRIMARY KEY 컬럼에 존재하지 않는 데이터 삽입 불가 /* dept 테이블 생성 */ create table dept( deptno number(2) constraint pr_dept primary key, dname varchar2(14), loc varchar2(13)); /* 값 삽입하기 */ insert into dept values(10,’영업’,’서울’); insert into dept values(20,’경리’,’서울’); insert into dept values(30,’인사’,’부산’); insert into dept values(40,’마케팅’,’대구’);

12 /* EMP테이블에 FOREGN KEY 추가*/
ALTER TABLE emp 테이블 구조 변경 테이블명 ADD CONSTRAINT FK_DEPT 제약 조건 추가 제약조건이름 FOREIGN KEY (detpno) 외래키 외래키설정 컬럼 REFERENCES dept(deptno); 참조 테이블 컬럼 /* emp 테이블에 데이터 삽입 */ insert into emp(empno, empname,deptno) values (280304,’김태희’,10); /* dept 테이블에 없는 deptno이므로 삽입 불가 */ values (280304,’이영애’,100); /* deptno 생략 가능*/ insert into emp(empno, empname) values (280304,’김희선’);

13 /* 테이블 생성 pk: empno, fk: deptno*/
Create table emp( Empno number(10) constraint pk_emp primary key, Empname varchar2(50), Duty varchar2(50), Mgr number(10), Hire_date date, Sal number(20), Bonus number(20), Deptno number(10) constraint fk_deptno references dept); /* emp 테이블에 데이터 삽입 */ Insert into (empno,empname,deptno) Values(280304,’김태희’,10); /*테이블에 설정된 제약 조건 검색*/ Select * from user_constraints Where table_name = 'EMP'OR table_name='emp'; /* 제약조건 삭제*/ Alter table emp drop constraint pk_emp; 테이블 구조변경 [테이블명] 삭제 제약조건 [제약조건명]

14 /*다른 테이블에서 참조하고 있지만 삭제하고 싶을 경우*/
drop table dept cascade constraint; - UNIQUE - 유일한 값(중복불가) - 생략 가능 /* c1명령이 primary key 이고 c3컬럼이 unique인 테이블생성 */ create table tb_uniq_test( c1 number(10) primary key, c2 number(10), c3 number(10) unique); /* 데이터 삽입*/ insert into tb_uniq_test(c1,c2,c3) values (1,1,1); insert into tb_uniq_test(c1,c2,c3) values (2,1,2); /* c3 컬럼에 중복된 값 삽입 불가*/ insert into tb_uniq_test(c1,c2,c3) values (3,2,2); /* c3 컬럼값 생략 가능 */ insert into tb_uniq_test(c1,c2) values (4,4); /* 테이블에 제약 조건 확인*/ (제약조건명을 선택하지 않으면 임의로 생성됨) select * from user_constraints where table_name='tb_uniq_test' or table_name= 'TB_UNIQ_TEST'

15 alter table tb_uniq_test drop constraint sys_c004041;
/* 유니크 제약 조건 삭제 */ alter table tb_uniq_test drop constraint sys_c004041; 테이블 구조변경 [테이블명] 삭제 제약조건 [제약조건명] /* 유니크 제약 조건 삽입*/ alter table tb_uniq_test add constraint nui_c unique(c3); 테이블 구조변경 [테이블명] 삽입 제약조건 [제약조건명] [제약조건] /* 테이블 제약 조건 넣어 생성하기 */ create table tb_uniq_test( c1 number(10) constraint pk_c1 primary key, c2 number(10), c3 number(10) constraint uni_c3 unique); c1 Sys_c004040 Pk_c1(재생성) c2 c3 Nui_c3

16 - NOT NULL - 생략 불가 /* tb_not_null_test 테이블 생성 */ Create table tb_not_null_test( n1 number(10) constraint pk_n1 primary key, n2 number(10), n3 number(10) constraint nn_n3 not null); /* 테이블에 데이터 삽입 */ Insert into tb_not_null_test(n1,n2,n3)values(1,1,1); /* n3컬럼은 중복 가능*/ Insert into tb_not_null_test(n1,n2,n3)values(2,1,1); /* n3컬럼은 생략 불가 */ Insert into tb_not_null_test(n1,n2)values(4,4); tb_not_null_test n1 Primary key (pk_n1) Not null (nn_n3) n3 n2

17 c1 Pk_check_c1 c2 c3 10이상 100이하 삽입 기능 tb_check_test 10이상 100이하 옵션
/* n3컬럼의 not null 설정 삭제 */ Alter table tb_not_null_test Drop constraint nn_n3; /* n3컬럼의 not null 설정 추가 */ Alter table tb_not_null_test modify(n3 number(10) constraint nn_n3 not null); - CHECK - 컬럼의 값을 특정 범위로 제약 /* 테이블 : tb_check_test 생성 */ 컬럼 : c1 pk 컬럼 : c3 10이상 100이하의 데이터만 삽입 가능 Create table tb_check_test( c1 number(10) constraint pk_check_c1 primary key, c2 number(10), c3 number(10) constraint check_c3 check(c3>=10 and c3<=100)); c1 Pk_check_c1 c2 c3 10이상 100이하 삽입 기능 tb_check_test 10이상 100이하 옵션

18 /* 데이터 삽입 */ Insert into tb_check_test(c1,c2,c3) Values(200,3000,20); /* c3 컬럼은 10이하 100이상의 조건에 위배*/ Insert into tb_check_test(c1,c2,c3) Values(200,3000,101); /* check 제약 조건 삭제*/ Alter table tb_check_test Drop constraint check_c3; /* 제약 조건 추가 n3컬럼에 10이상 100이하*/ Alter table tb_check_test Add constraint check_c3 Check(c3>=10 and c3<=100); /* 데이터 추가 */ Insert into tb_check_test(c1,c2,c3) Values(202,3000,100); /* 10미안이므로 삽입 불가*/ Insert into tb_check_test(c1,c2,c3) Values(202,3000,9); /*tb_check_test의 레코드 삭제 */ Delete from tb_check_test;

19 /*c3에 100,200,300,400,500 중 하나만 삽입 가능*/ Alter table tb_check_test Add constraint check_c3 Check(c3 in(100,200,300,400,500)); /* 컬럼에 데이터 삽입*/ Insert into tb_check_test(c1,c3) Values(1000,100); Insert into tb_check_test(c1,c3) Values(1001,200); /*101은 100,200,300,400,500 중 하나가 아니므로 삽입 불가*/ Insert into tb_check_test(c1,c3) Values(1002,101); - DEFAULT - 삽입시 삽입한 데이터가 저장 - 생략시 DEFAULT로 설정한 데이터가 삽입 d1 pk date d2 d3 DATE-날짜와시간 - 삽입한 날짜와 시간 - 생략시 해제 날짜와 시간 저장 tb_default_test

20 /* 테이블 생성*/ d1 : pk d3 : date 타입 생략시 현재 날짜와 시간이 저장 Create table tb_default_test( d1 number(10) primary key, d2 date, d3 date constraint d3_df default sysdate)); /* 데이터 삽입 */ Insert into tb_default_test(d1,d2,d3) values(1,to_date('16:27:00','hh24:mi:ss'),to_date('2008/12/23','yyyy/mm/dd')); Commit; /* 삽입된 데이터값 확인*/ select d1,to_char(d2,'hh24-mi-ss'),to_char(d3,'yyyy-mm-dd') from tb_default_test; /* d2와 d3를 생략하여도 d3에 date가 저장*/ insert into tb_default_test(d1) values(2);

21 /*default 제약 조건 삭제*/ alter table tb_default_test modify (d3 date default null); /* d3의 제약조건이 삭제되고 넣으면 d3은 null*/ insert into tb_default_test (d1) values(20); 오라클의 데이터 타입 1. 숫자타입 number(p, s) p : 전체 유효 자리 숫자의 자릿수 (p : 1 ~ 38) s : 소수점 아래 유효 자릿수 ( s : -84 ~ 127) (의미있는 데이터) n2 n1 n3 Number (5) - 전체5자리 - 소수점 아래 유효자릿수 : 0 - 정수 number (5, 2) 유효자릿수 : 2 - 소수점 위 유효자릿수 : 3 (5, -2) 전체5자리 소수점위 2자리부터 유효자릿수가 시작 소수점 위 2자리부터 5개의 유효자리 숫자 존재 tb_number_test

22 /* 테이블 생성 */ create table tb_number_test( n1 number(5), n2 number(5,2), n3 number(5,-2)); /* 데이터 삽입 */ insert into tb_number_test(n1,n2,n3) values(12345, ,12345); - n1 => , n2 => 123,46, n3 => 이 저장된다 insert into tb_number_test(n1,n2,n3) values(23456,234.56, ); - n1 => 23456, n2 => , n3 => 이 저장된다 /* n2가 범위를 벗어났으므로 삽입 불가 */ insert into tb_number_test(n2) values( ); /* n3가 범위를 벗어났으므로 삽입 불가 */ insert into tb_number_test(n3) values( ); insert into tb_number_test(n1) values( ); - n1 => 12346이 저장된다

23 문자 데이터 타입 (n : 최대 byte) 1. VARCHAR2(n) : 가변 문자 타입 2. CHAR(n) : 고정 문자 타입 /* 테이블 생성 */ create table tb_string_test( s1 char(20), s2 varchar2(20)); /* 데이터 삽입 */ insert into tb_string_test(s1,s2) values('hi','hi'); insert into tb_string_test(s1,s2) values('good','good'); /* 최대 20 byte 초과이므로 삽입 불가 */ insert into tb_string_test(s1,s2) values('good good good after noon‘,'good good good after noon'); s1 s2 CHAR(20) 최대 20 byte - 고정 문자열 VARCHAR2(20) 가변 문자열 tb_String_test Hi%%%%%%%%%... (공백으로 나머지를 채워서 20 byte를 만든다) Hi (글자수만큼 사이즈가 조정됨)

24 날짜와 시간 DATE : 날짜와 시간 7byte /*테이블 생성 */ create table tb_date_test(d1 date); /* 현재 날짜와 시간저장 */ insert into tb_date_test(d1) values (sysdate); /* 저장 */ values(to_date(' ','yyyy-mm-dd')); /* 2008/12/24 10시 38분 49초 저장 */ values(to_date('2008/12/24 10:38:49', 'yyyy/mm/dd hh24:mi:ss')); DATE 날짜와 시간 저장 d1 tb_date_test

25 테이블 컬럼 관리 ALTER TABLE [테이블명] ADD : 컬럼 추가 MODIFY : 컬럼 수정 DROP : 컬럼 삭제 /* emp 테이블에 addr 컬럼 추가 */ alter table emp add(addr varchar2(50)); /* emp테이블 empname 컬럼의 타입을 varchar2(80)으로 수정 */  컬럼에 저장된 데이터가 없으면 컬럼의 타입을 자유롭게 변경가능  컬럼에 저장된 데이터가 있으면 컬럼의 사이즈를 크게 하는 변경만 가능 이때 새로운 컬럼의 사이즈는 저장된 데이터의 크기보다 커야함 alter table emp modify(empname varchar2(80)); /* 컬럼사이즈를 다시 50으로 변경 */ alter table emp modify(empname varchar2(50)); /* ADDR 컬럼 삭제 */ alter table emp drop column addr;

26 테이블의 복사 - 테이블의 구조 복사 - 레코드 복사 - NOT NULL 제외한 제약 조건은 복사가 안됨 CREATE TABLE [테이블 명] AS 복사할 레코드 쿼리; 예제) Create table emp2 As 새로 만들 테이블 emp2 Select * from emp; 복사할 레코드 테이블 구조 /* emp테이블의 모든 레코드를 emp2로 복사*/ create table emp2 as select * from emp;

27 /* 2000년 1월 1일 이후에 입사한 사원의 레코드를 emp3에 복사 */
create table emp3 as select * from emp where hire_date>=to_date('2000/01/01','yyyy/mm/dd'); /* emp 테이블의 empname컬럼을 not null로 변환 */ alter table emp modify(empname varchar2(20) not null); /* emp 테이블에서 월급이 2000이상 3000이하인 사원을 emp5로 복사 */ create table emp5 where(sal>=2000 and sal<=3000); /* emp테이블 월급이 3000이상인 사원의 사번, 이름, 월급, 입사일을 복사 */ create table emp6 select empno,empname, sal,hire_date from emp where (sal>=3000);

28 전체컬럼 테이블에 정의된 컬럼 순서대로 삽입시 생략 가능
데이터(Record)의 삽입 - insert 쿼리 - 형식 insert into 테이블 이름 (컬럼1, 컬럼2, 컬럼3……)  values(컬럼1, 컬럼2, 컬럼3…..) 전체컬럼 테이블에 정의된 컬럼 순서대로 삽입시 생략 가능 테이블의 구조 컬럼 순서가 자주 바뀌므로 써주는것이 좋다 /* emp테이블 모든 컬럼에 데이터 삽입 */ insert into emp values(1700,’정지훈’,’이사’,1644,sysdate,3000,500,10); /* dept 테이블에 deptno, dname 2개의 데이터만 삽입*/ insert into dept(deptno,dname) values(50,’회계’); /* emp#테이블에 직책이 과장인 사원을 추가 */ Insert into emp3 select * from emp where duty=‘과장’;

29 데이터(레코드)의 수정 - update 쿼리 - 형식 update 테이블명 set 컬럼1=새로운값, 컬럼2=새로운값….. where 수정할 레코드의 조건 수정 조건과 일치하는 레코드 값 모든 레코드를 수정시 에는 조건 생략 /* 사번이 16359인 사원의 부서번호를 30으로 수정*/ Update emp Set deptno=30 Where empno=16359; /* 부서가 20인 사원을 검색*/ Select * from emp where deptno=20; /*부서가 20인 사원의 월급을 20%인상 */ update emp set sal=sal*1.2 where deptno=20; /* 모든사원의 입사일을 오늘로 수정 */ update emp set hire_date=sysdate

30 데이터(레코드)를 삭제 - DELETE 쿼리 - 형식 DELETE from 테이블명 where 지우고 싶은 레코드에 관한 조건  해당조건에 맞는 레코드 삭제 /* 사번이 16359인 사원 삭제*/ delete from emp where empno=16359 /* 월급의 평균을 구하고 월급이 평균 이하인 사원 삭제 */ select avg(sal) from emp  월급의 평균구하기 delete from emp where sal<=(select avg(sal) from emp) /* 모든 사원 삭제 */ delete from emp

31 레코드의 검색 - select 쿼리 - 형식 select 컬럼1, 컬럼2……(검색하고 싶은 컬럼들) from 테이블명 where 검색 조건 검색조건에 일치하는 레코드 검색 생략시 모든 레코드 /* deptno가 10인 사원의 모든 컬럼을 검색 */ Select * from emp Where deptno=10; /* detpno가 10인 사원의 사번과 이름 검색 */ Select empno, empname from emp Where deptno =10; /* detpno가 10인 사원의 사번과 이름을 검색 empno -> 사번, empname-> 이름 */ Select empno 사번, empname 이름 from emp

32 WHERE 절의 연산자 = : 같다 >=, >, < , <= , not, and, or In : 열거된 값 중 하나와 일치 Not in : 열거된 값 와 어떤 것과도 일치하면 안된다 /* 사번이 16359, 인 사원 */ select empno, empname,duty,hire_date from emp where empno in(16359, 16900); /* 사번이 16359, 이 아닌 사원 */ where empno not in(16359, 16900);

33 BETWEEN 연산자 - BETWEEN 2000 and 3500 2000 이상 3500이하 LIKE 연산자 - 문자열의 포함여부를 비교 - % : 0 이상의 문자열 - _ : 1개의 문자열 /* 월급이 2000이상 3500 이하인 사원 검색 */ select * from emp where sal between 2000 and 3000; /* LIKE를 사용한 조건 검색 */ select * from emp where empname like '%이%'; where empname like ‘이%'; where empname like ‘%이'; where empname like '_이_'; where empname like ‘이__'; where empname like '__이';