SQL函数查询语句

最新推荐文章于 2024-03-07 19:52:35 发布
最新推荐文章于 2024-03-07 19:52:35 发布
阅读量2.7k 收藏 13
点赞数 1
分类专栏： SQL函数汇总 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Lz133153/article/details/106023638
版权

 

 

 

目录

重要函数

实例

函数练习

重要函数

  1. length(str):返回一个字符串的长度
  2. concat(str1,str2)：字符串连接函数,它和||实现的功能一样。
  3. chr():将一个ASCII码转换成字符
  4. ascii(字符)：将一个字符转换成ASCII码值
  5. trim(str)函数：如果只跟一个参数，表示去除字符串两端的空格 
  6.  ltrim(str,):去掉左边空格
  7.   rtrim(str):去掉右边空格 
  8. pad(str,len,'符号'):填充函数,len表示长度,'符号'表示不够的用特定符号填充
  9.  lpad():在左边填充
     rpad():在右边填充
  10. replace(str,s,d)：字符串替换函数，将字符串str中的s字符替换成字符d
  11. instr(str,字符,ind,n):表示在字符串str中查找字符，从ind位置开始，n表示查询第n次出现
  12. .substr(str,ind,len):表示从ind位置开始截取字符串str，截取的长度为len
  13. initcap(str)：将字符串的英文单词首字母大写
  14. upper/lower():大小写转换函数
  15. floor/ceil:向下取整和向上取整
  16. mod()：求余函数
  17. round(num,精度)：四舍五入函数，num是数字，精度是小数点后边保留几位小数
  18. trunc():截断函数，可以将数字，日期等截断
  19. power()：幂指函数
  20. sqrt()：平方根
  21. sysdate：返回当前时间
  22. .to_number():将偏数据类型字符串转换成数字，货币符号也可以添加 $,￥是货币符号 L是本地货币符号
  23. .to_date('时间'，'yyyy'):将字符串转换成日期 yyyy表示4位的年
       MM 表示2位的月
       dd 表示2位日
       hh24 表示24小时制时间
       mi:表示分钟  ss：表示秒  day:表示时期
  24. to_char():可以将数字，日期等转换成字符串
  25. last_day(日期):取当前日期月的最后一天
  26. next_day():求周
  27. add_months(日期,月)：给一个日期加上若干个月
  28. add_months(日期,月)：给一个日期加上若干个月
  29. 开窗函数（分析函数）：partition by 替换group by
       row_number()over（）:必须和over形容函数联用，排序的序号和rownum伪列相同，连续序号，不考虑值相等的情况（值   相同序号不相同）
       dense_rank()over:可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同 1123
       rank()over:可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同 1134
  30. sum(case when) :列转行
  31. case when then/decode:如果....则...

     

