数据库设计与应用.doc

数据(Data)是数据库中存储的基本对象，是描述事物的符号记录。其种类包括：文字、图形、图像、声音。其特点：数据与其语义是不可分的。数据结构：逻辑结构：级2数据之间存在的逻辑关系。如表、树、图、数组 物理结构：数据在计算机内的存储方式，如顺序方式、链接方式数据库：人们收集并抽取出一个应用所需要的大量数据之后，应将其保存起来以供进一步加工处理，进一步抽取有用信息。数据库的特征：数据按一定的数据模型(网状，层次，关系型）组织、描述和储存；可为各种用户共享；冗余度较小（便于保持数据的一致性)；数据独立性较高；易扩展数据库管理系统（Database Management System，简称DBMS）由一个互相关联的数据的集合和一组用以访问这些数据的程序组成。是位于用户与操作系统之间的一层数据管理软件。DBMS的用途：科学地组织和存储数据、高效地获取和维护数据。主要功能：数据定义功能，即提供数据定义语言(DDL)，定义数据库中的数据对象；数据操纵功能（DML)：如插入删除等；数据库的运行管理功能；数据库的建立维护功能。数据库系统（Database System，简称DBS）是指在计算机系统中引入数据库后的系统构成在不引起混淆的情况下常常把数据库系统简称为数据库。由数据库、数据库管理系统、应用系统（及其开发工具） 、数据库管理员（和用户）构成。数据库的特点：面向全组织的复杂的数据结构；具有较高的数据和程序的独立性；数据的冗余度小，易扩充；统一的数据控制功能，数据共享程度高型是对数据的结构和属性的说明-模式值是型的一个具体赋值 -实例数据库三级模式外模式(Sub-Schema 子模式)：用户的数据视图，是数据的局部逻辑结构，模式的子集可以有多个模式(Schema)：所有用户的公共数据视图，是数据库全体数据的全局逻辑结构和特性的描述只有一个内模式(Storage Schema 存储模式)：是数据的物理结构及存储方式，只有一个数据抽象：物理层：最低层次的抽象，描述数据如何存储 逻辑层：描述数据及数据间的关系 视图层：描述整个数据库的某一部分，使用户与系统交互更简单数据模型：描述数据、数据联系、数据语义以及一致性约束的概念工具的集合三大数据模型：关系模型：用二维表来表示实体及其相互联系；层次模型：用树结构表示实体之间联系的模型叫层次模型；网状模型：是一个满足下列条件的有向图：可以有一个以上的节点无父节点；至少有一个节点有多于一个的父节点（排除树结构）事务是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位。事务具有原子性（事务包含的所有操作，要么做完，要么不做），一致性（事务开始前和事务结束后，数据库都保持一致性），隔离性（对于两个事），持久性（即使发生故障，也无法改变其永久性）超码(superkey)：是一个或多个属性的集合，这些属性的集合可以使我们在一个关系中唯一地标识一个元组候选码（Candidate Key）：关系中的一个属性组，其值能唯一标识一个元组。若从属性组中去掉任何一个属性，它就不具有这一性质了，这样的属性组称作候选码。主码（Primary Key）：进行数据库设计时，从一个关系的多个候选码中选定一个作为主码。主码不可为空。实体完整性：关系的主码中的属性值不能为空值（若主码为空，则出现不可标识的实体，这是不容许的）空值：不知道或无意义。参照完整性：如果关系R2的外部码Fk与关系R1的主码Pk相对应，则R2中的每一个元组的Fk值或者等于R1 中某个元组的Pk 值，或者为空值。常用完整性约束：主码约束；唯一性约束；非空值约束：NOT NULL；参照完整性约束关系代数运算：基本运算：一元运算：选择、投影、更名；多元运算：笛卡儿积、并、集合差；其它运算：集合交、连接、自然连接、除、赋值空值：不知道或不存在。空值的表现：参与算术运算：结果为Null；参与比较运算：结果为Null；参与逻辑运算：1、Null or true=ture 2、Null and false=false 3、其它情况结果为null（空值是一种状态，不是一个明确的值）空值测试：is not null （例如 ：where AGE is null ，不可写为where AGE = null）易错点 ：除is not null之外，空值不满足任何查找条件；对于is null，为空则true，否则false；不会返回null；如果null参与聚集运算，则除count(*)之外其他聚集函数都忽略null；对于聚集函数，若输入集合为空，count返回0，其他返回null。例：select count(*) from SC 结果为 6例：select count(score) from SC 结果为 4（不计入那两个null）表的删除：DROP table表名命令元组的删除：delete from 表名 where 条件表达式 从表中删除符合条件的元组，如果没有where语句，则删除所有元组建立索引格式：create cluster index s-index on S（SN）（可以动态地定义索引，即可以随时建立和删除索引；一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率）SQL基本语句格式SELECT ALL|DISTINCT <目标列表达式> ，<目标列表达式> FROM <表名或视图名>，<表名或视图名> WHERE <条件表达式> GROUP BY <列名1> HAVING <条件表达式> ORDER BY <列名2> ASC|DESC GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用聚集函数HAVING短语：筛选出只有满足指定条件的组ORDER BY子句：对查询结果表按指定列值的升序或降序排序WHERE中的运算符算术比较：< , <= , > ,>= , = ,<> 或 !=逻辑：and , or , not （不使用 ，不支持）集合成员资格： in , not in 谓词：exists, not exists, all, some, unique， between . and . , not between. and ., like , not like , is null，is not null聚集函数：avg, min , max , sum , count.集合： union , intersect , except /minus常用SQL语句示例：示例1 给出所有老师的信息 select * fromPROF示例2 Select (all):不去重复元组；Select distinct:去重复元组；r.*:表r的全部属性示例3 找出工资低于500的职工的姓名、工资、系别 selectPNAME , SAL , DNAME from PROF , DEPT where SAL < 500 and PROF.DNO = DEPT.DNO 示例4 列出姓名以“张”打头的教师的所有信息 select * from PROF where PNAME like “张%”示例5 列出姓名中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的教师的所有信息 select * from PROF where PNAME like “% d ” escape “”示例示例6 按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列 select DNAME，PNAME from PROF，DEPT where PROF.DNO = DEPT.DNO order by DNAME asc，PNAME desc注：当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示示例7：列出每一年龄组中男学生（超过50人）的人数 select AGE，count(S#) from S where SEX = M group by AGE having count(*) > 50示例8 查询有3门以上课程是90分以上的 学生的学号及（90分以上的）课程数 SELECT Sno, COUNT(*) FROM SC WHERE score>=90 GROUP BY Sno HAVING COUNT(*)>=3;集合操作：集合并：union 集合交：intersect 集合差： except(minus)提示集合操作自动去除重复元组，如果要保留重复元组的话，必须用all关键词指明测试集合成员资格 in ；测试集合是否为空 exists ；测试集合是否存在重复元组 unique视图的特点：虚表，是从一个或几个基本表（或视图）导出的关系；只存放视图的定义，不会出现数据冗余；基表中的数据发生变化，从视图中查询出的数据也随之改变；查询时,视图名可以出现在任何关系名可以出现的地方；视图的定义方式不支持递归定义。简单属性：不可再分的属性如学号、年龄、性别复合（Composite）属性：可以划分为更小的属性。如电话号码=区号+本地号码；出生日期=年+月+日单值属性：每一个特定的实体在该属性上的取值唯一。如学生的学号，年龄、性别、系别等多值属性：某个特定的实体在该属性上有多于一个的取值。如学生（学号，联系电话）（多值用双圈表示；派生属性用虚圈表示）弱实体集的分辨符用下划虚线标明双横线表示全部参与，单横线表示部分参与ER图示例：示例1示例2范式：范式是对关系的不同数据依赖程度的要求第一范式：如果关系模式R所有属性都源自原子域，称模式属于1NF第二范式：若R1NF，且每个非主属性完全依赖于码，则称R2NF数据库作用？外键约束性？这是咱们做过的实验，参考下吧1找出没有选修任何课程的学生的学号、姓名。create table test2_01 as select sid,name from pub.student where sid not in (select sid from pub.student_course)2找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名create table test2_02 as select sid,name from pub.student where sid in(select distinct sid from pub.student_course where cid in (select distinct cid from pub.student_course where sid=200900130417)3找出至少选修了一门其先行课程号为“300002”号课程的学生的学号、姓名。create table test2_03 as select sid,name from pub.student where sid in (select distinct sid from pub.student_course where cid in (select distinct cid from pub.course where fcid=300002)4找出选修了“操作系统”并且也选修了“数据结构”的学生的学号、姓名。create table test2_04 as select sid,name from pub.student where sid in (select distinct sid from pub.student_course where cid=(select cid from pub.course where name=操作系统) intersect (select distinct sid from pub.student_course where cid=(select cid from pub.course where name=数据结构)5查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score) Test2_05有四个列，并且列名必须是：sid、name、avg_score、sum_score。通过下面方式实现列名定义create table test2_05 as select sid,name，（表达式） avg_score,（表达式） sum_score from 答案1 create table test2_05 as (select tone.sid,name,round(avg(score) as avg_score,sum(score) as sum_score from pub.student tone,pub.student_course ttwo where age=20 and tone.sid=ttwo.sid group by tone.sid,name)答案2 create table test2_05 as select pub.student.sid,name,round(avg(score) as avg_score,sum(score) as sum_score from pub.student,pub.student_course where age=20 and pub.student.sid=pub.student_course.sid group by pub.student.sid,name6查询所有课以及这门课的最高成绩，test2_06有两个列：课程号cid、最高成绩max_scorecreate table test2_06 as select cid,max(score) as max_score from pub.student_course group by cid7查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名namecreate table test2_07 as select sid,name from pub.student where name not like 张% and name not like 李% and name not like 王%8查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08有两个列：second_name、p_countcreate table test2_08 as select substr(name,0,1) as second_name,count(*) as p_count from pub.student group by substr(name,0,1)9查询选修了300003号课程的学生的sid、name、scorecreate table test2_09 as select tone.sid,name,score from pub.student tone,pub.student_course ttwo where ttwo.cid=300003 and tone.sid=ttwo.sid10查所有有成绩记录的学生sid和cid.create table test2_10 as select sid,cid from pub.student_course