格式化结果集

格式化结果集是铜鼓一些函数，将结果集中的某列数据进行格式化，以方便用户查看。例如将结果集中的日期进行格式化，对小数的位数进行格式化等

1. 格式化日期

在mysql中，对日期进行格式化使用的是DATE_FROMAT()函数，语法

DATE_FORAMT(date,format)

参数说明

• date : 一个合法的日期
• format : 规定日期/时间的输出格式

格式如下所示：

格式	说明
%a	简写的英文日期
%b	简写的英文月份
%c	数值表示月份
%D	表示带有英文后缀的月中第几天
%d	表示月中的第几天（00-31）
%e	表示月中的第几天（0-31）
%f	微秒数
%H	小时数（00-23）
%h	小时数（01-12）
%I	小时数（01-12）
%i	分钟数（00-59）
%j	表示年中的第几天（001-366）
%k	小时数（0-23）
%l	小时数（1-12）
%M	完整的英文月份
%m	月份（00-12）
%p	AM或者PM
%r	12小时制表示时间（hh:mm:ss AM或者PM)
%S	秒数（00-59）
%s	秒数（00-59）
%T	24小时制代表的时间(hh:mm:ss)
%U	年中的第几周（00-53）星期日是一周的第1天
%u	年中的第几周（00-53）星期1是一周的第1天
%W	完整的英文星期
%w	一周的第几天（0表示的是星期日）
%Y	4位数表述年份
%y	2位数表示年份

SELECT
	id,
	`name`,
	original_price,
	current_price,
	DATE_FORMAT(addtime, '%Y/%m/%d') AS '注册时间'
FROM
	goods
LIMIT 6;

2. 数据表的数值类型的转换

在数据表中，某些数值会以字符串的类型保存，而在使用的时候，可能需要数值的类型，可以使用CAST()函数进行转换

CAST(expr AS type)

• expr : 表达式

• type : 目标系统所提供的数据类型，

SELECT CAST('2365' AS int);

模糊查询

在对象表中的数据进行查询的时候，常常会用到模糊查询，模糊查询可以轻松查询到比较模糊数据

1. LIKE 谓词

使用LIKE 或者NOT LIKE谓词来指定模糊查询条件。定义模糊查询条件需要使用通配符在字符串内查询指定的搜索模式

常用的通配符

通配符	说明
%	由零个或者多个字符组成的任意字符串
_	任意单个字符
[ ]	用于指定范围，例如[A-F],表示A到F范围内的任意字符
[^ ]	表示指定的范围之外的，例如[^A-F] ,表示A到F范围以为的任意单个符

LIKE谓词通过使用通配符可以指定任何的模糊查询

查询某列中包含mr字符的数据，可以出现在任何位置

WHRER 列名 LIKE '%mr%'

查询出某列包含mr字符的数据，其中mr出现在开头的位置

WHERE 列名 LIKE 'mr%'

查询出某列包含mr字符的数据，其中mr出现在结尾的位置

WHERE 列名 LIKE 'mr_'

查询出后两个字符为mr的数据

WHERE 列名 LIKE '_mr'

查询出某列中以m字段或者r字符开头的数据

WHERE 列名 LIKE '[mr]%'

查询出某列以a~c之间的字符开头的数据，其中包括a和e

WHERE 列名 LIKE '[a-e]$'

查询某列不是以a~c字符开头的数据，其中包括a和e

WHERE 列名 LIKE '[^mr]'%

查询出某列不是以a-c的字符开头的数据

WHERE 列名 LIKE '[^a-c]%'

2. %通配符的使用

%通配符表示由零个或者多个字符组成的字符串，在使用LIKE查询的时候，可以在查询条件的任何位置使用%来通配查询任意长度的字符串，也可以使用两个%%通配符进行查询

SELECT id,`name`,original_price   FROM goods
WHERE `name` LIKE '%西门子%';

3. _通配符的使用

