数据仓库实践系列课程(1)——数据库基础与SQL.pptx
- 文档编号:15122965
- 上传时间:2023-07-01
- 格式:PPTX
- 页数:79
- 大小:3.41MB
数据仓库实践系列课程(1)——数据库基础与SQL.pptx
《数据仓库实践系列课程(1)——数据库基础与SQL.pptx》由会员分享,可在线阅读,更多相关《数据仓库实践系列课程(1)——数据库基础与SQL.pptx(79页珍藏版)》请在冰点文库上搜索。
数据仓库实践系列课程数据库基础与SQL,文思海辉,课程说明,Pactera.Confidential.AllRightsReserved.,2,01,02,03,04,数据库基本概念,关系代数,SQL基础,练习,数据库基本概念,数据库发展的历史,1963年:
美国Honeywell公司IDS(IntegratedDataStore)系统投入运行1965年:
美火箭公司用IDS帮助设计阿波罗登月火箭1968年:
IBM公司:
层次IMS1969年:
美CODASYL:
网状DBTG标准1970年:
IBM的E.F.Codd(EdgarFrankCodd)提出关系模型(1981)20世纪70年代以层次、网状为主流20世纪80年代关系系统逐渐代替层次与网状模型,目前流行的数据库,IBM公司的DB2、Netezza,微软公司的SQLServer产品,Oracle公司的Oracle、Exadata,Greenplum公司的GP,TD公司的Teradata,数据模型与概念模型,数据模型与概念模型,信息的三种描述,现实世界事物个体特征事物间联系,信息世界实体属性概念模型,机器世界记录字段数据模型,现实世界:
即客观世界,产生最原始的数据。
信息世界:
是现实世界在人们头脑中反映并用文字或符号记载下来,是人对现实世界的认识抽象过程,经选择、命名、分类等抽象工作后进入信息世界。
机器世界:
用数据模型描述现实世界中的事物及其联系。
概念模型,示例:
学生选修课程,数据模型,数据模型分类:
非关系模型(层次、网状)、关系模型、面向对象模型非关系模型实体:
记录属性:
数据项(字段)联系:
记录之间的联系非关系模型中数据结构的单位是基本层次联系。
基本层次联系:
两个记录以及它们之间的一对多(包括一对一)的联系。
关系模型建立在数据概念的基础上,数据的逻辑结构是一张二维表,由行和列组成。
关系模型,学生,课程,选修,关系模型,关系数据模型实体以及实体之间的联系都是用关系来表示的。
关系:
二维表(规范化的)元组:
行属性:
列(属性名唯一)主码:
唯一确定一个元组的属性组。
域:
属性的取值范围。
分量:
元组中的一个属性值。
关系模式:
对关系的描述。
关系名(属性名1,属性名2,),课程说明,Pactera.Confidential.AllRightsReserved.,12,01,02,03,04,数据库基本概念,关系代数,SQL基础,练习,关系代数,笛卡尔积:
给定一组域D1,D2Dn(可以完全不同,也可以部分或全部相同),D1,D2Dn的笛卡尔积为:
D1D2Dn=(d1,d2dn)|diDi,i=1,2,n,关系代数,一、关系代数运算的三个要素1、运算对象:
关系2、运算符号:
集合运算符:
、专门的关系运算符:
、算术比较符:
、逻辑运算符:
、3、运算结果:
关系,关系代数,二、专门的关系运算,1、选择选择又称为限制。
它是在关系R中选择满足给定条件的诸元组。
选择是从行的角度进行运算的。
记作:
F(R)F:
表示选择条件,是一个逻辑表达式。
2、投影投影是从R中选择出若干个属性列组成一个新的关系。
投影操作是从列的角度进行的运算。
记作:
A(R)A为R中的属性列。
投影操作后,不仅取消了原有关系中的某些列,而且还可能取消某些元组。
关系代数,3、连接连接是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。
记作:
RSAB,两种最为常用的连接:
等值连接:
为“=”的连接运算称为等值连接。
自然连接:
是一种特殊的等值连接,它要求两个关系中进行比较的分量必须是相同的属性组,并且在结果中把重复的属性列去掉。
关系代数,学生,选修,学生选修,关系代数,4、除运算:
给定关系R(X,Y)和S(Y,Z),R与S的除运算得到一个新的关系P(X),P是R中满足下列条件的元组在X属性列上的投影:
元组在X上分量值x的象集Yx包含S在Y上的投影的集合。
R,S,R/S,关系代数,例1:
查询姓名为“王一”的男生记录。
姓名=“王一”性别=“男”(学生)例2:
查询王一同学“0001”号课程成绩。
成绩(姓名=“王一”(学生)课程号=“0001”(选修)例3:
查询选修了全部课程的学生学号。
学号,课程号(选修)课程号(课程)三、五种基本的关系运算并、差、笛卡尔积、选择和投影为基本的关系运算。
其他三种运算:
交、连接和除运算均可以用这5种运算来表达。
关系数据库,关系数据库,是建立在关系数据模型基础上的数据库,借助于集合代数等概念和方法来处理数据库中的数据。
关系数据库,事务(Transaction):
是用户定义的一个数据库操作序列,这些操作要么全做,要么全不做,是一个不可分割的工作单位。
多个事务可以并行执行一个事务的执行必须保证数据库的一致性在事务执行过程中允许暂时的不一致当事务成功执行后,数据库必须是一致的当事务成功提交后,对数据库的改变应该是持久的,即使以后系统出现故障,关系数据库事务,事务的特性:
原子性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability),事务的所有操作在数据库中要么全部正确反映,要么全部不反映,事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
一个事务的执行不能被其他事务干扰。
即使事务并发也要感觉不到其它事务的存在,一个事务成功完成后,它对数据库的改变是永久性的。
关系数据库事务,事务的控制命令包括:
COMMIT(事务提交)ROLLBACK(事务回退)SAVEPOINT(设置保留点),课程说明,Pactera.Confidential.AllRightsReserved.,24,01,02,03,04,数据库基本概念,关系代数,SQL基础,练习,SQL简介,SQL(StructuredQueryLanguage)结构化查询语言是IBM公司SanJose实验室为SystemR而设计的查询语言,后被国际标准化组织(ISO)批准作为关系数据库语言的国际标准。
SQL目前遵循的是1999年标准,即SQL-99,最新的标准是SQL:
2008(比SQL99主要增加XML,Windows函数,Merger语句等)。
各数据库厂家对SQL-99标准均有扩充,扩充部分不能完全保证数据库之间的兼容性。
SQL简介,SQL可以分为:
DCL:
数据控制语言(DataControlLanguage)DDL:
数据定义语言(DataDefinitionLanguages)DML:
数据操作语言(DataManipulationLanguage),DCL数据定义语言,GRANT(授权)REVOKE(废除、撤销)SETROLE(设置角色状态),27,Pactera.Confidential.AllRightsReserved.,DDL数据定义语言,CREATE(建立)ALTER(修改)DROP(删除)TRUNCATE(截断),DDL数据定义语言,语言描述约定,:
表示可选项|:
表示选择项CREATE:
粗体表示关键字或必输项Table_name:
细体表示用户输入项创建:
蓝色表示说明,DDL数据定义语言,CREATE-创建用户CREATEUSERuser_nameIDENTIFIEDBYpasswordDEFAULTTABLESPACEtablespace_nameTEMPORARYTABLESPACEtablespace_name;创建user_name用户,密码为password,使用tablespace_name表空间,如果没有指定表空间,默认表空间为system表空间。
Oracle10g有用户默认表空间设置。
例:
CREATEUSERtestIDENTIFIEDBYtest;,DDL数据定义语言,CREATE-创建数据表CREATETABLEtable_name(Column1datatypedefaultnotnullprimarykey,constraintkey_nameprimarykey(column_list)usingindextablespacetablespace_name)tablespacetablespace_name;Datatype是数据类型:
varchar2(x),number(x,x),date,integer等。
Notnull非空限制,如果不写表示可为空。
Primarykey主键,可以紧跟在字段后,或在最后使用constraint。
未指名表空间,数据表建立在用户默认的表空间中。
例:
CREATETALBEtest(aaINTEGER,bbDATE)CONSTRAINTkey_testPRIMARYKEY(aa);,DDL数据定义语言,CREATE-创建索引CREATEUNIQUEINDEXindex_nameONtable_name(column_list)tablespacetablespace_name;在table_name表上按照column_list建立索引,索引名为index_name,索引保存在tablespace_name表空间中。
UNIQUE:
唯一性索引。
例:
CREATEINDEXincex_testONtable_test(aa);,索引在数据库中的概念和作用,索引是一种提高查找速度的机制,建立索引,就是按某个关键字段的值,升序或降序排列,建立索引文件。
索引,那么,它是如何提高查找速度的呢?
索引,学生表student,A、索引文件的内部构成,索引,以在学生表student中建立“学号”索引(升序)为例:
学生表student,索引文件,索引,由二部分组成:
索引关键字,记录号(原表),索引文件,索引,索引文件如何影响原表:
没有索引文件时:
指针在原表中顺序移动,20060101张建男1820060102田静男1820060103田静女1720060201王欢女1720060202鲜恒男19,有索引文件时:
LIST:
1、表文件打开后,记录指针总指向1#记录,且按记录号顺序移动。
但同时打开索引文件后,指针指向的是索引文件的首记录,并按照索引文件的记录顺序移动;2、索引文件改变的是对表文件的显示顺序。
(而不是表中记录的物理顺序)3、索引文件是独立文件,但不能单独使用,只与所从属的表文件配合使用。
说明:
索引,索引文件如何加快查找速度:
原因:
(1)索引后,指针在索引文件中顺序移动。
(2)索引文件中记录是有序的。
(3)有序后,可以用各种方法加快查询速度,如折半(二分)查找法,而排序前,只能顺序查找记录。
DDL数据定义语言,ALTER-修改表,ALTERTABLEtable_nameRENAMETOnew_table_name;,ALTERTABLEtable_nameADD(column1datatypedefaultnotnull,column2datatypedefaultnotnull,.);,ALTERTABLEtable_nameMODIFY(column1datatypedefaultnotnull,column2datatypedefaultnotnull,.);,ALTERTABLEtable_nameDROPCOLUMNcolumn_name;,ALTERTABLEtable_nameADDCONSTRAINTpk_namePRIMARYKEY(column_list);,ALTERTABLEtable_nameDROPCONSTRAINTpk_name;,将table_name表名修改为new_table_name。
在table_name表中增加column1,column2.字段,追加到字段最后。
修改table_name表字段属性,长度或精度不能小于原长度或精度。
删除table_name表中的column_name字段。
建立table_name表的主键,主键名pk_name,主键字段column_list。
删除table_name表的pk_name主键。
一个表只能有一个主键,DDL数据定义语言,DROP-删除DROPTABLEtable_name;删除table_name表。
DROPINDEXindex_name;删除index_name索引。
删除主键(强制限制的一种),使用ALTERTABLEtable_nameDROPCONSTRAINTpk_name;,DDL数据定义语言,TRUNCATE截断TRUNCATETABLEtable_name;截断(清空)table_name表。
TRUNCATE的速度比DELETEFROMtable_name快。
DDL小结,从ALTER和CREATE及DROP语法数量再一次证明需求是易变的。
如果增加表字段,并要求字段的排列位置,可以使用:
CREATETABLEtemp_table_nameASSELECT*FROMtable_name;DROPTABLEtable_name;CREATETABLEtable_name(column);INSERTINTOtable_nameVALUES(column_list)(SELECTcolumn_list1FROMtemp_table_name);DROPTABLEtemp_table_name;需要注意的是,删除表后,表中的索引也被删除,所以,在执行上述操作前要保留好表的索引脚本。
DML数据操作语言,SELECT(查询)INSERT(插入)UPDATE(更新)DELETE(删除),DML数据操作语言,SELECT-查询SELECTALL|DISTINCTON(expression,.)*|expressionASoutput_name,.INTOTEMPORARY|TEMPTABLEnew_tableFROMfrom_item,.WHEREconditionGROUPBYexpression,.HAVINGcondition,.UNION|INTERSECT|EXCEPTALLselectORDERBYexpressionASC|DESC|USINGoperator,.FORUPDATEOFclass_name,.LIMITcount|ALLOFFSET|,start,DML数据操作语言,DEPT部门定义表,DEPTNO是主键EMP职工表,EMPNO主键,DEPTNO外键关联DEPT,DML数据操作语言,SELECT-单表查询查询表中所有字段和所有记录,select后跟*表示所有字段SELECT*FROMDEPT;查询指定字段,在select后跟查询的字段名列表,字段间用,隔开SELECTDEPTNO,DNAMEFROMDEPT;条件查询,FROM后面使用WHERE,在WHERE中可以使用=,=,=,BETWEEN,LIKE,IN,返回WHERE条件为true的数据SELECT*FROMDEPTWHEREDEPTNO=20;-返回部门编码为20的部门数据SELECT*FROMDEPTWHEREDEPTNOBETWEEN20AND30;-返回部门编码在20到30之间的部门数据(包括20和30),DML数据操作语言,SELECT-单表查询SELECT*FROMDEPTWHEREDNAMELIKEACCOUNT%;-返回部门名称以ACCOUNT开始的部门数据SELECT*FROMDEPTWHEREDNAMELIKE%UNT%;-返回部门名称含UNT的部门数据,%全匹配符,?
单匹配符SELECT*FROMDEPTWHEREDEPTNOIN(20,30)AND(DNAMELIKE%UNT%ORDNAME=SALES);-返回部门编码等于20和30并且部门名称含UNT或等于SALES的部门数据,AND与,OR或,IN和(等同OR),DML数据操作语言,51,Pactera.Confidential.AllRightsReserved.,SELECT-多表查询,DML数据操作语言-连接,DML数据操作语言-内连接InnerJoin,使用JOIN语法,将连接条件写在ON后面或直接在where条件中写连接条件例如:
select*fromempainnerjoindeptbona.deptno=b.deptno;selecta.empno,a.ename,b.deptnoAS部门编号,b.nameas部门名称fromempa,deptbwherea.deptno=b.deptno;注意:
连接条件不一定是等号,可是是=;等运算符号字段和表名可以使用别名,方法为AS别名,或直接写别名,表如果指定别名,原表名在该查询中不能再被使用。
DML数据操作语言-左外连接LeftJoin,使用JOIN语法,将连接条件写在ON后面例如:
select*fromempaleftouterjoindeptbona.deptno=b.deptno;Anda.select*fromempa,deptbwherea.deptno=b.deptno(+),左表数据全部返回,右表无对应数据返回NULL。
DML数据操作语言-右外连接RightJoin,使用JOIN语法,将连接条件写在ON后面例如:
select*fromemparightouterjoindeptbona.deptno=b.deptno;,右表数据全部返回,左表无对应数据返回NULL。
DML数据操作语言-全外连接FullJoin,使用JOIN语法,将连接条件写在ON后面例如:
select*fromempafullouterjoindeptbona.deptno=b.deptno;,两个表数据全部返回,相互之间没有对应数据返回NULL。
注意:
Fullouterjoin的效率差于左外连接,非必要不使用,DML数据操作语言-连接,注意1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。
这时已经没有leftjoin的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
57,Pactera.Confidential.AllRightsReserved.,DML数据操作语言-笛卡尔积,无关联条件,简单的记录拼接,得到n*m条记录例如:
select*fromempa,deptb;注意:
由于没有任何关联条件,基本在关联中很少使用,缺少业务意义的支持.特殊:
将返回1条记录的结果集和另外一个结果集连接.字段和表名可以使用别名,方法为AS别名,或直接写别名,表如果指定别名,原表名在该查询中不能再被使用。
DML数据操作语言-组合查询,DML数据操作语言-集合并,UNION由每个查询选择的所有不重复的行select*fromempawherea.empnoin(7369,7499)unionselect*fromempbwhereb.empnoin(7369);UNIONALL由每个查询选择的所有的行,包括所有重复的行select*fromempawherea.empnoin(7369,7499)unionallselect*fromempbwhereb.empnoin(7369);UNIONALL的效率高于UNION.UNION需要做额外的排序和去除重复行的操作.,DML数据操作语言-集合交,INTERSECT由每个查询选择的所有不重复的相交行select*fromempawherea.empnoin(7369,7499)intersectselect*fromempbwhereb.empnoin(7369);INTERSECTALL由每个查询选择的所有的相交行,包括所有重复的行select*fromempawherea.empnoin(7369,7499)intersectallselect*fromempbwhereb.empnoin(7369);,DML数据操作语言-集合差,EXCEPT相减运算,求差集.无重复行select*fromempawherea.empnoin(7369,7499)exceptselect*fromempbwhereb.empnoin(7369);EXCEPTALL相减运算,求差集,有重复行.select*fromempawherea.empnoin(7369,7499)exceptallselect*fromempbwhereb.empnoin(7369);,63,Pactera.Confidential.AllRightsReserved.,DML数据操作语言-组合查询,UNION,UNIONALL,EXCEPT,INTERSECT,DML数据操作语言-组合查询注意事项,在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配可以用圆括号改变执行的顺序ORDERBY子句:
只能出现在语句的最后从第一个SELECT语句接收列名、别名,或者位置记号.查询结果的列名与第一个SELECT语句保持一致。
可以用于子查询。
在结果中的列名是第一个查询中出现的列名,DML数据操作语言-连接与组合查询,相同点将两个结果集合成一个结果集区别连接是字段上拼接,是横向的扩展(如果是多对多的连接也会造成记录条数的增加)组合查询是记录集的运算,是纵向的运算,DML数据操作语言-子查询,子查询:
嵌套在其他查询中的查询称子查询.子查询又称内部查询,而包含子查询的语句称之外部查询(又称主查询)。
DML数据操作语言-相关子查询与非相关子查询,-非相关子查询SELECTEMPNO,LASTNAMEFROMEMPLOYEEWHEREWORKDEPT=A00ANDSALARY(SELECTAVG(SALARY)FROMEMPLOYEEWHEREWORKDEPT=A00)子查询是一个独立的查询-相关子查询SELECTE1.EMPNO,E1.LASTNAME,E1.WORKDEPTFROMEMPLOYEEE1WHERESALARY(SELECTAVG(SALARY)FROMEMPLOYEEE2WHEREE2.WORKDEPT=E1.WORKDEPT)依赖于外部查询结果,DML数据操作语言-相关子查询与非相关子查询,非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。
故非相关子查询比相关子查询效率高,IN与EXISTS,IN与EXISTS的使用若子查询结果集比较小,优先使用in,若外层查询比子查询小,优先使用exists。
因为若用in,会优先查询子查询,然后匹配外层查询,若使用exists,则会优先查询外层表,然后再与内层表匹配。
最优化匹配原则,拿最小记录匹配大记录。
使用inSelectlast_name,titlefroms_empwheredept_idin(selectidfroms_deptwherename=Sales);使用existsSelectlast_name,titlefroms_empewhereexists(selectx-把查询结果定为constant,提高效率froms_deptswheres.id=e.dept_idands.name=Sales
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据仓库 实践 系列 课程 数据库 基础 SQL
![提示](https://static.bingdoc.com/images/bang_tan.gif)