一文读懂数据库（精华）！！！耗时36小时！！万字长文，吊打监考老师！！！

绪论

数据库系统概述

数据库系统概念

通常由软件、数据库和数据管理员组成。

其软件主要包括操作系统、各种宿主语言、实用程序以及数据库管理系统。

数据库系统 一般由4个部分组成：

数据库、硬件、软件、人员。

数据库：是指长期存储在计算机内的，有组织，可共享的数据的集合

首先，它存的东西是数据，它可以有很多种，比如你给学妹写的情书，出去玩拍的照片，舍友唱歌时偷录的声音等等

为什么是长期呢？

比如，在哪个月黑风高的夜晚，你突发奇想想看一看当年自己的《舔狗日记》，结果被自己不知道弄丢到哪里啦，是不是很扫兴，扫兴你就要拿起手机打一局游戏，一打游戏你的早睡计划是不是就泡汤了，早睡计划泡汤了你是不是就很自责、愧疚，自己很自责是不是就…“话说这跟长期有什么关系啊 哎”

长期就是当你想见到他的时候就能见到他，可以很长时间的存起来

那有组织呢？

所谓组织，就是纪律，整齐。

我们知道，普通的词典侧边都有按顺序排列的字母，目的就是帮助我们快速检索到目标

试想一下，如果所有的单词都是乱序的，那我们找一个单词要付出很大的时间成本，有组织可以提高我们的效率。

为什么要可共享呢？

比如现在在写期末大作业，但是你和贪玩的室友们，开黑到最后两小时，但作业最少需要4小时才能完成。于是就跟舍友们讲，团结就是力量的故事。

四台电脑，一个作业，半个小时。在腾讯文档的多人协作模式下，你们造就了一个奇迹。

看到了吧，这就是共享作业的威力。如果这个作业可以分为四个部分，那么每个人一个部分，就能让效率提高很多倍。

这也是共享的好处，在分工明确的条件下，可以提高团队的合作效率。实现大家资源共享

硬件：构计算机系统的各种物理设备、包括存储所需的外部设备
硬件的配置应满足整个数据库系统的需要。

软件：包括操作系统、数据库管理系统及应用程序。

数据库管理系统是什么勒？

上文我们提到数据库的定义是指长期存储在计算机内的，有组织，可共享的数据的集合 。那如何实现这些功能呢？这时候数据库管理系统的作用就来啦，它在操作系统的支持下，高效的处理数据。

其主要功能包括：数据定义功能、数据操纵功能、数据库的运行管理和数据库的建立与维护。

人员：

第一类为系统分析员和数据库设计人员 :

负责数据库中数据的确定、数据库各级模式的设计。

第二类为应用程序员，负责编写使用数据库的应用程序。

也就是我们口中的SQLboy，负责编写使用数据库的应用程序。这些应用程序可对数据进行检索、建立、删除或修改

第三类为最终用户，他们利用系统的接口或查询语言访问数据库

第四类用户是数据库管理员（data base administrator，DBA），负责数据库的总体信息控制

类似于架构师的人员，具体数据库中的信息内容和结构，决定数据库的存储结构和存取策略，定义数据库的安全性要求和完整性约束条件，监控数据库的使用和运行，负责数据库的性能改进、数据库的重组和重构，以提高系统的性能。

数据库系统特点

数据结构化

结构化实际上是有组织的一种体现 让无序变成有序

结构化数据

结构化的数据是指可以使用关系型数据库表示和存储，表现为二维形式的数据。
一般特点是：数据以行为单位，一行数据表示一个实体的信息，每一行数据的属性是相同的。

如mysql数据库中的数据、csv文件

非结构化数据

非结构化数据是指信息没有一个预先定义好的数据模型或者没有以一个预先定义的方式来组织。非结构化数据一般指大家文字型数据，但是数据中有很多诸如时间，数字等的信息。相对于传统的在数据库中或者标记好的文件，由于他们的非特征性和歧义性，会更难理解。

包括所有格式的办公文档、文本、图片、XML、HTML、各类报表、图像和音频/视频信息等等。

半结构化数据

