MySQL数据库（4）

已于 2023-05-02 16:39:32 修改
阅读量673 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql sql
于 2023-04-30 02:15:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_53616401/article/details/130414287
版权

  

目录

日期函数

细节

加密系统函数

流程控制函数

单表查询加强

where子句加强  orderby子句加强

分页查询

groupby子句加强

数据分组总结

日期函数

在我们的数据库中，难免要存放日期，比如我们要存储，开始日期和结束日期，在设计数据库的时候，一遍都会有这么两个字段，一个是开始时间，还有一个是修改时间，还有比如我们在收集一个人的信息的时候，在收集到出生年月的时候，也会使用到日期类型

细节

DATE ADD()中的 interval 后面可以是 year minute second day 等

DATE SUB()中的 interval 后面可以是 year minute second hour day等

DATEDIFF(date1,date2) 得到的是天数，而且是date1-date2 的天数，因此可以取负数

这四个函数的日期类型可以是 date,datetime 或者 timestamp

代码演示：

-- 日期时间相关函数

-- CURRENT_DATE (  )	当前日期
#DUAL表示亚元表 是系统表
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME (  )	当前时间
SELECT CURRENT_TIME()  FROM DUAL;
-- CURRENT_TIMESTAMP (  ) 当前时间戳
SELECT CURRENT_TIMESTAMP()  FROM DUAL;

-- 创建测试表 信息表
CREATE TABLE mes(
	id INT , 
	content VARCHAR(30), 
	send_time DATETIME);
	
	
-- 添加一条记录
INSERT INTO mes 
	VALUES(1, '北京新闻', CURRENT_TIMESTAMP()); 
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());

SELECT * FROM mes;
SELECT NOW() FROM DUAL;

-- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id, content, DATE(send_time) 
	FROM mes;
-- 请查询在10分钟内发布的新闻, 思路一定要梳理一下.
#表示从发送的时间(send_time)基础上 加了10分钟 如果大于当前时间 就表示在10分钟内

SELECT * 
	FROM mes
	WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()

#这句话表示 当前的时间（NOW()）	减去10分钟之后 如果小于发送的时间（send_time）也可以表示在10分钟内发送
SELECT *FROM mes WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE)<=send_time

-- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL
-- 请用mysql 的sql语句求出你活了多少天? [练习] 2003-03-03 出生
SELECT DATEDIFF(NOW(), '2003-03-03')/365 FROM DUAL;
-- 如果你能活80岁，求出你还能活多少天.[练习] 2003-03-03 出生
-- 先求出活80岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 2003-03-03->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '2003-03-03' 可以date,datetime timestamp 
SELECT DATEDIFF(DATE_ADD('2003-03-03', INTERVAL 80 YEAR),NOW()) FROM DUAL



-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
--   
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;



SELECT * FROM mysql.user \G

加密系统函数

我们知道，在记录用户的登录信息和密码的时候，比如我们注册的账号密码，都是存放在一个数据库中的，但是我们真的就把数据进来的密码，直接存放在数据库中吗，和明显这不显示，如果直接存放在数据库中，万一数据库被入侵，导致数据泄露，所以我们一般存放在数据库中的 密码都是经过加密的，常见的加密格式有md5 password加密

代码演示：

-- 演示加密函数和系统函数

-- USER()	查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
-- DATABASE()	查询当前使用数据库名称
SELECT DATABASE();

-- MD5(str)	为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('yjw') FROM DUAL;
SELECT LENGTH(MD5('yjw')) FROM DUAL;

