数据库基本操作-数据表和索引

在SQL语言中，字符型数据被放在一对单引号（‘ ’）中，用于区别其他类型的数据；例如，‘home’、‘张三’、‘047122813810’、‘123_**^’等都是字符型数据；每个字符型数据都有长度，其长度是该字符型数据的字符个数，例如，‘home’的长度为4；需要注意的是，每个汉字占两个字符的位置，例如，‘张三’的长度是4，而不是2。

还有一点，‘047122813810’看起来是数字，但因为将其放在了单引号内（英文输入法状态下），所以是字符型数据。

数字型数据

数字型数据就是通常据所说的数字，它可以由0~9之间的数字、正负符号与小数点（.）组成；例如100、23.234、-123、-58.42等都是数字型数据。数字型数据不允许放在任何定界符之内；可以用浮点形式的科学记数法表示，如3.46E+03。数字型数据的长度：例如，100的长度为3，23.234的长度为6（数字型数据长度包含小数点），-123的长度为3.

日期时间数据

SQL中还有一种日期时间数据，例如，2023-11-11 12:25:30、2023年11月15日、01/JAN/2023、22:30:10等。

二进制数据

这里所说的二进制数据是专指用来表示图形图像、视频动画和其他类型的文件等，当前流行的所有数据库系统都支持二进制数据。例如，在SQL Server中提供了IMAGE数据类型等，通常用于存放图片；在MySQL中提供了BLOB和LONGBOLB数据类型，用来存放BLOBs数据等。

1.2、MySQL中的数据类型

MySQL支持所有标准SQL数值数据类型，包括整数类型（INTEGER、SMALLINT、DECIMAL和NUMERIC），以及小数类型（FLOAT、REAL和DOUBLE PRECISION）。作为SQL标准的扩展，MySQl也支持整数类型TINYINT、MEDIUMINT和BIGINT。

数值类型

TINYINT类型：可以存放-128~127之间的所有正负整数，在内存中占用1个字节的空间。
TINYINT UNSIGNED类型：可以存放0~255之间的所有整数，占用内存中的1个字节。
SMALLINT类型：在内存中占用2个字节的空间。
SMALLINT UNSIGNED类型：无符号的SMALL类型，在内存中占用2个字节的空间。
MEDIUMINT类型：在内存中占用3个字节的空间。
MEDIUMINT UNSIGNED类型：在内存中占用8个字节的空间。
INT或INTEGER类型：在内存中占用4个字节的空间。
INT UNSIGNED或INTEGER UNSIGNED类型：在内存中占用4个字节的空间。
INT UNSIGNED或INTEGER UNSIGNED类型：在内存中占用4个字节的空间。
BIGINT类型：在内存中占用8个字节的空间。
BIGINT UNSIGNED类型：在内存中占用8个字节的空间。
FLOAT类型：在内存中占用4个字节的空间。
DOUBLE或DOUBLE PRECISION或REAL类型：在内存中占用8个字节的空间。
DECIMAL[(M,D)]或NUMERIC(M,D)类型：由M（整个数字的长度，包括小数点，小数点左边的位数，小数点右边的位数，但不包括负号）和D（小数点右边的位数）决定的数字数据类型，M默认为10,D默认为0。

字符串类型

CHAR(M)[BINARY]或NCHAR(M)[BINARY]类型：用于保存定长的字符串，字符串中的每个字符占用1个字节的存储空间。默认BINARY项，则表示不分大小写字母。NCHAR表示使用默认的字符集。M表示字符串的最大长度，当输入的字符串个数小于M，则数据库系统将以空格补足，但在取出来时末尾的空格将自动去掉。
[NATIONAL]VARCHAR(M)[BINARY]类型：用于存放变长的字符串，默认为BINARY项，不区分大小写字母。M的聚会范围是1~255，当输入的字符串个数小于M，则数据库系统将以空格补足，但在取出来时末尾的空格将自动去掉。
TINYBLOB类型：用于保存不超过255个字符的二进制字符串，所占用的存储空间为0~255字节。
TINYTEXT类型：用于存储短文字符串，所占用的存储空间范围为0~255字节。
BLOB类型：用于存储二进制的长文本数据。
TEXT类型：用于存储长文本数据。
MEDIUMBLOB类型：用于存储二进制形式的中等长度的长文本数据。
MEDIUMTEXT类型：用于存储中等长度的长文本数据。
LONGBLOB类型：用于保存极大长度的长文本数据。

日期与时间类型

