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;错的
