dev-train-doc/sql/02.db&table.md

库表操作

一、数据库操作

1.新建数据库

（1）可视化创建

​ 右键服务器下的数据库节点，在弹出的窗口中设置新建数据库的属性。

（2）语句创建

​ 在查询编辑器中执行查询语句新建数据库，执行后在对象资源管理器中刷新即可选择新建的数据库。

create database testDb1
(
    name = testDb1_data,
	filename = '从盘符开始的物理文件路径\testDb1.ss' --设置数据库主文件的文件名和物理路径，自动创建文件
    
    /* 有需要可以设置更多属性，比如下面几个 */
    size = 1mb, --系统创建数据库时，给主文件分配的初始空间大小
	maxsize = 64mb, --主文件的最大空间
	filegrowth = 15% --文件自动增长的幅度
)

2.修改数据库属性

（1）可视化修改

​ 右键数据库，选择属性，在弹窗里设置数据库的属性。

（2）语句修改

1. 更改数据库排序规则

   alter database testDb1 collate Chinese_PRC_CI_AS --数据库按中文排序

1. 添加/删除数据库文件

   /* 添加数据库日志文件 */
   alter database testDb1
   add log file
   (
   	name = 'testDb1_log', --日志文件逻辑文件名
   	filename = 'D:\testDb1_log.ldf', --日志文件物理文件名
   	size = 5mb, --日志文件初始大小
   	maxsize = 500mb, --日志文件最大大小
   	filegrowth = 5 --启动自动增长
   )
   
   /* 删除日志文件 */
   alter database testDb1
   remove file testDb1_log

1. 增加数据库容量

   alter database testDb1 modify file 
   (
   	name = 'testDb1_data', --通过修改数据库文件，增加数据库容量
   	maxsize = 128mb
   )

3.删除数据库

（1）可视化删除

​ 右键数据库，选择删除，在弹出的对话框中判断是否删除备份。

（2）语句删除

/* 方法一 */
drop database testDb1

/* 方法二：当直接删除报错时，尝试下面的语句 */
declare @dbname sysname 
set @dbname = 'testDb1' --填写无法删除的数据库名称

declare @s nvarchar(1000) 
declare tb cursor local 
for 
select s = 'kill ' + cast(spid as varchar) 
from master.dbo.sysprocesses 
where dbid = DB_ID(@dbname) 

open tb 
fetch next from tb into @s 
while @@fetch_status = 0 
begin 
exec (@s) 
fetch next from tb into @s 
end 
close tb 
deallocate tb 

exec ('drop database [' + @dbname + ']')

二、表操作

1.新建表

（1）可视化新建

​ 右键数据库下的表节点，在表设计器中填写列名、数据类型、是否为空以及其他属性。

（2）语句新建

create table testTable1
(
	id varchar(10) not null primary key,
	name nvarchar(50) not null,
	age int not null,
	joindate date null,
	note nvarchar(50) null
)

2.修改表结构

（1）可视化修改

​ 右键要修改的表，选择设计，在窗口中增/删/改列，设置索引等。

（2）语句修改

/* 添加列 */
alter table testTable1 add data float null

/* 修改列 */
alter table testTable1 alter column data decimal null
--修改建立了索引的列时，要先将索引删除再修改
alter table testTable1 drop constraint PK_testTable1_id
alter table testTable1 alter column id int not null

/* 删除列 */
alter table testTable1 drop column data

3.删除表

（1）可视化删除

​ 右键要删除的表，选择删除。删除之前，确定所有的依赖都已经被删除之后，点击确认删除。

（2）语句删除

/* 删除数据表中的全部数据 */
delete from t01

/* 删除数据库表 */
drop table t01

三、主键

1.主键的特点

• 主键列的值不可重复，
• 可以根据主键唯一确定数据库表中的一条数据，
• 值不能为null，
• 主键约束可以是一列，也可以是多列，

2.设置主键

（1）可视化设置

​ 右键要修改的表，选择设计，右键要设置为主键的列，选择设置主键。

​ 右键要修改的表，选择设计，右键主键列，选择删除主键。

（2）语句设置

/* 创建表时设置主键 */
create table testTable1
(
	id varchar(10) not null primary key, --primary key 标识此列为主键，但修改和删除主键时比较麻烦
	name nvarchar(50) not null,
	age int not null,
	joindate date null,
	note nvarchar(50) null
)

/* 单独设置主键 */
alter table testTable1 add constraint PK_testTable1_name_age  primary key (name, age) --设置主键约束的名称为PK_testTable1_name_age，方便后续修改和删除

/* 修改主键 */
--先删除原有主键
alter table testTable1 drop constraint PK_testTable1_name_age
--再新建主键
alter table testTable1 add constraint PK_testTable1_id primary key (id)

四、约束

1.约束的种类

1. 主键约束(PRIMARY KEY)：定义数据库表的主键，主键的所有列都不能为null

   1. 一个表中只能有一个主键约束；
   2. 主键的值不重复，且不为null；
   3. 主键可以设置一列或多列；

   constraint constraint_name 
   primary key (column_name[,…n]) 

1. 惟一性约束(UNIQUE)：用于限制某一列或某几列的组合值惟一

   1. 一个表中只能有一个主键约束，但可以有多个唯一约束；
   2. 唯一约束允许值为null；
   3. 对于同一列，不能同时定义主键约束和唯一约束；

   constraint constraint_name 
   unique (column_name[,…n]) 

1. 检查约束(CHECK)：为某一列或整个表中的值设置限制条件

   1. 一个表中可以有多个检查约束；
   2. 检查约束分为列级检查约束和表级检查约束；
   3. 表中的每个字段最多只能定义一个检查约束；
   4. 对多个字段进行检查，则必须将约束定义为表级约束；
   5. 检查约束中不能包含子查询；

   constraint constraint_name 
   check (logical_expressions) 

1. 默认值约束(DEFAULT)：若执行插入操作时没有提供该列的值，系统自动插入默认值

   1. 默认追可以为常量、函数、不带变量的内置函数、空值等；
   2. 每个字段只能定义一个默认约束；
   3. 默认约束不能加在带有IDENTITY属性或数据类型为timestamp(时间戳)字段上；
   4. 若默认值的长度超过字段的允许长度，则插入时默认值会被切断；

   constraint  constraint_name
   default constraint_expression [for column_name]

1. 外键约束(FOREIGN KEY)：用于建立和加群两个表数据之间的关联

2.设置约束

（1）可视化设置

​ 右键要设置约束的表，选择设计，右键要设置索引的列，选择“索引/键”或“CHECK约束”。在弹出的窗口中添加或删除索引。

（2）语句设置

/* 创建表时设置约束 */
create table testTable2
(
	id varchar(10) not null 
    	constraint PK_testTable1 primary key,
	name nvarchar(50) not null 
    	constraint UK_testTable1_name unique,
	age int not null 
    	constraint CHK_testTable1_age check (age > 0),
	joindate date null 
    	constraint DF_testTable1_date default getdate(),
	note nvarchar(50) null
)

/* 单独设置约束 */
alter table testTable1 add constraint PK_testTable1_name_age primary key (id)
alter table testTable1 add constraint UK_testTable1_name unique (name)
alter table testTable1 add constraint CHK_testTable1_age check (age > 0)
alter table testTable1 add constraint DF_testTable1_date default getdate()

/* 修改约束 */
--先删除约束
alter table testTable1 drop constraint CHK_testTable1_age
alter table testTable1 drop constraint DF_testTable1_date
--再添加约束
alter table testTable1 add constraint CHK_testTable1_age check (age > 0 and age < 100)
alter table testTable1 add constraint DF_testTable1_date default null