day04-MySQL常用函数

5.MySQL常用函数

5.1合计/统计函数

5.1.1合计函数-count

count 返回行的总数

Select count(*)|count (列名) from table_name 
	[WHERE where_definition]

练习

-- 统计一个班级共有几个学生
SELECT COUNT(*) FROM student
 
-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
	WHERE math > 90
	
-- 统计总分大于250的人数有多少个
SELECT COUNT(*) FROM student
	WHERE chinese+english+math> 250

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

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

5.1.2合计函数-sum

sum函数返回满足where条件的行的和，一般使用在数值列

Select sum(列名) {,sum(列名)...} from tablename
	[WHERE where_definition]

练习

-- sum函数
-- 统计一个班的数学总成绩
SELECT SUM(math) FROM student
 
-- 统计一个班语文、英语、数学各科的总成绩
SELECT SUM(math),SUM(english),SUM(chinese) FROM student
 
-- 统计一个班语文、英语、数学的成绩总和
SELECT SUM(math+english+chinese) FROM student
 
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student

注意：

sum仅对数值起作用，否则没有意义

对多列求和，“ , ” 不能少

5.1.3合计函数-avg

avg函数返回满足where条件的一列的平均值

Select avg (列名) {,avg(列名)...} from tablename
	[WHERE where_definition]

练习

-- avg 函数
-- 求一个班级数学平均分
SELECT AVG(math) FROM student
-- 求一个班级总分平均分
SELECT AVG(chinese+english+math) FROM student

5.1.4合计函数-Max/min

Max/min函数返回满足where条件的一列的最大/最小值

Select max (列名) {,avg(列名)...} from tablename
	[WHERE where_definition]

练习

-- max和min 函数
-- 求班级最高分和最低分
SELECT MAX(chinese+english+math),MIN(chinese+english+math)FROM student
 
-- 求班级数学最高分和最低分
SELECT MAX(math),MIN(math)FROM student

5.1.5分组统计-group by

• 使用group by子句对列进行分组

SELECT column1,column2,column3... FROM table
	group by column

• 使用having子句对分组后的结果进行过滤

SELECT column1,column2,column3... 
	FROM table
	group by column having ...

练习：

-- 先创建测试表
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
 
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), 
(20, 'RESEARCH', 'DALLAS'), 
(30, 'SALES', 'CHICAGO'), 
(40, 'OPERATIONS', 'BOSTON');
 
SELECT * FROM dept;
 
-- 员工表
 
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利 奖金*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
 
 
-- 添加测试数据
 INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
 
SELECT * FROM emp;
 
-- 工资级别
#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/ 
losal DECIMAL(17,2)  NOT NULL, /* 该级别的最低工资 */
hisal DECIMAL(17,2)  NOT NULL /* 该级别的最高工资*/
);
 
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
 
SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;
 
# 演示group by + having
# GROUP by用于对查询的结果分组统计, having子句用于限制分组显示结果.
-- 如何显示每个部门的平均工资和最高工资
-- 按照部门来分组查询
SELECT AVG(sal),MAX(sal) ,deptno
	FROM emp 
	GROUP BY deptno;
 
-- 显示每个部门的每种岗位的平均工资和最低工资
# 1.先显示每个部门的平均工资和最低工资
# 2.再显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal) ,deptno,job
	FROM emp 
	GROUP BY deptno,job; -- 先按照部门分组，再按照岗位分组
 
 
-- 显示平均工资低于2000的部门号和它的平均工资
# 1.先显示各个部门的平均工资和部门号
# 2.再显示平均工资低于2000的（having过滤）
SELECT AVG(sal),deptno
	FROM emp 
	GROUP BY deptno
	HAVING AVG(sal)<2000
	
-- 也可以使用别名进行过滤，如下：
SELECT AVG(sal) AS avg_sal,deptno
	FROM emp 
	GROUP BY deptno
	HAVING avg_sal<2000

5.2字符串函数

字符串相关函数

函数	解释
charset(str)	返回字串字符集
concat(string2[,...])	连接字串
instr（string，substring）	返回substring在string中出现的位置，没有就返回0
ucase（string2）	转换成大写
lcase（string2）	转换成小写
left（string2，length）	从string2的左边起，取length个字符
right（string2，length）	从string2的右边起，取length个字符
length（string）	string的长度[按照字节长度返回]
replace（str，search_str，repace_str）	在str中用replace_str替换seacher_str
strcmp(string1,string 2)	逐字符比较两字符串的大小
substring（str，position[,length]）	从str的position开始[从1开始计算]，取length个字符
ltrim（string2）	去除前端空格
rtrim（string2）	去除后端空格
trim(stirng2)	去除前后的空格

练习

-- -- 演示字符串相关的操作
-- -- 使用前面创建的emp表来演示
-- charset(str)  返回字串字符集 
SELECT CHARSET(ename) FROM emp;
 
-- concat(string2[,...])  连接字串,将多个列拼接成一列
SELECT CONCAT(ename,'工作是',job) FROM emp;
 
-- instr（string，substring）返回substring在string中出现的位置，没有就返回0
SELECT INSTR('hanshunping','ping') FROM DUAL; -- 8
-- dual 亚元表，是系统表，可以作为测试表使用
 
