[mysql]mysql排序和分页

#排序和分页本身是两块内容,因为都比较简单,我们就把它分到通一个内容里.

#1排序:

SELECT * FROM employees
#我们会发现,我们没有做排序操作,但是最后出来的107条结果还是会按顺序发出,而且是每次都一样.这我们就有一个疑惑了,现在我们的数据库是根据什么来排序的,在我们没有进行排序操作之前,是按工资还是部门id.默认的顺序总得有一个储存的顺序把,在这里面的顺序其实默认的顺序就是我们先后添加的顺序,这个我们做个了解

这里我们会发现,我们买东西之类开发的软件都会有一个排序多个功能.对应京东淘宝默认的显示顺序,我们可以理解成添加顺序,用户如果想按照效率,价格等顺序排序,这些排序的诉求是很正常的.我们的述求现在是按照salary从高到第的顺序去显示员工信息

基本排序

#使用ORDER BY对查询到达数据进行排序操作,order代表的就是排序,by代表的急速那个字段
SELECT * FROM employees ORDER BY salary;

我们发现,诶不对,不是按从高到低的顺序排序,反而是按照从低到高的顺序排序,那怎么办呢,不对怎么去表达从高到低呢,所以我们要指明升序操作还是降序操作.这里升序代表的是ASC和DESC,代表的是ascend和descent升序和降序的缩写

那么我们就在salary的后面加是desc

升序降序

SELECT * FROM employees ORDER BY salary DESC;

如果我们没有写ASC或者DESC的话我们默认就是升序排序排列,也就是ASC.这里我们就明白了,我们在升序的时候可以写ASC也可以不写,但是降序就必须写DESC了

别名和ORDER BY的使用

我们可以使用列的别名,进行排序.什么意思呢,就如下图

SELECT employee_id.salary*12 annual_salary FROM employees ORDER BY annual_salary DESC;

,大家可能会疑惑,这个不是挺简单的吗,没有必要将,但是因为我们是先将的列的别名讲完在讲的过滤WHERE,那么我问你们,能不能直接使用过滤别名这个操作呢,我们试一试

我们发现报错了,我们的别名只能在ORDER BY里使用,但是不能在WHERE里使用,这里我们透露一个天机,我们的SELECT语句,还没有既把WHERE和ORDER BY要求使用,一定要记住,WHERE是和FROM一起使用的.

SELECT employee_id,salary*12 annual_salary FROM employees 
WHERE employee_id>10
ORDER BY annual_salary DESC;

强调格式

这里我提醒一下大家如果使用的不是这个字段进行排序,那么也不会报错,我们强调的点是我们的SQL的语句不是从前往后走的,如果是从前往后的话,那么我们的别名肯定就被发现了,但是我们实际是使用的是先对FROM进行查看有什么表和什么条件WHERE然后我们看看你想查什么字段,SELECT 然后我们再对ORDER BY进行排序.

也就是说先创建一个总表和条件的临时表,然后再挑选字段修改别名,然后再对这个表进行排序.

WHERE我们必须声明在FROM后面.再次提醒.

多级排序

我们实际的需求中,实际上我们看一下我们的字段,我们是按照部门的降序排列的,我们有很多员工他们的部门id是一样的.这种情况下他们是那个优先显示呢

.

这个时候我们就要对它进行二级排序,我们要显示员工信息,安装department_id降序排序然后再按salary升序排序,.这样怎么写呢

这时我们就发现是先按降序然后如果序号相同就按工资排序,如果我们把salary的顺序去掉,我们要记得NULL代表的最小的值,降序在第一个升序在最后一个,如果要进行三级排序的话,就是再加一个,

字段和排序规则.我们就统一叫多列排序,,

LIMIT分页,

我们刚才说了我们希望他们有排序的功能的,我们排序的综合排序里可能就涉及很多个规则,广告费之类的,我们查询数据的时候,我们希望数据库给我们发几万条数据吗,其实是不希望,我们也看不过来,我们就只需要几页就可以了.我们知道吃饭的时候我们点外卖的时候,我们把方圆2公里的饭店都反馈给你,我们是不是看不过来,没有必要返回那么多数据,如果网速一样,我们返回的数据的时间也会不一样,我们希望快一点,我们也看不了这么多.我们需要我们再下一页看是不是就可以了.如果我们看下一页是不是会对网络有要求.

