mysql从头学一 1.1 小小记忆

巧用 RAND()提取随机行
大多数数据库都会提供产生随机数的包或者函数，通过这些包或者函数可以产生用户需要的
随机数，也可以用来从数据表中抽取随机产生的记录，这对一些抽样分析统计是非常有用的。
例如ORACLE中用DBMS_RANDOM包产生随机数，而在MySQL中，产生随机数的方法是RAND()
函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实
就是ORDER BY RAND()能够把数据随机排序。
例如，可按照随机顺序检索数据行：
mysql> select * from sales2 order by rand();
+------+------------+--------+------+
| 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 条记录
就可以了，比如：
mysql> select * from sales2 order by rand() limit 5;
+------+------------+--------+------+
| id | company_id | moneys | year |
+------+------------+--------+------+
| 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 个样本，随机抽取样本对总体的统计具有十分重要的
198
意义，因此这个函数非常有用。
17.3 利用 GROUP BY 的 WITH ROLLUP 子句做统计
在SQL语句中，使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，它不仅
仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信
息，具体如下例所示。
（1）创建表sales并初始化数据：
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);
（2）按照year、country、product列分组对profit列进行聚合计算如下：
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 |
+------+---------+---------+-------------+
199
9 rows in set (0.00 sec)
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 | | 6006 |
| 2004 | | | 6006 |
| 2005 | china | tnt1 | 4011 |
| 2005 | china | tnt2 | 4013 |
| 2005 | china | tnt3 | 4015 |
| 2005 | china | | 12039 |
| 2005 | | | 12039 |
| 2006 | china | tnt1 | 2010 |
| 2006 | china | tnt2 | 2011 |
| 2006 | china | tnt3 | 2012 |
| 2006 | china | | 6033 |
| 2006 | | | 6033 |
| NULL | | | 24078 |
+------+---------+---------+-------------+
16 rows in set (0.00 sec)
从上面的例子中可以看到第 2 个 SQL 语句的结果比第一个 SQL 语句的结果多出了很多行，
而这些行反映出了更多的信息，例如，第 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 子句进行结果排序。换言之， ROLLUP
和 ORDER BY 是互相排斥的
2、LIMIT 用在 ROLLUP 后面。
17.4 用 BIT GROUP FUNCTIONS 做统计
在本小节，主要介绍如何共同使用 GROUP BY 语句和 BIT_AND、BIT_OR 函数完成统计工作。
这两个函数的一般用途就是做数值之间的逻辑位运算，但是，当把它们与 GROUP BY 子句联
合使用的时候就可以做一些其他的任务。
假设现在有这样一个任务：一个超市需要记录每个用户每次来超市都购买了哪些商品。为了
200
将问题简单化，假设该超市只有面包、牛奶、饼干、啤酒 4 种商品。那么通常该怎么做呢？
一般先建立一个购物单表，里面记录购物发生的时间、顾客信息等；然后再建立一个购物单
明细表，里面记录该顾客所购买的商品。这样设计表结构的优点是顾客所购买的商品的详细
信息可以记录下来，比如数量、单价等，但是如果目前的这个任务只需要知道用户购买商品
的种类和每次购物总价等信息的话，那么这种数据库结构的设计就显得太复杂了。一般还可
能会想到用一个表实现这个功能，并且用一个字段以字符串的形式记录顾客所购买的所有商
品的商品号，这也是一种方法，但是如果顾客一次购买商品比较多的话，需要很大的存储空
间，而且将来做各种统计的时候也会捉襟见肘。
下面给出一种新的解决办法，类似于上面讲到的第二种方案，仍然用一个字段表示顾客购买
商品的信息，但是这个字段是数值型的而不是字符型的，该字段存储一个十进制数字，当它
转换成二进制的时候，那么每一位代表一种商品，而且如果所在位是“1”那么表示顾客购
买了该种商品，“0”表示没有购买该种商品。比如数值的第 1 位代表面包（规定从右向左开
始计算）、第 2 位代表牛奶、第 3 位代表饼干、第 4 位代表啤酒，这样如果一个用户购物单
的商品列的数值为 5，那么二进制表示为 0101，这样从右向左第 1 位和第 3 位是 1，那么就
可以知道这个用户购买了面包和饼干，而如果这个客户有多个这样的购物单（在数据库中就
是有多条记录），把这些购物单按用户分组做 BIT_OR()操作就可以知道这个用户都购买过什
么商品。
下面举例说明一下这个操作，首先初始化一组数据：
mysql> create table order_rab (id int,customer_id int,kind int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into order_rab values (1,1,5),(2,1,4);
Query OK, 2 rows affected (0.00 sec)
mysql> insert into order_rab values (3,2,3),(4,2,4);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from order_rab;
+------+-------------+------+
| id | customer_id | kind |
+------+-------------+------+
| 1 | 1 | 5 |
| 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 子句联合起来，统计一下这两个顾客在这个超市一共都购
买过什么商品，如下例：
mysql> select customer_id,bit_or(kind) from order_rab group by customer_id;
+-------------+--------------+
| customer_id | bit_or(kind) |
201
+-------------+--------------+
| 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 的各个值做了一个“或”操作，最终结果是十进制的 5。逻辑计算公式如下：
# ..0101
# ..0100
# OR ..0000
# ---------
# ..0101
同理，可以用 BIT_AND()统计每个顾客每次来本超市都会购买的商品，具体如下：
mysql> 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 举例说
明一下，逻辑计算公式如下：
# ..0101
# ..0100
# AND ..1111
# ----------
# ..0100
从上面的例子可以看出，这种数据库结构设计的好处就是能用很简洁的数据表示很丰富的信
息，这种方法能够大大地节省存储空间，而且能够提高部分统计计算的速度。不过需要注意
的是，这种设计其实损失了顾客购买商品的详细信息，比如购买商品的数量、当时单价、是
否有折扣、是否有促销等，因此还要根据应用的实际情况有选择地考虑数据库结构设计
202
小写不敏感，因此在 Windows 下 MySQL 数据库名和表名对大小写也不敏感。
列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在
UNIX 中对大小写敏感，但在 Windows 或 Mac OS X 中对大小写不敏感。下面的查询在 UNIX
中会报错，因为它同时引用了别名 a 和 A：
mysql> select id from order_rab a where A.id = 1;
ERROR 1054 (42S22): Unknown column 'A.id' in 'where clause'
然而，该查询在 Windows 中是可以的。要想避免出现差别，最好采用一致的转换，例如，
总是用小写创建并引用数据库名和表名。
在 MySQL 中如何在硬盘上保存、使用表名和数据库名由 lower_case_tables_name 系统变量
决定，可以在启动 mysqld 时设置这个系统变量。lower_case_tables_name 可以采用如表 17-2
所示的任一值。
表 17-2 lower_case_tables_name 的取值范围
值 含义
0 使用 CREATE TABLE 或 CREATE DATABASE 语句指定的大写和小写在硬盘上保存表名和数据库名。名
称对大小写敏感。在 UNIX 系统中的默认设置就是这个值
1 表名在硬盘上以小写保存，名称对大小写敏感。MySQL 将所有表名转换为小写以便存储和查找。
该值为 Windows 和 Mac OS X 系统中的默认值
2 表名和数据库名在硬盘上使用 CREATE TABLE 或 CREATE DATABASE 语句指定的大小写进行保存，但
MySQL 将它们转换为小写以便查找。此值只在对大小写不敏感的文件系统上适用
如果只在一个平台上使用 MySQL，通常不需要更改 lower_case_tables_name 变量。然而，如
果用户想要在对大小写敏感性不同的文件系统的平台之间转移表，就会遇到困难。例如，在
UNIX 中，my_tables 和 MY_tables 是两个不同的表，但在 Windows 中，这两个表名相同。
在 UNIX 中使用 lower_case_tables_name=0，而在 Windows 中使用 lower_case_tables_name=2，
这样可以保留数据库名和表名的大小写。不利之处是必须确保在 Windows 中的所有 SQL 语
句总是正确地使用大小写来引用数据库名和表名，如果 SQL 语句中没有正确引用数据库名
和表名的大小写，那么虽然在 Windows 中能正确执行，但是如果将查询转移到 UNIX 中，大
小写不正确，将会导致查询失败。
注意：1、在 UNIX 中将 lower_case_tables_name 设置为 1 并且重启 mysqld 之前，必须先将旧的
数据库名和表名转换为小写。
2、尽管在某些平台中数据库名和表名对大小写不敏感，但是最好养成在同一查询中使用
相同的大小写来引用给定的数据库名或表名的习惯。
17.6 使用外键需要注意的问题
在 MySQL 中，InnoDB 存储引擎支持对外部关键字约束条件的检查。而对于其他类型存储引
擎的表，当使用 REFERENCES tbl_name(col_name)子句定义列时可以使用外部关键字，但是
该子句没有实际的效果，只作为备忘录或注释来提醒用户目前正定义的列指向另一个表中的
一个列。
例如，下面的 myisam 表外键就没有起作用：
mysql> create table users(id int,name varchar(10),primary key(id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
203
mysql> 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;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into books values(1,'book1',1);
Query OK, 1 row affected (0.00 sec)
如果用 InnoDB 存储引擎建表的话，外键就会起作用，具体如下：
mysql> create table users2(id int,name varchar(10),primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.14 sec)
mysql> create table books2(id int,bookname varchar(10),userid int ,primary
key(id),constraint fk_userid_id foreign key(userid) references users2(id)) engine=innodb;
Query OK, 0 rows affected (0.18 sec)
mysql> insert into books2 values(1,'book1',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`sakila/books2`, CONSTRAINT `fk_userid_id` FOREIGN KEY (`userid`) REFERENCES `users2` (`id`))
而且，用 show create table 命令查看建表语句的时候，发现 MyISAM 存储引擎的并不显示外
键的语句，而 InnoDB 存储引擎的就显示外键语句，具体如下：
mysql> show create table books\G;
*************************** 1. row ***************************
 Table: books
Create Table: CREATE TABLE `books` (
 `id` int(11) NOT NULL DEFAULT '0',
 `bookname` varchar(10) DEFAULT NULL,
 `userid` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_userid_id` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> show create table books2\G;
*************************** 1. row ***************************
 Table: books2
Create Table: CREATE TABLE `books2` (
 `id` int(11) NOT NULL DEFAULT '0',
 `bookname` varchar(10) DEFAULT NULL,
 `userid` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_userid_id` (`userid`),
 CONSTRAINT `fk_userid_id` FOREIGN KEY (`userid`) REFERENCES `users2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
204

本文讲述了在数据库开发时会用到的一些 SQL 小技巧和需要注意的问题。
 RAND()函数与 ORDER BY 字句的配合使用能够实现随机抽取样本的功能，这个技巧
在进行数据统计的时候很方便。
 GROUP BY 的 WITH ROLLUP 字句能够实现类似于 OLAP 的查询。
 BIT 函数与 GROUP BY 子句的联合使用在某些应用场合可以大大降低存储量，提高
统计查询效率。
 MySQL 数据库名和表名的大小写与操作系统对大小写的敏感性关系密切，因此需
要格外引起用户的注意。
 MySQL 的外键功能仅对 InnoDB 存储引擎的表有作用，其他类型存储引擎的表虽然
可以建立外键，但是并不能起到外键的作用。