Oracle基础知识.docx
《Oracle基础知识.docx》由会员分享，可在线阅读，更多相关《Oracle基础知识.docx（39页珍藏版）》请在冰豆网上搜索。
Oracle基础知识
Oracle基础知识
目录
1.选择行4
1.1.简单的SELECT语句4
1.2.处理NULL4
1.3.使用SQL*PLUS
（1）4
1.4.SQL*PLUS里规定字段的显示格式4
1.5.SQL*PLUS里规定字段的显示格式例子5
2.限制选择行5
2.1.按指定的规则排序5
2.2.用WHERE限制选择行
（1）5
2.3.用WHERE限制选择行
（2）5
2.4.LIKE操作6
2.5.日期字段的比较6
2.6.不能用到索引的比较操作符6
3.单行函数6
3.1.数字函数6
3.2.字符函数7
3.3.日期函数7
3.4.转换函数
（1）7
3.5.转换函数
（2）7
3.6.转换函数（3）8
3.7.转换函数（4）8
3.8.输入字符,返回数字的函数8
3.9.有逻辑比较的函数NVL（EXPR1,EXPR2）函数9
4.从多个表里选取数据记录9
4.1.数据表间的连接9
4.2.数据表间的连接例子9
4.3.数据表间的连接技巧10
4.4.如何分析和执行SQL语句10
5.集合函数10
5.1.集合函数列表10
5.2.使用集合函数的语法11
5.3.使用count时的注意事项11
5.4.用groupby时的限制条件11
5.5.例子11
5.6.判断题（T/F）11
6.子查询12
6.1.查询语句可以嵌套12
6.2.何处可用子查询?
12
6.3.两个查询语句的结果可以做集合操作12
6.4.子查询的注意事项12
6.5.子查询的例子
（1）12
6.6.子查询的例子
（2）13
6.7.取出结果集的80到100的SQL语句13
7.在执行SQL语句时绑定变量13
7.1.接收和定义变量的SQL*PLUS命令13
7.2.绑定变量SQL语句的例子
（1）13
7.3.绑定变量SQL语句的例子
（2）14
7.4.ACCEPT的语法和例子14
7.5.DEFINE的语法和例子14
7.6.SQL*PLUS里传递参数到保存好的*.sql文件里15
8.概述数据模型和数据库设计15
8.1.系统开发的阶段:
15
8.2.数据模型15
8.3.实体关系模型（ERM）概念15
8.4.实体关系模型例子16
8.5.实体关系的类型16
8.6.校正实体关系的原则16
8.7.定义结构时的注意事项16
8.8.完整性约束的要求16
8.9.把实体关系图映射到关系数据库对象的方法17
8.10.实体关系图里符号的含义17
9.创建表17
9.1.ORACLE常用的字段类型17
9.2.创建表时给字段加默认值和约束条件18
9.3.创建表的例子18
9.4.创建表时的命名规则和注意事项18
9.5.约束名的命名规则和语法19
9.6.使用约束时的注意事项19
9.7.由实体关系图到创建表的例子s_dept19
9.8.较复杂的创建表例子19
9.9.通过子查询建表20
9.10.用子查询建表的注意事项20
9.11.ForeignKey的可选参数ONDELETECASCADE20
9.12.如果数据库表里有不满足的记录存在，建立约束条件将不会成功.20
9.13.给表创建和删除同义词的例子21
10.ORACLE里的数据字典21
10.1.什么是数据字典?
21
10.2.数据字典里存了以下内容：
21
10.3.常用的数据字典21
10.4.数据字典的分类22
10.5.查询数据字典22
11.控制数据22
11.1.INSERT（往数据表里插入记录的语句）22
11.2.插入字符串类型的字段的注意事项:
23
11.3.UPDATE（修改数据表里记录的语句）23
11.4.DELETE（删除数据表里记录的语句）23
11.5.SQL语句的分类23
11.6.ORACLE里事务控制24
11.7.DML操作的注意事项24
12.改变表和约束条件24
12.1.改变表的几种情况
（1）运行时会加表级锁24
12.2.改变表的几种情况
（2）运行时会加表级锁25
12.3.改变表的几种情况（3）运行时会加表级锁25
12.4.删除表及表里的数据25
12.5.删除表后应该注意的问题26
12.6.给表加注释26
13.创建序列号26
13.1.创建序列号里各参数的解释26
13.2.插入自动增长序列号字段的方法26
13.3.查询序列号的情况27
13.4.改变序列号27
13.5.删除序列号27
13.6.不能用序列号的nextval和currval的地方27
14.创建视图27
14.1.视图的概念和优点28
14.2.创建视图的语法28
14.3.创建修改视图的例子28
14.4.查询视图的数据字典28
14.5.简单和复杂的视图对比29
14.6.在视图上可以用DML命令吗?
29
14.7.在视图里使用WITHCHECKOPTION约束条件29
14.8.删除视图29
15.创建索引29
15.1.索引的概念29
15.2.什么时候创建索引?
30
15.3.B*树索引的结构每个索引由字段值和指针或ROWID组成30
15.4.创建索引的语法30
15.5.创建索引的注意事项30
15.6.查询索引的方法30
15.7.不用索引的地方31
15.8.重建索引的语法31
15.9.删除索引31
16.控制用户访问31
16.1.权限的类别31
16.2.查看当前数据库的用户信息31
16.3.创建新用户31
16.4.常用的角色及其权限32
16.5.改变老用户32
16.6.删除用户32
16.7.角色的概念和管理33
16.8.赋于系统的权限语法和例子33
16.9.赋于数据对象级的权限语法和例子33
17.补充33
1.选择行
1.1.简单的SELECT语句
SELECT字段名1[AS]'字段名1解释'FROMtable;
1.2.处理NULL
NVL函数可把NULL转换成其它类型的符号
编程技巧:
NVL函数在多条件模糊查询的时候比较有用
NVL函数可返回多种数据类型:
返回日期NVL（start_date,'2002-02-01'）
返回字符串NVL（title,'notitle'）
返回数字NVL（salary,1000）
1.3.使用SQL*PLUS
（1）
SQL>desctable;显示表结构
SQL>select*fromtab;查看用户下所有的表
SQL>setpauseon;可以使大量结果集在用户按“Enter”（回车）后翻页
SQL>setpagesize100;设定SQL语句返回结果集一页的行数100,默认值是14
SQL>setlinesize100;设定SQL语句返回结果集一行的宽度100,默认值是80
1.4.SQL*PLUS里规定字段的显示格式
规定数字的显示格式
SQL>column字段名format99999999999;
SQL>column字段名format999,999,999,999;
SQL>column字段名formata数字[word_wrapped];
规定long字符的显示宽度
SQL>setlong200;
规定字段名的显示内容
SQL>column字段名heading'字段名显示内容';
SQL>setheadingoff;查询时不显示字段名称
规定字段的对齐方向
SQL>column字段名justify[left|right|center];
清除字段的格式
SQL>column字段名clear;
1.5.SQL*PLUS里规定字段的显示格式例子
SQL>columnlast_nameheading'Employee|Name'formata15;
SQL>columnsalaryjustifyrightformat$99,999.99;
SQL>columnstart_dateformata10null'NotHired';
说明：
如果start_date为null,显示字符串'NotHired'
2.限制选择行
2.1.按指定的规则排序
SELECTexprFROMtable[ORDERBY{column,expr}[ASC|DESC]];
默认的排序是ASC升序（由小到大）
还可以ORDERBY字段名的位置[1]|[2]ASC|DESC;
2.2.用WHERE限制选择行
（1）
比较操作符=><>=<=!
=<>^=与NULL比较不能用上面的比较操作符ANYSOME
ALL
SQL操作符BETWEEN…AND…INLIKEISNULL
NOTBETWEEN…AND…NOTINNOTLIKEISNOTNULL
逻辑操作符ANDORNOT
2.3.用WHERE限制选择行
（2）
比较顺序（可以用括号改变它们的顺序）
（1）.=<>>=<=inlikeisnullbetween
（2）.and
（3）.Or
2.4.LIKE操作
%零到任意多个字符_一个字符
例如:
字段名like'M%'字段名like'%m%'字段名like'job_'
如果要找含下划线的字符,要加反斜线例如:
字段名like'%X/_Y%'escape'/'
2.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'）;
2.6.不能用到索引的比较操作符
ISNULL
ISNOTNULL
LIKE'%m%'
3.单行函数
3.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（日期）,确省的是去掉时间
3.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.3.日期函数
ADD_MONTHS（d,n）日期值加n月
LAST_DAY返回当月的最后一天的日期
MONTHS_BETWEEN（d1,d2）两个日期值间的月份,d1NEXT_DAY返回日期值下一天的日期
SYSDATE当前的系统时间
DUAL是SYS用户下一个空表，它只有一个字段dummy
3.4.转换函数
（1）
TO_CHAR（date,'日期显示格式'）
TO_CHAR（number）用于显示或报表的格式对齐
TO_DATE（char,'日期显示格式'）
TO_LOB把long字段转换成lob字段
TO_NUMBER（char）用于计算或者比较大小
3.5.转换函数
（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
3.6.转换函数（3）
如果想固定日期的显示格式可以用altersession命令改变
SQL>altersessionsetnls_date_format='yyyy-mm-ddhh24:
mi:
ss';
它的作用顺序如下:
initializationparameter
Environmentvariable
ALTERSESSIONcommand
3.7.转换函数（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'）
3.8.输入字符,返回数字的函数
instr（c1,c2）字符c2出现在c1的位置,不出现,返回0,常用于模糊查询
length按数据库的字符集,算出字符c的长度,跟数据库的字符集有关,一个汉字长度为1
3.9.有逻辑比较的函数NVL（EXPR1,EXPR2）函数
解释:
IFEXPR1=NULLRETURNEXPR2
ELSERETURNEXPR1
DECODE（AA0V10R10V20R2....）函数
解释:
IFAA=V1THENRETURNR1
IFAA=V2THENRETURNR2
..…
ELSE
RETURNNULL
举例:
decode（id,1,'deptsale',2,'depttech'）
4.从多个表里选取数据记录
4.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不能一起用
外连接有一个字段名（+）,没有满足的条件补空值
自连接同一个表自己跟自己连接例如找重复记录
4.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;
4.3.数据表间的连接技巧
连接N个表,需要N-1个连接操作
被连接的表最好建一个单字符的别名,字段名前加上这个单字符的别名
BETWEEN..AND..比用>=AND<=要好
连接操作的字段名上最好要有索引
连接操作的字段最好用整数数字类型
有外连接时,不能用OR或IN的比较操作
4.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;说明：
关闭自动分析统计
5.集合函数
经常和groupby一起使用
5.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）取平方偏差值
5.2.使用集合函数的语法
SELECTcolumn,group_functionFROMtable
WHEREconditionGROUPBYgroup_by_expression
HAVINGgroup_conditionORDERBYcolumn;
5.3.使用count时的注意事项
SELECTCOUNT（*）FROMtable;
SELECTCOUNT（常量）FROMtable;
都是统计表中记录数量,如果没有PK后者要好一些
SELECTCOUNT（all字段名）FROMtable;
SELECTCOUNT（字段名）FROMtable;
不会统计为NULL的字段的数量
SUM,AVG时都会忽略为NULL的字段
5.4.用groupby时的限制条件
SELECT字段名不能随意,要包含在GROUPBY的字段里
GROUPBY后ORDERBY时不能用位置符号和别名
限制GROUPBY的显示结果,用HAVING条件
5.5.例子
SQL>selecttitle,sum（salary）payrollfroms_emp
wheretitlelike'VP%'groupbytitle
havingsum（salary）>5000orderbysum（salary）desc;
找出某表里字段重复的记录数,并显示
SQL>select（duplicatefieldnames）fromtable_name
groupby（listoutfields）havingcount（*）>1;
5.6.判断题（T/F）
（1）Groupfunctionsincludenullsincalculations[F]
（2）Usingthehavingclausetoexcluderowsfromagroupcalculation[F]
解释:
Groupfunction都是忽略NULL值的如果您要计算NULL值,用NVL函数
Where语句在GroupBy前把结果集排除在外Having语句在GroupBy后把结果集排除在外
6.子查询
6.1.查询语句可以嵌套
例如:
SELECT……FROM（SELECT……FROM表名1,[表名2,……]WHERE条件）WHERE条件2;
6.2.何处可用子查询?
当查询条件是不确定的条件时
DML（insert,update,delete）语句里也可用子查询
HAVING里也可用子查询
6.3.两个查询语句的结果可
升级会员 