ORACLE SQL and SQLPLUS.docx
《ORACLE SQL and SQLPLUS.docx》由会员分享，可在线阅读，更多相关《ORACLE SQL and SQLPLUS.docx（44页珍藏版）》请在冰点文库上搜索。
ORACLESQLandSQLPLUS
ORACLESQLandSQL*PLUS
前言:
关系数据库的简单介绍
1970.Dr.E.F.codded创建了关系数据库的模型（类似现在常用的二维表格）
关系数据库由许多数据对象组成,被关系操作SQL命令管理着,数据之间有完整性的约束条件.
RDBMS（relationaldatabasemanagementsystem）
ORDBMS（objectrelationaldatabasemanagementsystem）
ORACLERDBMS常称为ORACLESERVER
它的内核是用C语言写的.
ORACLE的简单介绍
ORACLE公司是一家提供综合技术产品、方案、服务的数据库公司
ORACLE数据库设计成可大量存储数据，快速查询数据，保证数据的安全和一致性，
跨网络的分布式管理及客户-服务器的配置等。
ORACLESQL语法符合ANSI1986标准
SQL和数据库交流的命令式语言
SQL*PLUSORACLE的一种工具,用来运行SQL和PL/SQL语句
PL/SQLORACLE的过程化编程语言
一、选择行
1.简单的SELECT语句
SELECT[DISTINCT]{*,COLUMN[ALIAS],……}FROMtable_name;
字段的非唯一的结果集
DISTINCT字段名1[,字段名2]
数字类型字段名之间可以进行算术运算
+-*/等
例如:
（字段名1*字段名2）/3
合并字段内容的连接字符||
字段名1||字段名2
字段可以有别名
SELECT字段名1[AS]'字段名1解释'FROMtable;
2.处理NULL
NULL未定义，不可操作，什么都不是
NULL!
=0NULL!
='空格'
和NULL的任何运算都返回NULL
ORACLE里未定义的变量值都为NULL
NVL函数可把NULL转换成其它类型的符号
NVL（EXPR1,EXPR2）函数
解释:
IFEXPR1=NULLRETURNEXPR2
ELSERETURNEXPR1
编程技巧:
NVL函数在多条件模糊查询的时候比较有用
nvl（'+input_value+','0'）='0'orfiled_namelike'%+input_value+%'
NVL函数可返回多种数据类型:
返回日期NVL（start_date,'2002-02-01'）
返回字符串NVL（title,'notitle'）
返回数字NVL（salary,1000）
3.使用SQL*PLUS
（1）
登录SQL*PLUS的方法
用户名，密码，数据库连接字符串
数据库连接字符串可以用net8easyconfiguration建立本地网络服务名配置，
也可以手工编辑$ORACLE_HOME/network/admin/tnsnames.ora文件。
在UNIX系统下不要用
$sqlplususername/password@dbname
这样别的用户用$ps命令能看出用户的密码
SQL>desctable;显示表结构
SQL>select*fromtab;查看用户下所有的表
SQL>setpauseon;可以使大量结果集在用户按“Enter”（回车）后翻页
SQL>setpagesize100;设定SQL语句返回结果集一页的行数100,默认值是14
SQL>setlinesize100;设定SQL语句返回结果集一行的宽度100,默认值是80
3.使用SQL*PLUS
（2）SQL*PLUS里的编辑命令
最近一条SQL命令语句存在ORACLE内存（sqlbuffer）里,但SQL*PLUS命令却不会存进去。
SQL*PLUS里的编辑命令:
A[PPEND]text把text增加到当前行后面
C[HANGE]/old/new/把当前行old符号替换成new符号,new为空，删除old符号
CL[EAR]BUFF[ER]从sqlbuffer里删除所有的行
DELn删除sqlbuffer里第n行
I[NPUT]text在sqlbuffer后面插入text
L[IST]n显示sqlbuffer里第n行
n使第n行为当前行
ntext第n行替换成text
0text在第一行前面插入一行
3.使用SQL*PLUS（3）SQL*PLUS里的文件命令:
SAV[E]filename[REP[LACE]|APP[END]]
把sqlbuffer里的SQL命令存到OS下一个文件filename,默认的文件名后缀为.sql.
REP[LACE]替换filename里的SQL命令
APP[END]把sqlbuffer里的SQL命令添加到filename后
GETfilename从filename里把SQL命令读到sqlbuffer
STA[RT]filename运行上次保存到文件filename里的SQL命令
@filename运行上次保存到文件filename里的SQL命令
EDIT打开编辑窗口，编辑sqlbuffer里的SQL命令
EDITfilename打开编辑窗口，编辑文件filename里的SQL命令
SPO[OL]filename[OFF|OUT]把SQL命令结果输出到OS下一个文件filenameOFF结束输出到文件,
OUT结束输出到文件并打印文件
4.SQL*PLUS里规定字段的显示格式
规定数字的显示格式
SQL>column字段名format99999999999;
SQL>column字段名format999,999,999,999;
规定字符串的显示宽度
SQL>column字段名formata数字[word_wrapped];
说明:
一行只显示数字位的长度,超过长度折行,加word_wrapped后,单词不会折行
规定long字符的显示宽度
SQL>setlong200;
规定字段名的显示内容
SQL>column字段名heading'字段名显示内容';
SQL>setheadingoff;查询时不显示字段名称
规定字段的对齐方向
SQL>column字段名justify[left|right|center];
清除字段的格式
SQL>column字段名clear;
5.SQL*PLUS里规定字段的显示格式例子
SQL>columnlast_nameheading'Employee|Name'formata15;
SQL>columnsalaryjustifyrightformat$99,999.99;
SQL>columnstart_dateformata10null'NotHired';
说明：
如果start_date为null,显示字符串'NotHired'
6.判断题（T/F）
（1）.SQLcommandarealwaysheldinsqlbuffer.[T]
（2）.SQL*PLUScommandassitwithquerydata.[T]
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
注意:
char和varchar2的比较规则有不同:
char比较时会忽略字符串后面的空格.varchar2会计算字符串后面的空格
4.LIKE操作
%零到任意多个字符_一个字符
例如:
字段名like'M%'字段名like'%m%'字段名like'job_'
如果要找含下划线的字符,要加反斜线例如:
字段名like'%X/_Y%'escape'/'
5.日期字段的比较
举例:
日期字段betweento_date（'2001-12-12','YYYY-MM-DD'）andto_date（'2002-02-01','YYYY-MM-DD'）
日期字段>to_date（'2001-12-12','YYYY-MM-DD'）and日期字段<=to_date（'2002-02-01','YYYY-MM-DD'）;
6.不能用到索引的比较操作符
ISNULL
ISNOTNULL
LIKE'%m%'
三、单行函数
1.数字函数
ABS取绝对值POWER乘方LN10为底数取冪
SQRT平方根EXPe的n次乘方LOG（m,n）m为底数n取冪
数学运算函数:
ACOSATANATAN2COSCOSHSIGNSINSINHTANTANH
CEIL大于或等于取整数
FLOOR小于或等于取整数
MOD取余数
ROUND（n,m）按m的位数取四舍五入值如果round（日期）:
中午12以后将是明天的日期.round（sysdate,'Y'）是年的第一天
TRUNC（n,m）按m的位数取前面的数值如果trunc（日期）,确省的是去掉时间
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（d）返回当月的最后一天的日期
MONTHS_BETWEEN（d1,d2）两个日期值间的月份,d1NEXT_DAY（d）返回日期值下一天的日期
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-ddhh24:
mi:
ss
4.转换函数（3）
如果想固定日期的显示格式可以用altersession命令改变
SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24:
mi:
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）按数据库的字符集,算出字符c的长度,跟数据库的字符集有关,一个汉字长度为1
6.有逻辑比较的函数NVL（EXPR1,EXPR2）函数
解释:
IFEXPR1=NULLRETURNEXPR2
ELSERETURNEXPR1
DECODE（AA﹐V1﹐R1﹐V2﹐R2....）函数
解释:
IFAA=V1THENRETURNR1
IFAA=V2THENRETURNR2
..…
ELSE
RETURNNULL
举例:
decode（id,1,'deptsale',2,'depttech'）
四、从多个表里选取数据记录
1.数据表间的连接
简单的连接语法:
SELECT字段名1,字段名2,……FROM表名1,[表名2,……]
WHERE表名1.字段名=表名2.字段名[AND……];
SELECT字段名1,字段名2,……FROM表名1,[表名2,……]
WHERE表名1.字段名=表名2.字段名（+）[AND……];
有（+）号的字段位置自动补空值
连接的分类:
等于的连接=
不等于的连接!
=BETWEEN…AND…IN注意IN和OR不能一起用
外连接有一个字段名（+）,没有满足的条件补空值
自连接同一个表自己跟自己连接例如找重复记录
2.数据表间的连接例子
删除table_name表里字段名email重复的记录:
SQL>deletefromtable_namet1
wheret1.rowid>
（selectmin（rowid）fromtable_namet2
wheret1.email=t2.email
groupbyemail
havingcount（email）>1）;
找到手机用户的服务区域:
SQL>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
SQL>settimeon;说明：
打开时间显示
SQL>setautotraceon;说明：
打开自动分析统计，并显示SQL语句的运行结果
SQL>setautotracetraceonly;说明：
打开自动分析统计，不显示SQL语句的运行结果
接下来你就运行测试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）取合计值
STDDEV（DISTINCT|ALL|N）取偏差值,如果组里选择的内容都相同,结果为0
VARIANCE（DISTINCT|ALL|N）取平方偏差值
2.使用集合函数的语法
SELECTcolumn,group_functionFROMtable
WHEREconditionGROUPBYgroup_by_expression
HAVINGgroup_conditionORDERBYcolumn;
3.使用count时的注意事项
SELECTCOUNT（*）FROMtable;
SELECTCOUNT（常量）FROMtable;
都是统计表中记录数量,如果没有PK后者要好一些
SELECTCOUNT（all字段名）FROMtable;
SELECTCOUNT（字段名）FROMtable;
不会统计为NULL的字段的数量
SUM,AVG时都会忽略为NULL的字段
4.用groupby时的限制条件
SELECT字段名不能随意,要包含在GROUPBY的字段里
GROUPBY后ORDERBY时不能用位置符号和别名
限制GROUPBY的显示结果,用HAVING条件
5.例子
SQL>selecttitle,sum（salary）payrollfroms_emp
wheretitlelike'VP%'groupbytitle
havingsum（salary）>5000orderbysum（salary）desc;
找出某表里字段重复的记录数,并显示
SQL>select（duplicatefieldnames）fromtable_name
groupby（listoutfields）havingcount（*）>1;
6.判断题（T/F）
（1）Groupfunctionsincludenullsincalculations[F]
（2）Usingthehavingclausetoexcluderowsfromagroupcalculation[F]
解释:
Groupfunction都是忽略NULL值的如果您要计算NULL值,用NVL函数
Where语句在GroupBy前把结果集排除在外Having语句在GroupBy后把结果集排除在外
7.在SQL*PLUS里可使用的其它命令:
Ctrl^C终止正在运行的SQL语句
remark/*...*/--注释符号
HOST可执行的操作系统下的命令有些unix可以用!
BREAKONcolumn_nameSKIPn[ONcolumn_nameSKIPn]
按字段的名称column_name分隔显示,更清晰,SKIPn是在分隔处空行的数量n
BREAKONROWSKIPn每一行间隔都放n个空行
COMPUTE集合运算符OF字段1ON字段2按字段2对字段1进行集合运算
COMPUTE后面可以跟的集合运算符:
SUMMINIMUMMAXIMUMAVGSTDVARIANCECOUNTNUMBER
8.在SQL*PLUS里可使用的其它命令举例:
（scott用户）
BREAKONREPORT
COMPUTESUMLABELTOTALOFSALONREPORT在全部结果集后面算合计
selectename,salfromempwherejob='SALESMAN';
COMPUTEAVGLABELavgOFSALONREPORT在全部结果集后面算平均值
/再次执行上次的sql语句
breakonDEPTNOskip2onJOBskip1在BREAK字段结果集后面算合计
COMPUTESUMOFSALONDEPTNO
SELECTDEPTNO,JOB,ENAME,SALFROMEMPORDERBYDEPTNO,JOB;
SQL>CLEARBREAKS;清除设置的BREAK条件
SQL>CLEARCOMPUTES;清除设置的COMPUTE条件
六、子查
升级会员 