051知识点.docx
- 文档编号:3326147
- 上传时间:2022-11-21
- 格式:DOCX
- 页数:18
- 大小:26.69KB
051知识点.docx
《051知识点.docx》由会员分享,可在线阅读,更多相关《051知识点.docx(18页珍藏版)》请在冰豆网上搜索。
051知识点
一、创建表时引用另一个表的内容,原表的NOTNULL约束会传递到新表,而外键则不会。
验证:
1.查看原表,在系统中找了一个符合题目要求的表
SQL>descHS$_CLASS_INIT;
NameNull?
Type
--------------------------------------------------------------------------
FDS_CLASS_INIT_IDNOTNULLNUMBER
FDS_CLASS_IDNOTNULLNUMBER
INIT_VALUE_NAMENOTNULLVARCHAR2(64)
INIT_VALUENOTNULLVARCHAR2(255)
INIT_VALUE_TYPENOTNULLVARCHAR2
(1)
2.查看原表外键
selecta.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE
2fromUSER_CONSTRAINTSa,USER_CONS_COLUMNSb
3wherea.CONSTRAINT_NAME=b.CONSTRAINT_NAMEand
4A.table_name='HS$_CLASS_INIT'and
5CONSTRAINT_TYPE='R';
TABLE_NAMECOLUMN_NAMECONSTRAINT_NAMECONSTRAINT_TYPE
--------------------------------------------------------------------------------
HS$_CLASS_INITFDS_CLASS_IDHS$_CLASS_INIT_FK1R
3.按原表创建新表
SQL>createtabletest(a,b,c)
2asselectFDS_CLASS_INIT_ID,FDS_CLASS_ID,INIT_VALUE_NAMEfromHS$_CLASS_INIT;
Tablecreated.
4.查看新表
SQL>desctest
NameNull?
Type
--------------------------------------------------------------------------
ANOTNULLNUMBER
BNOTNULLNUMBER
CNOTNULLVARCHAR2(64)
selecta.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE
2fromUSER_CONSTRAINTSa,USER_CONS_COLUMNSb
3wherea.CONSTRAINT_NAME=b.CONSTRAINT_NAMEand
4A.table_name='test';
norowsselected
5.上述实验验证了从原表创建新表notnull可以继承,而约束则无法继承,包括外键,主键,check
下表为约束类型简写
TypeCode
TypeDescription
ActsOnLevel
C
Checkonatable
Column
O
ReadOnlyonaview
Object
P
PrimaryKey
Object
R
ReferentialAKAForeignKey
Column
U
UniqueKey
Column
V
CheckOptiononaview
Object
二、视图修改需要注意引用的表的notnull条件,对视图做的DML操作不能违反原有表的约束条件
Withcheckoption需要在where之后使用,如没有where则没有任何作用
1.对于update,有withcheckoption,要保证update后,数据要被视图查询出来
2.对于delete,有无withcheckoption都一样
3.对于insert,有withcheckoption,要保证insert后,数据要被视图查询出来
4.对于没有where子句的视图,使用withcheckoption是多余的
三、逃脱符
_和%在oracle里有特殊意义,_表示匹配任意一个字符,%表示匹配任意数量的任意字符,当字符串中使用了这2个字符就需要使用逃脱符来标记,逃脱符可以为任意字符
例如需要查找包含_abc的字符串,
LIKE'%\_abc%'ESCAPE'\',使用ESCAPE命令指定\为逃脱符,跟在逃脱符后的第一个字符就只作为字符解释,不再包含任何其他意义。
四、单行函数
1.可以接受一个或多个参数
2.可以被嵌套多级
3.参数还可以是变量和表达式
4.对于查询表的每一行总是返回一个结果行
5.返回的数据类型值可以与之前引用的数据类型不同
五、转换数字格式
G表示分组分隔符group
D表示小数点decimalpoint
V表示移动N位(小数点),乘以10的n次方后面跟几位就是几次方
实验:
SQL>selectto_char(1234,'$999G999')new_numberFROMDUAL;
NEW_NUMBE
---------
$1,234
SQL>selectto_char(1234.129,'$999G999D99')new_numberFROMDUAL;
NEW_NUMBER
------------
$1,234.13
SQL>selectto_char(1234.129,'$999G9V999')new_numberFROMDUAL;
NEW_NUMBER
----------
$123,4129
六、MONTHS_BETWEEN(DATE1,DATE2)计算DATE1和DATE2之间相差的月份,小数点后数字表示天数部分,可以为正也可以为负
SQL>selectmonths_between(sysdate,'1-may-2014')fromdual;
MONTHS_BETWEEN(SYSDATE,'1-MAY-2014')
------------------------------------
4.75687388
SQL>selectmonths_between(sysdate,'24-may-2014')fromdual;
MONTHS_BETWEEN(SYSDATE,'24-MAY-2014')
-------------------------------------
4
七、DECODE(expr,search1,result1[,search2,result2……,default]):
比较expr与search,如果等于search1则返回result1,如果等于search2则返回result2,依次类推,如果都不等于,如果有default则返回default,否则返回NULL.
ORACLE在比较之前,会自动把expr和每一个search隐式转换成第一个search(search1)的数据类型。
自动把返回值转换成第一个result(result1)的数据类型。
如果第一个result的数据类型为CHAR或者值是null,则Oracle转换返回值为VARCHAR2.
在DECODE函数中,NULL是相等的,如果expr为空,则Oracle将会返回第一个为NULL的search所对应的result。
DECODE列表中的最大表达式个数为255个。
八、USING和ON
1.USING不能用于非等值连接,ON可以。
2.使用ON子句,可以连接多个列
3.ON子句用于连接表的列可以是不同的名字,但是数据类型要兼容。
ON子句还可以用于联接同一表内或者不同表中具有不同名称的列。
4.在包含ON或USING子句的SELECT命令中,WHERE子句可以做为附加的条件
九、INTERSECT将两个sql结果相交,取共有的部分
1.不会忽略空值
2.交换交集表的前后顺序不会改变结果
3.所有SELECT查询语句中的列名可以不必相同
4.对于所有SELECT查询语句,列的数量和数据类型必须相同。
一十、‘’和Q-quote的表达式
为了区分字符串中的’和标识字符串时使用的’,定义在字符串中如使用’则通过再增加一个’来区分。
如,显示字符串a’b
SQL>select'a'b'fromdual;
ERROR:
ORA-01756:
quotedstringnotproperlyterminated
不做任何修改则会报错
SQL>select'a''b'fromdual;
'A'
---
a'b
使用’’表示’
Q-quote的表达式,使用q’[]’这样的方式将字符串包起来,就会完全按照字符串的内容显示,不会把里面的任何符号作为其他意思解释。
[]是可以替换成别的字符,但是必须前后一致。
一十一、DISTINCT此函数只能用在select中的第一个列
一十二、别名不能用在表达式中
SQL>selectjob,salas"gongzi",gongzi+100fromemp;
selectjob,salas"gongzi",gongzi+100fromemp
*
ERRORatline1:
ORA-00904:
"GONGZI":
invalididentifier
一十三、Null与任何值进行任何计算的结果都是null
一十四、INTERVAL用来表示一段时间,方便进行时间计算
1.Interval后面可以跟year,month,day,hour,minute,seconed,默认数字长度是2,如果数字超过就会报错
SQL>selectinterval'21'yearfromdual;
INTERVAL'21'YEAR
---------------------------------------------------------------------------
+21-00
SQL>selectinterval'111'yearfromdual;
selectinterval'111'yearfromdual
*
ERRORatline1:
ORA-01873:
theleadingprecisionoftheintervalistoosmall
SQL>selectinterval'111'year(4)fromdual;
INTERVAL'111'YEAR(4)
---------------------------------------------------------------------------
+0111-00
2.Interval后面还可以跟yeartomonth和daytohour,daytominute,daytosecond
时分秒的格式hh:
mm:
ss.nn
selectinterval'12:
20:
40.99'hourtosecondfromdual;
一十五、表
参数:
1.每个表中只能使用一个LONG列。
正确
2.一个TIMESTAMP数据类型列可以存储不带小数秒的日期或时间
3.BLOB数据类型列被用于存储二进制数据的文件。
4.对于VARCHAR2数据类型最小的列宽可以为1(VARCHAR2为可变长度的数据类型).
5.CHAR数据类型列的值是用空格填充到列定义的最大值(也就是说,如果定义了CHAR(20),而字符串长度不够20个,则用空格填充不够的位数,CHAR为固定长度数据类型)
6.在使用子查询创建表时不复制LONG列
7.不能在GROUPBY或ORDERBY子句中包括LONG列
8.不能对LONG列定义约束条件
表名,列名
1.表名不能以数字开头,只能以字母开头
2.表名只能有字母,数字、下划线_、美元符$、井字号#等,没有星号*号
3.列名,不能用预留字作为列名
4.表名的长度为1-30个字符
5.表的列最多为1000个
6.相同的schema里,表和view的名字不能相同
7.表和同义词在相同的schema里名称可以相同
表操作
1.Drop表后,数据及表结构全部删除
2.Drop是DDL操作,所以会提交所有之前的操作,commit
一十六、数据类型
1.VARCHAR2长度可变的字符数据(必须指定最大size,最小size为1;最大size为4000)
2.CHAR固定长度的字符数据,长度为size字节(默认和最小size为1;最大size为2000),可以不用指定size
3.NUMBER[(p,s)]精度为p和小数位为s的数字(精度是十进制数字的总位数,而小数位数是小数点右侧的位数,精度范围在1~38之间,而该小数位数范围在-84~127之间),可以不指定参数,P默认38,S根据P来决定,如设置了P,则S为0,如没设置,S为最大
4.DATE:
公元前4712年1月1日到公元9999年12月31日之间的日期和时间值,精确到最接近的秒。
但是没有小数秒
5.INTERVALDAYTOSECOND:
可以将时间存储为以天数、时数、分钟数和秒数表示的间隔。
用于表示两个日期时间值之间的精确差
一十七、约束
列级和表级,列级是在定义列后直接跟约束,表级是在列定义完后跟约束
1.外键可以包含空值
2.因为约束可以作用在INSERT,UPDATE,DELETE操作上
3.因为NOTNULL约束只能在列级定义,不能在表级定义
4.唯一约束可以包含空值
5.即使约束列上有数据,也可以让约束不可用
6.可以多个组合列组成一个表的主键
7.sysdate不能用在check约束中
8.序列的NEXTVAL不能用作默认值
9.即使数据存在,如果没有重复值,你能添加主键约束
一十八、视图
1.简单视图可以执行DML操作
2.包含DISTINCT关键字不能被修改
3.删除视图不会影响视图的基表
4.使用ORREPLACE选项,可以创建一个视图,甚至可以创建一个与已存在的视图同名的视图,以便替换旧版本的视图。
这意味着可以更改视图,而不必经过删除、重新创建对象和重新授予对象权限的过程。
5.WITHCHECKOPTION子句指定通过视图执行的INSERT和UPDATE不能创建该视图无法选择的行。
因此,能够对要插入或更新的数据强制执行完整性约束条件和数据验证检查。
6.子查询中带有ROWNUM关键字不能对视图进行DML操作
7.子查询中带有DISTINCT不能对视图进行DML操作
8.视图是一种基于表或其它视图的逻辑表
9.简单视图和复杂视图都能使用相关表的索引
10.复杂视图的子查询可以是相同或者不同的schema间的多张表
一十九、序列
1.序列是循环的,到达最大值后从最小值MINVALUE开始循环,省略MINVALUE,默认为NOMINVALUE最小值为1,所有又从1开始。
2.CREATESEQUENCE时可以省略所有参数,默认起始值为1,步长为1,无上限
此处提一下CACHE这个关键字:
如果建立序列时不指定CACHE和NOCACHE,默认值为20,
即一次性从序列里取20个数放入内存,如果内存崩溃,则这20个数就会丢失,再取值时从第21个数开始取值,CACHE设置的最小值为2。
3.一个序列生成的值可以用于多个表
4.删除序列的语句为DROPSEQUENCEsequencename
二十、索引
1.表删除时索引会被删除,同义词不会,但同义词失效
2.在表定义中定义了PRIMARYKEY或UNIQUE约束条件,则会自动创建一个唯一的索引
3.在具有索引的表上提交每项DML操作后,都意味着必须更新相应的索引
4.可以在表和簇上面创建索引
5.不能在简单视图上创建索引
6.可以和别的列组合来创建别的索引
二十一、同义词
1.只能由数据库管理员创建公用的同义词,自己创建的同义词只能自己用
2.数据库所有的对象,如表、视图、序列、过程都可以创建同义词
3.共有同义词和私有同义词的名字在一张表中可以相同
二十二、Where
1.where子句不能跟别名,可以跟表达式
2.不能带组函数
二十三、字符类型转换
1.在表达式中,OracleServer可以自动执行以下转换:
从VARCHAR2或CHAR隐式转换为NUMBER
从VARCHAR2或CHAR隐式转换为DATE,但必须符合日期的规定的格式。
二十四、Between
1.Between使用在字母参数时,例如between'B'and'M',包含字母B开头的单词,但不包含M开头的单词
2.Between使用在数字参数时,例如between1300and3000,1300和3000都包含
3.Between使用在date参数时与数字一样
二十五、查看执行计划
1.setautotraceon开启setautotraceoff关闭,开启后执行DML语句后就会显示执行过程
二十六、orderby
1.orderby必须在select的最后
2.参数可以为列名,列的别名,列的编号(这里和where要区分开,where是不允许别名)
SQL>selectjob,salgongzifromemporderbygongzi;
JOBGONGZI
-------------------
CLERK800
CLERK950
CLERK1100
SQL>selectjob,salgongzifromemporderby2;//2是查询语句里第二列,不是原表中的第二列
JOBGONGZI
-------------------
CLERK800
CLERK950
CLERK1100
3.在字符排序中,是区分大小写的
4.Null为无穷大,升序排最后,降序排最前。
可以跟参数nullslast和nullsfirst另外指定
SQL>selectEMPNO,ENAME,SAL,COMMfromemporderbycomm;
EMPNOENAMESALCOMM
----------------------------------------
7844TURNER15000
7499ALLEN1600300
7521WARD1250500
7654MARTIN12501400
7788SCOTT3000
7839KING5000
7876ADAMS1100
SQL>selectEMPNO,ENAME,SAL,COMMfromemporderbycommnullsfirst;
EMPNOENAMESALCOMM
----------------------------------------
7369SMITH800
7782CLARK2450
7902FORD3000
7900JAMES950
7876ADAMS1100
7566JONES2975
7698BLAKE2850
7934MILLER1300
7788SCOTT3000
7839KING5000
7844TURNER15000
EMPNOENAMESALCOMM
----------------------------------------
7499ALLEN1600300
7521WARD1250500
7654MARTIN12501400
二十七、Sql函数
1.Round()TRUNC()函数
Round传回一个数值,该数值是按照指定的小数位元数进行四舍五入运算的结果语法ROUND(number,decimal_places)
number:
需四舍五入处理的数值
decimal_places:
四舍五入,小数取几位(预设为0)
ROUND(156.00,-1)=160,四舍五入,精确到十位
TRUNC()函数截取,不四舍五入
TRUNC(160,-1)=160,截断精确到十位。
2.RPAD:
返回一个表达式,右边使用一个字符表达式填充到n个字符的长度。
RPAD(XXXX,10,’*’)在XXXX字符右边添加*补充到10字符长度
3.SUBSTR:
提取确定长度的字符串
SQL>selectsubstr('abcde',1,2)fromdual;
SU
--
ab
SQL>selectsubstr('abcde',2,2)fromdual;
SU
--
Bc
SQL>selectsubstr('abcde',-3,2)fromdual;
SU
--
Cd
SQL>selectsubstr('abcde',2)fromdual;
SUBS
----
bcde
4.INSTR:
查找指定字符串的数字位置,如果没有找到,instr函数返回0
语法如下:
instr(string1,string2[,start_position[,nth_appearance]])
参数分析:
string1源字符串,要在此字符串中查找。
string2要在string1中查找的字符串.
start_position代表string1的哪个位置开始查找。
此参数可选,如果省略默认为1.字符串索引从1开始。
如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。
nth_appearance代表要查找第几次出现的string2.此参数可选,如果省略,默认为1.如果为负数系统会报错。
SQL>selectinstr('abcdabcd','a')fromdual;
INSTR('ABCDABCD','A')
---------------------
1
SQL>selectinstr('abcdabcd','a',3)fromdual;
INSTR('ABCDABCD','A',3)
-----------------------
5
SQL>selectinstr
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 051 知识点