create
database students
use
students
create
table student
(
Id varchar(10),
[name] nvarchar(50) not null,
age int constraint con_age check(age between 0 and 120),
sex int constraint con_sex default 1,
constraint con_prim primary key
(
id
)
)
create
table student2
(
Id varchar(10),
[name] nvarchar(50) not null,
age int constraint con_age2 check (age between 0 and 120),
sex int constraint con_sex2 default 1,
constraint con_prim2 primary key(id)
)
insert
into dbo.student2(Id,[name],age,sex)values('852','jin',22,1)
--
可以多插入几条数据
select
* from dbo.student
select
* from dbo.student2
select
[name] from dbo.student where [name] not in(select [name] from student2)
union
--
将两个表的信息放到一个表中
select
[name] from dbo.student2
select
* --
查找表的列名和数据类型
--c.name as tablename,
--a.name as colname,
--b.name as typename
from
syscolumns a,systypes b ,sysobjects c
where
a.xusertype=b.xusertype
and
a.id=c.id
and
c.name='student'
order
by c.id
select
*--
获取表的约束
from
sysconstraints
select
*
from
syscomments --
获取约束的详细信息
select
*
from
sysobjects
where
type='k'--
获取约束名称
类型
d
表示默认约束
c
表示检查约束
select
* from systypes --
数据类型表
select
* from syscolumns--
列性息表
sysobjects
表中
xtype
的类型
-
11-07 10:25C=CHECK
约束
D=
默认值或
DEFAULT
约束
F=FOREIGN KEY
约束
L=
日志
FN=
标量函数
IF
=
内嵌函数
P=
存储过程
PK=PRIMARY KEY
约束(类型是
K
)
RF=
复制筛选存储过程
S=
系统表
U=
用户表
TF=
表函数
TR=
触发器
UQ=UNIQUE
约束(类型是
K
)
V=
视图
X=
扩展存储过程