服务器相关（3）：mysql中的常用函数（SQLyog中操作）

一、统计函数

1.1 count

-- 统计总共多少人
SELECT COUNT(*)FROM student;
-- 数学大于70的学生
SELECT COUNT(*)FROM student
	WHERE math>70;
-- 统计总分大于210的学生
SELECT COUNT(*)FROM student
	WHERE (math+english+chinese)>210;

1.2 sum

-- 统计数学总成绩
SELECT SUM(math)FROM student;

-- 统计语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student;

1.3 分组统计（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');
SELECT * FROM dept;
-- 创建员工表
CREATE TABLE emp2
(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 -- 部门编号
);
DROP TABLE emp2;
INSERT INTO emp2 VALUES(7788,'SCOUT','CLERK',7698,'1991-9-8',1500.00,NULL,30);
INSERT INTO emp2 VALUES(7689,'SCOUT1','CLERK',7697,'1991-9-8',2000.00,NULL,20);
INSERT INTO emp2 VALUES(7690,'SCOUT2','CLERK',7699,'1991-9-8',2100.00,NULL,10);
INSERT INTO emp2 VALUES(7690,'SCOUT3','CLERK',7696,'1991-9-8',2100.00,NULL,40);
SELECT * FROM emp2;

-- 创建工资级别表
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 AVG(sal),MAX(sal),deptno
	FROM emp2 GROUP BY deptno -- 根据部门显示平均工资和最高工资
	HAVING AVG(sal)<2000; -- 保留平均工资小于2000的。

二、 字符串函数

-- charset(str)返回字符串符集
SELECT CHARSET(ename) FROM emp2;

-- concat 拼接字符串
SELECT CONCAT (ename,'job is',job)FROM emp2;

-- instr 返回字符串出现的位置
SELECT INSTR('chen','ping') FROM DUAL;

-- ucase转换成大写
SELECT UCASE(ename) FROM emp2;

-- lcase转换成小写
SELECT LCASE(ename) FROM emp2;

-- left左边取length个字符
SELECT LEFT(ename,2) FROM emp2;

-- length 长度统计
SELECT LENGTH(ename) FROM emp2;

-- 替换
	SELECT ename,REPLACE(job,'CLERK','CLERK0')FROM emp2;

-- 从ename的第一个位置取出两个字符
	SELECT SUBSTRING(ename,1,2)FROM emp2;

-- 去除前端空格或者后端空格
	SELECT LTRIM(' 去除前端空格或者后端空格 ')FROM emp2;

三、 数学函数

-- ABS 绝对值
	SELECT ABS(-10) FROM DUAL;
	-- bin 十进制转二进制
	SELECT BIN(10) FROM DUAL;
	-- ceiling 向上取整
	SELECT CEILING(-1.1) FROM DUAL;
	-- floor 向下取整
	SELECT FLOOR(-1.1) FROM DUAL;
	-- format 保留小数位数
	SELECT FORMAT(-1.1123456,2) FROM DUAL;
	-- hex 转十六进制
	
	-- least 求最小值
	SELECT LEAST(-1.1,-10,1) FROM DUAL;
	-- 求余
	SELECT MOD(10,3) FROM DUAL;
	-- 返回随机数，如果给上随机种子，每次返回的随机数是一样的
	SELECT RAND(3) FROM DUAL;

四、时间相关的函数

-- 当前日期
	SELECT CURRENT_DATE() FROM DUAL;
	
	-- 当前时间
	SELECT CURRENT_TIME() FROM DUAL;
	
	-- 当前时间戳
	SELECT CURRENT_TIMESTAMP() FROM DUAL;
	
	-- DATE(datetime)
	-- DATE_ADD、DATE_SUB
	-- DATE_DIFF()两个时间差 
	
	-- TIMEDIFF(DATE1,DATE2) 两个时间差
	SELECT TIMEDIFF('10:11:11','06:30:30') FROM DUAL;
	-- NOW()当前时间
	SELECT YEAR(NOW()) FROM DUAL;
	-- YEAR\MONTH\DAY\
	SELECT MONTH(NOW()) FROM DUAL;
	-- UNIX_TIMESTAMP 1970-1-1到现在的秒数
	SELECT UNIX_TIMESTAMP() FROM DUAL;
	-- FROM_UNIXTIME\
	SELECT FROM_UNIXTIME(1618483484,'%Y-%m-%d') FROM DUAL;
	SELECT FROM_UNIXTIME(1618483484,'%Y-%m-%d %H-%m-%s' ) FROM DUAL;

五、加密函数和系统函数

-- 查询用户
	SELECT USER() FROM DUAL;
	
	-- DATABASE()当前数据库
	SELECT DATABASE() FROM DUAL;
	
	-- MD5(str)加密后的密码
	SELECT MD5(123) FROM DUAL;
	
	-- PASSWORD() 对字符串生成加密后的新的字符串
	SELECT PASSWORD('123') FROM DUAL;

六、流程控制函数

-- IF()如果为true，返回第一个，否则返回第二个
	SELECT IF(TRUE,'北京','上海') FROM DUAL;
	-- IFNULL()不为空，返回第一个，否则返回第二个
	SELECT IFNULL('北京','上海') FROM DUAL;
	
	SELECT CASE
		WHEN TRUE THEN 'jack'
		WHEN FALSE THEN 'tom'
		ELSE'mary'END