精华内容
下载资源
问答
  • 多表查询

    万次阅读 2017-09-10 12:50:23
    员工和部门 #建表 create table department( id int, name varchar(20) ); create table employee1( id int primary key auto_increment, name varchar(20), sex enum('male','female') not

    一、介绍

    首先先准备表

    员工表和部门表

    #建表
    create table department(
    id int,
    name varchar(20) 
    );
    
    create table employee1(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
    );
    
    #插入数据
    insert into department values
    (200,'技术'),
    (201,'人力资源'),
    (202,'销售'),
    (203,'运营');
    
    insert into employee1(name,sex,age,dep_id) values
    ('egon','male',18,200),
    ('alex','female',48,201),
    ('wupeiqi','male',38,201),
    ('yuanhao','female',28,202),
    ('liwenzhou','male',18,200),
    ('jingliyang','female',18,204)
    ;

    查看表:

    二、多表连接查询

    1.交叉连接:不适用任何匹配条件。生成笛卡尔积、

    select * from employee1 ,department;

    2.内连接:找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。(只连接匹配的行

    #找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果
    #department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来
    select * from employee1,department where employee1.dep_id=department.id;
    
    #上面用where表示的可以用下面的内连接表示,建议使用下面的那种方法
    select * from employee1 inner join department on employee1.dep_id=department.id;

    #也可以这样表示哈
    select employee1.id,employee1.name,employee1.age,employee1.sex,department.name from 
    employee1,department where employee1.dep_id=department.id;

     

    注意:内连接的join可以忽略不写,但是还是加上看起来清楚点

    3.左连接:优先显示左表全部记录。

    #左链接:在按照on的条件取到两张表共同部分的基础上,保留左表的记录
    select * from employee1 left join department on department.id=employee1.dep_id;
    select * from department left join employee1 on department.id=employee1.dep_id;

     

    4.右链接:优先显示右表全部记录。

    #右链接:在按照on的条件取到两张表共同部分的基础上,保留右表的记录
    select * from employee1 right join department on department.id=employee1.dep_id;
    select * from department right join employee1 on department.id=employee1.dep_id;

    5.全外连接:显示左右两个表的全部记录。

    注意:mysql不支持全外连接 full join
    强调:mysql可以使用union间接实现全外连接
    select * from employee1 left join department on department.id=employee1.dep_id
    union
    select * from employee1 right join department on department.id=employee1.dep_id; 

    三、符合条件连接查询

    示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,
    即找出公司所有部门中年龄大于25岁的员工
    select * from employee1 inner join department on employee1.dep_id=department.id 

    and age>25;

     

    示例2:以内连接的方式查询employee和department表,并且以age字段的升序方式显示
    select * from employee1 inner join department on employee1.dep_id=department.id 
    =

    and age>25 and age>25 order by age asc;

    四、子查询

    #1:子查询是将一个查询语句嵌套在另一个查询语句中。
    #2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
    #3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
    #4:还可以包含比较运算符:= 、 !=、> 、<等

    小练习

    #查询平均年龄在25岁以上的部门名
    select name from department where id in (
    select dep_id from employee group by dep_id having avg(age) > 25
    );
    #查看技术部员工姓名
    select name from employee where dep_id = (select id from department where name='技术');
    
    #查看小于2人的部门名
    select name from department where id in (
    select dep_id from employee1 group by dep_id having count(id) < 2
    )
    union
    select name from department where id not in (select distinct dep_id from employee1);
    
    #提取空部门                              #有人的部门
    select * from department where id not in (select distinct dep_id from employee1);
    
    
    或者:
    select name from department where id in
    (
    select dep_id from employee1 group by dep_id having count(id) < 2
    union
    select id from department where id not in (select distinct dep_id from employee1);
    );

    五、综合练习

    一、SELECT语句关键字的定义顺序

    1 SELECT DISTINCT <select_list>
    2 FROM <left_table>
    3 <join_type> JOIN <right_table>
    4 ON <join_condition>
    5 WHERE <where_condition>
    6 GROUP BY <group_by_list>
    7 HAVING <having_condition>
    8 ORDER BY <order_by_condition>
    9 LIMIT <limit_number>
    SELECT语句关键字的定义顺序

    二 、SELECT语句关键字的执行顺序

    (7)     SELECT 
    (8)     DISTINCT <select_list>
    (1)     FROM <left_table>
    (3)     <join_type> JOIN <right_table>
    (2)     ON <join_condition>
    (4)     WHERE <where_condition>
    (5)     GROUP BY <group_by_list>
    (6)     HAVING <having_condition>
    (9)     ORDER BY <order_by_condition>
    (10)    LIMIT <limit_number>

    init.sql文件内容

    View Code

     从init.sql文件中导入数据

    #准备表、记录
    mysql> create database db1;
    mysql> use db1;
    mysql> source /root/init.sql
     1 1、查询所有的课程的名称以及对应的任课老师姓名
     2 
     3 2、查询学生表中男女生各有多少人
     4 
     5 3、查询物理成绩等于100的学生的姓名
     6 
     7 4、查询平均成绩大于八十分的同学的姓名和平均成绩
     8 
     9 5、查询所有学生的学号,姓名,选课数,总成绩
    10 
    11 6、 查询姓李老师的个数
    12 
    13 7、 查询没有报李平老师课的学生姓名
    14 
    15 8、 查询物理课程比生物课程高的学生的学号
    16 
    17 9、 查询没有同时选修物理课程和体育课程的学生姓名
    18 
    19 10、查询挂科超过两门(包括两门)的学生姓名和班级
    20 、查询选修了所有课程的学生姓名
    21 
    22 12、查询李平老师教的课程的所有成绩记录
    23  
    24 13、查询全部学生都选修了的课程号和课程名
    25 
    26 14、查询每门课程被选修的次数
    27 
    28 15、查询之选修了一门课程的学生姓名和学号
    29 
    30 16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
    31 
    32 17、查询平均成绩大于85的学生姓名和平均成绩
    33 
    34 18、查询生物成绩不及格的学生姓名和对应生物分数
    35 
    36 19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名
    37 
    38 20、查询每门课程成绩最好的前两名学生姓名
    39 
    40 21、查询不同课程但成绩相同的学号,课程号,成绩
    41 
    42 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称;
    43 
    44 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名;
    45 
    46 24、任课最多的老师中学生单科成绩最高的学生姓名
    题目

    答案待续。。。。

     

    展开全文
  • mysql进阶(九)多表查询

    万次阅读 2015-07-27 20:29:24
    MySQL多表查询 一使用SELECT子句进行多表查询 SELECT字段名FROM表1,表2…WHERE表1.字段=表2.字段AND其它查询条件 SELECTa.id,a.name,a.address,a.date,b.math,b.english,b.chineseFROMtb_demo065_telASb,tb_demo065...

    MySQL多表查询

    一 使用SELECT子句进行多表查询

    SELECT 字段名 FROM 表1,表2 … WHERE 表1.字段 = 表2.字段 AND 其它查询条件

    SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo065 AS a WHERE a.id=b.id

    注:在上面的的代码中,以两张表的id字段信息相同作为条件建立两表关联,但在实际开发中不应该这样使用,最好用主外键约束来实现

    二 使用表的别名进行多表查询

    如:SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065  a,tb_demo065_tel  b WHERE a.id=b.id AND b.id='$_POST[textid]'

    SQL语言中,可以通过两种方式为表指定别名 

    第一种是通过关键字AS指定,如

    SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065 AS a,tb_demo065_tel AS b WHERE a.id=b.id

    第二种是在表名后直接加表的别名实现

    SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065  a,tb_demo065_tel  b WHERE a.id=b.id 

    使用表的别名应注意几下几点

    (1)别名通常是一个缩短了的表名,用于在连接中引用表中的特定列,如果连接中的多个表中有相同的名称列存在,必须用表名或表的别名限定列名

    (2)如果定义了表的别名就不能再使用表名

    三 合并多个结果集

    SQL语言中,可以通过UNION 或 ALL将多个SELECT语句的查询结果合并输出,这两个关键字的使用说明如下:

    UNION:利用该关键字可以将多个SELECT 语句的查询结果合并输出,并删除重复行

    ALL:利用该关键字可以将多个SELECT 语句的查询结果合并输出,但不会删除重复行

    在使用UNION或ALL关键字将多个表合并输出时,查询结果必须具有相同的结构并且数据类型必须兼容,另外使用UNION时两张表的字段数量也必须相同,否则会提示SQL语句有错误。

    e.x:SELECT id,name,pwd FROM tb_demo067 UNION SELECT  uid,price,date FROM tb_demo067_tel

    四 简单嵌套查询

    子查询:子查询是一个SELECT查询,返回单个值且嵌套在SELECT、INSERT、UPDATE和DELETE语句或其它查询语句中,任何可以使用表达式的地方都可以使用子查询.

    SELECT id,name,sex,date FROM tb_demo068 WHERE id in(SELECT id FROM tb_demo068 WHERE id='$_POST[test]')

    内连接:把查询结果作为WHERE子句的查询条件即称为内连接

    五 复杂的嵌套查询

    多表之间的嵌套查询可以通过谓词IN实现,语法格式如下:

    test_expression[NOT] IN{

     subquery

    }

    参数说明:test_expression指SQL表达式,subquery包含某结果集的子查询

    多表嵌套查询的原理:无论是多少张表进行嵌套,表与表之间一定存在某种关联,通过WHERE子句建立此种关联实现查询

    六 嵌套查询在查询统计中的应用

    实现多表查询时,可以同时使用谓词ANY、SOME、ALL,这些谓词被称为定量比较谓词,可以和比较运算符联合使用,判断是否全部返回值都满足搜索条件.SOME和ANY谓词是存在量的,只注重是否有返回值满足搜索条件,这两个谓词的含义相同,可以替换使用;ALL谓词称为通用谓词,它只关心是否有谓词满足搜索要求.

    SELECT * FROM tb_demo069_people WHERE uid IN(SELECT deptID FROM tb_demo069_dept WHERE deptName='$_POST[select]')

    SELECT a.id,a.name FROM tb_demo067 AS a WHERE id<3)

    >ANY 大于子查询中的某个值 

     

    >=ANY 大于等于子查询中的某个值  

    <=ANY 小于等于子查询中的某个值 

    =ANY 等于子查询中的某个值  

    !=ANY或<>ANY 不等于子查询中的某个值  

    >ALL 大于子查询中的所有值  

     

    >=ALL 大于等于子查询中的所有值 

    <=ALL 小于等于子查询中的所有值 

    =ALL 等于子查询中的所有值 

    !=ALL或<>ALL 不等于子查询中的所有值

    七 使用子查询作派生的表

    在实际项目开发过程中经常用到从一个信息较为完善的表中派生出一个只含有几个关键字段的信息表,通过子查询就可以来实现这一目标,如

    SELECT people.name,people.chinese,people.math,people.english FROM (SELECT name,chinese,math,english FROM tb_demo071) AS people

    注:子查询应遵循以下规则:

    (1)由比较运算符引入的内层子查询只包含一个表达式或列名,在外层语句中的WHERE子句内命名的列必须与内层子查询命名的列兼容

    (2)由不可更改的比较运算符引入的子查询(比较运算符后面不跟关键字ANY或ALL)不包括GROUP BY 或 HAVING子句,除非预先确定了成组或单个的值

    (3)用EXISTS引入的SELECT列表一般都由*组成,不必指定列名

    (4)子查询不能在内部处理其结果

    八 使用子查询作表达式

    SELECT (SELECT AVG(chinese)FROM tb_demo071),(SELECT AVG(english)FROM tb_demo071),(SELECT AVG(math)FROM tb_demo071) FROM tb_demo071

    注:在使用子查询时最好为列表项取个别名,这样可以方便用户在使用mysql_fetch_array()函数时为表项赋值,如

    SELECT (SELECT AVG(chinese) FROM tb_demo071) AS yuwen ,(SELECT AVG(english) FROM tb_demo071) AS yingyu,(SELECT AVG(math) FROM tb_demo071) AS shuxue FROM tb_demo071

    九 使用子查询关联数据

    SELECT * FROM tb_demo072_student WHERE id=(SELECT id FROM tb_demo072_class WHERE className = '$_POST[text]')

    十 多表联合查询

    利用SQL语句中的UNION,可以将不同表中符合条件的数据信息显示在同一列中。

    e.x:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten

    注:使用UNION时应注意以下两点:

    (1)在使用UNION运算符组合的语句中,所有选择列表的表达式数目必须相同,如列名、算术表达式及聚合函数等

    (2)在每个查询表中,对应列的数据结构必须一样。

    十一 对联合后的结果进行排序

    为了UNION的运算兼容,要求所有SELECT语句都不能有ORDER BY语句,但有一种情况例外,那就是在最后一个SELECT语句中放置ORDER BY 子句实现结果的最终排序输出。

    e.x:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten ORDER BY id

    使用UNION条件上相对比较苛刻,所以使用此语句时一定要注意两个表项数目和字段类型是否相同

    十二 条件联合语句

    SELECT * FROM tb_demo076_BEIJING GROUP BY name HAVING name='人民邮电出版社' OR name='机械工业出版社' UNION SELECT * FROM tb_demo076_BEIJING GROUP BY name HAVING name <>'人民邮电出版社' AND name <>'机械工业再版社' ORDER BY id

    上面语句应用了GROUP BY分组语句和HAVING语句实现条件联合查询。其实现目的是先保证将'人民邮电出版社'和'机械工业出版社'始终位于名单最前列,然后再输出其它的出版社

    十三 简单内连接查询

    SELECT filedlist FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column1

    其中,filedlist是要显示的字段,INNER表示表之间的连接方式为内连接,table1.column1=table2.column1用于指明两表间的连接条件,如:

    SELECT a.name,a.address,a.date,b.chinese,b.math,b.english FROM tb_demo065 AS a INNER JOIN tb_demo065_tel AS b on a.id=b.id

    十四 复杂内连接查询

    复杂的内连接查询是在基本的内连接查询的基础上再附加一些查询条件,如:

    SELECT a.name,a.address,a.date,b.chinese,b.math,b.english FROM tb_demo065 AS a INNER JOIN tb_demo065_tel AS b on a.id=b.id WHERE b.id=(SELECT id FROM  tb_demo065 WHERE tb_demo065.name='$_POST[text]')

    总之,实现表与表之间的关联的本质是两表之间存在共同的数据项或者相同的数据项,通过WHERE 子句或内连接INNER JOIN … ON 语句将两表连接起来,实现查询

    十五 使用外连接实现多表联合查询

    (1)LEFT OUTER JOIN表示表之间通过左连接方式相互连接,也可简写成LEFT JOIN,它是以左侧的表为基准故称左连接,左侧表中所有信息将被全部输出,而右侧表信息则只会输出符合条件的信息,对不符合条件的信息则返回NULL

    e.x:SELECT a.name,a.address,b.math,b.english FROM tb_demo065 AS A LEFT OUTER JOIN tb_demo065_tel AS b ON a.id=b.id

    (2)RIGHT OUTER JOIN表示表之间通过右连接方式相互连接,也可简写成RIGHT JOIN,它是以右侧的表为基准故称右连接,右侧表中所有信息将被全部输出,而左侧表信息则只会输出符合条件的信息,对不符合条件的信息则返回NULL

    E.X:SELECT a.name,a.address,b.math,b.english FROM tb_demo065 AS A RIGHT OUTER JOIN tb_demo065_tel AS b ON a.id=b.id

    十六 利用IN或NOTIN关键字限定范围

    e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code BETWEEN '$_POST[text1]' AND '$_POST[text2]')

    利用IN可指定在范围内查询,若要求在某范围外查询可以用NOT IN代替它

    十七 由IN引入的关联子查询

    e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code = '$_POST[text]')

    十八 利用HAVING语句过滤分组数据

    HAVING子句用于指定组或聚合的搜索条件,HAVING通常与GROUP BY 语句一起使用,如果SQL语句中不含GROUP BY子句,则HAVING的行为与WHERE子句一样.

    e.x:SELECT name,math FROM tb_demo083 GROUP BY id HAVING math > '95'

     

    展开全文
  • 数据库中的多表查询总结

    万次阅读 多人点赞 2018-08-31 21:12:56
    在数据库的查询中,多表连接查询是一大难点,也是多表查询里的重点。连接主要有以下四种情况: INNER JOIN(内连接):如果表中有至少一个匹配,则返回行 【在语法中可以省略INNER关键字】 LEFT JOIN(左连接):...

    数据库在单个表里操作其实很简答,但是涉及在多张表里寻找数据的时候,难度会大大增加,这里解释一些多表联合查询常用的操作。

    一、join操作

    在数据库的查询中,多表连接查询是一大难点,也是多表查询里的重点。连接主要有以下四种情况:

    • INNER JOIN(内连接):如果表中有至少一个匹配,则返回行 【在语法中可以省略INNER关键字】
    • LEFT JOIN(左连接):从左表返回所有的行,如果右表中没有匹配,对应的列返回Null
    • RIGHT JOIN(右连接):从右表返回所有的行 ,如果左表中没有匹配,对应的列返回Null
    • FULL JOIN(全连接):只要其中一个表中存在匹配,则返回行(即结合左连接和右连接的结果)

    这里主要要理清两个问题:1)以哪个表为基础(从哪个表返回);2)遇到没有匹配的怎么处理。

    以下是各个连接方式的关系图:
    这里写图片描述

    对于学生表,课程表两张表来说,分别进行四种连接方式,结果如下:
    在这里插入图片描述

    注:

    1. 实际中,最常用的是inner join
    2. 在有些语句里应用了“,”隔开两张表,它的作用相当于inner join

    二、union操作

    UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
    请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。

    这是在展示结果的时候使用。
    比如,还是上边的两张表,可以这样操作:
    SELECT 学号 FROM 学生表 UNION SELECT 学号 FROM 课程表
    返回结果:
    100 200 300

    SELECT 学号 FROM 学生表 UNION ALL SELECT 学号 FROM 课程表

    这样会允许重复值,返回结果:

    100 200 300 100 100 200

    三、关于多表查询我的一些小技巧:

    先附上一个挺全的数据库题,再以这个题为例说明一些多表查询的技巧:SQL数据库查询练习题及答案(四十五道题)

    1、顺藤摸瓜法

    以23题为例:

    第二十三题 查询“张旭“教师任课的学生成绩。

    通过给出的数据表,我完全可以写出这样一个关系式:
    因为给出的条件是张旭老师的名字,那么我就要从Teacher表中查找Tno,然后再通过Tno在Course表中找Cno,最后在通过Cno在Score表中找到条件符合的成绩。
    那么,关系就是这样的。知道了这条线,就可以从后往前写出查询语句:

    select Sno,Cno,Degree from Score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname=‘张旭’))

    #### 2、合起来搞事情法
    根据上边的分析,我可以看到要想完成题目要求,需要用到三个表:Score,Course,Teachet。
    那么我把三个表按照对应的联系联合起来即可,格式如下。

    select Degree from Score,Teacher,Course where Teacher.Tname=‘张旭’ and Teacher.Tno=Course.Tno and Course.Cno=Score.Cno

    总之,多表查询最重要的是对着给定的数据库表查一遍,只要能根据已知数据倒推回去,那么就可以倒着写出其查询语句。

    当然,会和一些统计语句进行结合,但是思路不变:

    第二十四题 查询选修某课程的同学人数多于5人的教师姓名。

    同样可以屡出这样一条线:(【】代表在箭头上边)
    count(cno)>5 →【Score】→Cno→【Course】→Tno→【Teacher】→Tname。
    最后可以写出查询语句:

    select Tname from Teacher where Tno in (select Tno from Course where
    Cno in (select Cno from Score group by Cno having COUNT(*)>5) )

    附:关于统计类函数的使用:

    1、group by函数

    GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。
    其实只要分组处理,一般就会求出这个组里的一些统计值,下边列出一些常用的统计函数:

    • AVG() :求平均数
    • MAX() :返回指定列的最大值。
    • MIN() :返回指定列的最小值。
    • SUM() :返回数值列的总和
    • COUNT():返回行数
    • SQL MID() :从文本字段中提取字符
    • SQL LEN() :返回文本字段的长度
    • SQL ROUND() :用于把数值字段舍入为指定的小数位数。
    • SQL NOW() :返回系统当前时间

    2、having函数

    这个函数其实用一句话就可以说清楚:它是聚合函数中的 where 函数。也就是说只是因为group by和where两个函数“一山不容二虎”,所以才出现的having函数进行补充。具体的使用方法和where函数没有什么区别。
    例如:

    第三十六题 查询至少有2名男生的班号。

    select Class FROM student where Ssex=‘男’ group by Class having
    COUNT(*)>1

    我们意图选择count(*)>1的班级,本来可以用group by class where (数量条件)即可,只是因为where不能用在这里,所以使用having就行。

    【待:补充一些全连接,左右连接的应用实例】

    参考资料:

    1. SQL教程
    展开全文
  • MySQL多表查询核心优化

    万次阅读 多人点赞 2016-03-22 17:00:33
    在一般的项目开发中,对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时(例如百万级数据量),我们就需要从数据库的各个方面来进行优化,本文就先从多表查询开始。

    概述

    在一般的项目开发中,对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时(例如百万级数据量),我们就需要从数据库的各个方面来进行优化,本文就先从多表查询开始。其他优化操作,后续另外更新,敬请关注。


    版权说明

    著作权归作者所有。
    商业转载请联系作者获得授权,非商业转载请注明出处。
    作者:Coding-Naga
    发表日期: 2016年3月22日
    链接:http://blog.csdn.net/lemon_tree12138/article/details/50921193
    来源:CSDN
    更多内容:分类 >> 数据库


    数据背景

    现假设有一个中学学校,学校中的年级有一年级、二年级、三年级,每个年级有两个班级。分别为101、102、201、202、301、302.
    现在我们要为这个学校建立一个考试成绩统计系统。为此,我们对数据库的设计画了如下ER图:
    这里写图片描述
    根据ER图,我们设计了数据表,结构如下:
    class 班级表:

    +------------+---------+------+-----+---------+----------------+
    | Field      | Type    | Null | Key | Default | Extra          |
    +------------+---------+------+-----+---------+----------------+
    | id         | int(11) | NO   | PRI | NULL    | auto_increment |
    | class_name | int(11) | NO   |     | NULL    |                |
    | master_id  | int(11) | YES  |     | NULL    |                |
    | is_key     | int(11) | NO   |     | NULL    |                |
    +------------+---------+------+-----+---------+----------------+
    

    student 学生表:

    +------------+-------------+------+-----+---------+----------------+
    | Field      | Type        | Null | Key | Default | Extra          |
    +------------+-------------+------+-----+---------+----------------+
    | id         | int(11)     | NO   | PRI | NULL    | auto_increment |
    | school_id  | int(11)     | NO   |     | NULL    |                |
    | name       | varchar(30) | NO   |     | NULL    |                |
    | sex        | int(11)     | NO   |     | NULL    |                |
    | age        | int(11)     | NO   |     | NULL    |                |
    | class_name | int(11)     | NO   |     | NULL    |                |
    +------------+-------------+------+-----+---------+----------------+
    

    course 课程表:

    +--------------+-------------+------+-----+---------+----------------+
    | Field        | Type        | Null | Key | Default | Extra          |
    +--------------+-------------+------+-----+---------+----------------+
    | id           | int(11)     | NO   | PRI | NULL    | auto_increment |
    | course_name  | varchar(10) | NO   |     | NULL    |                |
    | grade        | int(11)     | NO   |     | NULL    |                |
    | president_id | int(11)     | YES  |     | NULL    |                |
    | is_neces     | int(11)     | NO   |     | NULL    |                |
    | credit       | int(11)     | NO   |     | NULL    |                |
    | class_name   | int(11)     | YES  |     | NULL    |                |
    +--------------+-------------+------+-----+---------+----------------+
    

    score 成绩表:

    +-----------+---------+------+-----+---------+----------------+
    | Field     | Type    | Null | Key | Default | Extra          |
    +-----------+---------+------+-----+---------+----------------+
    | id        | int(11) | NO   | PRI | NULL    | auto_increment |
    | course_id | int(11) | NO   |     | NULL    |                |
    | school_id | int(11) | NO   |     | NULL    |                |
    | score     | int(11) | YES  |     | NULL    |                |
    +-----------+---------+------+-----+---------+----------------+
    

    **注:**关于本文的数据库数据大家可以在文章最下方的相关下载中获取。资源链接中有两个版本的数据库,school.sql为初始数据库,school_2.sql为优化后的数据库。


    连接(JOIN)简介

    内连(INNER JOIN)

    INNER JOIN 关键字在表中存在至少一个匹配时返回行。

    我们也用下面的交集维恩图来描述内连操作:

    这里写图片描述

    上面的维恩图只是表达了一个有限制情况(即存在JOIN ON),而对于没有约束的情况下,其实就是一个笛卡尔积运算。
    **注:**INNER JOIN 与 JOIN 是相同的。一般情况下,在SQL语句中可以省略INNER关键字。

    左连接(LEFT JOIN)

    LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。

    使用维恩图描述内连操作:

    这里写图片描述

    对于上面结果为 NULL的这一条,通过对实际测试的数据表进行操作,得到如下的测试结果:

    +------------+-------+
    | class_name | name  |
    +------------+-------+
    |        202 | NULL  |
    |        301 | Bob   |
    |        302 | Alice |
    +------------+-------+
    

    右连接(RIGHT JOIN)

    RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。

    这里写图片描述

    **注:**右连接可以理解成左连接的对称互补,详细说明可参见左连接。

    全连(FULL JOIN)

    FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行.
    FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

    这里写图片描述

    联合(UNION)

    UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
    请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。


    MySQL的JOIN实现原理

    在MySQL 中,只有一种Join 算法,就是大名鼎鼎的Nested Loop Join,他没有其他很多数据库所提供的Hash Join,也没有Sort Merge Join。顾名思义,Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与Join,则再通过前两个表的Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复。
                                                      – 《MySQL 性能调优与架构设计》


    多表查询实战

    查询各个班级的班长姓名

    优化分析

    对于这个多表的查询使用where是可以很好地完成查询,而查询的结果从表面上看,完全没什么问题,如下:

    +------------+---------+
    | class_name | name    |
    +------------+---------+
    |        101 | William |
    |        102 | Peter   |
    |        201 | Judy    |
    |        202 | Polly   |
    |        301 | Grace   |
    |        302 | Sunny   |
    +------------+---------+
    

    可是,由于我们使用的是where,这个与内连接在有条件限制的情况下是一样的,其维恩图也可以一并参考。可是,如果现在我们假设,有一个新的班级303,或是这个303的班级暂时还没有班长。这个时候通过where就无法完成查询了。上面的结果中就已经很好地给出解释。
    这个时候,我们就需要通过外连接中的左连接(如果采用右连接,那么相应的表位置也要进行替换)来进行查询了。在左连的查询中,因为是包含了"左表"的全部行,所以对于未选出班长的303来说,这个很有必要。采用左连操作的结果如下:

    +------------+---------+
    | class_name | name    |
    +------------+---------+
    |        101 | William |
    |        102 | Peter   |
    |        201 | Judy    |
    |        202 | Polly   |
    |        301 | Grace   |
    |        302 | Sunny   |
    |        303 | NULL    |
    +------------+---------+
    

    SQL展示

    朴素的WHERE

    SELECT cl.class_name, st.name
    FROM class cl, student st
    WHERE cl.master_id=st.school_id;
    

    INNER JOIN

    SELECT cl.class_name, st.name
    FROM class cl
    JOIN student st
    ON cl.master_id=st.school_id;
    

    LEAF JOIN

    SELECT cl.class_name, st.name
    FROM class cl
    LEFT JOIN student st
    ON cl.master_id=st.school_id;
    

    RIGHT JOIN

    SELECT cl.class_name, st.name
    FROM student st
    RIGHT JOIN class cl
    ON cl.master_id=st.school_id;
    

    利用 EXPLAIN 检查优化器

    通过EXPLAIN我们分别检查上面WHERE语句和LEFT JOIN的优化过程。结果如下:
    WHERE

    +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
    +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
    |  1 | SIMPLE      | cl    | ALL  | NULL          | NULL | NULL    | NULL |    7 |                                |
    |  1 | SIMPLE      | st    | ALL  | NULL          | NULL | NULL    | NULL |  301 | Using where; Using join buffer |
    +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
    

    LEFT JOIN

    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    |  1 | SIMPLE      | cl    | ALL  | NULL          | NULL | NULL    | NULL |    7 |       |
    |  1 | SIMPLE      | st    | ALL  | NULL          | NULL | NULL    | NULL |  301 |       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    

    对于上面的两个结果,我们可以看到有一个很明显的区别在于Extra。
    Using where说明进行了where的过滤操作,Using join buffer说明进行join缓存。关于这两者的说明可参考这里
    从上面的结果中,还可以看到每种情况的两种查询操作都是经过了全表扫描。而这对于大量数据而言是很不利的。
    现在,我们可以为被驱动表的join字段添加索引,再对其进行EXPLAIN检查。
    添加索引

    ALTER TABLE student ADD INDEX index_school_id (school_id);
    

    通过EXPLAIN我们分别检查上面WHERE语句和LEFT JOIN的优化过程。结果如下:
    WHERE

    +----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
    | id | select_type | table | type | possible_keys   | key             | key_len | ref                 | rows | Extra |
    +----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
    |  1 | SIMPLE      | cl    | ALL  | NULL            | NULL            | NULL    | NULL                |    7 |       |
    |  1 | SIMPLE      | st    | ref  | index_school_id | index_school_id | 4       | school.cl.master_id |    1 |       |
    +----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
    

    LEFT JOIN

    +----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
    | id | select_type | table | type | possible_keys   | key             | key_len | ref                 | rows | Extra |
    +----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
    |  1 | SIMPLE      | cl    | ALL  | NULL            | NULL            | NULL    | NULL                |    7 |       |
    |  1 | SIMPLE      | st    | ref  | index_school_id | index_school_id | 4       | school.cl.master_id |    1 |       |
    +----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
    

    现在,可以很明显地看出rows列的数值,在被驱动表处都是1,这大降低了查询的复杂度。而且对于type列,也从一开始的ALL变成了现在的ref。还有一些其他的列也被修改了。关于type字段的说明可参考这里


    查询番外

    根据学号查询一个学生的成绩单
    WHERE 查询

    EXPLAIN SELECT st.name, co.course_name, sc.score
    FROM student st, score sc, course co
    WHERE sc.school_id=st.school_id
    AND co.id=sc.course_id
    AND st.school_id=100005;
    

    JOIN 查询

    EXPLAIN SELECT st.name, co.course_name, sc.score
    FROM student st
    JOIN score sc ON sc.school_id=st.school_id
    JOIN course co ON co.id=sc.course_id
    WHERE st.school_id=100005;
    

    结果

    +----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
    | id | select_type | table | type   | possible_keys                         | key                | key_len | ref                 | rows | Extra |
    +----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
    |  1 | SIMPLE      | st    | ref    | index_school_id                       | index_school_id    | 4       | const               |    1 |       |
    |  1 | SIMPLE      | sc    | ref    | index_school_id_sc,index_course_id_sc | index_school_id_sc | 4       | const               |    3 |       |
    |  1 | SIMPLE      | co    | eq_ref | PRIMARY                               | PRIMARY            | 4       | school.sc.course_id |    1 |       |
    +----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
    

    优化总结

    • 对于要求全面的结果时,我们需要使用连接操作(LEFT JOIN / RIGHT JOIN / FULL JOIN);
    • 不要以为使用MySQL的一些连接操作对查询有多么大的改善,核心是索引;
    • 对被驱动表的join字段添加索引;

    Ref

    • 《高性能MySQL(第3版)》
    • 《MySQL 性能调优与架构设计》
    • SQL教程 | 菜鸟教程
    • http://bbs.chinaunix.net/thread-4069615-1-1.html
    • http://s.petrunia.net/blog/?p=18

    相关资源


    征集

    如果你也需要使用ProcessOn这款在线绘图工具,可以使用如下邀请链接进行注册:
    https://www.processon.com/i/56205c2ee4b0f6ed10838a6d

    展开全文
  • SQL多表查询

    万次阅读 2019-06-05 22:29:09
    多表查询的话,SQL提供了JOIN关键字来连接多张表的查询(即连接的是SELECT结果集)基本分为以下几种: JOIN / INNER JOIN-也称作内连接,是最为常用的一种JOIN,其效果是在表中存在一个匹配即返回该条数据 等值连接...
  • 多表查询和子查询

    万次阅读 2016-11-13 17:59:11
    主要介绍了多表查询,以及在多表/单表查询时常用的函数和关键字,介绍了如何使用子查询。 多表查询  employee表,departments表,localtions表结构 引例: select  last_name, department_...
  • 一个orderitem表(从表),一个product表(主表)如下图所示orderitem表:product表:2、如果要查询两个表中指定列的信息,比如orderitem表中的count,subtotal,product表中的pname,pimage等那么就涉及到多表查询,...
  • JAVA多表查询

    千次阅读 2016-11-21 10:18:16
    多表查询练习一 通过对多表查询的合并结果集、连接查询以及子查询的学习,下面我们来练习多表查询,如下所示: (1)查询至少有一个员工的部门,并显示部门编号、部门名称、部门位置、部门人数: 分析: 查询...
  • Mysql多表查询

    千次阅读 2019-09-05 00:03:32
    mysql多表查询
  • 多表查询建表语句

    万次阅读 2020-10-05 15:14:43
    学生 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师 Teacher(t_id,t_name) –教师编号,教师...
  • 多表查询信息

    千次阅读 2016-02-04 21:26:16
    现如果想进行多表查询如查询教师编号为XX的所有的学生的信息,就可以使用该操作的前提是数据库中已经按照规则设计好了数据库表。"select s.* from teacher_student ts , student s where ts.teacher_id =?
  • 【MySQL】多表查询

    万次阅读 多人点赞 2018-06-18 00:00:22
    多表查询如果不加任何条件,得到的结果称为笛卡尔积。 例如,查找雇员名、雇员工资以及部门所在的名字。 可以发现,结果是这样的,从第一个表中选出第一条记录,和第二个表中的所有所有记...
  • select单表查询,多表查询,子查询

    千次阅读 2018-03-03 16:17:50
    DML:语句,常用的select ,insert into ,delete,updata select 语句: select 语句一般用法为: select ...单表查询,多表查询,子查询 最简单的单表查询 : select * from tb_name; *表示,所有字段 查询特...
  • 跨库查询和多表查询

    万次阅读 2016-12-07 22:51:17
    介绍如何使用MySQL进行跨库查询和多表查询(几张表的信息纵向连接在一张表中) information_schema MySQL内部使用的数据库 information_schema 和 mysql information_schema库保存了当前使用的MySQL的所有数据库...
  • mysql 多表查询

    千次阅读 2019-06-30 16:20:46
    一、使用SELECT子句进行多表查询 SELECT 字段名 FROM 表1,表2 … WHERE 表1.字段 = 表2.字段 AND 其它查询条件 SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo...
  • 基于MybatisPlus的多表查询

    千次阅读 2020-07-04 09:32:54
    基于MybatisPlus的多表查询 mybaitsPlus配置文档 mybatis3.x文档 目录: 一对一查询 1.1 方式一:非select配置方式 1.2 方式二,通过select进行多表查询 一对多查询(用户对应多账户示例) 2.1 方式一:非select配置...
  • hive 之多表查询

    千次阅读 2018-05-21 15:05:02
    hive 多表查询,转化为一个mapreduce的作业hive&gt;desc dept;deptno doubledname stringloc stringhive&gt;desc emp;empno doubleename stringjob stringmgr doublehiredate stringsal double...
  • Oracle 查询技巧与优化(二) 多表查询

    万次阅读 多人点赞 2016-08-05 08:59:59
    关于Oracle多表查询的一些技巧和优化~
  • SQL 语句的多表查询方式

    万次阅读 2017-11-28 17:58:08
    SQL 语句的多表查询方式  例如:按照 department_id 查询 employees(员工表)和 departments(部门表) 的信息。 方式一(通用型):SELECT ... FROM ... WHERE SELECT e.last_name,e.department_id,d.department_name ...
  • Mysql 多表查询详解

    万次阅读 多人点赞 2016-04-27 16:33:48
    1)多表连接有哪些分类; 2)针对这些分类有哪些连接方法? 3)这些连接方法分别作用于哪些应用场景? 这篇针对这三个点通过实例来讲述,目的是穷尽所有的场景和所有的方法,并且对每个方法的使用做实例。 2.假设有两张...
  • MySQL 高级多表查询

    千次阅读 2020-05-05 14:03:33
    MySQL多表查询 添加练习表 -- 用户表(user) CREATE TABLE `user`( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)', `username` VARCHAR(50) COMMENT '用户姓名', `age` CHAR(3) COMMENT '用户...
  • 笛卡尔积与多表查询

    千次阅读 2017-08-09 19:36:12
    笛卡尔积与多表查询 笛卡尔积是关系代数中的概念, 是 DB 中多表连接的理论基础; 本文通过 demo 演示常见的四种多表连接 1. 笛卡尔积与多表连接1.1 笛卡尔积原理解释笛卡尔积 描述的是多表连接组成一个新表的情况. ...
  • Select之多表查询

    千次阅读 2018-06-03 18:34:45
    今天来学习多表查询。 我们用一个简单的公司管理系统,有三张表EMP ,DEPT,SALGRADE来演示如何进行多表查询。 表内容如下: 创建部门表 drop table if exists dept; create table if not exists dept ( ...
  • Hibernate中的HQL的基本常用小例子,单表查询与多表查询 本文章实现HQL的以下功能: /** * hql语法: * 1)单表查询 * 1.1 全表查询 * 1.2 指定字段查询 * 1.3 排除重复记录 * 1.4 条件查询(重点) * 1.5 ...
  • 【Mybatis】多表查询

    千次阅读 多人点赞 2017-11-21 21:58:45
    多表查询一直是数据库的关键部分。网上关于Mybatis的例子天华龙凤,又要自己新建实体类,又在一个实体类中将另一个实体类联系起来,在我看来大可不必,只要将sql查询结果的视图映射到Java里面即可。下面举一个例子来...
  • Hibernate Criteria多表查询

    千次阅读 2018-05-15 18:00:53
    Hibernate Criteria多表查询Hibernate有多种查询方式,如果想要多表查询,有3种方法1.HQL查询(写连接语句)2.Native SQL(手写原生SQL语句)3.Criteria查询本次内容就是介绍Criteria的多表查询这是我需要持久化的...
  • 多表查询,查询表中某字段值相同的数据
  • MySQL_单表查询、多表查询

    万次阅读 2018-05-25 19:21:22
    (单表查询) create table star( id int auto_increment, name varchar(50)not null, money float not null,  province varchar(20)default null,  age tinyint unsigned not null,  sex tinyint ...
  • 多表查询SQL语句 1、表架构 student(sid,sname,sage,ssex) 学生表  course(cid,cname,tid) 课程表  sC(sid,cid,score) 成绩表  teacher(tid,tname) 教师表 2、建表sql语句 CREATE TABLE stu...
  • mybatis分页多表查询

    千次阅读 2017-12-08 14:36:01
    mybatis分页多表查询 我们常遇到一种情况,数据量比较大,如果使用前台分页,在前台如果有权限,有逻辑判断,都会大大降低页面加载的速度,我们需要分页,单表查询的网上方法很多,这里就不赘余了,多表关联查询的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,417,311
精华内容 566,924
关键字:

多表查询