数据库基本操作总结（上）【数据定义、数据操纵、数据查询】

文章目录

零、前言一、数据定义（create，alter，drop）二、数据操纵（insert，update，delete）三、数据查询（select）

零、前言

本文所有操作均在Oracle数据库下运行，在某些地方的语法可能与MySQL不同。 另外，是否区分大小写也有不同。（参看此文：MySQL与Oracle的大小写问题）

本文下篇：数据库基本操作总结（下）【数据控制、安全性和完整性】

SQL语言集数据定义、数据操纵、数据查询、数据控制功能于一体。 数据定义：create，drop，alter 数据操纵：增(insert into … )，删(delete from …)，改(update … set …) 数据查询：select 数据控制：grant(授予权限)，revoke(收回权限)

一、数据定义（create，alter，drop）

1.建立基本表 创建教材中的学生表（Student）、学生选课表（SC）、课程表（Course） （1）学生表：Student (Sno，Sname，Sage，Ssex，Sdept），其中学号Sno为主码。

create table Student ( Sno number primary key, Sname varchar2(10), Sage int, Ssex char(2), Sdept varchar2(10) );

（2）课程表：Course (Cno, Cname, Cpno, Ccredit)其中课程号Cno主码；先行课为外码参照Course表中Cno字段。

create table Course ( Cno number primary key, Cname varchar2(10), Cpno varchar2(10) references Course(Cno), Ccredit int );

（3）学生选课表：SC(Sno, Cno, Grade)其中学号Sno、课程号Cno为主码；Sno为外码参照Student表中Sno字段；Cno为外码参照Course表中Cno字段。

create table SC ( Sno number references Student(Sno), Cno number references Course(Cno), Grade int, primary key (Sno,Cno) );

2.修改基本表 （1）在Student表中加入属性BloodType【char(2)型】。

alter table Student add BloodType char(2);

（2）修改表Student中的Sdept属性的数据类型为varchar2(40)，注意和定义表的时候类型不同。

alter table Student modify Sdept varchar2(40);

（3）给表Student的sage列添加一个自定义约束，sage必须大于15且小于30。

alter table Student add constraint age_ck check(Sage>15 and Sage<30);

（4）删除（3）中新添加的约束。

alter table Student drop constraint age_ck;

（5）SC表中的sno增加外键约束f_sno，参照Student表中的sno字段。

alter table sc add constraint f_sno foreign key (sno) references student(sno);

（6）删除表Student中的字段BloodType。

alter table Student drop (BloodType); #记得加括号，不加括号就要在drop前面写column

3.删除基本表 （1）删除基本表Student。

drop table Student;

（2）删除基本表SC。

drop table SC;

4.索引操作 （1）在SC表上建立关于Sno升序、Cno降序的唯一索引i_sc+学号后四位。

create unique index i_sc on SC(Sno asc,Cno desc,substr(Sno,-4));

（2）删除Course表上的索引i_sc。

drop index i_sc;

二、数据操纵（insert，update，delete）

1.插入数据 1）向Student表中插入数据 2）向Course表中插入数据 3）向SC表中插入数据 可参考如下数据，也可不参考。

insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215121,'李勇','男',20,'CS'); insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215122,'刘晨','女',19,'CS'); insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215123,'王敏','女',18,'MA'); insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215125,'张立','男',19,'IS'); # 记得先把COURSE表中的外键约束关了，否则数据插不进去 insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(1,'数据库',5,4); insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(2,'数学',null,2); insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(3,'信息系统',1,4); insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(4,'操作系统',6,3); insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(5,'数据结构',7,4); insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(6,'数据处理',null,2); insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(7,'PASCAL语言',6,4); insert into SC(SNO,CNO,GRADE) values(200215121,1,92); insert into SC(SNO,CNO,GRADE) values(200215121,2,85); insert into SC(SNO,CNO,GRADE) values(200215121,3,88); insert into SC(SNO,CNO,GRADE) values(200215122,4,90); insert into SC(SNO,CNO,GRADE) values(200215122,3,80);

2.修改数据 1）将王敏的同学的年龄改为20。

update STUDENT set sage=20 where sname='王敏';

2）将全部同学的年龄加1。

update STUDENT set sage=sage+1;

3）将’CS’系同学的选课信息中的成绩置0。

update SC set grade=0 where sno in ( select sc.sno from STUDENT join SC on(student.sno=sc.sno) where sdept='CS' );

3.删除数据 1）删除和’刘晨’在同一个系的学生的信息。（包括刘晨自己也会被删除）

delete from STUDENT where sdept in ( select sdept from STUDENT where sname='刘晨' );

然后发现删除不了，因为有外键约束，SC表依赖当前表的SNO， 所以要先删掉SC表中SNO的外键约束alter table sc drop constraint SYS_C0051208; 再执行删除语句即可。

2）删除’CS’系同学的选课信息。

delete from SC where sno in ( select sno from STUDENT where sdept='CS' );

三、数据查询（select）

1.包括排序、分组的单表查询 （1）求数学系学生的学号和姓名。

select sno,sname from student where sdept='MA';

（2）求选修了课程的学生学号。

# 学号distinct去重，因为SC表中学号可以重复，一个学号可选多门课 select distinct sno from sc;

（3）求选修课程号为‘2’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

select sno,grade from sc where cno=2 order by grade DESC,sno ASC;

