首页 > 代码库 > MySQL1-基础知识点2-查询

MySQL1-基础知识点2-查询

本文中有一些例子,例子中假设DB为test表名为user。


1、语法
(1)select 列名称 from 表名称 [查询条件]:列名称可以包含多个,用逗号隔开;可以用通配符,如*查询表中所有内容,列的顺序一般是按照表定义的顺序(除非确实需要每个列,否则尽量不要使用,可能影响性能)。无论哪些列,检索出的行的顺序是不确定的。
(2)select distinct 列名称 from ……:只返回不同的列;如果查询多列,则只有当多列都相同时,相应数据才会被隐藏,即只有有一列不同,数据都会显示。
(3)select XX from XX limit 5:只返回前5行(不够5行就返回所有行);如果是limit 3,5,则从行3(第4行)开始返回5行【可以实现分页查询】
(4)select user.name from test.user:可以对表名或者列名进行完全限定,也可以两者都完全限定(例子所示)。
(5)补充:虽然select常用来从表中检索数据,但也可以省略from子句以便简单地访问和处理表达式;如select 3*3返回9,select now()返回当前日期和时间;因此select经常用来测试。

 

2、按条件查询:where 条件
(1)支持:运算符(=/!=/<>/</<=/>/>=)、扩展运算符(is [not] null,in,like,between)、and/or组合查询、not
(2)a in (‘a‘,‘b‘,‘c‘);a是‘a‘,‘b‘,‘c‘其中一个;a between 10 and 20。
(3)and/or计算次序:先计算and,再计算or;可以使用括号。如where con1 or con2 and con3,则返回满足con1,或者同时满足con2和con3的列;而where (con1 or con2) and con3可以改变这种行为。
(4)not:可以对后面的in、between、exists子句取反,而不是所有子句;如where lastname not in (‘li‘,‘wang‘)
(5)like:可以使用通配符。%通配符可以匹配任意字符任意多次,如a like ‘%a%‘表示a中含有‘a‘即可;‘%‘可以匹配任何值,但是不能匹配null。_通配符只能匹配任意单个字符。【通配符应尽量少使用,因为搜索比较慢;尽量避免将通配符放在搜索的开始处,这种情况是最慢的】【like ‘a‘则只能匹配‘a‘】
(6)注意,当查询时对空的判断使用的是is null与is not null;但是在修改数据库时,使用的是set column_name = null。null代表的是未知,因此查询时使用匹配过滤(=)或不匹配过滤(!=)时,都不会返回null值。

 

3、select count() from table_name [where...];
(1)查询数据表中满足条件记录数目。
(2)select count()括号中的内容很随意(但是不能为空);原理是判断查询到的记录中,对应的值是否为null,如果不为null,则该记录计数;因此count(1)、count(*)、count("anything")等都可以返回所有满足要求记录数(即便该记录所有列都为null,也会被计数),而count(列名)返回满足要求的记录中对应列不为null的记录数,而count(null)返回值为0。
(3)常使用select count(distinct+列名),返回满足要求的记录中对应列不为null且不重复的记录数。

 

4、聚合函数/聚集函数
(1)sum()/max()/min()/avg(),还有一些标准差的,略【count()也算聚合函数,不在本小节讨论范围】【没有group by时,聚合函数对所有检出行进行计算;有group by时,聚合函数对每个分组进行计算】
(2)语法:select sum(salary) from table_name where ……
(3)()内对应表达式(不一定是列名),但是只能对应一个,不能为空或多个;四个函数都会忽略null行。
(4)distinct:对五个函数(包括count),都有两种模式,all或distinct,前者对所有行计算;distinct只计算值不同的行;使用distinct时,后面必须是列名,不能是表达式;max()/min()使用distinct并不错,但是没有意义。
(5)max()/min()一般用于数值与日期,用于文本结果不好说。

 

5、gourp by
(1)对满足条件的记录进行分组,在每个分组内对结果进行统计;group by子句可以包含任意数目的列,每个列完全一样才是一组,称为嵌套分组;group by后面可以是列名、普通函数,不能是聚合函数;如果分组列中具有null值,则该列中所有null行(在该列的分组中)被分作一组;select的表达式,包括列名、*、函数(普通函数或聚合函数),不受group by的影响,都是合法的,这一点跟老版本不太相同。
(2)注意,如果select子句中使用的列名,并不是group by使用的列名,仍然是每个组只返回一个结果,而不是全部返回。如下语句,对每个dept,只返回一个name。
SELECT name from staff group by dept;
(3)having与where:having支持所有的where操作符;但where的筛选是在group分组之前对整个表进行筛选,而having是在分组之后;where条件不可以使用聚合函数,having可以使用聚合函数(对组内的所有行进行聚合计算)。
(4)with rollup:当使用多个列进行分组时,加上[with rollup]可以给出使用单个列分组的统计数据。举例如下:

