[大数据]sqlserver数据表的创建与管理

创建表

use mydbone
go
create table Dept_info
(
  dno char(4) primary key,
  dname  char(16) not null,
  d_char  char(50),
  d_address   varchar(50),
  tel  char(12)
)
 
go
create table Class_info
(
  classno char(4) primary key,
  classname char(16) not null,
  monitor  char(8),
  instructor   char(8),
  tel  char(12),
  dno char(4),
  foreign key (dno) REFERENCES Dept_info(dno)

)
go
create table Student_info
(
  sno char(8) primary key,
  sname char(8) not null,
  sex  char(2),
  s_native   varchar(50),
  birthday  smalldatetime,
  dno  char(4),
  classno char(4),
  entime smalldatetime,
  home varchar(50),
  tel char(12),
  foreign key (dno) REFERENCES Dept_info(dno),
  foreign key (classno) REFERENCES Class_info(classno)

)

在表S中插入数据

use mydbone
go
insert into S(sno,sname,age,sex,sdept)
values('s3','小明','18','男','SE')

给SC(sno,cno,grade)表指定属性赋值
use mydbone
go
insert into SC(sno,cno)

修改表中某个数据

给SC表中sno = 's2' and cno = 'c10'的人的grade赋值
use mydbone
go
update SC set grade = 90
where sno = 's2' and cno = 'c10'

删除表数据

use mydbone
go
delete from SC where sno = 's3' and cno = 'c10'

表结构修改

为S表中age属性增加约束
use mydbone
go
alter table S
add constraint Con_age check(age>=15 and age <=30)

为表S增加属性class
use mydbone
go
alter table S
add class varchar(20)

限制S表中class字段长度为50
use mydbone
go
alter table S
alter column class varchar(50)

删除S表中属性class
use mydbone
go
alter table S
drop column class

查看表信息

查看表S空间大小等信息
use mydbone
go
exec sp_spaceused 'S'

如图：

查看所有用户表空间大小等信息
use mydbone
go
exec sp_MSforeachtable "sp_spaceused '?'"

如图：