Oracle学习日记

字段

EMPNO 员工编号 Ename 员工姓名 JOB 职位岗位 MGR 领导编号 HIREDATE 入职日期 SAL 工资 COMM 奖金 DEPTNO 部门编号

字段

DEPTNO 部门编号 DNAME 部门名称 LOC 地址

列别名

SELECT 列名1 AS "别名1",列名2 AS "别名2" FROM 表名;

AS、双引号可省略不写，如果别名为纯数字或者特殊符号，双引号不可省略

        简化后：

SELECT 列名1 别名1 FROM 表名;

表别名

SELECT 列名 FROM 表名 表别名;

别名命名规则：必须以字母开头；最好是缩写；表别名可以缩写也可以写为T_1 T_2

别名并没有改变原来的名字，只是当前语句使用（分号之前）

连接符||

SELECT  列名1||列名2 FROM 表名;

将两个或多个字符拼接到一起，变成一个字符

SELECT  列名1||'文本'||列名2 FROM 表名;

2. 条件查询

语法

SELECT */列名/计算/常量 FROM 表名 WHERE 过滤条件

常见的数据类型：数值、字符、日期

（1）逻辑运算

< >   <=   >=    =    <>/!=

 数值比较

查询工资大于2000的员工信息

字符比较

<>/!= 或 =

查询姓名是SMITH的员工信息

注意：数据区分大小写，字符要加单引号

日期比较 

查询入职日期在1981/1/1之前入职的员工信息

转换函数：将目标字符转换成想要的日期格式

to_date(数据，日期格式)

（2）空值判断

IS NULL 是空

IS NOT NULL 不是空

查询有奖金的员工信息（奖金不是空）

（3）包含查询

IN(集合)：在集合里面即满足条件

NOT IN(集合)：不在集合里面即满足条件

集合必须是同属性的数据放在一起

查询部门编号是10和20的员工信息

（4）模糊查询

LIKE '目标格式'：像目标格式即满足条件

NOT LIKE '目标格式'：不像目标格式即满足条件

目标格式：% 代表占有零或多个字节

_ 代表只占一个字节

查询姓名以s开头的员工信息

查找姓名是以A开头且倒数第二位是M的员工信息

查询姓名中有A的员工信息

（5）范围查询

BETWEEN 值1 AND 值2：在1和2之间即满足条件

NOT BETWEEN 值1 AND 值2：不在1和2之间即满足条件

查询工资在1000到3000之间的员工信息

注意：BETWEEN  AND 包含边界，小值写在前面，大值在后面

（6）ANY和ALL

ANY(集合)、ALL(集合)通常和<、>联用

>ANY(100,200) 大于集合中任意值即满足条件

>ALL(100,200) 大于集合中所有值即满足条件

（7）条件的连接

AND：并且，要同时满足两个条件

OR：或者，满足一个条件即可

查询10号部门的经理

查询10号部门的经理或者30号部门的销售

注意：AND优先于OR，有括号先执行括号

例题

查询薪资超过1000并且小于3000的员工信息（2种）
查询部门编号是10号或20号的员工信息（2种）
查询岗位是销售SALESMAN，并且奖金超过400的员工信息
查询20号部门的经理
查询所有20号部门的员工或岗位是MANAGER的员工信息
查询10号部门的部门经理或20号部门的分析师ANALYST
查询10号部门的员工、30号部门的经理及所有的分析师ANALYST

SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
SELECT * FROM EMP WHERE SAL>1000 AND SAL<3000;
SELECT * FROM EMP WHERE DEPTNO IN(10,20);
SELECT * FROM EMP WHERE DEPTNO='10' OR DEPTNO='20';
SELECT * FROM EMP WHERE JOB='SALESMAN' AND COMM>400;
SELECT * FROM EMP WHERE DEPTNO='20'AND JOB='MANAGER';
SELECT * FROM EMP WHERE DEPTNO='20'AND JOB='CLERK' OR JOB='MANAGER';
SELECT * FROM EMP WHERE (DEPTNO='10'AND JOB='MANAGER')OR(DEPTNO='20' AND JOB='ANALYST');
SELECT * FROM EMP 
WHERE DEPTNO='10'AND JOB='CLERK' OR DEPTNO='30' AND JOB='MANAGER' OR JOB='ANALYST';

3. 排序查询

定义：对查询结果按照规则排序

语法：

SELECT */列名/计算/常量/函数 FROM 表名 WHERE 过滤条件 
ORDER BY 字段 
升序:order by 字段 [asc];默认asc可省
降序:order by 字段 desc;

查询员工信息，结果按照工资升序排列

查询工资大于2000的员工，结果按部门升序，按工资降序

