检索数据——mysql必知必会（一）

最新推荐文章于 2022-11-30 09:42:06 发布
最新推荐文章于 2022-11-30 09:42:06 发布
阅读量760 收藏 1
点赞数
分类专栏： 数据库 文章标签： select
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40369829/article/details/78997436
版权

文章目录

检索数据

SELECT检索

  • 检索多个列。
SELECT col1, col2 FROM table;
  • 检索所有列。*通配符 **
SELECT * FROM table;
  • 检索不同的行，相同的值不重复出现。
    • DISTINCT，用在列前，针对所有列。col1和col2都相同，才认定为相同的结果。
SELECT DISTINCT col1 FROM table;
SELECT DISTINCT col1, col2 FROM table;

分页限制结果

  • LIMIT检索前4行。
SELECT col1 FROM table LIMIT 4;
  • 检索下标3（第4行）开始的4行。
SELECT col1 FROM table LIMIT 4 OFFSET 3;
SELECT col1 FROM table LIMIT 3, 4;

排序检索

排序数据

  • ORDER BY操作。
  • 先按照col1，先按照col2排序。
SELECT col1, col2, col3 FROM table ORDER BY col1, col2;

降序排序

  • DESC，用在order by的列后，针对一列
  • 按col1降序排列，col2升序排列。
SELECT col1 FROM table ORDER BY col1 DESC, col2;
  • 检索按col1排列的TOP k。
SELECT col1 FROM table ORDER BY col1 DESC LIMIT k;

limit和Order by混用时，对于没有索引的列排序可能会出现分页重复问题。参考。

过滤数据

WHERE过滤

  • WHERE操作。
SELECT col1 FROM table WHERE col1 = x;
SELECT col1 FROM table WHERE col1 < x;
  • 范围检查。BETWEEN、IN。
    • IN一般效率高。可以在IN后跟SELECT语句。
SELECT col1 FROM table WHERE col1 BETWEEN x AND y;
SELECT col1 FROM table WHERE col1 IN (x,y);
  • 空值检查。IS NULL。
SELECT col1 FROM table WHERE col1 IS NULL;
  • 使用 EXISTS 判断是否存在。可与 IS NULL 转化。
SELECT * FROM employees 
WHERE NOT EXISTS (
	SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no
);
  • 逻辑条件。AND、OR、NOT。
    • AND优先于OR，最好使用（）确定优先级。
    • NOT否定之后的条件。支持NOT IN， NOT BETWEEN。
SELECT col1 FROM table WHERE c1 AND c2;
SELECT col1 FROM table WHERE c1 OR c2;
SELECT col1 FROM table WHERE NOT c1 OR c2;

通配符过滤

  • LIKE操作符。
    • % 表示任意字符串（包括空字符串），但不能匹配NULL。
    • _ 匹配单个字符。
    • 通配符效率较低，不应过度使用。
    • 通配符置于搜索模式开始处，效率最低。
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

正则表达式过滤

  • mysql支持正则表达式的一个子集。
  • REGEXP操作，通常不区分大小写，可用BINARY区分大小写。
SELECT prod_name FROM products WHERE prod_name REGEXP '[^123] Ton' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'jetpack .000' ORDER BY prod_name;
  • 类似java里的正则表达式，特殊字符需要转义。
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
  • 字符类的使用，需要再使用一个[]。
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
  • 正则表达式的验证。返回0不匹配，1匹配。
SELECT '' REGEXP '';

计算

拼接字段

SELECT CONCAT(TRIM(vend_name),' (',TRIM(vend_country),')')  AS vend_title
FROM vendors ORDER BY vend_name;

