常用的SQL技巧和常见问题

    正则表达式的使用

        正则表达式（Regular Expression），是一个用来描述或者撇配一系列某个句法规则的字符串的单个字符串。

   

        "^"在字符窜的开始处进行匹配，返回结果为1表示匹配，返回结果为0表示不匹配，下例尝试匹配字符串"abcdefg"是否以字符"a"开始，

    select 'abcdefg' REGEXP '^a';// 返回1

        "$"在字符串末尾处进行匹配，下例尝试匹配字符串'abcdefg'是否以字符'g'结束，

    select 'abcdefg' REGEXP 'g$';// 返回1

        "."匹配任意单个字符，包括换行符，下例字符串"abcdefg"尝试匹配单个字符串"h"和"f"，

    select 'abcdefg' REGEXP '.h', 'abcdefg' REGEXP '.f';// 返回0和1

        "[...]"匹配出括号内的任意字符，下例中字符串"abcdefg"尝试匹配"fhk"中的任意一个字符，如果有一个字符能匹配上，返回1，

    select 'abcdefg' REGEXP "[fhk]";// 返回1

        "[^...]"匹配不出括号内的任意字符，和"[...]"刚好相反，下例中字符串"efg"和"x"中如果有任何一个字符匹配不上"[XYZ]"中的任意一个字符，返回0，如果全部匹配上，返回1

    select 'efg' REGEXP "[^XYZ]",'X' REGEXP "[^XYZ]";

        使用正则查询出使用163.com邮箱的用户和邮箱

    select name, email from t where email REGEXP "@163[.,]com$"

        将上例改为普通的LIKE语句，WHERE的条件改为，

    email like "[e-mail]" or email like "[e-mail]"

        采用正则表达式使得我们的写法更加简单易读。

      巧用RAND()提取随即行

       RAND()函数与ORDER BY子句一起完成，随机抽取某些行动的功能，ORDER BY RAND()能够把数据随机排序。

    select * from sales2 order by rand();    // 通过limit抽取一部分样本    select * from sales2 order by rand() limit 5;// 在sales表中抽取了5个样本

      利用GROUP BY的WITH ROLLUP子句做统计

    select year,country,product,sum(profit) from sales group by year,country,product with rollup;

        查询出来的数据附带检索本组类的整体聚合信息。WITH ROLLUP反应的是一种OLAP思想，这个GROUP BY语句执行完毕后，可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。

        注意，当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，ROLLUP和ORDER BY是相互排斥的。LIMIT用在ROLLUP后面。

     用BIT GROUP FUNCTIONS做统计

        使用GROUP BY语句和BIT_AND，BIT_OR函数，完成统计工作，这两个函数一般用途是做数值之间的逻辑位运算，但是当他们与GROUP BY联合使用的时候可以做一些其他任务。

        通过将物品的种类使用二进制表示，使用BIT_OR函数将该字段的各个值进行或运算，可以得到主键值的所有历史记录对应物品的所有种类，使用BIT_AND函数将该字段的各个值进行与运算，可以得到主键值的所有历史记录每次都有的物品。通过将种类较多的某字段使用二进制表示，可以用简洁的数据表示丰富的信息，节省存储空间，提高部分统计计算的速度。

      数据库名、表名大小写问题

        数据库中的每个表，至少对应数据库目录中的一个文件，所使用的操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。大多数unix环境中，操作系统对大小写敏感性导致了数据库名和表名对大小写敏感性，而在windows中，操作系统本身对大小写不敏感，在windows下mysql数据库名和表名对大小写不敏感。

        列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在UNIX中对大小写敏感，但在window和Mac OS X中对大小写不敏感，

    select id from order_rab a where A.id = 1;

        该查询在UNIX中会报错，而在windows中是可以的。要想避免出现差错最好采用一致的转换。例如，总是用小写创建和引用数据库名和表名。mysql如何在硬盘上保存、使用表名和数据库名有lower_case_tables+_name系统变量决定，可以在启动时设置。

      使用外键需要注意的问题

        在mysql中，InnoDB存储引擎支持对外部关键字约束条件的检查。而对于其它类型存储引擎的表，当使用 REFERENCES tbl_name(col_name)子句定义列时可以使用外部关键字，但是该句没有实际效果，只是作为备忘录或注释提醒用户目前正定义的列指向另一个表的一个列。

    create table users(id int, name varchar(), primary key(id)) engine=myisam;

    create table books(id int, bookname varchar(10), userid int, primary key(id), constraint fk_userid_id foreign key(userid) references users(id)) engine=myisam;

        myisam的表外键就没有起作用，但如果是InnoDB存储引擎建表的话，外键就会起作用。而且使用show create table命令查看建表语句，发现MyISAM存储引擎并不现实外键的语句，而InnoDB存储引擎显示外键语句。