SQL(三)

SQL(三)

9.2.6 数字函数

• ROUND 四舍五入

  • round(arg1,arg2)

    • arg1 :表示要进行四舍五入操作的数字
    • arg2 :第二个参数表示保留到哪一位

  • 保留到小数点后面 2 位

    select round(45.926,2)    from dual;   45.93
    

  • 保留到个位 (个十百千万…)

    select round(45.923,0)    from dual;  46
    

  • 保留到十位 (个十百千万…)

    select round(45.923,-1)    from dual; 50
    

• TRUNC 截取到某一位

  • trunc(arg1,arg2) 和 round 的用法一样,但是 trunc 只舍去不进位

  • 截取到小数点后面 2 位

    select trunc(45.929,2)    from dual;  45.92
    

  • 截取到个位 (个十百千万…)

    select trunc(45.923,0)    from dual;  45
    

  • 截取到十位 (个十百千万…)

    select trunc(45.923,-1)    from dual;  40
    

• MOD 取余

  • mod(arg1,arg2)

    • 第一个参数表示要进行取余操作的数字
    • 第二个参数表示参数 1 和谁取余

  • 把 10 和 3 进行取余 (10 除以 3 然后获取余数)

    select mod(10,3)    from dual; 
    

  • 任何数对 0取 余是其本身，对1取余是 0

9.2.7 日期函数

• 当前时间(sysdate)

  • 查询系统当前日期

    select sysdate from dual; 
    

  • 可以日期进行加减，单位是天

  • 显示时间:明天的这个时候

    select sysdate + 1 from dual;
    

  • 显示时间:昨天的这个时候

    select sysdate - 1 from dual;
    

  • 显示时间:1 小时之后的这个日期

    select sysdate + 1/24 from dual;
    

• MONTHS_BETWEEN
  俩个日期之间相差多少个月【单位是月】

  • 30 天之后和现在相差多少个月

    select months_between(sysdate+30,sysdate)   from dual; 
    

  • 如果不满一个月则返回一个小数

    select months_between(sysdate+10,sysdate)   from dual; 
    

• ADD_MONTHS
  返回一个日期数据:表示一个时间点,往后推 x 月的日期

  • ‘01-2月-2016’ 往后推 2 个月

    select add_months('01-2月-2016',2)   from dual; 
    

  • 当前时间往前推 4 个月

    select add_months(sysdate,-4)      from dual;
    

• NEXT_DAY
  返回日期:表示下一个星期几在哪一天

  • 离当前时间最近的下一个星期 5 是哪一个天

     select next_day(sysdate,'星期五')   from dual; 
    

    注意: 如果要使用’FRIDAY’,那么需要把当前会话的语言环境修改为英文

    alter session set nls_language=‘english’;
    

• LAST_DAY
  返回日期:表示指定月份的最后一天

  • 当前日期所在月份的最后一天(月底)

    select last_day(sysdate) from dual;
    

  • 显示指定日期所在月份的最后一天(月底)

    select last_day('3-5月-2015') from dual;
    

• ROUND
  对日期进四舍五入,返回操作后的日期数据

  • 把当前日期四舍五入到月

    select round(sysdate,'MONTH')   from dual;
    

    结论: 15 不进，16 进

  • 把当前日期四舍五入到年(6 月舍弃，7 月进位)

    select round(sysdate,'YEAR')   from dual; 
    

  • 使用默认的日期格式进行四舍五入会出错,为数字函数也有一个 round，俩个 ronnd 函数有冲突

    select round('01-2月-16','MONTH')   from dual; 
    

• TRUNC

  • 对日期进行截取 和 round 类似,但是只舍弃不进位

    select trunc(sysdate,'MONTH')   from dual;
    select trunc(sysdate,'YEAR')   from dual;
    

9.2.8 转换函数

