ORDER BY 与 GROUP BY
ORDER BY | GROUP BY |
---|---|
排序产生的输出 | 分组行。但输出可能不是分组的顺序 |
不一定需要 | 如果与聚集函数一起使用列(或表达式)则必须使用 |
任意列都可以使用 | 只可能使用选择列或表达式列 而且必须用每个选择列表达式 |
使用 order by 排序
- SELECT order_num, SUM(quantity*item_price) AS ordertotal
- GROUP BY order_num
- HAVING SUM(quantity*item_price)>= 50
- ORDER BY ordertotal;
在这个例子中, GROUP BY 子句用来按订单号( order_num 列)
分组数据, 以便 SUM(*) 函数能够返回总计订单价格. HAVING 子
句过滤数据, 使得只返回总计订单价格大于等于 50 的订单. 最后, 用 ORDER BY 子句排序输出.
? SELECT 子句及其顺序
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或者表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
使用子查询(其实就是嵌套查询 )
- 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'))
执行从内到外
列必须匹配 在 WHERE 子句中使用子查询(如这里所示), 应
该保证 SELECT 语句具有与 WHERE 子句中相同数目的列. 通常,
子查询将返回单个列并且与单个列匹配, 但如果需要也可以
使用多个列.
- 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 vend_name, prod_name, prod_price
- FROM vendors, products
- WHERE vendors.vend_id=products.vend_id
- ORDER BY vend_name, prod_name;
内部联结
目前为止所用的联结称为等值联结(equijoin), 它基于两个表之间的相等测试. 这种联结也称为内部联结. 其实, 对于这种联结可以使用稍微不同的语法来明确指定联结的类型. 下面的 SELECT 语句返回与前面例子完全相同的数据:
- SELECT vend_name, prod_name, prod_price
- FROM vendors INNER JOIN products
- ON vendors.vend_id = products.vend_id;
联结多个表
- SELECT prod_name, vend_name, prod_price, quatity
- FROM orderitems,products,vendors
- WHERE products.vend_id = vendors.vend_id
- AND orderitems.prod_id = products.prod_id
- AND order_num = 20005;
修改原来子查询的例子
- SELECT cust_name, cust_contact
- FROM customers, orders, orderitems
- WHERE prod_id = 'TNT2'
- AND orders.order_num = orderitems.order_num
- AND customers.cust_id = orders.cust_id;
联结是 SQL 中最重要最强大的特性, 有效地使用联结需要对关系数据库设计有基本的了解. 本章随着对联结的介绍讲述了关系数据库设计的一些基本知识, 包括等值联结 (也称为内部联结) 这种最经常使用的联结形式. 下一章将介绍如何创建其他类型的联结.
一些高级联结
给表使用别名
- SELECT cust_name, cust_contact
- FROM custmers AS c, orders AS o, orderitems AS oi
- WHERE c.cust_id = o.cust_id
- AND oi.order_num = o.order_num
- AND prod_id = 'TNT2';
1. 自联结
- 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';
此查询要求首先找到生产 ID 为 DTNTR 的物品的供应商, 然后找出这个供应商生产的其他物品.
感觉好像复制了一个表 , 其实是在两个表中进行选择
2. 自然联结
- SELECT c.*, o.~~~~
- ~~~
- ;
3. 外部联结
- SELECT customers.cust_id, orders.order_num
- FROM customers LEFT OUTER JOIN orders
- ON customers.cust_id = orders.cust_id;
使用聚集函数的联结
- SELECT customers.cust_name,
- customers.cust_id
- 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 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 必须由两条或两条以上的 SELECT 语句组成, 语句之间用关
键字 UNION 分隔(因此, 如果组合 4 条 SELECT 语句, 将要使用 3 个 UNION 关键字).
? UNION 中的每个查询必须包含相同的列, 表达式或聚集函数(不过
各个列不需要以相同的次序列出).
? 列数据类型必须兼容: 类型不必完全相同, 但必须是 DBMS 可以隐含地转换的类型(例如, 不同的数值类型或不同的日期类型). 如果遵守了这些基本规则或限制, 则可以将并用于任何数据检索任务.
使用 UNION 会自动去掉多余的行
如果想要返回所有的匹配行可以使用 UNION ALL 不是 UNION
UNION 和 WHERE 完成的工作基本都一样
排序使用 ORDER BY 只能使用一次 在最后一个 SELECT 语句之后
全文本搜索
并非所有引擎都支持全文本搜索 MySQL 支持几种基本的数据库引擎. 并非所有的引擎都支持本书所描述的全文本搜索. 两个最常使用的引擎为 MyISAM 和 InnoDB , 前者支持全文本搜索, 而后者不支持. 这就是为什么虽然本书中 创 建 的 多 数 样 例 表 使 用 InnoDB , 而 有 一 个 样 例 表 ( productnotes 表) 却使用 MyISAM 的原因. 如果你的应用中需要全文本搜索功能, 应该记住这一点.
CREATE TABLE 语句 (第 21 章中介绍) 接受 FULLTEXT 子句, 它给出被索引列的一个逗号分隔的列表.
在索引之后, 使用两个函数 Match() 和 Against() 执行全文本搜索,
其中 Match() 指定被搜索的列, Against() 指定要使用的搜索表达式.
传递给 Match() 的值必须与 FULLTEXT() 定义中的相同. 如果指定多个列, 则必须列出它们(而且次序正确).
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) Against('rabbit');
查询扩展
还找出可能与你的搜索有关的所有其他行, 即使它们不包含关键词
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释 with query expansion
- Against('rabbit' WITH QUERY EXPANSION);
布尔文本搜索
即使没有 FULLTEXT 索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于, 即使没有定义 FULLTEXT 索引, 也可以使用它. 但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低).
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释 in boolean mode
- Against('rabbit' IN BOOLEAN MODE);
匹配包含 heavy 但是不包含任意以 rope 开始的词的行
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释 with query expansion
- Against('heavy -rope*' IN BOOLEAN MODE);
布尔操作符 | 说明 |
---|---|
+ | 包含,词必须存在 |
- | 排除,词必须不出现 |
< | 包含,并且增加等级 |
> | 包含,并且减少等级 |
() | 把词组成子表达式(允许这些子表达式作为一个组被包含排除、排列) |
~ | 取消一个词的排序值 |
* | 词尾通配符 |
"" | 定义一个短语(与单个词的列表不一样,他匹配整个短语以便包含或者排除) |
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释
- Against('+rabbit +bait' IN BOOLEAN MODE);
匹配包含这两个关键词的行
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释
- Against('rabbit bait' IN BOOLEAN MODE);
匹配至少一个词的行
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释
- Against('"rabbit bait"' IN BOOLEAN MODE);
把 rabbit bait 作为一个整体短语进行匹配
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释
- Against('>rabbit <bait' IN BOOLEAN MODE);
增加降低等级
- SELECT note_text
- FROM productnotes
- WHERE Match(note_text) # 注释
- Against('+rabbit +(>bait)' IN BOOLEAN MODE);
匹配这两个词 降低后面的等级
?? 在索引全文本数据时, 短词被忽略且从索引中排除. 短词定义为
那些具有 3 个或 3 个以下字符的词(如果需要, 这个数目可以更改).
?? MySQL 带有一个内建的非用词 (stopword) 列表, 这些词在索引
全文本数据时总是被忽略. 如果需要, 可以覆盖这个列表(请参阅 MySQL 文档以了解如何完成此工作).
?? 许多词出现的频率很高, 搜索它们没有用处(返回太多的结果). 因此, MySQL 规定了一条 50% 规则, 如果一个词出现在 50% 以上的行中, 则将它作为一个非用词忽略. 50% 规则不用于 IN BOOLEAN MODE .
?? 如果表中的行数少于 3 行, 则全文本搜索不返回结果(因为每个词
或者不出现, 或者至少出现在 50% 的行中).
?? 忽略词中的单引号. 例如, don't 索引为 dont .
?? 不具有词分隔符 (包括日语和汉语) 的语言不能恰当地返回全文
本搜索结果.
?? 如前所述, 仅在 MyISAM 数据库引擎中支持全文本搜索.
MySQL 总结(3)
来源: http://www.bubuko.com/infodetail-3280927.html