半结构化数据就是介于完全结构化数据（如关系型数据库、面向对象数据库中的数据）和完全无结构的数据（如声音、图像文件等）之间的数据。半结构化数据是结构化数据的一种形式，它并不符合关系型数据库或其他数据表的形式关联起来的数据模型结构，但包含相关标记，用来分隔语义元素以及对记录和字段进行分层。因此，它也被称为自描述的结构。

半结构化数据，属于同一类实体可以有不同的属性，即使他们被组合在一起，这些属性的顺序并不重要。也就是它一般数据的结构和内容混在一起，没有明显的区分。

包括日志文件、XML文档、JSON文档、Email等。

数据的共享性高、冗余度低且易扩充

共享性高与冗余度低：同样的作业，如果大家都写自己的，不和其他伙伴相互共享，那么就会造成作业的冗余。

假设，现在全校的教职工都有一样作业，写《PHP是世界上最好的语言.java》读后感，假设读后感分为，观前印象，读书笔记，读后感悟，美句摘抄。如果学生的不共享给老师，老师的不共享给食堂阿姨，那作业的完成效率很低。

所说数据不再面向某个应用而是面向整个系统，因此数据可以被多个用户、多个应用共享使用。数据共享可以大大减少数据冗余，节约存储空间。

假如说，校长的需求又变了，写一遍议论文《本文作者与徐公孰美？》这时候我们只需要把赞美 《PHP是世界上最好的语言.java》读后感的句子中，描写java美好的部分，替换成“本文作者就好了”，也就是是说可以取整体数据的各种子集于不同的应用系统，当应用需求改变或增加时，只要重新选取不同的子集或加上一部分数据便可以满足新的需求。

数据独立性高

包括数据的物理独立性和数据的逻辑独立性

物理独立性:是指用户的应用程序与存储在磁盘上的数据库中的数据是相互独立的。

也就是说，数据在磁盘上的数据库中的存储是由DBMS管理的，用户程序不需要了解，应用程序要处理的只是数据的逻辑结构，这样当数据的物理存储改变了，而应用程序却不用改变

逻辑独立性:是指用户的应用程序与数据库的逻辑结构是相互独立的.

也就是说，数据的逻辑结构改变了，用户程序也可以不变。数据与程序的独立，把数据的定义从程序中分离出去，加上数据的存取又由DBMS负责，从而简化了应用程序的编制，大大减少了应用程序的维护和修改。

数据由数据库管理系统统一管理和控制

数据库的共享是并发的共享，即多个用户可以同时存取数据库中的数据，甚至可以同时存取数据库中的同一数据。

为此，DBMS还必须提供以下几方面的数据控制功能：

(1)数据的安全性(Security)保护

(2)数据的完整性(Integrity)检查

(3)并发(Concurrency)控制

(4)数据库恢复(Recovery)

数据模型

就像在建筑设计和施工的不同阶段需要不同图纸一样，在开发实施数据库应用系统中也需要使用不同的数据模型，根据模型应用的不同目的，可分为两大类

• 概念模型（信息模型）
• 逻辑模型和物理模型

概念模型

概念模型的一种表示方法：实体 - 联系方法（简称E-R方法）

为了把现实世界中的具体事物抽象、组织为某一数据库管理系统支持的数据模型，人们常常首先将现实世界抽象为信息世界，然后将信息世界转换为机器世界。

把现实世界中的东西，通过人的主观认知，抽象为信息世界的概念模型

而概念模型的一种表示方法：实体-联系方法

实体联系方法用E-R图来描述现实世界的概念模型，所刻画的某些也称为E-R模型

数据模型的组成要素

数据结构

数据结构是所研究的对象类型的集合

数据操作

是指对数据库中各种对象(型)的实例(值)允许执行的操作及有关的操作规则

• 查询
• 更新

数据的完整性的约束条件

数据的完整性约束条件是一组完整性规则的集合，规定数据库状态及状态变化所应满足的条件，以保证数据的正确性、有效性和相容性

实体完整性：规定表的每一行在表中是唯一的实体。

参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。

用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

完整性约束的类型

可分为三种类型：与表有关的约束、域(Domain)约束、断言(Assertion)

• 与表有关的约束

包括列约束(表约束+NOT NULL)和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。

• 域约束 sqlserver不支持
• 断言约束 不必与特定的列绑定，可以理解为能应用于多个表的check约束，因此必须在表定义之外独立创建断言。

数据库系统的结构

数据库系统的三级模式结构

