MySQL1-基础知识点2-查询

行数：取决于条件，如果查询条件是涉及多个表的查询，则返回满足结果的总条数(两个表组合)；如果查询条件是单个表或没有查询条件，则结果数目是两个表满足条件的相乘。
列数：取决于select子句，如果为*，则将A,B的列并列排列；否则按照select子句规定的排列。

9、子查询
（1）查询结果作为一个新的表：尽量不要这样用，效率比较低；可以的话尽量使用AND代替
错误用法
SELECT * FROM (SELECT * FROM `user` WHERE `name`=‘465465456‘) WHERE id<‘20‘
报错：Every derived table must have its own alias(别名)
正确用法
SELECT * FROM (SELECT * FROM `user` WHERE `name`=‘465465456‘)AS t WHERE id<‘20‘#方法1
SELECT * FROM (SELECT * FROM `user` WHERE `name`=‘465465456‘)t WHERE id<‘20‘#省略AS
（2）查询结果在where子句中，where子句的列数与子查询中的select子句的列数应一致(可以大于1个)
select * from `user` where id=(select id from `user` where name=‘465465457‘); //=
select * from `user` where id in (select id from `user` where name!=‘465465457‘);//in
select * from user where id=(select max(id) from `user`);
select * from user where id=max(id); //错误用法
（3）作为计算字段使用子查询：对于每行数据(有分组时应是每组数据)，都执行一次子查询【可以理解为，每行/组数据作为每个子查询的常量/参数】第二种方式也可以实现查询每个客户订单数的功能，但是不能同时查询出客户的id和name信息。
select cust_id,
cust_name,
(select count(*) from orders where order.cust_id = customers.cust_id) as order_count
from customers;
select count(*) from orders where cust_id in (select cust_id from customers)

10、正则表达式
（1）MySQL仅支持多数正则表达式实现的一个很小的子集；MySQL中的正则表达式不区分大小写，为了区分大小写，要使用BINARY关键字。
（2）语法与一般正则表达式类似，注意几点：除非把|括在一个集合中，否则它将应用于整个串，而表示个数的系列(?+*等)则应用于前一个字符。[[:<:]]和[[:>:]]分别匹配词的开始和结尾。
（3）示例(下例中匹配name含有1000的数据，而like ‘1000‘则只能匹配‘1000‘)
where name REGEXP ‘1000‘
where name REGEXP BINARY ‘Jack‘
select ‘hello‘ REGEXP ‘[0-9]‘#返回0
select ‘hello‘ REGEXP ‘[a-z]‘#返回1

11、计算字段/field
（1）概述：作用是可以直接在DB中检索出转换、计算或格式化过的数据(这比在客户机上计算要快很多，因为DBMS经过了优化)；计算字段并不存在于数据库表中，而是运行时在select语句内创建；在客户机角度看，计算字段的数据与其他列的数据以完全相同的方式返回，只有DB知道其中差别。
（2）别名：字段的替换名(当然也可以用做普通列的替换名)；如果不加as子句，则客户端看到的返回的结果的标题是concat(last_name," ",first_name)，加上之后，则标题是full_name。
select concat(last_name," ",first_name) as full_name from user where ……
（3）举例：concat()、trim()、LTrim()、RTrim()、+-*/【更多函数介绍见函数相关章节】

12、组合查询/union
（1）定义：使用union将多个select语句链接起来，并将结果作为单个查询结果返回。
（2）使用限制：每个查询必须包含相同的列、表达式或聚合函数，但是给出的列的次序不要求相同(以名字进行对应)，名字相同的列的类型不要求相同，但是必须互相兼容；可以从相同或不同的表查询。
（3）union all：当通过同一个表查询时，默认情况下，会将重复的数据去掉；但是如果使用union all，会保留重复数据。
（4）union与where：一般情况下，union的功能通过where or可以实现；但是某些复杂情况，不能通过where or实现，如union all。此外，二者的查询性能也不相同。
（5）union与order by：只能加在最后一个查询后面(个人猜测，当查询使用一个表时，可以使用非查询列排序；当查询多个表时，只能使用查询列排序)

13、全文本搜索
为什么有了like和正则表达式，还需要全文本搜索？
1、快速：全文本搜索使用了索引，而且索引是针对词进行的，搜索也就可以针对词进行；可以快速判断是否匹配、匹配频率等。
因此更迅速；以个人不认同这一点，因为like和正则表达式也可使用索引
2、匹配更精确：使用布尔全文本匹配，更加精确
3、结果显示更加智能：查询扩展可以查相关行；可以按照匹配程度进行排序等

