初写MySQL四张表:(4/4)

进度条很喜人，你是否已经修炼到这一步了呢？

初写MySQL四张表:(1/4)-CSDN博客

初写MySQL四张表:(2/4)_数据库表样例-CSDN博客

初写MySQL四张表:(3/4)-CSDN博客

若现在你已经有了前面的基础，那就正式开始吧。

四张表：

这次在实现需求之前，我做了一些准备工作（简单有效地删除添加数据操作，同时解决一个删除常见问题），也为了咱们做需求的时候，有数据可操作。但是文章以需求为先，具体准备工作操作会在文末介绍。

现在咱们四张表的数据如下：——如果你是喜欢先做完需求再看文章的同学，可以先对照自己的表，进行添加。

学院表：(testdb.dept)

课程表：(testdb.course)

选课表:（testdb.sc）

学生表 :（testdb.stu）

需求一：

查询课程名称中包含“数据库”的课程信息，显示“课程编号”、“课程名称”和“学分”。

“查询”二字，一眼指定查询，我们会使用SELECT ... FROM ... 显示的内容，就是我们通过条件筛选出来剩下的内容——所以，我们要求显示“课程编号”，“课程名称”和“学分”，这三个字段都来自哪张表：课程表(testdb.course)

按照语法也有逻辑地说：SELECT 后接字段名 FROM 后接表名

#这种筛选出来的字段记录只在一张表，字段前不用加前缀
SELECT course.cid,course.cname,course.credit
FROM testdb.course

原话：指定查询，使用SELECT ... FROM ... 显示的字段内容，就是我们通过条件筛选出来剩下的内容。通过条件，我们使用WHERE语句：

按照需求意思，条件是“课程名称中包含“数据库””，就是凭借这一条件，我们会淘汰掉课程名称不包含“数据库”的记录，进而也不会显示它们的cid,cname,credit。

那怎么表达课程名称中包含“数据库”这个意思，这就会引出——模糊查询

模糊查询：

模糊查询，也称部分匹配查询。查询时，如果不知道完全精确的值，可以使用LIKE或者NOT LIKE进行匹配查询。比如咱们这儿，课程名称包含“数据库”，因为咱们不确定它到底是“数据库原理”还是叫“MySQL数据库原理”......只是确定它肯定有“数据库”这三个字。

LIKE,"像"，NOT LIKE"不像"。

具体格式： WHERE 字段名 LIKE 字符串常量

ouwu，华生你发现了，模糊查询是针对字段类型为字符型(字符串)的。

那么，这里应该是这样写的吧。

WHERE cname LIKE '数据库'

是，又不是。要完全做到模糊查询，还需要通配符。

%	代表0个或多个字符
_(下划线)	代表一个字符
[ ]	表示在某一范围的字符
[^ ]	表示不在某一范围的字符

你发现谁最好用，或者在这里谁最恰当？

应该是%，可有可无都能匹配。我们不确定“数据库” 三个字前后是否有字，若有但是又有多少个。

所以这里需求一的完整代码：

SELECT cid,cname,credit
FROM testdb.course
WHERE cname LIKE '%数据库%';

运行结果：

需求二：

查询课程名称为“数据库原理及应用”的选课信息，显示选课学生的“学号”和“姓名”。（要求使用子查询）

我们先不看子查询这个概念，就按照咱们自己的分析来：也是指定查询，显示的字段记录"学号"来自选课表（sc）或者学生表 (stu)；“姓名”只来自学生表(stu)。从字段来源发现就怀疑要联合两张表了，因着前面有“选课学生”四个字，我们姑且就把“学号”这个字段归给sc表吧。接下来我们着重关心，筛选条件是什么：“课程名称为“数据库原理及应用””的选课信息。

核心还是选课，那核心表就是sc。但是我怎么记得，sc表里面没有字段叫“课程名称”只有“课程id”；但是课程表(course)既有“课程名称”和“课程编号”这两个字段。

以前我们会使用JOIN ... ON ... 是因为显示的字段都来自不同的表。现在显示的也是来自不同的表，但是连查询条件也是来自另外一个表，我们会怎么处理呢？

子查询：

子查询，其实就是先执行一次查询，返回的结果作为父查询的查询条件。

“普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较确定查询结果集合”。抽象的概念需要实践的验证，才能说明它真的是在提取本质，囊括普遍，不然就是在纸上谈兵。—(金句来自码文😎)

按照题意，我们先来写框架：

查询课程名称为“数据库原理及应用”的选课信息，显示选课学生的“学号”和“姓名”。（要求使用子查询）

SELECT sc.stuid,stuname
FROM testdb.sc
#从选课表来，1.选课表是核心表 2.学号字段来自选课表
JOIN testdb.stu
#联合查询：JOIN ... ON ... 使用联合查询，一般是显示的字段来自另一张表：姓名来自stu表
ON

