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

发布时间：2022-10-27 11:10:34 所属栏目：MySql教程来源：

导读：　　文章目录

　　零、前言

　　本文所有操作均在Oracle数据库下运行，在某些地方的语法可能与MySQL不同。

　　另外，是否区分大小写也有不同。（参看此文：MySQL与Oracle的大小写问题）

　　本文下

　　文章目录

　　零、前言

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

（编辑：我爱制作网_池州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MySQL8.0新特征支持原	如何处理Mongodb中关于
mysql8 更改my.ini配置	mysql运用命令行模式备