ORACLE数据库——SQL知识点2

ORACLE数据库  SQL语句基础知识点2  适合有SQL基础的人群。 禁止转载！

基本语法
  SELECT [*] <选择列表> 
  [FROM] <表资源> [,…n] 
  [WHERE] <行搜索条件> 
  [GROUP BY] <分组表达式> [,…n]
  [HAVING] <组搜索条件>
  [ORDER BY] <字段名[ASC|DESC]> [,…n]

执行顺序：FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY

FROM 子句

1.查询所有列
SELECT * FROM emp;

2.从表中选出指定的列
SELECT <选择列表> FROM <表名>
    eg. SELECT deptno,dname FROM dept;

3.显示行号
SELECT rownum,ename FROM emp;

4.显示年薪的工资清单。  ----    年薪(500+sal*12)
SELECT empno,ename,(500+SAL)*12 a FROM emp;     // 这里给年薪起了别名a    (500+SAL)*12  [as] a

5.消除重复行
SELECT DISTINCT deptno FROM emp;

WHERE子句

6.显示工资大于等于3000的雇员姓名、职务和工资
SELECT ename,job,sal FROM emp, WHERE sal >=3000;

7.显示职务为“SALESMAN”的雇员的姓名、职务和工资
SELECT ename,job,sal FROM emp WHERE job = 'SALESMAN';

8.显示工资在1000～2000之间(不包括1000和2000)的雇员信息
SELECT * FROM emp WHERE sal>1000 AND sal<2000;

9.显示职务为CLERK或MANAGER的雇员信息。
SELECT * FROM emp WHERE job='CLERK' OR job='MANAGER';

10.显示部门10以外的其他部门的雇员。
SELECT * FROM emp WHERE NOT deptno = 10;

11.显示部门10和部门20中工资小于1500的雇员。   
SELECT * FROM emp WHERE sal < 1500 AND (deptno = 20 OR deptno = 10);        //可以使用括号优先级

12.显示工资在1500～2900之间的雇员信息。
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 2900;

列表查询条件 可以显示值满足特定集合的结果  [NOT] IN (...)

13.显示职务为 “ SALESMAN' ，“ CLERK” 和“MANAGER”的雇员信息。
SELECT * FROM emp WHERE job IN ('SALESMAN' ,'CLERK','MANAGER');         

模式查询条件  模糊查询    

        ---------   [NOT] LIKE 匹配模式     %：代表0个或多个任意字符。    _：代表一个任意字符

14.显示姓名以“S”开头的雇员信息。
SELECT * FROM emp WHERE ename LIKE 'S%';

15.显示姓名第二个字符为“A”的雇员信息。
SELECT * FROM emp WHERE ename LIKE '_A%';

16.显示姓名包含“_A%”的雇员信息。      //  使用转义（escape）操作符 / 可以完成此任务
SELECT * FROM emp WHERE ename LIKE '%\_A\%%';

判断空值NULL      

                ------      IS [NOT] NULL
17.显示经理编号mgr没有填写的雇员。
SELECT ename,mgr FROM emp WHERE mgr IS NULL;

ORDER BY 子句

                --------   对数据进行排序   升序(ASC)   降序(DESC)      缺省值---ASC
  
19.查询雇员姓名和工资，并按工资从低到高排序。
SELECT ename,sal FROM emp ORDER BY sal;

20.查询雇员姓名和工资，并按工资从高到低排序。
SELECT ename,sal FROM emp ORDER BY sal DESC;

可以按照字段名、计算出的值或表达式进行排序

21.按年薪排序。    ----    年薪(500+sal*12) 
SELECT empno,ename,(500+sal*12) as 'annual salary' FROM emp ORDER BY 'annual salary' DESC;        // 这里的as可省略

ORDER BY 子句中指定的列并不一定要出现在选择列表中

22.按照雇员表中的工资列进行降序排序，并将结果仅显示姓名：
SELECT ename FROM emp ORDER BY sal DESC;

23.查询雇员信息，先按职位由A到Z排序，再按工资由高到低排序。    ----------     按照多个列排序
SELECT ename,job,sal FROM emp ORDER BY job asc,sal desc;       //这里的asc可以省略

在 ORDER BY 子句中引用某字段在选择列表中的位置（数字）而进行排序

24.查询雇员信息，先按职位由A到Z排序，再按工资由高到低排序。
SELECT ename,job,sal FROM emp ORDER BY 2, 3 desc;       //这里的asc可以省略

GROUP BY子句  -------分组查询

