本文共 12872 字,大约阅读时间需要 42 分钟。
丰富的函数能够让开发人员的工作事半功倍
MySQL 提供了丰富的内建函数帮助开发人员编写简单快捷的SQL 语句,其中常用的函数有字符串函数、日期函数和数值函数
在MySQL 中,函数可以用于SELECT 语句及其字句(WHERE、Order By、Having),也可用于 Update、Delete语句及其字句中
毋庸置疑,字符串函数是使用最频繁的函数
MySQL 中的字符串函数非常多,除了下面介绍的几个之外,还有trim、strcmp、repeat、ltirm、rtrim、lpad、rpad等,需要自己去学习
把传入的参数连接成一个字符串
mysql> select concat('aaa','bbb','ccc'),concat('aaa',NULL);+---------------------------+--------------------+| concat('aaa','bbb','ccc') | concat('aaa',NULL) |+---------------------------+--------------------+| aaabbbccc | NULL |+---------------------------+--------------------+
可以看出,任何字符串与NULL连接,结果都是NULL
将字符串str从第x位置开始,y个字符长的字符串替换成instr,x从1开始
mysql> select insert('beijing2008you',12,3,'me');+------------------------------------+| insert('beijing2008you',12,3,'me') |+------------------------------------+| beijing2008me |+------------------------------------+
将字符串转成小写或者大写
mysql> select lower('ME'),upper('me');+-------------+-------------+| lower('ME') | upper('me') |+-------------+-------------+| me | ME |+-------------+-------------+
用字符串b替换字符串str中出现的所有字符串a
mysql> select replace('2008mememe','m','y');+-------------------------------+| replace('2008mememe','m','y') |+-------------------------------+| 2008yeyeye |+-------------------------------+
返回从字符串str中的第x位置起y个字符长度的字符串,此函数经常用来对给定字符串进行字符串提示
mysql> select substring('2008me',5,2);+-------------------------+| substring('2008me',5,2) |+-------------------------+| me |+-------------------------+
所以数值函数就是处理数值运算的函数,如果MySQL中没有这些函数,就需要将数据取出来,在程序中进行运算
返回x的绝对值
mysql> select abs(8),abs(-8);+--------+---------+| abs(8) | abs(-8) |+--------+---------+| 8 | 8 |+--------+---------+
返回大于x的最小整数
mysql> select ceil(0.8),ceil(0.4);+-----------+-----------+| ceil(0.8) | ceil(0.4) |+-----------+-----------+| 1 | 1 |+-----------+-----------+
返回小于x的最大整数
mysql> select floor(1.8),floor(1.4);+------------+------------+| floor(1.8) | floor(1.4) |+------------+------------+| 1 | 1 |+------------+------------+
返回x/y的模
mysql> select mod(15,10),mod(1,11);+------------+-----------+| mod(15,10) | mod(1,11) |+------------+-----------+| 5 | 1 |+------------+-----------+
返回0~1的随机数,每次执行结果都不一样
mysql> select rand(),rand();+--------------------+--------------------+| rand() | rand() |+--------------------+--------------------+| 0.9647017679779807 | 0.8716893381175486 |+--------------------+--------------------+
利用此函数可以取任意范围内的随机数,如0~100,下面的命令获取的是0~100之间的任意随机整数
mysql> select ceil(100*rand());
返回参数x的四舍五入的有y位小数的值
如果是整数,则会保留y位数量的0,如果不写y,则y的默认值为0,即将x四舍五入后取整,此函数适合将所有数字保留同样小数位的情况
mysql> select round(1.1),round(1.1,2),round(1.25,1);+------------+--------------+---------------+| round(1.1) | round(1.1,2) | round(1.25,1) |+------------+--------------+---------------+| 1 | 1.10 | 1.3 |+------------+--------------+---------------+
用法与round一样,不过这里仅仅是截断,并不四舍五入
日期和时间函数有什么用?比如下面的需求
当前时间是多少
下个月的今天是星期节
统计截止到当前日期的3天收入总和等等
不都说,看吧例子
mysql> select curdate(),curtime(),now();+------------+-----------+---------------------+| curdate() | curtime() | now() |+------------+-----------+---------------------+| 2016-02-18 | 10:09:43 | 2016-02-18 10:09:43 |+------------+-----------+---------------------+
返回日期date的unix时间戳
mysql> select unix_timestamp(now()) -> ;+-----------------------+| unix_timestamp(now()) |+-----------------------+| 1455761536 |+-----------------------+
根据unix时间戳返回日期
mysql> select from_unixtime(1455761662);+---------------------------+| from_unixtime(1455761662) |+---------------------------+| 2016-02-18 10:14:22 |+---------------------------+
前者返回所给日期是当前的第一周,后者返回所给日期是哪一年
mysql> select week(now()),year(now());+-------------+-------------+| week(now()) | year(now()) |+-------------+-------------+| 7 | 2016 |+-------------+-------------+
分别返回所给事件的小时和分钟
mysql> select HOUR('14:22:20'),minute('22:22:36');+------------------+--------------------+| HOUR('14:22:20') | minute('22:22:36') |+------------------+--------------------+| 14 | 22 |+------------------+--------------------+
后面的参数也可以包含日期
mysql> select minute(now());+---------------+| minute(now()) |+---------------+| 19 |+---------------+
* MONTH()
返回月份
select month(now());
MONTHNAME()函数返回月份的英文名称
mysql> select monthname(now());+------------------+| monthname(now()) |+------------------+| February |+------------------+
返回日期
select day(now());
返回日期的英文名称
select dayname(now());
按字符串 fmt 格式化日期 date的值,此函数能够按指定的格式显示日期,可以用到的格式符如下
下面的例子将日期显示为 年 月 日 时 分 秒 格式
mysql> select date_format(NOW(),'%Y,%m,%d,%H,%i,%s');+----------------------------------------+| date_format(NOW(),'%Y,%m,%d,%H,%i,%s') |+----------------------------------------+| 2016,02,18,10,30,09 |+----------------------------------------+
换一种显示
mysql> select date_format(NOW(),'%Y年%m月%d日%H时%i分%s秒');+-----------------------------------------------------+| date_format(NOW(),'%Y年%m月%d日%H时%i分%s秒') |+-----------------------------------------------------+| 2016年02月18日10时31分25秒 |+-----------------------------------------------------+
这是一个很有用的函数,用来进行时间计算的,INTERVAL是间隔类型关键字,不能更改,expr是一个表达式,这个表达式跟后面的类型有关,type是间隔类型,下面是各种间隔类型
看下面的例子,分别在当前时间上加上2个小时,加上1年,加上1年零2个月,注意,最后的1_2要使用” 包含起来
mysql> select date_add(now(),INTERVAL 2 HOUR) H,date_add(now(),interval 1 year) y,date_add(now(),interval '1_2' year_month) y_m;+---------------------+---------------------+---------------------+| H | y | y_m |+---------------------+---------------------+---------------------+| 2016-02-18 12:46:52 | 2017-02-18 10:46:52 | 2017-04-18 10:46:52 |+---------------------+---------------------+---------------------+
当然,expr 也可以是负数,用于计算之前的时间
计算两个日期之间相差的天数
mysql> select datediff('2016-02-15',now());+------------------------------+| datediff('2016-02-15',now()) |+------------------------------+| -3 |+------------------------------+
由上面的例子可以看出,计算方式就是使用参数二的值减去参数1的值
除了上面的函数之外,还有流程函数,以及其他函数,如返回数据库版本,返回当前登录的用户名等,这里不做讲解,可以留作作业
前面已经介绍了最基本的SELECT 查询,即查询表中所有记录,以及去除重复记录,现在我们看是深入探讨MySQL 中的查询
为了使用下面要介绍的函数,也为了复习表的操作,先创建一张我们博客管理系统中需要的表,Blogs(博客表)
首先切换到CNBlog
mysql> create table Blogs(Blog_id int not null auto_increment, Blog_title varchar(32) not null, Blog_content varchar(100) not null, Blog_des varchar(1000) not null, Blog_publish datetime not null, Blog_User int not null, primary key(Blog_id));Query OK, 0 rows affected (0.22 sec)
插入数据
insert into Blogs(Blog_title,Blog_content,Blog_des,Blog_publish,Blog_User) values ('也谈json','json是用于数据交换的一种数据格式,是js的一个子集','json的简介','2013-12-20',1), ('js闭包','js闭包是js特有的一种语法特性','js闭包简介','2013-01-05',1), ('json基本语法','本节我们介绍js的基本语法......','课程目标','2014-05-06',2), ('js高级技术','本节我们介绍js的高级技术......','js高级技术简介','2015-05-06',3);
发现 发表时间 字段的数据只有日期没有时间,修改如下
mysql> update blogs set Blog_publish='2013-12-20 12:22:36' where Blog_id=1;Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> update blogs set Blog_publish='2013-01-05 14:22:36' where Blog_id=2;Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> update blogs set Blog_publish='2014-02-05 17:42:36' where Blog_id=3;Query OK, 1 row affected (0.07 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> update blogs set Blog_publish='2015-03-15 20:42:46' where Blog_id=4;Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0
大多时候,用户并不需要所有记录,而只是根据限定条件查询部分记录,使用where关键字可以实现这一点
代码如下:
mysql> select * from blogs where blog_id=2;+---------+------------+------------------------------------------+----------------+---------------------+-----------+| Blog_id | Blog_title | Blog_content | Blog_des | Blog_publish | Blog_User |+---------+------------+------------------------------------------+----------------+---------------------+-----------+| 2 | js闭包 | js闭包是js特有的一种语法特性 | js闭包简介 | 2013-01-05 14:22:36 | 1 |+---------+------------+------------------------------------------+----------------+---------------------+-----------+1 row in set (0.01 sec)
上面例子中,where后面是一个字段的=比较,除了 = 之外,还可以是>、<、>=、<=、!=等比较运算符;多个条件之间还可以使用 or、and 等逻辑运算符进行多条件联合查询
如看下面代码
select * from blogs where blog_user=1 and blog_publish>'2013-02-06';
我们经常会有如下需求:去除按某个字段进行排序后的记录结果集,MySQL 使用ORDER BY实现,如法如下
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC/ASC]],field2[DESC/ASC],....fieldn[DESC/ASC]]
其中,DESC和ASC是排序顺序关键字,DESC表示降序,ASC表示升序,如果没写排序关键字,则默认为ASC,order by后面可以跟多个排序字段,并且每个排序字段可以有不同的排序顺序
下面的命令将blogs表根据发表时间降序排序
select * from blogs order by blog_publish desc;
如果排序字段的值一样,则值相同的字段按照第二个排序字段进行排序,以此类推。如果只有一个排序字段,则这些字段相同的记录将会无序排列
select * from blogs order by blog_user,blog_publish asc;
对于排序后的记录,如果只希望显示一部分,而不是全部,就可以使用LIMIT关键字,语法如下:
SELECT…[LIMIT offset_start,row_count]
其中offset_start表示记录的起始偏移量,row_count表示显示的行数,在默认情况下,其实偏移量为0,只要写记录函数就可以,这时实际显示的就是前row_count条记录,
下面的命令显示blogs表的前2条记录
`mysql> select * from blogs order by blog_publish desc limit 2;+---------+------------------+-------------------------------------------+----------------------+---------------------+-----------+| Blog_id | Blog_title | Blog_content | Blog_des | Blog_publish | Blog_User |+---------+------------------+-------------------------------------------+----------------------+---------------------+-----------+| 4 | js高级技术 | 本节我们介绍js的高级技术...... | js高级技术简介 | 2015-03-15 20:42:46 | 3 || 3 | json基本语法 | 本节我们介绍js的基本语法...... | 课程目标 | 2014-02-05 17:42:36 | 2 |+---------+------------------+-------------------------------------------+----------------------+---------------------+-----------+`
下面命令显示排序后第3条记录开始的2条记录
select * from blogs order by blog_publish desc limit 2,2;
注意:limit经常与order by配合使用实现记录的分页,limit属于MySQL 对SQL标准的扩展,其他数据库中无法使用
很多情况下,用户需要进行一些汇总操作,,比如统计整个公司的人数,或者统计整个部门的人数,这是就要用到SQL的集合操作,语法如下
SELECT [field1,field2,....fieldn] fun_name FROM tablename[WHERE CONDITION][GROUP BY field1,field2,....fieldn[WITH ROLLUP]][HAVING CONDITION]
参数解释
注意:havind和where的区别在于,having是对聚合后的结果进行过滤,而where是在聚合前就进行过滤,如果条件允许,应尽可能是否where先过滤条件
为了测试聚合函数,我们先创建博客管理系统中的另外一张表,Blog_Type(博客类型表)
创建表
create table Blog_Type(Type_id int not null auto_increment,Type_name varchar(32) not null,primary key(Type_id),unique(Type_name));
录入数据
insert into Blog_Type(Type_name) values('前端'),('JAVA'),('NET'),('PHP'),('数据库');
更改Blogs表,添加Blog_Type字段和Blog_click(点击次数)
alter table blogs add column Blog_Type int;alter table blogs add column Blog_click int;
添加值
update blogs set Blog_Type=1 where blog_id=1;update blogs set Blog_Type=1 where blog_id=2;update blogs set Blog_Type=2 where blog_id=3;update blogs set Blog_Type=3 where blog_id=4;update blogs set Blog_click=3 where blog_id=1;update blogs set Blog_click=4 where blog_id=2;update blogs set Blog_click=24 where blog_id=3;update blogs set Blog_click=34 where blog_id=4;update blogs set Blog_click=10 where blog_id=5;update blogs set Blog_click=7 where blog_id=6;
为blogs表再添加一些测试数据
命令如下:
select count(1) from blogs;
命令如下
mysql> select blog_type,count(1) from blogs group by(blog_type);+-----------+----------+| blog_type | count(1) |+-----------+----------+| 1 | 2 || 2 | 1 || 3 | 2 || 4 | 1 |+-----------+----------+
这里需要用到 WITH ROLLUP关键字对分类后的结果再次进行汇总
mysql> select blog_type,count(1) from blogs group by(blog_type) with rollup;+-----------+----------+| blog_type | count(1) |+-----------+----------+| 1 | 2 || 2 | 1 || 3 | 2 || 4 | 1 || NULL | 6 |+-----------+----------+
命令如下,结果为类型1和3
mysql> select blog_type,count(1) from blogs group by(blog_type) having count(1)>1;+-----------+----------+| blog_type | count(1) |+-----------+----------+| 1 | 2 || 3 | 2 |+-----------+----------+
统计所有博客的总的点击量、最大点击量和最小点击量
mysql> select max(blog_click) m,min(blog_click) i,sum(blog_click) s from blogs;
+——+——+——+ | m | i | s | +——+——+——+ | 34 | 1 | 73 | +——+——+——+转载地址:http://pkxqj.baihongyu.com/