oracle学习笔记(第五章:数据字典)

潘若云

怎样修改oracel数据库的默认日期？
alter session set nls_date_format='yyyymmddhh24miss'
查看表的注释
select comments from user_tab_comments where table_name = 'empm';
查看列注释
select comments from user_col_comments where table_name= 'empm' and column_name='emp_nm';
为表或列加注释
comment on table table_nm is '基本信息表';
comment on column table_nm.col_nm is '工号';
伪列﹕rowid,rownum  
select * from empm where ROWNUM <= 10 --查询一个表的前10行记录
删除重复记录，只保留第一条
delete from dumpy_part a
where a.rowid <>(select min(rowid) from dumpy_part b where a.id = b.id )  
在select 语句中 distinct 和 Unique的用法相同
使用了distinct 或unique 后,order by 后面的列需要出现在select列表中
例如:tab_1表中有a,b,c三列;select distinct a,b from tab_1 order by c;这样写会报错，原因

update/insert 与 select 的配合
UPDATE empm SET emp_age =(SELECT age + 10 FROM empm WHERE emp_no = '0001')
INSERT INTO empm_copy SELECT * FROM empm

外连接 替代 not in ,可以提高查询效率
SELECT emp_no FROM empm WHERE emp_no NOT IN (SELECT emp_no FROM empimg)
--等价于(但是效率较高)
SELECT empm.emp_no,empimg.emp_no from empm,empimg where empm.emp_no=empimg.emp_no(+) AND empimg.emp_no IS NULL;

单行子查询包括﹕where单行子查询,having单行子查询﹐from单行子查询
---------where单行子查询
select * from empm
where emp_no =(select emp_no from empimg where emp_no='123');
---------having单行子查询
select avg(age),min(age),max(age)
from empm
having avg(age)> min(age)

---------from 子句中的单行查询子查询
select emp_nm from empm a,(select emp_no, emp_nm from emp_d) x
where a.emp_no=x.emp_no

---------多行子查询﹐包括﹕in 多行子查询﹐all 多行子查询﹐any 多行子查询
---------in 多行子查询
select * from empm where emp_no in (select emp_no from empimg)
---------any 多行子查询
select * from empm where age < any (select avg(age) from empm group by emp_no);

select * from empm where eage = any (select avg(age) from empm group by emp_no);
等价于
select * from empm where age in (select avg(age) from empm group by emp_no)
--------any 多行子查询
select * from empm
where age > all (select avg(age) from empm)

-------多列子查询﹕与单列子查询不一杨﹐多列子查询要返回多列﹐多列子查询
-------又分为成对比较子查询和非成对比较子查询
-------成对子比较多列查询
-------哪些员工的年龄在本部门最高﹖
select emp_no,dept_no,age from empm
where (dept_no,age) in (select dept_no,max(age) from empm group by dept_no)
------非成对比较多列子查询
------哪些员工的年龄与某一职位的最高工资相同
select emp_no,dept_no,age from empm
where age in (select max(age) from empm group by dept_no)
and dept_no in (select distinct dept_no from empm)


-- WITH  AS 的用法：将查询结果放在临时表中
WITH
T1 AS
(SELECT EMP_NO,EMP_NM,SEX,AGE FROM empm WHERE sex='1'),
T2 AS
(SELECT EMP_NO,EMP_NM,SEX,AGE FROM empm WHERE AGE<=35)
SELECT * FROM T1,T2 WHERE T1.EMP_NO=T2.EMP_NO;