本章，主要就数据库和表的基本操作作以总结。

需要事先说明的一点是，我写的所有MySQL的代码将采用大写形式。虽然说至少在MySQL环境下，SQL语句的大小写并不会影响代码运行结果，但是，有同行讲到，SQL语句大写比是一个更好地习惯。因为在执行程序的时候，如果你用的是小写，会先转化为大写字母然后执行，而如果大写，那就可以省一个转化的时间。在大型的ERP系统开发时，往往会这么要求。所以，我也就尽量规范一些了。算是帮自己养成好的习惯。

数据库的操作

MySQL中，数据库的操作基本有：创建，查看，选择，以及删除4种。

创建数据库：CREATE DATABASE database_name;，后面的database_name是你要创建的数据库的名称；
查看数据库：SHOW DATABASES;，查看已经创建的所有数据库
选择数据库：USE database_name;，选择名为database_name的数据库，很多数据库的操作都需要先选中一个数据库，再执行进一步的操作；
删除数据库：DROP DATABASE database_name;，删除名为database_name的数据库；

创建表

建好了数据库，我们选择一个数据库，来对它进行表的相关操作。表是数据在数据库中一种逻辑上的存储形式，和常见的电子表格类似，其中每一行代表一个记录（可以理解成一个数据对象），而记录的每一列代表一个字段（其实你可以理解成数据对象的属性）。

表的基本操作有创建，查看，删除，修改4种基本操作。我先从创建表说起。

CREATE TABLE table_name(
    Att_name_1 Data_type_1,
    Att_name_2 Data_type_2,
    ...
    Att_name_n Data_type_n,
);

其中，table_name表示要创建的表的名称；Att_name_i表示第i个字段（也就是属性）的名称，Data_type_i表示第i个属性所对应的值的数据类型。这里多说一句，Mysql中的数据类型大致上分为“整数”，“浮点数”，“定点数”，“位”，“日期”，“时间”，“字符串”这么几种。东西太杂，也没必要具体介绍，简单知道就行，具体用到时，可以再查。

举个例子，现在如下创建一个表"tab_student"（最好养成良好的编程习惯，这里为了表明在创建表，最好以 “tab_”+ 的形式设置表名）：

CREATE TABLE tab_student(
    ID VARCHAR(20),
    Score INT
);

表"tab_student"中有两个字段，学生的身份ID以及对应的成绩Score，其中，ID是一个VARCHAR(20)型的字符变量，里面的20是参数；Score则是一个整数类型的变量。

另外，如果想要查看当前数据库中有哪些表，可以先选择当前数据库，再通过语句SHOW TABLES查看。而具体查看已经创建的某个表的信息，则通过下面讲的方式了。

查看表

先说说如何查看数据库当中有哪些表。方法是选择数据库后，用语句SHOW TABLES：

USE test; # 选择数据库test
SHOW TABLES; # 查看test中的表tab_student

比如我们创建了数据库test，然后在test中创建了表tab_student，运行结果如下图所示：

可以看到系统给出了一个列表，里面是现在数据库test中所有的表。

那么如何查看某个表的具体信息呢？还是先选择数据库，然后用DESCRIBE语句查看。

USE test; # 选择数据库test
DESCRIBE tab_student; # 查看test中的表tab_student

运行结果如下图所示：

如果想要查看表结构更详细的定义，可以采用SHOW CREATE TABLE语句，例如：

SHOW CREATE TABLE tab_student;

需要注意的是，在显示表的详细信息时，可以用;, \g或者\G来结束语句（我上面的代码用的是;）。其实，如果用\G可以让表的详细信息显示得更加美观。运行结果你自己跑一下就能发现区别，我这里不给图了。

删除表

DROP TABLE table_name语句用来删除表，其中"table_name"是表名。

修改表

修改表主要分为修改表名和修改表中的字段。

1. 修改表名

ALTER TABLE old_table_name RENAME new_table_name语句用来修改表名，其中"old_table_name"是旧表名，"new_table_name"是新表名，当然，这里所要操作的对象一定得是存在的，不然会报错。

例如，还是上面的例子：

ALTER TABLE tab_student RENAME tab_student1;

我分两行写，是为了看起来更清楚。现在将表格tab_student更名为tab_student1。然后我们在用上面说的查看表格的命令，可以看到以新名命名的表格，同时查看旧的名字时，报错。

2. 增加字段

ADD语句在表格末尾加字段：

ALTER TABLE table_name ADD Att_name_1 Data_type_1;

在表"table_name"末尾，增加属性名为Att_name_1，数据类型为Data_type_1的字段

ADD ... FIRST语句在表格开头加字段：

ALTER TABLE table_name ADD Att_name_1 Data_type_1 FIRST;

在表"table_name"开头，增加属性名为Att_name_1，数据类型为Data_type_1的字段

ADD ... AFTER ...语句在指定字段之后添加字段：

ALTER TABLE table_name ADD Att_name_1 Data_type_1 AFTER Att_name_2;

在属性名为Att_name_2的字段后，增加属性名为Att_name_1，数据类型为Data_type_1的字段

就上边那个"tab_student"表为例，我现在在字段ID的后面增加一个属性名为Gender，数据类型为CHAR的字段。

ALTER TABLE tab_student ADD Gender CHAR AFTER ID;

可以看到一开始如左图的表变成了右图的样子。其他添加字段的操作类似，这里不举例了。

3. 删除字段

语法形式如下：

ALTER TABLE table_name DROP Att_name;

将属性名为"Att_name"的字段删除。

4. 修改字段

修改字段的数据类型：

ALTER TABLE table_name MODIFY Att_name New_data_type;

将属性名为"Att_name"的字段的数据类型新设置为"New_data_type"。

修改字段名：

ALTER TABLE table_name CHANGE Old_att_name New_att_name Old_data_type;

将属性名为"Old_att_name"，数据类型为"Old_data_type"的字段名修改为"New_att_name"。注意此处用的是CHANGE而非MODIFY（用混了会报错），再者，三个参数在代码中的顺序不要搞错。以表tab_student为例，我们现在想要将字段名"ID"改为"IDs"，代码如下：

ALTER TABLE tab_student CHANGE ID IDs CHAR;

这样，将表的形式由下面的左图变成了右图：

同时修改字段名和数据类型：

ALTER TABLE table_name CHANGE Old_att_name New_att_name New_data_type;

将属性名为"Old_att_name"的字段名修改为"New_att_name"，数据类型为"New_data_type"的字段。基本语法结构与上面的修改字段名是一样的。

修改字段顺序：

ALTER TABLE table_name MODIFY Att_name_1 Data_type_1 FIRST (AFTER Att_name_2);

我解释一下，括号里面是另一种形式，先不看，括号外头的意思是将属性名为"Att_name_1"，数据类型为"Data_type_1"的字段放置于表的开头。而括号里面的是另一种修改次序的方法，表示将属性名为"Att_name_1"，数据类型为"Data_type_1"的字段放置于属性名为"Att_name_2"的字段后面。

以表tab_student为例，现在将字段"Gender"放置到字段"Score"之后，代码如下：

ALTER TABLE tab_student MODIFY Gender CHAR AFTER Score;

tab_student表的形式由下面的左图变为右图：

表的约束

表的约束是用来检验表中数据的完整性的，说的直白一些，就是检验表中的数据是否满足了规定的条件。以tab_student表举例来说，如果我不愿意令字段ID为空，就可以在建表时定义相关的约束。如果用户在插入数据时，违反了这样的约束条件，就会报错。当然可以定义的约束条件分很多种，下面会一一讲解。

1. 设置非空约束

关键字NOT NULL用来约束某字段不能为空，语法形式如下：

CREATE TABLE table_name(
    Att_name Data_type NOT NULL,
    ...
);

上述代码将字段"Att_name"设置为非空。

2. 设置字段的默认值

