Mysql常用函数的基本使用

 函数

合计/统计函数

统计函数

SELECT count(*)|count(列名) from table_name
    WHERE where_definition

注：

count（*）返回满足条件的记录的行数

count（列）统计满足条件的某列有多少个，但会排除内容为NULL的记录

实例：

创建tt表并向其中插入相关数据

CREATE TABLE tt(
	`name` VARCHAR(20)
);
INSERT INTO tt VALUES('jack');
INSERT INTO tt VALUES('mike');
INSERT INTO tt VALUES('lili');
INSERT INTO tt VALUES('lucy');
INSERT INTO tt VALUES(NULL);
SELECT * FROM tt;

统计表中有多少人

1. 

SELECT COUNT(*) FROM tt

2. 

SELECT COUNT(`name`) FROM tt

合计函数

sum（求数值）

语法：

SELECT sum（列名）(,sum(列名),..) FROM table_name
    [WHERE where_definition] 

注：

sum函数只对数值起作用

实例：

统计班级的数学总成绩

SELECT SUM(math) FROM student;

avg函数（求平均数）

求班级学生的数学平均成绩

SELECT AVG(math) FROM student;

Max/Min函数

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

语法：

SELECT MAX(列名) FROM table_name
 [WHERE where_definition]

实例：

求班级成绩最高分和最低分的同学的名字和总分

select max(math+english+chinese),Min(math+english+chinese) from student;

分组统计

group by

语法：

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

group by ... having..

语法：

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

注：group by 用于对查询的结果分组统计

        having 子句用于限制分组显示结果

创教新表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;

创建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),
    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),
(7400,'ALLEN','SALESMAN',7698,'1991-2-20',1600,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,30),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-12-3',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,20),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23',1300.00,NULL,10);

创建salgrade表

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);
SELECT * FROM salgrade;

实例：

查找每个部门的最高工资和平均工资

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

以上一实例为基础，找出平均工资低于两千的部门

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

字符串函数

CHARSET	返回字串字符集
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,replace_str)	在str中用replace_str替换search_str
STRCMP(string1，string2)	逐字符比较字串大小
SUBSTRING(str,position[length])	从str的position开始[从1开始计算]，取length个字符
LTRIM(string2)RIRIM(string2)trim	去除前端空格或后端空格

实例：

查看emp中ename列的字符集

SELECT CHARSET(enmae) FROM emp;

CONCAT函数使用

SELECT CONCAT(enmae,'job is ',job) FROM emp;

INSTR函数使用

SELECT INSTR('chengcheng','cheng')

SELECT INSTR('chengcheng','cheng') FROM DUAL;

注：

dual为亚元表（系统表），可以作为测试表使用

LENGTH函数用法

SELECT LENGTH('cheng'),LENGTH('程');

REPLACE函数使用

SELECT enmae,REPLACE(job,'MANAGER','经理') FROM emp;

STRCMP函数使用

SELECT STRCMP('cheng','aheng'),STRCMP('cheng','cheng'),
	STRCMP('cheng','abcng'),STRCMP('cheng','zheng'),
	STRCMP('cheng','ahfng'),STRCMP('cheng','chen');

注：

此函数逐字符比，若有在相同位置上的字符不同，则比较，若前面大于后面，返回1，反之返回-1，不进行后续字符的比较，都相同返回0

数学函数

ABS(num)	绝对值
BIN(decimal_number)	十进制转二进制
CEILING(number2)	向上取整，得到比num2大的最小整数
CONV(number2,from_base,to_base)	进制转换
FLOOR(number2)	向下取整，得到比number2更小的最大整数
FORMAT(number,decimal_places)	保留小数位数
HEX(DecimalNumber)	转十六进制数
LEAST(number，number2[，...])	求最小值
MOD(numerator，denominator)	求余
RAND([seed])	RAND([seed])范围为【0-1.0】

注：

若使用RAND()返回随机数,其范围为【0，1.0】

若使用RAND(seed)返回随机数，范围【0，1.0】，若seed不变，该随机数不变（seed为常数）

日期函数

