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

语言问题

修改语言注册表\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;错的