项目七 完成学生信息的综合查询

于 2024-03-19 22:02:46 发布
阅读量784 收藏 17
点赞数 21
CC 4.0 BY-SA版权
文章标签： 后端 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_57789471/article/details/136856940
该博客围绕学生信息综合查询，介绍了MySQL多表查询的相关知识。包括笛卡尔积运算原理及对数据表的运算，初步多表查询的实现，用join关键字进行多表查询的多种方式，单表自连接查询，多个查询结果的运算，以及使用子查询获取表中信息等内容。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

项目七 完成学生信息的综合查询

1，探究综合查询理论的必备理论知识

笛卡尔积运算原理

  • 笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尓积（Cartesian product），又称直积，表示为 X × Y。

对数据表做笛卡尔积运算

  • 从多个表中查询数据，可以获得更全面的信息。
  • 加上条件的笛卡尔积运算结果才有意义。

2，实现初步的多表查询

完成一个简单的连接查询

select 语句的多表查询有两种形式：简单连接查询和使用 JOIN 关键字的连接查询。

SELECT columnlist
FROM tablename1, tablename2 [,…] 
[WHERE connectioncondition AND | OR searchcondition]

1，columnlist：字段列表。
2，将连接查询的连接条件写在 WHERE 子句中。#格式：表 1.公共字段名 = 表2.公共字段名
3，没有 WHERE 子句的连接查询实现的就是表之间的笛卡尔积运算
4，还需要额外的查询条件，那么在连接条件的前面或后面使用逻辑运算符 AND 或 OR将二者连接起来

#准备两个表，并插入数据
mysql> create table s (ID int primary key auto_increment,StudentName varchar(10) not null,StudentClassID int);
mysql> insert into s values (null,'Careyson',1),(null,'Tony',2),(null,'Jack',3),(null,'Nancy',2),(null,'Peter',1);

mysql> create table c (ClassID int primary key auto_increment,ClassName varchar(30) unique);
mysql> insert into c values (null,'软件一班'), (null,'软件二班'), (null,'软件三班');

#做积运算
mysql> select * from s,c;
+----+-------------+----------------+---------+--------------+
| ID | StudentName | StudentClassID | ClassID | ClassName    |
+----+-------------+----------------+---------+--------------+
|  1 | Careyson    |              1 |       2 | 软件二班     |
|  1 | Careyson    |              1 |       3 | 软件三班     |
|  1 | Careyson    |              1 |       1 | 软件一班     |
|  2 | Tony        |              2 |       2 | 软件二班     |
|  2 | Tony        |              2 |       3 | 软件三班     |
|  2 | Tony        |              2 |       1 | 软件一班     |
|  3 | Jack        |              3 |       2 | 软件二班     |
|  3 | Jack        |              3 |       3 | 软件三班     |
|  3 | Jack        |              3 |       1 | 软件一班     |
|  4 | Nancy       |              2 |       2 | 软件二班     |
|  4 | Nancy       |              2 |       3 | 软件三班     |
|  4 | Nancy       |              2 |       1 | 软件一班     |
|  5 | Peter       |              1 |       2 | 软件二班     |
|  5 | Peter       |              1 |       3 | 软件三班     |
|  5 | Peter       |              1 |       1 | 软件一班     |
+----+-------------+----------------+---------+--------------+


#过滤无用行
mysql> select * from s,c where s.StudentClassID=c.ClassID;
+----+-------------+----------------+---------+--------------+
| ID | StudentName | StudentClassID | ClassID | ClassName    |
+----+-------------+----------------+---------+--------------+
|  1 | Careyson    |              1 |       1 | 软件一班     |
|  2 | Tony        |              2 |       2 | 软件二班     |
|  3 | Jack        |              3 |       3 | 软件三班     |
|  4 | Nancy       |              2 |       2 | 软件二班     |
|  5 | Peter       |              1 |       1 | 软件一班     |
+----+-------------+----------------+---------+--------------+


***#例如：使用 xsgl 数据库中的 department 表和 major 表中的数据查询每个系部都有哪些专业。
mysql> select departmentname as 系部名称,majorname as 专业名称
-> from department,major
-> where department.departmentid=major.departmentid;

#可以使用 CONVERT 函数将要排序的字段的字符集先转换为gbk，然后再进行 排序
mysql> select departmentname as 系部名称,majorname as 专业名称
 -> from department,major
 -> where department.departmentid=major.departmentid
 -> order by convert(departmentname using gbk );

根据业务需求实现多表查询

#可以在 where 子句后面继续增加查询条件，通常使用逻辑运算符 and 将连接条件和 查询条件连接起来
mysql> select * from s,c where s.StudentClassID=c.ClassID and c.classID=1;

