SQL语句常见问题的总结(持续更新)

原创 2013年12月04日 23:14:49

语言问题

修改语言注册表\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432\ORACLE\KEY_DevSuitHome1中的NLS_LANG修改为AMERICAN_AMERICA.ZHS16GBK(这是英语)

查询当前语言select userenv('LANG') from DUAL;

易错select NEXT_DAY ('01-SEP-95',1) from dual;中的1代表周几而不是几天

查看当前数据库参数,plsql_dev中的report/dba/NLS Database Parameters

精确到小时的hire_date+8/24表示的是hire_date时间加上8小时,然后精确显示到小时

 

AVG函数自动忽略NULL值求平均

 

Cop一张表create table copy_emp as select *from employees;

 

当如下事件发生是,会隐式的执行Commit动作:

1、数据定义语句被执行的时候,比如新建一张表:Create Table …

2、数据控制语句被执行的时候,比如赋权GRANT …( 或者DENY)

3、正常退出iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行COMMIT 或者ROLLBACK语句。

 

NOT NI 易错点

SELECT department_id, department_name

FROM departments

WHERE department_id NOT IN (SELECT department_id

FROM employees)子查询表中有null这句话就查不出东西就是错的

当然也可以这样解决

SELECT *

FROM   departments

WHERE  department_id NOT IN(SELECT department_id

                             FROM employees

                             WHERE job_id = 'SA_REP'

                             AND department_id IS NOT NULL)
还可以这样

SELECT department_id, department_name

FROM departments d

WHERE NOT EXISTS (SELECT 'X'

FROM employees

WHERE department_id

= d.department_id)

 

当查询的内容有多项,其中包括有分组查询的例如sum等分组函数,但是有的内容不需要,也不能分组的时候,方法是把有分组函数的内容拿出来单独建个子查询,子查询中建立需要的分组,不能分组的就放在子查询的查询外,例如

SELECT e.employee_id,e.last_name,e.salary,e.department_id,m.avg_salary

FROM employees e, (SELECT department_id,AVG(salary) avg_salary

                  FROM employees

                  GROUP BY department_id) m

WHERE  m.department_id = e.department_id

ORDER BY m.avg_salary DESC;

 

 

带有数量限制的查询举例

--Practices_18:Show the department number, department name, and the number of employees working in each department that:

--a.  Includes fewer than 3 employees

SELECT d.department_id,d.department_name,COUNT(*)

FROM departments d,employees e

WHERE d.department_id = e.department_id

GROUP BY d.department_id,d.department_name

HAVING COUNT(*)<3;

--b.  Has the highest number of employees:

SELECT d.department_id,d.department_name,COUNT(*)

FROM departments d,employees e

WHERE d.department_id = e.department_id

GROUP BY d.department_id,d.department_name

HAVING COUNT(*)>=ALL (SELECT COUNT(*)

                      FROM departments d,employees e

                      WHERE d.department_id = e.department_id

                      GROUP BY d.department_id,d.department_name);

--c.  Has the lowest  number of employees:

SELECT d.department_id,d.department_name,COUNT(*)

FROM departments d,employees e

WHERE d.department_id = e.department_id

GROUP BY d.department_id,d.department_name

HAVING COUNT(*)<=ALL (SELECT COUNT(*)

                      FROM departments d,employees e

                      WHERE d.department_id = e.department_id

                      GROUP BY d.department_id,d.department_name);

 

 

Top-N查询

--Practices_29:Write a query to display the top three earners in the EMPLOYEES table. Display their last names and salaries

方法一:

select last_name,salary    

from employees e1   

where    

  (

   select count(1)   

   from employees e2   

   where  e2.salary>=e1.salary

  ) <=3 

order by salary desc;

方法二:先排序然后利用ROWNUM取需多少数据

SELECT [column_list], ROWNUM

FROM (SELECT [column_list]

FROM table

ORDER BY Top-N_column)

WHERE ROWNUM <= N;

 

 

两个时间间隔的计算处理

精确到年月

--Practices_4:Show the last names of all employees together with the number of years

            --and the number fcompleted months that they have been employed.

SELECT last_name,

       TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date)/12,0) "YEARS",

       ROUND(MOD(MONTHS_BETWEEN(SYSDATE,hire_date),12),0) "MONTHS"         

FROM employees;

精确到天:两个日期相减的结果是精确到天的,例如

Select last_name,sysdate,hire_date,sysdate-hire_date,(sysdate-hire_date)/365 from employees;

1   King   2013/8/2 13:05:57 1987/6/17  9543.54579861111  26.1467008181126

根据时间处于前半月(年)还是后半来处理数据

--Practices_12:Show all employees who were hired in the first half of the month (before the 16th of the month).

SELECT last_name,hire_date

FROM employees

WHERE ROUND(hire_date,'MONTH') = TRUNC(hire_date,'MONTH');

 

易错点

oraclesql左右外连接是带(+)的那边是被驱动表,显示的信息少,不带的那边是驱动表,不满足条件的也显示,所以显示的多

SELECT d.department_id,d.department_name,d.location_id,COUNT(e.employee_id)

FROM departments d,employees e

WHERE e.department_id(+) = d.department_id

GROUP BY d.department_id,d.department_name,d.location_id;

d表中不满足的也显示

 

思路易错点

例如查找一个工作种类在1991年前半年招聘了而且在1990年前半年也招聘了,如果用AND条件就不行,应为“同一个表”中不可能同时满足两个条件。此时应该用交集处理

SELECT job_id

FROM   employees

WHERE  hire_date

BETWEEN '01-JAN-1990' AND '30-JUN-1990'

INTERSECT

SELECT job_id

FROM   employees

WHERE  hire_date BETWEEN '01-JAN-1991'AND '30-JUN-1991';

 

 

易错:

SELECT last_name,salary,DECODE(commission_pct,null,'NO','YES') COMM ,CASE

                             WHEN commission_pct IS NULL THEN 'YES'

                             ELSE 'NO'

                        END COMM1

FROM employees;对的

SELECT last_name,salary,DECODE(commission_pct,null,'NO','YES') COMM ,CASE commission_pct

                             WHEN IS NULL THEN 'YES'

                             ELSE 'NO'

                        END COMM1

FROM employees;错的

版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

sql语句优化常见问题总结

(1)选择最有效率的表名顺序(只在基于规则的优化器中有效):  Oracle的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将...

二叉树的常见问题总结--持续更新中

二叉树的常见问题有如下几个,如果解决好了,就跟链表一样轻松:唯一不一样的是,二叉树是非线性结构。常见的问题如下: 二叉树的问题 .二叉树三种周游(traversal)方式:  ...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

Sql常见问题总结二(Sql语句怎么样查询IP,游标去重复,各种函数使用,各种取时间格式,字符串精确排序,超时锁问题)

请稳步我的博客查阅并下载所有资源以及源代码 http://www.cckan.net 文章导航  SQL Server 2005  学习笔记系列文章导航(存储过程,视频,索引,Clr,各种工具...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)