mysql--函数

合计/统计函数

使用的表材料

-- 演示mysql的统计函数

# 1.统计一个班级有多少个学生
SELECT COUNT(*) FROM student;

# 2.统计数学成绩>90的学生有多少个

SELECT COUNT(*) FROM student
WHERE math>90;

# 3.统计总分大于250的人数有多少

SELECT COUNT(*) FROM student
WHERE (chinese+math+english)>250;

count(*)和count(列)的区别

count(*)：返回满足条件的记录的行数
count(列)：统计满足条件的某列有多少个，但是会排除为null
表中的数据

-- count(*)：返回满足条件的记录的行数
-- count(列)：统计满足条件的某列有多少个，但是会排除为null
CREATE TABLE test2(
`name` VARCHAR(20));
INSERT INTO test2 VALUES('tom');
INSERT INTO test2 VALUES('bob');
INSERT INTO test2 VALUES('mary');
INSERT INTO test2 VALUES('jack');
INSERT INTO test2 VALUES(NULL);

# 1.使用count（*）
SELECT COUNT(*) FROM test2;-- 5
# 2.使用count('name')
SELECT COUNT(`name`) FROM test2;-- 4 会排除null

-- 其实记录的行数和列的行数都是一样的，因为查询列的行数回排除列值为Null的情况

统计函数Sum

表中的数据

-- 演示sum函数的使用
-- 1.统计一个班级数学总成绩
SELECT SUM(math) FROM student;

-- 2.统计一个班级语文 数学 英语各科的总成绩
SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
-- 3.统计一个班级语文 数学 英语 数学的成绩总和
SELECT SUM(math+chinese+english)AS total FROM student;
-- 4.统计一个班级语文成绩的平均分
SELECT SUM(chinese)/COUNT(*) FROM student;

注意：
该函数仅仅只对数值起作用，对非数值没有意义

合计函数 avg

表中的数据

-- 演示avg的使用
-- 1.求一个班数学成绩的平均分

SELECT AVG(math) FROM student;

-- 2.求一个班总分平均分

SELECT AVG (math+english+chinese)AS '总分平均分' FROM student;

合计和函数Max/Min

表中的数据

-- Max/Min的使用演示

-- 1.求班级总分的最高分和最低分(函数可以同时使用多个用,隔开)

SELECT MAX(math+english+chinese)AS'总分最高分',
MIN(math+english+chinese)AS'总分最低分' FROM student;

分组统计

测试数据
部门表
雇员表emp

工资级别表

对于分组查询不是很理解，在此标记

字符串相关的函数

黑色标识的用到不是很多

拼接结果显示

-- 演示字符串函数的使用

-- 1.CHARSET(str)返回字串字符集(括号里面为是字符串的字段名)

SELECT CHARSET(user_name) FROM employee;


-- 2.CONCAT（String2[,...]）连接字符串，将多个列拼接成一列


SELECT CONCAT(user_name,'工作是',job ) FROM employee;

-- 3.INSTR(String,substring)返回subString在string中的出现的位置，没有则返回0

-- dual亚元表，系统表，可以作为测试表使用

SELECT INSTR('天下第一','天') FROM DUAL;-- 输出1
-- 5 ucase(String2)转成大写

SELECT UCASE(user_name) FROM employee;
-- 6.Lcase(str)转成小写
SELECT LCASE(user_name) FROM employee;

-- 7.LEFT(String2,length)从String2的左边起取length个字符,然后显示
-- RIGHT(String,length) 从右边取length个字符出来

SELECT LEFT(user_name,2) FROM employee;

-- 8.LENGTH(String)统计string的长度怕[按照字节]

SELECT LENGTH(user_name) FROM employee;

-- 9.REPLACE(str,search_str,replace_str)(将str中的search_str替换成replace_str)
SELECT REPLACE(user_name,'石','王') FROM employee;
SELECT REPLACE (user_name,"石文涛","天才") FROM employee;-- 为石文涛则替换成天才
-- 10. STRCMP（String1,string2）逐个字符比较两字符的大小(相同返回0不相同返回-1)
SELECT STRCMP('hello','helo') FROM employee;
-- 11.SUBSTRING（str,position[,length]）
-- 从str的position开始【从1开始算】，取length个字符 并返回
SELECT SUBSTRING("hello",1,2) FROM DUAL;

