【网学网提醒】：网学会员为大家收集整理了sql入门到精通学习教程提供大家参考，希望对大家有所帮助!
oracle常用sql语句
.执行一个SQL脚本文件
SQL>startfile_name
SQL>@file_name
2.对当前的输入进行编辑
SQL>edit
3.重新运行上一次运行的sql语句
SQL>/
4.将显示的内容输出到指定文件
SQL>SPOOLfile_name
在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。
5.关闭spool输出
SQL>SPOOLOFF
只有关闭spool输出，才会在输出文件中看到输出的内容。
6．显示一个表的结构
SQL>desctable_name
7.COL命令：
主要格式化列的显示形式。
该命令有许多选项，具体如下：
COL[UMN][{column|expr}[option...]]
Option选项可以是如下的子句:
ALI[AS]alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT]format
HEA[DING]text
JUS[TIFY]{L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE{expr|alias}
NEWL[INE]
NEW_V[ALUE]variable
NOPRI[NT]|PRI[NT]
NUL[L]text
OLD_V[ALUE]variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
1).改变缺省的列标题
COLUMNcolumn_nameHEADINGcolumn_heading
Forexample:
Sql>select*fromdept;
DEPTNODNAMELOC
-----------------------------------------------
10ACCOUNTINGNEWYORK
sql>colLOCheadinglocation
sql>select*fromdept;
DEPTNODNAMElocation
------------------------------------------------
10ACCOUNTINGNEWYORK
2).将列名ENAME改为新列名EMPLOYEENAME并将新列名放在两行上：
Sql>select*fromemp
DepartmentnameSalary
------------------------------
10aaa11
SQL>COLUMNENAMEHEADING’Employee|Name’
Sql>select*fromemp
Employee
DepartmentnameSalary
------------------------------
10aaa11
note:thecolheadingturnintotwolinesfromoneline.
3).改变列的显示长度：
FOR[MAT]format
Sql>selectempno,ename,jobfromemp;
EMPNOENAMEJOB
-----------------------------
7369SMITHCLERK
7499ALLENSALESMAN
7521WARDSALESMAN
Sql>colenameformata40
EMPNOENAMEJOB
-----------------------------------------------------------
7369SMITHCLERK
7499ALLENSALESMAN
7521WARDSALESMAN
4).设置列标题的对齐方式
JUS[TIFY]{L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL>colenamejustifycenter
SQL>/
EMPNOENAMEJOB
-----------------------------------------------------------
7369SMITHCLERK
7499ALLENSALESMAN
7521WARDSALESMAN
对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边
5).不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL>coljobnoprint
SQL>/
EMPNOENAME
--------------------------------------------------
7369SMITH
7499ALLEN
7521WARD
6).格式化NUMBER类型列的显示：
SQL>COLUMNSALFORMAT$99,990
SQL>/
Employee
DepartmentNameSalaryCommission
---------------------------------------
30ALLEN$1,600300
7).显示列值时，如果列值为NULL值，用text值代替NULL值
COMMNUL[L]text
SQL>COLCOMMNUL[L]text
8).设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
COL1
--------------------
HOWAREYOU?
SQL>COLCOL1FORMATA5
SQL>COLCOL1WRAPPED
COL1
-----
HOWA
REYO
U?
SQL>COLCOL1WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?
SQL>COLCOL1WORD_WRAPPED
COL1
-----
HOWA
9).显示列的当前的显示属性值
SQL>COLUMNcolumn_name
10).将所有列的显示属性设为缺省值
SQL>CLEARCOLUMNS
8.屏蔽掉一个列中显示的相同的值
BREAKONbreak_column
SQL>BREAKONDEPTNO
SQL>SELECTDEPTNO,ENAME,SAL
FROMEMP
WHERESAL<2500
ORDERBYDEPTNO;
DEPTNOENAMESAL
------------------------------
10CLARK2450
MILLER1300
20SMITH800
ADAMS1100
9.在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
BREAKONbreak_columnSKIPn
SQL>BREAKONDEPTNOSKIP1
SQL>/
DEPTNOENAMESAL
------------------------------
10CLARK2450
MILLER1300
20SMITH800
ADAMS1100
10.显示对BREAK的设置
SQL>BREAK
11.删除6、7的设置
SQL>CLEARBREAKS
12.Set命令：
该命令包含许多子命令：
SETsystem_variablevalue
system_variablevalue可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE]{15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT]{ON|OFF}
AUTORECOVERY[ON|OFF]
AUTOT[RACE]{ON|OFF|TRACE[ONLY]}[EXP[LAIN]][STAT[ISTICS]]
BLO[CKTERMINATOR]{.|c}
CMDS[EP]{;|c|ON|OFF}
COLSEP{_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT]{.|c|ON|OFF}
COPYC[OMMIT]{0|n}
COPYTYPECHECK{ON|OFF}
DEF[INE]{&;|c|ON|OFF}
DESCRIBE[DEPTH{1|n|ALL}][LINENUM{ON|OFF}][INDENT{ON|OFF}]
ECHO{ON|OFF}
EDITF[ILE]file_name[.ext]
EMB[EDDED]{ON|OFF}
ESC[APE]{\|c|ON|OFF}
FEED[BACK]{6|n|ON|OFF}
FLAGGER{OFF|ENTRY|INTERMED[IATE]|FULL}
FLU[SH]{ON|OFF}
HEA[DING]{ON|OFF}
HEADS[EP]{||c|ON|OFF}
INSTANCE[instance_path|LOCAL]
LIN[ESIZE]{80|n}
LOBOF[FSET]{n|1}
LOGSOURCE[pathname]
LONG{80|n}
LONGC[HUNKSIZE]{80|n}
MARK[UP]HTML[ON|OFF][HEADte
xt][BODYtext][ENTMAP{ON|OFF}][SPOOL
{ON|OFF}][PRE[FORMAT]{ON|OFF}]
NEWP[AGE]{1|n|NONE}
NULLtext
NUMF[ORMAT]format
NUM[WIDTH]{10|n}
PAGES[IZE]{24|n}
PAU[SE]{ON|OFF|text}
RECSEP{WR[APPED]|EA[CH]|OFF}
RECSEPCHAR{_|c}
SERVEROUT[PUT]{ON|OFF}[SIZEn][FOR[MAT]{WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT]{VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE]{ON|OFF}
SQLBL[ANKLINES]{ON|OFF}
SQLC[ASE]{MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE]{>|text}
SQLN[UMBER]{ON|OFF}
SQLPRE[FIX]{#|c}
SQLP[ROMPT]{SQL>|text}
SQLT[ERMINATOR]{;|c|ON|OFF}
SUF[FIX]{SQL|text}
TAB{ON|OFF}
TERM[OUT]{ON|OFF}
TI[ME]{ON|OFF}
TIMI[NG]{ON|OFF}
TRIM[OUT]{ON|OFF}
TRIMS[POOL]{ON|OFF}
UND[ERLINE]{-|c|ON|OFF}
VER[IFY]{ON|OFF}
WRA[P]{ON|OFF}
1).设置当前session是否对修改的数据进行自动提交
SQL>SETAUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
SQL>SETECHO{ON|OFF}
3).是否显示当前sql语句查询或修改的行数
SQL>SETFEED[BACK]{6|n|ON|OFF}
默认只有结果大于6行时才显示结果的行数。如果setfeedback1，则不管查询到多少行都返回。当为off时，一律不显示查询的行数
4).是否显示列标题
SQL>SETHEA[DING]{ON|OFF}
当setheadingoff时，在每页的上面不显示列标题，而是以空白行代替
5).设置一行可以容纳的字符数
SQL>SETLIN[ESIZE]{80|n}
如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。
6).设置页与页之间的分隔
SQL>SETNEWP[AGE]{1|n|NONE}
当setnewpage0时，会在每页的开头有一个小的黑方框。
当setnewpagen时，会在页和页之间隔着n个空行。
当setnewpagenone时，会在页和页之间没有任何间隔。
7).显示时，用text值代替NULL值
SQL>SETNULLtext
8).设置一页有多少行数
SQL>SETPAGES[IZE]{24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题
9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL>SETSERVEROUT[PUT]{ON|OFF}
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。
10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。
SQL>SETWRA[P]{ON|OFF}
当输出的行的长度大于设置的行的长度时（用setlinesizen命令设置），当setwrapon时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。
11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。
SQL>SETTERM[OUT]{ON|OFF}
在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕
上会耗费大量的时间，设置settermspooloff后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。
12).将SPOOL输出中每行后面多余的空格去掉
SQL>SETTRIMS[OUT]{ON|OFF}
13)显示每个sql语句花费的执行时间
setTIMING{ON|OFF}
14).遇到空行时不认为语句已经结束，从后续行接着读入。
SETSQLBLANKLINESON
Sql*plus中,不允许sql语句中间有空行,这在从其它地方拷贝脚本到sql*plus中执行时很麻烦.比如下面的脚本:
selectdeptno,empno,ename
fromemp
whereempno='7788';
如果拷贝到sql*plus中执行,就会出现错误。这个命令可以解决该问题
15).设置DBMS_OUTPUT的输出
SETSERVEROUTPUTONBUFFER20000
用dbms_output.put_line('strin_content');可以在存储过程中输出信息，对存储过程进行调试
如果想让dbms_output.put_line('abc');的输出显示为：
SQL>abc，而不是SQL>abc，则在SETSERVEROUTPUTON后加formatwrapped参数。
16).输出的数据为html格式
setmarkuphtml
在8.1.7版本(也许是816?不太确定)以后,sql*plus中有一个setmarkuphtml的命令,可以将sql*plus的输出以html格式展现.
注意其中的spoolon,当在屏幕上输出的时候,我们看不出与不加spoolon有什么区别,但是当我们使用spoolfilename输出到文件的时候,会看到spool文件中出现了等tag.
14．修改sqlbuffer中的当前行中，第一个出现的字符串
C[HANGE]/old_value/new_value
SQL>l
1*select*fromdept
SQL>c/dept/emp
1*select*fromemp
15．编辑sqlbuffer中的sql语句
EDI[T]
16．显示sqlbuffer中的sql语句，listn显示sqlbuffer中的第n行，并使第n行成为当前行
L[IST][n]
17．在sqlbuffer的当前行下面加一行或多行
I[NPUT]
18．将指定的文本加到sqlbuffer的当前行后面
A[PPEND]
SQL>selectdeptno,
2dname
3fromdept;
DEPTNODNAME
------------------------
10ACCOUNTING
20RESEARCH
30SALES
40OPERATIONS
SQL>L2
2*dname
SQL>a,loc
2*dname,loc
SQL>L
1selectdeptno,
2dname,loc
3*fromdept
SQL>/
DEPTNODNAMELOC
-------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
19．将sqlbuffer中的sql语句保存到一个文件中
SAVEfile_name
20．将一个文件中的sql语句导入到sqlbuffer中
GETfile_name
21．再次执行刚才已经执行的sql语句
RUN
or
/
22．执行一个存储过程
EXECUTEprocedure_name
23．在sql*plus中连接到指定的数据库
CONNECTuser_name/passwd@db_alias
24．设置每个报表的顶部标题
TTITLE
25．设
置每个报表的尾部标题
BTITLE
26．写一个注释
REMARK[text]
27．将指定的信息或一个空行输出到屏幕上
PROMPT[text]
28．将执行的过程暂停，等待用户响应后继续执行
PAUSE[text]
Sql>PAUSEAdjustpaperandpressRETURNtocontinue.
29．将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库）
COPY{FROMdatabase|TOdatabase|FROMdatabaseTOdatabase}
{APPEND|CREATE|INSERT|REPLACE}destination_table
[(column,column,column,...)]USINGquery
sql>COPYFROMSCOTT/TIGER@HQTOJOHN/CHROME@WEST
createemp_temp
USINGSELECT*FROMEMP
30．不退出sql*plus，在sql*plus中执行一个操作系统命令：
HOST
Sql>hosthostname
该命令在windows下可能被支持。
31．在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus：
!
sql>!
$hostname
$exit
sql>
该命令在windows下不被支持。
32．显示sql*plus命令的帮助
HELP
如何安装帮助文件：
Sql>@?\sqlplus\admin\help\hlpbld.sql?\sqlplus\admin\help\helpus.sql
Sql>helpindex
33．显示sql*plus系统变量的值或sql*plus环境变量的值
Syntax
SHO[W]option
whereoptionrepresentsoneofthefollowingtermsorclauses:
system_variable
ALL
BTI[TLE]
ERR[ORS][{FUNCTION|PROCEDURE|PACKAGE|PACKAGEBODY|
TRIGGER|VIEW|TYPE|TYPEBODY}[schema.]name]
LNO
PARAMETERS[parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER
1).显示当前环境变量的值：
Showall
2).显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Showerror
当创建一个函数、存储过程等出错时，变可以用该命令查看在那个地方出错及相应的出错信息，进行修改后再次进行编译。
3).显示初始化参数的值：
showPARAMETERS[parameter_name]
4).显示数据库的版本：
showREL[EASE]
5).显示SGA的大小
showSGA
6).显示当前的用户名
showuser
34.查询一个用户下的对象
SQL>select*fromtab;
SQL>select*fromuser_objects;
35.查询一个用户下的所有的表
SQL>select*fromuser_tables;
36.查询一个用户下的所有的索引
SQL>select*fromuser_indexes;
37.定义一个用户变量
方法有两个：
a.define
b.COL[UMN][{column|expr}NEW_V[ALUE]variable[NOPRI[NT]|PRI[NT]]
OLD_V[ALUE]variable[NOPRI[NT]|PRI[NT]]
下面对每种方式给予解释：
a.Syntax
DEF[INE][variable]|[variable=text]
定义一个用户变量并且可以分配给它一个CHAR值。
assignthevalueMANAGERtothevariablePOS,type:
SQL>DEFINEPOS=MANAGER
assigntheCHARvalue20tothevariableDEPTNO,type:
SQL>DEFINEDEPTNO=20
list
thedefinitionofDEPTNO,enter
SQL>DEFINEDEPTNO
―――――――――――――――
DEFINEDEPTNO=”20”(CHAR)
定义了用户变量POS后，就可以在sql*plus中用&;POS或&;&;POS来引用该变量的值，sql*plus不会再提示你给变量输入值。
b.COL[UMN][{column|expr}NEW_V[ALUE]variable[NOPRI[NT]|PRI[NT]]
NEW_V[ALUE]variable
指定一个变量容纳查询出的列值。
例:columncol_namenew_valuevar_namenoprint
selectcol_namefromtable_namewhere……..
将下面查询出的col_name列的值赋给var_name变量.
一个综合的例子:
得到一个列值的两次查询之差(此例为10秒之内共提交了多少事务):
columnredo_writesnew_valuecommit_count
selectsum(stat.value)redo_writes
fromv$sesstatstat,v$statnamesn
wherestat.statistic#=sn.statistic#
andsn.name='usercommits';
--等待一会儿(此处为10秒);
executedbms_lock.sleep(10);
setverioff
selectsum(stat.value)-&;commit_countcommits_added
fromv$sesstatstat,v$statnamesn
wherestat.statistic#=sn.statistic#
andsn.name='usercommits';
38.定义一个绑定变量
VAR[IABLE][variable[NUMBER|CHAR|CHAR(n)|NCHAR|NCHAR(n)|VARCHAR2(n)|NVARCHAR2(n)|CLOB|NCLOB|REFCURSOR]]
定义一个绑定变量，该变量可以在pl/sql中引用。
可以用print命令显示该绑定变量的信息。
如：
columninst_numheading"InstNum"new_valueinst_numformat99999;
columninst_nameheading"Instance"new_valueinst_nameformata12;
columndb_nameheading"DBName"new_valuedb_nameformata12;
columndbidheading"DBId"new_valuedbidformat9999999999justc;
prompt
promptCurrentInstance
prompt~~~~~~~~~~~~~~~~
selectd.dbiddbid
,d.namedb_name
,i.instance_numberinst_num
,i.instance_nameinst_name
fromv$databased,
v$instancei;
variabledbidnumber;
variableinst_numnumber;
begin
:dbid:=&;dbid;
:inst_num:=&;inst_num;
end;
/
说明：
在sql*plus中，该绑定变量可以作为一个存储过程的参数，也可以在匿名PL/SQL块中直接引用。为了显示用VARIABLE命令创建的绑定变量的值，可以用print命令
注意：
绑定变量不同于变量：
1．定义方法不同
2．引用方法不同
绑定变量：:variable_name
变量：&;variable_nameor&;&;variable_name
3.在sql*plus中，可以定义同名的绑定变量与用户变量，但是引用的方法不同。
39.&;与&;&;的区别
&;用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。
&;&;用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&;&;命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。
如，将下面三行语
句存为一个脚本文件，运行该脚本文件，会提示三次，让输入deptnoval的值：
selectcount(*)fromempwheredeptno=&;deptnoval;
selectcount(*)fromempwheredeptno=&;deptnoval;
selectcount(*)fromempwheredeptno=&;deptnoval;
将下面三行语句存为一个脚本文件，运行该脚本文件，则只会提示一次，让输入deptnoval的值：
selectcount(*)fromempwheredeptno=&;&;deptnoval;
selectcount(*)fromempwheredeptno=&;&;deptnoval;
selectcount(*)fromempwheredeptno=&;&;deptnoval;
40．在输入sql语句的过程中临时先运行一个sql*plus命令(摘自itpub)
#
有没有过这样的经历?在sql*plus中敲了很长的命令后,突然发现想不起某个列的名字了,如果取消当前的命令,待查询后再重敲,那太痛苦了.当然你可以另开一个sql*plus窗口进行查询,但这里提供的方法更简单.
比如说,你想查工资大于4000的员工的信息,输入了下面的语句:
SQL>selectdeptno,empno,ename
2fromemp
3where
这时,你发现你想不起来工资的列名是什么了.
这种情况下,只要在下一行以#开头,就可以执行一条sql*plus命令,执行完后,刚才的语句可以继续输入
SQL>>selectdeptno,empno,ename
2fromemp
3where
6#descemp
NameNull?Type
---------------------------------------------------------------
EMPNONOTNULLNUMBER(4)
ENAMEVARCHAR2(10)
JOBVARCHAR2(9)
MGRNUMBER(4)
HIREDATEDATE
SALNUMBER(7,2)
COMMNUMBER(7,2)
DEPTNONUMBER(2)
6sal>4000;
DEPTNOEMPNOENAME
------------------------------
107839KING
41.SQLPlus中的快速复制和粘贴技巧(摘自cnoug.org)
1)鼠标移至想要复制内容的开始
2)用右手食指按下鼠标左键
3)向想要复制内容的另一角拖动鼠标，与Word中选取内容的方法一样
4)内容选取完毕后（所选内容全部反显），鼠标左键按住不动，用右手中指按鼠标右键
5)这时，所选内容会自动复制到SQL*Plus环境的最后一行
Sql*plus中蕴藏着好多技巧，如果掌握这些技巧，对于在oracle数据库下进行快速开发与有效维护数据库都是有益的。
下面就接受一二，
1．使用SQL*PLUS动态生成批量脚本
将spool与select命令结合起来使用，可以生成一个脚本，脚本中包含有可以批量执行某一任务的语句。
例1：
生成一个脚本，删除SCOTT用户下的所有的表：
a.创建gen_drop_table.sql文件，包含如下语句：
SPOOLc:\drop_table.sql
SELECT'DROPTABLE'||table_name||';'FROMuser_tables;
SPOOLOFF
b.以SCOTT用户登录数据库
SQLPLUS>@…..\gen_dorp_table.sql
c.在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:
SQL>SELECT'DROPTABLE'||table_name||';'FROMuser_tables;
'DROPTABLE'||TABLE_NAME||';'
--------------------------------------------------------------------------------
DROPTABLEDEPT;
DROPTABLEEMP;
DROPTABLEPARENT;
DROPTABLESTAT_VENDER_TEMP;
DROPTABLETABLE_FORUM;
5rowsselected.
SQL>SPOOLOFF
d.对生成的drop_table.sql文件进行编辑去掉不必要的部分，只留下droptable…..语句
e.在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。
SQLPLUS>@c:\dorp_table.sql
在上面的操作中，在生成的脚本文件中会有多余的字符，如运行的sql语句，标题，或返回的行数，需要我们编辑该脚本后再运行，给实际的操作带来诸多不便。懒惰是人的本性，这促使我们用更简单的办法来实现上面的任务。
a.创建gen_drop_table.sql文件，包含如下语句：
setechooff
setfeedbackoff
setnewpagenone
setpagesize5000
setlinesize500
setverifyoff
setpagesize0
settermoff
settrimson
setlinesize600
setheadingoff
settimingoff
setverifyoff
setnumwidth38
SPOOLc:\drop_table.sql
SELECT'DROPTABLE'||table_name||';'FROMuser_tables;
SPOOLOFF
b.以SCOTT用户登录数据库
SQLPLUS>@…..\gen_dorp_table.sql
c.在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:
DROPTABLEDEPT;
DROPTABLEEMP;
DROPTABLEPARENT;
DROPTABLESTAT_VENDER_TEMP;
DROPTABLETABLE_FORUM;
d.在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。
SQLPLUS>@c:\dorp_table.sql
2．将一个表中的数据导出生成一个文本文件，列与列之间以”,”隔开
setechooff
setfeedbackoff
setnewpagenone
setpagesize5000
setlinesize500
setverifyoff
setpagesize0
settermoff
settrimson
setlinesize600
setheadingoff
settimingoff
setverifyoff
setnumwidth38
SPOOLc:\drop_table.sql
selectDEPTNO||','||DNAMEFROMDEPT;
SPOOLOFF
将上面的内容保存为一个文本文件后，以scott登录，执行该文件后显示结果：
10,ACCOUNTING
20,RESEARCH
30,SALES
40,OPERATIONS
通过上面的两个例子，我们可以将：
setechooff
setfeedbackoff
setnewpagenone
setpagesize5000
setlinesize500
setverifyoff
setpagesize0
settermoff
settrimson
setlinesize600
setheadingoff
settimingoff
setverifyoff
setnumwidth
38
SPOOLc:\具体的文件名
你要运行的sql语句
SPOOLOFF
作为一个模版，只要将必要的语句假如这个模版就可以了。
在oracle的较新版本中，还可以用setcolsep命令来实现上面的功能：
SQL>setcolsep,
SQL>select*fromdept;
10,ACCOUNTING,NEWYORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
35,aa,bb
3．动态生成spool命令所需的文件名
在我们上面的例子中，spool命令所需要的文件名都是固定的。有时我们需要每天spool一次，并且每次spool的文件名都不相同，如文件名包含当天的日期，该如何实现呢？
columndat1new_valuefilename;
selectto_char(sysdate,'yyyymmddhh24mi')dat1fromdual;
spoolc:\&;&;filename..txt
select*fromdept;
spooloff;
4．如何从脚本文件中得到WINDOWS环境变量的值：
在windos中：
spoolc:\temp\%ORACLE_SID%.txt
select*fromdept;
...
spooloff
在上面的例子中，通过%ORACLE_SID%的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt
在UNIX中：
spoolc:\temp\$ORACLE_SID.txt
select*fromdept;
...
spooloff
在上面的例子中，通过$ORACLE_SID的方式引用环境变量ORACLE_SID的值，如果ORACLE_SID的值为orcl，则生成的spool文件名为：orcl.txt
5．如何指定缺省的编辑脚本的目录
在sql*plus中，可以用save命令，将上一条执行的sql语句保存到一个文件中，但是如何设置该文件的缺省目录呢？
通过SQL>seteditfilec:\temp\file.sql命令，可以设置其缺省目录为c:\tmpe，缺省文件名为file.sql。
6．如何除去表中相同的行
找到相同的行：
SELECT*FROMdepta
WHEREROWID<>(SELECTMAX(ROWID)
FROMdeptb
WHEREa.deptno=b.deptno
ANDa.dname=b.dname--Makesureallcolumnsarecompared
ANDa.loc=b.loc);
注释：
如果只找deptno列相同的行，上面的查询可以改为：
SELECT*FROMdepta
WHEREROWID<>(SELECTMAX(ROWID)
FROMdeptb
WHEREa.deptno=b.deptno)
删除相同的行：
DELETEFROMdepta
WHEREROWID<>(SELECTMAX(ROWID
FROMdeptb
WHEREa.deptno=b.deptno
ANDa.dname=b.dname--Makesureallcolumnsarecompared
ANDa.loc=b.loc);
注意：上面并不删除列值为null的行。
7．如何向数据库中插入两个单引号(’’)
Insertinotdeptvalues(35,’aa’’’’bb’,’a’’b’);
在插入时，用两个’表示一个’。
8．如何设置sql*plus的搜寻路径，这样在用@命令时，就不用输入文件的全路径。
设置SQLPATH环境变量。
如：
SQLPATH=C:\ORANT\DBS;C:\APPS\SCRIPTS;C:\MYSCRIPTS
9．@与@@的区别是什么？
@等于start命令，用来运行一个sql脚本文件。
@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。
@@用在脚本文件中，用来指定用@@执行的文件与@@所在的文件在同一目录，而不用指定全路径，也不从SQLPATH环境变量指定的路径中寻找文件，该命令一般用在嵌套脚本文件中。
10．&;与&;&;的区别
&;用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值。
&;&;用来创建一个持久变量，就像用用define命令或带new_vlaue字句的column命令创建的持久变量一样。当用&;&;命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。
如，将下面三行语句存为一个脚本文件，运行该脚本文件，会提示三次，让输入deptnoval的值：
selectcount(*)fromempwheredeptno=&;deptnoval;
selectcount(*)fromempwheredeptno=&;deptnoval;
selectcount(*)fromempwheredeptno=&;deptnoval;
将下面三行语句存为一个脚本文件，运行该脚本文件，则只会提示一次，让输入deptnoval的值：
selectcount(*)fromempwheredeptno=&;deptnoval;
selectcount(*)fromempwheredeptno=&;deptnoval;
selectcount(*)fromempwheredeptno=&;deptnoval;
11．引入copy的目的
Copy命令在两个数据库之间拷贝数据时特别有用，特别是该命令可以在两个数据库之间传递long型字段的数据。
缺点：
在两个数据库之间传递数据时，有可能丢失精度(loseprecision)。
12．问什么在修改大量的行时，我的脚本会变得很慢？
当通过PL/SQL块修改一个表中的许多行时，你会创建在表上创建一个cursor，但是只有在你关闭cursor时，才会释放ROLLBACKSEGMENT，这样，当cursor仍然打开时，修改过程会变慢，这是因为数据库不得不搜寻大量的rollbacksegment以便于维护读一致性。为了避免这样情况，试着在表上加一个标志字段来描述该行是否已经被修改，然后关闭该cursor，然后再打开该cursor。每次可以修改5000行
掌握SQL四条最基本的数据操作语句：Insert，Select，Update和Delete。
练掌握SQL是数据库用户的宝贵财富。在本文中，我们将引导你掌握四条最基本的数据操作语句—SQL的核心功能—来依次介绍比较操作符、选择断言以及三值逻辑。当你完成这些学习后，显然你已经开始算是精通SQL了。
在我们开始之前，先使用CREATETABLE语句来创建一个表（如图1所示）。DDL语句对数据库对象如表、列和视进行定义。它们并不对表中的行进行处理，这是因为DDL语句并不处理数据库中实际的数据。这些工作
由另一类SQL语句—数据操作语言（DML）语句进行处理。
SQL中有四种基本的DML操作：INSERT，SELECT，UPDATE和DELETE。由于这是大多数SQL用户经常用到的，我们有必要在此对它们进行一一说明。在图1中我们给出了一个名为EMPLOYEES的表。其中的每一行对应一个特定的雇员记录。请熟悉这张表，我们在后面的例子中将要用到它。
INSERT语句
用户可以用INSERT语句将一行记录插入到指定的一个表中。例如，要将雇员JohnSmith的记录插入到本例的表中，可以使用如下语句：
INSERTINTOEMPLOYEESVALUES
('Smith','John','1980-06-10',
'LosAngles',16,45000);
通过这样的INSERT语句，系统将试着将这些值填入到相应的列中。这些列按照我们创建表时定义的顺序排列。在本例中，第一个值“Smith”将填到第一个列LAST_NAME中；第二个值“John”将填到第二列FIRST_NAME中……以此类推。
我们说过系统会“试着”将值填入，除了执行规则之外它还要进行类型检查。如果类型不符（如将一个字符串填入到类型为数字的列中），系统将拒绝这一次操作并返回一个错误信息。
如果SQL拒绝了你所填入的一列值，语句中其他各列的值也不会填入。这是因为SQL提供对事务的支持。一次事务将数据库从一种一致性转移到另一种一致性。如果事务的某一部分失败，则整个事务都会失败，系统将会被恢复（或称之为回退）到此事务之前的状态。
回到原来的INSERT的例子，请注意所有的整形十进制数都不需要用单引号引起来，而字符串和日期类型的值都要用单引号来区别。为了增加可读性而在数字间插入逗号将会引起错误。记住，在SQL中逗号是元素的分隔符。
同样要注意输入文字值时要使用单引号。双引号用来封装限界标识符。
对于日期类型，我们必须使用SQL标准日期格式（yyyy-mm-dd），但是在系统中可以进行定义，以接受其他的格式。当然，2000年临近，请你最好还是使用四位来表示年份。
既然你已经理解了INSERT语句是怎样工作的了，让我们转到EMPLOYEES表中的其他部分：
INSERTINTOEMPLOYEESVALUES
('Bunyan','Paul','1970-07-04',
'Boston',12,70000);
INSERTINTOEMPLOYEESVALUES
('John','Adams','1992-01-21',
'Boston',20,100000);
INSERTINTOEMPLOYEESVALUES
('Smith','Pocahontas','1976-04-06',
'LosAngles',12,100000);
INSERTINTOEMPLOYEESVALUES
('Smith','Bessie','1940-05-02',
'Boston',5,200000);
INSERTINTOEMPLOYEESVALUES
('Jones','Davy','1970-10-10',
'Boston',8,45000);
INSERTINTOEMPLOYEESVALUES
('Jo
nes','Indiana','1992-02-01',
'Chicago',NULL,NULL);
在最后一项中，我们不知道Jones先生的工薪级别和年薪，所以我们输入NULL（不要引号）。NULL是SQL中的一种特殊情况，我们以后将进行详细的讨论。现在我们只需认为NULL表示一种未知的值。
有时，像我们刚才所讨论的情况，我们可能希望对某一些而不是全部的列进行赋值。除了对要省略的列输入NULL外，还可以采用另外一种INSERT语句，如下：
INSERTINTOEMPLOYEES(
FIRST_NAME,LAST_NAME,
HIRE_DATE,BRANCH_OFFICE)
VALUE(
'Indiana','Jones',
'1992-02-01','Indianapolis');
这样，我们先在表名之后列出一系列列名。未列出的列中将自动填入缺省值，如果没有设置缺省值则填入NULL。请注意我们改变了列的顺序，而值的顺序要对应新的列的顺序。如果该语句中省略了FIRST_NAME和LAST_NAME项（这两项规定不能为空），SQL操作将失败。
让我们来看一看上述INSERT语句的语法图：
INSERTINTOtable
[(column{,column})]
VALUES
(columnvalue[{,columnvalue}]);
和前一篇文章中一样，我们用方括号来表示可选项，大括号表示可以重复任意次数的项（不能在实际的SQL语句中使用这些特殊字符）。VALUE子句和可选的列名列表中必须使用圆括号。
SELECT语句
SELECT语句可以从一个或多个表中选取特定的行和列。因为查询和检索数据是数据库管理中最重要的功能，所以SELECT语句在SQL中是工作量最大的部分。实际上，仅仅是访问数据库来分析数据并生成报表的人可以对其他SQL语句一窍不通。
SELECT语句的结果通常是生成另外一个表。在执行过程中系统根据用户的标准从数据库中选出匹配的行和列，并将结果放到临时的表中。在直接SQL（directSQL）中，它将结果显示在终端的显示屏上，或者将结果送到打印机或文件中。也可以结合其他SQL语句来将结果放到一个已知名称的表中。
SELECT语句功能强大。虽然表面上看来它只用来完成本文第一部分中提到的关系代数运算“选择”（或称“限制”），但实际上它也可以完成其他两种关系运算—“投影”和“连接”，SELECT语句还可以完成聚合计算并对数据进行排序。
SELECT语句最简单的语法如下：
SELECTcolumnsFROMtables;
当我们以这种形式执行一条SELECT语句时，系统返回由所选择的列以及用户选择的表中所有指定的行组成的一个结果表。这就是实现关系投影运算的一个形式。
让我们看一下使用图1中EMPLOYEES表的一些例子（这个表是我们以后所有SELECT语句实例都要使用的。而我们在图2和图3
中给出了查询的实际结果。我们将在其他的例子中使用这些结果）。
假设你想查看雇员工作部门的列表。那下面就是你所需要编写的SQL查询：
SELECTBRANCH_OFFICEFROMEMPLOYEES;
以上SELECT语句的执行将产生如图2中表2所示的结果。
由于我们在SELECT语句中只指定了一个列，所以我们的结果表中也只有一个列。注意结果表中具有重复的行，这是因为有多个雇员在同一部门工作（记住SQL从所选的所有行中将值返回）。要消除结果中的重复行，只要在SELECT语句中加上DISTINCT子句：
SELECTDISTINCTBRANCH_OFFICE
FROMEMPLOYEES;
这次查询的结果如表3所示。
现在已经消除了重复的行，但结果并不是按照顺序排列的。如果你希望以字母表顺序将结果列出又该怎么做呢？只要使用ORDERBY子句就可以按照升序或降序来排列结果：
SELECTDISTINCTBRANCH_OFFICE
FROMEMPLOYEES
ORDERBYBRANCH_OFFICEASC;
这一查询的结果如表4所示。请注意在ORDERBY之后是如何放置列名BRANCH_OFFICE的，这就是我们想要对其进行排序的列。为什么即使是结果表中只有一个列时我们也必须指出列名呢？这是因为我们还能够按照表中其他列进行排序，即使它们并不显示出来。列名BRANCH_OFFICE之后的关键字ASC表示按照升序排列。如果你希望以降序排列，那么可以用关键字DESC。
同样我们应该指出ORDERBY子句只将临时表中的结果进行排序；并不影响原来的表。
假设我们希望得到按部门排序并从工资最高的雇员到工资最低的雇员排列的列表。除了工资括号中的内容，我们还希望看到按照聘用时间从最近聘用的雇员开始列出的列表。以下是你将要用到的语句：
SELECTBRANCH_OFFICE,FIRST_NAME,
LAST_NAME,SALARY,HIRE_DATE
FROMEMPLOYEES
ORDERBYSALARYDESC,
HIRE_DATEDESC;
这里我们进行了多列的选择和排序。排序的优先级由语句中的列名顺序所决定。SQL将先对列出的第一个列进行排序。如果在第一个列中出现了重复的行时，这些行将被按照第二列进行排序，如果在第二列中又出现了重复的行时，这些行又将被按照第三列进行排序……如此类推。这次查询的结果如表5所示。
将一个很长的表中的所有列名写出来是一件相当麻烦的事，所以SQL允许在选择表中所有的列时使用*号：
SELECT*FROMEMPLOYEES;
这次查询返回整个EMPLOYEES表，如表1所示。
下面我们对开始时给出的SELECT语句的语法进行一下更新（竖直线表示一个可选项，允许在其中选择一项。）：
SELECT[DISTINCT]
(column[{,columns}])|*
FROMtable[{,table}]
[ORDERBYcolumn[ASC]|DESC
[{,column[ASC]|DESC}]];
定义选择标准
在我们目前所介绍的SELECT语句中，我们对结果表中的列作出了选择但返回的是表中所有的行。让我们看一下如何对SELECT语句进行限制使得它只返回希望得到的行：
SELECTcolumnsFROMtables[WHEREpredicates];
WHERE子句对条件进行了设置，只有满足条件的行才被包括到结果表中。这些条件由断言（predicate）进行指定（断言指出了关于某件事情的一种可能的事实）。如果该断言对于某个给定的行成立，该行将被包括到结果表中，否则该行被忽略。在SQL语句中断言通常通过比较来表示。例如，假如你需要查询所有姓为Jones的职员，则可以使用以下SELECT语句：
SELECT*FROMEMPLOYEES
WHERELAST_NAME='Jones';
LAST_NAME='Jones'部分就是断言。在执行该语句时，SQL将每一行的LAST_NAME列与“Jones”进行比较。如果某一职员的姓为“Jones”，即断言成立，该职员的信息将被包括到结果表中（见表6）。
使用最多的六种比较
我们上例中的断言包括一种基于“等值”的比较（LAST_NAME='Jones'），但是SQL断言还可以包含其他几种类型的比较。其中最常用的为：
等于=
不等于<>
小于<
大于>
小于或等于<=
大于或等于>=
下面给出了不是基于等值比较的一个例子：
SELECT*FROMEMPLOYEES
WHERESALARY>50000;
这一查询将返回年薪高于$50,000.00的职员（参见表7）。
逻辑连接符
有时我们需要定义一条不止一种断言的SELECT语句。举例来说，如果你仅仅想查看DavyJones的信息的话，表6中的结果将是不正确的。为了进一步定义一个WHERE子句，用户可以使用逻辑连接符AND，OR和NOT。为了只得到职员DavyJones的记录，用户可以输入如下语句：
SELECT*FROMEMPLOYEES
WHERELAST_NAME='Jones'ANDFIRST_NAME='Davy';
在本例中，我们通过逻辑连接符AND将两个断言连接起来。只有两个断言都满足时整个表达式才会满足。如果用户需要定义一个SELECT语句来使得当其中任何一项成立就满足条件时，可以使用OR连接符：
SELECT*FROMEMPLOYEES
WHERELAST_NAME='Jones'ORLAST_NAME='Smith';
有时定义一个断言的最好方法是通过相反的描述来说明。如果你想要查看除了Boston办事处的职员以外的其他所有职员的信息时，你可以进行如下的查询：
SELECT*FROMEMPLOYEES
WHERENOT(BRANCH_OFFICE='Boston');
关键字NOT后面跟着用圆括号括起来的比较表达式。其结果是对结果取否定。如
果某一职员所在部门的办事处在Boston，括号内的表达式返回true，但是NOT操作符将该值取反，所以该行将不被选中。
断言可以与其他的断言嵌套使用。为了保证它们以正确的顺序进行求值，可以用括号将它们括起来：
SELECT*FROMEMPLOYEES
WHERE(LAST_NAME='Jones'
ANDFIRST_NAME='Indiana')
OR(LAST_NAME='Smith'
ANDFIRST_NAME='Bessie');
SQL沿用数学上标准的表达式求值的约定—圆括号内的表达式将最先进行求值，其他表达式将从左到右进行求值。
以上对逻辑连接符进行了说明，在对下面的内容进行说明之前，我们再一次对SELECT语句的语法进行更新：
SELECT[DISTINCT]
(column[{,column}])|*
FROMtable[{,table}]
[ORDERBYcolumn[ASC]|[DESC
[{,column[ASC]|[DESC}]]
WHEREpredicate[{logical-connectorpredicate}];
NULL和三值逻辑
在SQL中NULL是一个复杂的话题，关于NULL的详细描述更适合于在SQL的高级教程而不是现在的入门教程中进行介绍。但由于NULL需要进行特殊处理，并且你也很可能会遇到它，所以我们还是简略地进行一下说明。
首先，在断言中进行NULL判断时需要特殊的语法。例如，如果用户需要显示所有年薪未知的职员的全部信息，用户可以使用如下SELECT语句：
SELECT*FROMEMPLOYEES
WHERESALARYISNULL;
相反，如果用户需要所有已知年薪数据的职员的信息，你可以使用以下语句：
SELECT*FROMEMPLOYEES
WHERESALARYISNOTNULL;
请注意我们在列名之后使用了关键字ISNULL或ISNOTNULL，而不是标准的比较形式：COLUMN=NULL、COLUMN<>NULL或是逻辑操作符NOT（NULL）。
这种形式相当简单。但当你不明确地测试NULL（而它们确实存在）时，事情会变得很混乱。
例如，回过头来看我们图1中的EM-PLOYEES表，可以看到IndianaJones的工薪等级或年薪值都是未知的。这两个列都包含NULL。可以想象运行如下的查询：
SELECT*FROMEMPLOYEES
WHEREGRADE<=SALARY;
此时，IndianaJones应该出现在结果表中。因为NULL都是相等的，所以可以想象它们是能够通过GRADE小于等于SALARY的检查的。这其实是一个毫无疑义的查询，但是并没有关系。SQL允许进行这样的比较，只要两个列都是数字类型的。然而，IndianaJones并没有出现在查询的结果中，为什么？
正如我们早先提到过的，NULL表示未知的值（而不是象某些人所想象的那样表示一个为NULL的值）。对于SQL来说意味着这个值是未知的，而只要这个值为未知，就不能将其与其他值比较（即使其他值也是NULL）。所
以SQL允许除了在true和false之外还有第三种类型的真值，称之为“非确定”（unknown）值。
如果比较的两边都是NULL，整个断言就被认为是非确定的。将一个非确定断言取反或使用AND或OR与其他断言进行合并之后，其结果仍是非确定的。由于结果表中只包括断言值为“真”的行，所以NULL不可能满足该检查。从而需要使用特殊的操作符ISNULL和ISNOTNULL。
UPDATE语句
UPDATE语句允许用户在已知的表中对现有的行进行修改。
例如，我们刚刚发现IndianaJones的等级为16，工资为$40,000.00，我们可以通过下面的SQL语句对数据库进行更新（并清除那些烦人的NULL）。
UPDATEEMPLOYEES
SETGRADE=16,SALARY=40000
WHEREFIRST_NAME='Indiana'
ANDLAST_NAME='Jones';
上面的例子说明了一个单行更新，但是UPDATE语句可以对多行进行操作。满足WHERE条件的所有行都将被更新。如果，你想让Boston办事处中的所有职员搬到NewYork，你可以使用如下语句：
UPDATEEMPLOYEES
SETBRANCH_OFFICE='NewYork'
WHEREBRANCH_OFFICE='Boston';
如果忽略WHERE子句,表中所有行中的部门值都将被更新为'NewYork'。
UPDATE语句的语法流图如下面所示：
UPDATEtable
SETcolumn=value[{,column=value}]
[WHEREpredicate[{logical-connectorpredicate}]];
DELETE语句
DELETE语句用来删除已知表中的行。如同UPDATE语句中一样，所有满足WHERE子句中条件的行都将被删除。由于SQL中没有UNDO语句或是“你确认删除吗？”之类的警告，在执行这条语句时千万要小心。如果决定取消LosAngeles办事处并解雇办事处的所有职员，这一卑鄙的工作可以由以下这条语句来实现：
DELETEFROMEMPL, OYEES
WHEREBRANCH_OFFICE='LosAngeles';
如同UPDATE语句中一样，省略WHERE子句将使得操作施加到表中所有的行。
DELETE语句的语法流图如下面所示：
DELETEFROMtable
[WHEREpredicate[{logical-connectorpredicate}]];
现在我们完成了数据操作语言（DML）的主要语句的介绍。我们并没有对SQL能完成的所有功能进行说明。SQL还提供了许多的功能，如求平均值、求和以及其他对表中数据的计算，此外SQL还能完成从多个表中进行查询（多表查询，或称之为连接）的工作。这种语言还允许你使用GRANT和REVOKE命令控制使用者的数据访问权限。
学习SQL语句之SQL语句大全
--语句功能
--数据操作
SELECT--从数据库表中检索数据行和列
INSERT--向数据库表添加新数据行
DELETE--从数据库表中删除数据行
UPDATE--更新数据库表中的数
据
--数据定义
CREATETABLE--创建一个数据库表
DROPTABLE--从数据库中删除表
ALTERTABLE--修改数据库表结构
CREATEVIEW--创建一个视图
DROPVIEW--从数据库中删除视图
CREATEINDEX--为数据库表创建一个索引
DROPINDEX--从数据库中删除索引
CREATEPROCEDURE--创建一个存储过程
DROPPROCEDURE--从数据库中删除存储过程
CREATETRIGGER--创建一个触发器
DROPTRIGGER--从数据库中删除触发器
CREATESCHEMA--向数据库添加一个新模式
DROPSCHEMA--从数据库中删除一个模式
CREATEDOMAIN--创建一个数据值域
ALTERDOMAIN--改变域定义
DROPDOMAIN--从数据库中删除一个域
--数据控制
GRANT--授予用户访问权限
DENY--拒绝用户访问
REVOKE--解除用户访问权限
--事务控制
COMMIT--结束当前事务
ROLLBACK--中止当前事务
SETTRANSACTION--定义当前事务数据访问特征
--程序化SQL
DECLARE--为查询设定游标
EXPLAN--为查询描述数据访问计划
OPEN--检索查询结果打开一个游标
FETCH--检索一行查询结果
CLOSE--关闭游标
PREPARE--为动态执行准备SQL语句
EXECUTE--动态地执行SQL语句
DESCRIBE--描述准备好的查询
---局部变量
declare@idchar(10)
--set@id='10010001'
select@id='10010001'
---全局变量
---必须以@@开头
--IFELSE
declare@xint@yint@zint
select@x=1@y=2@z=3
if@x>@y
print'x>y'--打印字符串'x>y'
elseif@y>@z
print'y>z'
elseprint'z>y'
--CASE
usepangu
updateemployee
sete_wage=
case
whenjob_level=’1’thene_wage*1.08
whenjob_level=’2’thene_wage*1.07
whenjob_level=’3’thene_wage*1.06
elsee_wage*1.05
end
--WHILECONTINUEBREAK
declare@xint@yint@cint
select@x=1@y=1
while@x<3
begin
print@x--打印变量x的值
while@y<3
begin
select@c=100*@x+@y
print@c--打印变量c的值
select@y=@y+1
end
select@x=@x+1
select@y=1
end
--WAITFOR
--例等待1小时2分零3秒后才执行SELECT语句
waitfordelay’01:02:03’
select*fromemployee
--例等到晚上11点零8分后才执行SELECT语句
waitfortime’23:08:00’
select*fromemployee
***SELECT***
select*(列名)fromtable_name(表名)wherecolumn_nameoperator
value
ex:(宿主)
select*fromstock_informationwherestockid=str(nid)
stockname='str_name'
stocknamelike'%findthis%'
stocknamelike'[a-zA-Z]%'---------([]指定值的范围)
stocknamelike'[^F-M]%'---------(^排除指定范围)
---------只能在使用like关键字的where子句中使用通配符)
orstockpath='stock_path'
orstocknumber<1000
andstockindex=24
notstock***='man'
stocknumberbetween20and100
stocknumberin(10,20,30)
orderbystockiddesc(asc)---------排序，desc-降序，asc-升序
orderby1,2---------by列号
stockname=(selectstocknamefromstock_informationwhere
stockid=4)
---------子查询
---------除非能确保内层select只返回一个行的值，
---------否则应在外层where子句中用一个in限定符
selectdistinctcolumn_nameformtable_name---------
distinct指定检索独有的列值，不重复
selectstocknumber,"stocknumber+10"=stocknumber+10from
table_name
selectstockname,"stocknumber"=count(*)fromtable_name
groupbystockname
---------groupby将表按行分组,指定列中有相同的值
havingcount(*)=2---------having选定指定的组
select*
fromtable1,table2
wheretable1.id*=table2.id--------
左外部连接，table1中有的而table2中没有得以null表示
table1.id=*table2.id--------右外部连接
selectstocknamefromtable1
union[all]-----union合并查询结果集，all-保留重复行
selectstocknamefromtable2
***insert***
insertintotable_name(Stock_name,Stock_number)value
("xxx","xxxx")
value(selectStockname,Stocknumberfrom
Stock_table2)---value为select语句
***update***
updatetable_namesetStockname="xxx"[whereStockid=3]
Stockname=default
Stockname=null
Stocknumber=Stockname+4
***delete***
deletefromtable_namewhereStockid=3
truncatetable_name-----------删除表中所有行，仍保持表的完整性
droptabletable_name---------------完全删除表
***altertable***---修改数据库表结构
altertabledatabase.owner.table_nameaddcolumn_namechar(2)
null.....
sp_helptable_name----显示表已有特征
createtabletable_name(namechar(20),agesmallint,lname
varchar(30))
insertintotable_nameselect.........-----实现删除列的方法（创建新表）
altertabletable_namedropconstraintStockname_default----
删除Stockname的default约束
***function(/*常用函数*/)***
----统计函数----
AVG--求平均值
COUNT--统计数目
MAX--求最大值
MIN--求最小值
SUM--求和
--AVG
usepangu
selectavg(e_wage)asdept_avgWage
fromempl
oyee
groupbydept_id
--MAX
--求工资最高的员工
usepangu
selecte_name
fromemployee
wheree_wage=
(selectmax(e_wage)
fromemployee)
--STDEV()
--STDEV()函数返回表达式中所有数据的标准差
--STDEVP()
--STDEVP()函数返回总体标准差
--VAR()
--VAR()函数返回表达式中所有值的统计变异数
--VARP()
--VARP()函数返回总体变异数
----算术函数----
/***三角函数***/
SIN(float_expression)--返回以弧度表示的角的正弦
COS(float_expression)--返回以弧度表示的角的余弦
TAN(float_expression)--返回以弧度表示的角的正切
COT(float_expression)--返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression)--返回正弦是FLOAT值的以弧度表示的角
ACOS(float_expression)--返回余弦是FLOAT值的以弧度表示的角
ATAN(float_expression)--返回正切是FLOAT值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1/float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT类型
RADIANS(numeric_expression)--把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT类型
EXP(float_expression)--返回表达式的指数值
LOG(float_expression)--返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10为底的对数值
SQRT(float_expression)--返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression)--返回>=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT类型
FLOOR(numeric_expression)--返回<=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT类型
ROUND(numeric_expression)--返回以integer_expression
为精度的四舍五入值返回的数据
--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT类型
ABS(numeric_expression)--返回表达式的绝对值返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT类型
SIGN(numeric_expression)--测试参数的正负号返回0零值1正数或-1负数返回的数据类型
--与表达式相同可为INTEGER/MONEY/REAL/FLOAT类型
PI()--返回值为π即3.1415926535897936
RAND([integer_expression])--用任选的[integer_expression]做种子值得出0-1
间的随机浮点数
----字符串函数----
ASCII()--函数返回字符表达式最左端字符的ASCII码值
CHAR()--函数用于将ASCII码转换为字
符
--如果没有输入0~255之间的ASCII码值CHAR函数会返回一个NULL值
LOWER()--函数把字符串全部转换为小写
UPPER()--函数把字符串全部转换为大写
STR()--函数把数值型数据转换为字符型数据
LTRIM()--函数把字符串头部的空格去掉
RTRIM()--函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING()--函数返回部分字符串
CHARINDEX(),PATINDEX()--函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX()--函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0值
DIFFERENCE()--函数返回由SOUNDEX函数返回的两个字符表达式的值的差异
--0两个SOUNDEX函数返回值的第一个字符不同
--1两个SOUNDEX函数返回值的第一个字符相同
--2两个SOUNDEX函数返回值的第一二个字符相同
--3两个SOUNDEX函数返回值的第一二三个字符相同
--4两个SOUNDEX函数返回值完全相同
QUOTENAME()--函数返回被特定字符括起来的字符串
/*selectquotename('abc','{')quotename('abc')
运行结果如下
----------------------------------{
{abc}[abc]*/
REPLICATE()--函数返回一个重复character_expression指定次数的字符串
/*selectreplicate('abc',3)replicate('abc',-2)
运行结果如下
----------------------
abcabcabcNULL*/
REVERSE()--函数将指定的字符串的字符排列顺序颠倒
REPLACE()--函数返回被替换了指定子串的字符串
/*selectreplace('abc123g','123','def')
运行结果如下
----------------------
abcdefg*/
SPACE()--函数返回一个有指定长度的空白字符串
STUFF()--函数用另一子串替换字符串指定位置长度的子串
----数据类型转换函数----
CAST()函数语法如下
CAST()(
AS[length])
CONVERT()函数语法如下
CONVERT()([length],[,style])
selectcast(100+99aschar)convert(varchar(12),getdate())
运行结果如下
------------------------------------------
199Jan152000
----日期函数----
DAY()--函数返回date_expression中的日期值
MONTH()--函数返回date_expression中的月份值
YEAR()--函数返回date_expression中的年份值
DATEADD(,,)
--函数返回指定日期date加上指定的额外日期间隔number产生的新日期
DATEDIFF(,,)
--函数返回两个指定日期在datepart方面的不同之处
DATENAME(,)--函数以字符串的形式返回日期的指定部分
DATEPART(
,)--函数以整数值的形式返回日期的指定部分
GETDATE()--函数以DATETIME的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME()--函数返回当前执行的应用程序的名称
COALESCE()--函数返回众多表达式中第一个非NULL表达式的值
COL_LENGTH(<'table_name'>,<'column_name'>)--函数返回表中指定字段的长度值
COL_NAME(,)--函数返回表中指定字段的名称即列名
DATALENGTH()--函数返回数据表达式的数据的实际长度
DB_ID(['database_name'])--函数返回数据库的编号
DB_NAME(database_id)--函数返回数据库的名称
HOST_ID()--函数返回服务器端计算机的名称
HOST_NAME()--函数返回服务器端计算机的名称
IDENTITY([,seedincrement])[AScolumn_name])
--IDENTITY()函数只在SELECTINTO语句中使用用于插入一个identitycolumn列到新表中
/*selectidentity(int,1,1)ascolumn_name
intonewtable
fromoldtable*/
ISDATE()--函数判断所给定的表达式是否为合理日期
ISNULL(,)--函数将表达式中的NULL
值用指定值替换
ISNUMERIC()--函数判断所给定的表达式是否为合理的数值
NEWID()--函数返回一个UNIQUEIDENTIFIER类型的数值
NULLIF(,)
--NULLIF函数在expression1与expression2相等时返回NULL
值若不相等时则返回expression1的值
sql中的保留字
actionaddaggregateall
alterafterandas
ascavgavg_row_lengthauto_increment
betweenbigintbitbinary
blobboolbothby
cascadecasecharcharacter
changecheckchecksumcolumn
columnscommentconstraintcreate
crosscurrent_datecurrent_timecurrent_timestamp
datadatabasedatabasesdate
datetimedayday_hourday_minute
day_seconddayofmonthdayofweekdayofyear
decdecimaldefaultdelayed
delay_key_writedeletedescdescribe
distinctdistinctrowdoubledrop
endelseescapeescaped
enclosedenumexplainexists
fieldsfilefirstfloat
float4float8flushforeign
fromforfullfunction
globalgrantgrantsgroup
havingheaphigh_priorityhour
hour_minutehour_secondhostsidentified
ignoreinindexinfile
innerinsertinsert_idint
integerintervalint1int2
int3int4int8into
ifisisamjoin
keykeyskilllast_insert_id
leadingleftlengthlike
lineslimitloadlocal
locklogslonglongblob
longtextlow_prioritymaxmax_rows
matchmediumblobmediumtextmediumint
middleintmin_rowsminuteminute_second
modifymonthmonthnamemyisam
naturalnumericnonot
nullonoptimizeoption
optionallyororderouter
outfilepack_keyspartialpassword
precisionprimaryprocedureprocess
processlistprivilegesreadreal
referencesreloadregexprename
replacerestrictreturnsrevoke
rlikerowrowssecond
selectsetshowshutdown
smallintsonamesql_big_tablessql_big_selects
sql_low_priority_updatessql_log_offsql_log_update
sql_select_limit
sql_small_resultsql_big_resultsql_warningsstraight_join
startingstatusstringtable
tablestemporaryterminatedtext
thentimetimestamptinyblob
tinytexttinyinttrailingto
typeuseusingunique
unlockunsignedupdateusage
valuesvarcharvariablesvarying
varbinarywithwritewhen
whereyearyear_monthzerofill
--WAITFOR
--例等待1小时2分零3秒后才执行SELECT语句
waitfordelay’01:02:03’
select*fromemployee
--例等到晚上11点零8分后才执行SELECT语句
waitfortime’23:08:00’
select*fromemployee
***SELECT***
select*(列名)fromtable_name(表名)wherecolumn_nameoperator
value
ex:(宿主)
select*fromstock_informationwherestockid=str(nid)
stockname='str_name'
stocknamelike'%findthis%'
stocknamelike'[a-zA-Z]%'---------([]指定值的范围)
stocknamelike'[^F-M]%'---------(^排除指定范围)
---------只能在使用like关键字的where子句中使用通配符)
orstockpath='stock_path'
orstocknumber<1000
andstockindex=24
notstock***='man'
stocknumberbetween20and100
stocknumberin(10,20,30)
orderbystockiddesc(asc)---------排序，desc-降序，asc-升序
orderby1,2---------by列号
stockname=(selectstocknamefromstock_informationwhere
stockid=4)
---------子查询
---------除非能确保内层select只返回一个行的值，
---------否则应在外层where子句中用一个in限定符
selectdistinctcolumn_nameformtable_name---------
distinct指定检索独有的列值，不重复
selectstocknumber,"stocknumber+10"=stocknumber+10from
table_name
selectstockname,"stocknumber"=count(*)fromtable_name
groupbystockname
---------groupby将表按行分组,指定列中有相同的值
havingcount(*)=2---------having选定指定的组
select*
fromtable1,table2
wheretable1.id*=table2.id--------
左外部连接，table1中有的而table2中没有得以null表示
table1.id=*table2.id--------右外部连接
selectstocknamefromtable1
union[all]-----union合并查询结果集，all-保留重复行
selectstocknamefromtable2
***insert***
insertintotable_name(Stock_name,Stock_number)value
("xxx","xxxx")
value(selectStockname,Stocknumberfrom
Stock_table2)---value为select语句
***update***
updatetable_namesetStockname="xxx"[whereStockid=3]
Stockname=default
Stockname=null
Stocknumber=Stockname+4
***delete***
deletefromtable_namewhereStockid=3
truncatetable_name-----------删除表中所有行，仍保持表的完整性
droptabletable_name---------------完全删除表
***altertable***---修改数据库表结构
altertabledatabase.owner.table_nameaddcolumn_namechar(2)
null.....
sp_helptable_name----显示表已有特征
createtabletable_name(namechar(20),agesmallint,lname
varchar(30))
insertintotable_nameselect.........-----实现删除列的方法（创建新表）
altertabletable_namedropconstraintStockname_default----
删除Stockname的default约束
***function(/*常用函数*/)***
----统计函数----
AVG--求平均值
COUNT--统计数目
MAX--求最大值
MIN--求最小值
SUM--求和
--AVG
usepangu
selectavg(e_wage)asdept_avgWage
fromemployee
groupbydept_id
--MAX
--求工资最高的员工
usepangu
selecte_name
fromemployee
wheree_wage=
(selectmax(e_wage)
fromemployee)
--STDEV()
--STDEV()函数返回表达式中所有数据的标准差
--STDEVP()
--STDEVP()函数返回总体标准差
--VAR()
--VAR()函数返回表达式中所有值的统计变异数
--VARP()
--VARP()函数返回总体变异数
----算术函数----
/***三角函数***/
SIN(float_expression)--返回以弧度表示的角的正弦
COS(float_expression)--返回以弧度表示的角的余弦
TAN(float_expression)--返回以弧度表示的角的正切
COT(float_expression)--返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression)--返回正弦是FLOAT值的以弧度表示的角
ACOS(float_expression)--返回余弦是FLOAT值的以弧度表示的角
ATAN(float_expression)--返回正切是FLOAT值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1/float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT类型
RADIANS(numeric_expression)--把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT类型
EXP(float_expression)--返
回表达式的指数值
LOG(float_expression)--返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10为底的对数值
SQRT(float_expression)--返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression)--返回>=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT类型
FLOOR(numeric_expression)--返回<=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT类型
ROUND(numeric_expression)--返回以integer_expression
为精度的四舍五入值返回的数据
--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT类型
ABS(numeric_expression)--返回表达式的绝对值返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT类型
SIGN(numeric_expression)--测试参数的正负号返回0零值1正数或-1负数返回的数据类型
--与表达式相同可为INTEGER/MONEY/REAL/FLOAT类型
PI()--返回值为π即3.1415926535897936
RAND([integer_expression])--用任选的[integer_expression]做种子值得出0-1
间的随机浮点数
----字符串函数----
ASCII()--函数返回字符表达式最左端字符的ASCII码值
CHAR()--函数用于将ASCII码转换为字符
--如果没有输入0~255之间的ASCII码值CHAR函数会返回一个NULL值
LOWER()--函数把字符串全部转换为小写
UPPER()--函数把字符串全部转换为大写
STR()--函数把数值型数据转换为字符型数据
LTRIM()--函数把字符串头部的空格去掉
RTRIM()--函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING()--函数返回部分字符串
CHARINDEX(),PATINDEX()--函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX()--函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0值
DIFFERENCE()--函数返回由SOUNDEX函数返回的两个字符表达式的值的差异
--0两个SOUNDEX函数返回值的第一个字符不同
--1两个SOUNDEX函数返回值的第一个字符相同
--2两个SOUNDEX函数返回值的第一二个字符相同
--3两个SOUNDEX函数返回值的第一二三个字符相同
--4两个SOUNDEX函数返回值完全相同
QUOTENAME()--函数返回被特定字符括起来的字符串
/*selectquotename('abc','{')quotename('abc')
运行结果如下
----------------------------------{
{abc}[abc]*/
REPLICATE()--函数返回一个重复character_expression指定次数的字符串
/*selectreplicate('abc',3)replicate('abc',-2)
运行结果如下
----------------------
abcabcabcNULL*/
REVERSE()--函数将指定的字符串的字符排列顺序颠倒
REPLACE()--函数返回被替换了指定子串的字符串
/*selectreplace('abc123g','123','def')
运行结果如下
----------------------
abcdefg*/
SPACE()--函数返回一个有指定长度的空白字符串
STUFF()--函数用另一子串替换字符串指定位置长度的子串
----数据类型转换函数----
CAST()函数语法如下
CAST()(AS[length])
CONVERT()函数语法如下
CONVERT()([length],[,style])
selectcast(100+99aschar)convert(varchar(12),getdate())
运行结果如下
------------------------------------------
199Jan152000
----日期函数----
DAY()--函数返回date_expression中的日期值
MONTH()--函数返回date_expression中的月份值
YEAR()--函数返回date_expression中的年份值
DATEADD(,,)
--函数返回指定日期date加上指定的额外日期间隔number产生的新日期
DATEDIFF(,,)
--函数返回两个指定日期在datepart方面的不同之处
DATENAME(,)--函数以字符串的形式返回日期的指定部分
DATEPART(,)--函数以整数值的形式返回日期的指定部分
GETDATE()--函数以DATETIME的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME()--函数返回当前执行的应用程序的名称
COALESCE()--函数返回众多表达式中第一个非NULL表达式的值
COL_LENGTH(<'table_name'>,<'column_name'>)--函数返回表中指定字段的长度值
COL_NAME(,)--函数返回表中指定字段的名称即列名
DATALENGTH()--函数返回数据表达式的数据的实际长度
DB_ID(['database_name'])--函数返回数据库的编号
DB_NAME(database_id)--函数返回数据库的名称
HOST_ID()--函数返回服务器端计算机的名称
HOST_NAME()--函数返回服务器端计算机的名称
IDENTITY([,seedincrement])[AScolumn_name])
--IDENTITY()函数只在SELECTINTO语句中使用用于插入一个identitycolumn列到新表中
/*selectidentity(int,1,1)ascolumn_name
intonewtable
fromoldtable*/
ISDATE()--函数判断所给定的表达式是否为合理日期
ISNULL(,)--函数将表达式中的NULL
值用指定值替换
ISNUMERIC()--函数判断所给定的表达式是否为合理的数值
NEWID()--函数返回一个UNIQUEIDENTIFIER类型的数值
NULLIF(,)
--NULLIF函数在expression1与expression2相等时返回NULL
值若
不相等时则返回expression1的值
sql中的保留字
actionaddaggregateall
alterafterandas
ascavgavg_row_lengthauto_increment
betweenbigintbitbinary
blobboolbothby
cascadecasecharcharacter
changecheckchecksumcolumn
columnscommentconstraintcreate
crosscurrent_datecurrent_timecurrent_timestamp
datadatabasedatabasesdate
datetimedayday_hourday_minute
day_seconddayofmonthdayofweekdayofyear
decdecimaldefaultdelayed
delay_key_writedeletedescdescribe
distinctdistinctrowdoubledrop
endelseescapeescaped
enclosedenumexplainexists
fieldsfilefirstfloat
float4float8flushforeign
fromforfullfunction
globalgrantgrantsgroup
havingheaphigh_priorityhour
hour_minutehour_secondhostsidentified
ignoreinindexinfile
innerinsertinsert_idint
integerintervalint1int2
int3int4int8into
ifisisamjoin
keykeyskilllast_insert_id
leadingleftlengthlike
lineslimitloadlocal
locklogslonglongblob
longtextlow_prioritymaxmax_rows
matchmediumblobmediumtextmediumint
middleintmin_rowsminuteminute_second
modifymonthmonthnamemyisam
naturalnumericnonot
nullonoptimizeoption
optionallyororderouter
outfilepack_keyspartialpassword
precisionprimaryprocedureprocess
processlistprivilegesreadreal
referencesreloadregexprename
replacerestrictreturnsrevoke
rlikerowrowssecond
selectsetshowshutdown
smallintsonamesql_big_tablessql_big_selects
sql_low_priority_updatessql_log_offsql_log_update
sql_select_limit
sql_small_resultsql_big_resultsql_warningsstraight_join
startingstatusstringtable
tablestemporaryterminatedtext
thentimetimestamptinyblob
tinytexttinyinttrailingto
typeuseusingunique
unlockunsignedupdateusage
valuesvarcharvariablesvarying
varbinarywithwritewhen
whereyearyear_monthzerofill
--简单查询与具体的表无关的公用信息
select1+2;
selectnow();
--从表中查询所有记录的某些字段的信息
select*frompet;
selectname,owner,sexfrompet;
--可以对字段进行数学运算
selectid*12frompet;
--使用distinct合并掉重复记录
selectdistinctspecies,sexfrompet;
--只查询满足条件的某些记录
select*frompetwherespecies='dog';
select*frompetwhereid<>5;
--多个查询条件可以用and或者or连接起来
select*
frompet
wherespecies='dog'orsex='m';
--and的优先级比or高
select*
frompet
wheresex='m'and
(species='dog'orspec
ies='cat');
--查询Gwen和Harold养的雄性宠物
--查询所有的猫和Benny养的狗
--某个字段的值在某个连续的范围内：between..and..
select*frompetwherebirth>='1990'andbirth<='2000';
select*frompetwherebirthnotbetween'1990'and'2000';
--某个字段的值在某个离散的集合内:in(..)
select*frompetwherespecies='dog'orspecies='cat'
orspecies='bird';
select*frompetwherespeciesnotin('dog','cat','bird');
--使用通配符查询。_表示任意一个字符，%表示任意个字符。like
select*frompetwherebirthlike'____-08%';
select*frompetwherenamenotlike'B%';
--查询null必须使用is来描述
select*frompetwheresexisnull;
select*frompetwheredeathisnotnull;
--查询90年代8月份出生的还没有死亡的狗和猫
--数据库内置函数，输入某个字段的名字，该字段的每条记录都会被处理
selectleft('asdf',2);
--字符串的长度
selectname,length(name)frompet;
--字符串的合并
selectconcat(name,'is',owner,'\'s',species)frompet;
--字符串的截取
selectname,left(name,3),right(name,3),substr(name,2,2)frompet;
--字符串转换大小写、倒序
selectname,lower(name),upper(name),reverse(name)frompet;
--查询8月份出生的、名字以B开头的宠物
--上取整、四舍五入取整、下取整
selectceil(1.23),round(1.23),floor(1.23);
--0到1间的随机小数
selectrand()frompet;
--求100-200间的随机整数
selectround(rand()*100)+100;
--日期可以自动转换为字符串或者数字进行相应的处理
selectbirth,birth+1frompet;
--得到当前的时间
selectnow(),curdate(),curtime();
--求两个日期间的天数差
selectdatediff('1999-01-01','1990-01-01');
--得到某个日期是星期几
selectdayofweek('2010-06-22'),dayname('2010-06-22');
--查询周二出生的狗
select*frompetwherespecies='dog'anddayofweek(birth)=3;
--查询各宠物目前的年龄
selectbirth,curdate(),year(birth),year(curdate()),
year(curdate())-year(birth),
floor(datediff(curdate(),birth)/365)
frompet;
--单行函数可以作为查询条件
select*frompetwheremonth(birth)=8;
--多行函数，主要进行数学统计
selectmax(birth),min(birth),avg(id),sum(id)frompet;
--查询记录数，跟具体的字段值无关
selectcount(*)frompet;
--按照种类、性别进行分组，统计每个组的记录数
selectspecies,sex,count(*)
frompet
groupbyspecies,sex;
--可以对字段进行函数处理后再进行分组
selectdayofweek(birth),count(*)
frompet
groupbydayofweek(birth);
--groupby语句在where语句的后面
selectsex,count(*)
frompet
wheredeathisnullandsexisnotnull
groupbysex;
--分组函数的结果作为查询条件，不能写在where后，而要写在h
aving后
--给字段起别名，别名不能在where里使用，可以在groupby后面使用
selectownerown,count(*)cou
frompet
whereownerin('gwen','harold')
groupbyownhavingcou>=2;
--查询90年代出生的宠物，按类别分组，数量大于2的宠物及其数量
selectspecies,count(*)c
frompet
whereyear(birth)between1990and2000
groupbyspecieshavingc>=2;
--查询的结果按照主人名字的倒序排列显示
select*frompet
orderbyownerdesc;
--先按种类倒序排列，种类相同时，再按性别排列。。
select*frompet
orderbyspeciesdesc,sex,birthdesc;
--未死的宠物，按照主人进行分组，查询最早的生日，按照生日倒序结果
selectowner,min(birth)mb
frompet
wheredeathisnull
groupbyowner
orderbymbdesc;
链接多个表进行查询
selectpet.name,pet.birth,event.`date`,event.`type`
frompet,event
wherepet.name=event.name;
selectpet.name,pet.birth,event.`date`,event.`type`
frompet,event
wherepet.name=event.nameandpet.id<5;
selectpet.name,pet.birth,event.`date`,event.`type`
frompetjoineventonpet.name=event.name
wherepet.id<5;
selectpet.owner,event.name,event.`type`,count(*)
frompet,event
wherepet.name=event.namegroupbyevent.namehavingcount(*)>1;
selectp.name,e.`type`
frompetpleftjoineventeonp.name=e.name;以那个表为主
select*frompet
wherebirth=(selectmin(birth)frompet);有关子查询
insertintopet(name,owner,sex)values('bb','cccc','m');
updatepetsetname='aaaaa',owner='vvvvvvvv'whereid='9'
updatepetsetname=concat(name,'aaa');在所有的字段后面加上aaa
truncatepet;删除表只是删除表的指针可以通过硬盘进行恢复
--从头开始取几条
select*frompetlimit3;
--限制从第几条记录开始取几条记录
select*frompetlimit0,3;
--查询年龄最大的三个宠物
select*frompetorderbybirthlimit3;
--多表关联查询，一定要写N-1个关联条件
selectp.name,owner,species,`type`,`date`
frompetp,evente
wherep.name=e.nameandp.id<5;
--SQL99标准，将关联条件从where中提取出去
selectp.name,p.id,e.type
frompetpjoineventeonp.name=e.name
wherep.id<5;
--查询gwen养的宠物都发生了哪些事件
selectp.name,date,type,remark
frompetp,evente
wherep.name=e.nameandowner='gwen';
--查询哪些人养的宠物生产了小宠物
selectp.name,date,type,remark
frompetp,evente
wherep.name=e.nameandtype='litter';
--查询发生了多件事件的宠物的信息
selectp.id,p.name,p.species,count(*)c
frompetp,evente
wherep.name=e.name
groupbye.namehavingc>1；
selectsex,count(*)c
frompetp,evente
wherep.name=e.name
andyear(birth)between1990and1999
groupbysex
orderbyc;
--左(右)关联：以左边的表为主，该表所有记录都
要显示出来，如果右侧的表中没有与左表某一记录相对应的记录，就显示null
selectp.name,e.`type`
frompetpleft(right)joinevente
onp.name=e.name;
--子查询的结果作为限定条件的值来使用。要求只能返回一行一列的查询结果
select*
frompet
wherebirth=(selectmin(birth)frompet);
--子查询的结果作为一个临时表来用。一定要给该子查询起别名
select*
from( select*
frompet
whereyear(birth)between1990and1999
)p,evente
wherep.name=e.name;
--查询饲养宠物最多的人，所养的所有的宠物信息
select*
frompet
whereowner=( selectowner
frompet
groupbyowner
orderbycount(*)desc
limit1
);
--过过生日的宠物，过的是几岁生日
selectp.name,p.birth,e.date,year(date)-year(birth)age
frompetp,evente
wherep.name=e.nameande.type='birthday'
--哪种类别的宠物发生的事件最多？
selectspecies,count(*)c
frompetp,evente
wherep.name=e.name
groupbyspecies
orderbycdesc
limit1
--发生事件最多的类别的宠物，是由几个人饲养的？
selectcount(distinctowner)
frompet
wherespecies='dog';
--哪个人养的宠物发生的事件最多？
selectowner,count(*)c
frompetp,evente
wherep.name=e.name
groupbyowner
orderbycdesc
limit1;
--狗和猫分别发生的哪种类型的事件最多？
(
select*from
(
selectspecies,`type`,count(*)c
frompetp,evente
wherep.name=e.name
groupbyspecies,`type`
orderbycdesc
)p
wherespecies='dog'limit1
)
union
(
select*from
(
selectspecies,`type`,count(*)c
frompetp,evente
wherep.name=e.name
groupbyspecies,`type`
orderbycdesc
)p
wherespecies='cat'limit1
);
--插入
insertintopet(id,name,owner,species,sex,birth,death)
values(null,'a','b','dog','m',curdate(),null);
--按照表中字段的默认顺序输入了所有的值，就可以不写字段名称
insertintopetvalues(null,'a','b','dog','m',curdate(),null);
--只插入某些字段的值，其他字段设为null
insertintopet(name)values('aaa');
--一个SQL语句批量插入多条记录
insertintopetvalues
(null,'a','b','dog','m',curdate(),null),
(null,'a','b','dog','m',curdate(),null);
--使用子查询批量插入多条记录
insertintopet(name,owner,species,sex)
(selectname,owner,species,sexfrompet);
--更新一条记录
updatepetsetname='aa',owner='bb'whereid='9';
--更新多条记录
updatepetsetdeath=curdate()wheredeathisnull;
--在原有值的基础上进行修改，可以通过字段名引用原先的值
updatepetsetname=concat(name,'a');
--删除
deletefrompetwhereid>10;
--清空表
truncatepet;
注意:数据库版本是10g,不过大部分9i的也适用,闪回9i就没有.
1.曾经不小心
把开发库的数据库表全部删除，当时吓的要死。结果找到下面的语句恢复到了1个小时之前的数据！很简单。
注意使用管理员登录系统：
select*from表名asoftimestampsysdate-1/12//查询两个小时前的某表数据！既然两小时以前的数据都得到了，继续怎么做，知道了吧。。
如果drop了表，怎么办？？见下面：
droptable表名;
数据库误删除表之后恢复:（绝对ok，我就做过这样的事情，汗）不过要记得删除了哪些表名。
flashbacktable表名tobeforedrop;
2.查询得到当前数据库中锁，以及解锁：
查锁
SELECT/*+rule*/s.username,
decode(l.type,'TM','TABLELOCK',
'TX','ROWLOCK',
NULL)LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROMv$sessions,v$lockl,dba_objectso
WHEREl.sid=s.sid
ANDl.id1=o.object_id(+)
ANDs.usernameisNOTNULL;
解锁
altersystemkillsession'sid,serial';
如果解不了。直接倒os下kill进程kill－9spid
ORA-28000:账户被锁定
因为密码输入错误多次用户自动被锁定.
解决办法:alteruseruser_nameaccountunlock;
3.关于查询数据库用户，权限的相关语句：
Sql代码
1.查看所有用户：
select*fromdba_user;
select*fromall_users;
select*fromuser_users;
2.查看用户系统权限：
select*fromdba_sys_privs;
select*fromall_sys_privs;
select*fromuser_sys_privs;
&n, bsp;
3.查看用户对象权限：
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
4.查看所有角色：
select*fromdba_roles;
5.查看用户所拥有的角色：
select*fromdba_role_privs;
select*fromuser_role_privs;
1.查看所有用户：
select*fromdba_user;
select*fromall_users;
select*fromuser_users;
2.查看用户系统权限：
select*fromdba_sys_privs;
select*fromall_sys_privs;
select*fromuser_sys_privs;
3.查看用户对象权限：
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
4.查看所有角色：
select*fromdba_roles;
5.查看用户所拥有的角色：
select*fromdba_role_privs;
select*fromuser_role_privs;
4.几个经常用到的oracle视图：注意表名使用大写....................
Sql代码
1.查询oracle中所有用户信息
select*fromdba_user;
2.只查询用户和密码
selectusername,passwordfromdba_users;
3.查询当前用户信息
select*fromdba_ustats;
4.查询用户可以访问的视图文本
select*fromdba_varrays;
5.查询数据库中所有视图的文本
select*fromdba_views;
6.查询全部索引
select*fromuser_indexes;
查询全部表格
select*fromuser_tables;
查询全部约束
select*fromuser_constraints;
查询全部对象
select*fromuser_objects;
1.查询oracle中所有用户信息
select*fromdba_user;
2.只查询用户和密码
selectusername,passwordfromdba_users;
3.查询当前用户信息
select*fromdba_ustats;
4.查询用户可以访问的视图文本
select*fromdba_varrays;
5.查询数据库中所有视图的文本
select*fromdba_views;
6.查询全部索引
select*fromuser_indexes;
查询全部表格
select*fromuser_tables;
查询全部约束
select*fromuser_constraints;
查询全部对象
select*fromuser_objects;
5.查看当前数据库中正在执行的语句，然后可以继续做很多很多事情，例如查询执行计划等等
Sql代码
(1).查看相关进程在数据库中的会话
Selecta.sid,a.serial#,a.program,a.status,
substr(a.machine,1,20),a.terminal,b.spid
fromv$sessiona,v$processb
wherea.paddr=b.addr
andb.spid=&;spid;
(2).查看数据库中被锁住的对象和相关会话
selecta.sid,a.serial#,a.username,a.program,
c.owner,c.object_name
fromv$sessiona,v$locked_objectb,all_objectsc
wherea.sid=b.session_idand
c.object_id=b.object_id;
(3).查看相关会话正在执行的SQL
selectsql_textfromv$sqlareawhereaddress=
(selectsql_addressfromv$sessionwheresid=&;sid);
(1).查看相关进程在数据库中的会话
Selecta.sid,a.serial#,a.program,a.status,
substr(a.machine,1,20),a.terminal,b.spid
fromv$sessiona,v$processb
wherea.paddr=b.addr
andb.spid=&;spid;
(2).查看数据库中被锁住的对象和相关会话
selecta.sid,a.serial#,a.username,a.program,
c.owner,c.object_name
fromv$sessiona,v$locked_objectb,all_objectsc
wherea.sid=b.session_idand
c.object_id=b.object_id;
(3).查看相关会话正在执行的SQL
selectsql_textfromv$sqlareawhereaddress=
(selectsql_addressfromv$sessionwheresid=&;sid);
6.查询表的结构：表名大写！！
selectt.COLUMN_NAME,
t.DATA_TYPE,
nvl(t.DATA_PRECISION,t.DATA_LENGTH),
nvl(T.DATA_SCALE,0),
cments
fromall_tab_columnst,user_col_commentsc
whEret.TABLE_NAME=c.table_name
andt.COLUMN_NAME=c.column_name
andt.TABLE_NAME=UPPER('OM_EMPLOYEE_T')
orderbyt.COLUMN_ID
7.行列互换：
Sql代码
建立一个例子表：
CR
EATETABLEt_col_row(
IDINT,
c1VARCHAR2(10),
c2VARCHAR2(10),
c3VARCHAR2(10));
INSERTINTOt_col_rowVALUES(1,'v11','v21','v31');
INSERTINTOt_col_rowVALUES(2,'v12','v22',NULL);
INSERTINTOt_col_rowVALUES(3,'v13',NULL,'v33');
INSERTINTOt_col_rowVALUES(4,NULL,'v24','v34');
INSERTINTOt_col_rowVALUES(5,'v15',NULL,NULL);
INSERTINTOt_col_rowVALUES(6,NULL,NULL,'v35');
INSERTINTOt_col_rowVALUES(7,NULL,NULL,NULL);
COMMIT;
下面的是列转行:创建了一个视图
CREATEviewv_row_colAS
SELECTid,'c1'cn,c1cv
FROMt_col_row
UNIONALL
SELECTid,'c2'cn,c2cv
FROMt_col_row
UNIONALL
SELECTid,'c3'cn,c3cvFROMt_col_row;
下面是创建了没有空值的一个竖表:
CREATEviewv_row_col_notnullAS
SELECTid,'c1'cn,c1cv
FROMt_col_row
wherec1isnotnull
UNIONALL
SELECTid,'c2'cn,c2cv
FROMt_col_row
wherec2isnotnull
UNIONALL
SELECTid,'c3'cn,c3cv
FROMt_col_row
wherec3isnotnull;
建立一个例子表：
CREATETABLEt_col_row(
IDINT,
c1VARCHAR2(10),
c2VARCHAR2(10),
c3VARCHAR2(10));
INSERTINTOt_col_rowVALUES(1,'v11','v21','v31');
INSERTINTOt_col_rowVALUES(2,'v12','v22',NULL);
INSERTINTOt_col_rowVALUES(3,'v13',NULL,'v33');
INSERTINTOt_col_rowVALUES(4,NULL,'v24','v34');
INSERTINTOt_col_rowVALUES(5,'v15',NULL,NULL);
INSERTINTOt_col_rowVALUES(6,NULL,NULL,'v35');
INSERTINTOt_col_rowVALUES(7,NULL,NULL,NULL);
COMMIT;
下面的是列转行:创建了一个视图
CREATEviewv_row_colAS
SELECTid,'c1'cn,c1cv
FROMt_col_row
UNIONALL
SELECTid,'c2'cn,c2cv
FROMt_col_row
UNIONALL
SELECTid,'c3'cn,c3cvFROMt_col_row;
下面是创建了没有空值的一个竖表:
CREATEviewv_row_col_notnullAS
SELECTid,'c1'cn,c1cv
FROMt_col_row
wherec1isnotnull
UNIONALL
SELECTid,'c2'cn,c2cv
FROMt_col_row
wherec2isnotnull
UNIONALL
SELECTid,'c3'cn,c3cv
FROMt_col_row
wherec3isnotnull;
8.下面可能是dba经常使用的oracle视图吧。呵呵
Sql代码
1.示例：已知hash_value:3111103299，查询sql语句：
select*fromv$sqltext
wherehashvalue='3111103299'
orderbypiece
2.查看消耗资源最多的SQL：
SELECThash_value,executions,buffer_gets,disk_reads,parse_calls
FROMV$SQLAREA
WHEREbuffer_gets>10000000ORdisk_reads>1000000
ORDERBYbuffer_gets+100*disk_readsDESC;
3.查看某条SQL语句的资源消耗：
SELECThash_value,buffer_gets,disk_reads,executions,parse_calls
FROMV$SQLAREA
WHEREhash_Value=228801498ANDaddress=hextoraw('CBD8E4B0');
4.查询sql语句的动态执行计划:
首先使用下面的语句找到语句的在执行计划中的address和hash_code
SELECT
sql_text,address,hash_valueFROMv$sqlt
where(sql_textlike'%FUNCTION_T(表名大写!)%')
然后:
SELECToperation,options,object_name,costFROMv$sql_plan
WHEREaddress='C00000016BD6D248'ANDhash_value=664376056;
5.查询oracle的版本:
select*fromv$version;
6.查询数据库的一些参数:
select*fromv$parameter
7.查找你的session信息
SELECTSID,OSUSER,USERNAME,MACHINE,PROCESS
FROMV$SESSIONWHEREaudsid=userenv('SESSIONID');
8.当machine已知的情况下查找session
SELECTSID,OSUSER,USERNAME,MACHINE,TERMINAL
FROMV$SESSION
WHEREterminal='pts/tl'ANDmachine='rgmdbs1';
9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100
selectb.sql_text
fromv$sessiona,v$sqlareab
wherea.sql_hashvalue=b.hash_valueanda.sid=100
1.示例：已知hash_value:3111103299，查询sql语句：
select*fromv$sqltext
wherehashvalue='3111103299'
orderbypiece
2.查看消耗资源最多的SQL：
SELECThash_value,executions,buffer_gets,disk_reads,parse_calls
FROMV$SQLAREA
WHEREbuffer_gets>10000000ORdisk_reads>1000000
ORDERBYbuffer_gets+100*disk_readsDESC;
3.查看某条SQL语句的资源消耗：
SELECThash_value,buffer_gets,disk_reads,executions,parse_calls
FROMV$SQLAREA
WHEREhash_Value=228801498ANDaddress=hextoraw('CBD8E4B0');
4.查询sql语句的动态执行计划:
首先使用下面的语句找到语句的在执行计划中的address和hash_code
SELECTsql_text,address,hash_valueFROMv$sqlt
where(sql_textlike'%FUNCTION_T(表名大写!)%')
然后:
SELECToperation,options,object_name,costFROMv$sql_plan
WHEREaddress='C00000016BD6D248'ANDhash_value=664376056;
5.查询oracle的版本:
select*fromv$version;
6.查询数据库的一些参数:
select*fromv$parameter
7.查找你的session信息
SELECTSID,OSUSER,USERNAME,MACHINE,PROCESS
FROMV$SESSIONWHEREaudsid=userenv('SESSIONID');
8.当machine已知的情况下查找session
SELECTSID,OSUSER,USERNAME,MACHINE,TERMINAL
FROMV$SESSION
WHEREterminal='pts/tl'ANDmachine='rgmdbs1';
9.查找当前被某个指定session正在运行的sql语句。假设sessionID为100
selectb.sql_text
fromv$sessiona,v$sqlareab
wherea.sql_hashvalue=b.hash_valueanda.sid=100
9.树形结构connectby排序：
Sql代码
查询树形的数据结构，同时对一层里面的数据进行排序
SELECTlast_name,employee_id,manager_id,LEVEL
FROMemployees
STARTWITHemployee_id=100
CONNECTBYPRIORemployee_id=manager_id
ORDERSIBLINGSBYlast_name;
下面是查询结果
LAST_NAMEEMPLOYE
E_IDMANAGER_IDLEVEL
--------------------------------------------------------
King1001
Cambrault1481002
Bates1721483
Bloom1691483
Fox1701483
Kumar1731483
Ozer1681483
Smith1711483
DeHaan1021002
Hunold1031023
Austin1051034
Ernst1041034
Lorentz1071034
Pataballa1061034
Errazuriz1471002
Ande1661473
Banda1671473
查询树形的数据结构，同时对一层里面的数据进行排序
SELECTlast_name,employee_id,manager_id,LEVEL
FROMemployees
STARTWITHemployee_id=100
CONNECTBYPRIORemployee_id=manager_id
ORDERSIBLINGSBYlast_name;
下面是查询结果
LAST_NAMEEMPLOYEE_IDMANAGER_IDLEVEL
--------------------------------------------------------
King1001
Cambrault1481002
Bates1721483
Bloom1691483
Fox1701483
Kumar1731483
Ozer1681483
Smith1711483
DeHaan1021002
Hunold1031023
Austin1051034
Ernst1041034
Lorentz1071034
Pataballa1061034
Errazuriz1471002
Ande1661473
Banda1671473
10.有时候写多了东西，居然还忘记最基本的sql语法，下面全部写出来，基本的oracle语句都在这里可以找到了。是很基础的语句！
Sql代码
1.在数据字典查询约束的相关信息：
SELECTconstraint_name,constraint_type,search_condition
FROMuser_constraintsWHEREtable_n
ame='EMPLOYEES';
//这里的表名都是大写！
2对表结构进行说明：
descTablename
3查看用户下面有哪些表
selecttable_namefromuser_tables;
4查看约束在那个列上建立：
SELECTconstraint_name,column_name
FROMuser_cons_columns
WHEREtable_name='EMPLOYEES';
10结合变量查找相关某个表中约束的相关列名：
selectconstraint_name,column_namefromuser_cons_columnswheretable_name='&;tablename'
12查询数据字典看中间的元素：
SELECTobject_name,object_type
FROMuser_objects
WHEREobject_nameLIKE'EMP%'
ORobject_nameLIKE'DEPT%'
14查询对象类型：
SELECTDISTINCTobject_typeFROMuser_objects;
17改变对象名：（表名，视图,序列）
renameemptoemp_newTable
18添加表的注释：
COMMENTONTABLEemployeesIS'EmployeeInformation';
20查看视图结构：
describeview_name
23在数据字典中查看视图信息：
selectviewe_name,textfromuser_views
25查看数据字典中的序列：
select*fromuser_sequences
33得到所有的时区名字信息：
select*fromv$timezone_names
34显示对时区‘US/Eastern’的时区偏移量
selectTZ_OFFSET('US/Eastern')fromDUAL--dual英文意思是‘双重的’
显示当前会话时区中的当前日期和时间:
ALTERSESSIONSETNLS_DATE_FORMAT='DD-MON-YYYYHH24:MI:SS';--修改显示时间的方式的设置
ALTERSESSIONSETTIME_ZONE='-5:0';--修改时区
SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;--真正有用的语句！
SELECTCURRENT_TIMESTAMPFROMDUAL;--返回的时间是当前日期和时间，含有时区
SELECTCURRENT_TIMESTAMPFROMDUAL;--返回的时间是当前日期和时间，不含有时区！！！
35显示数据库时区和会话时区的值：
selectdatimezone,sessiontimezonefromdual;
13普通的建表语句：
CREATETABLEdept
(deptnoNUMBER(2),
dnameVARCHAR2(14),
locVARCHAR2(13));
15使用子查询建立表:
CREATETABLEdept80
ASSELECTemployee_id,last_name,
salary*12ANNSAL,
hire_dateFROMemployeesWHEREdepartment_id=80;
6添加列：//altertableEMPaddcolumn(dept_idnumber(7))；错误！！
altertableEMPadd(dept_idnumber(7));
7删除一列：
altertableempdropcolumndept_id;
8添加列名同时和约束：
altertableEMPadd(dept_idnumber(7)
constraintmy_emp_dept_id_fkreferencesdept(ID));
9改变列：//注意约束不能够修改的！！
altertabledept80modify(last_namevarchar2(30));//这里使用的是modify而不是alter!
24增加一行：
insertintotable_namevalues();
5添加主
键:
alterTableEMPaddconstraintmy_emp_id_pkprimarykey(ID);
11添加一个有check约束的新列：
altertableEMP
add(COMMISSIONnumber(2)constraintemp_commission_ckcheck(commission>0))
16删除表：
droptableemp;
19创建视图：
CREATEVIEWempvu80
ASSELECTemployee_id,last_name,salary
FROMemployeesWHEREdepartment_id=80;
21删除视图：
dropviewview_name
22找到工资最高的5个人。（top-n分析）（行内视图）
selectrownum,employee_idfrom(selectemployee_id,salaryfrom
employeesorderbysalarydesc)
whererownum<5;
26建立同义词：
createsynonym同义词名for原来的名字
或者createpublicsynonym同义词名for原来的名字
27建立序列：（注意，这里并没有出现说是哪个表里面的序列！！）
CREATESEQUENCEdept_deptid_seq
INCREMENTBY10
STARTWITH120
MAXVALUE9999
NOCACHE
NOCYCLE
28使用序列：
insertintodept(ID,NAME)values(DEPT_ID_SEQ.nextval,'Administration');
29建立索引：//默认就是nonunique索引，除非使用了关键字：unique
CREATEINDEXemp_last_name_idxONemployees(last_name);
30建立用户：（可能有错，详细查看帮助）
createuserusername(用户名)
identifiedbyoracle(密码)
defaulttablespacedata01(表空间名//默认存在system表空间里面)
quota10M(设置大小，最大为unlimited)on表空间名//必须分配配额！
31创建角色：createROLEmanager
赋予角色权限：grantcreatetable,createviewtomanage
赋予用户角色：grantmanagertoDENHAAN,KOCHHAR(两个用户)
32分配权限：
GRANTupdate(department_name,location_id)
ONdepartments
TOscott,manager;
回收权限
REVOKEselect,insert
ONdepartments
FROMscott;
36从时间中提取年，月，日：使用函数extract
selectextract(yearfromsysdate)year,extract(monthfromsysdate),
extract(dayfromsysdate)fromdual;
37使用函数得到数月之后的日期：to_yminterval（‘01-02’）表示加上1年2月，不能够到天！！
selecthire_date,hire_date+to_yminterval('01-02')ashire_date_newfromemployeeswheredepartment_id=20
得到多少天之后的日期：直接日期加数字！
selecthire_date+3fromemployeeswheredepartment_id=20
38一般的时间函数：
MONTHS_BETWEEN('01-SEP-95','11-JAN-94')--两个日期之间的月数，返回一个浮点数
ADD_MONTHS('11-JAN-94',6)--添加月数
NEXT_DAY('01-SEP-95','FRIDAY')--下一个星期五的日期
LAST_DAY('
01-FEB-95')--当月的最后一天！
ROUND(SYSDATE,'MONTH')--四舍五入月
ROUND(SYSDATE,'YEAR')--四舍五入年
TRUNC(SYSDATE,'MONTH')--阶段月
TRUNC(SYSDATE,'YEAR')--截断年
39group语句：和高级的应用语句：
SELECTdepartment_id,job_id,SUM(salary),COUNT(employee_id)FROMemployees
GROUPBYdepartment_id,job_id;
使用having进行约束：
1.groupbyrollup:对n列组合得到n+1种情况
SELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYROLLUP(department_id,job_id);
2.groupbycube:得到2的n次方种情况
SELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYCUBE(department_id,job_id);
3.使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)
SELECTdepartment_idDEPTID,job_idJOB,SUM(salary),GROUPING(department_id)GRP_DEPT,GROUPING(job_id)GRP_JOB
FROMemployeesWHEREdepartment_id<50GROUPBYROLLUP(department_id,job_id);
4.groupingsets:根据需要得到制定的组合情况
SELECTdepartment_id,job_id,manager_id,avg(salary)FROMemployeesGROUPBYGROUPINGSETS((department_id,job_id),(job_id,manager_id));
40from中使用子查询：返回每个部门中大于改部门平均工资的与员工信息
SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,--下面的地方就是子查询了，主要返回的是一组数据！
(SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)b
WHEREa.department_id=b.department_id
ANDa.salary>b.salavg;
41exists语句的使用：
SELECTemployee_id,last_name,job_id,department_id
FROMemployeesouter--下面的exists里面的select选择出来的是随便的一个字符或者数字都可以
WHEREEXISTS(SELECT'X'FROMemployeesWHEREmanager_id=outer.employee_id);
42厉害的with语句：
WITH
dept_costsAS(--定义了一个临时的表
SELECTd.department_name,SUM(e.salary)ASdept_total--其间定义了一个临时的列dept_total
FROMemployeese,departmentsd
WHEREe.department_id=d.department_id
GROUPBYd.department_name),/*注意这里有逗号*/
avg_costAS(
SELECTSUM(dept_total)/COUNT(*)ASdept_avg
FROMdept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！
SELECT*FROMdept_costsWH
EREdept_total>(SELECTdept_avgFROMavg_cost)ORDERBYdepartment_name;---最后的查询语句中使用了前面的临时表
43遍历树：
SELECTemployee_id,last_name,job_id,manager_id
FROMemployees
STARTWITHemployee_id=101
CONNECTBYPRIORmanager_id=employee_id;--自底向上的遍历树。
44.更新语句
UPDATEemployeesSET
job_id='SA_MAN',salary=salary+1000,department_id=120
WHEREfirst_name||''||last_name='DouglasGrant';
UPDATETABLE(SELECTprojs
FROMdeptdWHEREd.dno=123)p
SETp.budgets=p.budgets+1
WHEREp.pnoIN(123,456);
1.在数据字典查询约束的相关信息：
SELECTconstraint_name,constraint_type,search_condition
FROMuser_constraintsWHEREtable_name='EMPLOYEES';
//这里的表名都是大写！
2对表结构进行说明：
descTablename
3查看用户下面有哪些表
selecttable_namefromuser_tables;
4查看约束在那个列上建立：
SELECTconstraint_name,column_name
FROMuser_cons_columns
WHEREtable_name='EMPLOYEES';
10结合变量查找相关某个表中约束的相关列名：
selectconstraint_name,column_namefromuser_cons_columnswheretable_name='&;tablename'
12查询数据字典看中间的元素：
SELECTobject_name,object_type
FROMuser_objects
WHEREobject_nameLIKE'EMP%'
ORobject_nameLIKE'DEPT%'
14查询对象类型：
SELECTDISTINCTobject_typeFROMuser_objects;
17改变对象名：（表名，视图,序列）
renameemptoemp_newTable
18添加表的注释：
COMMENTONTABLEemployeesIS'EmployeeInformation';
20查看视图结构：
describeview_name
23在数据字典中查看视图信息：
selectviewe_name,textfromuser_views
25查看数据字典中的序列：
select*fromuser_sequences
33得到所有的时区名字信息：
select*fromv$timezone_names
34显示对时区‘US/Eastern’的时区偏移量
selectTZ_OFFSET('US/Eastern')fromDUAL--dual英文意思是‘双重的’
显示当前会话时区中的当前日期和时间:
ALTERSESSIONSETNLS_DATE_FORMAT='DD-MON-YYYYHH24:MI:SS';--修改显示时间的方式的设置
ALTERSESSIONSETTIME_ZONE='-5:0';--修改时区
SELECTSESSIONTIMEZONE,CURRENT_DATEFROMDUAL;--真正有用的语句！
SELECTCURRENT_TIMESTAMPFROMDUAL;--返回的时间是当前日期和时间，含有时区
SELECTCURRENT_TIMESTAMPFROMDUAL;--返回的时间是当前日期和时间，不含有时区！！！
35显示数据库时区和会话时区的值：
selectdatimezone,sessiontimezonefromdual;
13普通的建表语句：
CREATETABLEdept
(deptnoNUMBER(2),
dnameVARCHAR2(14),
locVARCHAR2(13));
15使用子查询建立表:
CREATETABLE
dept80
ASSELECTemployee_id,last_name,
salary*12ANNSAL,
hire_dateFROMemployeesWHEREdepartment_id=80;
6添加列：//altertableEMPaddcolumn(dept_idnumber(7))；错误！！
altertableEMPadd(dept_idnumber(7));
7删除一列：
altertableempdropcolumndept_id;
8添加列名同时和约束：
altertableEMPadd(dept_idnumber(7)
constraintmy_emp_dept_id_fkreferencesdept(ID));
9改变列：//注意约束不能够修改的！！
altertabledept80modify(last_namevarchar2(30));//这里使用的是modify而不是alter!
24增加一行：
insertintotable_namevalues();
5添加主键:
alterTableEMPaddconstraintmy_emp_id_pkprimarykey(ID);
11添加一个有check约束的新列：
altertableEMP
add(COMMISSIONnumber(2)constraintemp_commission_ckcheck(commission>0))
16删除表：
droptableemp;
19创建视图：
CREATEVIEWempvu80
ASSELECTemployee_id,last_name,salary
FROMemployeesWHEREdepartment_id=80;
21删除视图：
dropviewview_name
22找到工资最高的5个人。（top-n分析）（行内视图）
selectrownum,employee_idfrom(selectemployee_id,salaryfrom
employeesorderbysalarydesc)
whererownum<5;
26建立同义词：
createsynonym同义词名for原来的名字
或者createpublicsynonym同义词名for原来的名字
27建立序列：（注意，这里并没有出现说是哪个表里面的序列！！）
CREATESEQUENCEdept_deptid_seq
INCREMENTBY10
STARTWITH120
MAXVALUE9999
NOCACHE
NOCYCLE
28使用序列：
insertintodept(ID,NAME)values(DEPT_ID_SEQ.nextval,'Administration');
29建立索引：//默认就是nonunique索引，除非使用了关键字：unique
CREATEINDEXemp_last_name_idxONemployees(last_name);
30建立用户：（可能有错，详细查看帮助）
createuserusername(用户名)
identifiedbyoracle(密码)
defaulttablespacedata01(表空间名//默认存在system表空间里面)
quota10M(设置大小，最大为unlimited)on表空间名//必须分配配额！
31创建角色：createROLEmanager
赋予角色权限：grantcreatetable,createviewtomanage
赋予用户角色：grantmanagertoDENHAAN,KOCHHAR(两个用户)
32分配权限：
GRANTupdate(department_name,location_id)
ONdepartments
TOscott,manager;
回收权限
REVOKEselect,insert
ONdepartments
FROMscott;
36从时间中提取年，月，日：使用函数extract
selectextract(yearfromsysdate)year,extract(monthfromsysdate),
extract(dayfromsysdate)fromdual;
37使用函数得到数月之后的日期：to_yminterval（‘01-02’）表示加上1年2月，不能够到天！！
selecthire_date,hire_date+to_yminterval('01-02')ashire_date_newfromemployeeswheredepartment_id=20
得到多少天之后的日期：直接日期加数字！
selecthire_date+3fromemployeeswheredepartment_id=20
38一般的时间函数：
MONTHS_BETWEEN('01-SEP-95','11-JAN-94')--两个日期之间的月数，返回一个浮点数
ADD_MONTHS('11-JAN-94',6)--添加月数
NEXT_DAY('01-SEP-95','FRIDAY')--下一个星期五的日期
LAST_DAY('01-FEB-95')--当月的最后一天！
ROUND(SYSDATE,'MONTH')--四舍五入月
ROUND(SYSDATE,'YEAR')--四舍五入年
TRUNC(SYSDATE,'MONTH')--阶段月
TRUNC(SYSDATE,'YEAR')--截断年
39group语句：和高级的应用语句：
SELECTdepartment_id,job_id,SUM(salary),COUNT(employee_id)FROMemployees
GROUPBYdepartment_id,job_id;
使用having进行约束：
1.groupbyrollup:对n列组合得到n+1种情况
SELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYROLLUP(department_id,job_id);
2.groupbycube:得到2的n次方种情况
SELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYCUBE(department_id,job_id);
3.使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)
SELECTdepartment_idDEPTID,job_idJOB,SUM(salary),GROUPING(department_id)GRP_DEPT,GROUPING(job_id)GRP_JOB
FROMemployeesWHEREdepartment_id<50GROUPBYROLLUP(department_id,job_id);
4.groupingsets:根据需要得到制定的组合情况
SELECTdepartment_id,job_id,manager_id,avg(salary)FROMemployeesGROUPBYGROUPINGSETS((department_id,job_id),(job_id,manager_id));
40from中使用子查询：返回每个部门中大于改部门平均工资的与员工信息
SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,--下面的地方就是子查询了，主要返回的是一组数据！
(SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)b
WHEREa.department_id=b.department_id
ANDa.salary>b.salavg;
41exists语句的使用：
SELECTemployee_id,last_name,job_id,department_id
FROMemployeesouter--下面的exists里面的select选择出来的是随便的一个字符或者数字都可以
WHEREEXISTS(SELECT'X'FROMemployeesWHEREmanager_id=outer.employee_id);
42厉害的with语句：
WITH
dept_costsAS(--定义了一个临时的表
SELECTd.department_name,SUM(e.salary)ASdept_total--其间定义了一个临时的列dept_total
FROMempl
oyeese,departmentsd
WHEREe.department_id=d.department_id
GROUPBYd.department_name),/*注意这里有逗号*/
avg_costAS(
SELECTSUM(dept_total)/COUNT(*)ASdept_avg
FROMdept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！
SELECT*FROMdept_costsWHEREdept_total>(SELECTdept_avgFROMavg_cost)ORDERBYdepartment_name;---最后的查询语句中使用了前面的临时表
43遍历树：
SELECTemployee_id,last_name,job_id,manager_id
FROMemployees
STARTWITHemployee_id=101
CONNECTBYPRIORmanager_id=employee_id;--自底向上的遍历树。
44.更新语句
UPDATEemployeesSET
job_id='SA_MAN',salary=salary+1000,department_id=120
WHEREfirst_name||''||last_name='DouglasGrant';
UPDATETABLE(SELECTprojsFROMdeptdWHEREd.dno=123)pSETp.budgets=p.budgets+1
WHEREp.pnoIN(123,456);
11.导入导出dmp文件:
imp用户名/密码@数据库ignore=yfile=备份文件log=D:\DBtest\db_bak\imp.log
expsystem/manager@TESTfile=d:\daochu.dmpfull=y
12.大对象字段blob:查看blob字段的大小:
selectdbms_lob.getLength(字段名)from表名;
13.下面收集的是有意思的sql语句,说不定正是你需要的:
Java代码
--创建一个只允许在工作时间访问的视图
createorreplaceviewnewviewemp
as
select*from表名
whereexists(select1fromdualwheresysdate>=
to_date(to_char(sysdate,'yyyy-mm-dd')||'08:00:00','yyyy-mm-ddhh24:mi:ss')
andsysdate<
to_date(to_char(sysdate,'yyyy-mm-dd')||'18:00:00','yyyy-mm-ddhh24:mi:ss'))
