字符函数

lower将字符串转化为小写

select lower(‘HellO’) from dual;

查询员工名字为patel的信息

select last_name,salary

from s_emp

where lower(last_name)=lower(’patel’)

upper 将字符串转化为大写

select upper(‘hello’) from dual;

initcap 将字符串中的单词首字母转化为大写

select initcap(‘hello world’) from dual;

concat 做字符串拼接，同 ||

select concat(‘hello’,’world’) from dual;

last_name.first_name

select concat(last_name,concat(‘.’,first_name))

from s_emp;

注意：函数可以进行嵌套，从内部往外部执行

length 显示字符串的长度

select length(‘hello’) from dual;

nvl处理列存在的null值的情况（时间，字符串，数字）

注意：nvl处理列为null的时候，第二个参数

和第一个参数类型要匹配

利用lower()或upper()函数实现查询时不区分大小写

eg:

select id,last_name

from s_emp

where lower(last_name) = lower('用户输入的字符串');

数字函数

round 四舍五入

//参数为一个取整，看的是小数点后第一位

select round(35.478) from dual;//35

第二个参数正数表示小数点后取几位

select round(35.478,1) from dual;//35.5

select round(35.478,2) from dual;//35.48

第二参数负数小数点前看的位数，指向的位置

制0看是否有进位

select round(35.478,-1) from dual;//40

select round(35.478,-2) from dual;//0

trunc 截取

select trunc(35.478) from dual;//35

select trunc(35.478,1) from dual;//35.4

select trunc(35.478,-1) from dual;//30

mod 取余

select mod(1500,200) from dual;//100

时间函数

months_between 两个时间点相差几个月

select months_between(’01-8月-18’,’01-9月-18’)

from dual;//1

add_months 给某个时间增加几个月

select add_months(sysdate,2)

from dual;

next_day 下一个星期几是几号

select next_day(sysdate,‘星期五’)

from dual;

last_day 查询指定时间所出月的最后一天是几号

select last_day(sysdate)

from dual;

round 基于时间四舍五入 day month year

按天四舍五入，看星期，逢周四进入下一个星期的第一天

小于周四回到本周的第一天

select round(to_date(’04-9月-18’,’dd-Mon-yy’),’day’)

from dual;

按照星期四舍五入，看天数，逢16号进入下一个月的第一天

    小于16号的进入本月的第一天

select round(to_date(’04-9月-18’,’dd-Mon-yy’),’month’)

from dual;

按照年截取，看月份，逢7月进入下一年的第一天

小于7月的进入单前年份的第一天

select round(to_date(’04-9月-18’,’dd-Mon-yy’),’year’)

from dual;

trunc 基于时间截取

按天截取，回到单前星期的第一天

select trunc(to_date(’04-9月-18’,’dd-Mon-yy’),’day’)

from dual;

按照月截取，回到单前月的第一天

select trunc(to_date(’04-9月-18’,’dd-Mon-yy’),’month’)

from dual;

按照年截取，回到单前年的第一天

select trunc(to_date(’04-9月-18’,’dd-Mon-yy’),’year’)

from dual;

转换函数

to_date() 将字符串转化为时间

第二个参数表示时间的格式

简体中文环境

select to_date(’04-9月-18’,’dd-Mon-yy’)

from dual;

英文

select to_date(’04-May-18’,’dd-Mon-yy’)

from dual;

时间格式英文环境下 dd-mm-yy

select to_date(’04-May-18’,’dd-mm-yy’)

from dual;

select to_date(’04-09-18’,’dd-mm-yy’)

from dual;

to_number 将字符串类型的数字转化为数字类型

select to_number(‘23232’)

from dual;

to_char

将数字类型转化为特定格式的字符串

第一个参数表示数字，第二个参数表示格式

select to_char(34.567，‘fm$999,999.999’)

from dual;

$ 表示美元符号

9 表示占位，不补位

0 表示占位 补位

，千分符

.小数点

L 本地币种，和操作系统有关

fm 去掉打印字符串中的空格

将时间类型转化为特定格式的字符串

select to_char(sysdate,’’)

from dual;

第二个参数格式：

YYYY 完整的年份

MM 月份

D 单前时间指定星期的第几天

DD 单前时间指定月份的第几天

DDD 单前年份的第几天

year 英文的年份

month 英文的月份

ddsp 英文的本月几号

ddspth  英文的本月第几号

day英文版本的星期几

dy 英文版本的星期几缩写

hh:mi:ss 小时分钟秒

AM 和PM效果一样，显示上下午

