大学课程-Oracle-sql基础语句与例子

  • by

上学期学sql service就混嘛,这学期打算好好练习oralce这方面的语句,就把上学期与这学期的基础语句与例子放出来

没合理整理,相对较乱,顺序大致没有问题

一.两个学生表数据库插入语句(老师写的.sql文件)
二.sql service ppt上的一些语句与例子
三.sql service课后的一些练习题语句
四.oracle的一些语法语句

一.两个学生表数据库插入语句(老师写的.sql文件)

上学期sql service数据插入:

--判断表格是否存在,若存在则删除
if exists(select * from sysobjects where name='sc')
--if Object_id('sc') is not null
Drop table sc
if exists(select * from sysobjects where name='student')
--if Object_id('student') is not null
Drop table student
if exists(select * from sysobjects where name='course')
--if Object_id('course') is not null
Drop table course

--建立表格
Create table student
(sno char(10) primary key,
sname varchar(20) not null,
sage smallint,
ssex char(2),
sdept varchar(20))

Create table sc
(sno char(10),
cno char(10),
grade smallint,
primary key(sno,cno),
--foreign key(sno) references student(sno)
--on delete cascade on update cascade ,
--foreign key(cno) references course(cno)
--on delete cascade on update cascade
)

Create table course
(cno char(10) primary key,
cname varchar(20) not null,
credit smallint)

--添加数据
insert into student (sno,sname,sage,ssex,sdept) values('001','王倩',18,'f','CS')
insert into student (sno,sname,sage,ssex,sdept) values('002','张三',18,'m','E')
insert into student (sno,sname,sage,ssex,sdept) values('003','张志明',19,'m','E')
insert into student (sno,sname,sage,ssex,sdept) values('004','刘佳',20,'f','CS')
insert into student (sno,sname,sage,ssex,sdept) values('005','李四',18,'m','CS')
insert into student (sno,sname,sage,ssex,sdept) values('006','王小平',19,'m','C')
insert into student (sno,sname,sage,ssex,sdept) values('007','吴红梅',18,'f','CS')
insert into student (sno,sname,sage,ssex,sdept) values('008','杜海',20,'m','E')
insert into student (sno,sname,sage,ssex,sdept) values('009','杨然',18,'f','C')
insert into student (sno,sname,sage,ssex,sdept) values('010','郭晨光',17,'m','CS')
insert into student (sno,sname,sage,ssex,sdept) values('011','司马光明',19,'m','CS')

insert into course values('c01','数据库',3)
insert into course values('c02','C语言',3)
insert into course values('c03','数据结构',4)
insert into course values('c04','计算机基础',2)
insert into course values('c05','操作系统',3)
insert into course values('c06','信息安全',2)
insert into course values('c07','英语',5)
insert into course values('c08','高数',4)

insert into sc values('001','c01',78)
insert into sc values('001','c02',34)
insert into sc values('001','c03',56)
insert into sc values('001','c04',69)
insert into sc values('001','c05',55)
insert into sc values('001','c06',33)
insert into sc values('001','c07',64)
insert into sc values('001','c08',35)
insert into sc values('002','c03',65)
insert into sc values('002','c05',88)
insert into sc values('002','c06',93)
insert into sc values('002','c07',74)
insert into sc values('003','c01',78)
insert into sc values('003','c04',45)
insert into sc values('003','c05',77)
insert into sc values('003','c06',23)
insert into sc values('003','c07',80)
insert into sc values('004','c02',96)
insert into sc values('004','c03',74)
insert into sc values('004','c05',72)
insert into sc values('004','c07',90)

本学期oralce数据插入:

--Oracle创建表结构
declare
vcnt number;
begin
select count(*) into vcnt from user_tables where table_name='SC';
If vcnt = 1 Then
Execute immediate 'drop table sc';
end if;
select count(*) into vcnt from user_tables where table_name='STUDENT';
If vcnt = 1 Then
Execute immediate 'drop table student';
end if;
select count(*) into vcnt from user_tables where table_name='COURSE';
If vcnt = 1 Then
Execute immediate 'drop table course';
end if;
end;
/

