MySQL学习历程（六）：各种查询

朴灿烈づ我的快乐病毒、 2022-08-09 05:09 140阅读 0赞

# 1、基本查询语句 #

SELECT * FROM tb_name;//返回表tb_name的所有字段
    SELECT id, name FROM tb_name;//返回指定的两个字段
    SELECT id, name FROM tb_name WHERE name = "simon";//返回满足条件的指定字段

IN 表示一个集合；BETWEEN AND表示两个值之间

SELECT * FROM stu WHERE stu_id IN(8000609050, 8000609051, 8000609053);//stu_id在集合里的返回
    SELECT * FROM stu WHERE stu_id NOT IN(8000609050, 8000609051, 8000609053);
    SELECT * FROM stu WHERE stu_id IN(8000609050, 8000609051) ORDER BY name;//再按名字升序排序
    SELECT * FROM fruits WHERE price BETWEEN 2.0 AND 10.0;//在两个值之间的返回
    SELECT * FROM fruits WHERE price NOT BETWEEN 2.0 AND 10.0;//不在两个值之间

LIKE通配符查询，“%”匹配任意长度的字符，“\_”匹配单个字符

SELECT * FROM stu WHERE name LIKE "z%";//满足name以z开头
    SELECT * FROM stu WHERE name LIKE "z%a";//满足name以z开头，以a结尾
    SELECT * FROM stu WHERE name LIKE "%y";//满足name以y结尾
    SELECT * FROM stu WHERE name LIKE "a_";//满足name只有两个字符，并且a是第一个

AND、OR组合条件查询

SELECT * FROM tb_name WHERE name IS NULL;//判空的时候用IS NULL
    SELECT * FROM tb_name WHERE name IS NOT NULL;
    SELECT * FROM tb_name WHERE name IS NOT NULL AND stu_id IN(8000609050, 8000609053);
    SELECT * FROM tb_name WHERE name IS NOT NULL OR stu_id IN(8000609050, 8000609053);
    SELECT DISTINCT * FROM fruits WHERE price BETWEEN 2.0 AND 10.0;//DISTINCT删除重复结果

ORDER BY查询结果排序，默认升序，ASC关键字是升序，DESC关键字是降序。

SELECT * FROM tb_name ORDER BY price;//默认升序排列
    SELECT * FROM tb_name ORDER BY name, price;//先按name升序排序，name相同的再按price升序排序
    SELECT * FROM tb_name ORDER BY price DESC; //指定降序
    SELECT * FROM tb_name ORDER BY price ASC; //指定升序
    SELECT * FROM tb_name ORDER BY name ASC, price DESC;//先按name升序排序，name相同的再按price降序排序

GROUP BY分组查询是对数据按照某个或多个字段进行分组。

SELECT s_id, COUNT(*) AS total FROM fruits GROUP BY s_id;//对s_id进行分组，然后统计每个分组的行数
    SELECT s_id, GROUP_CONCAT(f_name) AS names FROM fruits GROUP BY s_id;//对s_id进行分组，显示每个分组里的f_name。
    SELECT s_id, GROUP_CONCAT(f_name) AS names FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 2;//对s_id进行分组，显示分组f_name数量大于2的分组
    SELECT s_id, COUNT(*) AS total FROM fruits GROUP BY s_id WITH ROLLUP;//ROLLUP增加一行，统计记录总和
    SELECT * FROM fruits GROUP BY s_id, f_name;//多字段分组，先按s_id分组再按f_name分组
    
    //对num进行分组，把同一分组内的quantity*price的总和大于等于100的分组显示出来。
    SELECT num, SUM(quantity * price) AS orderTotal FROM orderitems GROUP BY num HAVING SUM(quantity * price) >= 100;
    
    //再上一条的基础上再加上一个显示排序的功能
    SELECT num, SUM(quantity * price) AS orderTotal FROM orderitems GROUP BY num HAVING SUM(quantity * price) >= 100 ORDER BY orderTotal;
    //limit限制查询结果的数量
    SELECT * FROM fruits LIMIT 5;//显示前两条记录
    SELECT * FROM fruits LIMIT 4, 2;//从第五行开始显示，显示两条记录

# 2、使用集合函数进行查询 #

