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) referencescourse(Cno),);

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

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

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

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

update SCset Grade=99where Sno in(select Snofrom student where Sno='202215129');

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

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

update SCset Grade=98where Cno='1005'and Sno in(select Snofrom studentwhere student.Sno='202215129');

5.修改基本表（格式）

alter table studentdrop column address;

例子1，删除多余的列address

原来的图片：

运行后：

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

alter table studentadd S_entrance date;

6.删除数据（打个样）

 Delete student where SSex is Null

7.单表查询

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

select Sname 姓名,Birthday , YEAR(GETDATE())-YEAR(Birthday) as agefrom studentwhere age>20;

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

select Sname 姓名,Birthday , YEAR(GETDATE())-YEAR(Birthday) as agefrom studentorder by age desc;

结果：

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

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

结果：

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

select SC.Sno,Sname from SC inner join student on SC.Sno=student.Snogroup 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 studentinner join SC on SC.Sno=student.Snoinner join course on SC.Cno=course.Cnogroup 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.cnogroup by course.cno,cname

9.嵌套查询

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

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

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

create view Vgrade as