-- 12.LTRIM（String）去除左边的空格
-- RTRIM（String）去除右边的空格
-- TRIM去除前端和后端的空格
SELECT LTRIM("      hlello") FROM DUAL;
SELECT RTRIM( "    天才   " ) FROM DUAL;
SELECT TRIM("    大聪明    ") FROM DUAL;

SELECT * FROM employee;

字符串函数练习

实验数据表

注意这里的下面都是从1开始的，而不是从0开始

-- 注意：substring第三个参数不写则是默认取出从指定下标开始的所有
SELECT CONCAT (UCASE( SUBSTRING(user_name,1,1)),
SUBSTRING(user_name,2,LENGTH(user_name)-1))  FROM employee;

运行结果

该题还有其他的实现思路如repalce 等

数学函数

-- 演示数学相关的函数
-- 1.ABS（num）绝对值
SELECT ABS(-10) FROM DUAL;-- 将会显示10
-- 2.BIN(decimal_number)-- 十进制转二进制
SELECT BIN(10) FROM DUAL;
-- 3.ceiling(number)向上取整，得到比number大的最小整数(负数也一样)

SELECT CEILING(2.2) FROM DUAL;
-- 4.conv(number ,from_base,to_base)进制转换
-- 下面含义：是十进制的8，转成二进制输出
SELECT CONV(8,10,2) FROM DUAL;

-- 5.floor(number)向下取整，得到比number小的最大整数
SELECT FLOOR(1.1) FROM DUAL;
-- 6.format(number,decimal_places)保留小数位(会进行四舍五入保留)
SELECT FORMAT(1.23556,2) FROM DUAL;

-- 7.hex(decimalNumber)转十六进制

-- 8.least(number,number1[,...])求多个里面的最小值
SELECT LEAST(0,9,-1,-98) FROM DUAL;-- -98
-- 9 .mod(10,3) 取余
SELECT MOD(10,3) FROM DUAL;

-- 10.rand([seed]) 返回随机数 其范围为 0=<v<=1.0


SELECT RAND() FROM DUAL;-- 每次运行将得到(0.1.0)的随机数
-- 将rand()加上随机数种子 seed，则将每次获取固定的随机数

SELECT RAND(3) FROM DUAL; -- 种子的值什么都行，但是种子换了返回的值就会变，否则不变

日期函数1

基本使用


-- 演示日期相关的函数 

-- 1.current_date（） 当前日期
SELECT CURRENT_DATE() FROM DUAL;-- 2023-04-05
-- 2.current_time（）当前时间
SELECT CURRENT_TIME() FROM DUAL;-- 15:14:45
-- 3.current_timestamp()当前实践戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;-- 2023-04-05 15:15:38

进阶使用---使用函数填充插入的值

-- 创建信息表
 CREATE TABLE mes(
 id INT,
 content  VARCHAR(30),
 sent_time  DATETIME);-- 消息的发送时间
-- 使用函数填充
 INSERT INTO mes VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
 SELECT * FROM mes;

综合应用

-- 演示日期相关的函数 

-- 1.current_date（） 当前日期
SELECT CURRENT_DATE() FROM DUAL;-- 2023-04-05
-- 2.current_time（）当前时间
SELECT CURRENT_TIME() FROM DUAL;-- 15:14:45
-- 3.current_timestamp()当前实践戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;-- 2023-04-05 15:15:38

-- 1.now()返回当前的日期+时间
SELECT NOW() FROM DUAL;-- 2023-04-05 15:32:49

-- 创建信息表
 CREATE TABLE mes(
 id INT,
 content  VARCHAR(30),
 sent_time  DATETIME);-- 消息的发送时间
-- 使用函数填充
 INSERT INTO mes VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
 INSERT INTO mes VALUES(2,'上海新闻',NOW());
 INSERT INTO mes VALUES(3,'广州新闻',NOW());
  INSERT INTO mes VALUES(4,'天津新闻',CURRENT_DATE());
  -- 1.显示所有的新闻信息，发布日期只显示日期，不显示时间
SELECT id,content,DATE(sent_time) FROM mes;
-- 2.请查询十分钟内发布的新闻
-- 在发布时间上+10和现在时间做比较即可
 SELECT * FROM mes
