oracle系列笔记(1)---查询数据

时间：2022-05-14 20:00:41

查询数据

1. 查询（select .. form ..）   

(1)普通查询

select * from employees --代表查询employees表中所有数据

select last_name, job_id from employees--查询特定的列

select email,salary*10+100 from employees --运用+-*/运算符

/*

  空值（null）与对空值的处理

    空值是无效的未指定的未知的不可预知的值

    空值不是指空格也不是0

    包含空值的数学表达式都是空值

*/

(2)列的别名

规则：重命名一个列，紧跟在列名之后 ，关键字as  也可以省略

*/

select salary*(commission_pct+12) as yearsalary ,commission_pct jiangjing from employees

/*

 通过as对列明重新命名，也可以不写as中间不加逗号。

  commission_pct中有空值，包含空值的数学表达式都是空值，比如：100*(null+12),结果不是0，不是1200，而是null

*/

(3) 使用连接符（连接字符串或列)

select first_name||'is a like'||last_name as names from employees

/*

类如：first_name的一行为zhang， last_name一行为san

那么通过||,那就代表他们列为一列，两列中的内容合并为zhangsan，列名改为names

*/

select first_name||'is a like'||last_name as names from employees

--相当于变成了zhangis a likesan，中间添加固定字符串，这里只能是单引号

(4)删除相同的行

select distinct department_id from employees

/*

有相同的id，那么只显示一个，这并不是真正删除数据，只是你看到的是没有重复的数据

*/

2.过滤(select...from... where...)

 注意：Where：使用WHERE 子句是不能使用别名，因为where的执行顺序优于select

--查询部门编号为90号所有员工id，员工工资，员工姓名

select employee_id,salary,last_name

from employees

where department_id=90
/*处理字符串需要注意的：

    字符和日期必须出现在单引号中

    字符大小写敏感日期格式敏感

    默认的日期格式 DD-Mon-RR

*/

select employee_id,salary,last_name

from employees

where last_name='Weiss'

3.运算符

BETWEENAND（包含边界）;  IN(包含); like（像）

WHERE salary BETWEEN  2500 AND 3100; --代表工资在2500和3100之间，包括左右

WHERE  manager_id in (100,101,201);  --只包含100,101,201的部门

WHERE first_name like 'S%';          --只要是s开头的first_name都能查询到

/*

LIKE

1    使用LIKE运算符选择类似的值

2    选择条件 可以包括字符或者数字

3    % 代表的0个或者多个字符(任意个字符)

4    _代表一个字符

*/
--筛选出 last_name 首字母任意字母 第二个字母必须是o 后方任意

SELECT first_name,last_name,salary

FROM employees

WHERE first_name like '_o%';

对空值（null）的处理：is null来判断当前是否为空

--得到当前公司的老板是谁？

SELECT first_name,last_name, manager_id

FROM employees e

WHERE  manager_id IS NULL;

逻辑运算符:  AND  OR  NOT

WHERE salary>=10000 AND job_id LIKE '%MAN%' --AND是并关系

WHERE salary>=10000 OR job_id LIKE '%MAN%'  --or或关系

WHERE job_id NOT IN('MK_MAN','AD_VP','ST_MAN','SH_CLERK')--NOT IN 不包含关系

3.排序(ORDER BY)

ASC：升序排序(可以省略  默认), DESC:降序排序.  ORDER BY子句 必须在SELECT语句结尾处

排序规则：

1)可以按照select出现的列名排序

2)按照列名的别名排序

3)可以按照select语句中的列名的顺序值排序

--查找部门为101，的员工的姓名,工资，同时工资按升序排序,如果工资相同员工编号按降序排序

SELECT employee_id ,last_name,salary as ids, manager_id

FROM employees e

where manager_id=101

order by ids,employee_id DESC

4.oracle单行函数

oracle系列笔记(1)---查询数据

oracle系列笔记(1)---查询数据

--trim 没有任何参数默认去除首尾空格

select trim('   hello  world  ') from dual;

--指定c2参数 去除头尾

select trim ('o' from 'oHello oWorldoo') from dual;

--指定leading参数 去除头部

select trim(leading 'W' from 'WWhat is this W w W')from dual; //两个ww都去除了

--指定trailing 去除尾部

