mysql之连接查询、联合查询、子查询

一、 子查询的定义
出现在其他语句中的select语句,称为子查询或者内查询,外部的查询语句称为主查询或者外查询,子查询可以包含普通select可以包含的任何语句。
外部查询:select、insert、update、delete、set等,主要就是在select的应用。
二、 子查询的分类
1.按照子查询出现的位置
①select后面:仅仅支持标量子查询
②from后面:支持表子查询
③where或having后面:支持标量子查询、列子查询、行子查询
④exist后:表子查询
2.结果集的行列数
①标量子查询:结果集一行一列
②列子查询:结果一列多行
③行子查询:一行多列
④表子查询:多行多列
3.子查询的特点
①子查放在小括号内
②子查询一般放在条件右侧
③标量子查询,一般搭配着单行操作符使用[> < >= <= =
<>]
④列子查询,一般搭配着多行操作符使用[in、any/some、all]
⑤子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
3.select后面,仅仅支持标量子查询
图片 1
4.from后面:支持表子查询
其查询结果可以比作一张表,必须给表起别名
图片 2
5.where或having后面
①支持标量子查询、行子查询、列子查询
②in、any、all的理解
in:等于列表中的任意一个,如:a
[10,30,60,80,100],a等于5个数中的任意一个,等价于=any
not in:列表中的任意一个都不等于,如a[10,20,30,40,50] a
不等于其中任何一个,等价于<>all
any/some:和子查询返回的某一个值进行比较,<any表示小于任意一个,等价于<max(字段名)
all:和子查询的所有值进行比较,<all表示小于所有的,等价于<min(字段名)
③标量子查询
图片 3
④列子查询(一行多列)
图片 4
⑤行子查询(一行多列)
图片 5
6.exists后面
语法:exists(完整的查询语句),结果为0或者1
图片 6
三、 分页查询
由于数据量较大,一页显示不完整,需要分页查询。
1.基本语法:
Select 查询列表 from 表1
[连接类型 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后筛选
order by 排序字段
limit 分组条件]
2.limit特点
①放在查询语句的最后面
②limit [index,] size
index开始的下标,size每页显示的条目数,省略初始位置,默认为0
③公式:limit (page-1)*size,size;page是要显示的页数
图片 7
四、 联合查询(union)
union: 将多条查询语句的结果合并成一个结果
基本语法:查询语句1 union 查询语句2 union 查询语句3…
基本应用:要查询的结果来自于多个表,且多个表没有直接联系,查询的信息内容一致
①多条查询语句字段数目一致
②要求每一列的类型和顺序尽量一致
③union关键之默认去重,union all可以包含重复项
图片 8

一.外键(foreign key)

外键用于记录表之间的联系
外面的键(键不在自己表中):如果有一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键
(一张表可以有多个外键)

1.增加外键

  • 创建表时候增加外键:
    foreign key(外键字段) reference 外部表 (主键字段);
  • 在新增表之后增加外键:
    alter table 表名 add [constraint 外键名字] foreign key(外键字段)
    reference 父表(主键字段);

图片 9

创建表时候增加外键

图片 10

在新增表之后增加外键

2.修改外键&&删除外键

外键不能修改,只能删除后新增

删除外键
alter table 表名 drop foreign key 外键名;

一张表中可以有多个外键,但名字不能相同

图片 11

删除外键

外键删除不能通过查看表结构体现,应该通过查看表创建语句查看

3.外键作用

  • 1.对父表

对父表的约束:父表数据进行写操作(删和改:都必须涉及到主键本身),如果对应的主键在子表中已经被数据引用,那么不允许操作。

  • 2.对子表(外键字段所在的表)

约束子表数据操作:子表数据进行写操作的时候,如果对应的外键字段在父表中找不到对应的匹配——操作失败

4.外键条件

(1)外键要存在:首先必须保证表的存储引擎innodb,如果不是innodb存储引擎,那么外键可以创建成功但是没有约束效果
(2)外键字段的字段类型(列类型)必须与父类的主键类型完全一致
(3)一张表中的外键名不能重复
(4)增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应

5.外键约束

针对父表的约束

  • district:严格模式(默认的),父表不能删除或者更新一个已经被子表数据引用的记录
  • cascade:级联模式:对父表操作,子表关联的数据也跟着操作
  • set
    null:置空模式:父表的操作之后,子表对应的数据(外键字段)被置空。

约束模式:删除的时候子表置空,更新的时候子表级联操作指定模式的语法

