SQL语句容易出现错误的地方-连载

1.语言问题

修改语言注册表\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小时，然后精确显示到小时

 

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

3.当如下事件发生是，会隐式的执行Commit动作

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

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

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

4.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;

5.带有数量限制的查询举例

--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);

 

例如查找一个工作种类在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 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;