数据库(database).ppt

Lecturer： 周晓慧,Fundamentals of Software Technology Part 3 Database,assignment p112: 12, 15,16,17,19 p267: 一. From DB to 索引 二. From 1 to 14, and 16, 24, 26, 38, 40, 42 四. 8.,Chapter 1 数据库管理系统的结构 (Database management system architecture) 一 . 为什么要数据库 Database system versus file system 数据冗余和数据不一致 文件系统存在数据冗余，从而导致数据的不一致 Student ID student name course score 年龄 200161120 李平 C 语言 90 19 200161120 李平 C+ 80 19 200161121 张三 C 语言 65 18 200161121 张三 C+ 78 18 200161122 王五 C+ 88 230,数据完整性问题 文件系统不能保证数据的完整性，而存在数据库中的数据可以满足一定的约束。有三类数据完整性约束 a. 用户定义的数据完整性 用户要求存在数据库中的数据要满足一定的要求。 For example, age must be never beyond 220。 定义完age220后，在插入age数据到数据库时，数据库系统（是一软件）会自动判断age是否小于220，若否，数据库系统报警，不让插入。 b. 实体完整性 主码不能为空,并且唯一，因为一实体表示一客观存在，该客观存在是唯一的，所以主码不能为空，并且唯一。 见操作,数据完整性问题 存在数据库中的数据要求满足一定的约束。 c. 参照完整性 主码不能为空,并且唯一，因为一实体表示一客观存在，该客观存在是唯一的，所以主码不能为空，并且唯一。 见操作,一 . 为什么要数据库 迸发操作的异常（Concurrent-access anomalies） 文件系统中，当多个用户对同一数据进行迸发修改时，产生数据异常。 Bus from city to city amount (票的数量) 杭州 到 上海 1 杭州 到 北京 30 Two customers read the number of tickets from Hangzhou to Shanghai is one almost in the same time. Then, each of them gets a ticket from Hangzhou to Shanghai with the same seat number. Finally, they will fight on the bus for that seat.,程序对数据的依赖（Program independent on data） 文件系统中，程序与数据是不独立的。 For example, fopen( c:zhouletter.doc, “rw” ); 文件名或文件路径发生变化, fopen的第一个参数就要重写。,迸发控制 文件系统中，多个用户对同一数据操作，数据可能产生数据不一致。 如，前页PPT的例子中，系统实现卖出一张票的程序如下： amount = amount 1; /设amount的初值为8。 假设有2个迸发操作，即有2位客户各买了一张票。 P1 P2 amount = amount 1; amount = amount 1; 由于P1、P2执行顺序的问题,amount的值可能为7，6,数据恢复 文件系统中，机器突然掉电，数据可能丢失或产生数据不一致。 如，超市销售： Supermarket_amount为超市的销售总金额， person_amount为个人信用卡总金额 个人采购金额为：500元 则，销售程序为： . . . Supermarket_amount = Supermarket_amount + 500; /语句 1 person_amount = person_amount 500; /语句 2 执行完语句1，机器突然掉电，谁吃亏了？,数据库基本概念 1. 数据库（DataBase，简称DB） 数据库是长期存放在计算机内、有组织、可共享的数据集合，可供多用户共享，数据库中的数据按一定的数据模型组织、描述和储存，具有尽可能小的冗余度和较高的数据独立性和易扩展性，并可为各种用户共享。,2. 数据库管理系统（DBMS） 是软件，如ORACLE、SQL-SERVER、ACESS等 数据库系统的核心组成部分，对数据进行管理的软件。 主要功能： 数据定义 :对数据库中的数据对象（包括表、视图、索引等）进行定义 。 数据操纵 ：对数据库操作 查询 插入 删除 修改等 数据库的运行管理：对数据库的建立、运用和维护进行统一管理和控制 安全性控制、完整性控制、并发控制、备份与恢复 数据库的建立和维护功能 数据库初始数据的输入和转换功能、数据库的转储和恢复功能以及数据库重组织功能和性能监视、分析功能等。,3. 数据库应用系统 在计算机系统中引入数据库后的系统构成 组成：数据库（及相关硬件）、数据库管理系统及开发工具、应用系统、数据库管理员和用户等。 数据库管理员(DataBase Administrator,简称DBA),数据库应用系统 示意图,数据库在计算机系统中的地位示意图,table students Student ID student name age sex 200161120 李平 19 male 200161121 张三 18 female 200161122 王五 230 male table courses Course ID course prerequisite 0001 C 语言 0002 C+ table student_course Student ID Course ID score 200161120 0001 90 200161120 0002 80 200161121 0001 65 200161121 0001 78,4. 数据库应用系统的结构（Architecture）,5. 数据库系统结构,数据库的三级模式结构,数据库系统体系结构 集中式数据库系统：数据是集中的，数据的管理也是集中的 客户/服务器数据库系统：数据是集中的，应用分布 分布式数据库系统 ：数据分布、应用分布,分布式数据库系统,客户/服务器数据库系统,数据模型(Data model): 描述数据和数据间的联系(数据结构), 描述的方法有：层次模型、网状模型、关系模型和面向对象的数据模型，既存在数据库中的数据是结构化的。,层次模型（树） 要通过链表自己确定查询路径,网状模型（图） 要通过链表自己确定查询路径,table students Student ID student name age sex 200161120 李平 19 male 200161121 张三 18 female 200161122 王五 230 male table courses Course ID course prerequisite 0001 C 语言 0002 C+ table student_course Student ID Course ID score 200161120 0001 90 200161120 0002 80 200161121 0001 65 200161121 0001 78,表（用表来描述数据和数据间的联系） DBMS会确定查询路径,模式（Schema）: 描述整个数据库的结构 (对关系数据库而言，所有表的表定义， e.g. table students, course, and students_courses). 子模式（Subschema）: portion of the database pertinent to a particular users needs. What Program developers see the schema, e.g. maybe portion of the a table. 物理模式（Physical schema）: 存储模式（Stored schema）, designed by DBA (database administrator). Application developer and user dont know where and how schema stored in the disk.,数据的独立性（Data independency） 当数据库中的模式或物理模式发生变化时，程序不变或变化很少。（When the schema or physical schema changed, but the programs are not changed or changed a little）. 逻辑独立性（Logical independency） 当数据库中的模式变化时，子模式不变或变化很少，从而基于子模式的程序不变或变化很少。通过物理模式与模式之间的映射实现。 （While the schema in a database is changed, but the subschema is not changed or changed a little. This leads to the programs based on the subschema not changed or changed a little. ）,物理独立性（Physical independency） 当数据库中的物理模式发生变化，但，模式不变或变化很少，从而子模式不变或变化很少，导致程序不变或变化很少。通过物理模式与模式之间的映射实现。 （While the physical schema in a database is changed, but the schema is not changed. This leads to the programs based on the subschema not changed.） 分布式数据库（Distributed database）: a database spread over several machines in a network,6. Transaction (事务） 事务是原子操作（一段访问数据库数据的程序），要么这段程序全部执行完，要么一句都不执行，一保证数据库中数据的完整性。 Each transaction must specify as its final action either commit(i.e., complete successfully) or roll back (i.e., terminate and undo all the actions carried out thus far) start transaction; my_account = my_account - 200; supermarket_account = supermarket_account + 200; commit; if errors occur then rollback;,三. 结构化查询语言SQL,SQL（Structured Query Language,结构化查询语言）: 国际标准数据库语言 SQL语言的基本概念及特点 SQL数据定义 SQL数据查询 SQL数据更新 SQL数据控制,1、SQL语言的基本概念及特点,SQL的发展 1986: SQL(ANSI) 1989: SQL89(ISO) SQL92 (SQL2)-面向关系数据库 SQL99 (SQL3)面向对象关系数据库 SQL的特点 综合统一 高度非过程化 面向集合的操作方式 以同一种语法结构提供两种使用方式 交互式SQL 、嵌入式SQL 语言简洁、易学易用,SQL数据库的体系结构,1、SQL语言的基本概念及特点,SQL语句类型 DDL:数据定义语言 定义数据库、定义表、定义索引等 DML :数据操纵语言 查询、插入、删除、更新 DCL :数据控制语言 授权，事务控制等,1、SQL语言的基本概念及特点,SQL语句汇总,1、SQL语言的基本概念及特点,2、 SQL数据定义,SQL提供的基本数据类型,数值型 通常用于定义取值为数值的属性如年龄、成绩、工资等。数值型的类型名有： INTEGER 长整数(也可写成INT) SMALLINT 短整数 REAL 取决于机器精度的浮点数 DOUBLE PRECISION 取决于机器精度的双精度浮点数 FLOAT（n） 浮点数,精度至少为n位数字 NUMERIC（p,d） 定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d位数字(也可写成DECIMAL(P，d)或DEC(P，d),字符串型 通常用于定义取值为字符串的属性如学号、姓名、课程名称等。字符串型的类型名有： CHAR（n） 长度为n的定长字符串 VARCHAR(n) 具有最大长度为n的变长字符串 时间型 通常用于定义取值为日期或时间的属性如出生年月、交易时间等。时间型的类型名有： DATE 日期，包含年、月、日，形式为YYYY-MM-DD TIME 时间，包含一日的时、分、秒，形式为HH:MM:SS,SQL提供的基本数据类型,基本表创建,CREATE TABLE （ ， ， ）； ：所要定义的基本表的名字 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件，如：NOT NULL(非空)、UNIQUE(唯一性)、CHECK(条件表达式)（列取值范围）等。 ：涉及一个或多个属性列的完整性约束条件 ，如：PRIMARY KEY(定义主码)、FOREIGN KEYREFERENCES(定义外码及参照完整性)、CHECK(条件表达式) (各列上数据必须符合的关联条件)等。,样例数据库教学管理数据库,S(SNO，SNAME，AGE，SEX) (学生信息表) SC(SNO，CNO，GRADE) (学生选课表) C(CNO，CNAME，TEACHER) (课程信息表),例,CREATE TABLE S(SNO CHAR（5）NOT NULL， /类型为定长字符串，不允许取空值 SNAME CHAR（8）， AGE INTEGER， /类型为整型 SEX CHAR（2）NOT NULL CHECK (SEX IN (男,女), /SEX不为空，取值只能为男或女 PRIMARY KEY(SNO)）； /指明SNO为主码 CREATE TABLE C(CNO CHAR（5）NOT NULL， /类型为定长字符串，不允许取空值 CNAME CHAR（20）， CREDIT INTEGER， / CREDIT类型为整型 PRIMARY KEY(CNO)）； /指明CNO为主码 CREATE TABLE SC ( SNO CHAR（5）NOT NULL， CNO CHAR（5）NOT NULL， GRADE REAL， / GRADE类型为实型 PRIMARY KEY(SNO, CNO)； /指明SNO，CNO为主码 FOREIGN KEY SNO REFERENCES S, /指明SNO为外码 FOREIGN KEY CNO REFERENCES C ) /指明CNO为外码,基本表修改,ALTER TABLE ADD DROP MODIFY ； ：指定需要修改的基本表名 ADD子句：用于增加新列、定义新列的类型和新列的完整性约束。 DROP子句：用于删除指定的完整性约束。 MODIFY子句：用于修改原有的列定义，可以修改列名，列的数据类型。,基本表删除,DROP TABLE ,3、 索引,建立索引是加快查询速度的有效手段 建立索引 DBA或表的属主（即建立表的人）根据需要建立 有些DBMS自动建立以下列上的索引 PRIMARY KEY, UNIQUE 维护索引 DBMS自动完成 使用索引 DBMS自动选择是否使用索引以及使用哪些索引,4、SQL数据查询,查询是数据库的核心操作。 SQL只提供一条查询语句SELECT，但该语句功能丰富，使用方法灵活，可以满足用户的任何查询要求。 使用SELECT语句时，用户不需指明被查询关系的路径，只需要指出关系名、查询什么、有何附加条件即可。,SELECT语句的基本语法,SELECT A1，A2，Ak FROM R1 ，R2 ，Rm WHERE F,F（条件）中可使用的运算符 算术比较运算符：，=，=， 或 != 逻辑运算符：AND，OR，NOT。 聚合函数：AVG(平均值)，MIN(最小值)，MAX(最大值)，SUM(和)，COUNT（计数）,样例数据库教学管理数据库,S(SNO，SNAME，AGE，SEX) (学生信息表) SC(SNO，CNO，GRADE) (学生选课表) C(CNO，CNAME，TEACHER) (课程信息表),单表查询 要查询的信息来自一张表或视图,查询指定列 查询全部列,查询全体学生的学号与姓名。 SELECT SNO,SNAME FROM S,查询全体学生的详细记录。 SELECT * FROM S “*”表示选择表中的所有列进行输出。,查询经过计算的值 消除取值重复的行,查全体学生的姓名及其出生年份。 SELECT SNAME, 2006-AGE FROM S,SELECT DISTINCT SNO FROM SC,单表查询 要查询的信息来自一张表或视图,带搜索条件（WHERE子句）的查询,比较条件 通常用于比较大小，可以使用比较运算符有：=(等于)，(大于)，=(大于等于)，(不等于)，! (不大于)，! (不小于)，还可用逻辑运算符NOT与比较运算符共同构成比较条件。,检索学习课程号为C2的学生学号与成绩。 SELECT SNO，GRADE FROM SC WHERE CNO=C2,单表查询 要查询的信息来自一张表或视图,带搜索条件（WHERE子句）的查询,单表查询 要查询的信息来自一张表或视图,多重条件 通常用于查询满足多个条件即复合条件的元组，可以使用逻辑运算符AND和 OR来联结连接多个查询条件。在多条件逻辑表达式中，AND的优先级高于OR，也可以用括号改变优先级。,检索年龄在20岁与22岁之间、性别为男的学生学号与姓名。 SELECT SNAME，AGE FROM S WHERE ( 20 = AGE ）AND （ AGE =22 ) AND SEX=男,多表查询,要查询的信息来自多个表的连接查询 在FROM子句中指出各个关系的名称，在WHERE子句里正确指出联接连接条件即可 如果不同关系中具有相同的属性名，为避免混淆必须用“关系名.属性名”指定属性属于哪一个关系。,例子,查询选修课程号为C2的学生的学号与姓名。 SELECT S.SNO，SNAME FROM S，SC WHERE S.SNO=SC.SNO AND CNO=C2,检索选修课程名为MATHS学生的学号和姓名。 SELECT S.SNO，SNAME FROM S，SC，C WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND CNAME=MATHS,附加子句,对SELECT语句，还可以附加某些子句，以实现某些特殊功能如对查询结果进行分组、统计或对查询结果以指定形式进行排序。 聚集函数 GROUP BY ORDER BY子句,聚集函数,对查询结果进行统计 计数函数COUNT() 统计字段名所在列的行数。一般用COUNT（*）表示计算查询结果的行，即元组的个数。 求和函数SUM() 对某一列的值求和（必须是数值型字段）。 计算平均值AVG() 对某一列的值计算平均值（必须是数值型字段）。 求最大值MAX() 找出一列中的最大值。 求最小值MIN() 找出一列中的最小值。,计算选修课程名为MATHS的总人数、最高分、最低分和平均分。 SELECT COUNT(*), MAX(GRADE), MIN(GRADE), AVG(GRADE) FROM SC，C WHERE SC.CNO = C.CNO AND CNAME=MATHS,GROUP BY,按指定项目对元组分组，通常分组项目为属性，并要求将该属性列在查询结果列表中，否则分不清统计结果属于哪一组。聚集函数通常与分组子句联合使用，以细化聚集函数的作用对象。,求各个课程号及相应的选课人数。 SELECT CNO，COUNT(SNO) FROM SC GROUP BY CNO；,求每个学生的姓名、该学生选课门数和平均分。 SELECT SNAME，COUNT(*), AVG(GRADE) FROM S，SC WHERE SC.SNO = S.SNO GROUP BY SNAME,ORDER BY子句,ORDER BY子句可以指出对查询结果进行排序。用属性名或查询结果的列序号指定排序关键字。DESC表示降序，ASC表示升序。系统默认为升序。允许多重排序。,查询选修了课程名为“数据结构”的学生的学号及其成绩，查询结果按分数降序排列。 SELECT SNO，GRADE FROM SC, C WHERE CNAME= 数据结构 AND C.CNO=C.CNO ORDER BY GRADE DESC；,SELECT语句的完整句法,SELECT DISTINCT , FROM , WHERE GROUP BY HAVING /（分组，查询结果中的组应满足的条件） ORDER BY ASC|DESC / （查询结果排序）,5、 SQL数据更新,SQL的数据更新： 数据插人: INSERT 删除: DELETE 修改: UPDATE,数据插人: INSERT,插入一个元组 INSERT INTO () VALUES (),向基本表SC中插入一个新记录(S4，C6，90)，可用下列语句实现： INSERT INTO SC(SNO，CNO，GRADE) VALUES(S4，C6，90),删除: DELETE,从基本表中删除元组，其一般形式为： DELETE FROM WHERE；,把课程名为MATHS的成绩从基本表SC中删除。 DELETE FROM SC WHERE CNO IN (SELECT CNO FROM C WHERE CNAME=MATHS),修改: UPDATE,当需要修改基本表中记录某些列值时，用UPDATE语句实现，其一般形式为： UPDATE SET =，=， WHERE ,对基本表SC中学生的英语（课程号为C03）成绩提高10%。 UPDATE SC SET GRADE=GRADE+GRADE*0.1 WHERE CNO=C03 对基本表中女同学的成绩提高10。 UPDATE SC SET GRADE=GRADE*（1+0.1） WHERE SNO IN (SELECT SNO FROM S WHERE SEX=F),五、 视图,视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。无论从方便用户的角度，还是从加强数据库安全的角度，视图都有着极其重要的作用。 一个视图是从一个或多个关系（基本表或已有的视图）导出的关系。导出后，数据库中只存有此视图的定义（在数据字典中），但并没有实际生成此关系，因此视图是虚表。当用户使用视图时，系统才去求找对应的数据。基本表中的数据发生变化，从视图中查询得出的数据也就随之改变了。视图一经定义，就可以和基本表一样被查询和删除，但对视图的修改(插入、删除、更新)操作则有一定的限制。,视图的创建,CREATE VIEW （，,） AS ：是创建视图的名字 ：是创建视图包含的属性，可以是一列也可以是多列，当是多列时，各列之间用逗号隔开，其中某列不是单纯的属性名，可以是聚集函数或列表达式。如果视图后的列名表与SQL的列名表完全相同时，视图名后的列名表可以省略。 ：该子句的执行是对基本表进行查询，获得视图所需要的数据。,建立全体男生的视图。 CREATE VIEW boy_S AS SELECT SNAME，AGE FROM S where SEX=“男”,视图的删除,DROP VIEW ,删除全体男生的视图： DROP VIEW boy_S,Chapter 2 Relational model (p375-387) 1. SQL(Structured Query Language) (p385) SQL is used to define, alter, delete a table that called Data Definition Language. Also, SQL is used to insert data into a table, modify data in a table, or delete data from a table that called Data Manipulate Language.,Examples Create table AA_COURSES ( COURSE_ID VARCHAR2(30) not null, COURSE_NAME VARCHAR2(100) not null, PREREQUSITIVE VARCHAR2(30) ) insert into aa_course values( 0005, database, c) update aa_courses set prerequsitive = c where course_id = 0004 delete from aa_courses where coures_name = OS,Project operation -投影 运算 select s.student