7.29实习培训日志-Oracle题目
总结
这周主要学习了SQL，oracle中的SQL基础，以前学习的是SQLserver的SQL，和oracle的还是有略微不同，所以重新去学习了一段时间，然后对于oracle中的各种函数有了初步了解，比如to_char()，decode(),nvl2()等函数，对于oracle中表的连接的独立写法using,+也有了初步了解，对于over开窗函数不是很了解，对老师给的练习题做了总结，对自己不知道做了以下笔记。
Oracle题目
根据日期得到年
select extract (year from sysdate) from dual
select * from employees where to_char(hire_date,'yyyy') > 1997;
group by字段问题
使用group by 时：select中的字段不可以单独出现，必须出现在group语句中或者在组函数中。
where 子句：分组之前过滤数据，条件中不能包含聚合函数。
having 子句：分组之后过滤数据，条件中经常包含聚合函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
select employee_id,count(last_name) from employees group by employee_id --正确
select * from employees group by employee_id --错误
select count(last_name),employee_id from employees -- 错误
using select不带表名
ORA-25154: column part of USING clause cannot have qualifier：连接条件出现在查询中，不能带表名
oracle正则
oracle 10g 增加的正则表达式函数有以下四种：
- regexp_like() --返回满足条件的字段
- regexp_instr() --返回满足条件的字符或字符串的位置
- regexp_replace() --返回替换后的字符串
- regexp_substr() --返回满足条件的字符或字符串
regexp_like(search_string ,pattern[,match_option]);
--match_option是一个文本串，允许用户设置该函数的匹配行为。可以使用的选项有：
--c 匹配时，大小写敏感，默认值
--i 匹配时，大小写不敏感等
count()
count(列)和count()其实一样快，如果索引列是非空的，count()可用到索引，此时一样快。
count(*)是针对于全表的，而count(列)是针对于某一列的，如果此列值为空的话，count(列)是不会统计这一行的。
关于count(1)，count(*),和count(列名)的区别
查询索引
select * from user_indexes where table_name='EMPLOYEES';--查索引
nvl2，decode，case
-
nvl2()
-
SQL中的case when then else end用法
-
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
decode(字段或字段的运算，值1，值2，值3）:当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
over lag lead
over()表示 lag()与lead()操作的数据都在over()的范围内
lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。
max(avg(salary))错误
select department_id,max(avg(salary)) from employees group by department_id --错了？？？
mot in后面不为null
--能查询到数据
select department_id,department_name,manager_id,location_id from departments where department_id not in (select department_id from employees join jobs using(job_id) where job_title='Sales Representative' and department_id is not null)
--查询不到数据，子查询为null
select department_id,department_name,manager_id,location_id from departments where department_id not in (select department_id from employees join jobs using(job_id) where job_title='Sales Representative')
over(partition by )和group by
--对于此题，用over(partition by department_id),清楚什么时候用group by，什么时候使用over()
select employee_id,last_name,department_id,round(avg(salary) over(partition by department_id),4) from employees order by employee_id
order by + 数字
select_expr [, select_expr …] [FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position} [ASC | DESC], … [WITH ROLLUP]] [HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], …]
--order语句中position与col_name是替代关系,它代表select后边的参数位置.
--group语句中position的含义相同
SELECT a, b FROM t
GROUP BY 2
ORDER BY 2
--两个2都表示b
grouping sets分组集
--例如查询（Department ID, Job ID（Job ID, Manager ID），不要用rollup,cube
group by grouping sets((department_id,job_id),(job_id,manager_id)) order by 1,2,3
oracle取前十条记录
Rownum和row_number() over()
--全表扫描后再排序，然后再取10条纪录
select * from (select * from tbname order by id desc ) where rownum<=10;
--不会全表扫描，只会取出10条纪录
SELECT * FROM torderdetail WHERE ROWNUM <= 10 ORDER BY order_date DESC
--order by 的字段是pk，则是先排序，再取10条
标量子查询
子查询分标量子查询、表子查询。
标量子查询：子查询的返回值必须只有一行记录，而且只能有一个列。可以用在select语句的列表中、表达式中、where语句中等。可看做是一个拥有返回值的函数。
表子查询：列值子查询可以返回一个多行多列的结果集。表子查询可以看作一个临时表，表子查询可以用在select语句的from子句、insert语句、连接、in子句等。可以看作是一个在内存中临时存在的数据表。
oracle 高效设计：oracle中有标量子查询，性能要由于关联查询?
《Oracle 高效设计》 读书思考--标量子查询查询性能讨论
SQL server中知道关联查询的性能要优于标量子查询。
窗口函数over()
某些时候用over取代group by
--报错，因为x2、x3、x5没有包含在聚合函数的group by子句中。
select x1, x2, x3, x4,
case when x4=max(x4) then '单内最大值' else '' end, x5
from y group by x1;
--结果不正确
select x1, x2, x3, x4,
case when x4=max(x4) then '单内最大值' else '' end, x5
from y group by x1 ,x2,x3,x4;
--改成窗口函数
select x1, x2, x3, x4,
case when x4=max(x4) over(partition by x1) then '单内最大值' else '' end, x5
from y;
--不支持窗口函数，用相关子查询
select x1, x2, x3, x4,
case when x4=(select max(x4) from y t where t.x1 = t1.x1) then '单内最大值' else '' end, x5
from y;
flashback
oracle 不能rollback ddl语句,修改表结构语句
system用户不支持flashback
可以回滚dml语句
--查看是否闪回功能
select * from v$OPTION where parameter like 'Flashback%';
--回滚删除的表
flashback table tablename to before drop;
--[回退到几分钟之前,用的多]
flashback table tablename to timestamp systimestamp - interval '5' minute;
--删了或修改里面的数据，可以先建立一个快表将删除修改之前状态的数据找回到这个表(1小时前)
CREATE TABLE QUICK_TABLE AS
SELECT * FROM TABLE_NAME AS OF TIMESTAMP SYSDATE-1/24
insert all插入多条
--no 好像必须指定字段名
insert all into job_history values
select * from job_history as of timestamp sysdate-1/24
--ok
insert all into job_history values(employee_id,start_date,end_date,job_id,department_id)
select employee_id,start_date,end_date,job_id,department_id from job_history as of timestamp sysdate-1/24
interval 求时间
select sysdate - interval '10' day as "10天前",
sysdate - interval '10' hour as "10小时前",
sysdate - interval '10' minute as "10分钟前",
sysdate - interval '10' second as "10秒钟前",
sysdate - 10 as "10天前",
sysdate - 10 / 24 as "10小时前",
sysdate - 10 / (24 * 60) as "10分钟前",
sysdate - 10 / (24 * 3600) as "10秒钟前"
from dual;
in多个字段
--(employee_id,start_date) in前的属性顺序和select的顺序要一样
select * from job_history where (employee_id,start_date) in (select employee_id,min(start_date) m from job_history group by employee_id having count(*)>1)
with临时表
--不加()括上select会报错
with MAX_SAL_CALC as
(select job_id,max(salary) max_salary from employees group by job_id order by 2 desc)
递归查询
--???
select … from tablename
start with 条件1
connect by 条件2
where 条件3;--过滤条件
--where看成与递归查询无关.只是对整个结果起过滤作用
Select
employee_id, first_name, last_name, hire_date, salary
From
employees
where last_name!='De Haan'
start with last_name = 'De Haan'
connect by prior employee_id = manager_id
常用系统表
--Oracle查询所有用户
select * from all_users;
select * from dba_users;
select * from v$Session --Oracle查看当前用户连接
select * from session_privs --Oracle查看当前用户权限
select * from user_source --Oracle查询所有函数和储存过程
select * from user_all_tables --Oracle查询用户表空间
select * from user_cons_columns
select * from user_constraints
in,exists
- in适合于外表大而内表小的情况；exists适合于外表小而内表大的情况
- 如果两个表相当则in/exists差别不大
- 用not exists比not in执行的效率要高的多
- 使用in时不对null进行处理
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引,使用
minus 子句都比not in 子句快，虽然使用minus子句要进行两次查询：
单双引号区别
双引号的作用：关键字，对象名、字段名加双引号，则示意 Oracle将严格区分大小写，否则Oracl都默认大写。
由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性
mon与MON
to_char(hire_date,'dd-mon-yyyy') --月份小写
to_char(hire_date,'dd-MON-yyyy') --月份大写
引起全表扫描：
- 模糊查询效率很低,like本身效率就比较低，应该尽量避免查询条件使用like；尽量用左模糊，考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。
- Oracle 9i中，查询字段is null时单索引失效，引起全表扫描。is not null 时永远不会使用索引，is null查询时可以重新启用索引查找,但是效率还不是值得肯定
- QL中，不等于操作符（（<>、!=））会限制索引，引起全表扫描，即使比较的字段上有索引，把不等于操作符改成or，如column<>’aaa’，改成column<’aaa’ or column>’aaa’
- where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。
- Update 语句，如果只更改1、2个字段，不要Update全部字段
- 组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。例如：create index skip1 on emp5(job,empno，date); 改成select job，empno from emp5 where job=’manager’and empno=’10’ order by job,empno,date desc;
