5.MySQL函数

5、MySQL函数

mysql函数和运算符参考

5.1、常用函数

-- ==========常用函数===============
-- 数学运算
select abs(-8) -- 绝对值
select ceiling(9.4)  -- 向上取整
select floor(9.8) -- 向下取整
select rand() -- 返回一个0~1的随机数
select sign(-10) -- 判断一个数的符号  负数返回-1 正数返回1

-- 字符串函数
select char_length('我是你大爷，你服不服')  -- 字符串长度
select concat('我','爱','你')  -- 拼接字符串
select insert('我爱编程helloword',2,3,'超级热爱')  -- 查询，替换 从某个位置开始替换某个长度
select lower('ASDSAA')  -- 小写字母
select upper('sfddscdsc')  -- 大写字母
select instr('WeiGuangHua','G') -- 返回第一次出现的子串的索引
select replace('学海无涯苦作舟','学海','人生')  -- 替换指定的字符串
select substr('学海无涯苦作舟',4,2) -- 返回指定的子字符串（源字符串，开始的位置，要截取的字符串的长度）
select reverse('学海无涯苦作舟') -- 反转

-- 查询姓赵的同学，名字 魏
select replace(`studentname`,'赵','魏') from student
where studentname like '赵%'


-- 时间和日期函数（记住）
select current_date()  -- 获取当前日期

select curdate() -- 获取当前日期

select now()  -- 获取当前时间
select localtime() -- 获取本地时间
select sysdate()  -- 获取系统时间
select year(now())
SELECT month(NOW())
SELECT day(NOW())
SELECT hour(NOW())
SELECT minute(NOW())
SELECT second(NOW())

-- 系统
select system_user()
select user()
select version()

5.2、聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

4.7 分组和过滤

-- ==================聚合函数=========
-- 都能够统计 表中的数据(想查询一个表中有多少个记录，就使用count())
SELECT COUNT(`studentname`) FROM `student` -- count(字段)，会忽略所有的Null值
SELECT COUNT(*) FROM `student`  -- 不会忽略所有的Null值，本质 计算行数 (走所有的列)
SELECT COUNT(1) FROM `student`  -- 不会忽略所有的Null值，本质  计算行数 （只走一列）

SELECT SUM(`studentresult`) AS '总和' FROM `result`

SELECT AVG(`studentresult`) AS '平均值' FROM `result`
SELECT MAX(`studentresult`) AS '最大值' FROM `result`
SELECT MIN(`studentresult`) AS '最小值' FROM `result`


-- 查询不同课程的平均分，最高分，最低分， 平均分大于70分的
-- 核心 根据不同的课程分组
SELECT `subjectname`,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno`  -- 通过什么字段来分组
HAVING AVG(`studentresult`)>=70

SELECT `subjectname`,AVG(studentresult) AS 平均分 ,MAX(studentresult),MIN(studentresult)
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno`  -- 通过什么字段来分组
HAVING 平均分>=70

5.3、数据库级别的MD5加密

什么是MD5：主要增强算法复杂度和不可逆性
MD5不可逆，具体的值的MD5是一样的
MD5破解网站的原理,背后有一个字典，MD5加密后的值，加密前的值

-- =================== 测试 MD5 加密 ===========
DROP TABLE testmd5
CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name`VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8
INSERT INTO `testmd5` VALUES (1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456')

-- 加密
UPDATE testmd5 SET pwd =MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd =MD5(pwd) -- 加密全部的密码

-- 插入时加密
INSERT INTO testmd5 VALUES (4,'魏光华',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密的值
SELECT * FROM testmd5 WHERE `name`='魏光华' AND pwd = MD5('123456')

4.8 select 小结