【MySQL数据库开发之四】MySQL NULL值操作、批处理模式、常用查询的例子、计数行、模式匹配等

本站文章均为 李华明Himi 原创,转载务必在明显处注明：
转载自【黑米GameDev街区】 原文链接: http://www.himigame.com/mysql/781.html

　　　　　　　　　　☞ 点击订阅 ☜ 本博客最新动态!及时将最新博文通知您！

继续上一篇继续讲解MySQL的相关知识点；

     1. NULL 值操作：

NULL值可能令人感到奇怪直到你习惯它。概念上，NULL意味着“没有值”或“未知值”，且它被看作与众不同的值。为了测试NULL，你不能使用算术比较 操作符例如=、<或!=。为了说明它，试试下列查询：

1

2

3

4

5

6

7

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

+----------+-----------+----------+----------+

| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |

+----------+-----------+----------+----------+

|     NULL |      NULL |     NULL |     NULL |

+----------+-----------+----------+----------+

1 row in set (0.05 sec)

很显然你不能通过这些比较得到有意义的结果。相反使用IS NULL和IS NOT NULL操作符：

1

2

3

4

5

6

7

mysql> select 1 is null, 1 is not null;

+-----------+---------------+

| 1 is null | 1 is not null |

+-----------+---------------+

|         0 |             1 |

+-----------+---------------+

1 row in set (0.04 sec)

注意：可以在定义为NOT NULL的列内插入0或空字符串，实际是NOT NULL。

  2.  模式匹配：

MySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。 SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符；

找出所有以y开头的名字；        

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

mysql> select *from people;

+------+------+-------+------------+

| name | age  | sex   | birthday   |

+------+------+-------+------------+

| Himi | 21   | man   | 1990-01-01 |

| xioi | 23   | woman | 1980-03-08 |

| uhi  | 23   | woman | 1980-08-08 |

| ouha | 23   | woman | 1989-04-02 |

| yunu | 20   | man   | 1993-10-19 |

| yyun | 20   | man   | 1993-10-19 |

+------+------+-------+------------+

6 rows in set (0.00 sec)

 

mysql> select* from people where name like 'y%';

+------+------+------+------------+

| name | age  | sex  | birthday   |

+------+------+------+------------+

| yunu | 20   | man  | 1993-10-19 |

| yyun | 20   | man  | 1993-10-19 |

+------+------+------+------------+

2 rows in set (0.00 sec)

      找出所有以i结尾的名字；   

1

2

3

4

5

6

7

8

9

mysql> select* from people where name like '%i';

+------+------+-------+------------+

| name | age  | sex   | birthday   |

+------+------+-------+------------+

| Himi | 21   | man   | 1990-01-01 |

| xioi | 23   | woman | 1980-03-08 |

| uhi  | 23   | woman | 1980-08-08 |

+------+------+-------+------------+

3 rows in set (0.00 sec)

找出所有以包含h的名字；   

1

2

3

4

5

6

7

8

9

mysql> select* from people where name like '%h%';

+------+------+-------+------------+

| name | age  | sex   | birthday   |

+------+------+-------+------------+

| Himi | 21   | man   | 1990-01-01 |

| uhi  | 23   | woman | 1980-08-08 |

| ouha | 23   | woman | 1989-04-02 |

+------+------+-------+------------+

3 rows in set (0.00 sec)

要想找出正好包含3个字符的名字，使用3个“_”模式字符：           

1

2

3

4

5

6

7

mysql> select* from people where name like '___';

+------+------+-------+------------+

| name | age  | sex   | birthday   |

+------+------+-------+------------+

| uhi  | 23   | woman | 1980-08-08 |

+------+------+-------+------------+

1 row in set (0.00 sec)

 由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。

     3.  正则表达式的使用；

扩展正则表达式的一些字符是：

·         ‘.’匹配任何单个的字符。

·         字符类“[...]”   匹配在方括号内的任何字符。例如，“[abc]” 匹配 “a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

·“ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。

• 如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。

• 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。

为了说明扩展正则表达式如何工作，下面使用REGEXP重写上面所示的LIKE查询：

为了找出以“b”开头的名字，使用“^”匹配名字的开始：

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

+--------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth      | death      |

+--------+--------+---------+------+------------+------------+

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |

| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写‘b’。

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

