【MySQL数据库】项目中用到的一些SQL查询总结

前言

下面的内容是做毕业设计时，涉及到的一些数据库查询语句。很多语句都是临时学习，为了便于后期整理以及复用，在此做个总结。

1. 有A，B两张表，需要统计A表中某个属性值的总数，更新到B表中

详细需求：

现在数据库中有两张表，分别是：paper_manage，exam_manage

paper_manage结构如下，该表存储的就是对应考试试卷中的所有试题编号。

exam_manage结构如下，该表存储的就是考试的一些安排信息

现在需要更新exam_manage表中，total_num属性的值，也就是统计这个试卷中总题目数量。

实现代码

UPDATE exam_manage set total_num=
	(
	select count(exam_code  OR NULL) from paper_manage
WHERE 
	paper_manage.exam_code=exam_manage.examCode
	);

2. 将三张（或n张）表中的结果合并一起

现在有三张表multi_qustion，judge_qustion，fill_qustion，分别存储 选择题、判断题、填空题的试题信息。也即将这三种题型查询的结果在前端全部展示给用户。如下图所示。

实现代码

select questionId,question,questionType,subject, score, section,level  from multi_question  
	WHERE subject_id=10001
	union 
	select questionId,question,questionType, subject, score, section,level  from judge_question 
	WHERE subject_id=10001
	union
	select  questionId,question,questionType,subject, score, section,level from fill_question 
	WHERE subject_id=10001

其中的subject_id是试题所属的科目id.

注意：选择题中比填空题和判断题多了四个属性，分别是 A、B、C、D四个选项，若使用UNION合并结果为一张表，union中的每个查询必须包含相同的列。

3. 删除表以及下属子表的所有信息

本次数据库中只是简单存储了三张表：

• curriculum，存储的是不同题库信息。结构如下:

• multi_question 选择题数据库,结构如下:
• fill_question ，填空题数据库，结构如下：
  
• judge_question ， 判断题数据库，结构如下：
• 
  现在如果要删除题库表中的某个题库信息，同时要把该题库所属的所有试题全部删除。（三张表中没有设置外键，同时设置冗余属性,各种试题题型中的subject_id对应题库表中的curriculum_id）
  我们先实现，如何查询到对应题库下的所有试题信息

--  查询题库。以及题库下的所有试题信息
SELECT * from curriculum c
left join fill_question as f on c.curriculum_id=f.subject_id
left join multi_question as m on c.curriculum_id=m.subject_id
left join judge_question as j on c.curriculum_id=j.subject_id
where curriculum_id=10001 # 题库id

修改一下就成了删除题库以及所属试题

实现代码

--  删除题库。以及题库下的所有信息
DELETE  fill_question,multi_question,judge_question,curriculum 
from curriculum  
left join fill_question on curriculum.curriculum_id=fill_question.subject_id
left join multi_question on curriculum.curriculum_id = multi_question.subject_id
left join judge_question  on curriculum.curriculum_id=judge_question.subject_id
where curriculum_id=10004;		#要删除的题库id

4. 统计同一张表中，不同属性的数量于一个结果集中

实际需求为：统计一张试卷中，选择题、判断题、填空题各自的数量。

先给出三条SQL语句分别查询不同题型个数的SQL语句

-- 同一个表值为n的不同属性的个数 ，填空题
SELECT paperId,COUNT(*) as '填空题个数'  FROM paper_manage 
WHERE paperId =1003 AND questionType=2;
-- 选择题
SELECT paperId,COUNT(*) as '选择题个数'  FROM paper_manage 
WHERE paperId =1001 AND questionType=1;

-- 判断题
SELECT paperId,COUNT(*) as '判断题个数'  FROM paper_manage 
WHERE paperId =1002 AND questionType=3;

结果截图：

如果将三个结果合并为一个结果集中，代码如下:

实现方法1

方法一

-- 方法1
SELECT
paperId as '试卷编号',
sum(if(questionType =1, 1, 0)) AS  '单选题',
sum(if(questionType =2, 1, 0)) AS  '填空题',
sum(if(questionType =3, 1, 0)) AS  '判断题'
FROM paper_manage  WHERE paperId=1001;

实现代码2

方法二

-- 方法2
SELECT
paperId as '试卷编号',
count(questionType =1 OR NULL) AS  '单选题',
count(questionType =2 OR NULL) AS  '填空题',
count(questionType =3 OR NULL) AS  '判断题'
FROM paper_manage WHERE paperId=1001

两种方法都指向了同一个结果：