关键字DEFAULT用来设置字段的默认值。如果在插入数据时，没有为某个字段赋值，则Mysql会自动为这个字段赋值为默认值。其语法形式如下：

CREATE TABLE table_name(
    Att_name Data_type DEFAULT deVal,
    ...
);

上述代码将字段"Att_name"的默认值设置为"deVal"。

3. 设置唯一约束

关键字UNIQUE用来设置对某个字段的唯一约束，唯一约束表示，插入的数据在这个字段上的值得是唯一的，不能重复（你可以类比一般编程语言中“键值对”的“键”）。以学生的信息系统为例，每个人的学号信息应该是唯一的，那么在录入数据时，如果这一项出现了重复，那必然是出错了。所以我们最好设置唯一值约束，如果出现重复，则报错。其语法形式如下：

CREATE TABLE table_name(
    Att_name Data_type UNIQUE,
    ...
);

上述代码将字段"Att_name"设置为了唯一约束。

现在以上面的tab_student为例，我对tab_student中的属性设置上面说的“非空”，“默认值”，“唯一”的约束：

CREATE TABLE tab_student(
    ID VARCHAR(20) NULL UNIQUE, # 设置学生ID非空，且唯一
    Score INT DEFAULT 0, # 设置Score默认值为0
    Gender CHAR
);

我们看到表会变成下面的样子：

4. 设置主键约束

主键的作用在于用某个字段唯一地标识所有记录，以便在数据库中快速地查找数据。比如我们一般用索引号来唯一标识每一条数据记录。由于主键可以是单一字段，也可以是多个字段，所以，下面分两种情况讨论：

单字段主键：用关键字PRIMARY KEY设置主键。比如，还是tab_student的例子，现在将学号设置为主键。

CREATE TABLE tab_student(
    ID VARCHAR(20) PRIMARY KEY, # 设置学生ID为主键
    Score INT,
    Gender CHAR
);

我们查看刚才建的表：

发现ID字段也是非空的，其实，设置了主键，则主键字段必然是非空且唯一的。

多字段主键：当主键由多个字段组合而成时，则需要关键字CONSTRAINT和PRIMARY KEY配合设置。其语法结构如下：

CREATE TABLE tab_student(
    ID VARCHAR(20),
    Name VARCHAR(20),
    Score INT,
    CONSTRAINT ID_Name PRIMARY KEY (ID, Name)
);

在上面代码展示的例子中，语句CONSTRAINT ID_Name PRIMARY KEY (ID, Name)，将学生的ID和Name组合起来设置为主键。最后括号内表示的是主键涵盖的字段，而CONSTRAINT后面的ID_Name则是为这个约束起的名字（就设置主键约束来说，这个名字不要也没什么影响）。

例子最后生成的表信息如下：

5. 设置字段值自动增加

往表中插入记录时，往往会遇到需要递增的字段。例如商品编号啊，等等。Mysql支持对这类字段设置字段值自动增加的约束。默认情况下，设置了自动增加的字段值为整数，从1开始，每次加1。又因为这种自动增加的字段肯定是唯一的，所以一般我们也把它设置为主键。

设置自动增加的关键字为AUTO_INCREMENT

CREATE TABLE tab_student(
    ID INT PRIMARY KEY AUTO_INCREMENT, # 将ID设置为整数类型，并且设置字段值自动增加
    Name VARCHAR(20),
    Score INT
);

现在这个表示这样的：

注意，如果要将某个字段设置为自动增加，那么必须将这个字段设置“唯一”的属性，即设置为主键或者唯一约束，否则会报错。

6. 设置外键约束

上面的5种约束都是对于单表，而外键约束则是保证多个表之间的参照完整性。

设置外键约束的两个表之间具备父子关系，即子表中某个字段的取值依赖于父表。举例来说，现在有两个表：部门表和雇员表，部门表的主键为部门编号depNo，而雇员表中表示雇员所在部门编号的字段depno就成为了雇员表（子表）的外键。外键设置时有以下几点要求：

外键所代表的字段必须依赖于已经存在的父表的主键；
外键与相应的父表的主键可以不同名；
外键可以为空（null）

设置外键的语法用FOREIGN KEY (attribute) + REFERENCES father_table_name (attribute)的形式：

CREATE TABLE child_table_name(
    Att_name_1, DataType_1,
    Att_name_2, DataType_2,
    CONSTRAINT FK_name FOREIGN KEY (Att_name_1) REFERENCES father_table_name (Att_name)
);

收起

1，概念

1）数据库

数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。
数据库中存储的是数据及数据之间的关系。

正常情况读写文件系统比数据库快一到两个数据级；
数据库的查询，大量并发的时候可能最浪费时间的是connect和close。
数据库的优势是体现的大量数据的查询、统计以及并发读写，不是在速度上。

2）数据库数据特点

永久存储、有组织、可共享。
（数据的最小存取单位是数据项）

3）数据库系统的特点

①数据结构化

②数据的共享性，冗余度，易扩充

③数据独立性高

数据独立性包括：物理独立性和逻辑独立性
a)物理独立性(外模式\模式映像)：
用户程序不需要了解，应用程序要处理的只是数据的逻辑结构，这样当数据的物理存储改变了，应用程序不用改变。
b)逻辑独立性（模式\内模式映像）：
逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的，即，当数据的逻辑结构改变时，用户程序也可以不变。
逻辑数据独立性（logical data independence)是指概念模式改变，外模式和应用程序不变。在逻辑数据独立性里，数据的逻辑结构发生改变或存储关系的选择发生改变时用户不会受到影响。改变概念模式，例如增加和删除实体、增加和删除属性、增加和删除联系，不需要改变现有的外模式或重写应用程序。在DBMS中只需要修改视图的定义和映像来支持逻辑数据独立性。对用户来说，不再关心所做的修改是非常重要的。换句话说，模式经过逻辑重构之后，根据外模式构建的应用程序还是和从前一样工作。

4）概念模型（E-R模型）

①概念

概念模型的一种表示方法：实体联系方法，用E-R方法（E-R模型）来描述。
概念模型是用于信息世界的建模，是一种信息模型，与具体的DBMS无关。且能满足用户对数据的处理要求，易于修改。
概念模型与具体数据模型无关且容易向数据库模型转化。

实体：举行表示
属性：椭圆表示，并用直线与实体连接
联系：菱形表示，用直线与实体连接，同时在边上标上联系的类型（1：1，1：n，m:n)。

一个联系转化为一个关系模式，与该联系相连的各实体的码以及联系的属性转化为关系的属性，该关系的码则有三种情况：
若联系为1：1，则每个实体的码均是该关系的后选码。
若联系为1：n，则关系的码为n端实体的码。
若联系为m：n，则关系的码为诸实体码的组合。

数据库模式定义语言DDL(Data Definition Language)：是用于描述数据库中要存储的现实世界实体的语言。一个数据库模式包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作方法定义等。

数据库逻辑设计: 将概念设计所得到的概念模型转换为某一具体的数据模型（层次、网状、关系、面向对象）.

5）关系完整性

在关系模型中,关系完整性主要是指以下三方面：

实体完整性

所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值；
比如学生表的主码通常是取学号为主码

参照完整性

是指参照关系中每个元素的外码要么为空(NULL)，要么等于被参照关系中某个元素的主码；
参照关系也称为外键表，被参照关系也称为主键表。

用户定义的完整性

指对关系中每个属性的取值作一个限制(或称为约束)的具体定义。比如性别属性只能取”男“或”女“，再就是年龄的取值范围,可以取值0-130 ，但不能取负数，因为年龄不可能是负数。

6）关系数据库规范化

目地：使结构更合理，消除存储异常，使数据冗余尽量小，便于插入、删除和更新。
原则：遵从概念单一化“一事一地”原则，即一个关系模式描述一个实体或实体间的一种联系。
规范的实质：概念的单一化。
规范化的方法：将关系模式投影分解成两个或两个以上的关系模式。

2，依赖和范式

