MySQL 检索数据总结

逻辑织雪使
• 阅读 1362

1. 基本查询

// 检索单个列
select prod_name from products; 

// 检索所有列
select * from products; 

// 检索不同的行
select distinct vend_id from products; 

// 限制结果
select prod_name from products limit 4,5;
1.LIMIT ：若有两个参数，则第一个参数为开始位置（从 0 行开始），第二个参数为返回结果的数量；若只有一个参数，则表示限制返回结果的数量。
2.除非确实需要表中的每个列，否则最好别使用 * 通配符，检索不需要的列会降低检索的性能。

2. 排序数据

// 对 `prod_name` 进行升序排序
select prod_name from products order by prod_name; 

//  按多个列排序： 对 `prod_price` 降序排序，对 `prod_name` 升序排序 
select prod_price, prod_name 
form products 
order by prod_price desc, prod_name;
如果没有明确规定排序顺序，MySQL 默认以数据添加到表中的顺序返回。

3.过滤数据

使用 WHERE 子句来指定搜索条件。

// 检查单个值，MySQL 在执行匹配时默认不区分大小写，因此 'jochen' 和 'Jochen' 都会匹配
select prod_name, prod_price from products where prod_name = 'jochen'; 

// 不匹配检查
select vend_id, prod_name from products where vend_id != 1000; 

// 范围检查
select prod_name, prod_price 
from products 
where prod_price between 5 and 10; 

// 空值检查
select cust_id from customers where cust_email is null; 

// AND 操作符
select prod_id, prod_price 
from products 
where vend_id = 1003 and prod_price <=10; 

// OR 操作符
select prod_name, prod_price 
from products 
where vend_id = 1002 or vend_id = 1003; 

// IN 操作符
select prod_name, prod_price 
from products 
where vend_id in (1002, 1003); 

// NOT 操作符
select prod_name, prod_price 
from products 
where vend_id not in (1002, 1003);
1.在同时使用 ORDER BY 和 WHERE 语句时，应该让 ORDER BY 位于 WHERE 之后，否则会报错
2.在 MySQL 中，AND 操作符的计算次序优先级高于 OR；可以使用圆括号()提高操作符的优先级。

4. 通配符过滤

MySQL 中有两种通配符来实现匹配值的一部分的特殊字符：

// `%` 通配符
select prod_id, prod_name from products where prod_name like 'jo%'; 

// `_` 通配符
select prod_id, prod_name from products where prod_name like '_ochen';
1.% 通配符表示任意字符出现任意次数；_ 通配符表示只匹配单个字符。
2.通配符搜索的处理一般要比普通搜索所花时间更长，因此不要过度使用通配符。

5. 正则表达式检索

正则表达式是用来匹配文本的特殊的串，通过使用 REGEXP 子句来实现。MySQL 中使用 POSIX 规范正则表达式。

select prod_name 
from products 
where prod_name regexp '1000|2000';

select prod_name 
from products 
where prod_name regexp '[123] Ton';

// 连续4位数字
select prod_name 
from products 
where prod_name regexp '[[:digit:]]{4}';

6. 计算字段

应用程序所需要的数据往往并不存在于数据库表中，我们需要从数据库中检索并进行拼接、计算、转换或者格式化等处理而得到，这就是计算字段。计算字段并不实际存在于数据库表中，而是运行时由 SELECT 语句创建。

// 拼接
select concat(vend_name, '(', vend_country, ')') as vend_title from vendors; 

// 算术计算
select prod_id, quantity, item_price quantity*item_price as expanded_price from orderitems;

7. 分组数据

分组允许把数据分为多个逻辑组，以便对每个组进行聚集计算。

// 返回每个供应商提供的产品数目
select vend_id, count(*) as num_prods 
from products 
group by vend_id; # 创建分组

// 返回至少有两个订单的所有顾客
select cust_id, count(*) as orders 
from orders 
group by cust_id 
having count(*) >= 2; # 过滤分组