mysql> select dep,pos,avg(sal) from employee group by dep,pos with rollup; 
+------+------+-----------+ 
| dep | pos | avg(sal) | 
+------+------+-----------+ 
| 01 | 01 | 1500.0000 | 
| 01 | 02 | 1950.0000 | 
| 01 | NULL | 1725.0000 | 
| 02 | 01 | 1500.0000 | 
| 02 | 02 | 2450.0000 | 
| 02 | NULL | 2133.3333 | 
| 03 | 01 | 2500.0000 | 
| 03 | 02 | 2550.0000 | 
| 03 | NULL | 2533.3333 | 
| NULL | NULL | 2090.0000 | 
+------+------+-----------+ 
10 rows in set (0.00 sec) 

需要注意的是,使用有 WITH ROLLUP 子句的 GROUP BY 语句时,不能再使用 ORDER BY 语句对结果集进行排序,如果对返回的结果顺序不满意,需要应用程序获得结果后在程序中进行排序。具体见网址http://blog.csdn.net/id19870510/article/details/6254358
(5)虽然group by输出结果的顺序一般是按照分组,但是不一定,如果需要明确的输出顺序,一定要加上order by。

 

6、order by
(1)对查询结果进行排序;排序列可以与查询列不同;可以有多个列,用逗号隔开,先按前面的排序,如果前面相同,再按后面排序;order by子句必须在from子句之后。
(2)desc:指定排序方向;默认是asc,无需指定;每个desc只对它前面的一列起作用,若要多个列降序,每个列指定一个desc
(3)区分大小写:MySQL默认A和a相同,无法通过order by子句区分;如果需要改变这种行为,需要改变数据库的设置。
(4)order by与limit:逻辑上,先排序再给出前几个(可以用来找最大最小值);语法上,order by必须在limit之前。
(5)group by与order by一起使用:语法上,group by必须在order by之前;逻辑上,先进行分组,再按照分组进行排序。
(6)如果没有order by,检索的结果如何排序呢?官方的说法是,Innodb默认以主键排序,但是这个不能保证,不应依赖这个作为排序顺序。对于MyIsam,网上说法是默认以插入顺序排序,但是如果update,不能保证。
(7)执行顺序总结:where>group by>having>order by>limit【distinct???】
语法:select>from>where>group by>having>order by>limit【聚合函数既可以在select子句中做结果,也可以在having/order by(分组)子句中做条件】

 

7、查询结果数目(行数)总结
(1)没有group by时:如果select子句只有列名/普通函数,则全部显示;如果select子句只有聚合函数,则只显示一个;如果既有列名,又有聚合函数,则只显示一个。
(2)group by存在时:无论select子句是什么,显示结果的数目与满足查询条件(包括where和having)的组的数目一致。

 

8、联结 /多表查询
(1)背景/意义:关系表的设计时保证把信息分解成多个表,一个数据一个表;各表通过某些关系(列)互相关联。而联结是一种机制,可以在一条select语句中关联表,因此称之为联结。应该注意,联结不是物理实体,在实际的数据库表中不存在;由MySQL根据需要建立,存在于查询的执行过程中。
(2)创建联结
形式:既可以使用where子句,也可以使用join+on的形式(join+on也可与where子句配合,不冲突),如下例;不同的方法可能影响性能。
select vend_name, prod_name, prod_price
from vendors,products
where vendors.vend_id=products.vend_id
select vend_name, prod_name, prod_price
from vendors join products
on vendors.vend_id=products.vend_id
联结条件:不论是使用where,还是使用join+on,联结的条件不仅可以是=,也可是其他条件。
联结表的个数:对于联结表的数目没有限制,但是联结的表的数目越多,性能下降越厉害,应该谨慎使用。
(3)内部联结与外部联结:内部/外部联结的定义,与使用where还是join+on无关,与联结的条件无关(虽然一般是=,但不尽然),只与是否包含基础表中不满足条件的列有关。具体详见“内连接与外连接”。
(4)自联结
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‘
(5)自然联结:排除多次出现,使每个列只返回一次;非自然联结很少碰到,暂时不做了解。
(6)使用带聚集函数的联结【如果没有group by子句,则由于select中有count()函数,所以只会返回一行数据,其中cust_id为customers的第一个cust_id,而count()为订单总数】
select customers.cust_id, count(orders.order_num)
from customers join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id
(7)性能考虑:对于同一个查询功能,可以使用多种方式实现,如子查询(子查询所在位置也可以不同)、联结(包括使用where或join+on)、联合查询、吊炸天的where语句等;不同的方式查询性能不同,甚至可能差异较大。然而,不同方式性能可能受到操作类型、表中数据量、是否存在索引以及其他一些条件的影响,且不同方式受不同参数影响的方式不尽相同。因此,当所需查询可能需要较长时间时,应通过实验比较不同实现方式,选出查询速度最快的一种。如,要查询订购了TNT2的客户列表,可以使用以下两种方式:

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‘);
select cust_name,cust_contact
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
	and orderitems.order_num = orders.order_num
	and orderitems.prod_id = ‘TNT2‘;

(8)应该注意的是,联结强调的是表之间的联系;而没有锁死where/join+on的表达能力,即多表查询中可以不包含联结,也就是说多表查询(where/join+on)的条件可以只涉及单个表,而不一定非要涉及两个表。

行数:取决于条件,如果查询条件是涉及多个表的查询,则返回满足结果的总条数(两个表组合);如果查询条件是单个表或没有查询条件,则结果数目是两个表满足条件的相乘。
列数:取决于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-查询