hh24:mi:ss显示时间小时按照24进制

注意：在时间转化为字符串的过程中

格式中有特定的单词符号等，用双引号引起来

select to_char(sysdate,’dd “of” Month’)

from dual;

千年虫

在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，

假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情

况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算

就会出现问题。这个事情当时被称为千年虫

数据库中表示日期中年份的有俩种: yy和rr

之前一直使用的时候yy格式,后来才有的rr格式

yy表示使用一个俩位数表示当前年份:

1990 ---yy数据库格式---> 90

1968 ---yy数据库格式---> 68

1979 ---yy数据库格式---> 79

rr格式表示: 另外参照图片:rr日期格式规则.png

如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：

规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。

规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。

规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。

规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。

注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。

多表查询中的运算：union、union all，minus、intersect

union并集（重复的元素只保留一个）

select id

from s_emp

where id<5 //1,2,3,4

union

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果1，2，3，4，5，6，7，8，9

union all并集(不会去掉重复的元素)

select id

from s_emp

where id<5 //1,2,3,4

union all

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果1，2，3，4，4，5，6，7，8，9

minus 第一个结果去掉和第二个结果相同的数据

select id

from s_emp

where id<5 //1,2,3,4

minus

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果1，2，3

注意：rownum是伪列数据库表不存在该列，查询数据的时候数据库会给每一行数据分配一个rownum，rownum从1开始的连续数字的标记

rownum=1 获取第一条数据，其它的数字不能写

rownum<=n n可以是大于0的任意值

rownum>=m m只能为0，获取所有的数据

eg:部门表中第3条到第5条数据？

select id,name,region_id

from s_dept

where rownum<=5

minus

select id,name,region_id

from s_dept

where rownum<=2；

intersect交集

select id

from s_emp

where id<5 //1,2,3,4

intersect

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果4

组函数：对数据库中的表进行分组，分完组之后每组给出一个结果（一行）

group by 基于某列或某几列对表进行分组

分完组之后给出一行结果,分组的标准该列值相同的

归位一组

having 对group by 分组之后的结果一行一行

的过滤用的，having可以不出现，一旦出现只能

在group by的后面

select

from table_name…

where 条件语句（对from查询的结果处理）

group by column_name…

having 条件语句（对group by 分组的结果处理）

order by column_name [asc|desc]...

执行的流程 from->where->group by->

having->select->order by

注意：group by在where后面执行，所有分组

函数不能出现where中

group by 后面出现的列可以出现在select

后面，也可以不出现，但是select后面出现的列

一定要出现在group by后面

分组函数 select having order by

分组函数（对分好的组中数据处理）

先不用group by，把整张表的数据作为

一组

avg求平均值

查询员工的平均工资

select avg(salary)

from s_emp;

select avg(distinct salary)

from s_emp;

count统计有多少行

查询员工总数

select count(*)

from s_emp;

max求最大值

min求最小值

查询员工的最大工资和最小工资

select max(salary),min(salary)

from s_emp;

stddev 标准差

查询薪水的标准差

select stddev(salary)

from s_emp;

sum 求总和

查询每个月发薪水的总和

select sum(salary)

from s_emp;

variance方差

查询薪水的方差

select variance(salary)

from s_emp;

和group by结合

avg求平均值

查询的每个部门平均工资

select avg(salary),dept_id

from s_emp

group by dept_id;

count统计有多少行

查询每个部门员工总数

select count(*)

from s_emp

group by dept_id;

max求最大值

min求最小值

查询每个部门员工的最大工资和最小工资

select max(salary),min(salary)

from s_emp

group by dept_id;

stddev 标准差

查询每个部门薪水的标准差

select stddev(salary)

from s_emp

group by dept_id;

sum 求总和

查询每个部门每个月发薪水的总和

select sum(salary)

from s_emp

group by dept;

variance方差

查询每个部门薪水的方差

select variance(salary)

from s_emp

group by dept_id;

select avg(salary),dept_id,last_name

from s_emp

group by dept_id,last_name;

注意：group by后面出现多列的时候

按照组合分组，

案列

查询s_emp表中部门的平均工资大于

等于1400的部门

select avg(salary),dept_id

from s_emp

group by dept_id

having avg(salary)>=1400;

查询s_emp表中部门的平均工资大于等

于1400的部门,并且显示出这些部门的

名字,同时按照部门编号进行排序

select avg(salary),s.dept_id,d.name

from s_emp s,s_dept d

where s.dept_id=d.id

group s.dept_id,d.name

having avg(s.salary)>=1400

order by s.dept_id;