select 컬럼명
from 테이블명;
select *
from tab; <-- 모든 테이블
set linesize 100 <-가로길이 압축
set pagesize 50 <-페이지를 늘려줌(항목이 다시 나오는것을 막아줌)
select *
from emp;
empno ename job mgr hiredate sal comm deptno
select *
from dept;
deptno dname loc
select empno, ename from emp;
desc emp <- ; 없음 , 항목보기
select empno, ename, sal, job from emp;
select ename, sal, sal+500 from emp;
select (256+34)*6-54/9 from emp; <- 답이 14번 나옴
select (256+34)*6-54/9 from dual; <- 답이 1번 나옴, 가상 테이블 답
select (256+34)*6-54/9 결과 from dual; <- 결과=alias(별칭)
select empno 사원번호, ename 사원명 from emp;
select empno 사원 번호, ename 사원명 from emp;
ora-00923: from 키워드가 있어야할 곳에 없습니다.
select empno "사원 번호", ename 사원명 from emp; <- 공백이나 따옴표를 제외한 특수문자사용시
** 대개의 경우 ' 를 사용, " 는 2가지 경우만..
select empno as 사원번호, ename as 사원명 from emp;
ex4:
select empno, ename, sal, comm, sal+comm/100 from emp;
ex5:
select ename, sal, comm, sal*12+NVL(comm,0) from emp;
** NVL(a1, a2) <- 대상 컬럼이 Null인경우 a2값을 반환
a1: 대상컬럼명
a2: 변환목표값
ex6:
select ename as NAME, sal as SALARY from emp;
ex7:
select ename as NAME, sal*12 as "Annual Salary" from emp;
ex8:
select ename as "성 명", sal as "급 여" from emp;
select * from emp where sal>1300 and job=SALESMAN; <- 에러
ora-00904:열명이 부적합합니다
select * from emp where sal>1300 and job='SALESMAN';
select 'Dear ' ename from emp;
Dear
select 'Dear '||ename from emp;
Dear smith
select 'Dear ', ename from emp; <- 별개의 컬럼
Dear smith
** 로그저장법
Spool c:\aa.txt
spool off
ex9:
select ename, job from emp;
select ename||' '||job as Employees from emp;
ex10:
select ename||' is a'||job as "Employees Details" from emp;
ex11:
select ename||': 1 Year Salary ='||sal*12 as Monthly from emp;
ex12:
select job from emp;
ex13:
select distinct job from emp;
select distinct job, ename from emp;
ex14:
select deptno, job from emp;
select distinct deptno, job from emp;
연습문제
01:
참
02:
참
03:
select empno, ename, sal*12 "연 봉" from emp;
04:
desc emp
05:
select distinct deptno from emp;
06:
select ename||' '||job from emp;
07:
select dname||' '||loc from dept;
08:
select job||' '||sal from emp;
09:
ed <-sql plus 명령어
select *
from emp
/ <-에디트 실행
sql buffer 편집기
Alt + F4
select empno, ename, sal, job from emp order by sal desc;
select empno, ename, sal*12 ansal, job from emp order by ansal desc;
select ename. sal, deptno from emp order by deptno, sal desc;
select ename. sal, deptno from emp order by deptno desc, sal;
