小驴不开心要学java -- MySQL入门篇4

引言：这个章节补充小知识点限制行数/分页查询的用法。本章节第一部分主要针对于SQL中的单行函数（数学函数、字符串函数、日期和时间函数、转换函数、流程控制函数以及其他的一些函数）的用法详细介绍；第二部分主要针对于多表连接的用途和用法展开简单的介绍，并且使用范例进行详细介绍，如何在多表间实现数据查询。

（以后每次的更新都会在引言部分里标明一下这一章节包含的知识点大纲）

---

首先，补充一个很小但是很重要的知识点

限制行数/分页查询

使用select语句时，如果碰到需要返回的是某几条或者中间几条的记录，如果不添加限制是不能实现要求的，可以使用关键字limit实现这个功能。

语法格式：

SELECT 字段名称

FROM 表名

LIMIT [开始的下标，] 获取的个数  

注意：这里需要使用的是英文标点，为了明显显示出有标点的存在，使用的是中文标点。

范例1：

-- 场景：获取20部门工资最高的三个员工的薪资

-- tables? emp

-- columns? sal

-- conditions? 20部门 deptno = 20

-- order by1? sal desc 取前三个

SELECT sal
FROM emp 
WHERE deptno = 20
ORDER BY sal DESC 
LIMIT 3;

PS：以后不是所有的代码都有运行截图

范例2：

-- 场景：获取emp表中薪资排列在4-8位的员工编号和薪资

-- tables? emp

-- columns? empno sal

-- conditions? 无

-- order by1? sal desc 3,5

SELECT empno, job, sal, comm, hiredate
FROM emp 
ORDER BY sal DESC 
LIMIT 3,5;

范例3：

-- 场景：将emp表中的内容显示出来，每页显示5条信息，请查询出第二页显示的信息|

-- 第二页的信息 第二页从哪里开始

-- 第一页5条 第一条到第五条 0 ~ 4 起始 0 1 5 (页数 - 1) * 每页显示的条数 = 当前页的起始下标

-- 第二页5条 第一条到第五条 5 ~ 9 起始 5 2 5

SELECT * 
FROM emp 
LIMIT 5,5;

练习
1. 查询入职日期最早的前五名员工姓名，入职日期。
2. 查询工作在啊CHICAGO并且入职日期最早的前2名员工姓名，入职日期。
3. 按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门编号。

---

今日的主题内容

 单行函数

什么是单行函数？

单行函数其实就是MySQL为我们提供的一些功能强大且方便使用的函数，在进行数据库管理和数的查询或者对数据操作的时候，提高我们对数据库的管理效率。

注：dual 虚表 表没有真实的存在，仅仅是为了补全查询语句的结构

 单行函数的特点

• 单行函数对单行操作
• 每行返回一个结果
• 有可能返回值与原参数数据类型不一致
• 单行函数可以写在SELECT、WHERE、ORDER BY子句中
• 有些函数没有参数，有些函数包括一个或者多个参数
• 函数可以嵌套

单行函数的分类

• 数学函数
• 字符串函数
• 日期和时间函数
• 流程控制函数
• 其他的一些函数

1）数学函数

函数	作用
ABS(x)	返回x的绝对值；
SQRT(x)	返回非负数x的平方根；
PI(x)	返回圆周率
MOD(x,y)	返回x被y除的余数；
CEIL(x)、CEILING(x)	返回大于或者等于x的最小整数值；
FLOOR(x)	返回小于或者等于x的最大整数值；
ROUND(x,y)	返回保留小数点后面y位，四舍五入的整数；
TRUNCATE(x,y)	返回被舍弃的小数点后y位的数字；
RAND()	每次产生不同的随机数；
SIGN(x)	返回参数的符号；
POW(x,y)和POWER(x,y)	返回x的y次乘方的结果的值；
EXP(x)	返回以e为底的x乘方后的值；
LOG(x)	返回x的自然对数，x相对于基数e的对数；
LOG10(x)	返回x的基数为10的对数；
RADIANS(x)	将参数x由角度转化为弧度；
DEGREES(x)	将参数x由弧度转化为度；
SIN(x)	返回x正弦，其中x为弧度值；
ASIN(x)	返回x的反正弦，即正弦为x的值；
COS(x)	返回x的余弦；
ACOS(x)	返回x反余弦；
TAN(x)	返回x的正切；
ATAN(x)	返回x的反正切；
注意：	用“橙色标记的是必须掌握的”，其余的了解即可

