7.29实习培训日志-Oracle题目

总结

这周主要学习了SQL，oracle中的SQL基础，以前学习的是SQLserver的SQL，和oracle的还是有略微不同，所以重新去学习了一段时间，然后对于oracle中的各种函数有了初步了解，比如to_char()，decode(),nvl2()等函数，对于oracle中表的连接的独立写法using,+也有了初步了解，对于over开窗函数不是很了解，对老师给的练习题做了总结，对自己不知道做了以下笔记。

Oracle题目

Orcale-SQL语句 习题-长期更新

根据日期得到年

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 条件显示特定的组，也可以使用多个分组标准进行分组。

关于group by 两个或以上条件的分析

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 增加的正则表达式函数有以下四种：

1. regexp_like() --返回满足条件的字段
2. regexp_instr() --返回满足条件的字符或字符串的位置
3. regexp_replace() --返回替换后的字符串
4. 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

1. nvl2()

2. SQL中的case when then else end用法

3. 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

oracle子查询中not 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条

oracle 取前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

1. in适合于外表大而内表小的情况；exists适合于外表小而内表大的情况
2. 如果两个表相当则in/exists差别不大
3. 用not exists比not in执行的效率要高的多
4. 使用in时不对null进行处理

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引,使用

minus 子句都比not in 子句快，虽然使用minus子句要进行两次查询：

in 和 exists性能研究

单双引号区别

双引号的作用：关键字，对象名、字段名加双引号，则示意 Oracle将严格区分大小写，否则Oracl都默认大写。

oracle单引号和双引号的区别

由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性

mon与MON

to_char(hire_date,'dd-mon-yyyy') --月份小写
to_char(hire_date,'dd-MON-yyyy') --月份大写

引起全表扫描：

1. 模糊查询效率很低,like本身效率就比较低，应该尽量避免查询条件使用like；尽量用左模糊，考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。
2. Oracle 9i中，查询字段is null时单索引失效，引起全表扫描。is not null 时永远不会使用索引，is null查询时可以重新启用索引查找,但是效率还不是值得肯定
3. QL中，不等于操作符（（<>、!=））会限制索引，引起全表扫描，即使比较的字段上有索引，把不等于操作符改成or，如column<>’aaa’，改成column<’aaa’ or column>’aaa’
4. where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。
5. Update 语句，如果只更改1、2个字段，不要Update全部字段
6. 组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。例如：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;