SELECT dept_no, GROUP_CONCAT(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no;

算数计算

  • 四则运算。
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems WHERE order_num = 20005;

文本处理函数

  • 常用子串、去空格。
    textf.png
  • Soundex能对文本的发音比较。
SELECT cust_name, cust_contact FROM customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Y.Lie');

日期和时间处理函数

  • 直接 x='2005-09-01'，只有x的日期为2005-09-01且时间为00:00:00才匹配。一般使用方式为：
SELECT cust_id, order_num FROM orders 
WHERE DATE(order_date) = '2005-09-01';
  • 查询和修改日期对应的年月等。
    dateTimef.png

数值处理函数

  • 代数运算。
  • mathf.png

聚集函数

  • 按照行组汇总数据，返回结果。
  • 用在 SELECT 后面。
  • 聚集函数忽略值为NULL的行，除了COUNT(*)。
  • aggregatef.png
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min 
FROM products;
  • 汇总不同值的结果，DISTINCT。
    • DISTINCT不能用于COUNT(*)，即COUNT(DISTINCT)。
SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM products WHERE vend_id = 1003;

分组数据

  • GROUP BY操作。
    • 除了聚集函数，SELECT中的每个列都要在GROUP BY中给出。
    • WITH ROLLUP可以汇总每个分组的结果。
    • 使用ORDER BY明确分组排序。
SELECT vend_id, COUNT(*) AS num_prods FROM products 
GROUP BY vend_id WITH ROLLUP ORDER BY vend_id;

过滤分组

  • HAVING。过滤分组，语法类似WHERE。
SELECT vend_id, COUNT(*) AS num_prods FROM products 
GROUP BY vend_id HAVING num_prods > 2;
  • WHERE在分组前对行过滤，HAVING在分组后对分组过滤。

SELECT子句顺序

  • SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT。

子查询

  • 嵌套在查询中的查询。

用作过滤

  • WHERE中的列需要和子查询SELECT中列匹配。
SELECT cust_id FROM orders 
WHERE order_num IN 
( SELECT order_num FROM orderitems 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过滤，得到匹配联结条件的配对，该配对排除了重复出现的列。
  • 没有联结条件的返回结果称为笛卡尔积，行的数目为表一行数*表二行数。
  • 联结的表越多，性能下降越厉害。
  • 和子查询相比，联结有可能效率更高，但是受索引、数据量、数据类型等的影响，也可能子查询效率高。

内部联结

  • 基于两个表之间的相等测试，也叫等值联结。
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
  • 可以使用INNER JOIN连接表，ON表示联结条件。这样可以避免忘记联结条件，有时性能更优。
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

自联结

  • 联结相同的表。需要使用表的别名。
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

外部联结

  • 可以包含没有关联行的行。
  • 分为LEFT OUTER JOIN和RIGHT OUTER JOIN，语法类似内部联结。left显示左表的所有关联词，right相反。
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
  • 多个外部联结连用是需要一个LEFT JOIN 对应一个 ON，不能只在最后使用一个ON，相关
select e.last_name, e.first_name, d.dept_name
from employees e 
left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no;

使用聚集函数的联结

  • 从多个表汇总数据。
SELECT customers.cust_id ,customers.cust_name , COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

组合查询

  • 执行多个查询，并将结果作为单个查询结果返回。
  • UNION必须由两个以上SELECT组成，每个查询必须包含相同的列、表达式、聚集函数。
  • 组合查询只能在最后一个查询之后使用一个ORDER BY，即结果按照一个规则排序。
  • 使用UNION ALL包含重复的行，UNION自动去重。
    使用WHERE的多个条件无法做到包含重复的行，只能得到UNION的结果。
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. 性能较高。索引被搜索的列，正则等通常需要匹配所有行。
    2. 控制明确。可以指定什么匹配什么不匹配等。
    3. 结果智能。例如按照更好的规则排列结果。
  • 创建表时使用FULLTEXT（索引的列）。或者在导入数据之后修改表。后者效率高，因为前者在导入数据的时候需要更新索引。
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;
  • MATCH和AGAINST执行全文本搜索。
    • 返回结果按照优先级排序。该优先级由行中词的数目等因素决定。
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');

扩展查询

  • WITH QUERY EXPANSION搜索词相关的其他所有行。
    • 通过两遍扫描完成。全文本搜索找出匹配的行->选出有用的词->使用原来的条件和有用的词再次全文本搜索。
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);

布尔文本搜索

  • IN BOOLEAN MODE可以决定
    • 要匹配的词、要排斥的词。
    • 排列提示，调高或者降低词的优先级。
    • 表达式分组。
  • 布尔文本搜索无须FULLTEXT索引也可使用，但是效率会较低。
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('+heavy -rope*' IN boolean MODE);
  • boolean.png

说明

  • 短词（<=3个字符的词）被忽略。
  • 超过50%的行出现某个词，这个词为无用词。不适用于布尔文本搜索。
  • 表中行数<3，则不返回结果，因为出现的词都是无用词。
  • 词中的单引号忽略。
  • MyISAM支持全文本搜索，InnoDB不支持。
娟宝宝萌萌哒
关注
专栏目录
《MYSQL必知必会》—3~9.使用MySQL、检索数据列、排序检索数据列、过滤数据(WHERE子句、组合WHERE子句、通配符、正则表达式)
柳杰的博客
04-12 287
文章目录1.如何用USE选择数据库2.如何用SHOW查看MySQL数据库、表和内部信息3. 如何使用SELECT语句从表中检索一个或多个数据列3.1 检索单个列3.2 检索多个列3.3 检索所有列3.4 检索不同的行—DISTINCT3.5 限制结果—LIMIT3.6 使用完全限定的表名4. 如何使用SELECT语句的ORDER BY子句，根据需要排序检索出的数据4.1 排序数据4.2 按多个列排序4.3 指定排序方向—DESC与ASC5. 如何使用SELECT语句的WHERE子句过滤数据5.1 使用WHE
《MySQL必知必会》学习笔记——第8章 过滤数据（通配符、正则表达式）
qq_42325416的博客
08-09 514
文章目录第8章 过滤数据（通配符、正则表达式）1、LIKE操作符1.1 百分号（%）通配符1.2 下划线（_）通配符1.3 使用通配符的技巧2、正则表达式2.1 基本字符匹配2.2 进行OR匹配2.3 匹配几个字符之一2.4 匹配范围2.5 匹配特殊字符2.6 预定义的字符集2.7 匹配多个实例2.8 定位符 第8章 过滤数据（通配符、正则表达式） 1、LIKE操作符 通配符：用来匹配值的一部分的特殊字符。 为了在搜索子句中使用通配符，必须使用LIKE操作符。LIKE操作符指示MySQL，后跟的搜索模式利
mysql 优化器代码_Mysql查询优化器
weixin_34738304的博客
01-19 121
Mysql查询优化器本文的目的主要是通过告诉大家，查询优化器为我们做了那些工作，我们怎么做，才能使查询优化器对我们的sql进行优化，以及启示我们sql语句怎么写，才能更有效率。那么到底mysql到底能进行哪些优化那，下面通过以下几个方面来探讨一下：1常量转化它能够对sql语句中的常量进行转化，比如下面的表达式：　WHERE col1 = col2 AND col2 = 'x';...
SELECT COUNT语句三种用法
热门推荐
70大盗
10-24 3万+
数据库查询相信很多人都不陌生，所有经常有人调侃程序员就是CRUD专员，这所谓的CRUD指的就是数据库的增删改查。 在数据库的增删改查操作中，使用最频繁的就是查询操作。而在所有查询操作中，统计数量操作更是经常被用到。 关于数据库中行数统计，无论是MySQL还是Oracle，都有一个函数可以使用，那就是COUNT。 但是，就是这个常用的COUNT函数，却暗藏着很多玄机，尤其是在面试的时候，一不小心就会...
MySQL 索引使用有哪些注意事项呢?（从六个方面回答）
最新发布
youandme520的专栏
11-30 1302
如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。5.多列索引（组合）:在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。索引是一种特殊的数据库结构，有数据表中的一列或者多列组合而成，可以快速查询数据表中的值，相当于图书的目录，根据目录的页码快速找到所需内容。2.唯一性索引:唯一索引与普通索引类似，不同：索引列的值必须唯一，允许有空值。1.普通索引:普通索引是最基本的索引，它没有任何限制，值可以为空，仅加速查询。
MySQL使用索引
weixin_40486739的博客
04-09 209
1、索引的作用 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。 在数据量和访问量不大的情况下，mysql访问是非常快的，是否加索引对访问影响不大。但是当数据量和访问量剧增的时候，就会发现mysql变慢，甚至down掉，这就必须要考虑优化sql了，给数据库建立正确合理...
MySQL 中数据的检索（一：select 语句的基本用法）
zhrq95的博客
03-20 666
首先创建要用到的数据表：use zhrq95;create table T_Employee(FNumber varchar(20),FName varchar(20),FAge int,FSalary decimal(10,2),primary key (FNumber));insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘DE...
MySQL必知必会
大数据精读周刊
08-23 1万+
文章目录第一章：数据库基础基本概念什么是SQL第二章：MySQL 简介第三章：了解数据库和表第四章：检索数据SELECT语句第五章：排序检索数据找出一列中最高或最低的值第六章：过滤数据使用WHERE子句WHERE子句操作符第七章：数据过滤第八章：用通配符进行过滤LIKE操作符使用技巧第九章：用正则表达式进行搜索使用MySQL正则表达式基本字符匹配进行OR匹配匹配几个字符之一匹配范围匹配特殊字符匹配字符类匹配多个实例定位符第十章：创建计算字段计算字段拼接字段使用别名执行算术计算第十一章：使用数据处理函数使用函
读书笔记系列1——MySQL必知必会
weixin_43114740的博客
12-07 1325
读书笔记系列1——MySQL必知必会 文章目录读书笔记系列1——MySQL必知必会MySQL官方文档：https://dev.mysql.com/doc/第一章 数据库基础 *2021.11.23*概念定义：书中样例：第三章 使用MySQL *2021.11.24*关键操作Use、Show第四章 检索数据Select *2021.11.26*第五章 排序检索数据order by *2021.11.28*第六章 过滤数据1where *2021.11.29*第七章 过滤数据2组合条件 *2021.11.
读书笔记系列1——MySQL必知必会.pdf
12-07
《MySQL必知必会》这本书提供了全面的MySQL基础知识，涵盖了从数据库基础到高级功能的诸多方面。以下是根据书中的章节内容概述的一些关键知识点： 1. **数据库基础**： - **数据库**：是一个组织化的数据存储单元...
SQL必知必会--基础篇（一）
小白壮壮的博客
06-06 1万+
    最近又把《SQL 必知必会》仔细翻了一遍，因此将基础知识整理回顾，加深印象。 sql 结构化查询语言（Structured  Query Language  的缩写），用于访问和处理数据库； sql 不区分大小写，处理时空格被忽略； 多条语句必须以分号（;）分隔，建议每条语句末端都使用分号。 本篇包含知识点如图： 假设有两张表：student(sno,name,sex,age,...
Oracle Statistic 统计信息 小结
aiaix的专栏
10-04 858
一.  Statistic 说明             Oracle 官网对Statistic 有详细说明，参考：             Managing Optimizer Statistics             http://download.orac
redis实现CAS
qq_40369829的博客
12-13 1982
文章目录CASmultiwatchJedis实现 CAS mysql的UPDATE，hbase的checkAndPut提供CAS操作。 redis基于watch和multi也可以实现CAS乐观锁。 multi multi：multi开启事务（非原子性），包含多个命令。 exec触发。 可以理解为打包的批量执行脚本，某条命令执行失败不会导致之前的命令回滚，后续命令也会继续执行。 watch ...
高级特性——mysql必知必会（三）
qq_40369829的博客
01-09 1762
视图 使用 索引 使用 存储过程 使用 游标 使用 触发器 使用 事务处理 字符集和校对 安全管理 设置账户 设置账户权限 性能分析 EXPLAIN 视图 作用： 重用 sql语句。 简化复杂的sql。 保护数据，给予用户部分数据的访问权限而不是整个表。 更改数据的格式和表示。 视图不包含数据，是虚拟的表。每次使用视图时，必须处理查询的所有检索，...
redis集群方案
qq_40369829的博客
02-01 1124
文章目录redis集群架构容错机制codis架构特点参考客户端分片架构特点参考 redis集群 架构 redis集群两两之间互相通信，并将16384个哈希槽分配给每个节点。 当需要在 Redis 集群中set一个 key-value 时，redis 先对 key 使用 crc16 算法算出一个结果，然后把结果对 16384 求余数，这样每个 key 都会对应一个编号在 0-16383 之间的哈希...
事务隔离级别和锁
qq_40369829的博客
02-24 920
简介 数据库操作中为了有效保证并发读取数据的正确性，定义事务隔离级别，共有4级。 mysql默认 REPEATABLE_READ , Oracle默认 READ_COMMITTED 。 隔离级别逐渐增强，每种都是为了解决 脏读、不可重复读、幻读 中的一个问题。 READ_UNCOMMITTED 一个事务可以读取另一个未提交事务的数据。 最低隔离级别，存在脏读问题：事务读取另一个事务......
mysql（四）——快照读
qq_40369829的博客
06-10 764
文章目录简介MVCC原理回滚指针快照参考 简介 Innodb高并发的原因：所有普通select均为快照读，快照读使用 MVCC ，不加锁。 MVCC实现RC,RR隔离级别，其余两个隔离级别和MVCC无关。 MVCC原理 回滚指针 旧数据存在undo日志中，通过DB_ROLL_PTR（回滚指针）查找历史版本数据。 插入时，回滚指针为null。 更新时。排他锁锁定行，修改前的值copy到undo......
数据库相关
qq_40369829的博客
11-08 738
如何建表 HAVING Statement和PreparedStatement区别 悲观锁 与 乐观锁 的比较 悲观锁 乐观锁 关键点 数据库的三大范式 第一范式 第二范式 第三范式 参考 数据库连接池 SQL常用命令： 视图 如果视图定义中有如下子句则不能更新 优点 缺点 完整性约束 索引 索引类型（Oracle） 优缺点 实现 索引之间的关系 存储过程 优点 缺点 日...
MySQL数据库基础：查询与数据概念解析
例如，`SELECT * FROM SALES` 这条SQL命令就是一个简单的查询，它会从名为"SALES"的表中提取所有数据。查询结果并不实际存储为一个新的表，而是以虚拟表的形式呈现，每次执行查询时都会实时从数据表中提取并展示数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值