MySQL查询数据

发表于 2022-09-08 更新于 2022-09-09 本文字数： 12k 阅读时长 ≈ 11 分钟

查询数据

数据库管理系统的一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作，本章将介绍如何使用SELECT语句查询数据表中的一列或多列数据、使用集合函数显示查询结果、连接查询、子查询

基本查询语句

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

SELECT 
{ * | 字段列表
  [FROM table1,table2...
  [WHERE <exp>
  [GROUP BY 字段]
  [HAVING <EXP>]
  [ORDER BY 字段]
  [LIMIT START,END]
 ]
}

其中，各条子句的含义如下：

{* | <字段列表>}包含星号通配符和字段列表，表示查询的字段。其中，字段列表至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不加逗号。
FROM <表1>,<表2>…，表1和表2表示查询数据的来源，可以是单个或者多个。
WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。
GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY <字段>]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）、降序（DESC）。
[LIMIT [<offset>,] <row count>]，该子句告诉MySQL每次显示查询出来的数据条数

下面，我们通过示例来学习，先建一张表。

CREATE DATABASE DB_STUDENT;
USE DB_STUDENT;
CREATE TABLE STUDENT(
	ID INT PRIMARY KEY AUTO_INCREMENT,
  STUDENT_NAME VARCHAR(20),
  STUDENT_AGE TINYINT,
  STUDENT_GENDER CHAR(4)
 );
 INSERT INTO STUDENT VALUES (1,'李信',18,'男');
 INSERT INTO STUDENT VALUES (2,'周瑜',19,'男');
 INSERT INTO STUDENT VALUES (3,'安其拉',20,'女');
 INSERT INTO STUDENT VALUES (4,'妲己',18,'女');
 INSERT INTO STUDENT VALUES (5,'花木兰',18,'女');

首先，我们对表内所有的数据进行查询。

1 2	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT; SELECT * FROM STUDENT;

上面的两种写法都可以查询出表内的所有字段，在第一种写法中，查询多个字段，那么字段间使用”,”进行分割，第二种写法可以使用”*”代替所有的字段，但是一般不推荐第二种写法，因为如果表中字段过多的话，并且有些字段不是所必需的，使用第二种写法会大大降低查询效率，因此在查询中我们应该遵循一个原则就是：用什么，查什么。例如：现在需要查询所有学生的姓名和性别，sql语句就可按照以下方式书写。

1	SELECT STUDENT_NAME,STUDENT_GENDER FROM STUDENT;

单表查询

查询指定记录

数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在SELECT语句中，通过WHERE子句可以对数据进行过滤，语法格式为：

1	SELECT 字段1,字段2....字段n FROM TABLE WHERE EXP;

WHERE子句中，MySQL提供了一系列的条件判断符，查询结果如表

操作符	说明
=	相等
<>,!=	不相等
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN AND	位于两个值之间

查询18岁的学生的姓名和年龄。

1	SELECT STUDENT_NAME,STUDENT_AGE FROM STUDENT;

从查询结果可以看出只有张三和赵六两人年龄是18岁，不满足此条件的数据被过滤。
查询姓名为张三的所有信息。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHRER STUDENT_NAME = '张三';

查询年龄大于18岁的学生信息。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_AGE > 18;

查询年龄在18到20之间的同学。

1 2	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_AGE BETWEEN 18 AND 20;

从结果可以看出查询出的数据包含了年龄为18和20的同学，说明使用BTWEEN时是一个左闭右闭区间。

模糊查询

LIKE关键字

在前面的检索操作中讲述了如何查询多个字段的记录，如何进行比较查询或者是查询一个条件范围内的记录，如果要查找姓张的所有同学，该如何查找呢？简单的比较操作在这里已经行不通了，需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。执行这个任务的关键字是LIKE。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_NAME LIKE '张%';

从查询结果可以看出，使用like查询出了所有张姓同学，这里需要注意的是，%是通配符，匹配任意长度的字符。也就是说%告诉MySQL，返回所有以张开头的记录，不管张后面有多少个字符。
在搜索匹配时通配符‘%’可以放在不同位置，例如：查询姓名中包含“三”的学生信息。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_NAME LIKE '%三%';

