数据库学习1：数据库学习入门1-10题（基于MySQL）

最新推荐文章于 2022-02-15 22:18:17 发布

绝对是谨慎提交的昵称

最新推荐文章于 2022-02-15 22:18:17 发布

阅读量491

点赞数

分类专栏：数据库SQL·所践所习文章标签： mysql

本文链接：https://blog.csdn.net/sunsimiaofromsh/article/details/104551088

版权

数据库SQL·所践所习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

工具

本文所使用的工具是MySQL+Navicat for MySQL(for Mac)，下载及安装可以参考网页上的文章。
在这里插入图片描述

MySQL环境配置

1.打开【终端】输入命令行：mysql -u root -p，看到【Enter password】输入：MySQL安装时设置的密码（Mac终端输入密码时，不会显示在页面上）；
在这里插入图片描述 2. 密码输入后出现如下界面，说明已经在mysql内了：
3.博主安装环境没有碰到太大问题，如果您在安装时碰到问题或者是想设置环境变量，请参看这篇帖子，写得非常好：🔗https://www.jianshu.com/p/0e219ea23599

Navicat使用

1.打开左上角【连接】选择：“MySQL”，出现以下界面；在【连接名】输入：“localhost”，在【密码】输入：MySQL安装时设置的密码；点击【保存】；
在这里插入图片描述
2.左边栏为数据库，新建数据库方法1：右键【localhost】->【新建数据库】；新建数据库方法2:【工具】->【命令列界面】->输入：create database +（数据库名）

3.打开数据库，打开选中的数据库->打开【查询】->点击【新建查询】输入：建表语句。

SQL面试必会50题¹

0.新建表：在新建查询中复制粘贴以下内容：

-- 建表
-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

用一张图介绍4张表联结关系²：
在这里插入图片描述

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

原Score表如下：第一列是“学生学号”，第二列是“课程编号”，第三列是“对应分数”。
在这里插入图片描述
若想要查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号，我们最希望先得到一张这样的表格：

id	score01	score02
学号1	课程01分数	课程02分数
…	…	…

根据上表，查询伪代码可以写为：SELECT id FROM Score WHERE s_score01 > s_score01

SELECT a.s_id'学号',a.s_score'课程01分数',b.s_score'课程02分数' FROM 
-- 从a表和b表内联结得到的表中选择a.s_id,a.s_score,b.s_score并命名为'学号'，'课程01分数'，'课程02分数'
(
SELECT s_id,c_id,s_score FROM Score WHERE c_id = '01' -- 从Score表中选出c_id = ‘01’的行，将所有这些行并起来构成a表
) AS a
INNER JOIN -- inner join：内联结
(
SELECT s_id,c_id,s_score FROM Score WHERE c_id = '02'
) AS b ON a.s_id = b.s_id -- on + 内联结条件：“a表和b表学号相同”

得到如下结果，也就是我们理想的表：
在这里插入图片描述
在上面的代码后补充语句：

WHERE a.s_score > b.s_score

在这里插入图片描述
得到本题答案，学号02和04的学生课程01分数比02高。

补充：从Student表中，查询课程01比课程02分数高的学生姓名。

先查看Student表：
在这里插入图片描述
即再内联结Student表，在所有联结起来的表中再选上s_name。

SELECT a.s_id'学号',c.s_name'姓名',a.s_score'课程01分数',b.s_score'课程02分数' FROM

(
SELECT s_id,c_id,s_score FROM Score WHERE c_id = '01'
) AS a
INNER JOIN
(
SELECT s_id,c_id,s_score FROM Score WHERE c_id = '02'
) AS b ON a.s_id = b.s_id
INNER JOIN
Student AS c ON c.s_id = a.s_id -- as 设置表名

WHERE a.s_score > b.s_score

在这里插入图片描述

2.查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

查看Score表。
在这里插入图片描述
若想要查询平均成绩大于60分的学生的学号和平均成绩，我们最希望先得到一张这样的表格：

id	average
学号1	平均分分数
…	…

将Score表根据s_id分类，

SELECT s_id'学号',AVG(s_score)'平均分'
	FROM Score
GROUP BY s_id HAVING AVG(s_score) > 60

GROUP BY中的条件不用WHERE，而用HAVING + 选择条件！

在这里插入图片描述

3.查询所有学生的学号、姓名、选课数、总成绩（不重要）

若想要查询所有学生的学号、姓名、选课数、总成绩，我们最希望先得到一张这样的表格：

学号	姓名	课程编号	这门课的成绩
1	小张	1	60
1	小张	2	50
2	小李	1	75
2	小李	2	80
…	…	…	…

把Student表和Score表联结在一起，我们此处选择左联结left join。

SELECT * 
FROM Student AS a
LEFT JOIN Score AS b ON a.s_id = b.s_id

