MySQL基础（二）排序与分页、多表查询、单行函数
创始人
2024-05-28 14:50:49
0次

上接 

MySQL基础（一）SQL分类、导入、SELECT语句，运算符_独憩的博客-CSDN博客

目录

排序与分页 

排序

二级排序

分页

多表查询

基础多表查询

等值连接vs非等值连接

自连接vs非自连接

内连接vs外连接

自然连接

单行函数

 数值函数

 字符串函数

 日期和时间函数

获取日期、时间 

日期与时间戳的转换

 获取月份、星期、星期数、天数等函数

日期的操作函数

 时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

 流程控制函数

 加密与解密函数

MySQL信息函数

 其他函数

排序与分页 

排序

如果不加任何排序操作，那显示数据的顺序就是数据添加的顺序

使用 ORDER BY 子句排序
        ASC（ascend）: 升序
        DESC（descend）:降序
ORDER BY 子句在SELECT语句的结尾。

SELECT * 
FROM employees
ORDER BY salary ASC

也可以根据 列的别名 来排序

SELECT salary*12 salary_all
FROM employees
ORDER BY salary_all ASC
  • 值得注意的是，列的别名 只能在order by中使用，不能在 where 使用 ，在一起使用时，先使用where再使用order by
  • 因为这里的操作顺序是  FROM--WHERE--SELECT--ORDER BY，这也解释了WHERE不能用别名的原因
SELECT salary*12 salary_all
FROM employees
WHERE salary>8000
ORDER BY salary_all ASC

二级排序

在排序时，会遇到数据相同的情况，那么这些数据就可以通过二级排序再次进行排序

SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC

先根据department_id 降序排列，再根据salary升序排列

分页

  • 所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
  • MySQL中使用 LIMIT 实现分页

 显示前20条记录，代表从 第0个数据开始，显示20条数据

SELECT employee_id,salary,department_id
FROM employees
LIMIT 0,20

同理，显示第二页，即20-40数据

SELECT employee_id,salary,department_id
FROM employees
LIMIT 20,20

同时使用where，order by ，limit的顺序为

SELECT employee_id,salary,department_id
FROM employees
WHERE salary>8000
ORDER BY salary ASC
LIMIT 20,20

多表查询

基础多表查询

  • 多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
  • 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联

 这里提供一个例子说明什么是多表查询

这里有三个表，employees表通过department_id与departments表 联系，departments表通过location_id与location表联系，这就是一个典型的多表结构

那为什么不直接用一个表写所有的信息呢？

举一个例子：departments表中有很多员工的department_id是1，在department表中，部门1的信息一行就能描述完全，但如果合成一个表，那所有部门是1的员工后面都要加上department表中部门1的信息，这会造成 冗余

在这里，例如通过一个员工的employee_id查询其city，就是一个典型的多表查询

例如我想找到employee_id =110这个员工的department_name，很自然的想到：

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.employee_id =110

 

会出现错误，结果显示这个人在每个部门都工作过，这是因为这里出现了笛卡尔积的错误 ，即将employee_id=110这个人与每一个department信息连接，原因是没有建立两个表直之间的连接

正确方式，先建立连接employees.department_id = departments.department_id 再提供需求

employees.employee_id =110 

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id AND employees.employee_id =110

注意到，两个表中都有department_id这列，当我想要查询它时，必须指明来源：

SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id AND employees.employee_id =110

为了简化代码，可以给表起别名：

SELECT employee_id,department_name,e.department_id
FROM employees e,departments d
WHERE e.department_id = d.department_id AND e.employee_id =110

同理，三个表的时候，只需要再加一个连接条件就可以了

即如果n个表实现多表查询，则至少需要n-1个连接条件

SELECT employee_id,department_name,e.department_id,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_idAND e.employee_id =110

等值连接vs非等值连接

前面说的都是等值连接

案例：

假设 job_grades表 中的存放着一些薪水分类标准

现在想根据这个标准，查询employee表中的数据：

SELECT e.employee_id,salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

 这就是一种 非等值连接

自连接vs非自连接

自连接的意思是，在一个表中，会自己引用自己

例如下面中这个表，每个 employee  都有一个 manager_id，这个 manager_id 本身也是employee_id

需求是，查询每个人的id，名字及其manager的id，名字 ：

SELECT e1.employee_id,e1.last_name,e2.employee_id 'manager id',e2.last_name 'manager name'
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id

 

这种连接就叫自连接，显然之前的都是非自连接

内连接vs外连接

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为主表 ，右边的表称为从表。
  • 如果是右外连接，则连接条件中右边的表也称为主表 ，左边的表称为从表。

 对于基础多表查询中的那个例子中，连接条件是e.department_id = d.department_id ，但是由于e表中并不是每个人都有department_id，所以在联合查询中会省略这种数据，这就是内连接

这里引入SQL99语法，使用 JOIN 表名 ON  连接条件 ，可以改写为：

SELECT employee_id,department_name,e.department_id,l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id 
JOIN locations l
ON d.location_id = l.location_id

 

左外连接，右外连接：

SELECT employee_id,department_name,e.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id

e中某些没有 department_id，用 左外连接  LEFT JOIN 可以使这部分也显示

同理 d中某些没有department_id，用 右外连接  RIGHT JOIN 可以使这部分也显示

对应了上图中的左上图与右上图

左中图与右中图：

SELECT employee_id,department_name,e.department_id
FROM employees e left join departments d
on e.department_id = d.department_id 
where d.department_id is null

d.department_id is null 就将中间部分去除了 

满外连接（左下图）

FULL OUTER JOIN在mysql中不能用，故我们这里考虑采用左上图与右中图联合

