首頁 資料庫 mysql教程 Oracle经典sql语句总结

Oracle经典sql语句总结

Jun 07, 2016 pm 03:39 PM
oracle select sql 總結 經典 語句

1. 经典的select sql 语句 // 注意:包含空的数学表达式求出的结果为空 SQL select salcomm from emp; // 连接员工编号与员工姓名这两个字段 SQL select empno||ename as 员工编号和员工姓名 from emp; // 查询去掉重复行的员工部门编号 SQL select distinct

1.经典的select sql语句

//注意:包含空值的数学表达式求出的结果为空值

SQL> select sal+comm from emp;

//连接员工编号与员工姓名这两个字段

SQL> select empno||ename  as "员工编号和员工姓名" from emp;

//查询去掉重复行的员工部门编号

SQL> select distinct(deptno) from emp;

//查询薪水不等于3000的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal3000;

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal !=3000;

//查询薪水大于等于1600并且小于等于3000的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal between 1600 and 3000;

等效于

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal>=1600 and sal

//查询部门号位10,20的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno in(10,20);

相当于:

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno =10 or deptno=20;

//查询员工名称以S开头的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like 'S%';

//查询员工名称以S结尾的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '%S';

//查询员工名称第三个字符为N的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '__N%';

//查询员工名称中含有N的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '%N%';

//查询员工名称中倒数第二个字符为%的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '%\%_'escape'\';

//查询员工名称总含有%的员工信息

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '%\%%'escape'\';

   关系数据库-à层次关系---》网状的关系-à关系数据库-à对象关系。

备注:可以与条件语句结合使用但order by子句放在最后

//查询员工信息并按照员工的部门编号升序并且编号进行降序排列

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp  order by deptno asc,empno desc;

备注:理解思路:首先查询员工信息按照部门的编号进行升序,然后每个部门中的员工按照员工的编号进行降序排列

2. 字符串函数

字符串函数是oracle使用最广泛的一种函数.

LOWER:小写

UPPER:大写

INITCAP:首字母大写

CONCAT:连接

SUBSTR:截取  (参数,开始,数目)

LENGTH:返回字符串的长度

INSTR:(参数,字母) 返回字母出现的位置

LPAD:(参数,长度,在前补齐参数字母)

RPAD:(参数,长度,在后补齐参数字母)

TRIM :截取前后空格

REPLACE:(参数,参数[,参数]):第一个参数操作数,第二是要查找的字符,第三个是替换的字符,如果没有第三个就删除查找的字符。

//查询名称为scott的员工信息(不区分大小写)

//查询员工名称中含有O字符的位置

3.经典字符串函数sql语句

//查询员工名称中含有O字符的位置并且求出了员工名称字符长度

SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp;

//在查询上面结果中过滤出员工工作从第三字母开始为ERK员工

SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp where substr(job,3)='ERK';

//当含有两个参数的时候,从开始位置直接到参数结束的结束为止;在效果同上的同时指明了截取个数为3

 SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp where substr(job,3,3)='ERK';

//查询员工信息  薪资是10位位数不够在左部分补*填充

SQL> select empno,ename,job,mgr,hiredate,LPAD(sal,10,'*'),comm,deptno from emp;

//查询员工信息  薪资是10位位数不够在右部分补*填充

SQL> select empno,ename,job,mgr,hiredate,RPAD(sal,10,'*'),comm,deptno from emp;

//查询员工信息  把员工名称中含有S字符去除掉

SQL> select empno,TRIM('S' from ename),job,mgr,hiredate,comm,deptno from emp;

等效于:

SQL> select empno,TRIM( both 'S' from ename),job,mgr,hiredate,comm,deptno from emp;

//查询员工信息  把员工名称中前面有S字符去除掉

SQL> select empno,TRIM( Leading 'S' from ename),job,mgr,hiredate,comm,deptno from emp;

//等效于

SQL> select empno,LTRIM( ename,'S'),job,mgr,hiredate,comm,deptno from emp;

//查询员工信息  把员工名称中后面有S字符去除掉

