MySQL_纸上谈兵之入门2

这段时间忙于写毕业论文，读的书没有更新，计划写多线程方面的简单一些读书笔记。

正则表达式

正则表达式是用来匹配文本的特殊的串(字符串集)，也就是将一个模式(正则表达式)与一个文本串进行比较。

基本字符匹配：

       SELECT prod_name WHRER products WHRER prod_name REGEXP‘1000’ ORDER BY prod_name;

       REGEXP后跟的东西为正则表达式处理。

       SELECT prod_name FORM products WHERE prod_name REGEXP‘.000’ ORDER BY prod_name;

注意： .是正则表达式语言中的一个特殊的字符。表示任意一个字符。

进行OR匹配：

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

匹配几个字符之一：

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

[123]定义一个数组，它的意思是匹配1或者2 或3。

字符集合可以被否定：[^123]是匹配除这些字符外的任意东西。

匹配范围：

       SELECT prod_name FROM products WHRER prod_name REGEXP ‘[1-5]Ton’ ORDER BY prod_name;

匹配特殊字符： 为了匹配特殊在字符，必须用\\ 为前导。 \\-表示查找-, \\.表示查找.

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

匹配多个实例：

       元字符：*：0个或多个匹配；  +：1个或多个匹配； ?: 0个或1个匹配

       SELECT prod_name FROM products WHRER prod_name REGEXP‘\\[0-9] sticks? \\’ ORDER BY prod_name;

说明：\\(匹配),[0-9]匹配任意数字，sticks?匹配stick和sticks，(s后的?可选，因为?匹配它前面的任何字符的0次或1次出现，\\匹配).

 

创建计算字段

拼接字段：将值联结到一起构成单个值。解决办法就是把两个列拼接起来，使用Conact()函数。

在MySQL中可以使用Concat()函数来拼接两个列：

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

输出如：Jet Set (England)

MySQL还支持使用RTrim和LTrim来删除数据左右侧的多余空格：

        SELECT Concat(RTrim(vend_name),  '(',RTrim(vend_country),  ')') 

                    FROM vendorsORDER BY vend_name;

RTrim()函数去掉右边的所有空格，LTrim()函数去掉左边的所有空格。

使用别名：如果字段太长，可以使用别名代替原字段，别名的关键字为 AS：

       SELECT Concat(RTrim(vend_name),  '(', RTrim(vend_country), ')') AS vend_titleORDER BY vend_name;

此时，原来的字段名Concat(vend_name),  '(', vend_country, ')')将变成 vend_title.

执行算数计算：

首先，检索订单号20005中的所有物品：

       SELECT prod_id, quantity, item_price FROMorderitems WHRER order_num = 20005;

expanded_price列为一个计算字段：

        SELECT prod_id, quantity,item_price, quantity*item_price AS expanded_price;

 

使用数据处理函数

文本处理函数：

       SELECT vend_name, Upper(vend_name) ASvend_name_upcase FROM vendors ORDER BY vend_name;

此处的额Upper()函数是将文本转换为大写。

文本处理函数：

       Left()                                  返回串左边的字符

       Length()                              返回串的长度

       Locate()                              找出串的一个子串

       Lower()                               将串转换为小写

此处有个特殊函数：Soundex()：返回串的Soundex值。

现假设某客户正确名字为Y. Lee,由于输入错误，导致系统只有错误名为Y. Lie的客户存在，如果用系统的联系名进行如下的查找，则无法查找结果：

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

因此应使用如下的查找方式：

       SELECT cust_name, cust_contact FROMcustomers WHERE Soundex(cust_contact) = Soundex(‘Y. Lie’);

通过上述方式即可找到名为Y.Lee的客户。

日期和时间处理函数：

       AddDate()                                                增加一个日期

       AddTime()                                               增加一个时间

       CurTime()                                                返回当前时间

       CurDate()                                                 返回当前日期

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

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

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

       Hour()                                                     返回一天的小时部分

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

注意：MySQL使用的日期格式必须为：yyyy-mm-dd。如2018年3月15日，给出的为2018-3-15。

查找order_date为2018年3月15日…..:

        SELECT cust_is, order_num FROMorders WHERE orders_date = ‘2018-03-15’;

但是如果存储的order_date = 2018-03-1511:21:53,那么WHERE orders_date =‘2018-03-15’查找失败，如何解决：

解决办法：指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是整个进行比较，为此必须使用Date()函数：

       SELECT cust_id, order_num FROM orderWHERE Date(order_data) = ‘2015-03-15’;

汇总数据

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

        函数                                                                      说明

        AVG()                                                                    返回某列的平均值

        COUNT()                                                               返回某列的行数

        MAX()                                                                   某列的最大值

        MIN()…….SUM()……

eg:

        SELCET AVG(prod_price)AS avg_price FROM products;

