SQL的学习学生表_sql数据库期末大作业学生表

1.创建数据库

create database school;

2.创建学生表，成绩表，课程表

create table student(
Sno int primary key not null,
Sname varchar(20) not null,
Ssex varchar(2) null,
Birthday datetime null,
Sdept varchar(20) null);

 

create table course(
Cno int primary key not null,
Cname varchar(40) null,
Cpno int null,
Credit dec(3,1) null,
foreign key(Cpno) references course (Cno));

create table SC(
Sno int not null,
Cno int not null,
Grade dec(4,1) null,
foreign key(Sno) references student (Sno),
foreign key(Cno) references  course(Cno),
);

3.插入数据（举个例子）格式如下

insert
into SC(Sno,Cno,Grade)
values('202215129','1005',59);

 4.修改数据（格式如下）

    例子：把202215129的1005课程分数改成99分。

update SC
set Grade=99
where Sno in
(
  select Sno
  from student 
  where Sno='202215129'
);

   结果，全部分数都变成99分了。

 修改：（加上限制条件课程号即可）

update SC
set Grade=98
where Cno='1005'and Sno in
(
  select Sno
  from student
  where student.Sno='202215129'
);

 5.修改基本表（格式）

alter table student
drop column address;

例子1，删除多余的列address

原来的图片：

 运行后：

 例子2：在学生表中增加入学时间

alter table student
add S_entrance date;

6.删除数据（打个样）

 Delete student
 where SSex is Null

7.单表查询

例子1：计算并查询学生年龄大于20岁的人

select Sname 姓名,Birthday , YEAR(GETDATE())-YEAR(Birthday) as age
from student
where age>20;

例子2：计算学生年龄并且排序

select Sname 姓名,Birthday , YEAR(GETDATE())-YEAR(Birthday) as age
from student
order by age desc;

结果：

 例子3：查询成绩不合格学生以及学生的成绩

select distinct student.Sno 学号,Sname,Cno 课程号,Grade 分数
from student,SC
where student.Sno=SC.Sno and Grade<60;

结果：

例子4：查询选择全部课程的学生（格式打个样，两种思路）

select SC.Sno,Sname 
from SC inner join student on SC.Sno=student.Sno
group by Sname,SC.Sno 
having count(Cno)=(select count(*)from course)

select * from student where Sno in(
select Sno from SC
 group by Sno 
having count(Cno)=(select count(*)from course))

 8.连接查询（注意粉红色部分的格式）

例子1：查询所有学生学号、姓名、性别、及总学分

select student.Sno,student.Sname,Ssex,sum(credit) as 总学分
from student
inner join SC on SC.Sno=student.Sno
inner join course on SC.Cno=course.Cno
group by student.Sno,student.Sname,Ssex;

 例子2：查询各科选修人数，课程最高分，最低分，及格人数，不及格人数，平均分

select course.cno,cname,count(sno) as 选课人数,MAX(GRADE) AS 最高分,min(grade) as 最低分,avg(grade) as 平均分,
sum(case when Grade >60 then 1 else 0 end) as 及格人数,
sum(case when Grade <60 then 1 else 0 end) as 不及格人数
from course left join SC on course.cno=SC.cno
group by course.cno,cname

9.嵌套查询

例子：查询计算机学院(CS)比数学学院(MA)某一个同学年龄小的学生(出生日期越大,年龄越小)

select * from student
where Sdept='CS' and Birthday > any(select Birthday from student where Sdept='MA')

select * from student
where Sdept='CS' and Birthday > (select min(Birthday) from student where Sdept='MA')

10.视图创建（格式如下）

create view Vgrade as