DATA类型：用于存储日期数据，日期数据的范围为1000-01-01至9999-12-31，每个DATE类型的数据占用3字节的存储空间，其输入格式为“年-月-日（YYYY-MM-DD）”。
DATETIME类型：用于存储混合日期和时间数据，占用8字节的存储空间，输入格式为“年-月-日时-分-秒（YYYY-MM-DD HH:MM:SS）”。
TIME类型：用于存储时间数据或持续时间的数据，占用3字节的存储空间，输入格式为：“时-分-秒（HH:MM:SS）”。
YEAR类型：用于存储年份数据，占用1字节的存储空间，输入格式为“年（YYYY）”。
TIMESTAMP类型：用于存储混合日期和时间值、时间戳，占用8字节的存储空间，输入格式为“年-月-日时-分-秒（YYYY-MM-DD HH:MM:SS）”。

1.3、SQL Server中的数据类型

整数数据类型

INT(INTEGER)数据类型：在内存中占用4个字节。
SMALLINT数据类型：在内存中占用2个字节。
TINYINT数据类型：在内存中占用1个字节。
BIGINT数据类型：在内存中占用8个字节。

浮点数据类型

该数据类型用于存放带有小数点的数值。

DECIMAL[p [s] ]数据类型：用于存放浮点数据，其精度非常的高，可以保留到浮点数据的最小有效数字；这里的p代表浮点数的总位数，但是不包括小数点；s代表小数点后的位数。
NUMERIC数据类型：与DECIMAL数据类型基本相同。
REAL数据类型：用于存放1~7之间的浮点数。
FLOAT数据类型：用于存放精度在8~15之间的浮点数。

二进制数据类型

BINARY(n)数据类型：用于存放二进制数据。其中，n表示数据的长度。
VARBINARY(n)数据类型：与BINARY类型基本相同，不同的是该数据类型存放可变长度二进制数据。

字符数据类型

四种常用的字符数据类型：

CAHR数据类型：CHAR数据类型的定义形式为CHAR[(n)]，以CHAR类型存储的每个字符和符号占一个字节的存储空间；n表示所有字符所占的存储空间，n的取值为1~8000，若不能指定n值，则系统默认值为1；若输入数据的字符数小于n，则系统自动在其后添加空格来填满设定好的空间，若输入过长，将会截掉其超出部分。
NCHAR数据类型：NCHAR数据类型的定义形式为NCHAR[(n)]，它与CHAR类型相似，NCAHR数据类型n的取值为1~4000，NCHAR类型采用UNICODE字符集，UNICODE标准规定每个字符占用两个字节的存储空间。
VARCHAR数据类型：VARCHAR数据类型具有变动长度的特性，存储长度为实际数值长度，若输入数据的字符数小于n，则系统不会在其后添加空格来填满设定好的空间。
NVARCHAR数据类型：NVARCHAR数据类型采用UNICODE字符集，n的取值为1~4000.

例如：

姓名 VARCHAR(20)

姓名 CHAR(20)

都声明了“姓名”是一个字符类型的字段，其后括号内的20代表了该字段中能够输入的最大长度。

注意：虽然VARCHAR能够自动调整字段长度，以此达到节省空间的目的，但是，在查询检索方面，查询CHAR类型的数据会比查询VARCHAR类型的数据更快。

文本和图形数据类型

TEXT数据类型：用于存放大量的文本数据。
IMAGE数据类型：用于存放大量的二进制数据，通常用来存储图像。

日期和时间数据类型

DATETIME数据类型：用于存放日期时间数据，是日期和时间的组合，其数据格式为“YYYY-MM-DD HH:MM:SS”，占用 8 个字节，精确到毫秒级别。
SMALLDATETIME数据类型：与DATETIME数据类型相似，占用 4 个字节，精确到分钟级别。

货币数据类型

MONEY数据类型：是一种特殊的DECIMAL数据，它有4位小数，占用 8 个字节，采用标准数字表示法。
SMALLMONEY数据类型：与MONEY类型相似，占用 4 个字节，采用没有逗号的标准数字表示法。

二、数据表

数据表又被称为表，在关系型数据库系统中，一个关系就是一个表，表结构指的是数据库的关系模型。表是若干列（Column）和若干行（Row）的集合，每一行代表一个唯一的记录，每一列代表一个字段。

数据表由行和列组成，通常人们将行称为记录，而将列称为字段。一个非空数据表实际上是由两部分组成，分别是表结构和其内的数据。

2.1、表的创建

