ORACLE数据库(一)-----基础操作

前言

最近正在学习ORACLE数据库的操作

一、简单查询

1 语法

SELECT COL_NAME1,COL_NAME2 FROM  TB_NAME

2 常量

数值型：查询结果右对齐，并且以最简的形式显示

字符型：查询结果左对齐

• SYSDATE 获取系统当前时间

SELECT '员工姓名：',ENAME FROM EMP; -- 字符型
SELECT 123.45,DEPTNO FROM EMP;    -- 数值型
SELECT SYSDATE,HIREDATE FROM EMP; -- 日期型数值常量  一般不能直接写出
SELECT '2021/03/08' FROM EMP;  -- 字符型数据表示日期
SELECT 2021/01/01 FROM EMP;  -- 除法计算

3 连接符

|| 可以代替逗号，将查询结果中的多列合成一列

例子

SELECT '员工'||ENAME||'薪资是：每月'||SAL||'元' FROM EMP; -- 代替逗号，只有一列

结果

4 别名

要么不取别名；要么取了就要用

别名中不能使用特殊字符，如要使用 可以用双引号引起来，并不建议这样做

• 列别名：在查询结果中用别名代替本名

  SELECT COL_NAME (AS) 别名 FROM TB_NAME; 
  -- 1. 可以是中文，但是一般不建议 
  -- 2. AS可省略
  

• 表别名：在表明后面直写一个别名

  SELECT COL_NAME FROM TB_NAME TN;
  -- TN即为表的别名
  

例子

-- 列别名
SELECT ENAME AS 姓名,JOB AS 工作,SAL AS 薪资 FROM EMP;

-- 表别名
SELECT T.ENAME FROM EMP T;

-- 强制使用特殊字符作为列别名    非常不建议
SELECT ENAME "E-NAME" FROM EMP;

结果

5 补充

• SELECT子句中，如果出现号，以及号之外的其他字段，*必须添加表名(别名)指向

• 引号问题

  双引号用来引起列名

  单引号用来引起字符型数据

  单纯的列名不需要加引号

  数据要么加单引号，要么不加引号

  列的名字要么加双引号，要么不加引号

  SELECT 'ENAME' AS "ENAME",ENAME ename,ENAME "ename" FROM EMP;
  

  结果
  

• || 和 CONCAT()函数

  ORACLE中 || 可以连接多个字段，CONCAT(参数1,参数2) 只能连接两个字段

二、条件查询

1 语法

SELECT 子句影响列数 WHERE子句影响行数

SELECT 字段名 FROM 表名 WHERE 条件

2 条件查询七种情况

• 逻辑运算

  1. 逻辑运算符：= ，>，<， >=，<=，<> 和!= 表示不等于
  2. 非相关条件：与表中的数据无关 只影响输出的行数

  SELECT * FROM EMP WHERE 1=1; --恒成立
  SELECT * FROM EMP WHERE 1=0; --恒不成立
  

  注：

  1. 空值不参与运算比较

  2. 数值型：单纯的数值上进行比较

  3. 字符型：比较字符对应的ASCII码

  4. 常用的ASCII码值 已经标红
     

  5. 两个函数

  ASCII()：查找字符对应的码值

  CHR()： 查找码值对应的字符

  日期条件查询

  使用日期查询，不能手写日期，必须把字符串转化为日期格式，或者把日期转化为字符串才能进行比较

  字符串转日期：TO_DATE(‘字符串’,‘与字符串对应的日期格式’)
  日期转字符串：TO_CHAR(‘日期’,‘日期转成字符串的目标格式’)

  例子

  -- 查询1980年12月17日入职的员工是谁
  SELECT * FROM EMP WHERE HIREDATE = TO_DATE('1980/12/17','YYYY/MM/DD');
  SELECT * FROM EMP WHERE HIREDATE = TO_DATE('19801217','YYYYMMDD'); -- 后面格式向前面格式看齐
  
  SELECT HIREDATE,TO_CHAR(HIREDATE,'YYYY/MM/DD') FROM EMP; -- 前边的日期向后面格式看齐
  SELECT HIREDATE,TO_CHAR(HIREDATE,'YYYYMMDD') FROM EMP; -- 前边的日期向后面格式看齐
  

  思考：第二行语句查询结果比第一行多了好多

  SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYYMMDD') < '19810501';
  SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY-MM-DD') < '1981/05/01';
  因为 - 的ASCII码值比 /的小
  

