精华内容
下载资源
问答
  • 连接查询
    千次阅读
    2021-01-19 10:08:39

    一、交叉连接(cross join)

    交叉连接(cross join):有两种,显式的和隐式的,不带on子句,返回的是两表的乘积,也叫笛卡尔积。

    例如:下面的语句1和语句2的结果是相同的。

    语句1:隐式的交叉连接,没有cross join。

    selecto.id, o.order_number, c.id, c.name fromorders o , customers c where o.id=1;

    语句2:显式的交叉连接,使用cross join。

    selecto.id,o.order_number,c.id,c.name from orders o cross joincustomers c whereo.id=1;

    语句1和语句2的结果是相同的,查询结果如下:

    二、内连接(inner join)

    内连接(inner join):有两种,显式的和隐式的,返回连接表中符合连接条件和查询条件的数据行。(所谓的链接表就是数据库在做查询形成的中间表)。

    例如:下面的语句3和语句4的结果是相同的。

    语句3:隐式的内连接,没有inner join,形成的中间表为两个表的笛卡尔积。

    selecto.id,o.order_number,c.id,c.name fromcustomers c, orders o wherec.id=o.customer_id;

    语句4:显示的内连接,一般称为内连接,有inner join,形成的中间表为两个表经过on条件过滤后的笛卡尔积。

    selecto.id,o.order_number,c.id,c.name from customers c inner join orders o onc.id=o.customer_id;

    语句3和语句4的查询结果:

    三、外连接(outer join):

    外连不但返回符合连接和查询条件的数据行,还返回不符合条件的一些行。外连接分三类:左外连接(left outer join)、右外连接(right outer join)和全外连接(full outer join)。

    三者的共同点是都返回符合连接条件和查询条件(即:内连接)的数据行。不同点如下:

    左外连接还返回左表中不符合连接条件单符合查询条件的数据行。

    右外连接还返回右表中不符合连接条件单符合查询条件的数据行。

    全外连接还返回左表中不符合连接条件单符合查询条件的数据行,并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集(去掉重复),即“全外=左外 union 右外”。

    说明:左表就是在“(left outer join)”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中,outer 关键字是可省略的。

    下面举例说明:

    语句5:左外连接(left outer join)

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer joincustomers c on c.id=o.customer_id;

    语句6:右外连接(right outer join)

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o right outer joincustomers c on c.id=o.customer_id;

    注意:where条件放在on后面查询的结果是不一样的。例如:

    语句7:where条件独立。

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer joincustomers c on c.id=o.customer_id where o.order_number<>'mike_order001';

    语句8:将语句7中的where条件放到on后面。

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer joincustomers c on c.id=o.customer_id and o.order_number<>'mike_order001';

    从语句7和语句8查询的结果来看,显然是不相同的,语句8显示的结果是难以理解的。因此,推荐在写连接查询的时候,on后面只跟连接条件,而对中间表限制的条件都写到where子句中。

    语句9:全外连接(full outer join)。

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer joincustomers c on c.id=o.customer_id;

    注意:mysql是不支持全外的连接的,这里给出的写法适合oracle和db2。但是可以通过左外和右外求合集来获取全外连接的查询结果。下图是上面sql在oracle下执行的结果:

    语句10:左外和右外的合集,实际上查询结果和语句9是相同的。

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o left outer joincustomers c on c.id=o.customer_id union selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o right outer join customers c on c.id=o.customer_id;

    语句9和语句10的查询结果是相同的,如下:

    四、联合连接(union join):

    这是一种很少见的连接方式。oracle、mysql均不支持,其作用是:找出全外连接和内连接之间差异的所有行。这在数据分析中排错中比较常用。也可以利用数据库的集合操作来实现此功能。

    语句11:联合查询(union join)例句,还没有找到能执行的sql环境。

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o union joincustomers c on c.id=o.customer_id

    语句12:语句11在db2下的等价实现。还不知道db2是否支持语句11呢!

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer joincustomers c on c.id=o.customer_id except selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o inner join customers c onc.id=o.customer_id;

    语句13:语句11在oracle下的等价实现。

    selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer joincustomers c on c.id=o.customer_id minus selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o inner join customers c onc.id=o.customer_id;

    查询结果如下:

    五、自然连接(natural inner join):

    说真的,这种连接查询没有存在的价值,既然是sql2标准中定义的,就给出个例子看看吧。自然连接无需指定连接列,sql会检查两个表中是否相同名称的列,且假设他们在连接条件中使用,并且在连接条件中仅包含一个连接列。不允许使用on语句,不允许指定显示列,显示列只能用*表示(oracle环境下测 试的)。对于每种连接类型(除了交叉连接外),均可指定natural。下面给出几个例子。

    语句14:

    select * from orders o natural inner join customers c;

    语句15:

    select * from orders o natural left outer join customers c;

    语句16:

    select * from orders o natural right outer join customers c;

    语句17:

    select * from orders o natural full outer join customers c;

    六、sql查询的基本原理:两种情况介绍。

    第一、   单表查询:根据where条件过滤表中的记录,形成中间表(这个中间表对用户是不可见的);然后根据select的选择列选择相应的列进行返回最终结果。

    第二、   两表连接查询:对两表求积(笛卡尔积)并用on条件和连接类型进行过滤形成中间表;然后根据where条件过滤中间表的记录,并根据select指定的列返回查询结果。

    第三、   多表连接查询:先对第一个和第二个表按照两表连接做查询,然后用查询结果和第三个表做连接查询,以此类推,直到所有的表都连接上为止,最终形成一个中间的结果表,然后根据where条件过滤中间表的记录,并根据select指定的列返回查询结果。

    理解sql查询的过程是进行sql优化的理论依据。

    七、on后面的条件(on条件)和where条件的区别:

    on条件:是过滤两个链接表笛卡尔积形成中间表的约束条件。

    where条件:在有on条件的select语句中是过滤中间表的约束条件。在没有on的单表查询中,是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。

    从这里可以看出,将where条件移入on后面是不恰当的。推荐的做法是:

    on只进行连接操作,where只过滤中间表的记录。

    八、总结

    连接查询是sql查询的核心,连接查询的连接类型选择依据实际需求。如果选择不当,非但不能提高查询效率,反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据:

    1、 查两表关联列相等的数据用内连接。

    2、 col_l是col_r的子集时用右外连接。

    3、 col_r是col_l的子集时用左外连接。

    4、 col_r和col_l彼此有交集但彼此互不为子集时候用全外。

    5、 求差操作的时候用联合查询。

    多个表查询的时候,这些不同的连接类型可以写到一块。例如:

    selectt1.c1,t2.cx,t3.cy fromtab1 t1 inner join tab2 t2 on (t1.c1=t2.c2) inner jointab3 t3 on (t1.c1=t2.c3) left outer join tab4 on(t2.c2=t3.c3); where t1.x >t3.y;

    更多相关内容
  • 连接查询

    千次阅读 2018-05-30 10:07:34
    连接查询连接查询:将多张表(可以大于2张)进行记录的连接(按照某个指定的条件进行数据拼接)。最终结果:记录数有可能变化,字段数一定会增加(至少两张表的合并)。 连接查询会产生笛卡尔积,假设集合A={a,b},...

    连接查询

    连接查询:将多张表(可以大于2张)进行记录的连接(按照某个指定的条件进行数据拼接)。最终结果:记录数有可能变化,字段数一定会增加(至少两张表的合并)。

     

    连接查询会产生笛卡尔积,假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。那么多表查询产生这样的结果并不是我们想要的,那么怎么去除重复的、不想要的记录呢?当然是通过条件过滤。通常要查询的多个表之间都存在关联关系,那么就通过关联关系去除笛卡尔积。

    你能想像到emp和dept表连接查询的结果么?emp一共14行记录,dept表一共4行记录,那么连接后查询出的结果是56行记录。但你只是想在查询emp表的同时,把每个员工的所在部门信息显示出来,那么就需要使用主外键来去除无用信息了,即使用主外键关系去除。

     

    连接查询的分类:内连接外连接自然连接交叉连接

    内连接

    内连接:[inner] join,从左表中取出每一条记录,去右表中与所有的记录进行匹配,匹配必须是某个条件在左表中与右表中相同,最终才会保留结果,否则不保留。

     

    基本语法:

    左表 [inner] join 右表 on 左表.字段 = 右表.字段;

    on表示连接条件:条件字段就是代表相同的业务含义(如my_student.c_idmy_class.id),大多数情况下为两张表中的主外键关系。

     

    什么是内连接?内连接的关键字是什么?

    答:仅将两个表中满足连接条件的行组合起来作为结果集,称为内连接。在内连接中,只有在两个表中匹配的行才能在结果集中出现。关键字:[inner] join ... on

     

    外连接

    外连接:outer join,以某张表为主,取出里面的所有记录,然后每条与另外一张表进行连接,不管能不能匹配上条件,最终都会保留。能匹配,正确保留;不能匹配,其它表的字段都置空(null)。

     

    外连接分为两种:是以某张表为主,有主表

    left join:左外连接(左连接),以左表为主表

    right join:右外连接(右连接),以右表为主表

     

    基本语法:

    左表 left/right join 右表 on 左表.字段 = 右表.字段;

    on表示连接条件:条件字段就是代表相同的业务含义(如my_student.c_idmy_class.id),大多数情况下为两张表中的主外键关系。

     

    什么是左外连接?左外连接的关键字是什么?

    答:在内连接的基础上,还包含左表中所有不符合条件的数据行,并在其中的右表列填写NULL。关键字:left join ... on。

    什么是右外连接?右外连接的关键字是什么?

    答:在内连接的基础上,还包含右表中所有不符合条件的数据行,并在其中的左表列填写NUL。关键字:right join ... on。

     

    左表为主表,最终记录数至少不少于左表已有的记录数。

     

     

    右表为主表,最终记录数至少不少于右表已有的记录数。

    虽然左连接和右连接有主表差异,但是显示的结果是:左表的数据在左边,右表的数据在右边。左连接和右连接可以互转。

    自然连接

    自然连接:natural join,自然连接,就是自动匹配连接条件,系统以字段名字作为匹配模式(同名字段就作为条件,多个同名字段就都作为条件)。

     

    自然连接:可以分为自然内连接自然外连接

     

    自然内连接:左表 natural join 右表;

     

     

    自然外连接:左表 natural left/right join 右表;

     

     

    其实,内连接和外连接都可以模拟自然连接,使用同名字段,合并字段。

    左表 left/right/inner join 右表 using(字段名)--使用同名字段作为连接条件,自动合并条件

     

    交叉连接

    交叉连接:cross join,从一张表中循环取出每一条记录,每条记录都去另外一张表进行匹配,匹配一定保留(没有条件匹配),而连接本身字段就会增加(保留),最终形成的结果叫做:笛卡尔积。

     

    基本语法:左表 cross join 右表; <==>  from 左表,右表;

     

    笛卡尔积没有意义:应该尽量避免(交叉连接没用)

    交叉连接存在的价值:保证连接这种结构的完整性。

     

    【补充】

    连接查询心得

    连接查询不限于两张表,连接查询也可以是三张、四张,甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积,而只是需要其中一部分,那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

    两张表的连接查询一定有一个主外键关系,三张表的连接查询就一定有两个主外键关系,所以在大家不是很熟悉连接查询时,首先要学会去除无用笛卡尔积,那么就是用主外键关系作为条件来处理。如果两张表的查询,那么至少有一个主外键条件,三张表连接至少有两个主外键条件,N张表连接至少有N-1个主外键条件

     

    1. 交叉连接查询(基本不会使用-得到的是两个表的乘积)

    语法:select * from A,B;

     

    2. 内连接查询(使用的关键字 inner join  -- inner可以省略)

    隐式内连接:select * from A,B where 条件;

    显示内连接:select * from A inner join B on 条件;

     

    3. 外连接查询(使用的关键字 outer join  --outer可以省略)

    左外连接:left outer join

    select * from A left outer join B on 条件;

     

    右外连接:right outer join

    select * from A right outer join B on 条件;

     


    什么时候使用连接查询?什么时候使用子查询?

    答:查询的是两张表及以上的信息,或者查询的是两个字段及以上的信息,这两个字段不在同一张表内。有时候是两个字段,有时候是两张表,总之是两个,而不是一个。

    子查询一般查的是一个字段的信息或一张表里面的信息,只是给的条件并不能直接映射到这张表,需要推出来。当没有给出任何条件时,查询的是两张表综合在一起的信息,并且两张表是主外键相关的。

    内连接就是前面所讲的连接查询,只是前面的省略了inner关键字而已。

     

    -- 多表查询方法要用distinct关键字去除重复数据 )(答案有争议)

    select distinct i.*

    from ajia_cart_item c,ajia_user u, ajia_item i

    where u.username='lisi' and c.user_id=u.id and c.item_id=i.id;

    【解析】

    这个题很重要,我研究了一个小时,总结如下:

    什么时候用子查询呢?查询的是一个字段,但是给的条件无法直接作用到这个条件上,需要中间推几张表才能作用上这个字段什么时候用多表查询呢?查询的是两个字段,但是这两个字段不在同一个表中,在两张表或三张表中,并且这几张表间具有主外键关联拿上面的题来说,lisi用户的购物车商品信息,它最终要查的是商品信息,一个字段,只是它给的条件无法直接用在这上面,需要推,怎么推呢?

    我知道用户的usernamelisi,通过ajia_user表,我可以得到用户编号,知道了用户编号,我通过ajia_cart_item这个表可以得到商品编号知道了商品编号,我就可以通过ajia_item表得到商品的信息。所以,我选择用子查询的方法。但是老师用的是多表查询的方法,这就出了一个问题,查出来的数据有大量的重复,所以我们要用distinct关键字来去除重复数据,不建议使用,或者直接就不对

     

    例子:

    1. 查询所有用户的订单信息包括没有下过订单的用户

    select u.id,u.username,o.* from ajia_user u left join ajia_order o on u.id=o.user_id;

     

    select u.id,u.username,o.* from ajia_user u,ajia_order o where u.id=o.user_id;(错误)

    -- 过滤掉了没有下订单的用户

     

    2. 查询所有用户的下订单数量

    -- 左外连接

    select u.id,u.username,count(o.order_id) 订单数量

    from ajia_user u left join ajia_order o on u.id=o.user_id

    group by u.id;

     

    -- 右外连接

    select u.id,u.username,count(o.order_id) 订单数量

    from ajia_order o right join ajia_user u

    on u.id=o.user_id

    group by u.id;

     

    3. 查找用户编号为14的商品收藏夹

    select u.id,u.username,c.*

    from ajia_user u left join ajia_collect_item c

    on u.id=c.user_id

    where u.id=14;

     

    4. 统计所有用户收藏夹中的商品个数

    select u.id,u.username,count(c.id)

    from ajia_user u left join ajia_collect_item c

    on u.id=c.user_id

    group by u.id;

     

    面试题:

    部门表(deptcode:部门编号,deptname:部门名称)

    员工表(humancode:员工编号,humanname:员工姓名,deptcode:部门编号)

    考核表(resultcode:结果编号,humancode:人员编号,kpicode:考核结果---分 不合格,合格,优秀)

    1. 统计各部门合格的人数;

    2. 统计考核结果情况;

    3. 统计部门考核结果情况;

    答:

    1.

    select count(*),C.deptname
    from 考核表 A
    inner join 员工表 B on A.humancode=B.humancode
    inner join 部门表 C on B.deptcode=C.deptcode
    where A.kpicode<>'不合格'
    group by ,C.deptname;

    2.

    select A.*,B.*,C.*
    from 考核表 A
    inner join 员工表 B on A.humancode=B.humancode
    inner join 部门表 C on B.deptcode=C.deptcode;

    3.

    select count(*),A.kpicode,C.deptname
    from 考核表 A
    inner join 员工表 B on A.humancode=B.humancode
    inner join 部门表 C on B.deptcode=C.deptcode
    group by ,C.deptname,A.kpicode;

     

     

     

    展开全文
  • MySQL中的连接查询

    千次阅读 2021-08-11 20:38:21
    连接的过程 在MySQL中,连接查询的语法很随意,只要在FROM语句后边跟多个表名 就好了,我们可以连接任意数量张表,但是如果没有任何限制条件的话,这些表连接 起来产生的笛卡尔积可能是非常巨大的。比方说3个100行...

    对于本文章,所用的表为student表和score表:

    
    mysql> create table student(id int, name varchar(20));
    mysql> insert into student values(1, "morris"), (2, "bob"), (3, "tom"), (4, "jack");
    mysql> create table score(id int, score int);
    mysql> insert into score values(1, 80), (2, 90), (4, 100);
    
    mysql> select * from student;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | morris |
    |    2 | bob    |
    |    3 | tom    |
    |    4 | jack   |
    +------+--------+
    4 rows in set (0.00 sec)
    
    mysql> select * from score;
    +------+-------+
    | id   | score |
    +------+-------+
    |    1 |    80 |
    |    2 |    90 |
    |    4 |   100 |
    +------+-------+
    3 rows in set (0.00 sec)
    

    笛卡尔积

    笛卡尔积原本是代数的概念,他的意思是对于两个不同的集合A,B。对于A中的每一个元素,都有对于在B中的所有元素做连接运算 。可以见得对于两个元素分别为m,n的表。笛卡尔积后得到的元素个数为m*n个元组。而对于mysql来说,默认的连接就是笛卡尔积连接。

    连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果
    集并返回给用户。

    mysql> select * from student,score;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    4 |   100 |
    |    1 | morris |    2 |    90 |
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    4 |   100 |
    |    2 | bob    |    2 |    90 |
    |    2 | bob    |    1 |    80 |
    |    3 | tom    |    4 |   100 |
    |    3 | tom    |    2 |    90 |
    |    3 | tom    |    1 |    80 |
    |    4 | jack   |    4 |   100 |
    |    4 | jack   |    2 |    90 |
    |    4 | jack   |    1 |    80 |
    +------+--------+------+-------+
    12 rows in set (0.00 sec)
    

    连接查询的结果集中包含一个表中的每一条
    记录与另一个表中的每一条记录相互匹配的组合,像这样的结果集就可以称之为
    笛卡尔积。因为表student中有4条记录,表score中有3条记录,所以这两个表连接
    之后的笛卡尔积就有3×4=12行记录。

    连接的过程

    在MySQL中,连接查询的语法很随意,只要在FROM语句后边跟多个表名
    就好了,我们可以连接任意数量张表,但是如果没有任何限制条件的话,这些表连接
    起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产
    生的笛卡尔积就有100×100×100=1000000行数据!所以在连接的时候过滤掉特
    定记录组合是有必要的。

    在连接查询中的过滤条件可以分成两种,比方说下边这
    个查询语句:

    
    mysql> select * from student st,score sc where st.id > 2 and st.id = sc.id and sc.score >= '80';
    
    • 涉及单表的条件:st.id > 2、sc.score >= ‘80’

    • 涉及两表的条件
      :st.id = sc.id

    那么这个连接查询的大致执行过程如下:

    1. 首先确定第一个需要查询的表,这个表称之为驱动表。单表中执行
      查询语句只需要选取代价最小的那种访问方法去执行单表查询语句就好了(就是
      说从 const、ref、ref_or_null、range、index、all等等这些执行方法中选取代价最
      小的去执行查询)。
      此处假设使用student作为驱动表,那么就需要到student表中找满足st.name = 'morris’的记
      录,因为表中的数据太少,我们也没在表上建立二级索引,所以此处查询student表
      的访问方法就设定为all,也就是采用全表扫描的方式执行单表查询。
      很明显,student表中符合st.name = 'morris’的记录有2条。

    2. 针对上一步骤中从驱动表产生的结果集中的每一条记录,分别需要
      到score表中查找匹配的记录,所谓匹配的记录,指的是符合过滤条件的记录。因
      为是根据student表中的记录去找score表中的记录,所以score表也可以被称之为被驱动
      。上一步骤从驱动表中得到了2条记录,所以需要查询2次score表。此时涉及
      两个表的列的过滤条件st.id = sc.id就派上用场了。

    从上边两个步骤可以看出来,这个两表连接查询共需要查询1次student表,2
    次score表。也就是说在两表连接查询中,驱动表只需要访问一次,被驱动表可能被访问多次。

    外连接

    左外连接

    左(外)连接的语法还是挺简单的,比如我们要把e1表和e2表进行左外连
    接查询可以这么写:

    SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条
    件];
    

    其中中括号里的OUTER单词是可以省略的。对于LEFT JOIN类型的连接来说,
    我们把放在左边的表称之为外表或者驱动表,右边的表称之为内表或者被驱动表。
    所以上述例子中e1就是外表或者驱动表,e2就是内表或者被驱动表。需要注意
    的是,对于左(外)连接和右(外)连接来说,必须使用ON子句来指出连接条
    件。

    
    mysql> select * from student st left join score sc on st.id = sc.id;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    2 |    90 |
    |    3 | tom    | NULL |  NULL |
    |    4 | jack   |    4 |   100 |
    +------+--------+------+-------+
    4 rows in set (0.00 sec)
    

    在这里插入图片描述

    右外连接

    右(外)连接和左(外)连接的原理是一样的,语法也只是把LEFT换成RIGHT
    而已:

    SELECT * FROM e1 RIGHT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤
    条件];
    

    只不过驱动表是右边的表e2,被驱动表是左边的表e1。

    
    mysql> select * from student st right join score sc on st.id = sc.id;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    2 |    90 |
    |    4 | jack   |    4 |   100 |
    +------+--------+------+-------+
    3 rows in set (0.00 sec)
    

    在这里插入图片描述

    全连接

    MySQL不支持全连接。一定要使用的话可以用左连接+右连接+去重=全连接。

    在这里插入图片描述

    内连接

    内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接
    条件时不会把该记录加入到最后的结果集,一种最简单的内连接语法,就是直接
    把需要连接的多个表都放到FROM子句后边。其实针对内连接,MySQL 提供了
    好多不同的语法:

    SELECT * FROM e1 [INNER | CROSS] JOIN e2 [ON 连接条件] [WHERE 普通过滤
    条件];
    

    也就是说在 MySQL 中,下边这几种内连接的写法都是等价的:

    SELECT * FROM e1 JOIN e2;
    SELECT * FROM e1 INNER JOIN e2;
    SELECT * FROM e1 CROSS JOIN e2;
    

    上边的这些写法和直接把需要连接的表名放到FROM语句之后,用逗号,分
    隔开的写法是等价的:

    SELECT * FROM e1, e2;
    

    再说一次,由于在内连接中ON子句和WHERE子句是等价的,所以内连接
    中不要求强制写明ON子句。

    我们前边说过,连接的本质就是把各个连接表中的记录都取出来依次匹配的
    组合加入结果集并返回给用户。不论哪个表作为驱动表,两表连接产生的笛卡尔
    积肯定是一样的。而对于内连接来说,由于凡是不符合ON子句或WHERE子句
    中的条件的记录都会被过滤掉,其实也就相当于从两表连接的笛卡尔积中把不符
    合过滤条件的记录给踢出去,所以对于内连接来说,驱动表和被驱动表是可以互
    换的,并不会影响最后的查询结果。

    但是对于外连接来说,由于驱动表中的记录即使在被驱动表中找不到符合
    ON子句条件的记录时也要将其加入到结果集,所以此时驱动表和被驱动表的关
    系就很重要了,也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

    
    mysql> select * from student st,score sc where st.id = sc.id;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    2 |    90 |
    |    4 | jack   |    4 |   100 |
    +------+--------+------+-------+
    3 rows in set (0.00 sec)
    

    在这里插入图片描述

    展开全文
  • MYSQL中的连接查询

    千次阅读 2022-03-02 17:50:56
    时查询学⽣的班级信息),可以通过连接查询从多张数据表提取数据: 在MySQL中可以使⽤join实现多表的联合查询——连接查询, join按照其功能不同分为 三个操作: inner join 内连接 left join 左连接 right join 右...

    通过对DQL的学习,我们可以很轻松的从⼀张数据表中查询出需要的数据;在企业的应
    ⽤开发中,我们经常需要从多张表中查询数据(例如:我们查询学⽣信息的时候需要同
    时查询学⽣的班级信息),可以通过连接查询从多张数据表提取数据:
    在MySQL中可以使⽤join实现多表的联合查询——连接查询, join按照其功能不同分为
    三个操作:
    inner join 内连接
    left join 左连接
    right join 右连接

    1.数据准备

    1.1 创建数据表

    创建班级信息表 和 学⽣信息表

    create table classes(
    class_id int primary key auto_increment,
    class_name varchar(40) not null unique,
    class_remark varchar(200)
    );
    create table students(
    stu_num char(8) primary key,
    stu_name varchar(20) not null,
    stu_gender char(2) not null,
    stu_age int not null,
    cid int,
    constraint FK_STUDENTS_CLASSES foreign key(cid) references
    classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE
    );
    

    1.2 添加数据

    添加班级信息

    # Java2104 包含三个学⽣信息
    insert into classes(class_name,class_remark) values('Java2104','...');
    # Java2105 包含两个学⽣信息
    insert into classes(class_name,class_remark) values('Java2105','...');
    # 以下两个班级在学⽣表中没有对应的学⽣信息
    insert into classes(class_name,class_remark) values('Java2106','...');
    insert into classes(class_name,class_remark) values('Python2105','...');
    

    添加学生信息

    # 以下三个学⽣信息 属于 class_id=1 的班级 (Java2104)
    insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
    values('20210101','张三','男',20,1);
    insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
    values('20210102','李四','⼥',20,1);
    insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
    values('20210103','王五','男',20,1);
    # 以下三个学⽣信息 属于 class_id=2 的班级 (Java2105)
    insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
    values('20210104','赵柳','⼥',20,2);
    insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
    values('20210105','孙七','男',20,2);
    # ⼩红和⼩明没有设置班级信息
    insert into students(stu_num,stu_name,stu_gender,stu_age)
    values('20210106','⼩红','⼥',20);
    insert into students(stu_num,stu_name,stu_gender,stu_age)
    values('20210107','⼩明','男',20);
    

    2.内连接 INNER JOIN

    语法

    select ... from tableName1 inner join tableName2 ON 匹配条件 [where 条件];
    

    2.1 笛卡尔积

    1.笛卡尔积(A集合&B集合):使⽤A中的每个记录⼀次关联B中每个记录,笛卡尔积的总数=A总数*B总数
    2.如果直接执⾏ select … from tableName1 inner join tableName2; 会获取两种数
    据表中的数据集合的笛卡尔积(依次使⽤tableName1 表中的每⼀条记录 去 匹配
    tableName2的每条数据)

    2.2 内连接条件

    两张表时用inner join 连接查询之后生成的笛卡尔积数据中很多数据都是无意义的,我们如何消除无意义的数据呢? --添加进行连接查询是的条件
    1.使用 on 设置两张表连接查询的匹配条件

    -- 使⽤where设置过滤条件:先⽣成笛卡尔积再从笛卡尔积中过滤数据(效率很低)
    select * from students INNER JOIN classes where students.cid =
    classes.class_id;
    -- 使⽤ON设置连接查询条件:先判断连接条件是否成⽴,如果成⽴两张表的数据进⾏组合⽣成⼀
    条结果记录
    select * from students INNER JOIN classes ON students.cid =
    classes.class_id;
    

    结果:只获取两种表中匹配条件成⽴的数据,任何⼀张表在另⼀种表如果没有找到对应
    匹配则不会出现在查询结果中(例如:⼩红和⼩明没有对应的班级信息, Java2106和
    Python2106没有对应的学⽣

    3.左连接 LIFT JOIN

    需求:请查询出所有的学⽣信息,如果学⽣有对应的班级信息,则将对应的班级信息也
    查询出来
    左连接:显示左表中的所有数据,如果在有右表中存在与左表记录满⾜匹配条件的数据,则进⾏匹配;如果右表中不存在匹配数据,则显示为Null

    # 语法
    select * from leftTabel LEFT JOIN rightTable ON 匹配条件 [where 条件];
    -- 左连接 : 显示左表中的所有记录
    select * from students LEFT JOIN classes ON students.cid =
    classes.class_id;
    

    在这里插入图片描述

    4.右连接 RIGHT JOIN

    -- 右连接 :显示右表中的所有记录
    select * from students RIGHT JOIN classes ON students.cid =
    classes.class_id;
    

    在这里插入图片描述

    5.数据表别名

    如果在连接查询的多张表中存在相同名字的字段,我们可以使⽤ 表名.字段名 来进⾏区
    分,如果表名太⻓则不便于SQL语句的编写,我们可以使⽤数据表别名
    示例:

    select s.*,c.class_name
    from students s
    INNER JOIN classes c
    ON s.cid = c.class_id;
    

    6.子查询/嵌套查询

    ⼦查询 — 先进⾏⼀次查询,第⼀次查询的结果作为第⼆次查询的源/条件(第⼆次查询
    是基于第⼀次的查询结果来进⾏的)

    6.1 子查询返回单个值—单行单列

    案例1: 查询班级名称为’Java2104’班级中的学⽣信息 (只知道班级名称,⽽不知道班级ID)
    1.传统的方式

    -- a.查询Java2104班的班级编号
    select class_id from classes where class_name='Java2104';
    -- b.查询此班级编号下的学⽣信息
    select * from students where cid = 1;
    

    2.子查询

    -- 如果⼦查询返回的结果是⼀个值(单列单⾏),条件可以直接使⽤关系运算符(= !=
    ....)
    select * from students where cid = (select class_id from classes where
    class_name='Java2105');
    

    6.2 子查询返回多个值—多行多列

    案例2: 查询所有Java班级中的学⽣信息

    1. 传统的方式:
    -- a.查询所有Java班的班级编号
    select class_id from classes where class_name LIKE 'Java%';
    +--------------+
    | class_id |
    +--------------+
    | 1 |
    | 2 |
    | 3 |
    +--------------+
    -- b.查询这些班级编号中的学⽣信息(union 将多个查询语句的结果整合在⼀起)
    select * from students where cid=1
    UNION
    select * from students where cid=2
    UNION
    select * from students where cid=3;
    

    2.子查询

    -- 如果⼦查询返回的结果是多个值(单列多⾏),条件使⽤IN / NOT IN
    select * from students where cid IN (select class_id from classes where
    class_name LIKE 'Java%');
    

    案例3: 查询cid=1的班级中性别为男的学⽣信息

    -- 多条件查询:
    select * from students where cid=1 and stu_gender='男';
    -- ⼦查询:先查询cid=1班级中的所有学⽣信息,将这些信息作为⼀个整体虚拟表(多⾏多列)
    -- 再基于这个虚拟表查询性别为男的学⽣信息(‘虚拟表’需要别名)
    select * from (select * from students where cid=1) t where
    t.stu_gender='男';
    
    展开全文
  • 本实验要求掌握:①简单表的数据查询时数据连接查询的操作方法 ②简单表的数据排序时数据连接查询的操作方法 ③简单表的多表操作时数据连接查询的操作方法 二、 实验内容及结果 任务1 在数据库JWGL中的学生表Student...
  • 【MySQL】连接查询的本质

    万次阅读 2022-01-01 11:31:19
    了解连接、内连接、外连接这些基本概念后,我们需要理解MySQL怎么样来进行表与表之间的连接,才能明白为什么有的连接查询运行的快,有的却慢。 嵌套循环连接(Nested-Loop Join) 我们前边说过,对于两表连接来说,...
  • Mysql—— 内连接、左连接、右连接以及全连接查询

    万次阅读 多人点赞 2018-10-05 23:03:31
    一、内连接查询 inner join 关键字:inner join on 语句:select * from a_table a inner join b_table b on a.a_id = b.b_id; 说明:组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集...
  • 文章目录内连接自然连接和等值连接的区别内连接的实现方式外连接连接连接连接连接连接INNERJOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句...
  • 连接查询和子查询哪个效率高

    万次阅读 多人点赞 2021-03-04 00:44:12
    需要进行多表查询的情况下,用连接查询和子查询哪个效率高? 1、什么是子查询?举个简单的例子,那么子查询有什么优劣呢? 子查询 (内查询) 在主查询之前一次执行完成。 子查询的结果被主查询(外查询)使用 。 可以...
  • 数据库的几种连接查询

    千次阅读 2021-01-28 05:37:14
    1:连接查询2: join_type 指出连接类型3: 内连接4: 交叉连接1:连接查询通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型 数据库管理系统的一个标志。在关系数据库管理...
  • 数据库-连接查询

    千次阅读 2021-12-06 14:57:39
    数据库连接查询可以进行多表联查,可以同时展示多个表的数据,方便进行查阅。那么连接查询分为多种方式,我们可以根据要求筛选合适的方式进行查询。下面我们来讲解一下怎么进行多表联查。 首先连接查询大致分为3种...
  • 连接(Semi Join)返回左表中与右表至少匹配一次的数据行,通常体现为 EXISTS 或者 IN 子查询,反连接(Anti Join)返回左表中与右表不匹配的数据行,通常体现为 NOT EXISTS 或者 NOT IN 子查询
  • SQL中的内连接查询和外连接查询

    万次阅读 多人点赞 2019-03-14 21:31:00
    在项目开发中,单表的查询肯定无法满足所有的业务,所以很多时候都是多表的连接查询。接下来总结一下SQL中的多表关联查询,以MySQL为例。 前期准备 在数据库中创建以下几张表并添加一些数据。 1、部门表(tb_dept)...
  • 连接是关系数据模型的主要特点,连接查询是关系数据库中最主要的查询,包括内连接、外连接等。 当两个或多个表中存在相同意义的字段时,便可以通过这些字段对不同的表进行 连接查询,得到存放在多个表中的记录数据。...
  • 【MySQL】多表联合查询、连接查询、子查询

    万次阅读 多人点赞 2019-06-03 17:08:20
    文章目录 【1】连接查询连接查询连接查询 左连接 右连接 【2】联合查询 【3】子查询 带in关键字的子查询 带比较运算符的子查询 带exists的子查询 带any关键字的子查询 带all关键字的子查询 【1】连接查询 连接...
  • 单表查询(1)选择表中的若干列① 查询指定列② 查询全部列③ 查询经过计算的值❶ 算术表达式❷ 字符串常量及函数❸ 使用列别名改变查询结果的列标题(2)选择表中的若干元组(行)① 关键词DISTINCT去掉表中重复的...
  • SQL(三)- 连接查询

    千次阅读 2021-07-19 10:28:35
    连接查询概念 一、什么是连接查询? 在实际开发中,大部分的情况下都不是从单张表中查询数据,一般都是多张表联合查询最终取出最终结果。 在实际再发中,一般一个业务都会对应多张表,比如学生和班级,最起码两张表...
  • 1.掌握涉及一个以上数据表的查询方法。 2.掌握等值连接 3.掌握自然连接 4.掌握非等值连接 5.掌握自身连接、外连接和复合条件连接 本次实验sql脚本: INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF...
  • 假设A和B进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询,这就是内连接。 AB两张表没有主副之分,两张表是平等的。 外连接: 假设A和B进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是...
  • Mysql表关系 连接查询

    千次阅读 2022-06-15 09:05:09
    连接查询只会查找到符合条件的记录,其实结果和表关联查询是一样的,官方更推荐使用内连接查询。 左连接 左表全部显示,显示右表中与左表匹配的项 右连接 右表全部显示,显示左表中与右表匹配的项...
  • MySQL数据库连接查询(内连接)

    千次阅读 2021-05-31 21:50:54
    连接查询 1. 交叉连接(笛卡尔积)Cross Join 实例1.1:完成车辆表与线路表的交叉连接 语法:SELECT 字段列表 FROM 表名1 CROSS JOIN 表名2 要点:使用select语句查询 通过from 表名1 CROSS JOIN 表名2 实现交叉连接...
  • 数据查询--连接查询

    千次阅读 2020-08-17 16:46:31
    连接查询 SC表:(下面例子会用到这些表) Student表: 连接查询:同时涉及多个表的查询 连接条件或连接谓词:是用来连接两个表 一般格式: [<表名1>.]<列名1><比较运算符>[<表名2>.]<...
  • MySQL数据库 - 连接查询

    千次阅读 2021-01-20 12:18:35
    第1关:内连接查询 任务描述 本关任务:使用内连接查询数据表中学生姓名和对应的班级。 相关知识 为了完成本关任务,你需要掌握: 1.什么是内连接查询; 2.如何使用内连接查询。 内连接查询 仅将两个表中满足连接...
  • 实验3 连接查询和嵌套查询

    千次阅读 2021-05-13 19:12:57
    2.熟练掌握简单表的数据连接查询和嵌套查询的操作方法。 实验内容: 创建教学管理数据库“JXGL”,在“JXGL”数据库中创建3-2中的三张表并添加数据,实现数据的单表查询操作。 实验步骤: 写出下列操作的SQL语句 在...
  • mysql左右连接查询(有示例图)

    千次阅读 2021-02-01 18:40:39
    小小的连接查询,其实里面有很多学问,今天我就来简单剖析一下。左连接,右连接,内连接的本质:将两个表的数据依据一定条件横向连接起来。给出建表语句:--------------------------------------------------create...
  • SQL连接查询和嵌套查询详解

    千次阅读 2020-08-19 14:30:02
    SQL连接查询和嵌套查询详解 连接查询 若一个查询同时涉及两个或两个以上的表,则称之为连接查询连接查询是数据库中最最要的查询, 包括1、等值与非等值连接查询2、自然连接查询 1、等值连接查询 2、自然连接查询 3...
  • mybatis-plus 连接查询分页

    千次阅读 2021-06-05 22:34:02
    这篇简单讲讲连接查询分页 表数据在我这篇博客有 mybatis 多表连接查询,万能map 多表查询分页,其实和单表查询类似,只是换个泛型和方法,当然你不写泛型也可以,毕竟他只是泛型,不会影响代码执行 单表分页 使用 ...
  • MySQL连接查询

    千次阅读 2019-05-08 23:43:02
    首先创建两个表 ...1.内连接查询(INNOR JOIN) 使用普通sql语句 select fruits.id,name,price,num from fruits,orders where fruits.id=orders.id; 使用内连接查询语句(结果与上图相同) s...
  • 多表连接查询

    千次阅读 2021-01-17 16:52:08
    一、多表查询的基础语法 SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column; 二、表与表之间的连接 在Oracle中主要有如下3种类型的连接: 1、内连接,这种连接但会即...
  • MySQL (内、左、右)连接查询一、MySQL内连接查询内连接的语法结构实例操作二、MySQL左连接查询左连接的语法结构实例操作三、MySQL右连接查询右连接的语法结构实例操作 一、MySQL内连接查询 内连接的语法结构 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,757,312
精华内容 702,924
关键字:

连接查询