转自：https://juejin.cn/post/7123032814819213348

1. 数据库概念

1.1 数据库基本概念

1. 数据库(DataBase:DB)
   数据库是是按照数据结构来组织、存储和管理数据的仓库。---->存储和管理数据的仓库
2. 数据库管理系统（Database Management System:DBMS）
   是专门用于管理数据库的计算机系统软件。数据库管理系统能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并完成对数据完整性、安全性进行控制的功能。
   注意：我们一般说的数据库，就是指的DBMS

1.2 数据库技术发展历程

1. 层次数据库和网状数据库技术阶段
   使用指针来表示数据之间的联系。
2. 关系型数据库技术阶段
   经典的里程碑阶段，代表的DBMS有：Oracle、DB2、MySQL、SQL Server、SyBase等。
3. 后关系型数据库技术阶段
   由于关系型数据库中存在数据模型、性能、拓展伸缩性差的缺点，所以出现了ORDBMS（面向对象数据库技术），NoSQL（结构化数据库技术）。

1.3 常见关系型数据库

• Oracle：运行稳定，可移植性高，功能齐全，性能超群。适用于大型企业领域。
• DB2：速度快、可靠性好，适于海量数据，恢复性极强。适用于大中型企业领域。
• SQL Server：全面，效率高，界面友好，操作容易，但是不跨平台。适用于中小型企业领域。
• MySQL：开源，体积小，速度快。适用于中小型企业领域。

1.4 常见NoSQL数据库

• 键值存储数据库：Oracle BDB、Redis、BeansDB
• 列式储数数据库：HBase、Cassandra、Riak
• 文档型数据库：MongoDB、CouchDB
• 图形数据库：Neo4J、InfoGrid、Infinite Graph

1.5 结构化查询语言(SQL)

Structured Query Language，即SQL，SQL是关系型数据库标准语言，其特点：简单，灵活，功能强大。SQL包含6个部分：

1. 数据查询语言（DQL）
   其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

2. 数据操作语言（DML）
   其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

3. 事务处理语言（TPL）
   它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

4. 数据控制语言（DCL）
   它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5. 数据定义语言（DDL）
   其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

6. 指针控制语言（CCL）
   它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

2. 数据库安装

2.1 SqlServer2019

下载路径：https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads
直接下载Developer版本，下载后：是一个镜像文件---直接解压，就可以去执行Exe文件安装了

2.2 SQL Server Management Studio

下载路径：https://docs.microsoft.com/zh-cn/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

2.3 docker安装SqlServer2019

拉取镜像:

docker pull mcr.microsoft.com/mssql/server:2019-latest

查看镜像:

docker images

启动容器:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=密码" -u 0:0 -p 1433:1433 --name mssql -v /data/mssql:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest

检查容器是否启动:

docker ps -a

检查STATUS 是不是 Up 状态，如果是 Exited 状态的话，可以尝试使用 docker logs mssql 查看日志,日志内会提供相对应的代码，以及解决链接。

服务器本地连接测试:

docker exec -it mssql /bin/bash

不报错就是连接成功

SSMS连接测试:

3. 数据库设计

3.1 设计的重要性

1. 提高开发效率
2. 节省空间，硬件资源
3. 直接关乎到数据库的性能

3.2 设计的流程

1. 确定需求
2. 创建E-R图，可视化数据展示
3. 数据库字段的设计

3.3 PowerDesigner实操

3.3.1 PowerDesigner16.5下载安装

链接：pan.baidu.com/s/1y65sOlVh…
提取码：1234

3.3.2 PD生成SQL脚本

建议先画E-R图，再生成数据库

创建项目，填写项目名称，选择保存目录

创建模型文件:

创建表，添加字段:

可以显示字段说明

生成物理数据模型：

生成SQL脚本

3.3.3 PD从数据库生成模型

创建模型，选择数据库类型

配置数据库连接导出模型

3.3.4 PD数据类型说明

3.4 三大范式

数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的。

3.4.1 第一范式（1NF）列不可再分

• 每一列属性都是不可再分的属性值，确保每一列的原子性
• 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

3.4.2 第二范式（2NF）属性完全依赖于主键

• 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
• 第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键。

3.4.3 第三范式（3NF）属性不依赖于其它非主属性，属性直接依赖于主键

• 数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
• 比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）应该拆解成两张表（学号，姓名，年龄，性别，所在院校）+（所在院校，院校地址，院校电话）

3.4.4 平衡范式与冗余

• 冗余是以存储换取性能，范式是以性能换取存储。
• 模型设计时，这两方面的具体的权衡，首先要以企业提供的计算能力和存储资源为基础。其次，建模也是以任务驱动的，因此冗余和范式的权衡需要符合任务要求。

3.5 表和表关系

3.5.1 一对一