联合查询，需要两张表建立联系，

建立联系，一般靠的是相同的字段。它俩相同的字段可以看到是stuid。

那么联合条件：

SELECT sc.stuid,stuname
FROM testdb.sc
JOIN testdb.stu
ON  stu.stuid = sc.stuid
#其实按理说，sc.stuid = stu.stuid这个先后顺序并不强求。但是我之所以选择stu放在前面，是因为我把sc表当做已知，但未处理的表，我们是通过选课表的学号去对应学生表的学号，再去对应名字
# 编程语言里面，= 往往表示赋值，其实这里也可以这样理解：由已知的sc.stuid -> stu.stuid -> stu.stuname

其实，这个先后顺序，只是我自己的理解——get到我意思的小伙伴，那咱们可以先握个手哈哈，一家之言罢了，听听就好。（我其实觉得还蛮有用的）

若单纯连接stu表，它最后会显示选课表上出现过的stuid及其对应学生表上的stuname,它不能只保证这些学生选的都是“数据库原理及应用”，所以得加个“并且”后面再加个条件。

接下来，子查询的代码，我会慢慢写，各位瞧好了：

ON  stu.stuid = sc.stuid AND

查询课程名称为“数据库原理及应用”的选课信息，显示选课学生的“学号”和“姓名”。（要求使用子查询）

我们的核心还是选课表，现在一个“课程名称‘’把课程表拉上了。

我们可以利用sc.cid是"数据库原理及应用"的course.cid去淘汰一些sc.stuid。用代码表达：“sc.cid是"数据库原理及应用"的course.cid”

sc.cid = (
         SELECT course.cid
         FROM testdb.course
         WHERE 
)
#（）最后会返回course.cid
# sc.cid被右边cid赋值限制,course.cid 通过 课程名称为“数据库原理及应用” 来淘汰不满足的course.cid

所以，WHERE接：

sc.cid = (
         SELECT course.cid
         FROM testdb.course
         WHERE  cname = '数据库原理及应用'
)

再看一眼需求：查询课程名称为“数据库原理及应用”的选课信息，显示选课学生的“学号”和“姓名”。（要求使用子查询）

SELECT sc.stuid,stuname
FROM testdb.sc
JOIN testdb.stu
ON  stu.stuid = sc.stuid AND

sc.cid = (
         SELECT course.cid
         FROM testdb.course
         WHERE  cname = '数据库原理及应用'
)
# cname->course.cid->sc.cid->sc.stuid
#解释：一张表里的字段可以淘汰掉一些记录，(cname限制course.cid,sc.cid限制sc.stuid);不同表，但是相同名称字段又可以淘汰一些记录(course.cid和sc.cid, course.cid经过一番淘汰后，赋值给sc.cid,sc.cid会照着course.cid的名单进行下一轮同表淘汰)

同表的筛选，我们往往采用SELECT ... FROM ... ，不同表，相同字段我们会使用 = (赋值)——出现这个差别，是因为字段。相同字段在不同表都是相通的，直接赋值就成。不同字段，则需要SELECT ... FROM... WHERE ...

sc.cid = (
         SELECT course.cid
         FROM testdb.course
         WHERE  cname = '数据库原理及应用'
)

sc.cid = course.cid 不同表，相同字段，直接赋值就可。但从cname -> course.cid 则需要WHERE同表指定查询。

明白了这个，这个需求的完整代码也就出来了。

SELECT sc.stuid,stuname
FROM testdb.sc
JOIN testdb.stu
ON  stu.stuid = sc.stuid AND sc.cid = (
         SELECT course.cid
         FROM testdb.course
         WHERE  cname = '数据库原理及应用'
);
#stu.stuid = sc.stuid在被直接赋值之前，若没有被封装，还需要先连表JOIN...ON...
#所以不同表的相同字段进行限制，（连表） + 赋值

运行一下：

验证一下：(你发现，选课表里面明明还有个2022122002选了CS004F这门课，为什么没有显示这个人)，是因为从选课表传给学生表的学号，在学生表中并不存在，自然姓名也没有，说明传过来的还有无效(错误)数据，这个学生并不存在。赋值一次肯定会淘汰很多记录，但也不会因此保留错误数据，所以，也会淘汰自己认为无效的数据。

准备工作介绍：

这次在实现需求之前，我做了一些准备工作：删除了原先的数据，再统一添加数据，既为了自然插入删除操作的学习，趁机复习添加数据语句。

先来看怎么删除数据：

删除数据：

格式：DELETE FROM 表名;

我还记得我们的表名，需要指定数据库。这里咱们的四张表的则表名分别是：testdb.sc,testdb.course,testdb.stu,testdb.dept。

