知新_RL

这个屌丝很懒，什么也没留下！

热门标签

Mysql两张表连接多字段查询_【连接查询】mySql多表连接查询与union与union all用法...

作者：知新_RL | 2024-05-08 12:31:49

踩

mysql 两张表多个字段相连

1.准备两个表

表a:

结构:

mysql> desca;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

数据

表b:

结构

mysql> descb;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| ageB | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

数据:

2.进行连接查询测试:

(1)交叉连接(笛卡尔积) cross join

mysql> select * froma,b;　　#第一种+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

| A2 | 2 | B1 | 1 |

| A1 | 1 | B2 | 22 |

| A2 | 2 | B2 | 22 |

+------+------+-------+------+

4 rows in set (0.00sec)

mysql> select * from a cross joinb;　　#第二种+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

| A2 | 2 | B1 | 1 |

| A1 | 1 | B2 | 22 |

| A2 | 2 | B2 | 22 |

+------+------+-------+------+

4 rows in set (0.00sec)

mysql> select a.*,b.* from a cross joinb;　　#第二种的又一个写法+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

| A2 | 2 | B1 | 1 |

| A1 | 1 | B2 | 22 |

| A2 | 2 | B2 | 22 |

+------+------+-------+------+

4 rows in set (0.00 sec)

(2)内连接 join 或 inner join(在笛卡尔积的基础上过滤)

显示内连接

(1)不带条件的内连接

mysql> select a.*,b.* from a inner join b on a.age=b.ageb;　#第一种 inner join+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

+------+------+-------+------+

1 row in set (0.00 sec)

mysql> select a.*,b.* from a join b on a.age=b.ageb;　　#第二种 join (默认是inner join)+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

+------+------+-------+------+

1 row in set (0.00 sec)

三个表的显示内连接:

SELECTa.*,

b.*,

c.*

FROMexampaper aINNER JOINbigquestion bINNER JOINexampaperquestion cON a.paperId =b.paperIdAND b.bigQuertionId = c.bigQuertionId

四个表的显示内连接:

SELECTtrain.trainingSchemaName,

train.majorName,

train.createTime,

tc.*, course.*, type.*

FROMtrainschemeinfo trainJOIN train_course tc ON train.trainingSchemeID =tc.trainningSchemeIDINNER JOIN t_course_base_info course ON tc.courseID =course.courseIdINNER JOIN coursetypeinfo type ON tc.typeNum =type.typeNumWHEREtc.trainningSchemeID= '661ecb064b164d1ea133956f89beddb7'

与之等价的隐士内连接:

SELECTtrain.trainingSchemaName,

train.majorName,

train.createTime,

tc.*, course.*, type.*

FROMtrainschemeinfo train,

train_course tc,

t_course_base_info course,

coursetypeinfo typeWHEREtrain.trainingSchemeID=tc.trainningSchemeIDAND tc.courseID =course.courseIdAND tc.typeNum =type.typeNumAND tc.trainningSchemeID = '661ecb064b164d1ea133956f89beddb7'

(2)显示内连接带条件

mysql> select a.*,b.* from a join b on a.age=b.ageb having a.name='A1';　　#having从查出的数据中挑选满足条件的元祖+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

+------+------+-------+------+

1 row in set (0.00sec)

mysql> select a.*,b.* from a join b on a.age=b.ageb where a.name='A1';　#where查询满足条件的元素+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

+------+------+-------+------+

1 row in set (0.00 sec)

隐士内连接:

mysql> select * from a,b where a.age=b.ageb;+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

+------+------+-------+------+

1 row in set (0.00sec)

mysql> select * from a,b where a.age=b.ageb and a.name='A1';+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

+------+------+-------+------+

1 row in set (0.00sec)

mysql> select * from a,b where a.age=b.ageb having a.name='A1';+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

+------+------+-------+------+

1 row in set (0.00 sec)

where是从本地磁盘查询满足条件的元素，having是从查出的数据中挑选满足条件的元素。执行权限 where>sum()..聚合函数>having

(3)左外连接:(拿左边的匹配右边的，没有找到右边的为null)

mysql> select * from a left join b on a.age =b.ageb;　　#第一种 left join+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

| A2 | 2 | NULL | NULL |

