oracle基本语句格式.docx

oracle基本语句格式.docx

《oracle基本语句格式.docx》由会员分享，可在线阅读，更多相关《oracle基本语句格式.docx（20页珍藏版）》请在冰豆网上搜索。

oracle基本语句格式

数据定义语言

❑数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象

❑用于操纵表结构的数据定义语言命令有：

❑CREATETABLE

❑ALTERTABLE

❑TRUNCATETABLE

❑DROPTABLE

数据操纵语言

❑数据操纵语言用于检索、插入和修改数据

❑数据操纵语言是最常见的SQL命令

❑数据操纵语言命令包括：

❑SELECT

❑INSERT

❑UPDATE

❑DELETE

DML–SELECT命令

❑利用现有的表创建表

❑语法：

CREATETABLEAS

SELECTcolumn_namesFROM;

SQL>CREATETABLEnewstudent

ASSELECT*FROMstudent;

SQL>CREATETABLEnewstudent1

ASSELECTsno,sname

FROMstudent;

SQL>CREATETABLEnewstudent2

ASSELECT*FROMstudent

WHERE1=2;

❑选择无重复的行

❑在SELECT子句，使用DISTINCT关键字

SQL>SELECTDISTINCTsnameFROMstudent;

❑使用列别名

❑为列表达式提供不同的名称

❑该别名指定了列标题

SQL>SELECTsno学号,

2010-extract（yearfrombirthday）“年龄”----中间有空格，用“”

FROMstudent;

DML–INSERT命令

❑插入日期类型的值

❑日期数据类型的默认格式为“DD-MON-RR”

❑使用日期的默认格式

❑使用TO_DATE函数转换

❑插入来自其它表中的记录

❑语法：

INSERTINTO[（cloumn_list）]

SELECTcolumn_namesFROM;

SQL>INSERTINTOstudent2

SELECT*FROMstudent;

数据控制语言

❑数据控制语言为用户提供权限控制命令

❑用于权限控制的命令有：

❑GRANT授予权限

❑REVOKE撤销已授予的权限

算术操作符

❑算术操作符用于执行数值计算

❑可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成

❑算术操作符包括加（+）、减（-）、乘（*）、除（/）

❑比较操作符用于比较两个表达式的值

❑比较操作符包括=、!

=、<、>、<=、>=、BETWEEN…AND、IN、LIKE和ISNULL等，LIKE可以使用匹配符_、%

逻辑操作符

❑逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。

❑逻辑操作符包括与（AND）、或（OR）和非（NOT）

显示2005-5月-10至2005-5月-26的订单信息

SQL>SELECT*FROMorder_master

WHEREodate>‘10-5月-05'

ANDdel_date<‘26-5月-05’;

❑连接操作符用于将多个字符串或数据值合并成一个字符串

通过使用连接操作符可以将表中

的多个列合并成逻辑上的一行列

SQL>SELECT（venname||'的地址是'

||venadd1||''||venadd2||''||venadd3）address

FROMvendor_masterWHEREvencode='V001

SQL操作符的优先级从高到低的顺序是：

❑算术操作符--------最高优先级

❑连接操作符

❑比较操作符

❑NOT逻辑操作符

❑AND逻辑操作符

❑OR逻辑操作符--------最低优先级

❑单行函数对于从表中查询的每一行只返回一个值

❑可以出现在SELECT子句中和WHERE子句中

❑单行函数可以大致划分为：

❑字符函数

❑日期时间函数

❑数字函数

❑转换函数

❑混合函数

❑以下是一些其它的字符函数：

❑CHR和ASCII

❑LPAD和RPAD

❑TRIM

❑LENGTH

❑DECODE（在2.2视频中讲解）

SQL>SELECTLENGTH（'frances'）FROMdual;

SQL>SELECTTRIM（9from9999876789999）FROMdual;

日期时间函数

❑日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果

❑日期函数包括：

❑ADD_MONTHS

❑MONTHS_BETWEEN

❑LAST_DAY

❑ROUND

❑NEXT_DAY

❑TRUNC

❑EXTRACT

转换函数

❑转换函数将值从一种数据类型转换为另一种数据类型

❑常用的转换函数有：

❑TO_CHAR

❑TO_DATE

❑TO_NUMBER

SELECTTO_DATE（‘2005-12-06’,‘yyyy-mm-dd’）

FROMdual;

SELECTTO_CHAR（sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:

MI:

SS'）

FROMdual;　　　　

SELECTTO_NUMBER（'100'）FROMdual;

混合函数

❑以下是几个用来转换空值的函数：

❑NVL，第一为空返回二；否则返回一。

❑NVL2，第一个不空则返回二；否则返回三。

❑NULLIF，两个表达式，相等则返回空；否则第一个。

分组函数

❑分组函数基于一组行来返回结果

❑为每一组行返回一个值

SELECTCOUNT（DISTINCTqty_hand）FROMitemfile;

SELECTCOUNT（*）FROMitemfile

