数据表的创建和管理

数据表的创建和管理

数据表结构的创建

create table 语句

create table <表名>
{
	<字段名> <数据类型> [列级完整性约束条件,]
	<字段名> <数据类型> [列级完整性约束条件,]
	......
}

说明：

1. <表名>所要定义的表的名字。一个表可以由一个或多个属性组成；
2. <字段名>一般取具有实际意义的名字；
3. <数据类型>选取合适的类型；
4. 完整性约束条件：

①空值约束（NULL 或 NOT NULL）
②主键约束

[constraint 约束名] primary key [clustered |nonclustered](列名[,...])

③唯一性约束

[constraint 约束名] unique [clustered |nonclustered](列名[,...])

④检查约束

constraint 约束名
check [not for replication](logical_expression)

⑥缺省约束
⑦外部键约束
⑧用规则对象和默认值对对象实现约束
默认约束（default constraint）
外部键约束

[constraint 约束名] foreign key (外键列名) references 参照表 (参照列名) [on update cascade on delete cascade]

练习

create table 部门表(
	部门号 char(4) not null,
	部门名 char(10),
	部门电话 char(13),
	部门地址 char(30),
	部门人数 char(4),
	constraint pk_部门表_部门号 primary key(部门号)
)

create table 员工表(
	员工号 char(4) not null,
	姓名 char(8),
	性别 char(2),
	出生年月 datetime,
	所在部门号 char(4),
	constraint pk_员工表_员工号 primary key(员工号),
	constraint fk_员工表_所在部门号 foreign key(所在部门号) references 部门表(部门号)  
)

create table 项目表(
	项目编号 char(5) not null,
	项目名称 char(20),
	所在地方 char(30),
	项目类型 char(10),
	constraint pk_项目表_项目编号 primary key(项目编号)
)

create table 员工参与项目表(
	员工号 char(4) not null,
	项目编号 char(5) not null,
	职责 char(10),
	constraint pk_员工参与项目_员工号项目编号 primary key(员工号,项目编号),
	constraint fk_员工参与项目_员工号 foreign key(员工号) references 员工表(员工号),
	constraint fk_员工参与项目_项目编号 foreign key(项目编号) references 项目表(项目编号),
)

数据表结构的修改

练习

alter table 部门表
add 部门领导 int
alter table 部门表
add foreign key(部门领导) references 员工表(员工号)	--增加外键

alter table 项目表
add 项目主管 int
alter table 项目表
add foreign key(项目主管) references 员工表(员工号)

alter table 员工表
add 技术职称 char(10)
alter table 员工表
add 工资 numeric

alter table 项目表
add 开工日期 datetime
alter table 项目表
add 完工日期 datetime
alter table 项目表
add check(开工日期<完工日期)   --增加检查约束

alter table 部门表
alter column 部门人数 int   --修改列属性

alter table 项目表
drop column 项目类型	   --删除列

注意

新增加的列属性不能定义为not null
删除属性需要先删除约束，再删除去除了约束的属性

alter table 学生表 
drop constraint ck_学生表_年龄	--删除约束
alter table 学生表		--删除去除了约束的属性
drop column 年龄

或者

alter table 学生表 
drop constraint ck_学生表_年龄,column 年龄

检查约束----举例

create table tablename
(
	num char(6) not null,
	grade int check(grade>0 and grade<100),
	constraint ck_tablename_num check(num like 'T[0-9][0-9][0-9][0-9][0-9]')
)

查看表的信息

select  *from sysobjects 
--系统表中查看数据库、表的信息
select *from sysobjects where name='员工表'	--对象信息（表的信息）
--select *from sys.objects where name='员工表'
select *from sysobjects where name='部门表'
select *from sysobjects where name='项目表'
select *from sysobjects where name='员工参与项目表' 

select *from syscolumns	--列信息
select *from syscolumns where id='277576027'
select *from syscolumns where id=object_id('员工表')

select *from sysindexes	--索引信息
select *from sysindexes where id=object_id('员工表')

select *from sysreferences	--参照信息

注意：

• 查找对象ID的函数 object_id(‘表名’)
• select *from sysobjects 与 select *from sys.objects 区别
  真实存在的表 vs 视图表（虚表）；注意这两种表的列信息不一致，在后续需要用的列信息时注意与自己使用的表列名一致
• 新创建的表系统会更新 sysobjects、sysindexes、syscolumns信息，未插入数据前sysindexes先用来占位