// 返回总计订单价格大于等于50的订单的订单号和总计订单价格，并按总计订单价格排序
select order_num, sum(quantity*item_price) as ordertotal 
from orderitems 
group by order_num 
having sum(quantity*item_price) >= 50 
order by ordertotal;
1.GROUP BY 子句指示 MySQL 分组数据，然后对每个组而不是整个结果集进行聚集。
2.GROUP BY 子句中列出的每个列必须是检索列或者有效的表达式（不能是聚集函数），同时不能使用别名。
3.HAVING 在数据分组之后进行过滤，WHERE 在数据分组之前进行过滤。
4.一般在使用 GROUP BY 子句时，应该也给好 'ORDER BY' 子句，这是保证数据正确排序的唯一方法。

8. 子查询

MySQL 允许创建子查询，即嵌套在其他查询中的查询，例如把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。

select cust_id 
from orders 
where order_num in (select order_num from ordreitems where prod_id = 'TNT2');

// 作为计算字段使用子查询
select cust_name, cust_state, (select count(*) 
from orders 
where orders.cust_id = customers.cust_id) as orders from customers 
order by cust_name;
保证子查询中的 SELECT 语句具有与父查询中的 WHERE 子句有相同数目的列。

9. 联接表

关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值（即关系设计中的关系）互相关联。分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。如果数据存储在多个表中，怎样用单条 SELECT 语句检索出数据？答案是使用联接。简单地说，联接是一种机制，用来在一条 SELECT 语句中关联表，使用特殊的语法，可以联接多个表返回一组数据。

联接不是物理实体，它在实际的数据库表中不存在。

常用的联接类型有：

  • 内部联接（INNER JOIN）：两表执行笛卡尔积后，取列值符合查询条件的数据。
  • 左外部联接（LEFT OUTER JOIN）：指将左表的所有记录与右表符合条件的记录，返回的结果除内连接的结果，还有左表不符合条件的记录，并在右表相应列中填NULL。
  • 右外部联接（RIGHT OUTER JOIN）：
// 等值联接1
select vend_name, prod_name, prod_price 
from vendors, products 
where vendors.id = products.vend_id 
order by vend_name, prod_name;

// 等值联接2
select vend_name, prod_name, prod_price 
from vendors 
inner join products on vendors.id = products.vend_id 
order by vend_name, prod_name;
1.应该保证所有联接都 SELECT 子句，否则 MySQL 将返回比想要的数据多得多的数据（笛卡尔积）。
2.MySQL 在运行时关联指定的每个表以及处理联接，这种处理可能时非常耗费资源的。

10. 复合查询

MySQL 允许执行多个查询（多条 SELECT 语句），并将结果作为单个查询结果集返回，这些组合查询称为复合查询。有两种情况下，需要使用复合查询：

  • 在单个查询中从不同的表返回类似结果的数据；
  • 对单个表执行多个查询，按单个查询返回数据。