2.1.1、使用CREATE TABLE语句创建表

CREATE TABLE <表名>

（

<字段名1> <数据类型> <NOT NULL> [DEFAULT] <默认值>，

<字段名2> <数据类型> <NOT NULL> [DEFAULT] <默认值>，

...

）；

NOT NULL:可选项，如果在某字段后加上该项，则向表添加数据时，必须给该字段输入内容，即不能为空。
DEFAULT<默认值>：可选项，如果在某字段后加上该项，则向表添加数据时，如果不向该字段添加数据，系统会自动用默认值填充该字段。
示例：创建一个studen表，设置其学号、姓名、和性别三个字段不能为空，给性别字段指定默认值为“男”。

-- 创建表

CREATE TABLE Student (

ID CHAR(4) NOT NULL COMMENT '学号',

Name CHAR(20) NOT NULL COMMENT '姓名',

Sex CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',

Birthday DATETIME COMMENT '出生日期',

Origin VARCHAR(50) COMMENT '来源地',

Contact CHAR(12) COMMENT '联系方式',

Institute CHAR(20) COMMENT '所属学院'

) COMMENT='学生信息表';

-- 查看表结构

SHOW FULL COLUMNS FROM Student;

查看表结构：

SHOW FULL COLUMNS FROM Student;

2.1.2、创建带有主键的表

-- 创建表

CREATE TABLE Student (

ID CHAR(4) PRIMARY KEY NOT NULL COMMENT '学号',

Name CHAR(20) NOT NULL COMMENT '姓名',

Sex CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',

Birthday DATETIME COMMENT '出生日期',

Origin VARCHAR(50) COMMENT '来源地',

Contact CHAR(12) COMMENT '联系方式',

Institute CHAR(20) COMMENT '所属学院'

) COMMENT='学生信息表';

此SQL语句创建以“学号”字段作为主键的student表。

示例：创建score表，并设置“学号”和“课号”两个字段为联合主键。

CREATE TABLE scores (

student_id CHAR(4) NOT NULL COMMENT '学号',

course_id CHAR(4) NOT NULL COMMENT '课号',

score INT COMMENT '成绩',

PRIMARY KEY (student_id, course_id)

);

在这个示例中，使用了 PRIMARY KEY (student_id, course_id) 来将 "学号" 和 "课号" 两个字段设置为联合主键。这表示这两个字段的组合必须唯一标识表中的每一行。主键必须设置为NOT NULL。

2.2、表结构的修改

2.2.1、ALTER TABLE语句格式

修改字段的语法格式

MySQL修改字段名的语法格式

ALTER TABLE 表名 MODIFY 字段名新数据类型[(长度)]；

-- 修改表名为 scores，字段名为 score 的数据类型为 DECIMAL(5,2)

ALTER TABLE scores

MODIFY COLUMN score DECIMAL(5,2);

SQL Server修改字段名的语法格式

ALTER TABLE 表名 ALTER COLUMN 字段名新数据类型[(长度)]；

-- 修改表名为 scores，字段名为 score 的数据类型为 DECIMAL(5,2)

ALTER TABLE scores

ALTER COLUMN score DECIMAL(5,1);

sp_columns 存储过程用于检索指定表的列信息。

添加字段的语法格式

ALTER TABLE 表名 ADD 字段名数据类型[(长度)]；

删除字段的语法格式

ALTER TABLE 表名 DROP COLUMN 字段名；

注意：使用ALTER COLUMN时要更改的字段不能是：数据类型为text、image、ntext或timestamp的字段、表的ROWGUIDCOL字段、计算字段或用于计算字段中的字段、被复制字段、用在索引中的字段。

2.2.2、增加带有默认值的新字段

ALTER TABLE 表名 ADD 字段名数据类型[(长度)] 默认值；

ALTER TABLE users ADD Account varchar(50) DEFAULT 'admin';

2.2.3、修改字段的类型和宽度

不可以修改的数据类型字段

数据类型为text、image、ntext或timestamp的字段。
有UNIQUE约束的字段。
设置默认值的字段。
重复的字段。
计算的或用在计算的字段中。
用于CHECK约束的字段。

SQL Server环境

ALTER TABLE 表名 ALTER COLUMN 字段名数据类型[(新长度)]；

MySQL环境

ALTER TABLE 表名 MODIFY 字段名数据类型[(新长度)]；

2.2.4、删除带有默认值的字段

在SQL Server中，删除带有约束和默认值的字段，必须先删除约束和取消默认值：

