orcal备课笔记.doc

Oracle备课笔记 Oracle第一课(数据库介绍)备课笔记 一、了解数据库管理系统的不同类型 1、数据存储系统 将数据存储在计算机上的概念就叫数据存储系统 。 数据以文本、数字、图片或声音等形式存储。2、数据库是一组在逻辑上相关的信息的集合 。表以行和列的形式保存数据。实现数据库管理任务的软件叫做数据库管理系统(DBMS)。 3、DBMS的不同类型为 ：RDBMS和ORDBMS RDBMS是由E.F.Codd博士在1970年首先提出的。RDBMS 的例子包括 Sybase, Microsoft Access, Ingres, Oracle, 及 SQL Server。在RDBMS的表中 ，每个属性叫做一列，每条记录被称为一行。4、ORDBMSORDBMS将面向对象的前端与RDBMS集成起来 。5、面向对象的概念 在面向对象的方法中，系统被当作一组真实世界中对象的集合 。这些对象以类来分类 ，类就是拥有共同属性和行为的一组对象 。面向对象方法具有以下特性 ：抽象、继承、封装、多态性二、了解Oracle9i的特性 可以使用结构化查询语言(SQL)或面向对象的编程语言来访问数据 。 可使用内置数据类型在表中存储数据存储。将复合结构当作单个实体进行存储 。 使用ORDBMS 的继承特性 。允许创建许多同名函数。 可扩展性使得用户能够定义新的数据类型来存储复合数据。允许创建对象视图。三、了解ER 图的组成 1、实体 - 关系(ER)图: 以图形方式表示数据库的逻辑结构，可被看作是数据库的蓝图 。 2、画ER图的指南是：(1)确定各实体 (2)确定实体的属性 (3)确定实体之间的关系,联系的类型: 一对一、一对多或多对一、多对多 四、ER图映射成表的规则：1、独立存在的实体转换成一个表2、属性转换成列（字段），可唯一标识一行的属性设置为主键3、一对一关系，可在两表中互设为主键，也可将两表合并例如：部门和部分负责人4、一对多关系，第一个表的主键可作为第二个表的外键例如：班级和学生5、多对多关系，关系转换成表，原来的两表主键在新表中用做外键例如：学生和课程四、使用 SQL*Plus 语句1、SQL语句介绍 SQL用来检索和维护数据库中数据 。 使用SQL的优点 ：SQL是所有RDBMS使用的公共语言 。SQL不遵循任何特定的执行模式，它一次可以访问多个记录 。SQL使用简单的维护数据的命令。SQL语句非常接近英语自然语言，因此，易学易懂。2、查询使用类似英语的语句来从数据库中检索信息 。 SQL SQL语句和查询命令用来 ：创建和维护数据库 。创建和维护表 。 删除数据库和表 。 基于条件查询数据。 按所需格式提供查询输出。 创建视图 ，报告，和表单。3、数据定义语言 (DDL) ：包含一组命令，用来创建数据库对象，如表等 。 DDL可用于： 创建对象、改变对象 、撤消对象 4、数据操纵语言 (DML) ：用来操纵表数据 ，包含INSERT、UPDATE和 DELETE等命令 。DML 可用于： 存储数据、更新数据、删除数据 5、SELECT 语句 用于检索表中所有数据，或特定范围的数据。语法 SELECT * | select_list FROM tablename; 五、SQLPlus的使用介绍Oracle第二课(查询数据库)备课笔记 一、显示格式化的查询输出1、显示表的指定列语法如下：SELECT column_name, column_name FROM tablename;2、使用列别名可以给这些列取些友善的标题来取代表中缺省列标题。其语法是： SELECT column_name column_heading , column_name FROM tablename;举例：SELECT cName Recruiter Name FROM CONTRACTRECRUITER或者使用SELECT cName AS Recruiter Name FROM CONTRACTRECRUITER3.DISTINCT子句在SELECT 语句中用来观看表列中唯一的值语法如下: SELECT DISTINCT column_name FROM tablename; 二、识别不同类型的运算符算术运算符：+、-、*、/、%比较运算符：=、=、 =、 =、 !=、betweenand或 in等比较运算符.例子SELECT ExternalCandidate.vFirstName, vLastName, Rating.cCandidateRating FROM ExternalCandidate, Rating WHERE ExternalCandidate.nRating BETWEEN Rating.nLowerRating AND Rating.nHigherRating; (三)NATURAL JOIN （自然联接）基于两个表的同名的一个或多个列.例子SELECT cEmployeeCode, vFirstName, nAnnualSalary, nYear FROM Employee NATURAL JOIN AnnualSalary; 用USING子句检索记录 如果两个表中你有同名的列，那么你在联接语句里可以使用USING子句.例子SELECT cEmployeeCode, vFirstName, nAnnualSalary, nYear FROM Employee JOIN AnnualSalary USING (cEmployeeCode); (四) CROSS JOIN（交叉联接） 使用关键字CROSS的包含多个表的联接. 例子SELECT * FROM Requisition CROSS JOIN Position; (五)OUTER JOIN （外部联接） OUTER JOIN 用于检索一个表的所有记录和另一表中的匹配行.语法SELECT column_name, column_name ,column_name FROM table_name LEFT | RIGHT | FULL OUTER JOIN table_name ON table_name.ref_column_name join_operator table_name.ref_column_name(六)SELF JOIN （自联接） SELF JOIN 用于联接在同一个表内的行.语法 SELECT a.vFirstName “Employee”, b.vFirstName “Supervisor” FROM Employee a, Employee b WHERE a.cSupervisorCode = b.cEmployeeCode; Oracle第五课(作用于多行的函数)备课笔记 一、了解多行函数的不同类型用来组合查询中多行. 多行函数的类型有:AVG返回n个数值的平均值. SUM n个值的总和.COUNT显示查询返回的行数MAX 返回一个列中的最大值. MIN 返回一个列中的最小值. STDDEV 返回一组值的标准偏差. VARIANCE返回一组值的方差. 举例：SELECT COUNT (cNewspapercode) “No of Newspapers” FROM Newspaper; 二、对查询结果进行分组GROUP BY子句用来对表中的行进行分组. 语法SELECT column_name1, column_name2 FROM table_name WHERE search_condition GROUP BY group_expressionHAVING search_condition;在GROUP BY子句里使用组合函数 多行或组合函数可以和GROUP BY 子句一起使用. 三、检索表中被选的组合数据HAVING 字句用来指定GROUP BY子句执行的分组操作的条件.语法SELECT column_name, column_name FROM table_name WHERE search_conditionGROUP BY group_expressionHAVING search_condition oracle上课用示例数据库脚本 set serveroutput onspool tableLog.txt/* - Creating Objects for Recruitment */create table Recruitmentusers(cUserName char(10),cPassword char(10) ;create table Department(cDepartmentCodechar(4) constraint dt_pk primary key,vDepartmentNamevarchar(25) ,vDepartmentHead varchar(25),vLocation varchar(20);create table Position(cPositionCodechar(4) constraint ptv_pk primary key,vDescriptionvarchar(35),nBudgetedStrengthnumber(3),nYear number(4),nCurrentStrength number(2);-create table Country(cCountryCodechar(3) constraint c_pk primary key,cCountrychar(35) not null) ;create table Newspaper(cNewspaperCodechar(4) constraint np_pk primary key,cNewspaperNamechar(20) not null,vRegion varchar(20),vTypeOfNewspaper varchar(20),vContactPerson varchar(35),vHOAddressvarchar(35),cCitychar(20),cStatechar(20),cZipchar(10),cCountryCodechar(3) references Country(cCountryCode),cFaxchar(15),cPhone char(15) ;create table NewsAd(cNewsAdNochar(4) constraint nat_pk primary key,cNewspaperCodechar(4) references Newspaper(cNewspaperCode),dAdStartDatedate,dDeadlinedate) ;create table JobFair(cJobFairCodechar(4) constraint jft_pk primary key ,vLocationvarchar(35),vJobFairCompanyvarchar(40),nFeenumber(10,2),dFairDatedate) ;create table ContractRecruiter(cContractRecruiterCodechar(4) constraint crtp_pk primary key,cNamechar(35),vAddressvarchar(35),cCitychar(20),cStatechar(15),cZipchar(10),cFaxchar(15),cPhone char(15),nPercentageChargenumber(2),nTotalPaidnumber(10,2) ;create table RecruitmentAgencies(cAgencyCodechar(4) constraint rat_pk primary key,cNamechar(20),vAddressvarchar(35),cCitychar(15),cState char(15),cZipchar(10),cPhone char(15),cFaxchar(15),nPercentageChargenumber(2),nTotalPaidnumber(10,2) ;create table College(cCollegeCodechar(4) constraint ct_pk primary key,cCollegeNamechar(30) not null,vCollegeAddress varchar(35),cCity char(20),cStatechar(20),cZipchar(10),cPhonechar(15) ;create table CampusRecruitment(cCampusRecruitmentCodechar(4) constraint cr_pk primary key,cCollegeCodechar(4) references College(cCollegeCode),dRecruitmentStartDatedate,dRecruitmentEndDate date) ;create table InternalJobPosting(cInternalJobPostingCodechar(4) constraint ijp_pk primary key,cPositionCodechar(4), /* references, Position(cPositionCode), */nNoOfVacanciesnumber(3) not null,vRegionvarchar(20),dNoticeReleaseDate date not null,dDeadlinedate) ;create table InternalCandidate(cCandidateCode char(6) ,cEmployeeCodechar(6) constraint emp_unq unique, cInternalJobPostingCodechar(4) references InternalJobPosting(cInternalJobPostingCode),cPositionCodeAppliedForchar(4) references Position(cPositionCode) ,dDateOfApplicationdate,dTestDatedate,nTestScorenumber(3),dInterviewDatedate,cInterviewerchar(25),vInterviewCommentsvarchar(256),cRatingchar,cStatuschar,constraint ict_pk primary key(cCandidateCode,cEmployeeCode,cInternalJobPostingCode) ;create table Employee(cEmployeeCodechar(6) constraint etv_pk primary key,vFirstNamevarchar(20),vLastNamevarchar(20),cCandidateCodechar(6),vAddressvarchar(35),cCitychar(20),cStatechar(20),cZipchar(10),cCountryCodechar(3) references Country(cCountryCode),cPhonechar(15),vQualificationvarchar(20),dBirthDatedate,cSexchar,cCurrentPositionchar(20),cDesignationchar(20),cEmailIdchar(20),cDepartmentCodechar(4) references Department(cDepartmentCode),cRegionchar(20),vPhotovarchar(30),dJoiningDatedate,dResignationDatedate,cSocialSecurityNochar(15) unique,cSupervisorCode char(6) ;create table MonthlySalary(cEmployeeCodechar(6)references Employee(cEmployeeCode),nMonthlySalarynumber(10,2),dPayDatedate,nReferralBonusnumber(10,2),constraint mst_pk primary key(cEmployeeCode,dPayDate);create table AnnualSalary(cEmployeeCodechar(6) references Employee(cEmployeeCode),nAnnualSalarynumber(10,2),nYearnumber(4),constraint ast_pk primary key(cEmployeeCode,nYear);create table ExternalCandidate(cCandidateCodechar(6) constraint ectv_pk primary key,vFirstNamevarchar(20),vLastNamevarchar(20),vAddressvarchar(35),cCitychar(20),cStatechar(15),cZipchar(10),cCountryCodechar(3) references Country(cCountryCode),cPhonechar(15),cPositionCodechar(4) references Position(cPositionCode),dDateOfApplicationdate,cEmployeeReferralNochar(6),cNewsAdNochar(4) references NewsAd(cNewsAdNo),cAgencyCodechar(4) references RecruitmentAgencies(cAgencyCode),cContractRecruiterCodechar(4) references ContractRecruiter(cContractRecruiterCode),cJobFairCodechar(4) references JobFair(cJobFairCode),cCampusRecruitmentCodechar(4) references CampusRecruitment(cCampusRecruitmentCode),cExEmployeeCodechar(6),vQualificationvarchar(20),nPrevWorkExperiencenumber,dBirthDatedate,cSexchar,cCollegeCodechar(4),nPrevAnnualSalarynumber(10,2),vPhotograph varchar(30),vEmailIdvarchar(20),cStatuschar,dTestDatedate,nTestScorenumber(3),dInterviewDatedate,cInterviewerchar(20),vInterviewCommentsvarchar(256),nRatingnumber(2);create table EmployeeReferrals(cEmployeeReferralNochar(6) constraint ert_pk primary key,cEmployeeCodechar(6) references Employee(cEmployeeCode),cCandidateCodechar(6) references ExternalCandidate(cCandidateCode) ;create table Skill(cSkillCodechar(4) constraint stv_pk primary key,vSkillvarchar(35);create table EmployeeSkill(cEmployeeCodechar(6) references Employee(cEmployeeCode),cSkillCodechar(4)references Skill(cSkillCode),constraint vest_pk primary key(cEmployeeCode,cSkillCode);create table Requisition(cRequisitionCode char(6),cPositionCode char(4)references position(cPositionCode),dDateofRequisitiondate,dDeadline date,cDepartmentCodechar(4),vRegionvarchar(20),nNoOfVacancynumber(3),constraint RTP_PK primary key(cRequisitionCode,cPositionCode) ;create table CandidateSkill(cCandidateCodechar(6) references ExternalCandidate(cCandidateCode),cSkillCodechar(4) references Skill(cSkillCode),constraint sctv_pk primary key(cCandidateCode,cSkillCode);create table PositionSkill(cPositionCodechar(4) references Position(cPositionCode),cSkillCodechar(4) references Skill(cSkillCode),constraint pstv_pk primary key(cPositionCode,cSkillCode);create table Payment(cSourceCodechar(6) ,nAmountnumber(10,2),cChequeNochar(12),dDatedate,constraint tp_pks primary key(cSourceCode,cChequeNo,dDate);create table Rating(cCandidateRating char(10), nLowerRating number(2), nHigherRating number(2);insert into recruitmentusers values (Sandra,Adams);insert into recruitmentusers values (William,Perez);insert into recruitmentusers values (David,Cooper);insert into department values(0001,Production,Samuel Moore,New York);insert into department values(0002,Sales,Donald Fleming,Houston);insert into department values(0003,Marketing,Sandra Hawkins,Addison);insert into department values(0004,Human Resource,Bill Mayse,Abilene);insert into department values(0005,Quality Assurance,Robin Dmello,Hampstead);insert into department values(0007,Training,Will Smith,Columbus);insert into department values(0008,Transportation,Dabb Mike,Columbus);insert into department values(0009,Finance,Faber Michel,New Jersy);insert into department values(0010,Inventory,Taber James,New Jersy);insert into position values(0001,Sales Manager,100,1997,82 );insert into position values(0002,Maketing Manager,100,1997,83 );insert into position values(0003,Financial Analyst,50 ,1997,30 );insert into position values(0004,Training Co-ord,20 ,1997,15 );insert into position values(0005,Database Analyst,10 ,1997,8 );insert into position values(0006,Staff Accountant,20 ,1997,18 );insert into position values(0007,Audit Manager,20 ,1997,18 );insert into position values(0008,Telephone Operator,20 ,1997,18 );insert into position values(0009,Office Clerk,20 ,1997,18 );insert into position values(0010,Legal Secretary,20 ,1997,18 );insert into position values(0011,Administrative Assistant,20 ,1997,18 );insert into position values(0012,Senior Receptionist,20 ,1997,18 );insert into position values(0013,Consultant,20 ,1997,18 );insert into position values(0014,Maintenance Technician,20 ,1997,18 );insert into position values(0015,Receptionist,21,1997,18 );insert into position values(0016,Sales Executive,20,1997,18 );insert into Country values(001,United States of America);insert into Country values(002,Albania);insert into Country values(003,Andorra);insert into Country values(004,Argentina);insert into Country values(005,Austria);insert into Country values(006,Bangladesh);insert into Country values(007,Belgium);insert into Country values(008,Bosnia);insert into Country values(009,Bulgaria);insert into Country values(010,Czech Republic);insert into Country values(011,Denmark);insert into Country values(012,Yugoslavia );insert into country values(013,France);insert into country values(014,Gibraltar);insert into country values(015,Greece);insert into country values(016,Georgia);insert into country values(017,Croatia);insert into country values(018,Estonia);insert into country values(019,Iceland);insert into country values(020,Italy);insert into country values(021,Israel);insert into country values(022,India);insert into country values(023,Cyprus);insert into country values(024,Russia);insert into country values(025,Liechtenstein);insert into country values(026,Great Britain);insert into country values(027,Lithuania);insert into country values(028,Luxembourg);insert into country values(029,Yugoslavia);insert into country values(030,Malta);insert into country values(031,Moldova);insert into country values(032,Monaco);insert into country values(033,Netherlands);insert into country values(034,Norway);insert into country values(035,Poland);insert into country values(036,Portugal);insert into country values(037,Romania);insert into country values(038,San Marino);insert into country values(039,Slovak Republic);insert into country values(040,Slovenia);insert into country values(041,Finland);insert into country values(042,Spain);insert into country values(043,Switzerland);insert into country values(044,Turkey);insert into country values(045,Ukraine);insert into country values(046,Hungary);insert into country values(047,Germany);insert into country values(048,Sweden);insert into country values(049,Latvia);insert into country values(050,India);/* data for newspaper*/insert i