MySQL 检索数据总结

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 BYWHERE 语句时,应该让 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 语句之后。

PHP 学习总结
研读官网文档和网络优秀个人文章,对 PHP 编程相关作出学习总结。

Stay hungry, stay foolish.

846 声望
84 粉丝
0 条评论
推荐阅读
PHP 微服务集群搭建
微服务架构是一种架构模式,它提倡将单一应用程序划分成一组小的服务,服务之间相互协调、互相配合,为用户提供最终价值。每个服务运行在其独立的进程中,服务和服务之间采用轻量级的通信机制相互沟通。每个服务...

Jochen112阅读 14.2k评论 6

万字长文~vue+express+mysql带你彻底搞懂项目中的权限控制(附所有源码)
所谓的权限,其实指的就是:用户是否能看到,以及是否允许其对数据进行增删改查的操作,因为现在开发项目的主流方式是前后端分离,所以整个项目的权限是后端权限控制搭配前端权限控制共同实现的

水冗水孚11阅读 1.5k

花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

民工哥11阅读 1.1k

封面图
一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山8阅读 2.2k评论 2

2023最新MySQL高频面试题汇总
本文已经收录到Github仓库,该仓库包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点,欢迎star~

程序员大彬3阅读 978

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 936

封面图
初学后端,如何做好表结构设计?
这篇文章介绍了设计数据库表结构应该考虑的4个方面,还有优雅设计的6个原则,举了一个例子分享了我的设计思路,为了提高性能我们也要从多方面考虑缓存问题。

王中阳Go2阅读 407评论 1

封面图

Stay hungry, stay foolish.

846 声望
84 粉丝
宣传栏