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字符串函数

字符串相关函数

练习

-- -- 演示字符串相关的操作
-- -- 使用前面创建的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数学函数

5.4时间函数

5.5流程控制