+--------+--------+---------+------+------------+-------+

| name   | owner  | species | sex  | birth      | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |

+--------+--------+---------+------+------------+-------+

为了找出包含一个“w”的名字，使用以下查询：

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |

| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |

+----------+-------+---------+------+------------+------------+

既然如果一个正则表达式出现在值的任何地方，其模式匹配了，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个SQL模式那样。

为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.” 实例在两者之间：

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

你也可以使用“{n}”“重复n次”操作符重写前面的查询：

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

附录G：MySQL正则表达式 提供了关于正则表达式的句法的详细信息。

 4.计数行：

计算表内共计算多少人，如下：

1

2

3

4

5

6

7

mysql> select count(*) from people;

+----------+

| count(*) |

+----------+

|        6 |

+----------+

1 row in set (0.05 sec)

计算指定item的group 数量(使用GROUP BY对每个owner的所有记录分组)：

1

2

3

4

5

6

7

8

9

10

11

12

mysql> select name ,count(*) from people group by name;

+------+----------+

| name | count(*) |

+------+----------+

| Himi |        1 |

| ouha |        1 |

| uhi  |        1 |

| xioi |        1 |

| yunu |        1 |

| yyun |        1 |

+------+----------+

6 rows in set (0.05 sec)

计算指定多个 item的group 数量(使用GROUP BY对每个owner的所有记录分组)：

1

2

3

4

5

6

7

8

9

10

11

12

mysql> select name,age ,count(*) from people group by name,age;

+------+------+----------+

| name | age  | count(*) |

+------+------+----------+

| Himi | 21   |        1 |

| ouha | 23   |        1 |

| uhi  | 23   |        1 |

| xioi | 23   |        1 |

| yunu | 20   |        1 |

| yyun | 20   |        1 |

+------+------+----------+

6 rows in set (0.00 sec)

5.常用查询的例子

  5.1 列的最大值

下面是一些学习如何用MySQL解决一些常见问题的例子。

在一些例子中，使用数据库表“shop”来储存某个商人（经销商）的每件物品(物品号)的价格。假定每个商人对每项物品有一个固定价格，那么(物品，商人)即为该记录的主关键字。

启动命令行工具mysql并选择数据库：

shell> mysql your-database-name

（在大多数MySQL中，你可以使用test数据库）。

你可以使用以下语句创建示例表：

mysql> CREATE TABLE shop (

    -> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

    -> dealer  CHAR(20)                 DEFAULT ''     NOT NULL,

    -> price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,

    -> PRIMARY KEY(article, dealer));

mysql> INSERT INTO shop VALUES

    -> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),

    -> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

执行语句后，表应包含以下内容：

mysql> SELECT * FROM shop;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

|    0001 | A      |  3.45 |

|    0001 | B      |  3.99 |

|    0002 | A      | 10.99 |

|    0003 | B      |  1.45 |

|    0003 | C      |  1.69 |

|    0003 | D      |  1.25 |

|    0004 | D      | 19.95 |

+---------+--------+-------+

3.6.1. 列的最大值

“最大的物品号是什么？”

SELECT MAX(article) AS article FROM shop;+---------+| article |+---------+|       4 |+---------+

3.6.2. 拥有某个列的最大值的行

任务：找出最贵物品的编号、销售商和价格。这很容易用一个子查询做到：

SELECT article, dealer, price

FROM   shop

WHERE  price=(SELECT MAX(price) FROM shop);

另一个解决方案是按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行：

SELECT article, dealer, price

FROM shop

ORDER BY price DESC

LIMIT 1;

注:如果有多项最贵的物品( 例如每个的价格为19.95)，LIMIT解决方案仅仅显示其中一个！

3.6.3. 列的最大值：按组

任务：每项物品的的最高价格是多少？

SELECT article, MAX(price) AS priceFROM   shopGROUP BY article+---------+-------+| article | price |+---------+-------+|    0001 |  3.99 ||    0002 | 10.99 ||    0003 |  1.69 ||    0004 | 19.95 |+---------+-------+

3.6.4. 拥有某个字段的组间最大值的行

任务：对每项物品，找出最贵价格的物品的经销商。

可以用这样一个子查询解决该问题：

SELECT article, dealer, priceFROM   shop s1WHERE  price=(SELECT MAX(s2.price)              FROM shop s2              WHERE s1.article = s2.article);

