Mysql数据库-多表查询

数据库-多表查询

1.连续查询

• 等值连接

• select table.column1,table2.column from table,table2 where table.column3 = table2.column
  

• 默认情况下等值连接并不会去掉重复字段

• 如果在等值连接在结果集中是唯一的，那么就可以直接写字段名，否则，必须在字段名前加上表名加以区分

• select name,city.countrycode ,language from city,countrylanguage where city.countrycode = countrylanguage.countrycode limit 10;
  -- name和language在两个表中是唯一的，没有歧义的，所以查询该字段的时候，前面没有加表名，但是countrycode在两个表都存在，所以必须指定表名，但是为了提高性能，避免字段名的使用产生歧义，在多表查询的时候，建议以  表名.字段名的方式来书写select 子句
  

  ---

• 表别名

• 以上的例子实现的功能很简单，但是子句过长，所以我们在多表查询的时候可以给表取别名，这样缩减子句的长度

• 表别名和字段别名很相似，就是给表取另一个名字，但是两个也有不同点，字段别名是为了让结果易于理解，但是表别名是为了SQL语句更加简单

• select t1.coulum,t2.column from table1 t1,tabl1 t2 where t1.column3 = t2.column4
  

• **注意：**表别名的作用范围仅仅局限于该条SQL语句中，如果离开这个SQL语句就会无效

  ---

• 多表等值连接

• 前面的等值连接代表的是两张表之间的连接，如果连接的表超过两张，那么则需要使用and来组合多个等值条件

• select t1.column1,t2.column2,t3.column3 from tabl1 t1,tabl2 t2,table3 t3 where t1.column4 = t2.column5 and t1.column 6 = t3.column7
  

  ---

• 自然连接

• 在等值连接的时候，并没有强调用来练连接的字段名必须相同，只要字段值相同就可以进行连接，在等值连接中，如果用于连接的两个字段，其字段名和数据类型完全相同，比如city表中的countrycode字段与countrylanguage表中countrycode字段，这种情况可以使用自然连接，换句话说自然连接是特殊的等值连接

• 特殊之处：在多个表中用于连接的字段同名且是相同类型

• select t1.column1,t2.column2 from table1 t1 natural join table2 t2
  

• 使用该语法，解析引擎会自动探测两个表中相同字段并设定等值条件，这样的字段可以不止一个，有多少个相同的字段就会生成多少个等值条件，比起显式等值连接来说，该语句结构更加简洁

• 自然连接会自动去掉重复列

• 自然连接需要mysql中相同的字段，在多个相同字段时，如果想要指定某个字段进行等值连接，需要使用join...using...语法指定

• select t1.column1,t2.column2 from table1 t1 join table2 t2 using(字段)
  注意：using中的字段不能加表名作为前缀，该字段目前时一个连接字段，不属于单张表
  	 连接的表中必须拥有相同的字段才能使用using
  
  除了join...using...还有join...on...是一样的
  select t1.column1,t2.column2 from table1 t1 join table2 t2 on(t1.字段 = t2.字段)
  注意：on子句中的字段名可以不相同
  	使用on子句，就不会消除相同列，因为on中的等值条件无须列相同
  	使用on，相当于 where ** = **
  

• 使用using和on的区别

  • 使用using子句进行连接的时候，结果中用于连接的列不会出现重复的列，但是on子句，在不干预的情况下，用于连接的列会出现两次
  • 使用using子句的时候，连接的表必须要有相同的字段名，但是on子句时可以不相同，相对比较灵活

  ---

• 自连接

• 就是一张表与自己这张表进行连接

• select e1.emp_name,e2.emp_name from employee e1,employee e2 where e1.mgr_id = e2.emp_id
  

  ---

• 非等值内连接

• 非等值内连接，即不用=来计算，用其他关系运算的结果

• select s.name,le.level from score s,level le where score between le.low and le.high
  

  ---

• 外连接

• 既然有内连接就有外连接，外连接分为左外连接和右外连接，没有全连接，还有交叉连接（笛卡尔积）

• 左外连接

• 简称左连接，指对两个表进行连接时，返回左表的全部记录（行）以及右表中符合条件的记录（行），右表没有匹配的用null来补齐

• select * from table1 t1 left join table2 t2 on(t1.column1 = t2.column2)
  
  

• 右外连接

• 反过来，返回右表的全部记录（行）以及左表中符合条件的记录（行），如果左表中没有匹配的记录就用null补全

• select * from table1 t1 right join table2 t2 on(t1.column1 = t2.column2)
  
  

• 笛卡尔积

• 又叫交叉连接，原理就是一张表中的每一条记录都要和另一张表中的每一条记录进行连接，如果两张表分别有n和m条记录，那么进行笛卡尔积的结果有n×m条记录

• select * from table1 join table2
  
  

• 笛卡尔积是没有条件的连接，会导致惩罚效应，产生的数据量远远超过需要的数据，而且多数配对不符合业务逻辑，尽量避免笛卡尔积

2. 子查询

• 子查询就是select查询语句中还有select语句，里面的称为子查询或者是内查询，外面的称为主查询或者是外查询

• 根据查询结果记录数量，子查询可以分为

  • 单行子查询
  • 多行子查询

• 根据内外查询的相关性，可分为

  • 不相关子查询
  • 相关子查询

• 使用子查询需要注意

  • 子查询需要放在小括号里面
  • 子查询一般放在条件判断的右边
  • 对于单行子查询，常见的搭配操作符有>、<、=、<>、>=、<=等
  • 对于多行子查询，常见的搭配操作符有In、any、all等
  • 不相关子查询，子查询优先于主查询，用子查询的结果构造外查询的条件
  • 相关子查询，以exists为代表，是一个内外一一匹配的过程

• 单行子查询

• 只返回一行结果的子查询

• select * from city where population > (select population from city where name = 'tokyo')
  
  

• 多行子查询

• 返回多行结果的子查询

• 简单介绍一下三个多行操作符的含义：

  • in ：等于多行子查询返回的结果中的任意一个即可
  • any：和多行子查询返回的某一个值进行比较即可
  • all：和多行子查询返回的所有值进行比较

• select distict countrycode from countrylanguage where language in(select language from countrylanguage where countrycode = 'abw')
  
  

• 相关子查询exists

• 不相关子查询：子查询的查询条件不依赖于父查询

• 相关子查询：子查询的查询条件依赖于外层父查询的某个属性值，带exists的子查询就是相关子查询

• exists表示存在量词，带有exists的子查询不返回任何记录的数据，只返回逻辑值true或者false

• select * from city c1 where exists(select * from country c2 where c1.countrycode = c2.code and c2.name = 'Netherlands');
  
  select * from city c1 where c1.countrycode = (select code from country where name = 'Netherlands')
  第二条的性能优于第一条，子查询行数多可以使用exists，但是子查询行数少可以使用=或者是in
  not是对in、between、exists取反