ORACLE SQL and SQLPLUS.docx

ORACLE SQL and SQLPLUS.docx

《ORACLE SQL and SQLPLUS.docx》由会员分享，可在线阅读，更多相关《ORACLE SQL and SQLPLUS.docx（70页珍藏版）》请在冰豆网上搜索。

ORACLESQLandSQLPLUS

ORACLESQLandSQL*PLUS

目录

第一部分ORACLESQLandSQL*PLUS7

前言:

关系数据库的简单介绍7

一、选择行7

1.简单的SELECT语句7

2.处理NULL8

3.使用SQL*PLUS8

4.SQL*PLUS里规定字段的显示格式9

5.SQL*PLUS里规定字段的显示格式例子9

6.判断题（T/F）9

二、限制选择行10

1.按指定的规则排序10

2.用WHERE限制选择行10

3.LIKE操作10

4.日期字段的比较10

5.不能用到索引的比较操作符10

三、单行函数10

1.数字函数10

2.字符函数11

3.日期函数11

4.转换函数11

5.输入字符,返回数字的函数12

6.有逻辑比较的函数NVL（EXPR1,EXPR2）函数12

四、从多个表里选取数据记录13

1.数据表间的连接13

2.数据表间的连接例子13

3.数据表间的连接技巧13

4.如何分析和执行SQL语句14

五、集合函数14

1.集合函数列表14

2.使用集合函数的语法14

3.使用count时的注意事项15

4.用groupby时的限制条件15

5.例子15

6.判断题（T/F）15

7.在SQL*PLUS里可使用的其它命令:

16

8.在SQL*PLUS里可使用的其它命令举例:

（scott用户）16

六、子查询16

1.查询语句可以嵌套16

2.何处可用子查询?

17

3.两个查询语句的结果可以做集合操作17

4.子查询的注意事项17

5.子查询的例子17

6.取出结果集的80到100的SQL语句18

七、在执行SQL语句时绑定变量18

1.接收和定义变量的SQL*PLUS命令18

2.绑定变量SQL语句的例子18

3.ACCEPT的语法和例子19

4.DEFINE的语法和例子19

5.SQL*PLUS里传递参数到保存好的*.sql文件里19

八、概述数据模型和数据库设计20

1.系统开发的阶段:

20

2.数据模型20

3.实体关系模型（ERM）概念20

4.实体关系模型例子21

5.实体关系的类型21

6.校正实体关系的原则21

7.定义结构时的注意事项21

8.完整性约束的要求21

9.把实体关系图映射到关系数据库对象的方法21

10.实体关系图里符号的含义22

11.实体关系图的例子22

九、创建表22

1.ORACLE常用的字段类型22

2.创建表时给字段加默认值和约束条件23

3.创建表的例子23

4.创建表时的命名规则和注意事项23

5.约束名的命名规则和语法24

6.使用约束时的注意事项24

7.由实体关系图到创建表的例子24

8.较复杂的创建表例子24

8.通过子查询建表25

10.用子查询建表的注意事项25

10.ForeignKey的可选参数ONDELETECASCADE25

12.如果数据库表里有不满足的记录存在，建立约束条件将不会成功.26

13.给表创建和删除同义词的例子26

十、ORACLE里的数据字典26

1.什么是数据字典26

2.数据字典里存了以下内容：

26

3.常用的数据字典26

4.数据字典的分类27

5.查询数据字典27

十一.控制数据27

1、INSERT（往数据表里插入记录的语句）27

2.插入字符串类型的字段的注意事项:

28

3、UPDATE（修改数据表里记录的语句）28

4、DELETE（删除数据表里记录的语句）28

5、SQL语句的分类28

6、ORACLE里事务控制29

6、事物控制和SAVEPOINT命令29

7.DML操作的注意事项29

十二、改变表和约束条件29

1.改变表的几种情况29

2.删除表及表里的数据30

3.删除表后应该注意的问题30

4.给表加注释31

十三、创建序列号31

1.创建序列号里各参数的解释31

2.插入自动增长序列号字段的方法31

3.查询序列号的情况32

4.改变序列号32

5.删除序列号32

6.不能用序列号的nextval和currval的地方32

十四、创建视图33

1.视图的概念和优点33

2.创建视图的语法33

3.创建修改视图的例子33

4.查询视图的数据字典33

5.简单和复杂的视图对比34

6.在视图上可以用DML命令吗?

34

7.在视图里使用WITHCHECKOPTION约束条件34

8.删除视图34

十五、创建索引35

1.索引的概念35

2.什么时候创建索引?

35

3.B*树索引的结构35

4.创建索引的语法35

5.创建索引的注意事项35

6.查询索引的方法35

7.不用索引的地方36

8.重建索引的语法36

9.删除索引36

十六、控制用户访问36

1.权限的类别36

2.查看当前数据库的用户信息37

3.创建新用户37

4、常用的角色及其权限37

5、改变老用户37

6、删除用户38

7、角色的概念和管理38

8、赋于系统的权限语法和例子38

9、赋于数据对象级的权限语法和例子38

Oracle数据库EXP、IMP使用简介39

一、EXP、IMP用途：

39

Oracleerror39

ORA-12541：

TNS：

没有监听器40

ORA-12560：

TNS：

协议适配器错误40

ORA-12154：

TNS：

能解析服务名40

ORA-12560：

TNS：

协议适配器错误（顽固性的）40

EXP-00008:

ORACLE错误904解决41

第一部分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

（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里的文件命令:

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限制选择行

比较操作符=><>=<=!

=<>^=与NULL比较不能用上面的比较操作符ANYSOMEALL

SQL操作符BETWEEN…AND…INLIKEISNULL

NOTBETWEEN…AND…NOTINNOTLIKEISNOTNULL

逻辑操作符ANDORNOT

比较顺序（可以用括号改变它们的顺序）

（1）.=<>>=<=inlikeisnullbetween

（2）.and

（3）.Or

注意:

char和varchar2的比较规则有不同:

char比较时会忽略字符串后面的空格.varchar2会计算字符串后面的空格

3.LIKE操作

%零到任意多个字符_一个字符

例如:

字段名like'M%'字段名like'%m%'字段名like'job_'

如果要找含下划线的字符,要加反斜线例如:

字段名like'%X/_Y%'escape'/'

4.日期字段的比较

举例:

日期字段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'）;

5.不能用到索引的比较操作符

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）两个日期值间的月份,d1

NEXT_DAY（d）返回日期值下一天的日期

SYSDATE当前的系统时间

DUAL是SYS用户下一个空表，它只有一个字段dummy

4.转换函数

TO_CHAR（date,'日期显示格式'）

TO_CHAR（number）用于显示或报表的格式对齐

TO_DATE（char,'日期显示格式'）

TO_LOB把long字段转换成lob字段

TO_NUMBER（char）用于计算或者比较大小

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-ddhh24:

mi:

ss可以在UNIX环境变量或者NT的注册表里的设置NLS_DATE_FORMAT=yyyy-mm-ddhh24:

mi:

ss

如果想固定日期的显示格式可以用altersession命令改变

SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24:

mi:

ss';

它的作用顺序如下:

initializationparameter

Environmentvariable

ALTERSESSIONcommand

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个连接操作

被连接的表最好建一个单字