• 单个表存储
• 两个表存储：两个表之间，数据记录是一对一的关系，通过同一个主键来约束

3.5.2 一对多

• 单个表存储
• 两个表存储：（推荐）主表的一条记录对应从表中的多条记录，通过主外键关系来关联存储

3.5.3 多对多

• 单个表存储
• 两个表存储
• 三个表存储：（推荐）两个数据主要保存数据，关系表保存关系数据

3.6 设计习惯

以公司为单位，定制的一些内部的规范，统一的习惯对这个团队来说，会有促进作用

3.6.1 命名规范

• 驼峰命名法: 指当变量名和方法名称是由二个或二个以上单词连结在一起，首个单词首字母小写，其他单词首字母大写，而构成的唯一识别字时，用以增加变量和函式的可读性。
• 帕斯卡命名法：指当变量名和方法名称是由二个或二个以上单词连结在一起，每个单词首字母大写。而构成的唯一识别字时，用以增加变量和函式的可读性。
• 坚决抵制中文，提倡使用英文单词，建议不要用汉语拼音 ，坚决抵制使用拼音首字母，建议不要太长，意思明确

3.6.2 常用公共字段

• ID：表的物理ID
• CreateBy：行记录创建人
• CreateTime：行记录创建时间
• UpdateBy：行记录更新人
• UpdateTime：行记录更新时间
• UserIP：操作人IP
• TS：时间戳，版本号，乐观锁更新用
• ISDEL：假删除标识

4. 数据库数据类型

4.1 整数类型

1. int
   存储在4个字节中，其中1个二进制位表示符号位，其它31个二进制位表示长度和大小，可以表示-2^31~231-1范围内的所有整数。

2. bigint
   存储在8个字节中，其中1个二进制位表示符号位，其它63个二进制位表示长度和大小，可以表示-2^63~263-1范围内的所有整数。

3. smallint
   存储在2个字节中，其中1个二进制位表示符号位，其它15个二进制位表示长度和大小，可以表示-2^15~215-1范围内的所有整数。

4. tinyint
   存储在1个字节中，可以表示0~255范围内的所有整数。

4.2 浮点类型

浮点数据类型存储十进制小数，浮点数据为近似值，Sql Server中采用了只入不舍的方式进行存储，即当要舍入的数是一个非零数时，就进1。

1. real
   存储在4个字节中，可以存储正的或者负的十进制数值，它的存储范围从-3.40E+38~-1.18E-38、0以及 1.18E-38~3.40E+38。

2. float

   • float数据类型可以写成float(n)的形式，n为指定float数据的精度，n为1-53之间的整数值。n的默认值为53，占用8个字节的存储空间，其范围从-1.79E+308-2.23E-308、0以及2.23E+308~1.79E-308。
   • 当n取1-24时，实际上定义了一个real类型的数据，系统用4个字节存储它。
   • 当n取25-53时，系统认为其是float类型，用8个字节存储它。

