use shiyan
go
create table Student
(
Sno char(9) not null,
Sname char(20) not null,
Sex char(2) null,
Sage smallint null,
Sdept char(20) null,
primary key (Sno),
unique(Sname),
)


use shiyan 
go
create table Course
(
Cno char(4) not null,
Cname char(40) null,
Cpno char(4) null,
Credit smallint null,
primary key(Cno),
foreign key(Cpno) references Course(Cno)
)


use shiyan
go
create table SC
(
Sno char(9) not null,
Cno char(4) not null,
Grade smallint null,
primary key (Sno,Cno),
foreign key (Cno) references Course(Cno),
foreign key (Sno) references Student(Sno),
)
go




insert into Student values(‘200215121‘,‘李勇‘,‘男‘,‘20‘,‘cs‘)
insert into Student values(‘200215122‘,‘刘晨‘,‘女‘,‘19‘,‘cs‘)
insert into Student values(‘200215123‘,‘王敏‘,‘女‘,‘18‘,‘ma‘)
insert into Student values(‘200215125‘,‘张立‘,‘男‘,‘19‘,‘is‘)








insert into Course values(‘1‘,‘数据库‘,null,‘4‘)
insert into Course values(‘2‘,‘数学‘,null,‘2‘)
insert into Course values(‘3‘,‘信息系统‘,null,‘4‘)
insert into Course values(‘4‘,‘操作系统‘,null,‘3‘)
insert into Course values(‘5‘,‘数据结构‘,null,‘4‘)
insert into Course values(‘6‘,‘数据处理‘,null,‘2‘)
insert into Course values(‘7‘,‘pascal语言‘,null,‘4‘)








insert into SC values(‘200215121‘,‘1‘,‘92‘)
insert into SC values(‘200215121‘,‘2‘,‘85‘)
insert into SC values(‘200215121‘,‘3‘,‘88‘)
insert into SC values(‘200215122‘,‘2‘,‘90‘)
insert into SC values(‘200215122‘,‘3‘,‘80‘)
insert into SC values(‘200215123‘,‘4‘,‘87‘)
insert into SC values(‘200215123‘,‘5‘,‘95‘)


use shiyan 
go
alter table Student 
add Brithday datetime


use shiyan
go
alter table Student 
drop column Brithday


insert into SC values(‘200215121‘,‘5‘,null)


use shiyan
update Student
set Sno=‘200215188‘
where Sno=‘200215127‘


use shiyan
update Student
set Sname=‘李咏‘
where Sname=‘李勇‘ and Sdept=‘cs‘


use shiyan
update SC
set Grade=Grade+5
where Cno=‘1‘


insert into Student values(‘200215199‘,‘王靖靖‘,‘女‘,‘21‘,‘cs‘)


use shiyan
update Student
set Sname=‘王丹丹‘,Sex=‘女‘,Sdept=‘ma‘,Sage=‘20‘
where Sno=‘200215199‘ 


use shiyan
update Student
set Sdept=null
where Sno=‘200215199‘ 


use shiyan
go
delete from Student
where Sdept is NULL   --此处用“where Sdept = null”错误
go


use shiyan 
go
select * from Student




use shiyan 
go
select * from Course


use shiyan 
go
select * from SC


use shiyan 
go
select Sdept,count(Sdept) as ‘人数‘ from Student  group by Sdept
order by 人数


select Sdept,SUM(Sage)/COUNT(Sdept) as ‘平均年龄‘  from Student group by Sdept
order by 平均年龄 desc


select Sex ,SUM(Sage)/COUNT(Sex)as‘平均年龄‘from Student group by Sex


select Sno,count(Sno)as ‘课程数‘ from SC group by Sno


update SC
set Grade = 60
where Cno =2 and Grade<(select sum(Grade)/COUNT(*) from SC where Cno =2)


use shiyan
go
select Sno,Sname from Student
where Sdept=‘cs‘ and Sage not between 19 and 21




use shiyan
go
create table customer
(
customid Varchar(17) not null,
name Varchar(10) null,
sex Varchar(2) null check (sex=‘男‘ or sex =‘女‘),
age Int null check (age>18 and age<45),
xfg Dec(10,2) null,
address Varchar(50) null,
memo Varchar(100) null,
primary key(customid),
unique(name),
)

数据库学习