SQL> select empno,TRIM( trailing 'S' from ename),job,mgr,hiredate,comm,deptno from emp;

等效于:

SQL> select empno,RTRIM( ename,'S'),job,mgr,hiredate,comm,deptno from emp;

1、字符串连接  || 或  concat(‘’,’’)

SQL> select 'a' || 'b' from dual;    //oracle 特有的符号操作

'A'||'B'

--------

ab

SQL> select concat('a','b') from dual;

CONCAT('A','B')

---------------

ab

SQL> select concat(concat('a','b'),'c') from dual;

CONCAT(CONCAT('A','B'),'C')

---------------------------

Abc

子串函数 substr(‘’,开始位置,字符长度)  instr(‘’,’出现的字符’)

SQL> select substr('abcdef',1) from dual;

SUBSTR('ABCDEF',1)

------------------

abcdef

备注:开始位置从1开始,如果有两个参数(substr(‘’,2))那么代表的意思是从位置2开始到字符串的结尾的字符串

 SQL> select substr('abcdef',1,3) from dual;

 SUBSTR('ABCDEF',1,3)

--------------------

abc

随机字符串的函数

dbms_random.string('a',6) //第一个参数有如下几个具体含义也有解释,第二参数代表产生的字符串的长度。

u 大写字母

l小写字母

a大小写字母

x大写字母和数字

p任意字符

//随机产生一个长度为5并且字符大写的字符串  注意观察下面的结果

DBMS_RANDOM.STRING('U',5)

-----------------------------------------------------------

RAVTG

DBMS_RANDOM.STRING('U',5)

-----------------------------------------------------------

NOAJN

备注重点理解u,l,a,x,p的含义。

SQL> select dbms_random.string('l',5) from dual; //随机产生一个长度为5并且字符小写的字符串

DBMS_RANDOM.STRING('L',5)

-----------------------------------------------------------

tawgc

SQL> select dbms_random.string('a',5) from dual; //随机产生一个长度为5并且字符大小写混合的字符串

DBMS_RANDOM.STRING('A',5)

-----------------------------------------------------------TEczi

SQL> select dbms_random.string('x',5) from dual; //随机产生一个长度为5并且字符与数字组合的字符串

 

DBMS_RANDOM.STRING('X',5)

-----------------------------------------------------------4TDUU

 

SQL> select dbms_random.string('x',5) from dual;

DBMS_RANDOM.STRING('X',5)

-----------------------------------------------------------RONOZ

select dbms_random.string('p',5) from dual; //随机产生一个长度为5任意的字符串

DBMS_RANDOM.STRING('P',5)

-----------------------------------------------------------5=3)*

 

SQL> select dbms_random.string('p',5) from dual;

 

DBMS_RANDOM.STRING('P',5)

-----------------------------------------------------------

zf,@z

 

4.数字函数:

ROUND: 四舍五入

TRUNC:  截断

MOD: 求余

ABS:绝对值

CEIL:返回大于或等于value的最小整数

FLOOR:返回小于或等于value的最大整数

SQRT :返回value的平方根 负数无意义。

//四舍五入 结果为46

5.经典数字函数sql语句

四舍五入(更详细参考本人oracle博客oracle心得2)

//结果为1800.11

SQL> select round(1800.11111,2) from dual;

//结果为1800

SQL> select round (1800.11111,-2) from dual;

//结果为1900

SQL> select round (1899.11111,-2) from dual;

//结果为1800.67

SQL> select round (1800.6666,2) from dual;

//绝对值结果为45.56

SQL> select abs(-45.56) from dual;

//大于等于最小整数结果为46

SQL> select ceil(45.56) from dual;

//大于等于最小整数结果为-45

SQL> select ceil(-45.56) from dual;

//小于等于最大整数结果为-46

SQL> select floor(-45.56) from dual;

//求余数结果为300

SQL> select mod(1800,500) from dual

//截取的数的操作数是正数的情况下:只操作小数位结果为1800.11

SQL> select trunc(1800.11111,2) from dual;

