常用SQL技巧及相关问题Word下载.docx

1、匹配不出括号内的任意字符a* 匹配零个或多个a（包括空串）a+匹配1个或多个a（不包括空串）a？匹配1个或零个aa1|a2匹配a1或a2a（m）匹配m个aa（m,）匹配m个或更多个aa（m,n）匹配m到n个aa（,n）匹配0到n个a（）将模式元素组成单一元素 下面举一些例子来介绍常用正则表达式的使用方法。 “”在字符串的开始处进行匹配，返回结果为1表示匹配，返回结果为0表示不匹配。下例中尝试匹配字符串“abcdefg”是否以字符“a”开始：mysql select abcdefg REGEXP a;+-+| | 1 |1 row in set （0.39 sec） “$”在字符串的末尾处进行匹

2、配，下例中尝试匹配字符串“abcdefg”是否以字符“g”结束：g$1 row in set （0.01 sec） “.”匹配任意单个字符，包括换行符。下例中字符串“abcdefg”尝试匹配单字符“h”和“f”：.h, .f+-+-+ | | 0 | 1 |1 row in set （0.00 sec） “”匹配出括号内的任意字符。下例中字符串“abcdefg”尝试匹配“fhk”中的任意一个字符，如果有一个字符能匹配上，则返回1。 REGEXP fhk+-+ “”匹配不出括号内的任意字符。和“”刚好相反。下例中字符串“efg”和“X”中如果有任何一个字符匹配不上“XYZ”中的任意一个字符，则返

3、回0；如果全部都能匹配上，则返回1。efgXYZ,X+-+-+| 1 | 0 |上面我们介绍了正则表达式的常见使用方法。但是在实际工作中正则表达式到底什么地方会用到呢？下面举一个实际的例子，使用正则表达式查询出使用邮箱的用户和邮箱。（1）创建测试数据： create table t（name varchar（20）,email varchar（40）;Query OK, 0 rows affected （0.00 sec） insert into t values（beijingbeijing）;Query OK, 1 row affected （0.00 sec）beijing126beij

4、ing188 select * from t; +-+-+| name | email | beijing | beijing | beijing126 | beijing | beijing188 | beijing |3 rows in set （0.00 sec）（2）使用正则表达式“$”和“.”进行匹配： select name ,email from t where email REGEXP 163.,com$+-+-+从上例中可以看出，如果不使用正则表达式而使用普通的LIKE语句，则WHERE条件需要写成如下格式：email like “163%.com” or email lik

5、e “163%,com”显然，采用正则表达式使得我们的写法更加简单易读。巧用RAND（）提取随机行大多数数据库都会提供产生随机数的包或者函数，通过这些包或者函数可以产生用户需要的随机数，也可以用来从数据表中抽取随机产生的记录，这对一些抽样分析统计是非常有用的。例如ORACLE中用DBMS_RANDOM包产生随机数，而在MySQL中，产生随机数的方法是RAND（）函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实就是ORDER BY RAND（）能够把数据随机排序。例如，可按照随机顺序检索数据行： select * from sales2 order by r

6、and（）;+-+-+-+-+| id | company_id | moneys | year | 106 | 106 | 106 | 2007 | 362 | 362 | 362 | 0000 | 702 | 702 | 702 | 0000 | 871 | 871 | 871 | 0000 | 398 | 398 | 398 | 0000 | 639 | 639 | 639 | 0000 | 45 | 45 | 45 | 1946 | 129 | 129 | 129 | 2030 |这样的话，如果想随机抽取一部分样本的时候，就可以把数据随机排序后再抽取前n条记录就可以了，比如： sele

7、ct * from sales2 order by rand（） limit 5;| 876 | 876 | 876 | 0000 | 283 | 283 | 283 | 0000 | 442 | 442 | 442 | 0000 | 874 | 874 | 874 | 0000 | 849 | 849 | 849 | 0000 |5 rows in set （0.00 sec）;上面的例子从sales2表中随机抽取了5个样本，随机抽取样本对总体的统计具有十分重要的意义，因此这个函数非常有用。利用GROUP BY的WITH ROLLUP子句做统计在SQL语句中，使用GROUP BY的WITH

8、ROLLUP字句可以检索出更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息，具体如下例所示。（1）创建表sales并初始化数据：create table sales（ year int not null, country varchar（20） not null, product varchar（32） not null, profit intinsert into sales values（2004,chinatnt1,2001）; insert into sales values（2004,tnt2,2002）;tnt3,2

9、003）; insert into sales values（2005,2004）;,2005）;,2006）;,2007）;,2008）;,2009）; insert into sales values（2006,2010）;,2011）;,2012）;（2）按照year、country、product列分组对profit列进行聚合计算如下： select year, country, product, sum（profit） from sales group by year, country, product;+-+-+-+-+| year | country | product | su

10、m（profit） | 2004 | china | tnt1 | 2001 | 2004 | china | tnt2 | 2002 | 2004 | china | tnt3 | 2003 | 2005 | china | tnt1 | 4011 | 2005 | china | tnt2 | 4013 | 2005 | china | tnt3 | 4015 | 2006 | china | tnt1 | 2010 | 2006 | china | tnt2 | 2011 | 2006 | china | tnt3 | 2012 |9 rows in set （0.00 sec） sel

11、ect year, country, product, sum（profit） from sales group by year, country, product with rollup;| 2004 | china | | 6006 | 2004 | | | 6006 | 2005 | china | | 12039 | 2005 | | | 12039 | 2006 | china | | 6033 | 2006 | | | 6033 | NULL | | | 24078 |16 rows in set （0.00 sec）从上面的例子中可以看到第2个SQL语句的结果比第一个SQL语句的

