T-SQL：表的创建和管理

男娘i 2022-07-28 11:58 125阅读 0赞

# T-SQL：表的创建和管理 #

# 数据类型 #

## 整数类型 ##

**bit**：可以取值为0、1或null。当作布尔类型来用，1为true。  
smallint -32,768到32,767 2字节  
**int**：-20亿到20亿，4字节  
**bigint**：-2^63到2^63-1，8字节

## 小数类型 ##

**numeric\[ (p\[ ,s\] )\]**：- 10^38 +1 到 10^38 – 1，p（精度） 包括小数点左边和右边的位数。 1—38。默认 18。s（小数位数） 小数点右边的位数。0—p，默认0, 最大存储大小基于精度而变化。decimal和numeric等价。  
**money**：代表货币或货币值的数据类型。-9220亿—9220亿，精确到货币单位的万分之一，8字节。

## 字符串 ##

固定长度的字符串检索效率更高。  
**char \[ ( n ) \]**：ASCII编码，固定长度,小于指定的字符数目时，后面自动填充空格，大于指定的数时，会截取超出的字符，n 用于定义字符串长度，1—8,000,默认1。 存储大小为 n 字节。  
**varchar \[ ( n | max ) \]**：可变长度。 max 指示最大存储大小是 2^31-1 个字节 (2 GB)。 存储大小为所输入数据的实际长度 + 2 个字节，很少用max。  
**nchar \[ ( n ) \]**：Unicode编码，固定长度。1—4000，存储大小为 n 字节的两倍。  
**nvarchar \[ ( n | max ) \]**：可变长度

## 日期与时间 ##

**datetime**：日期范围1753 年 1 月 1 日到 9999 年 12 月 31 日，时间范围00:00:00 到 23:59:59.997，8字节  
date：0001-01-01 到 9999-12-31，3 个字节  
time：00:00:00.0000000 到 23:59:59.9999999  
datetime2 ：日期范围0001-01-01 到 9999-12-31，时间范围00:00:00 到23:59:59.9999999  
如果要兼容08以前的只能用datetime

# 创建数据表 #

use 数据库名；--使用当前数据库
    create table 表名 （ 列名称1 数据类型 not null ,列名称2 数据类型 default 默认值 ,列名称3 数据类型 identity（开始值，步）--自动增长 ,列名称4 数据类型 unique ,列名称5 数据类型 identity（初始值，步长） ,Age int check(Age>0) primary key(主键字段), foreign key（外键字段）references 目标表（主键字段） ）；

## not null（非空约束） ##

在类型定义后边加not null

## default（定义默认值） ##

在类型定义后边加default 默认值  
撤销约束  
alter table 表名  
alter column 表名 drop default

## identity（初始值，步长） ##

设定一个字段为自动增长列，设定初始值和每次增长的值

## primary key(主键约束) ##

每张表都要有主键约束，主键字段中的值必须是唯一的。  
可定义到所有字段之后，primary key(主键字段)  
定义到末尾  
constraint 约束名 primary key（主键字段）  
给已有表添加  
alter table 表名  
add constraint 约束名 primary key（主键字段）  
最好不要使用联合主键（让多个字段做主键），选定主键时，最好选一个和业务无关的字段

## foreign key（外键约束） ##

foreign key 外键字段 reference 目标表(主键字段）  
给已建表添加约束  
alter table 表名  
add constraint 约束名  
foreign key 外键字段 reference 目标表(主键字段）  
Author表，外键表

<table> 
 <thead> 
  <tr> 
   <th>Id</th> 
   <th align="center">Name</th> 
   <th align="right">Age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="center">Danny</td> 
   <td align="right">23</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="center">Jenny</td> 
   <td align="right">43</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="center">Limng</td> 
   <td align="right">53</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="center">Tom</td> 
   <td align="right">12</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="center">Kim</td> 
   <td align="right">22</td> 
  </tr> 
 </tbody> 
</table>

Book表

<table> 
 <thead> 
  <tr> 
   <th>Id</th> 
   <th align="center">Name</th> 
   <th align="right">PageCount</th> 
   <th align="right">AuthorId</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="center">JavaScript</td> 
   <td align="right">300</td> 
   <td align="right">1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="center">WPF</td> 
   <td align="right">430</td> 
   <td align="right">1</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="center">C#</td> 
   <td align="right">500</td> 
   <td align="right">3</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="center">VB</td> 
   <td align="right">450</td> 
   <td align="right">5</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="center">C</td> 
   <td align="right">250</td> 
   <td align="right">4</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td align="center">C</td> 
   <td align="right">250</td> 
   <td align="right">2</td> 
  </tr> 
 </tbody> 
</table>

create table Author ( Id int primary key, Name varchar(50), Age int );
    
    create table Book ( Id int primary key, Name varchar(50), PageCount int, AuthorId int foreign key (AuthorId) references Author(Id) )

限制AuthorId的值只能来源于Author表中的Id。

## unique（唯一约束） ##

防止一个特定的列中两个记录具有一致的值。  
在字段定义后增加unique  
或定义在所有字段列表之后  
constraint 约束名 unique（字段1，字段2…）  
在已经创建的表上创建唯一约束  
alter table 表名  
add constraint 约束名 unique（字段1，字段2…）  
撤销约束  
alter table 表名  
drop constraint 约束名

## check约束 ##

检查输入到记录中的值是否满足一个条件，如果不满足这个条件则对数据库的修改不会成功。  
可在字段定义后添加check约束，但缺点是约束条件不能引用其他列。  
若要使用其他列，在末尾定义  
constraint 约束名 check（约束名）  
给已建表添加约束  
alter table 表名  
add constraint 约束名 check（约束名）

## index(索引) ##

在不读取整个表的情况下，创建索引可更快的查找数据。  
可以把索引想象成汉语字典的按笔画查找的目录。汉语字典中的汉字是按拼音的顺序排列在书页中的，如果好查询笔画数为12的汉字就必须挨个查找每个汉字来比较每个汉字的笔画数，但如果我们创建一个按笔画查找的目录：将笔画为5的汉字列出来，将笔画为6的汉字列出来。。。，这样当要查找笔画数为12的汉字的话只要来查找这个目录就可以非常快速的查找到需要的数据了。  
但索引会占据一定的磁盘空间，减慢数据插入和删除的速度。

create index 索引名 on 表名（字段1，字段2...）
    
    drop index 表名.索引名

# 修改已有数据表 #

## 添加字段 ##

alter table 表名 add 字段名 字段类型

## 删除字段 ##

alter table 表名 drop 字段名

# 删除数据表 #

若表之间创建了外键关联关系，必须先删除创建外键的表，再删除被引用的表。

drop table 表名