DAY25 - 聚合函数（count、sum、avg、max,min、group、日期,字符串,数学,分支控制函数、其他函数）

8章 select 语句的基本使用

介绍：我们先讲解select 语句的基本使用，后面我们再讲解加强的部分.

8.1 基本介绍

(1) 红色关键字

(2) 如果我们希望过滤重复的数据，则加上 distinct

(3) * 号表示将所有的字段都检索出来，一般来说我们开发中不会使用select * 语句.这种语句会返回所有字段，效率较低.

(4) 如果我们只希望检索某几列，则写清楚字段名就可以。

(5) 我们的使用原则是，需要什么字段，就取回什么字段

 

8.2 练习

 

8.3 select 语句可以对列进行运算

 

关于字段别名的写法还有另外三种.

select 字段名 AS 别名 from 表名; [推荐使用]

select 字段名 AS '别名' from 表名;

select 字段名 别名 from 表名;

select 字段名 '别名' from 表名;

 

•  课堂练习

 

 

8.4 使用where子句，进行过滤查询

 

8.5 在where子句中经常使用的运算符

•  一览图:

•  举例说明

(1) 案例1

between .... and 是包含两边.

(2) 案例2

比如我们查询id 是 100, 200, 300的

select * from student where id = 100 or id = 200 or id = 300;

<==等价==>

select * from student where id in(100, 200,300);

(3) like 和 not like

是模糊查询, 表示某个字段中含有什么信息.

select * from 表名 where 字段名 like '张%' ; // %表示任意多个字符.

select * from 表名 where 字段名 not like '张%' ; // %表示任意多个字符

(4) 关于is null 的使用

 

 

8.6 课后练习题

 

8.7 order by子句

•  order by 子句是用于排序的.

•  基本语法

说明

(1) 红色是关键字.

(2) order by 一般来说是放在sql语句的最后.

(3) order by 字段名 [asc|desc], asc 表示升序排序，desc 表示降序排序，默认是升序排序.

(4) order by也可以对多个字段进行排序

order by 字段名1 [asc|desc], 字段名1 [asc|desc]....

 

8.8 合计/聚合函数-count

•  基本介绍: count 函数是进行统计满足条件的记录数有多少条, 是安照分组的形式统计.
•  基本的语法:

count(*) / count(列名)

•  举例说明

 

•  count细节

(1) count(*) 和 count(列名) 的区别

看一个案例:

 

 

8.9 聚合函数-sum

•  基本介绍: Sum函数返回满足where条件的行的和

•  基本语法

•  案例说明

 

•  细节

当我们sum时，列值有null时.

(1) 当我们对单列进行sum(列), 进行统计时，是正确

 

(2) 当我们对多列进行sum(列1+列2...), 进行统计，就会有误.

 

当一个值和另外一个值进行运算时，结果会为null <===> 0

(3) 解决方法

 分开统计:

 

 使用ifnull

ifnull 函数是这一个 ifnull(expr1, expr2), 如果expr1为null, 则取expr2值，如果expr1不为null,就取expr1值.

 

•  多说一句

 

　　等价于

 

 

8.10 聚合函数-avg

•  基本: AVG函数返回满足where条件的列的平均值

•  基本语法

•  案例说明

(1) select avg(math) from student; //单列ok

(2) select avg(ifnull(math,0.0) + ifnull(english,0.0) + ifnull(chinese,0.0)) from student;

 

8.11 聚合函数-max/min

•  基本介绍: 返回满足where条件的列的最大/最小值
• 基本语法

•  案例说明

 

 

8.12 group by子句

•  基本介绍: group by 就是对数据(列)进行分组统计, 如果我们需要对分组的结果进行过滤则可以使用关键字 having
•  基本语法:

 

可以对多列进行分组，分组的语法是

group by 列名1, 列名2  ...

•  案例说明
  
   需要先几张测试表

 

 画出分组查询示意图

 

 

---

 第9章 mysql函数(重点)

9.1 mysql函数一览表

日期函数

字符串函数

数学函数

流程控制函数

其它函数

 

9.2 日期函数

对日期进行处理的函数.

• 第一组的日期函数

(1) 基本的使用

 

•  第二组日期函数

 

(1) 基本使用案例

date函数：返回日期的部分

date_add(date1, inteval  val date_type)函数:

在date1的基础上加上 val 这个值的时间.

 

date_sub(date1, inteval  val date_type)函数:

在date1的基础上减去 val 这个值的时间.

 

datediff(date1, date2): 计算两个日期的差(天)

(2) 综合案例

 

(3) 本组日期函数的细节说明

关于date_add/date_sub 可以带的日期类型有.

 

• 课后练习

 

•  日期函数的第三组

 

(1) 基本使用