_ 通配符表示任意单个字符，该通配符只能匹配一个字符，可以出现在查询条件的任意位置，也可以出现多个

SELECT id,LTRIM(`name`) AS name ,original_price   FROM goods
WHERE LTRIM(`name`) LIKE '_ _ _';

4. [ ]通配符

[ ]表示匹配指定的范围或者集合中的任意当字符，包括指定范围两端的字符

SELECT id, `name`,introduction ,current_price FROM goods LIMIT 6
WHERE `name` LIKE '[A-Z]%';

SELECT id,`name`,currentprice FROM goods
WHERE `name` LIKE '%[6-9]

5.[ ^]通配符

[ ^]用来查询不在指定范围内的任何单个字符

SELECT TOP 6 id,name,cat_name
FROM brand
WHERE name LIKE '[^A-Z]%';

6 . 使用ESCAPE定义转义字符

在使用通配符查询数据时，数据可能包含着通配符。当把定义的转义字符放在通配符之间的时候，该通配符被解释为普通字符

SELECT id ,`name`,introduction  FROM goods
WHERE introduction LIKE '%/_%' ESCAPE '/';

行数据过滤

在对数据进行查询的时候，有时候需要查询出表中某一行数据，或者对数据的空值进行处理

行查询

1. 查询指定行

查询表中第n行数据,可以先将前n行的数据查询出来，再将表中的前n-1行数据查询出来，最后通过使用NOT EXIST操作将表中第N行的数据显示出来

SELECT
	id,
	`name`,
	current_price
FROM
	(SELECT * FROM goods LIMIT 6) AA
WHERE
	NOT EXISTS (
		SELECT
			*
		FROM
			(SELECT * FROM goods LIMIT 5) BB
		WHERE
			AA.id = BB.id
	)

2. 随机查询一行数据

随机查询可以在表中随机查询抽出几行数据，供用户查看

在mysql中，随机查询可以通过RAND()函数来实现，它可以生成一个0~1之间的随机数

SELECT id,name,current_price FROM goods
ORDER BY RAND() LIMIT 5;

3. 在结果集中添加行号

在结果集中添加行号，可以方便查看

--- id 按照从大到小顺序重新排序，并对每行的数据进行编号 ---
SELECT
	(
		SELECT
			COUNT(id)
		FROM
			goods A
		WHERE
			A.id >= B.id
	) id,
	`name`,
	introduction,
	current_price
FROM
	goods B
ORDER BY
	1;

2 空值的判断

空值从技术上来说就是”未知的值“。但是空值并不包括0，一个或者多个空格组成的字符串，以及零长度的字符串。在实际应用中，空值还说明没有向数据库输入相应的数据，或者某个特定的记录行不需要使用该列。在实际操作中，下列几种情况可能使一列成为空值。

• 其值未知。例如课程表中不明确具体的课程内容
• 其值不存在。例如学习表中某个学生没有参数考试，所以该学生的考试成绩为空值
• 列对表行不可用

1. 查询空值(IS NULL)

在表中有可能会出现空值，由于空值是未知的值，在对其进行查询的时候不可以像查询其他的值那样用比较或运算等。此时可以用IS NULL 操作符对空值进行查询，它可以查询到列中所有的控制

SELECT id,`name`,introduction FROM goods
WHERE introduction IS NULL;

2. 查询非空值（IS NOT NULL)

SELECT id,`name`,introduction FROM goods
WHERE introduction IS NOT NULL;

3. 对空值进行处理

通常需要把空值转化成一个有效的值，便于对数据的理解，或者防止表达式出错。 ISNULL()函数可以对控制转换成为有效的值，而NULLIF()函数可以根据指定的条件来生成空值

SELECT id,`name`,ISNULL(addtime) AS time FROM goods

列的数值不为NULL的时候，ISNULL()函数将返回原值，当为NULL的时候，ISNULL()函数将对其进行处理，用数值来替代

数据排序