//截取的数的操作数是负数的情况下:操作的是小数点之前的位,把操作位小数点之前的位数全部改写成0. 结果就是1000

SQL> select trunc(1899.11111,-3) from dual;

6.日期时间函数

Oracle 中的日期型数据实际含有两个值: 日期和时间默认的日期格式是 DD-MON-RR.日期时间函数用来返回当前系统的日期和时间、以及对日期和时间类型的数据进行处理运算。

常用函数有:

add_months(date,count);在指定的日期上增加count个月

last_day(date);返回日期date所在月的最后一天

months_between(date1,dates);返回date1到date2之间间隔多少个月

new_time(date,‘this’,’other’);将时间date从this时区转换成other时区

next_day(day,’day’);返回指定日期或最后一的第一个星期几的日期,这里day为星期几

sysdate();获取系统的当前日期

current_timestamp();获取当前的时间和日期值

round:日期的四舍五入

trunc 日期的截取

日期的数学运算:

在日期上加上或减去一个数字结果仍为日期

两个日期相减返回日期之间相差的天数。

可以用数字除24来向日期中加上或减去小时。

//获取系统的当前时间 显示的格式采用默认格式 显示结果:07-4月 -11 11.15.38.390000 上午 +08:00

7.经典日期sql语句

//为当前日期加上3个月 显示的结果:2011-7-7 11:18:36

select add_months(sysdate,3) from dual;

//返回当前月的最后一天显示的结果:2011-4-30 11:19:4

select last_day(sysdate) from dual;

//返回两个日期之间的间隔月是几:结果为:4(sysdate,4)这里数字是多少结果就是多少

SQL> select months_between(add_months(sysdate,4),sysdate) from dual;

//从GMT时区转换成AST时区的日期结果

 SQL> select new_time(sysdate,'GMT','AST') from dual;

//返回下一个星期一的日期值   必须写成星期’几’

 SQL> select next_day(sysdate,'星期一') from dual;

8.转换函数

  隐式转换:在运算过程中由系统自动完成的

  显式转换:在运算过程中需要调用相应的转换函数实现。

显式转换:

to_char(date,’format’):按照指定的格式format把数字或日期类型的数据转换成字符串

9.经典转换函数sql语句

//把当前日期转换成YYYY/MM/DD的格式

SQL> select to_char(current_timestamp,'YYYY/MM/DD') from dual;

//把当前日期转换成YYYY/MM/DD HH24/MI/SS AM的格式

SQL> select to_char(current_timestamp,'YYYY/MM/DD HH24/MI/SS AM') from dual;

//DD “of” MONTH

SQL> select to_char(current_timestamp,'YYYY DD "of" MONTH  HH/MI/SS AM') from dual;

//把当数字按照$99,999这种方式返回字符串  并且操作数的位数不能够大于5(即$后边的位数)位,否则话结果会是########

SQL> select to_char(11111,'$99,999') from dual;

to_number(char);把包含了数字格式的字符串转换成数字数据

to_date(string,’format’);按照指定格式的format把字符串转换成日期数据,如果省略了foramt格式,那么就采用默认的日期格式(DD-MON-YY);

//求出两个日期之间相差的天数

SQL> select to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD') from dual;

//求出两个日期之间相差的周次

