@[TOC]

零、前言

本文所有操作均在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
    )
);