1.组处理函数（聚合函数）                    使用distinct关键字，则取消指定列名中的重复值
sum([all|distinct]表达式)                ------返回数字表达式中所有值的和
avg([all|distinct]表达式)                  ------返回数字表达中所有值的平均值
count([all|distinct]表达式)               ------返回表达式中值的个数  统计
count(([all|distinct] *)                      ------返回选定的行数  统计
max(([all|distinct]表达式)                ------返回表达式中的最高值
min(([all|distinct]表达式)                 ------返回表达式中的最低值

注意：    

    1. 聚合函数只能出现在选择列表、order by子句、having子句中，而不能出现在where 子句和group by子句中。
    2. 如果在选择列表中同时包含列、表达式和聚合函数，则这些列、表达式都必须出现在group by子句中。
    3. SQL Server 的聚合函数（COUNT(*) 除外）将忽略字段中的空值。

25.求雇员总人数。
SELECT COUNT(*) FROM emp;

26.求由经理管理的员工人数。  mgr---每个人对应的经理编号
SELECT COUNT(mgr) FROM emp;

27.求雇员表中不同职务的个数。
SELECT COUNT(DISTINCT job) FROM emp;

28.求部门10的雇员的平均工资和工资总和。
SELECT AVG(sal) 'average salary', SUM(sal) 'sum salary' FROM emp WHERE deptno = 10;

29.求最晚和最早雇佣的雇员的雇佣日期。
SELECT MIN(hiredate) 'earliest hired', MAX(hiredate) 'latest hired' FROM emp;

******使用GROUP BY时，group_list中的每一列都必须出现在select_list中。*********

30.按职务统计工资总和。
SELECT job,SUM(sal) FROM emp GROUP BY job;

31.查询按职位（job）分类的每类员工的平均工资，并且按平均工资由大到小的顺序排列。
SELECT job,AVG(sal) 'avg salary' FROM emp GROUP BY job ORDER BY 'avg salary' DESC;

*******如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须在group by子句中*******

32.按部门和岗位分组统计工资总和。
SELECT deptno,job,SUM(sal) FROM emp GROUP BY deptno,job;

33.Select job,avg(sal)  from emp group by job order by deptno;
请问上述的查询语句中是否有错误？如果有错误，错在什么地方？    
答：    错误：deptno不在分组函数中，也没有出现在group by子句  
    修改方法：Select job,avg(sal)  from emp group by job, deptno order by deptno;

HAVING子句

-----对分组查询的结果进行过滤，要使用HAVING从句。HAVING 从句过滤分组后的结果 ， 它只能出现在GROUP BY从句之后

34.统计各部门的平均工资，排除平均工资小于3000的部门。
SELECT AVG(sal) FROM emp GROUP BY deptno HAVING avg(sal)>=3000;

35.按职务统计平均工资并排序。
SELECT job 职务,AVG(sal) 平均工资 FROM emp GROUP BY job ORDER BY  avg(sal);

36.求各部门平均工资的总和。
SELECT SUM(AVG(sal)) FROM emp GROUP BY deptno;

多表连接查询  -------  相等连接(简单连接或内连接)、自连接、不等连接、外连接

1. 相等连接     -------     通过两个表具有相同意义的列，可以建立相等连接条件。
        ①n个表的连接需要至少n-1个连接条件
        ②如果多个表中有相同列名的列时，在这些列的前面要冠以表名来区别它们，表名和列名之间用逗号隔开。
        ③可以使用表的别名，但是别名一经定义，在整个的查询语句中就只能使用表的别名而不能现使用表名。

37.显示雇员的名称和所在的部门及其具体地点。
SELECT e.ename,d.deptname,d.loc FROM emp e, dept d WHERE e.deptno = d.deptno;

38.显示工资大于3000的雇员的名称、工资和所在的部门名称。
SELECT ename,sal,deptname FROM emp,dept WHERE emp.deptno = dept.deptno AND sal>3000;

2.自连接       -------       自连接就是一个表，同本身进行连接。自己连接自己。

39.显示雇员名称和雇员的经理名称。
SELECT worker.ename || '的经理是' || manager.ename AS 雇员经理 FROM emp worker,emp manager WHERE worker.mgr = manager.empno;

3.不等连接       -------       连接运算符不是等号的所产生的连接

salgrade表      -----Grade 表示工资等级，losal和hisal分别表示某等级工资的下限和上限。

DESC salgrade;
名称                   是否为空             类型
-------------        -----------------    -----------------
GRADE            (默认为空)         NUMBER
LOSAL                                       NUMBER
HISAL                                        NUMBER

40.显示雇员名称，工资和工资所属等级。
SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

4.外连接       -------     外连操作符为(+)，它可以出现在相等连接条件的左侧left join或右侧right join。     +号放在缺少相应信息的那一面。

使用外连接显示不满足相等条件的记录。    (***注意select列表中有2个deptno)
SELECT empno,ename,sal,emp.deptno,dept.deptno,loc FROM emp,dept WHERE emp.deptno(+) = dept.deptno;      // emp 表为左表，dept 表为右表。 连成完整的表
SELECT empno,ename,sal,emp.deptno,dept.deptno,loc FROM emp,dept WHERE dept.deptno = emp.deptno(+);

5.使用join…..on子句的连接

1）内连接 (join.. on )         (***注意select列表中有2个deptno)
SELECT empno,ename,sal,emp.deptno,dept.deptno,loc FROM emp JOIN dept IN(emp.deptno = dept.deptno) ORDER BY dept.loc;

2)左外连接 (left outer join .. on)         (***注意select列表中有2个deptno)
SELECT empno,ename,sal,emp.deptno,dept.deptno,loc LEFT OUTER JOIN emp ON (emp.deptno=dept.deptno);

3)右外连接(right outer join ..on)         (***注意select列表中有2个deptno)
SELECT e.empno,e.ename,e.sal,e.deptno,d.deptno,d.loc FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno=d.deptno);