SELECTAVG（re_level）FROMitemfile

WHEREp_category='accessories';

SELECTCOUNT（itemrate）FROMitemfile;

SELECTMAX（max_level）FROMitemfile;

SELECTSUM（itemrate*max_level）FROMitemfile;

❑GROUPBY子句

❑用于将信息划分为更小的组

❑每一组行返回针对该组的单个结果

❑HAVING子句

❑用于指定GROUPBY子句检索行的条件

思考：

查出平均成绩大于所有学生的平均成绩的学生的学号和平均成绩

查出平均成绩大于60的学生的学号和平均成绩，并按照学号的降序排列

SQL>selectsno,max（score）as最高分,sum（score）as总分from成绩groupbysno;

Oracle的多表查询

❑等值连接

❑外连接

❑自连接

❑子查询

相等连接（第一种写法）：

selecttable1.column,table2.column

fromtable1,table2

wheretable1.column1=table2.column2

可以使用表的别名，为了书写的简化。

相等连接（第二种写法）：

selecttable1.column,table2.column

fromtable1innerjointable2

ontable1.column1=table2.column2

可以使用表的别名，为了书写的简化。

左外连接（第一种写法）：

selecttable1.column,table2.column

fromtable1leftouterjointable2

ontable1.column1=table2.column2

可以使用表的别名，为了书写的简化。

左外连接（第二种写法）：

selecttable1.column,table2.column

fromtable1,table2

wheretable1.column1=table2.column2（+）

可以使用表的别名，为了书写的简化。

集合操作符

❑集合操作符将两个查询的结果组合成一个结果

MINUS操作符返回从第一个查询结果中排除第二个查

询中出现的行。

SQL>SELECTordernoFROMorder_master

MINUS

SELECTordernoFROMorder_detail;

INTERSECT操作符只返回两个查询的公共行。

SQL>SELECTordernoFROMorder_master

INTERSECT

SELECTordernoFROMorder_detail

重命名表：

renametable_name1totable_name2;

重命名列：

altertabletable_namerenamecolumncol_oldnametocolnewname;

常见的select、from、where的顺序：

1,from2,where3,select

完整的select、from、where、groupby、having、orderby的顺序：

1,from2,where3,groupby4,having5,select6,orderby

EXISTS用来判断查询所得的结果中，是否有

满足条件的纪录存在。

例:

select*　fromstudent

whereexists（select*fromaddress

wherezz='郑州'）;

从select、from、where三者的先后执行顺

序来分析。

子查询的使用

createtablestudent（snonumber（6）,birthdaydate,snamevarchar2（10））;

insertintostudentvalues（1,'11-1月-81','张三'）;

insertintostudentvalues（2,'10-3月-82','李四'）;

insertintostudentvalues（3,'06-1月-83','王五'）;

insertintostudentvalues（4,'26-1月-83','赵六'）;

createtableaddress（snonumber（6）,zzvarchar2（10））;

insertintoaddressvalues（1,'郑州'）;

insertintoaddressvalues（2,'开封'）;

insertintoaddressvalues（3,'洛阳'）;

insertintoaddressvalues（4,'郑州'）;

要求：

找出zz是郑州的学生中，sno最大的学生的sname

selectsname　　　　　

　fromstudent　　　

　wheresno=（　　　　　　　

　selectmax（sno）　　　　　　　　　　　　　

　fromaddress　　　　　　　　　　　　　　　　

wherezz='郑州'）;　　

1.创建student表

createtablestudent（xhnumber,xmvarchar2（10）,nlint）;

insertintostudentvalues（1,'A',21）;

insertintostudentvalues（2,'B',22）;

insertintostudentvalues（3,'A',23）;

insertintostudentvalues（4,'A',24）;

insertintostudentvalues（5,'A',25）;

insertintostudentvalues（6,'C',26）;

insertintostudentvalues（7,'B',27）;

任务：

查找xm有相同的纪录，并显示出来，如下：

XHXMNL

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

1A21

3A23

4A24

5A25

2B22

7B27

答案：

select*fromstudentwherexmin（selectxmfromstudentgroupbyxmhavingcount（*）>1）;

CREATETABLE管理人员（

编号char（10）,

姓名varchar2（10）,

管理人员编号char（10）

）;

insertinto管理人员values（'001','张一','004'）;

insertinto管理人员values（'002','张二','004'）;

insertinto管理人员values（'003','张三','003'）;

insertinto管理人员values（'004','张四','004'）;

要求：

现在想显示：

编号，姓名，管理人员姓名

selecta.编号,a.姓名,b.姓名as管理人员姓名

from管理人员ajoin管理人员bona.管理人员编号=b.编号;

SELECTCASEWHEN的的使用

语法：

CASE

  WHEN条件1THENaction1

WHEN条件2THENaction2

  WHEN条件3THENaction3

  …..

  ELSEactionN

ENDCASE

selectcase

