oracle数据库(二)

model 2   Sorting & Limiting Selected Rows
1.order by字句的使用:
1)order by子句始终在整个select语句中的最后面(实际执行的时候，也是最后执行)
2)order by后可以跟列名，别名，列的序号
3)order by后可跟排序规则：默认升序(asc),降序为desc
4)order by后可跟多列，表示先按第一列进行排序，排序后当第一列的值相同再按第二列排序...,多个排序规则之间用逗号,分隔
5)空值排序null(永远是最大的)，升序在最下边，降序在最上边
 (1)查询s_emp表中id，first_name,salary，并且按照工资排序(升、降)
 select id,first_name,salary sa
 from  s_emp
 order by 3 desc;


 (2)查询s_emp表，并且按照last_name降序排序
注意：如果按照字符串类型的列排序，是根据ACSII值进行排序
select last_name
from s_emp
order by 1 desc;
 (3)查询s_emp表，并且按照manager_id升序排序,并按照工资（降序）排序
select manager_id,salary
from s_emp
order by manager_id asc,salary desc;


 (4)查询s_emp表员工的last_name,commission_pct按照提成排序(升降)
select last_name,commission_pct
from s_emp
order by 2;


2.where子句的使用：
  语法：select col_name
from table_name
where col_name 比较操作表达式 逻辑操作符 col_name2 比较操作表达式2
order by ...
    1)作用：对sql的结果集进行筛选过滤。
    2)位置：紧跟在from子句后边
3)内容：由一个或多个限定条件(是否满足该条件)组成，限定条件由表达式、比较符、字面值组成
4)where子句中所有字符串和日期必须要用单引号括起来，区分大小写，日期在Oracle里有特定的格式
’DD-MON-YY’如'12-3月-16',否则作为一个字符串,数值不需要单引号
5)常见的几种比较操作符：
(1)逻辑操作比较符：
>,<,=,>=,<=,不等号!=,<>,^=
  (5)查询last_name是Chang的员工的id，last_name，start_date-----查询41号部门的信息
  select id,last_name,start_date
  from s_emp
  where last_name = 'shang';


  (5)查找所有工资大于1000的员工的first_name，salary
select first_name,salary
from s_emp
where salary>1000;


  (6)查找不在45号部门工作的所有员工的id，last_name,dept_id,并且按照dept_id升序排序
select id,last_name,dept_id
from s_emp
where dept_id != 45
order by dept_id asc;


  (7)查找员工入职日期在1990年11月30日以后的员工的last_name，start_date,按照start_date升序排序
  日期：select sysdate from dual;
> <
select last_name,start_date
from s_emp
where start_date > '30-11月-90';


  (8)查找员工入职日期在1990年11月30日到1991年11月30日的last_name，start_date,按照start_date
 降序排序
select last_name,start_date
from s_emp
where start_date between '30-11月-90' and '30-11月-91'
order by start_date desc;




(2)sql比较操作符:
 between a1 and a2:在a1与a2之间,包括a1，a2
 in(a1,a2,a3):在一个列表中，如in(1,2,3)，值在1,2,3中
 like:模糊匹配，用于值不是精确的值时
      需使用通配符(可以代表任何内容的符号)
  %:匹配任意多个字符,0--n个
  _:匹配一个字符
  escape：指定转义符，转义符可将特殊符号转义为本身字符含义
 is null:值为空，对空值需要用is null或者is not null进行判断
 is not null：值不为空
(3)逻辑操作符：
 当条件有多个的时候使用
and:且逻辑
or:或逻辑
同时出现and优先级更高
 not:非逻辑
   (7)查找员工id在[5,20]之间的所有员工的id和last_name
select id,last_name
from s_emp
where id between 5 and 20;


   (8)查找员工id不在[5,20]之间且职位名称为Stock Clerk（存库管理员）的所有员工的id和last_name
select id,last_name,title
from s_emp
where (id < 5 or id >20) and title ='Stock Clerk';
select id,last_name,title
from s_emp
where id not between 5 and 20 and title ='Stock Clerk';
   (9)查找在41或者43或10号部门工作的员工的id和last_name;
select id,last_name,dept_id
from s_emp
where dept_id /not/ in(41,43,10);


   (10)查看员工last_name以C字母开头的员工的id，last_name,工资
   _:匹配一个字符
   %
