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讲：河北省征信

相关内容

热门资讯

愉快假期作文五年级35篇【优... 愉快假期作文五年级35篇 篇一我度过的愉快假期假期终于来临了，我迫不及待地踏上了愉快的假期之旅。这个...
我的奶奶五年级作文500字【... 篇一：我的奶奶五年级作文500字我的奶奶是我最亲近的人之一，她是一位和蔼可亲的老人，也是我心目中的英...
第一次走夜路五年级作文【推荐... 第一次走夜路五年级作文 篇一第一次走夜路昨天晚上，我第一次独自走夜路回家。这是一个令人紧张又刺激的经...
五年级上册语文作文（精彩3篇... 五年级上册语文作文 篇一我最喜欢的动物我最喜欢的动物是熊猫。熊猫是中国的国宝，也是世界上最可爱的动物...
创新想象作文五年级400字2... 创新想象作文五年级400字26篇 篇一标题：未来的交通工具未来，交通工具将发生翻天覆地的变化。现在我...
五年级关于去肯德基作文大全【... 五年级关于去肯德基作文大全 篇一第一篇内容：我对肯德基的喜爱作为一个五年级的学生，我非常喜欢去肯德基...
五年级作文美丽就在我身边40... 篇一：美丽就在我身边五年级作文美丽就在我身边400字作文结尾 篇一通过写这篇作文，我深刻地感受到了身...
小学五年级书的作文400字【... 小学五年级书的作文400字 篇一我的好朋友我有一个好朋友，她叫小芳。她是我班上的同学，我们从一年级就...
漫画老师作文300字五年级上... 漫画老师作文300字五年级上册 篇一我喜欢的漫画老师我们学校有一位非常特别的老师，他就是我们的漫画老...
我的座右铭五年级作文（经典6... 我的座右铭五年级作文 篇一勇敢迎接挑战挑战是生活中不可避免的一部分。无论是学习上的困难，还是生活中的...
小学五年级母爱的作文600字... 小学五年级母爱的作文600字 篇一母爱是最伟大的妈妈是一个家庭中最重要的人，也是最伟大的人。她为我们...
我的妹妹五年级500字作文1... 我的妹妹五年级500字作文12篇 篇一标题：我和妹妹的五年级生活我是一个五年级的学生，而我的妹妹也刚...
小学五年级写信作文400字（... 小学五年级写信作文400字 篇一亲爱的爸爸妈妈：您好！我是你们的儿子，现在正在上小学五年级。我很想和...
杭州乐园游记五年级作文【精简... 杭州乐园游记五年级作文 篇一我喜欢杭州乐园终于盼到了暑假，我和家人一起来到了杭州乐园。杭州乐园是一个...
小学五年级作文博物馆21篇（... 小学五年级作文博物馆21篇 篇一：我的首次博物馆之旅今天，我们班级去了博物馆参观，这是我第一次去博物...
狼与狗的作文五年级【优秀6篇... 狼与狗的作文五年级篇一狼与狗是两种相似又不同的动物。狼是一种野生动物，而狗是人类驯化的宠物。它们在外...
孔雀五年级作文【精选6篇】 孔雀五年级作文 篇一我的宠物孔雀我有一只非常特别的宠物——孔雀。它拥有绚丽多彩的羽毛和优雅的姿态，每...
小学五年级母爱的作文400字... 小学五年级母爱的作文400字 篇一母爱的伟大在我们的成长过程中，母爱是我们最亲近的陪伴者。她们默默地...
小学五年级作文我多么快乐记叙... 小学五年级作文我多么快乐记叙文 篇一快乐的周末游玩周末终于来临了，我迫不及待地和爸爸妈妈一起计划着如...
午夜饭作文五年级13篇【精选... 午夜饭作文五年级13篇 篇一：午夜饭的美味与温暖午夜饭，是我小时候最喜欢的一餐。每当夜幕降临，家里的...