Create table student
(sno char(10) primary key,
sname varchar(20) not null,
sage smallint,
ssex char(2),
sdept varchar(20));

Create table course
(cno char(10) primary key,
cname varchar(20) not null,
credit smallint);

Create table sc
(sno char(10),
cno char(10),
grade smallint,
primary key(sno,cno));

--向表中插入数据
insert into student values('001','葛灵','19','f','MA');
insert into student values('002','岳林月','25','f','MA');
insert into student values('003','姬胜俊','16','f','CS');
insert into student values('004','马源','20','f','MA');
insert into student values('005','翁印','23','m','C');
insert into student values('006','穆祖','17','f','E');
insert into student values('007','卫刚','19','f','MA');
insert into student values('008','于生','21','f','CS');

insert into course values('C01','C语言','4');
insert into course values('C02','数学','4');
insert into course values('C03','英语','2');
insert into course values('C04','计算机','5');
insert into course values('C05','数字电路','3');
insert into course values('C06','数据库','2');
insert into course values('C07','操作系统','2');
insert into course values('C08','信息安全','3');
insert into course values('C09','数据结构','4');
insert into course values('C10','可视化编程','4');
insert into course values('C11','VB程序设计','5');
insert into course values('C12','软件工程','2');

--001号学生选修了8门课程
insert into sc values('001','C05','75');
insert into sc values('001','C01','68');
insert into sc values('001','C03','86');
insert into sc values('001','C08','80');
insert into sc values('001','C12','88');
insert into sc values('001','C02','40');
insert into sc values('001','C10','51');
insert into sc values('001','C04','97');
--002号学生选修了4门课程
insert into sc values('002','C06','60');
insert into sc values('002','C11','67');
insert into sc values('002','C09','47');
insert into sc values('002','C07','98');
--003号学生选修了6门课程
insert into sc values('003','C05','82');
insert into sc values('003','C10','54');
insert into sc values('003','C09','84');
insert into sc values('003','C11','71');
insert into sc values('003','C03','54');
insert into sc values('003','C07','76');
--004号学生选修了6门课程
insert into sc values('004','C08','87');
insert into sc values('004','C06','69');
insert into sc values('004','C12','40');
insert into sc values('004','C09','58');
insert into sc values('004','C01','40');
insert into sc values('004','C10','83');
--005号学生选修了5门课程
insert into sc values('005','C08','51');
insert into sc values('005','C03','41');
insert into sc values('005','C05','62');
insert into sc values('005','C09','70');
insert into sc values('005','C01','98');

alter table course add snumber smallint;
update course set snumber=dbms_random.value(4,10);

commit;

二.sql service ppt上的一些语句与例子

exec sp_tables

select * from sc;
select grade as '学分' from sc;
select distinct grade from sc;
select grade from sc where grade like '9%';
--escape
select * from sc as s;
select * from sc order by cno desc;
select * from sc order by cno asc;
select * from sc where cno between 1 and 10;
select * from sc where cno in ('1','2');

--检索张三同学所学课程的课程号及成绩
select cno,grade from sc,student where student.sno=sc.sno and sname='张三';
--检索所有学生的姓名、选课名称和成绩
select sname,cname,grade from sc,student,course where student.sno=sc.sno and sc.cno=course.cno;
--查询选修‘c05’课程,并且年龄不大于26岁的学生的学号和成绩,并按成绩降序排列。
select student.sno,grade from student,sc where cno='c05' and sage<=26 and student.sno=sc.sno order by grade desc;
--all
--检索选修课程C02的学生中成绩最高的学生的学号
select sno from sc,student where cno='c02' and grade >=all(select sno from sc,student where cno='c02');
--some
--查询比任意一个女同学年龄大的男同学
select sname from student where sage > some(select sage from student where ssex='f') and ssex='m';
--检索选修C01课程号的学生姓名
--SELECT Sname  FROM Student WHERE Sno=Some(SELECT Sno  FROM SCWHERE  Cno='C01');
--select sname from student where cno='c01';




