“数据库”学习笔记


概述

  • DB 是数据的集合,存储在磁盘中;
  • DBMS 称作数据库管理系统,是一个基础软件,用来存储和管理数据库;
  • DBS 数据库管理系统是针对一个用户的数据管理需求开发的应用系统。
  • 文件系统存储数据的缺点
    • 数据冗余和矛盾
    • 存取资料比较困难
    • 数据隔离
    • 数据合法性的约束
    • 数据修改的完整性
    • 多用户同时进入问题
    • 安全性问题
  • 数据库系统的构造(从用户角度出发)
    • view level :隐藏一些数据的类型和出于保密所需要的数据信息
    • logical level :描述数据库中的数据以及数据之前的关系
    • physical level :记录数据如何存储,一般有比较复杂的数据结构。
    • physical storage :物理存储介质。
  • 实例和模式
    • 实例:数据库中的实际数据;
    • 模式:数据库的逻辑结构。
  • 数据库的物理独立性:指的是修改数据库的物理模式不会影响数据库的逻辑模式的一种能力。
  • 数据库应用依赖的是逻辑模式。
  • 数据模型是描述数据和数据之间的关系、数据的一些约束的工具。
    • 关系模型、ER 模型、基于对象的模型、XML、JSON等
  • 数据库语言是一类访问数据库中数据的语言,具有数据定义和数据操纵的能力
    • Data Manipulation Language (DML): 执行数据库查询和修改操作
      • 过程语言:用户需要指出获取数据的方法
      • 非过程语言:用户无需指出获取数据的方法(例如 SQL)
    • Data Definition Language (DDL): 执行数据库模式定义操作
    • SQL: 结构化查询语言,但是不仅仅执行查询,包括定义数据库模式、修改数据库以及说明安全约束等都可以完成。
  • 数据库的设计方法
    • E-R模型:实例-关系 模型。
    • 形式化的方法
  • 存储管理模块提供了存储在数据库中的底层数据与应用程序以及提交到系统的查询的接口
    • 负责:与文件系统的交互、有效的数据存储、检索和修改
    • 解决的问题:存储访问、文件组织、索引和hash
  • 事务管理系统:包括事务管理和并发控制

关系模型

  • 关系数据库包括:关系名称、元组、属性名称
    • 属性有类型,描述的是每个属性允许取值的范围
    • 属性是原子不可分的
    • 属性可以取一个特殊的值: null
  • 关系模式 \(R=(A_1,A_2,\cdots , A_n)\) 是属性的集合;
  • 关系是 \(D_1\times D_2\times \cdots \times D_n\) 的子集
  • 键:唯一的编号不同的区分元组
    • 设 \(K\subseteq R\) ,\(R\) 是所有属性的集合
    • superkey:\(K\) 的取值足以唯一确定 \(R\) 的每一个可能的记录
    • candidate key:最小的 super key 集合
    • primary key:主码。多个 candidate key 中选一个作为主码,实现时用。应该选择属性值很少变化的属性
    • foreign key:外码。假设有 \(R\) 表和 \(S\) 表, \(R\) 表中的属性 \(A\) 在 \(S\) 表中是主码,那么这个 \(A\) 属性在 \(R\)表中是外码
      • \(R\) 称为 referencing relation
      • \(S\) 称为 referenced relation
      • 外码是一种数据的完整性约束,即在 \(S\) 中未出现过的 key 不能在 \(R\) 中被引用
  • 关系查询语言分为程序语言和非程序语言
  • 关系运算
    • select
      • Select tuples with \(A=B\) and \(D > 5\) \(\sigma _{A=B\; and \; D>5}(R)\)
      • Select \(A\) and \(C\) \(\prod _{A,C}(R)\)
    • join
      • \(r\times s\)
      • \(r\cup s\)
    • difference
      • \(r-s\)
    • intersection
      • \(r\cap s\)
    • natural join
      • \(r\bowtie s\)
      • \(r\cup s\) 但是合并的时候,只保留满足 \(r\cap s\) 的部分,例如:

