本次博客带领大家学习mysql数据库中各种函数的使用。

统计函数 - count

• count 返回行的总数
  

• 案例1：

  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的情况。

合计函数 - sum

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

• 案例2：

  1. 统计一个班级的数学总成绩。

  SELECT SUM(math) FROM student;
  

  2. 统计一个班级语文、英语、数学各科的总成绩。

  SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
  

  3. 统计一个班级语文、英语、数学的成绩的总和。

  SELECT SUM(math+chinese+english) FROM student;
  

  4. 统计一个班级语文成绩平均分。

  SELECT SUM(chinese)/COUNT(*) FROM student;
  

• 注意：

  • sum 仅对数值起作用。
  • 对多列求和，","号不能少。

合计函数 - avg

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

• 案例3：

  1. 求一个班级数学平均分。

  SELECT AVG(math) FROM student;
  

  2. 求一个班级总分平均分。

  SELECT AVG(chinese+math+english) FROM student;
  

合计函数 - max/min

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

• 案例4：

  • 求班级最高分和最低分(数值范围在统计中特别有用)

  SELECT MAX(chinese+math+english),MIN(chinese+math+english) FROM student;
  

分组统计 - group by

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

• 使用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');

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),
(7900,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);

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),
    (2,1201,1400),
    (3,1401,2000),
    (4,2001,3000),
    (5,3001,9999);

• group by 用于对查询的结果分组统计，having子句用于限制分组显示结果。

• 案例5：

  1. 如何显示每个部门的平均工资和最高工资。

  SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP BY deptno;
  

  2. 显示每个部门的每种岗位的平均工资和最低工资。

  SELECT AVG(sal),MIN(sal),deptno,job FROM emp GROUP BY deptno,job;
  

  3. 显示平均工资低于2000的部门号和它的平均工资。

  SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)<2000;
  

字符串函数

• 案例6：以首字母小写的方式显示所有员工emp表的姓名。

  • 方法一：

  SELECT  CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) new_name FROM emp;
  

  • 方法二：

  SELECT  CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) new_name FROM emp;
  

数学函数

日期函数

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());

• 案例7：

  • 显示所有的新闻信息，发布日期只显示日期，不用显示时间。

  SELECT id,content,DATE(send_time) FROM mes;
  

  • 查询在10分钟内发布的新闻。

  SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();
  SELECT * FROM mes WHERE DATE_SUB(NOW(),INTERVAL 20 MINUTE) <= send_time ;
  

  • 求出2011-11-11 和 1990-1-1相差多少天。

  SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;
  

  • 求出你活了多少天。

  SELECT DATEDIFF(NOW(),'2000-11-29') FROM DUAL;
  

  • 如果你能活80岁，求出你还能活多少天。

  SELECT DATEDIFF(DATE_ADD('2000-11-29',INTERVAL 80 YEAR),NOW()) 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。

• unix_timestamp() : 返回的是从1970-1-1 到现在的秒数。

• from_unixtime() : 可以把一个unix_timestamp 秒数，转成指定格式的日期。

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s') FROM DUAL;

加密和系统函数

CREATE TABLE users(
	id INT,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	pwd CHAR(32) NOT NULL DEFAULT '');
	
INSERT INTO users VALUES(100,'领导',MD5('ld'));

SELECT * FROM users;

流程控制函数

• 案例8：

  1. 查询emp表，如果comm是null，则显示0.0。

  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;