Java编程学习-MySQL（函数）

合计/统计函数

count 函数返回行的总数

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

-- 统计一个班级一共有多少人
SELECT COUNT(*) FROM student;
SELECT COUNT(math) FROM student;
-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
		WHERE math>90;
SELECT COUNT(math) FROM student
		WHERE math>90;
-- 统计总分大于260的学生人数有多少个
SELECT COUNT(*) FROM student
		WHERE (chinese+english+math)>260;
SELECT COUNT(math) FROM student
		WHERE(chinese+english+math)>260;
-- count（*）和count（列）的区别
-- count(*)：返回满足条件的记录的行数
-- count(列)：统计满足条件的某列有多少个学生，但是会排除null的数据
CREATE TABLE t4(
		`name` VARCHAR(20));
INSERT INTO t4 VALUES ('tom');
INSERT INTO t4 VALUES ('marry');
INSERT INTO t4 VALUES (NULL);
INSERT INTO t4 VALUES ('jake');
INSERT INTO t4 VALUES ('lucy');
INSERT INTO t4 VALUES ('smith');
SELECT * FROM t4;
SELECT COUNT(*) FROM t4;		-- 6
SELECT COUNT(`name`) FROM t4;	-- 5

Sum 函数返回满足where条件的行数据的和（统计数）

select sum(列名) {,sum(列名)...} from table_name
        [WHERE where_sefinition]

注意事项：

1）sum函数只对数值起作用；

2）对多列求和，“,”号不能少。

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

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

SELECT avg(列名) {，acg(列名)...} from table_name
        [WHERE where_definition]

#AVG函数使用
-- 求一个班级数学平均分
SELECT AVG(math) AS '数学平均分' FROM student;
-- 求一个班级总分平均分
SELECT AVG(总分) AS '总分平均分' FROM student;
SELECT AVG(chinese+english+math) AS '总分平均分' FROM student;

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

#求最大值
select max(列名) from table_name
        [WHERE where_definition]
 
#求最小值
select min(列名) from table_name
        [WHERE where_definition]

-- 求班级最高分和最低分
SELECT MAX(总分) AS '总分最高' FROM student;
SELECT MIN(chinese+english+math) AS '总分最低分' FROM student;
 
--求班级数学最高分和数学最低分
SELECT MAX(math) AS '数学最高分' FROM student;
SELECT MIN(math) AS '数学最低分' FROM student;

分组统计

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

select column1,column2,column3.. from table_name
        group by column;

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

select column1,column2,column3...
        from table_name
        group by column having ...

测试代码

#创建部门表
DROP TABLE dept;
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;
 
#创建雇员表
DROP TABLE EMP;
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',1850.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 slagrade(
		grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
		losal DECIMAL(17,2) NOT NULL,		-- 该级别的最低工资
		hisal DECIMAL(17,2) NOT NULL);		-- 该级别的最高工资
INSERT INTO slagrade VALUES (1,700,1200);
INSERT INTO slagrade VALUES (2,1201,1400);
INSERT INTO slagrade VALUES (3,1401,2000);
INSERT INTO slagrade VALUES (4,2001,3000);
INSERT INTO slagrade VALUES (5,3001,9999);
SELECT * FROM slagrade;
 
#分组统计
-- having 子句用于限制分组显示结果
-- 如何显示每个部门的平均工资和最高工资
-- 分析：1.每个部门员工的平均工资AVG
--	       2.每个部门最高工资MAX
SELECT AVG(sal), MAX(sal),deptno FROM emp	-- 查询平均工资、最高工资、和部门编号
		GROUP BY deptno;	-- 按照部门进行分组
 
-- 显示每个部门的每个岗位的平均工资和最低工资
-- 分析：1.显示每个部门的平均工资和最低工资
--		2.每个部门的每个岗位的平均工资和最低工资  -- 再加一个分组的标准（job）
SELECT AVG(sal), MAX(sal), deptno, job FROM emp
		GROUP BY deptno, job;
 
-- 显示平均工资低于2000的部门号和它的平均工资 //别名
-- 分析：1.显示部门号和它的平均工资
--		2.筛选，保留平均工资低于2000的
SELECT deptno, AVG(sal) FROM emp
		GROUP BY deptno HAVING AVG(sal) < 2000;
-- 使用别名
SELECT deptno AS `部门编号`, AVG(sal) AS `平均工资` FROM emp
		GROUP BY `部门编号` HAVING `平均工资` < 2000;

字符串相关函数

测试代码 

#字符串常用函数的使用
-- CHARSET(str)返回字串字符集
SELECT CHARSET (ename) FROM emp;		-- utf8.......
 