内联结：例如以s_id内联结，则会选择两表学号的交集进行联结。例如Student表中有01～08号学生，但08号学生没有考试成绩，故Score表中仅有01～07号学生，内联结后取交集便是01～07号学生的个人信息和成绩。

左联结：例如以s_id左联结，则会选择LEFT JOIN语句前的表的学号作为新表的学号。例如Student表中有01～08号学生，Score表中仅有01～07号学生，内联结后便以前表的学号为准，便是01～08号学生的个人信息和成绩（无成绩的学生课程栏和成绩栏以NULL填充）。

右联结：例如以s_id右联结，则会选择RIGHT JOIN语句后的表的学号作为新表的学号。例如Student表中有01～08号学生，Score表中仅有01～07号学生，内联结后便以后表的学号为准，便是01～07号学生的个人信息和成绩；如果Student表中有02～08号学生，Score表中仅有01～07号学生，内联结后还是以后表的学号为准，便是01～07号学生的个人信息和成绩。

全联结：MySQL是不支持full join的，但仍然可以同过 a LEFT JOIN b + UNION + a RIGHT JOIN b 实现。这样无论a，b两表学号是怎么样的，全联结得到的表学号包含了a，b两表中所有的学号。

将左联结后的表中不同学号的人通过group by函数分类，选课数通过count函数计数，总成绩通过sum函数计算。

SELECT a.s_id,a.s_name,COUNT(b.c_id),SUM(b.s_score) 
FROM Student AS a
LEFT JOIN 
Score AS b ON a.s_id = b.s_id
GROUP BY s_id,s_name

经GROUP BY函数分类后，原则上，SELECT函数只能选择GROUP BY函数后跟的列名以及统计项（例如COUNT、SUM函数）。

在这里插入图片描述
补充：将王菊的总成绩从NULL改为一般我们使用的“0”。

SELECT a.s_id,a.s_name,COUNT(b.c_id)'选课数',
SUM(CASE WHEN b.s_score IS NULL THEN 0 ELSE b.s_score END)'总成绩'
FROM Student AS a
LEFT JOIN Score AS b ON a.s_id = b.s_id
GROUP BY s_id,s_name

CASE WHEN结构³如下：
CASE case_value
WHEN when_value THEN
statement_list
WHEN when_value THEN
statement_list
ELSE
statement_list
END CASE;

注意⚠️：需要注意的是，如果两个条件都针对一个字段的话，会显示第一个when的值。
CASE WHEN是一个非常好的结构，面试加分项。

在这里插入图片描述

4.查询姓“猴”的老师的个数（不重要）

SELECT COUNT(t_id)
FROM Teacher
WHERE t_name LIKE '猴%'

LIKE通常与通配符%一起使用，%表示通配pattern中出现的内容，例如此处的通配pattern为“猴”；而不加通配符%的LIKE语法，表示精确匹配，其实际效果等同于“= 运算符”。

在这里插入图片描述
补充：查询姓“张”的老师的个数和姓名含“张”的老师的个数。

SELECT COUNT(t_name)
FROM Teacher
WHERE t_name LIKE '张%' -- 名字开头是“张”

在这里插入图片描述

一般情况下，表格可能有统一姓名多次重复，可以用DISTINCT函数做去重复的工作。

SELECT COUNT(DISTINCT t_name) -- DISTINCT 去重复
FROM Teacher
WHERE t_name LIKE '张%' -- 名字开头是“张”

在这里插入图片描述

SELECT COUNT(t_id)
FROM Teacher
WHERE t_name LIKE '%张%' -- 名字包含“张”

通配符%用法：’%张%'代表查询含“张”的字符串，'张%'代表查询开头字符为“张”的字符串。

在这里插入图片描述

5.查询没学过“张三”老师课的学生的学号、姓名（重点）

最开始的想法💡：先查询学过“张三”老师课的学生的学号，再找“补集”。

法一

步骤：从Teacher表中根据姓名t_name“张三”查询教师编号t_id
$\rightarrow$ 到Course表中根据t_id查询对应的课程编号c_id
$\rightarrow$ 到Score表中根据c_id查询对应的学生学号s_id，“张三”老师课程有分数的被认为选了课
$\rightarrow$ 以上构造出了学过“张三”老师课的学生学号的表，到Student表中查询不在上述学号中的同学学号及姓名

查询学过“张三”老师课的学生的学号：

SELECT s_id FROM Score
WHERE c_id =
	(
	SELECT c_id FROM Course
	WHERE t_id =
		(
		SELECT t_id FROM Teacher
		WHERE t_name = '张三'
		)
	)

此处一定要注意，WHERE c_id = SELECT 后面一定要精确到查询出c_id，不能多查询出其他列，不然无法一一对应。

在这里插入图片描述

查询没学过“张三”老师课的学生的学号、姓名：