我们知道%是指通配符，可以匹配任意个字符，但是如果要查询的字段当中包含%，并且需要筛选出包含了%的数据，就需要对%进行转义。MySQL中默认的转义字符是“\”,即在%前加“\”即可，当然也可是使用ESCAPE字句自定义转义字符。

SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_NAM LIKE '%\%%';
--或者自定义转义字符
SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_NAM LIKE '%1%%' ESCAPE '1';

上例中第一条SQL 语句使用了默认的转义字符，第二条则使用ESCAPE字句自定义1为转义字符

_通配符

“_”通配符和“%”的不同之处就是，%可以匹配多个字符，但是“_”能且只能匹配一个字符。
例如：查询表中所有张某同学的信息。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_NAM LIKE "张_";

同理，如果要查询张某某同学的信息，则使用两个“_”即可

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_NAM LIKE "张__";

查询空值

在数据表中，有可能某些字段的值为NULL，如果要筛选出这些数据，并不是说在where字句后添加字段名=NULL，测试发现，这种写法并不能查询出数据。如果要判断某个字段是否为NULL，要使用关键字IS.

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_GENDER IS NULL;

非空值查询

在上一小节我们使用IS关键字判断某个字段值是否为null，但是非空该如何判断呢？在这里，我们需要使用关键字NOT来判断字段是否为null

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_GENDER IS NOT NULL;

AND多条件查询

在大多数查询时可能会存在多个条件同时成立时的过滤，例如查看所有大于18岁的男生的信息，或者查看所有大于18岁女生的信息，再或者在电商系统中查询订单金额大于1000元并且已经支付的订单，这时就需要用到and关键字，也就是告诉数据库，我只查询and前后的条件都成立的数据，数据库会根据条件进行过滤。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_AGE > 18 AND STUDENT_GENDER = '男';

在上面的示例中，我们查询了两个条件同时成立的数据，使用and也可以查询多个条件同时成立的数据。例如：查询年龄大于18岁，性别为女，姓名为王五的学生信息。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_AGE = 18 AND STUDENT_GENDER = '女' AND STUDENT_NAME = '王五';

需要注意的是，使用and连接查询条件时，条件表达式的顺序并不是固定的，可以在and前后任意位置。

OR多条件查询

有时查询数据时可能存在多个过滤条件，但是这些条件符合至少1个即可，此时就需要用关键字OR。表示OR前后的条件满意任意一个即可。同样，OR也可以查询多个条件，和上述一致只需要满足任意一个条件即可。
例如：查询年龄为18岁或者19岁的同学

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT WHERE STUDENT_AGE >18 OR STUDENT_AGE>

查询去重

使用DISTINCT可以将重复的值过滤，只保留一个,例如：对学生进行性别去重

1	SELECT DISTINCT STUDENT_GENDER FROM STUDENT;

排序

单列排序

在查询过程中经常需要根据某个字段进行升序或者降序查询，此时就需要用到关键字ORDER BY，例如根据成绩进行升序处理。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER,SCORE FROM STUDENT ORDER BY SCORE ;

从查询结果来看，数据库默认是按照升序进行数据显示。

升序和降序

上小节我们对成绩进行了升序排序，也可以使用关键字ASC，ASC写在GROUP BY后的字段名称以后，由于数据库默认是按照升序显示数据，因此ASC可以省略。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER,SCORE FROM STUDENT ORDER BY SCORE ASC;

从结果可以看出，添加ASC和默认排序的结果是一致的。

如果我们要进行降序显示，我们只需要将关键字ASC更换为DESC即可，例如：对所有同学的分数进行降序排序。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER,SCORE FROM STUDENT ORDER BY SCORE DESC;

分组查询

GROUP BY分组

分组在数据库查询中也是出现频率比较高的需求，如果要对数据分组就要用到关键字GROUP BY，例如：对学生性别进行分组。

1	SELECT STUDENT_GENDER FROM STUDENT GROUP BY STUDENT_GENDER;