1）依赖

①部分函数依赖

设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。

    举个例子：通过AB能得出C，通过A也能得出C，通过B也能得出C，那么说C部分依赖于AB。

②完全函数依赖

设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。

    举个例子：通过AB能得出C，但是AB单独得不出C，那么说C完全依赖于AB.

③传递函数依赖

设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。

    举个例子：通过A得到B，通过B得到C，但是C得不到B，B得不到A，那么成C传递依赖于A

④多值依赖

设R(U)是属性集U上的一个关系模式。X，Y，Z是U的子集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成立，当且仅当对R(U)的任一关系r,给定的一对(x，z)值有一组Y的值，这组值仅仅决定于x值而与z值无关。

举例：
有这样一个关系 <仓库管理员，仓库号，库存产品号> ，假设一个产品只能放到一个仓库中，但是一个仓库可以有若干管理员，那么对应于一个 <仓库管理员，库存产品号>有一个仓库号，而实际上，这个仓库号只与库存产品号有关，与管理员无关，就说这是多值依赖。

2）范式

各个范式联系：
5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF

①1NF（满足最低要求的范式：字段不可再分，原子性）

如果一个关系模式R的所有属性都是不可分的基本数据项，则R∈1NF。
自我理解1NF就是无重复的列。
如：（X1，X2）→X3，X2→X3 其中x3对x2部分依赖
如：（X1，X2）→X3，X2→X4 其中有非主属性X4部分依赖于候选键{X1,X2}，所以这个关系模式不为第二范式；又因为范式之间的关系满足1NF⊇2NF⊇3NF ⊇ BCNF，所以是第一范式。

②2NF（消除部分子函数依赖：一个表只能说明一个事物）

若R∈1NF，且每一个非主属性完全函数依赖于码，则R∈2NF。
即要求数据库表中的每个实例或行必须可以被唯一地区分。

③3NF（消除传递依赖，即消除非主属性对键的传递依赖：每列都与主键有直接关系，不存在传递依赖。任何非主属性不依赖于其它非主属性。）

若R∈3NF，则每一个非主属性既不部分依赖于码，也不传递依赖于码。
自我理解是：表中所有的数据元素不但要能唯一地被主键所标识，而且他们之间还必须相互独立，不存在其他的函数关系。

