MYSQL必知必会 再捋[6-12]（二）

--------------------------------------------------------------理解 练习----------------------------------------------------------

(六) 过滤数据

1 使用where子句

数据表中根据特定条件查询：

只检索所需数据需要指定 搜索条件（也称过滤条件）

SELECT prod_name, prod_price FROM products where prod_price = 2.50;

SQL过滤与应用过滤：

数据可以在应用层过滤，为此目的，SQL的SELECT语句为客服机应用检索出超过实际所需的数据，然后客户机代码对返回数据进行循环，以提取出需要的行。

通常这种实现并不令人满意，因此，对数据库进行了优化，以便快速有效地对数据进行过滤，让客户机应用（或开发语言）处理数据库的工作将会极大影响应用的性能，并且使所常见的应用完全不具备可伸缩性，此外，如果客户机上过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。

WHERE子句的位置： 在同时使用ORDER BY 和 WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

 

2 WHERE 子句操作符

=  <>  !=  < >  <=  >  >=  BETWEEN

 

2.1 检查单个值

SELECT prod_name, prod_price FROM products WHERE

prod_name = ‘fuses’；

注意：mysql执行匹配默认不区分大小写

SELECT prod_name, prod_price FROM products WHERE

prod_price < 10;

 

SELECT prod_name, prod_price FROM products WHERE

  Prod_price <=10;

2.2 不匹配检查

SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;

何时使用引号： 如果仔细观察上述WHERE子句中使用的条件，

会看到有的值括在单引号内，而有的值未括起来，单引号用来限定字符串，如果将值和串类型的列进行比较，则需要限定引号，用来与数值列进行比较的值不用引号。

 

2.3范围值检查

BETWEEN 需要两个值  开始值和结束值

范围：低端值和高端值 用AND关键字分隔

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

 

2.4 空值检查

在一个列不包含值时，称其为包含控制NULL

NULL 无值（no value），它与字段包含0， 空字符串或仅仅包含空格不同。

使用WHERE子句检查是否具有NULL的值的列。

SELECT prod_name FROM products WHERE prod_price IS NULL;

 

SELECT cust_id FROM customers WHERE cust_email IS NULL;

 

NULL与不匹配：

再通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行，但是，不行，因为未知具有特殊的含义，数据库不知道它们是否匹配，所在匹配过滤或不匹配过滤时不返回它们。

因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。

 

（七）数据过滤

1 组合WHERE子句

更强的过滤控制

MYSQL允许给出多个WHERE子句，这些子句可用两种方式使用：

以AND子句的方式或OR子句的方式使用

 

操作符（逻辑操作符）： 用来联结或改变WHERE子句中的子句的关键字

 

2 AND 、OR操作符

SELECT prod_id, prod_price, prod_naem FROM products

where vend_id = 1003 AND prod_price <=10;

AND:指示DBMS只返回满足所有给定条件的行。

AND：用在WHERE子句中的关键字，用来检索满足所有给定条件的行。（和BETWEEN中的AND区分）

 

SELECT prod_name, prod_price FROM products where vend_id = 1003 OR vend_id = 1002;

分析：如果此处使用AND，没有数据返回。

OR：WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。

 

2 计算次序

WHERE可包含任意数目的AND和OR操作符，允许两者结合以进行复杂和高级的过滤

SELECT prod_name, prod_price FROM products

 WHERE vend_id = 1003 OR vend_id = 1002 AND prod_price >= 10;

分析：不能正确返回价格>10美元 且由1002或1003制造的所有产品。

错误原因： AND在计算次序中优先级更高，操作符被错误地组合了。

解决方法： SELECT prod_name, prod_price FROM products

WHERE （vend_id = 1003 OR vend_id = 1002） AND prod_price >=10;

在WHERE子句中使用圆括号： 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。使用圆括号可以消除分歧。

3 IN操作符

IN操作符用来制定条件范围，范围中的每个条件都可以进行匹配。

IN取合法值的由逗号分隔的清单， 全都在圆括号中。

SELECT prod_name, prod_price FROM products WHERE

vend_id IN (1002, 1003) ORDER BY prod_name;

分析：IN操作符后跟由逗号分隔的合法值清单，整个清单都必须括在圆括号中。

使用IN操作符的优点：

（1） 在使用长的合法选项清单时，IN操作符的语法更清楚直观

（2） 在使用IN时， 计算的次序更容易管理。

