巧用 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 存储引擎的表有作用,其他类型存储引擎的表虽然
可以建立外键,但是并不能起到外键的作用。
mysql从头学一 1.1 小小记忆
最新推荐文章于 2022-09-02 20:35:08 发布