④BCNF(修正第三范式、扩充第三范式消除主属性对键的传递依赖）

所有非主属性对每一个码都是完全函数依赖；
所有主属性对每一个不包含它的码，也是完全函数依赖；
没有任何属性完全函数依赖于非码的任何一组属性。

⑤4NF

关系模式R<U,F>∈1NF，如果对于R的每个非平凡多值依赖X->->Y（Y∉X），X都含有码，则称R<U,F>∈4NF

3，数据库平台

数据库管理系统（DBMS）：是系统软件，是数据库系统的核心。
常见数据库管理系统有：Access、mysql、sql server

4，数据库语句

SQL 语言是非过程化的语言，易学习。
SQL语言具有两种使用方式：一种是在终端交互方式下使用，称为交互式SQL；另一种是嵌入在高级语言的程序中使用，称为嵌入式SQL，而这些高级语言可以是C、PASCAL、COBOL等，称为宿主语言。

1）基本对象

关系数据库系统支持三级模式结构，其概念模式、外模式和内模式中的基本对象有表、视图和索引。
三级模式结构有效地组织、管理数据，提高了数据库的逻辑独立性和物理独立性。使数据库达到了数据独立性。

①模式（schema，逻辑模式）

A.概念

是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。是数据库系统模式结构的中间层，即不涉及数据的物理存储细节和硬件环境，也与具体的应用程序、开发工具及高级设计语言无关。
模式是数据库数据在逻辑级上的视图，一个数据库只有一个模式。

也用于区分一个大项目中的各个小项目，这样若有相同名字的表的话, 不同模式不会发生冲突。相当于编程时的命名空间。
如：
一个公司的系统，分2个子系统，分别为财务系统和人力资源系统.
这2个子系统, 共用一个数据库。
那么财务系统的表, 可以放在财务的模式（schema）.
人力资源系统的表，放在人力资源系统的模式里面。
这2个子系统，能够互相访问对方的表。
但是又不因为表重名的问题，影响对方。

B.访问

访问具体的一个表，可以由 4个部分组成
分别为服务器名，数据库名，模式名，表名。

对于访问本地的数据库：
不指定模式名的话，数据库默认使用dbo模式。
（DBO是每个数据库的默认用户，具有所有者权限，即DbOwner ）
pg不指定模式的话默认使用public模式。

C.操作

--创建
CREATE SCHEMA schema_name;

②外模式（子模式，用户模式）

是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
外模式通常是模式的子集，一个数据库可以有多个外模式，但一个应用程序只能有一个外模式。
外模式是保证数据库安全性的一个有力措施：用户只能访问外模式的数据，其余数据不可见。

③内模式（存储模式）

一个数据库只有一个内模式。
内模式是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。

数据库管理系统在三级模式之间提供了两层映像：
外模式/模式映像（保证数据的逻辑独立性）
模式/内模式映像（保证了物理独立性）

④表

表分为临时表和永久表。

临时表

临时表存储在tempdb中（如下），当不再使用时会自动删除。

IF OBJECT_ID('tempdb..#ownerAnnouce') IS NOT NULL

根据进程独立，只有进程的拥有者有表的访问权限，其它用户不能访问该表；
不同的用户进程，创建的临时表虽然“名字”相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证临时表的进程独立性。

临时表有两种类型：本地和全局。

A.本地临时表

名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。

B.全局临时表

名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

临时表优点

真正的临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的I/O次数，因此也提高了系统效率。

临时表的创建

A. create table #临时表名
B.select * into #临时表名 from 表名（永久表或临时表）

⑤视图

A.概念

视图是一张虚拟表，视图的字段是自定义的，视图只支持查询，查询数据来源于实体表。

一般视图是只读的，在pg中通过添加规则可以进行视图的更新。从pg9.1开始，用户可以通过INSTEAD OF的触发器来实现视图更新。

B.优缺点

优点
视图可以将多个复杂关联表提取信息，方便查询，但不能优化查询速度（调用视图查询时才进行动态检索数据）。
即，如果你认为一个sql查询非常慢，为了优化它的速度把它建立成视图，这是不可取的，视图是每次调用的时候生成，并不是数据源变化就刷新数据，并不能提高检索效率。
缺点
视图就是临时表，即调即用，如果数据源没有任何变化，在反复调用中，临时表会缓存到内存中（SHOW STATUS LIKE ‘Qcache%’;），视图中不能创建索引，但视图可以基于索引生成。

C.场景

重用SQL语句；
简化复杂SQL操作（生成视图），重用查询且不需要知道基本查询细节。
保护数据。用户有表的部分权限。
更改数据格式和表示。视图可返回与底层表不同的表示和格式。

D.操作

--创建视图
CREATE OR REPLACE VIEW view_name(studentName, studentAge)  --(studentName, studentAge) 可以去掉，加上是重命名列名
AS 
SELECT user_info.name, user_info.age from user_info;

--删除视图
DROP VIEW view_name;

⑥实体视图

相对于普通的视图来说，实体化视图的不同之处在于实体化视图管理存储数据，占据数据库的物理空间。

实体化视图的结果会保存在一个普通的数据表中，在对实体化视图进行查询的时候不再会对创建实体化视图的基表进行查询，而是直接查询实体化视图对应的结果表，然后通过定期的刷新机制来更新实体化视图表中的数据。

demo

-- 创建物化视图
CREATE MATERIALIZED VIEW MAX_ID_MVIEW 
AS
  SELECT PART_ID, MAX(ID)  MAX_ID
  FROM PART_DETAIL GROUP BY PART_ID;
  
-- 如果刷新时不带CONCURRENTLY则无需创建唯一索引
CREATE UNIQUE INDEX IDX_MAX_ID ON MAX_ID_MVIEW(PART_ID);

-- 利用watch命令每120s刷新一次物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY MAX_ID_MVIEW; \watch 120

作用

减轻网络负担：通过实体化视图将数据从一个数据库分发到多个不同的数据库上，通过对多个数据库访问来减轻对单个数据库的网络负担。
搭建分发环境：通过从一个中央数据库将数据分发到多个节点数据库，达到分发数据的目的。
复制数据子集：实体化视图可以进行行级/列级的筛选，这样可以复制需要的那一部分数据。
实体化视图是用于汇总,预计算,复制或分发数据的对象, 在大型的数据库中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度。
物化视图的快速刷新采用了增量的机制，在刷新时，只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。

⑦索引

为了改变数据库的性能和可访问性所增加的一组辅助性数据。
详细介绍见下文。

2）数据结构创建及修改

1>数据库操作

--查看数据库
show databases;
--建库
create database children;
--删库
drop database children;
--调用数据库
use children;

2>表操作

--pg建表
CREATE TABLE if not exists public.stu_info(   --创建public模式下的表
    FOREIGN KEY (ID) REFERENCES people_info (ID),  --单个外键，一般情况下不建议增加这种强约束
    id int8 PRIMARY KEY,    --系统会自动为主键创建一个隐含的索引  primary key(Sno,Cno)组合主键
    address VARCHAR (255) UNIQUE NOT NULL,
    birthday TIMESTAMP NOT NULL,  
    age int default 15,  --默认值，影响后续插入值。但对旧数据没有影响。
   CONSTRAINT student2_pkey PRIMARY KEY (id),
   CONSTRAINT ck_age CHECK(age<18), --检查约束，约束某些字段需要满足的要求。NULL被认为满足条件。
   CONSTRAINT uk_tbl_unique_a_b unique(id ,address) --唯一约束。唯一键中可以写入任意多个NULL！即可以存在多组 1，null  
)
WITH (
  OIDS=FALSE
);
ALTER TABLE myschema.tb_test
  OWNER TO postgres;

--重命名表
alter table tableName RENAME TO newName;--pg

i>指定默认值

一般用于数据预置或create_time、update_time的自动录入。各个DBMS获得系统日期如下：

DBMS	函数/变量
Access	NOW()
DB2	CURRENT_DATE
MySQL	CURRENT_DATE()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	GETDATE()
SQLite	date(‘now’)

--修改默认值
alter table tableName alter column age set DEFAULT 15;--pg
--删除默认值
alter table tableName alter column age drop DEFAULT 15;--pg

ii>表约束

表约束有：主键、外键、检查约束、唯一约束、非NULL约束。

--添加主键(有些DBMS不允许在建表之后修改主键）
ALTER TABLE tableName ADD PRIMARY KEY(fieldName) ; --fieldName在库中不能有重复数据
--增加约束
alter table tableName add check (age<16);--pg 增加检查约束，约束名为：tableName_age_check
alter table tableName add constraint uk_tbl_unique_a_b unique (a,b);--pg 增加唯一约束
alter table tableName alter column fieldName set NOT NULL;--pg 增加非空约束
--删除约束
alter table tableName drop constraint constraintName;--pg 根据约束名删除检查约束、唯一约束
alter table tableName alter column fieldName drop NOT NULL;--pg 删除非空约束（非空约束没有约束名）

iii>修改表字段

--增加列\添加一个字段
alter table tableName add column columnName varchar(30) default 'a' not null; --column 可加可不加
--删除列（会连同字段上的约束一并删除）
alter table tableName drop column columnName; --column 可加可不加
--修改列名：
alter table tableName rename column fieldName TO fieldNameNew;--pg、oracle中
exec sp_rename '[表名].[列名]‘，’[表名].[新列名]'--在sqlserver
ALTER TABLE 表名 CHANGE 列名 新列名 列类型--mysql

--修改字段类型或长度:
alter table tableName modify column 字段名 类型;
alter table tableName alter column fieldName TYPE text;--pg修改字段数据类型。仅在当前数据都可以隐式转换为新类型时才可以执行成功
--将NAME最大列宽增加到10个字符
ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

3）数据查询

数据库处理一个查询的步骤：
客户端连接->查询缓存->解析器->预处理器->查询优化器->查询执行引擎->数据

1. 客户端发送一条查询给服务器；
2. 服务器先会检查查询缓存query cache，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
3. 服务器端进行SQL解析parsing、预处理transition，再由优化器optimization生成对应的执行计划；
4. 根据优化器生成的执行计划，调用存储引擎的API来执行分布distribution查询；
5. 将结果返回给客户端。

1>简单查询

select * from student;
select 1+2; #当表达式与表列无关时，在pg和mysql中不适用“from tableName”

拼接查询：
Access和 SQL Server使用 + 号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base 使用 ||。

select label || '_' || id from user_info;  --结果：abc_1

2>条件查询

功能	表达	举例	备注
等于	`=`
不等于	`<>`或`!=`
空值	`is null，is not null`	`select * from student where class is not null;`
确定集合，in	`in` ，`not in`	`select * from student where age not in(21,23);`	在sql标准中仅支持100个以内的占位符作为查询参数。根据数据库不同，对in的参数和长度有不同的限制，否则会直接报错。
确定范围	`between and` , `not between and`
模糊查询	`like` ,`not like`	`select * from student where name like '%丽%';’` %代表任意长度（可为0）的字符串；_（下划线）：代表任意单个字符。（汉字代表2个字符，所以一个汉字用两个下划线）；\为转义字符

select出的别名是否可以作为where查询条件？不能，因为执行计划中where在selectz之前。如：select label a from asset_field where a = '分类'

3>排序查询

非排序查询的数据顺序：pg默认返回数据的顺序是插入表的数据顺序。

# 单个排序：
select name,age from student order by age desc; # 默认为asc：升序排列。desc：降序排序。

#多重排序：
order by 字段5,字段6 asc  //先按字段5排序，再按字段6排序

4>case when then查询

--简单case函数
case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' end
--case搜索函数
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' end

应用：

select (case sex
		  when '1' then '男'
		  when '2' then '女’
		  else '其他' end)sex from student where class = 11;

5>where、group by、having

大部分的where都可以背having代替，不同的是where过滤行，而having过滤分组，用在group by之后。(where在分组前过滤，having在分组后过滤）

select class,avg(age) as age from student 
group by class 
having avg(age)>23 /*要求平均年龄大于23*/

where肯定在group by 之前
where后的条件表达式里不允许使用聚合函数，而having可以。

6> 函数

聚合函数

avg平均数，同min(age)、max(age)、sum(age)

select avg(age)  as age from student group by class order by age desc;

count

select count(class)from student;
/*数量 因为使用了92标准，所以null不计入count*/
count(*) 跟count(1) 的结果一样，返回记录的总行数，都包括对NULL 的统计，
count(column) 是不包括NULL 的统计。

distinct

select distinct(class)from student;/*去重复，出现所有不同的内容*/
select count(distinct(class)) from student;

其它

LEFT（“123456789”，LEN（“数据库”））/*分两步运算，第一步是运算LEN函数，结果是3。第二步针对123456789这个字符从左边开始连续取三个数*/

select top 100 * from student where no=11;/*显示前100行*/
select isnull(name,'无') as name,age,class from student;/*isnull之后就无列名了 用as给列重命名*/
select name,age,class,'the name is' + name as introduce from student;/*用加号形成一个自定义列*/

7>SQL-92 规则

是数据库的一个标准。以下代码写在存储过程前面，表示遵从SQL-92 规则。
SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<) 比较时取值为 FALSE。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
即使 column_name 中包含非空值，使用 WHERE column_name < NULL 的 SELECT 语句仍会返回零行。

SET QUOTED_IDENTIFIER ON
为ON：标识符可以由双引号分隔，而文字必须由单引号分隔。
为OFF：标识符不可加引号。

8>多层查询 EXISTS

如果内层查询语句查询到符合条件的记录，就返回一个真值（true），否则，将返回
一个假值（false）。

SELECT * FROM employee
WHERE EXISTS
(SELECT d_name FROM department WHERE d_id=1003);

同理还有：NOT EXISTS。

9>关联查询、联结（JOIN）表

关系数据库设计中表的设计是把信息分解成多个表，一类数据一个表，各表通过某些共同的值相互关联。
一般情况下我们不建议建立外键这种强关联的关联信息。

可伸缩（scale）
能够适应不断增加的工作量而不失败。关系数据库的可伸缩性远远优于非关系数据库。

注意：

联结的表越多效率越低。
SQL本身不限制联结表的数目，但DBMS有最大数目限制。
一般情况下，联结查询比子查询快，实际应用中应该尝试两种方法看哪种快。

JSON类型	说明	备注
JOIN	如果表中有至少一个匹配，则返回行	INNER已省略。外联结比内联结返回的行数多（还包括没有关联的行）
LEFT JOIN	即使右表中没有匹配，也从左表返回所有的行	OUTER已省略
RIGHT JOIN	即使左表中没有匹配，也从右表返回所有的行	OUTER已省略
FULL JOIN	只要其中一个表中存在匹配，就返回行	OUTER已省略

luo_persons表：

id_p	last_name	first_name	address	city

luo_orders表：

id_o	order_no	id_p

要求输出：谁订购了产品，并且他们订购了什么产品？

①联表查询（等值联结，equijoin）

SELECT
	a.last_name, a.first_name, b.order_no
FROM
	luo_persons a,
	luo_orders b 
WHERE
	a.id_p = b.id_p

②join查询（内联结，inner join，推荐）

/*（推荐）等值联结明确指定联结类型可转换为inner join

SELECT
	last_name,
	first_name,
	order_no 
FROM
	luo_persons
	INNER JOIN luo_orders ON luo_persons.id_p = luo_orders.id_p

③union查询（复合查询、并查询）

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

注意：

UNION 内部的 SELECT 语句必须拥有相同数量的列、表达式或聚集函数。列也必须拥有相似的数据类型（可以不完全相同，但是可以互相转换）。同时，每条 SELECT 语句中的列的顺序必须相同。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
UNION能组合的最大语句数目限制需要查询具体的DBMS文档。

	select id_p from luo_persons 
	union 
	SELECT id_p from luo_orders

某些DBMS中还支持其它类型的UNION：

EXCEPT（或MINUS）：检索在第一个表中存在而在第二个表中不存在的行；
INTERSECT：检索两个表中都存在的行。

4）数据更新

①数据插入

i> insert

insert into tableName(no,name) values（'1','kate');
--按表中列的顺序，但如果表结构发生了变化那么对应 sql也要改。不推荐
insert into product values('001','001','N','N');

有自增长主键(id)的插入：
i>可以把id的值设置为null或者0，这样mysql会自己做处理
ii>手动指定需要插入的列，不插入这一个字段的数据！

ii> insert select

将select结果插入表中，一般用于可重复执行的sql。
注：
1.insert select语句中，如果select返回多行，那么会insert多行数据。

INSERT INTO "public"."vendors"("vend_name", "vend_id") select 'vend_name1', 1 
WHERE NOT EXISTS (select 1  FROM "public"."vendors" WHERE vend_id = 1);

iii> select into

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
select into 可以从多个表中检索数据，但只能插入到一个表中。

函数里面，把一个查询出来的值存入临时变量：

SELECT LastName,FirstName
INTO _lName,_fName  FROM Persons

也可以存入临时表中：

SELECT *
INTO Persons_backup
FROM Persons

②数据修改

update tableName set name = 'Tom' where name='kate';
update tableName set age = age + 1;

5）数据删除

删除表中几行：

DELETE FROM Person WHERE LastName = 'Wilson'

删除表中所有行，保留表、不释放空间。所删除的每行记录都会进日志，可以回滚。

DELETE FROM table_name

删除表：删除内容和定义，释放空间

drop table user;    
DROP TABLE IF EXISTS "public"."role_relation"; 可重复执行sql

删除表中所有数据，保留表、同时释放空间（速度比delete快，但是无法撤回，日志里面只记录页释放）：

truncate table book;

truncate是DDL语句（Data Definition，数据定义语句），相当于用重新定义一个新表的方法把原表的内容直接丢弃了，所以执行起来很快。delete语句是DML语句（Data Manipulation，数据操作语句），把数据一条一条的删除，所以删除多行数据执行较慢。

6）其他注意

①加中括号

列名、表名、存储过程名、函数名等都可以按需要加中括号。防止某些关键字在应用中引起歧义。

select [select] from 表名;

7）数据库授权

①授权GRANT

    GRANT <权限>
    ON <对象类型>  <对象名>
    TO <用户>
    [WITH GRANT OPTION]  // 如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他用户，允许用户传递权限，但是不允许循环授权。

举例：

例1：把查询Student表的权限授给用户U1
GRANT SELECT
ON TABLE Student
TO U1;

例2：把全部操作权限授予用户U2和U3
GRANT ALL PRIVILEGES
ON TABLE Student,Course
TO U2,U3;

例3：把查询权限授予所有用户
GRANT SELECT
ON TABLE SC
TO PUBLIC;

③权限的收回 REVOKE

REVOKE <权限>
ON <对象类型>  <对象名>
FROM <用户>

举例：

例6：收回所有用户对表sc的查询权限
REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

③对用户模式的授权

由DBA（数据库管理员，Database Administrator，简称DBA）在创建用户时实现。

CREATE USER <username>
[WITH] [DBA|RESOURCE|CONNECT]

只有系统的超级用户才有权创建一个新的数据库用户
新创建的用户有三种权限：DB，|RESOURCE，CONNECT

④数据库角色创建及授权

CREATE ROLE <角色名>

给角色授权：

GRANT <权限>
ON <对象类型>  对象名
TO <角色>

将一个角色授予其他的角色或用户

GRANT <角色1>
TO <角色3>
[WITH ADMIN OPTION]//如果指定了WITH ADMIN OPTION 子句，则获得某种权限的角色或用户还可以把这种权限再授予其他角色

角色权限的收回

REVOKE <权限>
ON <对象类型>  <对象名>
FROM <角色>

⑤DENY 拒绝账户访问

在安全系统中创建一项，以拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。

DENY { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]

和授权区别：
不授权是没有权限，但是如果这个用户属于某个角色，这个角色有了权限，那么这个用户可以从角色继承这个权限。如果选择了deny，即使这个用户属于某个具有权限的角色，他也没有权限。

8）数据类型

①uniqueidentifier

可存储16字节的二进制值，其作用与全局唯一标记符（GUID）一样。GUID是唯一的二进制数：世界上的任何两台计算机都不会生成重复的GUID值。GUID主要用于在用于多个节点，多台计算机的网络中，分配必须具有唯一性的标识符。

9）函数

①OBJECT_ID

A. 返回指定对象的对象 ID

USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder') AS 'Object ID';
GO

B. 验证对象是否存在

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

N是显式的将非unicode字符转成unicode字符，它来自 SQL-92 标准中的 National（Unicode）数据类型，用于扩展和标准化，在这里可以不用，写作object_id(PerPersonData)。

10）SQL中的借书经典案例

①问题描述

本题用到下面三个关系表：
CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书。 BNO 书号，BNAME 书名, AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

要求1．写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。

CREATE TABLE BORROW(
    CNO int FOREIGN KEY REFERENCES CARD(CNO),
    BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
    RDATE datetime,
    PRIMARY KEY(CNO,BNO))

要求2．找出借书超过5本的读者,输出借书卡号及所借图书册数。

SELECT CNO,借图书册数=COUNT(*)
FROM BORROW
GROUP BY CNO
HAVING COUNT(*)>5

要求3．查询借阅了"水浒"一书的读者，输出姓名及班级

CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书。 BNO 书号，BNAME 书名, AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期

SELECT * FROM CARD c
WHERE EXISTS(
    SELECT * FROM BORROW a,BOOKS b 
    WHERE a.BNO=b.BNO
        AND b.BNAME=N'水浒'
        AND a.CNO=c.CNO)

要求4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

SELECT * FROM BORROW 
WHERE RDATE<GETDATE()

要求5．查询书名包括"网络"关键词的图书，输出书号、书名、作者。

SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N'%网络%'

N’string’ 表示string是个Unicode字符串

要求6．查询现有图书中价格最高的图书，输出书名及作者。

SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE=(
    SELECT MAX(PRICE) FROM BOOKS)

要求7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'
    AND NOT EXISTS(
        SELECT * FROM BORROW aa,BOOKS bb
        WHERE aa.BNO=bb.BNO
            AND bb.BNAME=N'计算方法习题集'
            AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC

要求8．将"C01"班同学所借图书的还期都延长一周。

UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
FROM CARD a,BORROW b
WHERE a.CNO=b.CNO
    AND a.CLASS=N'C01'

DATEADD(datepart,number,date)  
date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。

要求9．从BOOKS表中删除当前无人借阅的图书记录。

DELETE FROM BOOKS a
WHERE NOT EXISTS(
    SELECT * FROM BORROW
    WHERE BNO=a.BNO)

要求11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。

CREATE TRIGGER TR_SAVE ON BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO
    AND b.BNAME=N'数据库技术及应用'

要求13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。

SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
    AND b.BNAME IN(N'计算方法',N'组合数学')
GROUP BY a.CNO
HAVING COUNT(*)=2
ORDER BY a.CNO DESC

5，索引

6，关系运算

1）集合运算符

并（∪）、差（-）、交（∩）、笛卡尔积（×）

笛卡尔积（直积）：表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
例如，A={a,b}, B={0,1,2}，则
A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

2）专门的关系运算符

①选择（限制、σ）

在关系R中选择满足给定条件的诸元组。

②投影（π）

关系R上的投影是从R中选择出若干属性列组成新的关系。
这里写图片描述
投影之后可既改变行，又改变元组的数量。

③连接（θ连接、⋈）

从两个关系的笛卡尔积中选取属性间满足一定条件的元组。（连接由乘积（笛卡尔积）、选择、投影组成）
分为等值连接（=）、自然连接（要求比较的分量是相同的属性组，并在结果中把重复的属性列去掉）。
这里写图片描述

④除运算（➗）

RS÷S的意义就是：“在R和S的联系RS中，找出与S中所有的元组有关系的R元组”。

3）算术比较符

4）逻辑运算符

非与或

7，数据库完整性

1）实体完整性

主键唯一且不为空。

2）参照完整性

不允许修改外码
级连操作：当删除或修改被参照表时，同时删除或修改参照表中的不一致元祖。

3）用户定义的完整性

4）触发器（Trigger）

是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对标的增删改操作均由服务器自动激活相应触发器，在DBMS核心层进行集中的完整性控制。

8，存储过程（Stored Procedure）

1）概念

存储过程是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

2）优点

①执行效率高

存储过程因为SQL 语句已经预编译过了，因此运行的速度比较快。

②降低了客户机和服务器之间的通信

存储过程在服务器端运行，减少客户端的压力。
减少网络流量，客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL 语句相比自然数据量少了很多。

③方便实施企业规则（提高了可维护性、安全性）

可以把企业规则的运算程序写成存储过程放入数据库服务器中，由RDBMS管理，既有利于集中控制，又能够方便地进行维护。
当用户规则发生变化时，只要修改存储过程，无须修改其他应用程序。

允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用。
增强了使用的安全性，充分利用系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。程序员直接调用存储过程，根本不知道表结构是什么，有什么字段，没有直接暴露表名以及字段名给程序员。

④安全性高

可设定只有某些用户才具有对指定存储过程的使用权。

3）缺点

调试麻烦（至少没有像开发程序那样容易），可移植性不灵活（因为存储过程是依赖于具体的数据库）。

4）场景

当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。

sql尽量放在存储过程中。
面对大量数据，用orcle比sql server稳定。

5）代码

①创建

use test1
set ansi_nulls on
go
set quoted_identifier on
go
create procedure procedure_student
	-- add the parameters for the stored procedure here
	@gradeid int,
	@gradename varchar(10) --传入的参数
as
begin
	--计算内容
end
go

②执行

exec dbo.procedure_student 1,'g'

9，数据库恢复技术

1）事务

10，并发控制

为了保证事务的隔离性和一致性，DBMS需要对并发操作进行正确调度。

1）并发操作带来的数据不一致性

①更新丢失

②读“脏”数据

事务T1修改数据，T2读取数据，T1由于某种原因被撤销，则数据修改回原值，但T2读取的数据是之前修改的数据，即脏数据、不正确的数据。

③不可重复读

事务T1读数据后，T2修改了数据，T1无法再现上一次读取的结果。

④幻读

事务T1读数据后，T2新增或者删除了数据，T1无法再现上一次读取的结果。

2）并发控制技术

悲观锁：封锁
乐观锁：版本号、时间戳

3）封锁分类（悲观锁）

①共享锁（S锁、读锁）

（读取）操作创建的锁。其他用户可以并发读取数据，但任何事物都不能获取数据上的排它锁，直到已释放所有共享锁。
若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

②排它锁（X锁、写锁，eXclusive lock）

若事物T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。

③更新锁（U锁)

用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的页将要被更新时，则升级为X锁；U锁一直到事务结束时才能被释放。

4）封锁问题

①活锁

i>饥饿

考虑一台打印机分配的例子，当有多个进程需要打印文件时，系统按照短文件优先的策略排序，该策略具有平均等待时间短的优点，似乎非常合理，但当短文件打印任务源源不断时，长文件的打印任务将被无限期地推迟，导致饥饿以至饿死。

ii>活锁概念

与饥饿相关的另外一个概念称为活锁，在忙式等待条件下发生的饥饿，称为活锁。

a）忙式等待：不进入等待状态的等待。
b）阻塞式等待：进程得不到共享资源时将进入阻塞状态，让出CPU 给其他进程使用。
c）忙等待和阻塞式等待的相同之处：
在于进程都不具备继续向前推进的条件，不同之处在于处于忙等待的进程不主动放弃CPU，尽管CPU 可能被剥夺，因而是低效的；而处于阻塞状态的进程主动放弃CPU ，因而是高效的。

iii>举例

事务T1请求封锁R，T2请求封锁R，T3请求封锁R……
T1释放R之后，系统批准了T3的请求，然后是T4……请求，T2可能永远等待下去。（在整个过程中，事务T2 在不断的重复尝试获取锁R）。

iv>与死锁区别

活锁的时候，进程是不会阻塞的，这会导致耗尽CPU 资源，这是与死锁最明显的区别。
处于活锁的实体是在不断的改变状态，所谓的“活”，而处于死锁的实体表现为等待；活锁有一定几率解开，而死锁是无法解开的。

v>避免方式

采用先来先服务策略。

②死锁

i>概念

是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁。

ii>举例

T1请求封锁R1，T2请求封锁R2，然后T1又请求封锁R2，T1一直等待T2释放R2，此时，T2请求封锁R1，T2将一直等待T1释放R1。

iii>死锁原因

在数据库中，产生死锁的原因主要是：
两个或多个事务都已封锁了一些数据对象，然后又都请求其他事务已封锁的数据对象，从而出现死等待。

产生死锁的四个必要条件：
（1）互斥条件：一个资源每次只能被一个进程使用。
（2）请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3）不可剥夺条件: 进程已获得的资源，在末使用完之前，不能强行剥夺。
（4）环路等待条件: 若干进程之间形成一种头尾相接的循环等待资源关系。
只要系统发生了死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死
锁。

iv>死锁预防

预防死锁的发生只需破坏死锁产生的四个必要条件之一即可。

破坏互斥条件
如果允许系统资源都能共享使用，则系统不会进入死锁状态。但有些资源根本不能同时访问，如打印机等临界资源只能互斥使用。所以，破坏互斥条件而预防死锁的方法不太可行，而且在有的场合应该保护这种互斥性。
破坏不剥夺条件
当一个已保持了某些不可剥夺资源的进程，请求新的资源而得不到满足时，它必须释放已经保持的所有资源，待以后需要时再重新申请。这意味着，一个进程已占有的资源会被暂时释放，或者说是被剥夺了，或从而破坏了不可剥夺条件。
该策略实现起来比较复杂，释放已获得的资源可能造成前一阶段工作的失效，反复地申请和释放资源会增加系统开销，降低系统吞吐量。这种方法常用于状态易于保存和恢复的资源，如CPU 的寄存器及内存资源，一般不能用于打印机之类的资源。
破坏请求和保持条件
釆用预先静态分配方法，即进程在运行前一次申请完它所需要的全部资源，在它的资源未满足前，不把它投入运行。一旦投入运行后，这些资源就一直归它所有，也不再提出其他资源请求，这样就可以保证系统不会发生死锁。
这种方式实现简单，但缺点也显而易见，系统资源被严重浪费，其中有些资源可能仅在运行初期或运行快结束时才使用，甚至根本不使用。而且还会导致“饥饿”现象，当由于个别资源长期被其他进程占用时，将致使等待该资源的进程迟迟不能开始运行。
破坏环路等待条件
为了破坏循环等待条件，可釆用顺序资源分配法。首先给系统中的资源编号，规定每个进程，必须按编号递增的顺序请求资源，同类资源一次申请完。也就是说，只要进程提出申请分配资源Ri，则该进程在以后的资源申请中，只能申请编号大于Ri 的资源。
这种方法存在的问题是，编号必须相对稳定，这就限制了新类型设备的增加；尽管在为资源编号时已考虑到大多数作业实际使用这些资源的顺序，但也经常会发生作业使甩资源的顺序与系统规定顺序不同的情况，造成资源的浪费；此外，这种按规定次序申请资源的方法，也必然会给用户的编程带来麻烦。

都不好用，一般采用死锁的诊断和解除。

v>死锁的诊断和解除

a)超时法
如果一个事务等待时间超时，则认为发生死锁。（可能误判）
b)事务等待图法
事务等待图是一个有向图，反映了事务的等待情况。如果图中出现回路，就表示出现了死锁。

处理方案是：选择一个处理代价最小的事务，将其撤销并释放所有锁。
a) 从死锁进程处剥夺资源
b) 终止部分或全部进程

5) 两段锁协议（Two-Phase Locking――2PL）

两段锁协议规定所有的事务应遵守的规则：
① 在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁。
② 在释放一个封锁之后，事务不再申请和获得其它任何封锁。
即事务的执行分为两个阶段：
第一阶段是获得封锁的阶段，称为扩展阶段。
第二阶段是释放封锁的阶段，称为收缩阶段。

定理：若所有事务均遵守两段锁协议，则这些事务的所有交叉调度都是可串行化的。
对于遵守两段协议的事务，其交叉并发操作的执行结果一定是正确的。值得注意的是，上述定理是充分条件，不是必要条件。一个可串行化的并发调度的所有事务并不一定都符合两段锁协议，存在不全是2PL的事务的可串行化的并发调度。
同时我们必须指出，遵循两段锁协议的事务有可能发生死锁。

此时事务T1 、T2同时处于扩展阶段，两个事务都坚持请求加锁对方已经占有的数据，导致死锁。
为此，又有了一次封锁法。一次封锁法要求事务必须一次性将所有要使用的数据全部加锁，否则就不能继续执行。因此，一次封锁法遵守两段锁协议，但两段锁并不要求事务必须一次性将所有要使用的数据全部加锁，这一点与一次性封锁不同，这就是遵守两段锁协议仍可能发生死锁的原因所在。

11，常见图

DFD 数据流图（Data Flow Diagram）：
这里写图片描述
ER图实体-联系图（Entity-Relationship Diagram）

12，数据库连接：JDBC与JdbcTemplate

13，数据库安全

1）SQL注入

①概念

在SQL 语句在拼接的情况下，用户输入为一部分sql语句。

②解决方法

i> 对特殊字符进行过滤、转义或者使用预编译的sql 语句绑定变量

SQL执行时，2种方式：
①字符串处理（拼接），然后执行SQL
用户输入的时候，可以通过输入sql语句来进行SQL注入。
②传参，执行SQL -->交给SQL引擎**（推荐）**
用prepareStatement,参数用set 方法进行填装。

String sql= "insert into userlogin values(?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
for(int i=1;i<100;i++){
ps.setInt(1, i);
ps.setInt(2, 8888);
ps.executeUpdate();
ps.close();
conn.close();

ii> 当sql 语句运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息

iii>检查变量的数据类型和格式

只要是有固定格式的变量，在SQL 语句执行前，应该严格按照固定格式去检查，确保变量是我们预想的格式，这样很大程度上可以避免SQL 注入攻击。
例如：对于where id={$id}这种形式，数据库里所有的id 都是数字，那么就应该在SQL 被执行前，检查确保变量id 是int 类型。

iv>所有的SQL 语句都封装在存储过程中

所有的SQL 语句都封装在存储过程中，这样不但可以避免SQL 注入，还能提高一些性能。

14，分布式数据库

1）概念

分布式数据库是一个物理上分散的而逻辑上集中的数据集。
它有三大特点：数据分布性逻辑关联性站点自治性

2）五个基本原则

①资源的重复性
指分布式系统中硬件，软件以及数据的冗余配置。
②物理上的分布性
从硬件，软件以及数据上看都是相互独立地分布。
③高层操作系统（或者分布式操作系统）
高层操作系统负责对分布性的资源进行统一的控制，它使一个简单的硬件堆积转变为一个统一协调的工作系统。
④系统的透明性
透明性是分布式系统的灵魂，实现不同层次的透明性是分布式系统必须解决的关键问题之一。
⑤协作的自治性
每一节点都是一个完整的处理系统，同时又是合作的。简而言之：分布式系统是一个多节点的，处理或数据分布的，在统一下提高综合处理能力的协作体。

3）待解决问题

不完整系统状态信息
时间延迟
通信的代价
负载均衡

4）分类（从控制方式角度）

①紧耦合式DDBMS

全局控制信息放在一个称为中心站点的站点上。所有的全局访问都必须通过中心站点来确定远程数据片的位置。
优点：容易实现数据的一致性和完整性。
缺点：易产生访问瓶颈，系统效率不高，可靠性较差。

②联邦式DDBMS

每个站点都包含全局控制信息的一个副本，都可以接受全局访问。任何对远程数据的请求，都可以通过广播方式传播到其他节点。
优点：具有较好的可靠性和可用性，并行性好，更容易适应旧有的系统集成和异构分布式数据库系统的建立。
缺点：保持数据的一致性很困难，实现难度大。

③组合式DDBMS

是上述方案的折衷，它把站点分为两类，一类具有全局控制信息，称为主节点，可以接受全局任务，另一类没有全局信息，只能为主节点提供数据服务。
优点：灵活性较好，易于实现层次控制结构。
缺点：设计复杂。

5）分布透明性

即在分布式数据库系统中用户不必关心数据的分布情况。分为三个层次：

①分片透明性

它是分布式数据库系统的最高透明性层次，它向用户完全屏蔽了DDB的分片信息。这样的透明性保持了高水平的数据独立性。

②位置透明性

用户的应用程序不需要关心数据分片的具体存储站点，当数据库的数据片的存储站点发生改变时，只需改变对应的GRS/NRS映射就可以保持全局表示模式不发生改变

③数据模型透明性

它向用户屏蔽的只是本站点的具体数据库存储及其管理情况。在异构的情况下，这种透明性避免了用户对不同数据模型的转换的实现。
本地透明性是3种透明方式中最低的。

6）数据分割方法

①水平分割

把全局关系的元组分割成一些子集，这些子集被称为数据分片或段（Fragment）。
水平分割可以通过关系运算“选择”来定义。

水平分片是对全局关系执行“选择”操作，把具有相同性质的元组进行分组，构成若干个不相交的子集.水平分片的方法可归为初级分片和导出分片两类。

②垂直分割

把全局关系按照属性组（纵向）分隔成一些数据分片或段。
垂直分割可以通过关系运算“投影”来定义。

③混合分割

可把水平分割和垂直分割这两种方法结合起来使用，产生混合式数据分片。

④数据分片应遵循的原则

若R={R1，R2，…，Rn}满足：
1)完整性(completeness)条件：
如果分片 a∈R，则必有a∈Ri，i=l，2，…，n
2)可重构(reconstructed)条件：
R=∪ Ri，(水平分片)或R=∞Ri，(垂直分片)
3)不相交(disjoint)条件：
Ri∩ Rj=φ,i≠j,I,j：=1,2,…,，n(水平分片)
Ri∩Rj=主键属性,I,j=1，2,…,n(垂直分片)

7）分布式数据库和集中式区别

分布式（distributed）是指在多台不同的服务器中部署不同的服务模块，通过远程调用协同工作，对外提供服务。
集群（cluster）是指在多台不同的服务器中部署相同应用或服务模块，构成一个集群，通过负载均衡设备对外提供服务。

15，数据库优化

1）优化SQL 语句

①explain

通过explain(查询优化神器)用来查看SQL 语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。
通常我们可以对比较复杂的尤其是涉及到多表的SELECT 语句，把关键字EXPLAIN 加到前面，查看执行计划。例如：explain select * from news;

explain语法：

explain select … from … [where ...]

② 用具体的字段列表代替“*

任何地方都不要使用select * from t ，不要返回用不到的任何字段。

③ 不在索引列做运算或者使用函数

④ 查询尽可能使用limit 减少返回的行数，减少数据传输时间和带宽浪费。

2）优化表的数据类型

① 使用procedure analyse()函数对表进行分析

该函数可以对表中列的数据类型提出优化建议。能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu 缓存的使用。
使用方法：select * from 表名procedure analyse();

② 对表进行拆分

通过拆分表可以提高表的访问效率。有2 种拆分方法：
1.垂直拆分
把主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中。如果一个表中某些列常用，而另外一些不常用，则可以采用垂直拆分。
2.水平拆分
根据一列或者多列数据的值把数据行放到二个独立的表中。

③ 使用中间表来提高查询速度

创建中间表，表结构和源表结构完全相同，转移要统计的数据到中间表，然后在中间表上进行统计，得出想要的结果。

3）硬件优化

①CPU 的优化

选择多核和主频高的CPU。

②内存的优化

使用更大的内存。将尽量多的内存分配给MYSQL 做缓存。

③磁盘I/O 的优化

i>使用磁盘阵列

RAID 0 没有数据冗余，没有数据校验的磁盘陈列。实现RAID 0至少需要两块以上的硬盘，它将两块以上的硬盘合并成一块，数据连续地分割在每块盘上。
RAID1 是将一个两块硬盘所构成RAID 磁盘阵列，其容量仅等于一块硬盘的容量，因为另一块只是当作数据“镜像”。
使用RAID-0+1 磁盘阵列。RAID 0+1 是RAID 0 和RAID 1 的组合形式。它在提供与RAID 1 一样的数据安全保障的同时，也提供了与RAID 0 近似的存储性能。

ii>调整磁盘调度算法

选择合适的磁盘调度算法，可以减少磁盘的寻道时间。

4）MySQL 自身的优化

对MySQL 自身的优化主要是对其配置文件my.cnf 中的各项参数进行优化调整。如指定MySQL 查询缓冲区的大小，指定MySQL 允许的最大连接进程数等。

5）应用优化

①使用数据库连接池

②使用查询缓存

它的作用是存储select 查询的文本及其相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表，当表中数据更改后，查询缓存中的相关条目就会被清空。

6）大访问量的优化

①使用优化查询的方法

（见上面）

②主从复制，读写分离

i>主从复制（master，slave）:

通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves 进行操作（简单的轮循算法来决定使用哪个slave）。
利用数据库的读写分离，Web 服务器在写数据的时候，访问主数据库（Master），主数据库通过主从复制机制将数据更新同步到从数据库（Slave），这样当Web 服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的Web 应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得的方案。
这里写图片描述

③负载均衡(Load Balance，简称LB)

7）数据库分表、分区、分库

分表见上面描述。
分区就是把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O 读写性能，实现比较简单。包括水平分区和垂直分区。
分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog 等库。

17，应用

1）服务器与服务器之间传输文件夹下的文件，一个文件夹下有10 个文件，另一个文件夹下有100 个文件，两个文件夹大小相等，问，哪个传输更快？

10 个文件更快。
1）建立连接数更少，建立连接的开销比传输文件的开销大。
2）文件写入磁盘，要计算文件的起始位置，文件数目少的话，这个开销就小了

收起

数据库多表联合查询附简单例子

数据库之数据库和表的创建

数据库和表的创建

1 目的

2 要点

3 主要内容

4 详细步骤和SQL命令

4.1 使用 SQL 命令管理数据库和表

4.2 向数据表中录入测试数据

4.3 分离与附加数据库

MySQL基本操作一：数据库和表

数据库的操作

创建表

查看表

删除表

修改表

1. 修改表名

2. 增加字段

3. 删除字段

4. 修改字段

表的约束

1. 设置非空约束

2. 设置字段的默认值

3. 设置唯一约束

4. 设置主键约束

5. 设置字段值自动增加

6. 设置外键约束

全国行政区划简表数据库

SQLServer数据库基础

【数据库学习】数据库总结

1，概念

1）数据库

2）数据库数据特点

3）数据库系统的特点

①数据结构化

②数据的共享性，冗余度，易扩充

③数据独立性高

4）概念模型（E-R模型）

①概念

5）关系完整性

实体完整性

参照完整性

用户定义的完整性

6）关系数据库规范化

2，依赖和范式

1）依赖

①部分函数依赖

②完全函数依赖

③传递函数依赖

④多值依赖

2）范式

①1NF（满足最低要求的范式：字段不可再分，原子性）

②2NF（消除部分子函数依赖：一个表只能说明一个事物）

③3NF（消除传递依赖，即消除非主属性对键的传递依赖：每列都与主键有直接关系，不存在传递依赖。任何非主属性不依赖于其它非主属性。）

④BCNF(修正第三范式、扩充第三范式 消除主属性对键的传递依赖）

⑤4NF

3，数据库平台

4，数据库语句

1）基本对象

①模式（schema，逻辑模式）

A.概念

B.访问

C.操作

②外模式（子模式，用户模式）

③内模式（存储模式）

④表

临时表

A.本地临时表

B.全局临时表

临时表优点

临时表的创建

⑤视图

A.概念

B.优缺点

C.场景

D.操作

⑥实体视图

demo

作用

⑦索引

④BCNF(修正第三范式、扩充第三范式消除主属性对键的传递依赖）

②join查询（内联结，inner join，推荐）

要求1．写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。

要求2．找出借书超过5本的读者,输出借书卡号及所借图书册数。

要求3．查询借阅了"水浒"一书的读者，输出姓名及班级

要求4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

要求5．查询书名包括"网络"关键词的图书，输出书号、书名、作者。

要求6．查询现有图书中价格最高的图书，输出书名及作者。

要求8．将"C01"班同学所借图书的还期都延长一周。

要求9．从BOOKS表中删除当前无人借阅的图书记录。