范例：

-- 数学函数  ABS() 绝对值、PI() 圆周率、SQRT() 非负数的平方根、MOD() 余数、CEIL() 向上取整、FLOOR() 向下取整、ROUND() 返回保留位数的，四舍五入的整数、TRUNCATE() 返回舍弃小数点指定位数以后的数字

SELECT abs(-12), pi(), sqrt(9), MOD(4, 3), CEIL(4.5), floor(4.5), round(5.256, 2), truncate(3.1415926, 2)
FROM DUAL;

2）字符函数

函数	作用
CHAR_LENGTH(str)	返回字符串str的所包含字符个数；
LENGTH(str)	返回字符串str的长度；
CONCAT(s1,s2,...)	字符串连接；
CONCAT_WS(x,s1,s2,…)	字符串连接，x是其它参数的分隔符；
INSERT(s1,x,len,s2)	返回字符串s1，s1中插⼊字符串s2；
LOWER (str) | LCASE (str)	将字符串全部转换成小写字母；
UPPER(str) | UCASE(str)	将字符串全部转成大写字母；
LEFT(s,n)	返回最左边指定长度的字符；
RIGHT(s,n)	返回最右边指定长度的字符；
LPAD(s1,len,s2) | RPAD(s1,len,s2)	填充字符串函数；
TRIM(s1 FROM s) | LTRIM(s) | RTRIM(s)	删除空格函数；
REPEAT(s,n)	重复生成字符串函数；
SPACE(n)	返回一个由n个空格组成的字符串；
REPLACE(s,s1,s2)	字符串替换函数；
STRCMP(s1,s2)	比较字符串大小函数；
SUBSTRING(s,n,len)	获取子串函数；
LOCATE(str1,str) | POSITION(str1 IN str) | INSTR(str, str1)	匹配子串开始位置函数；
REVERSE(s)	将字符串s反转；
ELT(N,字符串1,字符串2,字符串3,…)	返回指定位置函数；
注意：	用“橙色标记的是必须掌握的”，其余的了解即可

范例1：

-- 字符函数  CHAR_LENGTH() 返回字符串包含的字符个数、LENGTH() 返回字符串长度、CONCATE() 字符串拼接、CONCATE_WS() 返回的是以指定符号拼接的字符串

SELECT char_length('123456789'), LENGTH('123456789'), concat('hello', 'world'), concat_ws(',', 'hello', 'world')  
FROM DUAL;

范例2：

-- 字符函数   INSERT() 返回插入字符串后的字符串、LOWER() 将字符串字母全部转成小写、UPPER() 将字符串全部转成大写

SELECT INSERT('hello', 2, 2, 'DATA'), lower('DATA'), upper('hello')  
FROM DUAL;

 范例3：

-- 返回字符串指定方向的几个字符

SELECT LEFT('hello world', 4), RIGHT('hello world', 4)
FROM DUAL;

范例4：

-- 补全位数

SELECT rpad(ename, 10, '*'), lpad('hello world', 18, '*'), rpad('hello world', 18, '*')
FROM emp ;

 范例5：

-- trim除了可以直接删除两端的空格之外，还能删除两端的指定字符

SELECT trim('   nihao   '), ltrim(' nihao   '), rtrim(' nihao   '), trim('l' FROM 'lhellol')  
FROM DUAL;

范例6： 

-- 重复、替换、截取指定位置/长度 的字符

SELECT REPEAT('hello', 3), REPLACE('hello', 'l', '-'), substring('hello world', 3, 3), substring('hello world', 3)  
FROM DUAL;

 范例7：

-- 反转

SELECT reverse('hello') 
FROM DUAL;

练习
1. 写一个查询，分别计算100.456四舍五入到小数点后第2位，第一位，整数位的值。
2. 写一个查询，分别计算100.456从小数点后第2位，第1位，整数位截断的值。
3. 显示所有员工姓名的前三个字符。
4. 显示正好为5个字符的员工的姓名，工资，部门号。
5. 写⼀个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，
并给每列⼀个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的
雇员，并对查询结果按雇员的ename升序排序。（提示：使⽤length、substr）
6. 查询员工姓名中中包含大写或小写字母A的员工姓名。

3）日期和时间相关的函数