-- 演示用户表，存放密码时，是md5
CREATE TABLE hsp_user
	(id INT , 
	`name` VARCHAR(32) NOT NULL DEFAULT '', 
	pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user 
	VALUES(100, 'jack', MD5('yjw'));
SELECT * FROM hsp_user; -- csdn

SELECT * FROM hsp_user  -- SQL注入问题
	WHERE `name`='jack' AND pwd = MD5('yjw')  


-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密

SELECT PASSWORD('yjw') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC


-- select * from mysql.user \G 	从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表 
SELECT * FROM mysql.user

流程控制函数

所谓流程控制函数，也就是和Java中的if语句差不多

如果是if()后面有三个表达式

 IF(expr1,expr2,expr3)    如果expr1为True ,则返回 expr2 否则返回 expr3

这就有点类似于Java中的三元运算符了，只不过语法形式不同


# IFNULL(expr1,expr2)    如果expr1不为空NULL,则返回expr1,否则返回expr2

ifnull函数是判断是否为空，如果为空返回第二个表达式的值，如果不为空就返回第一个表达式的值

代码演示：

# 演示流程控制语句

# IF(expr1,expr2,expr3)	如果expr1为True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2)	如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL( NULL, '明天你好') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果expr1 为TRUE,则返回expr2,如果expr2 为t, 返回 expr4, 否则返回 expr5

SELECT CASE 
	WHEN TRUE THEN 'jack'  -- jack
	WHEN FALSE THEN 'tom' 
	ELSE 'mary' END

-- 1. 查询emp 表, 如果 comm 是null , 则显示0.0
--    老师说明，判断是否为null 要使用 is null, 判断不为空 使用 is not
#表示 comm 如果为空 那么返回 第一个表达式的值 否则返回第二个表达式的值
SELECT ename, IF(comm IS NULL , 0.0, comm)
	FROM emp;
#表示 comm如果为空就继续显示原来的值 如果为空 就显示0.0	
SELECT ename, IFNULL(comm, 0.0)
	FROM emp;
-- 2. 如果emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
--     如果是 SALESMAN 则显示 销售人员，其它正常显示

SELECT ename, (SELECT CASE 
		WHEN job = 'CLERK' THEN '职员' 
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员' 
		ELSE job END) AS 'job'
	FROM emp; 

SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

单表查询加强

where子句加强  orderby子句加强

代码演示：

-- 查询加强
-- ■ 使用where子句
-- 	?如何查找1992.1.1后入职的员工
-- 老师说明： 在mysql中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
	WHERE hiredate > '1992-01-01'
-- ■ 如何使用like操作符(模糊)
-- 	%: 表示0到多个任意字符 _: 表示单个任意字符
-- 	?如何显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp
	WHERE ename LIKE 'S%'
-- 	?如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM emp
	WHERE ename LIKE '__O%'

-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp
	WHERE mgr IS NULL;
-- ■ 查询表结构 
DESC emp 

-- 使用order by子句
--   ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
	ORDER BY sal 
--   ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息

SELECT * FROM emp
	ORDER BY deptno ASC , sal DESC;

分页查询

代码演示：

-- 分页查询
-- 按雇员的id号升序取出， 每页显示3条记录，请分别显示 第1页，第2页，第3页

-- 第1页
SELECT * FROM emp 
	ORDER BY empno 
	LIMIT 0, 3;
-- 第2页
SELECT * FROM emp 
	ORDER BY empno 
	LIMIT 3, 3;
-- 第3页
SELECT * FROM emp 
	ORDER BY empno 
	LIMIT 6, 3;
-- 推导一个公式 
SELECT * FROM emp
	ORDER BY empno 
	LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
	
	
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY  job;
-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp  WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;

groupby子句加强

代码演示：

-- 增强group by 的使用

-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job 
	FROM emp 
	GROUP BY job; 
-- (2) 显示雇员总数，以及获得补助的雇员数。
--  思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)，如果该列的值为null, 是
--  不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)
	FROM emp 

--  扩展要求：统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
	FROM emp 

SELECT COUNT(*), COUNT(*) - COUNT(comm)
	FROM emp 

-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr) 
	FROM emp; 

-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) 
	FROM emp;

SELECT * FROM e
mp;
SELECT * FROM dept;



-- 应用案例：请统计各个部门group by 的平均工资 avg，
-- 并且是大于1000的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2

SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno   #先按部门进行分组
	HAVING  avg_sal > 1000#在使用having过滤出 平均工资大于1000的
	ORDER BY avg_sal DESC
	LIMIT 0,2

数据分组总结

代码演示：

-- 增强group by 的使用

-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job 
	FROM emp 
	GROUP BY job; 
-- (2) 显示雇员总数，以及获得补助的雇员数。
--  思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)，如果该列的值为null, 是
--  不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)
	FROM emp 

-- 扩展
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
	FROM emp 

SELECT COUNT(*), COUNT(*) - COUNT(comm)
	FROM emp 

-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr) 
	FROM emp; 

-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) 
	FROM emp;

SELECT * FROM e
mp;
SELECT * FROM dept;



-- 应用案例：请统计各个部门group by 的平均工资 avg，
-- 并且是大于1000的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2

SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno   #先按部门进行分组
	HAVING  avg_sal > 1000#在使用having过滤出 平均工资大于1000的
	ORDER BY avg_sal DESC
	LIMIT 0,2

_雨_
关注
专栏目录
MySQL数据库入门看这一篇就够（超级详细，从零学起）
啊渊的专栏
06-23 4774
学习MySQL数据库是一个循序渐进的过程，从基本的概念到高级的操作和优化都有很多知识点需要掌握。下面是一个详细的学习指南，涵盖了从基础到高级的内容。
Mysql数据库基础语句
m0_54594153的博客
09-11 3390
DDL语句可用于创建数据库对象（库，表，索引）删除数据库和表2.1 创建数据库和表—create#创建一个名为nba的数据库#创建一个名为james的表,（定义id不为空，名字不为空，年龄不为空，成绩可为空默认为“0”，主键为id）# 在任意库中创建nba库中的james表sql语言的分类DDL数据定义语言，用于创建和删除数据库表操作create（创建） drop（删除）DML：数据操作语言，用于管理表中的记录，对数据进行增，删，该，的操作。
MySQL中的DATEDIFF()函数
jialuosi的博客
09-10 2万+
注意：在MySQL中，DATEDIFF有且仅有两个参数，那就是结束时间和开始时间，不存在第一个参数为day，year这种的，那不是MySQL中的DATEDIFF函数！可以看到结果为使用了向上取整函数后返回结果为3 ，那么具体是向上还是向下还是四舍五入，那就要看你具体的业务了，根据你具体的业务进行调整。是一个日期函数，用于计算两个日期之间的差距，并以指定的时间单位返回结果。：用于将数值向下取整，去掉小数部分，保留整数部分。：用于将数值向上取整，进位到最接近的整数。之间的天数差异，可以为负数。
datedif函数mysql_DateDiff函数
weixin_29650949的博客
02-23 2877
datediff函数的使用方法是如何的？datediff函数用法。请举例说明下datediff的用法，或是有什么好的教程推荐datediff函数语法用法： 表达式DateDiff(timeinterval,date1,date2 [, firstdayofweek [, firstweekofyear]]) 允许数据类型: timeinterval 表示相隔时间的类型，代码为：年份 yy、yyyy...
Mysql DATEDIFF() 函数
qq_46021503的博客
11-19 342
DATEDIFF() 函数使用： 当我们想用日期做减法运算的时候可以使用这个函数： DATEDIFF(date1，date2) 就是date1-date2 select DATEDIFF(NOW()+interval 8 hour,NOW()) NOW() 函数 获取当前数据库系统时间， NOW()+interval 8 hour 数据库当前时间 + 8小时 ...
MySQL DATEDIFF() 函数
许进进的博客
04-11 1万+
定义和用法 DATEDIFF() 函数返回两个日期之间的天数。 语法 DATEDIFF(date1,date2) date1 和 date2 参数是合法的日期或日期/时间表达式。 #select date_time from wenzhang_info where _id = 6559 -- select now() -- select datediff(
MySQL中DATEDIFF函数的使用-获取两个日期的时间间隔
LeungChunwai的博客
02-18 3386
DATEDIFF(date1，date2) MySQL中 DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。 DATEDIFF() 函数返回 date1-date2 后的值，因此 DATEDIFF('2020-11-30'，'2020-11-29') 的返回值为 1，DATEDIFF('2020-11-30 23：59：59'，'2020...
Java连接MySQL数据库的超级详细步骤（Windows）
热门推荐
firstlt0217的博客
05-19 11万+
1. 数据准备 1. 下载JDK 下载Java开发工具包JDK，下载地址：https://www.oracle.com/java/technologies/javase-jdk14-downloads.html，下载待用。 2. 下载Mysql Java 连接 MySQL 需要驱动包，下载地址为：http://dev.mysql.com/downloads/connector/j/。 解压后得到 jar 库文件，待用，最终会将红框文件导入相应项目文件中。 3. 下载MySQL 下载地址：https:/
MySQL数据库基本操作
Drw_Dcm的博客
09-09 5万+
MySQL数据库基本操作
MySQL数据库安全
m0_65159351的博客
12-27 2054
无论是对于企业还是个人，数据库中所保存数据的安全性是尤其重要的。尤其是对于公司企业等来讲，其商业数据往往带有着巨大的价值。本文对MySQL数据库安全做了一些介绍。以上就是今天要讲的内容，本文仅仅简单介绍了MySQL数据库安全的一些基础知识。
mysql计算日期时间的差值，DATEDIFF() 和 TIMESTAMPDIFF()函数的用法
最新发布
weixin_51704238的博客
03-21 2616
当涉及到计算日期或时间之间的差值时，MySQL 提供了两个很好用的函数：DATEDIFF() 和 TIMESTAMPDIFF()，下面简要讲讲用法。在mysql中，DATEDIFF() 和 TIMESTAMPDIFF()都能用于求两个日期之间的差值。DATEDIFF() 函数仅适用于计算日期之间的差值，而 TIMESTAMPDIFF() 函数除了求日期之间的差值，还能求时间之间的差值，且能够指定时间单位。
Java sql 计算时间差
weixin_44840242的博客
03-04 820
DATEDIFF(now(),‘目标日期’) 函数 例如： SELECT db.bo_id, db.bo_code, db.bop_name, db.bop_year, db.bop_belong_delta_division, db.create_time, DATEDIFF(now(),db.create_time) as day FROM dps_bo db ORDER BY day Asc ...
MySql7——当前时间 now() 、TIMESTAMPDIFF、DATEDIFF
m0_38057941的博客
10-11 1356
0. now() 当前时间 select now(); 1.datediff函数 返回值是相差的天数，不能定位到小时、分钟和秒。 语法： select datediff('大的时间','小的时间') 1.1. 相差6天 select datediff('2020-10-7 00:00:00', '2020-10-1 00:00:00'); select datediff('2020-10-7 00:00:00', '2020-10-1 23:59:59'
Impala计算日期差datediff
王世洪的博客
11-10 7167
在数据库查询上，除了获取当前日期、进行日期加减外，常见的还有计算两个日期之后的日期差，为一些指标的计算提供时间基础；Impala中常用的日期差函数 datediff，就是用来计算两个日期之间的间隔；类似日期差的计算中，还有其他计算时间差的函数，如计算月份差（int_months_between）、浮点数月份差（months_between）、日期大小比较（date_cmp、timestamp_cmp）
Mysql 日期函数详解（now、date_add、datediff ）
鱼丸丶粗面
01-19 4717
文章目录1 概述2 示例2.1 获取日期时间各部分2.2 日期时间相加减3 扩展3.1 now() 和 sysdate() 区别3.2 格式转换3.3 世界协调时间 UTC 1 概述 #mermaid-svg-Sq7iR5kHgyrk0Ymx {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-Sq7iR5kHgyrk0Ymx .error-icon{fill:#552222;
mysql DATEDIFF使用填坑
TryCatch
12-29 7147
今天在使用mysql DATEDIFF函数的时候出现了一个取值的错误，究其原因是因为它的值有可能是负数。SELECT DATEDIFF('216-12-29','216-12-29') AS DiffDate结果为0SELECT DATEDIFF('216-12-28','216-12-29') AS DiffDate 结果为-1 SELECT DATEDIFF('216-12-30','216-1
mysql中DATADIFF()函数使用
AinUser的博客
06-26 7186
[html] view plain copy 用过mssql的朋友应该了解datediff函数了，在mssql中，该函数的第一个参数是开始日期，第二个是结束日期，开始日期应该在结束日期之前，这样算出来的结果是大于或等于0的一个数，反之是一个负数，但在MySQL中就完全相反，
mysql奇葩的datediff函数
架构设计
01-23 381
用过mssql的朋友应该了解datediff函数了，在mssql中，该函数的第一个参数是开始日期，第二个是结束日期，开始日期应该在结束日期之前，这样算出来的结果是大于或等于0的一个数，反之是一个负数，但在mysql中就完全相反，你若想得到一个正数结果，那第一个日期应该填结束日期，第二个参数应该填开始日期，很奇葩的设计。 ...
MySql 时间比较：DATEDIFF函数与大于小于号
勿忘初心
11-22 1万+
SELECT COUNT(*) FROM tableA WHERE RECORDDATE>='2015-11-11' AND RECORDDATE<='2016-11-11'; SELECT COUNT(*) FROM tableA WHERE DATEDIFF(RECORDDATE,'2015-11-11')>=0 AND DATEDIFF(RECORDDATE,'2016-11-11')<=0;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值