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 |
练习
- 查询emp表,如果comm是null,则显示 0.0
- 如果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;
来源:https://www.cnblogs.com/liyuelian/p/16751117.html
本站部分图文来源于网络,如有侵权请联系删除。