whensubstr（'20090310',5,2）='01'then'一月份'

whensubstr（'20090310',5,2）='02'then'二月份'

whensubstr（'20090310',5,2）='03'then'三月份'

whensubstr（'20090310',5,2）='04'then'四月份'

elsenull

end

fromdual;

语法：

CASEselector

  WHENvalue1THENaction1

  WHENvalue2THENaction2

  WHENvalue3THENaction3

  …..

  ELSEactionN

END[CASE]

selectcasesubstr（'20090310',5,2）

when'01'then'一月份'

when'02'then'二月份'

when'03'then'三月份'

when'04'then'四月份'

elsenull

end

fromdual;

createtable成绩（snonumber,kmvarchar2（10）,scorenumber,gradechar（6））;

insertinto成绩values（1,'语文',65,null）;

insertinto成绩values（2,'数学',76,null）;

insertinto成绩values（3,'英语',86,null）;

insertinto成绩values（4,'语文',94,null）;

要求：

把每个学生的grade列，用相应的等级来更新。

update成绩setgrade=（

selectgradefrom（

selectsno,

casewhenscore>=90then'优秀'

whenscore>=80then'良好'

whenscore>=70then'中等'

whenscore>=60then'及格'

else'不及格'

endgrade

from成绩

）a

where成绩.sno=a.sno）;

表T1里有a,b,c...N个字段，表T2里有a,b,c三个字段，

然后想在T1中"c"与表T2中"c"相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做？

createtableT1（aint,bint,cint,dint,eint）;

createtableT2（aint,bint,cint）;

insertintoT1values（1,2,3,4,5）;

insertintoT1values（10,20,3,4,5）;

insertintoT1values（10,20,4,40,50）;

insertintoT2values（-1,-1,3）;

insertintoT2values（-2,-2,4）;

updatet1seta=（selectafromt2wheret1.c=t2.c）,b=（selectbfromt2wheret1.c=t2.c）wheret1.cin（selectcfromt2）;

分析函数

分析函数用于计算完成聚集的累计排名、序号等

分析函数为每组记录返回多个行

以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始

ROW_NUMBER返回连续的排序，不论值是否相等

RANK具有相等值的行排序相同，序数随后跳跃

DENSE_RANK具有相等值的行排序相同，序号是连续的

DECODE中的if-then-else逻辑

❑在逻辑编程中，经常用到If–Then–Else进行逻辑判断。

在DECODE的语法中，实际上就是这样的逻辑处理过程。

它的语法如下：

❑DECODE（value,if1,then1,if2,then2,if3,then3,...else）

❑Value代表某个表的任何类型的任意列或一个通过计算所得的任何结果。

当每个value值被测试，如果value的值为if1，Decode函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。

事实上，可以给出多个if/then配对。

如果value结果不等于给出的任何配对时，Decode结果就返回else。

❑需要注意的是，这里的if、then及else都可以是函数或计算表达式。

Createtablestudent（idnumber,namevarchar2（10）,sexchar

（1））;

Insertintostudentvalues（1,'张','1'）;

Insertintostudentvalues（2,'王','2'）;

Insertintostudentvalues（3,'李','1'）;

Selectname,decode（sex,'1','男生','2','女生'）

fromstudent;

用case实现：

selectid,name,

casesex

when'1'then'男'

when'2'then'女'

end性别

fromstudent;

DECODE取出一行内两列中的较大值

Createtablesales（monthchar

（2）,sales_tvnumber,sales_computernumber）;

Insertintosalesvalues（'01',10,18）;

Insertintosalesvalues（'02',28,20）;

Insertintosalesvalues（'03',36,33）;

selectmonth,decode（sign（sales_tv-sales_computer）,1,sales_tv,sales_computer）as较大销售量fromsales;

Oracle中的行列转换

createtable销售（商品名称varchar2（10）,季度char

（2）,销售额number）;

insertinto销售values（'电视机','01',100）;

insertinto销售values（'电视机','02',200）;

insertinto销售values（'电视机','03',300）;

insertinto销售values（'空调','01',50）;

insertinto销售values（'空调','02',150）;

insertinto销售values（'空调','03',180）;

格式1：

商品名称季度销售额

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

电视机01100

电视机02200

电视机03300

空调0150

空调02150

空调03180

格式2：

商品名称一季度二季度三季度四季度

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

电视机1002003000

空调501501800

从格式1到格式2：

selecta.商品名称,

sum（decode（a.季度,'01',a.销售额,0））一季度,

sum（decode（a.季度,'02',a.销售额,0））二季度,

sum（decode（a.季度,'03',a.销售额,0））三季度,

sum（decode（a.季度,'04',a.销售额,0））四季度

from销售a

groupbya.商品名称

orderby1;

ROWNUM的使用

创建yggz表

createtableyggz（

bhnumber（6）,

gznumber

）;

insertintoyggzvalues（1,1000）;

insertintoyggzvalues（2,1100）;

insertintoyggzvalues（3,