1.select sal salary from emp;
select sal as salary from emp;
select sal as "salary" from emp;
select comm "comm sal" from emp;
column alias can not use where clause
2.DML select,insert,update,delete,merge
DCL grant,revoke
DDL create,drop,alter,rename,truncate
DTL commit,rollback,savepoint
3.select statements are not case-sensitive
select * from emp;
select ename,id from emp;
select sal+100 from emp; (+,-,*,/,()),number or date data can use them
4.NULL value unknown value,is not the same as zero or a blank space
the value of numeric expression containing NULL is NULL
the value of character expression containing NULL is no null, is original value
5.||
select ename||' '||job from emp;
6.string character or date using single quotation
select ename||'is a'||job from emp;
select ename||q'['s a ]'||job from emp;
7.select distinct deptid from emp;
select distinct ename,empno from emp;
8 desc emp;
select dbms_metadata.getddl('TABLE','T') from dual;
9.character strings and dates
strings and dates must use single quotations
strings are case sensitive
date are format-sensitive
the defult date format is dd-mon-rr
10.comparison operators(>,>=,<,<=,=,<>,between...and(>=...<=),in(set),like,is null)
11.select sal from emp where sal between 100 and 3000;
select ename from emp where ename between 'ALLEN' and 'CLARK';
12.in/not in
select ename from emp where mgr in(7698,7782,7788);
select ename from emp where mgr=7698 or mgr=7788 or mgr=7782;
select ename from emp where job in ('CLERK','MANAGER');
select ename from emp where job='CLERK' or job='MANAGER';
13.like,_,%
select ename from emp where ename like 's%';
select ename from employees where hire_date like '%95';
select job_id from employees where job_id like '%ST\_%' escape '\'
14.is null
is not null
15.and,or,not
not in
not like
16.order by
1. asc(default)
desc
2. come last the select clause
3. sorting by alias
4. in asc, null last
desc,nul first
select employee_id,salary,hire_date from employees order by 3;
select employee_id,salary,hire_date from employees order by hire_date;
select employee_id,salary*12 sal,hire_date from employees order by sal;
select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc;
select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc nulls first;
select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc nulls last;
17.substitution variables
1.&|&&
2.using quotation when date or character string '&hire_date'
3. can use in anyway in the sql statement
4. is client function ,not oracle server
5.&& can not promt the same column_name
select employee_id,department_id,salary*12 sal,hire_date from employees where employee_id=&empid;
select employee_id,department_id,salary*12 sal,hire_date from employees where hire_date='&hiredate';
select employee_id,&hire_date,salary*12 sal,hire_date from employees where hire_date='&hiredate';
select employee_id,&&hire_date,salary*12 sal from employees order by &hire_date
18.define
define empno=20
undefine empno
select employee_id from employees where employee_id=&empno;
19.verify: weather is or display the before and after of substitution variables
set verify on
set verify off
20.single row:return one result per row
character functions
lower() concat('hello','world')
upper() substr('helloworld',1,5)
initcap() length()
instr('helloworld','w')
lpad(salary,10,'*'),rpad(salary,10,'*')
replace('jack and jue','j','bl')
trim('h' from 'helloworld') elloworld
number functions
round()
trunc()
mod()
date functions
select sysdate from dual;
months_between('01-sep-95','11-jan-94')
add_months('31-jan-96',1)
next_day('01-sep-95','friday')
last_day('01-feb-95')
trunc()
round()
21.conversion function
select '12'+1 from dual;
select to_char(sysdate,'yy/mm') from dual;
select to_char(sysdate,'fmyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual;
select to_char(6000,'$999,999.00') from dual;
select to_number('-$95.24','$99.99') from dual;
select to_date('12,05 99','dd,mm yyyy') from dual;
22. genaral function
nvl(1,2)
nvl2(1,2,3)
nullif(length(1),length(2)) from dual if the length of 1 ,2 is equal, return null, not equal,return lenth(1)
coalese(1,2,3,'') if 1is not null, return 1,else do remaing coalse
23.condition expression
1.case
select last_name,job_id,
case job_id when 'SH_CLERK' THEN 1.10*salary
when 'SA_REP' then 1.15*salary
when 'SA_MAN' then 2.0*salary
else salary end "revised salary"
from employees
2.decode
select last_name,job_id,
decode(job_id,'SA_REP',salary*1.10,'SA_MAN',salary*1.15,'SA_CLERK',salary*2.0,salary)
from employees
24.mutiple rows function : ignore null value
avg()
count()
min()
max()
sum()
stddev()
variance()
select count(*) from emp;
select count(department_id) from emp; //return the number of rows with not null vlues for expr
select count(department_id) from employees
select count(distinct department_id) from employees
select avg(nvl(commission_pct,0)) from employees;
25.group by
1.all columns in the select list that are not in group functions must be in the group by clause;
2.can nou use group functions in the where clause;
3. can not use where to restrict groups
4. can use having to restrict groups
select department_id,avg(salary)
from employees
group by department_id
select department_id,job_id,sum(salary)
from employees
group by department_id,job_id
select job_id,max(salary) payroll
from employees
group by job_id
having max(salary)>10000
26.execute order of the select statements
from-where-group by-group functions-having-order by
27.select from muti tables
1.tables alias, when we use table alias, we must use alias in places;
2.column alias
3.natural join(using) ,the same name,the same type
一。sql std
select department_id,department_name,location_id,city
from departments
natural join locations; (sql standard)
where department_id in(20,30);
select employee_id,last_name,department_id,department_name
from employees join departments
using(department_id)
where department_id=30;
select e.employee_id,e.last_name,e.department_id,d.department_name
from employees e join departments d
on (e.department_id=d.department_id) and (e.manager_id=d.manager_id)
二。oracle std
select department_id,department_name,d.location_id,city
from departments d,locations l
where d.location_id=l.location_id(oracle sql)
4.self join
select w.last_name,m.last_name
from employees w join employees m
on (w.manager_id=m.employee_id)
5.not equaljoin
select e.last_name,e.salary,j.grade_level
from employees e join job_grades j
on e.salary between j.lowerest_sal and j.hightest_sal;
select e.last_name,e.salary,j.grade_level
from employees e , job_grades j
where e.salary between j.lowerest_sal and j.hightest_sal;
6.left/right/full outer join
select e.last_name,e.department_id,d.department_name
from employees e left outer join departments d
on (e.department_id=d.department_id)
select e.last_name,e.department_id,d.department_name
from employees e right outer join departments d
on (e.department_id=d.department_id)
select e.last_name,e.department_id,d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id)
oracle std
select e.last_name,e.department_id,d.department_name
from employees e ,departments d
where e.department_id(+)=d.department_id (rigth join)
select e.last_name,e.department_id,d.department_name
from employees e ,departments d
where e.department_id =d.department_id(+) (left join)
7.cross join
select last_name,department_name
from employees cross join departments (sql std)
select last_name,department_name
from employees ,departments (oracle std)
28.subquery(inner query):
1. the subquery(inner query) executes before the main query
2. the subquery(inner query) exists from,where,having
3. enclose subquery in parentheses
4. place subquery on the right side of the comparison condition for readlibility(howerver,the subquery can appear on either side of the comparison operator)
5. using single-row operators with single-row subquery
multiple operators with mutilple operators
一.single-row subquery
select last_name,job_id,salary
from employees
where salary =(select min(salary) from employees );
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id=50);
select job_id,avg(salary)
from employees
group by job_id
having avg(salary)=(select min(avg(salary)) from employees group by job_id);
二.multi-row subquery(in ,any,all)
not in ==== <> all
in ==== =any
select employee_id,last_name
from employees
where salary in (select min(salary) from employees group by department_id);
select employee_id,last_name,job_id,salary
from employees
where salary>all(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary<all(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary>any(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary<any(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary=any(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';