MYSQL之查询篇
2. 数据库操作
数据库在创建以后最常见的操作便是查询
2.1 查询
为了便于学习和理解,我们预先准备了两个表分别是stduents
表和classes
表两个表的内容和结构如下所示students
表的内容:
id | class_id | name | gender | score |
---|---|---|---|---|
1 | 1 | 小明 | m | 90 |
2 | 1 | 小红 | f | 95 |
3 | 1 | 小军 | m | 88 |
4 | 1 | 小米 | f | 73 |
5 | 2 | 小白 | f | 81 |
6 | 2 | 小兵 | m | 55 |
7 | 2 | 小林 | m | 85 |
8 | 3 | 小新 | f | 91 |
9 | 3 | 小王 | m | 89 |
10 | 3 | 小丽 | f | 85 |
创建students
表的sql命令:
/*创建表的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
表的内容和结构:
id | name |
---|---|
1 | 一班 |
2 | 二班 |
3 | 三班 |
4 | 四班 |
创建classes
表的sql命令:
/*创建表的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; /*插入测试数据*/
2.1.1基本查询
查询数据库中某个表的所有内容:
select * form <table_name>
例如查询students表中的所有内容
注意:
对于select语句来说,并不一定非要有from子句,例如如下语句
select 1+2;
上述查询会直接计算出表达式的结果。虽然select可以用作计算,但它并不是sql的强项。但是,不带from子句的select语句有一个有用的用途,就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条select 1;来测试数据库连接。
2.1.2 条件查询
定义:
大部分情况下,我们查询一张表的时候并不想获取一张表中的所有内容,而是想从所有记录筛选出我们所需要,此时便需要我们在查询过程中对查询条件进行限制,这边是条件查询
条件查询的语法:
select * from <表名> where <条件表达式>
例如查询students表中分数大于等于80 (score>=80)
的学生信息
查询分数大于等于80的学生信息sql命令
select * from students where score >= 80;
查询结果
条件表达式中常用的查询条件有如下这些
查询条件 | 谓词 |
---|---|
比较 | =,>,<,>=,<=,!=,<>,!>,!<;not+前边的比较符 |
确定范围 | between and,not between and |
确定集合 | in,not in |
字符匹配 | like,not like |
空值 | is null,is not null |
多重条件(逻辑运算) | and,or,not |
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)
注意:
-
offset
值的设置是可选的,如果只写limit 3
,dbms不会报错,而是默认认为是limit 3 offset 0
。 -
offset
设置的值如果大于最大数量并不会报错,而只是得到一个空的结果集 - 在
mysql
中,limit 3 offset 6
还可以写为limit 3,6
- 在使用
limit <m> offset <n>
时随着n的值越来越大,查询的效率也会越来越低
2.5 聚集查询
在日常开发的某些应用场景中,我们并不需要获得具体数据集,而只是想要获得满足条件的数据集的条数(例如:查询班级表中男生的人数),此时便需要嵌套查询
。对于统计总数、平均数这类查询来说,sql
已经为我们提供了专门的聚合函数
,使用聚合函数进行查询,我们便称之为聚合查询
,仍然以查询students
表中男生的人数为例,我们可以通过sql
内置的count()
函数来进行查询。
查询students
表中男生的人数:
select count(*) from students where gender='m';
查询结果:
当然除了count()
函数之外sql
还提供了如下的聚合函数
函数 | 说明 |
---|---|
sum | 计算某一列的合计值,该列必须为数值类型 |
avg | 计算某一列的平均值,该列必须为数值类型 |
max | 计算某一列的最大值 |
min | 计算某一列的最小值 |
注意:
-
max()
个min()
函数并不仅限于数值类型。如果字符类型,max()
和min()
会返回排序在最后边和最前边的字符 - 如果
聚合查询
的结果没有匹配到任何行,count()
会返回0,而sum()
、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查询的写法为:
- 先确定主表,仍然使用from 的语法;表1>
- 再确定需要连接的表,使用inner join 的语法;表2>
- 然后确定连接条件,使用on <条件...>,这里的条件是s.class_id = c.id,表示students表的class_id列与classes表的id列相同的行需要连接;
- 可选:加上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;
查询结果:
id | name | class_id | class_name | gender | score |
---|---|---|---|---|---|
1 | 小明 | 1 | 一班 | m | 90 |
2 | 小红 | 1 | 一班 | f | 95 |
3 | 小军 | 1 | 一班 | m | 88 |
4 | 小米 | 1 | 一班 | f | 73 |
5 | 小白 | 2 | 二班 | f | 81 |
6 | 小兵 | 2 | 二班 | m | 55 |
7 | 小林 | 2 | 二班 | m | 85 |
8 | 小新 | 3 | 三班 | f | 91 |
9 | 小王 | 3 | 三班 | m | 89 |
10 | 小丽 | 3 | 三班 | f | 88 |
11 | 李平 | 5 | null | m | 95 |
注意: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是嵌套查询
中经常使用的谓词。比如我们要查询跟小丽
同学考同样分数学生的信息我们可以通过如下步骤来构造嵌套查询。
- 首先确定小丽同学所考的分数:
查询小丽同学所考的分数:
select score from students where name='小丽';
查询结果为:
- 查询
students
表中所有score=85
的学生信息:
查询score=85
的学生信息:
select * from students where score=85;
查询结果:
- 将第一步构造的查询语句嵌套到第二句中构造
嵌套查询
。查询语句为:
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
等谓语以及和比较符号一起连用来对结果进行限定,常见的用法以及含义如下表所示
限定符 | 作用 |
---|---|
>any | 大于子查询中的某个结果值 |
>all | 大于子查询的所有结果值 |
<any | 小于子查询结果中的某个值 |
<all | 小于子查询结果中的所有值 |
>=any | 大于等于子查询中的某个结果只 |
>=all | 大于等于子查询中的所有结果只 |
<=any | 小于等于子查询中的某个结果值 |
<=all | 小于等于子查询的所有结果值 |
=any | 等于子查询中的某个结果值 |
=all | 等于子查询中的所有结果值(通常没有任何意义) |
!=any | 不等于子查询中的某个结果值 |
!=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的具体使用,需要读者自己去发掘和探索。