《MySQL必知必会》学习笔记(1)—数据检索

2023-01-04 19:28:24

目录

简单检索数据

排序检索数据

数据过滤

过滤数据

用通配符进行过滤

用正则表达式进行搜索

创建计算字段

使用数据处理函数

汇总数据

分组数据

使用子查询

联结表

组合查询

全文本搜索

简单检索数据

  • 简单查询
--检索多列
SELECT 列名1,列名2,列名3 FROM 表名;
--检索所有列
SELECT * FROM 表名;

注意：多条SQL语句必须以分号（；）分隔；SQL语句不区分大小写，因此 SELECT与select是相同的。但为了使代码更易于阅读和调试，习惯将所有SQL关键字使用大写，而对所有列和表名使用小写。

  • 检索不同的行

SELECT返回所有匹配的行,使用DISTINCT关键字来指示MySQL 只返回不同的值。

注意：DISTINCT关键字必须直接放在列名的前面，并且应用于所有列而不仅是前置它的列。

SELECT DISTINCT 列名 FROM 表名;
  • 限制结果

SELECT语句返回所有匹配的行，如果只想返回第一行或前几行，可使用LIMIT子句。

--LIMIT 5指示MySQL返回不多于5行
SELECT 列名 FROM 表名 limit 5;
--LIMIT 5, 5指示MySQL返回从行5开始的5行
--注意：检索出来的第一行为行0而不是行1
SELECT 列名 FROM 表名 limit 5,5;

排序检索数据

  • 排序数据

为了明确地对SELECT语句检索出的数据进行排序，可使用ORDER BY子句，默认升序排序。

注意：通常，ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

--按一个列排序
SELECT 列名 FROM 表名 ORDER BY 列名;
--按多个列排序
SELECT 列名 FROM 表名 ORDER BY 列名1,列名2,列名3;
  • 指定排序方向

默认排序方向为升序(ASC关键字)，如果想进行降序排序， 就必须指定DESC关键字。

注意：DESC关键字只应用到直接位于其前面的列名。

SELECT 列名 FROM 表名 ORDER BY 列名 DESC;
--按一个列降序，一个列升序
SELECT 列名 FROM 表名 ORDER BY 列名1 DESC,列名2;

使用ORDER BY和LIMIT的组合，能够找出一个列中的最大或最小值。

SELECT 列名 FROM 表名 ORDER BY 列名 LIMIT 1;

数据过滤

过滤数据

  • 使用 WHERE 子句

在SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。

注意：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

SELECT 列名 FROM 表名 WHERE 条件;
  • WHERE 子句操作符
-- <> 和 != 均是不匹配检查
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
-- 如果将值与串类型的列进行比较，则需要限定引号
SELECT vend_id, prod_name FROM products WHERE prod_name = 'fuses';
-- 范围值检查
SELECT prod_name， prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
-- 空值检查
SELECT prod_name FROM products WHERE prod_price IS NULL;
  • AND 和 OR 操作符

AND 和 OR 用来连接WHERE子句中的多个条件，注意 AND 的优先级比 OR 高，任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符，而不要过分依赖默认计算次序。

  • IN 操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配（等同于OR）。IN取合法值的由逗号分隔的清单，全都括在圆括号中。

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
  • NOT 操作符

WHERE子句中的NOT操作符用来否定它之后所跟的任何条件。

注意：MySQL支持使用NOT对IN、BETWEEN和 EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

SELECT prod_name, prod_price FROM products WHERE vend_id  NOT IN (1002,1003) ORDER BY prod_name;

用通配符进行过滤

  • LIKE 操作符

如果在搜索子句中使用通配符，那么必须使用 LIKE 操作符。LIKE 会指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。主要有两种通配符：

  1. 百分号（%）：任何字符出现任意次数；特别注意%不能匹配NULL；
  2. 下划线（_）：只匹配单个任意字符。
  • 使用通配符技巧
  1. 不要过度使用通配符，能不用就不用；
  2. 在确实需要使用通配符时，最好不要将通配符用在搜索模式开始处；
  3. 仔细注意通配符位置。如果放错地方，可能不会返回想要的数据。

用正则表达式进行搜索

  • 基本字符匹配

MYSQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。REGEXP关键字告诉MySQL它后面所跟的东西作为正则表达式。

注意：LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。

--检索列prod_name包含文本1000的所有行
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
  • 进行OR匹配

为搜索两个串之一，使用 | 。使用 | 从功能上类似于在SELECT语句中使用OR语句，多个OR条件可并入单个正则表达式。

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000|3000' ORDER BY prod_name;
  • 匹配几个字符之一

通过指定一组用 [ 和 ] 括起来的字符来匹配特定的字符

--[123]意思是匹配1或2或3
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

注意 | 和 [ ]两者的联系：正则表达式 [123]Ton其实是 [1|2|3]Ton 的缩写。正则表达式‘1|2|3 Ton’的意思是‘1’或‘2’或‘3 ton’，需要区分于 ‘[1|2|3] Ton’。

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。比如[^123]匹配除这些字符外的任何东西。

  • 匹配范围

正则表达式 '[0-9]' 跟 ‘[0123456789]’ 一样，会匹配数字0到9。另外，[a-z]匹配任意字母字符。

  • 匹配特殊字符

为了匹配特殊字符，必须用 \\ 为前导，比如\\- 表示 -，\\. 表示。

  • 匹配字符类

