MySql查询语句小汇总二（DQL语句）

多表查询：

       分类：合并结果集，连接查询，子查询

 

一：合并结果集

如果两张表的列类型和列数相同，可以进行合并，

关键字：union（去除重复行）；union all（不去除重复行）

     

例:
select * from cd union all select * from ad;

      

 

 

 

 

 

 

 

 

二：连接查询

       分类：内连接，外链接（左外连接，右外连接，全外链接（MySql不支持）），自然连接

 

1，内连接

       1.1，直接查询两张表会得到笛卡尔积表

             

             

1.2，方言写法：查询表stu 和表teacher，得到每个学生的姓名，对应的课程，课程老师等信息；

      

Select s.name , s.age, t.name, t.class from stu s, teacher t where s.classID=t.id;

             

             

       1.3，标准的写法：

      

Select s.name , s.age, t.name inner t.class from stu s inner join teacher t on s.classID=t.id;

             

             

 

 

 

 

 

 

 

 

 

 

 

 

2，外连接：

2.1，左外连接：（左外即左表为主表，左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来，左表不满足条件的记录，右表部分都为NULL）

             

Select * from stu s left outer join teacher t on s.classID=t.id;

             

             

2.2，右外连接：右表就无论满足条件都会查询出来，而左表只有满足条件才能查询出来，右表不满足条件的记录，其左表部分都为NULL

             

 Select * from stu s right outer join teacher t on s.classID=t.id;

             

             

2.3，全连接：在其他数据库中使用full outer join实现全连接，但是MySql不支持，我们可以使用union来完成全连接。在左外查询语句和右外查询语句之间加上union，得到左外连接和右外连接的结果集，可以间接实现全连接。

             

             

             

 

 

 

 

 

 

 

三：子查询

       子查询即查询中包含查询

 

1，出现的位置：

       1.1，where后作为条件存在

       例：查询stu表中年龄最大的人的详细信息

      

Select * from stu where age = (select max(age) from stu);

      

      

       1.2，from后作为表存在（多行多列）

       例：查询上课程1的学生的姓名，性别，年龄信息：

      

Select s.name,s.sex,s.age from (select * from stu where classID=1) s;

      

      

 

2，条件：    

2.1，单行单列：是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧 （Mysql中“<>”表示不等号的意思，有的地方写成！=或#）

              例：查询stu表中年龄大于平均年龄的人的信息

            

  Select * from stu where age>(select avg(age) from stu);

             

             

2.2，多行单列：指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。 
　　　　可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧 
　　　　可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。 

              例子：查询年龄大于，课程标号为1的人的，所有人的详细信息

             

 Select * from stu age> all (select age from stu where classID=1);

             

             

2.3，单行多列：指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。 

              例子：查询stu表中，跟“张三”选修同一课程且年龄相同的人的信息

            

  Select * from stu where (classID,age) in (select classID,age from stu where where name=‘张三’ ) ;

             

             

       2.4，多行多列：指子查询返回的结果集是 N 行 N 列的一个表数据。 

例句：

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)；

             

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

四：子查询中关键字举例

 

1，any关键字：any关键词的意思是“对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE”。

       例：如果“10>any(10,21,11,3,21,11)”,由于10>3，所以该判断会返回true。

 

2，in关键字：使用in进行子查询，这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。

       例子：查询stu表中男性，且选过课的人的信息

select * from stu where sex='男' and exists
(select *
from teacher
where stu.classID=teacher.id);

   

   

3，all关键字：all必须与比较操作符一起使用。all的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”

例：如果“10 >all(2, 4, 5, 1)”，由于10大于集合中的所有值，所以这条判断就返回TRUE；而如果为“10 >all(20, 3, 2, 1, 4)”，这样的话，由于10小于20，所以该判断就会返回FALSE

 

 

4，独立子查询：独立字查询是不依靠外部查询的结果就能单独运行的查询语句；什么叫依赖外部查询，我们看下面的例子

       例子：查询stu表中，男性，且选过课的人的信息       方式1：

select * from stu
where sex=’男’ and classID in
(select id from teacher);

              方式2：

select * from stu
where sex=’男’ and exits
(select * from teacher
where stu.classID=teacher.id);

      

      

对于sql语句1，我们将子查询单独复制出来，也是可以单独执行的，就是子查询与外部查询没有任何关系。

对于sql语句2，我们将子查询单独复制出来，就无法单独执行了，由于sql语句2的子查询依赖外部查询的某些字段，这就导致子查询就依赖外部查询，就产生了相关性。

 

5，exists谓词：EXISTS是一个非常牛叉的谓词，它允许数据库高效地检查指定查询是否产生某些行。根据子查询是否返回行，该谓词返回TRUE或FALSE。与其 它谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN，对于EXISTS来说，UNKNOWN就是FALSE。 还是上面的语句，获得城市为hangzhou，并且存在订单的用户。

 

 

 

四：子查询的优化问题

很多查询中需要使用子查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死。子查询可以使查询语 句很灵活，但子查询的执行效率不高。

　　子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕 后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

　　在MySQL中可以使用连接查 询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快

 

 

不能优化的子查询： 
　　1、mysql不支持子查询合并和聚合函数子查询优化，mariadb对聚合函数子查询进行物化优化； 
　　2、mysql不支持from子句子查询优化，mariadb对from子句子查询进行子查询上拉优化； 
　　3、mysql和mariadb对子查询展开提供有限的支持，如对主键的操作才能进行上拉子查询优化； 
　　4、mysql不支持exists子查询优化，mariadb对exists关联子查询进行半连接优化，对exists非关联子查询没有进一步进行优化； 
　　5、mysql和mariadb不支持not exists子查询优化； 
　　6、mysql和mariadb对in子查询，对满足半连接语义的查询进行半连接优化，再基于代价评估进行优化，两者对半连接的代价评估选择方式有差异； 
　　7、mysql不支持not in子查询优化，mariadb对非关联not in子查询使用物化优化，对关联not in子查询不做优化； 
　　8、mysql和mariadb对>all非关联子查询使用max函数，<all非关联子查询使用min函数，对=all和非关联子查询使用exists优化； 
　　9、对>some和>any非关联子查询使用min函数，对<some和<any非关联子查询使用max函数，=any 和=some子查询使用半连接进行优化，对>some和>any关联子查询以及<some和<any关联子查询只有exists 优化。