(数据库原理第一天)数据库原理

一、关系模式

        数据库管理系统DBMS是用于创建、处理和管理数据库的计算机程序，DBMS接受以SQL编码的请求，并将这些请求转化为数据库中的操作。

        数据库定义为关联记录的自描述集合，对于所有的关系数据库而言。自描述意味着数据库本身有对数据库就够的描述。数据库帮助人们记录事物，关系型DBMS以表的形式存储数据，被记录的事物称为实体，非常重要的内容，DBMS产品在表中存储数据并不完全正确，其实数据以关系的形式来存储，关系是一种特殊类型的表，表中每个单元格都必须包含一个值，单元格中不允许重复的元素，任意一列中的所有条目的类型都必须一致，每一列都必须有唯一的名称，但表中列的顺序任意，行的顺序任意，表中任意两行不能有完全相同的数据值。关系表中的每个单元格不允许包含多个条目

        首先用全大写字母写出表名，例如EMPLOYEE，如果表名是两个或者多个单词的组合，就使用下划线连接这些单词，列名包含在圆括号中，且首字母大写

二、键

        代理键是具有DBMS分配的唯一标识符的列，该标识符已经作为主键添加到表中，每次创建行时由DBMS分配代理键的唯一值，该值永远不变。代理键就是理想的主键。

         将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键，第二个表中保存这些值的属性称为外键。主键和外键的列名不一定相同，唯一的要求就是他们的值集必须相同。

        外键是表中用于表示关联的属性。外键是其所在表以外的另一个表的主键。

        参照完整性约束：只要看到一个外键，就总是能看到与之相关的参照完整性约束。就是一个表的主键值在另一个表的该值有对应项

三、函数依赖与规范化

        一个表应该只有一个主题，将一个具有多个主题的表分割为一组表，使每个表只有一个主题。

        在结构良好的表中，每个决定因子都必须是候选键，非结构良好的表应该分解成两个或者多个结构良好的表。规范化就是检查并且修改表使其结构良好的过程，这个过程叫做规范化。

        函数依赖是指一个属性值决定另一个属性值的情况，函数依赖左边的属性称为决定因子。

