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;

发表评论

电子邮件地址不会被公开。 必填项已用*标注