• TO_CHAR
  把日期或者数字转换为字符

  • to_char （arg1,arg2） 把日期转换为字符

    • arg 1：日期
    • arg2:格式

  • 把当前日期按照指定格式转换为字符串

    select to_char(sysdate,'yyyy-mm-DD')   from dual; 
    

  • 日期格式

    格式	说明
    yyyy	四位数的年份
    rrrr	四位数的年份
    yy	两位数的年份
    rr	两位数的年份
    mm	两位数的月份(数字)
    D	一周的星期几
    DD	一月的第几天
    DDD	一年的第几天
    YEAR	英文的年份
    MONTH	英文全称的月份
    mon	英文简写的月份
    ddsp	英文的第几天(一个月的)
    ddspth	英文序列数的第几天(一个月的)
    DAY	全英文的星期
    DY	简写的英文星期
    hh[12|24]	小时 默认 12 进制
    mi	分钟
    ss	秒
    am	上下午

  • 测试常见的一些日期数据转换为字符串的格式

    select to_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY')    from dual; 
    select to_char(sysdate,'dd-mm-yy')   from dual; 
    select to_char(sysdate,'yy-mm-dd')   from dual; 
    select to_char(sysdate+5/24,'dd-mm-yy HH24:MI:SS AM')   from dual
    

  • 千年虫问题

  在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是 1971 年，在计算机里就会被表示为 71，但是到了 2000 年的时候这个情况就出现了问题，计算机 就会将其年份表示为 00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时 被称为千年虫

  数据库中表示日期中年份的有俩种: yy 和 rr
  之前一直使用的时候 yy 格式,后来才有的 rr 格式
  yy 表示使用一个俩位数表示当前年份:
  1990 —yy 数据库格式—> 90
  1968 —yy 数据库格式—> 68
  1979 —yy 数据库格式—> 79

• to_char 把数字转换为字符

- to_char(number,格式)
  - 0：表示强制显示小数点后精度 
  - 9: 表示显示数字 
  - .: 表示小数点 
  - ,：千位标识符 
  - L: 表示系统本地的货币符号 
- $: 美元货币

• 以美元符号显示薪水

  select to_char(salary,'$999,999.00')    from s_emp; 
  

  【fm】表示【去除】结果显示中的【开始的空格】

  select to_char(salary,'fm$999,999.00')    from s_emp; 
  

  以本地符号显示薪水

   select to_char(salary,'fmL999,999.00')    from s_emp; 
  

• TO_NUMBER 把字符转换为数

  select to_number('0100')    from dual; 
  
  select to_number('0x100')    from dual; 
  
  select to_number('abc')   from dual;  
  

