数据库概论

数据库概论

数据库系统概述

数据库管理系统（DBMS）是位于用户和操作系统之间的一层数据管理软件。
数据库系统（DBS）是由数据库、数据库管理系统、应用程序和数据库管理人员（DBA）组成的存储、管理、处理和维护数据的系统。

两层数据模型

逻辑模型（从软件管理角度组织数据）由数据结构、数据操作、数据的完整性约束三部分组成。
物理模型（从硬件存储角度组织数据）

概念模型

一对一联系
一对多联系
多对多联系

  • 数据库系统的三级模式结构：模式、外模式、内模式。
  • 数据库系统的两级映像：外模式/模式、模式/内模式映像。

SQL Server Mangement2008简单使用及查询基本操作

  • 连接到服务器

  • 新建查询 create database db_StuInfoMangement 执行！use db_StuInfoMangement

  • create table tb_Student
    ( Sno CHAR (10) PRIMARY KEY,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    );
    建表成功！

  • 数据库->db_StuInfoMangement->表->系统表->dbo.tb_Student；

  • 增加一列数据 alter table tb_Student ADD Sphone CHAR(10) ;

  • dbo.tb_Student->右键编辑前两百行（自己输入数据）

  • 查询所有select * from student；
    查询计算机系（cs）的学生的姓名、年龄。

select sname, sage from student where sdept = ' cs '

  • 查询选修了01号课程的学生的学号和成绩。

select sno, grade from sc where cno = '01'

  • 查询成绩在70到80分之间的学生的学号、课程号和成绩。

select sno, cno, grade from tb_student where grade between 70 and 80

  • 查询计算机系（cs）年龄在18到20之间且性别为'男'的学生的姓名、年龄。

select sname, sage from student where sdept = 'cs' and ssex='男' and sage between 18 and 20

  • 查询9512101号学生的修课情况。

select * from sc where sno = '9512101'

  • 查询01号课程成绩最高的分数。

select max(grade) as max_grade from sc where cno = '01'

  • 查询学生都修了哪些课程,要求列出课程号。

select distinct cno from sc

  • 查询学生的最大的年龄和最小的年龄

select max(ssag) as 最大年龄, min(ssag) as 最小年龄 from student

*查询修了02号课程的所有学生的平均成绩、最高成绩和最低成绩。

select avg(grade) 平均成绩, max(grade) 最高成绩, min(grade) 最低成绩

from sc where cno = '02'

  • 统计每个系的学生人数。

select sdept,count(*) 学生人数 from student group by sdept

  • 统计每门课程的修课人数和考试最高分。

select cno 课程号, count(*) 修课人数, max(grade) 最高分 from sc group by cno

  • 统计每个学生的选课门数，并按选课门数的递增顺序显示结果。

select sno, count(cno) 选课门数 from sc group by sno order by 选课门数

  • 统计各系修课的学生总数和考试的平均成绩

select sdept, count(distinct sc.sno), avg(grade)
from student , sc
where student.sno = sc.sno
group by sdept

  • 查询选课门数超过2门的学生的平均成绩和选课门数。

select sno, sum(grade) 总成绩, avg(grade) 平均成绩, count() 选课门数
from sc
group by sno having count() > 2

  • 列出总成绩超过200分的学生，要求列出学号、总成绩。

select sno, sum(grade) 总成绩
from sc
group by sno having sum(grade) > 200

  • 查询选修了02号课程的学生的姓名和所在系。

select sname, sdept
from Student ， SC
where Student.Sno = SC.Sno and cno = '02'

  • 查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。

select sname, cno, grade
from student ， sc
where Student.Sno = SC.Sno and grade > 80
order by grade desc

  • 在表中填加数据

insert into student values('200215121','李勇','男',20,'CS');
insert into student values('200215122','刘晨','女',19,'CS');
insert into student values('200215123','王敏','女',18,'MA');
insert into student values('200215125','张立','男',19,'IS');
go

  • 向course表中添加数据并更新