select * from student;
select sage from student where sdept='CS' group by sage having COUNT(sno) >=1;

--group by
select sage from student where sdept='CS' group by sage;
--having
select sage from student where sdept='CS' group by sage having COUNT(sno) >=1;
--列出每位同学的平均成绩和选课门数
select sno,avg(grade) savg ,count(*) scnt from sc group by sno;
--列出CS系每位同学的平均成绩和选课门数。
select sno,avg(grade) savg ,count(*) scnt from sc,student where sc.sno=student.sno and sdept='CS' group by sno;
--列出CS系女同学考试合格的课程数
select sc.cno,count(*) scnt from sc,student where sc.sno=student.sno and sdept='CS' and grade>'60' and ssex='f' group by sno;
--列出CS系获得8分以上学分的女同学,列出学号和所得总学分。
select sc.sno,sname scnt from sc,student where sc.sno=student.sno and sdept='CS' and grade>'60' and ssex='f' group by sno having sum(credit)>8;
--找出具有最高平均成绩的学号及平均成绩**
select sno,avg(grade) from sc having avg(grade) >=all(select avg(grade) from sc group by sno)
--列出选修了C01课程的学生的学号、姓名
select sname,sno from student where exists(select  * from sc where sc.sno=student.sno and cno='c01');
select student.sname,sno from sc,student where sc.sno=student.sno and cno='c01';
--列出得过100分的学生的学号、姓名
select sno,sname from student,sc where sc.sno=student.sno and grade=100;
select sno,sname from student where exists (select * from sc where sc.sno=student.sno and grade=100);
--列出没有选C01课程的学生的学号、姓名
select sno,sname from student,sc where sc.sno=student.sno and cno<>'c01';
select sno,sname from student where not exists(select * from sc where sc.sno=student.sno and cno='c01')


--块插入
--多行插入:插入一个集合例:给CS系的学生开设必修课C05,建立选课信息
insert into sc select sno,'c05',null from student where sdept='cs';
--给CS系的学生开设必修课C05,建立选课信息
insert into sc(sno,cno) select sno,'c05' from student where sdept='cs' and not exists (select * from sc where sno=student.sno and cno='c05');



--group by
select sage from student where sdept='CS' group by sage;
--having
select sage from student where sdept='CS' group by sage having COUNT(sno) >=1;
--列出每位同学的平均成绩和选课门数
select sno,avg(grade) savg ,count(*) scnt from sc group by sno;
--列出CS系每位同学的平均成绩和选课门数。
select sno,avg(grade) savg ,count(*) scnt from sc,student where sc.sno=student.sno and sdept='CS' group by sno;
--列出CS系女同学考试合格的课程数
select sc.cno,count(*) scnt from sc,student where sc.sno=student.sno and sdept='CS' and grade>'60' and ssex='f' group by sno;
--列出CS系获得8分以上学分的女同学,列出学号和所得总学分。
select sc.sno,sname scnt from sc,student where sc.sno=student.sno and sdept='CS' and grade>'60' and ssex='f' group by sno having sum(credit)>8;
--找出具有最高平均成绩的学号及平均成绩**
select sno,avg(grade) from sc having avg(grade) >=all(select avg(grade) from sc group by sno)
--列出选修了C01课程的学生的学号、姓名
select sname,sno from student where exists(select  * from sc where sc.sno=student.sno and cno='c01');
select student.sname,sno from sc,student where sc.sno=student.sno and cno='c01';
--列出得过100分的学生的学号、姓名
select sno,sname from student,sc where sc.sno=student.sno and grade=100;
select sno,sname from student where exists (select * from sc where sc.sno=student.sno and grade=100);
--列出没有选C01课程的学生的学号、姓名
select sno,sname from student,sc where sc.sno=student.sno and cno<>'c01';
select sno,sname from student where not exists(select * from sc where sc.sno=student.sno and cno='c01')


