.net网站开发实例,景点网站设计与制作,公司企业logo设计免费,学会python做网站1. 单行函数与多行函数
1.1 单行函数 指单行数据输入,返回一个值的函数. 所以查询一个表时,对选择的每一行数据都返回一个结果.[oracleoracle-db-19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 7 07:59:44 2023
Version 19.3.0.0.0Copyri…1. 单行函数与多行函数
1.1 单行函数 指单行数据输入,返回一个值的函数. 所以查询一个表时,对选择的每一行数据都返回一个结果.[oracleoracle-db-19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 7 07:59:44 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL show con_name;CON_NAME
------------------------------
CDB$ROOT
SQL show user;
USER is SYS
SQL alter session set containerPDB1;Session altered.SQL conn scott/tigerPDB1;
Connected.
SQL show user;
USER is SCOTT
SQL set pagesize 200 linesize 200
SQL select empno,lower(ename) from emp;EMPNO LOWER(ENAME)
---------- ------------------------------7369 smith7499 allen7521 ward7566 jones7654 martin7698 blake7782 clark7788 scott7844 turner7876 adams7900 james7902 ford7934 miller13 rows selected.SQL 单行函数是用于处理数据项的,它接受一个或多个参数,并为查询返回的每个行返回一个值,参数可以是下列对象: 用户提供的常数变量值列名表达式单上函数具有以下特性: 作用于查询返回的每个行为每行返回一个结果可能会返回与引用数据类型不同的数据值可能需要一个或多个参数可能用在select、where和order by字句中1.2 多行函数 指多行数据输入,返回一个值的函数.所以对表的多组进行操作,并且每组返回一个结果.(典型的是聚合函数) SQL
SQL show user;
USER is SCOTT
SQL select sum(sal) from emp;SUM(SAL)
----------24025SQL
2. 单行函数的几种类型
2.1 字符函数
2.1.1 lower upper initcap
-- upper(sql course) -- sql course 返回大写
-- upper(sql course) -- sql course 返回大写
-- initcap(sql course) -- sql course 返回只有首字母大写SQL select ename,lower(ename),upper(ename),initcap(ename) from emp where enameSMITH;ENAME LOWER(ENAME) UPPER(ENAME) INITCAP(ENAME)
------------------------------ ------------------------------ ------------------------------ ------------------------------
SMITH smith SMITH SmithSQL
2.1.2 concat
-- concat(good,string) -- good string 拼接,只能拼接2个字符串SQL show user
USER is SCOTT
SQL show con_name;CON_NAME
------------------------------
PDB1
SQL select concat(ename,job) from emp where enameFORD;CONCAT(ENAME,JOB)
---------------------------------------------------------
FORDANALYSTSQL
2.1.3substr
-- substr(String,1,3) -- Str 从第1位开始截取3位数
SQL select substr(ename,1,3) from emp where enameSCOTT;SUBSTR(ENAME,1,3)
------------------------------------
SCOSQL
2.1.4 instr
-- instr(t#i#m#r#a#n#,#,5) -- 从第5位起始找#字符在那个绝对位置
SQL select instr(K#L#A#U#S#,#,5) from dual;INSTR(K#L#A#U#S#,#,5)
-------------------------6SQL
2.1.5 length
-- length(String) -- 长度
SQL
SQL show user;
USER is SCOTT
SQL show con_name;CON_NAME
------------------------------
PDB1
SQL select length(ename) from emp where enameSCOTT;LENGTH(ENAME)
-------------5SQL
2.1.6 lpad rpad
-- lpad(first,10,$) -- 左填充
-- rpad (676768,10,*) -- 右填充SQL select rpad(ename,10,$) from emp where rownum 6;RPAD(ENAME,10,$)
------------------------------------------------------------------------------------------------------------------------
SMITH$$$$$
ALLEN$$$$$
WARD$$$$$$
JONES$$$$$
MARTIN$$$$SQL select lpad(ename,10,*) from emp where rownum 6;LPAD(ENAME,10,*)
------------------------------------------------------------------------------------------------------------------------
*****SMITH
*****ALLEN
******WARD
*****JONES
****MARTINSQL
2.1.7 replace
-- replace (JACK and JUE,J,BL) -- 替换字符
SQL select replace(Gkkd Jkb,k,o) from dual;REPLACE(GKKDJKB,K,O
------------------------
Good JobSQL 2.1.8 trim
-- trim (a from aaklausaa) -- 去除前后指定字符
SQL
SQL select trim (a from aaklausaa) from dual;TRIM(AFROMAA
---------------
klausSQL
2.2 数值函数
2.2.1 round 对指定的值做四舍五入 round (p, s) s为正数时, 表示小数点后要保留的位数, s为0或不填写,表示取整四舍五入. s为负数时,表示小数点前要保留的位数. SQL
SQL select round(14535.9856) from dual;ROUND(14535.9856)
-----------------14536SQL select round(14535.9856,2) from dual;ROUND(14535.9856,2)
-------------------14535.99SQL select round(14535.9856,-2) from dual;ROUND(14535.9856,-2)
--------------------14500SQL select round(14555.9856,-2) from dual;ROUND(14555.9856,-2)
--------------------14600SQL 2.2.2 trunc
对指定的值取整
trunc (p, s)
s为正数时, 表示小数点后要保留的位数,
s为0或不填写,表示取整.
s为负数时,表示小数点前要保留的位数.SQL select trunc(14535.9856) from dual;TRUNC(14535.9856)
-----------------14535SQL select trunc(14535.9856,2) from dual;TRUNC(14535.9856,2)
-------------------14535.98SQL select trunc(14535.9856,-2) from dual;TRUNC(14535.9856,-2)
--------------------14500SQL
2.2.3 mod 返回除法后的余数 SQL
SQL select mod(100,12) from dual;MOD(100,12)
-----------4SQL 2.3 日期函数
2.3.1 格式和语言
select * from v$nls_parameters; -- 查询日期语言
select SYSTIMESTAMP from dual; -- 查看日期格式
-- 设置日期语言
alter session set NLS_DATE_LANGUAGEAmerican; -- 只对当前会话起作用
alter system set NLS_DATE_LANGUAGEAmerican; -- 对系统设置生效
alter session set NLS_DATE_LANGUAGESIMPLIFIED CHINESE;
-- 或者这样
TO_DATE (2002-08-26, YYYY-mm-dd, NLS_DATE_LANGUAGE American)
-- 缺省格式:DD-MON-RR.
-- 可以表示日期范围:(公元前)4712 至(公元)9999
2.3.2 日期转字符
# (to_date,to_char)
select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) as nowTime from dual; -- 日期转化为字符串
select to_char(sysdate,yyyy) as nowYear from dual; -- 获取时间的年
select to_char(sysdate,mm) as nowMonth from dual; -- 获取时间的月
select to_char(sysdate,dd) as nowDay from dual; -- 获取时间的日
select to_char(sysdate,hh24) as nowHour from dual; -- 获取时间的时
select to_char(sysdate,mi) as nowMinute from dual; -- 获取时间的分
select to_char(sysdate,ss) as nowSecond from dual; -- 获取时间的秒
2.3.3 字符和时间互换
-- 显示Two Hundred Twenty-Two
SQL
SQL select to_date(2023-03-07 09:32:46,yyyy-mm-dd hh24:mi:ss) from dual;TO_DATE(2023-0)
---------------
07-MAR-23SQL select to_char(to_date(222,J),Jsp) from dual;TO_CHAR(TO_DATE(222,J),JSP)
------------------------------------------------------------------
Two Hundred Twenty-TwoSQL 2.3.4 天数差floor
SQL select floor(sysdate - to_date(20020405,yyyymmdd)) from dual;FLOOR(SYSDATE-TO_DATE(20020405,YYYYMMDD))
---------------------------------------------7641SQL 2.3.5 Month_between
因为日期在oracle里是以数字形式存储的,所以可对它进行加减运算,计算是以天为单位
-- 1、数据类型转换
SQL select months_between (1994-04-01,1992-04-01) mm from dual;
select months_between (1994-04-01,1992-04-01) mm from dual
ERROR at line 1:
ORA-01861: literal does not match format string
-- 报错字符串格式不匹配
-- 原因:
-- 如果直接按照字符串方式,或者直接使用2013-2-26 11:07:25,没有指定日期格式,就会报错
to_date(2013-2-26 11:07:25 , yyyy-mm-dd hh24:mi:ss)
-- 正确如下
select months_between (to_date(1994-04-01,yyyy-mm-dd),to_date(1992-04-01,yyyy-mm-dd)) mm from dual;
-- 参考第三章[数据类型转换]部分-- 2、查找emp表中参加工作时间30年的员工
SQL select * from emp where months_between (sysdate, hiredate)/1230;知识点: 很容易认为单行函数返回的数据类型与函数类型一致,对于数值函数类型而言的确如此,但字符和日期函数可以返回任何数据类型的值. 比如 instr 函数是字符型的, months_between函数是日期型的,但它们返回的都是数值.
2.3.6 Next_day
-- NEXT_DAY的第2个参数可以是数字1-7, 分别表示周日--周六, 比如要取下一个星期六, 则应该是:SQL select next_day(sysdate,7) from dual;NEXT_DAY(SYSDAT
---------------
11-MAR-23SQL select last_day (to_date(1999-06,yyyy-mm)) ld from dual;LD
---------------
30-JUN-99SQL 2.3.7 extract
-- extract()找出日期或间隔值的字段值
-- 获得小时数
SQL SELECT EXTRACT(HOUR FROM TIMESTAMP 2020-09-16 2:39:40) from dual;EXTRACT(HOURFROMTIMESTAMP2020-09-162:39:40)
---------------------------------------------2
--获取分钟数SQL SELECT EXTRACT(minute FROM TIMESTAMP 2020-09-16 2:39:40) from dual;EXTRACT(MINUTEFROMTIMESTAMP2020-09-162:39:40)
-----------------------------------------------39
---获取天数
SQL SELECT EXTRACT(day FROM TIMESTAMP 2020-09-16 2:39:40) from dual;EXTRACT(DAYFROMTIMESTAMP2020-09-162:39:40)
--------------------------------------------16
--- 获取月份数
SQL SELECT EXTRACT(month FROM TIMESTAMP 2020-09-16 2:39:40) from dual;EXTRACT(MONTHFROMTIMESTAMP2020-09-162:39:40)
----------------------------------------------9
--- 获取年份数
SQL SELECT EXTRACT(year FROM TIMESTAMP 2020-09-16 2:39:40) from dual;EXTRACT(YEARFROMTIMESTAMP2020-09-162:39:40)
---------------------------------------------2020SQL select sysdate,to_char(sysdate,hh24) from dual;SYSDATE TO_CHA
--------------- ------
07-MAR-23 09SQL
2.3.8 add_month
SQL
SQL select add_months(to_date(2023-03-01,yyyy-mm-dd),4) am from dual;AM
---------------
01-JUL-23SQL select add_months(sysdate,-6) from dual;ADD_MONTHS(SYSD
---------------
07-SEP-22SQL
2.3.9 日期函数的运用
2.3.9.1 查看月份天数不定的方法
SQL select to_char(add_months(last_day(sysdate) 1, -1), yyyymmdd) s,to_char(last_day(sysdate),yyyymmdd) e from dual;S E
------------------------ ------------------------
20230301 20230331SQL
2.3.9.2 今年的天数
SQL select add_months(trunc(sysdate,year), 12) - trunc(sysdate,year) days from dual;DAYS
----------365SQL --- 今年2月份的最后一天
SQL select to_char(last_day(to_date(02||to_char(sysdate,yyyy),mmyyyy)),dd) days from dual;DAYS
------
28SQL
2.3.9.3 某天是星期几
SQL select to_char(to_date(2023-03-31,yyyy-mm-dd),day) from dual;TO_CHAR(TO_DATE(2023-03-31
---------------------------
fridaySQL select to_char(to_date(2023-03-31,yyyy-mm-dd),day,NLS_DATE_LANGUAGE simplified chinese) from dual;TO_CHAR(TO_DATE(2023-03-31
---------------------------
星期五SQL
2.3.9.4 去除指定周天的天数
-- 查找2002-02-28至2002-02-01间除星期一和七的天数SQL select count(*)2 from(3 select rownum-1 rnum 4 from all_objects5 where rownum to_date(2023-03-31,yyyy-mm-dd)-to_date(2023-03-01,yyyy-mm-dd)1) 6 where to_char( to_date(2023-03-01,yyyy-mm-dd)rnum-1,D)not in (1,7);COUNT(*)
----------23SQL
2.3.9.5 ROUND(p,s),TRUNC(p,s)在日期中的应用
-- 如何舍入要看具体情况,s是MONTH按30天计,应该是15舍16入,s是YEAR则按6舍7入计算.SQL SELECT empno, hiredate,2 round(hiredate,MONTH) AS round,3 trunc(hiredate,MONTH) AS trunc4 FROM emp WHERE empno7788;EMPNO HIREDATE ROUND TRUNC
---------- --------------- --------------- ---------------7788 24-JAN-87 01-FEB-87 01-JAN-87
SQL
SQL SELECT empno, hiredate,2 round(hiredate,YEAR) AS round,3 trunc(hiredate,YEAR) AS trunc4 FROM emp WHERE empno7369;EMPNO HIREDATE ROUND TRUNC
---------- --------------- --------------- ---------------7369 17-DEC-80 01-JAN-81 01-JAN-80SQL
2.4 几个有用的函数
实现sql语句中的条件判断语句,具有类似高级语言中的if语句的功能. decode函数源自oracle, case表达式源自sql标准, 实现功能类似
2.4.1 decode函数
-- 第一种形式
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下:
IF 条件值1 THENRETURN(翻译值1)
ELSIF 条件值2 THENRETURN(翻译值2)......
ELSIF 条件值n THENRETURN(翻译值n)
ELSERETURN(缺省值)
END IF
-- 【eg.】----
SELECT empno, job, sal,DECODE (job,ANALYST, SAL*1.1,CLERK, SAL*1.15,MANAGER, SAL*1.20, SAL) newsal
FROM emp;
-------------------------------------
-- 第二种形式
decode(字段或字段的运算,值1,值2,值3)
这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3
当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多
2.4.2 case表达式
------- -- 格式1---------
CASE WHEN 条件表达式1 THEN语句段1WHEN 条件表达式2 THEN语句段2......WHEN 条件表达式n THEN 语句段n[ELSE 语句段]
END;
-----------------------------
-- 【eg.】
select ename, job, sal
, case jobwhen ANALYST then SAL*1.1when CLERK then SAL*1.15when MANAGER then SAL*1.20else sal end newsal
from emp;
-------- -- 格式2---------
CASE 条件表达式WHEN 条件表达式结果1 THEN 语句段1WHEN 条件表达式结果2 THEN语句段2......WHEN 条件表达式结果n THEN语句段n[ELSE 条件表达式结果]
END;
-----------------------------
-- 【eg.】
select ename, job, sal,case when jobANALYST then SAL*1.1when jobCLERK then SAL*1.15when jobMANAGER then SAL*1.20else sal end newsal
from emp;
-- case第二种语法比第一种语法增加了搜索功能.形式上第一种when后跟定值,而第二种还可以使用比较符.
-- eg.
select ename,sal,case when sal3000 then Hwhen sal2000 then Melse L end Job_Level
from emp;
----------
select ename,sal,case when sal3000 then Hwhen sal2000 then Melse L end
from emp;
2.4.3 DISTINCT
-- distinct貌似多行函数,严格来说它不是函数.
-- DISTINCT一定要放在开头,否则报错
-- 语法
SELECT DISTINCT column_name,column_name FROM table_name;
---------------------------------------------------------------------------------------
SQL
SQL -- 消除表行重复值
SQL select distinct job from emp;JOB
---------------------------
CLERK
SALESMAN
ANALYST
MANAGERSQL -- 重复值是后面的字段组合起来考虑的
SQL select distinct job,deptno from emp;JOB DEPTNO
--------------------------- ----------
CLERK 20
MANAGER 20
MANAGER 30
MANAGER 10
SALESMAN 30
ANALYST 20
CLERK 30
CLERK 108 rows selected.SQL
2.4.4 CHAR()和ASCII()函数
-- chr()函数将ASCII码转换为字符 :ASCII码 –》 字符
-- ascii()函数将字符转换为ASCII码 :字符 –》 ASCII码
-- 在oracle中chr()函数和ascii()是一对反函数.
SQL select ASCII(K) FROM dual; -- 大写K
ASCII(K)
------------
75
SQL select ASCII(k) FROM dual; -- 小写k
ASCII(K)
------------
107
SQL select chr(75) from dual;
C
-
K
2.4.5 sys_context获取环境上下文的函数很有用
-- scott远程登录(查询IP时)
select sys_context(USERENV,AUTHENTICATION_TYPE) from dual; -- DATABASE 用户的认证类型
select sys_context(USERENV,AUTHENTICATION_DATA) from dual; -- null 未知
select sys_context(USERENV,BG_JOB_ID) from dual; -- null 当前指定id的会话是否为oracle后台程序建立,不是则返回null
select sys_context(USERENV,CLIENT_INFO) from dual; -- null 通过dbms_application_info包可以存储高达64字节的用户会话信息
select sys_context(USERENV,CURRENT_SCHEMA) from dual; -- DICPTEST 默认的schema将被当做当前的schema.-- 当在当前会话中使用ALTER SESSION SET CURRENT_SCHEMA语句的时候,它的查询返回值将被改变
select sys_context(USERENV,CURRENT_SCHEMAID) from dual; -- 当前schema的id
select sys_context(USERENV,CURRENT_USER) from dual; -- DICPTEST 当前的登陆用户
select REPLACE(SUBSTR(sys_context(USERENV,HOST),1,30),/,:) from dual; -- 当前会话主机操作系统名
select sys_context(USERENV,CURRENT_USERID) from dual; -- 当前登陆的用户的id
select sys_context(USERENV,DB_DOMAIN) from dual; -- null 为数据库的域指定初始化参数
select sys_context(USERENV,DB_NAME) from dual; -- iomtest 数据库实例名
select sys_context(USERENV,ENTRYID) from dual; -- null 可用的审计标示符.不能再分布式sql语句中使用此选项-- 使用USERENV关键字必须置AUDIT_TRAIL的初始化参数为真.
select sys_context(USERENV,EXTERNAL_NAME) from dual; -- null 数据库用户的扩展名
select sys_context(USERENV,FG_JOB_ID) from dual; -- 0 返回作业id当此会话是客户端进程创建.否则,返回null
select sys_context(USERENV,INSTANCE) from dual; -- 1 当前数据库实例的标示id
select sys_context(USERENV,ISDBA) from dual; -- FALSE 当前用户是否是以dba身份登录
select sys_context(USERENV,LANG) from dual; -- ZHS iso对LANGUAGE’的简称,查询的参数比”LANGUAGE”短
select sys_context(USERENV,LANGUAGE) from dual; -- SIMPLIFIED CHINESE_CHINA.ZHS16GBK 结果为当前数据库使用的存储语言,跟上面查询意义一样
select sys_context(USERENV,NETWORK_PROTOCOL) from dual; -- tcp 用于通信的网络协议
select sys_context(USERENV,NLS_CALENDAR) from dual; -- GREGORIAN 当前会话使用的,格林尼治时间
select sys_context(USERENV,NLS_CURRENCY) from dual; -- 本地化的货币符,如人民币为,美元符为$
select sys_context(USERENV,NLS_DATE_FORMAT) from dual; -- DD-MON-RR 当前使用的日期格式,一般中国为dd-mon-rr
select sys_context(USERENV,NLS_DATE_LANGUAGE) from dual; -- SIMPLIFIED CHINESE 表示日期的语言,如中文简体SIMPLIFIED CHINESE
select sys_context(USERENV,NLS_TERRITORY) from dual; -- CHINA 数据库服务器所在区域,如中国CHINA
select sys_context(USERENV,OS_USER) from dual; -- 操作系统的用户名
select sys_context(USERENV,PROXY_USER) from dual; -- null 是否使用代理用户.否返回null
select sys_context(USERENV,PROXY_USERID) from dual; -- null 代理用户id
select sys_context(USERENV,SESSION_USER) from dual; -- DICPTEST 当前认证的数据库用户名
select sys_context(USERENV,SESSION_USERID) from dual; -- 当前认证的数据库用户名id
select sys_context(USERENV,SESSIONID) from dual; -- 当前会话id
select sys_context(USERENV,TERMINAL) from dual; -- 操作系统用户组
select sys_context(USERENV,IP_ADDRESS) from dual; -- 当前会话主机ip
select sys_context(USERENV,HOST) from dual; -- 当前会话主机操作系统名-- 在sqldeveloper中 一键查询
select
SYS_CONTEXT(USERENV,TERMINAL) terminal,
SYS_CONTEXT(USERENV,LANGUAGE) language,
SYS_CONTEXT(USERENV,SESSIONID) sessionid,
SYS_CONTEXT(USERENV,INSTANCE) instance,
SYS_CONTEXT(USERENV,ENTRYID) entryid,
SYS_CONTEXT(USERENV,ISDBA) isdba,
SYS_CONTEXT(USERENV,NLS_TERRITORY) nls_territory,
SYS_CONTEXT(USERENV,NLS_CURRENCY) nls_currency,
SYS_CONTEXT(USERENV,NLS_CALENDAR) nls_calendar,
SYS_CONTEXT(USERENV,NLS_DATE_formAT) nls_date_format,
SYS_CONTEXT(USERENV,NLS_DATE_LANGUAGE) nls_date_language,
SYS_CONTEXT(USERENV,NLS_SORT) nls_sort,
SYS_CONTEXT(USERENV,CURRENT_USER) current_user,
SYS_CONTEXT(USERENV,CURRENT_USERID) current_userid,
SYS_CONTEXT(USERENV,SESSION_USER) session_user,
SYS_CONTEXT(USERENV,SESSION_USERID) session_userid,
SYS_CONTEXT(USERENV,PROXY_USER) proxy_user,
SYS_CONTEXT(USERENV,PROXY_USERID) proxy_userid,
SYS_CONTEXT(USERENV,DB_DOMAIN) db_domain,
SYS_CONTEXT(USERENV,DB_NAME) db_name,
SYS_CONTEXT(USERENV,HOST) host,
SYS_CONTEXT(USERENV,OS_USER) os_user,
SYS_CONTEXT(USERENV,EXTERNAL_NAME) external_name,
SYS_CONTEXT(USERENV,IP_ADDRESS) ip_address,
SYS_CONTEXT(USERENV,NETWORK_PROTOCOL) network_protocol,
SYS_CONTEXT(USERENV,BG_JOB_ID) bg_job_id,
SYS_CONTEXT(USERENV,FG_JOB_ID) fg_job_id,
SYS_CONTEXT(USERENV,AUTHENTICATION_TYPE) authentication_type,
SYS_CONTEXT(USERENV,AUTHENTICATION_DATA) authentication_data
from dual;