模式（逻辑模式或概念模式）

模式实际上是数据库数据在逻辑级上的视图。

一个数据库只有一个模式。

外模式（用户模式）

一个数据库可以有多个外模式

数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

应用程序都是和外模式打交道的。

外模式是保证数据库安全性的一个有力措施。

内模式（存储模式）

一个数据库只有一个内模式

它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式

数据库的二级映像功能与数据独立性

为了能够在内部实现这3个抽象层次的联系和转换，数据库管理系统在这三级模式之间提供了两层映象：

二级映像就是这三层模式之间的桥梁，他们之间的逻辑关系类似于奥利奥，五层奥利奥，中间两层夹心

分别是：

• 外模式——模式 映像
• 模式——内模式映像

外模式——模式映像

对应于同一个模式可以有任意多个外模式。对于每一个外模式，数据库系统都有一个外模式/模式映象，它定义了该外模式与模式之间的对应关系

当模式改变（如增加新的关系、新的属性、改变属性的数据类型等）时，

由数据库管理员对各个外模式/模式的映象作相应改变，可以使外模式保持不变

应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称为数据的逻辑独立性。

内模式——模式映像

在数据库的三级模式结构中，数据库模式即全局逻辑结构是数据库的中心与关键，它独立于数据库的其他层次

因此设计数据库模式结构时应首先确定数据库的逻辑模式。

当数据库的存储结构改变了（如选用了另一种存储结构），由数据库管理员对模式/内模式映象作相应改变，可以使模式保持不变，从而应用程序也不必改变。保证了数据与程序的物理独立性，简称数据的物理独立性。

关系数据库

关系数据库结构及形式化定义

关系

码的作用

用于区分不同元组、也就是不同行，此表中则为区分不同学生

主码

若候选码多于一个，则选其中的一个为主码（Primary Key）；

候选码：

若关系中的某一属性或属性组的值能唯一标识一个元祖，而其任何真子集都不能再标识，则称该属性组为候选码

主属性

包含在任一候选码中的属性，叫做主属性（Primary Attribute）；

非主属性

不包含在任何码中的属性称为非主属性（Nonprime Attribute）或非码属性（Nonkey Attribute）

超级码

是一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一地标识一个元组；

基本关系的6条性质

• 列是同质的，即列中的每一个分量来自同一个域
• 不同列可以同质
• 列的顺序无所谓
• 任意两个元祖的候选码不能取相同的值
• 行的顺序无所谓，即行的顺序可以任意交换
• 分量需取原子值，即不可分的数据项

实体完整性

实体完整性规则

• 保证关系中的每个元祖都是可识别的和唯一的

指关系数据库中所有的表都必须有主键，而且表中不允许存在如下的记录：

无主键值的记录

主键值相同的记录

• 原因：实体必须可区分

参照完整性

参照完整性规则（引用完整性）

• 现实世界中的实体之间往往存在着某种联系，在关系模型中，实体以及实体之间的联系都是用关系来表示的，这样就自然存在着关系与关系之间的引用
• 参照完整性就是描述实体之间的联系的
• 参照完整性一般就是值多个实体或关系之间的关联关系

此完整性涉及到了外键

• 参照完整性规则就是定义外键与被参照的主键之间的引用规则

• 外键一般应符合如下要求

  -或者值为空
  -或者等于其所参照的关系中的某个元祖的主键值

关系数据库标准语言SQL

基本表的定义、删除与修改

定义基本表

create table <表名>(
<属性名1><数据类型>[列级完整性约束条件]，
  <属性名2><数据类型>[列级完整性约束条件]  
)

删除基本表

• 删除原有的列或约束规则

  ALTER TABLE <表名>

  DROP {[CONSTRAINT]<完整性约束>|COLUMN<列名>[CASCADE|RESTRICT]};

  RESTRICT: 在没有视图或约束引用该属性时，该属性列才能被删除

  CASCADE：表示删除某列时，所有引用该列的视图和约束也被自动删除

  例子：

  • 删除基本表S中学生的性别SEX属性列

    ALTER TABLE S
    DROP COLUMN SEX;
    

• 删除基本表

• DROP TABLE <表名>[CASCADE|RESTRICT]

  例子：

  • 删除基本表

  • DROP TABLE S CASCADE;
    

修改基本表