3.6.5. 使用用户变量

你可以清空MySQL用户变量以记录结果，不必将它们保存到客户端的临时变量中。（参见 9.3节，“用户变量”.）。

例如，要找出价格最高或最低的物品的，其方法是：

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0003 | D      |  1.25 ||    0004 | D      | 19.95 |+---------+--------+-------+

3.6.6. 使用外键

在MySQL中，InnoDB表支持对外部关键字约束条件的检查。参见15.2节，“InnoDB存储引擎”。还可以参见 1.8.5.5节，“外键”。

只是联接两个表时，不需要外部关键字。对于除InnoDB类型的表，当使用REFERENCES tbl_name(col_name)子句定义列时可以使用外部关键字，该子句没有实际的效果，只作为备忘录或注释来提醒，你目前正定义的列指向另一个表中的一个列。执行该语句时，实现下面很重要：

·         MySQL不执行表tbl_name 中的动作，例如作为你正定义的表中的行的动作的响应而删除行；换句话说，该句法不会致使ON DELETE或ON UPDATE行为（如果你在REFERENCES子句中写入ON DELETE或ON UPDATE子句，将被忽略）。

·         该句法可以创建一个column；但不创建任何索引或关键字。

·         如果用该句法定义InnoDB表，将会导致错误。

你可以使用作为联接列创建的列，如下所示：

CREATE TABLE person (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    name CHAR(60) NOT NULL,

    PRIMARY KEY (id)

);

CREATE TABLE shirt (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,

    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),

    PRIMARY KEY (id)

);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES

(NULL, 'polo', 'blue', @last),

(NULL, 'dress', 'white', @last),

