오라클 명령어
최고관리자로 접속하여 scott 사용자의 암호를 scott로 변경
C:\>sqlplus system/orcl
SQL> alter user scott identified by scott account unlock;
SQL> alter user scott identified by scott account unlock;
scott 사용자로 로그인
SQL> conn scott/scott
사용자가 관리하는 테이블 목록
SQL> select * from tab;
특정 테이블의 구조(필드 리스트/데이터형식/제약조건) : desc
SQL> desc dept;
dept 테이블에 모든 정보(레코드)를 출력
SQL> select * from dept;
SQL> select deptno, dname, loc from dept; * 필요한 필드만 선택해서 출력
SQL> select deptno, dname, loc from dept; * 필요한 필드만 선택해서 출력
각각의 필드명에 별칭을 주어서 출력
SQL> select deptno as "부서번호" from dept;
SQL> select deptno "부서번호" from dept; -- as 생략가능
SQL> select deptno "부서번호" from dept; -- as 생략가능
사원들의 직업명을 중복 제거 후 출력
SQL> select distinct job from emp;
조건절(where)을 이용한 데이터 출력
SQL> select empno, ename, sal from emp where sal >= 3000; * 급여가 3000 이상인 사원
SQL> select empno, ename, sal from emp where ename = 'SCOTT'; * 이름이 'SCOTT' 인 사원
SQL> select empno, ename, hiredate from emp where hiredate >= '1985/01/01';
SQL> select ename from emp where deptno = 10 and job = 'MANAGER';
SQL> select ename from emp where deptno = 10 or job = 'MANAGER';
SQL> select deptno, ename from emp where not (deptno = 10);
SQL> select deptno, ename from emp where deptno <> 10;
SQL> select ename, sal from emp where sal >= 1000 and sal <= 3000;
SQL> select ename, sal from emp where sal between 1000 and 3000;
SQL> select ename, sal from emp where sal = 1300 or sal = 1500 or sal = 1600;
SQL> select ename, sal from emp where sal in (1300, 1500, 1600);
SQL> select empno, ename from emp where ename like 'K%';
SQL> select empno, ename from emp where ename like '%K%';
SQL> select empno, ename from emp where ename like '%K';
SQL> select empno, ename from emp where ename like '_A%';
SQL> select empno, ename, comm from emp where comm is null;
SQL> select empno, ename, comm from emp where comm is not null;
SQL> select empno, ename from emp order by empno asc; -- asc 생략 가능
SQL> select empno, ename from emp order by empno desc;
SQL> select empno, ename, sal from emp where ename = 'SCOTT'; * 이름이 'SCOTT' 인 사원
SQL> select empno, ename, hiredate from emp where hiredate >= '1985/01/01';
SQL> select ename from emp where deptno = 10 and job = 'MANAGER';
SQL> select ename from emp where deptno = 10 or job = 'MANAGER';
SQL> select deptno, ename from emp where not (deptno = 10);
SQL> select deptno, ename from emp where deptno <> 10;
SQL> select ename, sal from emp where sal >= 1000 and sal <= 3000;
SQL> select ename, sal from emp where sal between 1000 and 3000;
SQL> select ename, sal from emp where sal = 1300 or sal = 1500 or sal = 1600;
SQL> select ename, sal from emp where sal in (1300, 1500, 1600);
SQL> select empno, ename from emp where ename like 'K%';
SQL> select empno, ename from emp where ename like '%K%';
SQL> select empno, ename from emp where ename like '%K';
SQL> select empno, ename from emp where ename like '_A%';
SQL> select empno, ename, comm from emp where comm is null;
SQL> select empno, ename, comm from emp where comm is not null;
SQL> select empno, ename from emp order by empno asc; -- asc 생략 가능
SQL> select empno, ename from emp order by empno desc;
오라클 집계 함수
SQL> select sum(sal) from emp;
SQL> select count(*) from emp;
SQL> select avg(sal) from emp;
SQL> select max(sal) from emp;
SQL> select min(sal) from emp;
SQL> select count(*) from emp;
SQL> select avg(sal) from emp;
SQL> select max(sal) from emp;
SQL> select min(sal) from emp;
Group by 절 : 직업별 급여 평균
SQL> select job, avg(sal) from emp group by job;
Having 절 : 직업별 급여 평균(단, 급여평균 2000 이상 ) - group 에 대해서 추가적인 조건을 주고 싶을 때 사용
SQL> select job, avg(sal) from emp group by job having avg(sal) >= 2000;