实例

  • 1.length(str):返回一个字符串的长度
      ----查询所有员工的姓名长度
      select length(ename) from emp;
      ----查询 hahaha 的长度
      select length('hahahha') from dual;
      ----查询工作长度为5的员工姓名和工种
      select ename,job from emp where length(job)=5;
    2.concat(str1,str2)：字符串连接函数,它和||实现的功能一样。
      select 'aaa'||'bbb' from dual;
      select concat('aaa','bbb') from dual;
      select empno||':'||ename||':'||job from emp;
      select concat(empno,ename,job) from emp;
      ---求员工的工资，以 姓名是：smith,工资是：800 的形式显示
      select ' 姓名是：'||ename,'工资是：'||sal from emp;
    3.chr():将一个ASCII码转换成字符
      select chr(97) from dual;
      select chr(10)  from dual;
      将工资与姓名之间以空格的形式显示
      select sal||chr(10) ||ename from emp;
      select sal||' '||ename from emp;
    4.ascii(字符)：将一个字符转换成ASCII码值
      select ascii('a') from dual;
      select ascii('$') from dual;
    5.trim(str)函数：如果只跟一个参数，表示去除字符串两端的空格
      ltrim(str,):去掉左边空格
      rtrim(str):去掉右边空格
      select trim('         adsf          ') from dual;
      select ltrim('         adsf          ') from dual;
      select rtrim('         adsf          ') from dual;
    6.pad(str,len,'符号'):填充函数,len表示长度,'符号'表示不够的用特定符号填充
      lpad():在左边填充
      rpad():在右边填充
      select lpad(ename,10,'*') from emp;
      ---将姓名补充到10位，不够的在左边用‘*’表示，再将名字补充到15位，不够的在右边用‘￥’表示
      select Rpad(lpad(ename,10,'*'),15,'￥') from emp;
    6.replace(str,s,d)：字符串替换函数，将字符串str中的s字符替换成字符d
      select replace('asdfasdf','f','A') from dual;
    7.instr(str,字符,ind,n):表示在字符串str中查找字符，从ind位置开始，n表示查询第n次出现
      select instr('abkasdawe','a',1,1) from dual;
      select instr('abkasdawe','a',-1,1) from dual;
      select ename,instr( ename,'A',1) from emp;
      select ename,instr( ename,'A') from emp;
      ---求第五位是H的员工姓名
      select ename from emp where ename like '____H%';
      select ename from emp where instr(ename,'H',5,1)=5;
    8.substr(str,ind,len):表示从ind位置开始截取字符串str，截取的长度为len
      select substr('aa|bb|cc|dd|ee',1,3) from dual;
      select substr('aa|bb|cc|dd|ee',-3,2) from dual;
      select ename,substr(ename,3) from emp;
      求第五十位是H的员工姓名
      select ename from emp where instr(ename,'H',50,1)=50;
      select ename from emp where substr(ename,50,1)='H';
    9.initcap(str)：将字符串的英文单词首字母大写
      select initcap(ename) from emp;
    10.upper/lower():大小写转换函数
      select * from emp;
      select lower(ename) from emp;
      select upper('hahhah') from dual;
      ---将员工的姓名以首字母小写，其他字母大写的方式显示
      select lower(substr(ename,1,1))||upper(substr(ename,2)) from emp； 
    11.floor/ceil:向下取整和向上取整
       select floor(1234.838134) from dual;
       select floor(-166.1235) from dual;
       select ceil(1234.1234) from dual;
       select ceil(-1234.1234) from dual;
    ---11：floor向下取整 ceil向上取整
           select * from emp
           select floor (1254.2342) from dual ---返回结果1254
           select floor (-1667.324) from dual ---返回结果-1668
           select ceil (234.45) from dual;    ---返回结果235
           select ceil (-1234.456) from dual ---返回结果-1234
           select ceil (-1234.456) from emp ;--已有表查询
    ---12：mod()求余函数
           select mod(123,4) from dual ;
           查询姓名，编号是 编号/工资余数是169的信息
           select  ename,empno from emp where mod(empno,sal)=169;
           查询工资数是偶数的员工姓名
           select  ename,empno from emp where mod(,sal，2)=0;
    ---13：round(num,精度)：四舍五入函数，num是数字，精度正数是小数点后边保留几位小数，负数是小数点前面几位
           select round(48888.866859,2) from dual;
           select round(48888.866859,-2) from dual;
     
    ---14：trunc():截断函数，可以将数字，日期等截断 精度正数是小数点后边保留几位小数，负数是小数点前面几位
           select trunc(1234.8964,-2) from dual; 
           select trunc(-1234.8934) from dual; ---默认截断小数点后所有
           select trunc (’yuiefd‘,2) from dual 不能截断字符串， 
           select trunc(sysdate,'yyyy')from dual ;
           select trunc(hiredate,'dd') from emp; 
           select trunc(sysdate,'mm') from dual;
           截取当前字段的日期，之后返回字段当月第一天或当年日期的第一月第一天
    ---15 sysdate返回当前系统日期
          select sysdate from dual;--
    ---16 --power(2,2)幂指函数，第一个数值，第二个指数  
          select power(20,2) zhi from dual;-
    ---17--sqrt（）开方根
          select sqrt(64)from dual;
    ---18-- to_number 将偏数据类型字符串转换成数字，货币符号也可以添加(模板需要加‘’)
     数值模板的最小位数需要包含是转换字符数值最小位位数的，模板数值需为9或0(0时需要加’‘)
        select to_number ('2647.00',9999.99)from dual 
        select to_number ('2647.00',9999.99)from dual
        select to_number('97.9890','99.0000000') from dual;
        select to_number('$97.9890','$99.0000000') from dual;
        select to_number('$97.9890','$99.0000000') from dual;
    ---19.to_date('时间'，'yyyy'):将字符串转换成日期
        yyyy表示4位的年
        MM表示2位的月
       dd表示2位日
       hh24表示24小时制时间
       mi:表示分钟
       ss：表示秒
       day:表示时期
       select to_date('1999','yyyy') from dual;
       select to_date('1999-05-07','yyyy-mm-dd') from dual;
       select to_date('1999-05-07-12-14-45','yyyy-mm-dd-hh24-mi-ss') from dual;
    --- 20--to_char():可以将数字，日期等转换成字符串
     select to_char(hiredate,'yyyy-mm') from emp;
     select to_char(sysdate,'yyyy-mm') from dual; 
    ---21--last_day（） :去当前日期约的最后一天
     select last_day(hiredate)from emp;
     select last_day(sysdate) from dual;
    ---查询员工倒数第三天入职的员工
     select hiredate，ename from emp where hiredate=last_day(hiredate)-2; 
    ---22--next_day() 未来几天的日期
     select next_day(sysdate,7)from dual;
     select next_day(next_day(sysdate,7),7) "下下周" from dual;
    ---23--add-months 某日期加上若干个月
       select add_months(sysdate,6)from dual
       select add_months(sysdate,-6)from dual
    ---24 months_between(date1,date2)取两个日期相差的月数
     --查询2015-09-09入职的年数
       select months_between(sysdate,to_date('2015-09-09','yyyy-mm-dd'))/12 from dual;
    ***25.开窗函数（分析函数）：
       partition by 替换group by
       row_number()over（）:
       必须和over形容函数联用，排序的序号和rownum伪列相同，连续序号，不考虑值相等的情况（值相同序号不相同）
       dense_rank()over:
       可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同 1123
       rank()over:
       可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同 1134
       select deptno ,sal ,row_number() over(partition by deptno order by sal desc) biaoyi from emp; 
       select deptno ,sal ,dense_rank()over (partition by deptno order by sal desc) biaoer from  emp;
       select deptno ,sal,rank ()over(partition by deptno order by sal desc) biaoer from emp;     
     
       select deptno,sal,row_number()over(partition by deptno order by sal desc) from emp ;
       select deptno,sal,dense_rank()over(partition by deptno order by sal desc) from emp ;
       select deptno,sal,rank()over(partition by deptno order by sal desc)  from emp ; 
    ---26.sum(case when) :列转行  
       ---查询十号、二十号、三十号和其他部门和部门的人数
       select deptno, 
       sum(case when deptno =10 then 1 end) 十号部门人数,
       sum (case when deptno=20 then 1 end) 二十号部门人数，
       sum(case when deptno=30 then 1 end ) 三十好部门人数，
       sum(case when deptno not in (10,20,30) then 1 end) 其余部门人数
       from emp group by deptno
       ---查询 clerk salesman manager analyst 的人数
       select 
       sum(case when job ='CLERK' then 1 else 0 end ) clerk,
       sum(case when job='SALESMAN' then 1  else 0end) salseman,
       sum(case when job='MANAGER' then 1  else 0 end) manager ,
       sum (case when job='ANALYST' then 1  else 0 end )analayst
       from emp 
      ---case when then/decode:如果....则...
       select  sal,   case when sal < 1000 then 'low'
        when sal >=1000 and sal <2000 then 'middle'
        else 'high'
        end gzfb
       from emp;
       select sal,decode(sal,800,'low',2000,'middle',3000,'high') from emp