为表设置别名

  • 在 select 语句中可以为查询的字段设置别名，同样也可以为表设置别名。

  • 表别名设置的格式：

    SELECT columnlist FROM table_name1 [AS] table_alias, table_name2 [AS] table_alias,…

table_alias：表别名

***#例如：查询 Student 表中有哪些同学。查询计算机工程系和信息工程系有的专业名称。为两个数据表设置别名。
select departmentname as 系部名称,majorname as 专业名称
from department as t1,major t2
where t1.departmentid=t2.departmentid and t1.departmentname in('计算机工程系','信息工程系')
order by convert(t1. departmentname using gbk) desc;

使用表别名注意：

  • 如果为表指定了别名，则所有引用表名的地方都必须使用别名，不能再使用原表名。

  • 当查询多个表之间的同名字段时，必须使用“表名.同名字段”进行限制。

  • 对于表间不同名的字段，可以在字段名前加表名进行限制，也可以不加表名，如果字段名前加上表

    名，则查询效率更高。

也可以进行多个表之间的连接查询，原理与两张表的相同。

#例如：：在上例 cj 表中，以 name（yw,sx,yy）的格式来显示每个同学的姓名及成绩。查询每个学生的学生信息、选课信息和成绩。
select * from student t1,course t2,score t3 where t1.stuid=t3.stuid and t2.courseid=t3.courseid;

3，用join关键字实现多表查询

  • 不仅可以使用“,”进行表间连接，还可以使用“join”关键字进行表间连接
  • join 关键字指明了两表之间的连接类型，on 关键字指明了连接条件，如果还有额外的查询条件可以再增加 where 子句，相比之前的连接查询而言“连接条件”从 where 子句中解脱出来，使用 on 关键字引出，而 where 子句只负责指出查询中需要的查询条件。
#join语法：
SELECT column_list
FROM table_name1 [AS] table_alias [ join_type ] JOIN table_name2 [AS] table_alias 
ON connection_condition [[ join_type ] JOIN table_name3 ON connection_condition […]]
WHERE search_condition

#解读
[join_type] ：表示连接类型，可省略。
1，内连接：INNER JOIN，当 JOIN 前面省略了连接类型时，默认就是内连接。
2，自然连接：NATURAL JOIN （是一种特殊的内连接）。 
3，外连接：又分为左外连接、右外连接、完全外连接，分别为[LEFT| RIGHT|FULL] OUTER JOIN。 
4，交叉连接：CROSS JOIN，可以不使用连接条件，实现了笛卡尔全集运算的功能。
table_alias：表别名。
column_list：列表。
connection_condition：连接条件。
search_condition：搜索条件。
ON 关键字：用来引出两表的连接条件，一般是两表的公共字段相等。
注意：#两表的公共字段是指表达的含义相同、数据类型相同、取值范围相同，但公共字段的字段名不一 定相同。

#简化版
from 表 1 inner join|natural join| left outer join|right outer join|full outer join |cross join 表 2 on 表 1.公共字段=表 2.公共字段 | using 字段名

做一个内连接查询

内连接查询的特点

  • 使用 inner join 作内连接查询，inner 关键字可以省略，即默认的连接类型就是内连接。

  • 内连接查询是指在连接的总结果集（笛卡尔积）中获取满足 on 后面指定连接条件的行。

  • on 后面给出的连接条件是以公共字段值相等比较，当然也可以是其他非等值比较，但是相等比较应用较为普遍。

#例如：使用 xsgl 数据库中的 teacher 表和 department 表，查询每个老师和他所属的系部信息。
select * from teacher t inner join department d on t.departmentid=d.departmentid;

#例如：改进一下，只查询每个老师的姓名和他所属的系部名称。
mysql> select teachername as 教师姓名,departmentname as 所在系部
 -> from teacher t inner join department d
 -> on t.departmentid=d.departmentid;
 
#例如：再改进一下，统计每个系有多少人。
mysql> select count(teachername) as 总人数,departmentname as 所在系部
 -> from teacher t inner join department d
 -> on t.departmentid=d.departmentid
 -> group by departmentname;

多个表之间的内连接查询

  • 只是在两表的 on 连接条件后继续使用 join 关键字连接第 3 张表，
#例如：使用 xsgl 数据库中的 student 表、class 表和 major 表，查询每个学生所属的班级名称和专业称。
select s.*,c.classname,m.majorname
from student s inner join class c on s.classid=c.classid 
inner join major m on c.majorid=m.majorid;

#例如：改进一下，再查询每个学生的班主任姓名。
select s.*,c.classname,m.majorname,t.teachername
from student s inner join class c on s.classid=c.classid
inner join major m on c.majorid=m.majorid
inner join teacher t on c.teacherid=t.teacherid;

#例如：再改进一下，查询每个学生的姓名、班级名称、专业名称、班主任姓名。
select s.stuname as 学生姓名,c.classname as 班级名称,
m.majorname as 专业名称,t.teachername as 班主任姓名
from student s inner join class c on s.classid=c.classid
inner join major m on c.majorid=m.majorid
inner join teacher t on c.teacherid=t.teacherid;

using子句的应用

  • 如果两表的共字段名相同，那么可以使用 using 子句代替等值的 on 连接条件。
#格式：from 表 1 join 表 2 using(同名字段);

select s.stuname as 学生姓名,c.classname as 班级名称,
m.majorname as 专业名称,t.teachername as 班主任姓名
from student s inner join class c using(classid)
inner join major m using(majorid)
inner join teacher t using(teacherid);

带查询条件的连接查询

  • 如果需要在查询中添加查询条件，可以增加 where 子句。
#例如：只查询籍贯在“河北”的学生信息，和他所在的班级名称。
select s.*,c.classname from student s join class c using(classid) where s.native like('河北%');

不等值连接

  • 这个不等值得不等值得比较，但几乎无意义。
#例如：使用 xsgl 数据库中的 department 表和 major 表，查询 department 表中的 departmentid 大于 major表中的 departmentid 的记录。
select * from department d inner join major m on d.departmentid>m.departmentid;

使用自然连接进行查询

  • 如果进行等值比较的内连接的两张表的公共字段名完全相同，那么可以使用 natural join 自然连接代替等值得内连接。

  • 自然连接中不能写on 连接条件

#格式：from 表1 natural join 表2；

#例如：使用自然连接查询每个系部有哪些专业。
select * from department natural join major;
等价于：
select * from department inner join major on department.departmentid=major.departmentid;

在自然连接中，公共字段的列只显示一次，而等值内连接中显示两次

使用外连接进行多表查询

  • 它的查询结果集中不仅包含符合连接条件的行，也包含两个连接表中不 符合连接条件的行。

  • 左外连接：left outer join

  • 右外连接：right outer join

  • 完全外连接：full outer join

1，左外连接

  • 公共字段值在左表中有但在右表中找不到匹配（满足连接条件）字段值的行，对于这样的行，从右表中选择的列显示为 Null。
#例如：使用 xsgl 数据库中的 department 表和 major 表，查询所有的系部信息和它们的专业信息，要求包括没有专业的系部信息。
select * from department left join major on department.departmentid=major.departmentid;

2，右外连接

  • 除满足查询条件的记录外还包含 join 关键字右边表中不满足查询条件的记录，而左表的相应位置返回 null 值。
select * from major right join department on department.departmentid=major.departmentid;

3、完全外连接

  • 完全外连接的查询结果集中除了包含满足条件的行外，还包括左表和右表两个表中找不到匹配记录的行。但是 mysql 中不支持这种连接方式。

使用交叉连接进行多表查询

  • 在连接查询中指定了 cross join 关键字的连接称为交叉连接
#格式：from 表1 cross join 表2；

1，#不指定连接条件的交叉连接与表的笛卡尔积运算结果相同
#例如：使用 xsgl 数据库中的系部信息表 department 和教师表 teacher 进行交叉连接查询。
select * from department cross join teacher;
与
select * from department,teacher;

2，#具有连接条件的交叉连接与内连接的结果相同
#例如：使用 xsgl 数据库中的系部信息表 department 和教师表 teacher 进行带条件的交叉连接查询。
select * from department cross join teacher using(departmentid);
或者：
select * from department cross join teacher on department.departmentid=teacher.departmentid;

4，使用单表自连接进行查询

  • 自连接查询并不是一种新的连接类型，而是指一个表自己连接自己，是连接查询的特殊形式。但是，自连接查询的格式与两张不同的表进行连接查询是相同的，通常是给一张表定义两个不同的别名，将一张表当作两张表用。
1，#创建职工表（字段有：职工编号、职工姓名、部门名称、经理的职工编号。）
create table emp1(eid varchar(5),ename varchar(5),dept varchar(10),manager varchar(5));

2，#使用下面语句向表中插入几条记录。
insert into emp1 values('e0001','张三','人事部','e0002');
insert into emp1 values('e0002','李四','人事部',null);
insert into emp1 values('e0003','王五','人事部','e0002');
insert into emp1 values('e0004','刘能','市场部',null);
insert into emp1 values('e0005','谢广坤','市场部','e0004');
insert into emp1 values('e0006','赵玉田','市场部','e0004');

3，#为职工表起两个不同的别名。
select * from emp1 a,emp1 b;

4，#改进一下，增加条件查询每个员工和他的经理信息
select * from emp1 a , emp1 b where a.manager=b.eid;
或者：
select * from emp1 a inner join emp1 b on a.manager=b.eid;
#使用内连接查询后，得到的职工信息不全，将那些没有经理的员工 经理本人 给过漏掉了。因此， 将上面的内连接查询改为左外连接查询。

5，#左外连接
select * from emp1 a left outer join emp1 b on a.manager=b.eid;

6，#根据5，再改进一下，只显示职工姓名、所在部门、他的经理姓名。
select a.ename,a.dept,b.ename from emp1 a left outer join emp1 b on a.manager=b.eid;

5，将多个查询结果的进行运算

  • 集合操作就是将两个或多个 SQL 查询结果集合并到一起的操作。

  • 运算符

    • UNION ALL（联合所有）
    • UNION （联合）
    • INTERSECT （交集）
    • MINUS （找差异）

  • 以上集合运算符在使用时都要求查询语句中查出的字段个数要相同，而且对应位置的字段数据类型必须一致或兼容，这样才能将结果放到一个集合中。

1，UNION ALL运算符：将两个结果和到一个集合中，不去重复。

#格式：
SELECT_statement1
UNION ALL
SELECT_statement2
UNION ALL

#查询籍贯是河北石家庄的学生和 1997 年之后出生的学生信息。
select * from student where native like('河北%石家庄%')
union all
select * from student where year(stubirthday)>=1997;

2，UNION 运算符：将两个结果和到一个集合中，去重复。

#格式：
SELECT_statement1
UNION 
SELECT_statement2
UNION 

#查询籍贯是河北石家庄的学生和 1997 年之后出生的学生信息。
select * from student where native like('河北%石家庄%')
union
select * from student where year(stubirthday)>=1997;

#将该语句改为 or 逻辑运算符，执行结果相同，只是记录的排序顺序不同。
select * from student where native like('河北%石家庄%') or year(stubirthday)>=1997;

3，INTERSECT 运算符：交集。但MySQL不支持。

4、MINUS 运算符：找差异。但MySQL不支持。

7，使用子查询获得表中信息

  • 子查询是指被嵌套在其他 SQL 语句中，作为其他语句一部分的 SELECT 语句。大部分子查询是一个完整的 SELECT 语句，而且多数用“（）”括起来。子查询可以嵌套在另一个查询语句内部，外层的 SELECT 语句称为外部查询，内层的 SELECT 语句称为子查询，子查询可以嵌套多层。除此之外，子查询还可以用在SELECT…INTO 语句、INSERT 语句、INSERT…INTO 语句、UPDATE 语句和 DELETE 语句中。

  • 根据子查询返回的记录集的结构可以将子查询划分为：单行子查询、多行子查询和多列子查询。根据子查询和外查询的执行逻辑、依赖关系，我们可以将子查询划分为非相关子查询和相关子查询。

  • 非相关子查询是指子查询的结果不受外查询影响，首先会执行子查询中的语句，然后将返回的结果再交给外查询语句使用，子查询只执行一次。

  • **相关子查询是指子查询的结果受外查询当前行的影响，**外查询每检测一条记录，系统就会将相关字段的值传递到内查询中，作为内查询的查询条件，因此子查询的结果集与外查询的当前行数据相关，而且外查询中有多少行数据子查询就会执行多少遍。

单个子查询***

  • 单行子查询是指子查询只返回单列单行数据，即只返回一个值，也可称为单值子查询

  • 运算符：包括等于（=）,不等于（<>）,小于（<）,大于（>）,小于等于（<=）和大于等于（>=）

  • 经常用在 SELECT、UPDATE、DELETE 语句的 WHERE 子句中充当查询、修改或删除的条件， 也可以用在 UPDATE 语句中的 SET 子句中充当新值.

1，#例如：使用 xsgl 数据库中的班级表 class 和学生表 student，查询“计算机 16 级 2 班”的所有学生信息。
select * from student where classid=(select classid from class where classname='计算机 16 级 2 班');
或者
select * from student inner join class using(classid) where class.classname='计算机 16 级 2 班';
#连接查询也可以实现。
不同之处在于连接查询中的 SELECT关键字的后面可以查询出两张表中的字段，而在（1）中的结果集中只能输出学生表的信息。

