–给某一列设置主键
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