// 返回价格小于等于5的所有物品、并且包括供应商1001和1002生产的所有物品
select vend_id, prod_id, prod_price
from products
where prod_price <=5
union
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002);
1.UNION 中的每个查询必须包含相同的列、表达式或聚集函数。
2.在使用 UNION 复合查询是，只能使用一条 ORDER BY 子句，且必须在最后一条 SELECT 语句之后。
mysql
点赞
收藏
评论区
推荐文章
Wesley13 Wesley13
•
3年前
SIFT+BOW 实现图像检索
原文地址：https://blog.csdn.net/silence2015/article/details/77374910本文概述图像检索是图像研究领域中一个重要的话题，广泛应用于医学，电子商务，搜索，皮革等。本文主要是探讨学习基于局部特征和词袋模型的图像检索设计。图像检索概述图像检索按照描述图像不同方式可以分为两
1146
0
0
Peter20 Peter20
•
4年前
什么是索引？Mysql目前主要的几种索引类型
一、索引MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创
1851
0
0
Wesley13 Wesley13
•
3年前
MySQL数据查询（重点）
1.查询所有列  \为所有列select\fromtable\_name;2.查询指定列selectid,agefromtable\_name;3.查询时添加常量列本次查询有效，不会添加到表中，只显示 可以用as来命名本列名select'java0328'fromtable\_name;
933
0
0
Stella981 Stella981
•
3年前
Elasticsearch全文检索实战小结
一、项目概述这是一个被我称之为“没有枪、没有炮，硬着头皮自己造”的项目。项目是和其它公司合作的三个核心模块开发。 使用ES的目的是： 1）、采集数据、网站数据清洗后存入ES； 2）、对外提供精确检索、通配符检索、模糊检索、分词检索、全文检索接口等二次封装接口。二、项目架构!这里写图片描述(http
790
0
0
Stella981 Stella981
•
3年前
Lucene 全文检索入门
博客地址：http://www.moonxy.com(https://www.oschina.net/action/GoToLink?urlhttp%3A%2F%2Fwww.moonxy.com%2F)一、前言Lucene是apache软件基金会的一个子项目，由DougCutting开发，是一个开放源代码的全文检索引擎工具包
979
0
0
Wesley13 Wesley13
•
3年前
MySQL学习笔记20
二十、使用视图1、视图视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。例如：SELECTcust\_name,cust\_contact     FROMcustomers,orders,orderitems     WHEREcustomers.cust\_idorder
653
0
0
Wesley13 Wesley13
•
3年前
Hibernate中get和load方法的区别以及close()、clear()、evict()
下边详细说一下get和load的不同，因为有些时候为了对比也会把find加进来。1.从返回结果上对比：load方式检索不到的话会抛出org.hibernate.ObjectNotFoundException异常get方法检索不到的话会返回null2.从检索执行机制上对比：get方法和find方法都是直接从数据库中检索而load
793
0
0
Stella981 Stella981
•
3年前
Net Core使用Lucene.Net和盘古分词器 实现全文检索
Lucene.netLucene.net是Lucene的.net移植版本，是一个开源的全文检索引擎开发包，即它不是一个完整的全文检索引擎，而是一个全文检索引擎的架构，提供了完整的查询引擎和索引引擎，是一个高性能、可伸缩的文本搜索引擎库。它的功能就是负责将文本数据按照某种分词算法进行切词，分词后的结果存储在索引库中，从索引库检索数据的
783
0
0
Wesley13 Wesley13
•
3年前
MySQL的存储引擎InnoDB选择了B+ 树
     我们知道数据的存储和检索是两个很重要的功能，当我们的数据量大了，怎么能快速的检索数据呢，答案是使用索引，可索引具体的技术实现有很多，选择哪一种呢，我就以mysql为例记录下它为什么选择了B树作为索引的实现方式。1. 索引简介　　索引是一种用于快速查询行的数据结构，就像一本书的目录就是一个索引，如果想在一本书中找
643
0
0
Stella981 Stella981
•
3年前
Django的全文检索
我们曾经愉快的使用HaystackWhoosh作为全文检索引擎，可伴随着数据的增加，我们毫无意外的遇到了性能问题。检索时间从毫秒级一下子跌入秒级，常常一个检所需要数秒钟才能返回。而且一旦我们要对索引内容进行调整，而不得不全部重建索引，只能等待数个小时才能建好。为了解决这个问题，我们考察了众多的方案，并最终决定使用xapain替换Whoosh。考察过
585
0
0
Wesley13 Wesley13
•
3年前
MySQL学习笔记4——DQL
DQL(数据查询语言)一、基本查询1.字段(列)控制1)查询所有列SELECT\FROM表名;\"\"表示查询所有列2)查询指定列SELECT列1\,列2,...,列n\FROM表名;3)让完全重复的记录只显示一次当查询结果中的多行记录一模一样时，只显示一行。一般查询所有列时
589
0
0
逻辑织雪使
逻辑织雪使
Lv1
烽火平安夜，归梦到家山。
文章
8
粉丝
0
获赞
0
热门文章
react-webpack工程中怎么使用iconfont图标
从零开始学Spring Boot系列-集成MyBatis-Plus
weex-iOS实战
HTML中<IMG>标签onerror属性图片加载失败用法
基于rollup+typescript+gulp+less搭建react 前端组件库 （一）