• 模糊查询

  SELECT 字段 FROM 表名 WHERE 字段 LIKE '';
  SELECT 字段 FROM 表名 WHERE 字段 NOT LIKE '';
  

  1 LIKE 后面跟的内容

  % 零个/多个
  _ 只能是一个 (占位符)
  %S% 包含S ，%S 最后一位是S ，S% 首字母是S

  用法

  查找姓名中含有两个AA的员工信息
  SELECT * FROM EMP WHERE ENAME LIKE '%A%A%';
  
  查找姓名中含有A字母和E字母的员工信息
  SELECT * FROM EMP WHERE ENAME LIKE '%A%E%' OR ENAME LIKE '%E%A%';
  

  2 转义字符

  对于 % _ ’ 这样的字符 在查询的时候需要转义

  %/%% 表示含有%的字段

  %''% 表示含有单引号的字段

  转义字符随便设置   ESCAPE后面的内容可以随意设置
  SELECT * FROM EMP WHERE ENAME LIKE '%/%%' ESCAPE '/'; -- 含有%的字符
  SELECT * FROM EMP WHERE ENAME LIKE '%^%%' ESCAPE '^'; -- 含有%的字符
  SELECT * FROM EMP WHERE ENAME LIKE '%/_%' ESCAPE '/'; -- 含有_的字符
  查找名字中含有单引号的员工  
  SELECT * FROM EMP WHERE ENAME LIKE '%''%';  -- 使用两个单引号转义成一个单引号
  

• 包含查询

  SELECT 字段 FROM 表明 WHERE 字段名 IN (集合)
  SELECT 字段 FROM 表明 WHERE 字段名 NOT IN (集合)
  -- 集合有相同的属性，相同的数值类型
  -- NULL 可以和字符数值日期随意搭配
  

  例子

  -- 集合中多个成员指的是多行 而非多列
  SELECT * FROM EMP WHERE (ENAME,JOB) IN (('SMITH','CLERK'),('ALLEN','SALESMAN'));
  

• 范围查询

  SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN NUM1 AND NUM2
  -- 包含 NUM1 和NUM2
  -- NUM1 <= NUM2
  

  例子

  -- 4.查询1981年入职的人
  SELECT * FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19810101','YYYYMMDD') AND 
           TO_DATE('19811231','YYYYMMDD');
  

• NULL值判断

  SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL ; 
  --空值
  SELECT 字段名 FROM 表名 WHERE 字段名 IS NOT NULL ;
   --非空值
  

  只能用IS NULL 或者IS NOT NULL 判断空值，不能使用逻辑运算

  例子

  -- 查找无奖金资格的员工信息
  SELECT * FROM EMP WHERE COMM IS NULL;
  

• ANY和ALL

  = ANY(集合)
  > ANY(集合) 大于最小值
  < ANY(集合) 小于最大值
  = ALL(集合)
  > ALL(集合) 大于集合中的最大值
  < ALL(集合) 小于集合中的最小值
  

  例子

  SELECT * FROM EMP WHERE DEPTNO = ANY(10,20); -- 等于集合中的任意一个值 类似于IN
  SELECT * FROM EMP WHERE DEPTNP = ALL(10,20); -- 等于集合中的所有的值  
  SELECT * FROM EMP WHERE SAL > ANY(1000,2000,3000); 
  

• 布尔连接

  AND(和 且) OR(或者)

  注：AND 优先级高于 OR

  查询10号部门的部门经理或20号部门的分析师ANALYST
  SELECT * 
    FROM EMP 
   WHERE (DEPTNO = 10 AND JOB = 'MANAGER') OR (DEPTNO = 20 AND JOB = 'ANALYST');
   -- 优先级：AND的优先级大于OR 有括号的优先运行括号内的
  

3 小结

1 任何一个字段和空值做任何比较得到的都是空值

2 IN(集合) 含有NULL 会被忽略，将非空的数据取出

3 NOT IN(集合) 含有空值时，会导致最终结果没有数据取出

SELECT * FROM EMP WHERE DEPTNO NOT IN (10,20,NULL);
-- 等价于
SELECT * FROM EMP WHERE DEPTNO <> 10 AND DEPTNO <> 20 AND DEPTNO <> NULL; -- 因任何一个字段和空值做任何比较得到的都是空值

三、排序查询

1 语法

SELECT COL_LIST FROM TB_NAME ORDER BY ODR_CONDITION [ASC|DESC]; -- ASC升序(默认是升序) DESC降序 
语法解释：
对于从TB_NAME中查出的COL_LIST数据按照规则ODR_CONDITION进行排序

2 排序规则

升序、降序、同升同降、升降混合

在ORDER BY 中越写在前面 排序时优先级越高

例子

SELECT ENAME,JOB,DEPTNO,SAL FROM EMP ORDER BY DEPTNO DESC,SAL ASC;-- 部门降序 薪资升序
SELECT ENAME,JOB,DEPTNO,SAL FROM EMP ORDER BY SAL ASC,DEPTNO DESC;

3 排序内容

原字段
表达式（函数、计算）
代号（字段别名、字段序号）-- 不常用 不用

例子