四、查询语句

        用于数据定义的SQL语句：SQL DDL用于创建和改变数据库结构与插入、修改和删除表中的数据，

        使用表约束定义主键，表约束CONSTRAINT关键字标识，用于实现各种约束。

         SQL关系查询语句，查询数据库中单个表的基本SQL架构使用带有FROM和WHERE子句的SQL SELECT命令。

          希望DBMS检查和删除重复行，使用DISTINCT关键字，如 SELECT DISTINCT Department  FROM PROJECT;

          从单个表中读取指定行：通过SQL WHERE子句可以指定要选择的行，如下面的SQL语句会获取PROJECT表中财务部资助项目的所有列，SELECT ProjectID,ProjectName,Department,MaxHours,StartDate,EndDate  FROM PROJECT WHERE Department='Finance'.

           指定表中所有列的第二种方法是在SELECT关键字后使用*，如SELECT * FROM PROJECT WHERE Department = 'Finance'   

          可以在WHERE子句中放置许多不同的条件，SELECT * FROM PROJECT WHERE MaxHours > 135;从PROJECT表中选择所有MaxHours列值大于135的所有列。当列数据类型是char或者Varchar时，要比较的值必须放在单引号中，且放在引号中的值分大小写，'Finance'和'FINANCE'是不等价的。或者可以使用AND关键字在WHERE子句中放置更多的条件，如果使用了AND关键字，从单个表中读取指定行和指定列，只获取Accounting部门中所有部员的FirstName、LastName、Phone、Department值，如：SELECT FirstName,LastName.Phone,Department FROM EMPLOYEE WHERE Department = 'Accounting';使用AND和OR关键字，可以在WHERE子句中结合两个或多个条件，使用AND只选择满足所有条件的行，使用OR，选择满足任意一个条件的行。如使用AND关键字请求在Accounting部门工作、且电话号码为360-285-8310的员工：SELECT FirstName,LastName,Phone,Department FROM EMPLOYEE WHERE Department = 'Accounting' AND Phone = '360-285-8310'.  在WHERE子句中使用日期时，通常可以把日期放在单引号中，如：SELECT * FROM PROJECT  WHERE StartDate = #05/10/10#;  WHERE子句通过IN关键字，指定列值必须是指集中的一个，如：SELECT FirstName,LastName,Phone,Department FROM EMPLOYEE WHERE Department IN ('Accounting','Finance','Marketing');而选择Department的值不是所列值的行，使用NOT IN关键字。

         WHERE子句指定值的范围和部分值，关键字BETWEEN用来指定值的范围和部分值，SELECT FirstName,LastName,Phone,Department FROM EMPLOYEE WHERE EmployeeNumber BETWEEN 2 AND 5;关键字LIKE在表达式用于选择部分匹配的值，与通配符一起使用，是_和%，下划线代表一个未指定的字符，百分号表示一个或者多个未指定的字符，如：SELECT * FROM PROJECT WHERE ProjectName LIKE '2010 Q_ Portfolio Analysis';下划线表示任一字符均可以取代下划线。对每个未知的字符使用一个下划线，为了找到电话号码为360-287-开头的所有雇员，可以使用四个下划线代替后面的四个数字：SELECT * FROM EMPLOYEE WHERE Phone LIKE '360-287-____';百分号表示一个或者多个未知字符，查找电话号码以360-287-开头的所有雇员的查询写为：SELECT * FROM EMPLOYEE  WHERE Phone LIKE '360-287-%'; 想查询部门名以ing结尾的所有雇员：...WHERE Department LIKE '%ing';想查找部门名不以ing结尾的所有雇员，使用...WHERE Department  NOT LIKE '%ing'

         对查询结果排序，在SELECT语句的结果中，行的顺序是任意的，可以使用ORDER BY 子句对行排序，若要显示按照Department排序的所有雇员的FirstName,LastName,Phone和Department值：SELECT FirstName,LastName,Phone,Department FROM EMPLOYEE ORDER BY Department;默认按照升序排序，从A-Z，关键字ASC和DESC需要指定升序或者降序，如按照降序可以：...ORDER BY Department DESC;  可以用两个或者多个列来排序，为员工的FirstName,LastName,Department 值先按照Department降序排列，然后按照Department中按LastName升序排列，可以使用：SELECT FirstName,LastName,Phone,Department FROM EMPLOYEE  ORFER BY Department DESC,LastName ASC;

        SQL内置函数和计算，允许基于表中的数据计算值，SQL有五个内置函数，COUNT、SUM、AVG、MAX和MIN。COUNT函数可以同于任何数据类型，但是SUM,AVG,MAX,MIN只能对整型，数值型和其他数字类型执行操作。COUNT统计结果中行的数目，SUM统计一个数值列的总和，如统计PROJECT表中的行数  SELECT COUNT(*) FROM PROJECT;若SELECT语句总是一个表，结果是一个数值，但是该数字仍然视为仅有一行和一列的表，没有列名，可以使用AS关键字将列名赋给该结果： SELECT COUNT(*) AS NumberOfProjects FROM PROJECT;  内置函数的示例：SELECT MIN(MaxHours) AS MInmumMaxHours, MAX(MaxHours) AS MaximumMaxHours,SUM(MaxHours) AS TotalMaxHours FROM PROJECT WHERE ProjectID <=1200;且列名不能和内置函数一起使用，

         内置函数和分组：GROUP BY子句可以按公共值给行分组，因为可以将内置函数应用于一组数据行，如要统计每个部门的雇员人数：SELECT Department,Count(*) AS NumberOfEmployees FROM EMPLOYEE GROUP BY Departrment; GROUP BY 子句告诉DBMS根据给定的列对结果排序，再对在指定列中含有相同值的一组行运用内置函数，使用GROUP BY后，分组列的名称和内置函数可以同时出现在SELECT语句中，对组使用HAVING子句，应用某些条件以进一步限制所得的结果，如果想获取两个以上成员的组，可以指定：SELECT Department,Count(*) AS NumberOfEmployees FROM EMPLOYEE GROUP BY Department HAVING COUNT(*) > 1   使用GROUP BY 时，添加WHERE子句，同时WHERE和GROUP BY时，首先运用WHERE条件。having 只能与GROUP BY连用

         使用子查询处理多个表，假如确定在某个任务中工作时间超过40小时的所有雇员的姓名，雇员的姓名存储在EMPLOYEE表中，但是其工作时间存储在ASSIGNMENT表中，所以：SELECT FirstName,LastName FROM EMPLOYEE WHERE EmployeeNumber IN (SELECT DISTINCT EmployeeNumber FROM ASSIGNMENT WHERE HoursWorked > 50);

         使用连接查询多个表，即连接两个或者多个表的内容，建立一个新表。如SELECT FirstName,LastName,HoursWorked FROM EMPLOYEE,ASSIGNMENT WHERE EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber;作用是创建一个含有两列ProjectName和HoursWorked的新表.过程为：从EMPLOYEE表的第一行开始，使用第一行中EmployeeNumber的值，检查ASSIGNMENT表中的行，SQL查询中的行徐是随意的。

        SQL JOIN...ON语法，使用ORDER BY子句对结果排序：

