oracle练习笔记



描述一个表用 desc employees
过滤重复的部门 select distinct department_id from employees
别名的三种方式： 1、空格 2、加as 3、" "（多个单词组成的别名必须加空格，要么用下划线分开）
条件匹配日期的： where to_char(date,'yyyy-mm-dd')='1997-06-07'
默认格式： where date = '7-6月-1997'
like: where name like '%\_%' escape '\' (%:0个或者多个字符，_表示任意一个字符，escape表示转义关键字)
order by salary asc(升序) desc（降序）
多层排序： order by salary asc,name asc (在工资相同的情况下按照名字升序排列)
删除表中字段重复的记录：
delete from job_grades j1
where rowid <> (select min(rowid) from job_grades j2 where j1.grade_level = j2.grade_level);
如果删除表中自然顺序的第15行，下面语句可实现。
（rowid是数据库的一个伪列，建立表的时候数据库会自动为每个表建立ROWID列
用来唯一标识一行记录。rowid是存储每条记录的实际物理地址，对记录的访问是基于ROWID。）
delete from tab where rowid=(
select ii from (select ROWNUM nn,ROWID ii from tab WHERE ROWNUM<=15) WHERE nn=15);

等值连接：
select employee_id,e.department_id,department_name,city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
或者
select employee_id,e.department_id,department_name,city
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
等值连接另外方式：
select last_name,department_id,department_name
from employees join departments
--using (department_id) （前提是两表的列名以及列的数据类型要一样）

非等值连接
select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal
左外连接（哪边空就把“+”号放在哪个表上）
select e.employee_id,e.last_name,d.department_name
from employees e,departments d
where e.department_id = d.department_id(+)
左外（右外、满）连接
select employee_id,e.department_id,department_name
from employees e
left outer
--right outer
--full
join departments d on e.department_id = d.department_id
自连接