【SQL Server】数据库——表操作

建表

数据类型

建表同时加四个约束

create table test_name 
(
	number char(10) not null,
	name char(20) not null,
	sex char(2) constraint def_cst default '男', //默认约束
	phone char(11) constraint uq_cst unique, //唯一约束
	age int constraint check_cst check (age > 1 and age < 80), //检查约束
	primary key (number,name),
)

建表同时建外键

create table grade
(
	number char(10) not null primary key,
	constraint FK_test_name_grade foreign key (number) references test_name(number)
)

表操作

修改表名

 exec sp_rename test_name,test_newname

增加字段

alter table test_name
 add grade int

修改字段信息

alter table test_name
 alter column grade tinyint

删除字段

alter table test_name
 drop column grade

修改字段名

exec sp_rename 'test_newname.phone1','phone2'

插入数据

insert into test_name values('10090106','张三','女','123456',23)
insert into test_name values('10090122','李四',NULL,'123456',22)
insert into test_name values('10090126','王五',NULL,'123456',22)

删除数据

delete test_name where name = '张三'

修改数据

update test_name set age = age - 3 where name = '李四'

索引

创建索引

• unique：唯一索引
• clustered： 聚集索引
• noclustered：非聚集索引

create unique index uq_phone
ON test_newname(phone2)

查看索引

exec sp_helpindex test_newname

删除索引

drop index test_newname.uq_phone

约束

增加字段约束

--增加字段约束——检查约束
alter table test_newname
 add constraint check_sex check (sex = '男' or sex = '女')
--增加字段约束——唯一约束
alter table test_newname
 add constraint unique_phone unique (phone2)
--增加字段约束——默认约束
alter table test_newname
 add constraint default_phone default '110' for phone2
--增加字段约束——外键约束
alter table grade
 add constraint fk_test_newname_grade 
 foreign key (number) references test_newname(number)

删除约束

alter table test_name
	drop constraint def_cst