+------+------+-------+------+

2 rows in set (0.00sec)

mysql> select * from a left outer join b on a.age =b.ageb;　　#第二种 left outer join+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

| A2 | 2 | NULL | NULL |

+------+------+-------+------+

2 rows in set (0.00 sec)

(4)右外连接:(拿右边的匹配左边的，没有找到左边的为null)

mysql> select * from a right join b on a.age =b.ageb;　#第一种 right join+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

| NULL | NULL | B2 | 22 |

+------+------+-------+------+

2 rows in set (0.00sec)

mysql> select * from a right outer join b on a.age =b.ageb;　#第二种 right outer join+------+------+-------+------+

+------+------+-------+------+

| A1 | 1 | B1 | 1 |

| NULL | NULL | B2 | 22 |

+------+------+-------+------+

2 rows in set (0.00 sec)

3.Union 和 union all 取两个表的并集测试

修改b表，加一条和a表重复的数据

b表数据:

a表数据:

(1) union: 自动去掉重复元素

mysql> select * from a union select * fromb;+------+------+

| name | age |

+------+------+

| A1 | 1 |

| A2 | 2 |

| B1 | 1 |

| B2 | 22 |

+------+------+

4 rows in set (0.00 sec)

总结:

union:联合的意思，即把两次或多次查询结果合并起来。

要求：两次查询的列数必须一致

推荐：列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样

可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。

如果不想去掉重复的行，可以使用union all。

如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选，可以对union之后的数据进行排序和分页等操作。

例如:采用union合并的多个表的数据的SQL

需求是:为了显示学院、专业、班级树，但是这些信息不在一个月表，而且班级表中有专业编号，专业表中有学院编号。思路就是:分别从三个表中获取数据，然后采用union进行合并数据。

SELECTclassIDASdepartNum,

classNameASdepartName,

"class"ASdepartType,

(SELECTmajorIDFROMt_major_base_infoWHERE majorID = class.majorID) ASupdepartNumFROMt_class_base_info classUNION

SELECT majorID ASdepartNum,

majorNameASdepartName,

"major"ASdepartType,

(SELECTcollegeIDFROMt_college_base_infoWHERE collegeID=major.collegeID) ASupdepartNumFROMt_major_base_info majorUNION

SELECT collegeId ASdepartNum,

collegeNameASdepartName,

"college"ASdepartType,

"000" ASupdepartNumFROM t_college_base_info

(2) union all　　保留重复元素

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

mysql> select * from a union all select * fromb;+------+------+

| name | age |

+------+------+

| A1 | 1 |

| A2 | 2 |

| B1 | 1 |

| B2 | 22 |

| A1 | 1 |

+------+------+

5 rows in set (0.00 sec)

总结:

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。

同时，每条 SELECT 语句中的列的顺序必须相同.

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行

注意：

1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名

2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

补充:今天在项目种用到了从多个表种查询数据把并且分页，实现过程是将所有表的名字传到dao，然后遍历表名字，一起添加条件(前提是需要查询的列名字相同)：

public List> getAllData(ListtableNames) {

List sqls = newArrayList();

StringBuilder sb= null;for(String tableName : tableNames) {

sb= newStringBuilder();

sb.append("select id,name from ");

sb.append(tableName);

sb.append(" where 1=1");

sb.append(" and name = 'zhangsan'");

sqls.add(sb.toString());

}

String sqlFinally= StringUtils.join(sqls, " union ");

sqlFinally+= "order by name limit 5,5";

System.out.println(sqlFinally);/*Session session = getSessionFactory().openSession();

SQLQuery sqlQuery = session.createSQLQuery(sqlFinally);

sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

return sqlQuery.list();*/

return null;

}

测试：

public static voidmain(String[] args) {

GroupDaoImpl g = newGroupDaoImpl();

List tableNames = newArrayList();

tableNames.add("t1");

tableNames.add("t2");

tableNames.add("t3");

g.getAllData(tableNames);

}

结果:

select id,name from t1 where 1=1 and name = 'zhangsan' union select id,name from t2 where 1=1 and name = 'zhangsan' union select id,name from t3 where 1=1 and name = 'zhangsan' order by name limit 5,5

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/554592