ALTER TABLE 表名 DROP CONSTRAINT 约束名|默认值名；

ALTER TABLE 表名 DROP COLUMN 字段名；

MySQL中是可以直接删除的

2.2.5、更改主键

ALTER TABLE 表名 ADD CONSTRAINT 主键约束名 PRIMARY KEY (<字段名1> [,<字段名2>,...]);

示例：为employee表中的“编号”字段添加主键约束

ALTER TABLE employee ADD CONSTRAINT xh_1 PRIMARY KEY (ID);

删除当前主键约束

ALTER TABLE employee DROP PRIMARY KEY；

添加字段联合主键约束

ALTER TABLE employee ADD CONSTRAINT xh_xm PRIMARY (ID,Name)；

复合主键（Composite Primary Key）是由表中的多个列组成的主键。与单一主键（由单个列组成）不同，复合主键使用多个列的组合来唯一标识表中的每一行

2.3、表的删除、截断与重命名

2.3.1、删除表

DROP TABLE 表名；

有时在使用DROP TABLE语句删除数据表时会出现删除失败的情况，导致删除失败的绝大数原因是该表可能与数据库中的其他表存在联系。此时，应先解除表之间的联系，再进行删除表的操作。

2.3.2、截断表

只删除表中的数据，而删除表本身

TRUNCATE TABLE 表名；

使用TRUNCATE语句会更快的删除表中的数据，而且会重置表的存储空间。

2.3.3、重命名表

重命名表在MySQL中使用RENAME语句；在SQL Server中要使用SP_RENAME完成。

MySQL修改表名的语法格式

ALTER TABLE 表名 RENAME TO 新表名；

SQL Server修改表名的语法格式

EXEC SP_RENAME 原表,新表名；

2.4、创建与删除数据库

2.4.1、创建数据库

CREATE DATABASE 数据库名；

2.4.2、删除数据库

DROP DATABASE 数据库名；

三、索引

3.1、索引基础

索引是一种树型结构，如果使用正确的话，可以减少定位和查询数据所需的I/O操作；索引可以加快表中查找数据记录的速度。

使用索引能够提高性能的原因其实很好理解，例如：一种是从第1页开始一页一页地向后找；另一种是在目录中先找到所在的页数，然后直接翻到该页上。在书比较厚的情况下，采用第二种会很快就能找到需要的内容。这里的索引就好比本书的目录，因此使用索引会提高查询性能；假设本书只有3页，则使用第一种方法会更实惠；表明数据表中的记录越多，使用索引可能就会得到越大的效益，反之，使用索引就没有什么价值了。

3.1.1、索引的种类

聚簇索引

一个聚簇索引就是一个在物理上与表融合在一起的视图，表和视图共享相同的存储区域；聚簇索引在物理上以索引顺序重新整理了数据的行，这种体系结构中的一个表只允许有一个聚簇索引。

非聚簇索引

在非聚簇索引中，索引数据和表数据在物理上是分离的，表中的记录并不按照索引中的顺序存储；非聚簇索引的查询效率相对于聚簇索引来说比较低，但由于一个数据表只能创建一个聚簇索引，所以当用户需要使用多个索引时就只能创建非聚簇索引了。

3.2、索引的创建和使用

3.2.1、使用索引应注意的内容

对于只有少量数据记录的表，使用索引查询数据没有任何好处，应当省掉存取和使用索引块的开销，直接执行全表扫描得到表中的所有数据，这样会更快一些。
如果索引字段中有很多不同的数据值和空值时，使用索引会极大的提高性能。
当查询返回的记录较少时，通过索引快速定位和检索目标数据行的速度更快，因此查询性能得到提升；，索引列中不同值的数量与总行数的比率，返回记录数少于全部数据的25%是一个经验法则，则索引可以优化该查询。
索引可以提高查询数据的速度，但它也降低了数据的更新速度。因此，如果要进行大量的更新操作，在执行更新操作前应该删除一些不必要的索引，在更新完毕后再重新创建索引，这样会提高效率。
索引也会占用数据库空间，所以在设计数据库的可用空间时应当考虑索引所占用的空间。
在某字段上创建索引时，应当考虑是否经常使用字段筛选；如果不是，则不应该创建索引，因为该索引不会起什么作用，反而在修改数据时会影响性能。
尽量不要对经常需要更新或修改的字段创建索引，更新索引的开销会降低期望获得的性能。
尽量不要将索引与表存储在同一个驱动器上，分开存储会避免访问冲突，从而提高性能。