函数练习

1.将所有员工的名字按小写的方式显示
select lower(ename) from emp ; 
2.显示正好为5个字符的员工的姓名。
select ename from emp where length(ename)=5;
3.显示所有员工姓名的前三个字符。
select substr(ename,1,3) from emp 
4 显示所有员工姓名的后三个字符
select substr(ename,-3) from emp;
5.以首字母大写,后面小写的方式显示所有员工的姓名。(两种方法)
select initcap(ename) from emp ;
 select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp;
6.以首字母小写后面大写方式显示所有员工姓名。
select lower(substr(ename,1,1))||upper(substr(ename,2)) from emp 
7.显示所有员工的姓名，用“我是老虎”替换所有“A”
select replace(ename,'A','我是老虎') from emp 

8.以字符长度为10的方式显示员工职位，多余的位数在右边以*来填充
selselect rpad(job,10,'*') from emp 
9.找出字符串"oracle training"中第二个ra出现的位置
select instr('oracle training','ra',1,2) from dual
10. 去除字符串‘ aadde gf ’两边的空格
select trim(' aadde gf ') from dual
11.以指定格式显示员工的工资（格式：SMITH 的工资是 800）
select ename||'的工资是'||sal from emp
12.显示所有员工的姓名,用a替换所有"A"
select replace(ename,'A','a') from emp
13.显示员工姓名中包含‘H’的员工（三种方法）
select ename from emp where ename like'%H%'
select ename from emp where instr(ename,'H')>0
select ename from emp where substr(ename,instr(ename,'H'),1)='H'
14.显示员工姓名中第二个字符是‘L’的员工（三种方法）
select ename from emp where ename like '_L%';
select ename from emp where instr(ename,'L',2,1)=2;
select ename from emp where substr(ename,2,1)='L'
15.显示员工姓名中最后一个字符是‘T’的员工（四种方法）
select ename from emp where ename like'%T';
select ename from emp where substr(ename,-1)='T';
select ename from emp where instr(ename,'T',-3,1)=length(ename)-2;
select ename from emp where instr(ename,'T',length(ename),1)>0;
 