HAVING 过滤分组

如果要对分组后的数据进行筛选，那么我们就要使用HAVING关键字，HAVING关键字是指对分组后的结果进行筛选，在前面的内容中，我们使用了WHERE对数据进行筛选，需要注意的是WHERE筛选的数据是分组前进行筛选，而HAVING则是分组后进行筛选。例如：查询分组查询出男生的平均成绩和女生的平均成绩，并筛选出大于60分的分组。

1	SELECT STUDENT_GENDER,AVG(SCORE) FROM STUDENT GROUP BY STUDENT_GENDER HAVING (AVG(SCORE)>80);

上面的示例中，出现了avg()函数，这个函数是一个聚合函数，用于求平均值。

分组后排序

例如：对上例中的结果进行降序处理。

1	SELECT STUDENT_GENDER,AVG(SCORE) FROM STUDENT GROUP BY STUDENT_GENDER HAVING (AVG(SCORE)>80) ORDER BY AVG(SCORE) DESC;

聚合函数

函数	说明
COUNT()	计算总条数
AVG()	求字段的平均值
SUM()	求字段总和
MAX()	求最大值
MIN()	求最小值

例如：查询数据表中总共有多少条数据。

1	SELECT COUNT(*) FROM STUDENT;

在上面的结果中，我们可以看出查询出数据库总共有10条记录，需要注意的是当COUNT()函数的参数为*时，会统计该表中总共的记录数，但是，如果参数传的是某个字段的话，则统计该字段有多少条数据，如果该字段中存在NULL值，则会忽略该行。我们在表中新增一条数据，将该条数据的SCORE字段置为NULL，然后使用COUNT()函数进行统计。

1	SELECT COUNT(SCORE) FROM STUDENT;

从结果中可以看到，统计时忽略了NULL值，并没有将该条记录统计在内。

AVG()函数则是用来统计平均值，例如上例中对学生根据性别进行分组，分组后统计男女同学的平均成绩，现在我们可以使用该函数统计所有同学的平均值。注意：如果传入的字段中存在NULL值，则忽略该行

1	SELECT AVG(SCORE) FROM STUDENT;

SUM()函数则是对某个字段计算总和。例如求出所有同学的总成绩。注意：如果传入的字段中存在NULL值，则忽略该行

1	SELECT SUM(SCORE) FROM STUDENT;

MAX()函数和MIN()函数则是获取某个字段的最大值和最小值，例如我们获取学生的最高成绩和最低成绩。

1	SELECT MAX(SCORE),MIN(SCORE) FROM STUDENT;

查询男生的最高成绩和最低成绩，以及女生的最高成绩和最低成绩。

1	SELECT MAX(SCORE),MIN(SCORE) FROM STUDENT GROUP BY STUDENT_GENDER;

分页查询

我们在查询过程中发现，有些数据量比较大的表，通常不是一次性显示所有数据，而是分页显示，如果要对查询结果进行分页，就要使用LIMIT关键字
例如：显示所有学生的5条数据。

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT LIMIT 5;

此时，我们在LIMIT后只写了一个参数，代表查询出数据表中前5条数据。LIMIT也可以传入2个参数，第一个参数代表从第几条数据开始，第二个参数代表显示几条数据。需要注意的是，MySQL中第一条数据的编号是0.
例如：查询数据库中前三条数据

1	SELECT ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER FROM STUDENT LIMIT 0,3;

连接查询

在开发过程中绝大多数情况都需要操作2张或者2张以上的表，因此在查询中就需要多表联查或者说多张表进行连接，在MySQL中连接查询分为内连接和外连接，其中外连接又分为左外连接和右外连接。
首先我先创建一张学生信息表。

CREATE TABLE STUDENT_INFO (
  ID INT PRIMARY KEY AUTO_INCREMENT,
  ADDRESS VARCHAR(50),
  TELPHONE VARCHAR(14),
  STUDENT_ID INT
 );
 INSERT INTO STUDENT_INFO (ADDRESS,TELPHONE,STUDENT_ID) VALUES ('河南','1999999999',1),('河北','1999999999',2);