WHERE DATE_ADD(sent_time,INTERVAL 10 MINUTE)>=NOW(); 
-- 也可以使用date_sub来实现
SELECT * FROM mes
WHERE    sen_time >=DATE_SUB(NOW(),INTERVAL 10 MINUTE)
 -- 3.请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天
 
  SELECT DATEDIFF('2011-11-11','1990-1-1')FROM DUAL; 
 
 -- 4.使用sql语句求出你活了多少天
 SELECT DATEDIFF(NOW(),'2001-4-26') FROM DUAL;//8014
 
 -- 5.如果你会活到80岁，求出你还能活多少天
 -- 先求出活到80岁的时的日期，再和现在的日期相减
 SELECT DATEDIFF(DATE_ADD('2001-4-26',INTERVAL 80 YEAR),NOW());

日期相关函数的使用细节

日期函数3

-- 1.year|month|day|date(datetime)
SELECT NOW() FROM DUAL; 
SELECT YEAR('2022-11-23') FROM DUAL;
SELECT YEAR(NOW()) FROM DUAL;-- 只会返回当前的年

 SELECT MONTH(NOW()) FROM DUAL;
 SELECT DAY(NOW()) FROM DUAL;
 -- 2.unix_timestamp()返回1970-1-1到现在的秒数
SELECT  UNIX_TIMESTAMP() FROM DUAL; -- 1680685199
-- 3.from_unixtime:可以把一个unix_timestamp秒数，转换成指定格式的日期

# %Y %m %d %i %H等都是已经规定好的
-- 该函数的意义：再开发中，可以存放一个整数表示时间，通过
-- from_unixtime进行转换
SELECT FROM_UNIXTIME(12343355,'%Y-%m-%d') FROM DUAL;-- 1970-05-24

SELECT FROM_UNIXTIME(12343355,'%Y/%m/%d %H:%i:%s')FROM DUAL;-- Y-05-24 04:42:35

加密函数和系统函数

-- 演示加密函数和系统函数
-- 1.user()查询用户(可以查询登录到mysql有那些用户和所登录的ip)

SELECT USER() FROM DUAL;-- 返回登录mysql的用户名和ip地址(root@localhost)
-- 2.database()查询当前使用数据库的名称

SELECT DATABASE() FROM DUAL;
-- 3.md5()为字符串算出一个md5 32位的字符串，常用于（用户密码）加密
SELECT MD5('hsp') FROM DUAL;--  数据库中真正存储的-》c435a9810009800cafef7dce3b7844a6

# root密码是hsp->加密md5-->数据库中存储的是加密后的32位的md5
-- 演示用户表存放密码
 CREATE TABLE hsp_user1(
 id INT ,
 `name` VARCHAR(30) NOT NULL DEFAULT '',
 `password` CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user1 VALUES(1,"石文涛",MD5(123456));-- 将加密后的密码存入到数据库中

SELECT * FROM hsp_user1;-- 查询的密码：e10adc3949ba59abbe56e057f20f883e

-- select * from hsp_user1 where `name` = '石文涛' and `password` = '123456';-- 这样将查询不出来
-- 这样可以查询出来 select * from hsp_user1 where `name` = '石文涛' and `password`= md5(123456);

# 3.password(str)加密函数,mysql数据库的用户密码就是password函数加密的
SELECT * FROM mysql.user;-- 使用这个可以直接查询mysql的用户信息
SELECT PASSWORD('123456') FROM DUAL;-- $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED

流程控制函数、

-- 演示流程控制语句

#1.if(expr1,expr2,expr3)如果expr1为true,则返回expr2否则返回expr3
SELECT IF(TRUE,'北京','上海')FROM DUAL;-- 北京
#2.ifnull(expr1,expr2)如果expr1不为空返回expr1,否则返回expr2
SELECT IFNULL(NULL,0.0);-- 0.0
#3.case when expr1 then expr2 when expr3 then expr4 else end(类似多重分支)
-- 如果expr1为真,则返回expr2,如果expr3为真返回 expr4，否则返回expr5
 SELECT CASE
 WHEN TRUE THEN 'jack'-- 输入jack（和swich类似）
 WHEN TRUE THEN 'tom'
 ELSE 'marry' END 
 
-- 1.查询emp表，如果comm是null,则显示0.0
-- 说明：判断是否为Null，要使用is null,判断不为空,使用 is not null
SELECT ename,IF(comm IS NULL ,0.0,comm)-- 写法一
FROM emp;


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 '销售人员' )AS 'job'
           FROM emp;

 -- 括号里面相当于对该该表的所有记录的job进行判断

posted @ 2023-04-06 15:16 一往而深，阅读(38) 评论(0) 编辑收藏举报

刷新页面返回顶部