--块插入
--多行插入:插入一个集合
例:给CS系的学生开设必修课C05,建立选课信息
insert into sc select sno,'c05',null from student where sdept='cs';
--给CS系的学生开设必修课C05,建立选课信息
insert into sc(sno,cno) select sno,'c05' from student where sdept='cs' and not exists (select * from sc where sno=student.sno and cno='c05');
--将选修C05课程的学生的成绩改为该课的平均成绩
update sc set grade=(select avg(grade) from sc where cno='c05') where cno='c05';
--将CS系的学生成绩加入10分
update sc set grade=grade+10 where sno in (select sno from student where sdept='cs');
--将CS系的学生的数据库的成绩加入10分
update sc set grade=grade+10 where sno in (select sno from student where sdept='cs') 
and cno in (select cno from course where cname ='DB');
--查询选修了全部课程的学生姓名
--select sname from student where not exists (select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno))
select sname from student where sno in (select sno from sc group by sno having count(*)=(select count(*) from course));

--查看表是否存在
IF OBJECT_ID('sc') IS NOT NULL
    DROP TABLE sc

三.sql service课后的一些练习题语句

1.检索所有学生中年龄最大的学生的姓名及年龄
select name,age from student where (select age from student >=all (select age from student));
2.求每一个学生的最高分和最低分
select max(grade),min(grade) from student where (select sname from student);
3.查询CS系所有男同学考C05课程的成绩,列出这些学生的学号,姓名,成绩,并按成绩降序排列
select hao,sname,grade from student where sname=(select sname from sc where sex='男' and sno='cs') and sco='c05' order by grade desc;
4.检索选修了“C语言”课程的学生的姓名(可用子查询—IN或Exists)
select sname from student where sname in (select * from sco where sno='C');

5.检索选修了课程号为C01或C02课程,且成绩高于或等于70分的学生的姓名,课程名和成绩。
select sname,sno,grade from student where (select grade )


现有下面三个关系:

商店 S(Sno,Sname,City)

商品 P(Pno,Pname,Color)

销售 SP(Sno,Pno,Number)


1.查询没有选择红色零件的商店名
select Sname from S where not in (select Sno from SP where Pno=(select Pno from P where Color='red'));
2.
查询只选择了红色零件的商店名
select Sname from S where Color

查询选择了所有红色零件的商店名
3.





--查询jk系的女同学
Select Sno,Sname
from Student
where Sdept='jk'and Ssex='女'

--查询wy系姓韩的同学选修的课程,列出学号、课程号和成绩
Select Student.Sno,Cno,Grade
from Student,Sc
where Sdept='wy'and Sname LIKE '韩%'and Student.Sno=Sc.Sno

--查询选修了数据库课程的学生的学号,成绩,按成绩降序排列
Select Sno,Grade
from Sc,Course
where Cname='数据库' and Course.Cno=Sc.Cno
Order by Grade desc

--找出学分为4分以上的课程的选修情况,列出学号,课程名,成绩
Select Sno,Cname,Grade
from Course,Sc
where Ccredit=4 and Course.Cno=Sc.Cno

--检索数据库的成绩在90分以上的学生的学号和姓名
Select Student.Sno,Sname
from Student,Sc,Course
where Cname='数据库' and Grade>90 and Student.Sno=Sc.Sno and Course.Cno=Sc.Cno


--查询和数据库相同学分的课程。
Select Cno,Cname,Ccredit
from Course
where Ccredit=(Select Ccredit
               from Course
			   where Cname='数据库')
--查询选修了数据库课程的学生的学号。
Select Sno
from Sc
where Cno=(Select Cno
           from Course
		   where Cname='数据库')
