MySQL数据库----多表查询，表连接

一.查询概述：

查询：指的是针对表中已经存在的数据，可以按照特定组合，条件，或者次序等等来进行一系列的检索查看操作------>select语句。

二.查询的基本操作：

1.查询语句的基本结构：

select   列名1,列名2,列名3....     from   表名;

select子句：查什么，后面写的是要查询的列

from子句：从哪查，后面写的是查询过程中用到的表

2.   *    任意，所有

select    *     from   表名;       查看表中所有的数据

3.针对查询的结果进行去重复的操作---->distinct

格式：select   distinct   列名   from  表名;

--  查看student表中所有内容

select  *  from   student;

-- 查看sc表中所有的内容

select  *   from   sc;

--从student表中，查看学生学号sno，姓名sname，年龄sage，系别sdept

select  sno,sname,sage,sdept   from  student;

-- 从course表中查看课程的编号cno,以及课程的名字cname

select  cno,cname  from  course;

-- 从student表中查看一下学生都分布在哪些专业？

select  distinct  sdept   from   student;

-- 从学生信息表中，查看学员都来自于哪些城市？

select   *   from  学生信息;

select  distinct  籍贯   from  学生信息;

-- 从sc表中查看学生都修了哪些课程？

select  distinct  cno  from  sc;

4.别名查询

别名：另起名字的含义

思想：针对查询出来结果的标题，进行起名字

格式一：

select    列名1   '别名1',列名2  '别名2',列名3  '别名3'...  from   表名;

格式二：as关键词---->指定意思

select  列名1  as  '别名1',列名2  as  '别名2'...   from   表名;

5.针对查询的结果，也可以进行计算的操作

--  如果不给查询结果起标题，来看一下默认的标题情况--->和列名保持一致

--  查看student表中所有的内容

select   *    from    student;

-- select    列名1   '别名1',列名2  '别名2',列名3  '别名3'...  from   表名;

-- 从student表中查数据，生成别名

select  sno  '学号',sname  '姓名',ssex  '性别',sage  '年龄',sdept  '系别'   from  student;

-- 从student表中把学号，姓名，年龄另起别名查询出来   as

select   sno  as  '学号',sname  as  '姓名',sage  as  '年龄'   from   student;

-- 5.针对查询的结果，也可以进行计算的操作

-- 从sc表中查询每个学生成绩加10分之后的数据信息

select  *  from  sc;

select  sno,cno,grade+10  '加分后的成绩'   from  sc;

三.条件查询(选择查询)

概述：在实际查询工作中，很多时候也是需要条件筛选后的数据做操作，这种情况就可以利用条件查询(选择查询)---->where子句

格式：select  列名  from  表名  where   条件;

1.比较运算符

=   >   <     >=    <=   <>    !=  不等于

2.逻辑运算符，用来连接多个条件

and    连接条件同时满足

or     连接条件只需要满足其中一个即可

3.范围搜索条件查询

3.1查询在指定范围内的数据信息   between   开始值     and     结束值

select  列名  from  表名  where  列名   between  开始值  and  结束值;

3.2查询不在指定范围内的数据信息  not  between  开始值  and  结束值

select  列名  from  表名  where  列名  not  between  开始值  and  结束值;

-- 从sc中查看成绩及格的学生信息

select  *   from  sc   where   grade>=60;

-- 从student表中查看不是计算机系学生的信息

select   *   from  student   where   sdept<>'计算机系';

-- 从sc表中查看修了c02这门课，且成绩还得大于70分学生的信息

select  *   from   sc   where  cno='c02'  and  grade>70;

-- 从student表中查询年龄大于等于20或者是信息系的学生信息

select  *  from  student   where  sage>=20  or  sdept='信息系';

-- 从sc表中查看成绩在70-90之间的学员信息

select  *   from  sc  where  grade  between  70  and  90;

-- 从sc表中查看成绩不在70-90之间的学员信息

select  *  from   sc  where  grade not  between  70  and  90;

4.列表搜索条件查询

