数据库知识

---

title: 数据库知识
created: 2022-12-20T12:12:00Z
updated: 2022-12-27T21:42:23Z

数据库知识

基础知识

数据库系统的3级结构

3个抽象级

用户级数据库

用户级对应于对外模式，是最接近用户的一级，是用户看到和使用的数据库，又称为用户视图，

用户级数据库主要由外部记录组成，不同的用户视图可以相互重叠，用户的所有操作都是针对用户视图进行的

概念及数据库

对应于概念模式，介于用户与物理级之间，是数据库管理员看到和使用的数据库，又称DBA视图。概念级模式把用户视图有机地结合成一个整体，综合平衡考虑所有用户要求，实现数据的一致性，最大限度地降低数据冗余，准确地反映了数据之间地联系

物理机数据库

对应于内模式，是数据库地底层表示，它描述数据的实际储存组织，是最接近物理储存的级，又称为内部视图，物理级数据库由内部记录组成，物理级数据库并不是真正的物理储存，而是最接近物理储存的级。

3级模式

外模式

又称为子模式或用户模式，是数据库用户能够看到的和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，与某一应用有关的数据的逻辑表示，一个数据库可以有多个外模式。

外模式主要描述用户视图的各个记录的组成、相互关系、数据项的特征、数据的安全性和完整性约束条件

概念模式

又称为模式或逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，所有用户的公共数据视图，一个数据库只能由1个概念模式

内模式

又称为存储模式或物理模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式，一个数库只能有1个内模式

二级映射与数据独立性

二级映射

是外模式/模式映射和模式/内模式映射

3个抽象级之间通过二级映射进行相互转换，使得数据库抽象的3级模式形成一个整体

数据独立性

是指用户的应用程序和存储在磁盘上的数据库中的数据是独立的，物理独立性是通过模式/内模式映射实现的

逻辑独立性

是指用户的应用程序与逻辑结构是相互独立的，通过外模式/模式映射实现的

应用系统的开发架构

C/S模式

C/S模式是一种流行的解决分布式问题的架构模式，通过将网络环境划分为前台和后台部分，前台由客户机担任，负责客户接口相关的任务，后台为服务器，负责数据库的管理

两层C/S模式：

客户端(Client)：表示逻辑、业务逻辑

服务端(Server)：数据服务

客户端任务多，被称为胖客户机瘦服务机器的C/S模式

三层C/S模式

客户机(Client)：表示逻辑

应用服务器端(Application)：业务逻辑

DBMS服务器端(Server)：数据服务

实际实现时，Application 和 Server 可用于同一台计算机担任，被成为瘦客户机胖服务器的C/S模式

优点

C/S结构的优点是能充分发挥客户端PC的处理能力，很多工作可以在客户端处理后再提交给服务器。对应的优点就是客户端响应速度快。具体表现在以下两点：

（1）应用服务器运行数据负荷较轻。最简单的C/S体系结构的数据库应用由两部分组成，即客户应用程序和数据库服务器程序。二者可分别称为前台程序与后台程序。运行数据库服务器程序的机器，也称为应用服务器。一旦服务器程序被启动，就随时等待响应客户程序发来的请求；客户应用程序运行在用户自己的电脑上，对应于数据库服务器，可称为客户电脑，当需要对数据库中的数据进行任何操作时，客户程序就自动地寻找服务器程序，并向其发出请求，服务器程序根据预定的规则作出应答，送回结果，应用服务器运行数据负荷较轻。

（2）数据的储存管理功能较为透明。在数据库应用中，数据的储存管理功能，是由服务器程序和客户应用程序分别独立进行的，并且通常把那些不同的（不管是已知还是未知的）前台应用所不能违反的规则，在服务器程序中集中实现，例如访问者的权限，编号可以重复、必须有客户才能建立定单这样的规则。所有这些，对于工作在前台程序上的最终用户，是“透明”的，他们无须过问（通常也无法干涉）背后的过程，就可以完成自己的一切工作。在客户服务器架构的应用中，前台程序不是非常“瘦小”，麻烦的事情都交给了服务器和网络。在C/S体系下，数据库不能真正成为公共、专业化的仓库，它受到独立的专门管理。