foreign key(外键字段) references 父表(主键字段) on delete set null on
update cascade;

删除置空的前提条件:外键字段允许为空(如果不满足条件,外键无法创建)


二、联合查询

联合查询:将多次查询(多条select语句),在记录上次进行拼接(字段不会增加)

基本语法:多条select语句构成:每一条select语句获取的字段数必须严格一致(但是字段类型无关)

select 语句1 union [union选项] select 语句2……

union选项

  • all:保留所有
  • distinct:去重(默认的)

图片 12

默认去重

联合查询只要求字段数一样,跟数据类型无关

图片 13

联合查询

1.联合查询的意义

(1)查询同一张表,但是需求不同
eg:查询学生信息,男生身高升序,女生身高降序

(2)多表查询:多张表的结构是完全一样的,保存的数据结构也是一样的。

2.联合查询order by的使用

在联合查询中order by不能直接使用,需要对查询语句使用括号
若要order by生效必须搭配limit;用limit限制显示数量

图片 14

联合查询的应用

本文内容:

连接查询

联合查询

子查询

  • from子查询
  • where子查询
  • exists子查询

 

首发日期:2018-04-11


三、子查询(sub query)

子查询:就是一个select查询是另一个查询的附属(一条select语句内部包含了另一条select语句)

1.子查询分类

  • 按位置分类:子查询(select语句)在外部查询(select语句)中出现的位置
    • from子查询:子查询跟在from之后
    • where子查询:子查询出现where条件中
    • exists子查询:子查询出现在exists里面

判断某些条件是否满足(跨表),exists是接在where之后
exists返回结果只有0和1。

图片 15

exists子查询

图片 16

exists子查询

  • 按结果查询:根据子查询得到的数据进行分类
    • 标量子查询:子查询得到的结果是一行一列

图片 17

标量子查询

  • 列子查询:子查询得到的结果是一列多行

列子查询返回的结果为一列多行,需要使用in作为条件匹配
还有几个类似的条件:all,some,any

all:就是全部 ,使用时需要在前面加”=”
any:就和in是一样的,在使用的时候需要在前面加”=”或者”!=”
any和some是一样的,即:any==some

图片 18

列子查询

图片 19

all,some,any

  • 行子查询:子查询得到的结果是多行多列(多列一行)

图片 20

行子查询

(以上几个出现位置是在where之后)

  • 表子查询:子查询得到的结果是多行多列(出现位置是在from之后)

图片 21

表子查询

连接查询:

 

连接查询就是将多个表联合起来查询,连接查询方式有内连接、外连接、自然连接、交叉连接。连接查询使得可以同时查看多张表中数据。

  • 内连接:有条件连接,多个表之间依据指定条件连接,匹配结果是保留符合匹配结果的记录。
  • 外连接:与内连接不同的是不管匹配符不符合都保留,根据外连接连接方式来决定保留哪张表,比如保留左表的话,那么左表无法匹配右表时,保留左表数据,然后置右表字段数据为null.
  • 自然连接:有条件连接,自动依据“同名字段”连接(多个同名字段就都作为条件)。
  • 交叉连接cross
    join:无条件连接,将每一条记录与另外一个表的每一条记录连接(笛卡尔积),结果是字段数等于原来字段数之和,记录数等于之前各个表记录数之乘积。

 

 

-- 实验表结构
create table student(
id int,
name varchar(15),
gender varchar(15),
cid int
);
create table class(
cid int,
cname varchar(15)
);
drop table student,class;
-- 实验表数据:
insert into student values(1,"lilei","male",1),(2,"hanmeimei","male",2),(3,"jack","male",1),(4,"alice","female",4); --这里特意创建了一个class中没有的4
insert into class values(1,"linux"),(2,"python"),(3,"java"),(5,"html5");--这里特意创建了一个student中没有的5
select * from student;
select * from class;

图片 22图片 23

 

 

内连接:

从左表中取出每一条记录,去右表中与所有的记录进行匹配,保留匹配成功的记录,并将两份记录拼接。

语法:select 字段列表 from 左表 [inner] join 右表 on 左表.字段 =
右表.字段;

  • 不使用on条件的时候,结果与交叉连接相同

    — 内连接
    — select from student inner join class; –结果与交叉连接相同
    select
    from student join class on student.cid = class.cid;
    select * from student inner join class on student.cid = class.cid;

图片 24

 

 

 

Copyright @ 2015-2019 ca88 版权所有
网站地图xml地图