SQL> select (to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7 as "date" from dual;

//对周次进行向上取整

SQL> select ceil((to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7) from dual;

 

chartorowid(char);把字符串转换成rowid类型

rowidtochar(x);把rowid类型转换成字符类型数据

10.通用函数

 这些函数适用于任何数据类型,同时也适用于空值:

NVL (expr1, expr2) :  将expr1的空值替换为expr2

NVL2 (expr1, expr2, expr3) :

判断expr1是否为空,如果为空返回expr3,如果不为空返回expr2;相当于:expr1!=null?exrp2:expr3;

NULLIF (expr1, expr2)  :

expr1与expr2相等返回NULL,不等返回expr1

COALESCE (expr1, expr2, ..., exprn) :

COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。 如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE 。

11.经典通用函数sql语句

//日期为空替换成给定的日期

SQL>select empno,ename,job,mgr,nvl(hiredate,to_date('2011-1-10','YYYY-MM-DD')),sal,nvl(comm,0),deptno from  emp;

//计算出员工在该月的工资=(薪资+奖金);使用nvl函数

SQL> select empno,ename,job,mgr,hiredate,(nvl(sal,0)+nvl(comm,0)) as "工资",deptno from  emp;

//计算员工的工资;使用nvl2函数

SQL> select empno,ename,job,mgr,hiredate,nvl2(comm,sal+comm,sal) as "工资",deptno from  emp;

//注意观察理解;ename的长度跟job的长度是否相等

SQL> select ename as "expr1",job as "expr2",nullif(length(ename),length(job)) from  emp;

//comm与sal交叉执行

SQL> select empno,ename,job,mgr,hiredate,coalesce(comm,sal,10000) as "salll", deptno from  emp;

重点理解:单行函数可以嵌套。嵌套函数的执行顺序是由内到外。

12.条件表达式 :IF-THEN-ELSE 逻辑

  Case函数的用法

CASE expr WHEN comparison_expr1 THEN return_expr1

         [WHEN comparison_expr2 THEN return_expr2

          WHEN comparison_exprn THEN return_exprn

          ELSE else_expr]

END

Deecode函数的用法

DECODE(col|expression, search1, result1

                 [, search2, result2,...,]

                 [, default])

//为职位是Manager的员工 发放5000元的奖金

SQL> select ename,job,case job when 'MANAGER' then nvl(sal,0)+5000  end  as "工资" from emp;

//员工的工资

SQL> select ename ,job,

  2  case job when 'MANAGER' then nvl(sal,0)+5000+nvl(comm,0)

  3  else nvl(sal,0)+nvl(comm,0)

  4  end

  5  from emp;

//改写成 decode的写法

SQL> select ename,job

  2  ,decode(job,'MANAGER', nvl(sal,0)+5000+nvl(comm,0),

  3  'CLERK',nvl(sal,0)+nvl(comm,0)+200,

  4  nvl(sal,0)+nvl(comm,0)) as "工资"

  5  from emp;

13、中文排序

order by nlssort(列,'NLS_SORT=SCHINESE_PINYIN_M');//拼音

order by nlssort(列,'NLS_SORT=SCHINESE_STROKE_M');//笔画

order by nlssort(列,'NLS_SORT=SCHINESE_RADICAL_M');//偏旁

举例如下:

SQL> select * from student; //查询所有数据

 

  SID NAME             DEPT    AGE

----- -------------------- ---------- ---

    1 张三               农大      21

    2 李四               农大      22

    3 王五               工大      21

    4 赵六               工大      20

SQL> select * from student order by name;  //注意观察

 

  SID NAME             DEPT    AGE

----- -------------------- ---------- ---

    1 张三               农大      21

    2 李四               农大      22

    3 王五               工大      21

    4 赵六               工大      20

SQL> select * from student order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');//按名笔画排序

  SID NAME             DEPT    AGE

----- -------------------- ---------- ---

    2 李四               农大      22

    3 王五               工大      21

    1 张三               农大      21

    4 赵六               工大      20

SQL> select * from student order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');//注意理解

  SID NAME             DEPT    AGE

----- -------------------- ---------- ---

    3 王五               工大      21

    1 张三               农大      21

    2 李四               农大      22

    4 赵六               工大      20

SQL> select * from student order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');//注意理解

  SID NAME             DEPT    AGE

----- -------------------- ---------- ---

    1 张三               农大      21

    2 李四               农大      22

    4 赵六               工大      20

    3 王五               工大      21

备注:null值参与排序 null值最大

 

 

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

<🎜>:泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
北端:融合系統,解釋
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Mandragora:巫婆樹的耳語 - 如何解鎖抓鉤
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

Java教學
1665
14
CakePHP 教程
1424
52
Laravel 教程
1322
25
PHP教程
1270
29
C# 教程
1249
24
oracle游標關閉怎麼解決 oracle游標關閉怎麼解決 Apr 11, 2025 pm 10:18 PM

解決 Oracle 游標關閉問題的方法包括:使用 CLOSE 語句顯式關閉游標。在 FOR UPDATE 子句中聲明游標,使其在作用域結束後自動關閉。在 USING 子句中聲明游標,使其在關聯的 PL/SQL 變量關閉時自動關閉。使用異常處理確保在任何異常情況下關閉游標。使用連接池自動關閉游標。禁用自動提交,延遲游標關閉。

oracle怎麼循環創建游標 oracle怎麼循環創建游標 Apr 12, 2025 am 06:18 AM

Oracle 中,FOR LOOP 循環可動態創建游標, 步驟為:1. 定義游標類型;2. 創建循環;3. 動態創建游標;4. 執行游標;5. 關閉游標。示例:可循環創建游標,顯示前 10 名員工姓名和工資。

oracle視圖怎麼導出 oracle視圖怎麼導出 Apr 12, 2025 am 06:15 AM

可以通過 EXP 實用程序導出 Oracle 視圖:登錄 Oracle 數據庫。啟動 EXP 實用程序,指定視圖名稱和導出目錄。輸入導出參數,包括目標模式、文件格式和表空間。開始導出。使用 impdp 實用程序驗證導出。

oracle日誌寫滿怎麼辦 oracle日誌寫滿怎麼辦 Apr 12, 2025 am 06:09 AM

Oracle 日誌文件寫滿時,可採用以下解決方案:1)清理舊日誌文件;2)增加日誌文件大小;3)增加日誌文件組;4)設置自動日誌管理;5)重新初始化數據庫。在實施任何解決方案前,建議備份數據庫以防數據丟失。