16.查找已经入职8个月多的员工

select empno,ename from emp where months_between(sysdate,hiredate)>8; 
--17显示满10年服务年限的员工的姓名和受雇日期。
select ename ,hiredate from emp where (months_between(sysdate,hiredate)/12)>=10;
--18对于每个员工，显示其加入公司的天数。
select ename,trunc (sysdate-hiredate,0)  天数 from emp;
--19找出各月倒数第3天受雇的所有员工。
select ename  from emp where hiredate=(last_day(hiredate)-2);
--20显示1981年入职的所有员工
select ename from emp  where   to_char(hiredate,'yyyy')=1981
--21显示所有10月份入职的员工
select ename from emp where to_char (hiredate,'mm')=10
--22查询一个月前入职的员工
select ename from emp where months_between(sysdate,hiredate)>1
select ename from emp where add_months(hiredate,1)<sysdate 
23.显示在一个月为30天的情况所有员工的日薪,忽略余数
select ename, trunc (sal/30) from emp 
24.显示员工在此公司工作了几个月（要求结果是整数）
select ename ,trunc(months_between(sysdate,hiredate))from emp
25.显示所有12月份入职的员工
select ename from emp where to_char(hiredate,'mm')=12
26. 显示所有员工的姓名、加入公司的年份和月份，并且按照年份排序
select ename ,to_char(hiredate,'yyyy') 年份,to_char(hiredate,'mm') 月份 from emp order by 2 ;

27.查询当前日期
select trunc (sysdate) from dual; 
28.将字符串"1980-12-17"转成日期
select to_date('1980-12-17','yyyy-mm-dd') 日期 from dual
29.使职位是分析员的，工资+1000;职位是经理的，工资+800；职位是其它的，工资+400
select ename， case when job='ANALYST' THEN SAL+1000
when job='MANAGER' then sal+800
  else sal+400
    end 工资
    from emp