• 增加列表或约束规则

  ALTER TABLE <表名>

  ADD<属性列名><数据类型>[完整性约束]

  例子:

  • 在学生表S中加入一属性列表表示学生的籍贯

    alter table S add sh char(30);
    

  • 在学生关系表S补充定义SNO为主键

    lter table S add priamary key (SNO);
    

• 修改原有列的类型

  ALTER TABLE <表名>

  ADD COLUMN<属性列名> <数据类型>

  例子：

  • 将基本表S的学生姓名SN长度修改为12；

  alter table S;
  alter column SN char(12);
  

数据查询

单表查询

查询所有学生的选课情况

select * from sc;

用distinct对重复数据进行去重

select distinct sno from sc;

查询表中所有或指定属性

select * from 表;

逐一列举属性(列名)型:

查询所有学生的姓名以及年龄。

select sname,age from s;

使用函数获取表中不存在属性: 比如根据表中单价和数量计算出利润

select 函数或计算公式 from 表;

查询所有学生的学号，姓名和出生年份，并在查询结果中修改列表题分别为“学号”、“姓名”和“出生年份”。

select sno as 学号,sname as 姓名,year(now()) - age as 出生年份 from s;

聚集函数

何为聚集函数？

聚集函数（aggregate function）运行在行组上，计算和返回单个值的函数

• AVG()

  AVG()函数只用于单个列，并且忽略列值为NULL的行。

• COUNT()

  COUNT()函数进行计数。可以利用COUNT()确定表中行的数目或者符合特定条件的行的数目。

  COUNT()函数的两种使用方式：

  （1）使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

  （2）使用COUNT（column）对特定列中具有值的行进行计数，忽略NULL值。

• MAX()

  MAX()返回指定列中的最大值。MAX()要求指定列名。

  对于非数值数据使用MAX() ：虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

  MAX()忽略列值为NULL的行。

• MIN()

  MIN()的功能正好与MAX()功能相反。

  在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。

• SUM()

  （1）求出单列

  select * sum(column) as newName;
  

  （2）用于合计计算值

  select sum(item_price * quantity) as total_price;
  

  所有的聚集函数都可以在多个列上进行计算。

  SUM()函数忽略列值为NULL的行。

连接查询

• 也叫多表查询。常用于查询字段来自于多张表
• 如果直接查询两张表，将会得到笛卡尔积

连接分类

• 内连接

  select 查询列表
  from 表1 别名
  inner join 表2 别名 on 连接条件
  inner join 表3 别名 on 连接条件
  [where 筛选条件]
  [group by 分组]
  [having 分组后筛选]
  [order by 排序列表]
  

  • 等值连接

    • 查询每个员工所在的部门名

       select name, dept_name
      from employees
       inner join departments
      on employees.dept_id=departments.dept_id;
      

  • 非等值连接

    • 查询2018年12月员工基本工资级别

      select employee_id, date, basic, grade
       from salary as s
       inner join wage_grade as g
      on s.basic between g.low and g.high
       where year(date)=2018 and month(date)=12;
      

  • 自连接

    • 要点：

      • 将一张表作为两张使用
      • 每张表起一个别名

    • 查看哪些员的生日月份与入职月份相同

       select e.name, e.hire_date, em.birth_date
      from employees as e
      inner join employees as em
       on month(e.hire_date)=month(em.birth_date)
      and e.employee_id=em.employee_id;
      +-----------+------------+------------+
      | name      | hire_date  | birth_date |
      +-----------+------------+------------+
      | 李玉英    | 2012-01-19 | 1974-01-25 |
      | 郑静      | 2018-02-03 | 1997-02-14 |
      | 林刚      | 2007-09-19 | 1990-09-23 |
      | 刘桂兰    | 2003-10-14 | 1982-10-11 |
      | 张亮      | 2015-08-10 | 1996-08-25 |
      | 许欣      | 2011-09-09 | 1982-09-25 |
      | 王荣      | 2019-11-14 | 1999-11-22 |
      +-----------+------------+------------+
      7 rows in set (0.00 sec)
      

