视图，索引

create view  testview

as

聚集索引     表中数据行的物理存储顺序与索引顺序完全相同

非聚集索引 不改变表中数据行的物理存储顺序，数据与索引分开存储，索引中仅包含 索引值和指向数据行的指针

create unique clustered index ix_briday on dbo.stu --nonclustered
(
id asc
)

sp_helpindex stu

exec sp_rename 'stu.new_index_name','index_test','index'

drop index stu.index_test

set showplan_all on  --off

check 约束

create table teacher(
id int not null primary key,
name char(10),
sex char(2) check(sex in ('男','女'))
)

alter table teacher
add age int null

alter table teacher 
add constraint ck_age check (age>=0 and age<=100)

alter table teacher
drop constraint ck_age 

 规则很少用 rule

use test 
go
create table  teacher(
id char(6) not null,
name char(16) not null,
nation char(10) not null,
sex char(2) not null,--default '男'
price int constraint max_price check(price<=20)
constraint sex default '男'
)

alter table teacher
add default '汉族' for nation

alter table teacher
add zzmm char(4) not null
constraint name default '李明' --with values

alter table teacher 
drop constraint name

create default mz_default as '汉族'
exec sp_binddefault 'mz_default','stu.nation'

exec sp_addtype sex,'cahr(2)','null'

if exists(select name from sysobjects where name ='xb_default' and type ='D')
BEGIN 
 EXEC sp_unbindefault 'sex'
 drop default xb_default
END

create table book(
书号 char(6),
类型 char(20),
价格 int 
constraint max_price1 check (价格<=200)
)

use test 
go 
create rule type_rule as @类型 in('计算机','科普','文学')
go 
use test 
go 
exec sp_bindrule 'type_rule','book.类型'
go

exec sp_unbindrule 'book.类型'
drop rule type_rule 
alter table book
 add constraint booktype default 'new book' for 类型

 alter table book 
 drop constraint booktype 

 use test
 go
 create default day as 'getdate()'
 go
 
 alter table book 
 add 入学时间 datetime null

 exec sp_bindefault 'day','book.入学时间'
 exec sp_addtype today,'datetime','null'
 exec sp_bindefault 'day','today'
 alter table book 
 add 购书时间 today null 

 exec sp_unbindefault 'book.入学时间'
 exec sp_unbindefault 'today'

create table stu(id int not null constraint xh_pk primary key)

alter table student 
add constraint kcb_pk primary key clustered(stuid)

alter table kcxx
drop constraint kcb_pk

create table table_name(
id int not null constraint xh_pk primary key,
name char(8) not null,
IDCard int constraint sfzh_uk unique
)

alter table kcxx 
add constraint cbh_uk unique nonclustered(id)

alter table table_name 
drop constraint kcb_uk

create table xsda(
学号 char(6) not null constraint xh_pk primary key,
姓名 char(8) not null
)

create table xscj(
 学号 char(6) not null foreign key refeRences xsda(学号) 
)

alter table xscj
add constraint kc_foreign foreign key(课程编号) references kcxx(课程编号)

alter table xscj
drop constraint kc_foreign

use test
go
declare @name char(6)
set @name =(select top 1 name from xsda)
select @name
go

select @@connections,@@rowcount,@@error,@@procid,@@remserver,@@servername,@@version,@@language,@@max_connections