SELECT FirstName,LastName,HoursWorked
FROM EMPLOYEE,ASSIGNMENT
WHERE EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber
ORDER BY EMPLOYEE.EmployeeNumber,ProjectID

　使用JOIN...ON可以写为：

SELECT FirstName,LastName,HoursWorked
FROM EMPLOYEE JOIN ASSIGNMENT
          ON EMPLOYEE.EmployeeNumber = 
                  ASSIGNMENT.EmployeeNumber
ORDER BY EMPLOYEE.EmployeeNumber,ProjectID;

　　可以使用JOIN...ON语法连接两个以上的表，重新编写连接EMPLOYEE,PROJECT.ASSIGNMENT中数据的查询：

SELECT ProjectName,FirstName,LastName,HoursWorked
FROM EMPLOYEE AS E JOIN ASSIGNMENT AS A 
         ON E.EmployeeNumber = A.EmployeeNumber
                  JOIN PROJECT AS P 
                           ON A.ProkectID = P.ProjectID
ORDER BY.ProjectID,A.EmployeeNumber;

　　将LEFT关键字添加到SQL查询中，目的是把PROJECT表中的行追加到ASSIGNMENT表的相应行中。

五、修改和删除关系数据的SQL语句 

        修改数据：UPDATE...SET修改已有的数据的值，如EMPLOYEE表中修改某人的电话号码：

UPDATE EMPLOYEE
SET       Phone = '360-287-8810'
WHERE EmployeeNumber = 11;

　　UPDATE语句可以一次修改多个列值，

        删除数据：DELETE，DROP TABLE（如果表包含需要实施参照完整性约束的值，则drop table无效）。使用ALTER TABLE用于添加、修改、删除列和约束，如需删除ASSIGN_EMP_FK约束：

ALTER TABLE ASSIGNMENT
          DROP CONSTRAINT ASSIGN_EMP_FK;　

使用DROP TABLE删除数据库中的表和其中的数据，ALTER TABLE DROP CONSTRAINT命令删除约束，使用ALTER TABLE命令修改表和约束，使用CHECK约束验证数据值。　

六、关系模型

        关系：实体之间通过关系来关联，E-R模型包括关系类和关系实例，关系类反应了实体类之间的关联，关系实例反映了实体实例之间的关联，关系中实体类的数目称为关系的度数。二元关系的3种类型：一对一关系(1:1)，一对多关系(1:N)，多对多关系(N:M),在1:1关系中，某种类型的一个实体实例仅仅和另一种类型的实体实例系那个相关联，比如在LOAKER-ASSIGNMENT关系将一个EMPLOYEE和一个LOCKER关联起来。

七、数据库底层

        当我们要对一个集合排序的时候，使用优秀的排序算法，比如合并排序，合并排序有助于我们以后理解数据库常见的联接操作，即合并联接。合并排序基于一个技巧：将2个大小为N/2的已经排序序列合并为一个N元素已排序序列仅仅需要N次操作。

 在此图中，在2个4元素序列里面只需要迭代一次，就可以构建最终的8元素已排序序列，因为两个4元素序列已经排好序，步骤为：

1) 在两个序列中，比较当前元素（当前=头一次出现的第一个）
2) 然后取出最小的元素放进8元素序列中
3) 找到（两个）序列的下一个元素，(比较后)取出最小的
重复1、2、3步骤，直到其中一个序列中的最后一个元素
然后取出另一个序列剩余的元素放入8元素序列中。

　伪代码：

array mergeSort(array a)
   if(length(a)==1)
      return a[0];
   end if
 
   //recursive calls
   [left_array right_array] := split_into_2_equally_sized_arrays(a);
   array new_left_array := mergeSort(left_array);
   array new_right_array := mergeSort(right_array);
 
   //merging the 2 small ordered arrays into a big one
   array result := merge(new_left_array,new_right_array);
   return result;

