Oracle笔记3

Oracle笔记

一些基本操作

修改表格数据(会锁表，不建议使用)

select * from dept for update;

注释

单行注释

-- 注释内容

多行注释

/** 注释内容 **/

查询入职日期在2010/6/6以后的员工信息

select * from emp where emp_hiredate >  to_date('2016/6/6','yyyy-mm-dd');

查询员工工资在2000-2500之间的

select * from emp where emp_salary > 2000and emp_salary <2500;

select * from emp where emp_salary between2000 and 2500;

like模糊查询关键字

--  %可以表示0到多个任意字符

select * from emp where emp_name like ‘s%’;

 

--  _可以表示单个字符

-- 第三个字符为’a’的

select * from emp where emp_name like ‘__a%’;

 

查询奖金等于300和500

select * from emp where emp_bonus = 300 or emp_bonus =500;

select * from emp where emp_bonus in (300,500);

工资大于500或者岗位为’manager’的员工，他们的名字首字母为’j’;

select * from emp where (emp_salary >500or emp_position = ‘manager’)

and emp_namelike ‘j%’;

组合函数

升序

order by 字段名 asc;

降序

order by 字段名 desc;

在dept_id升序的情况下emp_salary降序

select * from emp order bydept_id,emp_salary desc;

当指定的数据为空时，可以指定一个默认输出的number类型数字

nvl()

select nvl(emp_parent_id,20) from emp whereemp_name =’king’;

查询并计算

select emp_name.emp_salary,emp_salary*12 “年薪” from emp;

select emp_name.emp_salary,emp_salary*12 “年薪” from emp order by emp_salary*12;

一些常见的函数

max:求最大值

min:求最小值

nvl:替换空值

count:统计行数

avg:求平均值

sum:求和

all: 查询所有值

any:查询任意值

查询员工的最高工资和最低工资

select max(emp_salary),min(emp_salary) fromemp;

查询最高工资的员工姓名和职位

Select emp_name,emp_position from emp where

emp_salary =(select max(emp_salary) fromemp)

查询大于平均工资员工的信息

select * from emp

where emp_salary >(selectavg(emp_salary) from emp);

 

分组查询

group by

按照部门id分组查询出部门中最高工资和最低工资的数据

selectmax(emp_salary),min(emp_salary),dept_id from emp

group by dept_id;

 

分dept_id,emp_position两个组查询

selectmax(emp_salary),min(emp_salary),dept_id

from emp group by dept_id,emp_position;

 

小技巧:

       点击右键àdescribe然后双击可以添加字段

       可以给表名设置一个别名,然后将字段给(.)点出来

 

Having条件

 

查询平均工资高于2000的部门编号和他的平均工资

先分组再通过having查询

select dept_id,avg(emp_salary) from empgroup by dept_id

having avg(emp_salary) > 2000;

 

多表查询

select * from emp;

select * from dept;

在多表查询时尽量给表名设置一个别名

产生笛卡尔积

错误:select * from emp e,dept d;

不产生笛卡尔积的技巧时在后面加个条件

select * from emp e,dept d where e.dept_id = d.id;

 

查询两个表

select e.emp_id "员工编号",e.emp_name"员工姓名",d.dept_id "部门编号",d.dept_name"部门姓名"

from emp e,dept d;

 

查询部门编号为10的员工信息 及 对应的部门信息

select e.emp_id "员工编号",e.emp_name "员工姓名",d.dept_id "部门编号",d.dept_name "部门姓名"

from emp e,dept d where e.dept_id =d.dept_id and e.dept_id = 10;

查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门(大于最大的)

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id ande.emp_salary > (select max(emp_salary) from empwhere dept_id = 30);

 

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id ande.emp_salary > all(select emp_salary from empwhere dept_id = 30);

比部门为30的任意一个员工的工资都高的员工的姓名，工资和部门(大于最小的)

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id and e.emp_salary> (select min(emp_salary) from emp where dept_id = 30);

 

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id ande.emp_salary > any(select emp_salary from emp where dept_id = 30);

查询与smith的部门和岗位完全相同的员工

select * from emp

where (emp_position,dept_id) = (

select emp_position,dept_id from emp whereemp_name = 'smith');

分页查询数据4-10条(常见分页查询模板)

select * from (select a1.*,rownum rn(别名) from

(select * fromemp) a1

where rownum <= 10)

where rn(别名) > 4;