数据库基础知识培训胶.ppt

数据库基础知识培训胶片,集成产品部,数据库原理,目标,通过这部分课程的学习,您将了解以下内容:关系数据库基本概念常用数据库对象常用数据类型,Thedatabaseplaystheroleofthebase,savingandprocessingthedatathatshouldbeeasilyaccessedbytheuser.,Whatisadatabase?,Usually,adatabaserunsintheserverparttoserveaccessrequestsfromclients.,DefinitionofaRelationalDatabase,Arelationaldatabaseisacollectionofrelationsortwo-dimensionaltables.,Oracleserver,TableName:EMPLOYEES,TableName:DEPARTMENTS,RelationalDatabaseManagementSystem,RDBMS:RelationalDatabaseManagementSystemArelationaldatabaseconsistsoftablesthataremadeupofrowsandcolumns.,Tables,AdatabaseinaRDBMSisacollectionofinformationthatisgroupedintooneormoretables.Atableisanarrayofdataitemsorganizedintorowsandcolumns.,Stock,Tablesarerelational,Rows,RowsEachrowofatablestandsforoneinstanceofthesubjectofthetable,whichisoneparticularexampleofthatentity.,Stock,ColumnsEachcolumnofatablestandsforoneattribute,whichisonecharacteristic,feature,orfactthatistrueofthesubjectofthetable.,Columns,Stock,TheRelationalModel,OperationsonTables,DatabaseObjects,ObjectDescriptionTableBasicunitofstorage;composedofrowsandcolumnsViewLogicallyrepresentssubsetsofdatafromoneormoretablesSequenceNumericvaluegeneratorIndexImprovestheperformanceofsomequeriesSynonymGivesalternativenamestoobjects,WhatisaView?,EMPLOYEESTable:,WhyUseViews?,TorestrictdataaccessTomakecomplexquerieseasyToprovidedataindependenceTopresentdifferentviewsofthesamedata,WhatareConstraints?,Constraintsenforcerulesatthetablelevel.Constraintspreventthedeletionofatableiftherearedependencies.Thefollowingconstrainttypesarevalid:NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECK,WhatisanIndex?,Anindex:IsaschemaobjectIsusedbytheOracleservertospeeduptheretrievalofrowsbyusingapointerCanreducediskI/ObyusingarapidpathaccessmethodtolocatedataquicklyIsindependentofthetableitindexesIsusedandmaintainedautomaticallybytheOracleserver,HowAreIndexesCreated?,Automatically:AuniqueindexiscreatedautomaticallywhenyoudefineaPRIMARYKEYorUNIQUEconstraintinatabledefinition.Manually:Userscancreatenonuniqueindexesoncolumnstospeedupaccesstotherows.,DataTypes,DataTypeDescriptionVARCHAR2(size)Variable-lengthcharacterdataCHAR(size)Fixed-lengthcharacterdataNUMBER(p,s)Variable-lengthnumericdataDATEDateandtimevaluesLONGVariable-lengthcharacterdataupto2gigabytesCLOBCharacterdataupto4gigabytesRAWandLONGRAWRawbinarydataBLOBBinarydataupto4gigabytesBFILEBinarydatastoredinanexternalfile;upto4gigabytesROWIDA64basenumbersystemrepresentingtheuniqueaddressofarowinitstable.,总结,通过这部分课程的学习,您需要掌握以下内容:关系数据库基本概念常用数据库对象表索引约束常用数据类型,SQL语言基础,目标,通过这部分课程的学习,您将了解以下内容:SQL语句的种类执行基本的Select语句在表中插入数据行在表中修改数据行从表中删除数据行控制事务处理,SQLStatements,SELECTINSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATECOMMITROLLBACKSAVEPOINTGRANTREVOKE,Dataretrieval,Datamanipulationlanguage(DML),Datadefinitionlanguage(DDL),Transactioncontrol,Datacontrollanguage(DCL),基础SELECT语句语法,SELECT*|DISTINCTcolumn|expressionalias,.FROMtable;,使用算术操作符,SELECTlast_name,salary,salary+300FROMemployees;,使用连接符和字符串,SELECTlast_name|isa|job_idASEmployeeDetailsFROMemployees;,排除重复的行,SELECTDISTINCTdepartment_idFROMemployees;,使用WHERE条件,SELECTemployee_id,last_name,job_id,department_idFROMemployeesWHEREdepartment_id=90;,比较条件,操作符=,含义等于大于大于或等于小于小于或等于不等于,其它的比较条件,操作符BETWEEN.AND.IN(set)LIKEISNULL,含义两个值之间匹配值列表中值匹配一个字符样式空值,使用LIKE条件,搜索条件能包括两种模式匹配类型:%表示许多字符._表示一个字符.,SELECTfirst_nameFROMemployeesWHEREfirst_nameLIKES%;,使用NULL条件,测验空值使用ISNULL条件.,SELECTlast_name,manager_idFROMemployeesWHEREmanager_idISNULL;,逻辑条件,OperatorANDORNOT,含义两部分条件都返回真值则结果返回真值两部分条件中有任意一部分返回真值则结果返回真值如果条件返回假则结果返回真值,使用AND操作符,要求条件都为真.,SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary=10000ANDjob_idLIKE%MAN%;,SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_date;,ORDERBY条件,带ORDERBY条件的排序ASC:升序，缺省DESC:降序ORDERBY条件在Select语句最后.,数据操作语句,一条DML语句在下列情况下被执行:向表中增加新的数据行修改表中存在的数据从表中删除数据一个事务由DML语句集合形成的逻辑工作单元构成.,INSERT语句语法,.,INSERTINTOtable(column,column.)VALUES(value,value.);,向表中插入新的数据行,插入的新行应包括所有的列值.插入的值应按照表中列缺省的顺序排列.如插入所有的列,INSERT条件中可随意选择是否列出所有列.,INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,PublicRelations,100,1700);1rowcreated.,INSERTINTOemployeesVALUES(114,Den,Raphealy,DRAPHEAL,515.127.4561,TO_DATE(FEB3,1999,MONDD,YYYY),AC_ACCOUNT,11000,NULL,100,30);1rowcreated.,插入特殊的日期型值,Verifyyouraddition.,带子查询的INSERT语句.字段的数据类型必须匹配.,INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE%REP%;4rowscreated.,从另外一个表中复制行,UPDATE语句语法,可一次性修改多行,UPDATEtableSETcolumn=value,column=value,.WHEREcondition;,UPDATEemployeesSETdepartment_id=70WHEREemployee_id=113;1rowupdated.,在WHERE条件中指定需要修改的行.修改表中所有的行.,修改表中数据,UPDATEcopy_empSETdepartment_id=110;22rowsupdated.,UPDATEcopy_empSETdepartment_id=(SELECTdepartment_idFROMemployeesWHEREemployee_id=100)WHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=200);1rowupdated.,基于另外表SELECT得到的值修改数据,使用子查询实现：,DELETE语句语法,DELETEFROMtableWHEREcondition;,通过WHERE条件指定删除的行.省略WHERE条件，所有的行都将被删除.,从表中删除多行,DELETEFROMdepartmentsWHEREdepartment_name=Finance;1rowdeleted.,DELETEFROMcopy_emp;22rowsdeleted.,DELETEFROMemployeesWHEREdepartment_id=(SELECTdepartment_idFROMdepartmentsWHEREdepartment_nameLIKE%Public%);1rowdeleted.,基于另外表SELECT得到的值删除数据,通过子查询语句从另外表中得到值作为删除条件.,数据库事务,一个数据库完整事务包含以下情况:构成对数据的一次一致性改变的DML语句（一条或多条）一条DDL语句,数据库事务,当第一条DMLSQL语句执行时开始当下列任意事件发生时结束:提交或回滚语句执行一条DDL执行(自动提交)系统崩溃,控制事务,ROLLBACKtoSAVEPOINTB,ROLLBACKtoSAVEPOINTA,ROLLBACK,UPDATE.SAVEPOINTupdate_done;Savepointcreated.INSERT.ROLLBACKTOupdate_done;Rollbackcomplete.,回滚改变到一个标记,在当前的事务中可通过SAVEPOINT语句创建标记.ROLLBACKTOSAVEPOINT语句可回滚到标记.,自动的commit发生在:DDL语句执行,隐性的事务处理,COMMIT;Commitcomplete.,改变数据.Commit改变.,DELETEFROMemployeesWHEREemployee_id=99999;1rowdeleted.INSERTINTOdepartmentsVALUES(290,CorporateTax,NULL,1700);1rowinserted.,提交数据,回滚后数据状态,将丢弃所有未COMMIT的改变：,DELETEFROMcopy_emp;22rowsdeleted.ROLLBACK;Rollbackcomplete.,总结,通过这部分课程的学习,您将了解以下内容:SQL语句类型写一个SELECT语句:从一个表中返回所有的行和列从一个表中返回指定的列使用列别名描述列头描述DML语句在表中插入数据行在表中修改数据行从表中删除数据行控制事务处理,练习,请见Oracle数据库维护上机任务书课堂练习-SQL语言基础,数据库函数,目标,通过这部分课程的学习,您将了解以下内容:描述SQL中用到的不同类型的函数在SELECT语句中使用字符，数值和日期等函数转换函数的使用,SQLFunctions,Function,Functionperformsaction,字符型函数,字符型函数,LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE,大小写处理函数,字符处理函数,Function,Result,大小写处理函数,能对字符串进行大小写转换.,LOWER(SQLCourse)UPPER(SQLCourse)INITCAP(SQLCourse),sqlcourseSQLCOURSESqlCourse,使用大小写处理函数,SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name=higgins;norowsselected,SELECTemployee_id,last_name,department_idFROMemployeesWHERELOWER(last_name)=higgins;,CONCAT(Hello,World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld,W)LPAD(salary,10,*)RPAD(salary,10,*)TRIM(HFROMHelloWorld),HelloWorldHello106*2400024000*elloWorld,Function,Result,字符处理函数,下列函数能对字符串进行处理:,SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,a)Containsa?FROMemployeesWHERESUBSTR(job_id,4)=REP;,使用字符处理函数,1,2,3,1,2,3,日期函数,两个日期中的间隔月数,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,在日期中增加历法月数,指定日期中的下一天,这个月的最后一天,Round日期,Truncate日期,Function,Description,MONTHS_BETWEEN(01-SEP-95,11-JAN-94)