查询数据

7.1 基本查询语句

MySQL从数据表中查询数据的基本语句为SELECT语句。

SELECT 
        {* | <字段列表>} 
        [ 
            FROM <表1>,<表2>... 
            [WHERE <表达式> 
            [GROUP BY <group by definition>] 
            [HAVING <expression> [{<operator> <expression>}...]] 
            [ORDER BY <order by definition>] 
            [LIMIT [<offset>,] <row count>] 
        ]
SELECT [字段1,字段2,…,字段n]
FROM [表或视图]
WHERE [查询条件];

7.2 单表查询

7.2.1 查询所有字段

1．在SELECT语句中使用星号“*”通配符查询所有字段

SELECT * FROM 表名;

2．在SELECT语句中指定所有字段

SELECT f_id, s_id ,f_name, f_price FROM fruits;

7.2.2 查询指定字段

1．查询单个字段
查询表中的某一个字段，语法格式为：

SELECT 列名FROM 表名;

2．查询多个字段

SELECT 字段名1,字段名2,…,字段名n  FROM 表名;

7.2.3 查询指定记录

在SELECT 语句中通过WHERE子句，对数据进行过滤，语法格式为：

SELECT 字段名1,字段名2,…,字段名n 
FROM 表名
WHERE 查询条件

例子

查询价格为10.2元的水果的名称，SQL语句如下：

SELECT f_name, f_price
FROM fruits
WHERE f_price = 10.2;

7.2.4 带IN关键字的查询

IN操作符
查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

例子

【例7.7】查询s_id为101和102的记录，SQL语句如下：

SELECT s_id,f_name, f_price 
FROM fruits 
WHERE s_id IN (101,102) 
ORDER BY f_name;

7.2.5 带BETWEEN AND的范围查询

BETWEEN AND
查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。

例子

【例7.9】查询价格在2.00~10.20元之间的水果的名称和价格，SQL语句如下：

SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;

7.2.6 带LIKE的字符匹配查询

● 百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符

例子

查找所有以'b'字母开头的水果

select f_id,f_name from fruits where f_name Like 'b%';

查询f_name中包含字母'g'的记录，sql语句如下

select f_id,f_name from fruits where f_name like '%g%';

● 下划线通配符‘_’，一次只能匹配任意一个字符

例子

查询以字母"y"结尾且'y'前面只有4个字母的记录

select f_id,f_name from fruits where f_name like '____y';

7.2.7 查询空值

在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。

例子

【例7.15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下：

SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;

7.2.8 带AND的多条件查询

使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。

例子

【例7.17】在fruits表中查询s_id = 101并且f_price大于等于5的水果的价格和名称，SQL语句如下：

SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;

7.2.9 带OR的多条件查询

OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用AND分开。

例子

【例7.19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：

SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;

7.2.10 查询结果不重复

在SELECT语句中可以使用DISTINCE关键字指示MySQL消除重复的记录值。
SELECT DISTINCT 字段名 FROM 表名;

例子

SELECT DISTINCT s_id FROM fruits;

7.2.11 对查询结果排序

MySQL中可以通过在SELECT使用ORDER BY子句对查询的结果进行排序。

1．单列排序

select f_name from fruits order by f_name

2．多列排序

例子:查询fruits表中的f_name和f_price字段，先按f_name排序，在按f_price排序,sql语句如下

select f_name，f_price from fruits order by f_name,f_price

在进行多列排序的时候，首先排序的第一列必须具有相同的列值，才会对第二列进行排序。如果第一列所有值都是唯一的，将不再对第二列进行排序
3．指定排序方向
desc降序排序，asc升序排序(默认排序，不用写关键字也可以，将字段列中的数据按字母表顺序升序排序)

例子

对结果按f_price降序方式排序

select f_name,f_price from fruits  order by f_price desc;

先按f_price字段降序排序，再按f_name字段升序排序
desc关键字只对器前面的列进行降序排序，在这里只对f_price排序。

select f_name,f_price from fruits  order by f_price desc,f_name;

7.2.12 分组查询

分组查询是对数据按照某个或多个字段进行分组。
[GROUP BY 字段] [HAVING <条件表达式>]

1．创建分组

group by 关键字和group_concat()函数(将每个分组中各个字段的值显示出来)
例如，要返回每个水果供应商提供的水果种类,这时就要在分组过程中用到count()函数，把数据分为多个逻辑组,并对每个组进行集合计算
下方是更具s_id对fruits表中的数据进行分组，sql语句如下

select s_id,count(*) as Total from fruits group by s_id;

2．使用HAVING过滤分组

下方是更具s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息

select s_id,group_concat(f_name) as Total from fruits group by s_id having count(f_name) > 1;

3．在GROUP BY子句中使用WITH ROLLUP

下方是更具s_id对fruits表中的数据进行分组，并显示记录数量

select s_id,count(*) as Total from fruits group by s_id with rollup

4．多字段分组

下方是更具s_id和f_name对fruits表中的数据进行分组

select * from fruits group by s_id,f_name;

5．GROUP BY和ORDER BY一起使用

当使用rollup时，不能同时使用order by 子句进行结果排序，即rollup和order by时互相排斥的;
查询订单价格大于100的订单号和总订单价格,sql语句如下

select o_num,sum(quantity * item_price )as orderTotal from orderitems group by o_num having sum(quantity * item_price) >= 100;

7.2.13 用LIMIT限制查询结果的数量

LIMIT关键字可以返回指定位置的记录。
LIMIT [位置偏移量,] 行数

例子

【例7.32】显示fruits表查询结果的前4行，SQL语句如下：

SELECT * From fruits LIMIT 4;

7.3 使用集合函数查询

7.3.1 COUNT()函数

COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回的列中包含的数据行数。

COUNT(*)

查询customers表中总的行数

select count(*) as cust_num from customers;

COUNT(字段名)

查询customers表中有电子邮件的顾客的总数

select count(c_email) as email_num from customers;

7.3.2 SUM()函数

SUM()是一个求总和的函数，返回指定列值的总和。

例子

【例7.37】在orderitems表中查询30005号订单一共购买的水果总量，SQL语句如下：
mysql> SELECT SUM(quantity) AS items_total FROM orderitems
WHERE o_num = 30005;

7.3.3 AVG()函数

AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

例子

【例7.39】在fruits表中，查询s_id=103的供应商提供的水果价格的平均值，SQL语句如下：
mysql> SELECT AVG(f_price) AS avg_price FROM fruits
WHERE s_id = 103;

7.3.4 MAX()函数

MAX()函数返回指定列中的最大值。

例子

【例7.41】在fruits表中查找市场上价格最高的水果值，SQL语句如下：
mysql>SELECT MAX(f_price) AS max_price FROM fruits;

7.3.5 MIN()函数

MIN()返回查询列中的最小值。

例子

【例7.44】在fruits表中查找市场上价格最低的水果值，SQL语句如下：
mysql>SELECT MIN(f_price) AS min_price FROM fruits;

7.4 连接查询

通过连接运算符可以实现多个表查询

7.4.1 内连接查询

内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

例子

查询两个表中具有相同字段的数据;需要完全限定表名(格式为"表名.列名")

select suppliers.s_id,s_name,f_name,f_price from fruits,suppliers where fruits.s_id=suppliers.s_id;

再使用inner join语法对上面例子进行重写

select suppliers.s_id,s_name,f_name,f_price from fruits inner join suppliers on  fruits.s_id=suppliers.s_id;

标注:inner join 语法是ANSI SQL的标准规范,使用inner join 连接语法能够确保不会忘记连接条件

7.4.2 外连接查询

返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。
LEFT JOIN(左连接) 。
RIGHT JOIN(右连接) 。
Join指向哪则完全返回哪
就是把左连接有链接替换成insert join
左连接的含义:
返回左表中的记录，而右表中只返回匹配的记录；若没有匹配含行，则相关联的行中返回的是空值。
右连接的含义:
返回右表中的所有记录，而左表中只返回匹配的记录；若没有匹配含行，则相关联的行中返回的是空值。

实例

左连接
在customers表和orders表中,查询所有用户,包括没有订单的客户,sql语句如下

select customers.c_id,orders.o_num from customers left outer join orders on customers.c_id = orders.c_id;

右连接
在customers表和orders表中,查询所有订单，包括没有客户的订单，sql语句如下

select customers.c_id,orders.o_num from customers right outer join orders on customers.c_id = orders.c_id;

7.4.3 复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果。
复合条件连接查询
是在连接查询的过程中添加过滤限制条件来显示查询的结果。如and 指定id=xxx

例子

【例7.51】在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息，SQL语句如下：
mysql> SELECT customers.c_id, orders.o_num FROM customers INNER JOIN orders   ON customers.c_id = orders.c_id AND customers.c_id = 10001;

7.5 子查询

是指一个查询语句嵌套在另一个查询语句内部的查询

7.5.1 带ANY、SOME关键字的子查询

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

例子

返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何一个值,即为符合查询条件的结果

select num1 from tbl1 where num1 > any(select num2 from tbl2);

7.5.2 带ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。
ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。
ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。

例子

返回tbl1表中比tbl2表num2列所有值都大的值,sql语句如下

select num1 from tbl1 where num1 > all(select num2 from tbl2);

7.5.3 带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

例子

查询suppliers表中是否存在s_id=107的供应商,若不存在,则查询fruits表中的记录

select * from fruits where not exists(select s_name from suppliers where s_id = 107);

7.5.4 带IN关键字的子查询

IN关键字进行子查询时，内层查询语句仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

例子

【例7.58】在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有该订单号的客户c_id，SQL语句如下：

mysql> SELECT c_id FROM orders WHERE o_num IN   (SELECT o_num  FROM orderitems WHERE f_id = 'c0');

7.5.5 带比较运算符的子查询

在前面介绍的带ANY、ALL关键字的子查询时使用了>比较运算符，子查询时还可以使用其他的比较运算符，如<、<=、=、>=和!=等。

【例7.60】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类，SQL语句如下：
SELECT s_id, f_name FROM fruits
WHERE s_id =
(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

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 all连接查询结果，sql语句如下

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关键字为可选参数。

例子

为orders表取别名o,查询30001订单的下单如期,sql语句如下

select * from orders as o where o.o_num = 30001;

在为表取别名时，要保证不能与数据库中的其他表的名称冲突。

7.7.2 为字段取别名

从本章和前面各章节的例子中可以看到，在使用SELECT语句显示查询结果时，MySQL会显示每个SELECT后面指定的输出列，在有些情况下，显示的列的名称会很长或者名称不够直观，MySQL可以指定列别名、替换字段或表达式。为字段取别名的基本语法格式为：
列名 [AS] 列别名
“列名”为表中字段定义的名称，“列别名”为字段新的名称，AS关键字为可选参数。

例子

查询fruit表，为f_name取别名fruit_name,为f_price取别名fruit_price,为friuts表取别名fl,查询表中f_price小于8的水果的名称，sql语句如下

select fl.f_name as fruit_name,fl.f_price as fruit_price from frits as fl where fl.f_price < 8;

7.8 使用正则表达式查询

7.8.1 查询以特定字符或字符串开头的记录

字符‘^’匹配以特定字符或者字符串开头的文本。
字符“^”匹配以特定字符或者字符串开头的文本。

例子

【例7.68】在fruits表中，查询f_name字段以字母“b”开头的记录，SQL语句如下：

select * from fruits where f_name REGEXP '^b'

【例7.69】在fruits表中，查询f_name字段以“be”开头的记录，SQL语句如下：

select * from fruits where f_name REGEXP '^b';

7.8.2 查询以特定字符或字符串结尾的记录

字符‘$’匹配以特定字符或者字符串结尾的文本。
字符“$”匹配以特定字符或者字符串结尾的文本。

例子

【例7.70】在fruits表中，查询f_name字段以字母“y”结尾的记录，SQL语句如下：

select * from fruits where f_name REGEXP 'y$'

【例7.71】在fruits表中，查询f_name字段以字符串“rry”结尾的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'rry$';

7.8.3 用符号"."来替代字符串中的任意一个字符

字符‘.’匹配任意一个字符。
字符“.”匹配任意一个字符。
【例7.72】在fruits表中，查询f_name字段值包含字母“a”与“g”且两个字母之间只有一个字母的记录，SQL语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

7.8.4 使用"*"和"+"来匹配多个字符

星号“*”匹配前面的字符任意多次，包括0次。加号“+”匹配前面的字符至少一次。

例子

【例7.73】在fruits表中，查询f_name字段值以字母“b”开头的，且“b”后面出现字母“a”的记录，SQL语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';

7.8.5 匹配指定字符串

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用分隔符‘|’隔开。

例子

【例7.75】在fruits表中，查询f_name字段值包含字符串“on”的记录，SQL语句如下：

mysql>  SELECT * FROM fruits WHERE f_name REGEXP 'on';

7.8.6 匹配指定字符中的任意一个

方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。
方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。

例子

【例7.78】在fruits表中，查找f_name字段中包含字母“o”或者“t”的记录，SQL语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

7.8.7 匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符。
“[^字符集合]”匹配不在指定集合中的任何字符。

例子

【例7.80】在fruits表中，查询f_id字段包含字母a_e和数字12以外的字符的记录，SQL语句如下：

select * from fruits where f_id regexp '[^a-e1-2]';

7.7.8 使用{M}或者{M,N}来指定字符串连续出现的次数

“字符串{n,}”表示至少匹配n次前面的字符；“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。例如，a{2,}表示字母a连续出现至少2次，也可以大于2次；a{2,4}表示字母a连续出现最少2次，最多不能超过4次。

例子

查询f_name字段值出现字符串'ba'最少1次，最多3次的记录

select * from fruits where f_name regexp 'ba{1,3}';

7.9 GROUP BY不再隐式排序

从MySQL 8.0版本开始，MySQL对GROUP BY 字段不再隐式排序。如果确实需要排序，必须加上ORDER BY子句。
下面通过案例来对比不同的版本中GROUP By字段的排序情况。
分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表、插入数据和查询数据。

7.10 通用表表达式

公用表表达式简称为CTE（Common Table Expressions）。CTE是命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。
CTE的语法格式如下：

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

7.11 综合案例——数据表查询操作

数据表查询操作
SQL语句可以分为两部分，一部分用来创建数据库对象，另一部分用来操作这些对象，本章详细介绍了操作数据库对象的数据表查询语句。通过本章的介绍，读者可以了解到SQL中的查询语言功能的强大，用户可以根据需要灵活使用。本章的综合案例将回顾这些查询语句。

posted @ 2021-09-20 20:44 索匣阅读(441) 评论(2) 编辑收藏举报

刷新页面返回顶部

心若有她便……

个人博客网站 http://101.42.141.145/