4.1匹配列表中的内容   in(值1,值2,值3....)

select  列名  from  表名  where  列名  in(值1,值2,值3...);

思想：只要匹配到列表中任意一个值，都会有查询的结果

4.2不要列表中的内容   not   in(值1,值2,值3...)

select  列名  from  表名  where  列名  not  in(值1,值2,值3...);

思想：只要是列表中的内容，我都不做查询，排除在外

5.字符匹配符查询(模糊查询)

5.1匹配和字符模板相关的数据   like  '字符模板'

select  列名   from  表名  where   列名  like  '字符模板';

思想：只要匹配到和字符模板相关的数据，就直接查询出来

5.2把匹配到和字符模板相关的数据排除在外   not  like  '字符模板'

select  列名  from 表名  where  列名  not  like   '字符模板';

思想：不要和模板相关的数据

字符模板的写法：提供了两个通配符

%   代表的是零个或多个字符，任意个字符

_     代表的是一个字符

例：王% ,能够匹配到的数据有( ABCD   )

A.王    B.王一   C.王一二   D.王一二三   E.大王   F.小王一

例：王_ ,能够匹配到的数据有(B )

A.王    B.王一   C.王一二   D.王一二三   E.大王   F.小王一

-- 从student表中查看计算机系和数学系的学生信息

select  *   from  student  where  sdept  in('计算机系','数学系');

select  *   from  student  where  sdept='计算机系'  or  sdept='数学系';

-- 从sc表中查看除了修了c01，c02这两门课学生的情况

select  *   from  sc  where  cno  not  in('c01','c02');

select  *  from  sc  where  cno!='c01'  and  cno!='c02';

-- 从student表中查看姓王的学生信息

select  *   from  student  where  sname  like  '王%';

select  *   from  student  where  sname  like  '王_';

-- 从student表中查看不是姓王的学生信息

select  *  from  student  where  sname  not  like  '王%';

-- 从student表中查看名字中第二个字符是小的学生信息

select  *   from  student   where   sname   like  '_小%';

-- 从班级信息表中查看pu开头的班级信息

select  *  from  班级信息  where  班级名称  like  'pu%';

-- 从sc表中查看修了c01,c04,c06的学生修课情况

select *  from  sc  where  cno  in('c01','c04','c06');

6.空值查询

思想：看某一条记录中是否存在空的值

6.1判断(查询)为空的记录    is  null

select   列名   from   表名   where   列名  is  null;

6.2判断(查询)不为空的记录    is  not  null

select  列名   from  表名   where  列名  is  not   null;

-- 从sc表中查看有考试成绩的学生修课情况

-- 有考试成绩 ：成绩不为空,有数据

select  *   from  sc where  grade  is  not null;

-- 从sc表中查看没有考试成绩的学生修课情况

-- 没有考试成绩：成绩为空

select  *  from  sc  where  grade  is  null;

四.聚合函数

概述：是mysql中自带的函数，已经是声明好的，我们是直接拿来使用的，一般情况下是用来做计算的

1.sum(列名)   求和

2.avg(列名)    求平均值

3.max(列名)   求最大值

4.min(列名)    求最小值

格式：select   聚合函数   from   表名;

5count(*)    统计元组的个数(统计行的个数)

6.count(列名)    统计该列的值的总个数

除了count(*)外，其它函数在做操作时，都会忽略空值(空值null不参与计算)

五.行数限定查询  limit

思想：从一张表中，查看指定行的数据，比如：前十行，前十五行，第七行到第十五行.....

格式一：select  列名   from  表名  limit  行数;(默认从第一行开始查起)

格式二：select  列名   from  表名   limit   开始位置的下标,行数;(从指定的行开始做查看，下标=位置-1)

-- 查看student表中前五行记录

select  *  from  student  limit  5;

-- 查看student表中数据：从第三行开始查看，查看4条记录

select  *  from  student  limit 2,4;

-- 查看student表中从第四行到第七行数据

select  *   from  student  limit 3,4;

-- 从第二行开始做查询，查询两条记录

select  *  from  student  limit  1,2;

六.分组查询   group  by

思想：针对查询出来的结果，按照某个列来进行分组(划分)

例：统计学校中每个年级的总人数

套路：首先：先把学校的总人数给查出来(获取到)，然后：再来根据"年级"进行数据划分

比如：学校总人数是500人，按照年级划分，一年级80人，二年级110人....

格式：select   聚合函数  from  表名  group  by  列名;

针对分组后的数据，也可以进行条件筛选的操作

例：-- 从sc表中统计每门课程的修课总人数，限制条件：只看这门课选课总人数超过3人

having   分组的条件(一般情况下是聚合函数当条件)

where虽然作为条件查询，但是它后面不能写聚合函数

格式：

select  聚合函数   from  表名  group  by  列名   having   条件;

注意：having的使用，必须配合group  by，不能单独使用

-- 从student表中统计每个专业(系别)各自有多少人？

-- 查询出的是"人数"---->细化到每个专业

-- 在student表中，一行代表的是一个学生的记录，统计有多少行，就代表有多少个学生

select   sdept,count(*)  '总人数'  

from  student

group  by  sdept;

  七.排序查询     order  by

思想：把查询出来的结果，按照某个列进行排序(降序或升序)

升序  asc  (默认)       降序   desc

格式：select  列名   from  表名   order  by   列名   [asc或者desc];

一个完整的查询操作中包含的关键词：

select    要查的列名

from    表名

where  选择条件

group  by   分组的列名

having   聚合函数当条件(分组条件)

order  by  排序的列名     asc|desc;

八.多表查询---->表连接

在之前讲查询操作时，都是针对一个表中的数据来进行的，那么在实际的工作中，很多时候需要的数据，可能来源于多张表，这个时候就需要用到多表查询的操作(用到表的数量>=2)

例：查询学生的学号，姓名，系别，课程号，成绩

student(sno,sname,sdept)

sc(cno,grade)

================》只有两个表的结合，才能查询出最终的结果

多张表之间的查询，实现的方式：表连接(表与表之间的连接)

====》只有表与表之间建立了连接关系，才能进行多表查询

Ⅰ.表连接操作分类：

1.内连接查询(最常用)

提供表与表之间的连接方式，有两种写法：

格式一：where 来指定表与表之间的连接条件

select   表名1.列名1,表名1.列名2,表名2.列名1,表名2.列名2.....

from  表名1,表名2

where  表名1.列=表名2.列;

注意：表与表之间实现连接的列：列名可以不一致，但是列中的值和数据类型必须保持一致;内连接查询思想：只有两个表中匹配到的数据，才会有查询的结果；在表连接查询中，表名可以直接另起别名，方便操作，格式：表名  别名；select语句中的表名.列名的写法，其实表名是可以省略：如果查询的列，只存在于一个表中，那么表名可以省略，如果查询的列，两个表中都有，那么不可以省略表名；

格式二：on 来指定表与表之间的连接条件

select   表名.列名

from   表名1  inner  join  表名2

on  表名1.列=表名2.列;

--  查询修课门数等于或大于4门的学生的平均成绩和选课门数。   sc

-- 查：平均成绩，选课门数 ---->细化每个学生

-- 条件：选课门数>=4

select  sno,avg(grade)  '平均成绩',count(*)  '选课门数'

from  sc

group by  sno

having count(*)>=

-- 案例1:查询学生的学号，姓名，性别，以及所在的班级名称和年级