劣势

随着互联网的飞速发展，移动办公和分布式办公越来越普及，这需要我们的系统具有扩展性。这种方式远程访问需要专门的技术，同时要对系统进行专门的设计来处理分布式的数据。

客户端需要安装专用的客户端软件。首先涉及到安装的工作量，其次任何一台电脑出问题，如病毒、硬件损坏，都需要进行安装或维护。特别是有很多分部或专卖店的情况，不是工作量的问题，而是路程的问题。还有，系统软件升级时，每一台客户机需要重新安装，其维护和升级成本非常高。

对客户端的操作系统一般也会有限制。可能适应于Win98, 但不能用于win2000或Windows XP。或者不适用于微软新的操作系统等等，更不用说Linux、Unix等。

C/S架构的劣势还有高昂的维护成本且投资大。首先，采用C/S架构，要选择适当的数据库平台来实现数据库数据的真正“统一”，使分布于两地的数据同步完全交由数据库系统去管理，但逻辑上两地的操作者要直接访问同一个数据库才能有效实现，有这样一些问题，如果需要建立“实时”的数据同步，就必须在两地间建立实时的通讯连接，保持两地的数据库服务器在线运行，网络管理工作人员既要对服务器维护管理，又要对客户端维护和管理，这需要高昂的投资和复杂的技术支持，维护成本很高，维护任务量大。

其次，传统的C/S结构的软件需要针对不同的操作系统系统开发不同版本的软件，由于产品的更新换代十分快，代价高和低效率已经不适应工作需要。在JAVA这样的跨平台语言出现之后，B/S架构更是猛烈冲击C/S，并对其形成威胁和挑战

B/S模式

随着应用系统规模扩大，C/S模式的某些缺陷表现得异常突出，例如：客户端软件的安装、维护、升级、和发布以及用户的培训等，随着客户端规模的扩大而变得相当艰难，于是就有了B/S模式

多层B/S模式结构：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

由于客户端使用浏览器，通过Web服务器下载应用服务器上的应用，从而解决了上述问题，应用服务器提供了所有业务逻辑的处理能力，修改应用服务器上的程序即可完成升级

高级数据库系统

分布式数据系统

由一组数据组成。这组数据分布网络上的不同计算机上，网络中的每个节点都有独立处理的能力，这种情况叫场地自治，可以执行局部应用，同时，每个节点也能通过网络通信子系统执行全局应用，区分一个系统是否是分散式还是分布式，就是判断系统是否支持全局应用，

分布式数据库系统分为2部分

分布式数据库

是计算机网络互不干涉各场地上的数据库的逻辑集合，逻辑上属于统一系统，而物理上分布在计算机网络的各个不同的场地上，需要强调数据的分布性与逻辑的整体性

分布式数据库管理系统

是数据库系统中的一组软件，负责管理分布环境下逻辑集成数据的存储，一致性，有效性和完备性。

技术目标：

• 适应部门分布的组织结构，降低费用
• 提高系统的可用性和可靠性
• 充分利用数据库资源，提高现有集中式数据库的利用率
• 逐步扩展处理能力和系统规模

特点

数据独立性

集中与自治相结合的控制结构

适当增加数据冗余度

全局的一致性、可串行性和可恢复性

面向对象数据库系统

是数据库与面向对象程序设计方法相结合的产物

首先它是数据库系统，其次它也是面向对象系统。第一个标准即作为数据库系统应具备的能力(持久性、事务管理、并发控制、恢复、查询、版本管理、完整性、安全性)。第二个标准就是要求面向对象数据库充分支持完整的面向对象(OO)概念和控制机制