(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES

(NULL, 'dress', 'orange', @last),

(NULL, 'polo', 'red', @last),

(NULL, 'dress', 'blue', @last),

(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;

+----+---------------------+

| id | name                |

+----+---------------------+

|  1 | Antonio Paz         |

|  2 | Lilliana Angelovska |

+----+---------------------+

SELECT * FROM shirt;

+----+---------+--------+-------+

| id | style   | color  | owner |

+----+---------+--------+-------+

|  1 | polo    | blue   |     1 |

|  2 | dress   | white  |     1 |

|  3 | t-shirt | blue   |     1 |

|  4 | dress   | orange |     2 |

|  5 | polo    | red    |     2 |

|  6 | dress   | blue   |     2 |

|  7 | t-shirt | white  |     2 |

+----+---------+--------+-------+

SELECT s.* FROM person p, shirt s

 WHERE p.name LIKE 'Lilliana%'

   AND s.owner = p.id

   AND s.color <> 'white';

+----+-------+--------+-------+

| id | style | color  | owner |

+----+-------+--------+-------+

|  4 | dress | orange |     2 |

|  5 | polo  | red    |     2 |

|  6 | dress | blue   |     2 |

+----+-------+--------+-------+

按照这种方式使用，REFERENCES子句不会显示在SHOW CREATE TABLE或DESCRIBE的输出中:

SHOW CREATE TABLE shirt\G

*************************** 1. row ***************************

Table: shirt

Create Table: CREATE TABLE `shirt` (

`id` smallint(5) unsigned NOT NULL auto_increment,

`style` enum('t-shirt','polo','dress') NOT NULL,

`color` enum('red','blue','orange','white','black') NOT NULL,

`owner` smallint(5) unsigned NOT NULL,

PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

在列定义中，按这种方式使用REFERENCES作为注释或“提示”适用于表MyISAM和BerkeleyDB。

3.6.7. 根据两个键搜索

可以充分利用使用单关键字的OR子句，如同AND的处理。

一个比较灵活的例子是寻找两个通过OR组合到一起的关键字：

SELECT field1_index, field2_index FROM test_table

WHERE field1_index = '1' OR  field2_index = '1'

该情形是已经优化过的。参见7.2.6节，“索引合并优化”。

还可以使用UNION将两个单独的SELECT语句的输出合成到一起来更有效地解决该问题。参见13.2.7.2节，“UNION语法
”。

每个SELECT只搜索一个关键字，可以进行优化：

SELECT field1_index, field2_index

    FROM test_table WHERE field1_index = '1'

UNION

SELECT field1_index, field2_index

    FROM test_table WHERE field2_index = '1';

3.6.8. 根据天计算访问量

下面的例子显示了如何使用位组函数来计算每个月中用户访问网页的天数。

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,

             day INT(2) UNSIGNED ZEROFILL);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

            (2000,2,23),(2000,2,23);

示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1

       GROUP BY year,month;

将返回：

+------+-------+------+

| year | month | days |

+------+-------+------+

| 2000 |    01 |    3 |

| 2000 |    02 |    2 |

+------+-------+------+

该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

3.6.9. 使用AUTO_INCREMENT

可以通过AUTO_INCREMENT属性为新的行产生唯一的标识：

CREATE TABLE animals (

     id MEDIUMINT NOT NULL AUTO_INCREMENT,

     name CHAR(30) NOT NULL,

     PRIMARY KEY (id)

 );

INSERT INTO animals (name) VALUES

    ('dog'),('cat'),('penguin'),

    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

将返回：

+----+---------+

| id | name    |

+----+---------+

|  1 | dog     |

|  2 | cat     |

|  3 | penguin |

|  4 | lax     |

|  5 | whale   |

|  6 | ostrich |

+----+---------+

你可以使用LAST_INSERT_ID()SQL函数或mysql_insert_id() C API函数来查询最新的AUTO_INCREMENT值。这些函数与具体连接有关，因此其返回值不会被其它执行插入功能的连接影响。

注释：对于多行插入，LAST_INSERT_ID()和mysql_insert_id()从插入的第一行实际返回AUTO_INCREMENT关键字。在复制设置中，通过该函数可以在其它服务器上正确复制多行插入。

对于MyISAM和BDB表，你可以在第二栏指定AUTO_INCREMENT以及多列索引。此时，AUTO_INCREMENT列生成的值的计算方法为：MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。如果想要将数据放入到排序的组中可以使用该方法。

CREATE TABLE animals (

    grp ENUM('fish','mammal','bird') NOT NULL,

    id MEDIUMINT NOT NULL AUTO_INCREMENT,

    name CHAR(30) NOT NULL,

    PRIMARY KEY (grp,id)

);

INSERT INTO animals (grp,name) VALUES

    ('mammal','dog'),('mammal','cat'),

    ('bird','penguin'),('fish','lax'),('mammal','whale'),

    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

将返回：

+--------+----+---------+

| grp    | id | name    |

+--------+----+---------+

| fish   |  1 | lax     |

| mammal |  1 | dog     |

| mammal |  2 | cat     |

| mammal |  3 | whale   |

| bird   |  1 | penguin |

| bird   |  2 | ostrich |

+--------+----+---------+

请注意在这种情况下（AUTO_INCREMENT列是多列索引的一部分），如果你在任何组中删除有最大AUTO_INCREMENT值的行，将会重新用到AUTO_INCREMENT值。对于MyISAM表也如此,对于该表一般不重复使用AUTO_INCREMENT值。

如果AUTO_INCREMENT列是多索引的一部分，MySQL将使用该索引生成以AUTO_INCREMENT列开始的序列值。。例如，如果animals表含有索引PRIMARY KEY (grp, id)和INDEX(id)，MySQL生成序列值时将忽略PRIMARY KEY。结果是，该表包含一个单个的序列，而不是符合grp值的序列。

要想以AUTO_INCREMENT值开始而不是1，你可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

关于AUTO_INCREMENT的详细信息：

·         如何为列指定AUTO_INCREMENT属性：13.1.5节，“CREATE TABLE语法”和 13.1.2节，“ALTER TABLE语法”。

·         AUTO_INCREMENT的动作取决于SQL模式：5.3.2节，“SQL服务器模式”。

·         找出含有最新AUTO_INCREMENT值的行：12.1.3节，“比较函数和操作符”。

·         设置将用到的AUTO_INCREMENT值: 13.5.3节，“SET语法” 。

·         AUTO_INCREMENT和复制：6.7节，“复制特性和已知问题”.

·         AUTO_INCREMENT相关的可用于复制的Server-system变量(auto_increment_increment和auto_increment_offset)：5.3.3节，“服务器系统变量”。

更多API，可以参考，http://dev.mysql.com/doc/refman/5.1/zh/tutorial.html#retrieving-data