insert into course values('1', '数据库', NULL,4);
insert into course values('2', '数学', NULL,2);
insert into course values('3', '信息系统', NULL,4);
insert into course values('4', '操作系统', NULL,3);
insert into course values('5', '数据结构', NULL,4);
insert into course values('6', '数据处理', NULL, 2);
insert into course values('7', 'PASCAL语言', NULL,4);
go
update Course set Cpno = '5' where Cno = '1';
update Course set Cpno = '1' where Cno = '3';
update Course set Cpno = '6' where Cno = '4';
update Course set Cpno = '7' where Cno = '5';
update Course set Cpno = '6' where Cno = '7';
/为表Course添加数据/
go

第一、第二、第三范式

  • 第一范式（无重复的列）
    定义：数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性
    通俗解释：一个字段只存储一项信息

  • 第二范式（属性完全依赖于主键）
    定义：满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式
    通俗解释：任意一个字段都只依赖表中的同一个字段 ，第二范式规定非主属性的所有元素必须直接和主属性相关

  • 第三范式（属性不能传递依赖于主属性）
    定义：满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。
    通俗理解：一张表最多只存2层同类型信息

  • 函数依赖 定义：设R（U）是属性集U上的关系模式，X,Y是U的子集。若对于R（U）的任意一个可能的关系r，r不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作X->Y。若函数Y不函数依赖于X，则记作X-/->Y。
  • 平凡依赖 定义：若X->Y，且Y是X的子集（对任一关系模式，平凡函数依赖必然成立），就是平凡函数依赖。
  • 非平凡依赖 定义：若X->Y，但Y不是X的子集，就是非平凡函数依赖。
  • 完全函数依赖 定义：在R(U)中，如果X->Y。并且对于X的任何一个真子集X‘，都有X'-/->Y，则称Y对X有完全函数依赖，记作X->Y（箭头上有个大写F）。
  • 部分函数依赖 定义：若X->Y,但Y不完全函数依赖于X，则称Y对X部分函数依赖，记作X->Y（箭头上有个大写P）。
  • 传递函数依赖 定义：在R（U）中，如果X->Y(Y不是X的子集)，Y-/->X,Y->Z,Z是Y的子集，则称Z对X传递函数依赖。记为X->Z（箭头上是汉字 “传递”）
  • 直接函数依赖 定义：在R（U）中，如果X->Y，Y不是X的子集,Y->Z,Z是Y的子集，则称Z对X传递函数依赖。记为X->Z（箭头上是汉字 “直接”）