函数	作用
CURDATE()和CURRENT_DATE()	获取当前日期函数；
NOW()	返回服务器的当前日期和时间；
CURTIME()	返回当前时间，只包含时分秒；
UTC_DATE()	返回世界标准时间日期函数；
UTC_TIME()	返回世界标准时间函数；
TIMEDIFF(expr1, expr2)	返回两个日期相减相差的时间数；
DATEDIFF(expr1, expr2)	返回两个日期相减相差的天数；
DATE_ADD(date,INTERVAL expr type)	日期加上一个时间间隔值；
DATE_SUB(date,INTERVAL expr type)	日期减去⼀个时间间隔值；
DATE(date)、TIME(date)、YEAR(date)	选取日期时间的各个部分；
EXTRACT(unit FROM date)	从日期中抽取出某个单独的部分或组合；
DAYOFWEEK(date) 、DAYOFMONTH(date) 、 DAYOFYEAR(date)	返回日期在一周、一月、一年中第几天
DAYNAME、MONTHNAME	返回日期的星期和月份名称；
DATE_FORMAT(date,format)	格式化日期；
TIME_FORMATE(time,formate)	格式化时间；
注意：	用“橙色标记的是必须掌握的”，其余的了解即可

范例1：

-- 日期时间相关的函数 CURDATE()和CURRENT_DATE()  返回当前日期、CURRENT_TIME()  返回当前时间、CURRENT_TIMESTAMP() 返回当前日期和时间

SELECT curdate(), current_date(), current_time(), current_timestamp()
FROM DUAL;

范例2：

-- 时间计算

SELECT timediff('12:23:43', '23:32:32'), datediff('2023-12-12', '2020-02-05')
FROM DUAL;

SELECT date_add('2021-12-23', INTERVAL 1 DAY), date_sub('2021-12-23', INTERVAL 3 MONTH) 
FROM DUAL;

SELECT now() + INTERVAL 2 DAY, now() - INTERVAL 3 MONTH 
FROM DUAL;

范例3：

-- 获取时间的部分

SELECT now(), DATE(now()), TIME(now()), YEAR(now()), MONTH(now())
FROM DUAL;

范例4：

-- 格式化时间

-- 将日期和时间格式化的函数： DATE_FORMAT(date,format)、 TIME_FORMAT(time,format)

-- 根据format 指定的格式显示日期或者时间值。

-- date 参数是合法的日期

-- format 规定日期/时间的输出格式

SELECT date_format(now(), '%Y-%m-%d %H:%i:%s'), time_format(now(), '%H:%i:%s')
FROM DUAL;

tips：计算日期和时间的函数，分别为给定的日期(date)加上(add)或减去(sub)一个时间间隔expr

-- DATE_ADD(date, INTERVAL expr unit)

-- DATE_SUB(date, INTERVAL expr unit)

• interval是间隔类型关键字
• expr是一个表达式，对应后面的类型
• unit是时间间隔的单位（间隔类型）

练习
1. 查询服务器当前时间。
2. 查询部门10,20的员工截止到2000年1月1日，工作了多少个月，入职的月份。
3. 如果员工的试用期6个月，查询职位不是manager的员工姓名，入职日期，转正日期。

4）转换函数

str_to_date();

范例： 

-- 将字符串转成时间格式

SELECT str_to_date('2023-12-31 12:23:12', '%Y-%m-%d %H:%i:%s')
FROM DUAL;

5）流程控制函数

 1. case函数结构：

CASE value
WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result] END

CASE WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result] END

范例：

 -- 流程控制函数

-- case when

-- 场景：emp表中 如果工资在5000以上显示A 3000以上显示B 1000以上显示C 1000以下显示D

SELECT ename, empno,
CASE
	WHEN sal >= 5000 THEN 'A'
	WHEN sal >= 3000 THEN 'B'
	WHEN sal >= 1000 THEN 'C'
	ELSE 'D'
END AS salLevel
FROM emp ;

2. if函数 

if(条件, true的时候使用, false的时候使用)

 范例1：

-- 场景：如果部门是20，显示本部门，其余的显示其他部门

SELECT empno, ename, IF (deptno = 20, '本部门', '其他部门') deptype
FROM emp ;

范例2：

-- if如果处理的是null

SELECT empno, ename, ifnull(comm, 0)
FROM emp ;

 3. ifnull | nullif函数

nullif(ex1, ex2)：if ex1 = ex2, return null; else return ex1;