《MySQL必知必会》学习笔记(1)—数据检索

  • 匹配多个实例
《MySQL必知必会》学习笔记(1)—数据检索  
  • 定位符

《MySQL必知必会》学习笔记(1)—数据检索

创建计算字段

计算字段是运行时在SELECT语句内创建的，它实际并不存在于数据库表中。

  • 拼接字段

假设有一张vendors表包含供应商名和位置信息，我们需要要生成一个供应商报表，在供应商的名字中按照name(location)这样的格式列出供应商的位置。解决办法是把两个列拼接起来，用Concat()函数来拼接两个列，拼接的列用AS指定别名。

SELECT Concat（vend_name, '(', vend_country, ')'）AS vend_title FROM vendors ORDER BY vend_name;
  • 执行算术计算

计算字段的另一常见用途是对检索出的数据进行算术计算。

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded price FROM orderitems WHERE order_num = 20005;

使用数据处理函数

  • 文本处理函数

《MySQL必知必会》学习笔记(1)—数据检索

《MySQL必知必会》学习笔记(1)—数据检索

--WHERE子句使用Soundex()函数来转换cust_ contact列值和搜索串为它们的SOUNDEX值
--会匹配所有发音类似于Y.Lie的联系名
SELECT cust_name, cust_contact FROM customers WHERE Soudex(cust_contact) = Soudex( 'Y.Lie');
  • 日期和时间处理函数

《MySQL必知必会》学习笔记(1)—数据检索

--Date(order_date)指示MySQL仅提取列的日期部分
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
--检索出年份为2005和月份为9的日期
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 and Month(order_date) = 9;
  • 数值处理函数

《MySQL必知必会》学习笔记(1)—数据检索

汇总数据

  • 聚集函数

《MySQL必知必会》学习笔记(1)—数据检索

count()有两种用法，一种是使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值；另一种是和其它函数一样，count(column)对指定列计数，忽略null值。

  • 聚集不同值

对聚集函数都可以指定DISTINCT参数，从而只对不同的值进行汇总。注意：不允许使用COUNT(DISTINCT),只能在指定列名的情况下使用。

--计算不同产品的平均价格
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
  • 组合聚集函数

SELECT语句可根据需要包含多个聚集函数 

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;

分组数据

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

  • Group by 子句
--GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

使用Group by 子句的规定：

  1. group by子句可以包含任意数目的列；
  2. group by子句列出每个列都必须是检索列或有效表达式（但不能是聚集函数）；
  3. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出；
  4. 如果分组列中具有NULL值，则NULL将作为一个分组返回；
  5. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

注意：一般在使用GROUP BY子句时，应该也给出ORDER BY子句，这是保证数据正确排序的唯一方法

  • Having子句

HAVING非常类似于WHERE，两者唯一的差别是WHERE过滤行，而HAVING过滤分组。WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

SELECT子句顺序总结：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER  BY、LIMIT

使用子查询

  • 利用子查询进行过滤
--子查询一般与IN操作符结合使用
select cust_name, cust_contact from customers where cust_id in(
             select cust_id from orders where order_num in(
                      select order_num from orderitems where prod_id = 'TNT2'));
  • 作为计算字段使用子查询
--显示customers表中每个客户的订单总数
select cust_name, cust_state, (select count(*) from orders where orders.id = customers.cust_id) as orders from customers order by cust_name;

联结表

  • 内部联结

内部联结又称为等值联结，将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。有两种语法，建议使用第二种，使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。需要注意的一点是，使用INNER JOIN联结时，联结条件用特定的ON子句而不是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明确联结类型
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;
  • 外部联结

外部联结中包含了在相关表中没有关联的行。用LEFT/RIGHT OUTER JOIN 指定表之间的关系，用ON子句指定联结条件。

《MySQL必知必会》学习笔记(1)—数据检索

组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。

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);

UNION使用规则：

  1. UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔；
  2. UNION中的每个查询必须包含相同的列、表达式或聚集函数（顺序不要求相同）；
  3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型；

注意：UNION会从查询结果集中自动去除重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样），如果想返回所有匹配行，可使用UNION ALL。另外，在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

全文本搜索

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。

  • 启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句（不要在导入数据时使用FULLTEXT，应该首先导入所有数据，然后再修改表，定义FULLTEXT），它给出被索引列的一个逗号分隔的列表。如图所示，创建表时，MySQL会根据子句FULLTEXT(note_text)的指示对它进行索引，在定义之后，MySQL会自动维护该索引。

《MySQL必知必会》学习笔记(1)—数据检索

需要注意的一点是，两个最常使用的引擎为MyISAM和InnoDB，MyISAM支持全文本搜索，而InnoDB不支持。

  • 进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式

select note_text from productnotes where Match(note_text) Against('rabbit');

注意：传递给 Match() 的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。另外，全文本搜索不区分大小写。

SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';

上面两条select语句的作用相同，但结果的次序不同。全文本搜索的一个重要部分就是对结果排序，具有较高等级的行先返回。

  • 使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围，查询结果按照等级从高到低依次排序。

select note_text from productnotes where Match(note_text) Against('anvils' with query expansion);
  • 布尔文本搜索
--匹配包含heavy但不包含任意以rope开始词的行
select note_text from productnotes where  Match(note_text) Against('heavy -rope*' in boolenan mode);

《MySQL必知必会》学习笔记(1)—数据检索

上一篇：python 读入


下一篇：狄利克雷生成函数