ORDER BY后可以写多个字段，先按第一字段排序，第一字段相同再按第二字段排序，列和列之间永远都是逗号；NULL值在排序的时候默认最大

查询员工姓名、薪资、佣金、薪资佣金合计，按薪资佣金合计值升序排列，薪资佣金以“总计” 二字显示

统计员工姓名及员工资历，并按资历排序，将老员工排在前，新员工排在后

4. 分组查询

定义：按照一定规则分组，统一分析各组情况，每组返回一个值

（1）聚合函数（分组函数）：先分组后聚合

max(字段) min(字段) avg(字段) sum(字段) count(字段/*/数字)

最大   最小  平均   求和   计数

查询emp表中所有员工的各种薪资情况

注意：空值不参与聚合函数的统计

统计

四个人有奖金，表中有14行数据，

注意：COUNT(字段)，统计非空值的个数；COUNT(*)，统计该组所有行数；COUNT(数字)，统计该组所有行数

（2）GROUP BY和HAVING

SELECT 字段1，聚合函数 GROUPT BY 字段1 HAVING 聚合函数;

按照字段分组，统一分析各组情况，每一组返回聚合函数的值

查询每个部门的平均工资

注意：在统计查询中，select子句后只允许出现分组字段和统计函数，其它非分组字段不能使用；GROUP BY 后能放多个字段，先按第一组分，相同情况下按第二组分；如果没有group by 子句，则在select子句中只允许出现统计函数，其它字段不允许出现；

统计函数可以嵌套使用，但是嵌套统计函数之后的select子句中不允许再出现别的字段，包括分组字段,且必须有group by。

错：select id,max(avg(sal))  from t_ls_rkxx group by id;
正：select max(avg(sal))  from t_ls_rkxx group by id;

查询每个部门的每个岗位的平均薪资

查询每个部门平均薪资大于2000的岗位

注意：WHERE后面不能使用聚合函数，对接分组前的条件；HAVING后只能使用聚合函数，对接分组后的条件，没有GROUP BY 就没有HAVING

例题：

查询薪资合计超过5000的岗位及其平均薪资

SELECT JOB,AVG(SAL),SUM(SAL) 
FROM EMP  
GROUP BY JOB 
HAVING SUM(SAL)>5000

查询部门人数超过4人的部门编号及薪资合计

SELECT DEPTNO,SUM(SAL) 薪资合计 
FROM EMP 
GROUP BY DEPTNO 
HAVING COUNT(EMPNO)>4;

查询各部门下，人数超过两人的岗位有哪些，显示部门编号、岗位名称

SELECT DEPTNO,JOB,COUNT(EMPNO) FROM EMP GROUP BY DEPTNO,JOB HAVING COUNT(EMPNO)>2;

查询姓名重复的员工的姓名

SELECT ENAME,COUNT(ENAME) 
FROM EMP 
GROUP BY ENAME 
HAVING COUNT(ENAME)>1;

5. 完整查询语句

SELECT */字段/常量/计算/函数
    FROM 表名
        WHERE 过滤条件 --分组前的过滤条件，不能使用聚合函数
            GROUP BY 字段（分组内容）
                HAVING 过滤条件 --分组后的条件，可以使用聚合函数
                    ORDER BY 字段 [ASC]（排序内容） --正序排列 
                                  DESC  --降序

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

注意：WHERE后面不能用别名

例题：

1.查询 1982年以前入职的员工，按部门、岗位分组，查出
  平均薪资超过800的部门和岗位类型及其平均薪资和最高薪资，按最高薪资降序排列

SELECT DEPTNO,JOB,AVG(SAL) 平均薪资,MAX(SAL) 最高薪资 
 FROM EMP 
  WHERE HIREDATE<TO_DATE('1982/1/1','yyyy/mm/dd')
   GROUP BY DEPTNO,JOB
    HAVING AVG(SAL)>=800
      ORDER BY MAX(SAL) DESC

2.查询部门人数超过3的部门的部门编号、最高薪资、最低薪资、部门人数，按部门人数升序排列

SELECT DEPTNO,MAX(SAL),MIN(SAL),COUNT(EMPNO) FROM EMP
 GROUP BY DEPTNO                             
  HAVING COUNT(EMPNO)>3
   ORDER BY COUNT(EMPNO)

3.查询岗位名称不以S开头、并且岗位平均薪资超过2000的岗位的岗位名称、岗位人数、岗位平均薪资,按岗位平均薪资升序排列

SELECT JOB,COUNT(EMPNO) 岗位人数,AVG(SAL) FROM EMP
 WHERE ENAME NOT LIKE'S%'
  GROUP BY JOB
   HAVING AVG(SAL)>2000
    ORDER BY AVG(SAL)

4.查询各部门部门编号、最高薪资、最低薪资，以及最高和最低薪资的差值，按部门编号降序排列

SELECT DEPTNO,MAX(SAL),MIN(SAL),MAX(SAL)-MIN(SAL) 薪资差值 FROM EMP
 GROUP BY DEPTNO
  ORDER BY DEPTNO DESC

5.统计岗位类型、最高薪资、最低薪资、薪资合计、平均薪资，以及人数，
  各字段以中文列名展示

SELECT JOB 岗位类型,MAX(SAL) 最高薪资,MIN(SAL) 最低薪资,SUM(SAL) 薪资合计,AVG(SAL) 平均薪资,COUNT(EMPNO) 人数 FROM EMP
       GROUP BY JOB

6.统计薪资在1000到3000之间的人员中，有佣金的员工总数及无佣金的员工总数，

SELECT COUNT(COMM) 有佣金的员工数,COUNT(*)-COUNT(COMM) 无佣金的员工数 FROM EMP
 WHERE SAL BETWEEN 1000 AND 3000

6.数值计算

SELECT 字段运算符 数值/字段 FROM 表名

注意：空值（NULL值）不参与计算，任何数和NULL值进行计算得到的还是NULL值

空值不参与逻辑比较

7.子查询

定义：在一个查询语句中嵌套一个或多个语句

注意：

• 子查询要在括号内，括号内子查询只返回一行
• 子查询放在比较条件右侧增强可读性
• 单行操作符对应单行子查询，多行操作符对应多行子查询
• 子和主查询可以不是一张表
• 子可以放在SELECT后面，只能使用单行单列或相关子查询
• 子查询的底层思路：

①单行单列子查询 

• (>,<,=,<>,>=,<=)内部SELECT子句只返回一行结果
• 子查询返回的是一个具体列的内容，可以理解为一个单值数据；

例题

 查询工资最低的员工

②单行多列子查询

• (>,<,=,<>,>=,<= in)内部SELECT子句只返回一行结果
• 子查询返回一行数据中多个列的内容；
•  多行多列子查询要使用多行操作符（字段，字段，...）

例题

查询工资和scott相同，岗位也和scott相同的员工信息

③多行单列子查询

• (all, any, in,not in)
• 子查询返回多行记录之中同一列的内容，相当于给出了一个操作范围；

• < any :小于最大值

  > any :大于最小值

  = any :等价于 in

• < all :小于最小值

  > all :大于最大值

  != all :等价于not  in

例题 

查询是领导的员工(mgr存的是员工的领导工号,如果他的empno在mgr里有位置那就说明他是领导)

查询不是领导的员工

为什么要排除空值？

在 Oracle 中，如果子查询返回的结果集中包含 NULL 值，那么主查询中使用 NOT IN 子句可能会导致结果为空。这是因为 SQL 中的 NULL 值比较特殊，它与任何值的比较都会返回 UNKNOWN，而不是 TRU        E 或 FALSE。使用NOT IN时不能对null操作

 查询工资比职位是 clerk 的员工中任意(某)一个低的员工信息(ANY)

查询工资比各部门平均工资都高的员工(ALL)

注：加上GROUP BY DEPTNO的作用是计算每个部门的平均工资。这是因为你想要比较员工的工资与其所在部门的平均工资。如果不加GROUP BY DEPTNO，子查询中只会返回整个表的平均工资，而不是每个部门的平均工资。

④多行多列子查询

• 查询返回的结果是一张临时表，返回满足条件的多个信息

例题  

  查询工作与7369相同且工资大于7876的员工信息（这个表信息太少只返回了一个）

查询工资高于平均工资的所有员工的员工号和工资

查询前三个工资最高的员工信息

• 在这个查询中，t.* 中的 t 是一个别名，它表示子查询结果的别名。在外部查询中，我们需要明确指定所选列来避免歧义，因为可能存在多个表或子查询。因此，t.* 表示选择子查询 t 中的所有列，这样就能够正确地指定要选择的列，并且保持查询的简洁性。
• rownum 是一个伪列，用于表示返回的行的行号。在这个查询中，rownum<4 表示只选择行号小于4的数据，即选择前三行数据。这样可以实现对结果集进行分页或者限制返回的行数。
• 子查询返回一个表，可以在FROM后

⑤相关子查询

• 子(里层的查询)不能单独运行
• 子(里层的查询)会牵扯主(外层的查询)的部分内容
• 主只在乎与子的关系

⑥非相关子查询

• 子能单独运行
• 子不会牵推主的内容
• 主只在乎子的结果