select id,last_name,salary
from s_emp
where last_name like 'C%';


C%:
C_:只有两个字符，以C开头
C_%:至少两个字符，以C开头
C%_:


  查看last_name以g结尾的员工的id，工资
select id,salary,last_name
from s_emp
where last_name like '%g';


  查看last_name第二个字符是g的员工的id，工资
select id,salary,last_name
from s_emp
where last_name like '_g%';




       查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资
select id,salary,last_name
from s_emp
where last_name like '___n_%';


  插入一条语句：
insert into s_emp(id,last_name) values(999,'_briup');//
commit;
在s_emp表中添加一条信息


       查看last_name以_开头的员工id和last_name
select id,last_name
from s_emp
where last_name like '#_%' escape '#';
  查看last_name以_%开头的员工信息？
insert into s_emp(id,last_name) values(998,'_%briup');


select id,last_name
from s_emp
where last_name like '#_#%%' escape '#';


   (13)查看员工提成为为空的员工的id和名字
select id,last_name
from s_emp
where commission_pct is null;


   (14)查看员工部门id为41且   职位名称为Stock Clerk或者id不为null   的员工id和名字
select id,last_name
from s_emp
where dept_id =41 and (title ='Stock Clerk' or id is not null);


   (15)查看员工部门为41或者44号部门  且工资大于1000的员工id和名字
select id,last_name
from s_emp
where dept_id in (41,44) and salary>1000;


   (16)查看员工部门为41且工资大于1000 或者 44号部门的所有员工id和名字,salary
select id,last_name,salary,dept_id
from s_emp
where dept_id=41 and salary >1000 or dept_id=44;




Lesson 3  Single Row Functions
函数：这里的函数相当于java中写好的一些方法，有名字，可以传递参数，实现某一项具体功能。
函数分为:
 1.单行函数
 1)字符函数
 2)日期函数
 3)数字函数
 4)转换函数


学前须知：
哑表dual
 select sysdate
 from dual;
 dual是一个虚拟表，辅助查找和运算。通常用在select语句中，作为查询的目标表结构，oracle保证dual里面永远只有一条记录。
 例如:
 显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法
 select 1+1 from dual;
    1)字符函数
  (1)LOWER:转换为小写
  (2)UPPER:转换为大写
  (3)INITCAP：转换为首字母大写其他字母小写
  (4)CONCAT：拼接字符串(||)
  (5)SUBSTR(str,start,length):字符串截取
参数1:要截取的字符串
abcd
start：
1   :a
3 :c
-1 :d
-3 :b
参数2：开始截取的位置（从1开始）,如果参数2为负数，开始位置为倒数第n个值
参数3：截取的长度,如果没有参数三，会截取到最后一个字符
  (6)LENGTH:获取字符串长度
  (7)nvl(null_col,var):转换空值
参数1：值可能为空的列。
参数2：如果列值为null，则返回参数2的值


lower('')：将选中的或列值转换为全小写
   如将“HELLO”转化为小写
   select lower('HELLO') from dual
例：把s_emp表中的last_name列的值转换为小写
   查询s_emp表中last_name为chang的员工，姓名不区分大小写。


upper(''):将选中的列或值转换为全大写
   如将"Hello"转换为大写
   select upper('Hello') from dual;
例：查询s_emp表中last_name为Ngao的员工，姓名不区分大小写


initcap(''):字符串首字母大写,
（每一个单词的首字母大写，多个单词空格
分隔）
例如:把'hELLO'转换为首字母大写,其余字母小写


concat('','')：把俩个字符串连接在一起(类似之前的||的作用)
如把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg
例:把first_name和last_name俩个列的值连接到一起，中间以"."连接
'first_name'||'.'"last_name"
concat('fist_name',concat('.','last_name'))


substr:截取字符串,(str,start(从1开始),length)
   如:截取'hello'字符串,从第2个字符开始包括第二个,截取后面的3个字符
如果参数2为负数，则开始位置为str倒着往回数作为起始点
   例：select substr('hello',2,3) from dual;
ell


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




select substr('hello',1) from dual;


select substr('hello',0) from dual;//0和1都是表示截取的开始位置为第一个字符


select substr('hello',-1) from dual;


select substr('hello',-1,3) from dual;


select substr('hello',-5) from dual;


select substr('hello',-6) from dual;