year|month|day , 可以返回日期中的年,月,日.

 ?请查询1982年3月入职的员工.

 unix_timestamp(): 函数返回一个秒数(整数),从1970-1-1 0:0:0 到现在的一个秒数.

 

 from_unixtime(时间戳, 日期格式): 将一个时间戳转成你指定的一个格式

 

(2) 介绍

在php的开发中，我们设计表时，保存一个日期有两种方法

1. 使用date/datetime/timestamp 表保存时间
2. 使用int unsigned 来保存时间(使用也很多， php程序员)

• 在mysql控制台实现

 

• php程序实现

 

思考, 如何查询在10秒/ 1.5天 / 2小时 内发布的消息

 

•  课后练习

 

 

9.3 字符串相关函数

•  基本介绍: 专门用于处理字符串的函数 。

 

• 举例说明

(1) charset(str) 返回字符串对应的字符集

 

(2) concat(str1, str2....) 用于连接字符串的

(3) ucase/lcase 字母的大小写的转换

 

(4) left(str, len) , 表示从str的左边，取出 len个字符串 / right(str,len) 从str的右边取出len个字符

 

(5) length(str); 统计str的长度(字节)

 

(6) replace(str, str_find, str_repl), 在str这个字符串中，找 str_find, 替换成 str_repl

 

(7) substring(str, position, [len]);  表从str的position 位置开始，取出len个字符，如果不写len，表示从position开始取出所有字符. positon 是从1开始计算.

 

小练习:

 

9.4 数字函数

• 基本介绍: 用于处理数值的函数

 

Round()

•  举例说明

(1) abs(num) , 求出num的绝对值

 

(2) ceiling(num), 对num这个值，向上取整, 尽量返回大一点的整数

 

(3) floor(num), 对num这个值，向下取整, 尽量返回小一点的整数

 

(4) format(num,num2), 将num进行格式化处理, 并保留到小数点num2位

 

(5) mod(num1,num2) 求出num1%num2

 

(6) rand() 返回一个0.0<=v<=1.0 的随机数

 

(7) round(num1,num2 ): 这个是对num1进行四舍五入的处理，num2是保留的小数点的位数.

 

 

9.5 流程控制函数

•  基本的介绍

 

•  案例说明
  
  表达式:

if(expr1, expr2, expr3); 如果expr1 表达式为真，则返回expr2, 如果expr1为假，则返回expr2  

举例:  请查询emp表，显示ename 和  sal, 和 com 如果com 为null，则显示0.0

 

• ifnull这里我们就不说，前面我们做了详细的介绍
• case....end 

 

当我们需要有多个分支判断时，就可以使用case ..when then.else.end

先看一个具体的需求:

如果该雇员的sal  在 小于等于 500 ，给工资涨 20%

如果该雇员的sal  在500< val<=1000 ，给工资涨 10%

如果该雇员的sal  在1000< val<=2000 ，给工资涨 5%

其它情况不涨工资, 使用一个select 语句搞定.

基本语法

CASE

WHEN expr THEN ....

WHEN expr THEN ....

WHEN expr THEN ....

ELSE ...

END;

完成:

 

 

9.6 其他函数

•  基本的介绍

 

•  举例说明

(1) user() 可以显示当前用户名

 

(2) database() 可以显示你当前正在操作哪个数据库

 

(3) md5(str), 将你给的字符串，加密/编码成 一个长度为32个字符的字符串, 经常他来保存我们的密码

 

(4) password(str), 可以将一个字符串，加密/编码成一个新的字符. 比如我们的mysql.user 表，密码就是 password加密.

• 课后练习题

 

 

---

 

第10章 mysql查询的加强(重点、难点)

10.1 查询加强的说明

 

 

10.2 查看表的结构

•  desc 表名;

•  show create table 表名；

 

10.3 使用算数表达式加强

? 显示每个雇员的年工资

 

10.4 where子句的加强

 

 

10.5 逻辑操作符的加强

 

 

10.6 使用order by子句

 

图解:

 

在进行排序，当第一个排序规则执行后，第二个排序规则，是建立在第一个排序的基础上的。

 

10.7 mysql的分页查询

•  我们的记录很多，但是我们实际查询时，不可能把所有的记录都返回，而且一页一页返回. 这时我们就会使用分页查询(limit)
•  基本语法

分页查询有两个重要的参数 $pageSize 表示一页显示几条记录

分页查询有两个重要的参数 $pageNow 表示显示第几页

select 列名 from 表名 LIMIT  ($pageNow-1) * $pageSize , $pageSize;

 

说明： mysql 的limit 的 ($pageNow-1) * $pageSize 是从0开始计算，因此如果 limit 0, 2 表示从第一记录开始取，取2条.

•  案例说明

•  课堂练习

课堂练习题:

按雇员的id号降序取出， 每页显示4条记录。

请分别显示 第3页，第5页 对应的sql语句

显示第3页:

select * from emp order by empno desc   LIMIT  8,4;

 

?年薪从低到高，的5-10名