数据库教程:SQL-函数及多表关联

函数一般是在数据上执行的,它给数据的转换和处理提供了方便。只是将取出的数据进行处理,不会改变数据库中的值。函数根据处理的数据分为单行函数和聚合函数(组函数),组函数又被称作聚合函数,用于对多行数据进行操作,并返回一个单一的结果,组函数仅可用于选择列表或查询的having子句;单行函数对单个数值进行操 …

函数一般是在数据上执行的,它给数据的转换和处理提供了方便。只是将取出的数据进行处理,不会改变数据库中的值。函数根据处理的数据分为单行函数和聚合函数(组函数),组函数又被称作聚合函数,用于对多行数据进行操作,并返回一个单一的结果,组函数仅可用于选择列表或查询的having子句;单行函数对单个数值进行操作,并返回一个值。

dual是一个系统表。注意用于测试。

1 字符相关

 1 -- 1.字符串连接   2 select concat('aa','12') from dual;   3 select 'aa'||'12' from dual;   4    5 -- 2.首字母大写   6 select initcap('abc') from dual;   7 --- 把大写转化小写   8 select lower('abc') from dual;   9 select upper('abc') from dual;  10   11 -- 把所有员工的姓名小写输出  12 select lower(e.ename),e.empno  13 from emp e  14   15 -- 3.填充字符lpad/rpad  16 select lpad('sxt',5,'*') from dual;  17 select rpad('sxt',5,'*') from dual;  18   19 -- 4.去掉空白字符  20 select '  kallen' from dual;  21 select ltrim('  kallen',' ') from dual;  22 select rtrim('  kallen  ',' ') from dual;  23 -- trim 删除左右两边的字符  24 select trim('a' from 'abc') from dual;  25   26 -- 5.求子串 substr(str,loc,len)-->loc从1开始  27 select substr('abcd',2,2) from dual;  28   29 -- 6.查找字符串  30 /*  31 如果找到返回>=1的索引;如果没找到返回0  32 */  33 select instr('abcd','b') from dual;  34   35 -- 7.求长度  36 select length('abcd') from dual;

2 数值型函数

 1 -- 四舍五入round(x,y)对x保留y为小数   2 select round(23.652) from dual;   3 select round(23.652,1) from dual;   4 select round(25.652,-1) from dual;   5    6 -- 返回x按精度y截取后的值   7 select trunc(23.652) from dual;   8 select trunc(23.652,2) from dual;   9 select trunc(23.652,-1) from dual;  10   11 -- mod(x,y)求余数  12 select mod(9,2) from dual;  13   14 -- ceil 向上取整  15 select ceil(1.9) from dual;  16 -- floor 向下取整  17 select floor(1.9) from dual;

3 日期时间函数

 1 -- 返回系统当前时间   2 select sysdate from dual;   3 -- 返回当前会话时区中的当前日期    4 select current_date from dual;   5    6 -- 添加月数   7 select add_months(sysdate,1) from dual;   8 -- 返回两个时间相差的月数   9 select months_between(sysdate,add_months(sysdate,2)) from dual;  10   11 -- 需求:查询工作年限在30年以上  12 select e.ename,e.hiredate  13 from emp e  14 where months_between(sysdate,e.hiredate)/12 > 30  15   16 -- 返回date下一月份的最后一天  17 select last_day(add_months(sysdate,1)) from dual;  18 -- next_day(date1,week) 返回date1下周星期几的日期  19 select sysdate "当时日期",next_day(sysdate,'monday') "下周星期一" from dual;

日期计算相关

1 --两个时间进行四则运算的单位是天  2 select sysdate+2 from dual;  3 select sysdate-2 from dual;

4 类型转换

4.1 隐式类型转换

1 --字符和数字/日期之间的隐式转换  2 -- 字符隐式转换成数值  3 select '100' - 10 from dual;  4   5 -- 字符隐式转化为日期  6 -- dd-mon-rr 默认的日期格式  7 select 1 from dual  8 where sysdate > '13-may-19';

4.2 显示类型转换

SQL-函数及多表关联

4.2.1 to_char()

把日期转化成字符串,格式元素列表如下

SQL-函数及多表关联

