2008년 1월 25일

|

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;



And