length('')：获取字符串长度：
如获得'world'字符串的长度
select length('world') from dual;
例如:获得s_emp表中salary列的每个值的字符长度
nvl：转换空值，...
select nvl(commission_pct,'a') from s_emp;
select nvl(first_name,'a') from s_emp;
例：查找部门在41|42|43中，last_name中包含‘n’(不区分大小写)且first_name长度>5的所有员工的
2)数字函数
  (1)ROUND:四舍五入
  (2)TRUNC：只舍不入(截取)
  (3)MOD:取余


  round(arg1,arg2):
arg1表示要操作的数据
arg2(n)表示要保留到哪一位(0,正数表示保留到小数点后第n位，第n+1位四舍五入
负数表示保留到小数点前第|n|+1位，第n位 决定舍还是入)
不写默认0
保留到小数点后面2位
select round(45.923,2) from dual;
保留到个位
select round(45.923,0) from dual;
保留到十位
select round(45.923,-1)from dual;
例：select round(46.852,2) from dual;
select round(46.852,1) from dual;
select round(46.852,0) from dual;
select round(46.852,-1) from dual;
select round(46.852,-2) from dual;
select round(146.852,-2) from dual;
  trunc(arg1,arg2):round的用法一样,但是trunc只舍去不进位
arg1表示要操作的数据
arg2(n)表示要保留到哪一位(0,正数表示保留到小数点后第n位，多余的删除
负数表示截取到|n|+1位，其余全部置0)，不写默认0
select trunc(46.852,2) from dual;
select trunc(46.852,1) from dual;
select trunc(46.852,0) from dual;
select trunc(46.852,-1) from dual;
select trunc(46.852,-2) from dual;
select trunc(146.852,-2) from dual;
  mod(arg1,arg2)：取余
 第一个参数表示要进行取余操作的数字
 第二个参数表示参数1和谁取余
例如:
把10和3进行取余 (10除以3然后获取余数)
select mod(10,3) from dual;
3）日期函数：
 1) MONTHS_BETWEEN:两个日期相差几个月
 2) ADD_MONTHS:返回给定日期加上n个月后的日期
 3) NEXT_DAY:返回代表给定日期起即将到来的星期几的那一天
 4) LAST_DAY:返回给定日期所在月份的最后一天
     5) ROUND:对指定日期进行四舍五入
     6) TRUNC:对指定日期进行截取


sysdate关键字:表示系统的当前时间
显示时间:当前时间:select sysdate from dual;
注意:sysdate进行加减操作的时候,单位是天
显示时间,明天的这个时候:select sysdate+1 from dual;
显示时间,昨天的这个时候:select sysdate-1 from dual;
显示时间,1小时之后的时候:select sysdate+1/24 from dual;


months_between(arg1,arg2):俩个时间点之间相差多少个月(单位是月)
例如:
30天之后和现在相差多少个月
select months_between(sysdate+30,sysdate) from dual;
select months_between('01-7月-15','01-8月-16') from dual;
add_months(args1,x):返回第一个参数代表的时间点,往后推x月的日期
例如:
'01-2月-2016'往后推2个月
select add_months('01-2月-2016',2) from dual;
例如:
当前时间往后推4个月
select add_months(sysdate,4) from dual;


next_day(args1,args2):返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
例如:
当前时间的下一个星期5是哪一个天
select next_day(sysdate,'星期五') from dual;（按照当前语言环境设置）
select next_day(sysdate,3) from dual;


last_day(args):返回一个日期数据:表示一个日期所在月份的最后一天
例如:
当前日期所在月份的最后一天
select last_day(sysdate) from dual;


round('日期','格式'):对日期进n舍n+入,返回操作后的日期数据。




格式：
精确到年:year /y/yy/yyy/yyyy 6月舍7月进
精确到月:month /mm 15日舍16日进
精确到周:day /d 周3舍周4进
精确到日:dd 12点前舍12点后进
round(sysdate,'year/y/yy/yyy/yyyy') 年   7月节点
round(sysdate,'mm/month')  月 16号节点
round(sysdate,'d/day')  星期   星期四节点
round(sysdate,'dd') :  天  -》 12点节点
例如:
把当前日期15舍16入到月
select round(sysdate,'MONTH') from dual;
select round(sysdate,'mm') from dual;
把当前日期6舍7入到年
select round(sysdate,'YEAR') from dual;
select round(sysdate,'yyy') from dual;


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


   trunc  对日期进行截取 和round类似,但是只舍弃不进位