1 -- 把日期转化成字符  2 -- 按照默认格式dd-mon-rr  3 select to_char(sysdate) from dual;  4 -- 按指定格式  5 select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') as t from dual;  6 -- 格式内要打中文(非给定允许的时间分隔符)需要用双引号引起来

把数值格式化成字符串,控制符如下表

SQL-函数及多表关联

1 -- 把数值格式化成字符串  2 select to_char(12345,'99999.99') from dual;  3 select to_char(12345,'99,999.99') from dual;  4 -- 没有数的位置补空格  5 select to_char(12345,'999,999.99') from dual;  6 -- 没有数的位置补0  7 select to_char(12345,'000,000.00') from dual;  8 -- 格式化成美元显示  9 select to_char(12345,'$000,000.00') from dual;

4.2.2 to_number 、to_date

 1 -- to_number   2 select to_number('$12,345','$99,999') from dual;   3 select to_number('$12,345.12','$99,999.99') from dual;   4    5 -- to_date   6 select to_date('14-may-19','dd-mon-rr') from dual;   7 select to_date('2004-09-19','yyyy-mm-dd') from dual;   8    9 -- 查询雇用期满6个月的下一个周一的日期。  10 select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'monday')  11 from emp e  12 where months_between(sysdate,e.hiredate) > 6

5 decode/case when

decode(条件,值1,“返回值1″,值2,“返回值2”,,,“默认值”)

1 -- 需求:查询员工所在的部门名称  2 select   3 e.ename,  4 e.deptno,  5 decode(e.deptno,10,'部门1',20,'部门2',30,'部门3','未知')  6 from emp e;

case when – 值匹配

 1 select    2 e.ename,   3 e.deptno,   4 case e.deptno   5   when 10 then '部门1'   6   when 20 then '部门2'   7   when 30 then '部门3'   8   else '未知'   9 end  10 from emp e;

case when – 条件匹配

 1 -- 需求:根据工资分布输出以下信息   2 /*   3  <1000 真屌丝   4  (1001,2000] 屌丝   5  (2001,3000] 白领    6  (3001,5000] 高富帅   7  (5001,10000] 土豪   8 */   9   10 select   11 e.ename "姓名",  12 e.sal "工资",  13 case  14   when e.sal <= 1000 then '真屌丝'  15   when e.sal <= 2000 then '屌丝'  16   when e.sal <= 3000 then '白领'  17   when e.sal <= 5000 then '高富帅'  18   when e.sal <= 10000 then '土豪'  19   else '未知'  20 end "描述"  21 from emp e;

6 组函数

组函数把多行数据经过运算后返回单个值。也称聚合函数。

SQL-函数及多表关联

 1 -- 求公司雇员的数量   2 select count(*)   3 from emp e;   4    5 select count(e.empno)    6 from emp e;   7    8 select count(1)   9 from emp e;  10   11 -- avg:对多个记录的某个字段求平均值  12 -- 需求:求底薪的平均值  13 select avg(e.sal)  14 from emp e;  15   16 -- 需求:求雇员的最高薪资/最低薪资  17 select max(e.sal),min(e.sal),avg(e.sal)  18 from emp e;  19   20 -- 需求:求公司一个月的员工基本开销  21 select sum(e.sal)  22 from emp e;

  1. 组函数或聚合函数是对一个数据集(表数据、查询出来的表、分组的表)进行聚合。(组函数仅可用于选择列表或查询的having子句)
  2. 聚合函数对字段是 null 的值进行忽略。
  3. max/min 适合任意数据类型,sum/avg 只适用于数值类型。

聚合函数的结果可以作为其他查询条件。

1 -- 最早入职的员工  2 select e.ename,e.hiredate  3 from emp e  4 where e.hiredate = (select min(e.hiredate) from emp e);

7 分组

在处理统计或聚合数据时,很多时候需要对数据进行分组。语法

1 select field1,...  2 from tablename  3 group by field1[,field2,…]

按照field1[,field2,…] 分组,字段值相同的记录分到一组。

7.1 分组和聚合函数

 1 -- 需求:统计部门10的人数   2 select count(1)   3 from emp e   4 where e.deptno = 10;   5    6 -- 需求:求各个部门的平均薪资   7 select e.deptno,avg(e.sal)   8 from emp e   9 group by e.deptno  10   11 -- 需求:求各个部门的月收入平均值  12 select e.deptno,avg(e.sal+nvl(e.comm,0))  13 from emp e  14 group by e.deptno