（4）求选修课程号为’2’且成绩在80～90之间的学生学号和成绩，并将成绩乘以0.8输出。

select sno,grade*0.8 from sc where cno=2 and grade>=80 and grade<=90;

（5）求数学系或计算机系姓张的学生的信息。

# 注意and的优先级高于or，加一个括号即可 select * from student where sname like '张%' and (sdept='MA' or sdept='CS');

（6）求缺少了成绩的学生的学号和课程号。

select sno,cno from sc where grade is null;

（7）查询各个课程号与相应的选课人数。

select cno,count(*) from sc group by cno;

2.多表连接查询 （1）查询每个学生的情况以及他所选修的课程。

select student.sno,sname,ssex,sdept,cno from student,sc where student.sno=sc.sno;

（2）求学生的学号、姓名、选修的课程及成绩。

select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno;

（3）求选修课程号为‘1’且成绩在90分以上的学生学号、姓名和成绩。

select student.sno,sname,grade from student,sc where student.sno=sc.sno and cno=1 and grade>=90;

（4）查询每一门课程的间接先行课。

select c1.cno,c2.cpno from course c1,course c2 where c1.cpno=s2.cno;

（5）查询与’刘晨’在同一个系学习的学生。

select s1.sno,s1.sname,s1.sage,s1.ssex,s1.sdept from student s1,student s2 where s1.sdept=s2.sdept and s2.sname='刘晨'; #两个相同的表通过sdept连接

（6）查询选修了课程名为‘信息系统‘的学生学号和姓名。

select sc.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname='信息系统';

（7）查询平均成绩在80分以上的学生学号和平均成绩。

select sno,avg(grade) from sc group by sno having avg(grade)>=80;

（8）查询选修了1门以上课程的学生的学号。

select sno from sc group by sno having count(*)>1;

3.嵌套查询 （1）求选修了信息系统的学号和姓名。

select sno,sname from student where sno in ( select sno from sc where cno in ( select cno from course where cname='信息系统' ) );

（2）查询与刘晨在同一个系学习的学生。

select * from student where sdept in ( select sdept from student where sname='刘晨' );

（3）求选修1号课程的成绩高于刘晨的成绩（指刘晨选修的所有的课程的成绩）的学生学号及成绩。

select sno,grade from sc where cno=1 and grade > ( select max(grade) from sc where sno in ( select sno from student where sname='刘晨' ) );

（4）求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）。

select * from student where sage < ( select max(sage) from student where sdept='CS' );

（5）求其他系中比计算机系学生年龄都小的学生姓名及年龄。

select sname,sage from student where sage < ( select min(sage) from student where sdept='CS' );

（6）求选修课程超过2门的学生的学号和姓名。

select sno,sname from student where sno in ( select sno from sc group by sno having count(*)>2 );

（7）求没有选修3号课程的学生姓名。

select sname from student where not exists ( select * from sc where cno=3 and student.sno=sc.sno );

（8）查询选修了全部课程的学生姓名。【重点！】

select sname from student where not exists ( select * from course where not exists ( select * from sc where sc.cno=course.cno and sc.sno=student.sno ) );

分析： 这个比较难懂，我们先看看内两层查询，表示选出当前学号（比如说122）没选的课

select * from course where not exists ( select * from sc where sc.cno=course.cno and sc.sno=200215122 );

如果这个结果是空，表示当前学号（比如说122）没选的课为空，最外层加一个select sname from student where not exists，表示选出所有 没有没选的课 的学生。

可以将not exists当作“减法”理解，内两层的意思就是把所有的课减去122学号选的课，如果为空（所有的课被122选的课给减没了），那么就选出122来。

（9）求至少选修了学号为“200215121”的学生所选修全部课程的学生学号和姓名。【重点！】 分析： 先选出121学生选的课 减去 122学生选的课：

select * from sc sc1 where sno=200215121 and not exists ( select * from sc sc2 where sc1.cno=sc2.cno and sc2.sno=200215122 );

单纯的只有where not exist表示选出空的。 以上的整个式子表示减法：最内层用cno连接，表示找选课记录，然后121选课集合减去122选课集合。

过程的话实际就是两层for循环遍历，for(i: 121选课){for (j: 122选课)}，对于每个i，判断122选课中是否存在j满足i.cno=j.cno，如果有，不为空，那么not exists就不选出这个i，实际上这不就相当于当前遍历到的i的记录被相同的j减去了吗！而且外层循环是121选课，说明最终输出的答案只能是121选课的子集，只要答案为空我们就把他选出来！

如果为空，说明122这个学号把121的所有课都选了（所以121的课被减没了）。 还有一种情况，就是122这个学号不仅把121的所有课都选了，他还选了其他课，这个时候的减法结果不是按“负数”理解，就理解成122把121的所有课都减没了，他还剩了多余的课，那是122的选课集合剩了(不是121剩了)，最主要的是121被减没了，所以121的选课集合答案是空。

绕了这么多，反正核心就是： 找一个学生的选课集合作为“减数”，如果他大于等于121的选课集合，那么121的选课集合减去他之后就能变成空，这个“减数”就是我们要找的。

最后，把学号122改成变量，加一个最外层查询遍历所有学生， 因为找到为空的就要把他选出来，所以最外层写not exists选出空的，那么整个答案就是：

select sno,sname from student where not exists ( select * from sc sc1 where sno=200215121 and not exists ( select * from sc sc2 where sc1.cno=sc2.cno and sc2.sno=student.sno ) );