Database creation delete attribute modification statement

Time:2020-2-16
Create database database name


Create database database name    
on primary(
Name = 'database name',            
Filename ='d: \ first name. MDF ',
size=10mb,
maxsize=unlimited,
filegrowth=10%)    
log on(
name='test_log',
Filename ='d: \ first name. LDF ',
size=1mb,
maxsize=5mb,
filegrowth=1mb)
go


alter database test
    Add filegroup filegroup name
    
    

alter database test
    add file
    (name='test_data2',
    Filename ='d: \ 170508010430 Dong Zhiyang \ test_data2. NDF ',
    size=5mb,
    maxsize=100mb,
    filegrowth=5mb),
    (name='text_data3',
    Filename ='d: \ 170508010430 Dong Zhiyang \ text_data3. NDF ',
    size=5mb,
    maxsize=100mb,
    filegrowth=5mb)
    to filegroup testgroup
 go
 
 
alter database test
    add log file
    (name='test_log2',
    Filename ='d: \ 170508010430 Dong Zhiyang \ test_log2. LDF ',
    size=1mb,
    maxsize=10mb,
    filegrowth=1mb)
    go


alter database test
    remove filegroup textgroup    
    

alter database test 
    remove file test_data2
    

    Drop database database name  
    
    

alter database test
    modify file
    (name='test',
    maxsize=unlimited,
    filegrowth=15%)
    go
    

    
DBCC shrinkdatabase(test,20)


DBCC shrinkfile(test,10)



create database test
on
    (filename='d:\test.mdf')    
for attach

create database zhangsan
on
    (filename='d:\zhangsan_data1.mdf'),
    (filename='d:\zhangsan_data2.ndf'),
    (filename='d:\zhangsan_log.ldf')
    for attach


use master
go
execute sp_detach_db 'test','true'  






create table student        
(sno    char(10) check (sno like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
primary key,
sname   varchar(8) not null,    
Sex char (2) default 'male' check (sex in ('male ',' female '),        
scomegrade smallint check(scomegrade between 0 and 750),    
sbirthday date check(year(getdate())-year(sbirthday) between 15and 35),        
snation bit default 'false',        
smemo    text,                
sphone  char(11) unique,        
semail  varchar(50) unique)


            
            
            

    
alter table student add Stuaddress char(40) null,Zipcode char(6) not null

    
alter table student
    alter column sdept varchar(30) not null  
    
    
alter table student drop column stuaddress

    



 create table sc
 (sno char(10),
 cno char(10),
 grade tinyint,
 primary key (sno,cno),
 foreign key(sno) references student (sno) 
    on update cascade        
    on delete cascade,
 foreign key(cno) references course(cno)
    on update no action    
    on delete no action,
  )
  
 
Alter table table name add constraint PK ﹣ CNO primary key (segment name);  
    
Alter table table name DROP constraint PK ﹣ CNO;

Alter table table name add constraint u CNAME unique (segment name);

Alter table student add constraint CK ENU sex check (sex = 'male' or sex = 'female')
    
Alter table table name DROP constraint CK ﹣ sex;


Alter table student add constraint df_dept default 'computer system' for sdept
    
Alter table table name DROP constraint df_dept;

alter table score add constraint fk_cno foreign key(cno) references course(cno)
    
alter table score drop constraint fk_cno


Alter table table name
nocheck
constraint all

Alter table table name check constraint ck_sex



Exec sp_rename 'table name', 'new name'    

Exec sp_rename 'table name. Old field name', 'new field name', 'column'


Drop table table name