MYSQL 之查询篇

2 数据库操作

数据库在创建以后最常见的操作便是 查询

2.1 查询

为了便于学习和理解，我们预先准备了两个表分别是 stduents 表和 classes 表两个表的内容和结构如下所示
students 表的内容:

/*创建表的sql语句*/
CREATE TABLE students (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `score` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*插入测试数据*/
INSERT INTO `students` VALUES ('1', '1', '小明', 'M', '90');
INSERT INTO `students` VALUES ('2', '1', '小红', 'F', '95');
INSERT INTO `students` VALUES ('3', '1', '小军', 'M', '88');
INSERT INTO `students` VALUES ('4', '1', '小米', 'F', '73');
INSERT INTO `students` VALUES ('5', '2', '小白', 'F', '81');
INSERT INTO `students` VALUES ('6', '2', '小兵', 'M', '55');
INSERT INTO `students` VALUES ('7', '2', '小林', 'M', '85');
INSERT INTO `students` VALUES ('8', '3', '小新', 'F', '91');
INSERT INTO `students` VALUES ('9', '3', '小王', 'M', '89');
INSERT INTO `students` VALUES ('10', '3', '小丽', 'F', '85');

classes 表的内容和结构:

/*创建表的sql语句*/
CREATE TABLE `classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*插入测试数据*/
INSERT INTO `classes` VALUES ('1', '一班');
INSERT INTO `classes` VALUES ('2', '二班');
INSERT INTO `classes` VALUES ('3', '三班');
INSERT INTO `classes` VALUES ('4', '四班');

2.1.1 基本查询

查询数据库中某个表的所有内容：

SELECT * FORM <table_name>

例如查询 students 表中的所有内容

注意：
对于 select 语句来说，并不一定非要有 from 子句，例如如下语句

select 1+2;  /* 结果为3*/

上述查询会直接计算出表达式的结果。虽然 SELECT 可以用作计算，但它并不是 SQL 的强项。但是，不带 FROM 子句的 SELECT 语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条 SELECT 1;来测试数据库连接。

2.1.2 条件查询

定义：
大部分情况下，我们查询一张表的时候并不想获取一张表中的所有内容，而是想从所有记录筛选出我们所需要，此时便需要我们在查询过程中对查询条件进行限制，这边是 条件查询
条件查询的语法：

SELECT * FROM <表名> WHERE <条件表达式>

例如查询 students 表中分数 大于等于80 (score>=80) 的学生信息
查询分数大于等于 80 的学生信息 sql 命令

 SELECT * FROM students WHERE score >= 80;

查询结果

条件表达式中常用的查询条件有如下这些

2.1.3 多重条件查询

在实际生产过程中我们查询一个表可能查询条件并不仅仅只有一个，此时我们便需要 AND、OR 和 NOT 来进行连接和限定。
<条件一> AND <条件二>:查询结果既需要满足 < 条件一 > 同时也需要满足 < 条件二 >。
例如查询分数大于等于 60 且小于 80 的学生信息：

SELECT * FROM students WHERE score >=60 and score < 80; 

查询结果：

<条件一> OR <条件二>:查询结果需要满足条件一或者条件二。
例如查询分数小于 60 或者大于等于 80 的学生信息：

select * from students where score <60 or score >= 80;

查询结果:

select * from students where score = 90;
select * from students where NOT score = 90;

查询结果：

2.2 投影查询

有时我们在查询一个表时，可能并不需要所有表的信息，而只是需要一个表的部分列，此时我们便可以通过 SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为 投影查询。
eg: 查询所有学生的姓名和班级信息

select name,class_id from students;

查询结果：

同时在查询过程中我们可以对查询后的属性名称设计别名，并且也可以指定结果列的顺序（可以和原表的顺序不同）。
使用 SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是 SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。
eg: 将插叙结果中属性名 name 改成 student_name

select name student_name,class_id from students;

查询结果：

2.3 查询结果排序

细心的读者可能已经发现：我们在之前所做的查询最终的查询结果都是按照 id（或者 class_id）的升序排列的，那么我们如何来改变查询结果的排序顺哪？
其实我们可以通过 ORDER BY 语句来进行查询结果出顺序的控制。
eg: 按照 score 从小到大对结果进行排序

select * from students ORDER BY score;

查询结果：

默认的排序规则是 ASC 升序即从小到大，当然如果我们想要查询结果是降序排列，我们可以通过加上 DESC 来进行进行降序结果输出。
eg：将上边的查询结果按照 score 降序来进行输出

2.4 分页查询

有时在查询的过程中我们查询的到的结果集比较大，而程序在处理和显示这些数据时空间有限不能够一下子完全显示出来，此时便需要将查询到的结果集分成不同的页来进行显示，这边是 分页查询。分页查询 实际上也就是将大的数据集（比如几万条）进行拆分，分成若干页，比如 1-100 第一页、101-200 第二页、201-300 第三页，以次类推。具体使用分页查询时我们需要通过 LIMIT <M> OFFSET <N> 子句对查询结果集的大小以及页数进行控制。我很还以 students 为例，首先查询到它所有的结果集
查询 studnets 表所有结果集的 sql 语句

select * from students;

查询结果：

现在我们将查询到数据集进行分页，
查询第一页的数据（每页 3 条数据）：

select * from students limit 3 offset 0;

查询结果为：

上述查询 limit 3 offset 0 表示，结果集从 0 号记录开始查询。注意 SQL 记录集的索引从 0 开始。如果需要查询第 2 页，我们需要跳过前边的三条记录，索引此时应该从 3 开始，即我们需要将 offest 设置为 3。
查询第 2 页的结果集：

/*每页显示三条数据，获取其中的第2页*/
select * from students limit 3 offset 3;

查询结果：

同样的查询第 3 页的结果集时应该讲 offset 设置为 6；
查询第 3 页的结果集：

select * from students limit 3 offset 6;

查询结果：

查询第 4 页的结果集：

select * from students limit 3 offset 9;

查询结果为：

由于第 4 页只有一条记录所以查询结果只显示一条记录。limit 3 表示每页最多“显示三条记录”。
由此可见我们在进行分页查询的时候最关键的问题是设计每页需要显示的结果集大小 pageSize(这里设置的是 3)，然后根据当前页的索引 pageIndex（需要查第几页的结果），确定 limit 以及 offset 的值：

• limit 一般设置为 pageSize
• offset 设置为 pageSize*(pageIndex-1)

注意：

1. offset 值的设置是可选的，如果只写 limit 3，DBMS 不会报错，而是默认认为是 limit 3 offset 0。
2. offset 设置的值如果大于最大数量并不会报错，而只是得到一个空的结果集
3. 在 MYSQL 中，limit 3 offset 6 还可以写为 limit 3,6
4. 在使用 limit <m> offset <n> 时随着 n 的值越来越大，查询的效率也会越来越低

2.5 聚集查询

在日常开发的某些应用场景中，我们并不需要获得具体数据集，而只是想要获得满足条件的数据集的条数（例如：查询班级表中男生的人数），此时便需要 嵌套查询。对于统计总数、平均数这类查询来说，SQL 已经为我们提供了专门的 聚合函数，使用聚合函数进行查询，我们便称之为 聚合查询，仍然以查询 students 表中男生的人数为例,我们可以通过 SQL 内置的 count() 函数来进行查询。
查询 students 表中男生的人数：

select count(*) from students where gender='M';

查询结果：

当然除了 count() 函数之外 SQL 还提供了如下的聚合函数

注意：

1. MAX() 个 MIN() 函数并不仅限于数值类型。如果字符类型，MAX() 和 MIN() 会返回排序在最后边和最前边的字符
2. 如果 聚合查询 的结果没有匹配到任何行，count() 会返回 0，而 s um()、avg()、max() 和 min() 会返回 null。

2.6 多表查询

select 查询不仅可以从一张表中查询出结果，还可以同时在多张表中查询出结果，其语法为：select * form <table 1>,<table 2>。
例如，从同时从 students 表和 classes 表中查询出结果：
查询所用 sql 语句：

select * from students,classes;

查询结果为：

当然这种查询方式得到的结果只是 students 表和 classes 表的 笛卡尔积，它是 students 表和 classes 表的“乘积”，即 students 表的每一行与 classes 表的每一行都两两拼在一起返回。结果集的列数是 students 表和 classes 表的列数之和，行数是 students 表和 classes 表的行数之积。
当然如果简单的使用上边的查询方法不加限定条件，那么查询的结果几乎没有任何意义。因此我们可以通过 where 子句对查询的结果进行限定。
例如查询男生且位于一班的信息

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

查询结果为：

2.7 连接查询

在上一小结我们提到的连接查询，所得到的结果只是两个表 stuents 表 和classes 表的笛卡尔积，两个表直接没有任何的逻辑联系，而实际上我们进行多表查询时更多情况下两张表之间是有关系联系的，比如将 students 表中 class_id 和 classes 表中的 id 建立相等的联系 stduents.class_id=classes.id，这种连接两个表进行 JOIN 运算的的查询方式，我们称之为 连接查询。
连接查询 是我们在实际开发过程最常用的查询方式，连接查询 在查询过程中又分为 自然连接查询、内连接查询、外连接查询 等。
自然连接查询：在查询过程中，我们将目标列中重复的属性列去掉这一过程我们称之为 自然连接查询。
例如我们查询所有学生信息（包括班级信息），其中信息如姓名、性别、id 等信息都在 students 表中，而班级信息却在 classes 表中，此时如果直接通过上一节的夺表查询的查询方法进行查询。这样查询的结果会有许多重复的结果值。此时我们便可以通过 自然连接查询的方式（限定 students 表和 classes 表的属性关系）来进行查询。
自然连接查询方法查询所有学生信息：

select s.id,s.name,s.class_id,s.gender,s.score,c.name from students s,classes c where s.class_id=c.id

查询结果为：

当然我们在查询班级所有信息时我们也可以通过 内连接查询 来获取相同的查询结果。
内连接查询方式查询所有学生信息：

select s.id,s.name,s.class_id,s.gender,s.score,c.name from students s inner join classes c on s.class_id=c.id;

查询结果为：

此时可能有人要问了，既然自然连接查询和内连接查询可以相同结果，那么我们在实际开发过程中应该选择哪种查询方法？
针对这个问题，首先我们要明白虽然通过 自然连接查询 以及 内连接查询 可以得到相同的查询结果，但是它们在底层的实现原理是不同的。一般来说能获得相同的查询结果条件下，我们也一般都是通过 内连接来查询 的，因为内连接使用 ON，而 自然连接 是通过使用 WHERE子句 来进行限定，而 WHERE 的效率没有 ON 高（ON 指匹配到第一条成功的就结束，其他不匹配；若没有，不进行匹配,而 WHERE 会一直匹配，进行判断。）

注意： inner join 查询的写法为：

1. 先确定主表，仍然使用 FROM < 表 1> 的语法；
2. 再确定需要连接的表，使用 INNER JOIN < 表 2> 的语法；
3. 然后确定连接条件，使用 ON < 条件...>，这里的条件是 s.class_id = c.id，表示 students 表的 class_id 列与 classes 表的 id 列相同的行需要连接；
4. 可选：加上 WHERE 子句、ORDER BY 等子句。

那可能又有人问了“既然有 外连接，那么是否有 内连接 那？”答案是肯定的，我们暂且不说什么是外连接，我们先通过一个例子来看下 内连接 和 外连接 之间的区别，我们还是查询所有学生的信息，但是不同之处在于我们将 内连接 换成 外连接。
通过 外连接 查询所有学生信息:

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

查询结果为：

我们容易发现，此时的查询结果比 内连接 方式多了一行，多出来的一行是“四班”，但是，学生相关的列如 name、gender、score 都为 NULL。这也容易理解，因为根据 ON 条件 s.class_id = c.id，classes 表的 id=4 的行正是 “四班”，但是，students 表中并不存在 class_id=4 的行。
当然有 RIGHT OUTER JOIN，就有 LEFT OUTER JOIN，以及 FULL OUTER JOIN。它们的区别是：INNER JOIN 只返回同时存在于两张表的行数据，由于 students 表的 class_id 包含 1，2，3，classes 表的 id 包含 1，2，3，4，所以，INNER JOIN 根据条件 s.class_id = c.id 返回的结果集仅包含 1，2，3。RIGHT OUTER JOIN 返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以 NULL 填充剩下的字段。LEFT OUTER JOIN 则返回左表都存在的行。如果我们给 students 表增加一行，并添加 class_id=5，由于 classes 1 表并不存在 id=5 的行，所以，LEFT OUTER JOIN 的结果会增加一行，对应的 class_name 是 NULL:
先插入一个不含 class_id 的学生信息：

insert into stduents (id, name,gender,score) values(default,'李平','M',95);
select * from students;

通过外连接查询所有学生信息：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;

查询结果为:

最后，我们使用 FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为 NULL：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

查询结果：

注意： MYSQL 是不支持 FULL OUTER JOIN 查询的。
为了便于大家理解 JON 查询，下边我们用图来表示各种查询的关系：
假设查询语句是：

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

我们把 tableA 看作左表，把 tableB 看成右表，那么 INNER JOIN 是选出两张表都存在的记录：

LEFT OUTER JOIN 是选出左表存在的记录：

RIGHT OUTER JOIN 是选出右表存在的记录：

FULL OUTER JOIN 则是选出左右表都存在的记录：

2.7 嵌套查询

基于上边几节的描述常用的查询方法都已基本囊括，但是某些情况下我们需要将一个 查询块（一个 SELECT-FROM-WHERE 子句称为 查询块）嵌套在另一个 查询块 的 WHERE 子句 或 HAVING 短语 的条件中的查询称为 嵌套查询。例如：
通过嵌套查询 二班 的学生信息：

select * from students where class_id in
(select id from classes where name='二班' );

查询结果:

嵌套查询 可以使用户使用多个简单查询构成复杂的查询，从而增强 SQL 的查询能力。以层层嵌套的方式来构造程序正式 SQL 中“结构化”的含义所在。在使用 嵌套查询 的过程中根据 子查询 的方式不同，我们将查询分为下边三大类：

2.7.1 使用 IN 谓词的子查询

在 嵌套查询 中，子查询 的结果往往是一个集合，所以谓词 IN 是 嵌套查询 中经常使用的谓词。比如我们要查询跟 小丽 同学考同样分数学生的信息我们可以通过如下步骤来构造嵌套查询。

1. 首先确定小丽同学所考的分数：
   查询小丽同学所考的分数：

select score from students where name='小丽';

查询结果为：

2. 查询 students 表中所有 score=85 的学生信息:
   查询 score=85 的学生信息：

select * from students where score=85;

查询结果:

3. 将第一步构造的查询语句嵌套到第二句中构造 嵌套查询。
查询语句为：

select * from students where score in
(select score from students where name='小丽');

查询结果为：

在这个例子程序中细心的同学会发现，子查询 的查询条件是和 父查询 是相互独立的，对于此类查询我们称之为 不相关子查询。对于此种查询我们实际上是可以通过连接查询来获取同样的查询结果的。例如上边“查询跟 小丽 同学考同样分数学生的信息”，我们可以通过如下的 SQL 语句进行替代：

select s1.* from students s1,students s2 where s1.score=s2.score and s2.name='小丽';

查询结果：

由此可见实现同一个结果的查询方式有很多种，但是不同方法查询的效率确实不同的。这就是数据编程人员需要掌握的数据性能调优技术，后续时间充裕，我会对常用的 mysql 查询的调优技术进行总结，此处便不进行详尽阐述了。

2.7.2 带有比较符号的 子查询

有时某些 嵌套子查询 中，我们并不需要通过 IN 详尽匹配子查询中的结果，而只是需要对子查询返回的单个结果进行比较，此时我们便需要使用 比较运算符 对查询结果进行限定。比如我们查询分数高于平均分的学生信息：

select id,class_id,name,gender,score from students s where score >= 
(select avg(score) from students);

查询结果为：

**注意：**在使用比较符号时子查询的应当只有一个否则，查询结果是不正确的。

2.7.3 带有 ANY（SOME） 或者 ALL 位于的子查询

子查询的结果有一个时可以使用比较运算符，但是当返回值有多个时我们需要通过 ANY（SOME） 或者 ALL 等谓语以及和比较符号一起连用来对结果进行限定，常见的用法以及含义如下表所示

比如查询所有学生中分数最低的学生的姓名和成绩

select name,score from students where score<=ALL(select score from students);

查询结果为：

2.7.4 带 EXISTS 的子查询

EXISTS 代表存在量词。带有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真“true”和逻辑假“flase”。
例如我们可以通过 嵌套查询，查询 二班 所有学生的信息。

select * from students where exists 
(select * from classes where students.class_id=id and name='二班');

查询结果：

**注意：**通过 EXISTS 引出的子查询，其目标表达式通常都是使用 *，因为带有 EXISTS 的子查询只返回真值或者假值，给出列名没有任何意义。

2.8 基于派生表的查询

其实子查询有时候并不一定非要出现在 WHERE子句 中，还可以出现在 FROM子句 中，这是子查询产生的 临时派生表 成为朱查询的查询对象，这种查询方式我们称为 基于派生表的查询。
例如查询所有学生的姓名信息我们通过派生表查询方式实现：

select s1.name from 
(select * from students) as s1

查询结果为：

当然我们在此处举例子可能意义不大，仅仅只是为了说明 EXITS 子句的用法。具体在开发过程中 EXISTS 的具体使用，需要读者自己去发掘和探索。