以上显示结果均一样，只是写法不同。

6.子查询     -------     第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中

1) 单行子查询
41.查询比SCOTT(工号7788)工资高的雇员名字和工资。
SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE empno = 7788);

42.查询和SCOTT同一部门,但工资不超过ADAMS的所有员工的名字和工资。
SELECT ename,sal FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE empno = 7788) AND sal <= (SELECT sal FROM emp WHERE ename = 'ADAMS');

43.查询工资高于平均工资的雇员名字和工资。      -------    在子查询中也可以使用组函数。
SELECT ename,sal FROM emp WHERE sal > (SELECT avg(sal) FROM emp);

2) having子句中的单行子查询
44.平均工资高于最低平均工资（按职位分类）的所有职位的最低工资、平均工资和最高工资名单（除总裁）。
    SELECT job,min(sal),avg(sal),max(sal) 
    FROM emp 
    WHERE job not like 'presid%' 
    GROUP BY job 
    HAVING avg(sal) > (SELECT min(avg(sal)) FROM emp GROUP BY job);

3) FROM从句中使用子查询
45.除文员外，所有工资高于所任职位的平均工资的员工信息。
SELECT e.empno,e.ename,e.job,a.avgsal FROM emp e,(SELECT job,avg(sal) avgsal FROM emp GROUP BY job) a 
WHERE e.job = a.job AND e.sal > a.avgsal and NOT e.job = 'CLERK';

7.多行子查询     -------   子查询返回多行的结果   多行子查询要使用不同的比较运算符号，它们是IN、ANY和ALL

1) 使用[not] in操作符的多行子查询
46.查询除了文员(CLERK)和他自己（PRESIDENT）外，哪些人的工资与各职位（job）最高工资相同。
SELECT empno,ename,job,sal FROM emp WHERE sal in (SELECT max(sal) FROM emp GROUP BY job) and NOT job = 'CLERK' AND job not like 'PRES%';

2) 使用all操作符的多行子查询
47.查询员工的工资比所有职位的平均工资还低的名单。
SELECT empno,ename,job,sal FROM emp WHERE sal < all (select avg(sal) from emp group by job);

*******All操作符比较子查询返回列表中的每一个值。<all为小于最小的;  >all为大于最大的;  =all返回“未选定行”。*******

3) 使用any操作符的多行子查询
48.除文员外，哪些员工的工资比最低的平均工资高（按职位）？
SELECT empno, ename, job,sal FROM emp WHERE sal > any (SELECT avg(sal) FROM emp GROUP BY job) AND job <> 'CLERK';

*******Any操作符比较子查询返回列表中的每一个值。<any为小于最大的；>any为大于最小的；=any返回任意值，与IN等效。*******

8.相关子查询        -----------   EXISTS
49.显示工作在'NEW YORK'的雇员信息。
SELECT  ename,deptno,sal ,job FROM emp WHERE EXISTS (SELECT 'x' FROM dept WHERE dept.deptno = emp.deptno AND dept.loc = 'NEW YORK');

9.多列子查询
50.显示与SMITH部门和岗位完全相同的所有雇员的信息
SELECT deptno,ename,job,sal FROM emp WHERE (deptno,job) = (SELECT deptno,job FROM emp WHERE ename= 'SMITH');

10.多列子查询
51.显示岗位或管理员匹配于部门编号为10的所有雇员的信息
    SELECT deptno,ename,job,sal FROM emp 
    WHERE job in (select job from emp where deptno = 10) 
    OR mgr in (select mgr from emp where deptno =20)
    ORDER BY deptno