sql命令日常

①学生表:Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为：Student(Sno,Sname,Ssex,Sage,Sdept)，Sno 为关键字。

create table Student (
Sno number(5) primary key,
Sname varchar2(10),
Ssex number(5),
Sage number(5),
Sdept varchar2(15)
)

②课程表:Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记为： Course(Cno,Cname,C pno,Ccredit) Cno为关键字。

create table Course (
Cno number(5) primary key,
Cname varchar2(10),
Cpno number(5),
Ccredit number(5)
)

③成绩表:SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记为： SG(Sno,Cno,Grade) (SNO,CNO) 为关键字。

create table SG (
Sno number(5),
Cno number(5),
grade number(5),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)

insert into Student values(1,'s1',1,18,'dept1')
insert into Student values(2,'s2',0,17,'dept2');
insert into Student values(3,'s3',0,19,'dept3');
insert into Student values(4,'s4',1,16,'dept4');
insert into Student values(5,'s5',1,15,'dept3');
insert into Student values(6,'s6',0,16,'dept2');
insert into Student values(7,'s7',1,19,'dept1');
insert into Student values(8,'s8',0,16,'dept1');
insert into Student values(9,'s9',0,17,'dept2');
insert into Student values(10,'s10',1,17,'dept4');
insert into Student values(11,'s11',0,20,'dept2');
insert into Student values(12,'s12',1,17,'dept3');
insert into Student values(13,'s13',1,19,'dept2');
insert into Student values(14,'s14',0,18,'dept2');
insert into Student values(15,'s15',0,17,'dept1');
insert into Student values(16,'s16',1,18,'dept1')
select * from Student

insert into Course values(10,'course1',1,2);
insert into Course values(60,'course1',20,2);
insert into Course values(20,'course1',40,3);
insert into Course values(30,'course1',60,4);
insert into Course values(40,'course1',50,4);
insert into Course values(50,'course1',30,2)
select * from Course

insert into SG values(1,20,78);
insert into SG values(2,30,68);
insert into SG values(3,40,77);
insert into SG values(4,50,45);
insert into SG values(5,60,56);
insert into SG values(6,40,88);
insert into SG values(7,30,89);
insert into SG values(8,40,97);
insert into SG values(9,50,73);
insert into SG values(10,60,76);
insert into SG values(11,40,68);
insert into SG values(12,20,58);
insert into SG values(13,30,89);
insert into SG values(14,50,96);
insert into SG values(15,60,78);
insert into SG values(16,20,66);
insert into SG values(8,20,88)
select * from SG

用SQL语言实现下列功能：

1．建立学生表[Student]，其中学号属性不能为空，并且其值是唯一的。

2．查考试成绩有不及格的学生的学号。

select Student.Sno from Student inner join SG on Student.Sno=SG.SNO where SG.GRADE<60

3．将学号为05001学生的年龄改为22岁

update Student set Sage='22' where Sno=12

4．计算1号课程的学生平均成绩。

select * from Course

select avg(grade) as "20号课程平均成绩" from SG where Cno=20

5. 查计算机系姓赵的男同学的姓名(Sname)、性别(Ssex)、年龄(Sage)。

select * from Student

update Student set Sname='赵三胖' where Sno=5

update Student set Sname='赵大桥' where Sno=10

select Sname,Ssex,Sage from Student where Sname like '赵%' and Sdept='dept3'

6．将一个新学生记录(学号：05020；姓名：丁莉；性别：女；年龄：17岁；所在系：计算机；)插入Student表中。

insert into Student values(05020,'丁莉',0,17,'dept3')

7．向Student表增加“入学时间(Scome)”列，其数据类型为日期型。

alter table Student add Scome date;

8．查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

select Sno,grade from SG where Cno=20 order by grade desc;

9．查询学习1号课程的学生最高分数、平均成绩。

select * from SG

select max(grade) as "最高成绩",avg(grade) as "平均成绩" from SG where Cno=40

10．查询与“丁莉”在同一个系学习的学生。

select Sno,Sname from Student where Sdept=(select Sdept from Student where Sname='丁莉')

11．将计算机系全体学生的成绩置零。

update SG set grade='0' where Sno in (select Sno from Student where Sdept='dept3')

12．删除学号为20180032的学生记录。

delete from Student where Sno='5020'

select * from Student
13．删除计算机系所有学生的成绩记录。

delete from SG where Sno in (select Sno from Student where Sdept='dept3')

14、查询全体学生的详细记录。

select * from SG inner join Student on SG.SNO=Student.Sno inner join Course on SG.CNO=Course.Cno;

15、查询年龄在20至23岁之间的学生的姓名、系别、和年龄。

select Sname,Sdept,Sage from Student where Sage between 18 and 22;

16.student分页查询，每页10条(https://www.cnblogs.com/jhxxb/p/10670661.html)

select * from (
select Student.*,rownum hang from Student
)where hang between 0 and 10;

---

13题中，级联删除可以删除一个表，与之相关的表数据也会删除，如：

create table comments(
Sno number(4),
Cno number(2),
Grade number(4),
foreign key(Sno) references student(Sno) [on delete cascade],
foreign key(Cno) references course(Cno)
)

---

创建视图

create view aa as
select Student1.Sdept,SG.Grade from Student1,SG
where Student1.Sno = SG.Sno;