建立三个表:Student(学生信息表),Course(课程表),SC(学生选课表)
create table Student(Sno char(9)PRIMARY KEY,
Sname char(20)UNIQUE,
Ssex char(2),
Sage SMALLINT,
Sdept char(20));
create table Course(Cno char(4)PRIMARY KEY,
Cname char(40) not null,
Cpno char(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno)REFERENCES Course(Cno));
create table SC(Sno char(9),
Cno char(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno)REFERENCES Student(Sno),
FOREIGN KEY (Cno)REFERENCES Course(Cno),);
存入信息:
insert into Student () values ();
insert into Course () values ();
insert into SC () values ();
向student表添加记录(10条记录添加方式一样)
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values
('201616041','张三','男',20,'CS');
向course表添加记录(10条记录添加方式一样)
insert into Course (Cno,Cname,Cpno,Ccredit) Values
('1102','数学',NULL,2);
向sc表添加记录(10条记录添加方式一样)
insert into SC(Sno,Cno,Grade) values
('201616040','1101',54);
对Student的执行增删改操作(三条语句分别为插入,修改,删除)
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values
('201616055','宋十五','男',22,'CS');
update Student set Sage = 50 where Sno='201616055';
delete from Student where Sno='201616055';
对Course的执行增删改操作(三条语句分别为修改,插入,删除)
update Course set Ccredit = 10 where Cno='1101';
insert into Course(Cno,Cname,Cpno,Ccredit) values
('1111','政治',null,4);
delete from Course where Cno='1109';
delete from Course where Cno='1103';
对SC的执行增删改操作(三条语句分别为插入,修改,删除)
insert into SC(Sno,Cno,Grade) values
('201616058','1101',22);
update SC set Cno=1106 where Sno='201616058';
delete from SC where Sno='201616040';
对Student表做修改表结构练习(依次是增加列,修改列的属性,修改列的名称,删除列)
alter table Student add CardID varchar(16);
alter table Student alter column CardID varchar(400) ;
EXEC sp_rename 'Student.CardID','CardIDTest';
alter table Student drop column CardIDTest;
select * from Student where Sdept='CS'; //查询计算机系学生的信息;
select count(*) from Student; //查询全体学生的人数;
select Ssex, count(Ssex) 人数 from Student group by Ssex; //查询全体男生人数和女生人数;
//查询每个系中的男生人数并按人数的降序排列;
select Sdept,Ssex,count(Sdept) 人数
from Student
group by Ssex ,Sdept
having Ssex='男'
order by count(Sdept);
select * from Student Where Sname LIKE '%十%'; //查询名字中带“十”的学生信息;
//求被选修的各门课程的平均成绩和选修该课程的人数;
select Cno 课程号,count(Sno) 人数,AVG(Grade) 平均成绩
from SC
where Grade is not null
group by Cno;
//查找选修课程超过2门且成绩都在80分以上的学生的学号。由于最初使的表不存在这样的学生,于是我在SC表中又添加了一行201616044学生的信息 insert into SC values ('201616044','1108',98);
select Sno
from sc
where Grade>80
group by sno
having count(Sno)>=2;
单表查询(针对上述三个表):