把面向对象的方法和数据库技术结合起来可以使数据库系统的分析、设计最大程度地与人们对客观世界的认识相一致。面向对象数据库系统是为了满足新的数据库应用需要而产生的新一代数据库系统。

并行数据库系统

是在并行机上运行的具有并行处理能力的数据库系统

并行数据库系统的目标是高性能（High Performance）和高可用性（High Availability），通过多个处理节点并行执行数据库任务，提高整个数据库系统的性能和可用性。
性能指标关注的是并行数据库系统的处理能力，具体的表现可以统一总结为数据库系统处理事务的响应时间。并行数据库系统的高性能可以从两个方面理解，一个是速度提升（SpeedUp），一个是范围提升（ScaleUp）。速度提升是指，通过并行处理，可以使用更少的时间完成两样多的数据库事务。范围提升是指，通过并行处理，在相同的处理时间内，可以完成更多的数据库事务。并行数据库系统基于多处理节点的物理结构，将数据库管理技术与并行处理技术有机结合，来实现系统的高性能。
可用性指标关注的是并行数据库系统的健壮性，也就是当并行处理节点中的一个节点或多个节点部分失效或完全失效时，整个系统对外持续响应的能力。高可用性可以同时在硬件和软件两个方面提供保障。在硬件方面，通过冗余的处理节点、存储设备、网络链路等硬件措施，可以保证当系统中某节点部分或完全失效时，其它的硬件设备可以接手其处理，对外提供持续服务。在软件方面，通过状态监控与跟踪、互相备份、日志等技术手段，可以保证当前系统中某节点部分或完全失效时，由它所进行的处理或由它所掌控的资源可以无损失或基本无损失地转移到其它节点，并由其它节点继续对外提供服务。
为了实现和保证高性能和高可用性，可扩充性也成为并行数据库系统的一个重要指标。可扩充性是指，并行数据库系统通过增加处理节点或者硬件资源（处理器、内存等），使其可以平滑地或线性地扩展其整体处理能力的特性。
随着对并行计算技术研究的深入和SMP、MPP等处理机技术的发展，并行数据库的研究也进入了一个新的领域，集群已经成为了并行数据库系统中最受关注的热点。并行数据库领域主要还有下列问题需要进一步地研究和解决。
（1）并行体系结构及其应用，这是并行数据库系统的基础问题。为了达到并行处理的目的，参与并行处理的各个处理节点之间是否要共享资源、共享哪些资源、需要多大程度的共享，这些就需要研究并行处理的体系结构及有关实现技术。
（2）并行数据库的物理设计，主要是在并行处理的环境下，数据分布的算法的研究、数据库设计工具与管理工具的研究。
（3）处理节点间通讯机制的研究。为了实现并行数据库的高性能，并行处理节点要最大程度地协同处理数据库事务，因此，节点间必不可少地存在通讯问题，如何支持大量节点之间消息和数据的高效通讯，也成为了并行数据库系统中一个重要的研究课题。
（4）并行操作算法，为提高并行处理的效率，需要在数据分布算法研究的基础上，深入研究联接、聚集、统计、排序等具体的数据操作在多节点上的并行操作算法。
（5）并行操作的优化和同步，为获得高性能，如何将一个数据库处理事务合理地分解成相对独立的并行操作步骤、如何将这些步骤以最优的方式在多个处理节点间进行分配、如何在多个处理节点的同一个步骤和不同步骤之间进行消息和数据的同步，这些问题都值得深入研究。
（6）并行数据库中数据的加载和再组织技术，为了保证高性能和高可用性，并行数据库系统中的处理节点可能需要进行扩充（或者调整），这就需要考虑如何对原有数据进行卸载、加载，以及如何合理地在各个节点是重新组织数据。

多媒体数据库系统