trunc（sysdate,'yyyy/year'） --返回当年第一天。
　　trunc（sysdate,'mm/month'） --返回当月第一天。
　　trunc（sysdate,'d/day'） --返回当前星期的第一天。
　　trunc（sysdate,'dd'）--返回当前年月日
3)类型转换函数：
1)TO_CHAR:将日期或者数值转换成字符串
  2)TO_NUMBER:将字符串转换成数字(只能将能够转换的转换如“12”不是“ab”)
    3)TO_DATE:将日期字符串转换成日期


to_char(arg1,arg2):将日期或者数值转换为字符串
日期：arg1为给定日期，需日期类型，可按照arg2指定格式转换
arg1:sysdate,sysdate+-


 可选择转换的日期格式：
yyyy/YYYY/rrrr：四位数的年份
yyy
yy/rr：两位数的年份
y
YEAR：英文的年份
mm：两位数的月份（两位数字）
mon：英文简写的月份
MONTH：英文全称的月份
DAY：全英文的星期
DY：简写的英文星期
dd:日
D：一周的第几天
DD：一月的第几天
DDD ：一年的第几天
ddsp：英文的第几天
ddspth：英文序列数的第几天
hh：小时
hh24: 24小时制
mi：分钟
ss：秒
AM:上午
PM:下午
表示年份时yy与rr
千年虫:
2038年:
在早期的计算机的程序中为了节省存储空间规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫。
千年虫问题产生的原因是由于在计算机软、硬件以及数字化程序控制芯片的各种设备和业务处理系统中，只使用了两位十进制数来表示年份，因此，当日期从1999年12月31日进入2000年1月1日后，系统将无法正常识别由“00”表示的2000年（计算机可能将这个年分识别为1900年〕这一具体年份，从而带来进行跨世纪的年份、日期处理时的计算错误，引发各种各样的计算机业务处理系统和控制系统的功能紊乱


数据库中表示日期中年份的有俩种: 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。
select to_char(to_date('25-3月-10','dd-month-rr'),'yyyy-mm-dd') from dual;


select to_char(to_date('25-3月-88','dd-month-rr'),'yyyy-mm-dd') from dual;


例如:
把当前日期按照指定格式转换为字符串
select to_char(sysdate,'yyyy') from dual;
测试常见的一些日期数据转换为字符串的格式
select to_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY') from dual;
select to_char(sysdate,'dd-mm-yy') from dual;
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual;
select sysdate,to_char(sysdate,'yyyy-ddd pmhh24:mi:ss') from dual;
select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM')from dual;


数值：
to_char(数字，'格式') 把数字转换为字符
L : 本地货币符号
$ : $
. : 小数点
, : 千分符
9 :  0-9
0 :  0-9, 如果位数不足，强制补0


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


fm表示去除结果显示中的开始的空格
select to_char(salary,'fm$999,999.00') from s_emp;


L表示系统本地的货币符号
select to_char(salary,'fmL999,999.00') from s_emp;


to_number(char): 把字符转换为数字
例如:
select to_number('1000') from dual;


//这个写法是错的 abc不能转换为数字
select to_number('abc') from dual;


to_date(char, ['fmt']) 把字符转换为日期
to_date("要转换的字符串","转换的格式")
两个参数的格式必须匹配，否则会报错。即按照第二个参数的格式解释第一个参数。
select to_date ('10-9月-1992','dd-Month-YYYY') from dual
select to_date('10-12-2016','dd-mm-yyyy') from dual;
select to_date('25-5月-95','dd-month-yy') from dual;
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual;
select to_date('2003-10月-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual;//error,mm:2位数月份
select to_date('2003-10月-17 21:15:37','yyyy-month-dd hh24:mi:ss') from dual;
select to_date('1月-2005-01 13:14:20','mon-yyyy-dd HH24:mi:ss') from dual;




oracle数据库中表示一个日期数据的几种方式
1.sysdate
2.oracle默认的日期格式 例如:'25-9月-95'
3.使用日期函数ADD_MONTHS/NEXT_DAY/LAST_DAY/ROUND/TRUNC
4.to_date函数转换