MySQL必知必会(2):数据检索(SELECT)

千寻CHASE

千寻CHASE

发表于 2017-01-10 16:39:06
内容来源: 网络

本文介绍Mysql开发中使用最频繁的功能,SELECT数据检索

简介

检索数据是从数据库中使用特定语句获取页面需要的数据。每天你上网浏览的网页,绝大部分数据都是从数据库中检索出来,所以它也是mysql最常用的功能之一。

从Mysql检索数据至少需要提供两个信息:

  1. 你需要查找什么

  2. 你需要从哪个表中查询

检索数据

单列检索

我们有了上面两个两个信息,我们就可以组合成为一个最基本的查询语句:

SELECT name FROM my_user;

上面语句我们从my_user表查询了用户的名字。

由此我们可以看出,最基本的查询语句是由SELECT和FROM关键字组成。SELECT后面跟着你需要查询的字段,FROM后面跟着你需要从哪个表查询出这些数据。

上图的数据库表可以从本文最后附录获取。由于还没有讲解到数据排序,所以你检索出来的数据顺序和我的不一样也不要惊讶。

几个点说明:

  1. 如果在命令行执行SQL语句,那么需要使用分号结尾。

  2. 其他的情况下,Mysql不要求一定加上分号结尾,但是我们建议加上分号结尾。

  3. Mysql不区分大小写,实际开发中习惯将关键字大写,其他小写。比如SELECT、FROM

  4. 部分版本的Mysql中(比如4.1及之前的版本)数据库名、表名、字段名区分大小写。建议养成大小写一致的习惯。

  5. Mysql默认会将多个空格合并成一个空格,所以为了便于阅读,一般会将较长SQL语句换行书写

多列检索

多列检索类似于上面一个例子,只不过查询的字段变成了多个。多个字段之间使用英文逗号分隔:

SELECT name,age,code FROM my_user;

这一次我们检索了3个字段。记住,只能是字段之间使用逗号分隔,最后一个字段(这里是code)之后不能有逗号,否则Mysql会报错。

检索全部字段

除了一个个的写出需要检索的字段外,Mysql还提供了一个星号*代表检索所有字段:

SELECT * FROM my_user;

可以看到全部的字段信息都被检索了出来,少写了不少单词。这种方法十分简单,而且可以检索出数据库中你可能不知道存在的字段,在某些情况下十分有用。

但是,开发中建议不要使用这种方式检索。原因就是会导致数据库检索性能下降。由于,检索出来的所有数据都会放在内存。所以,当你只需要比如文章标题时候,你也检索出文章内容。内存就会被文章内容严重消耗。

我们建议,需要什么字段就检索什么字段,少用星号检索全部字段。

去除重复记录

之前使用的例子中共有5个用户,其中25岁的用户有两个,小黄和小颖。假设我们现在只想看看用户的年龄层次,每个年龄只能出现一次。

按照之前方法查询

SELECT age FROM my_user;

我们会得到5条记录,2次出现25.但是我们并不需要重复的25.Mysql提供了一个去除重复记录的关键字DISTINCT,只要将它加在检索的字段前面,即可去除重复记录。

SELECT DISTINCT age FROM my_user;

上图结果中25只出现了一次。但是这种方法只能用在一个字段时候,如果我们添加一个查询字段name那么,依然会显示所有的结果。

SELECT DISTINCT age,`name` FROM my_user;

LIMIT限制记录数量

数据库是可以存放千万条数据的地方,但是我们每次检索不可能将这么多数据。比如我们只需要前20条,或者需要第10条到第30条数据。这时,就需要LIMIT关键字作检索记录条数限制。

SELECT age,`name` FROM my_user LIMIT 2;

上面例子中,我们在之前SQL语句后面添加了一个LIMIT 2,检索出来的记录数量由5条变为了2条。

我们更改一下LIMIT后面的数值

SELECT age,`name` FROM my_user LIMIT 10;

这一次我们改为了10,而我们只有5条记录,所以只显示了5条。由此可见,LIMIT后面的数字代表的是不超过数值的记录数。

其实上面的写法是一种简写,LIMIT后面正常是2个数字。

SELECT age,`name` FROM my_user LIMIT 0,2;

我们将上面的例子填写完整,发现结果完全一样。

