数据查询——其他查询-CSDN博客

本文链接：https://blog.csdn.net/acm10139/article/details/145454612

数据查询

**查询数据是指从数据库中获取所需要的数据，eg:数据库已保存的用户表读取当前用户的密码进行验证。

联合查询

内连接查询

在实际开发中，我们会联合多个表来实现查询，比如把班级和学生表联合起来就同时可以看到班级、老师和学员

将两个表中ID相同的记录关联起来组成一个新的”列表“，这就是联合查询
select field1,field2,...,fieldn from tablename1
INNER JOIN tablename2 [INNER JOIN tablenamen] ON CONDITION;
'通过 inner join ... on 来实现'

create database school;
use school;
create table class(
`id` int not null auto_increment,
`name` varchar(128) default null,
`teacher` varchar(64) default null,
unique key `id`(`id`)
);
insert into class values(101,'萌新一班','Martin'),(102,'萌新二班','Rock'),(103,'萌新三班','Janny');
create table `student`(
`id` int not null auto_increment unique,
`name` varchar(64) default null,
`class_id` int default null,
`sex` enum('F','M')default null
);
insert into student values(1,'小花',101,'M'),(2,'小红',102,'F'),(3,'小军',102,'F'),(4,'小白',101,'F');
select * from class inner join student on class.id = student.class_id;
select * from class as a inner join student as b where a.id = b.id;#起别名 class as a

反引号：避免与sql关键字冲突（如果要建立名select的表，则可以用select）、支持特殊字符、区分大小写

起别名

当表名特别长或实现自连接时，直接使用表名无法区别表，所以MySQL提供了一种机制来为表取别名

select field1,field2,...,fieldn [AS] otherfieldn
from tablename1[AS] othertablename1,...,
tablenamen[AS] othertablenamen... where othertablename1.fieldx = othertablenamen.fieldx...;

自连接

特殊的等值查询——自连接就是表与自身进行连接
查询学生”小红“所在班级的所有学生
select t1.id,t1.name,t1.class_id from student t1 inner join student t2 on t1.class_id = t2.class_id and t2.name='小红';

等值连接

在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件
select * from class as a inner join student as b where a.id = b.class_id;

不等值连接

就是使用除（=）以外的关系运算符

select * from class as a inner join student as b where a.id != b.class_id;

外连接查询

查询数据时，要求返回操作表中至少一个表的所有数据记录，通过SQL语句”OUTER JOIN…ON“来实现
select field1,field2,...,fieldn
	from tablename1 LEFT|RIGHT[OUTER]JOIN tablename2 ON condition;

左外连接

外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表中的所有行，而不仅仅是连接列所匹配的行，如果左表中的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值
查询所有学生的学号、姓名、班级编号、性别、班级名、班主任信息
use school;
select * from student as a left join class as b on a.class_id = b.id;#左连接查询所有学生对应的班级信息
select * from class as a left join student as b on a.id = b.class_id;#左连接查询所有班级的学员信息

右外连接

外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。
查询所有班级的所有学生信息
use school;
select * from student as a right join class as b on a.class_id = b.id;#右连接查询所有班级对应的学员信息
select * from class as a right join student as b on a.id = b.class_id;#右连接查询所有学员对应的班级信息

合并查询数据记录

在MySQL中通过UNION来实现合并操作，可以将多个select语句查询结果合并在一起，组成新的关系
select field1,field2,...,fieldn from tablename1
union| union all
select field1,field2,...,fieldn from tablename2
union| union all select field1,field2,...,fieldn;
多个select的列数相同就可以合并，union和union all的主要区别就是union all把结果集合直接合并在一起，而union是将union all后的结果再执行一次distinct，去除重复的记录后的结果
use school;
select teacher from class union all select name from student;#查询班级表所有老师和学生表中所有学生姓名
select teacher as people from class union all select name as people from student;
select teacher,id from class union all select name,class_id from student;#查询班级表所有和学生表所有学生

子查询

所谓子查询，是指在一个查询中嵌套了其他若干查询，即在一个select查询语句的where或from子句中包含另一个select查询语句。在查询语句中，外层SELECT查询语句成为主查询，WHERE子句中的SELECT成为子查询，也成为嵌套查询

IN、ANY、ALL和EXISTS等关键字

带比较运算符的子查询

查询student表中”小花“所在班级班主任的名字

use school;
select teacher from class where id = (select class_id from student where name='小花');

在使用比较运算符时，select子句获得的记录不能大于一条

带关键字IN的子查询

查询student表中”小花“所在班级班主任的名字

use school;
select teacher from class where id in (select class_id from student where name='小花');
select teacher from class where id in (select class_id from student where name like '小%');#以”小“字开头的学生所在班级班主任的名字 like模糊查询

带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果返回至少一行，那么EXISTS的结果为TRUE

如果102班存在学生记录，就查询102班的班级信息的记录

select * from class where id=102 and exists(select * from student where class_id=102);

带关键字ANY的子查询

关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。

关键字ANY通常和比较运算符一起使用。例如，“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。

查询数据库school的表student中哪些学生可以获得奖学金。学生成绩达到其中任何一项奖学金规定的分数即可

use school;
create table schoolarship(score int, level varchar(64));
insert into schoolarship values(240,'二等奖'),(257,'一等奖');
select st.id,st.name,st.math+st.chinese+st.english total from grade st where (math+chinese+english) >= ANY(select score from scholarship);
#total相当于给该字段起了别名

带关键字ALL的子查询

**关键字ALL表示满足所有条件。**使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。

“>ALL”表示大于所有值，“<ALL”表示小于所有值。

查询数据库school的表student中哪些学生可以获得一等奖学金，即学生的总成绩要达到一等奖学金规定的分数，而一等奖学金是最高奖学金。

use school;
select st.id,st.name,st.math+st.chinese+st.english total from grade st where(math+chinese+english)>=ALL(select score from scholarship);
select st.id,st.name,st.math+st.chinese+st.english total from grade st where(math+chinese+english)<ALL(select score from scholarship);#不能获得奖学金的同学记录
#total相当于给该字段起了别名