【笔记】数据表管理

一、约束

【为什么要有约束？】

保证数据表数据的完整性和合理性

1.实体完整性（对行进行约束）

约束方法：唯一约束、主键约束、标识列

2.域完整性（对列进行约束）

限制数据类型、检查约束、外键约束、默认值、非空约束

3.引用完整性：要求两表相同字段必须一致

外键约束

二、一些名词的基本概念

主键：

• 表中一列或几列组合的，能够唯一标识表中的每一行
• 一个表只能有一个主键
• 多列组合构成的主键称为复合主键
• 原则：最少性和唯一性

外键：

• 相对于主键而言
• 一个表可以有多个外键

标识列

• “自动增长列”或“自动编号”
• 本身没有具体的含义只是用来标识不同的实体
• 实现方式：指定“标识种子”和“标识增量” 默认值都是1

默认值

• 表中的某列，用户不输入数据的时候，其值会被自动录入

数据库关系图

• 可以清楚的查看到表之间的引用关系
• 可以建立引用关系，从主表的主键向子表的引用键拖动

三、数据类型

1. 文本数据类型

• char：固定长度的非Unicode字符数据
• varchar：可变长度非Unicode字符数据
• nchar：固定长度的Unicode字符数据
• nvarchar：可变长度Unicode字符数据
• text：存储长文本信息
• ntext：存储可变长度的长文本

2. 日期和时间

• date：
  YYYY-MM-DD(格式)
  0001-01-01 到 9999-12-31(范围)
  1 天(精确度)
• time：
  hh:mm:ss[.nnnnnnn](格式)
  00:00:00.0000000~23:59:59.9999999(范围)
  100ns(精确度)
• datetime：
  YYYY-MM-DD hh:mm:ss[.nnn](格式)
  1753-01-1~9999-12-31(范围)
  0.00333秒(精确度)
• smalldatetime：
  YYYY-MM-DD hh:mm:ss(格式)
  1900-01-01 到 2079-06-06(范围)
  1 分钟(精确度)

3. 数字数据类型

• 关于int
  int：-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)
  bigint：-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)
  smallint：-2^15 (-32,768) 到 2^15-1 (32,767)
  tinyint：0 到 255
• float和real
  float：-1.79E + 308 至 -2.23E - 308、0 以及 2.23E - 308 至 1.79E + 308
  real：-3.40E + 38 至 -1.18E - 38、0 以及 1.18E - 38 至 3.40E + 38

4. 货币数据类型

• money：-922,337,203,685,477.5808 到 922,337,203,685,477.5807
• smallmoney：-214,748.3648 到 214,748.3647

5. bit数据类型

• 字符串值 TRUE 和 FALSE 可以转换为以下 bit 值：TRUE 转换为 1，FALSE 转换为 0。

四、T-SQL语句

1. 创建表

CREATE TABLE CommoditySort
(
    SortId int IDENTITY(1,1) NOT NULL,
    SortName varchar(50) NOT NULL
)

2. 添加约束

CREATE TABLE CommoditySort
(
    SortId int Primary Key,     -- 主键约束
    SortName varchar(50) Unique,   -- 唯一约束
    SortPrice float NOT NULL,     -- 非空约束
    SortKey char(6) Foreign Key References Table_A(SortKey),  -- 外键约束
    SortRom char(10) Check(SortRom='1' OR SortRom='0'),    --检查约束
    -- 多主键
    -- Primary Key(a,b,c)
)    
-- 或
ALTER TABLE 表名
    ADD CONSTRAINT 约束名 约束类型 具体的约束说明

约束名的取名规则推荐采用：约束类型_约束列

• 主键(Primary Key)约束：如PK_UserId
• 唯一(Unique Key)约束：如UQ_UserCardId
• 默认(Default Key)约束：如DF_UserPasspwd
• 检查(Check Key)约束：如CK_Gender
• 外键(Foreign Key)约束：如FK_SortId

范例1：

1. UserId 要求设置为主键
2. UserPwd 要求长度大于等于6
3. Gender 要求取值为1或0 默认值为0
4. Email 中要存在@字符

ALTER TABLE UserInfo
ADD CONSTRAINT PK_UserId PRIMARY KEY (UserId),
    CONSTRAINT CK_UserPwd CHECK (LEN(UserPwd)>=6),
    CONSTRAINT CK_Gender CHECK (Gender=0 OR Gender=1),
    CONSTRAINT DF_Gender DEFAULT(0) FOR Gender,
    CONSTRAINT CK_Email CHECK (Email LIKE '%@%')
GO

范例2：

1. OrderId 要求设置为主键
2. UserId 要求设置为外键，参照UserInfo表中的UserId列
3. CommodityId 要求设置为外键，参照CommodityInfo表中的CommodityId列
4. PayWay 要求默认值为“网上银行”
5. Confirm 要求取值为0或1 默认值为0
6. SendGoods 要求取值为0或1 默认值为0

ALTER TABLE OrderInfo
    ADD CONSTRAINT PK_OrderId PRIMARY KEY (OrderId),
        CONSTRAINT FK_UserId FOREIGN KEY (UserId) REFERENCES UserInfo(UserId),
        CONSTRAINT FK_CommodityId FOREIGN KEY (CommodityId) REFERENCES CommodityInfo(CommodityId),
        CONSTRAINT DF_PayWay DEFAULT ('网上银行') FOR PayWay,
        CONSTRAINT CK_Confirm CHECK(Confirm=0 OR Confirm=1),
        CONSTRAINT DF_Confirm DEFAULT(0) FOR Confirm,
        CONSTRAINT CK_SendGoods CHECK(SendGoods=0 OR SendGoods=1),
        CONSTRAINT DF_SendGoods DEFAULT(0) FOR SendGoods
GO

注意：

[1] 外键约束需要注意类型、长度必须与引用的主键列的类型、长度完全一致。
[2] 外键约束注意引用的表中必须有主键列

向已存在数据的表中添加约束

ALTER TABLE Employee WITH NOCHECK
    ADD CONSTRAINT CK_EmployeeId CHECK(LEN(EmployeeId)=18)
GO

3. 删除约束

ALTER TABLE Employee
    DROP CONSTRAINT PK_Employee
GO

4. 删除数据表

DROP TABLE CommodityInfo
DROP TABLE CommoditySort

• 检测当前要删除的表是否存在

IF EXISTS(SELECT * FROM sysobjects WHERE name='ComodityInfo')
    DROP TABLE ComodityInfo