阅读 49

MySQL(04)——查询(mysql查询语句)

4. 查询数据

4.1 单表查询

首先创建环境

CREATE TABLE fruits ( f_id CHAR ( 10 ) NOT NULL, s_id INT NOT NULL, f_name CHAR ( 255 ) NOT NULL, f_price DECIMAL ( 8, 2 ) NOT NULL, PRIMARY KEY ( f_id )      ); INSERT INTO fruits ( f_id, s_id, f_name, f_price ) VALUES ( 'a1', 101, 'apple', 5.2 ), ( 'b1', 101, 'blackberry', 10.2 ), ( 'bs1', 102, 'orange', 11.2 ), ( 'bs2', 105, 'melon', 8.2 ), ( 't1', 102, 'banana', 10.3 ), ( 't2', 102, 'grape', 5.3 ), ( 'o2', 103, 'coconut', 9.2 ), ( 'c0', 101, 'cherry', 3.2 ), ( 'a2', 103, 'apricot', 2.2 ), ( 'l2', 104, 'lemon', 6.4 ), ( 'b2', 104, 'berry', 7.6 ), ( 'm1', 106, 'mango', 15.6 ), ( 'm2', 105, 'xbabay', 2.6 ), ( 't4', 107, 'xbababa', 3.6 ), ( 'm3', 105, 'xxtt', 11.6 ), ( 'b5', 107, 'xxxx', 3.6 ); 复制代码

4.1.1 通配符查询

使用*可以匹配所有列

SELECT * FROM fruits; 复制代码

4.1.2 查询多个字段

  • 查询水果的名称(f_name)和价格(f_price)

SELECT f_id, s_id, f_name, f_price  FROM fruits; 复制代码

4.1.3 查询指定记录

MySQL表中的数据如果很多,我们需要查询到特定条件的数据就需要用到WHERE 条件语句

  • 查询价格为10.2元的水果名称

SELECT f_name,f_price FROM fruits WHERE f_price=10.2; 复制代码

  • 查询价格小于10元的水果名称

SELECT f_name,f_price FROM fruits WHERE f_price<10; 复制代码

4.1.4 带IN关键字的查询

查询字段满足列出的条件的数据

  • 查询s_id为101和102的记录

SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,102); 复制代码

  • 查询s_id不等于101也不等于102的数据

只需要在IN前面加一个NOT就可以了

SELECT s_id,f_name,f_price FROM fruits WHERE s_id NOT IN(101,102); 复制代码

4.1.5 带BETWEEN AND的范围查询

即查询范围内的数据BETWEEN 1 AND 2就是查询某个字段在1到2之间的数据

  • 查询价格在2元到10.2元之间的水果名称和价格

SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 2 AND 10.2; 复制代码

4.1.6 带LIKE的字符匹配查询

%匹配任意长度字符,包括空字符

_一次只能匹配一个字符

  • 查询名字中包含字母g的水果记录

SELECT f_id,f_name FROM fruits WHERE f_name LIKE '%g%'; 复制代码

  • 查询以b开头,y结尾的水果名称

SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'b%y'; 复制代码

  • 查询以字母y结尾,y前面有四个字母的水果名字

SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____y'; 复制代码

4.1.7 查询空值

NULL不同于0,也不是空字符串,它代表内容未知,可以使用IS NULL来判断字段是否为空

SELECT * FROM fruits WHERE f_name IS NULL; 复制代码

4.1.8 带AND的多条件查询

WHERE语句后面可以连接多个查询条件。使用AND表名每个条件都要满足

  • 在fruits表中查询s_id=101,并且f_price大于等于5的水果

SELECT f_id,f_price,f_name FROM fruits WHERE s_id='101' AND f_price>=5; 复制代码

4.1.9 带OR的多条件查询

使用OR连接查询条件,表示只要满足其中一个条件的记录,就可以返回

  • 查询s_id=101或s_id=102的水果供应商的记录