SQL

  • SQL 有四个能力:
    • Data-definiton language (DDL)
    • Data-mainpulation language (DML)
    • Query language
    • Data-control language : 控制数据的权限以及完整性约束
  • SQL是非过程语言,及只需告诉要什么,而不需告诉如何得到
  • Data-definition 包括:
    • 定义每个属性的值域
    • 定义完整性约束
    • 定义每个关系的索引(可以加快查询速度)
    • 定义每个关系的安全和权限
    • 定义每个关系在磁盘上的物理存储结构
  • Data-definition 基础类型
    • char(n) 长度为 \(n\) 的字符串
    • varchar(n) 最长长度为 \(n\) 的字符串
    • int, smallint
    • numeric(p,d) 包含 \(p\) 位的浮点数,其中小数点右边有 \(d\) 位
    • real, double precision
    • float(n) 浮点数,至少有 \(n\) 位有效数字
  • 创建数据表
    • create table 表格名称
      • (属性名称 属性类型 ,
      • primary key (属性名称));
  • 删除数据表
    • drop 表格名称
  • 删除某个内容
    • delete 属性名称 from 表格名称
  • alter
    • alter table 表格名称 add A D
      • A 是属性名称
      • D 是属性类型
    • alter table 表格名称 drop A
      • A 属性名称
  • 查询数据
    • selete 属性名称
    • from 表格名称
    • while 约束
  • 数据去重
    • selete distinct 属性名称
    • from 表格名称
    • 会返回属性去重之后的结果
  • 也可以返回不去重的结果
    • selete all 属性名称
    • from 表格名称
  • 查询所有的属性
    • selete *
    • from 表格名称
  • 查询属性的结果可以使用四则运算
  • 可以查询表格 \(\times \)  的结果
    • selete *
    • from 表格1 , 表格2
  • 可以查询表格 natural join 的结果
    • selete *
    • from 表格1 nature join 表格2;
  • 在使用Natural join时需要注意:同名的不相关属性被错误地做相等判断。
  • 判断字符串
    • where name like ‘%aaa%’ (包含子串 aaa 的字符串)
    • ‘Intro%’ matches any string beginning with “Intro”.
    • ‘%Comp%’ matches any string containing “Comp” as a substring.
    • ‘_ _ _’ matches any string of exactly three characters.
    • ‘_ _ _ %’ matches any string of at least three characters.
  • 排序
    • selete 属性名称
    • from 表格名称
    • order by 属性名称1, 属性名称 2 (desc(倒序)/asc(默认,顺序))
  • 聚合函数
    • avg(属性名称), sum(属性名称), min(属性名称), max(属性名称)
    • count(属性名称)  例如 count (distinct ID),Count考虑取 null 值的记录
  • Group By 语句
    • 例如,求所有同一个学院的平均工资
      • select dept_name, avg (salary)
        from instructor
        group by dept_name;
    • 写带有group by的SQL时,group by之后的属性必须在 select 子句出现,其他属性出现没意义。
      • 例如下面这个例子是无意义的
      • select dept_name, ID, avg (salary)
        from instructor
        group by dept_name;
  • having 语句
    • select dept_name, avg (salary)
    • from instructor
    • group by dept_name
    • having avg (salary) > 42000;
  • 子查询
    • select count (distinct ID)
    • from takes
    • where (course_id, sec_id, semester, year) in
                                      (select course_id, sec_id, semester, year                                        from teaches                                        where teaches.ID= 10101);
  • some 只要有一个成立即成立
  • all 只要有一个不成立即不成立
  • Correlation Variables
    •   select course_id
      from section as S
      where semester = ’Fall’ and year= 2009 and
      exists
      (select *
      from section as T
      where semester = ’Spring’ and year= 2010
      and S.course_id= T.course_id);
    • 括号中的子查询的where子句用了外层SQL查询的关系名 \(S\) ,所以 \(S\) 称为 Correlation name
    • 这类查询称作correlated subquery
  • not exists (X except Y) 等价于查询 X 是 Y 的子集
  • unique
    • select T.course_id
      from course as T
      where unique (select R.course_id
      from section as R
      where T.course_id= R.course_id
      and R.year = 2009);
  • from 中进行子集查询
    • select dept_name, avg_salary
      from (select dept_name, avg (salary) as avg_salary
      from instructor
      group by dept_name)
      where avg_salary > 42000;
  • lateral
    • 查找老师及其薪水和所在系的平均薪水
      • select name, salary, avg_salaryfrom instructor I1, lateral (select avg(salary) as avg_salary
        from instructor I2
        where I2.dept_name= I1.dept_name);
  • With子句提供了一种定义临时关系的方法,这个临时关系可以用于查询,但是只对包含with子句的查询有效。通过例子我们来理解with子句的定义与使用。
    • with max_budget (value) as
      (select max(budget)
      from department)
    • select budget
      from department, max_budget
      where department.budget = max_budget.value;
  • Scalar Subquery:简单说就是返回单一值的子查询。返回的不是单一值,就会 RE。
    • select dept_name,  (select count(*)
      from instructor
      where department.dept_name = instructor.dept_name)  as num_instructors
      from department;
  • delete 删除信息
    •  delete from instructor
      where dept_name in (select dept_name
      from department
      where building = ’Watson’);
  • insert 插入信息
    • insert into course (course_id, title, dept_name, credits)
      values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
  • select 信息,插入数据库,select查询语句全部执行完成之后再进行insert操作
    •  insert into student
      select ID, name, dept_name, 0
      from   instructor
  • update 修改数据,要注意修改的顺序。
    • update instructor
      set salary = salary * 1.03
      where salary > 100000;
  • case update
    • update instructor
      set salary = case
      when
      salary <= 100000 then salary * 1.05
      else salary * 1.03
      end