--查询成绩最高的学生的姓名。
Select Sname
from Student
where Sno In (Select Sno
              from Sc
		      where Grade>=All (Select Grade
		                      from Sc))
					
--查询jk系成绩最高的学生的学号、姓名以及成绩。
Select Student.Sno,Sname,Grade
from Student,Sc  
where Sdept='jk' And Grade>=All(Select Grade
                                from Sc)And Student.Sno=Sc.Sno
										
 
--查询数据库课程成绩最高的学生的姓名。
Select Sname
From Student
where Sno In(Select Sno
             From Sc,Course
		     where Cname='数据库'And Sc.Cno=Course.Cno And Grade>=All(Select Grade
			                                                          from Sc
																	  where Cno=(Select Cno
																	             from Course
																				 where Cname='数据库')))
--查询每门课程的平均成绩。
Select Cno,Avg(Grade) avg
From Sc
Group by Cno
--查询每门课程不及格的学生人数。
Select Cno,Count(*)
From Sc
where Grade<60
Group by Cno
--查询每个系学生的最高成绩。
Select Sdept,Max(Grade)
From Student,Sc
where Student.Sno=Sc.sno
Group by Sdept
--查询平均分在75以上的课程。
Select Cno
From Sc
Group by Cno
Having Avg(Grade)>75
--查询女同学中成绩最高的学生所在的系。
Select Sc.Sno,Sdept
From Student,Sc
where Ssex='女'and Student.Sno=Sc.Sno and Grade >=All(Select Grade
                                                      From Sc,Student
													  where Ssex='女'and student.Sno=Sc.Sno)
--查询选课人数最多的课程
Select Cno
From Sc
Group by Cno
Having Count(*)>=All(Select Count(*)
                     From Sc
                     Group by Cno)


--1、在Course表中添加“教师”列(20个长度的变长字符串)

ALTER TABLE table_name ADD column_name datatype
alter table course add teacher varchar(20);



--2、为每门课程添加教师信息。
update course set course.teacher='aaa' where sname='c语言';



3、将教师列修改为非空列。
alter table course alter column teacher varchar(20) not null; 



4、查询选修了刘老师的课程的学生
select sname from student where sno in (select sno from sc where cno in (select cno from course where cname='数据库'));



1、检索所有学生中年龄最大的学生的姓名及年龄。

select sname,sage from student where sage>=all(select sage from student);


2、求每一个学生的最高分和最低分。
select MAX(grade),MIN(grade) from sc group by sno


3、查询CS系所有男同学考C05课程的成绩,列出这些学生的学号,姓名,成绩,并按成绩降序排列。

select sc.sno,sname,grade from sc,student where ssex='f' and cno='c05' group by grade desc


4、检索选修了“C语言”课程的学生的姓名(可用子查询—IN或Exists)

select sname from student where sno in (select sno from course where cname='C语言') 




5、检索选修了课程号为C01或C02课程,且成绩高于或等于70分的学生的姓名,课程名和成绩。
select cname,grade from course,sc where grade>70 and cno in (select cno from course where cno='c01' and cno='c02');


*

6、检索所有学生的姓名、所选课程的课程名和成绩以及课程号,并且按成绩的降序和课程号的升序进行排列(使用外连接将没有选课的同学列出来)。





7. 列出没有选课的学生姓名
select sname from student 
	where sno in 
		(select sno from sc 
			where cno not in 
				(select cno from course));



8. 列出平均分最高的学生所在系的所有学生的姓名

select sname from student 
	where sdept=
		(select sdept from student,sc 
			where student.sno =sc.sno  
				group by student.sno,student.sname,sdept
					having AVG(grade) >=all(select AVG(grade)  from sc group by sno) )


9.查询CS系C01课程的成绩比C01课程的平均分高的学生学号

select sc.sno from sc,student 
	where sc.sno=student.sno and 
		(select grade from sc,student where cno='c01' and sc.sno=student.sno and sdept='CS')
			>=(select AVG(grade) from sc where cno='c01')