理论上讲SLECET语句所返回的结果集都是无序的，结果集中的记录之间顺序主要取决于物理位置，对结果集的排序的唯一方式就是在SELECT查询中嵌入ORDER BY子语句，ORDER BY 子语句用来指定最后结果集中的顺序

1. 数值排序

1. 按照升序和降序排列

当使用SELECT语句查询数据表的时候，数据库系统并不能将数据以特定顺序呈现给用户，如果查询的表的数据比较大，则用户浏览起来很不方便，考虑到效率问题，在SQL中读取数据表的行时引入了一种索引的方法，结果集的行可能就会按照索引的顺序进行排列。

如果想控制每行的数据在结果集中的出现的顺序，可以向SELECT语句添加ORDER BY子句。 它使数据库对查询结果排序，这样就无需对自己编写的应用程序进行手工排序，ODEDER BY 子句必须放在查询语句的末尾，语法如下

ORDER BY { oder_by_expression [ ASC | DESC ] } [,.....n]

参数说明

参数	描述
order_by_expression	指定要排序的列，可以将排序的列指定为列名或者列的别名（可以由表名或者视图名限定）和表达式，或者指定为代表列选择列表内的名称，别名或者表达式的位置的负整数。可以指定多个排序列，ORDER BY子句的排序定义排序结果集的结构
ORDER_BY	子句可以包括未出现在此选择列表的项目，而且如果指定SELECT DISTINCT或者如果SELECT 语句包含union运算符的时候，列名或者列的别名必须是第一选择列表内的列名或者列的别名
ASC	指定按递增顺序（升序）,从低到高排序，默认是递增顺序
DESC	指定按递减顺序（降序）,从高到底排序

# 对商品价格进行降序排列
SELECT id,`name` , current_price FROM goods
ORDER BY current_price DESC;

# 对商品价格进行升序排列
SELECT id,`name` , current_price FROM goods
ORDER BY current_price ASC;

2. 使用别名排序

# 对商品价格进行排列,别名
SELECT id 编号,`name` 名称, current_price 价格 FROM goods
ORDER BY current_price DESC;

# 对商品价格进行排列,别名如果出现空格，则需要使用引号
SELECT id 编号,`name` 名称, current_price `商品 价格` FROM goods
ORDER BY current_price  DESC;

3. 对多列排序

有时按照一列进行排序之后，如果查询的记录比较多，那么查询结果可能依然不是很清晰，此时可以按照多个列进行排序。SQL指定多列排序，初级排序是对查询结果进行分类排序，第二级排序是对初级排序分好类的数据再次排序，以此类推

实际编程中经常需要对多列进行数据排序，例如要显示员工清单，可能需要按照姓名进行排序（首先按照姓排序，然后再对每一个姓的集合中按照名字排序），对于多个员工具有相同姓的情况，这样做很有意义

如果需要对多列进行字段排序，可以对所有要用于排序的列加以限制，当对多列数据进行排序时，优先顺序应该从左到右依次降低，所以在查询语句的ODRDER BY语句的各个列排序很重要

# 按照商品的价格列和商品名称列对结果进行排序
# 首先按照价格排序，然后按照名称排序
SELECT id,`name`,current_price   FROM goods
ORDER BY current_price,`name`;

对多列排序时，排序顺序有一定的规则。实际上，如果对主排列没有重复值，则不行行次排列，如果在主排列中出现重复值，则需要进行次排列

说明，在ORDERY BY子句中的第一列为主排序，第二列为次排序

如果希望某个列上进行降序列，而在其他列上进行升序排列，可以使用DESC关键字对希望降序排列进行限制，如果要对多个列进行降序排序，必须对每个指定DESC关键字

4. 对数据表中的指定行进行排序

#对数据表的前几行数据进行排序
SELECT id,`name`,current_price FROM goods 
ORDER BY current_price DESC LIMIT 3;

#对数据表的后几行数据进行排序
SELECT id,`name`,current_price FROM goods 
ORDER BY current_price  LIMIT 3;