HDFS配置CentOS需要哪些步驟 HDFS配置CentOS需要哪些步驟 Apr 14, 2025 pm 06:42 PM

在CentOS系統上搭建Hadoop分佈式文件系統(HDFS)需要多個步驟,本文提供一個簡要的配置指南。一、前期準備安裝JDK:在所有節點上安裝JavaDevelopmentKit(JDK),版本需與Hadoop兼容。可從Oracle官網下載安裝包。環境變量配置:編輯/etc/profile文件,設置Java和Hadoop的環境變量,使系統能夠找到JDK和Hadoop的安裝路徑。二、安全配置:SSH免密登錄生成SSH密鑰:在每個節點上使用ssh-keygen命令

甲骨文在商業世界中的作用 甲骨文在商業世界中的作用 Apr 23, 2025 am 12:01 AM

Oracle不僅是數據庫公司,還是雲計算和ERP系統的領導者。 1.Oracle提供從數據庫到雲服務和ERP系統的全面解決方案。 2.OracleCloud挑戰AWS和Azure,提供IaaS、PaaS和SaaS服務。 3.Oracle的ERP系統如E-BusinessSuite和FusionApplications幫助企業優化運營。

oracle數據庫怎麼停止 oracle數據庫怎麼停止 Apr 12, 2025 am 06:12 AM

要停止 Oracle 數據庫,請執行以下步驟:1. 連接到數據庫;2. 優雅關機數據庫(shutdown immediate);3. 完全關機數據庫(shutdown abort)。

centos上weblogic的數據庫連接如何配置 centos上weblogic的數據庫連接如何配置 Apr 14, 2025 pm 02:06 PM

在CentOS系統上配置WebLogic數據庫連接,需要完成以下步驟:JDK安裝與環境配置:確保服務器已安裝與WebLogic版本兼容的JDK(例如,WebLogic14.1.1通常需要JDK8)。正確設置JAVA_HOME、CLASSPATH和PATH環境變量。 WebLogic安裝與解壓:從Oracle官方網站下載適用於CentOS系統的WebLogic安裝包,並將其解壓到指定目錄。 WebLogic用戶與目錄創建:創建一個專用的WebLogic用戶賬戶,並設置安全密碼

See all articles