多媒体数据库是数据库技术与多媒体技术结合的产物。多媒体数据库不是对现有的数据进行界面上的包装，而是从多媒体数据与信息本身的特性出发，考虑将其引入到数据库中之后而带来的有关问题。

(1)物理层：是多媒体数据库的物理存储描述，即形式描述多媒体数据在计算机的物理存储设备上是如何存放的。对多媒体数据库而言，实际的数据允许分散在不同的数据库中。例如，多媒体的人事档案管理中，某人的声音和照片可能保存在声音数据库和图像数据库中，他的其他的人事记录可能保存在关系数据库中。
(2)概念层：表示的是现实世界的抽象结构，是对现实世界事物对象的描述。多媒体应用开发人员通过该层提供的数据库语言可以对存储在多媒体数据库中的各种多媒体数据进行统一的管理。
概念层由一组概念对象构成。概念对象涉及的对象可能来自几个数据库。例如，人是由人事记录、照片等描述，它们可能分别来自一般的关系数据库和图像数据库。在概念层上，模式必须按照几个数据库的概念模式来定义。
(3)表现层：可以分为视图层和用户层。用户层是多媒体数据库的外部表现形式，即用户可见到的表格、图形、画面和播放的声音等。用户层可由专门的多媒体布局规格说明语言来描述，并向用户提供使用接口。多媒体数据管理系统的表现模式在多媒体数据库系统的研究中是一个需要重视的问题。由于各种非格式化数据的表现形式各不相同，同时它们之间存在一定的关联性，所以表现层在多媒体数据库系统中较之它在传统数据 [1]

(1)联邦型结构
对多媒体数据的管理是分开进行的，每一种媒体数据库的设计也不必考虑与其他媒体的匹配和协调。
(2)集中统一型结构 ‘
只存在一个单一的多媒体数据库和单一的多媒体数据库管理系统。各种媒体被统一地建模，对各种媒体的管理与操纵被集中到一个数据库管理系统之中，各种用户的需求被统一到一个多媒体用户接口上，多媒体的查询检索结果可以统一地表现。
(3)客户/服务型结构
各种单媒体数据仍然相对独立，系统将每一种媒体的管理体制与操纵各用一个服务器来实现，这种体系结构也很容易扩展到网络环境下工作。

多媒体数据与传统数据库数据有显著的不同，多媒体数据库有以下特点。
①数据量巨大且媒体之间量的差异十分明显，而使得数据在库中的组织方法和存储方法复杂。
②媒体种类的繁多使得数据处理变得非常复杂。前边介绍了4种多媒体数据，而实际上，在具体实现时，常常根据系统定义、标准转换而演变成几十种媒体形式。
③多媒体不仅改变了数据库的接口，使其声、图、文并茂，而且也改变了数据库的操纵形式，其中最重要的是查询机制和查询方法。媒体的复合、分散、时序性质及其形象化的特点，使得查询不再只是通过字符查询，查询的结果也不仅是一张表，而是多媒体的一组“表现”。接口的多媒体化将对查询提出更复杂、更友好的设计要求。