• 外连接

  • 常用于查询一个表中有，另一个表中没有的记录

  • 如果从表中有和它匹配的，则显示匹配的值

  • 如j要从表中没有和它匹配的，则显示NULL

  • 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录

  • 左外连接中，left join左边的是主表

  • 右外连接中，right join右边的是主表

  • 左外连接和右外连接可互换，实现相同的目标

  • 左外连接(重要)

    SELECT tb1.字段..., tb2.字段
    FROM table1 AS tb1
    LEFT OUTER JOIN table2 AS tb2 
    ON tb1.字段=tb2.字段
    

    • 查询所有部门的人员以及没有员工的部门

     select d.*, e.name
     from departments as d
    left outer join employees as e
     on d.dept_id=e.dept_id;
    

  • 右外连接(重要)

    SELECT tb1.字段..., tb2.字段
    FROM table1 AS tb1
    RIGHT OUTER JOIN table2 AS tb2 
    ON tb1.字段=tb2.字段
    

    • 查询所有部门的人员以及没有员工的部门

    • mysql> select d.*, e.name
          -> from employees as e
          -> right outer join departments as d
          -> on d.dept_id=e.dept_id;
      

  • 全外连接(mysql不支持，可以使用UNION实现相同的效果)

• 交叉连接

  • 返回笛卡尔积

  • 语法：

  • SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
    

  • 查询员工表和部门表的笛卡尔积

   select name, dept_name
   from employees
   cross join departments;
  

嵌套查询

子查询也称“内部查询”或者“嵌套查询”，是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。

子查询常用在 WHERE 子句和 FROM 子句后边：

• 当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。
• 当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

用于 WHERE 子句的子查询的基本语法如下：

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
    (SELECT column_name [, column_name ]
    FROM table1 [, table2 ]
    [WHERE])

用于 FROM 子句的子查询的基本语法如下：

SELECT column_name [, column_name ]
FROM (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE]) AS temp_table_name
WHERE  condition

示例:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

1. WHERE 子句中的子查询返回单行单列数据，也即子查询的结果只有一个值，一般是满足 WHERE 查询条件中的比较运算符，比如 >、<、>=、<=、=、!= 等。请看下面的代码：

SELECT *
   FROM CUSTOMERS
   WHERE SALARY > (SELECT SALARY
         FROM CUSTOMERS
         WHERE NAME='Komal');

2. WHERE 子句中的子查询返回单列多行数据，一般和 IN、NOT INT、ANY 关键字使用，也就是对集合范围的操作。 下面的代码将子查询和 IN 关键字一起使用

SQL> SELECT *
   FROM CUSTOMERS
   WHERE ID IN (SELECT ID
         FROM CUSTOMERS
         WHERE SALARY > 4500)
   AND AGE >= 25;

数据更新

插入数据

插入子查询结果

子查询嵌套在INSERT语句中以生成要插入的批量数据

语法：

INSERT
INTO<表名>[(<属性列1>[,<属性列2>]...)]
子查询;

-对每一个系，求学生的平均年龄，并把结果存入数据库

CREATE TABLE DEPT_AGE(SDEPT CHAR(15),AVG_AGE SMALLINT);
 
INSERT INTO  DEPT_AGE(SDEPT,AVG_AGE)
SELECT SDEPT,AVG(SAGE) FROM STUDENT GROUP BY SDEPT;

SELECT子句目标列必须与INTO子句匹配

• 关系数据库管理系统在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则

1. 实体完整性
2. 参照完整性（要先在引用外码的表中插入数据）
3. 用户定义的完整性

修改数据

删除数据

一般格式：

DELETE
FROM<表名>
[WHERE<条件>];

功能：从指定表中删除满足WHERE子句条件的所有元组

DEIETE语句删除的是表中的数据，而不是关于表的定义

1.3.1 删除某一个元组的值
–删除学号为201215128的学生记录

DELETE
FROM STUDENT
WHERE SNO='201215128'

1.3.2 删除多个元组的值
–删除所有的学生选课记录

DELETE
FROM SC

1.3.3 带子查询的删除语句
–删除计算机科学系所有学生的选课记录

DELETE
FROM SC
WHERE SNO IN(
    SELECT SNO
    FROM STUDENT
    WHERE SDEPT='CS');

视图

更新视图

指的是通过视图来插入、更新、删除表中的数据，使用的INSERT，UPDATE，DELETE语句。需要注意的是修改视图指的是修改视图本身，而更新视图则是更新视图数据

