合计/统计函数
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) | 返回字串字符集 |
CONCAT(string2 [,...] | 连接字串 |
INSTR(string ,substring) | 返回substring在string中出现的位置,如果没有则返回0 |
UCASE(string2) | 转换成大写 |
LCASE(string2) | 转换成小写 |
LEFT(string2,length) | 从string2中的左边取length个字符 |
LENGTH(string) | string长度【按照字节】 |
REPLACE(str,search_str,replace_str) | 在str中用replace_str替换search_str |
STRCMP(string1,string2) | 逐字符比较两字串大小 |
SUBSTRING(str,position[,length]) | 从str的position开始【从1开始计算】,取length个字符 |
LTRIM(string2)|RTRIM(string2)|TRIM(string2) | 去除前端空格或者后端空格 |
测试代码
#字符串常用函数的使用
-- 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) | 绝对值 |
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(numerator,denominator) | 求余 |
RAND([seed]) | RAND([seed])其范围为0≤v≤1.0 |
测试代码
-- 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;
日期函数
CURRENT_DATE() | 当前日期 |
CURRENT_TIME() | 当前时间 |
CURRENT_TIMESTAMP() | 当前时间戳 |
DATE(datetime) | 返回datetime的日期部分 |
DATE_ADD(date2, INTERVAL d_valued_type) | 在date2中加上日期或者时间 |
DATE_SUB(date2, INTERVAL d_calued_type) | 在date2上减去一个时间 |
DATEDIFF(date1, date2) | 两个日期差(结果返回是天数) |
TIMEDIFF(date1, date2) | 两个时间差(XX小时XX分钟XX秒) |
NOW() | 当前时间(年月日时分秒) |
YEAR|MONTH|DATE(datetime) FROM_UNIXTIME() unix_timestamp(); | 年月日 |
CURRENT_DATE( ) | 当前日期 |
CURRENT_TIME( ) | 当前时间 |
CURRENT_TIMESTAMP | 当前时间戳 |
细节说明:
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() | 查询用户 |
DATABASE() | 数据库名称 |
MD5(str) | 为运算符算出一个md5 32的字符串,(用户密码)加密 |
PASSWORD(str) select * from mysql.user \G | 从原文密码str计算并返回密码字符串,通常用于对mysql数据库的用户密码加密 |
测试代码
# 加密函数和系统函数
-- 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 |
IFNULL(expr1,expr2) | 如果expr1不为null,则返回expr1否则返回expr2 |
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; | 如果expr1为true,则返回expr2,如果expr3为true,则返回expr4,否则返回expr5 |
测试代码
#流程控制语句
-- 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;