12、结果多出了很多行，而这些行反映出了更多的信息，例如，第2个SQL语句的结果的前3行表示2004年在中国各个产品（tnt1、tnt2、tnt3）的利润，而第4行表示2004年在中国所有产品的利润是6006，这个信息在第一个SQL语句中是不能反映出来的，第5行表示2004年全世界所有产品的利润是6006（当然这里的country字段只有china）。其实WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。注意：1、当使用 ROLLUP时, 不能同时使用ORDER BY子句进行结果排序。换言之， RO

13、LLUP 和ORDER BY 是互相排斥的 2、LIMIT 用在 ROLLUP后面。用BIT GROUP FUNCTIONS做统计在本小节，主要介绍如何共同使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作。这两个函数的一般用途就是做数值之间的逻辑位运算，但是，当把它们与GROUP BY子句联合使用的时候就可以做一些其他的任务。假设现在有这样一个任务：一个超市需要记录每个用户每次来超市都购买了哪些商品。为了将问题简单化，假设该超市只有面包、牛奶、饼干、啤酒4种商品。那么通常该怎么做呢？一般先建立一个购物单表，里面记录购物发生的时间、顾客信息等；然后再建立一个购物单明细表，里

14、面记录该顾客所购买的商品。这样设计表结构的优点是顾客所购买的商品的详细信息可以记录下来，比如数量、单价等，但是如果目前的这个任务只需要知道用户购买商品的种类和每次购物总价等信息的话，那么这种数据库结构的设计就显得太复杂了。一般还可能会想到用一个表实现这个功能，并且用一个字段以字符串的形式记录顾客所购买的所有商品的商品号，这也是一种方法，但是如果顾客一次购买商品比较多的话，需要很大的存储空间，而且将来做各种统计的时候也会捉襟见肘。下面给出一种新的解决办法，类似于上面讲到的第二种方案，仍然用一个字段表示顾客购买商品的信息，但是这个字段是数值型的而不是字符型的，该字段存储一个十进制数字，当它转换成二

15、进制的时候，那么每一位代表一种商品，而且如果所在位是“1”那么表示顾客购买了该种商品，“0”表示没有购买该种商品。比如数值的第1位代表面包（规定从右向左开始计算）、第2位代表牛奶、第3位代表饼干、第4位代表啤酒，这样如果一个用户购物单的商品列的数值为5，那么二进制表示为0101，这样从右向左第1位和第3位是1，那么就可以知道这个用户购买了面包和饼干，而如果这个客户有多个这样的购物单（在数据库中就是有多条记录），把这些购物单按用户分组做BIT_OR（）操作就可以知道这个用户都购买过什么商品。下面举例说明一下这个操作，首先初始化一组数据： create table order_rab （id in

16、t,customer_id int,kind int）;Query OK, 0 rows affected （0.05 sec） insert into order_rab values （1,1,5）,（2,1,4）;Query OK, 2 rows affected （0.00 sec） insert into order_rab values （3,2,3）,（4,2,4）;Query OK, 2 rows affected （0.00 sec） select * from order_rab;+-+-+-+| id | customer_id | kind | 1 | 1 | 5 |

17、2 | 1 | 4 | 3 | 2 | 3 | 4 | 2 | 4 |4 rows in set （0.00 sec）其中customerid是顾客编号，kind是所购买的商品，初始化了两个顾客1和2的数据，他们每人购物两次，前者购买的商品数值是5和4，转化为二进制分别为0101、0100，表示这个顾客第一次购买了牛奶和啤酒，第二次购买了牛奶；后者购买的商品数值是3和4，转化为二进制分别为0011、0100，表示这个顾客第一次购买了饼干和啤酒，第二次购买了牛奶。下面用BIT_OR（）函数与GROUP BY子句联合起来，统计一下这两个顾客在这个超市一共都购买过什么商品，如下例： select c

18、ustomer_id,bit_or（kind） from order_rab group by customer_id;+-+-+| customer_id | bit_or（kind） | 1 | 5 | 2 | 7 |2 rows in set （0.00 sec）可以看到顾客1的BIT_OR（）结果是5即0101，表示这个顾客在本超市购买过牛奶和啤酒；顾客2的BIT_OR（）结果是7即0111，表示这个顾客在本超市购买过牛奶、饼干、啤酒。下面解释一下数据库在处理这个逻辑时的计算过程，以第一个顾客举例，那么BIT_OR（kind）就相当于把kind的各个值做了一个“或”操作，最终结果是十进

19、制的5。逻辑计算公式如下：# .0101# .0100# OR .0000# -同理，可以用BIT_AND（）统计每个顾客每次来本超市都会购买的商品，具体如下： select customer_id,bit_and（kind） from order_rab group by customer_id;+-+-+| customer_id | bit_and（kind） | 1 | 4 | 2 | 0 |2 rows in set （0.01 sec）顾客1的BIT_AND（）结果是4即0100，表示顾客1每次来本超市都会购买牛奶；顾客2的BIT_AND（）结果是0即0000，表示顾客2没有每次来本超市都会购买的商品。数据库在处理BIT_AND（）的时候就是把kind的各个值做了一个“与”操作，拿顾客1举例说明一下，逻辑计算公式如下：# AND .1111# -从上面的例子可以看出，这种数据库结构设计的好处就是能用很简洁的数据表示很丰富的信息，这种方法能够大大地节省存储空间，而且能够提高部分统计计算的速度。不过需要注意的是，这种设计其实损失了顾客购买商品的详细信息，比如购买商品的数量、当时单价、是否有折扣、是否有