如何使用全文本搜索
1、创建表格时指定列：可以指定多列；可以创建时不指定后来再指定。在定义之后，MySQL会自动维护该索引，增加、删除、更新行时，索引随之更新。提示：如果导入大量数据，建议先关掉索引，在导入完成后再重新建立索引；效率较高。
CREATE TABLE productnotes(
note_id int,
note_text text,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
2、一般全文本搜索
（1）MATCH()指定被索引的列，如果FULLTEXT指定了多个列，则MATCH()也必须指定多列，而且顺序必须相同。
（2）AGAINST()指定匹配的表达式。
（3）除非使用BINARY方式(与布尔文本搜索是两回事)，否则不区分大小写。
（4）与LIKE相比，区别搜索会按照匹配的良好程度排序，排序与许多因素有关，如匹配频率、出现位置等。
#全文本搜索在where子句中
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘world‘)
#全文本搜索在select子句中
SELECT note_text, MATCH(note_text) AGAINST(‘world‘) AS rank FROM productnotes
3、查询扩展：扫描两次，步骤为：(1)首先基本全文本搜索，找出匹配行(2)在匹配行中找出有用的词(3)再次全文本搜索，不仅匹配原始条件，还匹配找出的有用词
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘world‘ WITH QUERY expansion)
4、布尔文本搜索
（1）更加精确，可以指定要批评的词、要排斥的词、排列顺序的提示、表达式分组等；下例是一部分。
（2）没有fulltext也可以使用布尔方式但是太慢
（3）不按等级制降序排列返回的行(但是可以查看得分值)
（4）50%规则无效(该规则见后文)
#含world不含apple
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘world -apple‘ IN boolean MODE)
#world、hello含一个即可
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘world hello‘ IN boolean MODE)
#同时含world和hello
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘+world +hello‘ IN boolean MODE)
#world对排序影响更大，hello对排序影响更小
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘>world <hello‘ IN boolean MODE)
#hello world作为词组匹配
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘"hello world"‘ IN boolean MODE)
#以hello开头的单词
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST(‘hello*‘ IN boolean MODE)

注意事项
1、MyISAM支持，InnoDB暂不支持(以后的版本就不一定了)
2、全文本搜索是对单词进行索引的，而不是整行数据：不具有词分隔符的语言(如日语和汉语)全文本搜索结果很奇怪；短单词被忽略不被索引(短单词默认定义为<=3的单词)。
3、MySQL有个内建的非用词列表，在索引时会被忽略。注意这一点，某些很常用的词会被忽视，如hello、this等。
4、50%规则：如果一个词出现在50%以上的行中，作为非用词忽略；不适用于布尔模式。
5、词中单引号被忽略，如don‘t索引为dont。
6、MySQL暂不支持临近搜索，以后版本可能会支持。

14、视图【通常与多表查询有关】
什么是视图
视图是虚拟的表
视图是用来查看存储在别处的数据的一种设施；视图长得像一个表，其实是MySQL的select语句的包装。视图不包含表中应该有的任何列和数据，包含的是一个sql查询。
注意：在select中，视图用起来跟表似的，不特殊说明，用法类似。
为什么使用视图
1、简化复杂的SQL操作：视图(表)中可以实现过滤、格式化、计算字段等
2、保护数据：可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
示例：productcustomers就像是一个有2列(cust_name,prod_id)的表

#不使用视图
select cust_name
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
and orderitems.prod_id = ‘2000‘;
#创建视图
CREATE VIEW productcustomers AS
select cust_name, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
#使用视图查询
SELECT cust_name FROM productcustomers WHERE prod_id=‘2000‘
#查看视图
SHOW CREATE VIEW productcustomers
#删除视图
DROP VIEW productcustomers;
#修改视图
CREATE OR REPLACE VIEW ……

视图可重用

在创建视图时，不应该把查询条件写的太死，这样不方便复用，如把上述写成这样，就无法复用了。
CREATE VIEW productcustomers AS
select cust_name, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
and orderitems.prod_id = ‘2000‘
更新视图
通常来说，是可以更新视图的；更新包括insert/update/delete。更新是针对基表的数据进行的；如果MySQL不能确定要更新的基数据，则不能更新。
如果视图中包含以下操作，则不能更新：分组、联结、子查询、并、聚集函数、distinct、导出(计算)列。
视图一般用来查询，较少用来更新。
注意事项
1、视图可能包含复杂的sql语句，而视图看起来很简单；因此，对于包含视图较多的应用，注意测试性能。
2、视图命名唯一，不能与其他视图、表重名。
3、视图可以嵌套。
4、order by可以用在视图中，但是如果从该视图检索数据的select语句中也包含order by，视图中的order by将被覆盖。
5、视图不能索引，也不能有关联的触发器和默认值。
6、如果select中和视图中都有where子句，则两组子句将自动组合。

15、游标
什么是游标
游标类似于迭代器，作用的对象是select查询的结果。
使用select语句，只能得到结果集的所有行；而使用游标可以定位第一行、下一行、或前n行，也可以每次一行的对数据进行处理。
典型示例

drop procedure if exists processOrders;
CREATE PROCEDURE processOrders()
BEGIN
	DECLARE i INT;
	DECLARE o INT;
#定义/声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
#打开游标
	OPEN ordernumbers;
	SET i=1;
	WHILE i<3 DO
		FETCH ordernumbers INTO o;
		INSERT INTO ordercopy VALUES(o);
		SET i = i + 1; 
END WHILE ;
#关闭游标
	CLOSE ordernumbers;
END;

说明：

1、游标使用前必须声明，声明并没有检索数据，只是定义了要使用的select语句。注意，declare语句有严格次序要求：局部变量在游标之前，游标在句柄之前。
2、每次使用，必须先打开；但是声明一次就够了。游标需要关闭以释放内部资源，但是遇到end会自动关闭。
3、fetch语句指定使用哪个游标(哪些列)，以及将数据存储在哪里；并向前移动游标中的内部行指针。
注意事项
1、MySQL游标目前(5.6)只能用于存储过程/函数。
2、游标可以用于多列吗？当然可以。

16、sql_no_cache
（1）sql_no_cache的真正作用是禁止缓存查询结果，但并不意味着cache不作为结果返回给query。
（2）reset query cache可以清理缓存，或者对表的修改也会清理相关缓存。

MySQL1-基础知识点2-查询

标签：区分大小写   链接   包装   理解   reg   不同   函数   isa   its