COUNT()函数由两种用法：

       1  COUNT(*)对表中行的数目进行统计，包含非空与空(NULL)；

       2  COUNT(column)特定列中具有值的行进行计算，忽略NULL值。

聚集不同值：以上的五个函数在默认情况下都指定ALL计算，即对所有行进行计算；但是另一种情况下，只包含不同的值，指定DISTINCT参数。

eg:

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

组合聚集函数：

        SELECT COUNT(*) AS num_items, MIN(prod_price)AS price_min, MAX(prod_price) 

                    AS price_max, AVG(prod_price) AS price_avg FROMproducts;

统计行的数目并使用，别名计算最低价格、最高价格及平均价格。

 

分组数据

返回供应商1003提供的产品数目：

       SELECT COUNT(*) AS num_prods FROMproducts WHERE vend_is = 1003;

创建分组：是使用SELECT语句中的GROUP BY子句建立的。

       SELECT vend_id, COUNT(*) AS num_prodsFROM products GROUP BY vend_id;

上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段(使用COUNT(*)函数建立)，GROUP BY子句指示MySQL按cend_id排序并分组数据。即：GROUP BY自己指示MySQL分组数据，然后对每个组而不是整个结果进行聚集。

这里先了解GROUP BY的如下规则，后续还要对GROUP BY进行详解。

       ① GROUP BY 子句可以包含任意数目的列，这使得能对分组进行嵌套，可以对数据分组提供更详尽的控制。如上述的GROUP BY vend_id,vend_name;对这两个列进行分组。

       ② 如果在GROUP BY 中嵌套了分组，数据将在最后规定的分组上进行汇总，即：建立分组时，指定的所有列都在一起计算。

       ③ GROUPBY 子句中列出的每个列都必须是检索列或者有效的表达式，但不能是聚集函数。

       ④ 如果分组列中具有NULL值，则NULL将作为一个分组返回，如果列中有多行NULL值，它们将分为一组。

       GROUPBY子句的位置：必须出现在唉WHERE子句之后，ORDER BY子句之前。

过滤分组：由于WHERE过滤指定的是行而不是分组，所以使用HAVING子句。其中目前唯一的区别是WHEHE过滤行，而HAVING过滤分组。

       SELECT cust_id, COUNT(*) AS orders FROMorders GROUP BY cust_id HAVING COUNT(*) >=2;

前面属于数据分组，后面的HAVING子句是过滤COUNT(*) >= 2的分组。

WHEHE和HAVING的区别：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

列出2个以上(含2个)、价格为10以上的产品的供应商：

        SELECT vend_id，COUNT(*) AS num_prods FROM products 

                WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >=2;

说明：WHERE子句过滤所有prod_price至少为10的行，GROUP BY子句按vend_id分组数据，HAVING子句过滤计数为2或者2以上的分组。

分组与排序：

GROUPBY 与HAVING的区别：

       ORDER BY                                             GROUP BY

       排序产生的输出                                       分组行，但输出可能不是分组的顺序

       任意列都可以使用                                   之坑你使用选择列或者列表达式

不一定需要                                              如果与聚集函数一起，则必须使用

注意：一般在使用GROUP BY子句时，应该也给出ORDERBY子句，这是保证数据正确排序的唯一方法。

检索总计订单大于等于50的订单的订单号和总计订单价格：

        SELECT order_num, SUM(quantity *item_price) AS ordertotal 

            FROM orderitems GROUP BY order_num 

            HAVING SUM(quanlity* item_price) >= 50 ORDER BY ordertotal;

SELECT 子句的顺序：

     SELECT(目标列) =>FROM(目标表)=>WHERE(附加条件)=>GROUP BY(数据分组)=>HAVING(过滤)=>ORDER BY(排序)=>LIMIT(限制行数)

使用子查询：嵌套在其他查询语句中的查询。

利用子查询进行过滤：

现假设：需要列出订购物品TNT2的所有客户，

第一步：对于prod_id为TNT2的所有订单物品，其检索order_num列：

       SELECT order_numFROM orderitems WHEHE prod_id = ‘TNT2’;

结果如下： order_num

                    20005

                       20007

第二步：查询具有订单20005和20007的客户ID：

       SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

最终结果如下：    cust_id

                            10001

                            10004

上述利用子查询如下：

       SELECT cust_id FROM orders WHERE order_num IN(

                SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2’);

即：将第一步查询的结果作为第二步的条件进行查询。

注意：子查询总是由内向外处理的，这个在MySQL调优时将进一步讲解。

作为计算字段使用子查询：

计算customers表中每个客户的订单总数：

            SELECT cust_name, cust_state, (SELECTCOUNT(*) FROM orders WHERE

                         orders.cust_is =customers.cust_id) AS orders ORDER BYcust_name;

注意：子查询中的WHERE子句和前面使用的WHERE子句稍有不同,子查询中的WHERE子句使用了完全限定列名(串联表名.列名)。