3. decimal[(p[,s])] 和 numeric[(p[,s])

   • 带固定精度和小数位数的数值数据类型。使用最大精度时，有效值从-10³⁸⁺¹--10^38-1。numeric在功能上等价于decimal。
   • p(精度)：指定了数值的总位数，包括小数点左边和右边的位数，该精度必须是从1~38之间的值，默认精度为18。
   • s(小数位数)：指定小数点右边数值的最大位数，小数位数必须是从0到p之间的值，仅在指定精度后才可以指定小数的位数，默认小数位数是0；

4.3 字符串类型

1. 前缀说明

   • var：表示是实际存储空间是变长的，不带var，存储长度不足时，空格补足。带var节省空间，但效率低，不带var，浪费空间，但效率高。
   • n：表示为Unicode字符，字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。

2. char(n)

   • 固定长度，存储ANSI字符，不足的补英文半角空格。若插入字段的数据过长，则会截掉其超出部分，数据库不会异常。
   • n的取值为1~8000。最多8000个英文，4000个汉字，如不指定n的值，系统默认n的值为1。

3. varchar(n)

   • 可变长度，存储ANSI字符，根据数据长度自动变化。
   • n的取值为1~8000。最多8000个英文，4000个汉字，如不指定n的值，系统默认n的值为1，但可根据实际存储的字符数改变存储空间。存储大小是输入数据的实际长度加2个字节。加的两个字节是用来存储字段长度的。

4. nchar(n)

   • 固定长度，存储Unicode字符，不足的补英文半角空格。
   • n值必须在1~4000。最多4000个英文或者汉字，如不指定n的值，系统默认n的值为1。

5. nvarchar(n)

   • 可变长度，存储Unicode字符，根据数据长度自动变化。
   • n值必须在1~4000。最多4000个英文或者汉字，如不指定n的值，系统默认n的值为1。但可根据实际存储的字符数改变存储空间。存储大小是输入数据的实际长度加2个字节。加的两个字节是用来存储字段长度的。

4.4 日期和时间类型

1. date
   存储用字符串表示的日期数据，可以表示0001-01-01~9999-12-31(公元元年1月1日到公元9999年12月31日)间的任意日期值。数据格式为“YYYY-MM-DD”，该数据类型占用3个字节的空间。
   YYYY：表示年份的四位数字，范围为0001~9999。
   MM：表示指定年份中月份的两位数字，范围为01~12。
   DD：表示指定月份中某一天的两位数字，范围为01~31（最高值取决于具体月份）。

2. time
   以字符串形式记录一天的某个时间，取值范围为00:00:00.0000000~23:59:59.9999999，数据格式为“hh:mm:ss[.nnnnnnn]”，存储时占用5个字节的空间。
   hh：表示小时的两位数字，范围为0~23。
   mm：表示分钟的两位数字，范围为0~59。
   ss：表示秒的两位数字，范围为0~59。
   n：是07位数字，范围为09999999，它表示秒的小部分。

3. datetime
   用于存储时间和日期数据，从1753年1月1日到9999年12月31日，默认值为 1900-01-01 00:00:00，当插入数据或在其它地方使用时，需用单引号或双引号括起来。可以使用“/”、“-”和“.”作为分隔符。该类型数据占用8个字节的空间。

4. datetime2
   datetime的扩展类型，其数据范围更大，默认的最小精度最高，并具有可选的用户定义的精度。默认格式为：YYYY-MM-DD hh:mm:ss[.fractional seconds]，日期的存取范围是0001-01-01~9999-12-31(公元元年1月1日到公元9999年12月31日)。

5. smalldatetime
   smalldatetime类型与datetime类型相似，只是其存储范围是从1900年1月1日到2079年6月6日，当日期时间精度较小时，可以使用smalldatetime，该类型数据占用4个字节的存储空间。

6. datetimeoffset
   用于定义一个采用24小时制与日期相组合并可识别时区的时间。默认格式是：“YYYY-MM-DD hh:mm:ss[.nnnnnnn][{+|-}hh:mm]”。
   hh:两位数，范围是-14~14。
   mm:两位数，范围为00~59。
   这里hh是时区偏移量，该类型数据中保存的是世界标准时间（UTC）值，eg：要存储北京时间2011年11月11日12点整，存储时该值将是2011-11-11 12:00:00+08:00,因为北京处于东八区，比UTC早8个小时。存储该数据类型数据时默认占用10个字节大小的固定存储空间。

4.5 文本和图像类型

1. text
   用于存储文本数据，服务器代码页中长度可变的非Unicode数据，最大长度为2的31次方-1（2147 483 647）个字符。当服务器代码页使用双字节字符时，存储仍是2147 483 647字节。

2. ntext
   与text类型作用相同，为长度可变的非Unicode数据，最大长度为 2^30-1 (1073 741 283)个字符。存储大小是所输入字符个数的两倍。

3. image
   长度可变的二进制数据，范围为 :0---2^31-1个字节。用于存储照片、目录图片或者图画，容量也是2147 483 647个字节，由系统根据数据的长度自动分配空间，存储该字段的数据一般不能使用insert语句直接输入。

4.6 货币类型

1. money
   用于存储货币值，取值范围为正负922 337 213 685 477.580 8之间。money数据类型中整数部分包含19个数字，小数部分包含4个数字，因此money数据类型的精度是19，存储时占用8个字节的存储空间。

2. smallmoney
   与money类型相似，取值范围为正负214 748.346 8之间，smallmoney存储时占用4个字节存储空间。

4.7 位数据类型

bit 称为位数据类型，只取0或1为值，长度1字节。bit值经常当作逻辑值用于判断true(1)或false(0),输入非0值时系统将其替换为1。

4.8 二进制类型

1. binary(n)
   长度为n个字节的固定长度二进制数据，其中n是从1~8000的值。存储大小为n个字节。在输入binary值时，必须在前面带0x,可以使用0xAA5代表AA5，如果输入数据长度大于定于的长度，超出的部分会被截断。

2. varbinary(n)
   可变长度二进制数据。其中n是从1~8000的值，存储大小为所输入数据的实际长度+2个字节。

4.9 其它数据类型

1. rowversion
   每个数据都有一个计数器，当对数据库中包含rowversion列的表执行插入或者更新操作时，该计数器数值就会增加。此计数器是数据库行版本。一个表只能有一个rowversion列。每次修改或者插入包含rowversion列的行时，就会在rowversion列中插入经过增量的数据库行版本值。
   公开数据库中自动生成的唯一二进制数字的数据类型。rowversion通常用作给表行加版本戳的机制。存储大小为8个字节。rowversion数据类型只是递增的数字，不保留日期或时间。

2. timestamp
   时间戳数据类型，timestamp的数据类型为rowversion数据类型的同义词，提供数据库范围内的唯一值，反映数据修改的唯一顺序，是一个单调上升的计数器，此列的值被自动更新。在create table或alter table语句中不必为timestamp数据类型指定列名。

3. uniqueidentifier
   16字节的GUID(Globally Unique Identifier,全球唯一标识符)，是Sql Server根据网络适配器地址和主机CPU时钟产生的唯一号码，其中，每个为都是09或af范围内的十六进制数字。例如：6F9619FF-8B86-D011-B42D-00C04FC964FF，此号码可以通过newid()函数获得，在全世界各地的计算机由此函数产生的数字不会相同。

4. cursor
   游标数据类型，该类型类似与数据表，其保存的数据中的包含行和列值，但是没有索引，游标用来建立一个数据的数据集，每次处理一行数据。

5. sql_variant
   用于存储除文本，图形数据和timestamp数据外的其它任何合法的Sql Server数据，可以方便Sql Server的开发工作。

6. table
   用于存储对表或视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

7. xml
   存储xml数据的数据类型。可以在列中或者xml类型的变量中存储xml实例。存储的xml数据类型表示实例大小不能超过2GB。

5. 数据库和表操作

5.1 数据库相关

5.1.1 创建数据库

 create database database_name
  [ on
    [primary]  [<filespec> [,...n] ]
  ]
  [ log on
  [<filespec>[,...n]]
  ];

  <filespec>::=
   (
     name=logical_file_name
     [  ,  newname = new_login_name ]
     [  ,  fileName = {'os_file_name' | 'fileStream_path'} ]
     [  ,  size = size[ KB | MB | GB | TB]  ]
     [  ,  MaxSize  = {max_size [ KB | MB |GB |TB] | UNLIMITED}  ]
     [  ,  filegrowth  = growth_increment [ KB  | MB  |GB  | TB  | %]    ]
);

• database_name:数据库名称，不能与SQL SERVER中现有的数据库实例名称相冲突,最多可包含128个字符;
• ON:指定显示定义用来存储数据库中的数据的磁盘文件。
• PRIMARY：指定关联的列表定义的主文件，在主文件组项中指定第一个文件将生成主文件，一个数据库只能有一个主文件。如果没有指定primary，那么create datebase 语句中列出的第一个文件将成为主文件。
• LOG ON:指定用来存储数据库日志的日志文件。LOG ON后跟以逗号分隔的用以定义日志文件的列表。如果没有指定log on，将自动创建一个日志文件，其大小为该数据库的所有文件大小总和的25%或521KB，取两者之中最大者。
• name:指定文件的逻辑名称。指定filename时，需要使用name，除非指定 FOR ATTCH 子句之一。无法将filename文件组命名为primary。
• filename:指定创建文件时又操作系统使用的路径和文件名。执行create datebase 语句前，指定路径必须存在.
• size:指定数据库文件的初始大小，如果没有为主文件提供size，数据库引擎使用model数据库中主文件的大小。
• max_size:指定文件可增大的最大大小。可使用KB、MB、GB和TB做后缀，默认值为MB。max_size是整数值.如果不指定max_size,则文件将不断增长直至磁盘被占满。UNLIMITED表示文件一直增长到磁盘装满。
• filegrowth:指定文件的自动增量。文件的filegrowth设置不能超过MAXSIZE设置。该值可以 MB、KB、GB、TB或百分比（%）为单位指定，默认值为MB，如果指定%，则增量大小为发生增长时文件大小的的指定百分比。值为0表明自动增长被设为关闭，不允许增加空间。

创建一个数据库sample_db,该数据库的主数据文件逻辑名为sample_db，物理文件名称为sample_db.mdf,初始大小为5MB，最大尺寸为30MB，增长速度为5%；数据库日志文件的逻辑名称为sample_log,保存日志文件的物理名称为sample_log.ldf，初始大小为1MB，最大尺寸为8MB，增长速度为10%

create database[sample_db] on primary
(
    name='sample_db',
    filename='C:\SQL_SERVER_temp\sampl_db.mdf',
    size=5120KB,
    maxsize=30MB,
    filegrowth=5%
)
log on
(
    name='sample_log',
    filename='C:\SQL_SERVER_temp\sample_log.ldf',
    size=1024KB,
    maxsize=8192KB,
    filegrowth=10%
)

5.1.2 修改数据库

增加或删除数据文件、改变数据文件或日志文件的大小和增长方式，增加或者删除日志文件和文件组。

alter database database_name
{
   modify name=new_database_name
   | Add file<filespec> [ ,...n ] [ TO filegroup {  filegroup_name } ]
   | Add log file <filespec> [ ,...n ]
   | remove file logical_file_name
   |modify file <filespec>
}
  <filespec>::=
   (
     name=logical_file_name
     [  ,  newname = new_login_name ]
     [  ,  fileName = {'os_file_name' | 'filestream_path'} ]
     [  ,  size = size[ KB | MB | GB | TB]  ]
     [  ,  MaxSize  = {max_size [ KB | MB |GB |TB] | UNLIMITED}  ]
     [  ,  FILEGROWTH  = growth_increment [ KB  | MB  |GB  | TB  | %]    ]
     [    ,     offline ]
);

• database_name:要修改的数据库的名称；
• modify name:指定新的数据库名称；
• Add file:向数据库中添加文件。
• to filegroup{filegroup_name}:将指定文件添加到文件组。filegroup_name为文件组名称.
• Add log file:将要添加的日志文件添加到指定的数据库
• remove file logical_file_name:从SQL Server的实例中删除逻辑文件并删除物理文件。除非文件为空，否则无法删除文件。logical_file_name是在Sql Server 中引用文件时所用的逻辑名称。
• modify file:指定应修改的文件，一次只能更改一个属性。必须在中指定name,以标识要修改的文件。如果指定了size，那么新大小必须比文件当前大小要大。

将sample_db数据库中的主数据文件的初始大小修改为15MB

alter database sample_db
  modify file
   (
    name='sample_db',
    size=15MB
  );

5.1.3 删除数据库

drop database XXX

5.1.4 备份数据库

• 右键数据库→任务→生成脚本，然后根据自己的需要，选择导出对应的结构、数据、版本等
• 右键数据库→任务→备份→选择备份的数据库→备份类型选择"完整"→选择备份路径 →输入备份文件的名称→最后点击确定

5.1.5 还原数据库

• 右键→选择还原数据→选择【设备】选项→添加还原文件→编写备份完数据库的名称→修改还原后的数据库的位置→最后点击确定

5.2 表的相关操作

5.2.1 创建表

CREATE TABLE dbo.Products
   (ProductID int PRIMARY KEY NOT NULL,
   ProductName varchar(25) NOT NULL,
   Price money NULL,
   ProductDescription varchar(max) NULL)
GO

5.2.2 删除表

drop table 表名

5.2.3 修改字段名

alter table 表名 rename column A to B

5.2.4 修改字段类型

alter table 表名 alter column 字段名 type not null

5.2.5 修改字段默认值

--如果字段有默认值，则需要先删除字段的约束，在添加新的默认值
alter table 表名 add default (0) for 字段名 with values
--根据约束名称删除约束
alter table 表名 drop constraint 约束名
--根据表名向字段中增加新的默认值
alter table 表名 add default (0) for 字段名 with values

5.2.6 增加字段

alter table 表名 add 字段名 type not null default 0

5.2.7 删除字段

alter table 表名 drop column 字段名

5.2.8 新建约束

--修改字段为必填，此字段才能设置为主键
ALTER TABLE StudentTB ALTER COLUMN Code VARCHAR(12) NOT NULL 
--主键约束
ALTER TABLE StudentTB ADD CONSTRAINT main_key PRIMARY KEY(Code)
--唯一性约束
ALTER TABLE StudentTB ADD CONSTRAINT unique_Name UNIQUE(Name)
--添加默认约束
ALTER TABLE StudentTB ADD CONSTRAINT default_Name DEFAULT('HAHAHA') FOR Name
--添加检查约束
ALTER TABLE StudentTB ADD CONSTRAINT more_than_12 CHECK(Age < 12);

5.2.9 主键、外键

主键

• 标识了数据库中某一表中某一条数据的唯一性。
• 数字主键：自增主键（标识列），新增数据，数据库自动完成主键的递增；优势：默认会有聚集索引，按照区间查询性能高； 根据主键查询数性能高；劣势：就是害怕数据迁移。
• 联合主键：表中的多个字段联合起来，确定当前这条数据的唯一性，不推荐
• Guid主键：全球唯一的一个字符串；优势：方便数据迁移；劣势：性能会稍微差一些，无法作为聚集索引；

外键

• 关系的描述：一个表中的字段对应着另外一个表中的主键，就是外键
• 物理约束：如果外键的主表中不存在这条数据，外键所在表中的数据是无法插入的
• 级联删除：需要设置，可以做到删除外键的主表数据，可以把从表中的对应数据自动全部删掉
• 数据保护：需要设置，可以做到数据校验，如果要删除从表数据，从表中的外键对应的主表中的数据要先删除，然后才能删除从表

6. 数据库表增删改查

6.1 单表查询

6.1.1 简单查询

--查询全部字段
select *  from 表名
--查询部分字段
select 字段1，字段2 from 表名
--查询去重字段
select distinct 字段1 from 表名
--字段别名
select 字段1 别名 from 表名
select 字段1 as 别名 from 表名
--字段计算
select 字段1*字段2 as 别名 from 表名

6.1.2 过滤查询

--简单条件
select *  from 表名 where 字段=值
--in、not in
select *  from 表名 where 字段 in()
select *  from 表名 where 字段 not in()
--between and 
select *  from 表名 where 字段 between  A and B
--and  并且
select *  from 表名 where 条件1 and 条件2
--or 或者
select *  from 表名 where 条件1 or 条件2
--not
select *  from 表名 where 字段!=值
select *  from 表名 where 字段<>值
--空值
select *  from 表名 where 字段 is NULL
select *  from 表名 where 字段 is not NULL
--模糊查询  %: 表示零或多个字符  _ : 表示一个字符
select *  from 表名 where 字段 like '%值%'
select *  from 表名 where 字段 not like '%值%'
select *  from 表名 where 字段 like '_值%'

6.1.3 排序查询

--ASC： 升序 (默认，可省)，DESC：降序
--字段1升序基础上相同的，字段2降序
select *  from 表名 order by 字段1 asc,字段2 desc

6.1.4 分页查询

--ROW_NUMBER()  over 按照over的排序进行排序的结果集编号newRow，然后取值
select * from 
(select *, ROW_NUMBER() over(order by 字段 desc) as newRow from 表名) as t
where t.newRow between 1 and 2
--offset 跨过3行取剩下2行
select * from 表名 order by 字段 desc
offset 3 rows fetch next 2 rows only

6.1.5 分组聚集函数

--COUNT 统计结果的记录数
select count(*) from 表名
select count(字段1) from 表名
--MAX 统计计算最大值   
select max(字段1) from 表名
--MIN 统计最小值
select min(字段1) from 表名
--SUM 统计计算求和
select sum(字段1) from 表名
--AVG 统计计算平均值
select avg(字段1) from 表名
--分组查询+聚集函数+过滤条件
select 字段1，字段2，count(*) as 别名 from 表名 where 条件 group by 字段1，字段2 having count(*)>1

6.2 多表查询

6.2.1 笛卡尔积

多表查询会产生笛卡尔积。 假设集合A={a,b}，集合B={0,1}，则两个集合的笛卡尔积为{(a,0),(a,1),(b,0),(b,1)}，尽量避免，可以加链接条件去掉不需要的数据结果集，否则数据量是乘积行数，会非常大。

6.2.2 内连接

链接的表符合等式条件的数据才展示

--隐世内连接
select *  from A，B where A.列=B.列
--显示内连接 inner 可省略
select *  from A [inner] join B on A.列=B.列

6.2.3 外连接

• 左外连接：查询出JOIN左边表的全部数据，JOIN右边的表不匹配的数据用NULL来填充，关键字：left join
• 右外连接：查询出JOIN右边表的全部数据，JOIN左边的表不匹配的数据用NULL来填充，关键字：right join
• 全连接： (左连接 - 内连接) +内连接 + (右连接 - 内连接) = 左连接+右连接-内连接 关键字：full join

--左外连接
select *  from A left join B on A.列=B.列
--右外连接
select *  from A right join B on A.列=B.列
--全连接
select *  from A full join B on A.列=B.列

6.2.4 自连接

把一张表看成两张表来做查询

6.3 插入数据

--完整表达式
insert into tableName (c1,c2,c3 ...) values (x1,x2,x3,...)
--全字段插入可以省略字段名
insert into tableName values (x1,x2,x3,...)
--插入多条
insert into tableName values(x11,x21,x31,...),
(x12,x22,x32,...),
(x13,x23,x33,...);
--插入查询结果
insert into table1 (x1,x2)
select c1,c2 from table2 where condition

6.4 删除数据

--delete删除
delete from tableName where condition
--truncate是删除全表
--和delete删除全表而言，如果是自增主键，truncate 后从1开始，delete后还要接着原先的自增
--truncate无法回滚，delete可以回滚
truncate tableName

6.5 更新数据

--单表更新
update tableName set x1=xx [, x2=xx] where condition
--关联表更新
update table1
set table1.x1='abc'
from table1
join table2 on table1.id=table2.uId  where condition
--关联更新多张表
update table1,table2
set table1.字段1 ='XXX',table2.字段2='YYY'
where table1.字段3=table2.字段3

7. 数据库常用函数

7.1 字符串函数

--返回字符串中最左侧的第一个值的ASCII代码值
select ASCII('TEST'),ASCII('TS'),ASCII('123')
--将整数类型的ASCII值转换成对应的字符
select CHAR(123),CHAR(234)
--从左侧或者从右侧获取指定个数的元素
select LEFT('testtest',6) as p1
select RIGHT('testtest',5) as p2
--从左侧去空格或者从右侧去空格
select LTRIM('   test   ')
select RTRIM('   test   ')
--逆序字符串  tset
select REVERSE('test')
--返回字符串的长度  4 2
select LEN('test'),LEN('测试')
--查找字符串的开始位置
--CHARINDEX(str1,str,[start])函数返回子字符串str1在字符串str中的开始位置，start为搜索的开始位置，如果指定start参数，则从指定位置开始搜索；如果不指定start参数或者指定为0或者负值，则从字符串开始位置搜索
select CHARINDEX('a','banana'),CHARINDEX('a','banana',4), CHARINDEX('na','banana', 4)
--截取字符串的指定位置
--下面1代表从第一个位置开始截取，5代表截取字符的长度为5,testt
select SUBSTRING('testtest',1,5)
--大小写转换 TEST test
select UPPER('Test'),LOWER('Test')
--替换函数 xxx.baidu.com
select REPLACE('www.baidu.com','w','x')
--将数值类型转换成字符数据
--第一个参数是要转换的数值,第二个参数是转换後的总长度（含小数点，正负号）,第三个参数为小数位,这里长度优先于小数位
select STR(3141.59,6,1),STR(123.45,5,2)

7.2 数学函数

--绝对值
select ABS(-2.5),ABS(4.5)
--圆周率
select PI()
--平方根
select SQRT(4),SQRT(9)
--随机数
--RAND(x)返回一个随机浮点值v,范围在0~1之间(即0<=v<=1.0)。若指定一个整数参数x，则它被用作种子值，使用相同的种子数将产生重复序列。如果同一种子值多次调用RAND函数，它将返回同一生成值。
select RAND(),RAND(),RAND()，RAND(5),RAND(5),RAND(5)
--四舍五入
--ROUND(x,y)返回接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位
--1.50 1.60 10.00
select ROUND(1.54,1),ROUND(1.56,1),ROUND(12.54,-1)
--判断正、负、零  1 -1 0
select SIGN(10),SIGN(-10),SIGN(0)
--CEILING(x)返回不小于x的最小整数值  -3 4
select CEILING(-3.35), CEILING(3.35)
--FLOOR(x)返回不大于x的最大整数值  -4
select FLOOR(-3.35)
--POWER(x,y) 求x的y次方  8
select POWER(2,3) 
--SQUARE(x) 求x的平方  9 4 0
select SQUARE(3),SQUARE(-2),SQUARE(0)

7.3 类型转换函数

select CAST('121231' AS DATE), CAST(100 AS CHAR(3)),CAST('2012-05-01 12:11:10' AS CHAR(3))
select  CONVERT(DATE,'2012-05-01 12:11:10'),CONVERT(CHAR(3),100 ),CONVERT(DATE,'2012-05-01 12:11:10')

7.4 日期函数

--获取系统当前日期的函数(普通时间和UTC时间)
select GETDATE() as CurrentTime,GETUTCDATE() as UTCTIme
--返回指定日期的d是一个月中的第几天、月份、年数
select DAY('2020-08-05 12:11:08')
select MONTH('2020-08-05 12:11:08')
select YEAR('2020-08-05 12:11:08')
--返回指定日期的 年、月、第n天、天、第n周、星期几、小时、分钟、秒
SELECT DATENAME(year,'2020-04-03 08:12:36') AS yearValue,
    DATENAME(month,'2020-04-03 08:12:36') AS monthValue,
    DATENAME(dayofyear,'2020-04-03 08:12:36') AS dayofyearValue,
    DATENAME(day,'2020-04-03 08:12:36') AS dayValue,
    DATENAME(week,'2020-04-03 08:12:36') AS weekValue,
    DATENAME(weekday,'2020-04-03 08:12:36') AS weekdayValue,
    DATENAME(hour,'2020-04-03 08:12:36') AS hourValue,
    DATENAME(minute,'2020-04-03 08:12:36') AS minuteValue,
    DATENAME(second,'2020-04-03 08:12:36') AS secondValue
--获取日期中指定部分的整数值的函数
SELECT DATEPART(year,'2020-04-03 08:12:36') AS yearValue,
　　　　DATEPART(month,'2020-04-03 08:12:36') AS monthValue,
　　　　DATEPART(dayofyear,'2020-04-03 08:12:36') AS dayofyearValue
--日期的加运算
SELECT DATEADD(year,1,'2020-04-03 08:12:36') AS yearAdd,
    　　 DATEADD(month ,2, '2020-04-03 08:12:36') AS weekdayAdd,
 　　　　DATEADD(hour,3,'2020-04-03 08:12:36') AS hourAdd

8. 数据库事物、锁

8.1 数据库事务

8.1.1 事务是什么

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

8.1.2 事物的特征ACID

• Atomicity(原子性)：要么都成功，要么都失败。
• Consistency(一致性)：一个事务在执行之前和执行之后，数据库都必须处以一致性状态。
• Isolation(隔离性)：在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。
• Durability (持久性)：事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。

8.1.3 事务写法

begin transaction
begin try
  do somthing ...
  commit transaction
end try
begin catch
    rollback transaction
end catch

8.2 数据库锁

8.2.1 共享锁：(holdlock)

• select的时候会自动加上共享锁，该条语句执行完，共享锁立即释放，与事务是否提交没有关系。
• 显式通过添加(holdlock)来显式添加共享锁（比如给select语句显式添加共享锁），当在事务里的时候，需要事务结束，该共享锁才能释放。
• 同一资源，共享锁和排它锁不能共存，意味着update之前必须等资源上的共享锁释放后才能进行。
• 共享锁和共享锁可以共存在一个资源上，意味着同一个资源允许多个线程同时进行select。

8.2.2 排它锁：(xlock)

• update(或 insert 或 delete)的时候加自动加上排它锁，该条语句执行完，排它锁立即释放，如果有事务的话，需要事务提交，该排它锁才能释放。
• 显式的通过添加(xlock)来显式的添加排它锁（比如给select语句显式添加排它锁），如果有事务的话，需要事务提交，该排它锁才能释放。
• 同一资源，共享锁和排它锁不能共存，意味着update之前必须等资源上的共享锁释放后才能进行。

8.2.3 更新锁：(updlock)

• 更新锁只能显式的通过(updlock)来添加，当在事务里的时候，需要事务结束，该更新锁才能释放。
• 共享锁和更新锁可以同时在同一个资源上，即加了更新锁，其他线程仍然可以进行select。
• 更新锁和更新锁不能共存(同一时间同一资源上不能存在两个更新锁)。
• 更新锁和排它锁不兼容。
• 利用更新锁来解决死锁问题，要比xlock性能高一些，因为加了updlock后，其他线程是可以进行select的。

8.2.4 意向锁

• 意向锁分为三种：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。 意向锁可以提高性能，因为数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。　

8.2.5 计划锁(Schema Locks)

• 执行一条新的sql语句，数据库要先对之进行编译，在编译期间，也会加锁，称之为：计划锁。
• 编译这条语句过程中，其它线程可以对表做任何操作(update、delete、加排他锁等等），但不能做DDL(比如alter table)操作。

8.2.6 锁的颗粒：行锁、页锁、表锁

• rowlock：行锁，对每一行加锁，然后释放。(对某行加共享锁)
• paglock：页锁，执行时，会先对第一页加锁，读完第一页后，释放锁，再对第二页加锁，依此类推。(对某页加共享锁)　
• tablock：表锁，对整个表加锁，然后释放。 (对整张表加共享锁）

8.3 事务隔离级别

8.3.1 事务隔离级别

• read uncommitted：这个隔离级别最低啦，可以读取到一个事务正在处理的数据，但事务还未提交，这种级别的读取叫做脏读。
• read committed：这个级别是默认选项，不能脏读，不能读取事务正在处理没有提交的数据，但能修改。
• repeatable read：不能读取事务正在处理的数据，也不能修改事务处理数据前的数据。
• snapshot：指定事务在开始的时候，就获得了已经提交数据的快照，因此当前事务只能看到事务开始之前对数据所做的修改。
• serializable：最高事务隔离级别，只能看到事务处理之前的数据。

8.3.2 四种错误

• 脏读：第一个事务读取第二个事务正在更新的数据，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。
• 幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集进行“增删”操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。
• 更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
• 不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。

8.3.3 死锁

• 两个操作，相互等待，且一直等待，就会形成死锁。

8.3.4 如何避免死锁

• 数据库并不会出现无限等待的情况，是因为数据库搜索引擎会定期检测这种状况，一旦发现有情况，立马【随机】选择一个事务作为牺牲品。牺牲的事务，将会回滚数据。
• 死锁不可能完全避免，更多的是降低死锁的概率
• 不用锁就不会死锁，建议大家使用乐观锁
• 统一操作表顺序，先A后B再C，在系统中所有的操作都需要统一顺序
• 最小单元锁，锁里面操作尽量减少操作
• 避免事务中等待用户输入，避免在事务中等待时间过久
• 减少数据库并发，微服务
• 设置死锁时间 set lock_timeout（锁超时时间）

8.3.5 系统查询

• 查看锁活动情况

  select * from sys.dm_tran_locks
  

• 查看事务活动情况

  dbcc opentran
  

• 设置锁的超时时间

  set lock_timeout 4000
  

8.3.6 乐观锁，不是锁

• 可能有并发，但是并发不高，没有强制的锁机制，通过程序设计来完成的；
• 定义一条数据的中有一个版本字段或者是一个时间戳字段；
• 在操作的时候，就通版本字段、时间戳字段来判断；每操作一次就版本或者是时间戳+1；

update 表 set 字段=XXX where 主键=YYY and 版本=SSS