tia的学习笔记
0%

多表连接查询、SQL函数、分组聚合

Posted on Edited on In
4k 4 mins.

转载自友情链接

多表连接查询

功能：同时查询多张表里的数据

种类：inner join,left join,right join

连接方式 功能
inner join 显示两张表中，符合连接条件的记录。
left join 左表显示所有记录；右表只显示符合连接条件的记录。
right join 右表显示所有记录；左表只显示符合连接条件的记录。
  • inner join（内连接）
    功能：显示两张表中，符合连接条件的记录。
    • 命令格式： 
      1
      2
      3
      Select 字段清单(表名.字段名), …. From 表A
      inner join 表B						--连接方式：inner join 交集
      on 表A.共有字段=表B.共有字段		--连接条件
    • inner join查询的步骤

    1.列出要查的字段清单，以 表名.字段名 的形式列在 Select后面。

    2.数一数两张表种，哪张表要查的字段更多，出字段多的表，放在from后面；出字段少的表，放在inner join后面。

    3.两张表的共有属性用等号连接，放在on后面，作为连接条件。

  • Left join (左连接)
    功能：左表显示所有记录；右表只显示符合连接条件的记录。
    • 命令格式：
      1
      2
      3
      Select  字段清单(表名.字段名), …. From 表A
      left join 表B						--连接方式 
      on 表A.共有字段=表B.共有字段			--连接条件
    • Left join查询的步骤

    1.列出要查的字段清单，以 表名.字段名 的形式列在 Select后面。

    2.观察两张表中，哪一张需要显示所有记录，要显示所有记录的表，放在 from后面。另一张表放在left join 后面。

    3.两张表的共有属性用等号连接，放在on后面，作为连接条件。

    常用SQL函数

    类型转换函数

    to_char()、to_date()、to_number()

  • to_char(X, 格式,NLS参数)
    功能：将数据X（数值型或日期型），转成对应的字符串

    说明：数据X为要转换的数据；参数2为格式，可为空；参数3为NLS参数，可为空。

    返回：字符串类型
    • 数值型转字符串
      1
      2
      3
      4
      5
      to_char(1210)                返回 '1234'
      to_char(1210.73, '9999.9')   返回 '1210.7'
      to_char(1210.73, '9,999.99') 返回 '1,210.73'
      to_char(21, '000099')        返回 '000021'
      to_char(852,'xxxx')          返回 '354'
    • 日期型转字符串
      1
      2
      3
      4
      5
      6
      7
      to_char(sysdate,'dd')   --取出几号
      to_char(sysdate,'mm')   --取出月份 
      to_char(sysdate,'yyyy') --取出年份
      to_char(sysdate,'ddd')  --取出年中的第几天(1-366)
      to_char(sysdate,'ww')   --取出第几周（1-53）
      to_char(sysdate,'q')    --取出第几季度(1、2、3、4)
      to_char(sysdate,'d')    --取出是周几（1-7）
  • to_date(字符串X, 格式说明, NLS参数)
    功能：将字符串X转化为日期型

    说明：字符串X为要转换的数据；参数2为格式说明，可为空；参数3为NLS参数，可为空。

    返回：日期型
函数 结果
to_date(‘2020.09.14’,’yyyy.mm.dd’) 2020/09/14
to_date(‘20200914’,’yyyymmdd’) 2020/09/14
to_date(‘2020-09-14’,’yyyy-mm-dd’) 2020/09/14
  • to_number(字符串X)
    功能：字符串转成数值类型

    返回：number类型
函数 结果
to_number(‘1234’) 1234
to_number(‘123.45’) 123.4

滤空函数

背景：Oracle中，NULL + 任意数，结果都为NULL

这种情况与实际情况相违背。为了解决这个问题，Oracle提供了滤空函数。

  • nvl(表达示一, 表达式二)
    功能：一空，则返回二。
  • nvl2(表达式一, 表达式二, 表达式三)
    功能：一空，则返回三；否则返回二。

    转换器函数 decode

    表达式：(value1,output1,elseoutput)

    功能：输出转换。

    说明：当表达式的值为 value1时，返回output1，否则返回elseoutput。

    分析函数

    功能：对一组查询结果进行运算，返回多行

    说明：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT ename,deptno,sal,
      --按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同,
      --且和下一条不同记录的排名之间空出排名
      RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "RANK",
      --按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同,
      --且和下一条不同记录的排名之间不空出排名
      DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "DENSE_RANK",
      --按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号继续
      --递增，顺序排名
      ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) "ROW_NUMBER"
     FROM employee;

    聚合函数

（1）常用的聚合函数：

sum:返回表达式中所有值的和

avg:计算平均值

min:返回表达式的最小值

max:返回表达式的最大值

count:返回组中项目的数量

（2）特征：
a.对一列值进行计算，并返回一个值
举例：select min(sal) from employee
b.聚合函数经常与group by分组依据一起使用

  • （单列）分组聚合：
    • 命令格式：
      1
      2
      select 分组依据, 聚合函数(列) from 表
      group by 分组依据
  • （多列）分组聚合：
    • 命令格式：
      1
      2
      select 分组依据1,分组依据2,聚合函数(列) from 表
      group by 分组依据1, 分组依据2
      c.聚合表达式可作为筛选条件。要求放在group by之后使用having聚合表达式 …. 构成筛选条件。

命令格式：

1
2
3
select 分组依据,聚合函数(列) from 表
group by 分组依据
having 聚合函数(列) ….

练习习题

题1 显示所有员工的姓名、入职年度（hiredate中的YYYY部分）

代码：

1
select ename,to_char(hiredate,'yyyy') from employee;

题2 显示入职日期在每个月最后一天的所有员工

代码：

1
2
select * from employee
where hiredate=last_day(hiredate);

题3 （分组聚合）列出每个部门的deptno,dname,员工总数

代码：

1
2
3
4
select dept.deptno,dept.dname,count(empno) 员工总数 from dept
left join employee
on dept.deptno=employee.deptno
group by dept.deptno,dept.dname;

题4 （子查询）列出sal比SMITH 多的所有员工

代码：

1
2
3
select ename from employee
where sal>
(select sal from employee where ename='SMITH');

题5 （连接查询）列出岗位（job）为clerk的员工的姓名、部门名称

代码：

1
2
3
4
select employee.ename,dept.dname from employee
left join dept
on employee.deptno=dept.deptno
where job='CLERK';

题6 （分组聚合）列出各种工作类别的最低薪金，显示最低薪金大于6500的记录

代码：

1
2
3
select job,min(sal) from employee
group by job
having min(sal)>6500;

题7 （子查询）对客户表(customers)中地域（nls_territory）为AMERICA 的记录的客户经理，查询出客户经理的姓名（employee.ename）、薪水(employee.sal)

代码：

1
2
3
4
select ename,sal from employee
where empno in
(select distinct account_mgr_id from customers
where nls_territory='AMERICA');

题8 （操作符in）在客户表中查出地域（nls_territory）为AMERICA、ITALY、INDIA、CHINA的客户编号（customer_id）、语言(nls_language);

代码：

1
2
3
select customer_id,nls_language from customers
where nls_territory in
('AMERICA','ITALY','INDIA','CHINA');

题9 查询employee表，显示每个部门、每个岗位的最高工资

代码：

1
2
3
select deptno,job,max(sal) from employee
group by deptno,job
order by deptno;

点这里请我吃个小蛋糕吧～～

Welcome to my other publishing channels