Oracle数据库整理-(中篇)

继上一篇博文

二.数据查询操作

8.单值函数

函数分为:
1.单值函数

• 1.字符函数
• 2.日期函数
• 3.转换函数
• 4.数字函数

2.分组函数

• avg 求平均值
• count 计算有多少条数据
• max 最大值
• min 最小值
• sum 求和
• stddev 标准差
• variance 方差

字符函数

lower 把字符转为小写
例:把’HELLO’转换为小写

  select lower('HELLO')from dual;

upper 把字符转换为大写
例:把’world’转换为大写

  select upper('world') from dual;

concat 把俩个字符串连接在一起(类似之前的||的作用)
例: 把’hello’和’world’俩个字符串连接到一起,并且起个别名为msg

  select concat('hello','world') msg from dual;

substr 截取字符串
例: 截取’hello’字符串,从第2个字符开始(包含第二个字符),截取后面连续的3个字符

select substr('hello',2,3) from dual;

length 获得字符串长度
例:获得’world’字符串的长度

  select length('world') from dual;

我这里写几个比较重要的

数字函数

round 四舍五入
round(arg1,arg2)
第一个参数表示要进行四舍五入操作的数字
第二个参数表示保留到哪一位

例: 保留到小数点后面2位

  select round(45.923,2) from dual;

例:保留到个位 (个十百千万…)

  select round(45.923,0)  from dual;

trunc 截取到某一位
trunc(arg1,arg2)
和round的用法一样,但是trunc只舍去不进位
例:截取到小数点后面2位

 select trunc(45.929,2) from dual;

mod 取余
mod(arg1,arg2)
第一个参数表示要进行取余操作的数字
第二个参数表示参数1和谁取余
例:把10和3进行取余 (10除以3然后获取余数)

select mod(10,3) from dual;

日期函数
sysdate关键字
表示系统的当前时间
例:显示时间:当前时间

select sysdate from dual;

months_between 俩个日期之间相差多少个月(单位是月)
例:30天之后和现在相差多少个月

select months_between(sysdate+30,sysdate) from dual;

round 对日期进四舍五入,返回操作后的日期数据
例:把当前日期四舍五入到月

select round(sysdate,'MONTH') from dual;

trunc 对日期进行截取 和round类似,但是只舍弃不进位

转换函数
to_char 把日期转换为字符
把当前日期按照指定格式转换为字符串

select to_char(sysdate,'yyyy') from dual;

日期格式：

yyyy：四位数的年份
rrrr：四位数的年份
yy：两位数的年份
rr：两位数的年份
mm：两位数的月份(数字)
D：一周的星期几
DD：一月的第几天
DDD ：一年的第几天
YEAR：英文的年份
MONTH：英文全称的月份
mon：英文简写的月份
ddsp：英文的第几天(一个月的)
ddspth：英文序列数的第几天(一个月的)
DAY：全英文的星期
DY：简写的英文星期
hh：小时
mi：分钟
ss：秒

to_char 把数字转换为字符

 select to_char(salary,'$999,999.00')  from s_emp;

to_number 把字符转换为数字

 select to_number('1000')  from dual;

to_date 把字符转换为日期
例:

select to_date('10-12-2016','dd-mm-yyyy')from dual;

9.组函数

avg 求平均值
count 计算有多少条数据
max 最大值
min 最小值
sum 求和
stddev 标准差
variance 方差

• group by 在查询表中数据的时候进行分组的关键字
• having 分组之后的进行进一步数据筛选的关键字,having和where的功能类似
• 组函数(分组函数),是指将数据按照某列的值进行分组后，然后使用组函数分别对每个分好的小组中的数据进行处理。所以组函数一般要结合着分组关键字group来使用
• sql语句的各部分构成
  select …
  from …
  where …
  group by …
  having …
  order by …
  注:除了select和from之外其他的都不是必须的。
  假如select…from…后面的语句都出现了,那么他们的执行顺序为:
  where–>group by分组–>执行组函数–>having筛选->order by
• 组函数出现的位置:
  1.select后面
  2.having后面
  3.order by后面
  4.where后面一定【不能】出现组函数
  注意:如果select/having语句后面出现了组函数,那么select/having后面没有被组函数修饰的列，就必须出现在group by 后面
• where和having对比:
  1.where和having都是做条件筛选的
  2.where执行的时间比having要早
  3.where后面不能出现组函数
  4.having后面可以出现组函数
  5.where语句要紧跟from后面
  6.having语句要紧跟group by后面
• group by和having的关系:
  1.group by可以单独存在,后面可以不出现having语句
  2.having不能单独存在,有需要的话,必须出现在group by后面
• order by语句
  1.如果sql语句中需要排序,那么就一定要写在sql语句的最后面
  2.order by后也可以出现组函

例: 使用组函数:结合group分组以及having筛选使用数
查询s_emp表中部门的平均工资大于等于1400的部门

select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>=1400;

10.子查询

• 子查询(嵌套查询) :即一个select语句中嵌套了另外的一个或者多个select语句
  注意: 可以把子查询的结果作为一张表来使用

例:查询工资比Simth工资高的员工信息

select last_name,salary
from s_emp
where salary>(
	select salary
	from s_emp
	where last_name='Smith'
);

例:查询平均工资比 41号部门的平均工资 高 的部门中员工的信息
1.先查询44号部门的平均工资

select avg(salary) from s_emp where dept_id = 44

2.再查询平均工资比 41号部门的平均工资 高 的部门
select dept_id from s_emp
group by dept_id
having avg(salary) > (41号部门的平均工资)
=>也就是

select dept_id from s_emp 
group by dept_id
having avg(salary) > (
				select avg(salary) from s_emp 
				where dept_id = 44)

3.然后最后查其部门的员工信息
select dept_id, last_name,salary from s_emp
where dept_id in (上面查出来的部门)
=>也就是

select dept_id, last_name,salary from s_emp
where dept_id in (
			select dept_id from s_emp 
			group by dept_id
			having avg(salary) > (
					select avg(salary) from s_emp 
					where dept_id = 44)
				)

11.对查询结果集的操作

如果有俩条sql语句,每一条sql都可以查询出一个结果,这个被称之为结果集。那么我们可以使用下面的关键字对俩个结果集进行操作

• union 获得俩个结果集的并集
• union all 把俩个结果集 合在一起显示出来
• minus 第一个结果集除去第二个结果集和它相同的部分
• intersect 获得俩个结果集的交集

注意:前提条件 俩个结果集中【查询的列】要完全一致
union 获得俩个结果集的并集
例如:

        select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id=s_dept.id(+)
		union
		select last_name,dept_id,name 
		from s_emp,s_dept
		where s_emp.dept_id(+)=s_dept.id;

其他几个测试就是将其中关键字换一下;

12.oracle中的伪列 rownum

• 伪列rownum，就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。它会根据返回的结果为每一条数据生成一个序列化的数字.rownum是oracle才有的伪列

• rownum 只能等于1 如果让其等于其他数 则查不到数据

select last_name from s_emp where rownum=1;

• rownum 大于0 如果让其大于其他数 则查不到数据

select last_name from s_emp  where rownum>0

• rownum 可以小于任何数

select last_name from s_emp where rownum<7

因此我们可以根据rownum来对Oracle数据库中查询出来的数据进行分页

select last_name ,first_name 
from ( 
		select rownum r,last_name,first_name 
		from s_emp where rownum <= 20 
	 ) 
where r > 10