30.员工总工资，平均工资，四舍五入，保留小数点后2位
select sum(sal+nvl(comm,0)) 总工资,round(avg(sal+nvl(comm,0)),2) 平均工资 from emp;
select round(sum(sal+nvl(comm,0)),2),round(avg(sal+nvl(comm,0)),2) from emp;
31.按部门求出部门平均工资，且平均工资取整数
select deptno，trunc(avg(sal)) from emp group by deptno

wwlovecat
关注
  • 1
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
C#+SQL Server工资管理系统
xueba8的博客
07-03 1万+
一、 设计目的结合数据库原理的基本理论，选用一种现代关系数据库系统（如：SQL SERVER），设计一个具体的信息管理系统。通过本课程的训练，使学生掌握数据库技术的实际应用以及数据库信息管理系统的设计方法与开发过程；掌握数据库开发工具的使用以及综合编程调试的能力，为以后能进行数据库系统设计、开发与维护打下良好的基础。 二、 设计要求结合一个具体任务（见附件一：综合设计参考题目），完成一个数据库系统...
统计每年入职的员工人数
qq_24259223的博客
05-27 7394
所有员工的入职日期：select to_char(hiredate,’yyyy’) from emp;declare --定义每年入职的员工人数 count80 number := 0; --80年的员工人数 count81 number := 1; --81年的员工人数 count82 number := 2; --82年的员工人数 count87 number := 7; -
SQL题目练习总结
qq_43441284的博客
03-09 6650
SQL题目练习总结 1-10 1.查看员工信息表EMP中员工的所有信息 select * from emp; select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from emp; –2.查看员工信息表EMP中员工的部分信息，例如姓名、岗位和薪资，或员工编号、姓名和部门编号 select ename, job, sal from emp; select empno, ename, deptno from emp; –3.查看员工信息表EMP中
SQL函数练习题
最新发布
gailun66的博客
03-07 670
SELECT ename,hiredate,months_between(SYSDATE,add_months(hiredate,3)) 入职月数,SYSDATE-HIREDATE 入职天数 FROM EMP。SELECT ENAME,REPLACE(ENAME,'A','我是老虎') FROM EMP。31.显示员工的名字,工资, 如果工资大于1000小于2000, 显示一月一千多,18.将ename中的A,替换成ABC,显示编号,替换前的名字,替换后的名字。
查询一个表里面的姓名为 有 a 和e的员工
qq_37469931的博客
01-29 8612
select * from  employees where last_name like '%a%e%' or last_name like '%e%a%'
SQL查询字段被包含语句
09-09
总的来说，`CHARINDEX`是SQL中一个非常实用的函数，尤其在需要检查字段是否包含一系列关键词时，它可以替代多个`LIKE`条件，简化查询语句，提高代码可维护性。但在使用时，也需要考虑到性能因素，特别是在大数据场景...
SQL数据库查询语句基础文本——可复制的查询语句
05-23
本资源“SQL数据库查询语句基础文本——可复制的查询语句”提供了丰富的SQL语句实例，不仅方便用户直接在数据库中运行，还配以中文解释，帮助理解其工作原理。 1. **基本查询** SQL的基本查询包括`SELECT`语句，...
ASP中巧用Split()函数生成SQL查询语句的实例
10-26
这篇文档主要讲述了如何利用Split函数来动态生成SQL查询语句，这对于实现基于不同参数的动态条件查询非常有帮助。 Split函数的基本用法是： Split(字符串, 分隔符) 其中，“字符串”是我们要分割的源字符串，而...
基于SQL中的数据查询语句汇总
09-10
本文将汇总介绍SQL中的数据查询语句，帮助读者更好地理解和运用SQL进行数据操作。 1. **WHERE条件表达式**：在SQL查询语句中，WHERE子句用于指定筛选条件，只返回满足条件的记录。例如，`SELECT * FROM student ...
Oracle数据库学习笔记（五 —— 函数、视图、索引、同义词）
欢迎来到Gorit的博客
11-14 620
Oracle 函数、视图、索引、同义词引言一、字符串函数1.1 concat （字符串链接函数）1.2 首字符大写 initcap二、Oralce 的索引2.1 索引的概念及使用规则2.2 索引的分类从使用方式来区分从索引类型分2.3 索引的语法2.4 索引的创建2.5 索引的修改和删除三、Oracle 视图3.1 视图的基本概念3.2 视图的创建3.3 视图的原则3.4 视图的修改和删除3.5 ...
数据库常用的sql语句名称大全
qq_39652397的博客
03-24 1160
前言 本片博客使用mysql数据库进行数据操作，使用Navicat for mysql 这个IDE进行可视化操作。每个SQL语句都是亲身实验验证的，并且经过自己的思考的。能够保证sql语句的可运行性。 sql语句的命令不区分大小写，但储存的数据是区分大小写的。在这里我们统一使用英文小写进行命令编辑。如果喜欢大写的可以使用IDE编辑器的一键美化功能，可以统一转化为大写。并且会对你的sql语句进行美化，例如自动换行等。 创建删除（数据库、表） 创建数据库 create database <数
sql中使用特殊字符
qqwwwgg1983的专栏
06-29 435
sql中使用特殊字符 chr(13) 是一个回车Chr(10) 是个换行符chr(32) 是一个空格符例select 1|| /*chr(10) */||2 from dual;
MySQL函数
pipi187456的博客
04-25 1994
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 目录 MySQL函数 一、函数 二、函数示例 1.示例 2.练习 MySQL函数 一、函数 length(str):返回一个字符串的长度 concat(str1,str2)：字符串连接函数,它和||实现的功能一样。 chr():将一个ASCII码转换成字符 ascii(字符)：将一个字符转换成ASCII码值 trim(str)函数：如果只跟一个参数，表示去除字符串两端的空格 ltrim(str,):去掉左边...
Oracle数据库练习题
qq_45417911的博客
08-14 4059
Oracle数据库练习题 （1） 查询20号部门的所有员工信息 select * from emp where deptno=20; （2） 查询所有职业为CLERK的员工号、员工姓名和部门号 select empno,ename,deptno from emp where job=‘CLERK’; （3） 查询资金高于工资的员工信息 select * from emp where comm>sal; （4） 查询奖金高于工资20%的员工信息 select * from emp where c
sql判断字段是否以某个子串开头,结尾
热门推荐
weixin_42585386的博客
08-26 2万+
问题现象: 今天在学习中思考了这样一个问题: 如何判断字段是否以某个子串开头或结尾? 问题分析: 通过查阅资料和学习,总结出来以下几个方法: 1.like关键字 SELECT * FROM car WHERE car.type like '中国%' ; //查询在car表中 type字段 以 '中国' 开头的记录 SELECT * FROM car WHERE car.type like '%中国' ; //查询在car表中 type字段 以 '中国' 结尾的记录 2.left()函数 ..
表查询
sinat_38878030的博客
10-08 1228
第四章 表查询   SQL查询语句语法： select 表名1.列1，表名2.列2… from 表名1,表名2  where 条件 order by 排序项 asc或者desc; select 列1，列2… from表名group by 分组项 having 条件 order by 排序项 asc或者desc;    1 查看表结构  descdept;  desce
sql习题二--函数的使用，多表查询
weixin_34126557的博客
11-29 327
2019独角兽企业重金招聘Python工程师标准>>> ...
查询sql
dxm809的博客
03-21 1467
--别名、全部列、部分列 select UserInfo.UserName name,UserInfo.UserPwd pwd from UserInfo --top n select * from StudentInfo select top 1 * from StudentInfo select top 2 percent * from StudentInfo --排序,asc表示升,d...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值