本文共 12872 字，大约阅读时间需要 42 分钟。

函数和查询

函数

丰富的函数能够让开发人员的工作事半功倍

MySQL 提供了丰富的内建函数帮助开发人员编写简单快捷的SQL 语句，其中常用的函数有字符串函数、日期函数和数值函数

在MySQL 中，函数可以用于SELECT 语句及其字句(WHERE、Order By、Having)，也可用于 Update、Delete语句及其字句中

字符串函数

毋庸置疑，字符串函数是使用最频繁的函数

MySQL 中的字符串函数非常多，除了下面介绍的几个之外，还有trim、strcmp、repeat、ltirm、rtrim、lpad、rpad等，需要自己去学习

• CONCAT(S1,S2,….Sn)

把传入的参数连接成一个字符串

mysql> select concat('aaa','bbb','ccc'),concat('aaa',NULL);+---------------------------+--------------------+| concat('aaa','bbb','ccc') | concat('aaa',NULL) |+---------------------------+--------------------+| aaabbbccc                 | NULL               |+---------------------------+--------------------+

可以看出，任何字符串与NULL连接，结果都是NULL

• INSERT(str,x,y,instr)

将字符串str从第x位置开始,y个字符长的字符串替换成instr，x从1开始

mysql> select insert('beijing2008you',12,3,'me');+------------------------------------+| insert('beijing2008you',12,3,'me') |+------------------------------------+| beijing2008me                      |+------------------------------------+

• LOWER(str)和UPPER(str)

将字符串转成小写或者大写

mysql> select lower('ME'),upper('me');+-------------+-------------+| lower('ME') | upper('me') |+-------------+-------------+| me          | ME          |+-------------+-------------+

• REPLACE(str,a,b)

用字符串b替换字符串str中出现的所有字符串a

mysql> select replace('2008mememe','m','y');+-------------------------------+| replace('2008mememe','m','y') |+-------------------------------+| 2008yeyeye                    |+-------------------------------+

• SUBSTRING(str,x,y)

返回从字符串str中的第x位置起y个字符长度的字符串，此函数经常用来对给定字符串进行字符串提示

mysql> select substring('2008me',5,2);+-------------------------+| substring('2008me',5,2) |+-------------------------+| me                      |+-------------------------+

数值函数

所以数值函数就是处理数值运算的函数，如果MySQL中没有这些函数，就需要将数据取出来，在程序中进行运算

• ABS(x)

返回x的绝对值

mysql> select abs(8),abs(-8);+--------+---------+| abs(8) | abs(-8) |+--------+---------+|      8 |       8 |+--------+---------+

• CEIL(x)

返回大于x的最小整数

mysql> select ceil(0.8),ceil(0.4);+-----------+-----------+| ceil(0.8) | ceil(0.4) |+-----------+-----------+|         1 |         1 |+-----------+-----------+

• FLOOR(x)

返回小于x的最大整数

mysql> select floor(1.8),floor(1.4);+------------+------------+| floor(1.8) | floor(1.4) |+------------+------------+|          1 |          1 |+------------+------------+

• MOD(x,y)

返回x/y的模

mysql> select mod(15,10),mod(1,11);+------------+-----------+| mod(15,10) | mod(1,11) |+------------+-----------+|          5 |         1 |+------------+-----------+

• RAND()

返回0~1的随机数，每次执行结果都不一样

mysql> select rand(),rand();+--------------------+--------------------+| rand()             | rand()             |+--------------------+--------------------+| 0.9647017679779807 | 0.8716893381175486 |+--------------------+--------------------+

利用此函数可以取任意范围内的随机数，如0~100，下面的命令获取的是0~100之间的任意随机整数

mysql> select ceil(100*rand());

• ROUND(x,y)

返回参数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 |+------------+--------------+---------------+

• TRUNCAT(x,y)

用法与round一样，不过这里仅仅是截断，并不四舍五入

日期和时间函数

日期和时间函数有什么用？比如下面的需求

当前时间是多少

下个月的今天是星期节

统计截止到当前日期的3天收入总和等等

• curdate、curtime、now

不都说，看吧例子

mysql> select curdate(),curtime(),now();+------------+-----------+---------------------+| curdate()  | curtime() | now()               |+------------+-----------+---------------------+| 2016-02-18 | 10:09:43  | 2016-02-18 10:09:43 |+------------+-----------+---------------------+

• UNIX_TIMESTAMP()

返回日期date的unix时间戳

mysql> select unix_timestamp(now())    -> ;+-----------------------+| unix_timestamp(now()) |+-----------------------+|            1455761536 |+-----------------------+

• FROM_UNIXTIME(unixtime)

根据unix时间戳返回日期

mysql> select from_unixtime(1455761662);+---------------------------+| from_unixtime(1455761662) |+---------------------------+| 2016-02-18 10:14:22       |+---------------------------+

• WEEK(DATE)、YEAR(DATE)

前者返回所给日期是当前的第一周，后者返回所给日期是哪一年

mysql> select week(now()),year(now());+-------------+-------------+| week(now()) | year(now()) |+-------------+-------------+|           7 |        2016 |+-------------+-------------+

• HOUR(time),MINUTE(tiime)

分别返回所给事件的小时和分钟

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         |+------------------+

• day()

返回日期

select day(now());

返回日期的英文名称

select dayname(now());

• DATE-FORMAT(date,fmt)

按字符串 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秒                          |+-----------------------------------------------------+

• DATE_ADD(date,INTERVAL,expr,type)

这是一个很有用的函数，用来进行时间计算的，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 也可以是负数，用于计算之前的时间

• DATEDIFF(date1.date2)

计算两个日期之间相差的天数

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关键字可以实现这一点

• 查询Blog_id为2的博客信息

代码如下：

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]

参数解释

• fun_name：聚合函数名称，常用的有sum、count、max和min
• GROUP BY:表示要进行分裂聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在group by后面
• WITH ROLLUP:可选语法，表示是否对聚合后的结果进行汇总
• HAVING:表示对分类后的结果在进行过滤

注意：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的博客类型

命令如下，结果为类型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/