mysqlSQL - juedaiyuer/researchNote GitHub Wiki
#常用SQL技巧和常见问题#
##正则表达式##
^ 在字符串的开始处进行匹配
$ 在字符串的末尾处进行匹配
. 匹配任意单个字符,包括换行符
[...] 匹配出括号内的任意字符
[^...] 匹配不出括号内的任意字符
a* 匹配零个或多个a(包括空串)
a+ 匹配1个或多个a(不包括空串)
a? 匹配1个或零个a
a1|a2 匹配a1或a2
a(m) 匹配m个a
a(m,) 匹配m个或更多个a
a(m,n) 匹配m到n个a
a(,n) 匹配0到n个a
(...) 将模式元素组成单一元素
#字符串以a开头
mysql> select 'abcdefg' REGEXP '^a';
#字符串以g末尾
mysql> select 'abcdefg' REGEXP 'g$';
#.匹配任意单个字符,包括换行符
mysql> select 'abcdefg' REGEXP '.h';
mysql> select 'abcdefg' REGEXP '[axz]';
###实际应用-检查使用163.com的用户###
mysql> create table t(name varchar(20),email varchar(40));
mysql> insert into t values('beijing','[email protected]');
mysql> insert into t values('beijing126','[email protected]');
mysql> insert into t values('beijing188','[email protected]');
mysql> select name,email form t where email REGEXP "@163[.,]com$";
##RAND()提取随机行##
#随机顺序
#随机抽取样本对总体的统计很重要
mysql> select * from sales order by rand();
##利用GROUP BY的WITH ROLLUP子句做统计##
mysql> create table sales(
year int not null,
country varchar(20) not null,
product varchar(32) not null,
profit int
);
insert into sales values(2004,'china','tnt1',2001);
insert into sales values(2004,'china','tnt2',2002);
insert into sales values(2004,'china','tnt3',2003);
insert into sales values(2005,'china','tnt1',2004);
insert into sales values(2005,'china','tnt2',2005);
insert into sales values(2005,'china','tnt3',2006);
insert into sales values(2005,'china','tnt1',2007);
insert into sales values(2005,'china','tnt2',2008);
insert into sales values(2005,'china','tnt3',2009);
insert into sales values(2006,'china','tnt1',2010);
insert into sales values(2006,'china','tnt2',2011);
insert into sales values(2006,'china','tnt3',2012);
mysql> select year,country,product,sum(profit) from sales group by year,country,product;
+------+---------+---------+-------------+
| year | country | product | sum(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 |
+------+---------+---------+-------------+
#增加了聚合统计计算,任何一个分组以及分组组合的聚合信息
#反映了一种OLAP思想
#不能使用ORDER BY子句进行结果排序
mysql> select year,country,product,sum(profit) from sales group by year,country,product with rollup;
+------+---------+---------+-------------+
| year | country | product | sum(profit) |
+------+---------+---------+-------------+
| 2004 | china | tnt1 | 2001 |
| 2004 | china | tnt2 | 2002 |
| 2004 | china | tnt3 | 2003 |
| 2004 | china | NULL | 6006 |
| 2004 | NULL | NULL | 6006 |
| 2005 | china | tnt1 | 4011 |
| 2005 | china | tnt2 | 4013 |
| 2005 | china | tnt3 | 4015 |
| 2005 | china | NULL | 12039 |
| 2005 | NULL | NULL | 12039 |
| 2006 | china | tnt1 | 2010 |
| 2006 | china | tnt2 | 2011 |
| 2006 | china | tnt3 | 2012 |
| 2006 | china | NULL | 6033 |
| 2006 | NULL | NULL | 6033 |
| NULL | NULL | NULL | 24078 |
+------+---------+---------+-------------+
##用BIT GROUP FUNCTIONS做统计##
#购物单表,记录时间,顾客信息等
#购物单明细表,购买商品
#任务:用户购买商品的种类和每次购物总价
#从右向左计算
#数值第一位:面包
#数值第二位:牛奶
#数值第三位:饼干
#数值第四位:啤酒
...
#所代表的二进制表明了一个用户购物的情况
create table order_rab (id int,customer_id int,kind int);
insert into order_rab values(1,1,5),(2,1,4),(3,2,3),(4,2,4);
#统计两位顾客在这个超市一共购买过什么商品
# bit_or 逻辑或
select customer_id,bit_or(kind) from order_rab group by customer_id;
# bit_and 统计每个顾客每次购买的商品
##数据库名,表名大小写问题##
大多数UNIX环境,操作系统对大小写的敏感性导致数据库名,表名大小写敏感
window不敏感
列,索引,存储子程序和触发器名在任何平台上对大小写不敏感
默认情况下,表别名在UNIX中敏感；但在window,mac os 中不敏感
#如何在硬盘中保存,使用表名和数据库名
lower_case_tables_name
##使用外键需要注意的问题##
MySQL的外键功能仅对InnoDB存储引擎的表有作用
##source##
- MySQL数据库开发,优化与管理维护(PDF:230)