SELECT f_id,f_price,f_name FROM fruits WHERE s_id='101' OR  s_id='102'; 复制代码

在这里,使用关键字IN也能达到同样的效果

SELECT f_id,f_price,f_name FROM fruits WHERE s_id='101' OR  s_id='102'; 复制代码

注意:OR和AND可以一起使用,但是它们的优先级不一样,AND的优先级是高于OR的

4.1.10 对查询结果去重

fruits表中现在有多条数据,如果我们直接查询所有

  • 查询fruits中所有的供应商ID(s_id)

现在fruits表中有多条数据,如果直接查询s_id,会出现重复的数据

SELECT s_id FROM fruits; 复制代码

要去掉这些重复的数据,我们可以使用DISTINCT关键字

SELECT DISTINCT s_id FROM fruits; 复制代码

另外要注意的是DISTINCT只能作用于一个字段,如果同时查询两个字段的话,必须两个字段内容一模一样才会去重,如下是15条数据,由于107有两个价格都为3.60,所以就去掉了一条数据

SELECT DISTINCT s_id,f_price FROM fruits; 复制代码

4.1.11 对查询结果排序

(1)单字段排序

通过ORDER BY 字段名语句我们可以对查询到的记录进行排序

  • 查询fruits表中的f_name字段,并对其进行排序

SELECT f_name  From fruits ORDER BY f_name; 复制代码

(2)多字段排序

  • 查询fruits表中的f_name和f_price字段,先按照f_name排序,再按照f_price排序

SELECT f_name,f_price FROM fruits ORDER BY f_name,f_price; 复制代码

(3)指定排序方向

ASC(升序):如果我们不写排序方式,默认就按照这种方式正序排序

DESC(降序):通过指定排序方式,我们也可以倒着排序

  • 查询fruits表中的f_name和f_price字段,对结果先按f_price降序排序,再按f_name升序排序

SELECT f_name,f_price FROM fruits ORDER BY f_price DESC,f_name; 复制代码

4.1.12 分组查询

分组查询是按照一个或多个字段对返回的结果进行分组,使用GROUP BY 字段名语句。

(1)创建分组

SELECT s_id,COUNT(*) total FROM fruits GROUP BY s_id; 复制代码

根据结果可以看到id为101的供应商提供了3种水果...

(2)使用GROUP_CONCAT(字段名)显示每个分组里的内容

  • 根据s_id对fruits表种的数据进行分组,将每个供应商的水果名显示出来

SELECT s_id,GROUP_CONCAT(f_name) names FROM fruits GROUP BY s_id; 复制代码

(3)使用HAVING过滤分组

使用HAVING能对每个分组的数组进行过滤

  • 根据s_id对表中数据分组,并显示水果种类大于已的分组信息

SELECT s_id,GROUP_CONCAT(f_name) names FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1; 复制代码

可以看到manago被去掉了

初次使用HAVING往往会和WHERE混淆,其实区分起来非常简单,只需要理清楚优先级就好了。

WHERE语句的优先级是大于HAVING的,也就是说用WHERE过滤掉的数据不会出现在分组中。

(4)在GROUP BY字句中使用WITH ROLLUP

使用了WITH ROLLUP关键字后会在查询的语句中多出一条记录,该记录为分组中记录的数量

SELECT s_id,COUNT(*) total FROM fruits GROUP BY s_id WITH ROLLUP; 复制代码

(5)多字段分组

首先来看如果按照一个字段分组,查询所有字段会出来什么

SELECT *,COUNT(*),GROUP_CONCAT(f_name) names FROM fruits GROUP BY s_id; 复制代码

我们发现虽然分组以后,每个组里有多种水果,但是f_name字段只会选择其中一个名称进行展示

接下来先按照s_id分组,再按照f_name分组

SELECT *,COUNT(*),GROUP_CONCAT(f_name) names FROM fruits GROUP BY s_id,f_name; 复制代码

这样子每种水果名称都会展示出来,s_id也会重复显示

(6)GROUP BYORDER BY 一起使用

