简单的sql server操作

–给某一列设置主键
alter table COURSE
add constraint cpk primary key (cno)

–制定某一列为外键
alter table SC
add constraint cpok foreign key(CNO) references COURSE(CNO)

–添加一个列
alter table SC
add
COMETIMES int check(COMETIMES between 0 and 20)

–不能直接修改约束,需要drop原有的再add新的

–建立视图
create view STUDENTINFO(学号,姓名,年龄)
as
select SNO,SNAME,SAGE
from
STUDENT

–修改视图
alter VIEW STUDENTINFO(学号,姓名)
AS
SELECT SNO,SNAME
FROM STUDENT

–模糊查询
select * from STUDENT
where SNAME like ‘李%明’

select * from STUDENT
where SNAME like ‘李_明’

select * from STUDENT
where SNAME like ‘%’

select * from STUDENT
where SNAME = ‘李_明’

–in/not in
select * from STUDENT
where SNO in (select SC.SNO from SC)

select * from STUDENT
where SNO not in (select SC.SNO from SC)

–exist/not exist
–可用来代替联表查询
select * from STUDENT
where exists(select * from SC where STUDENT.SNO=SNO and CNO=’c1’)

select * from STUDENT
where not exists(select * from SC where STUDENT.SNO=SNO and CNO=’c1’)

–查询选修了所有课程的学生
select * from STUDENT
where (not exists
(select * from COURSE where not exists
(select * from SC where CNO=COURSE.CNO and SNO = STUDENT.SNO)
))

–查询选修了所有课程的学生
–和上面的方法比起来需要指定列名，临时表需要命名，在联表时需要指定临时表名
select STUDENT.SNO,SNAME from STUDENT,(
select SNO ,count(SNO) SCCOUNT from SC
group by SNO
having (count(SNO) in (select count(CNO) from COURSE))
) AA
where STUDENT.SNO = AA.SNO