参照完整性

  • 在create table 语句中用foreign key短语定义哪些列为外码
  • 用references短语指明这些外码参照哪些表的主码
  • 指明外码后参照完整性规则自动生效
  • 参照完整性检查和违约处理
    create table sc
    (Sno varchar(10) not null,
    Cno varchar(10) not null,
    Grade smallint,
    Primary key（Sno，Cno）
    foreign key(Sno) References S(Sno)
    on delete cascade
    on update set null,
    foreign key(Cno) References S(Cno)
    on delete no action
    on update cascade
    );
  • 用户定义的完整性实现
    create table student
    (Sno varchar2(10) not null,
    Sname varchar2(10) unique,
    Ssex varchar2(10) check(Ssex='男' or Ssex='女')，
    Sbirth date,
    College varchar2(40),
    PRIMARY KEY(Sno)
    );
    当学生性别是‘男’时，名字不能以Ms.开头
    create table student
    (Sno varchar2(10) not null,
    Sname varchar2(10) unique,
    Ssex varchar2(10) check(Ssex IN('男'，‘女’))，
    Sbirth date,
    College varchar2(40)，
    check(Ssex='女' or Sname not like 'MS.%')
    );
  • 建立学生登记表S

  • 完整性约束命名子句
    ![](https://img2020.cnblogs.com/blog/2047558/202005/2047558-20200527174404277-185012049

传统集合运算

  • 并、差、交
    R和S满足的条件：
  • 具有相同的目n
  • 相应的属性取自同一个域
    R U S 仍为n目关系，由属于R或属于S的元组组成

R - S 仍为n目关系，由属于R或不属于S的元组组成

R ∩ S 仍为n目关系，由属于R又属于S的元组组成

  • 广义笛卡尔积
    R:n目关系，k1个元组
    s:n目关系，k2个元组
    列：（n+m）列的元组的集合
  • 元组的前n列是关系R的一个元组
  • 后m列是关系s的一个元组
    行：k1*k2个元组

选择、投影、连接

选择

选择操作时在关系R中选择满足给定条件的诸元组，其表示形式为：
σf（R）={ t|t∈R∧F（t）=‘真’}

投影：从关系R中选择出若干属性列组成新的关系。（从列的角度出发）记作

连接：

R⋈S={tr⌒ts |tr∈R∧ts∈S∧tr[B]=ts[B]}

等值连接：

θ为“=”时的连接称为等值连接，从关系R和S的笛卡尔积中选取属性组A和B之相等的元组

自然连接：

  • 首先要求两个关系进行比较的分量必须是同名的属性组；
  • 最后，在等值连接的结果中去掉重复的属性列。
    关系R和S在所有公共属性（common attribute）上的等接（Equijoin）。但在得到的结果中公共属性只保留一次，其余删除。
    R⋈S=

外连接

除法运算

除法运算是二目运算



  • 在数据库中为什么要使用并发控制?
    多个用户并发地存取数据库时就会产生多个事务同时存取同一数据的情况，若对并发操作不加控制就可能存取和存储不正确的数据，破坏事务的一致性和数据库的一致性。所以数据库管理系统必须提供并发控制机制。
  • 并发操作带来数据不一致性问题分哪三类?
  1. 丢失修改2. 不可重复读3. 读“脏”数据
  • 封锁与封锁类型
    并发控制就是以正确的方式调度并发事务，使一个事务的执行不受其他事务的干扰。在数据库环境下，并发控制的主要方式是封锁机制，即加锁。排它锁又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。共享锁又称为读锁，若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。
  • 封锁协议
    在运用X锁和S锁对数据对象封锁时，需要约定一些规则：何时申请X锁和S锁 、持锁时间、何时释放
  • 常用的封锁协议：三级封锁协议：
    1级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。1级封锁协议可防止丢失修改在1级封锁协议中，如果是读数据，不需要加锁的，所以它不能保证可重复读和不读“脏”数据。2级封锁协议：1级封锁协议＋事务T在读取数据R前必须先加S锁，读完后即可释放S锁。
    2级封锁协议可以防止丢失修改和读“脏”数据。在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。
    3级封锁协议：1级封锁协议＋事务T在读取数据R前必须先加S锁，直到事务结束才释放S锁。3级封锁协议可防止丢失修改，读“脏”数据和不可重复读。
    三级协议的主要区别：§什么操作需要申请封锁§何时释放锁（即持锁时间）

视图的基本操作

  • 创建视图：
         create view 视图名 as select语句；
     * 修改视图，影响基表：
         update view_ename_dname set deptno=10 where ename='KING';修改视图
     * 修改基表，影响视图：
         update EMP set deptno=20 where ename='JAMES'; -- 修改基表 
     * 删除视图：
        drop view 视图名；
     * 实例：(虚表)
         create view view_ename_dname as     
         select ename, dname  from EMP, DEPT
         where EMP.deptno=DEPT.deptno;
         select * from view_ename_dname;
  • 权限赋予：
    Grant select Update on Table student To u1,u2;
    给u1u2赋予查询和更新student表的权力，不允许u1u2把权限再给别的用户
  • 权限再赋予：
    加上 WITH GRANT OPTION
  • 权限收回：
    REVOKE UPDATE(SNO),SELECT ON TABLE SC FROM U3 CASCADE
posted @ 2020-06-04 19:42  WANGXIN_YU  阅读(710)  评论(0)    收藏  举报