　　合并排序是通过分治法把问题拆分为小问题，通过解决小问题来解决最初的问题，先把序列分为更小的序列，然后再把小的序列合在一起通过合并算法来构成更大的序列。在拆分阶段过程中，使用3个步骤将序列分为一元序列，步骤数量的值为㏒₂(N)，在排序阶段中，从一元序列开始，在每一个步骤中，应用多次合并操作。合并排序可以直接修改输入序列，叫原地算法。可以更改算法，可以同时使用磁盘空间和少量内存而避免巨量磁盘I/O，只向内存中加载当前处理的部分，叫外部排序。

        3种数据结构是现代数据库支柱：①阵列，二维阵列是最简单的数据结构，一个表可以看作是一个阵列，这个二维阵列是带有行与列的表，每个行代表一个主体，列用来描述主体的特征，每个列保存某一种类型对数据②树，二叉查找树是带有特殊属性的二叉树，每个节点的关键字都必须比左子树的任何键值大，比右子树的任何键值要小，查询成本为log₂N.    B+树索引，就是为了查找特定值，需要查找两个值之间的多个元素的话就不行了，在一个B+树里只有最底层的叶子节点才能保存信息，其他节点只是在搜索中用来指引到正确节点的，在B+树中，需要找到40-100之间的值，只需要找到40或者40之后最贴近的值，然后用这个连接来收集40的后续节点，知道找到100. ③哈希表，当想要快速查找的时候，用哈希表，哈希表被数据库用来保存一些内部的东西，比如锁表或者缓冲池，哈希表可以用关键字来快速找到一个元素，为了构建一个哈希表，需要定义元素的关键字和关键字的哈希函数，关键字计算出来的哈希值给出了元素的位置，和关键字的比较函数，一旦找到了正确的元素的位置，必须用比较函数在位置上找到想要的元素。哈希表有10个位置，比如查找元素78，哈希表计算78的哈希码，等于8，查找哈希桶8，找到元素是78，返回元素78，查询仅仅耗费了2次运算，一次计算哈希值，领一次在哈希桶中查找元素。有一个好的哈希函数，在哈希表里的搜索的时间复杂度是O（1）。阵列与哈希表的区别，一个哈希表可以只装载一半到内存，剩下的哈希桶可以留在硬盘上，用阵列的话，你需要一个连续内存空间，如果加载一个大表，很难分配足够的连续内存空间，用哈希表的话，你可以选择你要的关键字。

        数据库时一个易于访问和修改的信息集合，数据库时由多种互相交互的组件构成的，进程管理器，网络管理器，文件系统管理器，内存管理器，安全管理器，客户端管理器，备份管理器，复原管理器，监控管理器，查询管理器，数据管理器。

        数据库如何通过客户端管理器，查询管理器，数据管理器来管理SQL查询的：客户端管理器是处理客户端通信的，客户端可以是一个网站服务器或者一个最终用户或最终应用，客户端管理器通过一系类知名的API提供不同的方式来访问数据库，客户端管理器也提供专有的数据库访问API，当你连接到数据库的时候管理器首先检查你的验证信息，比如用户名和密码然后检查你石是否有访问数据库的授权，这些权限由DBA分配，然后管理器检查是否有空闲进程来处理你查询，管理器还会检查数据库是否负载很重，可能会等待一会来获取需要的资源，如果等待时间达到超时时间，它会关闭连接并且给出一个可读的错误信息，然后管理器会把你的查询送给查询管理器来处理，一旦查询处理进程从查询管理器得到数据，它会把部分结果保存到一个缓冲区并且开始给你发送，如果遇到问题，管理器关闭连接，想你发送可读的解释信息，然后释放资源。

        查询管理器：代码执行的结果被送到客户端管理器，即查询首先被解析并判断是否合法，然后被重写，去除了无用的操作并且加入预优化部分，接着被优化以便提升性能，并被转换为可执行代码和数据访问计划，然后计划被编译，最后被执行。

        查询解析器：首先每一条SQL语句都要送到解析器来检查语法，如果你的查询有错，解析器将拒绝该查询，解析器还会检查关键字是否使用正确的顺序，然后解析器要分析查询中的表和字段，使用数据库元数据来检查表是否存在，表的字段是否存在，对某类型字段的运算是否可能，还有，解析器检查在查询中你是否有权限来读取表，在解析过程中，SQL查询被转换为内部表示，如果一切正常，内部表示被送到查询重写器

        查询重写器：已经有了查询的内部表示，重写器的目标是预优化查询，避免不必要的运算，帮助优化器找到合理的最佳解决方案，重写器按照一系列已知的规则对查询执行检测，如果查询匹配一种模式的规则，查询就会按照这条规则来重写。、

        数据库和操作系统使用的最小单位是页或块，数据库会计算表中行和页的数量，表中每个列中的唯一值、数据长度、数据范围和表的索引信息，这些统计信息会帮助优化器估计查询所需的磁盘I/O、CPU、和内存的使用

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

、