SELECT s_id'学号',s_name'姓名' FROM Student
WHERE s_id NOT IN
(	
SELECT s_id FROM Score
WHERE c_id =
	(
	SELECT c_id FROM Course
	WHERE t_id =
		(
		SELECT t_id FROM Teacher
		WHERE t_name = '张三'
		)
	)
)

IN函数后面加取值区间。

在这里插入图片描述

经典错误写法

SELECT * FROM Score
WHERE c_id != '02'

如果直接从Score表中找课程编号不等于“张三”老师课的学生学号，会出现以下问题：
在这里插入图片描述

因为Score表中是把一个学号拆开来分别对应每个课程建立的，所以选择c_id不等于“02”的学号，是把一个学号下的一条信息删除了，依然保留了该学号c_id等于“01”“03”课的成绩，即这个学号还是会被认为没选“张三”老师的课。

法二
考虑构造如下图表：

学号	课程编号	成绩	教师编号	教师姓名

这样就可以一下子查询到学过“张三”老师课的学生学号。

SELECT s_id,s_name FROM Student
WHERE s_id NOT IN
(
SELECT s_id
FROM Score AS s
INNER JOIN Course AS c ON s.c_id = c.c_id
INNER JOIN Teacher AS t ON c.t_id = t.t_id
WHERE t.t_name = '张三'
)

6.查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

此题和5.无独有偶。

7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

经典错误写法

SELECT * FROM Score
WHERE c_id = '01' and c_id = '02'

在Score表中c_id这项既等于‘01’又等于‘02’是不可能的，故取出来的只会是空集。
在这里插入图片描述
正确的想法——我们应该考虑建立以下两张表，再构造内联结取两表信息交集：

学过课程01 的学生学号
1
2
3
4
5
6

学过课程02 的学生学号
1
2
3
4
5
7

SELECT a.s_id FROM
(SELECT s_id FROM Score WHERE c_id = '01') AS a
INNER JOIN
(SELECT s_id FROM Score WHERE c_id = '02') AS b
ON a.s_id = b.s_id

在这里插入图片描述

8.查询课程编号为“02”的总成绩（不重点）

法一

SELECT SUM(s_score)'sum',AVG(s_score)'avg',COUNT(s_score)'count',COUNT(DISTINCT s_id)'count1' 
FROM Score
WHERE c_id = '02'

在这里插入图片描述

法二
先考虑建立每一门课程的总成绩/平均分/选修人数表，再从中选出程编号为“02”的。

SELECT c_id,SUM(s_score)'sum',AVG(s_score)'avg',COUNT(s_score)'count',COUNT(DISTINCT s_id)'count1' 
FROM Score
GROUP BY c_id

在这里插入图片描述

SELECT c_id,SUM(s_score)'sum',AVG(s_score)'avg',COUNT(s_score)'count',COUNT(DISTINCT s_id)'count1' 
FROM Score
GROUP BY c_id HAVING c_id = '02'

9.查询所有课程成绩小于60分的学生的学号、姓名（知乎小番茄的资料里写的不对）

步骤：先得出同学成绩小于60分的课程数 $\rightarrow$ 统计同学总共学了几门课 $\rightarrow$ 两数相同，则满足查询条件

SELECT a.s_id,c.s_name
FROM
(
SELECT s_id,COUNT(c_id) AS cnt FROM Score -- AS cnt改成'cnt'也可以
WHERE s_score < 60
GROUP BY s_id
) AS a
INNER JOIN
(
SELECT s_id,COUNT(c_id) AS cnt FROM Score
GROUP BY s_id
) AS b 
ON a.s_id = b.s_id
INNER JOIN Student AS c 
ON a.s_id = c.s_id 

WHERE a.cnt = b.cnt

在这里插入图片描述

10.查询没有学全所有课的学生的学号、姓名(重点)

易错点：要小心一门课都没选的同学！！故要使用LEFT JOIN把学号多的那张表放在前面！！

步骤：先找出总共课程数目
$\rightarrow$ 将Student表和Score表进行左联结，得到一张具有完整的学生信息和成绩的表
$\rightarrow$ 在该表中对学号分类，分类后以选课数小于总课数作为条件进行查询

先找出总共课程数目:

SELECT COUNT(DISTINCT c_id) FROM Course

Student表和Score表进行左联结，得到一张具有完整的学生信息和成绩的表:

SELECT * FROM 
Student AS a
LEFT JOIN 
Score AS b ON a.s_id = b.s_id

得到最终结果：

SELECT a.s_id,a.s_name
FROM 
Student AS a
LEFT JOIN 
Score AS b ON a.s_id = b.s_id
GROUP BY s_id 
HAVING COUNT(DISTINCT c_id) < 
	   (SELECT COUNT(DISTINCT c_id) FROM Course)