说明:

  1. LIMIT后面的第一个数字为从第几条开始检索。第一条为0,搞开发的都知道。

  2. LIMIT后面第二个数字为本次检索记录的条数。

  3. 当从首行记录开始检索时候,可以省略第一个数字,只写一个记录数。

代码写多了,你就会发现LIMIT n,m这种情况特别容易混淆两个数字的含义。所以,在Mysql 5版本之后,支持另外一种更为清晰的写法:

LIMIT 4 OFFSET 3 // 表示从行3处取4行数据,相当于LIMIT 3,4

数据排序

数据排序在数据检索中非常常用。打个比方,你写博客,在数据库中插入的顺序是最先写的在最后一行。但是我们想得到的列表是,最先写的排在第一行,这样我们就需要将列表倒过来,也就是倒序。当然还有正序。

单字段排序

排序,使用关键字ORDER BY。但是,这里必须要有一个参照的关键字,比如你想文章按照写的顺序倒序。你可以按照文章创建时间倒序,时间大的在前。这里的时间就被成为排序依照字段。

我们没有文章表,还是拿上面的例子:

SELECT age,`name` FROM my_user ORDER BY age DESC

这条语句的意思是将用户按照年龄从大到小的顺序检索出来。

我们看下结果,较上面结果发生了顺序变化,年龄从大到小。其中的age就是排序的依照字段,最后一个关键字DESC表示他前面的age按照倒序排列。

正序排列就是ORDER BY age ASC,其中默认就是ASC,所以可以不写。

多字段排序

上面例子检索出来结果我们看到,在前面有两个25岁的用户。他们年龄都是25,但是我现在想让小颖在第一个。这里就涉及到了多字段排序。

多字段排序也就是多次排序,其中第一个字段优先级最高,如果第一个字段出现重复,则使用第二个字段排序。

SELECT user_id,age,`name` FROM my_user ORDER BY age DESC,user_id DESC

这一次我们添加了一个排序字段user_id,并且使用倒序。为了显示清楚,我们把user_id字段也检索出来。这次发现小颖被排到了第一个。

Mysql执行的流程是:首先使用age排序,但是发现小黄和小颖age重复,所以使用第二个字段继续排序。第二个字段为user_id DESC所以user_id比较大的被排到了前面。

注意

  1. ORDER BY 后面的关键字DESC或者ASC必须跟在每一个字段后面,来表示当前字段的排序。

  2. 在Mysql的默认行为中,文本a和A开头的字符具有相同的排序优先级。但是也可以通过数据库设置来修改该行为。

  3. 如果在需要使用LIMIT的语句中也使用了ORDER BY关键字,那么LIMIT应该放在ORDER BY之后,否则数据库会报错。

条件检索

数据库一般都包含大量数据,但是往往我们都不需要一次检索出全部,也不会说只用LIMIT来限制。我们通常会有针对性的筛选需要的数据,比如年龄为25岁的所有用户。这时候就需要WHERE筛选数据,给出的搜索条件也被称为过滤条件。

WHERE子句

在Mysql,我们可以在FROM关键字之后给出WHERE关键字来筛选数据:

SELECT * FROM my_user WHERE age=25;

这条SQL我们检索出了所有年龄为25岁的用户。我们使用了age=25,当然Mysql还可以执行更多的功能,比如大于、小于等等,看下面一个知识点。

注意:如果在SQL语句中需要使用WHERE和ORDER BY,请将ORDER BY放到WHERE之后,否则会报错

操作符

上面我们仅仅演示了age=25的情况,其实Mysql还支持更多的操作符(上个例子中的=号就是操作符)

操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 指定的两个值之间

下面我们拿之前的用户表演示一下

所有数据

SELECT * FROM my_user;

不等于

SELECT * FROM my_user WHERE age <> 25;
SELECT * FROM my_user WHERE age != 25; // 这两个结果一样

小于

这次我们改为了小于20

SELECT * FROM my_user WHERE age < 20;

小于等于

SELECT * FROM my_user WHERE age <= 20;

大于

SELECT * FROM my_user WHERE age > 20;

大于等于

SELECT * FROM my_user WHERE age >= 20;

BETWEEN

SELECT * FROM my_user WHERE age BETWEEN 19 AND 22;

  1. BETWEEN关键字后面需要跟上2个数值,表示一个范围,两个数值之间使用AND分割。

  2. BETWEEN后面的两个数值检索出来的范围包含这两个数值。比如我们BETWEEN 19 AND 22就是19、20、21、22都可以