先介绍UNION 操作符和UNION ALL操作符

  • UNION 操作符返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
  • 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
    不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

只需要在两者之间加上UNION操作符就可以：

SELECT employee_id,department_name,e.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id 
UNION ALL
SELECT employee_id,department_name,e.department_id
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id 
WHERE e.department_id IS NULL

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接 

SELECT employee_id,department_name,e.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id 
AND e.manager_id = d.manager_id

等价于

SELECT employee_id,department_name,e.department_id
FROM employees e NATURAL JOIN departments d

单行函数

MySQL 函数 | 菜鸟教程

单行函数

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值

 数值函数

基础函数

 三角函数

 指数和对数

 字符串函数

 

 日期和时间函数

获取日期、时间 

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

  日期与时间戳的转换

 时间戳是将时间转换为一串数字进行存储

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2000-9-24'),FROM_UNIXTIME(UNIX_TIMESTAMP())
FROM DUAL;

 获取月份、星期、星期数、天数等函数

日期的操作函数

 

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

 时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

 FMT可以取以下：

SELECT DATE_FORMAT(NOW(),'%y-%M--%D-%e')
FROM DUAL;

 GET_FORMATE可以得到不同地区的FMT

SELECT get_format(DATE,'USA')
FROM DUAL;

 流程控制函数

 IF(value,value1,value2)，这个类似与三元运算符

SELECT last_name,salary,IF(salary>=6000,'high','low')
FROM employees

 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2
.... [ELSE resultn] END

SELECT last_name,salary,case when salary>=15000 then '高薪'when salary >=1000 then '中薪'when salary >=8000 then '低薪'else '草根' END 
FROM employees

 加密与解密函数

加密函数是不可逆的

SELECT MD5('123123213'),SHA('sada')

MySQL信息函数

 其他函数

 

词库加载错误:未能找到文件“E:\highferrum_mysql\Configuration\Dict_Stopwords.txt”。

上一篇：四川大学软件学院｜系统级编程期末复习

下一篇：地方征信平台第2讲：河北省征信

相关内容

热门资讯

我是歌手歌唱比赛主持词 我是歌手歌唱比赛主持词　　小歌手主持词篇一　　A：尊敬的各位领导　　B：敬爱的老师，亲爱的同学们　　...
中秋节联欢晚会主持词 中秋节联欢晚会主持词（精选11篇）　　主持词要注意活动对象，针对活动对象写相应的主持词。我们眼下的社...
初中新生开学欢迎词 初中新生开学欢迎词2017各位初一年全体同学石狮二中敞开胸怀迎接你们，真诚地欢迎你们加入这个大家庭，...
品鉴会主持词 品鉴会主持词　　借鉴诗词和散文诗是主持词的一种写作手法。随着中国在不断地进步，各种集会的节目都通过主...
新年半台词 新年三句半台词　　三句半是一种中国民间群众传统曲艺表演形式。每段内容有三长句一半句。一般由4人演出，...
消夏文艺晚会的主持词 消夏文艺晚会的主持词（精选11篇）　　主持词是主持人在节目进行过程中用于串联节目的串联词。在各种集会...
英雄联盟经典台词 英雄联盟经典台词　　英雄联盟经典台词　　1、正义，要么靠法律，要么靠武力!　　2、你迷失在黑暗之中，...
小学元旦节的主持词 小学元旦节的主持词（精选16篇）　　主持词是主持人在台上表演的灵魂之所在。在当今不断发展的世界，各种...
婚纱走秀主持词 婚纱走秀主持词三篇　　篇一：婚纱走秀演出主持词　　当您披上洁白的婚纱，点亮您一生中最美丽的日子，您是...
医者仁心台词 医者仁心台词大全　　1. 钟立行对丁祖望：我们都在努力做一个能够被人怀念的人。　　2.罗雪樱旁白：从...
《美丽人生》的经典台词 《美丽人生》的经典台词　　意大利电影《美丽人生》，由罗伯托贝尼尼自编自演，讲述了意大利一对犹太父子被...
二年级主持词 二年级主持词　　主持词分为会议主持词、晚会主持词、活动主持词、婚庆主持词等。在一步步向前发展的社会中...
年会的主持词 年会的主持词范文（通用5篇）　　根据活动对象的不同，需要设置不同的主持词。时代不断在进步，主持词是活...
姨妈的后现代生活经典台词分享 姨妈的后现代生活经典台词分享　　吉日良辰当欢笑，为什么鲛珠化泪抛?此时却又明白了，世上何尝尽富豪。也...
学校语文教研活动主持词 学校语文教研活动主持词　　借鉴诗词和散文诗是主持词的一种写作手法。在一步步向前发展的社会中，很多晚会...
六一庆祝大会主持词 六一庆祝大会主持词　　六一就是我们的节日，六一就是一个欢乐的日子，下面小编整理的六一庆祝大会主持词，...
婚礼感谢词 婚礼感谢词(15篇)婚礼感谢词1各位来宾，各位亲友:　　大家晚上好！　　今日是我女儿XXX和女婿XX...
文艺汇演主持词开场白 文艺汇演主持词开场白9篇　　根据活动对象的不同，需要设置不同的主持词。我们眼下的社会，活动集会越来越...
总结大会主持词 　　导语：春去秋回又一栽，似水流年旺年来。　　2017年公司年会主持词　　一.主持人开场白　　男：一...
农村结婚典礼主持词 农村结婚典礼主持词　　一对新人立堂前，两心相印似蜜甜，三生有幸结良缘，下面是小编分享的 农村结婚典礼...