Join

分类

  • inner join:结果只保留匹配的元组,不保留未匹配的元组
  • outer join:通过在结果中保留包含空值元组的方式保留在连接中丢失的元组
    • left outer join:保留出现在左边关系中的元组
    • right outer join:保留出现在右边关系中的元组
    • full outer join:保留两个关系中的所有元组

Views

任何这样不是逻辑模型的一部分,但作为虚拟关系对用户可见的关系,称为视图。不是实际存在的关系。

  • 定义一个 views :create view v as < query expression >
  • 定义的时候,同样可以引用,已经定义过的 views

Materialized Views

物化视图,必须保证: 用于定义视图的实际关系改变,视图也随之改变。

物化视图的优势:频繁使用物化视图的应用,因为存储了视图关系,可以提高响应熟读。比如:一些基于聚合函数做统计计算查询的应用,可以定义有关聚合函数计算的物化视图,在物化视图上查询要快很多,避免读取规模大的实际关系表。常用于查询业务量较大的应用。

大部分的 SQL 只支持在简单的 views 上更新数据,因为数据的更新可能会产生不确实性问题。

当view满足一些条件时,可以对view进行增删改操作:

  1. from子句只有一个关系
  2. select子句中只包含关系的属性,不含其它的任何表达式计算或distinct说明
  3. 任何没有出现在select字句中的属性可以取null值
  4. 查询中没有group和having子句

Integrity Constraints

完整性约束何时说明:定义数据库schema的时候说明

完整性约束何时检验:修改数据库表的时候检验是否违反了完整性约束

  • 单个关系上的约束
    • not null
      • name   varchar(20) not null
    • primary key
    • unique
    • check (P), where P is a predicate
      • semester   varchar (6),
      • check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)));
  • Foreign key是一类完整性约束
    • create table course (
    •     course_id   char(5) primary key,
    •     title             varchar(20),
    •     dept_name varchar(20) references department)

数据类型

  • date
    • Example:  date ‘2005-7-27’
  • time
    • Example:  time ‘09:00:30’         time ‘09:00:30.75’
  • timestamp
    • Example:  timestamp  ‘2005-7-27 09:00:30.75‘
  • interval
    • Example:   interval  ‘1’ day
  • blob:binary large object
  • clob:character large object

数据类型转换

  • cast   <string-valued-expression> as date
  • 自己定义一个新的类型
    • create type Dollars as numeric (12,2) final
    • create domain person_name char(20) not null

权限

database 四种权限:

  • read
  • insert
  • update:allows modification, but not deletion of data.
  • delete

database schema  四种权限:

  • Index:allows creation and deletion of indices.
  • Resources:allows creation of new relations.
  • Alteration:allows addition or deletion of attributes in a relation.
  • Drop:allows deletion of relations.

授权方式

  • grant <privilege list>
  • on <relation name or view name>
  • to <user list>

privilege

  • select: allows read access to relation,or the ability to query using the view
    • grant select on instructor to U1, U2, U3
  • insert: the ability to insert tuples
  • update: the ability  to update using the SQL update statement
  • delete: the ability to delete tuples.
  • all privileges: used as a short form for all the allowable privileges

收回权限

  • revoke <privilege list>
  • on <relation name or view name>
  • from <user list>

基于角色的访问控制

基于角色的访问控制广泛应用于应用中,分配权限时针对角色分配。

  • create role teaching_assistant
  • grant teaching_assistant to instructor;

Views 也可以 grant 相应的权限

You may also like

LEAVE A COMMENT

Statistics

  • 0
  • 44,606

Categories

Archive

Comments