• TO_DATE 把字符转换为日期

  • 将2016-1-1转换成日期

    select to_date(‘2016-1-1’,’yyyy-mm-dd')   from dual; 
    
    select to_date('2016-1月-1','yyyy-month-dd')   from dual; 	
    

    session 语言环境设置为英文下面可以运行

    select to_date('25-MAY-95','dd-MONTH-yy')   from dual;
    

案例

-- mod
select mod(9,3)
from dual;

--round 四舍五入  45.35
select round(45.3456,2)    
from dual; 

--trunc 截取到某一位 153.2
select trunc(153.264,1)
from dual;


--sysdate 当前时间
select sysdate 
from dual;

select sysdate + 1/24 
from dual;

--50天之后和现在相差多少个月 
select months_between(sysdate+50,sysdate)
from dual;

--如果不满一个月则返回一个小数
select months_between(sysdate+10,sysdate)   
from dual; 

--离当前时间最近的下一个星期5是哪一个天
select next_day(sysdate,'FRIDAY')   
from dual; 

--当前时间往前推 4 个月 
select add_months(sysdate,-4)   
from dual; 

--当前日期所在月份的最后一天(月底) 
select last_day(sysdate) 
from dual;

--把当前日期四舍五入到月 <15不进位
select round(sysdate,'MONTH')   
from dual;

--超过15就进位 >15 进位
select round(sysdate+10,'MONTH')   
from dual;

-- =15不进位
select round(sysdate+6,'MONTH')
from dual;

--四舍五入到年 月>6就进位 月<=6不进位
select round(sysdate,'YEAR')   
from dual; 

select round(add_months(sysdate,2),'YEAR')   
from dual;

select round(add_months(sysdate,-1),'YEAR')   
from dual;

--TRUNC  对日期进行截取 和 round 类似,但是只舍弃不进位 
select trunc(sysdate,'MONTH')   
from dual;  
-- 2020/5/1

select trunc(sysdate,'YEAR')   
from dual;
-- 2020/1/1

--转换函数 to_char
--将日期转换成字符串
--to_char(日期，日期格式) xxx年xxx月xxx日
--2020-05-09
select to_char(sysdate,'yyyy-mm-DD') 
from dual;

--美元形式展示员工薪水
select to_char(salary/7.0732,'$99999999.00')
from s_emp;

--本地操作系统以来的环境
select to_char(salary,'fmL99999999.00')
from s_emp;

-- to_number 将数字形式的字符串转化为数字
select to_number('1111')
from dual;

select to_number('0111')
from dual;

-- to_date 将字符串转换为日期
select to_date('2016-1-1','yyyy-mm-DD')+1
from dual;

-- 查询2016-1-1的这个月的最后一填的日期是多少

select last_day(to_date('2016-1-1','yyyy-mm-DD'))
from dual;

--距离2018-1-9的下一个星期五是哪一天
select next_day(to_date('2018-1-9','yyyy-mm-DD'),'Friday')
from dual

--将当前日期转成2018/08/01 12:52:44
select to_char(sysdate,'yyyy/mm/DD hh24:mi:ss')
from dual

--查询员工入职年限
select sysdate - start_date
from s_emp;
--入职月
select months_between(sysdate,start_date)
from s_emp;
--入职年
select months_between(sysdate,start_date)/12
from s_emp;

--以入职时间降序排序
select months_between(sysdate,start_date)/12,last_name,start_date
from s_emp
order by start_date desc;

--按照公司工作时长降序排序（工作了多少年）
select months_between(sysdate,start_date)/12 da,last_name,start_date
from s_emp
order by da desc;

9.2.9 总结

oracle中获取一个日期数据的方式:

• 使用 sysdate
• 使用 oracle 默认的日期格式 例如:’25-5月-95’
• 使用日期函数 ADD_MONTHS/NEXT_DAY/LAST_DAY/ROUND/TRUNC
• 使用转换函数 to_date

10、多表查询

多表查询，又称表联合查询，即一条 sql 语句涉及到的表有多张，数据通过特定的连接进行 联合显示.

11、笛卡尔积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积,表示为X* × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 [1] 。

我们也可以用 s_dept和s_region来测试

1. 查询 s_dept表

   select * from s_dept;
   

2. 查询 s_region

select * from s_region;

3.联合两张表进行查询

select * from s_dept,s_region;

明显的看到这里面产生了笛卡尔积，其实说白了就是多了一些重复的数据。

那我们如果去避免笛卡尔积的发生呢？

要想避免笛卡尔积的发生，前提条件是两张表存在关系，这样才有可能避免笛卡尔积的发生

12、表关系

一般来说，多表查询，不会在一条sql语句里面查询两个毫无关系的表。一定是查询两张具有关系的表。我们以s_dept,s_emp为例。

如图所示，在s_dept表中存着一列，用来存储区域编号,这样我们就可以清楚的知道每个部门所对应的区域编号,通过区域编号，从而可以去区域表里面去找到相对应的区域名称。

再比如s_dept与s_emp,如图

同样的道理，在s_emp中存在着部门编号这一列，通过部门编号就可以去s_dept表中找到相对应的部门名称。

也就是说这两张表是有关系的。

同样的道理：如果想要查看某一个员工所在部门的区域，那么就可以根据部门编号找到部门所对应区域编号，再去找到区域名称。

13、等值连接

在上面理解的基础上，接下来我们做一个这样的操作。

查询每个部门所在的区域名称

• 要想完成上述操作，就需要两张表s_dept,s_region

  s_dept存储着部门信息

  s_region存储着区域信息

  select * from s_dept,s_region;
  

经过查询，发现，红色选中部门是我们想要的数据，但是绿色部门就是笛卡尔积多余的部门。因此要想去掉多余的部门。我们可以采用等职连接去进行筛选。

• 等值连接

  利用一张表中某列的值和另一张表中某列的值相等的关系,把俩张表连接起来,把多余的数据过滤掉

  select *
  from s_dept,s_region
  where s_dept.region_id = s_region.id;
  

数据已经明显得到了我们想要的了。

• 给表起别名

  我们可以给表起别名

  select d.*,r.*
  from s_dept d,s_region r
  where d.region_id = r.id;
  

• 查询员工的名字、部门编号、部门名字

  select emp.last_name,emp.dept_id,dept.name
  from s_emp emp,s_dept dept
  where emp.dept_id = dept.id;
  

• 查询部门的 id,名称以及所在区域的名称

  select dept.name,dept.id,region.name
  from s_dept dept,s_region region
  where dept.region = region.id;
  

• 查询员工的名字以及员工所在部门的名称以及该部门所在的区域名称

  select emp.last_name,dept.name,region.name
  from s_emp emp,s_dept dept,s_region region
  where emp.dept_id = dept.id and dept.region_id = region.id;
  

• 查询Ngao所在部门的区域名称

  select region.name
  from s_emp emp,s_dept dept,s_region region
  where emp.dept_id = dept.id and dept.region_id = region.id and emp.last_name = 'Ngao';
  

14、不等值连接

• 建表语句

  create table s_grade(
  	id number(7) primary key,
  	gradeName varchar2(30) not null,
  	losal number(7),
  	hisal number(7)
  );
  -- 插入三条数据
  insert into s_grade values(1,'初级程序员',700,1200); 
  insert into s_grade values(2,'中级程序员',1201,2000); 
  insert into s_grade values(3,'高级程序员',2001,3000); 
  commit;
  

• 介绍

  连接两张表，但并非使用等号实现,也可以用非等号例如> <>等连接起来

  • 设数据库中还有一张工资等级表:salgrade
    • gradeName 列表示等级名称
    • losal 列表示这个级别的最低工资数
    • hisal 列表示这个级别的最高工资数

• 查询每个员工的工资工资等级

  select emp.last_name,grade.gradename
  from s_emp emp,s_grade grade
  where emp.salary between grade.losal and grade.hisal; 
  

  select grade.gradeName,emp.last_name
  from s_emp emp,s_grade grade
  where emp.salary >= grade.losal and emp.salary <= grade.hisal;
  

15、外连接

• 准备工作

  insert into s_emp(id,last_name) values(26,'tom');
  insert into s_dept(id,name) values(60,'st');
  commit;
  

  通过查询我们可以知道:

  select * from s_emp;
  

  我们知道 s_emp表中新增了一条tom数据,但是该数据没有部门编号
  

  select * from s_dept;
  

  s_dept表中多了一条st数据。
  

• 查询 员工的名字以及所属的部门名称

  select emp.last_name,dept.name
  from s_emp emp, s_dept dept
  where dept.id = emp.dept_id;
  

发现并没有查询到 tom 和 st数据。

这是因为:

新员工 tom 不在任何部门,新增部门 st 下面没有任何员工 也就是说，使用等值连接的话,查询出来的数据就会少,这两条数据发现会查询不出来 因为新增的员工 tom 和部门表中 的数据连接不上,当然新增的部门st也和员工表中的数据连接不上.那么这俩条数据都是在等 值连接中查询不出来。

但如果我们想要把tom数据或者st数据查询出来,那么就需要采用外连接。

外连接的基础是等值连接，同时外连接分为以下三种:

• 左外连接

• 右外连接

• 全连接

15.1 左外连接

• 概念

  在等值连接的基础上，额外显示左表中多出来的数

• 查询所有员工以及对应的部门的名字,没有部门的员工也要显示出来

  select emp.last_name,dept.name
  from s_emp emp,s_dept dept 
  where emp.dept_id = dept.id(+);
  
  select emp.last_name,dept.name
  from s_emp emp left outer join s_dept dept
  on emp.dept_id = dept.id;
  
  -- outer可以省略不写
  

上述两种都是同一种意思，只不过 + 形式是oracle特有的.下面那种方式是通用的。

15.2 右外连接

• 概念

  在等值连接的基础上，额外显示右表中多出来的数

• 查询所有员工以及对应的部门的名字,没有员工的部门也要显示出来

  select emp.last_name,dept.name
  from s_emp emp,s_dept dept 
  where emp.dept_id(+) = dept.id;
  
  select emp.last_name,dept.name
  from s_emp emp right outer join s_dept dept
  on emp.dept_id = dept.id;
  

上述两种都是同一种意思，只不过 + 形式是oracle特有的.下面那种方式是通用的。

15.3 全连接

• 概念

  在等值连接的基础上，额外展示两张表多余数据的内容

• 查询所有员工以及对应的部门的名字,没有员工的部门要显示出来,没有部门的员工也要显示出来

  select emp.last_name,dept.name
  from s_emp emp full outer join s_dept dept
  on emp.dept_id = dept.id;
  

注意: 不支持两个+

16、自连接

• 概念

两张相同的表，进行连接 也就是自己和自己进行关联

• 查询每个员工的名字以及员工对应的管理者的名字
  

  select emp.last_name,manager.last_name
  from s_emp emp,s_emp manager
  where emp.manager_id = manager.id;
  

17、结果集

• 概念

  如果有俩条 sql 语句,每一条 sql 都可以查询出一个结果,这个被称之为结果集。那么我们 可以使用下面的关键字对俩个结果集进行操作

  前提条件: 两个结果集查询出来的列名要一模一样

  • union 两个结果集的并集
    
  • union all 两个结果集合在一起展示
    
  • minus 差集 第一个结果集 除去 第二个结果集和它相同的部分
    
  • intersect 两个结果集的交集
    

17.1 union

• 概念

  union 获得俩个结果集的【并集】

  两个结果集公共部分 + 加上左表额外的 + 加上右表额外的

• sql

  select last_name,dept_id,name 
  from s_emp,s_dept 
  where s_emp.dept_id=s_dept.id(+) 
  union
  select last_name,dept_id,name
  from s_emp,s_dept
  where s_emp.dept_id(+)=s_dept.id;
  

  上面的sql其实就是一个并集
  

17.2 union all

• 概念

  把两个结果集合在一起展示 左表 + 右表

• sql

  select last_name,dept_id,name 
  from s_emp,s_dept 
  where s_emp.dept_id=s_dept.id(+) 
  union all
  select last_name,dept_id,name
  from s_emp,s_dept
  where s_emp.dept_id(+)=s_dept.id;
  

  出现了重复的部分。
  

17.3 minus

• 概念

  第一个结果集 减去 和第二个结果集相同的部分

• sql

  select last_name,dept_id,name 
  from s_emp,s_dept 
  where s_emp.dept_id=s_dept.id(+) 
  minus
  select last_name,dept_id,name
  from s_emp,s_dept
  where s_emp.dept_id(+)=s_dept.id;
  

  

17.4 intersect

• 概念

  求俩个结果集的【交集】(公共部分)

• sql

  select last_name,dept_id,name 
  from s_emp,s_dept 
  where s_emp.dept_id=s_dept.id(+) 
  intersect
  select last_name,dept_id,name
  from s_emp,s_dept
  where s_emp.dept_id(+)=s_dept.id;
  

  

18、伪列

• 概念

  伪列 rownum，就像表中的列一样，但是在表中并不存储。

  伪列只能查询，不能进行 增删改操作。它会根据返回的结果为每一条数据生成一个序列化的数字

  rownum 是 oracle 特有的。

• 基本使用

  select * 
  from s_emp
  where rownum = 1;
  

  查询第一行数据

• 伪劣能够执行的操作

  • rownum 可以等于1 表示查询第一行数据

    select * 
    from s_emp
    where rownum = 1;
    

    

  • rownum 可以 大于 0 或者 大于等于 1，但如果是其他数就查询不到数据

    select *
    from s_emp
    where rownum >= 1;
    

    

  • rownum 可以小于或者小于等于 任何数，代表查询前x行数据

    select *
    from s_emp
    where rownum < 6;
    

    查询前五行数据

    

• 分页

  伪劣最大的作用就是可以帮助oralce进行物理分页。

  • 查询第五条到第七条数据

    select *
    from s_emp
    where rownum <= 7
    minus
    select * 
    from s_emp
    where rownum < 5;
    

    

  • 一页展示2条数据,查询第3页数据

    select *
    from s_emp
    where rownum <= 6
    minus  
    select *
    from s_emp
    where rownum  < 5
    

    

19、组函数

• 概念

  组函数指将数据按照某列的值进行分组后，然后使用组函数分别对每个分好的小组中的 数据进行处理

  注意: 组函数一般要结合着分组关键字group by 来使用

• 组函数分类

  组函数具体分类如下:

  • avg 平均数
  • count 计算拥有多少条数据
  • max 最大值
  • min 最小值
  • sum 求和
  • stddev 标准差
  • variance 方差

• 使用(不结合分组关键字(group by))

  组函数必须要分组后才能使用,如果不使用 group by 关键字，那么默认将所有的数据当作是一个组

  • 查询员工平均工资

    select avg(salary) from s_emp;
    

  

  • 查询一共有多少个员工

    select count(*) from s_emp;
    

    

  • 查询最高员工工资是多少

    select max(salary) from s_emp;
    

    

  • 查询最低的员工工资

    select min(salary) from s_emp;
    

    

  • 查询所有员工工资总和

    select sum(salary) from s_emp;
    

    

  • 查询员工工资的标准差

    select stddev(salary) from s_emp;
    

    

  • 查询员工工资方差

    select variance(salary) from s_emp;
    

    

• 使用(结合分组关键字(group by))

  先分组，然后在组内使用组函数

  • 查询每个部分的平均工资

    select avg(salary),dept_id
    from s_emp
    group by dept_id;
    

    

  • 查询每个部门最高员工工资是多少

    select avg(salary),dept_id
    from s_emp
    group by dept_id
    

    

• having

  分组之后的进行进一步数据筛选的关键字

  having 和 where 的功能类似

  不能够单独存在，必须跟在group by后面

  group by 可以单独出现

  • 查询每个部门平均公司大于1400的部门

    select dept_id,avg(salary)
    from s_emp
    group by dept_id
    having avg(salary) > 1400;
    

    

    自此跟查询相关的所有关键字完毕。

  • 思考 下面这种方式可不可以执行：

    select dept_id,avg(salary)
    from s_emp
    where avg(salary) > 1400
    group by dept_id;
    

    答案是不可以的，因为这sql语句的执行顺序有关:

    1. 先执行 from
    2. 再执行 where
    3. 再执行 group by
    4. 在执行 having
    5. 再执行 select
    6. 最后执行 order by

    但是 组函数一定要分组以后才能使用，当组函数出现在 where 后面时还没有去分组，所以就sql语句就错误。因此我们也得出组函数可以出现的位置：

    • having / select / order by后面。

• 特别注意

  当select / having 出现组函数，那么其他未被组函数修饰的列就必须出现在 group by 后面

  例如下面sql语句就是错的:

  select avg(salary),dept_id,last_name
  from s_emp
  grou by dept_id;
  

  因为select中出现了组函数,但是last_name没有被组函数修饰，同时并没有出现在group by 后面。

• 查询部门平均工资大于100的 部门信息，并且只展示 部门编号 部门名字 平均薪水。同时部门以降序展示

  select emp.dept_id,dept.name,avg(emp.salary)
  from s_emp emp,s_dept dept
  where emp.dept_id = dept.id
  group by emp.dept_id,dept.name
  having avg(salary) > 1000
  order by emp.dept_id desc;
  

20、where & Having

• 区别
  • where和having都是做条件筛选的
  • where执行的时间比having要早
  • where后面不能出现组函数
  • having后面可以出现组函数
  • where语句要根from后面
  • having语句要紧跟group by后面

21、Select语句总结

• 除了select,from其他都不是必须的
• 执行顺序
  1. 先执行 from
  2. 再执行 where
  3. 再执行 group by
  4. 在执行 having
  5. 再执行 select
  6. 最后执行 order by

综合练习：

查询部门平均工资大于100的部门信息,并且只展示部门编号部门名字平均薪水。同时部门以降序展示

select avg(emp.salary),emp.dept_id,dept.name
from s_emp emp,s_dept dept
where emp.dept_id=dept.id
group by emp.dept_id,dept.name
having avg(emp.salary)>100
order by dept.name desc;

​