1. 合计/统计函数 758
1.1 count 758
1.1.1 练习
统计一个班级共有多少学生?
统计数学成绩大于90的学生有多少个?
统计总分大于250的人数有多少?
count(*)和count(列)的区别
-- 解释 :count(*) 返回满足条件的记录的行数
-- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况
代码在E:\java学习\初级\course155\db_
statistics
#合计/统计函数 758
-- 统计一个班级共有多少学生?
SELECT COUNT(*) FROM student
-- 统计数学成绩大于90的学生有多少个?
SELECT COUNT(*) FROM student
WHERE math>90
-- 统计总分大于250的人数有多少?
SELECT COUNT(*) FROM student
WHERE (math+chinese+english)>250
-- count(*)和count(列)的区别
-- 解释 :count(*) 返回满足条件的记录的行数
-- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况
CREATE TABLE t15(
`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);
SELECT * FROM t15;
SELECT COUNT(*) FROM t15;-- 4
SELECT COUNT(`name`) FROM t15;-- 3,因为排除了为null的情况1.2 sum 758
1.2.1 课堂练习:
■统计一个班级数学总成绩?
■统计一个班级语文、英语、数学各科的总成绩
■统计一个班级语文、英语、数学的成绩总和
■统计一个班级语文成绩平均分
注意: sum仅对数值起作用，没有意义。
注意:对多列求和，“，”号不能少。
代码在E:\java学习\初级\course155\db_
statistics
-- 演示sum函数的使用 758
-- ■统计一个班级数学总成绩?
SELECT SUM(math) FROM student
-- ■统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english) AS english_total_score,
SUM(chinese) AS chinese_total_score FROM student
-- ■统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math+english+chinese) FROM student
-- ■统计一个班级语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student1.3 avg和Max/Min 758
1.3.1 练习:
■求一个班级数学平均分?
■求一个班级总分平均分
练习:求班级最高分和最低分(数值范围在统计中特别有用)
代码在E:\java学习\初级\course155\db_
statistics
-- 演示avg的使用 758
-- ■求一个班级数学平均分?
SELECT AVG(math) FROM student
-- ■求一个班级总分平均分
SELECT AVG(math+english+chinese) FROM student
-- 演示Max/Min的使用 758
-- 练习:求班级最高分和最低分(数值范围在统计中特别有用)
SELECT MAX(math+english+chinese),MIN(math+english+chinese) FROM student
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
FROM student;2. 使用 group by 子句对列进行分组 [先创建测试表] 759
2.1 使用 having 子句对分组后的结果进行过滤 759
2.2 练习 759
2和3的代码在
group by用于对查询的结果分组统计，(示意图)
having子句用于限制分组显示结果
?如何显示每个部门 ]的平均工资和最高工资
?显示每个部门的每种岗位的平均工资和最低工资
?显示平均工资低于2000的部门号和它的平均工资//别名
代码在E:\java学习\初级\course155\db_
groupby
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) 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
#演示groupby和having的使用 759
-- group by用于对查询的结果分组统计
-- having子句用于限制分组显示结果
-- ?如何显示每个部门 ]的平均工资和最高工资
-- 分析: avg(sal) max(sal)
-- 按照部分来分组查询
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的部门号和它的平均工资//别名
-- 分析 [写 sql 语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
SELECT AVG(sal),deptno
FROM emp GROUP BY deptno
HAVING AVG(sal)<2000
-- 3. 使用别名进行过滤
SELECT AVG(sal) AS avg_sal,deptno
FROM emp GROUP BY deptno
HAVING avg_sal<20003. 字符串相关函数 760
代码在E:\java学习\初级\course155\db_
char_
#演示字符串相关函数的使用 使用emp表来演示 760
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp
-- CONCAT (string2 [,... ]) 连接字串,将多个列拼接成一列
SELECT CONCAT(ename,' 工作是 ',job) FROM emp;
-- INSTR (string ,substring ) 返回substring在string中出现的位置,没有返回0
SELECT INSTR('chinaandworld','world')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
-- 如果是 MANAGER 就替换成 经理
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp','asp') FROM DUAL;
-- SUBSTRING (str，position [,length]) 从str的position开始[从1开始计算] ,取length个字符
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename,1,2)FROM emp
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string) 去除前端空格或后端空格或者左右两端都去
SELECT LTRIM(' 中国世界')FROM DUAL
SELECT RTRIM('中国世界 ')FROM DUAL
SELECT TRIM(' 中国世界 ')FROM DUAL3.1 练习 761
练习:以首字母小写的方式显示所有员Lemp表的姓名使用两种方式
代码在E:\java学习\初级\course155\db_
char_
-- 练习:以首字母小写的方式显示所有员Lemp表的姓名使用两种方式 761
-- 方式一
-- 思路先取出 ename 的第一个字符，转成小写的
-- 把他和后面的字符串进行拼接输出即可
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) AS new_name#2后面可以不写直接默认取完
FROM emp
-- 方式二
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) AS new_name#2后面可以不写直接默认取完
FROM emp