Oracle基础知识Word格式文档下载.docx
《Oracle基础知识Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《Oracle基础知识Word格式文档下载.docx（35页珍藏版）》请在冰豆网上搜索。
规定字段的对齐方向
column字段名justify[left|right|center];
清除字段的格式
column字段名clear;
5.SQL*PLUS里规定字段的显示格式例子
columnlast_nameheading'
Employee|Name'
formata15;
columnsalaryjustifyrightformat$99,999.99;
columnstart_dateformata10null'
NotHired'
说明：
如果start_date为null,显示字符串'
6.判断题（T/F）
（1）.SQLcommandarealwaysheldinsqlbuffer.[T]
（2）.SQL*PLUScommandassitwithquerydata.[T]
5
J2EE@zxw
SQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.
二、限制选择行
1.按指定的规则排序
SELECTexprFROMtable[ORDERBY{column,expr}[ASC|DESC]];
默认的排序是ASC升序（由小到大）
还可以ORDERBY字段名的位置[1]|[2]ASC|DESC;
2.用WHERE限制选择行
（1）
比较操作符=>
<
>
=<
=!
>
^=与NULL比较不能用上面的比较操作符ANYSOME
ALL
SQL操作符BETWEEN…AND…INLIKEISNULL
NOTBETWEEN…AND…NOTINNOTLIKEISNOTNULL
逻辑操作符ANDORNOT
3.用WHERE限制选择行
（2）
比较顺序（可以用括号改变它们的顺序）
（1）.=<
=inlikeisnullbetween
（2）.and
（3）.Or
4.LIKE操作
%零到任意多个字符_一个字符
例如:
字段名like'
M%'
%m%'
job_'
如果要找含下划线的字符,要加反斜线例如:
字段名like'
%X/_Y%'
escape'
/'
5.日期字段的比较
举例:
日期字段betweento_date（'
2001-12-12'
'
YYYY-MM-DD'
）andto_date（'
日期字段>
to_date（'
）and日期字段<
=
to_date（'
）;
6.不能用到索引的比较操作符
ISNULL
ISNOTNULL
LIKE'
三、单行函数
1.数字函数
ABS取绝对值POWER乘方LN10为底数取0
SQRT平方根EXPe的n次乘方LOG（m,n）m为底数n取0
数学运算函数:
ACOSATANATAN2COSCOSHSIGNSINSINHTANTANH
CEIL大于或等于取整数
FLOOR小于或等于取整数
MOD取余数
ROUND（n,m）按m的位数取四舍五入值如果round（日期）:
中午12以后将是明天的日期.
round（sysdate,'
Y'
）是年的第一天
TRUNC（n,m）按m的位数取小数点后的数值如果trunc（日期）,确省的是去掉时间
6
2.字符函数
CHR按数据库的字符集由数字返回字符
CONCAT（c1,c2）把两个字符c1,c2组合成一个字符,和||相同
REPLACE（c,s,r）把字符c里出现s的字符替换成r,返回新字符
SUBSTR（c,m,n）m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,
m小与0,字符c从后面m处开始取n位字符
TRANSLATE（c,f1,t1）字符c按f1到t1的规则转换成新的字符串
INITCAP字符首字母大写,其它字符小写
LOWER字符全部小写
UPPER字符全部大写
LTRIM（c1,c2）去掉字符c1左边出现的字符c2
RTRIM（c1,c2）
TRIM（c1,c2）去掉字符c1左右两边的字符c2
LPAD（c1,n,c2）字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位
RPAD（c1,n,c2）
3.日期函数
ADD_MONTHS（d,n）日期值加n月
LAST_DAY
返回当月的最后一天的日期
MONTHS_BETWEEN（d1,d2）两个日期值间的月份,d1<
d2返回负数
NEXT_DAY
返回日期值下一天的日期
SYSDATE当前的系统时间
DUAL是SYS用户下一个空表，它只有一个字段dummy
4.转换函数
（1）
TO_CHAR（date,'
日期显示格式'
TO_CHAR（number）用于显示或报表的格式对齐
TO_DATE（char,'
TO_LOB把long字段转换成lob字段
TO_NUMBER（char）用于计算或者比较大小
4.转换函数
（2）
to_date里日期显示格式
YYYY年YEARYYYYYY
Q季度
MM月MONTHMON
W星期（weekofmonth）WW,IW（weekofyear）
（说明：
周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日）
DD日DAYDY
HH24小时HH12HH
MI分钟
SS秒
如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数
NLS_DATE_FORMAT=yyyy-mm-dd
hh24:
mi:
ss可以在UNIX环境变量或者NT的注册表里的设置NLS_DATE_FORMAT=yyyy-mm-dd
7
ss
4.转换函数（3）
如果想固定日期的显示格式可以用altersession命令改变
altersessionsetnls_date_format='
yyyy-mm-ddhh24:
ss'
它的作用顺序如下:
initializationparameter
Environmentvariable
ALTERSESSIONcommand
4.转换函数（4）
to_char（number）里数字显示格式
9数字位
0数字前面补0to_char（-1200,'
00000.00'
.小数点的位置
标记位置的逗号用在数字显示格式的左边
L根据数据库字符集加货币符号to_char（-1200,'
L9999.99'
B把数字0显示为空格,用在数字显示格式的右边
MI右边的负数标记to_char（-1200,'
9999.99MI'
PR括起来的负数to_char（-1200,'
9999.99PR'
EEEE用指数方式表示to_char（-1200,'
9999.99EEEE'
5.输入字符,返回数字的函数
instr（c1,c2）字符c2出现在c1的位置,不出现,返回0,常用于模糊查询
length
按数据库的字符集,算出字符c的长度,跟数据库的字符集有关,一个汉字长度为1
6.有逻辑比较的函数NVL（EXPR1,EXPR2）函数
解释:
IFEXPR1=NULLRETURNEXPR2
ELSERETURNEXPR1
DECODE（AA0V10R10V20R2....）函数
IFAA=V1THENRETURNR1
IFAA=V2THENRETURNR2
..…
ELSE
RETURNNULL
decode（id,1,'
deptsale'
2,'
depttech'
四、从多个表里选取数据记录
1.数据表间的连接
简单的连接语法:
SELECT字段名1,字段名2,……FROM表名1,[表名2,……]
WHERE表名1.字段名=表名2.字段名[AND……];
WHERE表名1.字段名=表名2.字段名（+）[AND……];
有（+）号的字段位置自动补空值
连接的分类:
等于的连接=
不等于的连接!
=BETWEEN…AND…IN注意IN和OR不能一起用
8
外连接有一个字段名（+）,没有满足的条件补空值
自连接同一个表自己跟自己连接例如找重复记录
2.数据表间的连接例子
删除table_name表里字段名email重复的记录:
deletefromtable_namet1
wheret1.rowid>
（selectmin（rowid）fromtable_namet2
wheret1.email=t2.email
groupbyemail
havingcount（email）>
1）;
找到手机用户的服务区域:
selecta.handphoneno,nvl（c.name,'
null'
）,a.totalscore
fromtopscorea,chargeoperatorcc,chargeoperatorinfoc
wheresubstr（a.handphoneno,1,7）=cc.hpnohead（+）
andcc.chargetype=c.chargetype（+）
orderbya.totalscoredesc;
3.数据表间的连接技巧
连接N个表,需要N-1个连接操作
被连接的表最好建一个单字符的别名,字段名前加上这个单字符的别名
BETWEEN..AND..比用>
=AND<
=要好
连接操作的字段名上最好要有索引
连接操作的字段最好用整数数字类型
有外连接时,不能用OR或IN的比较操作
4.如何分析和执行SQL语句
写多表连接SQL语句时要知道它的分析执行计划的情况.
Sys用户下运行@/ORACLE_HOME/sqlplus/admin/plustrce.sql产生plustrace角色
Sys用户下把此角色赋予一般用户SQL>
grantplustraceto&
username;
一般用户下运行@/ORACLE_HOME/rdbms/admin/utlxplan.sql
产生plan_table
settimeon;
说明：
打开时间显示
setautotraceon;
打开自动分析统计，并显示SQL语句的运行结果
setautotracetraceonly;
打开自动分析统计，不显示SQL语句的运行结果
接下来你就运行测试SQL语句，看到其分析统计结果了。
一般来讲，我们的SQL语句应该避免大表的全表扫描。
setautotraceoff;
关闭自动分析统计
五、集合函数
经常和groupby一起使用
1.集合函数列表
AVG（DISTINCT|ALL|N）取平均值
COUNT（DISTINCT|ALL|N|expr|*）统计数量
MAX（DISTINCT|ALL|N）取最大值
MIN（DISTINCT|ALL|N）取最小值
SUM（DISTINCT|ALL|N）取合计值
9
STDDEV（DISTINCT|ALL|N）取偏差值,如果组里选择的内容都相同,结果为0
VARIANCE（DISTINCT|ALL|N）取平方偏差值
2.使用集合函数的语法
SELECTcolumn,group_functionFROMtable
WHEREconditionGROUPBYgroup_by_expression
HAVINGgroup_conditionORDERBYcolumn;
3.使用count时的注意事项
SELECTCOUNT（*）
SELECTCOUNT（常量）FROMtable;
都是统计表中记录数量,如果没有PK后者要好一些
SELECTCOUNT（all字段名）FROMtable;
SELECTCOUNT（字段名）FROMtable;
不会统计为NULL的字段的数量
SUM,AVG时都会忽略为NULL的字段
4.用groupby时的限制条件
SELECT字段名不能随意,要包含在GROUPBY的字段里
GROUPBY后ORDERBY时不能用位置符号和别名
限制GROUPBY的显示结果,用HAVING条件
5.例子
selecttitle,sum（salary）payrollfroms_emp
wheretitlelike'
VP%'
groupbytitle
havingsum（salary）>
5000orderbysum（salary）desc;
找出某表里字段重复的记录数,并显示
select（duplicatefieldnames）fromtable_name
groupby（listoutfields）havingcount（*）>
1;
（1）Groupfunctionsincludenullsincalculations[F]
（2）Usingthehavingclausetoexcluderowsfromagroupcalculation[F]
Groupfunction都是忽略NULL值的如果您要计算NULL值,用NVL函数
Where语句在GroupBy前把结果集排除在外Having语句在GroupBy后把结果集排除在外
六、子查询
1.查询语句可以嵌套
SELECT……FROM（SELECT……FROM表名1,[表名2,……]WHERE条件）WHERE条件2;
2.何处可用子查询?
当查询条件是不确定的条件时
DML（insert,update,delete）语句里也可用子查询
HAVING里也可用子查询
3.两个查询语句的结果可以做集合操作
并集UNION（去掉重复记录）
并集UNIONALL（不去掉重复记录）
10
差集MINUS,
交集INTERSECT
4.子查询的注意事项
先执行括号里面的SQL语句，一层层到外面
内部查询只执行一次
如果里层的结果集返回多个，不能用=>
=等比较符要用IN.
5.子查询的例子
（1）
selecttitle,avg（salary）froms_emp
groupbytitleHavingavg（salary）=
（selectmin（avg（salary））froms_emp
groupbytitle）;
找到最低平均工资的职位名称和工资
5.子查询的例子
（2）
子查询可以用父查询里的表名
这条SQL语句是对的:
selectcty_namefromcitywherest_codein
（selectst_codefromstatewherest_name='
TENNESSEE'
and
t_code=t_code）;
父查询调用子查询只执行一次．
6.取出结果集的80到100的SQL语句
ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置,
并且只能用ROWNUM<
100,不能用ROWNUM>
80。
以下是经过分析后较好的两种ORACLE取得结果集80到100间的SQL语句
（ID是唯一关键字的字段名）
语句写法：
select*from（
（selectrownumasnumrow,c.*from（
select[field_name,...]fromtable_namewhere条件1orderby条件2）c）
wherenumrow>
80andnumrow<
=100）
orderby条件3;
七、在执行SQL语句时绑定变量
1.接收和定义变量的SQL*PLUS命令
ACCEPT
DEFINEUNDEFINE
&
2.绑定变量SQL语句的例子
（1）
selectid,last_name,salaryfroms_empwheredept_id=&
department_number;
Entervaluefordepartment_number:
10
old1:
selectid,last_name,salaryfroms_empwheredept_id=&
new1:
selectid,last_name,salaryfroms_empwheredept_id=10
SETVERIFYOFF|ON;
可以关闭和打开提示确认信息old1和new1的显示.
3.绑定变量SQL语句的例子
（2）
selectid,last_name,salary
froms_emp
wheretitle='
job_title'
Entervalueforjob_title:
StockClerk
11
wherehiredate>
to_date（'
start_hire_date'
Entervalueforstart_hire_date:
2001-01-01
把绑定字符串和日期类型变量时，变量外面要加单引号
也可绑定变量来查询不同的字段名
输入变量值的时候不要加;
等其它符号
4.ACCEPT的语法和例子
ACCEPTvariable[datatype][FORMAT][PROMPTtext][HIDE]
variable指变量名datatype指变量类型，如number,char等format指变量显示格
式prompttext可自定义弹出提示符的内容texthide隐藏用户的输入符号
使用ACCEPT的例子：
ACCEPTp_dnamePROMPT'
Providethedepartmentname:
'
ACCEPTp_salaryNUMBERPROMPT'
Salaryamount:
ACCEPTpswdCHARPROMPT'
Password:
HIDE
ACCEPTlow_datedateformat'
PROMPT“Enterthelowdaterange（'
）:
”
4.DEFINE的语法和例子
DEFINEvariable=value
variable指变量名value指变量值
定义好了变良值后,执行绑定变量的SQL语句时不再提示输入变量
使用DEFINE的例子：
DEFINEdname=sales
DEFINEdname
DEFINEdname=“sales”（CHAR）
selectnamefromdeptwherelower（name）='
dname'
NAME
-------------------------
sales
UNDEFINEdname
SymboldnameisUNDEFINED
5.
升级会员 