MySQL数据库中提供了很丰富的函数,比如我们常用的聚合函数,日期及字符串处理函数等。SELECT语句及其条件表达式都可以使用这些函数,函数可以帮助用户更加方便的处理表中的数据,使MySQL数据库的功能更加强大。本篇文章主要为大家介绍几类常用函数的用法:
一、聚合函数
聚合函数是平时比较常用的一类函数,这里列举如下:
- COUNT(col) : 统计查询结果的行数
- MIN(col): 查询指定列的最小值
- MAX(col): 查询指定列的最大值
- SUM(col): 求和,返回指定列的总和
- AVG(col): 求平均值,返回指定列数据的平均值
注意:select 查询列中如果出现聚合函数,则不能出现其他字段,除非其他字段是参与了分组的字段:
当一条sql语句中包含有group by,select后面只能跟分组函数和参加分组的字段
select job avg(sal) from emp group by job; #找出每个工作岗位的平均薪资
二、数值型函数
数值型函数主要是对数值型数据进行处理,得到我们想要的结果,常用的几个列举如下:
- CEILING(x): 返回大于x的最小整数值,向上取整
- FLOOR(x): 返回小于x的最大整数值,向下取整
- ROUND(x,y): 返回参数x的四舍五入的有y位小数的值 四舍五入
- TRUNCATE(x,y): 返回数字x截短为y位小数的结果
- PI(): 返回pi的值(圆周率)
- RAND(): 返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
一些示例:
# ABS()函数求绝对值
SELECT ABS(5),ABS(-2.4),ABS(-24),ABS(0);
# 取整函数 CEIL(x) 和 CEILING(x) 的意义相同,返回不小于 x 的最小整数值
SELECT CEIL(-2.5),CEILING(2.5);
# 求余函数 MOD(x,y) 返回 x 被 y 除后的余数
SELECT MOD(63,8),MOD(120,10),MOD(15.5,3);
# RAND() 函数被调用时,可以产生一个在 0 和 1 之间的随机数
SELECT RAND(), RAND(), RAND();
三、字符串函数
字符串函数可以对字符串类型数据进行处理,在程序应用中用处还是比较大的,同样这里列举几个常用的如下:
- LENGTH(s): 计算字符串长度函数,返回字符串的字节长度
- CONCAT(s1,s2...,sn): 合并字符串函数,返回结果为连接参数产生的字符串,参数可以是一个或多个
- LOWER(str): 将字符串中的字母转换为小写
- UPPER(str): 将字符串中的字母转换为大写
- LEFT(str,x): 返回字符串str中最左边的x个字符
- RIGHT(str,x): 返回字符串str中最右边的x个字符
- TRIM(str): 删除字符串左右两侧的空格
- REPLACE: 字符串替换函数,返回替换后的新字符串 REPLACE(name,'白','黑')
- SUBSTRING: 截取字符串,返回从指定位置开始的指定长度的字符换
- REVERSE(str): 返回颠倒字符串str的结果
一些示例:
```# LENGTH(str) 函数的返回值为字符串的字节长度
SELECT LENGTH('name'),LENGTH('数据库');
# CONCAT(sl,s2,...) 函数返回结果为连接参数产生的字符串 若有任何一个参数为 NULL,则返回值为 NULL
SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL);
# INSERT(s1,x,len,s2) 返回字符串 s1,子字符串起始于 x 位置,并且用 len 个字符长的字符串代替 s2
SELECT INSERT('Football',2,4,'Play') AS col1,INSERT('Football',-1,4,'Play') AS col2;
# UPPER,LOWER是大小写转换函数
SELECT LOWER('BLUE'),LOWER('Blue'),UPPER('green'),UPPER('Green');
# LEFT,RIGHT是截取左边或右边字符串函数
SELECT LEFT('MySQL',2),RIGHT('MySQL',3);
# REPLACE(s,s1,s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1
SELECT REPLACE('aaa.mysql.com','a','w');
# 函数 SUBSTRING(s,n,len) 带有 len 参数的格式,从字符串 s 返回一个长度同 len 字符相同的子字符串,起始于位置 n
SELECT SUBSTRING('computer',3) AS col1,SUBSTRING('computer',3,4) AS col2,
SUBSTRING('computer',-3) AS col3,SUBSTRING('computer',-5,3) AS col4;
+--------+------+------+------+
| col1 | col2 | col3 | col4 |
+--------+------+------+------+
| mputer | mput | ter | put |
+--------+------+------+------+
四、日期和时间函数
获取时间和日期
- 【CURDATE】 和 CURRENT_DATE】 两个函数作用相同,返回当前系统的【日期值】
- 【CURTIME 和 CURRENT_TIME】 两个函数作用相同,返回当前系统的【时间值】
- 【NOW】 和 【SYSDATE】 两个函数作用相同,返回当前系统的【日期和时间值】
时间戳或日期转换函数:
- 【UNIX_TIMESTAMP】 获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数
- 【FROM_UNIXTIME】 将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数
根据日期获取年月日的数值
- 【MONTH】 获取指定日期中的月份
- 【MONTHNAME】 获取指定日期中的月份英文名称
- 【DAYNAME】 获取指定曰期对应的星期几的英文名称
- 【DAYOFWEEK】 获取指定日期对应的一周的索引位置值
- 【WEEK】 获取指定日期是一年中的第几周,返回值的范围是否为 0〜52 或 1〜53
- 【DAYOFYEAR】 获取指定曰期是一年中的第几天,返回值范围是1~366
- 【DAYOFMONTH】 获取指定日期是一个月中是第几天,返回值范围是1~31
- 【YEAR】 获取年份,返回值范围是 1970〜2069
时间日期的计算
- 【DATE_ADD】 和 【ADDDATE】 两个函数功能相同,都是向日期添加指定的时间间隔
- 【DATE_SUB】 和【 SUBDATE】 两个函数功能相同,都是向日期减去指定的时间间隔
- 【ADDTIME】 时间加法运算,在原始时间上添加指定的时间
- 【SUBTIME】 时间减法运算,在原始时间上减去指定的时间
- 【DATEDIFF】 获取两个日期之间间隔,返回参数 1 减去参数 2 的值
- 【DATE_FORMAT】 格式化指定的日期,根据参数返回指定格式的值
当使用了表达式计算后,不能直接使用别名进行判断了。
一些示例:
# CURDATE() 和 CURRENT_DATE() 函数的作用相同,将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回
mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;
# MONTH(date) 函数返回指定 date 对应的月份
SELECT MONTH('2017-12-15');
# DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 两个函数的作用相同,都是用于执行日期的加运算。
SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1,DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' HOUR) AS C2, ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3;
# DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数
SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,DATEDIFF('2017-11-30','2017-12-15') AS col2;
# DATE_FORMAT(date,format) 函数是根据 format 指定的格式显示 date 值
SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1,DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2;
五、加密函数
- MD5() 计算字符串str的MD5校验和
SELECT MD5('abc');
结果:900150983cd24fb0d6963f7d28e17f72
六、流程控制函数
流程控制类函数可以进行条件操作,用来实现SQL的条件逻辑,允许开发者将一些应用程序业务逻辑转换到数据库后台,列举如下:
- IF(test,t,f): 如果test是真,返回t;否则返回f
- IFNULL(arg1,arg2): 如果arg1不是空,返回arg1,否则返回arg2
- NULLIF(arg1,arg2): 如果【arg1=arg2】返回NULL,否则返回arg1
SELECT NULLIF('abc','abc'); #返回null
SELECT NULLIF('abc','abcd'); #返回abc
通过对某一列的值进行判断,
- CASE [test] WHEN[val1] THEN [result]…ELSE [default] END:
如果test和valN相等,则返回resultN,否则返回default
创建表和数据如下:
例子一:输出学生各科的成绩,以及评级,60以下是D,60-70是C,71-80:是B ,80以上是A
结果集如下所示:
例子二:行转列案例,要求根据上边的表结构,查询出如下结果::
sql语句如下:
sql注释:以第一行举例,其意思是通过分组之后,如果取到的科目是数学,则取出他的分数值,否则输出0,然后取分数中的最大值
六、数据库设计
一、三范式
注:设计只是一种思想一种理念,我们按照规范的设计方式设计数据库对我们来说有好处,但绝对不是说一定要严格遵守,三范式能极大的减少数据冗余,但是相对编写sql而言是增加了难度的,所以所有好的设计都是要权衡利弊的,要对编码难度,存储大小,执行效率等多方面进行综合考量,但是在学习初期最好紧紧的遵循三范式,在后续的编码中体会和总结自己的经验。
设计数据库表的时候所依据的规范,共三个规范:
- 第一范式:要求有主键,并且要求每一个字段原子性不可再分
- 第二范式:要求所有非主键字段完全依赖主键,不能产生部分依赖
- 第三范式:所有非主键字段和主键字段之间不能产生传递依赖
第一范式
数据库表中不能出现重复记录,每个字段是原子性的不能再分
不符合第一范式的实例:
不符合第一范式的实例,不是说他错哈:
必须有主键,这是数据库设计的最基本要求,主要采用数值型或定长字符串表示,关于列不可再分,应该根据具体的情况来决定
。如联系方式,为了开发上的便利可能就采用一个字段。
关于第一范式,每一行必须唯一,也就是每个表必须有主键,这是数据库设计的最基本要求,主要采用数值型或定长字符串表示,关于列不可再分,应该根据具体的情况来决定
。如联系方式,为了开发上的便利可能就采用一个字段。
2、第二范式
第二范式是建立在第一范式基础上的,另外要求所有非主键字段完全依赖主键,不能产生部分依赖
不符合第二范式的案例:
其中学生编号和课程编号为联合主键
以上虽然确定了主键,但此表会出现大量的数据冗余,出现冗余的原因在于,学生信息部分依赖了主键的一个字段学生编号,和课程id没有毛线关系。同时课程的信息只是依赖课程id,和学生id没有毛线关系。只有成绩一个字段完全依赖主键的两个部分,这就是第二范式部分依赖。
解决方案:
学生表:学生编号为主键
课程表:课程编号为主键
成绩表:学生编号和课程编号为联合主键
如果一个表是单一主键,那么它就是复合第二范式,部分依赖和主键有关系
以上是典型的“多对多”设计
3、第三范式
建立在第二范式基础上的,非主键字段不能传递依赖于主键字段(不要产生传递依赖)
不满足第三范式的例子:
其中学生编号是主键
何为传递依赖?
专业编号依赖学生编号,因为该学生学的就是这个专业啊。但是专业名称和学生其实没多大关系,专业名称依赖于专业编号。这就叫传递依赖,就是某一个字段不直接依赖主键,而是依赖 依赖主键的另一个字段。
解决方法:
学生表,学生编号为主键:
学生编号为主键:
以上设计是典型的一对多的设计,一存储在一张表中,多存储在一张表中,在多的那张表中添加外键指向一的一方。
二、常见表关系
1、一对一 用的不多
一个表和另一张表存在的关系是一对一,此种设计不常用,因为此种关系经常会将多张表合并为一张表。
举例:
学生信息表可以分为基本信息表和详细信息表。
可能有这种需求,需要给个某个账户对学生表的操作,但是有些私密信息又不能暴露,就可以拆分。
第一种方案:分两张表存储,共享主键
第二种方案:分两张表存储,外键唯一
2、一对多
第三范式的例子
两张表 外键建在多的一方
1.分两张表存储,在多的一方添加外键,
2.这个外键字段引用一的一方中的主键字段
3、多对多
第二范式的例子
分三张表存储,在学生表中存储学生信息,在课程表中存储课程信息,
在成绩表中存储学生和课程的关系信息,这里的成绩表其实就是中间表