2，#例如：使用 xsgl 数据库中的成绩表 score，查询考试成绩低于整个班的平均成绩的学生编号、课程编号和考试成绩。
select stuid,courseid,examscore from score where examscore<(select avg(examscore) from score);

3，#例如：将学生张林浩的班级调到‘移动 15 级 2 班’。
分析：先使用子查询查出班级名称是移动 15 级 2 班的班级编号，再使用该编号作为 update 语句的新值。
update student set classid=(select classid from class where classname='移动 15 级 2 班') 
where stuname='张林浩';

多行子查询

  • 多行子查询是指子查询返回单列多行数据，即一组数据。

  • 当子查询是单列多行子查询时，必须使用多行比较运算符，包括 IN、NOT IN、ANY、ALL、SOME。

  • IN 和 NOT IN 可以独立使用，表示用来比较表达式的值是否在子查询的结果集中。

  • 但是 ANY 和 ALL 必须与单行比较运算符组合起来使用

    • <ANY：表示小于任何一个，即小于最大值即可。

    • =ANY：表示等于任何一个，与 in 类似。

    • ANY：表示大于任何一个，即大于最小值即可。

    • <ALL：表示小于所有值，即小于最小值。

    • ALL：表示大于所有值，即大于最大值。

    • =ALL：无意义。

    • SOME 和 ANY 类似。

#例如：使用 xsgl 数据库中的系部表 department 和教师表 teacher，查询所有部门名称是‘计算机工程系’和‘信息工程系’的教师信息。
mysql> select * from Teacher where DepartmentID in(select Departmentid from Department where Departmentname in('计算机工程系','信息工程系'));

#例如：使用 xsgl 数据库中的学生表 student、成绩表 score 和课程表 course，查询选修了课程名为“数据结构”的学生信息。
select * from student
where stuid in(select stuid from score 
where courseid=(select courseid from course
where coursename='数据结构'));

#使用 xsgl 数据库中的成绩表 score 和学生表 student，查询考试成绩低于整个班的平均成绩的学生信息。
select * from student where stuid in(select stuid from score where examscore<(select avg(examscore) from score));

多列子查询

  • 单行子查询和多行子查询获得的都是单列数据，但是多列子查询获得是多列任意行数据。
  • 当多列子查询返回单行数据时，在 where 子句中可以使用单行比较符（＝, >, <, >=, <=, <>）来进行比较。
  • 而返回多行数据时，在 where 子句中必须使用多行比较符（IN、ANY、ALL 和 SOME）来进行比较。
#例如：使用 xsgl 数据库中的学生表 student，查询和学号是“150101”的学生班级相同，籍贯也相同的学生信息，但不包括该学生本人。
select * from student 
where (native,classid)=(select native,classid from student where stuid='150101') and stuid<>'150101';

#例如：非成对比较方式查询和学号是“150101”的学生班级相同，籍贯也相同的学生信息，但不包括该学生本人。
select * from student 
where native=(select native from student where stuid='150101')
and classid=(select classid from student where stuid='150101')
and stuid<>'150101';

#多列子查询可以放到 from 子句后面当作虚拟表，但要记得用“（）”括起来，而且要给虚拟表起别名，

#使用 xsgl 数据库中的排课信息表 teachingcourse 和班级表 class，查询周一有课的班级名称、教室编号、星期和节次。
mysql> select classname,classroomid,weekday,part from Class t1,(select * from TeachingCourse where week

相关子查询

  • 相关子查询是指需要引用外查询表列的子查询语句，子查询的结果与外查询中当前被检索的记录的字段值相关，因此每次执行子查询的结果都不相同。

  • 非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行后将值传递给外部查询。而相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。

#例如：查询每门课程中考试成绩最低的学生编号、课程编号和考试成绩。
select stuid,courseid,examscore from score t1
where examscore=(select min(examscore) from score t2 group by courseid having t1.courseid=t2.courseid) ;

#在相关子查询中，还可以使用 EXISTS 运算符。该运算符用于测试子查询的结果是否为空，如子查询的结果集不为空，则 EXISTS 返回 TRUE，否则返回 FALSE。EXISTS 还可以与 NOT 合用，即 NOT EXISTS，其返回值与 EXISTS 恰好相反。

#例如：使用 xsgl 数据库中的教师表 teacher 和部门表 department，查询工作在“计算机工程系”的教师信息。
select * from teacher t
where exists
(select * from department d where t.departmentid=d.departmentid and d.departmentname='计算机工程系');

#相关子查询执行效率低，一般较少使用。
#例如：将上面的功能使用连接查询实现，代码如下：
mysql> select t.* from teacher t inner join department d
 -> using(departmentid)
 -> where d.departmentname='计算机工程系';
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值