7.2 null 值会归为一组

1 -- 特例:按照津贴分组  2 select e.comm,count(1)  3 from emp e  4 group by e.comm;

SQL-函数及多表关联

7.3 having (重点)

如果需要对分组后的数据进行条件过滤,必须使用having。

 1 -- group by having   2 -- 查询部门平均薪资大于3000的部门   3 select e.deptno   4 from emp e   5 group by e.deptno   6 having avg(e.sal) > 3000   7    8 -- 查询部门薪资大于3000的雇员按部门分组的平均薪资   9 select e.deptno,avg(e.sal)  10 from emp e  11 where e.sal > 3000  12 group by e.deptno;

  1. where过滤行,having过滤分组。
  2. having支持所有where操作符。

8 排序 (order by)

当需要对数据集进行排序操作时,语法

1 select field1, field2,...  2 from tablename  3 order by field1,field2

对数据集进行排序,先按 field1 排序,如果 field1 排序相同,按照 field2 排序,以此类推。

  • asc 升序,默认
  • desc 降序
 1 -- order by   2 -- 按雇员薪资排序   3 select e.ename,e.sal   4 from emp e   5 order by e.sal desc   6    7 -- 按薪资升序,名称降序   8 select e.ename,e.sal   9 from emp e  10 order by e.sal,e.ename desc;

综合运用

1 --薪资大于1200的雇员的平均薪资大于1500的部门,按照平均薪资升序排序   2 select e.deptno,avg(e.sal)  3 from emp e  4 where e.sal > 1200  5 group by e.deptno  6 having avg(e.sal) > 1500  7 order by avg(e.sal) asc

9 select 语言的执行顺序

  1. 读取from子句中的基本表、视图的数据,[执行笛卡尔积操作]。
  2. 选取满足where子句中给出的条件表达式的元组
  3. 按group子句中指定列的值分组,同时提取满足having子句中组条件表达式的那些组
  4. 按select子句中给出的列名或列表达式求值输出
  5. order by子句对输出的目标表进行排序。

10 多表关联

10.1 关联原理

1 -- 笛卡尔积  2 select *  3 from emp,dept

SQL-函数及多表关联

10.2 等值连接

1 -- 等值连接  2 -- 需求:查询雇员的部门名称  3 select e.ename,e.deptno,d.dname  4 from emp e,dept d  5 where e.deptno = d.deptno

10.3 不等值连接

1 -- 查询每个雇员的薪资等级  2 select e.ename,e.sal,sg.grade  3 from emp e,salgrade sg  4 where e.sal >= sg.losal and e.sal <= sg.hisal  5 -- where e.sal between sg.losal and sg.hisal

10.4 外连接

左外连接:左边的表作为主表,右边表作为从表,主表数据都显示,从表数据没有的位置,用null填充,用 (+) 在 where 中作为后缀标识主表。

1 -- 需求:查询所有部门的雇员  2 select *  3 from dept d,emp e  4 where d.deptno = e.deptno(+)

右外连接: 右边的表作为主表,左边表作为从表,主表数据都显示,从表数据没有的位置,用null填充,用 (+) 在 where 中作为后缀标识主表。

1 -- 右外连接(b)  2 select *  3 from emp e,dept d  4 where e.deptno(+) = d.deptno;

10.5 自连接

一个表自身连接自身时,称为自连接。自连接以不同的视角看待同一张表。

1 -- 查询每个雇员的上级领导  2 select e.ename "雇员",m.ename "领导"  3 from emp e,emp m  4 where e.mgr = m.empno

10.6 多于两张表的查询

如果有多个表参与查询,先把 t1 x t2 笛卡尔积得到一个大表 t1,再把 t1 x t3 笛卡尔积得到一个另外的大表t2,依次类推。所有的多表查询最终都是两种表的查询。

需要了解更多数据库技术:SQL-函数及多表关联,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!

www.ctvol.com true Article 数据库教程:SQL-函数及多表关联

本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。

ctvol管理联系方式QQ:251552304

本文章地址:https://www.ctvol.com/dtteaching/626166.html

(0)
上一篇 2021年5月25日 下午11:26
下一篇 2021年5月25日 下午11:29

精彩推荐