#全部删除：
DELETE FROM testdb.sc;

你可以发现，DELETE FROM 直接加表名，猜测应该是删除这张表的所有数据。事实上也确实如此。

在前面的需求中，我们直到若要实现"指定"，需要我们用到WHERE 进行指定。同样，若我们要实现指定删除，我们需要搭配使用WHERE关键字。

指定删除：

DELETE FROM 表名 WHERE 指定表达式

举个例子：我们想要删除课程表里面，课程名称为“数据库”这门课。

看这个需求，我们要删除的这门课的信息，存在课程表里面，所以初步我们需要从testdb.course里面删除。

DELETE FROM testdb.course

我们只删除...,这里是只删除课程名称为"数据库"这门课，(利用已知字段信息进行指定)，所以我们要使用WHERE关键字。

WHERE cname = '数据库';

所以合起来就是：

DELETE FROM testdb.course
WHERE cname = '数据库';

在执行这条语句之前，我们先来看看现在的表的原样：

执行之后：

哦豁，看来出师不利啊？我们来仔细看看到底是什么问题：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

咱们大致翻译一下：您正在使用安全更新模式，安全更新模式要求在使用 UPDATE 或 DELETE 语句时，必须有一个 WHERE 子句，且该子句最好基于主键或唯一索引列。

说白了，安全模式就是不相信咱们删得好，删得对。我们来看，我们删除语句中，满足有WHERE

子句，但是我们删除记录，是基于主键或者唯一索引列（UNIQUE约束）。

我们基于cname进行删除记录，但cname既不是主键也不是唯一索引列。

通过DESC + 表名，我们可以查看表的字段结构，可以看到cname 没有主键约束或者UNIQUE约束。

解决方案，我们有两种：现成把cname字段添加主键约束或者UNIQUE约束(唯一约束)；其二，索性关闭这个安全更新模式。

我们这里介绍怎么关闭安全更新模式。

#关闭安全更新模式
SET SQL_SAFE_UPDATES = 0;

咱们简单介绍一下这句话：SET 设置，SQL_SAFE_UPDATES ，SQL不用说，专有名词限定范围，SAFE_UPDATES安全更新模式,置零：就是关闭。

翻译过来：设置SQL安全更新模式为关闭状态。

我们把这句话执行一下：

再执行删除语句：

DELETE FROM testdb.course
WHERE cname = '数据库';

课程名为"数据库" 这条记录没了，当然，我还要添加回来——我只是举个例子而已🤠

准备工作—真正需要删除的：

#删除学生表数据
DELETE FROM testdb.stu;
#删除选课表
DELETE FROM testdb.sc;
#删除课程表
DELETE FROM testdb.course;
#我没有删学院表数据，因为从头学院表不需要添加新数据

执行完这三句话后，咱们的三张表都是空表。

stu表：

sc表：

course表：

准备工作——添加数据：

还记得添加数据的语句吗？ INSERT INTO 表名(指定字段名) VALUES(对应字段的值);

#stu表
INSERT INTO testdb.stu(stuid,stuname,sex,birthday,deptid)
VALUES('2022121001','张三','男','2006-05-01','1');

INSERT INTO testdb.stu(stuid,stuname,deptid)
VALUES('2022121002','王五','1');

INSERT INTO testdb.stu(stuid,stuname,deptid)
VALUES('2022121004','李四','1');

#sc表
INSERT INTO testdb.sc(stuid,cid,score)
VALUES(2022121004,'CS004F',98);

INSERT INTO testdb.sc(stuid,cid,score)
VALUES(2022121004,'1001',90);

INSERT INTO testdb.sc(stuid,cid,score)
VALUES(2022121001,'1001',78);

INSERT INTO testdb.sc(stuid,cid,score)
VALUES(2022121002,'1001',65);

INSERT INTO testdb.sc(stuid,cid,score)
VALUES(2022122002,'CS004F',90);
#字符串字段加入一定要加单引号

#course表
INSERT INTO testdb.course(cid,cname,credit,deptid)
VALUES('1001','计算机网络',1,'1');

INSERT INTO testdb.course(cid,cname,credit,deptid)
VALUES('1002','数据结构',2,'1');

INSERT INTO testdb.course(cid,cname,credit,deptid)
VALUES('CS003A','数据库',3,'1');

INSERT INTO testdb.course(cid,cname,credit,deptid)
VALUES('CS004F','数据库原理及应用',2,'1');

INSERT INTO testdb.course(cid,cname,credit,deptid)
VALUES('CS103B','C++',2,'1');

INSERT INTO testdb.course(cid,cname,credit,deptid)
VALUES('CS1017','JAVA',2,'1');

执行完毕，三张表“千呼万唤始出来”

stu表：