SELECT * FROM EMP ORDER BY UPPER(ENAME);  -- 使用函数
SELECT * FROM EMP ORDER BY UPPER(ENAME) DESC;
SELECT * FROM EMP ORDER BY UPPER(ENAME),ENAME;

注： 用来排序的字段不一定出现在SELECT子句中

四、分组查询

1 语法

SELECT COL_LIST,聚合函数,常量
  FROM TB_NAME
 GROUP BY COL_LIST;

2 聚合函数

概念：把分散的数据按某种规则将数据分成不同的组、或所有数据整体作为一个组，对组级别的数据进行分析，每组得到一个返回值。
五个聚合函数：
SUM():求合计值
MAX():求最大值
MIN():求最小值
AVG():求平均值
COUNT():计数

空值不参与聚合函数分析

-- 查询10号部门的薪资合计、最高薪资、最低薪资、平均薪资以及部门总人数
SELECT SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL),COUNT(ENAME) FROM EMP WHERE DEPTNO = 10; 
-- 查询整个公司的平均奖金（仅考虑有奖金的人）
SELECT AVG(COMM) FROM EMP WHERE COMM IS NOT NULL;
-- 查询整个公司的平均奖金（考虑公司的所有人）
SELECT AVG(COMM) FROM EMP;
SELECT AVG(NVL(COMM,0)) FROM EMP;   -- 考虑公司所有人

3 注意

1 所有聚合函数都是针对非空值进行的统计

2 COUNT()的特殊用法：若是在不确定哪些字段不含空值的情况下统计总数据量，可以使用COUNT(1)或COUNT(*)来统计

3 SUM和AVG只针对数值，其他三个可针对任意类型

4 关于GROUP BY 分组

-- GROUP BY 分组  
GROUP BY  可以当作是一种去重的方式
GROUP BY 之后的字段没有优先级，字段放在前还是后 不影响最终的分组结果
1.对于含有GROUP BY子句的SQL语句，SELECT子句中仅允许出现
  1）GROUP BY中出现过的字段
  2）聚合函数
  3）常量
2.对于不含有GROUP BY子句但SELECT子句中含有聚合函数的情况，SELECT子句中除聚合函数与常量外，不能再出现其他字段

五、完整的查询语句

1 语法

SELECT COL_LIST
  FROM TB_NAME
 WHERE CONDITION
 GROUP BY COL_LIST
HAVING CONDITION
 ORDER BY COL_LIST

2 执行顺序

FROM ... WHERE... GROUP BY ... HAVING ... SELECT ... ORDER BY ...

六、总结

1 NULL值 和CASE WHEN

1)NULL值的默认排序与强制排序
  NULL值默认排序为极大值，即升序排列时，空值排在最后，降序排列时，空值排在最前
  强制空值排在最前：NULLS FIRST  -- 放在ASC 和 DESC后面
  强制空值排在最后：NULLS LAST
  
2)函数CASE -- 根据不同的情况 返回不同的值   
  用法: -- CASE WHEN 中后一个条件被判定的前提是前一个判定结果不成立
CASE WHEN CONDITION1 THEN RESULT1
     WHEN CONDITION2 THEN RESULT2
     ...
     ELSE DEFAULT_RESULT

例子

-- 5.查询员工姓名、岗位及岗位类型 管理岗放在最前面
-- （行政岗：办事员 CLERK
--   管理岗：经理/总经理  MANAGER  
--   技术岗：分析师  ANALYST
--   销售岗：销售） SALESMAN
SELECT ENAME,JOB,
       CASE WHEN JOB = 'CLERK' THEN '行政岗'
            WHEN JOB IN ('MANAGER','PRESIDENT') THEN '管理岗'
            WHEN JOB = 'ANALYST' THEN '技术岗'
            ELSE '销售岗'
       END 岗位类型
  FROM EMP ORDER BY 
       CASE WHEN JOB = 'MANAGER' THEN 1
            ELSE 2
        END; 

2 WHERE和HAVIN区别和联系

1.都是做条件筛选用的
2.WHERE不必和GROUP BY连用，HAVING必须和GROUP BY连用
3.WHERE筛选的是针对FROM后表的数据，HAVING是针对GROUP BY分组之后的数据进行筛选
4.WHERE中的部分条件可以写在HAVING中，但非常影响执行效率，不建议使用
5.只有在GROUP BY子句中出现的字段，才能够由WHERE子句中转写到HAVING子句中

3 扩展函数

NVL(待处理值，预定值) ：将空值处理成预定值 预定值数据类型与待处理值相同
NVL2(表达式1,表达式2,表达式3) ： 如果表达式1为空，返回值为表达式3的值

SELECT COMM,NVL(COMM,0) FROM EMP;

结果

4 一个问题

哪些聚合函数可能出现空值，哪些聚合函数不可能出现空值?