【MySQL数据库开发之四】MySQL 处理模式/常用查询/模式匹配等(下)-阿里云开发者社区

开发者社区> 余二五> 正文

【MySQL数据库开发之四】MySQL 处理模式/常用查询/模式匹配等(下)

简介:
+关注继续查看

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 price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

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

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

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

SELECT article, dealer, price
FROM   shop s1
WHERE  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表,将会导致错误。

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

 


  1. CREATE TABLE person ( 
  2.     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  3.     name CHAR(60) NOT NULL
  4.     PRIMARY KEY (id) 
  5. ); 
  6. CREATE TABLE shirt ( 
  7.     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  8.     style ENUM('t-shirt''polo''dress'NOT NULL
  9.     color ENUM('red''blue''orange''white''black'NOT NULL
  10.     owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id), 
  11.     PRIMARY KEY (id) 
  12. ); 
  13. INSERT INTO person VALUES (NULL'Antonio Paz'); 
  14. SELECT @last := LAST_INSERT_ID(); 
  15. INSERT INTO shirt VALUES 
  16. (NULL'polo''blue', @last), 
  17. (NULL'dress''white', @last), 
  18. (NULL't-shirt''blue', @last); 
  19. INSERT INTO person VALUES (NULL'Lilliana Angelovska'); 
  20. SELECT @last := LAST_INSERT_ID(); 
  21. INSERT INTO shirt VALUES 
  22. (NULL'dress''orange', @last), 
  23. (NULL'polo''red', @last), 
  24. (NULL'dress''blue', @last), 
  25. (NULL't-shirt''white', @last); 
  26. SELECT * FROM person; 
  27. +----+---------------------+ 
  28. | id | name                | 
  29. +----+---------------------+ 
  30. |  1 | Antonio Paz         | 
  31. |  2 | Lilliana Angelovska | 
  32. +----+---------------------+ 
  33. SELECT * FROM shirt; 
  34. +----+---------+--------+-------+ 
  35. | id | style   | color  | owner | 
  36. +----+---------+--------+-------+ 
  37. |  1 | polo    | blue   |     1 | 
  38. |  2 | dress   | white  |     1 | 
  39. |  3 | t-shirt | blue   |     1 | 
  40. |  4 | dress   | orange |     2 | 
  41. |  5 | polo    | red    |     2 | 
  42. |  6 | dress   | blue   |     2 | 
  43. |  7 | t-shirt | white  |     2 | 
  44. +----+---------+--------+-------+ 
  45. SELECT s.* FROM person p, shirt s 
  46.  WHERE p.name LIKE 'Lilliana%' 
  47.    AND s.owner = p.id 
  48.    AND s.color <> 'white'
  49. +----+-------+--------+-------+ 
  50. | id | style | color  | owner | 
  51. +----+-------+--------+-------+ 
  52. |  4 | dress | orange |     2 | 
  53. |  5 | polo  | red    |     2 | 
  54. |  6 | dress | blue   |     2 | 
  55. +----+-------+--------+-------+ 

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

 


  1. SHOW CREATE TABLE shirt\G 
  2. *************************** 1. row *************************** 
  3. Table: shirt 
  4. Create TableCREATE TABLE `shirt` ( 
  5. `id` smallint(5) unsigned NOT NULL auto_increment, 
  6. `style` enum('t-shirt','polo','dress'NOT NULL
  7. `color` enum('red','blue','orange','white','black'NOT NULL
  8. `owner` smallint(5) unsigned NOT NULL
  9. PRIMARY KEY  (`id`) 
  10. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

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

3.6.7. 根据两个键搜索

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

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

 


  1. SELECT field1_index, field2_index FROM test_table 
  2. WHERE field1_index = '1' OR  field2_index = '1' 

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

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

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

 


  1. SELECT field1_index, field2_index 
  2.     FROM test_table WHERE field1_index = '1' 
  3. UNION 
  4. SELECT field1_index, field2_index 
  5.     FROM test_table WHERE field2_index = '1'

3.6.8. 根据天计算访问量

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

 


  1. CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, 
  2.              day INT(2) UNSIGNED ZEROFILL); 
  3. INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), 
  4.             (2000,2,23),(2000,2,23); 

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

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

将返回:

 


  1. +------+-------+------+ 
  2. year | month | days | 
  3. +------+-------+------+ 
  4. | 2000 |    01 |    3 | 
  5. | 2000 |    02 |    2 | 
  6. +------+-------+------+ 

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

3.6.9. 使用AUTO_INCREMENT

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

 


  1. CREATE TABLE animals ( 
  2.      id MEDIUMINT NOT NULL AUTO_INCREMENT, 
  3.      name CHAR(30) NOT NULL
  4.      PRIMARY KEY (id) 
  5.  ); 
  6. INSERT INTO animals (nameVALUES 
  7.     ('dog'),('cat'),('penguin'), 
  8.     ('lax'),('whale'),('ostrich'); 
  9. SELECT * FROM animals; 

将返回:

 


  1. +----+---------+ 
  2. | id | name    | 
  3. +----+---------+ 
  4. |  1 | dog     | 
  5. |  2 | cat     | 
  6. |  3 | penguin | 
  7. |  4 | lax     | 
  8. |  5 | whale   | 
  9. |  6 | ostrich | 
  10. +----+---------+ 

你可以使用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。如果想要将数据放入到排序的组中可以使用该方法。

 


  1. CREATE TABLE animals ( 
  2.     grp ENUM('fish','mammal','bird'NOT NULL
  3.     id MEDIUMINT NOT NULL AUTO_INCREMENT, 
  4.     name CHAR(30) NOT NULL
  5.     PRIMARY KEY (grp,id) 
  6. ); 
  7. INSERT INTO animals (grp,nameVALUES 
  8.     ('mammal','dog'),('mammal','cat'), 
  9.     ('bird','penguin'),('fish','lax'),('mammal','whale'), 
  10.     ('bird','ostrich'); 
  11. SELECT * FROM animals ORDER BY grp,id; 

将返回:

 


  1. +--------+----+---------+ 
  2. | grp    | id | name    | 
  3. +--------+----+---------+ 
  4. | fish   |  1 | lax     | 
  5. | mammal |  1 | dog     | 
  6. | mammal |  2 | cat     | 
  7. | mammal |  3 | whale   | 
  8. | bird   |  1 | penguin | 
  9. | bird   |  2 | ostrich | 
  10. +--------+----+---------+ 

请注意在这种情况下(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










本文转自 xiaominghimi 51CTO博客,原文链接:http://blog.51cto.com/xiaominghimi/908931,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Android记事本开发之SQLite数据库实现
package com.jk.service; /** * 完成数据库的创建和版本更新 */ import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import
1343 0
参考---创建sql数据库复制的发布、订阅的问题处理
操作使用的一些技巧(事务复制类型):1.如何修改一个已经发布的表的属性?将发布所有订阅删除,(发布不用删除),就可以在发布属性的项目中取消表,然后就 可以修改该表了,修改后,再将表加入发布内就可以了.
909 0
【汇编语言/底层开发】8、数据处理的两个基本问题
标题中所指的两个基本问题是:(1)处理的数据在什么地方?(2)要处理的数据有多长?要使得计算机正常工作,这两个问题必须给予说明,否则无法工作。 1、bx, si, di, bp 这四个寄存器用于内存寻址的规则有: (1)在8086中只有这四个寄存器可以用于[]中进行内存单元寻址。
743 0
4.mysql数据库创建,表创建模等模板脚本,mysql_SQL99标准的连接查询(内连接,外连接,满外连接,交叉连接)
 mysql数据库创建,表创建模等模板脚本 -- 用root用户登录系统,执行脚本   -- 创建数据库 create database mydb61 character set utf8 ;   -- 选择数据库 use mydb61;   -- 增加 dbuser1 用户
1362 0
SQLite数据库常用语句及MAC上的SQLite可视化工具MeasSQLlite使用(一)
SQLite数据库常用语句及MAC上的SQLite可视化工具MeasSQLlite使用
15 0
+关注
12613
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载