-- ucase（string2）  转换成大写  
SELECT UCASE(ename) FROM emp; -- 将指定列的所有信息变为大写
 
-- lcase（string2）  转换成小写   
SELECT LCASE(ename) FROM emp;-- -- 将指定列的所有信息变为小写
 
-- left（string2，length）  从string2的左边起，取length个字符 
SELECT LEFT(ename,2) FROM emp;
 
-- right（string2，length）  从string2的右边起，取length个字符 
SELECT RIGHT(ename,2) FROM emp;
 
-- length（string）    string的长度[按照字节]
SELECT LENGTH(ename) FROM emp;
 
-- replace（str，search_str，repace_str） 在str中用replace_str替换seacher_str  
-- 将表中的job职位的MANEGER替换成‘经理’
SELECT REPLACE(job,'MANAGER','经理') FROM emp;
            
-- strcmp(string1,string 2)   逐字符比较两字符串的大小  
SELECT STRCMP('hsp','asp') FROM DUAL;
 
-- substring（str，position[,length]） 从str的position开始[从1开始计算]，取length个字符
-- 从ename列的第一个位置开始取出两个字符
SELECT SUBSTRING(ename,1,2) FROM emp;
 
-- ltrim（string2）rtrim（string2） 去除前端或者后端空格   
SELECT LTRIM('  123nihao') FROM DUAL;
SELECT RTRIM('123nihao    ') FROM DUAL;  
                  
-- trim(stirng2) 去除前后的空格
SELECT TRIM('  123nihao   ') FROM DUAL;
 
-- 以首字母小写的方式显示所有员工emp表的姓名
-- 截取第一个字符将其小写,然后拼接剩下的字符串输出即可
SELECT CONCAT( LCASE(LEFT(ename,1)) , SUBSTRING(ename,2)) FROM emp; -- 拼接完成

5.3数学函数

函数	解释
abs（num）	绝对值
bin（decimal_number）	十进制转二进制
ceiling（number2）	向上取整，得到比num2大的最小整数
conv（number2，from_base,to_base）	进制转换
floor（number2）	向下取整，得到比num2小的最大整数
format（number，decimal_places）	保留小数位数
hex(decimalNumber)	转十六进制
least（number，number2[,...]）	求最小值
mod(numberator,denominator)	求余
rand（[seed]）	其范围在0<=v<=1.0

rand返回一个随机浮点值v，范围在0到1之间（即，其范围 为0<=v<=1.0）。若已指定一个整数参数N，则它被用作种子值，用来产生重复序列

练习

-- 演示数学相关函数
-- abs（num）绝对值
SELECT ABS(-10) FROM DUAL;
 
-- bin（decimal_number）十进制转二进制
SELECT BIN(10) FROM DUAL;
 
-- ceiling（number2）向上取整，得到比num2大的最小整数
SELECT CEILING(-2.1) FROM DUAL;
 
-- conv（number2，from_base,to_base）进制转换
SELECT CONV(8,10,2) FROM DUAL; -- 将十进制的8转换成2进制输出
 
-- floor（number2）向下取整，得到比num2小的最大整数
SELECT FLOOR(1.2) FROM DUAL;
 
-- format（number，decimal_places）保留小数位数
SELECT FORMAT(1.23456789,5) FROM DUAL;-- 四舍五入
 
-- hex(decimalNumber)转十六进制
SELECT HEX(-10000) FROM DUAL;
 
-- least（number，number2[,...]）求最小值
SELECT LEAST(-10,0,-22,4,3.45,99) FROM DUAL;
 
-- mod(numberator,denominator)求余
SELECT MOD(-10,8) FROM DUAL; -- -2
 
-- rand（[seed]）其范围在0<=v<=1.0
SELECT RAND(5) FROM DUAL;

5.4日期函数

函数	解释
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date（datetime）	返回datetime的日期部分
date_add(date2,interval d_value d_type)	在date2中加上日期或者时间
date_sub(date2,interval d_value d_type)	在date2中减去一个时间
datediff（date1,date2）	两个日期差（结果是天）
timediff（date1,date2）	两个时间差（结果是时分钟秒）
now（）	当前时间
year|month|day|date (datetime)	年月日
unix_timestamp()	返回1970-01-01 00:00:00到现在的秒数
from_unixtime	把一个 UNIX_TIMESTAMP()秒数[时间戳]转成指定格式的日期

练习1

 -- 日期相关函数
-- current_date()  当前日期  
SELECT CURRENT_DATE() FROM DUAL;
                  
-- current_time()   当前时间   
SELECT CURRENT_TIME() FROM DUAL;
                
--  current_timestamp()  当前时间戳   
SELECT CURRENT_TIMESTAMP() FROM DUAL;
 

练习2

-- 创建测试表 信息表
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;

-- 显示所有新闻信息，发布日期只显示日期，不用显示时间
-- date（datetime） 返回datetime的日期部分 
SELECT id,content,DATE(send_time) FROM mes;
 
 
-- 请查询在20分钟内发布的新闻  
-- now () 当前时间   
-- date_add(date2,interval  d_value d_type)  在date2中加上日期或者时间  
SELECT * FROM mes
	WHERE DATE_ADD(send_time,INTERVAL 20 MINUTE)>= NOW()
