汇总（聚合）函数
-
AVG(列名) AS 别名 ：必须给出列名，忽略列值为NULL的行，获取多列平均值需要使用多个AVG()函数
-
COUNT(*) AS 别名：对表中行的数目进行计数， 不管表列中包含的是NULL还是非空值。
COUNT(列名) AS 别名：对特定列中具有值的行进行计数，忽略NULL值 -
MAX(列名) AS 别名：必须给出列名，忽略列值为NULL的行，返回指定列中的最大值。
一般用来找出最大的数值或日期值， 但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。 在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MIN(列名) AS 别名：功能正好与MAX()功能相反，它返回指定列的最小值
-
SUM(列名) AS 别名：用来返回指定列值的和（总计），忽略列值为NULL的行
SUM()也可以用来合计计算值：
SELECT SUM(item_price*quantity) AS total_price //返回订单中所有物品价钱之和 FROM orderitems WHERE order_num = 20005; //保证只统计某个物品订单中的物品 -
利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。
-
MySQL5及后期版本聚合函数可用DISTINCT参数：
AVG(DISTINCT 列名) AS 别名：平均值只考虑各个不同的值
COUNT(DISTINCT 列名) AS 别名：只对不同的值进行计数，不能用于COUNT(*)
SUM(DISTINCT 列名) AS 别名
MAX和MIN使用DISTINCT参数没意义
分组数据
在具体使用GROUP BY子句前，需要知道一些重要的规定：
-
GROUP BY子句可以包含任意数目的列。
在MYSQL中使用GROUP BY对表中的数据进行分组时， GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里， GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。 参考：https://blog.youkuaiyun.com/u013408431/article/details/78002703这使得能对分组进行 嵌套（？），为数据分组提供更细致的控制。
-
如果在GROUP BY子句中嵌套了分组（？），数据将在最后规定的分组上进行汇总。
换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。 -
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。
如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。 -
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
（只有聚合函数的话，GROUP BY子句给出列，就根据该列分组？）。 -
如果分组列中具有NULL值，则NULL将作为一个分组返回。
如果列中有多行NULL值，它们将分为一组。 -
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前（可以和排序结合使得结果更清晰）。
WHERE过滤行，而HAVING过滤分组，HAVING支持所有WHERE操作符
HAVING和WHERE的差别：
这里有另一种理解方法，WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤。
数据分组前 ——> WHERE排除的行不包括在分组中。
这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
SELECT子句及其顺序
| 子 句 | 说 明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索（输出）的行数 | 否 |
子查询（ subquery）
-
利用子查询进行过滤
列出订购物品TNT2的所有客户 （也可用三表内部联结解决）
(1) 检索包含物品TNT2的所有订单的编号。SELECT order_num FROM orderitmes WHERE prod_id = 'TNT2';结果：order_num 20005 20007(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);结果：cust_id 10001 10004(3) 检索前一步骤返回的所有客户ID的客户信息。
SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);合并后
SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitmes WHERE prod_id = 'TNT2'));在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。
通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列（？）。 -
作为计算字段使用子查询
显示customers表中每个客户的订单总数。
(1) 从customers表中检索客户列表。SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;(2) 对于检索出的每个客户，统计其在orders表中的订单数目。
SELECT cust_name,cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders //完全限定列名 FROM customers ORDER BY cust_name;该子查询对检索出的每个客户执行一次。
相关子查询（correlated subquery） 涉及外部查询的子查询——使用完全限定列名
任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。
使用 列别名 (也称为导出列（derived column）)
1.计算字段（从数据库中检索后，经转换、计算或格式化的数据）
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
2.拼接字段
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
使用 表别名
- 多表联结时缩短SQL语句；
- 允许在单条SELECT语句中多次使用相同的表。
联结：
-
内部（等值）联结
SELECT 列名 FROM A,B WHERE A.id = B.id;（联结多表）
SELECT 列名 FROM A INNER JOIN B ON A.id = B.id; -
自联结：联结的两表为同一个表
假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。
（1）找到生产ID为DTNTR的物品的供应商
（2）找出这个供应商生产的其他物品
子查询解决：SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');自联结解决：
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'; -
自然联结
标准的联结（内部联结）返回所有数据，甚至相同的列多次出现。
自然联结排除多次出现，使每个列只返回一次。
自然联结是这样一种联结，其中你只能选择那些唯一的列。
这一般是通过对第一个表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。SELECT c.*,o.order_num,o.order_date, oi.prod_id,oi.quantity,oi.item_price FROM customer AS c,order AS o,orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB'; -
外部联结：包含了 那些在相关表中没有关联行的行 的联结
检索所有客户，包括那些没有订单的客户：SELECT c.cust_id,o.order_num //从FROM子句的左边表（customers表）中选择所有行 FROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o. cust_id;结果会出现 一条 cust_id 10002 order_num NULL的记录
使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表
（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）
左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。
因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。使用带聚集函数的联结
SELECT c.cust_id,c.cust_name COUNT(o.order_num) AS num_ord FROM customers AS c INNER JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id; SELECT c.cust_id,c.cust_name COUNT(o.order_num) AS num_ord FROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o. cust_id GROUP BY c.cust_id;
组合查询
-
通常称为并（ union） 或复合查询（compound query）
-
有两种基本情况，其中需要使用组合查询：
1.在单个查询中从不同的表返回类似结构的数据；
2.对单个表执行多个查询，按单个查询返回数据。 -
使用：给出每条SELECT语句，在各条语句之间放上关键字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);使用 UNION ALL会保留重复的行
等价于
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002);在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。
但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。 -
规则：
1.UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
2.UNION中的每个查询必须包含相同的列、表达式或聚集函数
（不过各个列不需要以相同的次序列出）。
3.列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型
（例如，不同的数值类型或不同的日期类型）。 -
组合结果的排序：
ORDER BY子句放在最后的SELECT语句之后且不允许使用多条ORDER BY子句
全文本搜索
两个最常使用的引擎MyISAM和InnoDB，前者支持全文本搜索，而后者不支持
-
全文本搜索的使用说明
1.在索引全文本数据时，短词被忽略且从索引中排除。
短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
2.MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。
如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
3.许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。
因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。
50%规则不用于IN BOOLEANMODE。
4.如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词
或者不出现，或者至少出现在50%的行中）。
5.忽略词中的单引号。例如， don’t索引为dont。
6.不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文
本搜索结果。
7.如前所述，仅在MyISAM数据库引擎中支持全文本搜索。 -
启用 全文本搜索 支持
CREATE TABLE productnotes( note_id int NOT NULL AUTO_INCREMENT, prod_id int NOT NULL, note_date datetime NOT NULL, note_text text NULL, PRIMARY KEY(note_id), FULLTEXT(note_text) ) ENGINE = MyISAM为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。
这里的FULLTEXT索引单个列，如果需要也可以指定多个列。1.在定义之后， MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。
2.可以在创建表时指定FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立即索引）。
3.如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。
应该首先导入所有数据，然后再修改表， 定义FULLTEXT。
这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。 -
进行 全文本搜索
在索引之后，使用两个函数Match()和Against()执行全文本搜索。
其中Match()指定被搜索的列， Against()指定要使用的搜索表达式。SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');(使用 note_text LIKE '%rabbit%'也可以完成，但排序不一样；
全文搜索返回的排序根据词出现的先后顺序，先出现排位等级高，先返回)
传递给Match()的值必须与FULLTEXT()定义中的相同。
如果指定多个列，则必须列出它们（而且次序正确）。
Match(note_text)指示MySQL针对指定的列进行搜索， Against(‘rabbit’)指定词rabbit作为搜索文本。
由于有两行包含词rabbit，这两个行被返回。
除非使用BINARY方式，否则全文本搜索不区分大小写。 -
使用查询扩展
找出所有提到anvils的注释。
只有一个注释包含词anvils，但还想找出可能与你的搜索有关的所有其他行，即使它们不包含词。利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
在使用查询扩展时， MySQL对数据和索引进行两遍扫描来完成搜索：
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次， MySQL检查这些匹配行并选择所有有用的词。
再其次， MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);（假装这里是结果）
结果的第二行与anvils无关，但因为它包含第一行中的两个词（customer和recommend），所以也被检索出来。
第三行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。 -
布尔搜索（即使没有FULLTEXT索引也可以使用，性能将随着数据量的增加而降低）
在布尔方式中，不按等级值降序排序返回的行
细节：
要匹配的词；
要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
表达式分组；
另外一些内容。布尔操作符 说 明 + 包含，词必须存在 - 排除，词必须不出现 > 包含，而且增加等级值 < 包含，且减少等级值 () 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等） ~ 取消一个词的排序值 * 词尾的通配符 “” 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语） 匹配包含heavy但不包含任意以rope开始的词的行:
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);匹配包含rabbit或bait的行：
‘rabbit bait’ IN BOOLEAN MODE
匹配包含词rabbit和bait的行：
‘+rabbit +bait’ IN BOOLEAN MODE
匹配包含词rabbit和bait的行,降低后者的等级：
‘+rabbit +(<bait)’ IN BOOLEAN MODE
匹配包含词rabbit和bait的行,增加前者的等级，降低后者的等级：
‘>rabbit <bait’ IN BOOLEAN MODE匹配短语rabbit bait而不是匹配两个词rabbit和bait：
‘“rabbit bait”’ IN BOOLEAN MODE
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
