数据库设计及应用

数据(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 *

from PROF

示例2 Select (all):不去重复元组；Select distinct:去重复元组；r.*:表r的全部属性

示例3 找出工资低于500的职工的姓名、工资、系别

select PNAME , 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

第二范式：若RÎ1NF，且每个非主属性完全依赖于码，则称RÎ2NF

数据库作用？外键约束性？

这是咱们做过的实验，参考下吧

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,name

6查询所有课以及这门课的最高成绩，test2_06有两个列：课程号cid、最高成绩max_score

create table test2_06 as select cid,max(score) as max_score from pub.student_course group by cid

7查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name

create 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_count

create 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、score

create 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.sid

10查所有有成绩记录的学生sid和cid

.create table test2_10 as select sid,cid from pub.student_course

本文来源：https://www.2haoxitong.net/k/doc/4c32898a760bf78a6529647d27284b73f24236d9.html