可以使用ORDER BY对分组后的数据进行排序

  • 按照s_id分组,计算每个供应商水果单价的和,并排序

SELECT s_id,SUM(f_price) totalPrice FROM fruits GROUP BY s_id ORDER BY totalPrice; 复制代码

4.1.13 使用LIMIT限制查询结果的数量

语法格式LIMIT 开始的行数 记录的条数

这里开始的行数是从0开始的,也就说LIMIT 2 4,表示从第三行开始,包括第三行一共查询4条数据

首先来看下所有的数据

SELECT * FROM fruits; 复制代码

接下来使用LIMIT

SELECT * FROM fruits LIMIT 2, 4; 复制代码

第一个参数开始的行数是可以省略的,代表直接从第0行开始

SELECT * FROM fruits LIMIT 4; 复制代码

4.2 使用集合函数查询

为了测试先给fruits表添加一些数据

ALTER TABLE fruits ADD COLUMN taste VARCHAR(20) DEFAULT NULL; INSERT INTO fruits values("x1",201,"pineApple",3,"good"); 复制代码

现在表中数据如下

4.2.1 COUNT()函数

(1)COUNT(*):计算表中的总行数

  • 查询fruits表下的总行数

SELECT COUNT(*) FROM fruits; 复制代码

(2)COUNT(字段名):计算指定列下的总行数,计算式会忽略空值(NULL

  • 根据taste字段查询总行数

SELECT COUNT(taste) FROM fruits; 复制代码

(3)COUNT(*)COUNT(1)的区别

在工作中经常能看到查询总行数使用COUNT(1)替代COUNT(*),问了同事,说是使用COUNT(1)效率比较高,但是为什么高也没人清楚,这种人云亦云的事情我们肯定是要搞清楚的。

对于COUNT(1)我们可以这样理解,将数字1当作一个伪列来显示,我们可以执行如下SQL

SELECT 1 FROM fruits; 复制代码

fruits表中本身是没有列名为1的列的,但是仍然查出了17条数据,这说明1也被当作了表的列。这样就好理解了,COUNT(1)就是根据列名1查询记录的条数,并且这个列里是没有空值的,所以能算出表中记录总数。

解决了这个问题后我们再来对比COUNT(*)COUNT(1)的区别。

下面这段话来自《阿里巴巴开发手册》

【强制】不要使用count(列名)或count(常量)来替代count(),count()是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。

说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行

这里就奇怪了,这段话和我们平时看到的并不太一样。

如果表中只有一个主键,没有其它所以的话,COUNT(*)COUNT(1)默认都会使用主键来计算总记录数,如果表中有较短的索引,那么会使用较短的索引来计算,这两个语句在计算时使用的索引都是相同的。

我使用这两个语句分别查询有大量数据的表时,它们消耗的时间几乎相同,所以不存在COUNT(1)COUNT(*)性能更好的情况。

参考1:zhuanlan.zhihu.com/p/28397595

参考2:www.hollischuang.com/archives/40…

4.2.2 SUM()函数

用来求和,会自动忽略为NULL 的行

  • 计算s_id为101的供应商所有水果单价总和

SELECT SUM(f_price) FROM fruits WHERE s_id='101'; 复制代码

  • 按照s_id分组,计算每组水果的单价和

SELECT s_id,GROUP_CONCAT(f_name),SUM(f_price) FROM fruits GROUP BY s_id; 复制代码

4.2.3 AVG()函数

  • 计算列数据的平均值

  • 查询fruits表中,s_id=103的供应商水果单价的平均值

SELECT AVG(f_price) avg_price FROM fruits WHERE s_id=103; 复制代码

  • 查询每一个供应商的水果价格平均值

SELECT s_id,AVG(f_price) FROM fruits GROUP BY s_id; 复制代码

4.2.3 MAX()函数

4.2.4 MIN()函数

4.3 连接查询

首先创建测试环境

CREATE TABLE suppliers   ( s_id INT NOT NULL, s_name CHAR(50) NOT NULL, s_city CHAR(50) NULL, s_zip CHAR(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY(s_id) ); INSERT INTO suppliers ( s_id, s_name, s_city, s_zip, s_call )          VALUES ( 101, 'Supplies A', 'Tianjin', '400000', '18075' ), ( 102, 'Supplies B', 'Chongqing', '400000', '44333' ), ( 103, 'Supplies C', 'Shanghai', '400000', '90046' ), ( 104, 'Supplies D', 'Zhongshan', '400000', '11111' ), ( 105, 'Supplies E', 'Taiyuang', '400000', '22222' ), ( 106, 'Supplies F', 'Beijing', '400000', '45678' ), ( 107, 'Supplies G', 'Zhengzhou', '400000', '33332' ); 复制代码

4.3.1 内连接查询

关键字:INNER JOIN

连接查询的含义就是将两张或多张表通过某个字段关联到一起,组合成一条记录返回。这个字段就是外键,但是前面我们说过实际开发中一般不创建外键约束,所以一张表的外键一般都是关联表的主键ID。

如果通过这个外键字段无法匹配到关联的数据,那么是不会返回任何记录的。

  • 在fruits表和supplier表之间使用内连接查询

SELECT b.s_id,s_name,f_price FROM fruits a,suppliers b WHERE a.s_id = b.s_id; 复制代码

可以看到fruits表和suppliers都有s_id字段,我们通过这个字段连接两张表后,不仅查出来价格,也查出来水果名称。

细心的小伙伴发现查出来的记录数既不是fruits表中的记录数,也不是suppliers表中的记录数,实际这里的情况是一对一也就是说一种水果对于一个供应商,但是由于有个水果的s_id在suppliers表中没有对于记录,所以就没有查到。

在实际写SQL的时候,我们需要根据需要查询的主表来写。比如需要查询水果的各种信息,要关联N张表,这种情况,水果表就写在第一个位置就可以了。所有的数据都会按照水果表的记录来查询。

上面的SQL也可以像这样写,效果是一样的:

SELECT b.s_id,s_name,f_price FROM fruits a INNER JOIN suppliers b ON a.s_id = b.s_id; 复制代码

4.3.2 自连接查询

如果在一个内连接查询中,涉及到的两个表都是自身,那么这种查询称为自连接查询。

SELECT a.f_id,a.f_name FROM fruits a INNER JOIN fruits b on a.f_id = b.f_id; 复制代码

应用:比如查询树结构的时候自连接查询就非常好用

  • 查询每种类别所属的生物

要注意这里字段必须都是设为utf8mb4否则存不了中文

CREATE TABLE `self_table` (   `id` varchar(36) NOT NULL,   `name` varchar(255) DEFAULT NULL,   `pid` varchar(36) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `self_table` VALUES ('1', 'animal', '0'); INSERT INTO `self_table` VALUES ('2', 'tiger', '1'); INSERT INTO `self_table` VALUES ('3', 'lion', '1'); INSERT INTO `self_table` VALUES ('4', 'monkey', '1'); INSERT INTO `self_table` VALUES ('5', 'plant', '0'); INSERT INTO `self_table` VALUES ('6', 'pear', '5'); INSERT INTO `self_table` VALUES ('7', 'apple', '5'); 复制代码

SELECT a.name fatherName,b.name sonName FROM self_table a INNER JOIN self_table b on a.id = b.pid where a.pid = 0; 复制代码

4.3.3 外连接查询

外连接也是通过某个字段把几张表关联起来,不同于内连接的是:表与表之间即使没有关联的数据也能返回

表1 LEFT OUTER JOIN 表2:返回表1的所有数据,即使和表2没有关联

表1 RIGHT OUTER JOIN 表2:返回表2的所有数据,即使和表1没有关联

这两个可以简写成LEFT JOINRIGHT JOIN

  • 查询所有的水果,包括没有供应商的水果

SELECT a.f_name,b.s_name FROM fruits a LEFT JOIN suppliers b on a.s_id = b.s_id; 复制代码

可以看到pineApple的s_id为201,即使供应商表中没有这个id的记录,pineApple这个水果数据也返回了,如果使用INNER JOIN的话,pineApple是不会返回的。

4.3.4 复合条件连接查询

  • 在fruits表和suppliers中,使用INNER JOIN语法查询供应商表中ID为101的水果信息信息

SELECT a.*,b.s_name FROM fruits a INNER JOIN suppliers b ON a.s_id = b.s_id AND b.s_id = '101'; 复制代码

  • 在fruits表和suppliers中,使用INNER JOIN进行内连接查询,并对结果进行排序

SELECT a.*,b.s_name FROM fruits a INNER JOIN suppliers b ON a.s_id = b.s_id ORDER BY a.s_id; 复制代码

4.4 子查询

子查询就是套娃,把一个查询语句嵌套在另一个查询语句内部

创建测试环境

CREATE TABLE tb1 ( num1 INT NOT NULL ); CREATE TABLE tb2 ( num2 INT NOT NULL ); INSERT INTO tb1 VALUES(1),(5),(13),(27); INSERT INTO tb2 VALUES(6),(14),(11),(20); 复制代码

4.4.1 带ANYSOME关键字的子查询

ANYSOME的含义相同,用于WHERE语句的条件判断中,它允许创建一个表达式对子查询的返回值列表进行比较

  • 查询tb1表中的num1列,要求num1的值至少大于tb2表中的任意一个num2字段的值

SELECT num1  FROM tb1 WHERE num1 > ANY(SELECT num2 FROM tb2); 复制代码

4.4.2 带ALL关键字的子查询

ALL相比ANY就是字面意思,ANY只需要满足一个子查询返回的其中一个值就可以了,而ALL是子查询返回的值全都要满足。

  • 返回tb1表中比tb2中num2列所有值都大的值

SELECT num1  FROM tb1 WHERE num1 > ALL(SELECT num2 FROM tb2); 复制代码

4.4.3 带EXISTS关键字的子查询

(1)EXISTS

EXISTS表示只要子查询返回至少一条数据,它就返回true,否则返回false

  • 查询suppliers表中是否存在s_id=107的供应商,如果存在,就返回fruits表中所有的记录

SELECT * FROM fruits WHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = '107'); 复制代码

(2)NOT EXISTS

与上面作用相反,如果子查询没有返回任何行,就表示true,否则为false


    • 查询suppliers表中是否存在s_id=107的供应商,如果不存在,就返回fruits表中所有的记录

SELECT * FROM fruits WHERE NOT EXISTS(SELECT s_name FROM suppliers WHERE s_id = '107'); 复制代码

4.4.4 带IN关键字的子查询

(1)IN

子查询返回一串数据,然后根据字段 IN (a,b,c,...)进行查询

  • 从fruits表中查询价格低于6元的水果ID,并根据这些ID查出供应商名称

这里我们不要被题目迷惑了,首先理清主次关系,我们要查询的是供应商名称,那么suppliers表就应该是查询的主表

SELECT s_name FROM suppliers WHERE s_id IN (SELECT s_id FROM fruits WHERE f_price < 6); 复制代码

(2)NOT IN

  • 从fruits表中查询价格不低于6元的水果ID,并根据这些ID查出供应商名称

SELECT s_name FROM suppliers WHERE s_id NOT IN (SELECT s_id FROM fruits WHERE f_price < 6); 复制代码

4.4.5 带比较运算符的子查询

刚刚我们使用了像ANYALL这些关键字,其实其它的比较运算符也是可以使用的,比如<<=

  • 在suppliers表中查询s_city等于Tianjin的供应商s_id,然后在fruits中查询所有该供应商提供的水果种类

SELECT f_name FROM fruits WHERE s_id IN (SELECT s_id FROM suppliers WHERE s_city = 'TianJin'); 复制代码

4.5 合并查询结果

关键字:UNION[ALL]

对于列数和数据类型相同的多个SELECT查询,使用UNION可以把查出来的结果合并到一起。UNION不适用ALL,执行的时候自动删除重复记录,所有返回的行都是唯一的。

优化:使用UNION ALL的效率比仅使用UNION要高,因为不必去重

  • 查询所有价格小于9的水果信息,查询s_id等于101和103所有的水果信息,使用UNION连接查询结果

SELECT * FROM fruits WHERE f_price < 9 UNION ALL SELECT * FROM fruits WHERE s_id IN('101','103'); 复制代码

  • 业务代码

select     ifnull(y.counts,0) as count,x.date from     (         SELECT CONCAT(#{nowYear},'-01') AS date UNION         SELECT CONCAT(#{nowYear},'-02') AS date UNION         SELECT CONCAT(#{nowYear},'-03') AS date UNION         SELECT CONCAT(#{nowYear},'-04') AS date UNION         SELECT CONCAT(#{nowYear},'-05') AS date UNION         SELECT CONCAT(#{nowYear},'-06') AS date UNION         SELECT CONCAT(#{nowYear},'-07') AS date UNION         SELECT CONCAT(#{nowYear},'-08') AS date UNION         SELECT CONCAT(#{nowYear},'-09') AS date UNION         SELECT CONCAT(#{nowYear},'-10') AS date UNION         SELECT CONCAT(#{nowYear},'-11') AS date UNION         SELECT CONCAT(#{nowYear},'-12') AS date     )x     left join     (         select count(1)as counts,DATE_FORMAT(a.CREATE_TIME,'%Y-%m') as date         from zs_ims_equipment_work_order a         left join zs_ims_equipment_work_order_defect b on b.ID = a.WORK_ORDER_DEFECT_ID         left join zs_ims_equipment_work_order_type c on a.WORK_ORDER_TYPE_ID = c.ID         <where>             <if test="equipmentId != null">                 and a.EQUIPMENT_ID = #{equipmentId}             </if>             <if test="defectCategoryId != null">                 and b.DEFECT_CATEGORY_ID = #{defectCategoryId}             </if>             AND c.CODE = "002"         </where>         GROUP BY date     ) y     on x.date = y.date     ORDER BY x.date 复制代码

4.6 使用正则表达式进行查询

正则基本语法:

选项说明例子匹配值示例
^匹配文本的开始字符^b匹配以字母b开头的字符串book,big,banana,bike
$匹配文本的结束字符st$匹配以字母b开头的字符串test,resist,persist
.匹配任何单个字符b.t匹配任何b和t之间有一个字符bit,bat,but,bite
*匹配0个或多个在它前面的字符f*n匹配字符n前面有任意个字符ffn,fan,faan,abcn
+匹配前面的字符1次或多次ba+匹配以b开头后面紧跟至少有一个aba,bay,bare,battle
<字符串>匹配包含指定的字符文本fafan,afa,faad
[字符集合]匹配字符集合中的任何一个字符[xz]匹配x或zdizzy,zebra,x-ray,extra
[^]匹配不在括号中的任何字符[^abc]匹配任何不包含a,b,c的字符串desk,fox,f8ke
字符串{n,}匹配前面的字符串至少n次b{2}匹配2个或更多的bbbb,bbbb,bbbbb
字符串{n,m}匹配前面的字符串至少n次,至多m次,b{2,4}匹配最少2个,最多4个bbb,bbb,bbbb

了解这些规则后,其实写起来就很简单了。

  • 在fruits表中,查询f_name以字母b开头的记录:

SELECT * FROM fruits WHERE f_name REGEXP '^b'; 复制代码


作者:梅长酥
链接:https://juejin.cn/post/7028881241994706975


文章分类
代码人生
文章标签
版权声明:本站是系统测试站点,无实际运营。本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 XXXXXXo@163.com 举报,一经查实,本站将立刻删除。
相关推荐