业软公共 新员工培训 Oracle数据库知识-SQL 培训胶片V1.0

20.Aug2006HUAWEITECHNOLOGIESCo.,Ltd.内部公开Oracle基础知识—SQLCC&CRM新员工报表培训HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage2两种类型的SQLHUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage3DDL（DataDefinitionLanguage）数据定义语言是SQL中定义数据库中数据结构的语言。顾名思义，就是帮助用户定义、创建表和权限的SQL语句。HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage4DML（DataManipulationLanguage）数据操纵语言允许用户对数据内容进行增、删、改等操作。HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage5ORACLE运算符运算符含义＝相等！＝或＜＞不等于＞大于＞＝大于等于＜小于＜＝小于等于IN(列表)等于列表中的任意值BETWEEN值1AND值2大于等于值1并且小于等于值2LIKE％或－模式匹配。“％”匹配0个或任意个字符，“－”匹配一个字符ISNULL空值ISNOTNULL非空值NOT逻辑非AND逻辑与OR逻辑或HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage6SELECT查询数据库查询操作是数据库系统中用得最为广泛的操作，由select语句来完成，基本语法为：SELECT[DISTINCT]{column1,column2,…}FROMtablenamesWHERE{conditions}GROUPBY{conditions}ORDERBY{expressions}[ASC/DESC];SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据，还可以把多个表、视图的数据结合起来，使得查询结果的每一行中包含来自多个表达式或视图的数据，这种操作被称为连接查询。WHERE子句中的条件可以是一个包含等号或不等号的条件表达式，也可以是一个含有IN、NOTIN、BETWEEN、LIKE、ISNOTNULL等比较运算符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成复合条件。HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage7INSERTINTO基本语法：INSERTINTOtablename{column1,column2,…}VALUES{expression1,expression2,…};样例1：INSERTINTODEPT（DNAME，DEPTNO）VALUES('ACCOUNTING'，10);样例2：INSERTINTODEPT（DNAME，DEPTNO）SELECT'ACCOUNTING'，10FROMDUAL;HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage8DELETE基本语法：DELETEFROMtablenameWHERE{conditions};示例：DELETEFROMEMPWHEREEMPNO=7654;DELETE命令删除一条记录,而且DELETE命令只能删除整行,而不能删除某行中的部分数据。DELETE语句和TRUNCATE语句的差别：DELETE语句是DML语句，TRUNCATE语句是DDL语句。DELETE语句在删除记录的时候不释放表空间占用的磁盘资源，TRUNCATE则释放。DELETE语句可以删除表中满足条件的记录，TRUNCATE只能进行整表删除。DELETE语句在删除时，在回滚段表空间中需要记录回滚信息，而TRUNCATE语句则没有。HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage9UPDATE基本语法：UPDATEtablenameSET{column1=expression1,column2=expression2,…}WHERE{conditions};实例：UPDATEEMPSETJOB='MANAGER'WHEREENAME='MAPTIN';事务控制命令提交命令(COMMIT)，可以使数据库的修改永久化。使用方法是：SQLSETCOMMIT;回滚命令(ROLLBACK)，消除上一个COMMIT命令后的所做的全部修改，使得数据库的内容恢复到上一个COMMIT执行后的状态。使用方法是:SQLROLLBACK;HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage10创建并管理表HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage11Oracle常用数据类型字段类型中文说明限制条件其它说明CHAR固定长度字符串最大长度2000bytesVARCHAR2可变长度的字符串最大长度4000bytes可做索引的最大长度749NCHAR根据字符集而定的固定长度字符串最大长度2000bytesNVARCHAR2根据字符集而定的可变长度字符串最大长度4000bytesDATE日期（日-月-年）DD-MM-YY（HH-MI-SS）经过严格测试，无千虫问题LONG超长字符串最大长度2G（231-1）足够存储大部头著作RAW固定长度的二进制数据最大长度2000bytes可存放多媒体图象声音等LONGRAW可变长度的二进制数据最大长度2G同上BLOB二进制数据最大长度4GCLOB字符数据最大长度4GNCLOB根据字符集而定的字符数据最大长度4GBFILE存放在数据库外的二进制数据最大长度4GROWID数据表中记录的唯一行号10bytes********.****.****格式，*为0或1NROWID二进制数据表中记录的唯一行号最大长度4000bytesNUMBER(P,S)数字类型P为整数位，S为小数位DECIMAL(P,S)数字类型P为整数位，S为小数位INTEGER整数类型小的整数FLOAT浮点数类型NUMBER(38)，双精度REAL实数类型NUMBER(63)，精度更高HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage12Oracle块空间的使用InsertsInsertsInsertsInserts1234PCTFREE=20PCTUSED=4080%80%40%HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage13创建表CREATETABLEt_Dept(DeptNoVARCHAR2(4)notnull,DeptNameVARCHAR2(20)notnull,CreateDateDATE,MembersNUMBER)tablespaceTBS_LOG_DATpctfree10pctused85initrans1maxtrans255storage(initial10Mnext1Mminextents1maxextentsunlimitedpctincrease0);HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage14使用子查询创建表CRETAETBALEt_Dept_bk(DeptNoVARCHAR2(4)notnull,DeptNameVARCHAR2(20)notnull,CreateDateDATE,MembersNUMBER)ASSELECTt.DeptNo,t.DeptName,t.CreateDate,t.MembersFROMt_DepttWHEREt.DeptNoLIKE’02%’;HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage15使用分区表把一个大的表或索引分割成多块数据片（区），SQL语句可以用数据区访问来代替对整个表或索引的访问。这种表存储方式叫分区表（PARTITION）。一般来说，表的Partitioning多应用在数据仓库（大量历史数据的储存和访问）。通过对表或索引的分区实现了大对象向小对象的转化。分区表的分区方式主要分成rangepartitioning和hashpartitioning：Range把表或索引中同一范围的值划分到一起。hash通过hashfunction划分数据。compositepartitioning把数据首先通过range方式划分，然后通过hash方式进行细化。HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage16创建分区表CRETAETBALEt_Dept(DeptNoVARCHAR2(2)notnull,DeptNameVARCHAR2(20)notnull,CreateDateDATE,MembersNUMBER)tablespaceTBS_DATpctfree10pctused85initrans1maxtrans255storage(initial10Mnext1Mpctincrease0)partitionbyrange(DeptNo)(partitionP01VALUESLESSTHAN('04')TABLESPACETBS_DAT,partitionP02VALUESLESSTHAN('07')TABLESPACETBS_DAT,partitionP03VALUESLESSTHAN(MAXVALUE)TABLESPACETBS_DAT);HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage17分区表扩容ALTERTABLEt_DeptADDpartitionP04valueslessthanTHAN(‘10')TABLESPACETBS_DATstorage(initial100knext100kminextents1maxextentsunlimitedpctincrease0);HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage18创建和使用视图HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage19使用视图视图是封装的复合查询。当对一组数据建立视图以后你像处理另外一个表一样去处理视图，但是在视图中修改数据时要受到一些限制；当表中的数据改变以后你将会在查询视图时发现相应的改变。视图并不占用数据库或表的物理空间。CREATEORREPLACEVIEWv_Rpt_OrgaASSELECT'99'ASOrgaID,'所有地市'ASOrgaNameFROMdualUNIONALLSELECTOrgaID,OrgaNameFROMt_pub_OrgaWHERESuperID='0';HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage20为什么使用视图简单性：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。安全性：通过视图用户只能查询和修改他们所能见到的数据，数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和列上。逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响使，应用程序和数据库表在一定程度上分割开来。HUAWEITECHNOLOGIESCo.,Ltd.HUAWEIConfidentialPage21在视图中修改数据的几个限制对于多表视图不能使用DELE