-- concat(String2 [,...])连接字串
SELECT CONCAT (ename,' job is ',job) AS '姓名和工作' FROM emp;
 
-- instr(string ,substring) 返回substring在string中出现的位置，没有则返回0
SELECT INSTR ('MARTIN','IN') FROM DUAL;	-- 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,replace_str) 在str中用replace_str替换search_str
SELECT REPLACE (job,'MANAGER','经理') FROM emp;
 
-- strcmp(string1,string2)逐字符比较两个字串大小
SELECT STRCMP('abcd','abcde') FROM DUAL;
 
-- substring(str,position[,length]) 从str的position开始【从1开始计算】，取length个字符
-- select substring (ename, 1, 2) from emp;	-- 报错
 
-- ltrim (string2) rtrim (string) trim(string) 去除前端空格或者后端空格,去除两端空格
SELECT LTRIM('  lucy  ') FROM DUAL;
SELECT RTRIM('  lucy  ') FROM DUAL;
SELECT TRIM('  lucy  ') FROM DUAL;
 
-- 以首字母小写的方式输出所有员工emp表中的姓名
-- 分析1：思路先取出ename的第一个字符，转成小写的
--		然后和后面的字符串进行拼接输出
SELECT CONCAT(
		LCASE(LEFT(ename,1)), 
		RIGHT(ename,LENGTH (ename) -1)) 
		AS new_name FROM emp;
 
-- select concat(
-- 		lcase(substring(ename,1,1)), 
-- 		substring (ename,2)) 		-- substring 函数报错
-- 		from emp;

数学函数

测试代码

-- ABS(num)	绝对值
SELECT ABS(-12) FROM DUAL;
 
-- BIN(decimal_number)	十进制转二进制
SELECT BIN(10) FROM DUAL;
 
-- CEILING(number2)	向上取整，得到比num2大的最小整数
SELECT CEILING(2.3) FROM DUAL;
 
-- CONV(number2,from_base,to_base)	进制转换
SELECT CONV(15,10,2) FROM DUAL;		-- 把15当作10进制转换成2进制
SELECT CONV(110111001,2,10) FROM DUAL;    -- 把110111001当作2进制转换成10进制
SELECT CONV('B',16,10) FROM DUAL;	-- 把' B '当作十六进制转换成10进制
 
-- FLOOR(number2)	向下取整，得到比num2小的最大整数
SELECT FLOOR(-12.8) FROM DUAL;
 
-- FORMAT(number,decimal_places)	保留小数位数
SELECT FORMAT(123456.789123456789,3) FROM DUAL;
SELECT FORMAT(AVG(sal),2) , MAX(sal) , deptno 
		FROM emp GROUP BY deptno;
 
-- HEX(DecimalNumber)	转十六进制
SELECT HEX(10) FROM DUAL;
 