（3）IN操作符一般比OR操作符清单执行更快。

（4）IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

 

IN WHERE子句中用来制定要匹配值的清单的关键字，功能与OR相当。

 

4 Not操作符

NOT WHERE子句中用来否定后跟条件的关键字

SELECT prod_id, prod_name, prod_price FROM products

WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_price

 

MYSQL中NOT: MYSQL 支持使用NOT对IN BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

 

（八）用通配符进行过滤

1 LIKE操作符

通配符： 用来匹配值的一部分的特殊字符

搜索模式： 由字面值、通配符或者两者配合构成的搜索条件

LIKE指示MYSQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

谓词：操作符何时不是操作符？ 答案是作为谓词时，从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解，以免SQL文档中遇到此术语时不知道。

 

2 百分号（%）通配符

% : 任何字符出现任意次数

%：搜索模式中给定位置的0个、1个或多个字符

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘jet%’;

区分大小写: 根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写，‘jet%’与JetPack 1000将不匹配

 

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘%anvil%’;

注意事项： 尾空格可能会干扰通配符的匹配。

注意NULL:  % 通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE ‘%’也不能匹配用值NULL作为产品名的行。

 

2 下划线（_）通配符

_ : 下划线只匹配单个字符而不是多个字符

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘_ ton anvil’;

 

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘% ton anvil’;

 

3 通配符的技巧

很有用，但功能有代价（搜索时间会更长）

1 不要过度使用通配符

2 在确实需要使用通配符时，除非决定对有必要否则不要把他们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

3 仔细注意通配符的位置.放错位置，可能不会返回想要的数据。

（九）使用正则表达式

(不懂正则表达式的查看

http://www.liujiangblog.com/course/python/72

学习)

1 MYSQL仅支持多数正则表达式实现的一个很小的子集。

SELECT prod_name FROM products WHERE prod_name

REGEXP ‘1000’ORDER BY prod_name;

分析：REGEXP所跟的东西作为正则表达式处理。

SELECT prod_name FROM products WHERE prod_name

REGEXP ‘.000’ ORDER BY prod_name;

 

2 基本字符匹配

SELECT prod_name FROM products WHERE prod_name LIKE ‘1000’ORDER BY prod_name;

SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY prod_name;

LIKE 和 REGEXP的区别：

LIKE：匹配的文本在列值中出现不会找到。

REGEXP：匹配的文本在列值中出现会进行匹配.

REGEXP如果用来匹配整个列值：（使用^和$）即可

区分大小写：使用BINARY关键字

WHERE prod_name REGEXP BINARY ‘JetPack .000’

 

 

 

3 进行OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用 |

SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000 | 2000’ORDER BY prod_name;

| 匹配其中之一

两个以上的OR条件： 可以给出两个以上OR条件

‘1000|2000|3000’ 将匹配1000 或2000 或 3000（中间不能有空格）

 

4 匹配几个字符之一

匹配任何单一个字符

SELECT prod_name FROM products WHERE products WHERE prod_name REGEXP ‘[123] Ton’ORDER BY prod_name;

SELECT prod_name FROM products WHERE products WHERE prod_name REGEXP ‘[1|2|3] Ton’ ORDER BY prod_name;

 

 

5 匹配范围 [0-9]  [a-z] [A-Z]

SELECT prod_name FROM products WHERE prod_name REGEXP ‘[0-9a-zA-Z] Ton’ORDER BY prod_name;

 

6 匹配特殊字符

. 匹配任意字符

SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘.’ORDER BY prod_name;

为了匹配 . 需要转义 \\. \\- 

SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘\\.’ORDER BY prod_name;

 

空白元字符：

\\f 换页

\\n 换行

\\r 回车

\\t 制表

\\v 纵向制表

匹配 \  需要使用 \\\

 

7 匹配字符类

 

[:alnum:] 任意字符和数字

[:alpha:] 任意字符

[:blank:] 空格和制表符

[:cntrl:] ASCII控制字符

[:digit:] 任意数字

[:graph:] 与[:print:]相同，但不包括空格

[:lower:] 任意小写字母

[:print:] 任意可打印字符