分页的必要性还是有,我们只需要一页一页的去查看.

如果前面有你想要的结果了那你就不会查看后面的结果了.在mysql里面我们是怎么处理分页的功能.

分页的关键字是LIMIT实现数据的分页显示

SELECT employee_id,last_name FROM employees LIMIT 这里我们运行之后会发现,全部都出现了

SELECT employee_id,last_name FROM employees LIMIT 0,20 这里的0代表的偏移量,他会指最开始的第一条数据,如果是1就是第二条记录,所以我们叫他偏移量,后面的20代表的就是需要多少条记录.

我们现在显示的就是第一页的数据.这是我们的需求1,我们现在想显示第二页的数据,我们就应该从120id开始

这个时候我们的偏移量应该写多少呢,SELECT employee_id,last_name FROM employees LIMIT 20,20,

如果这样往下走的话,我们是不是就可以写成一个公司,我要显示pageSize条记录,此时显示第pageNumber

SELECT employee_id,last_name FROM employees LIMIT (page-1*pagesize),pagesize.

因为第一页是不是偏移量是0阿,我们就要减一,如果我想显示,第某页的数据,这个就是我们要的pageno,我们点击页面的时候这个值就会发给我们后台,后台的程序就会把写好的程序交给数据库,就会出现我们需要的数据.

这个limit我们现在用的比较纯粹.

我们现在要考虑ORDER BY WHERE LIMIT 的声明顺序

SELECT employee_id,last_name,salary 

FROM employees

WHERE salary>6000

ORDER BY salary desc

LIMIT 0,10;

这里LIMIT 有两个参数偏移量和条目数,严格上来说是这样,但是如果你只输入一个参数,,LIMIT 0,条目数和LIMIT 条目数是相同的.也就是只输入一个参数,那它就会把偏移量看成0,参数看成条目数

我们要记住声明顺序,LIMIT 和ORDER BY是要放在最后的.

使用的几个场景

如果表里有4条记录我们只想看23条记录.

类似的函数 

如果我们不想看那么多数据,只想要一部分数据.

LIMIT 有个新场景MYSQL8.0 LIMIT...OFFSET

比如我们想显示107条数据的里面的32条和33条数据.

SELECT * FROM employees LIMIT 31 OFFSET 2.我们这个跑出来的结果会是,第2条之后的31条记录,也是比较简单.就是把参数换一个,然后把逗号换成limit.

来个小练习查询员工表中的工资最高的员工

SELECT  employee_id,last_name,salary FROM employees ORDER BY DESC LIMIT 1

拓展:

这个分页在mysql PGSQL mariaDB SQLite使用的是LIMIT,但是其他的数据库不一定

如果用的是SQL server 使用速TOP关键词,要写在SELECT后面 直接跟正在SELECT top 5这样就是取5个数字

DB2就要用FECTCH FIRST 5 ROWS ONLY这样

Oracle就是要用ROWNUM的条件来统计行数.WHERE rownum<5,这个rownum是Oracle对每个表提供的一个隐藏的索引字段,代表的急速前5个数据库.

课后练习题

第05章_排序与分页

#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据

( 一定要注意结果看起来对不对因为我们经常会出现SQL语句并没有报错,但是其实结果是不满足要求的情况.我这边是建议大家每写一个条件就跑一下,看看对不对,到时候子查询这种情况会更多,因为那是我们难度的巅峰)

S#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

( 还记得我们当时的比较规则也是在my.ini里对比的规则,类似我们现在排序,如果我们用的字符集就是utf那么比较规则也是utf-8的规则.那么我们要如何使用字节数来排序呢,那么我们就要用函数LENTH,这个是有点超纲的,不能直接用email排序.)

答案： 1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序显示

SELECT last_name,department_id,salary * 12 annual_sal FROM employees ORDER BY annual_sal DESC,last_name ASC;

2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第 21到40位置的数据 SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC LIMIT 20,20;

3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号 升序

SELECT last_name,email,department_id FROM employees #where email like '%e%' WHERE email REGEXP '[e]' ORDER BY LENGTH(email) DESC,department_id ASC