select trim( trailing 'W'from 'WWhat is this W w W')from dual;//之去除最后一个W，小w不去除

数字函数

 (1)ROUND: 四舍五入  例：ROUND(45.926, 2) ，结果45.93

(2) TRUNC: 截断 例：TRUNC(45.926, 2) ，结果45.92TRUNC(364, 25，-2) ，结果300

 (3)   MOD: 求余 例：MOD(1600, 300) ，结果100

--日期  sysdate是指当前系统时间，trunc没有说明的话默认保留0位

select first_name, trunc((sysdate-hire_date)/7) as week

from employees e

where department_id=90;

通用函数 这些函数适用于任何数据类型，同时也适用于空值：

NVL (expr1, expr2)，--函数将空值转换成一个已知的值

select first_name,salary, nvl(commission_pct,0)  from employees  --把没有奖金的空值变成0,没有新建列

NVL2 (expr1, expr2, expr3) :-- expr1不为NULL，返回expr2；为NULL，返回expr3

select first_name ,commission_pct,nvl2(commission_pct,'','') from employees --这个会新建列，如果为空，为0，如果不为空则为1，也可以把“1”改成 commission_pct，结果和nvl一样，但它新建列了。

NULLIF (expr1, expr2) :  --相等返回NULL，不等返回expr1

--COALESCE：COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值

select first_name,commission_pct,salary,coalesce(commission_pct,salary,10) comm from employees

--()中可以一直写

条件表达式：

--CASE：

select  e.first_name,e.last_name,e.salary ,e.job_id,

case e.job_id   when  'IT_PROG' then 2.2*salary

              when 'ST_MAN'   then 1.3*salary

              when 'HR_REP'    then 1.2*salary

               else e.salary

               end  "REVISED_SALARY"

from employees e;

--DECODE 函数

select last_name ,job_id,salary,

decode(job_id, 'IT_PROG',2.2*salary,

               'ST_MAN',1.3*salary,

               'HR_REP',1.2*salary,

               salary) REVISED_SALARY

from employees e;    --效果和上面作用是一样的，只是格式简单点

5.分组函数（多行函数）

什么是分组函数:分组函数作用于一组数据，并对一组数据返回一个值。

类型：AVG 平均值， COUNT 数量，MAX 最大值，MIN 最小值，SUM总和

--组函数忽略空值：

1) select  avg(commission_pct) from  employees;

在组函数中使用NVL函数，NVL函数使分组函数无法忽略空值。

2)select  avg(nvl(commission_pct,0)) from  employees;

--上面两组得出的结果是不一样的，第一个空值不计算，所以行数也不包括,下面一组，空值变成了0，那这一行也算一行了，所以平均值会小点

分组数据(GROUP BY)

select department_id,job_id ,avg(salary)

from employees

group by  department_id, job_id --先根据部门排序，在部门里有效的部门在排序

order by department_id

l 非法使用组函数

(1)所有包含在select列表中 而未包含在组函数中的 必须包含在Group by 子句中

(2) 不要在where子句中 使用组函数

(3) 使用HAVING子句中可以使用组函数

--求员工的工资 高于平均工资的所有员工

select first_name,avg(salary),salary

from  employees

where salary>avg(salary) ----这个是错误的 不能在where子句中使用组函数

过滤分组（Having）

使用HAVING 子句过滤分组

  1. 行数据已经被分组
  2. 使用了组函数
--部门工资比10000高的

select department_id,max(salary),avg(salary)  from employees

group by department_id

having max(salary)>10000  --having语句是可以对分组在刷选的，只要符合逻辑咯

组函数嵌套

--最多嵌套两次

select  max(avg(salary))--多嵌套也没有意义了，第二次嵌套就剩下一个值了

from employees

group by department_id

6.集合运算  UNION/UNION ALL 并集 INTERSECT 交集  MINUS 差集

UNION运算符返回两个集合去掉重复元素后的所有记录。

UNION ALL 返回两个集合的所有记录，包括重复的。

select employee_id,job_id

from employees

Minus     --返回属于第一个集合(上面），但不属于第二个集合的记录

select  employee_id,job_id

from job_history

这篇文件就讲到这里，有不足之处欢迎大家留言指点！

相关文章