<table> 
 <thead> 
  <tr> 
   <th align="left">函数名</th> 
   <th align="left">功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">AVG()</td> 
   <td align="left">返回平均值</td> 
  </tr> 
  <tr> 
   <td align="left">COUNT()</td> 
   <td align="left">计算行数</td> 
  </tr> 
  <tr> 
   <td align="left">MAX()</td> 
   <td align="left">返回最大值</td> 
  </tr> 
  <tr> 
   <td align="left">MIN()</td> 
   <td align="left">返回最小值</td> 
  </tr> 
  <tr> 
   <td align="left">SUM()</td> 
   <td align="left">求和函数</td> 
  </tr> 
 </tbody> 
</table>

SELECT COUNT(email) AS email_num FROM student;
    SELECT num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;
    SELECT s_id, AVG(price) AS avg_price FROM fruits GROUP BY s_id;
    SELECT s_id, MAX(f_price) AS max_price FROM fruits GROUP BY s_id;
    SELECT s_id, MIN(f_price) AS max_price FROM fruits GROUP BY s_id;

# 3、连接查询 #

## 3.1、内连接 ##

内连接是将两个表中相同列的值也相同的行保留下来。  
对于一个问题，两个表有相同的字段s\_id，找出两个表中s\_id相同的记录。有以下两种查询方法：

//非内连接形式
    SELECT suppliers.s_id, s_name, f_name, f_price FROM fruits, suppliers WHERE fruits.s_id = suppliers.s_id;
    //内连接形式
    SELECT suppliers.s_id, s_name, f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;

非内连接形式效率低。

## 3.2、外连接 ##

内连接是返回查询结果中仅符合查询条件和连接条件的行。外连接不进包含符合条件的行也包含不符合连接条件的行。  
1. LEFT OUTER JOIN(左外连接)：左表中的所有记录和右表中连接字段相等的记录。  
2. RIGHT OUTER JOIN(右外连接)：右表中的所有记录和右表中连接字段相等的记录。

SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;
    
    SELECT customers.c_id, orders.o_num FROM customers RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;

## 3.3、复合连接查询 ##

复合连接查询就是在内连接或外连接的基础上再加上过滤条件。

//再加一个条件
    SELECT suppliers.s_id, s_name, f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id AND f_price > 23;
    //加上排序条件
    SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id = orders.c_id ORDER BY customers.c_id;

# 4、嵌套查询 #

一个查询语句内部嵌套另一个查询语句。常见的操作符有ANY(SOME),ALL,IN,EXISTS。

//ANY：num1大于子查询里面的任意一行
    SELECT num1 FROM tb1 WHERE num1 > ANY (SELECT num2 FROM tb2);
    //ALL：num1大于子查询里面的所有行
    SELECT num1 FROM tb1 WHERE num1 > ALL (SELECT num2 FROM tb2);
    //EXISTS：因为WHERE后面是个表达式，EXISTS意思是子查询有行返回则是true，子查询没有结果返回则返回false
    SELECT num1 FROM tb1 WHERE EXISTS (SELECT num2 FROM tb2);
    //IN：num1在子查询返回的结果里则为真，另外还有NOT IN。
    SELECT num1 FROM tb1 WHERE num1 IN (SELECT num2 FROM tb2);

# 5、合并查询结果 #

使用UNION关键字将多个SELECT查询结果合并成单个结果集。合并时，两个表对应的列和数据类型必须严格相同。UNION会在合并时删除重复记录，UNION ALL不会删除重复记录并且效率较高。

SELECT ... FROM ... 
    UNION [ALL]
    SELECT ... FROM ...

# 6、为表和字段取别名 #

取个别名好偷懒，下次用着方便。

//表名 AS 表别名
    SELECT c.c_id, o.o_num FROM customers AS c OUTER JOIN orders AS o ON c.c_id = o.c_id //列名 AS 列别名 SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price FROM fruits AS f1 WHERE f1.f_price < 8;

# 7、使用正则表达式查询 #

正则表达式可以找个时间集中学习一下，使用方法就是使用正则表达式作为匹配条件，将满足条件的返回。

SELECT * FROM fruits WHERE f_name REGEXP '^p';//满足f_name以p开头的返回
    SELECT * FROM fruits WHERE f_name REGEXP 'p$';//满足f_name以p结尾的返回