组合查询

上面的例子中,我们WHERE后面值跟了一个条件。其实,WHERE后面允许跟随多个条件,他们允许用AND或者OR进行连接。

AND

and表示并且的关系。

SELECT * FROM my_user WHERE age = 25 AND user_id = 4;

上面的例子中我们使用and连接了两个条件age=25user_id=4。它的意思是年龄为25岁的用户,并且用户ID为4的用户。

之前我们查询age=25的时候有两条记录,但是我们and了一个条件,结果变为了一个。所以,and是缩小范围。

OR

or表示或者的关系。

SELECT * FROM my_user WHERE age = 25 OR user_id = 1;

这次我们查询年龄为25岁,或者用户ID为1的用户。所以年龄为25岁,或者用户ID等于1的用户都会被检索出来,哪怕年龄不为25岁。

所以可以看出or关键字链接的语句,可能会放大查询的范围。

计算优先级

首先查看一个例子

SELECT * FROM my_user WHERE age = 25 OR user_id = 1 AND user_id > 3;

按照正常的思维理解,我们先获取到了年龄等于25岁的,或者用户ID为1的用户:

我们应该得到如下结果:

再加上用户ID大于3的条件,我们应该得到用户ID为4的用户小颖的信息。而,实际操作中我们发现结果并不是一条,而是两条:

我们再次使用括号将前面两个条件括起来,执行一次:

SELECT * FROM my_user WHERE (age = 25 OR user_id = 1) AND user_id > 3;

得到了我们想要的答案(尽管没啥意义)。

第一条语句产生错误的结果是优先级不同。AND的优先级高于OR优先级。所以第一条语句中Mysql是先计算了user_id = 1 AND user_id > 3这个条件没有得到任何的数据。加上后面的or age =25.所以最后结果就只剩下年龄为25岁的用户记录了。

所以,在实际开发中,遇到OR和AND并存的情况一定要注意是否会应为优先级影响结果。使用圆括号可以改变Mysql计算的优先级,一定要慎重。

IN

IN关键字非常有意思。他包含一个括号,像一个函数。在括号里面你可以写上使用逗号分割的数值,只要数值与括号内的匹配,那么就符合条件。

SELECT * FROM my_user WHERE age IN(25,20);

我们使用上面的语句检索出了年龄等于20或者25岁的用户。而且IN还可以和OR关键字互相替换。比如上面的IN语句可以转换为:

SELECT * FROM my_user WHERE age =20 OR age =25;

但是为啥还是要用IN:

  1. IN操作符的语法更加简洁,SQL语句长度缩短,不容易出错。

  2. 计算的优先级更加容易看出,不用考虑OR和AND那些关系

  3. IN操作符执行速度快于OR

  4. IN还可以结合子查询使用,动态的生成查询值(后续文章中会说到子查询)

NOT

not操作符就只有一个功能,否定后面跟的条件。

SELECT * FROM my_user WHERE age NOT IN(20,25);

结果是不是和刚刚反了过来。

附录

创建my_user表的语句:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for my_user
-- ----------------------------
DROP TABLE IF EXISTS `my_user`;
CREATE TABLE `my_user` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `password` varchar(40) NOT NULL,
  `code` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of my_user
-- ----------------------------
INSERT INTO `my_user` VALUES ('1', '小红', '20', '7c4a8d09ca3762af61e59520943dc26494f8941b', '2arfs5dr6m');
INSERT INTO `my_user` VALUES ('2', '小明', '19', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'd59tg6dr5h');
INSERT INTO `my_user` VALUES ('3', '小黄', '25', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'w56tg9hjn3');
INSERT INTO `my_user` VALUES ('4', '小颖', '25', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'a5d23e9yh5');
INSERT INTO `my_user` VALUES ('5', '星空幻颖', '22', '7c4a8d09ca3762af61e59520943dc26494f8941b', '86d2sadft9');

图片所示的软件工具是navicat,更多的 数据库管理软件,百度:笔点资源:发现更多开发干货集合资源

星空幻颖,严颖

个人主页:segmentfault

内容来源:https://segmentfault.com/a/1190000008063533

用户评论
开源开发学习小组列表