这里就是简单地增、删、改、查数据

示例：

创建用户表

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) NOT NULL,

  PRIMARY KEY (`id`)

) 

插入演示用户数据

INSERT INTO `t_user` VALUES ('1', '甲');

INSERT INTO `t_user` VALUES ('2', '乙');

INSERT INTO `t_user` VALUES ('3', '丙');

INSERT INTO `t_user` VALUES ('4', '丁');

INSERT INTO `t_user` VALUES ('5', '戍');

INSERT INTO `t_user` VALUES ('6', '己');

INSERT INTO `t_user` VALUES ('7', '庚');

INSERT INTO `t_user` VALUES ('8', '辛');

INSERT INTO `t_user` VALUES ('9', '壬');

INSERT INTO `t_user` VALUES ('10', '癸');

创建用户信息表

DROP TABLE IF EXISTS `t_user_info`;

CREATE TABLE `t_user_info` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `uid` int(11) DEFAULT NULL,

  `age` varchar(20) DEFAULT NULL,

  `sex` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) 

插入演示信息数据

INSERT INTO `t_user_info` VALUES ('1', '1', '11', '男');

INSERT INTO `t_user_info` VALUES ('2', '2', '12', '女');

INSERT INTO `t_user_info` VALUES ('3', '3', '13', '男');

INSERT INTO `t_user_info` VALUES ('4', '4', '14', '女');

INSERT INTO `t_user_info` VALUES ('5', '5', '15', '男');

INSERT INTO `t_user_info` VALUES ('6', '6', '16', '女');

INSERT INTO `t_user_info` VALUES ('7', '7', '17', '男');

INSERT INTO `t_user_info` VALUES ('8', '8', '18', '女');

INSERT INTO `t_user_info` VALUES ('9', '9', '19', '男');

INSERT INTO `t_user_info` VALUES ('10', '10', '20', '女');

创建视图

CREATE VIEW test_view_1 AS SELECT `name` FROM t_user;
CREATE VIEW test_view_2(username) AS SELECT `name` FROM t_user;
CREATE VIEW test_view_3 (username, userage, usersex) AS SELECT

        t_user.`name`,

        t_user_info.age,

        t_user_info.sex

FROM

        t_user,

        t_user_info

WHERE

        t_user.id = t_user_info.uid;

更新单表视图

插入

INSERT INTO test_view_1 VALUES ('子');

删除

DELETE FROM test_view_1 WHERE tname2='丑';

修改

UPDATE test_view_1 set tname2 = '丑' where tname2='子';

查询

SELECT * FROM test_view_1;

视图的作用

视图是一个虚拟的表

从数据库系统内部来看，视图是由一张或多张表中的数据组成的

从数据库系统外部来看，视图就如同一张表一样。

数据库完整性

实体完整性

定义参照完整性

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码

CREATE TABLE SC 
    (Sno CHAR(9) NOT NULL,
     Cno CHAR(4) NOT NULL,
     Grade SMALLINT,
     PRIMARY KEY(Sno,Cno),                      /*表级定义实体完整性*/
     FOREIGN KEY(Sno) REFERENCES Student(Sno),  /*表级定义参照完整性*/
     FOREIGN KEY(Cno) REFERENCES Course(Cno)    /*表级定义参照完整性*/
    );

实体完整性检查和违约处理

被参照表	参照表	违约处理
可能破坏参照完整性	插入元组	拒绝
可能破坏参照完整性	修改外码值	拒绝
删除元组	可能破坏参照完整性	拒绝(NO ACTION)/级联删除(CASCADE)/设置为空值
修改主码值	可能破坏参照完整性	拒绝(NO ACTION)/级联删除(CASCADE)/设置为空值

CREATE TABLE SC
    (Sno CHAR(9),
     Cno CHAR(4),
     Grade SMALLINT,
     PRIMARY KEY(Sno, Cno),     /*在表级定义实体完整性，Sno，Cno都不能取空值*/
     FOREIGN KEY(Sno) REFERENCES Student(Sno)   /*表级定义参照完整性*/
        ON DELETE CASCADE       
                        /*当删除Student表中的元组时，级联删除SC表中相应的元组*/
        ON UPDATE CASCADE
                        /*当更新Student表中的元组时，级联更新SC表中相应的元组*/
     FOREIGN KEY(Cno) REFERENCES Course(Cno)
        ON DELETE NO ACTION
                        /*当删除Course表中的元组造成SC表不一致时，拒绝删除*/
        ON UPDATE CASCADE
                        /*当更新Course表中的元组时，级联更新SC表中相应的元组*/
    );

关系数据理论

规范化

信息学术语，理论正是用来改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余问题。

就好像酒店的评级一样，五星级宾馆比小巷子里的无名酒店更规范，要更多，更加干净又卫生

数据库的规范化 - 六大范式解析

码

主码

若候选码多于一个，则选其中的一个为主码（Primary Key）；

候选码：

若关系中的某一属性或属性组的值能唯一标识一个元祖，而其任何真子集都不能再标识，则称该属性组为候选码

主属性

包含在任一候选码中的属性，叫做主属性（Primary Attribute）；

非主属性

不包含在任何码中的属性称为非主属性（Nonprime Attribute）或非码属性（Nonkey Attribute）

范式

第一范式

1NF是关系型数据库的基本要求，创建表时不符合1NF的操作一定不成功

第一范式就是不可再次分的

第二范式

第二范式(确保表中的每列都和主键（主属性）相关

第二范式可以在很大程度上减少数据库的冗余

例如：

第一范式：

订单编号	商品编号	商品名称	数量	单位	价格	客户	所属单位	联系方式
001	1	手机	1	部	1313131313￥	张三	玛卡巴发	123

主键（订单编号、商品编号）

在以上表中存在部分依赖：商品信息和商品价格和订单只和 商品编号这个主键相关，所以不符合第二范式，进行拆分

订单编号	客户	所属单位	联系方式
001	张三	玛卡发卡	123

订单编号	商品编号	数量
001	1	1

商品编号	商品名称	单位	商品价格
1	手机	部	12132131

且非主属性完全依赖于主属性.

第三范式

消除了第二范式中的传递依赖

(确保每列都和主键列直接相关,而不是间接相关)

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息

订单信息表

订单编号	订单项目	负责人	业务员	订单数量	客户编号
001	挖掘机	刘明	张三	1台	1

客户信息表

客户编号	客户名称	所属公司	联系方式
1	李四	玛卡巴卡	123456

BCNF

BCNF是比第三范式更严格一个范式。

它要求关系模型中所有的属性（包括主属性和非主属性）都不传递依赖于任何候选关键字。

也就是说，当关系型表中功能上互相依赖的那些列的每一列都是一个候选关键字时候，该满足BCNF。

BCNF实际上是在第三范式的基础上，进一步消除了主属性的传递依赖。

有这样一个配件管理表WPE(WNO,PNO,ENO,QNT),其中WNO表示仓库号，PNO表示配件号，ENO表示职工号，QNT表示数量。

有以下约束要求：

（1） 一个仓库有多名职工；

（2） 一个职工仅在一个仓库工作；

（3） 每个仓库里一种型号的配件由专人负责，但一个人可以管理几种配件；

（4） 同一种型号的配件可以分放在几个仓库中。

分析表中的函数依赖关系，可以得到：

（1） ENO->WNO;

（2） （WNO，PNO）->QNT

（3） （WNO，PNO）->ENO

（4） （ENO，PNO）->QNT

可以看到，候选键有：（ENO,PNO）;(WNO,PNO)。所以，ENO,PNO,WNO均为主属性，QNT为非主属性。显然，非主属性是直接依赖于候选键的。所以此表满足第三范式。

而我们观察一下主属性：（WNO,PNO）->ENO;ENO->WNO。显然WNO对于候选键（WNO,PNO）存在传递依赖，所以不符合BCNF.

解决这个问题的办法是分拆为两个表：管理表EP（ENO，PNO，QNT）；工作表EW（ENO，WNO）。但这样做会导致函数依赖（WNO,PNO）->ENO丢失。

4． 应用

虽然，不满足BCNF，也会导致一些冗余和一致性的问题。但是，将表分解成满足BCNF的表又可能丢失一些函数依赖。所以，一般情况下不会强制要求关系表要满足BCNF。

数据库编程

概念结构设计

人们把数据库设计分为需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行与维护6个阶段。概念结构设计就是对信息世界进行建模，常用的概念模型是E-R模型，它是P.P.S.Chen 于1976年提出来的。

各子系统的E-R图之间的冲突

属性冲突

属性域冲突，即属性值的类型、取值范围或取值集合不同。 例如零件号，有的厂商把它定义为整数类型，有的部门把它定义为字符类型。 年龄，某些部门以出生日期形式表示职工的年龄，而另一些部门用整数表示职工的年龄。 属性取值单位冲突。 例如，零件的重量有的以公斤为单位，有的以斤为单位，有的以克为单位。

命名冲突

同名异义，即不同意义的对象在不同的局部应用中具有相同的名字。
异名同义（一义多名），即同一意义的对象在不同的局部应用中具有不同的名字。
如对科研项目，财务科称为项目，科研处称为课题，生产管理处称为工程。
命名冲突可能发生在实体、联系一级上也可能发生在属性一级上

结构冲突

同一对象在不同应用中具有不同的抽象。
例如，职工在某一局部应用中被当作实体，而在另一局部应用中则被当作属性。
解决方法：把属性变换为实体或把实体变换为属性，使同一对象具有相同的抽象。
同一实体在不同子系统的E-R图中所包含的属性个数和属性排列次序不完全相同。
解决方法：使该实体的属性取各子系统的E-R图中属性的并集，再适当调整属性的次序。

实体间的联系在不同的E-R图中为不同的类型。
实体E1与E2在一个E-R图中是多对多联系，在另一个E-R图中是一对多联系
解决方法是根据应用的语义对实体联系的类型进行综合或调整。

逻辑结构设计

逻辑结构设计是将概念结构设计阶段完成的概念模型，转换成能被选定的数据库管理系统(DBMS)支持的数据模型。

E-R图向关系模型的转换

E-R图向关系模型的转换就是将实体型、属性和实体之间的联系转换为关系模式)，或者说是将E-R图中的内容如何存储到关系中

1.一个实体型转换为一个关系模式

2.一个联系集转换为一个关系模式—通用规则

（1）多对多联系（m:n）

转换为一个独立的关系模式

主码： 关系模式的主码为各实体主码的集合

学生（学号，姓名，专业）
课程（课程编号，课程名称，学分）

选修（学号，课程编号，成绩）
以上“斜体加粗”为主码

（2）一对一联系（1:1）

① 转换为一个独立的关系模式

主码： 每个实体的主码均是关系的候选码，从候选码任选一个作为主码

管理员（管理员号，姓名，联系方式）
仓库（仓库编号，面积）

管理（仓库编号，管理员号*）

以上“斜体加粗”为主码

② 与任一端的关系模式合并

• 选择任一个实体对应的关系进行合并

• 在该关系中加入另一个关系的主码与联系的属性

• 主码： 保持不变
  

• 将管理员关系加入仓库关系中如下

管理员（管理员号，姓名，联系方式）

仓库（仓库编号，面积，管理员号）

以上“斜体加粗”为主码

（3）一对多联系（1:n）

① 转换为一个独立的关系模式

主码： 关系模式的主码为n端的主码

专业（专业编号，专业名称，所在院系）
学生（学号，姓名）

学习（学号，专业编号）

②与n端的关系模式合并

• 一端的主码及联系的属性并入n端的关系模式

  

  专业（专业编号，专业名称，所在院系）

  学生（学号，姓名，专业编号）

（4）3个或3个以上实体间的多元联系

转换为一个关系模式

主码： 关系模式的主码为各实体码的组合

供应商（供应商号，供应商名，地址，电话）
项目（项目编号，项目名称，开工日期）
零件（零件号，零件名，材料，颜色）

使用（供应商号，项目编号，零件号*，用量）

（5）同一个实体内部的联系

将该实体拆分为互相联系的两个子集，按其不同的联系方式进行处理

职工（*职工号*，姓名，年龄，专业，领导职工号）

可看作一对多联系中的“与n端关系合并”，即领导关系并入职工关系，将其主码加入。

参考文献

什么是结构化数据？非结构化数据？半结构化数据？

（数据库系统概论|王珊）第一章绪论-第二节：数据模型

数据完整性约束

数据库系统模式

聚集函数

连接查询

嵌套查询

数据库设计三大范式

BCNF范式（修正的第三范式）、第四范式和第五范式

E-R图向关系模型的转换