sql增删查改复习

select distinct Sname from S inner join C inner join Sc where Cname = "数据库" order by Sname desc;
select Sname ,sum(Ccredit)  from S inner join C inner join Sc  where Grade>=60 groupby Sno having sum(Ccredit)>80;


找出缺少部分学科成绩的学生的学号，课程号
select Sname,Cno from S inner join SC inner join C where Grade = null;

查询平均分在80分以上的学生的平均分和名单。
select Sname,avg(Grade) from S inner join SC inner join C groupby Sname having avg(Grade)>80;

找出同名同姓的学生，输出他们的学号，课程号,成绩

select Sno,Cno,Grade from S,C,SC where S.Sno=SC.Sno && C.cno=SC.cno &&S.Sname in(select Sname from S as s1,S as s2 where s1.Sname = s2.Sname && s1.Sno!=s2.Sno)

学生李四的语文成绩

select  S.Sno,C.Cno from S,C,SC where S.Sno=SC.Sno && C.cno=SC.cno && C.cname="语文"&&S.sname="李四"

udpate SC set grade =95 where Sno,Cno in(select  S.Sno,C.Cno from S,C,SC where S.Sno=SC.Sno && C.cno=SC.cno && C.cname="语文"&&S.sname="李四");

添加主键索引：
alter table tablename add primary key (column)
alter table tablename add unique(column)
alter table tablename  add index (column)

delete from tablename where column = value;

insert into tablename (c1,c2,,,)values (v1,v2,v3,,)