10.查询既选修了C01又选修了C02的学生

11.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。

12.检索所有CS系学生都选修了的课程(列出课程号)



13.查询年龄高于其所在系的平均年龄的学生姓名

14.查询每位同学的选课中成绩最高的课程对应的学号,姓名,课程名,成绩

15.为MA系学生选修必修课C05

16.将CS系,C01课程学生的成绩加10分

17.将每位同学的最低分加10分(选)

18.将”数据库”的选课记录全部删除

drop database student;







1、查询学号为S3学生所学课程的课程名与任课教师名。
2、查询至少选修LIU老师所教课程中一门课的女生姓名。
3、查询WANG同学不学的课程的课程号。
4、查询至少选修两门课程的学生学号。
5、查询选修课程中包含LIU老师所教全部课程的学生学号。




--根据学号分组,就得到每个学生所有的课程成绩,在某个学生这一组成绩里,如果他所有的课程成绩都大于60分则输出该组学生的学号
Select sno from sc group by sno having(min(grade)>=60)  
--查询既有课程大于90分又有课程不及格的学生的学号
select sno from sc where grade>=90 and sno in (select sno from sc where grade<60);
--查询平均分不及格的课程号和平均成绩
select cno,avg(grade) gr        oup by cno having avg(grade)<60
--查询平均分及格的课程号和课程名
select course.cno,cname from sc,course where course.cno=sc.cno group by course.cno,course.cname having avg(grade)>=60
--找出至少选修了2号学生选修过的全部课程的学生号,
--不存在这样的课程y,学生2选修了y,而学生x没有选。
SELECT DISTINCT Sno
   FROM SC as SCX
   WHERE NOT EXISTS
      (SELECT *
       FROM SC as SCY
       WHERE SCY.Sno =‘2’AND NOT EXISTS
                               (SELECT *
                                  FROM SC SCZ
                          WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))
 
求各门课程去掉一个最高分和最低分后的平均分
select   avg(GRADE)   from   SC     
  where   GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE)   
  and     GRADE   not   in (select   top   1   GRADE   from   SC order   by   GRADE   desc) 


四.oracle的一些语法语句

--grant
create user c##abc identified by abc;
grant create table to c##abc;
grant conn to c##abc;
grant unlimited tablespace to c##abc;
conn c##abc/abc;
create table abc(id number(10),name varchar(20));
insert into abc values(1,'a');


--remoke
remoke create table from c##abc;

--select
select * from abc;
select * from student;
select * from course;
select * from sc;

--copy table
create table abd as select * from student;
select * from abd;
create table abe as select * from student where 1=2;
select * from abe;

--dual
select * from dual;
select sysdate from dual;
select dbms_random.value(1,10) from dual;

--alter
alter table student add phone char(10);
select * from student;
alter table student modify phone char(20);
alter table student drop column phone;


--update
select * from student;
update student set SAGE=12 where SDEPT='MA' and SNO=001; 
update student set SAGE=sage+1;


--别名
select SNO 学号 from student;


--to_char
select sysdate from dual;
select to_char(sysdate,'yyyy') from dual;


--like
select * from student;
select SNO,SNAME from student where SNAME like '马%';
insert into student values('010','马一二',20,'f','CS');
select sno,sname from student where sname like '马_';


--from
select * from student;
select * from course;
select * from sc;
select student.sno as 学号,sname as 姓名,cname as 课程名,grade as 成绩 from student,course,sc where student.sno=sc.sno;


--使用元组
select s.sno as 学号,sname as 姓名,cname as 课程名,grade as 成绩 from student s,course,sc where s.sno=sc.sno;

--order by
select sno,sname,sage from student where sname like '马%' order by sage desc;

--rownum
select * from sc;
select grade from sc where rownum<=5;
select grade from (select rownum rn,grade from sc) where rn=2;
select grade from sc where rownum>=0;











