7. 查询数据
7.1 基本查询语句
MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是:
其中,各条子句的含义如下:
- {* | <字段列表>}包含星号通配符和字段列表,表示查询的字段。其中,字段列表至少包含一个字段名称,如果要查询多个字段,多个字段之间用逗号隔开,最后一个字段后不加逗号。
- F R O M < 表 1 > , < 表 2 > . . . FROM <表1>,<表2>... FROM<表1>,<表2>...,表1和表2表示查询数据的来源,可以是单个或者多个。
- W H E R E WHERE WHERE子句是可选项,如果选择该项,将限定查询行必须满足的查询条件
- G R O U P B Y < 字 段 > GROUP BY <字段> GROUPBY<字段>,该子句告诉MySQL如何显示查询出来的数据,并按照指定的字段分组
- [ O R D E R B Y < 字 段 > ] [ORDER BY <字段>] [ORDERBY<字段>],该子句告诉MySQL按什么样的顺序显示查询出来的数据,可以进行的排序有升序(ASC)、降序(DESC)。
- [ L I M I T [ < o f f s e t > , ] < r o w c o u n t > ] [LIMIT [<offset>,] <rowcount>] [LIMIT[<offset>,]<rowcount>],该子句告诉MySQL每次显示查询出来的数据条数。
# 创建表格
create table fruits
(
f_id char(10) not null,
s_id int not null,
f_name char(255) not null,
f_price decimal(8,2) not null,
primary key(f_id)
);
insert into fruits (f_id,s_id,f_name,f_price)
values('a1',100,'apple',5.2),('b1',101,'blackberry',9.2),('o1',102,'orange',8.2),('m1',101,'melon',7.2),('b2',103'banana',4.2),('g1',101,'grape',6.2),('c1',104,'coconut',1.2),
('c2',105,'cherry',2.2),('a2',106,'apricot',3.2),
('l1',107,'lemon',5.8),('b3',101,'berry',5.0),
('m2',108,'mango',5.3),('x1',101,'xbababa',5.9),
('x2',109,'xxtt',5.1),('x3',101,'xx',5.7);
# 使用SELECT语句查询f_id字段的数据
# 该语句的执行过程是,SELECT语句决定了要查询的列值,在这里查询f_id和f_name两个字段的值,FROM子句指定了数据的来源,这里指定数据表fruits,因此返回结果为fruits表中f_id和f_name两个字段下所有的数据。其显示顺序为添加到表中的顺序。
select f_id,f_name from fruits;
7.2 单表查询
单表查询是指从一张表数据中查询所需的数据。单表查询中的各种基本的查询方式,主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序等。
7.2.1 查询所有字段
- 在SELECT语句中使用星号(*)通配符查询所有字段
语法格式如下:select * from 表名
# 从fruits表中检索所有字段的数据
select * from fruits;
可以看到,使用星号(*)通配符时,将返回所有列,列按照定义表时候的顺序显示。
- .在SELECT语句中指定所有字段
SELECT关键字后面的字段名为将要查找的数据
# 查询fruits表中的所有数据
select f_id,s_id,f_name,f_price from fruits;
7.2.2 查询指定字段
- 查询单个字段
语法格式:select 列名 from 表名
select f_name from fruits;
- 查询多个字段
使用SELECT声明,可以获取多个字段下的数据,只需要在关键字SELECT后面指定要查找的字段的名称,不同字段名称之间用逗号(,)分隔开,最后一个字段后面不需要加逗号
select f_name,f_price from fruits;
7.2.3 查询指定记录
数据库中包含大量的数据,根据特殊要求,可能只需要查询表中的指定数据,即对数据进行过滤。在SELECT语句中,通过WHERE子句可以对数据进行过滤,语法格式为:
select 字段名1,字段名2,... 字段名n
from 表名
where 查询条件
select f_name,f_price
from fruits
where f_price=5.2;
7.2.4 带IN关键字的查询
IN操作符用来查询满足指定范围内的条件的记录,使用IN操作符,将所有检索条件用括号括起来,检索条件之间用逗号分隔开,只要满足条件范围内的一个值即为匹配项。
# 查询价格为5或者5.2 的记录
select s_id,f_name,f_price
from fruits
where f_price in(5,5.2)
order by f_name;
# 查询价格不5或者5.2 的记录
select s_id,f_name,f_price
from fruits
where f_price not in(5,5.2)
order by f_name;
7.2.5 带BETWEEN AND的范围查询
BETWEEN AND用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值和结束值,如果字段值满足指定的范围查询条件,则这些记录被返回。
select f_name,f_price from fruits where f_price between 5 and 8;
BETWEEN AND操作符前可以加关键字NOT,表示指定范围之外的值,如果字段值不满足指定的范围内的值,则这些记录被返回。
select f_name,f_price from fruits where f_price not between 5 and 8;
7.2.6 带LIKE的字符匹配查询
通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符。SQL语句中支持多种通配符,可以和LIKE一起使用的通配符有‘%’和‘_’。
- 百分号通配符‘%’,匹配任意长度的字符,甚至包括零字符
# 查找所有以‘b’字母开头的水果
select f_id,f_name
from fruits
where f_name like 'b%';
# 查询f_name中包含字母‘g’的记录
select f_id,f_name
from fruits
where f_name like '%g%';
# 查询以‘b’开头并以‘y’结尾的水果的名称
select f_id,f_name
from fruits
where f_name like 'b%y';
- 下划线通配符‘_’,一次只能匹配任意一个字符
# 在fruits表中,查询以字母‘y’结尾,且‘y’前面只有4个字母的记录
select f_id,f_name
from fruits
where f_name like '____y';
7.2.7 条件查询
-
查询空值 ,在SELECT语句中使用IS NULL子句,可以查询某字段内容为空记录
-
查询非空值
与IS NULL相反的是NOT NULL,该关键字查找字段不为空的记录。
-
带AND的多条件查询
-
带OR的多条件查询
与AND相反,在WHERE声明中使用OR操作符,表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件,多个条件表达式之间用OR分开。
-
带IN的多条件查询
OR操作符和IN操作符使用后的结果是一样的,它们可以实现相同的功能,但是使用IN操作符使得检索语句更加简洁明了,并且IN执行的速度要快于OR。更重要的是,使用IN操作符可以执行更加复杂的嵌套查询。
7.2.7 设置查询结果
- 查询结果不重复
在SELECT语句中,可以使用DISTINCT关键字指示MySQL消除重复的记录值。语法格式为:select distinct 字段名 from 表名
- 查询结果排序
ORDER BY
子句对指定的列数据进行排序。
【单列排序】
【多列排序】
- 指定排序方向
默认情况下,查询数据按字母升序进行排序(A~Z),但数据的排序并不仅限于此,还可以使用ORDER BY对查询结果进行降序排序(Z~A)。这可以通过关键字DESC
实现
【单列降序】
【多列降序】
7.2.8 分组查询
分组查询是对数据按照某个或多个字段进行分组。MySQL中使用GROUP BY关键字对数据进行分组,基本语法形式为[GROUP BY 字段] [HAVING<条件表达式>]
字段值为进行分组时所依据的列名称;“HAVING<条件表达式>”指定满足表达式限定条件的结果将被显示。
- 创建分组
GROUP BY关键字通常和集合函数一起使用,比如MAX()、MIN()、COUNT()、SUM()、AVG()。例如,要返回每个水果供应商提供的水果种类,这时就要在分组过程中用到COUNT()函数,把数据分为多个逻辑组,并对每个组进行集合计算
可以在GROUP BY子句中使用GROUP_CONCAT()
函数,将每个分组中各个字段的值显示出来。
- 使用HAVING过滤分组
HAVING关键字与WHERE关键字都是用来过滤数据的,两者有什么区别呢?其中重要的一点是,HAVING在数据分组之后进行过滤来选择分组,而WHERE在分组之前来选择记录。另外,WHERE排除的记录不再包括在分组中。
- 使用WITH ROLLUP
使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。
- 多字段分组
- 使用ORDER BY
当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的
7.2.9 限制查询结果的数量
SELECT返回所有匹配的行,有可能是表中所有的行,若仅仅需要返回第一行或者前几行,可使用LIMIT关键字,基本语法格式如下:LIMIT [位置偏移量,] 行数
第一个“位置偏移量”参数指示MySQL从哪一行开始显示,是一个可选参数,如果不指定“位置偏移量”,将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1,以此类推);第二个参数“行数”指示返回的记录条数。
7.3 使用集合函数查询
有时候并不需要返回实际表中的数据,而只是对数据进行总结。MySQL提供一些查询功能,可以对获取的数据进行分析和报告。这些函数的功能有:计算数据表中记录行数的总数、计算某个字段列下数据的总和,以及计算表中某个字段下的最大值、最小值或者平均值。
7.3.1 COUNT ()函数
COUNT()函数统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数。其使用方法有两种:
- COUNT(*)计算表中总的行数,不管某列是否有数值或者为空值。
- COUNT(字段名)计算指定列下总的行数,计算时将忽略空值的行。
7.3.2 SUM函数
SUM()
是一个求总和的函数,返回指定列值的总和。SUM()函数在计算时,忽略列值为NULL的行。
7.3.3 AVG() 函数
AVG()
函数通过计算返回的行数和每一行数据的和,求得指定列数据的平均值
7.3.4 MAX 函数
MAX
返回指定列中的最大值。
7.3.5 MIN() 函数
7.4 连接查询
7.4.1 内连接查询
内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作,并列出这些表中与连接条件相匹配的数据行,组合成新的记录,也就是说,在内连接查询中,只有满足条件的记录才能出现在结果关系中。
使用INNER JOIN
语法进行内连接查询
7.4.2 外连接查询
外连接查询将查询多个表中相关联的行,内连接时,返回查询结果集合中仅是符合查询条件和连接条件的行。有时候需要包含没有关联的行中数据,即返回查询结果集合中不仅包含符合连接条件的行,还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接
-
LEFT JOIN
(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。
左连接的结果包括LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果行中,右表的所有选择列表列均为空值。
-
RIGHT JOIN
(右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。 右连接是左连接的反向连接,将返回右表的所有行。如果右表的某行在左表中没有匹配行,左表将返回空值。
7.4.3 符合条件连接查询
复合条件连接查询是在连接查询的过程中,通过添加过滤条件限制查询的结果,使查询的结果更加准确。
使用连接查询,并对查询的结果进行排序。
7.5 子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。子查询中常用的操作符有ANY(SOME)、ALL、IN、EXISTS。子查询可以添加到SELECT、UPDATE和DELETE语句中,而且可以进行多层嵌套。子查询中也可以使用比较运算符,如“<”“<=”“>”“>=”和“!=”等。
7.5.1 带有ANY、SOME关键字的查询
ANY和SOME关键字是同义词,表示满足其中任一条件,它们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件。
ANY关键字接在一个比较操作符的后面,表示若与子查询返回的任何值比较为TRUE,则返回TRUE。
【例】返回tbl2表的所有num2列,然后将tbl1中的num1的值与之进行比较,只要大于num2的任何1个值,即为符合查询条件的结果
7.5.2 带ALL关键字的子查询
ALL关键字与ANY和SOME不同,使用ALL时需要同时满足所有内层查询的条件。例如,修改前面的例子,用ALL关键字替换ANY。ALL关键字接在一个比较操作符的后面,表示与子查询返回的所有值比较为TRUE,则返回TRUE.
【例】返回tbl1表中比tbl2表num2列所有值都大的值,SQL语句如下
7.5.3 带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此时外层查询语句将进行查询;如果子查询没有返回任何行,那么EXISTS返回的结果是false,此时外层语句将不进行查询
由结果可以看到,内层查询结果表明suppliers表中存在s_id=107的记录,因此EXISTS表达式返回true;外层查询语句接收true之后对表fruits进行查询,返回所有的记录。EXISTS关键字可以和条件表达式一起使用。
7.5.4 带IN关键字的子查询
IN
关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作。
【例】在orderitems表中查询f_id为c0的订单号,并根据订单号查询具有订单号的客户c_id
为了便于理解,该语句可以分为两步
- 首先内层子查询查出orderitems表中符合条件的订单号,单独执行内查询,查询结果如下
- 然后执行外层查询,在orders表中查询订单号等于30003或30005的客户c_id。
NOT IN
关键字,其作用与IN正好相反。
7.5.5 带比较运算符的子查询
在前面介绍的带ANY、ALL关键字的子查询时使用了“>”比较运算符,子查询时还可以使用其他的比较运算符,如“<”“<=”“=”“>=”和“!=”等
【例】在suppliers表中查询s_city等于“Tianjin”的供应商s_id,然后在fruits表中查询所有该供应商提供的水果的种类
【例】在suppliers表中查询s_city等于“Tianjin”的供应商s_id,然后在fruits表中查询所有非该供应商提供的水果的种类.
7.6 合并查询
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION不使用关键字ALL,执行的时候删除重复的记录,所有返回的行都是唯一的;使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。
语法格式:
select column, ... from table1
union [all]
select column,.. from table2
【例】查询所有价格小于9的水果的信息,查询s_id等于101和103所有的水果的信息,使用UNION连接查询结果。
select s_id,f_name,f_price from fruits where f_price<9.0
union select s_id,f_name,f_price from fruits where s_id in(101,103);
【例】查询所有价格小于9的水果的信息,查询s_id等于101和103的所有水果的信息,使用UNION ALL连接查询结果
select s_id,f_name,f_price from fruits where f_price<9.0
union all select s_id,f_name,f_price from fruits where s_id in(101,103);
7.7 为表和字段取别名
7.7.1 为表取别名
当表名字很长或者执行一些特殊查询时,为了方便操作或者需要多次使用相同的表时,可以为表指定别名,用这个别名替代表原来的名称。为表取别名的基本语法格式为表名[AS]表别名
“表名”为数据库中存储的数据表的名称,“表别名”为查询时指定的表的新名称,AS关键字为可选参数。
【例1】为orders表取别名o,查询30001订单的下单日期,SQL语句如下:
select *from orders as o where o.o_num=30001;
【例2】为customers和orders表分别取别名,并进行连接查询
select c.c_id,o.o_num from customers as c left outer join oders as o on c.c_id=o.o_id;
MySQL可以同时为多个表取别名,而且表别名可以放在不同的位置,如WHERE子句、SELECT列表、ON子句以及ORDER BY子句等。
在前面介绍内连接查询时指出自连接是一种特殊的内连接,在连接查询中的两个表都是同一个表,其查询语句如下:
select f1.f_id,f1.f_name from fruits as f1, fruits as f2 where f1.s_id=f2.s_id and f2.f_id='a1';
7.7,2 为字段字段取别名
为字段取别名的基本语法格式为:列名 [as] 列别名
“列名”为表中字段定义的名称,“列别名”为字段新的名称,AS关键字为可选参数。
【例】查询fruits表,为f_name取别名fruit_name,f_price取别名fruit_price,为fruits表取别名f1,查询表中f_price < 8的水果的名称,SQL语句如下:
select f1.f_name as fruit_name, f1.f_price as fruit_price from fruits as f1 where f1.price<8;
【例】查询suppliers表中字段s_name和s_city,使用CONCAT函数连接这两个字段值,并取列别名为suppliers_title。
select concat(trim(s_name),'(',trim(s_city),')') as suppliers_title from suppliers order by s_name;
7.8 使用正则表达式
正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串
- 查询以特定字符或字符串开头的记录字符
^
匹配以特定字符或者字符串开头的文本。
【例7.68】在fruits表中,查询f_name字段以字母‘b’开头的记录,SQL语句如下:
select *from fruits where f_name regexp '^b';
- 查询以特定字符或字符串结尾的记录
字符$
匹配以特定字符或者字符串结尾的文本。
【例7.70】在fruits表中,查询f_name字段以字母‘y’结尾的记录,SQL语句如下:
select *from fruits where f_name regexp 'y$';
- 用符号
.
来替代字符串中的任意一个字符
字符.
匹配任意一个字符。【例7.72】在fruits表中,查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录,SQL语句如下:
select *from fruits where f_name regexp 'a.g';
- 使用"“和”+"来匹配多个字符星号‘’匹配前面的字符任意多次,包括0次。
加号+
匹配前面的字符至少一次。
【例】在fruits表中,查询f_name字段值以字母‘b’开头且‘b’后面出现字母‘a’的记录,SQL语句如下:
- 匹配指定字符串
正则表达式可以匹配指定字符串,只要这个字符串在查询文本中即可,如要匹配多个字符串,多个字符串之间使用分隔符‘|’隔开。
【例】在fruits表中,查询f_name字段值包含字符串“on”的记录,SQL语句如下:
提示:LIKE运算符也可以匹配指定的字符串,但与REGEXP不同,LIKE匹配的字符串如果在文本中间出现,则找不到它,相应的行也不会返回。REGEXP在文本内进行匹配,如果被匹配的字符串在文本中出现,REGEXP将会找到它,相应的行也会被返回。
在fruits表中,使用LIKE运算符查询f_name字段值为“on”的记录,SQL语句如下:
f_name字段没有值为“on”的记录,返回结果为空。读者可以体会一下两者的区别
- 匹配指定字符中的任意一个
方括号“[]”指定一个字符集合,只匹配其中任何一个字符,即为所查找的文本。
【例】在fruits表中,查找f_name字段中包含字母‘o’或者‘t’的记录,SQL语句如下
“[a-z]”表示集合区间为从a~z的字母,“[0-9]”表示集合区间为所有数字。 - 匹配指定字符以外的字符
[\^字符集合]
匹配不在指定集合中的任何字符。
【例】在fruits表中,查询f_id字段中包含字母a~e和数字1~2以外字符的记录,SQL语句如下:
- 使用{n,}或者{n,m}来指定字符串连续出现的次数
“字符串{n,}”表示至少匹配n次前面的字符;“字符串{n,m}”表示匹配前面的字符串不少于n次,不多于m次。例如,a{2,}表示字母a连续出现至少2次,也可以大于2次;a{2,4}表示字母a连续出现最少2次,最多不能超过4次。
【例】在fruits表中,查询f_name字段值出现字母‘x’至少2次的记录,SQL语句如下:
【例】在fruits表中,查询f_name字段值出现字符串“ba”最少1次、最多3次的记录,SQL语句如下: