第五章查询.docx
- 文档编号:29416475
- 上传时间:2023-07-23
- 格式:DOCX
- 页数:20
- 大小:46.55KB
第五章查询.docx
《第五章查询.docx》由会员分享,可在线阅读,更多相关《第五章查询.docx(20页珍藏版)》请在冰豆网上搜索。
第五章查询
1.Select语句
Select命令用于从Oracle数据库中检索数据。
读者利用select命令告诉数据库要检索什么样的信息。
Select是读者看到的最常用的SQL语句,select命令(如下)有六个基本部分构成:
SELECT[DISTINCT]{*,COLUMN[ALIAS],……}
FROMtable
WHEREcondition(s)
ORDERBY{column,exper}[ASC|DESC]
GROUPBY{column,exper}
HAVINGhaving_condtions;
i.Select后跟用户需要检索的信息(如下一部分将要提到的表中表列的名字)。
这是select命令必不可少的部分。
ii.From后跟检索对象(如存放数据的一个或多个表的名称),from部分也是必不可少的。
iii.Where后跟检索条件(如限制检索内容的条件),where部分是可选的。
iv.Orderby后跟分类准则(如取自空值数据如何给出的第一部分的表列名称表),orderby部分是可选的。
v.Groupby后跟分组的字段或准则。
vi.Having后跟分组的查询条件。
下面我们首先使用select语句操作名为user_tables的数据字典视图:
SQL>select*
fromuser_tables
wheretable_name=‘customer’;
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAMEPCT_FREEPCT_USED
INI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENT
MIN_EXTENTSMAX_EXTENTSPCT_INCREASEFREELISTS
FREELIST_GROUPSLOGBNUM_ROWSBLOCKSEMPTY_BLOCKS
AVG_SPACECHAIN_CNTAVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKS
DEGREEINSTANCESCACHETABLE_LOSAMPLE_SIZE
LAST_ANALPARIOT_TYPETSNESBUFFER_ROW_MOVEGLO
USEDURATIONSKIPCORMON
CUSTOMER
SYSTEM
1040
12551024010240
1121501
1YESN
11NENABLED
NONNNODEFAULTDISABLEDNO
NODISABLEDNO
2.选择指定的列
我们可以在select关键字后跟一个或多个表列。
星号指示Oracle显示表中的所有字段。
我们使用同样的select语句,但指定了需要查看的一个表的某些字段:
SQL>selecttable_namefromuser_tables
TABLE_NAME
CUSTOMER
STATE
2rowsselected
3.条件查询
到目前为止,我们看到了select命令可用来查看表中所有表列(select*)或部分表列(selectcolumn1,column3)。
如果读者只想看特定的数据行,怎么办呢?
这就需要用wherer子句来解决了。
例如:
我们想要查看state_cd值为MA的所有客户,可以用命令:
selectlast_name,state_cd,salesfromcustomerwherestate_cd=‘MA’;
结果如下:
LAST_NAMESTSALES
TeplowMA23445.67
1)带and/or的Where子句
where子句指示Oracle查找表中数据,并只返回满足条件的行。
在上面的例子中,要求Oracle仅返回state_cd等于MA的数据行。
这是通过wherestate_cd=‘MA’;来实现的。
有时用户要求返回同时满足多个条件的行。
例如:
读者可能对state_cd为CA且sales超过6000的行感兴趣。
语句select*fromcustomerwherestate_cd=‘CA’andsales>6000;得到下列输出:
LAST_NAMESTSALES
AbbeyCA6969.96
NicholsonCA6989.99
上面的例子中,我们需要返回满足所有条件的行。
如果用户要求检索满足其中两个条件之一的行应该怎么办呢?
可用语句select*fromcustomerwherestate_cd=‘CA’orsales>6000;。
结果如下:
LAST_NAMESTSALES
TeplowMA23445.67
AbbeyCA6969.96
NicholsonCA6989.99
MartinCA2345.45
LaursenCA34.34
BambiCA1234.55
注意,尽管Telpow的state_cd不等于CA,但由于其sales值超过6000,因此也显示在输出列表中。
And和or如读者所知是逻辑操作符,决定查询语句中where条件之间的关系。
逻辑条件的概念及其在Oracle产品中的用途及使用方法要用一整本书才可讲清楚。
此处精力主要放在实用技术上。
表4-1解释了当and和or出现在同一个where子句中时,Oracle是怎样处理的。
读者应细心分析涉及多个and和or的逻辑(称为复合条件,compoundconditions),否则将会引起混乱。
例如:
检查语句selectlast_namefromcustomerwherestate_cd=’MA’andstate_cd=‘CA’;。
其中两个条件由关键字and连接,只有当两个条件为真(true)时,该复合条件才能为真。
表4-1逻辑操作符or和and
操作符
作用
Or
当所连接的两个条件之一为真时返回TRUE
And
当所连接的两个条件都为真时返回TRUE
语句selectlast_namefromcustomerwherestate_cd=’MA’andstate_cd=‘CA’;中,对state_cd值为MA的数据行,第一个条件为TRUE,而第二个条件为FALSE(因为CA不等于MA)。
该逻辑条件说明要显示state_cd既为MA又为CA的数据行,这种情况是不可能出现的,因此,该复合条件永远为假,结果是什么也显示不出来。
2)带NOT的where子句
Oracle支持否定条件的搜索。
例如:
读者可能想看看state_cd不为MA的所有客户,语句select*fromcustomerwherestate_cd!
=’MA’;(在SQL*Plus中符号!
=的意思是“不等于”),输出结果为:
LAST_NAMESTSALES
AbbeyCA6969.96
NicholsonCA6989.99
MartinCA2345.45
LaursenCA34.34
BambiCA1234.55
McGrawNJ123.45
3)带检索范围的where子句
Oracle也支持限定范围的检索。
例如:
读者可能需要查找sales值从1到10000之间的所有客户,可执行语句select*fromcustomerwheresalesbetween1and10000;。
执行结果如下:
LAST_NAMESTSALES
AbbeyCA6969.96
NicholsonCA6989.99
MartinCA2345.45
LaursenCA34.34
BambiCA1234.55
McGrawNJ123.45
4)带检索表的where子句
Oracle支持在列表内查找项的概念。
例如:
可用语句select*fromcustomerwherestate_cdin(‘NJ’,’CA’);检索state_cd为NJ或CA的所有客户,检索结果为:
LAST_NAMESTSALES
AbbeyCA6969.96
NicholsonCA6989.99
MartinCA2345.45
LaursenCA34.34
BambiCA1234.55
McGrawNJ123.45
5)带匹配检索的where子句
Oracle支持采用like命令的匹配检索。
例如,用户告诉Oracle检索所有以M开头的last_name,显示相应的数据行。
可用语句select*fromcustomerwherelast_namelike‘M%’;输出情况如下:
LAST_NAMESTSALES
MartinCA2345.45
McGrawNJ123.45
也可以输入语句select*fromcustomerwherelast_namelike‘%tin%’;来查找last_name中含有“tin”的行。
执行结果如下:
LAST_NAMESTSALES
MartinCA2345.45
6)where子句中的常用操作符
除了上面讲到的这许多例子外,Oracle还提供了许多功能强大的逻辑操作符限制行的检索。
表4-2是可在where子句中使用的部分操作符列表。
表4-2常用比较操作符
操作符
作用
样例
=
相等
Select*fromstatewherestate_cd=’MA’;
!
=
不相等
Select*fromstatewherestate_cd!
=’MA’;
^=
同!
=
Select*fromstatewherestate_cd^=’MA’;
<>
同!
=
Select*fromstatewherestate_cd<>’MA’;
<
小于
Select*fromcustomerwheresales<100;
>
大于
Select*fromcustomerwheresales>100;
<=
小于或等于
Select*fromcustomerwheresales<=1000;
>=
大于或等于
Select*fromcustomerwheresales>=1000;
In
等于括号内任一成员
Select*fromcustomerwherestate_cdin(‘MA’,’NJ’);
notin
不等于括号内任一成员
Select*fromcustomerwherestate_cdnotin(‘MA’,’NJ’);
betweenAandB
大于等于A与小于等于B
Select*fromcustomerwheresalesbetween1and50;
notbetweenAandB
不大于等于A与小于等于B
Select*fromcustomerwheresalesnotbetween1and50;
like‘%tin%’
包括给定子串(即’tin’)
Select*fromcustomerwherelast_namelike‘%tin%’
4.Orderby
我们再来看看customer表。
这次,我们希望查询结果以last_name的字母降序排列,命令Select*fromcustomerorderbylast_namedesc;结果如下:
LAST_NAMESTSALES
TeplowMA23445.67
NicholsonCA6989.99
McGrawNJ123.45
MartinCA2345.45
LaursenCA34.34
BambiCA1234.55
AbbeyCA6969.96
6rowsselected.
正如我们所见,查询结果按降序排列。
若发布命令Select*fromcustomerorderbylast_name;,查询结果将按升序排列。
若发布命令Select*fromcustomerorderbystate_cddesc,last_name;,则进行多级排序。
该命令按state_cd的降序(Vermont在Mississipi之前)和last_name的升序排列客户。
注意,在Orderby子句中未指定升序或降序时,Oracle按升序排列。
5.数据类型及其函数
1)、数值型数据
如果字段只包括数值型数据,select语句对它的数据操作如下表4-3所示:
表4-3算术操作符
操作符
运算
样例
+
加
Selectytd_sales+current_salesfromcustomer;
-
减
Selectytd_sales-current_salesfromcustomerwherestate_cd=‘NJ’;
*
乘
Selectytd_sales*commissionfromcustomer;
/
除
Selectytd_sales/12fromcustomer;
正如表4-3所示,我们可以执行所有标准的算术运算:
加、减、乘、除。
在Oracle中,除了这些标准运算符外,还有许多函数。
函数在SQL语句中用于处理表列内容。
在SQL语句中使用函数,函数作用的表列在显示值时将以作用后的值出现。
显示数值表列的绝对值是函数的一个很好的样例,值为-321的表列的绝对值为321。
在SQL*Plus中,求绝对值的表达式由关键字“abs”、后跟一对括号以及括号中的表列名称三部分组成,如:
abs(ytd_sales)。
因此SQL语句selectabs(ytd_sales)fromcustomer;将显示值321,而不论ytd_sales表列中的数据为-321或是+321。
表4-4列出了一些常见的处理数值型表列的函数,SQL*Plus示例以及显示的值。
select语句在表4-4中使用了一个名为dual的表,dual表的拥有者为SYS,在句法正确(即:
必须包含from子句),而数据库中又没有其他表可用于该语句时,可使用表dual。
表4-4数值型常用函数
函数
返回值
样例
显示
Ceil(n)
大于等于数值n的最小整数
selectceil(10.6)fromdual
11
Floor(n)
小于等于数值n的最大整数
selectfloor(10.6)fromdual
10
Mod(m,n)
M除以n的余数,若n=0,则返回m
selectmod(7,5)fromdual
2
Power(m,n)
M的n次方
selectpower(3,2)fromdual
9
Round(n,m)
将n四舍五入,保留小数点后m位
selectround(1234.5678,2)fromdual
1234.57
Sign(n)
若n=0,返回0;否则n>0,返回1;n<0,返回-1
selectsign(12)fromdual
1
Sqrt(n)
N的平方根
selectsqrt(25)fromdual
5
表4-4给出了部分可用于数值型数据的函数,要得到所有函数表及相应的示例和说明,可在SQL*Plus中键入命令helpfunctions。
如果我们将数值型函数用于非数值型数据,将出现Oracle错误。
如语句selectfloor(‘ABC’)fromdual;中,因为ABC不是数值,将引起下列的错误:
ERROR:
atline1:
ORA-01722:
invalidnumer
2)、字符型数据
字符型数据是createtable语句中定义为char、varchar或varchar2的域存放的数据。
字符型数据可以表示所有的字符、数字或可以从键盘输入的特殊字符。
有一系列的用于处理字符型数据的函数。
表4-5列出了最常用的字符型函数。
表4-5常用字符函数
函数
返回值
样例
显示
Initcap(char)
把每个字符串的第一个字符换成大写
Selectinitcap(‘mr.teplow’)fromdual;
Mr.Teplow
Lower(char)
整个字符串换成小写
Selectlower(‘Mr.FrankTownson’)fromdual;
mr.franktownson
Replace(char,str1,str2)
字符串中所有str1换成str2
Selectreplace(‘Scott’,’S’,’Boy’)fromdual;
Boycott
Soundex(char)
字符串的语音表示,常用于名字的模糊查询,可查找发音相似拼写不同的字符串
Selectlast_namefromemployeewheresoundex(last_name)=soundex(‘SMYTHE’)
SMITH
Substr(char,m,n)
取出从m字符开始的n个字符的子串
Selectsubstr(‘ABCDEF’,2,1)fromdual;
B
Length(char)
求字符串的长度
Selectlength(‘Anderson’)fromdual;
8
在介绍日期型数据前,我们着重介绍并置运算符,并联结两个字符域时,它非常有用。
虽然我们将其列为函数,但它实际是个运算符。
用两竖线“||”表示并置运算符。
执行语句select‘ABC’||’DEF’fromdual;,返回文本”ABCDEF”。
若某行的last_name值为John,执行语句select‘Dear’||last_name||’:
’fromcustomer;将返回文本“DearJohn:
”。
3)日期型数据
日期型数据是Oracle数据库中第三种常见的数据类型。
在建立customer表时,可以很容易字增加一个名为sale_date的日期域,如下所示:
SQL>createtablecustomer
2(last_namevarchar2(30)notnull,
3state_cdvarchar2
(2),
4salesnumber,
5sale_datedate);
Tablecreated.
Oracle中,日期型数据实际含有两个值:
日期和时间。
由于Oracle总是将日期和时间存放在一起,因此在比较两个日期时要加以注意。
Oracle中日期的缺省格式为:
DD-MON-YY,DD代表日,MON代表月,YY代表以两位数字表示的年。
注意,为保证进入21世纪不出问题,请尽可能的使用四位数字的年份DD-MON-YYYY。
4)日期型函数
Oracle为用户处理日期型数据提供了大量的函数。
例如,如果我们要在月末向一位顾客发催款单,在打印催款单时可用函数last_day将正确的日期输出到催款单的抬头。
表4-6给出了常用的日期函数。
表4-6常用日期型函数
函数
返回值
样例
显示
Sysdate
当前日期和时间
Selectsysdatefromdual;
28-FEB-02onFebruary28,2002
Last_day
本月最后一天
Selectlast_day(sysdate)fromdual;
31-MAR-02onMarch12,2002
Add_months(d,n)
当前日期d后推n个月
Selectadd_months(sysdate,2)fromdual;
18-MAY-02onMarch18,2002
Months_between(f,s)
日期f和s间相差月数
Selectmonths_between(sysdate,’12-MAR,-02fromdual;
13inApril2003
Next_day(d,day)
d后第一周指定的day日期
Selectnext_day(sysdate,’Monday’)fromdual;
03-JAN-02onDecember30,
2001
5)特殊格式的日期型数据
日期型数据有各种各样的格式。
表4-7给出了一些日期格式及其输出:
表4-7常用日期数据格式
格式
返回值
样例
显示
Y或YY或YYY
年的最后一位,两位或三位
Selectto_char(sysdate,’YYY’)fromdual;
002表示2002年
SYEAR或YEAR
年,SYEAR使公元前的年份前加一负号
Selectto_char(sysdate,’SYEAR’)fromdual;
-1112表示公元前1112年
Q
季度,1到3月为第一季度
Selectto_char(sysdate,’Q’)fromdual;
2
表示第二季度
MM
月份数
Selectto_char(sysdate,’MM’)fromdual;
12表示12月
RM
月份的罗马表示
Selectto_char(sysdate,’RM’)fromdual;
IV表示4月
Month
用9个字符长度表示的月份名
Selectto_char(sysdate,’Month’)fromdual;
May后跟6个空格表示5月
WW
当年第几周
Selectto_char(sysdate,’WW’)fromdual;
24表示2002年6月13日为第24周
W
本月第几周
Selectto_char(sysdate,’W’)fromdual;
1
2002年10月1日为第1周
DDD
当年第几天,1月1日为001,2月1日为032
Selectto_char(sysdate,’DDD’)fromdual;
363
2002年12月29日为第363天
DD
当月第几天
Selectto_char(sysdate,’DD’)fromdual;
04
10月4日为第4天
D
周内第几天
Selectto_char(sysdate,’D’)fromdual;
5
2002年3月14日为星期一
DY
周内第几天缩写
Selectto_char(sysdate,’DY’)fromdual;
SUN
2002年3月24日为星期天
HH或HH12
12进制小时数
Selectto_char(sysdate,’HH’)fromdual;
02
午夜2点过8分为02
HH24
24小时制
Selectto_char(sys
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第五 查询