范例：

 -- 场景：如果部门编号是20 返回null，否则返回 部门编号

SELECT empno, ename, NULLIF(deptno, 20)
FROM emp ;

6）其他函数

范例1：

-- 查看当前使用的数据库、查看当前的版本、查看当前的账户

SELECT DATABASE(), version(), USER()
FROM DUAL;

范例2：

-- 和加密相关的   [已经逐渐被弃用 MySQL8.0运行会报错]

SELECT password('123456'), MD5('123456')
FROM DUAL;

练习
1. 计算2000年1月1日到现在有多少月，多少周（四舍五入）。
2. 查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
3. 使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’分别处理得到下列字符串ello、Hello、ll、hello。
4. 将员工工资按如下格式显示：123,234.00 RMB 。
5. 查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
6. 将员工的参加工作日期按如下格式显示：月份/年份。
7. 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。
8. 创建⼀个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。

---

历经千辛万苦终于来到第二部分

可喜可贺

可歌可泣

 PS：要不点个关注？🤔🤔🤔😌😌😌🤥🤥🤥

---

 多表连接

为什么需要使用多表连接，一张表不能写明所有的数据么？

这个问题可以用三大范式来说明，那么又会有一个问题：什么是三大范式？

这是在   套娃？

三大范式

是目前关系数据库中最常使用的，即第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是所谓的三大范式。

第一范式

指的是，要求数据库表的每一列都是不可以分割的原子性数据。1NF是对属性的原子性，要求属性具有原子性，不可以再进行分解。

可以举个例子：

院校	姓名	年龄
001-南京大学	张三	18
002-南京航天航空大学	李四	20

在这个表中，明显的显示出“院校”这一列不满足原子性的要求，所以不满足第一范式，所以需要做出调整：

院校编号	院校	姓名	年龄
001	南京大学	张三	18
002	南京航空航天大学	李四	20

调整后，这个表格的每一列都是不可以再次分割的，所以满足第一范式（1NF）。

第二范式

在第一范式的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。（这句话仅供参考，博主也没有理解，有理解的小伙伴可以打在评论区里）

说白了第二范式，就是可以确保同一张表中的每列信息都是和主键相关的，而不是一部分相关。第二范式是针对于数据的唯一性。

 直接举例说明：

学号	姓名	课程名称	年龄	课程内容
001	张三	计算机	18	java
002	李四	音乐	20	音乐鉴赏

在表中，姓名-学号-年龄三者是相关联的，而课程名称-课程内容是相关联的，由于非主键字段必须依赖于主键，所以图表不符合第二范式，做出调整：

学生信息表：

学号	姓名	年龄
001	张三	18
002	李四	20

课程信息表：

课程名称	课程内容
计算机	java
音乐	音乐鉴赏

调整后，分成两张表，每张表的列之间存在直接依赖关系，满足第二范式。

第三范式

第三范式就是在第二范式的基础上，添加了直接关联和间接关联的属性，第三范式要求保证数据表中的每一列数据都是必须和主键直接关联，不能存在间接关联。第三范式是针对于数据的冗余性。

直接举例说明：

学号	姓名	年龄	学生宿舍	房间号
001	张三	18	A栋	202
002	李四	20	B栋	205

在表中，所有属性都可以依赖于“学号”，满足第二范式。但是“房间号”是直接依赖于“学生宿舍”的，所以对表进行调整：

学生信息表：

学号	姓名	年龄
001	张三	18
002	李四	20

学生宿舍表：

学生宿舍	房间号
A栋	202
B栋	204

调整后，每张表的属性之间都是直接关联的关系，不存在间接关联的关系，满足第三范式。

 如何进行多表连接？

多表连接 - 需要先找出表和表之间的关系

-- 在 WHERE子句中书写连接条件。

-- 如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。

-- N个表相连时，至少需要N－1个连接条件

语法格式：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

多表连接包含很多中语法，经常使用的有：

• 基础写法：绝大多数符合SQL标准，其他关系型数据也适用。
• ANNSI 99写法：ANSI标准提供的写法，所有关系型数据库必须都支持。

  SELECT ename, dname, loc
  FROM emp, dept;
  

 笛卡尔乘积

没有确定表间关系的时候，A表中的每一行数据都和b表中的每一行数据进行拼接，成为一条新的数据在结果中展现。

产生条件：

• 连接条件被省略
• 连接条件无效

范例：

