oracle 11g 学习笔记10_29(2)

来源：互联网发布：2006科比季后赛数据编辑：程序博客网时间：2024/04/28 22:46

sql函数的使用

一、字符函数

字符函数是oracle中最常用的函数，
字符函数：
.lower(char):将字符串转化为小写的格式
.upper(char):将字符串转化为大写的格式
.length(char):返回字符串的长度。
.substr(char,m,n):取字符串的子串
其中，m代表从第m个开始取，n代表取的子串长度

将所有员工的名字按小写的方式显示
将所有员工的名字按大写的方式显示
显示正好为5个字符的员工的姓名
显示所有员工姓名的前三个字符
以首字母大写的方式显示所有员工的姓名

//1、完成首字母大写select upper(substr(ename,1,1)) from emp;//2、完成后面字母小写select upper(substr(ename,1,;length(ename)-1)) from emp;//3、合并select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

以首字母小写的方式显示所有员工的姓名

select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;

.replace(char1,search_string,replace_string)
其中，search_sting 是要查找的原字符串，replace_string 是替换的字符串。
.instr(char1,char2,[,n[,m]])取子串在字符串的位置
显示所有员工的姓名，用"a"替换所有的"A"

select replace(ename,'A','a') from emp;

二、数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括
cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,
我们最常用的：
.round(n,[m])
该函数用来执行四舍五入，如果省掉m，则四舍五入到正数；如果m是正数，则四舍五入到小数点的m位后，如果m是负数，则四舍五入到小数点的m位前
.trunc(n,[m])
该函数用于截取数字，如果省掉m，就截去小数部分，如果m是正数就截去到小数点的m位后，如果m是负数，则截去到小数点的前m位
.mod(m,n) m对n取模
.floor(n) 返回小于或等于n的最大整数
.ceil(n) 返回大于或等于n的最大整数

三、日期函数

日期函数用于处理date类型的shuju默认情况下日期格式是dd-mon-yy 即12-7月-78
（1）sysdate:该函数返回系统时间
（2）add.months(d,n)
（3）last_day(d):返回指定日期所在月份的最后一天
查找已经入职8个多月的员工

select * from emp where sysdate > add_months(hiredate,8);

其中，add_months(hiredate,8)表示在雇佣日期加上8个月。要是sysdate大于这个已经加了8个月时间的，那么该员工就符合条件了。因为，如果是入职2个月的话，加8个月肯定是大于系统时间的。
显示满10年服务年限的员工的姓名和受雇日期

select * from emp where sysdate>= add_months(hiredate,12*10);

对于每个员工，显示其加入公司的天数
如果是这样：

select sysdate-hiredate "入职天数",ename from emp;

入职天数 ENAME
---------- ----------
8732.63312 pangzi
8943.63312 小红
11639.6331 SMITH
11574.6331 ALLEN
11572.6331 WARD
那么就会出现天数有小数点，那是因为它把不够一天的小时也算进去了。
如果不想出现小数点，那么我们可以这样：

select floor(sysdate-hiredate)||'天' "入职天数",ename from emp;

入职天数 ENAME
------------------------------------------ ----------
8732天 pangzi
8943天小红
11639天 SMITH
11574天 ALLEN

11572天 WARD

找出各月倒数第三天受雇的所有员工

select hiredate, ename from emp where last_day(hiredate)-2 = hiredate;

HIREDATE ENAME
----------- ----------
1981/9/28 MARTIN
其中要注意的是last_day(hiredate)-2是减去 2 不是 3；

四、转换函数

转换函数用于将数据类型从一种转为另一种，在某些情况下，oracle server 允许值的数据类型和实际的不一样，这是oracle server会隐含的转化数据类型
比如：
create table t1(id int);
insert into t1 values('10') -->这样oracle 会自动地将'10' -->10
create table t2 (id varchar2(10));
insert into t2 values(1); -->这样oracle 就会自动地将 1 -->'1'
尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况。为了提高可靠性，应该使用转换函数进行转换。

*to_char
.显示日期的时/分/秒
.to_char(time,'yyyy-mm-dd hh24:mi:ss')
yy:两位数字的年份 2012-->12
yyyy: 四位数字的年份
mm: 两位数字的月份 8月-->08
dd: 两位数字的天 29号 -->29
hh24: 24小时制
hh12: 12小时
mi,ss -->显示分钟\秒

select ename, empno, job, to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'), sal from emp;

ENAME EMPNO JOB TO_CHAR(HIREDATE,'YYYY-MM-DDHH SAL
---------- ----- --------- ------------------------------ ---------
pangzi 9999 CLERK 1988-12-02 00:00:00 2456.34
小红 9998 MANAGER 1988-05-05 00:00:00 28.90
shouzi 9997 manager 2012-10-29 16:51:38 3800.50
最后那个有时分秒是我最新插入进去的，插入时hiredate那用了sysdate。要是插入数据时没有用时分秒来表示，那么显示的时候时分秒那里都是为零。
.显示薪水的指定的货币符号
9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.: 在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前面加美元符号
L：在数字前加本地货币符号
C：在数字前面加国际货币符号

select ename, to_char(sal,'L9999.99') from emp;

ENAME TO_CHAR(SAL,'L9999.99')
---------- -----------------------
pangzi ￥2456.34
小红￥28.90
shouzi ￥3800.50
SMITH ￥800.00

select ename, to_char(sal,'L9,999.99') from emp;

ENAME TO_CHAR(SAL,'L9,999.99')
---------- ------------------------
pangzi ￥2,456.34
小红￥28.90
shouzi ￥3,800.50
SMITH ￥800.00

显示1980年入职的所有员工

select * from emp where to_char(hiredate,'yyyy')='1980';EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20

显示所有12月份入职的员工。
select * from emp where to_char(hiredate,'mm')=12;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
9999 pangzi CLERK 7782 1988/12/2 2456.34 55.66 10
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20

oracle的('yyyy-mm-dd hh24:mi:ss')这个可以用得很灵活，想取那个时间段就取那个时间段。

五、系统函数

terminal：当前会话客户所对应的终端标识符
lanuage：语音
db.name：当前数据库名称
nls_date_format：当前会话客户所对应的日期格式
session_user：当前会话客户所对应的数据库用户名
current_schema: 当前会话客户所对应的默认方案名
host：返回数据库所在主机的名称

select sys_context('userenv','nls_date_format') from dual;

SYS_CONTEXT('USERENV','NLS_DAT
--------------------------------------------------------------------------------
DD-MON-RR

select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME
--------------------------------------------------------------------------------
orcl

select sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAG
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

*用户和方案的关系：

一旦用户创建之后，oracle就会自动地创建一个方案，oracle是以方案的方式来管理数据库对象的，方案的名字跟用户名一模一样，方案里面有很多的数据对象，比如有表，视图，触发器，存储过程等等。

要是该列是中文列，那么列名称要用双引号括起来。要是是改表的谋列中的中文数据，那么只要单引号括起来。