[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符

[:space:] 包括在内的任意空白字符

[:upper:] 任意大写字母

[:xdigit:] 任意十六进制数字

 

8 匹配多个实例

*  0个或多个匹配

+  1个或多个匹配

？ 0个或1个匹配

{n}  指定数目的匹配

{n, } 不少于指定数目的匹配

{n, m} 匹配数目的范围（m不超过255）

 

SELECT prod_name from products WHERE prod_name REGEXP ‘\\([0-9] sticks?\\)’

ORDER BY prod_name;

 

SELECT prod_name from products WHERE prod_name REGEXP ‘[[:digit:]]{4}’ ORDER BY prod_name;

 

9 定位符

定位元字符：

^ 文本开始 $ 文本结尾 [[:<:]] 词的开始 [[:>:]] 词的结尾

SELECT prod_name FROM products WHERE prod_name REGEXP ‘^[0-9\\.]’ ORDER BY prod_name;

 

^的双重用途： ^ 有两种用法。 在集合中（用[和]定义）用它来否定该集合，否则，用来指串的开始处。

 

10 创建计算字段

1 计算字段

存储在表中的数据都不是应用程序所需要的，我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出的数据，然后再在客户机应用程序或报告程序中重新格式化。

 

计算字段并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的。

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列。而数据字段通常用在计算字段的连接上。

 

重要的是要注意到， 只有数据库知道SELECT语句中那些列是实际的表列，哪些列是计算字段。从客户机（如应用程序）的角度来看，计算的字段的数据是以其他列的数据相同的方式返回的。

 

2 拼接字段

为了说明如何使用计算字段

Vendors表包含供应商名和位置信息，假如要生成一个供应商报表，

需要在供应商的名字中按照name（location）这样的格式列出供应商的位置。

 

此报表需要单个值，而表中数据存储在两个列vend_name 和vend_country中，此外，需要用括号将vend_country括起来，这些东西都没有明确存储在数据库表中。

 

拼接： 将值联结到一起构成单个值

在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。

 

（注意）MySQL的不同之处 多数DBMS使用+或||来实现拼接，

MYSQL则使用Concat（）函数来实现。当把SQL语句转换成MYSQL语句时一定要把这个区别铭记在心。

 

SELECT Concat(vend_name, ‘(’, vend_country, ‘)’) FROM vendors ORDER BY vend_name;

 

Concat()拼接串，即把多个串连接起来形成一个较长的串。

Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

 

SELECT语句连接以下4个元素：

存储在vend_name列中的名字

包含一个空格和一个左圆括号的串

存储在vend_country列中的国家

包含一个右圆括号的串。

 

SELECT Concat(RTrim(vend_name),‘(’,Rtrim(vend_country), ‘)’) FROM vendors ORDER BY vend_name;

 

RTrim（）函数去掉值右边的所有空格，通过使用RTrim()，各个列都进行了整理。

 

Trim函数

RTrim() 去除右边的空格

LTrim() 去除左边的空格

Trim() 去除左右两边的空格

 

3 使用别名

SELECT

Concat(RTrim(vend_name),‘(’,RTrim(vend_country), ‘)’) As vend_title FROM vendors ORDER BY vend_name

 

导出列： 别名有时也成为导出列， 不管称为什么，它们所代表的都是相同的东西。

 

4 执行算数计算

SELECT prod_id, quantity, item_price FROM orderitems

WHERE  order_num = 20005;

汇总物品的价格：

SELECT prod_id, quantity, item_price, quantity * item_price AS expand_price FROM orderitems WHERE order num = 20005;

SELECT Trim(‘abc’) 返回abx

SELECT Now() 返回当前日期和时间

(十一) 使用数据处理函数

1 使用函数

大多数SQL实现支持一下类型的函数

用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。

用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数

用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数。

返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

 

2 文本处理函数

SELECT vend_name, Upper(vend_name)

AS vend_name_upcase

FROM vendors

ORDER BY vend_name

 

常用的文本处理函数

Left（） 返回串左边的字符

Length() 返回串的长度

Locate() 找出串的一个子串

Lower() 将串转换为小写

LTrim() 去掉串左边的空格

Right() 返回串右边的字符

RTrim() 去掉串右边的空格

Soundex() 返回子串的字符

SubString() 返回子串的字符

Upper() 将串转换为大写

 

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

SELECT cust_name, cust_contact FROM customers WHERE cust_contact = ‘Y.Lie’;

无匹配字符串 为空

SELECT xunst_name, cust_contack FROM customers WHERE

SOUNDEX(cust_contact) = SOUNDEX(‘Y.Lie’)

 

2 日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

 

一般，应用程序不适用用来存储日期和时间的搁置，因此日期和时间函数总是被用来读取、统计和处理这些值。

AddDate() 增加一个日期（天 周）

AddTime() 增加一个时间（时 分）

curDate() 返回当前日期

curTime() 返回当前时间

Date() 返回日期时间和日期部分

DateDiff() 计算两个日期之差

Date_Add() 高度灵活的日期运算函数

Date_Format() 返回一个格式化的日期或时间串

Day() 返回一个日期的天数部分

DayOfWeek() 对于一个日期，返回对应的星期几

Hour() 返回一个时间的小时部分

Minute() 返回一个时间的分钟部分

Month() 返回一个日期的月份部分

Now() 返回当前日期和时间

Second() 返回一个时间的秒部分

Time() 返回一个日期时间的时间部分

Year() 返回一个日期的年份部分

 

应该总是使用4位数字的年份

 

SELECT cust_id, order_num FROM orders WHERE order_date  = ‘2005-09-01’;

Date(order_date)指示Mysql仅提取列的日期部分，更可靠的SELECT语句为：

SELECT cust_id, order_num FROM orders WHERE Date(order_date) = ‘2005-09-01’;

如果要的是日期，请使用Date()  Time()

 

SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’;

 

SELECT cust_id, order_num FROM orders WHERE  Year(order_date) = 2005 AND Month(order_date) = 9;

 

3 数值处理函数

Abs()

Cos()

Exp()

Mod()

Pi()

Rnad()

Sin()

Sqrt()

Tan()

 

(十二) 汇总数据

检索例子的方式（一下都需要对表中的数据汇总），因此，返回实际表数据是对时间和处理资源的一种浪费：

1 确定表中行数（或满足某个条件或包含某个特定值的行数）

2 获得表中行组的和

3 找出表列（或所有行或某些特定的列）的最大值 最小值和平均值

 

实际想要的是汇总信息：

聚集函数： 运行在行组中，计算和返回单个值的函数

SQL聚集函数

AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

 

SELECT AVG(prod_price) AS avg_price FROM products;

 

SELECT AVG(prod_price) As avg_price FROM products

WHERE vend_id = 1003;

 

AVG（）只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，为了获得多个列的平均值，必须使用多个AVG（）函数。

NULL值  AVG()函数忽略列值为NULL的行

 

2 COUNT()函数

COUNT()函数进行计数，可利用COUNT确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

使用COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

 

SELECT COUNT(*) AS num_cust FROM customers;

SELECT COUNT(cust_email) AS num_cust FROM customers;

 

NULL值： 如果指定列名， 则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是（*），则不忽略。

 

3 MAX() 函数

SELECT MAX(prod_price) AS max_price FROM products;

对非数值数据使用MAX()

虽然MAX()一般用来找出最大的数值或日期值，但MYSQL允许将它用来返回任意列中的最大值，包括文本列中的最大值，在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

NULL值， MAX()函数忽略列值为NULL的行。

 

4 MIN()函数

SEELCT MIN(prod_price) AS min_price FROM products;

与MAX()类似

NULL值 MIN()函数忽略列值为NULL的行

 

5 SUM() 函数

SELECT SUM(quantity) AS items_ordered FROM orderitems

WHERE order_num = 20005;

 

SELECT SUM(quantity * item_price) AS total_price FROM orderitems WHERE order_num = 20005;

 所有聚集函数都可用来执行多个列上的计算

NULL值 SUM()函数忽略列值为NULL的行。

 

 

以上5个聚集函数都可以如下使用：

对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）

只包含不同的值，指定DISTINCT参数

如果不指定DISTINCT，则假定为ALL

 

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

分析：如果指定列名， 则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*),因此不允许使用COUNT(DISTINCT)，否则会产生错误，类似地，DISTINCT必须使用列名，不能用于计算或表达式。

将DISTINCT用于MIN()和MAX() 从技术上看是没有价值的。

 

6 组合聚集函数

SELECT COUNT(*) AS num_prices,

MIN(prod_price) AS price_min,

MAX(prod_price) AS price_max,

AVG(prod_price) AS price_avg

FROM products;

 

注意事项： 取别名以包含某个聚集函数的结果时，不应该使用表中实际的列名，虽然这样做并非不合法，但是用唯一的名字会使你的SQL更易与理解和使用（以及将来容易排除故障）