-- 场景：查询员工姓名、部门名称、工作地点

-- tables? emp dept

-- columns? ename dname loc

SELECT ename, dname, loc
FROM emp, dept;

 在此下面还有36条数据，一共56条数据（合理的数据应该是14条）。

解决方式：

如何判断/获取/确定连接条件呢？ -- 使用where子句

-- where连接条件

-- 连接条件 -- 两张表中 表示相同含义/近似含义的字段

-- emp表中 deptno字段表示部门编号

-- dept表中 dept字段表示部门编号

-- emp中deptno字段和dept表中的deptno字段含义相同 才做连接

-- 当表中的字段名一致的时候，可以使用表名，字段名的方式来区分

SELECT ename, dname, loc  
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

也可以使用别名，替代完整的列名，看起来更加清晰：

SELECT e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

练习
1. 查询出工作地点在CHICAGO的员工中薪资大于1500的信息。
2. 查询出部门名称是sales的员工中入职时间在1981年的员工信息。
3. 查询出薪资最高的员工的部门名称。
4. 查询出纽约工作的员工中没有奖金的员工的信息。
5. 查询出emp表中入职时间最晚的三个员工的工作地点

---

多表连接的基本写法

1. 先确定数据报表 -- 看需要显示的的字段分别在哪些表中

2. 寻找以下数据表之间的关系 -- 确定where子句中的写法

3. 寻找筛选的条件

4. 其他查询要求 排序?limit?

5. 最终确定select使用哪些字段

 1）自身连接

自身连接，也叫自连接，是一个表通过某一个条件连接自己本身的方式。

 范例：

-- emp表中数据mgr字段表示的是经理的员工编号

-- 经理 也是员工 所以他的所有信息也在emp中

-- emp 既可以看成员工表，也可以看成mgr经理表

SELECT DISTINCT m.ename, m.empno, m.sal, m.job
FROM emp e, emp m
WHERE e.mgr = m.empno;

 注意：该方法存在的问题：当连接的字段值是null的时候，这个字段自动被忽略

 2）SQL - 99 中标准的的连接语法

交叉连接

笛卡尔乘积 交叉连接 cross join

当没有书写连接条件的时候 -- 相当于没有设定条件 相当于笛卡尔乘积

SELECT * 
FROM emp e
CROSS JOIN dept d;

交叉连接使用on作为连接条件的时候，就不会产生笛卡尔乘积

-- ON子句

-- 自然连接条件基本上是具有相同列名的表之间的等值连接；

-- 如果要指定任意连接条件,或指定要连接的列，则可以使用on子句；

-- 用on将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句；

-- on子句可以提高代码的可读性。

SELECT *
FROM emp e
CROSS JOIN dept d ON e.deptno = d.deptno;

自然连接

自动使用两张表名称/数据类型一致的字段作为连接条件

SELECT * 
FROM emp e
NATURAL JOIN dept d;

 指定列

using：

-- 1. 一般来说要求字段名称和类型一致

-- 2. using中不能使用别名或者是序号

-- 3. using中只能确定一个字段

-- 4. using和natural join 互斥

-- 5. on可以在任何一个join的语句中使用

 范例：

-- 当两张表中存在名称/类型一致的字段作为连接条件的时候，可以使用using来决定连接字段

SELECT * 
FROM emp JOIN dept USING (deptno);

 外部连接 -- 左外部连接和右外部连接

使用需要确定的显示其中一张表的全部数据，可以使用方向的join。

-- 在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。

-- 左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。

-- 右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件⽆论是否与左边表能匹配上，都会被显示出来。

范例： 

SELECT * 
FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;

SELECT * 
FROM emp e RIGHT JOIN emp m ON e.mgr = m.empno;

 内连接

说白了，就是表示自己连接自己。

SELECT * 
FROM emp e INNER JOIN emp m ON e.mgr = m.empno;

练习
1. 创建一个员工表和部门表的交叉连接。
2. 使用自然连接，显示入职日期在1980年05月01日之后的员工姓名，部门名称，入职日期。
3. 使用using子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点。
4. 使用on子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级。
5. 使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
6. 使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

---

今日的欢乐时光到此结束~

欢迎各位看官，学习收藏。

因为现在已经凌晨1点了，比较晚的缘故，后面第二部分有点赶进度，如果有出现不正确或者有争议的地方，欢迎私信留言或者评论区留言。博主看到就会给予第一时间的回应。