select * from abc;
select * from student;
select * from course;
select * from sc;

--1、查询C01课程成绩不为Null的学生的姓名和成绩
select sname,grade from student,sc where student.sno=sc.sno and CNO is not Null;

--2、查询平均分高于85分的女同学的学号,姓名,平均成绩
select student.sno,sname,avg(grade) from student,sc where student.sno=sc.sno 
group by student.sno,sname having avg(grade)>=70;

--3、查询CS系学生“数据库”课程的最高分,列出姓名和最高分
select student.sno,sname,max(grade) from sc,course,student where sc.CNO=course.CNO 
and student.sno=sc.cno and CNAME='数据库' group by student.sno,sname;

--4、查询总学分在8分以上的学生的平均成绩,列出学号,平均成绩
select student.sno,avg(grade),sum(CREDIT) from student,sc,course where student.sno=sc.sno
and sc.CNO=course.CNO group by student.sno
having sum(CREDIT)>8

--5、查询所有18岁以上学生的选课门数,列出学号,姓名,年龄,选课门数
select student.sno,sage,sname,count(sc.CNO) from student,sc where sage>18 and student.sno=sc.sno
group by student.sno,sage,sname

--6、建立一个用户U1,给用户赋予Connect和DBA权限,并用该用户登录数据库
create user c##abc identified by abc;
grant dba to c##abc;
grant create table to c##abc;
grant connect to c##abc;
conn c##abc/abc;

--7、用System用户登录,收回用户U1的DBA权限
conn system/oracle;
remoke dba from c##abc;

--8、删除所有CS系不及格的选课信息
select * from student;
select * from course;
select * from sc;
select student.sno,sname,CNO,grade from student,sc where student.sno=sc.sno and sdept='CS'and grade<60;
delete from sc where sno in (select sno from student where sdept='CS') and  grade<60;

--9、将平均分不及格的学生成绩修改为空
update sc set grade=Null where sno in (select sno from sc group by sno having avg(grade)<60);




--找出具有最高平均成绩的学号及平均成绩
select sno,avg(grade) from sc group by sno 
having avg(grade)>=all(select avg(grade) from sc group by sno);

--列出选修了C01课程的学生的学号、姓名
select student.sno,sname,CNO from student,sc where student.sno=sc.sno and CNO='C01';
select sno,sname from student where sno in (select sno from sc where CNO='C01');
--select sno,sname from student where exists(select * from sc,student where sc.sno=student.sno and CNO='C01');


--给CS系的学生开设必修课C05,建立选课信息
insert into sc(sno,cno)
select sno,'C05' from student where sdept='CS' 
and not exists (select * from sc,student where sc.sno=student.sno and cno='C05');


--将选修C05课程的学生的成绩改为该课的平均成绩
update sc set grade=(select avg(grade) from sc where CNO='C05') where CNO='C05';



--全连接
--inner join

--右外连接
----将student表中的所有行都返回
select student.sno,sname,cno,grade from sc,student where student.sno=sc.sno(+);
select student.sno,sname,cno,grade from sc right join student on sc.sno=student.sno;


--5种基本的约束:
--主键约束(primary key constraint)
--唯一性约束(unique constraint)
--检查约束(check constraint)
--缺省约束(default constraint)
--外部键约束(foreign key constraint)

--Alter table sc add constraint fk_sc_sno foreign key(sno) references student(sno) deferrable;
--定义该约束为可延迟约束并设置为延迟约束
--immediate deferred



--1、为Student表的Sage(小于30),Ssex(M或F,缺省为M)添加约束。


--2、为SC表的sno(外码),cno(外码),grade(1到100分)添加约束。


--3、为Course表的Cname(唯一),Credit(1到5)添加约束。


--使用Comment语句可以为表,列等添加注释信息




2019.9.28

发表评论

电子邮件地址不会被公开。 必填项已用*标注