基于Oracle的SQL语法：
1、create语句：
create table tablename
( column1 datatype [constraint],
column2 datatype [constraint],
.
.
.
columnn datatype [constraint]
);
常用的数据类型：char(size),varchar(size),number(size),date.
如：create table customer (
last_name varchar2(30) not null,
state_cd varchar(2),
sales number);
customer:
Teplow MA 23445.67
Abbev GA 6969.96
Alloex MA 9898.78
Nicholson CA 6868.99
Martin CA 2345.45
Laursen CA 34.34
Bambi CA 1234.55
McGraw NJ 123.89
2、describe语句：
用法: DESCRIBE [schema.]object[@db_link]；
如：describe customer;
3、Insert语句：
insert into tablename values(column1,column2,column3);
如：insert into customer values('Abbev','GA',6969.96);
insert into tablename (column1_name,column3_name) values(column1,column3);
如：insert into customer (last_name,sales) values ('Alloex',9898.78);
4、update语句：
update tablename1[,tablname2..]
set column1=新值[,column2=新值,column3=新值,..]
[where <条件表达式>];
5、delete语句：
delete命令用来从表中删除一行或多行数据。
delete from tablename
[where <条件表达式>];
6、alter语句：
在创建表后，用户可能想要增加表列时用到alter table 命令。在多数情况下，用户都
是用alter增加表列到表中的。
例如:
如果表customer要增加表列sale_date alter table customer add (sale_date
date)；
改变一个已存在表列的数据类型 alter table x modify(col1 date)；
同时更改多个表列 alter table x modify (col1 date,col5
number(3,1))；
删除不再想要的列 alter table customer drop column
sale_date；
7、Select语句：
select 检索信息
from tablename1[,tablename2...]
[where<条件表达式〉]
[order by<分类规则>]; /*分类*/
where子句：
(1),选择所有的表中数据：
select * from tablename;
如：select * from customer;
(2)and,or
如：
select *
from customer
where state_cd='CA' and sales>2000;
(3)!=(^=,<>),<,<=,>,>=,=
如：
select *
from customer
where state_cd != 'MA';
(4)between..and..,not between..and
如：
select *
from customer
where sales between 1000 and 10000;
(5)in(..),not in
如：
select *
from customer
where state_cd in ('CA','MA');
(6)like,not like
如：
select *
from customer
where last_name like 'M%' or last_name like '%cho%';
order by子句：
升序：asc（默认）
降序：desc
如：select * from customer order by state_cd desc,last_name;
select * from customer order by last_name,state_cd desc;
多级排序有先后之分。
8、别名：
select right.col,left.col
from X right,Y left
where right.col=left.col;
表7-7 数值型常用函数
函数返回值样例显示
ceil(n) 大于等于数值n的最小整数 select ceil(10.6)
from customer； 11
floor(n) 小于等于数值n的最大整数 select floor(10.6)
from customer； 1 0
mod(m,n) m除以n的余数，若n = 0，则返回m select mod(7,5) from
customer； 2
power(m,n) m的n次方 select power(3,2)
from customer； 9
round(n,m) 将n四舍五入，保留小数点后m位 select round
(1234.5678,2) from customer； 1 2 3 4 . 5 7
sign(n) 若n = 0，返回0；否则n > 0，返回1 ; select sign(12) from
customer； 1
n < 0 ,返回- 1
sqrt(n) n的平方根 select sqrt(25) from
customer； 5
表7-8 常用字符函数
函数返回值样例显示
initcap(char) 把每个字符串的第一个字符换成大写 Select initcap
('mr.teplow') from customer； Mr.Teplow
lower(char) 整个字符串换成小写 Select lower
('Mr.Frank Townson') from customer； mr.terry beaker
replace(char,str1,str2) 字符串中所有str1换成str2 select replace
('Scott','S','Boy') from customer； B o y c o t t
soundex(char) 字符串的语音表示，常用于 select last_name from
employee where
名字的模糊查询，可查找发音相似拼写不同的字符串 Abbey soundex
(last_name)=soundex('SMYTHE')；
substr(char,m,n) 取出从m字符开始的n个字符的子串 select substr
('ABCDEF',2,1) from customer； C D
length(char) 求字符串的长度 select length
('Anderson') from customer； 8
表7-9 常用日期型函数
函数返回值样例显示
Sysdate 当前日期和时间 Select sysdate from customer；
28-FEB-02 on February 28,2002
Last_day 本月最后一天 Select last_day(sysdate) from
customer； 31-MAR-02 on March 12,2002
Add_months(d,n) 当前日期d后推n个月 Select add_months(sysdate,2)
from customer；18-MAY-02 on March 18,2002
Months_between(f,s) 日期f和s间相差月数 Select months_between
(sysdate,'12- 13 in April 2003 MAR,-02')from customer；
Next_day(d,day) d后第一周指定的day日期 Select next_day
(sysdate,'Monday') from customer；03-JAN-02 on December30,2001
表7-10 常用日期数据格式
格式返回值样例显示
Y或YY或YYY 年的最后一位，两位或三位 Select to_char(sysdate,'YYY') from
customer； 002表示2002年
SYEAR或YEAR 年,SYEAR使公元前的年份 Select to_char(sysdate,'SYEAR') from
customer； -1112表示公元前1112年
Q 季度,1～3月为第一季度 Select to_char(sysdate,'Q') from
customer； 2表示第二季度
MM 月份数 Select to_char(sysdate,'MM') from
customer； 12表示12月
RM 月份的罗马表示 Select to_char(sysdate,'RM') from
customer； IV表示4月
Month 用9个字符长度表示的月份名 Select to_char(sysdate,'Month')
from customer； May后跟6个空格表示5月
WW 当年第几周 Select to_char(sysdate,'WW') from customer；
24表示2002年6月13日为第24周
W 本月第几周 Select to_char(sysdate,'W') from customer；
1 2002年10月1日为第1周
DDD 当年第几天,1月1日为001， Select to_char(sysdate,'DDD') from
customer； 363 2002年12月29日为第363天
DD 当月第几天 Select to_char(sysdate,'DD') from customer；
04 10月4日为第4天
D 周内第几天 Select to_char(sysdate,'D') from customer；
5 2002年3月14日为星期一
DY 周内第几天缩写 Select to_char(sysdate,'DY') from customer；
SUN 2002年3月24日为星期天
HH或HH12 12进制小时数 Select to_char(sysdate,'HH') from customer；
0 2午夜2点过8分为02
HH24 24小时制 select to_char(sysdate,'HH24') from customer；
14下午2点08分为14
MI 分钟数(0～59) Select to_char(sysdate,'MI') from customer；
17下午4点17分
SS 秒数(0～59) Select to_char(sysdate,'SS') from customer；
22 11点3分22秒
提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分
钟也能工作，但结果是错误的。
Oracle提供三种主要的转换函数。
(1).To_char 将任意类型的数据转换成字符类型。语句select to_char(8897) from
customer；返回一个包含字符串8 8 9 7的字符类型的数据。
(2).To_number 将一组合法的数字字符串(如字符数据8897)转换成数值。语句select
to_number('8897') from customer；返回一个包括数值8897的数值类型的数据。
(3).To_date 将适合格式的字符串数据转换成日期型数据，这是最容易产生错误的转
换。因为12-DEC-02是合法的日期格式，语句select to_date('12-DEC-02') from
customer；能成功地进行数据转换。而语句select to_date('bad date') from
customer；在执行时会出问题。语句select to_date('20021227','YYYYMMDD') from
customer；将返回日期27-DEC-02，因为在to_date函数中的日期格式和被转化的数据是
合法的。语句select to_date ('20021236','YYYYMMDD') from customer；将失败，因
为在12月中没有36天。
提示用to_date转换机制时，如果被转换的数据格式不对，会出现各种各样的Oracle错误
。
现在将学习如何把它们组合起来编写一个大的报表。在SQL*Plus中，有许多参数可控制
SQL*Plus的输出显示格式。利用SQL*Plus命令show all，用户能知道显示格式的当前设
置。
该命令的输出格式大致如下：
SQL> show all;
appinfo为OFF并且已设置为"SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and 为下一条 SELECT 语句的前几个字符
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK 为 ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
用于6或更多行的 FEEDBACK ON
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 15
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt
Arial,Helvetica,sans-serif; color:blac
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE 为OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 902000400
repfooter OFF and 为 NULL
repheader OFF and 为 NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 8.1.7
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and 为下一条 SELECT 语句的前几个字符
underline "-" (hex 2d)
USER 为"CIY"
verify ON
wrap : 行将为已换行
如上所示，用户可以设置其中很多参数来改变当前工作环境，下面介绍更改其中一些主
要参数。
1 、页和行的大小
命令set linesize告诉Oracle页宽是多少，最常用的设置为80和132。为设置行宽为80
，可输入命令set linesize 80。set pagesize命令告诉Oracle页的长度是多少，常用设
置为55和60。为了更容易地看到分页，可以用命令set pagesize 30设置页长为30行。
2、 页头标
可以用ttitle命令设置每页的标题。ttitle命令包括许多选项。通常使用的缺省设置
为：标题文本在行中央，每页上都有日期和页号。如果需要两行头标，则需要使用竖字
符( | )。命令ttitle 'Database techologies|Customer Report'使Orale居中放置文本
“ Database Technologies”为第一个头标行，然后将文本“ Customer Report”居中
放置在第二行。
3、 页脚标
可用btitle命令在每页的底部放置一些信息。我们建议用户将程序名放在此处。如果
用户需要改一个报告时，只要指定页底部的程序名，就可知道要改那个报告，这有助于
避免混淆。命令btitle '---sample.sql---'使SQL*Plus把文本“---sample.sql---”放
于每一页底部中央。用户还可以使用left或right 关键字将btitle命令中的文字放到相
应的位置。如果btitle命令中没有用
于定位的关键字，Oracle将文本放在底行的中央。
4、 将SQL*Plus结果写到文件中
spool命令使Oracle将SQL*Plus的输出保存到一个数据文件中。spool命令中要指定输
出文件名。如在Windows NT上，要将SQL*Plus的输出保存到数据文件out.lis中，可用命
令spool c:/report/out.lis。在大多数操作系统中，如果用户未指定后缀，系统会自动
在文件名后增加后缀“.lst”。例如，命令spool report会自动产生一个名为
report.lst的文件。
提示SQL*Plus自动把文件扩展名加到spool命令中指定的文件名后。在不同的操作系统中
，扩展名可能不同。
如果已执行了命令set linesize 70, set pagesize 23，并用命令col sales format
99999999格式化了sales表列，要保存SQL语句select * from customer的输出到一个文
件。这个输出将放在用spool命令指定的文件中。请注意，因为sales表列是用99999999
格式化的，因此显示结果中没有小数。
为停止假脱机(spooling)，可用命令spool off 或spool out。后者关闭输出文件并打印
输出。
5、 格式化输出表列
大多数情况下，用户需要格式化实际的表列数据， Column 命令可完成这项工作。下
面先执行两条格式化命令，然后再查询customer表。
命令column last_name format a8 wrap heading 'Last|Name'告诉SQL*Plus在
last_name表列只显示8个字符。8代表last_name的显示宽度，a表示每个位置只能是字符
。其中wrap部分说明如果last_name多于8个字符，多余的字符显示在下一行的对应位置
。Heading部分告诉SQL*Plus把“Last Name”分两行打印在报告的头上。
命令column state_cd format a8 heading 'State|Code' 让SQL*Plus以8个字符位置
显示state_cd，并且在state_cd表列的顶部放一个两行的列标题“ State Code”。同样
的S Q L语句select * from customer； 的输出结果如图所示。
Last State YTD
Name Code Sales
-------- -------- ----------
Teplow MA 23445.67
Abbev GA 6969.96
Alloex MA 9898.78
Nicholso CA 6868.99
n
Martin 2345.45
Laursen 34.34
Bambi 1234.55
McGraw NJ 123.89
现在我们来格式化数值型域。format子句可规定用于显示每个数值的位数，并指明在何
处插入逗号。语句column sales format 999,999,999,999.00 heading 'Sales'使
SQL*Plus显示12位数字，两位小数，用逗号作分隔符。
1. 断开逻辑(Break Logic)
现在增加断开逻辑。在SQL*Plus中，增加断开逻辑的方法很简单。只要执行break命令
，SQL*Plus就会管理所有的断开逻辑。我们来看看执行表列格式化命令col sales
format 999999.00 和S Q L查询语句select state_cd,last_name, sales from
customer order by state_cd,last_name；，未用断开逻辑时，其输出如图7 - 4所示。
STAT LAST_NAME SALES
---- ------------------------------------------------------------ ----------
CA Bambi 1234.55
CA Laursen 34.34
CA Martin 2345.45
CA Nicholson 6868.99
GA Abbev 6969.96
MA Alloex 9898.78
MA Teplow 23445.67
NJ McGraw 123.89
执行断开命令break on state_cd，上述查询语句的输出如图7 - 5所示。
STAT LAST_NAME SALES
---- ------------------------------------------------------------ ----------
CA Bambi 1234.55
Laursen 34.34
Martin 2345.45
Nicholson 6868.99
GA Abbev 6969.96
MA Alloex 9898.78
Teplow 23445.67
NJ McGraw 123.89
注意，在图7 - 5中，state_cd为C A的在第1行显示后，第2行至第5行都不显示(因为从
第一行后state_cd没有改变)，然后第6行和第7行的值不同，又再显示。图7-5 使用断开
逻辑的输出
提示:为在SQL*Plus中实现断开逻辑，必须在断开命令指定的表列上进行查询排序。
为说明此点，执行查询select state_cd,last_name, sales from customer order by
last_name；再执行命令break on state_cd，输出如图7 - 6所示。
STAT LAST_NAME SALES
---- ------------------------------------------------------------ ----------
GA Abbev 6969.96
MA Alloex 9898.78
CA Bambi 1234.55
Laursen 34.34
Martin 2345.45
NJ McGraw 123.89
CA Nicholson 6868.99
MA Teplow 23445.67
图7-6 在state_cd上断开以last_name列排序的输出在图7 - 6中，先打印四个CA，后跟
一个NJ，再是一个CA，可见last_name排序的查询结果已与图7 - 5中用state_cd排序的
大不相同了。
2. 断开和跳过(Break and Skip)
通常，实现断开逻辑后，我们希望在出现新的断开列值前空几行，这时就要用到s k i
p命令。用命令col sales format $999,999,999.99 heading 'YTD|Sales'重新格式化sa
les表列，再执行断开命令break on state_cd skip 1。现在，语句select state_cd,
last_name, sales from customer order by state_cd, last_name；产生如图7 - 7所
示的输出。
STAT LAST_NAME
---- ------------
YTD
Sales
----------------
CA Bambi
$1,234.55
Laursen
$34.34
Martin
$2,345.45
Nicholson
$6,868.99
GA Abbev
$6,969.96
MA Alloex
$9,898.78
Teplow
$23,445.67
NJ McGraw
$123.89
图7-7 断开后后空一行的输出报告.请注意Oracle是怎样控制打印state_cd表列的。利用
这一点可以很容易地通知Oracle计算每个断开部分的总销售(sales)值。
3. 在断开点计算表列值
用compute sum 命令，用户可以让SQL*Plus计算所需的和。现在讨论完成断开格式化
和计算Y T D合计值所需的命令。
命令compute sum of sales on report是在报表结尾处，计算报表的合计值。r e p o r
t在此处用于触发数值域求和，并显示在报表的结尾处。而命令“ compute sum of
sales on state_cd” 使在打印新的state_cd时打印合计。
为同时打印报表合计和断开点合计，需要用命令break on report skip 1 on state_cd
skip 1重置断开条件。修改SQL 语句以便用 state_cd排序数据。对于断开报表，必须用
break 语句中同样的表列排序数据。
注意为使本节的输出能打印在一页中，必须增加页尺寸。为此输入命令set pagesize 28
。
现在，所需的S Q L语句为select state_cd, last_name, sales from customer order
by
state_cd, last_name；。用语句col sales format $999,999,999.00 heading
'YTD|Sales'重新格式化
s a l e s表列后，执行上述s e l e c t语句，其断开输出如图7 - 8所示
4. 基于双表的断开逻辑与求和
最后，利用本章学到的知识给出一个最后的报表。表连接：在多个表中，通过匹配表
列值选择数据。下面我们显示州名(state_name)而非州名代码(state_cd)。图7-8 结尾
处带总计的state_cd断开报表,我们需要state表中的state_name表列的值，可以通过
state_cd连接state和customer两个表。选择语句select state_name, last_name,
sales from state a,customer b where a.state_cd =b.state_cd order by
state_name, last_name；。因为计算时使用state_name表列，所以必须用break on
report skip 1 on state_name skip 1重新向SQL*Plus描述断开条件。我们也需要用
compute sum of sales on state_name和compute sum of sales on report语句重新定
义断开点合计和报表合计。现在执行连接两个表的查询，输出结果如图7 - 9所示。
扫一扫
1574
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
