Oracle sql语句

最近学习SQL记录

alter session set NLS_DATE_LANGUAGE='AMERICAN';

col hiredate for a15;
col ename for A8;

在SQL中使用算术表达式:+、-、*、/
SELECT empno,ename,sal,500+sal FROM emp;
SELECT empno,ename,sal,500+sal*12 FROM emp;
SELECT empno,ename,sal,(500+sal)*12 FROM emp;

在SQL中使用别名:
SELECT empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary" FROM emp;

连接运算符:
SELECT ename || ' annual salary is '|| (500+sal)*12 "Employee's Salary" FROM emp;
SELECT ename || ' 年薪为:'||(500+sal)*12 "员工的年薪" FROM emp;

DISTINCT:当查询比较大的表时候尽可能的避免使用DISTINCT，因为Oracle系统是通过排序的方式来完成DISTINCT这一功能的.
可以作用于多列,此时显示的结果为每一种列组合只显示一行.
SELECT DISTINCT deptno,job FROM emp;

限制性查询和数据的排序:
SELECT empno,ename,sal FROM emp WHERE sal >= 1500;
比较运算符:
>、>=、<、<=、=、<>、!=
BETWEEN AND、IN、LIKE
BETWEEN AND:用于数字、字符型、日期型数据,后两种需要用单引号括起来.
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 1500 ADN 2900;
SELECT empno,ename,sal hiredate FROM emp WHERE hiredate BETWEEN '01-JAN-81' AND '31-MAY-82';
SELECT empno,ename,sal hiredate FROM emp WHERE hiredate NOT BETWEEN '01-JAN-81' AND '31-MAY-82';

错:SELECT empno,ename,job FROM emp WHERE JOB = 'SALESMAN';
正:SELECT empno,ename,job FROM emp WHERE job = 'SALESMAN';

SELECT empno,ename,sal,job FROM emp WHERE job IN ('SALESMAN','CLERK','MANAGER');
SELECT empno,ename,sal,job FROM emp WHERE job NOT IN('ANALYST','PRESIDENT');

通配符:
%:代表0或者多个字符
_:代表一个且只能代表一个字符
SELECT empno,ename,sal,job FROM emp WHERE job LIKE 'S_L_S%';
SELECT empno,ename,sal,hiredate FROM emp WHERE hiredate LIKE  '%81'；

CREATE TABLE dept_temp AS SELECT * FROM dept;

INSERT INTO dept_temp VALUES (88,'IT_RESEARCH','BEIJING');
转义关键字:escape
SELECT * FROM dept_temp WHERE dname LIKE 'IT\_%' escape '\';
SELECT * FROM dept_temp WHERE dname LIKE 'IT~_%' escape '~';

ORDER BY:默认ASC由小到大,DESC:由大到小 注意:该语句一定是SQL语句的最后一个子句.
SELECT empno,ename,sal FROM emp WHERE sal >= 1500 ORDER BY sal;
SELECT empno,ename,sal FROM emp WHERE sal >= 1500 ORDER BY sal DESC;
SELECT
    empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary"
FROM
    emp
ORDER BY
    "Annual Salary"
DESC;


SELECT
    empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary"
FROM
    emp
ORDER BY
    (500+sal)*12
DESC;


SELECT
    empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary"
FROM
    emp
ORDER BY
    3
DESC;

先按照职位排序，然后按照工资排序；
SELECT ename,,job,sal FROM emp ORDER BY job,sal DESC;

SET LINE[SIZE]{80|n}
SET line 120

日期型数据的处理:
Oracle的日期型数据的内部存储格式为:世纪、年、月、日、时、分、秒.不论输入的格式如何,Oracle内存存储格式都是按照自己格式来存储的.

得到当前系统时间:
SELECT SYSDATE FROM dual;

可以把一个日期型数据和一个数字相加/减，结果仍然为日期
SELECT SYSDATE -/+ 10 FROM dual;

SELECT TO_DATE('15-JUL-02') - SYSDATE FROM dual;
SELECT TO_DATE('15-5月-03') - SYSDATE FROM dual;

SELECT SYSDATE - 22/24 FROM dual;
SELECT SYSDATE + 22/24 FROM dual;

SELECT empno,ename,job,sal,(SYSDATE-hiredate)/365 "Years" FROM emp WHERE job LIKE 'SAL%';

日期函数:
1、MONTHS_BETWEEN(日期1,日期2)
该函数用于返回日期1和日期2之间的月数,如果日期1大于日期2，其返回的月数为正，如果日期1小于日期2，返回的月数为负。
SELECT MONTHS_BETWEEN('0-JUL-99','03-FEB-98')

2、ADD_MONTHS(日期,n)
该函数用于把n个月加到日期上
SELECT ADD_MONTHS('15-OCT-01',8) FROM dual;

3、NEXT_DAY(日期,字符串)
该函数用于返回下一个由字符串(星期几)指定的日期.
SELECT NEXT_DAY('10-MAY-02','MONDAY') FROM dual;//这里如果是中文操作系统可以改成:星期一
表示:从2002年5月10日开始的下一个星期一是2002年5月13日

4、LAST_DAY(日期)
该函数用于返回该日期所在月的最后一天.
SELECT LAST_DAY('08-FEB-02') FROM dual;

