文章目录
- 2.3.2 创建表结构
- 索引（INDEX）
- 聚集索引
- 非聚集索引
- 2.3.3 查看表结构
- 2.3.4 修改表结构
- 2.4.1 向表中插入数据
- 2.4.2 修改表中数据
- 常用字符串函数
2.3.2 创建表结构
数据库中文件组、文件、表之间的关系：
创建“完整”表的相关工作：
- （1）确定表名；
- （2）确定字段基本信息：包括字段名、字段类型、字段长度、是否允许为空值；
- （3）确定字段附加信息：默认值、标识符、计算列、主键、外键、CHECK约束条件等；
- （4）确定主件组；
- （5）确定索引；
- （6）确定触发器；
- （7）添加数据。
方法：
- 使用SQL Server Management Studio
- 主外键的设置
- 标识列（自动增长列）的添加
- 默认值的添加
- 计算列的添加
- 索引的添加
- 使用SQL的语句
使用SQL语句
- 语法：
create table table_name
({column_name datatype
[not null|null]})
[on filegroup_name]
标识符的类型选择：
| 数据类型 | 优点 | 缺点 |
|---|---|---|
| 整型 | 简单，存储量少 | 分布式系统合并表时，内容肯能重复 |
| GUID | 不会重复 | 复杂（128位的16进制数字标识符），存储量大；不适合建立索引 |
默认值的内容：
1.该数据类型的一个常量。
2.该数据类型的一个函数：
| 数据类型 | 函数 |
|---|---|
| 日期时间 | getdate() |
| 整型 | identity() |
| GUID | newid() |
计算列持久化的选择：
| 持久化 | 对计算结果进行保存 | 能否创建索引 | 查询效率 |
|---|---|---|---|
| 否 (默认) |
否 (在查询时显示结果，不占用磁盘空间) |
否 | 高 |
| 是 | 是（占据磁盘空间） | 是 | 低（如果建立索引，效率比非持久化列高） |
创建数据库和表的标准格式：
use master
go
create database A
…………
go
use A
go
create table B
…………
go
…………
SQL中错误的类型：
- 语法错误
- 执行过程错误
- 执行结果错误
- 语法报错，但执行无措
索引（INDEX）
- 定义：索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。
- 内容：索引包含由表或视图中的一列或多列生成的键（键存储在一个B树中）。
- 分类：
- 聚集索引
- 非聚集索引
- 唯一索引
- 包含性列索引
聚集索引
聚集索引：确定表中数据的物理顺序（存储顺序），根据数据行的键值在表或视图中排序和存储数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。如果表没有聚集索引，则其数据行存储在一个无序的堆结构中。
说明：
- 检索效率比其他索引高；
- 在插入、修改和删除数据时要花费较多的时间（数据和索引都要随之变动）；
- 在表中建立主键后，系统会在主键字段自动添加聚集索引（也可以在其他字段上建立聚集索引）。
非聚集索引
非聚集索引：非聚集索引包含非聚集索引键值（并按内容进行排序，称为逻辑顺序），并且每个键值项都有指向包含该键值的数据行的指针。
说明：
- 非聚集索引建立后表中数据的物理顺序不变；
- 查询效率不如聚集索引；
- 在插入、修改和删除数据时影响较小；
- 非聚集索引可以有多个；
- 只能手动创建
聚集索引和非聚集索引的对比⭐
| 索引类型 | 个数 | 索引对应字段的值 | 索引对应字段值的顺序 | 效率 | 建立方法 |
|---|---|---|---|---|---|
| 聚集索引 | 0或1个 | 可以唯一，也可以不唯一 | 升序或降序 | 检索效率较高，更新效率较低 | 建立主键后自动生成，或手动建立 |
| 非聚集索引 | 0、1或多个 | 可以唯一，也可以不唯一 | 与索引建立前的顺序一致 | 检索效率较低，更新效率较高 | 手动建立 |
索引使用的技巧和注意事项：
- 非索引列在查询时无法使用索引；
- 建立索引的列值重复率越低越好；
- 在外键所在列上建立索引；
- 聚集索引要建立在查询频率最高的列上；
- 非聚集索引并不是越多越好；
- 建立复合索引时，将查询频率最高的列放于最左边。
- 在创建非聚集索引之前，先创建聚集索引；在删除聚集索引之前，先删除非聚集索引；
- 索引碎片：
①产生原因：表中数据的长期频繁修改；
②影响：当索引碎片量过多时，降低查询性能；
③解决方法：重建索引（碎片量超过40%时）。
索引的功能总结：
- 加快检索（不止是单一的索引项查询，还包括排序、分组、连接等查询）速度；⭐
- 加快参照完整性的维护速度；
- 约束索引所在列的数据取值：是否唯一；
- 改变数据的存储顺序（聚集索引）。
2.3.3 查看表结构
※方法：
- 使用SQL Server Management Studio
- 使用SQL语句
- 语法：sp_helo [表名]
※修改的风险（表中已经存放数据）：
- 宽度的减少 —— 数据部分丢失
- 类型的变化 —— 数据整体丢失
- 字段名的改变 —— 数据不一致、引用的SQL语句需修改
- 字段的删除 —— 数据丢失、表间关系的破坏
- 宽度的增加 —— 无风险
- 字段的增加 —— 无风险
- 非空→非空 —— 无风险
※使用SQL语句
- 语法：
alter table table_name
{ {add 列名{列的描述}}
| {alter column 列名{列的描述}}
| {drop column 列名}
}
※修改对象名
sp_rename ‘原名’，‘姓名’ [,‘类型’]
■ 类型：
- 表和其他对象：省略
- 数据库：database
- 列：column
- 索引：index
2.3.4 修改表结构
※方法：
■ 使用SQL Server Management Studio
■ 使用SQL语句
- 语法：drop table table_name
- 注意：不能删除系统表。
2.4.1 向表中插入数据
※方法：
■ 使用SQL Server Management Studio
■ 使用SQL语句
- 语法：
insert [into] 表名[(列名)]
values（数据）
注意：
- 输入数据的顺序和数据类型必须与表中列的顺序和数据类型一致。
- 字符型和日期型数据需要用单引号引起来。
- 没有赋值的列必须是可以为空或能自动写入数据的列。
- 数据必须满足所在列的约束规则：
（1）实体完整性规则
（2）参照完整性规则
（3）自己定义的完整性规则 - 尽量不要添加内容完全相同的行。
2.4.2 修改表中数据
※方法：
■ 使用SQL Server Management Studio
■ 使用SQL语句
- 语法：
update 表名
set 列名1 = 表达式1，列名2 = 表达式2，……
[where 条件]
常用字符串函数
- 求字符串长度
len(字符串) - 取子串
（1）left（目标字符串，长度）
（2）right（目标字符串，长度）
（3）substring（目标字符串，起始位置，长度） - 字符串填充
stuff（目标字符串，起始位置，替换长度，替换字符串） - 字符串连接 +
- 去除空格
（1）ltrim（字符串）
（2）rtrim（字符串）
（3）ltrim（rtrim（字符串）） - 返回字符串中子串的开始位置
charindex（ 子串，字符串 [，开始查找位置] ）