-- 或者用
-- date_sub(date2,interval  d_value d_type)   在date2中减去一个时间  
SELECT * FROM mes
	WHERE DATE_SUB(NOW(),INTERVAL 20 MINUTE)<= NOW()-- 现在的时间减去20分钟
 
 
-- 请在mysql的SQL语句中求出今天和1999-09-10相差多少天      
-- datediff（date1,date2）  两个日期差（结果是天）    
SELECT DATEDIFF(NOW(),'1999-09-10') FROM DUAL;
 
 
--   如果你能活到80岁，请计算你还能活多少天 
SELECT DATEDIFF('2079-09-10',NOW()) FROM DUAL;
-- 或者
SELECT DATEDIFF(DATE_ADD('1999-09-10',INTERVAL 80 YEAR),NOW())
 
 
-- timediff（date1,date2） 两个时间差（结果是时分钟秒）
SELECT TIMEDIFF('07:09;09','10:34:49') FROM DUAL;
 
-- year|month|day|date (datetime) 
SELECT YEAR(NOW()) FROM DUAL; -- 年
SELECT MONTH(NOW()) FROM DUAL; -- 月
SELECT DAY(NOW()) FROM DUAL; -- 日
SELECT YEAR('2011-11-09') FROM DUAL; -- 2011
 
-- unix_timestamp():返回1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL; 
 
-- from_unixtime  把一个 UNIX_TIMESTAMP()秒数[时间戳]转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- %H:%i:%s 表示时分秒
-- 意义在于：在开发中，可以存放一个整数(int)表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(1664876374,'%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1664876374,'%Y-%m-%d %H:%i:%s') FROM DUAL;

• 细节说明

5.5加密函数

函数	解释
user ( )	查询用户
database ( )	数据库名称
md5（str）	为字符串算出一个md5 32的字符串，常用于(用户密码)加密
password（str）	另一种加密函数，从原文密码str计算并返回密码字符串，mysql数据库的用户密码就是password函数加密

练习

-- user()查询用户
-- 可以查看登录到mysql的有哪些用户以及登录的ip
SELECT USER() FROM DUAL; -- 用户@IP地址
 
-- database()数据库名称
SELECT DATABASE() FROM DUAL;
 
-- md5（str）为字符串算出一个md5 32的字符串，常用于(用户密码)加密
SELECT MD5('123456') FROM DUAL;
SELECT LENGTH(MD5('bhfjksdhjfkshf')) FROM DUAL; -- 不管密码有多长，加密后都是32位
 
-- 演示
CREATE TABLE hsp_user(
 id INT,
 `name` VARCHAR(32) NOT NULL DEFAULT '',
 pwd CHAR(32)  NOT NULL DEFAULT ''
)
 
INSERT INTO hsp_user VALUES(100,'olien',MD5('hsp')); -- c435a9810009800cafef7dce3b7844a6
SELECT * FROM hsp_user;
 
SELECT * FROM hsp_user
	WHERE `name` = 'olien' AND pwd = MD5('hsp')
 
 
-- password（str）加密函数，mysql数据库的用户密码就是password函数加密
-- 从原文密码str计算并返回密码字符串
SELECT PASSWORD('hsp') FROM DUAL; -- *81220D972A52D4C51BB1C37518A2613706220DAC
 
-- select * from mysql.user 
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user -- '123456' 对应的加密： *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

5.6流程控制函数

函数	解释
if(expr1,expr2,expr3)	如果expr1为真，就返回expr2，否则返回expr3
ifnull(expr1,expr2)	如果expr1不为空，就返回expr1，否则返回expr2
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4...ELSE expr5 END;[类似多重分支]	如果expr1为真，就返回expr2；如果expr3为真，就返回expr4...否则返回expr5

练习

1. 查询emp表，如果comm是null，则显示 0.0
2. 如果emp的表的job是CLERK，则显示职员，如果是 MANAGER 则显示 经理 如果是 SALESMAN 则显示 销售人员，其他正常显示

SELECT * FROM emp
 
# 演示流程控制语句
-- if(expr1,expr2,expr3) 如果expr1为真，就返回expr2，否则返回expr3
SELECT IF(TRUE,'beijing','shanghai') FROM DUAL;
 
-- ifnull(expr1,expr2) 如果expr1不为空，就返回expr1，否则返回expr2
SELECT IFNULL(NULL,'12345') FROM DUAL;
 
-- SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4...ELSE expr5 END;[类似多重分支] 
-- 如果expr1为真，就返回expr2；如果expr3为真，就返回expr4...否则返回expr5
SELECT CASE 
	WHEN TRUE THEN 'jack' 
	WHEN FALSE THEN 'tom'
	ELSE 'mary' END;
	
# 应用	
-- 1. 查询emp表，如果comm是null，则显示 0.0
-- 判断是否为空 用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 '销售人员'	    
	    ELSE job END)AS 'job'
	FROM emp;