/*

多表查询的做题思路：

1.确定表--->根据题上的分析，看用到了哪些表

2.确定表与表之间能够建立起连接的列  ---->找相同列(列名可以不一致，但是列的内容和数据类型必须一致)

3.确定要查询的内容   ---->题上要什么，咱们就查什么

4.最后再来看题上是否有额外的条件

注意点：在inner join on写法中，如果有额外的条件，需要在on的后面，单独用一个where子句来书写(on只放表连接条件)

select  表名.列名

from  表名1 inner  join   表名2

on   表名1.列名=表名2.列名

where   额外的条件;

-- 例：查询学生的学号sno，姓名sname，系别sdept，课程号cno，成绩grade

select s.sno,sname,sdept,cno,grade

from  student  s,sc c

where s.sno=c.sno;

-- 案例2：查询籍贯是beijing，班级是以'pu'开头的学生的学号，姓名，性别，以及所在的班级名称和年级

select  学号,姓名,性别,班级名称,年级

from  学生信息 s,班级信息 c

where s.班级编号=c.班级编号  and  s.籍贯='beijing'  and  c.班级名称  like  'pu%';

select  学号,姓名,性别,班级名称,年级

from  学生信息 s  join  班级信息  c

on  s.班级编号=c.班级编号  and  籍贯='beijing'  and  班级名称 like 'pu%';

select  学号,姓名,性别,班级名称,年级

from 学生信息 s  join  班级信息  c

on  s.班级编号=c.班级编号

where 籍贯='beijing'  and  班级名称  like  'pu%';

-- 查看修了数据库基础cname这门课学生的学号，课程号，成绩   course  sc    cno

select sno,sc.cno,grade

from  course,sc

where  course.cno=sc.cno  and   cname='数据库基础';

2.inner  join  on表连接分类

等值连接：指的是除了连接条件外，即使有额外的条件，条件中也只有=操作

非等值连接：指的是除了连接条件外，额外的条件中，还有其它的比较运算符

-- 查看计算机系且修了c02这门课的学生学号，姓名，年龄，课程号，成绩  student  sc    sno

select student.sno,sname,sage,cno,grade

from  student  inner join  sc

on  student.sno=sc.sno

where sdept='计算机系'  and  cno='c02';

以上的SQL语句，就是一个等值连接的写法

非等值连接案例：

案例:查询成绩大于40的学生个人情况(学号,姓名,性别,班级编号,成绩)，并按照成绩降序排列。

select  s.学号,姓名,性别,班级编号,成绩

from  学生信息 s  inner join  成绩表 c

on  s.学号=c.学号

where 成绩>40

order  by  成绩  desc;

3.多表查询(表的数量>2)

格式一：where实现多表连接

select   表名.列名

from 表名1,表名2,表名3,表名4....

where  表名1.列=表名2.列   and   表名2.列=表名3.列   and  表名3.列=表名4.列....;

格式二：on实现多表连接

select   表名.列名

from   表名1  inner  join  表名2

on   表名1.列=表名2.列       inner   join    表名3

on    表名2.列=表名3.列       inner  join     表名4

on     表名3.列=表名4.列....;

-- 查看学生的学号sno，姓名sname，专业sdept，课程号cno，课程名cname，成绩grade

select student.sno,sname,sdept,sc.cno,cname,grade

from  student,sc,course

where  student.sno=sc.sno  and sc.cno=course.cno;

select student.sno,sname,sdept,sc.cno,cname,grade

from student  inner  join  sc

on  student.sno=sc.sno   inner  join   course

on  sc.cno=course.cno;

-- 查看修了数据结构的学生的学号，姓名，专业，成绩

-- 查看修了c02学生的学号，姓名，课程名，成绩，结果按成绩降序排列

-- 查看计算机系，且考试成绩及格的学生的学号，姓名，课程号，课程名

4.外连接：做表连接操作时,

基思想：至少会返回一个表的所有内容

到底会返回哪个表的所有内容？是和它的分类是有关系的。

外接分类：

4.1左外部连接(左连接)   left   outer  join

思想：返回的是左表的所有内容；如果左表的数据，在右表中没有找到匹配的信息，那么对应位置显示null

4.2右外部连接(右连接)   right  outer  join

思想：返回的是右表的所有内容；如果右表的数据，在左表中没有找到匹配的信息，那么对应位置显示null

区分左表和右表：在语法结构中，先写的表是左表，后写的表是右表

语法格式：

select  表名.列名

from   表名1  left  outer  join   表名2

on  表名1.列=表名2.列;