目录
(1)查询员工信息时,将部门编号为30的员工姓名、员工职位及员工所属部门编号的信息在一列中显示,各个字段值之间使用下画线“_”进行连接
(2)查询员工信息时,将部门编号为30的员工姓名、员工奖金及员工部门编号的信息在一列中显示,各个字段值之间使用下画线“_”进行连接
学习MySQL的高级查询中的聚合函数、排序查询、限量查询、内置函数
一、聚合函数
聚合函数的基本语法格式:
SELETC [ 字段名1, 字段名2 , ... , 字段名n] 聚合函数 FROM 数据表名 ;
MySQL常用的聚合函数
1、COUNT( ) 函数
COUNTO函数用于检索数据表行中的值的计数,COUNT(*)可以统计数据表中记录的总条数,即数据表中有多少行记录。
创建一个表,插入数据并查询
查询员工表中有多少个员工的记录(三种方式)
(1)COUNT( * ) 查询
(2)COUNT( ename ) 查询
COUNT( ename ) 中的字段ename 可以更改。
(3)COUNT( ename ) 更换字段名查询
2、SUM( ) 函数
如果字段中存放的是数值型数据,需要统计该字段中所有值的总数,可以使用SUM( )函数。SUM( )函数会对指定字段中的值进行累加,并且在数据累加时忽略字段中的NULL值。
查询员工表中员工奖金的总和。
3、AVG( )函数
如果字段中存放的是数值型数据,需要统计该字段中所有值的平均值,可以使用AVG( )函数。AVG( ) 函数会计算指定字段值的平均值,并且计算时会忽略字段中的NULL值。
1、查询员工表中员工的平均奖金
2、IFNULL( ) 函数
如果想要统计所有员工的平均奖金,即奖金平均到所有员工身上,可以借助
IFNULLO函数。
声明格式:IFNULL(V1,v2)
如果v1的值不为NULL,则返回v1的值,否则返回v2。
上述SELECT语句在执行AVG( )函数之前调用IFNULLO函数对COMM字段中的值进行判断,如果是NULL值就转换成0返回;由执行结果并结合数据表中的数据可以得出,本次统计的平均奖金是所有员工的平均奖金。
4、MAX函数
MAX函数用于计算指定字段中的最大值,如果字段的值是数值类型,则比较的是值的大小。
查询员工表中最高的工资
获取工资最高
select 语句嵌套
5、MIN( ) 函数
MIN( )函数用于计算指定字段中的最小值,如果字段的值是数值类型,则比较的是
值的大小。
查询员工表中最低的工资
SELECT 字段1 , 字段2 ,[ 表达式 ]...
FROM 数据表名
GROUP BY 字段名1 , 字段名2 , ... [ WAVING 条件表达式 ] ;
二、分组查询
在对数据表中的数据进行统计时,有时需要按照一定的类别作统计。例如,财务在统计每个部门的工资总数时,属于同一个部门的所有员工就是一个分组。在MySOL中,可以使用GROUP BY根据指定的字段对结果集进行分组,如果某些记录的指定字段身有相同的值,那么分组后被合并为一条数据。
语法格式
SELECT 字段名1 , 字段名2 ,[ 表达式 ]...
FROM 数据表名
GROUP BY 字段名1 ,字段名2 ,...[ HAVING 条件表达式 ];
在上面的语法格式中,GROUPBY后指定的字段名1和字段名2是对数据分组的依据,HAVING关键字指定条件表达式对分组后的内容进行过滤。
1、单独使用GROUP BY分组
单独使用GROUP BY进行分组时将根据指定的字段合并数据行。
查询员工表的部门编号有哪几种
2、GROUP BY 和聚合函数一起使用
如果分组查询时要进行统计汇总,此时需要将GROUPBY和聚合函数一起使用。
统计员工表各部门的薪资总和或平均薪资
在上述SELECT语句中,使用GROUP BY根据deptno字段中的值对数据表的记录进行分组,值相同的为一组。由执行结果可知,返回了不同部门的平均工资和工资总和。
3、GUOUP BY 和 HAVING 关键字一起使用
通常情况下GROUP BY和HAVING关键字一起使用,用于对分组后的结果进行条件过滤。
查询员工表中平均工资小于3000的部门编号及这些部门的平均工资
三、排序查询
对数据表的数据进行查询时,可能查询出来的数据是无序的,或者其排列顺序不是用户期望的。
语法格式
SELECT *| { 字段名1 , 字段名2 , ...}
FROM 数据表名
ORDER BY 字段名1 [ ASC | DESC ] , 字段名2 [ ASC | DESC ]...;
在上面的语法格式中,ORDER BY后指定的字段名1、字段名2等是对查询结果排序的依据,即按照哪一个字段进行排序。参数ASC表示按照升序进行排序,DESC去示按照降序进行排序。
使用ORDERBY对查询结果进行排序时,如果不指定排序方式,默认按照ASC式进行排序。
1、升序
查询员工表中部门编号为30的员工信息,查询出的结果根据员工工资升序排列
2、降序
查询员工表中部门编号为30的员工信息,查询出的结果根据工资降序排列
3、按照指定的字段进行升序
按照指定字段进行排序时,如果字段的值中包含NULL,NULL会被当作最小值进行排序。
查询员工表中部门编号为30的员工信息,并且根据奖金升序排序
上述SELECT语句查询部门编号为30的员工信息,并且根据员工奖金值进行升序排序。从执行结果可以看出,奖金值为NULL的员工信息排在第一位,说明排序时NULL被当作最小值。
4、对多个字段的值进行排序
ORDER BY可以对多个字段的值进行排序,并且每个排序字段可以有不同的排序顺序。
查询员工表中部门编号为30的所有记录,查询出的记录先按职位升序排序,职位相同的记录再按员工编号降序排序
在上述SELECT语句中,查询deptno字段值为30的所有记录,先将这些记录按照job字段的值升序排序,如果job字段的值相同,则按照empno字段的值进行降序排序。如果排序字段的值是字符串类型,则会按字符串中字符的ASCII码值进行排序。
四、限量查询
查询数据时,SELECT语句可能会返回很多条记录,而用户需要的记录可能只是其中的一条或几条。
语法格式
SELECT 字段名1 , 字段名2 , ...
FROM 数据表名
LIMIT [ OFFSET ,] 记录数 ;
在上面的语法格式中,LIMIT后面可以跟2个参数。第一个参数OFFSET为可选值,表示偏移量,如果偏移量为0则从查询结果的第一条记录开始,偏移量为 1 则从查询结果的第二条记录开始,以此类推。如果不指定OFFSET的值,其默认值为0。第二个参数“ 记录数 ”表示返回查询记录的条数。
1、查询员工表中工资最高的前5名的员工信息
在上述SELECT语句中,首先使用ORDER BY根据字段sal的值对数据表中的记录进行降序排序,接着使用LIMIT指定返回第1~5条记录。
2、查询员工表中工资前2-5名的员工信息
除指定查询记录数,LIMIT还可以通过指定OFFSET的值指定查询的偏移量,也就是查询时跳过几条记录。
在上述SELECT语句中,先使用ORDERBY根据字段sal的值对数据表中的记录进行降序排序,然后指定返回记录的偏移量为1,查询记录的条数为4。执行结果跳过了排序后的第一条员工信息,返回工资前2~5名的员工信息。
五、内置函数
为提高用户对数据库和数据的管理和操作效率,MySQL提供了大量的内置函数供开发者使用。内置函数也可以称为系统函数,无须开发者定义,直接调用即可。这些内置函数从功能方面划分,可分为数学函数、字符串函数、日期和时间函数、条件判断函数、加密函数等。
数字函数
字符串函数
日期和时间函数
条件判断函数
加密函数
1、CONCAT( ) 函数
执行CONCATO函数会返回函数参数连接之后的字符串。例如执行CONCAT(' a ' , '_' , ' b '),会返回字符串' a_b '。
(1)查询员工信息时,将部门编号为30的员工姓名、员工职位及员工所属部门编号的信息在一列中显示,各个字段值之间使用下画线“_”进行连接
(2)查询员工信息时,将部门编号为30的员工姓名、员工奖金及员工部门编号的信息在一列中显示,各个字段值之间使用下画线“_”进行连接
需要注意的是,CONCAT(strl,str2,..)的返回结果为所有参数连接后组成的字符串。如果CONCAT( )函数有任何一个参数为NULL,则返回值为NULL。
2、IF( ) 函数
IF( ) 函数有3个参数,具体格式为IF(expr,v1,v2)。如果表达式expr成立,返回结果v1;否则返回结果v2。
查询员工表中部门编号为30的员工姓名、员工奖金及员工部门编号的信息;如果奖金为NULL,则返回“ 无奖金 ”
例如:其中成绩>60分的该字段返回合格,成绩<60分的返回不合格
六、设置别名
在查询数据时,可以为数据表和字段取别名,可使用这个别名代替原来的数据表名和字段名。
1、为数据表设置别名
在进行查询操作时,如果数据表名很长或者需要执行一些特殊查询,为方便操作,可以为数据表取一个别名,用这个别名代替数据表的名称。
语法格式
SELECT * FROM 数据表名 [ AS ] 别名 ;
在上面的语法格式中,AS为可选项,用于指定数据表的别名。
查询员工信息时,为emp数据表起一个别名。并且使用别名e查询部门编号为30的员工信息
在上述SELECT语句中,emp e表示为emp数据表定义别名为e,此时在语句中使用别名e和数据表名emp有相同的效果;e.deptno=30表示筛选emp数据表中deptno字段的值为30的记录。由执行结果可知,上述SELECT语句返回的都是部门编号为30的记录。
2、为字段设置别名
在前面的查询操作中,每条记录中的列名都是定义表时的字段名,有时为了将查询
结果更加直观地显示,可以为查询的字段取一个别名。
语法格式
SELECT 字段名 [ AS ] 别名 [, 字段名 [ AS ] 别名,... ] FROM 数据表名 ;
在上面的语法格式中,AS为可选项,用于指定字段的别名。
查询员工表中部门编号为30的员工姓名、员工奖金及员工部门编号的信息,查询结果返回时将字段ename的名称设置别名“ 姓名 ”,字段comm的名称设置别名“ 奖金 ”,字段deptno的名称设置别名“ 部门编号 ”。
在上述SELECT语句中,使用WHERE子句查询出部门编号为30的记录后,SELECT关键字后的字段名使用对应的别名显示。由执行结果可知,返回结果的字段名都用别名替代了。