总结

这周主要学习了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 增加的正则表达式函数有以下四种：

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

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

in适合于外表大而内表小的情况；exists适合于外表小而内表大的情况如果两个表相当则in/exists差别不大用not exists比not in执行的效率要高的多使用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') --月份大写

引起全表扫描：

模糊查询效率很低,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;

转载于:https://www.cnblogs.com/sufferingStriver/p/9403471.html

相关资源：测试工具-Postman-win64-7.29.1-Setup.7z