CURRENT_DATE	当前日期（年月日）
CURRENT_TIME	当前时间（时分秒）
CURRENT_TIMESTAMP	当前时间戳（年月日 时分秒）
DATE(datetime)	返回datetime的日期部分
DATE_ADD(date2,INTERVAL d_value d_type)	在date2上加上日期或时间
DATE_SUB(date2,INERVAL d_value d_type)	在date2上减去一个时间
DATEDIFF(date1,date2)	两个日期差（结果是天）
TIMEDIFF(date1,date2)	两个时间差（时分秒）
NOW()	当前时间
YEAR|MONTH|DATE(datetime)	年月日
UNIX_TIMESTMP()	返回1970-1-1 00：00：00到现在的秒数
FROM_UNIXTIME()	将整数转换成时间戳

实例：

创建mes表并插入相关数据

CREATE TABLE mes(
	id INT,
	content VARCHAR(10),
	send_time DATETIME
);
INSERT INTO mes
	VALUES(1,'北京新闻',CURRENT_TIMESTAMP),
		(2,'上海新闻',CURRENT_TIMESTAMP),
		(3,'天津新闻',CURRENT_TIMESTAMP)
SELECT * FROM mes

查找各新闻的日期

SELECT id,content,DATE(send_time)
	FROM mes

查找插入记录时间在十分钟以内的记录

SELECT * FROM mes
	WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) > NOW()

DATE(datetime)	返回datetime的日期部分
DATE_ADD(date2,INTERVAL d_value d_type)	在date2上加上日期或时间
DATE_SUB(date2,INERVAL d_value d_type)	在date2上减去一个时间
DATEDIFF(date1,date2)	两个日期差（结果是天）

为每条记录的时间戳多加十分钟

SELECT id,content,DATE(send_time),DATE_ADD(send_time,INTERVAL 10 MINUTE) FROM mes

注：

1. DATE_ADD()中的interval后面可以是YEAR,MONTH,DAY,MINUTE,SECOND

2. DATE_SUB()中的interval后面可以是YEAR,MONTH,DAY,MINUTE,SECOND

3. DATEDIFF(date1，date2)得到的结果可以是负数

4. 这四个函数的日期类型可以是date（年月日），datetime（日期），timestamp（年月日时分秒）

FROM_UNIXTIME();函数使用

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

注：FROM_TIMESTAMP表示当前时间到1970-1-1 00：00：00的秒数

加密和系统函数

USER()	查询用户
DATABASE()	数据库名称
MD5(str)	为字符串算出一个MD5 32的字符串，(常用来进行用户密码加密)
PASSWORD(str) select * from mysql_user	从原文密码str计算机返回密码字符串。通常用于对mysql数据库的用户密码加密

实例：

查询当前登录mysql的用户及其IP

SELECT USER() FROM DUAL

查询当前使用的数据库

SELECT DATABASE()

加密函数

SELECT MD5('cc'),LENGTH(MD5('cc')),PASSWORD(MD5('cc'))

流程控制函数

IF(expr1,expr2,expr3)	如果expr1为True，则返回expr2，否则返回expr3
IFNULL（expr1,expr2）	如果expr1不为空NULL，则返回expr1，否则返回expr2
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5；【类似多重分支】	如果expr1为TRUE，则返回expr2，如果expr3为TRUE，返回expr4，否则返回expr5

实例：

IF函数使用

select if(true,'北京','上海')

查询emp表如果comm为null，则显示0.0

SELECT enmae,comm
	FROM emp

1.

SELECT enmae,IF(comm IS NULL,0.0,comm)
	FROM emp
	

2.

select enmae,ifnull(comm,0.0)
	from emp

如果emp表的job是CLERK，则显示职员，如果是MANAGER则显示经理，如果是SALESMAN则显示销售人员

SELECT enmae,(SELECT CASE WHEN 
		job = 'CLERK' THEN '职员'
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员' 
		ELSE job END)
	FROM emp

注：上面语句中末尾ELSE job END 一定要加，不然其他记录的显现会默认为NULL

SELECT enmae,(SELECT CASE WHEN 
		job = 'CLERK' THEN '职员'
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员' 
		END)
	FROM emp