-- LEAST(number,number2[,...]	求最小值
SELECT LEAST(1,2,5,8,6,4,3,-5,17,19) FROM DUAL;
 
-- MOD(numerator,denominator)	求余
SELECT MOD(10,3) FROM DUAL;		-- 10%3  = 1
 
-- RAND([seed])	返回一个随机数RAND([seed])其范围为0≤v≤1.0
-- seed 为种子，插入后返回一个随机数，
-- 随机数与种子数绑定不再改变，相同的种子数得到的随机数相同
SELECT RAND(3) FROM DUAL;

日期函数

细节说明：

1）DATE_ADD()中interval后面可以是 year minute second day等时间单位；

2）DATE_SUB()中interval后面可以是 year minute second hour day等时间单位；

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

4）这四个函数的日期类型可以是date，datetime，timestamp。

5）在实际开发中，经常使用int来保存一个unix时间戳，然后使用from_unixtime()进行转换。

-- CURRENT_DATE()	当前日期
SELECT CURRENT_DATE FROM DUAL;
 
-- CURRENT_TIME()	当前时间
SELECT CURRENT_TIME FROM DUAL;
 
-- CURRENT_TIMESTAMP()	当前时间戳
SELECT CURRENT_TIMESTAMP FROM DUAL;
 
-- DATE(datetime)	返回datetime的日期部分
SELECT DATE('2021-11-12 19:32:51') FROM DUAL;
 
-- DATE_ADD(date2, INTERVAL d_valued_type)	在date2中加上日期或者时间
SELECT * FROM mes
		WHERE DATE_ADD(sendtime,INTERVAL 10 MINUTE) >=NOW(); 
		
-- DATE_SUB(date2, INTERVAL d_calued_type)	在date2上减去一个时间
SELECT * FROM mes
		WHERE sendtime >= DATE_SUB(NOW(),INTERVAL 50 MINUTE);
 
-- DATEDIFF(date1, date2)	两个日期差（结果返回是天数）
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;	-- 大的日期放在前面
 
-- TIMEDIFF(date1, date2)	两个时间差（XX小时XX分钟XX秒）
SELECT TIMEDIFF('10:10:10','01:01:01:') FROM DUAL;
 
-- NOW()	当前时间
SELECT NOW() FROM DUAL;
 
-- YEAR|MONTH|DATE(datetime) 年月日
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT HOUR(NOW()) FROM DUAL;
SELECT MINUTE(NOW()) FROM DUAL;
SELECT SECOND(NOW()) FROM DUAL;
 
--返回1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
 
-- 可以转成一个自1970-1-1 00:00:00过去FROM_UNIXTIME秒数的日期
-- 固定格式年月日：'%Y-%m-%d 时分秒：%H:%i:%s
SELECT FROM_UNIXTIME(1618483484,'%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483484,'%Y-%m-%d %H:%i:%s') FROM DUAL;
 
# 创建测试表
CREATE TABLE mes(
		id INT , 
		content VARCHAR(30),
		sendtime DATETIME);
-- 添加记录
INSERT INTO mes VALUES (1,'新闻联播',CURRENT_TIMESTAMP());
INSERT INTO mes VALUES (2,'上海新闻',NOW());
INSERT INTO mes VALUES (3,'广州新闻',NOW());
 
-- 显示所有新闻信息，发布日期只显示日期不显示时间
SELECT id,content,DATE(sendtime) FROM mes;
 
-- 查询10分钟内发布的新闻
SELECT * FROM mes
		WHERE DATE_ADD(sendtime,INTERVAL 10 MINUTE) >=NOW(); 
 
-- 求出2011-11-11和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;
 
-- 求出生活了多少天
SELECT DATEDIFF(NOW(),'1996-01-20') FROM DUAL;
 
-- 如果能活100岁，求出剩余天数'1996-01-20'->date、'1996-01-20 11:10:10'->tatetime、timestamp;
SELECT DATEDIFF(DATE_ADD('1996-01-20',INTERVAL 100 YEAR),NOW()) FROM DUAL;
 
SELECT * FROM mes;

加密函数和系统函数

测试代码

# 加密函数和系统函数
 
-- user()		查询用户
SELECT USER() FROM DUAL;  -- 用户名@地址
 
-- database()		当前在使用的数据库名称
SELECT DATABASE() FROM DUAL;
 
-- MD5(str)		为运算符算出一个md5 32的字符串，（用户密码）加密
-- 在数据库中存放的是加密后的密匙
SELECT MD5('pero') FROM DUAL;
-- 加密后的数据长度为32位置
SELECT LENGTH(MD5('pero')) FROM DUAL;
 
#创建用户表
CREATE TABLE `users`(
		id INT UNSIGNED NOT NULL DEFAULT 0,
		`name` VARCHAR(32) NOT NULL DEFAULT ' ',
		pwd CHAR(32) NOT NULL DEFAULT ' ');
INSERT INTO users VALUES (1,'pero',MD5('look'));
SELECT * FROM users;
SELECT * FROM users
		WHERE `name` = 'pero' AND pwd = 'look';		-- 查出为空表
SELECT * FROM users
		WHERE `name` = 'pero' AND pwd = MD5('look');		-- 查出用户
 
-- password(str) 加密函数	mysql数据库的用户密码就是用password函数加密
SELECT PASSWORD('pero') FROM DUAL;
 
-- select * from mysql.user \G 从原文密码str计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示数据库.表
SELECT * FROM mysql.user

流程控制函数

测试代码

#流程控制语句
-- IF(expr1,expr2,expr3)	如果expr1为ture,则返回expr2否则返回expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL;	-- 北京
SELECT IF(FALSE,'北京','上海') FROM DUAL;	-- 上海
 
-- IFNULL(expr1,expr2)	如果expr1不为null，则返回expr1否则返回expr2
SELECT IFNULL(NULL,'pero') FROM DUAL;		-- pero
SELECT IFNULL('jake','pero') FROM DUAL;		-- jake
 
-- SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;	
-- 如果expr1为true，则返回expr2，如果expr3为true，则返回expr4，否则返回expr5
SELECT CASE
		WHEN TRUE THEN 'jake'
		WHEN FALSE THEN 'lucy'
		ELSE 'mary' END;
		
-- 查询emp表，如果comm是null，则显示0.0；
SELECT IFNULL(comm,0.0) FROM emp;
SELECT IF(comm IS NULL,0.0,comm) FROM emp;
 
-- 如果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;