(1)多媒体数据库系统必须能表达和处理各种媒体的数据。
(2)多媒体数据库系统必须能反映和管理各种媒体数据的特征，或各种媒体数据之间的时间和空间的关联。
(3)对于多媒体数据的查询应该是基于内容的，但内容应当事先被描述。
(4)多媒体数据库系统应该具有开放性，提供应用程序接口以及提供独立于外设和格式的接口。
(5)多媒体数据库系统的数据操作功能，除了提供对无格式数据的查询搜索功能外，还应能对不同媒体提供不同的操作方法，如图形、图像的编辑处理，声音数据的剪辑等。
(6)多媒体数据库系统的网络功能。由于多媒体应用一般以网络为中心，应解决分布在网络上的多媒体数据库中数据的定义、存储、操作等问题，并对数据的一致性、安全性进行管理。
(7多媒体数据库应提供供处理长事和版本控制的能力。

SQL

简介

SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

SQL 是什么？

• SQL 指结构化查询语言，全称是 Structured Query Language。
• SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。
• SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。

SQL 能做什么？

• SQL 面向数据库执行查询
• SQL 可从数据库取回数据
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限

虽然 SQL 是一门 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言，但是仍然存在着多种不同版本的 SQL 语言。

然而，为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。

​​	注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的专有扩展！

‍

语言组成

数据定义语言（DDL）
DDL用来定义、修改、删除数据库中的各种对象，包含创建、修改删除或重命名对象

包含：create,alter,drop,rename,truncate

数据操纵语句（DML）
DML用来查询、插入、删除数据库中的数据

包含：select,insert,update,delete

数据控制语言（DCL）

用于事物控制、并发控制、完整性和安全性控制等

包含：commit,rollback,savepoint,grant,revoke,locktable

‍

‍

数据类型

MySQL：

整数类型

类型名称	说明	存储需求
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个宇节
MEDIUMINT	中等大小的整数	3个字节
INT (INTEGHR)	普通大小的整数	4个字节
BIGINT	大整数	8个字节

类型名称	说明	存储需求
TINYINT	-128〜127	0 〜255
SMALLINT	-32768〜32767	0〜65535
MEDIUMINT	-8388608〜8388607	0〜16777215
INT (INTEGER)	-2147483648〜2147483647	0〜4294967295
BIGINT	-9223372036854775808〜9223372036854775807	0〜18446744073709551615

字符串类型

枚举类型

Mysql中的enum类型就是我们常说的枚举类型，它的取值范围需要在创建表时通过枚举方式(一个个的列出来)显式指定。对1至255个成员的枚举需要1个字节存储；对于255至65535个成员，需要2个字节存储。最多允许有65535个成员。

enum底层存的是十进制整数,严格按顺序1,2,3,4,5…排列,固千万不要用enum来存数字。

​有时候可以使用枚举代替常用的字符串类型，枚举列可以把一些不重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到1个或2个字节中​。MySQL在内部会将每个值在列表中的位置保存为整数，并且在.frm​文件中保存“数字-字符串”映射关系的“查找表”。

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2​^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR 和 VARCHAR 类型

CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M 表示列的长度，范围是 0～255 个字符。

例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当检索到 CHAR 值时，尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

例如，VARCHAR(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

【实例】下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列，说明 CHAR 和 VARCHAR 之间的差别，如下表所示。

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
’ ’	’ ’	4字节	‘’	1字节
‘ab’	'ab’	4字节	'ab’	3字节
'abc’	'abc’	4字节	'abc’	4字节
'abcd’	'abcd’	4字节	'abcd’	5字节
'abcdef’	'abcd’	4字节	'abcd’	5字节

对比结果可以看到，CHAR(4) 定义了固定长度为 4 的列，无论存入的数据长度为多少，所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1。

TEXT 类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

• TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。
• TEXT 表示长度为 65535（216-1）字符的 TEXT 列。
• MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。
• LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM 类型

ENUM 是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )

字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。

ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。

ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

例如，定义 ENUM 类型的列（‘first’，‘second’，‘third’），该列可以取的值和每个值的索引如下表所示。

值	索引
NULL	NULL
‘’	0
’first	1
second	2
third	3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。> 提示：ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

SET 类型

SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号,​隔开，语法格式如下：

SET( '值1', '值2', …, '值n' )

与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

提示：如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

小数类型

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL (M, D)，DEC	压缩的“严格”定点数	M+2 个字节

FLOAT 类型的取值范围如下：

• 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
• 无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。

FLOAT 类型的取值范围如下：

• 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
• 无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

日期时间类型

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC~2040-01-19 03:14:07 UTC	4字节

YEAR 类型

YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：

• 以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 ‘1901’～’2155’。输入格式为 ‘YYYY’ 或者 YYYY，例如，输入 ‘2010’ 或 2010，插入数据库的值均为 2010。
• 以 2 位字符串格式表示的 YEAR，范围为 ‘00’ 到 ‘99’。‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
• 以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。

TIME 类型

TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

可以使用各种格式指定 TIME 值，如下所示。

• ‘D HH：MM：SS’ 格式的字符串。还可以使用这些“非严格”的语法：‘HH：MM：SS’、‘HH：MM’、‘D HH’ 或 ‘SS’。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。
• ‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

DATE 类型

DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’，其中 YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

• 以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期，取值范围为 ‘1000-01-01’～’9999-12-3’。例如，输入 ‘2015-12-31’ 或者 ‘20151231’，插入数据库的日期为2015-12-31。
• 以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：‘00～69’ 范围的年值转换为 ‘2000_{2069’，'70}99’ 范围的年值转换为 ‘1970～1999’。例如，输入 ‘15-12-31’，插入数据库的日期为 2015-12-31；输入 ‘991231’，插入数据库的日期为 1999-12-31。
• 以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
• 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH：MM：SS’，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

• 以 ‘YYYY-MM-DD HH：MM：SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期，取值范围为 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。例如，输入 ‘2014-12-31 05：05：05’ 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
• 以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，‘00～79’ 范围的年值转换为 ‘2000～2079’，‘80～99’ 范围的年值转换为 ‘1980～1999’。例如，输入 ‘14-12-31 05：05：05’，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入数据时，要保证在合法的取值范围内。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

• DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
• 而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

二进制类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

BIT 类型

位字段类型。M 表示每个值的位数，范围为 1～64。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b’101’，其效果与分配 b’000101’ 相同。

BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

提示：默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。使用的语法格式如下：列名称 BINARY(M) 或者 VARBINARY(M)

BINARY 类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充 “\0” 补齐，以达到指定长度。例如，指定列数据类型为 BINARY(3)，当插入 a 时，存储的内容实际为 “\a0\0”，当插入 ab 时，实际存储的内容为“ab\0”，无论存储的内容是否达到指定的长度，存储空间均为指定的值 M。

VARBINARY 类型的长度是可变的，指定好长度之后，长度可以在 0 到最大值之间。例如，指定列数据类型为 VARBINARY(20)，如果插入的值长度只有 10，则实际存储空间为 10 加 1，实际占用的空间为字符串的实际长度加 1。## BLOB 类型

BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它们可容纳值的最大长度不同，如下表所示。

数据类型	存储范围
TINYBLOB	最大长度为255 (2​8​-1)字节
BLOB	最大长度为65535 (2​16​-1)字节
MEDIUMBLOB	最大长度为16777215 (2​24​-1)字节
LONGBLOB	最大长度为4294967295或4GB (2​31​-1)字节

BLOB 列存储的是二进制字符串（字节字符串），TEXT 列存储的是非进制字符串（字符字符串）。BLOB 列是字符集，并且排序和比较基于列值字节的数值；TEXT 列有一个字符集，并且根据字符集对值进行排序和比较。

转移字符

转义字符	转义后的字符
"	双引号（"）
’	单引号（'）
|反斜线（\）
\n	换行符
\r	回车符
\t	制表符
\0	ASCII 0（NUL）
\b	退格符

• 字符串的内容包含单引号'​时，可以用单引号'​或反斜杠\​来转义。
• 字符串的内容包含双引号"​时，可以用双引号"​或反斜杠\​来转义。
• 一个字符串用双引号"​引用时，该字符串中的单引号 '​不需要特殊对待，且不必被重复转义。同理，一个字符串用单引号'​引用时，该字符串中的双引号"​不需要特殊对待，且不必被重复转义。

运算符

算术运算符

运算符	作用
+	加法
-	-
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

比较运算符

符号	描述	备注
--------------------
=	等于
-	-	-
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

逻辑运算符

NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

位运算符

运算符号	作用
&	按位与
-	-
^	按位异或
!	取反
<<	左移
>>	右移

数据定义语言 DDL

数据库

创建数据库

create database database_name;

选择数据库

use database_name;

删除数据库

drop database_name;

表

创建表

create table table_name ( column_name data_type [default value_1] [auto_increment primary key]);

利用子查询来创建表

create table table_name select code;

显示表结构

desc table_name;

截断表/清空表（删除表中的全部数据，保留表结构）

truncate table table_name;

删除表（表结构和数据都删掉）

drop table table_name;

修改表

alter table table_1 
# 增加列
add column_1 data_type [default value_1]  
# 重命名 列
change column_1 column_2 data_type  
# 修改 列 信息
modify column_1 data_type [default value_1];  
# 删除 列
drop column_1  
# 重命名 表
rename to table_2

‍

数据操纵语言 DML

数据查询

select < * / column_name / column_expression >
  from < table_name >
  [ where [conditional_expression] ]
  [ group by code]
  [ having code]
  [ order by code]

<>表示必须值，[]表示可选值

基本查询

select [ all / distinct ] < * / column_name / column_expression >;

all : 默认项,显示查找出来的全部内容
distinct : 显示查找出来的不重复内容

起别名（修改查询结果的列名，便于阅读）

select column_name [as] new_name, column_name2 [as] new_name2 from table_name;

as 可以省略

where 子句指定查询条件

select * from table_name where condition_expression;

运算符	描述
=	等于	a = b
<> / !=	不等于	a <> b / a != b
>	大于	a > b
<	小于	a < b
>=	大于等于	a >= b
<=	小于等于	a <= b
BETWEEN	在某个范围内	a [not] between b and c
LIKE	搜索某种模式	a [not] link
IN	指定针对某个列的多个可能值	a [not] in <set()>
is null	判断是[否]空值	a [not] is null
not and or	表示 非 与 或

order by 排序

order by 子句必须放在最后

select column_1, column_2, ...
  from table_name
  order by column_1, column_2, ... asc|desc;

asc：表示按升序排序。默认选项。
desc：表示按降序排序。

order by 多列的时候，先按照第一个column_name排序，在按照第二个column_name2排序

模糊查询 like

[not] like 'a%'

/*
like 支持百分号“%”和下划线“_”通配符
“%”是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。例如，a%b表示以字母 a 开头，以字母 b 结尾的任意长度的字符串。该字符串可以代表 ab、acb、accb、accrb 等字符串。
“_”只能代表单个字符，字符的长度不能为 0。例如，a_b可以代表 acb、adb、aub 等字符串。
注意事项：
注意大小写。MySQL 默认是不区分大小写的。如果区分大小写，像“Tom”这样的数据就不能被“t%”所匹配到。
注意尾部空格，尾部空格会干扰通配符的匹配。例如，“T% ”就不能匹配到“Tom”。
注意 NULL。“%”通配符可以到匹配任意字符，但是不能匹配 NULL。也就是说 “%”匹配不到 tb_students_info 数据表中值为 NULL 的记录。

分组查询

分组查询通过加入group by子句完成，通常用聚合函数对每个组中的数据进行汇总，统计。

聚合函数

SQL Aggregate 函数

AVG() - 返回平均值
COUNT() - 返回行数
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值

SQL Scalar 函数

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

group by 子句

单列分组

+-----------+------+--------+-------+--------+
| name      | age  | gender | class | result |
+-----------+------+--------+-------+--------+
| chenjiale |   22 |      1 |     1 |   NULL |
| aa        |   18 |      0 |     1 |   NULL |
| bb        |   19 |      0 |     1 |   NULL |
| cc        |   29 |      0 |     1 |   NULL |
| dd        |   24 |      0 |     0 |   NULL |
| ee        |   34 |      1 |     0 |   NULL |
+-----------+------+--------+-------+--------+<