2008년 1월 25일 금요일
09:00 ~10:00
다이어그램으로 노멀라이제이션 하는 것
10:00 ~12:00
sql + 명령문
show user
conn system/암호 => systems계정으로 DB에 접속
conn /as sysdba => sys계정으로 DB에 접속(DB시작, 종료 작업가능)
show user
select status from v&instance; => DB의 상태확인
-open (사용자들이 접속해서 작업가능한 시작 상태)
-mounted (mount상태로서 DB의 복구, 상태변경등이 가능한 상태)
-started (nomount상태로서 인스턴스만 시작된 상태)
select * from all_users; => 현재 DB에 접속가능한 계정정보 확인
@C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\scott.sql
conn scott/tiger
select * from tab; => scott계정 소유의 모든 테이블들의 이름확인
//테이블 생성
CREATE TABLE employee(
emp_num NUMBER(4) CONSTRAINT employee_emp_num_pk PRIMARY KEY,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
soc_sec_num VARCHAR2(10),
address VARCHAR2(30),
tel VARCHAR2(15),
hire_date DATE,
store_code NUMBER(2));
//오타 수정
ALTER TABLE employee RENAME COLUMN larst_name TO last_name;
ed로 클립보드를 열어서 / 로 실행
desc employee => employee 테이블의 구조 확인
set linesize 100 한줄에 나오는 사이즈 조절
INSERT INTO employee VALUES(1,'길동','홍','800101-1000000', '서초구 양재동','100-111',SYSDATE, 10); =>새로운 한행 삽입
col first_name format a5 //폭 조절
SELECT * FROM employee;
**employee 테이블이 생성된 물리적인 파일 확인
conn /as sysdba
select table_name, tablespace_name
from dba_tables
where owner='SCOTT';
select file_name
from dba_data_files
where tablespace_name='USERS';
13:00~
**sql
1)데이터 조희 : select
SELECT 컬럼명 /* distinct, ""
FROM 테이블명
WHERE 행조건절;
2) 객체(테이블등)
생성 : create
수정 : alter
삭제: drop, truncate
=>DDL(Data Definition Language)
3)데이터
입력: insert
수정: update
삭제: delete
=>DML(Data Manipulation Language)
4)트렌젝션 제어: commit, rollback, savepoint
=> TCL(transaction Control Language)
5)User에게 권한 부여 : Grant
권한 취소 : Revoke
=>DCL(Data Control Language)
//sql + 설정 파일
C:\oraclexe\app\oracle\product\10.2.0\server\sqlplus\admin\glogin.sql
***select 예제
select 컬럼명/컬럼명(+,-,*,/) 숫자등의 표현식/distinct 컬럼명/*/컬럼명 "별칭"/ 연결연산자(||)
from 테이블명;
where 컬럼표현식 연산자 값; =>행을 제한하는 조건절
연산자
1)비교연산자 : = , >, <, >= <=, !=, <>
2)SQL연산자
- between 값1 and 값2
- in (값1, 값2, 값3,...)
-
like '...%..._' : 문자열의 패턴
- %: 모든, _ : 반드시
- is null : 널값
문제
1번) select empno, ename, sal, job form emp;
2번) select empno, ename, sal+300 , job form emp;
3번) select ename, sal, comm, sal*12+nvl(comm,0) from emp;
4번) select ename "NAME", sal "SALARY" from emp;
5번) select ename "성 명", sal*12 "급 여" from emp;
6번) select ename||' is a ' ||job from emp;
7번) select ename||': 1 Year salary= ' ||(sal*12+nvl(comm,0)) from emp;
8번) select distinct job from emp;
9번) select distinct deptno from emp;
10번) select empno, ename, job, sal from emp where sal >= 3000;
11번) select empno, ename, job, sal, deptno from emp where job like 'MANAGER';
12번) select empno, ename, job, sal, hiredate, deptno from emp where hiredate >
1982/01/01';
13번) select ename, job, sal, deptno from emp where sal between 1300 and 1500;
14번) select empno, ename, job, sal, hiredate from emp where empno in (7902 ,7788,
566);
15번) select empno, ename, job, sal, hiredate, deptno from emp where hiredate like
82/%%/%%';
select empno, ename, job, sal, hiredate, deptno from emp where to_char(hiredate,
rrrr') = '1982';
16번) select empno, ename, job, sal, hiredate, deptno from emp where comm is null;
17번) select empno, ename, job, sal, hiredate, deptno from emp where sal >=1100 and
ob ='MANAGER';
18번) select empno, ename, job, sal, hiredate, deptno from emp where job != MANAGER' and job != 'CLERK' and job !='ANALYST';
select empno, ename, job, sal, hiredate, deptno from emp where job not in ('MANAGER', 'CLERK', 'ANALYST');
19번) select empno, ename, job, sal, hiredate, deptno from emp where (job = 'PRESIDENT' AND SAL > 1500) OR JOB ='SALESMAN';
20번) select * from emp where (ename like '%L%L%' ) and ( (deptno = 30) or ( mgr = 7782) );
21번) select empno, ename, job, sal, hiredate "입사일 ", deptno from emp order by "입사일" desc;
**함수 : 좀 더 강력한 질의문을 작성하기 위해서
1) 오라클 제공 내장 함수
-단일행 함수
1번)
select empno,ename,lower(job),deptno
from emp
where upper(ename) = 'SCOTT';
2번)
select initcap(dname), initcap(loc)
from dept;
3번)
select empno, ename, job, sal, deptno
from emp
where substr(ename, 1,1) > 'K' and substr(ename, 1,1) < 'Y' order by ename;
4번)
select empno, ename, length(ename), sal, length(sal)
from emp
where deptno = 20;
5번)
select mod(sal,30)
from emp;
6번)
select round((sysdate - hiredate)/7), round(mod(sysdate - hiredate, 7))
from emp order by 1 desc;
7번)
select round(months_between(sysdate, hiredate))
from emp
where deptno = 10;
8번)
select add_months(hiredate,5)
from emp
where deptno = 10;
9번)
select next_day(hiredate, '금')
from emp
where deptno = 10;
10번)
select last_day(hiredate) - hiredate
from emp;