DB - Basic

SQL 명령어

purge recyclebin;	휴지통 비우기
select * from recyclebin;	휴지통 정보 보기
flashback table 연산 to before drop;	휴지통 내용 복구
drop table 연산 purge;	완전 삭제 (휴지통을 거치지 않음)

 

---

 

테이블 생성

1. 형식

create table 테이블명(필드명 필드타입, 필드명 필드타입,...);

• 테이블명, 필드명 한글 가능

 

2. 예시

create table 연산(
x int, -- 고정형, int는 소수이하 자른다(반올림 된다)
y number, -- 가변형, number는 소수이하 놔둔다
z number(10,3));

select * from tab; -- 테이블 보기
desc 연산;

create table dbtest(
name varchar2(15), -- char(고정형), varchar2(가변형)
age number,
height number(10, 2),
logtime date);

Enterprise 한글 1자 = 2byte

Express  한글 1자 = 3byte

 

---

 

레코드 추가

1. 형식

insert into 테이블명(필드명,...) values(값, ...); 
insert into 테이블명 values(값,...); -- 테이블의 필드순서가 일치하면 필드명은 생략가능

 

 

2. 예시

insert into 연산(x, y, z) values(25, 36, 12.34567);
insert into 연산(x, y, z) values(25.34567, 36.34567, 12.34567);
insert into 연산(x, y) values(25.666, 36.88888); -- 25.666 (반올림 되서 26 나온다)
insert into 연산(z, y, x) values(1, 2, 3); -- 순서가 바뀌어도 된다

insert into 연산 values(25, 36, 12.34567);
-- 필드명 생략 가능하게 되면 필드를 빠짐없이 순서대로 입력해야한다
insert into 연산 values(25, 36, 1234567.3456); -- 유효숫자는 최대7자리
insert into 연산 values(25, 36, 12345678.3456); -- error

 

insert into dbtest(name, age, height, logtime) values('홍길동', 25, 185.567, sysdate);
insert into dbtest(name, age, height, logtime) values('Hong', 30, 175.56, sysdate);
insert into dbtest(name, age) values('희동이', 3);
insert into dbtest(name, height) values('홍당무', 168.89);
insert into dbtest values('분홍신', 5, 123.5, sysdate);
insert into dbtest(name) values('진분홍')

 

---

 

트랜잭션

1. 개념

• commit을 하기 전까지의 모든 명령어
• insert, update, delete에 lock이 걸려있다

 

2. 종류

• commit
  • 갱신
• rollback
  • 취소
  • commit한 이후의 시점으로 돌아간다
  • 현재 트랜잭션이 취소된다

 

---

 

레코드 추출

1. 형식

select 필드, 필드, … from 테이블명;
select * from 테이블명;
select * from 테이블명 ordery by 필드명 asc;
select * from 테이블명 ordery by 필드명 desc;
select * from 테이블명 ordery by 필드명 asc, 필드명 desc; -- 첫번째 필드에  똑같은 데이터가 존재하면 두번째 필드로 정렬한다
select * from 테이블명 ordery by 필드명 where 조건;

 

 

2. 예시

select name, age, height, logtime from dbtest;
select name, age from dbtest;
select * from dbtest; select * from dbtest where name='홍길동';
select * from dbtest where name like '홍%';
select * from dbtest where name like '_홍%';
select * from dbtest where name like '__홍%';
select * from dbtest where name='hong'; -- 데이터는 대소문자 가린다.
select * from dbtest where lower(name) = 'hong'; -- lower() 소문자
select * from dbtest where upper(name) = 'HONG'; -- upper() 대문자
select * from dbtest where name like '%동%' and age<20;
select * from dbtest where age is null;
select * from dbtest where age is not null;
select name, age+height from dbtest;
select name, age+height as "나이와 키의 합" from dbtest;
select * from dbtest order by name asc; -- asc는 생략가능
select * from dbtest order by name desc;

 

---

 

레코드 수정

1. 형식

update 테이블명 set 수정할내용 where 조건;

 

2. 예시

update dbtest set age=0 where name='홍당무';
update dbtest set age=0, height=0 where name='진분홍';
update dbtest set age=age+1;
update dbtest set age=age+1 where name='홍길동';

 

---

 

레코드 삭제

1. 형식

delete 테이블명; -- 모든 레코드 삭제
delete 테이블명 where 조건

 

---

 

테이블 삭제

1. 형식

drop table 테이블명;
drop table 테이블명 purge; -- 휴지통을 거치지 않고 바로 삭제 (영구삭제)

flashback table 테이블명 to before drop; -- 복원
purge recyclebin; -- 휴지통비우기
select * from recyclebin; -- 휴지통에 테이블 정보 검색
show recyclebin;

 

---

시퀀스 객체

1. 개념

• 순차적으로 정수값을 자동으로 생성하는 객체 (고유한 숫자를 자동으로 생성)
• 자동 증가하는 값 : 주로 기본 키(primary key)나 고유 식별자(unique identifier)로 사용되며, 새로운 레코드를 삽입할 때마다 자동으로 증가하는 숫자 제공
• 고유 번호 생성 : 고유한 번호나 코드를 생성하여 다른 테이블이나 시스템에서 사용할 수 있다

 

2. 형식

create sequence 시퀀스명
[increment by 증가값]
[start with 시작값]
[maxvalue 최대값 | minvalue 최소값]
[cycle | nocycle]
[cache | nocache]

	increment by 증가값 : 증가/감소 간격(기본값 : 1)
	tart with : 시작번호(기본값 : 1)
	maxvalue/minvalue : 시퀀스의 최대/최소값 지정
	cycle/nocycle : 최대/최소값에 도달 시 반복여부 결정
	cache/nocache : 지정한 수량 만큼 메모리 생성여부 결정 (최소값 : 2, 기본값 : 20)

 

3. 예시

create sequence test increment by 2 start with 1 maxvalue 9 cycle nocache;
select test.nextval from dual; -- dual 가상 테이블명
select test.currval from dual;
select * from user_sequences;
drop sequence test;

 

---

 

HW

// D:\Oracle\HW\select.txt

-- 1. 모든 학생의 정보를 검색해라 (STUDENT)
select * from student;

-- 2. 모든 과목의 정보를 검색해라 (COURSE)
select * from course;

-- 3. 기말고사 시험점수를 검색해라 (SCORE)
select result from score;

-- 4. 학생들의 학과와 학년을 검색해라  (STUDENT)
select major, syear from student;

-- 5. 각 과목의 이름과 학점을 검색해라 (COURSE)
select cname, st_num from course;

-- 6. 모든 교수의 직위를 검색해라 (PROFESSOR)
select orders from professor;

-- 7. 각 학생의 평점을 검색하라(별명을 사용) (STUDENT)
select sno as 학번, sname as 이름, avr 평점 from student;

-- 8. 각 과목의 학점수를 검색하라(별명을 사용) (COURSE)
select cname, st_num as 학점수 from course;
 
-- 9. 급여를 10%인상했을 때 연간 지급되는 급여를 검색하라(별명을 사용) (EMP)
select sal as 급여, sal*1.1 as "10% 인상" from emp;
 
-- 10. 현재 학생의 평균 평점은 4.0만점이다. 이를 4.5만점으로 환산해서 검색하라(별명을 사용) (STUDENT)
select avr as "4.0만점", avr * 1.125 AS "4.5만점" from student;

-- 11. 성적순으로 학생의 이름을 검색하라 (STUDENT)
select sname, avr from student order by avr desc ;
 
-- 12. 화학과 학생을 검색하라 (STUDENT)
select sname, major from student where major = '화학' ;

-- 13. 평점이 2.0 미만인 학생을 검색하라 (STUDENT)
select sname, avr from student where avr < 2.0;

-- 14. 관우 학생의 평점을 검색하라 (STUDENT)
select sname, avr from student where sname = '관우';
 
-- 15. 담당 교수가 없는 과목의 정보를 검색하라 (COURSE)
select * from course where pno is null;
 
-- 16. 화학과가 아닌 학생중에 1학년 학생을 검색하라 (STUDENT)
select * from student where major != '화학' and syear = 1;
 
-- 17. 물리학과 3학년 학생을 검색하라 (STUDENT)
select * from student;
 
-- 18. 교수가 지정되지 않은 과목중에 학점이 3학점인 과목을 검색하라 (COURSE)
select * from course where pno is null and st_num = 3;
 
-- 19. 화학과 관련된 과목중 평점이 2학점 이하인 과목을 검색하라 (COURSE)
select * from course where cname like '%화학' and st_num <= 2;
 
-- 20. 화학과 정교수를 검색하라 (PROFESSOR)
select * from PROFESSOR where orders = '정교수';
 
-- 21. 물리학과 학생중에 성이 사마씨인 학생을 검색하라 (STUDENT)
select * from STUDENT where sname like '사마%';
  
-- 22. 부임일이 1995년 이전인 정교수를 검색하라 (PROFESSOR)
select * from PROFESSOR where hiredate < DATE '1995-01-01';
  -- WHERE TO_CHAR(HIREDATE,'YYYY')<'1995' ORDER BY HIREDATE DESC;

-- 23. 성과 이름이 각각 1글자인 교수를 검색하라 (PROFESSOR)
select * from PROFESSOR where length(pname) = 2; -- where pname like '__';