空值:
SELECT ename,job,comm FROM emp ORDER BY comm;//NULL值排在最后

NVL(表达式1,表达式2):两个表达式类型必须一致
对数字型:NVL(comm,0)
对字符型:NVL(TO_CHAR(comm),'No Commission')
对日期型:NVL(hiredate,'31-DEC-99)

DECODE:分支判断
SELECT ename "Name",job,sal,"Salary",
    DECODE(job,'SALESMAN',sal*1.15,
                         'CLERK',sal*1.20,
                         'ANALYST',sal*1.25,
                                             ,sal*1.40) "New Salary"
FROM emp
ORDER BY job;

总和数据和分组函数:
1、COUNT:返回非空的行
2、AVG、SUM
3、MIN、MAX:用于数字、字符型、日期型
SELECT MIN(hiredate) "First Day",MAX(hiredate) "Last Day" FROM emp;
4、GROUP BY:在上面几个函数中,都是把一个表看错一个大组来处理。可以使用GROUP BY子句把一个表划分成若干个组,在一个表中简历多组数据.
公司中按照职位(job)分类,每类员工的平均工资
SELECT job , AVG(sal) "Average Salary" FROM emp GROUP BY job;

SELECT job,AVG(sal) "Average Salary"
FROM emp
GROUP BY job
ORDER BY "Average Salary" DESC;

注意:如果在一个查询中使用了分组函数(COUNT/AVG/SUM/MAX/MIN),则任何不在分组函数中的列或表达式必须在GROUP BY子句中.另外这个列或表达式可能是在SELECT或者ORADER BY后面
SELECT job,AVG(sal) FROM emp GROUP BY job;
SELECT job，AVG(sal) FROM emp GROUP BY job ORDER BY deptno;//显示是错的

5、HAVING子句的使用:
在WHERE子句中不可以使用分组函数
SELECT job,AVG(sal) FROM emp WHERE AVG(sal) > 1500 GROUP BY job;//错误的
SELECT job,AVG(sal) FROM emp HAVING AVG(sal) > 1500 GROUP BY job;//顺序不好
SELECT
    job,AVG(sal)
FROM
    emp  
GROUP BY
    job
HAVING AVG(sal) > 1500
ORDER BY 2( AVG(sal) );
执行顺序:
1、首先对数据行(记录)进行分组
2、把所得到的分组应用于分组函数
3、最后显示满足HAVING子句所指定条件的结果

SELECT MIN(AVG(sal)) , MAX(AVG(sal))
FROM emp
WHERE job NOT LIKE 'PRESI%'
GROUP BY job;
执行顺序:
1、在emp表中找到所有职位(job)不是以PRESI开头的数据行
2、将这些数据行按照职位(job)分组
3、求出每一类的平均工资
4、最后求出这些平均工资的最小值、最大值
注意:分组函数只能嵌套两层
如果在一个查询中所使用的限制条件中包括了分组函数,则该限制条件必须放在HAVING子句中而不能放在WHERE子句中.
除了COUNT以外，其他分组函数都不处理NULLL


面试题:
一、取得每个部门最高薪水的人员名称
1.按照部门分组
2.根据部门、薪水查找到对应的人
二、哪些人的薪水在部门的平均薪水之上

三、取得部门中（所有人的）平均的薪水等级
四、不准用组函数（Max），取得最高薪水（给出两种解决方案）
五、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）


13.1 找出没选过“黎明”老师的所有学生姓名
    一、选择"黎明"老师的学号
        SELECT cno FROM C c WHERE c.CREACHTER = '黎明';
    二、排除学生
13.2 列出2门以上（含2门）不及格学生姓名及平均成绩。        
    一、查找成绩不及格的学生
    二、按照不及格成绩来分组
    三、sum来算每组的总数，avg来算平均成绩，HAVING 来算sum大于2

    select * from s join sc on s.sno = sc.sno
    group by
    
Oracle用连接(Join)来完成多表查询的.
始终类型的连接:
相等/内连接 Equi join
自连接 Self join 1、= 2、join on select * from t1  join t2 on t1.x=t2.y join t3 on t2.y = t3.y where ...
不等连接 Non-equijoin BETWEEN AND
外连接 Outer join
内连接:
SELECT empno,ename,sal,emp.deptno,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY loc;

自连接:
SELECT w.empno,w.ename,w.job,w.mgr.m.ename,m.job
FROM emp w,emp m
WHERE w.mgr = m.empno
AND w.job LIKE 'ANA%';

外连接:(+) 该连接运算符既可以放在等号的左边也可以放在等号的右边,但一定要放在缺少信息的那一面.
注意:在左边为左外连接、右边为右外连接
SELECT empno,ename,sal,emp.deptno,dept.deptno,loc
FROM emp,dept
WHERE emp.deptno(+) = dept.deptno;

SELECT empno,ename,sal,emp.deptno,dept.deptno,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno(+);

左外连接:
SELECT empno,ename,sal,emp.deptno,dept.deptno,loc
FROM dept
LEFT OUTER JOIN emp ON (emp.deptno = dept.deptno);

右外连接:
SELECT empno,ename,sal,emp.deptno,dept.deptno,loc
FROM dept
RIGHT OUTER JOIN emp ON (emp.deptno = dept.deptno);