内连接

当我们要连接查询的表存在1对1的关系时，我们就可以使用内连接，使用INNER JOIN将两张表进行连接，通常INNER可以省略，需要注意的是，如果要添加条件是用关键字ON，而不是使用WHERE.
例如：查询所有学生的基本信息及详细信息。

1	SELECT * FROM STUDENT JOIN STUDENT_INFO ON STUDENT.ID = STUDENT_INFO.ID;

外连接

在大多数情况下我们遇到的查询时一个对多或者多对多的情况，这时简单的内连接已经无法满足查询数据的需要了，这时就要用到外连接，外连接又分为左外连接和右外连接，关键字分别是LEFT JOIN 和RINGHT JOIN。其中，JOIN左边的表叫左表，JOIN右边的表叫右表。

LEFT JOIN左连接

在左连接中，左表中的数据会全部显示，右表中的数据符合过滤条件的显示。不符合过滤条件的数据则以NULL填充

create table Student(SId varchar(10),Sname varchar(10),Sage date,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

create table Course(CId varchar(10),Cname varchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

create table SC(SId varchar(10),CId varchar(10),score DEC(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
insert into SC values('09' , '01' , null);

例如：查询所有同学学习的课程成绩，因为学生和学习的课程是一对多的关系，所以我们在这里选用左外连接

1	SELECT * FROM STUDENT LEFT JOIN SC ON STUDENT.SID = SC.SID;

从结果中可以看出，学生表中的所有信息都显示了，但是由于张三，李四，李四，赵六，孙七没有学习课程，所以在SC表中没有相关记录，因为使用了左外连接，所以，右表中的数据不满足筛选条件的全部以NULL值填充。

RIGHT JOIN 右连接

右连接和左连接的规则相反，是指右表的数据全部显示，左表中的数据满足筛选条件的显示，不满足的则以NULL值填充。
我们在SC表中删除任意一条数据的SID来进行验证。

1	SELECT * FROM STUDENT RIGHT JOIN SC ON STUDENT.SID = SC.SID;

从结果中可以看出，删除了一个SID后，在左表中无法找到对应的学生信息，因此该条数据的左表字段全部以NULL填充。

子查询

 CREATE TABLE STUDENT2(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    STUDENT_NAME VARCHAR(20),
    STUDENT_AGE TINYINT,
    STUDENT_GENDER CHAR(4)
    );
    
INSERT INTO STUDENT2 VALUES (1,'李信',18,'男');
INSERT INTO STUDENT2 VALUES (2,'周瑜',19,'男');
INSERT INTO STUDENT2 VALUES (3,'安其拉',22,'女');
INSERT INTO STUDENT2 VALUES (4,'妲己',18,'女');
INSERT INTO STUDENT2 VALUES (5,'花木兰',18,'女');

ANY关键字

满足ANY子查询中任意一个即可,也可以理解为满足ANY子查询中最小的一个即可。

1	SELECT * FROM STUDENT WHERE STUDENT_AGE>ANY(SELECT STUDENT_AGE FROM STUDENT2);

ALL关键字

满足ALL子查询中所有的条件。或者满足ALL子查询中最大的即可

1	SELECT * FROM STUDENT2 WHERE STUDENT_AGE>ALL(SELECT STUDENT_AGE FROM STUDENT);

EXISTS关键字

1	SELECT * FROM STUDENT WHERE EXISTS(SELECT ID FROM STUDENT2 WHERE STUDENT2.STUDENT_AGE=STUDENT.STUDENT_AGE);

IN关键字

IN关键字可以理解为OR，即满足括号中的A条件，或者B条件，或者C条件

1	SELECT * FROM STUDENT WHERE STUDENT_AGE IN(SELECT STUDENT_AGE FROM STUDENT2);

比较运算符的子查询

1	SELECT * FROM STUDENT2 WHERE STUDENT_AGE>(SELECT STUDENT_AGE FROM STUDENT WHERE STUDENT_AGE=20);

SQL语句执行顺序

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT