小白学大数据入门

分组函数

group by：

单行函数

字符型函数：

****length(str) 返回一个字符串的长度

concat(str1,str2) 字符串连接函数 只能连两个

select concat(concat('姓名是',ename),concat('工资是',sal)) from emp

ascii(字符) ascii 将一个ASCII码转换成字符

****instr(str1,str2,start,n) instr(源字符串,目标字符串,开始位置,匹配序号)

****substr(str,start,len) 表示从start位置开始截取字符串str，截取的长度为len， 返回值是一个字符串

select '出生日期是'||substr(450001199001012021,7,4)||'年'||
substr(450001199001012021,11,2)||'月'||
substr(450001199001012021,13,2)||'日' from emp

lpad(str,length,len) 在字符串str的左边用字符串value填充，直到长度为时为 止

ltrim(str1,str2) 删除在字符串str1左边出现的字符串str2

initcap(str) 将首字母大写其他字母小写(以空格来区分单词的)

lower/upper() 大小写转换函数

select job,upper(job),lower(job),substr(lower(job),1,1)||substr(upper(job),2) from emp 

replace(str,s,d) 字符串替换函数，将字符串str中的s字符替换成字符d

translate(char, from, to) 返回将出现在from中的每个字符替换为to中的相应字符 以后的字符串

数值型函数

****round(num) 四舍五入函数，精度是正数小数点之后，负数时小数点之 前

ceil(x) 向上取整，返回大于等于x的最小整数值

floor(x) 向下取整，返回小于等于x的最大整数值

ABS(x) 返回x的绝对值

****mod(num1， num2) 求余函数

练习3

9.求部门平均工资大于1600的部门编号
select deptno,avg(sal) from emp  group by deptno having avg(sal)>1600
10.求部门平均工资大于1600,并且不是20号部门的部门编号
select deptno,avg(sal) from emp where deptno!=20 group by deptno having avg(sal)>1600
11.列出最低薪金大于1500的各种工作
select job,min(sal) from emp  group by job having min(sal)>1500
12.列出在每个部门工作的员工数量、平均工资
select deptno,count(1),avg(sal) from emp group by deptno
13.列出各个部门的MANAGER（经理）的最低薪金
select deptno,job,min(sal) from emp group by deptno,job having job='MANAGER'
14.查询每个部门每个岗位的工资总和。
select deptno,job,sum(sal) from emp group by deptno,job
15.查询部门人数大于2的部门编号，最低工资、最高工资,部门人数。
select deptno,min(sal),max(sal),count(1) from emp group by deptno having count(1)>2
16.查询部门平均工资大于2000，且人数大于2的 部门编号、部门人数、部门平均工资 ，并按照部门人数升序排序。
select deptno,count(1),avg(sal) from emp group by deptno having avg(sal)>2000 and count(1)>2 order by count(1)
17.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序
select job,avg(sal) from emp where job not like 'SA%' group by job having avg(sal)>2500 order by avg(sal) desc
18.查询岗位不为SALESMAN，工资和大于等于2500的岗位及 每种岗位的工资和
select job,sum(sal) from emp where job!='SALESMAN' group by job having sum(sal)>=2500
19.写一个查询，显示每个部门最高工资和最低工资的差额。
select deptno,max(sal)-min(sal) from emp group by deptno

练习4

1.求姓名为SMITH的员工的工作名称长度
select ename,length(job) from emp where ename='SMITH'
2.求名字长度为6的员工姓名，编号
select ename,empno from emp where length(ename)=6
3.求员工的工资，格式如：姓名是：SMITH,工资是：800
select '姓名是:'||ename||',工资是:'||sal from emp
select concat(concat('姓名是:',ename),concat(',工资是:',sal)) from emp
4.找出emp表中员工名字包含T字母的员工
select ename from emp where ename like '%T%'
select ename,instr(ename,'T',1,1) from emp where instr(ename,'T',1,1)!=0
5.找出emp表里名字第4位之后含T字母的员工
select ename,instr(ename,'T',5,1) from emp where instr(ename,'T',5,1)!=0
6.找出emp表里名字倒数第3位之前含M字母的员工
select ename,instr(ename,'M',-4) from emp where instr(ename,'M',-4)!=0
7.求第五位是H的员工姓名
select ename,instr(ename,'H',5,1) from emp where instr(ename,'H',5,1)=5
8.求姓名中带有三个L的姓名
select ename,instr(ename,'L',1,3) from emp where instr(ename,'L',1,3)！=0 and instr(ename,'L',1,4)=0
9.将员工姓名首字母大写，其余字母小写
select initcap(ename) from emp
10.将员工姓名首字母小写，其余字母大写
select lower(substr(ename,1,1))||upper(substr(ename,2)) from emp
11.把员工编号改为八位数长度显示，例：7001改为00007001  
select  lpad(empno,8,0) from emp